VIEW: APPS.GMS_TASK_BUDGETS2_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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