View: GMS_STATUS_AWARD_ACCUM_BASE_V

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

View Text

SELECT GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, SUM(NVL(GB.REVENUE_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'REV'
, 1
, 0) )
, SUM(NVL(GB.BUDGET_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'BGT'
, 1
, 0))
, SUM(NVL(GB.ACTUAL_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'EXP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'REQ'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'PO'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'AP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'ENC'
, 1
, 0))
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID
FROM GMS_BALANCES GB
, GMS_AWARDS GA
, GMS_BUDGET_VERSIONS GBV
WHERE GBV.AWARD_ID = GA.AWARD_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 GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID UNION ALL SELECT GBC.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, 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))
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID
FROM GMS_BC_PACKETS GBC
, GMS_AWARDS GA
, GMS_BUDGET_VERSIONS GBV
WHERE GBC.AWARD_ID = GA.AWARD_ID
AND 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') GROUP BY GBC.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID UNION ALL SELECT GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, SUM(PE.REVENUE_AMOUNT)
, 0
, 0
, 0
, 0
, 0
, 0
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID
FROM GMS_AWARDS GA
, PA_EVENTS PE
WHERE PE.PROJECT_ID = GA.AWARD_PROJECT_ID
AND PE.REVENUE_DISTRIBUTED_FLAG='Y'
AND PE.EVENT_TYPE = 'MANUAL' GROUP BY GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID

Columns