View: GMS_AWARD_BAL_V

Product:	GMS - Grants Accounting
Description:	- Retrofitted
Implementation/DBA Data:	APPS.GMS_AWARD_BAL_V

View Text

SELECT PROJECT_ID
, AWARD_ID
, NVL (TASK_ID
, 0)
, RESOURCE_LIST_MEMBER_ID
, BUDGET_VERSION_ID
, PARENT_MEMBER_ID
, START_DATE
, END_DATE
, SUM (NVL (BUDGET_PERIOD_TO_DATE
, 0)) BUDGET
, SUM (NVL (ACTUAL_PERIOD_TO_DATE
, 0)) ACTUALS
, SUM (NVL (ENCUMB_PERIOD_TO_DATE
, 0)) ENCUMBRANCES
, NVL (TOP_TASK_ID
, 0)
FROM GMS_BALANCES
WHERE BALANCE_TYPE IN ('AP'
, 'BGT'
, 'ENC'
, 'EXP'
, 'PO'
, 'REQ') GROUP BY BUDGET_VERSION_ID
, AWARD_ID
, TASK_ID
, PROJECT_ID
, RESOURCE_LIST_MEMBER_ID
, PARENT_MEMBER_ID
, START_DATE
, END_DATE
, TOP_TASK_ID UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, PAP.START_DATE
, PAP.END_DATE
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM PA_PERIODS PAP
, GMS_BC_PACKETS GBC
WHERE GBC.STATUS_CODE = 'A'
AND GBC.TIME_PHASED_TYPE_CODE = 'P'
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN PAP.START_DATE
AND PAP.END_DATE GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.PROJECT_ID
, GBC.TOP_TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, PAP.START_DATE
, PAP.END_DATE UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, GPS.START_DATE
, GPS.END_DATE
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM GL_PERIOD_STATUSES GPS
, GMS_BC_PACKETS GBC
WHERE GBC.STATUS_CODE = 'A'
AND GBC.TIME_PHASED_TYPE_CODE = 'G'
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = GBC.SET_OF_BOOKS_ID
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.PROJECT_ID
, GBC.TOP_TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, GPS.START_DATE
, GPS.END_DATE UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, NVL ( GB.START_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, GREATEST (NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)))
, GREATEST ( NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE))
, NVL (PT.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)) ) ) )
, NVL ( GB.END_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE))
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE)
, NVL (PT.COMPLETION_DATE
, GA.END_DATE_ACTIVE)) ) )
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM PA_BUDGET_ENTRY_METHODS BEM
, GMS_BUDGET_VERSIONS GBV
, GMS_AWARDS GA
, GMS_BC_PACKETS GBC
, PA_PROJECTS_ALL PP
, PA_TASKS PT
, GMS_NONE_BAL_V GB
WHERE GBC.STATUS_CODE = 'A'
AND GBV.BUDGET_VERSION_ID = GBC.BUDGET_VERSION_ID
AND BEM.BUDGET_ENTRY_METHOD_CODE = GBV.BUDGET_ENTRY_METHOD_CODE
AND BEM.TIME_PHASED_TYPE_CODE = 'N'
AND GA.AWARD_ID = GBC.AWARD_ID
AND PP.PROJECT_ID = GBC.PROJECT_ID
AND PT.TASK_ID = GBC.TASK_ID
AND GB.PROJECT_ID(+) = GBC.PROJECT_ID
AND GB.AWARD_ID(+) = GBC.AWARD_ID
AND GB.BUDGET_VERSION_ID(+) = GBC.BUDGET_VERSION_ID
AND GB.TASK_ID(+) = GBC.BUD_TASK_ID
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN NVL (GB.START_DATE
, GBC.EXPENDITURE_ITEM_DATE)
AND NVL (GB.END_DATE
, GBC.EXPENDITURE_ITEM_DATE) GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.TOP_TASK_ID
, GBC.PROJECT_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, NVL ( GB.START_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, GREATEST (NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)))
, GREATEST ( NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE))
, NVL (PT.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)) ) ) )
, NVL ( GB.END_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE))
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE)
, NVL (PT.COMPLETION_DATE
, GA.END_DATE_ACTIVE)) ) ) UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, NVL(GB.START_DATE
, GPS.START_DATE)
, NVL(GB.END_DATE
, GPS.END_DATE)
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM GMS_DTRANGE_BAL_V GB
, GMS_BC_PACKETS GBC
, GL_PERIOD_STATUSES GPS
WHERE GBC.STATUS_CODE = 'A'
AND GBC.TIME_PHASED_TYPE_CODE = 'R'
AND GB.PROJECT_ID(+) = GBC.PROJECT_ID
AND GB.AWARD_ID (+) = GBC.AWARD_ID
AND GB.BUDGET_VERSION_ID (+) = GBC.BUDGET_VERSION_ID
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = GBC.SET_OF_BOOKS_ID
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GBC.PERIOD_NAME = GPS.PERIOD_NAME
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN GB.START_DATE(+)
AND GB.END_DATE(+) GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.PROJECT_ID
, GBC.TOP_TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, NVL(GB.START_DATE
, GPS.START_DATE)
, NVL(GB.END_DATE
, GPS.END_DATE)

Columns