View: GMS_STATUS_ACCUM_PROJ_BASE_V

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

View Text

SELECT GB.AWARD_ID
, GB.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, SUM(NVL(GB.REVENUE_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'REV'
, 1
, 0) )
, SUM(NVL(GB.BUDGET_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'BGT'
, 1
, 0))
, SUM(NVL(GB.ACTUAL_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'EXP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'REQ'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'PO'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'AP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'ENC'
, 1
, 0))
FROM GMS_BALANCES GB
, PA_PROJECTS_ALL PPA
, GMS_BUDGET_VERSIONS GBV
WHERE GB.PROJECT_ID = PPA.PROJECT_ID
AND GBV.BUDGET_VERSION_ID = GB.BUDGET_VERSION_ID
AND GBV.CURRENT_FLAG IN( 'Y'
, 'R')
AND GBV.BUDGET_STATUS_CODE = 'B' GROUP BY GB.AWARD_ID
, GB.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME UNION ALL SELECT GA.AWARD_ID
, GEA.ACTUAL_PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, SUM(PE.REVENUE_AMOUNT)
, 0
, 0
, 0
, 0
, 0
, 0
FROM GMS_AWARDS_ALL GA
, PA_EVENTS PE
, GMS_EVENT_ATTRIBUTE GEA
, PA_PROJECTS_ALL PPA
WHERE PE.PROJECT_ID = GA.AWARD_PROJECT_ID
AND PE.REVENUE_DISTRIBUTED_FLAG='Y'
AND PE.EVENT_TYPE = 'MANUAL'
AND GEA.PROJECT_ID = PE.PROJECT_ID
AND GEA.EVENT_NUM = PE.EVENT_NUM
AND PPA.PROJECT_ID = GEA.ACTUAL_PROJECT_ID GROUP BY GA.AWARD_ID
, GEA.ACTUAL_PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME UNION ALL SELECT GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, 0
, 0
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'REQ'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'PO'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'AP'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'ENC'
, 1
, 0))
FROM GMS_BC_PACKETS GBC
, PA_PROJECTS_ALL PPA
, GMS_BUDGET_VERSIONS GBV
WHERE GBC.STATUS_CODE = 'A'
AND GBV.BUDGET_VERSION_ID = GBC.BUDGET_VERSION_ID
AND GBV.BUDGET_STATUS_CODE = 'B'
AND GBV.CURRENT_FLAG IN ('Y'
, 'R')
AND GBC.PROJECT_ID = PPA.PROJECT_ID GROUP BY GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME

Columns