GA Projects By Grants (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Grants Accounting (GMS-Fusion-SQL's)
1

This GA projects by grants query displays active award and project details for each organization, supporting grant-funded project oversight and status monitoring.

#sqlquery

SELECT 
 -- SQL4Fusion (An Orbit Analytics Project)
 -- This query displays active grant awards and related project details by organization.
 -- Organization Info
  HAOUT.NAME                     	ORGANIZATION,
 -- Award Info	
  OKC.AWARD_NUMBER,	
  OKC.AWARD_NAME,	
  OKC.AWARD_TYPE,	
  OKC.AWARD_PURPOSE_CODE            AWARD_PURPOSE,	
  -- Project Info	
  PPAB.SEGMENT1                  	PROJECT_NUMBER,
  PPAT.NAME                      	PROJECT_NAME,
  PPAB.PROJECT_STATUS_CODE       	PROJECT_STATUS
FROM 
PJF_PROJECTS_ALL_B  			PPAB,
PJF_PROJECTS_ALL_TL 			PPAT,
GMS_AWARD_PROJECTS  			GAP,
HR_ALL_ORGANIZATION_UNITS_TL 	HAOUT,
--Award Details sql
  ( SELECT
      OKHAB.CONTRACT_NUMBER 	AWARD_NUMBER,
      OKHT.COGNOMEN 			AWARD_NAME,
      OKHAB.ID 					AWARD_ID,
      GAHB.AWARD_TYPE,
      GAHB.AWARD_PURPOSE_CODE
    FROM OKC_K_HEADERS_TL		OKHT,
		 OKC_K_HEADERS_ALL_B 	OKHAB,
		 GMS_AWARD_HEADERS_B 	GAHB
    WHERE
      1 = 1
      AND OKHAB.ID = OKHT.ID
      AND OKHAB.ID = GAHB.ID
      AND OKHT.LANGUAGE = USERENV('LANG')
      AND OKHAB.MAJOR_VERSION 	= (	SELECT MAX(MAJOR_VERSION) FROM OKC_K_HEADERS_ALL_B WHERE ID = OKHAB.ID )
      AND OKHT.MAJOR_VERSION 	= ( SELECT MAX(MAJOR_VERSION) FROM OKC_K_HEADERS_TL WHERE ID = OKHT.ID)) OKC
WHERE 1=1
AND PPAB.PROJECT_ID             = PPAT.PROJECT_ID
AND PPAT.LANGUAGE               = USERENV('LANG')
AND GAP.PROJECT_ID 				= PPAB.PROJECT_ID
AND GAP.AWARD_ID 				= OKC.AWARD_ID
AND HAOUT.ORGANIZATION_ID       = PPAB.ORG_ID
AND HAOUT.LANGUAGE              = USERENV('LANG')
AND PPAB.PROJECT_STATUS_CODE 	<> 'CLOSED'