View: GMS_RESG_V

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

View Text

SELECT GMS.PROJECT_ID
, GMS.AWARD_ID
, DECODE(PBEM.ENTRY_LEVEL_CODE
, 'P'
, 0
, GMS.TASK_ID)
, DECODE(PBEM.ENTRY_LEVEL_CODE
, 'P'
, NULL
, GMS_BUDGET_UTILS.GET_TASK_NUMBER(GMS.TASK_ID))
, GMS.BUDGET_VERSION_ID
, DECODE(GMS.PARENT_MEMBER_ID
, NULL
, GMS.RESOURCE_LIST_MEMBER_ID
, 0
, GMS.RESOURCE_LIST_MEMBER_ID
, GMS.PARENT_MEMBER_ID)
, PR.NAME
, DECODE(PBEM.ENTRY_LEVEL_CODE
, 'P'
, 0
, GMS.TOP_TASK_ID)
, GMS.START_DATE
, GMS.END_DATE
, SUM(GMS.BUDGET)
, SUM(GMS.ACTUALS)
, SUM(GMS.ENCUMBRANCES)
FROM GMS_AWARD_BAL_V GMS
, GMS_BUDGET_VERSIONS GBV
, PA_BUDGET_ENTRY_METHODS PBEM
, PA_RESOURCES PR
, PA_RESOURCE_LIST_MEMBERS PRM
WHERE GMS.BUDGET_VERSION_ID = GBV.BUDGET_VERSION_ID
AND GBV.BUDGET_ENTRY_METHOD_CODE = PBEM.BUDGET_ENTRY_METHOD_CODE
AND PRM.RESOURCE_LIST_MEMBER_ID = DECODE(GMS.PARENT_MEMBER_ID
, NULL
, GMS.RESOURCE_LIST_MEMBER_ID
, 0
, GMS.RESOURCE_LIST_MEMBER_ID
, GMS.PARENT_MEMBER_ID)
AND PRM.RESOURCE_ID = PR.RESOURCE_ID
AND NOT EXISTS ( SELECT 1
FROM PA_RESOURCE_LISTS PA
, PA_RESOURCE_LIST_MEMBERS RLM
WHERE RLM.RESOURCE_LIST_MEMBER_ID = GMS.RESOURCE_LIST_MEMBER_ID
AND RLM.RESOURCE_LIST_ID = PA.RESOURCE_LIST_ID
AND PA.GROUP_RESOURCE_TYPE_ID = 0 ) GROUP BY GMS.BUDGET_VERSION_ID
, GMS.AWARD_ID
, GMS.PROJECT_ID
, DECODE(PBEM.ENTRY_LEVEL_CODE
, 'P'
, 0
, GMS.TOP_TASK_ID)
, DECODE(PBEM.ENTRY_LEVEL_CODE
, 'P'
, 0
, GMS.TASK_ID)
, DECODE(PBEM.ENTRY_LEVEL_CODE
, 'P'
, NULL
, GMS_BUDGET_UTILS.GET_TASK_NUMBER(GMS.TASK_ID))
, DECODE(GMS.PARENT_MEMBER_ID
, NULL
, GMS.RESOURCE_LIST_MEMBER_ID
, 0
, GMS.RESOURCE_LIST_MEMBER_ID
, GMS.PARENT_MEMBER_ID)
, PR.NAME
, GMS.START_DATE
, GMS.END_DATE

Columns