View: GMS_TASK_BUDGETS2_V

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

View Text

SELECT V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, T.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.PARENT_TASK_ID
, 0
, 0
, 0
, 0
FROM PA_TASKS T
, GMS_BUDGET_VERSIONS V
WHERE V.PROJECT_ID = T.PROJECT_ID
AND GMS_BUDGET_UTILS.GET_ENTRY_LEVEL_CODE <> 'P' UNION ALL SELECT V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, 0 TASK_ID
, NULL TASK_NUMBER
, NULL TASK_NAME
, 0
, 0
, 0
, 0
, 0
FROM GMS_BUDGET_VERSIONS V
WHERE GMS_BUDGET_UTILS.GET_ENTRY_LEVEL_CODE = 'P' UNION ALL /* THIS QUERY SELECTS THE TASKS FOR WHICH BUDGET LINES EXIST. INCLUDES BOTH LOWEST
AND TOP LEVEL TASKS */ SELECT V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, T.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.PARENT_TASK_ID
, SUM(NVL(L.RAW_COST
, 0))
, SUM(NVL(L.BURDENED_COST
, 0))
, SUM(NVL(L.REVENUE
, 0))
, SUM(DECODE(A.TRACK_AS_LABOR_FLAG
, 'Y'
, NVL(L.QUANTITY
, 0)
, 0))
FROM GMS_BUDGET_LINES L
, PA_TASKS T
, GMS_RESOURCE_ASSIGNMENTS A
, GMS_BUDGET_VERSIONS V
WHERE V.BUDGET_VERSION_ID = A.BUDGET_VERSION_ID
AND A.TASK_ID = T.TASK_ID
AND A.RESOURCE_ASSIGNMENT_ID = L.RESOURCE_ASSIGNMENT_ID
AND GMS_BUDGET_UTILS.GET_ENTRY_LEVEL_CODE <> 'P' GROUP BY V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, T.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.PARENT_TASK_ID /* THIS QUERY SELECTS THOSE TOP TASKS (ALONGWITH THE ROLLED UP TOTALS) FOR WHICH BUDGET LINES HAVE BEEN CREATED AT THE LOWEST TASK LEVEL*/ UNION ALL SELECT V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, T1.TOP_TASK_ID
, T1.TASK_NUMBER
, T1.TASK_NAME
, T1.PARENT_TASK_ID
, SUM(NVL(L.RAW_COST
, 0))
, SUM(NVL(L.BURDENED_COST
, 0))
, SUM(NVL(L.REVENUE
, 0))
, SUM(DECODE(A.TRACK_AS_LABOR_FLAG
, 'Y'
, NVL(L.QUANTITY
, 0)
, 0))
FROM GMS_BUDGET_LINES L
, PA_TASKS T
, PA_TASKS T1
, GMS_RESOURCE_ASSIGNMENTS A
, GMS_BUDGET_VERSIONS V
WHERE V.BUDGET_VERSION_ID = A.BUDGET_VERSION_ID
AND A.TASK_ID = T.TASK_ID
AND A.TASK_ID <> T1.TOP_TASK_ID
AND T.TOP_TASK_ID = T1.TASK_ID
AND T1.PARENT_TASK_ID IS NULL
AND A.RESOURCE_ASSIGNMENT_ID = L.RESOURCE_ASSIGNMENT_ID
AND GMS_BUDGET_UTILS.GET_ENTRY_LEVEL_CODE = 'M' GROUP BY V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, T1.TOP_TASK_ID
, T1.TASK_NUMBER
, T1.TASK_NAME
, T1.PARENT_TASK_ID /* THIS QUERY SELECTS THE ROLLUP FOR INTERMEDIATE LEVEL TASKS THAT ARE FUNDED */ UNION ALL SELECT V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, T.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.PARENT_TASK_ID
, SUM(NVL(L.RAW_COST
, 0))
, SUM(NVL(L.BURDENED_COST
, 0))
, SUM(NVL(L.REVENUE
, 0))
, SUM(DECODE(A.TRACK_AS_LABOR_FLAG
, 'Y'
, NVL(L.QUANTITY
, 0)
, 0))
FROM GMS_BUDGET_LINES L
, PA_TASKS T
, PA_TASKS T1
, GMS_RESOURCE_ASSIGNMENTS A
, GMS_BUDGET_VERSIONS V
WHERE V.BUDGET_VERSION_ID = A.BUDGET_VERSION_ID
AND A.TASK_ID = T1.TASK_ID
AND T.PARENT_TASK_ID IS NOT NULL
AND T1.TASK_ID = T.TASK_ID
AND T1.TASK_ID IN (SELECT TASK_ID
FROM PA_TASKS PT START WITH PT.TASK_ID = T.TASK_ID CONNECT BY PRIOR PT.TASK_ID = PT.PARENT_TASK_ID)
AND EXISTS (SELECT 'X' FROM PA_TASKS PT WHERE PT.PARENT_TASK_ID = T.TASK_ID)
AND A.RESOURCE_ASSIGNMENT_ID = L.RESOURCE_ASSIGNMENT_ID
AND GMS_BUDGET_UTILS.GET_ENTRY_LEVEL_CODE = 'M' GROUP BY V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, T.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.PARENT_TASK_ID UNION ALL SELECT V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE
, 0
, NULL
, NULL
, 0
, SUM(NVL(L.RAW_COST
, 0))
, SUM(NVL(L.BURDENED_COST
, 0))
, SUM(NVL(L.REVENUE
, 0))
, SUM(DECODE(A.TRACK_AS_LABOR_FLAG
, 'Y'
, NVL(L.QUANTITY
, 0)
, 0))
FROM GMS_BUDGET_LINES L
, GMS_RESOURCE_ASSIGNMENTS A
, GMS_BUDGET_VERSIONS V
WHERE V.BUDGET_VERSION_ID = A.BUDGET_VERSION_ID
AND A.TASK_ID = 0
AND A.RESOURCE_ASSIGNMENT_ID = L.RESOURCE_ASSIGNMENT_ID
AND GMS_BUDGET_UTILS.GET_ENTRY_LEVEL_CODE = 'P' GROUP BY V.BUDGET_VERSION_ID
, V.PROJECT_ID
, V.AWARD_ID
, V.BUDGET_TYPE_CODE
, V.VERSION_NUMBER
, V.BUDGET_STATUS_CODE

Columns