This GA Grants - Projects Expenditure query displays expenditure and financial data for active projects linked to awards, showing details by organization, project, task, and expenditure item, helping ensure accurate cost tracking, validate adjustments, and support compliance with sponsor and internal reporting requirements.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Displays projects and related expenditure details for active projects.
-- Organization Info
HAOUT1.NAME OPERATING_UNIT,
HAOUT.NAME EXPENDITURE_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,
PTV.TASK_NUMBER,
PTV.TASK_NAME,
-- Expenditure and Financial Info
PEIA.EXPENDITURE_ITEM_DATE,
PEIA.EXPENDITURE_COMMENT, --The free text comment entered for an expenditure item to further describe work done.
PETT.EXPENDITURE_TYPE_NAME EXPENDITURE_TYPE,
PEIAA.QUANTITY, --the amount of units associated with an latest expenditure item adjustment
PEGA.EXPENDITURE_GROUP, --The user-defined name that uniquely identifies the expenditure group
PTSB.TRANSACTION_SOURCE, --Implementation-defined classification of a transaction loaded into PA from an external system. This is defaulted to TRANSACTION_SOURCE_ID for user-defined sources.
PEIA.PROJECT_CURRENCY_CODE CURRENCY, --Project Currency Code of the Transaction
SUM(PEIA.PROJECT_BURDENED_COST) PROJECT_BURDENED_COST, --Burdened cost in project currency
SUM(PEIA.PROJECT_RAW_COST) PROJECT_RAW_COST --Raw cost in project currency
FROM
PJC_EXP_ITEMS_ALL PEIA,
PJC_EXP_GROUPS_ALL PEGA,
PJF_TXN_SOURCES_B PTSB,
-- Inline view for latest adjustment
( SELECT * FROM
( SELECT A.*, ROW_NUMBER() OVER ( PARTITION BY EXPENDITURE_ITEM_ID ORDER BY ACTIVITY_DATE DESC) AS RN
FROM PJC_EXPEND_ITEM_ADJ_ACTS A
) WHERE RN = 1 ) PEIAA,
HR_ALL_ORGANIZATION_UNITS_TL HAOUT,
HR_ALL_ORGANIZATION_UNITS_TL HAOUT1,
PJF_PROJECTS_ALL_B PPAB,
PJF_PROJECTS_ALL_TL PPAT,
PJF_TASKS_V PTV,
PJF_EXP_TYPES_TL PETT,
GMS_AWARD_PROJECTS GAP,
--Award Details
( 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 PEGA.EXP_GROUP_ID = PEIA.EXP_GROUP_ID
AND PTSB.TRANSACTION_SOURCE_ID = PEIA.TRANSACTION_SOURCE_ID
AND HAOUT.ORGANIZATION_ID = PEIA.EXPENDITURE_ORGANIZATION_ID
AND HAOUT1.ORGANIZATION_ID = PEIA.ORG_ID
AND PPAB.PROJECT_ID = PEIA.PROJECT_ID
AND PPAT.PROJECT_ID = PEIA.PROJECT_ID
AND PTV.TASK_ID = PEIA.TASK_ID
AND PTV.PROJECT_ID = PEIA.PROJECT_ID
AND PETT.EXPENDITURE_TYPE_ID = PEIA.EXPENDITURE_TYPE_ID
AND PEIAA.EXPENDITURE_ITEM_ID = PEIA.EXPENDITURE_ITEM_ID
AND GAP.PROJECT_ID = PEIA.PROJECT_ID
AND GAP.AWARD_ID = OKC.AWARD_ID
AND HAOUT.LANGUAGE = USERENV('LANG')
AND HAOUT1.LANGUAGE = USERENV('LANG')
AND PPAT.LANGUAGE = USERENV('LANG')
AND PETT.LANGUAGE = USERENV('LANG')
AND PPAB.PROJECT_STATUS_CODE <> 'CLOSED' -- Filtering all the projects which are not closed
GROUP BY
OKC.AWARD_NUMBER,
OKC.AWARD_NAME,
OKC.AWARD_TYPE,
OKC.AWARD_PURPOSE_CODE,
PPAB.SEGMENT1,
PPAT.NAME,
PPAB.PROJECT_STATUS_CODE,
PTV.TASK_NUMBER,
PTV.TASK_NAME,
HAOUT1.NAME,
HAOUT.NAME,
PEIA.EXPENDITURE_ITEM_DATE,
PEIA.EXPENDITURE_COMMENT,
PETT.EXPENDITURE_TYPE_NAME,
PEIAA.QUANTITY,
PEGA.EXPENDITURE_GROUP,
PTSB.TRANSACTION_SOURCE,
PEIA.PROJECT_CURRENCY_CODE
ORDER BY
OKC.AWARD_NUMBER,
PPAB.SEGMENT1