Product: | PA - Projects |
---|---|
Description: | This view displays the current, baselined budget lines by PA period |
Implementation/DBA Data: | APPS.PA_TODATE_BASE_ORIG_BUDGET_V |
SELECT V.PROJECT_ID
, A.TASK_ID
, V.BUDGET_TYPE_CODE
, A.RESOURCE_LIST_MEMBER_ID
, M.ALIAS
, M.RESOURCE_LIST_ID
, M.RESOURCE_ID
, V.RESOURCE_ACCUMULATED_FLAG
, DECODE(CURRENT_FLAG
, 'Y'
, NVL(L.RAW_COST
, 0)
, 0) BASE_RAW_COST
, DECODE(CURRENT_FLAG
, 'Y'
, NVL(L.BURDENED_COST
, 0)
, 0) BASE_BURDENED_COST
, DECODE(CURRENT_FLAG
, 'Y'
, NVL(L.REVENUE
, 0)
, 0) BASE_REVENUE
, DECODE(CURRENT_FLAG
, 'Y'
, NVL(L.QUANTITY
, 0)
, 0) BASE_QUANTITY
, DECODE(CURRENT_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, NVL(L.QUANTITY
, 0)))
, 0) BASE_LABOR_QUANTITY
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, NVL(L.RAW_COST
, 0)
, 0) ORIG_RAW_COST
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, NVL(L.BURDENED_COST
, 0)
, 0) ORIG_BURDENED_COST
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, NVL(L.REVENUE
, 0)
, 0) ORIG_REVENUE
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, NVL(L.QUANTITY
, 0)
, 0) ORIG_QUANTITY
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, NVL(L.QUANTITY
, 0)))
, 0) ORIG_LABOR_QUANTITY
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'C')
, 0) RAW_COST_PTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'C')
, 0) BURDENED_COST_PTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'C')
, 0) REVENUE_PTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'C')
, 0) QUANTITY_PTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'C')))
, 0) LABOR_QUANTITY_PTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'P')
, 0) RAW_COST_PP_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'P')
, 0) BURDENED_COST_PP_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'P')
, 0) REVENUE_PP_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'P')
, 0) QUANTITY_PP_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'P')))
, 0) LABOR_QUANTITY_PP_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'Y')
, 0) RAW_COST_YTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'Y')
, 0) BURDENED_COST_YTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'Y')
, 0) REVENUE_YTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'Y')
, 0) QUANTITY_YTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'Y')))
, 0) LABOR_QUANTITY_YTD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'I')
, 0) RAW_COST_ITD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'I')
, 0) BURDENED_COST_ITD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'I')
, 0) REVENUE_ITD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'I')
, 0) QUANTITY_ITD_BASE
, DECODE(CURRENT_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'I')))
, 0) LABOR_QUANTITY_ITD_BASE
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'C')
, 0) RAW_COST_PTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'C')
, 0) BURDENED_COST_PTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'C')
, 0) REVENUE_PTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'C')
, 0) QUANTITY_PTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'C')))
, 0) LABOR_QUANTITY_PTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'P')
, 0) RAW_COST_PP_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'P')
, 0) BURDENED_COST_PP_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'P')
, 0) REVENUE_PP_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'P')
, 0) QUANTITY_PP_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'P')))
, 0) LABOR_QUANTITY_PP_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'Y')
, 0) RAW_COST_YTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'Y')
, 0) BURDENED_COST_YTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'Y')
, 0) REVENUE_YTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'Y')
, 0) QUANTITY_YTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'Y')))
, 0) LABOR_QUANTITY_YTD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.RAW_COST
, 'I')
, 0) RAW_COST_ITD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.BURDENED_COST
, 'I')
, 0) BURDENED_COST_ITD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.REVENUE
, 'I')
, 0) REVENUE_ITD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'I')
, 0) QUANTITY_ITD_ORIG
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, TO_NUMBER(DECODE(A.TRACK_AS_LABOR_FLAG
, 'N'
, NULL
, PA_ACCUM_UTILS.GET_SPREAD_AMOUNT_VAL (L.START_DATE
, L.END_DATE
, L.QUANTITY
, 'I')))
, 0) LABOR_QUANTITY_ITD_ORIG
, DECODE(CURRENT_FLAG
, 'Y'
, A.UNIT_OF_MEASURE
, NULL) UNIT_OF_MEASURE_BASE
, DECODE(CURRENT_ORIGINAL_FLAG
, 'Y'
, A.UNIT_OF_MEASURE
, NULL) UNIT_OF_MEASURE_ORIG
, V.FIN_PLAN_TYPE_ID
, V.VERSION_TYPE
, V.APPROVED_COST_PLAN_TYPE_FLAG
, V.APPROVED_REV_PLAN_TYPE_FLAG
, A.TRACK_AS_LABOR_FLAG
, A.RESOURCE_CLASS_CODE
, V.PRIMARY_COST_FORECAST_FLAG
, V.PRIMARY_REV_FORECAST_FLAG
FROM PA_RESOURCE_LIST_MEMBERS M
, PA_BUDGET_LINES L
, PA_RESOURCE_ASSIGNMENTS A
, PA_BUDGET_VERSIONS V
WHERE M.RESOURCE_LIST_MEMBER_ID = A.RESOURCE_LIST_MEMBER_ID
AND L.RESOURCE_ASSIGNMENT_ID = A.RESOURCE_ASSIGNMENT_ID
AND NVL(M.MIGRATION_CODE
, '-99') <> 'N'
AND A.BUDGET_VERSION_ID = V.BUDGET_VERSION_ID
AND (V.CURRENT_ORIGINAL_FLAG='Y' OR V.CURRENT_FLAG = 'Y')