View: GMS_STATUS_RESOURCES_BASE_V

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

View Text

SELECT GB.AWARD_ID
, GB.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GB.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, 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))
, GB.START_DATE
, GB.END_DATE
FROM GMS_BALANCES GB
, PA_RESOURCES PAR
, GMS_BUDGET_VERSIONS GBV
, PA_RESOURCE_LIST_MEMBERS PARLM
, PA_RESOURCE_LISTS_ALL_BG PARLAB
, PA_PROJECTS_ALL PPA
WHERE GB.BUDGET_VERSION_ID= GBV.BUDGET_VERSION_ID
AND GBV.CURRENT_FLAG IN ('Y'
, 'R')
AND GBV.BUDGET_STATUS_CODE = 'B'
AND GBV. RESOURCE_LIST_ID = PARLM.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_ID = PARLAB.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_MEMBER_ID = GB.RESOURCE_LIST_MEMBER_ID
AND PAR.RESOURCE_ID = PARLM.RESOURCE_ID
AND PPA.PROJECT_ID = GB.PROJECT_ID GROUP BY GB.AWARD_ID
, GB.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GB.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, GB.START_DATE
, GB.END_DATE UNION ALL SELECT GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GBC.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, 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))
, GBC.BUDGET_PERIOD_START_DATE
, GBC.BUDGET_PERIOD_END_DATE
FROM GMS_BC_PACKETS GBC
, PA_RESOURCES PAR
, GMS_BUDGET_VERSIONS GBV
, PA_RESOURCE_LIST_MEMBERS PARLM
, PA_RESOURCE_LISTS_ALL_BG PARLAB
, PA_PROJECTS_ALL PPA
WHERE GBC.STATUS_CODE = 'A'
AND GBC.BUDGET_VERSION_ID = GBV.BUDGET_VERSION_ID
AND GBV.CURRENT_FLAG IN ('Y'
, 'R')
AND GBV.BUDGET_STATUS_CODE = 'B'
AND GBV. RESOURCE_LIST_ID = PARLM.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_ID = PARLAB.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_MEMBER_ID = GBC.RESOURCE_LIST_MEMBER_ID
AND PAR.RESOURCE_ID = PARLM.RESOURCE_ID
AND PPA.PROJECT_ID = GBC.PROJECT_ID GROUP BY GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GBC.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, GBC.BUDGET_PERIOD_START_DATE
, GBC.BUDGET_PERIOD_END_DATE

Columns