[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select
PAB.PROJECT_ID,
PAB.BUDGET_TYPE_CODE,
PAB.fin_plan_type_id,
PAB.TASK_ID task_id,
PAB.RESOURCE_LIST_MEMBER_ID,
PAB.RESOURCE_LIST_ID,
PAB.RESOURCE_ID,
----------------------
SUM(NVL(PAB.BASE_RAW_COST,0)) BASE_RAW_COST,
SUM(NVL(PAB.BASE_BURDENED_COST,0)) BASE_BURDENED_COST,
SUM(NVL(PAB.BASE_REVENUE,0)) BASE_REVENUE,
SUM(NVL(PAB.BASE_QUANTITY,0)) BASE_QUANTITY,
SUM(NVL(PAB.BASE_LABOR_QUANTITY,0)) BASE_LABOR_QUANTITY,
SUM(NVL(PAB.ORIG_RAW_COST,0)) ORIG_RAW_COST,
SUM(NVL(PAB.ORIG_BURDENED_COST,0)) ORIG_BURDENED_COST,
SUM(NVL(PAB.ORIG_REVENUE,0)) ORIG_REVENUE,
SUM(NVL(PAB.ORIG_QUANTITY,0)) ORIG_QUANTITY,
SUM(NVL(PAB.ORIG_LABOR_QUANTITY,0)) ORIG_LABOR_QUANTITY,
----------------------
SUM(NVL(PAB.RAW_COST_ITD_BASE,0)) BASE_RAW_COST_ITD,
SUM(NVL(PAB.BURDENED_COST_ITD_BASE,0)) BASE_BURDENED_COST_ITD,
SUM(NVL(PAB.REVENUE_ITD_BASE,0)) BASE_REVENUE_ITD,
SUM(NVL(PAB.QUANTITY_ITD_BASE,0)) BASE_QUANTITY_ITD,
SUM(NVL(PAB.LABOR_QUANTITY_ITD_BASE,0)) BASE_LABOR_QUANTITY_ITD,
SUM(NVL(PAB.RAW_COST_PTD_BASE,0)) BASE_RAW_COST_PTD,
SUM(NVL(PAB.BURDENED_COST_PTD_BASE,0)) BASE_BURDENED_COST_PTD,
SUM(NVL(PAB.REVENUE_PTD_BASE,0)) BASE_REVENUE_PTD,
SUM(NVL(PAB.QUANTITY_PTD_BASE,0)) BASE_QUANTITY_PTD,
SUM(NVL(PAB.LABOR_QUANTITY_PTD_BASE,0)) BASE_LABOR_QUANTITY_PTD,
SUM(NVL(PAB.RAW_COST_PP_BASE,0)) BASE_RAW_COST_PP,
SUM(NVL(PAB.BURDENED_COST_PP_BASE,0)) BASE_BURDENED_COST_PP,
SUM(NVL(PAB.REVENUE_PP_BASE,0)) BASE_REVENUE_PP,
SUM(NVL(PAB.QUANTITY_PP_BASE,0)) BASE_QUANTITY_PP,
SUM(NVL(PAB.LABOR_QUANTITY_PP_BASE,0)) BASE_LABOR_QUANTITY_PP,
SUM(NVL(PAB.RAW_COST_YTD_BASE,0)) BASE_RAW_COST_YTD,
SUM(NVL(PAB.BURDENED_COST_YTD_BASE,0)) BASE_BURDENED_COST_YTD,
SUM(NVL(PAB.REVENUE_YTD_BASE,0)) BASE_REVENUE_YTD,
SUM(NVL(PAB.QUANTITY_YTD_BASE,0)) BASE_QUANTITY_YTD,
SUM(NVL(PAB.LABOR_QUANTITY_YTD_BASE,0)) BASE_LABOR_QUANTITY_YTD,
SUM(NVL(PAB.RAW_COST_ITD_ORIG,0)) ORIG_RAW_COST_ITD,
SUM(NVL(PAB.BURDENED_COST_ITD_ORIG,0)) ORIG_BURDENED_COST_ITD,
SUM(NVL(PAB.REVENUE_ITD_ORIG,0)) ORIG_REVENUE_ITD,
SUM(NVL(PAB.QUANTITY_ITD_ORIG,0)) ORIG_QUANTITY_ITD,
SUM(NVL(PAB.LABOR_QUANTITY_ITD_ORIG,0)) ORIG_LABOR_QUANTITY_ITD,
SUM(NVL(PAB.RAW_COST_PTD_ORIG,0)) ORIG_RAW_COST_PTD,
SUM(NVL(PAB.BURDENED_COST_PTD_ORIG,0)) ORIG_BURDENED_COST_PTD,
SUM(NVL(PAB.REVENUE_PTD_ORIG,0)) ORIG_REVENUE_PTD,
SUM(NVL(PAB.QUANTITY_PTD_ORIG,0)) ORIG_QUANTITY_PTD,
SUM(NVL(PAB.LABOR_QUANTITY_PTD_ORIG,0)) ORIG_LABOR_QUANTITY_PTD,
SUM(NVL(PAB.RAW_COST_PP_ORIG,0)) ORIG_RAW_COST_PP,
SUM(NVL(PAB.BURDENED_COST_PP_ORIG,0)) ORIG_BURDENED_COST_PP,
SUM(NVL(PAB.REVENUE_PP_ORIG,0)) ORIG_REVENUE_PP,
SUM(NVL(PAB.QUANTITY_PP_ORIG,0)) ORIG_QUANTITY_PP,
SUM(NVL(PAB.LABOR_QUANTITY_PP_ORIG,0)) ORIG_LABOR_QUANTITY_PP,
SUM(NVL(PAB.RAW_COST_YTD_ORIG,0)) ORIG_RAW_COST_YTD,
SUM(NVL(PAB.BURDENED_COST_YTD_ORIG,0)) ORIG_BURDENED_COST_YTD,
SUM(NVL(PAB.REVENUE_YTD_ORIG,0)) ORIG_REVENUE_YTD,
SUM(NVL(PAB.QUANTITY_YTD_ORIG,0)) ORIG_QUANTITY_YTD,
SUM(NVL(PAB.LABOR_QUANTITY_YTD_ORIG,0)) ORIG_LABOR_QUANTITY_YTD,
PAB.UNIT_OF_MEASURE_BASE BASE_UNIT_OF_MEASURE,
PAB.UNIT_OF_MEASURE_ORIG ORIG_UNIT_OF_MEASURE,
PAB.ROLLUP_QUANTITY_FLAG,
PAB.RESOURCE_LIST_ASSIGNMENT_ID
FROM
(
SELECT
BGT.PROJECT_ID,
BGT.BUDGET_TYPE_CODE,
BGT.fin_plan_type_id,
BGT.TASK_ID,
BGT.RESOURCE_LIST_MEMBER_ID,
BGT.RESOURCE_LIST_ID,
BGT.RESOURCE_ID,
BGT.BASE_RAW_COST,
BGT.BASE_BURDENED_COST,
BGT.BASE_REVENUE,
BGT.BASE_QUANTITY,
BGT.BASE_LABOR_QUANTITY,
BGT.ORIG_RAW_COST,
BGT.ORIG_BURDENED_COST,
BGT.ORIG_REVENUE,
BGT.ORIG_QUANTITY,
BGT.ORIG_LABOR_QUANTITY,
BGT.RAW_COST_ITD_BASE,
BGT.BURDENED_COST_ITD_BASE,
BGT.REVENUE_ITD_BASE,
BGT.QUANTITY_ITD_BASE,
BGT.LABOR_QUANTITY_ITD_BASE,
BGT.RAW_COST_PTD_BASE,
BGT.BURDENED_COST_PTD_BASE,
BGT.REVENUE_PTD_BASE,
BGT.QUANTITY_PTD_BASE,
BGT.LABOR_QUANTITY_PTD_BASE,
BGT.RAW_COST_PP_BASE,
BGT.BURDENED_COST_PP_BASE,
BGT.REVENUE_PP_BASE,
BGT.QUANTITY_PP_BASE,
BGT.LABOR_QUANTITY_PP_BASE,
BGT.RAW_COST_YTD_BASE,
BGT.BURDENED_COST_YTD_BASE,
BGT.REVENUE_YTD_BASE,
BGT.QUANTITY_YTD_BASE,
BGT.LABOR_QUANTITY_YTD_BASE,
BGT.RAW_COST_ITD_ORIG,
BGT.BURDENED_COST_ITD_ORIG,
BGT.REVENUE_ITD_ORIG,
BGT.QUANTITY_ITD_ORIG,
BGT.LABOR_QUANTITY_ITD_ORIG,
BGT.RAW_COST_PTD_ORIG,
BGT.BURDENED_COST_PTD_ORIG,
BGT.REVENUE_PTD_ORIG,
BGT.QUANTITY_PTD_ORIG,
BGT.LABOR_QUANTITY_PTD_ORIG,
BGT.RAW_COST_PP_ORIG,
BGT.BURDENED_COST_PP_ORIG,
BGT.REVENUE_PP_ORIG,
BGT.QUANTITY_PP_ORIG,
BGT.LABOR_QUANTITY_PP_ORIG,
BGT.RAW_COST_YTD_ORIG,
BGT.BURDENED_COST_YTD_ORIG,
BGT.REVENUE_YTD_ORIG,
BGT.QUANTITY_YTD_ORIG,
BGT.LABOR_QUANTITY_YTD_ORIG,
BGT.UNIT_OF_MEASURE_BASE,
BGT.UNIT_OF_MEASURE_ORIG,
PAR.ROLLUP_QUANTITY_FLAG,
PARLA.RESOURCE_LIST_ASSIGNMENT_ID
FROM PA_TODATE_BASE_ORIG_BUDGET_V BGT
, PA_RESOURCES PAR
, PA_RESOURCE_LIST_ASSIGNMENTS PARLA
WHERE BGT.PROJECT_ID = x_project_id
and BGT.RESOURCE_ACCUMULATED_FLAG = 'N'
and BGT.RESOURCE_ID = PAR.RESOURCE_ID
And PARLA.PROJECT_ID = x_project_id
and PARLA.RESOURCE_LIST_ID = BGT.RESOURCE_LIST_ID
and bgt.budget_type_code IS NOT NULL -- r11.5.7 Budget Model
and bgt.budget_type_code = NVL(x_budget_type_code, bgt.budget_type_code) -- Added by Sachin.
UNION ALL
SELECT
BGT.PROJECT_ID,
to_char(BGT.fin_plan_type_id) BUDGET_TYPE_CODE,
BGT.fin_plan_type_id,
BGT.TASK_ID,
BGT.RESOURCE_LIST_MEMBER_ID,
BGT.RESOURCE_LIST_ID,
BGT.RESOURCE_ID,
BGT.BASE_RAW_COST,
BGT.BASE_BURDENED_COST,
BGT.BASE_REVENUE,
BGT.BASE_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.BASE_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.BASE_LABOR_QUANTITY) BASE_LABOR_QUANTITY,
BGT.ORIG_RAW_COST,
BGT.ORIG_BURDENED_COST,
BGT.ORIG_REVENUE,
BGT.ORIG_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.ORIG_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.ORIG_LABOR_QUANTITY) ORIG_LABOR_QUANTITY,
BGT.RAW_COST_ITD_BASE,
BGT.BURDENED_COST_ITD_BASE,
BGT.REVENUE_ITD_BASE,
BGT.QUANTITY_ITD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_ITD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_BASE) LABOR_QUANTITY_ITD_BASE,
BGT.RAW_COST_PTD_BASE,
BGT.BURDENED_COST_PTD_BASE,
BGT.REVENUE_PTD_BASE,
BGT.QUANTITY_PTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_BASE) LABOR_QUANTITY_PTD_BASE,
BGT.RAW_COST_PP_BASE,
BGT.BURDENED_COST_PP_BASE,
BGT.REVENUE_PP_BASE,
BGT.QUANTITY_PP_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PP_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_BASE) LABOR_QUANTITY_PP_BASE,
BGT.RAW_COST_YTD_BASE,
BGT.BURDENED_COST_YTD_BASE,
BGT.REVENUE_YTD_BASE,
BGT.QUANTITY_YTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_YTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_BASE) LABOR_QUANTITY_YTD_BASE,
BGT.RAW_COST_ITD_ORIG,
BGT.BURDENED_COST_ITD_ORIG,
BGT.REVENUE_ITD_ORIG,
BGT.QUANTITY_ITD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_ITD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_ORIG) LABOR_QUANTITY_ITD_ORIG,
BGT.RAW_COST_PTD_ORIG,
BGT.BURDENED_COST_PTD_ORIG,
BGT.REVENUE_PTD_ORIG,
BGT.QUANTITY_PTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_ORIG) LABOR_QUANTITY_PTD_ORIG,
BGT.RAW_COST_PP_ORIG,
BGT.BURDENED_COST_PP_ORIG,
BGT.REVENUE_PP_ORIG,
BGT.QUANTITY_PP_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PP_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_ORIG) LABOR_QUANTITY_PP_ORIG,
BGT.RAW_COST_YTD_ORIG,
BGT.BURDENED_COST_YTD_ORIG,
BGT.REVENUE_YTD_ORIG,
BGT.QUANTITY_YTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_YTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_ORIG) LABOR_QUANTITY_YTD_ORIG,
BGT.UNIT_OF_MEASURE_BASE,
BGT.UNIT_OF_MEASURE_ORIG,
PAR.ROLLUP_QUANTITY_FLAG,
PARLA.RESOURCE_LIST_ASSIGNMENT_ID
FROM PA_TODATE_BASE_ORIG_BUDGET_V BGT
, PA_RESOURCES PAR
, PA_RESOURCE_LIST_ASSIGNMENTS PARLA
WHERE BGT.PROJECT_ID = x_project_id
and BGT.RESOURCE_ACCUMULATED_FLAG = 'N'
and BGT.RESOURCE_ID = PAR.RESOURCE_ID
And PARLA.PROJECT_ID = x_project_id
and PARLA.RESOURCE_LIST_ID = BGT.RESOURCE_LIST_ID
and bgt.budget_type_code IS NULL -- FP Model Plan Type
and x_budget_type_code is null -- Added by Sachin
UNION ALL
SELECT
BGT.PROJECT_ID,
'AC' BUDGET_TYPE_CODE,
BGT.fin_plan_type_id,
BGT.TASK_ID,
BGT.RESOURCE_LIST_MEMBER_ID,
BGT.RESOURCE_LIST_ID,
BGT.RESOURCE_ID,
BGT.BASE_RAW_COST,
BGT.BASE_BURDENED_COST,
0 BASE_REVENUE,
BGT.BASE_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.BASE_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.BASE_LABOR_QUANTITY) BASE_LABOR_QUANTITY,
BGT.ORIG_RAW_COST,
BGT.ORIG_BURDENED_COST,
0 ORIG_REVENUE,
BGT.ORIG_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.ORIG_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.ORIG_LABOR_QUANTITY) ORIG_LABOR_QUANTITY,
BGT.RAW_COST_ITD_BASE,
BGT.BURDENED_COST_ITD_BASE,
0 REVENUE_ITD_BASE,
BGT.QUANTITY_ITD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_ITD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_BASE) LABOR_QUANTITY_ITD_BASE,
BGT.RAW_COST_PTD_BASE,
BGT.BURDENED_COST_PTD_BASE,
0 REVENUE_PTD_BASE,
BGT.QUANTITY_PTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_BASE) LABOR_QUANTITY_PTD_BASE,
BGT.RAW_COST_PP_BASE,
BGT.BURDENED_COST_PP_BASE,
0 REVENUE_PP_BASE,
BGT.QUANTITY_PP_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PP_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_BASE) LABOR_QUANTITY_PP_BASE,
BGT.RAW_COST_YTD_BASE,
BGT.BURDENED_COST_YTD_BASE,
0 REVENUE_YTD_BASE,
BGT.QUANTITY_YTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_YTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_BASE) LABOR_QUANTITY_YTD_BASE,
BGT.RAW_COST_ITD_ORIG,
BGT.BURDENED_COST_ITD_ORIG,
0 REVENUE_ITD_ORIG,
BGT.QUANTITY_ITD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_ITD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_ORIG) LABOR_QUANTITY_ITD_ORIG,
BGT.RAW_COST_PTD_ORIG,
BGT.BURDENED_COST_PTD_ORIG,
0 REVENUE_PTD_ORIG,
BGT.QUANTITY_PTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_ORIG) LABOR_QUANTITY_PTD_ORIG,
BGT.RAW_COST_PP_ORIG,
BGT.BURDENED_COST_PP_ORIG,
0 REVENUE_PP_ORIG,
BGT.QUANTITY_PP_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PP_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_ORIG) LABOR_QUANTITY_PP_ORIG,
BGT.RAW_COST_YTD_ORIG,
BGT.BURDENED_COST_YTD_ORIG,
0 REVENUE_YTD_ORIG,
BGT.QUANTITY_YTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_YTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_ORIG) LABOR_QUANTITY_YTD_ORIG,
BGT.UNIT_OF_MEASURE_BASE,
BGT.UNIT_OF_MEASURE_ORIG,
PAR.ROLLUP_QUANTITY_FLAG,
PARLA.RESOURCE_LIST_ASSIGNMENT_ID
FROM PA_TODATE_BASE_ORIG_BUDGET_V BGT
, PA_RESOURCES PAR
, PA_RESOURCE_LIST_ASSIGNMENTS PARLA
WHERE BGT.PROJECT_ID = x_project_id
and BGT.RESOURCE_ACCUMULATED_FLAG = 'N'
and BGT.RESOURCE_ID = PAR.RESOURCE_ID
And PARLA.PROJECT_ID = x_project_id
and PARLA.RESOURCE_LIST_ID = BGT.RESOURCE_LIST_ID
and bgt.budget_type_code IS NULL -- FP Model PSI AC Record
and nvl(bgt.approved_cost_plan_type_flag,'N') = 'Y' -- -- APPROVED C-O-S-T
and 'AC' = NVL(x_budget_type_code, 'AC') -- Added by Sachin.
UNION ALL
SELECT
BGT.PROJECT_ID,
'AR' BUDGET_TYPE_CODE,
BGT.fin_plan_type_id,
BGT.TASK_ID,
BGT.RESOURCE_LIST_MEMBER_ID,
BGT.RESOURCE_LIST_ID,
BGT.RESOURCE_ID,
0 BASE_RAW_COST,
0 BASE_BURDENED_COST,
BGT.BASE_REVENUE,
BGT.BASE_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.BASE_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.BASE_LABOR_QUANTITY) BASE_LABOR_QUANTITY,
0 ORIG_RAW_COST,
0 ORIG_BURDENED_COST,
BGT.ORIG_REVENUE,
BGT.ORIG_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.ORIG_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.ORIG_LABOR_QUANTITY) ORIG_LABOR_QUANTITY,
0 RAW_COST_ITD_BASE,
0 BURDENED_COST_ITD_BASE,
BGT.REVENUE_ITD_BASE,
BGT.QUANTITY_ITD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_ITD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_BASE) LABOR_QUANTITY_ITD_BASE,
0 RAW_COST_PTD_BASE,
0 BURDENED_COST_PTD_BASE,
BGT.REVENUE_PTD_BASE,
BGT.QUANTITY_PTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_BASE) LABOR_QUANTITY_PTD_BASE,
0 RAW_COST_PP_BASE,
0 BURDENED_COST_PP_BASE,
BGT.REVENUE_PP_BASE,
BGT.QUANTITY_PP_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PP_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_BASE) LABOR_QUANTITY_PP_BASE,
0 RAW_COST_YTD_BASE,
0 BURDENED_COST_YTD_BASE,
BGT.REVENUE_YTD_BASE,
BGT.QUANTITY_YTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_YTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_BASE) LABOR_QUANTITY_YTD_BASE,
0 RAW_COST_ITD_ORIG,
0 BURDENED_COST_ITD_ORIG,
BGT.REVENUE_ITD_ORIG,
BGT.QUANTITY_ITD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_ITD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_ORIG) LABOR_QUANTITY_ITD_ORIG,
0 RAW_COST_PTD_ORIG,
0 BURDENED_COST_PTD_ORIG,
BGT.REVENUE_PTD_ORIG,
BGT.QUANTITY_PTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_ORIG) LABOR_QUANTITY_PTD_ORIG,
0 RAW_COST_PP_ORIG,
0 BURDENED_COST_PP_ORIG,
BGT.REVENUE_PP_ORIG,
BGT.QUANTITY_PP_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PP_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_ORIG) LABOR_QUANTITY_PP_ORIG,
0 RAW_COST_YTD_ORIG,
0 BURDENED_COST_YTD_ORIG,
BGT.REVENUE_YTD_ORIG,
BGT.QUANTITY_YTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_YTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_ORIG) LABOR_QUANTITY_YTD_ORIG,
BGT.UNIT_OF_MEASURE_BASE,
BGT.UNIT_OF_MEASURE_ORIG,
PAR.ROLLUP_QUANTITY_FLAG,
PARLA.RESOURCE_LIST_ASSIGNMENT_ID
FROM PA_TODATE_BASE_ORIG_BUDGET_V BGT
, PA_RESOURCES PAR
, PA_RESOURCE_LIST_ASSIGNMENTS PARLA
WHERE BGT.PROJECT_ID = x_project_id
and BGT.RESOURCE_ACCUMULATED_FLAG = 'N'
and BGT.RESOURCE_ID = PAR.RESOURCE_ID
And PARLA.PROJECT_ID = x_project_id
and PARLA.RESOURCE_LIST_ID = BGT.RESOURCE_LIST_ID
and bgt.budget_type_code IS NULL -- FP Model PSI AR Record
and nvl(bgt.approved_rev_plan_type_flag,'N') = 'Y' -- -- APPROVED R-E-V-E-N-U-E
and 'AR' = NVL(x_budget_type_code, 'AR') -- Added by Sachin.
UNION ALL
SELECT
BGT.PROJECT_ID,
'FC' BUDGET_TYPE_CODE,
BGT.fin_plan_type_id,
BGT.TASK_ID,
BGT.RESOURCE_LIST_MEMBER_ID,
BGT.RESOURCE_LIST_ID,
BGT.RESOURCE_ID,
BGT.BASE_RAW_COST,
BGT.BASE_BURDENED_COST,
0 BASE_REVENUE,
BGT.BASE_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.BASE_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.BASE_LABOR_QUANTITY) BASE_LABOR_QUANTITY,
BGT.ORIG_RAW_COST,
BGT.ORIG_BURDENED_COST,
0 ORIG_REVENUE,
BGT.ORIG_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.ORIG_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.ORIG_LABOR_QUANTITY) ORIG_LABOR_QUANTITY,
BGT.RAW_COST_ITD_BASE,
BGT.BURDENED_COST_ITD_BASE,
0 REVENUE_ITD_BASE,
BGT.QUANTITY_ITD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_ITD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_BASE) LABOR_QUANTITY_ITD_BASE,
BGT.RAW_COST_PTD_BASE,
BGT.BURDENED_COST_PTD_BASE,
0 REVENUE_PTD_BASE,
BGT.QUANTITY_PTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_BASE) LABOR_QUANTITY_PTD_BASE,
BGT.RAW_COST_PP_BASE,
BGT.BURDENED_COST_PP_BASE,
0 REVENUE_PP_BASE,
BGT.QUANTITY_PP_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PP_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_BASE) LABOR_QUANTITY_PP_BASE,
BGT.RAW_COST_YTD_BASE,
BGT.BURDENED_COST_YTD_BASE,
0 REVENUE_YTD_BASE,
BGT.QUANTITY_YTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_YTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_BASE) LABOR_QUANTITY_YTD_BASE,
BGT.RAW_COST_ITD_ORIG,
BGT.BURDENED_COST_ITD_ORIG,
0 REVENUE_ITD_ORIG,
BGT.QUANTITY_ITD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_ITD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_ORIG) LABOR_QUANTITY_ITD_ORIG,
BGT.RAW_COST_PTD_ORIG,
BGT.BURDENED_COST_PTD_ORIG,
0 REVENUE_PTD_ORIG,
BGT.QUANTITY_PTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_ORIG) LABOR_QUANTITY_PTD_ORIG,
BGT.RAW_COST_PP_ORIG,
BGT.BURDENED_COST_PP_ORIG,
0 REVENUE_PP_ORIG,
BGT.QUANTITY_PP_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PP_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_ORIG) LABOR_QUANTITY_PP_ORIG,
BGT.RAW_COST_YTD_ORIG,
BGT.BURDENED_COST_YTD_ORIG,
0 REVENUE_YTD_ORIG,
BGT.QUANTITY_YTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_YTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_ORIG) LABOR_QUANTITY_YTD_ORIG,
BGT.UNIT_OF_MEASURE_BASE,
BGT.UNIT_OF_MEASURE_ORIG,
PAR.ROLLUP_QUANTITY_FLAG,
PARLA.RESOURCE_LIST_ASSIGNMENT_ID
FROM PA_TODATE_BASE_ORIG_BUDGET_V BGT
, PA_RESOURCES PAR
, PA_RESOURCE_LIST_ASSIGNMENTS PARLA
WHERE BGT.PROJECT_ID = x_project_id
and BGT.RESOURCE_ACCUMULATED_FLAG = 'N'
and BGT.RESOURCE_ID = PAR.RESOURCE_ID
And PARLA.PROJECT_ID = x_project_id
and PARLA.RESOURCE_LIST_ID = BGT.RESOURCE_LIST_ID
and bgt.budget_type_code IS NULL -- FP Model PSI FC Record
and nvl(bgt.primary_cost_forecast_flag,'N') = 'Y' -- -- PRIMARY FORECAST C-O-S-T
and 'FC' = NVL(x_budget_type_code, 'FC')
UNION ALL
SELECT
BGT.PROJECT_ID,
'FR' BUDGET_TYPE_CODE,
BGT.fin_plan_type_id,
BGT.TASK_ID,
BGT.RESOURCE_LIST_MEMBER_ID,
BGT.RESOURCE_LIST_ID,
BGT.RESOURCE_ID,
0 BASE_RAW_COST,
0 BASE_BURDENED_COST,
BGT.BASE_REVENUE,
BGT.BASE_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.BASE_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.BASE_LABOR_QUANTITY) BASE_LABOR_QUANTITY,
0 ORIG_RAW_COST,
0 ORIG_BURDENED_COST,
BGT.ORIG_REVENUE,
BGT.ORIG_QUANTITY,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.ORIG_LABOR_QUANTITY,to_number(null)),to_number(null)),
BGT.ORIG_LABOR_QUANTITY) ORIG_LABOR_QUANTITY,
0 RAW_COST_ITD_BASE,
0 BURDENED_COST_ITD_BASE,
BGT.REVENUE_ITD_BASE,
BGT.QUANTITY_ITD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_ITD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_BASE) LABOR_QUANTITY_ITD_BASE,
0 RAW_COST_PTD_BASE,
0 BURDENED_COST_PTD_BASE,
BGT.REVENUE_PTD_BASE,
BGT.QUANTITY_PTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_BASE) LABOR_QUANTITY_PTD_BASE,
0 RAW_COST_PP_BASE,
0 BURDENED_COST_PP_BASE,
BGT.REVENUE_PP_BASE,
BGT.QUANTITY_PP_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_PP_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_BASE) LABOR_QUANTITY_PP_BASE,
0 RAW_COST_YTD_BASE,
0 BURDENED_COST_YTD_BASE,
BGT.REVENUE_YTD_BASE,
BGT.QUANTITY_YTD_BASE,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_BASE,'HOURS',BGT.LABOR_QUANTITY_YTD_BASE,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_BASE) LABOR_QUANTITY_YTD_BASE,
0 RAW_COST_ITD_ORIG,
0 BURDENED_COST_ITD_ORIG,
BGT.REVENUE_ITD_ORIG,
BGT.QUANTITY_ITD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_ITD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_ITD_ORIG) LABOR_QUANTITY_ITD_ORIG,
0 RAW_COST_PTD_ORIG,
0 BURDENED_COST_PTD_ORIG,
BGT.REVENUE_PTD_ORIG,
BGT.QUANTITY_PTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PTD_ORIG) LABOR_QUANTITY_PTD_ORIG,
0 RAW_COST_PP_ORIG,
0 BURDENED_COST_PP_ORIG,
BGT.REVENUE_PP_ORIG,
BGT.QUANTITY_PP_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_PP_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_PP_ORIG) LABOR_QUANTITY_PP_ORIG,
0 RAW_COST_YTD_ORIG,
0 BURDENED_COST_YTD_ORIG,
BGT.REVENUE_YTD_ORIG,
BGT.QUANTITY_YTD_ORIG,
decode(bgt.track_as_labor_flag, NULL,decode(bgt.resource_class_code, 'PEOPLE',
decode(BGT.UNIT_OF_MEASURE_ORIG,'HOURS',BGT.LABOR_QUANTITY_YTD_ORIG,to_number(null)),to_number(null)),
BGT.LABOR_QUANTITY_YTD_ORIG) LABOR_QUANTITY_YTD_ORIG,
BGT.UNIT_OF_MEASURE_BASE,
BGT.UNIT_OF_MEASURE_ORIG,
PAR.ROLLUP_QUANTITY_FLAG,
PARLA.RESOURCE_LIST_ASSIGNMENT_ID
FROM PA_TODATE_BASE_ORIG_BUDGET_V BGT
, PA_RESOURCES PAR
, PA_RESOURCE_LIST_ASSIGNMENTS PARLA
WHERE BGT.PROJECT_ID = x_project_id
and BGT.RESOURCE_ACCUMULATED_FLAG = 'N'
and BGT.RESOURCE_ID = PAR.RESOURCE_ID
And PARLA.PROJECT_ID = x_project_id
and PARLA.RESOURCE_LIST_ID = BGT.RESOURCE_LIST_ID
and bgt.budget_type_code IS NULL -- FP Model PSI FR Record
and nvl(bgt.primary_rev_forecast_flag,'N') = 'Y' -- -- PRIMARY FORECAST R-E-V-E-N-U-E
and 'FR' = NVL(x_budget_type_code, 'FR')
) PAB
GROUP BY
PAB.PROJECT_ID,
PAB.BUDGET_TYPE_CODE,
PAB.fin_plan_type_id,
PAB.TASK_ID,
PAB.RESOURCE_LIST_MEMBER_ID,
PAB.RESOURCE_LIST_ID,
PAB.RESOURCE_ID,
PAB.UNIT_OF_MEASURE_BASE,
PAB.UNIT_OF_MEASURE_ORIG,
PAB.ROLLUP_QUANTITY_FLAG,
PAB.RESOURCE_LIST_ASSIGNMENT_ID;
SELECT Distinct
Budget_Type_Code,
Budget_Version_id
FROM
(
SELECT Budget_Type_Code
, Budget_Version_id
FROM PA_BUDGET_VERSIONS
Where Project_Id = x_project_id
And Current_Flag = 'Y'
and budget_type_code IS NOT NULL -- r11.5.7 Budgets Model
UNION ALL
SELECT to_char(fin_plan_type_id) Budget_Type_Code
, Budget_Version_id
FROM PA_BUDGET_VERSIONS
Where Project_Id = x_project_id
And Current_Flag = 'Y'
and budget_type_code IS NULL -- FP Model Plan Type, #3561255, changed to 'IS NULL'
UNION ALL
SELECT 'AC' Budget_Type_Code
, Budget_Version_id
FROM PA_BUDGET_VERSIONS
Where Project_Id = x_project_id
And Current_Flag = 'Y'
and budget_type_code IS NULL -- FP Model PSI AC Record, #3561255, changed to 'IS NULL'
and nvl(approved_cost_plan_type_flag,'N') = 'Y' -- -- APPROVED C-O-S-T
UNION ALL
SELECT 'AR' Budget_Type_Code
, Budget_Version_id
FROM PA_BUDGET_VERSIONS
Where Project_Id = x_project_id
And Current_Flag = 'Y'
and budget_type_code IS NULL -- FP Model PSI AC Record, #3561255, changed to 'IS NULL'
and nvl(approved_rev_plan_type_flag,'N') = 'Y' -- -- APPROVED R-E-V-E-N-U-E
UNION ALL
SELECT 'FC' Budget_Type_Code
, Budget_Version_id
FROM PA_BUDGET_VERSIONS
Where Project_Id = x_project_id
And Current_Flag = 'Y'
and budget_type_code IS NULL -- FP Model PSI FC Record
and nvl(primary_cost_forecast_flag,'N') = 'Y' -- -- PRIMARY FORECAST C-O-S-T
UNION ALL
SELECT 'FR' Budget_Type_Code
, Budget_Version_id
FROM PA_BUDGET_VERSIONS
Where Project_Id = x_project_id
And Current_Flag = 'Y'
and budget_type_code IS NULL -- FP Model PSI AC Record
and nvl(primary_rev_forecast_flag,'N') = 'Y' -- -- PRIMARY FORECAST R-E-V-E-N-U-E
);
Update PA_BUDGET_VERSIONS SET
RESOURCE_ACCUMULATED_FLAG = 'Y' WHERE
BUDGET_VERSION_ID = PA_budget_versions_Rec.BUDGET_VERSION_ID;
SELECT
PAB.TASK_ID,
PAB.BUDGET_TYPE_CODE,
PAB.RESOURCE_LIST_MEMBER_ID,
PAB.RESOURCE_LIST_ID,PAB.RESOURCE_ID,
NVL(PAB.BASE_RAW_COST,0) BASE_RAW_COST,
NVL(PAB.BASE_BURDENED_COST,0) BASE_BURDENED_COST,
NVL(PAB.BASE_REVENUE,0) BASE_REVENUE,
NVL(PAB.BASE_QUANTITY,0) BASE_QUANTITY,
NVL(PAB.BASE_LABOR_QUANTITY,0) BASE_LABOR_QUANTITY,
NVL(PAB.ORIG_RAW_COST,0) ORIG_RAW_COST,
NVL(PAB.ORIG_BURDENED_COST,0) ORIG_BURDENED_COST,
NVL(PAB.ORIG_REVENUE,0) ORIG_REVENUE,
NVL(PAB.ORIG_QUANTITY,0) ORIG_QUANTITY,
NVL(PAB.ORIG_LABOR_QUANTITY,0) ORIG_LABOR_QUANTITY,
PAR.ROLLUP_QUANTITY_FLAG,
PARLA.RESOURCE_LIST_ASSIGNMENT_ID
FROM
PA_BUDGET_BY_RESOURCE_V PAB,
PA_RESOURCES PAR,
PA_RESOURCE_LIST_ASSIGNMENTS PARLA
WHERE PAB.PROJECT_ID = x_project_id
AND PAB.RESOURCE_ACCUMULATED_FLAG = 'N'
AND PAB.RESOURCE_ID = PAR.RESOURCE_ID
And PARLA.PROJECT_ID = x_project_id
and PARLA.RESOURCE_LIST_ID = PAB.RESOURCE_LIST_ID;
Update PA_PROJECT_ACCUM_BUDGETS PAB SET
BASE_RAW_COST_TOT = NVL(BASE_RAW_COST_TOT,0 ) +
x_budget_rec.BASE_RAW_COST,
BASE_BURDENED_COST_TOT = NVL(BASE_BURDENED_COST_TOT,0) +
x_budget_rec.BASE_BURDENED_COST,
ORIG_RAW_COST_TOT = NVL(ORIG_RAW_COST_TOT,0) +
x_budget_rec.ORIG_RAW_COST,
ORIG_BURDENED_COST_TOT = NVL(ORIG_BURDENED_COST_TOT,0) +
x_budget_rec.ORIG_BURDENED_COST,
BASE_REVENUE_TOT = NVL(BASE_REVENUE_TOT,0 ) +
x_budget_rec.BASE_REVENUE,
ORIG_REVENUE_TOT = NVL(ORIG_REVENUE_TOT,0 ) +
x_budget_rec.ORIG_REVENUE,
BASE_LABOR_HOURS_TOT = NVL(BASE_LABOR_HOURS_TOT,0) +
x_budget_rec.BASE_LABOR_QUANTITY,
ORIG_LABOR_HOURS_TOT = NVL(ORIG_LABOR_HOURS_TOT,0 ) +
x_budget_rec.ORIG_LABOR_QUANTITY,
BASE_QUANTITY_TOT = NVL(BASE_QUANTITY_TOT,0) +
V_Base_Qty,
ORIG_QUANTITY_TOT = NVL(ORIG_QUANTITY_TOT,0) +
V_Orig_Qty,
LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
LAST_UPDATE_DATE = Trunc(Sysdate),
LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
Where Budget_Type_Code = x_Budget_rec.BUDGET_TYPE_CODE
AND
(PAB.Project_Accum_id In
(Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
Where Pah.Project_id = x_project_id and
pah.Resource_list_member_id = 0 and
Pah.Task_id in (Select Pt.Task_Id from PA_TASKS pt
start with pt.task_id = x_budget_rec.TASK_ID
connect by prior pt.parent_task_id = pt.task_id)
UNION
select to_number(X_Proj_accum_id) from sys.dual
UNION
(Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
Where Pah.Project_id = x_project_id and
pah.Resource_list_member_id =
x_Budget_rec.RESOURCE_LIST_MEMBER_ID and
Pah.Task_id in (select 0 from sys.dual union
Select Pt.Task_Id from PA_TASKS pt
start with pt.task_id = x_budget_rec.TASK_ID
connect by prior pt.parent_task_id = pt.task_id)))
);
Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
From sys.Dual;
PA_MAINT_PROJECT_ACCUMS.Insert_Headers_tasks
(X_project_id,
v_task_array(i),
x_current_period,
v_accum_id,
x_err_stack,
x_err_stage,
x_err_code);
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN) Values
(V_Accum_id,x_budget_type_code,
0,0,0,0,
0,0,
0,0,
0,0,0,0,
0,0,
0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
NULL,NULL,
0,0,0,0,0,0,0,0,0,0,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
SELECT Project_Accum_Id
FROM
PA_PROJECT_ACCUM_HEADERS
WHERE Project_id = X_project_id
AND Task_Id = 0
AND Resource_list_Member_id = X_resource_list_member_id;
Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
From sys.Dual;
PA_process_accum_actuals_res.insert_headers_res
(x_project_id,
v_task_array(i),
x_resource_list_id ,
x_resource_list_Member_id ,
x_resource_id ,
x_resource_list_assignment_id ,
x_current_period,
v_accum_id,
x_err_stack,
x_err_stage,
x_err_code);
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN) Values
(V_Accum_id,x_budget_type_code,
0,0,0,0,
0,0,
0,0,
0,0,0,0,
0,0,
0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
NULL,NULL,
0,0,0,0,0,0,0,0,0,0,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
From sys.Dual;
PA_process_accum_actuals_res.insert_headers_res
(x_project_id,
0,
x_resource_list_id ,
x_resource_list_Member_id ,
x_resource_id ,
x_resource_list_assignment_id ,
x_current_period,
v_accum_id,
x_err_stack,
x_err_stage,
x_err_code);
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN) Values
(V_Accum_id,x_budget_type_code,
0,0,0,0,
0,0,
0,0,
0,0,0,0,
0,0,
0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
0,0,0,0,
NULL,NULL,
0,0,0,0,0,0,0,0,0,0,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
SELECT task_id
FROM
pa_tasks pt
WHERE project_id = x_project_id
START WITH task_id = x_task_id
CONNECT BY PRIOR parent_task_id = task_id;
Select project_accum_id into V_Accum_id
from pa_project_accum_headers
where project_id = x_project_id
and task_id = x_task_id
and resource_list_member_id = x_resource_list_member_id;
Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
from sys.dual;
Insert into PA_PROJECT_ACCUM_HEADERS
(PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
REQUEST_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN
)
Values (v_accum_id,X_project_id,x_task_id,
x_current_period,
x_resource_id,x_resource_list_id,
x_resource_list_assignment_id,x_resource_list_Member_id,
pa_proj_accum_main.x_last_updated_by,
Trunc(sysdate),pa_proj_accum_main.x_request_id,
trunc(sysdate),
pa_proj_accum_main.x_created_by,
pa_proj_accum_main.x_last_update_login
);
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,
FIN_PLAN_TYPE_ID
)
Values
(
V_Accum_id,x_budget_type_code,
V_BASE_RAW_COST_ITD,V_BASE_RAW_COST_YTD,
V_BASE_RAW_COST_PP, V_BASE_RAW_COST_PTD,
V_BASE_BURDENED_COST_ITD,V_BASE_BURDENED_COST_YTD,
V_BASE_BURDENED_COST_PP,V_BASE_BURDENED_COST_PTD,
V_ORIG_RAW_COST_ITD,V_ORIG_RAW_COST_YTD,
V_ORIG_RAW_COST_PP, V_ORIG_RAW_COST_PTD,
V_ORIG_BURDENED_COST_ITD,V_ORIG_BURDENED_COST_YTD,
V_ORIG_BURDENED_COST_PP,V_ORIG_BURDENED_COST_PTD,
V_BASE_QUANTITY_ITD,V_BASE_QUANTITY_YTD,V_BASE_QUANTITY_PP,
V_BASE_QUANTITY_PTD,
V_ORIG_QUANTITY_ITD,V_ORIG_QUANTITY_YTD,V_ORIG_QUANTITY_PP,
V_ORIG_QUANTITY_PTD,
V_BASE_LABOR_HOURS_ITD,V_BASE_LABOR_HOURS_YTD,V_BASE_LABOR_HOURS_PP,
V_BASE_LABOR_HOURS_PTD,
V_ORIG_LABOR_HOURS_ITD,V_ORIG_LABOR_HOURS_YTD,V_ORIG_LABOR_HOURS_PP,
V_ORIG_LABOR_HOURS_PTD,
V_BASE_REVENUE_ITD,V_BASE_REVENUE_YTD,V_BASE_REVENUE_PP,V_BASE_REVENUE_PTD,
V_ORIG_REVENUE_ITD,V_ORIG_REVENUE_YTD,V_ORIG_REVENUE_PP,V_ORIG_REVENUE_PTD,
X_BASE_UNIT_OF_MEASURE,X_ORIG_UNIT_OF_MEASURE,
TOT_BASE_RAW_COST,TOT_BASE_BURDENED_COST,TOT_ORIG_RAW_COST,
TOT_ORIG_BURDENED_COST,TOT_BASE_REVENUE,TOT_ORIG_REVENUE,
TOT_BASE_LABOR_HOURS,TOT_ORIG_LABOR_HOURS,TOT_BASE_QUANTITY,
TOT_ORIG_QUANTITY,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login,
x_fin_plan_type_id
);
Update PA_PROJECT_ACCUM_BUDGETS PAB SET
BASE_RAW_COST_ITD = NVL(BASE_RAW_COST_ITD,0) +
NVL(V_Base_Raw_Cost_itd,0),
BASE_RAW_COST_YTD = NVL(BASE_RAW_COST_YTD,0) +
NVL(V_Base_Raw_Cost_ytd,0),
BASE_RAW_COST_PTD = NVL(BASE_RAW_COST_PTD,0) +
NVL(V_Base_Raw_Cost_ptd,0),
BASE_RAW_COST_PP = NVL(BASE_RAW_COST_PP,0) +
NVL(V_Base_Raw_Cost_pp,0),
ORIG_RAW_COST_ITD = NVL(ORIG_RAW_COST_ITD,0) +
NVL(V_Orig_Raw_Cost_itd,0),
ORIG_RAW_COST_YTD = NVL(ORIG_RAW_COST_YTD,0) +
NVL(V_Orig_Raw_Cost_ytd,0),
ORIG_RAW_COST_PTD = NVL(ORIG_RAW_COST_PTD,0) +
NVL(V_Orig_Raw_Cost_ptd,0),
ORIG_RAW_COST_PP = NVL(ORIG_RAW_COST_PP,0) +
NVL(V_Orig_Raw_Cost_pp,0),
BASE_BURDENED_COST_ITD = NVL(BASE_BURDENED_COST_ITD,0) +
NVL(V_Base_Burdened_Cost_itd,0),
BASE_BURDENED_COST_YTD = NVL(BASE_BURDENED_COST_YTD,0) +
NVL(V_Base_Burdened_Cost_ytd,0),
BASE_BURDENED_COST_PTD = NVL(BASE_BURDENED_COST_PTD,0) +
NVL(V_Base_Burdened_Cost_ptd,0),
BASE_BURDENED_COST_PP = NVL(BASE_BURDENED_COST_PP,0) +
NVL(V_Base_Burdened_Cost_pp,0),
ORIG_BURDENED_COST_ITD = NVL(ORIG_BURDENED_COST_ITD,0) +
NVL(V_Orig_Burdened_Cost_itd,0),
ORIG_BURDENED_COST_YTD = NVL(ORIG_BURDENED_COST_YTD,0) +
NVL(V_Orig_Burdened_Cost_ytd,0),
ORIG_BURDENED_COST_PTD = NVL(ORIG_BURDENED_COST_PTD,0) +
NVL(V_Orig_Burdened_Cost_ptd,0),
ORIG_BURDENED_COST_PP = NVL(ORIG_BURDENED_COST_PP,0) +
NVL(V_Orig_Burdened_Cost_pp,0),
BASE_LABOR_HOURS_ITD = NVL(BASE_LABOR_HOURS_ITD,0) +
NVL(V_Base_Labor_Hours_itd,0),
BASE_LABOR_HOURS_YTD = NVL(BASE_LABOR_HOURS_YTD,0) +
NVL(V_Base_Labor_Hours_ytd,0),
BASE_LABOR_HOURS_PTD = NVL(BASE_LABOR_HOURS_PTD,0) +
NVL(V_Base_Labor_Hours_ptd,0),
BASE_LABOR_HOURS_PP = NVL(BASE_LABOR_HOURS_PP,0) +
NVL(V_Base_Labor_Hours_pp,0),
ORIG_LABOR_HOURS_ITD = NVL(ORIG_LABOR_HOURS_ITD,0) +
NVL(V_Orig_Labor_Hours_itd,0),
ORIG_LABOR_HOURS_YTD = NVL(ORIG_LABOR_HOURS_YTD,0) +
NVL(V_Orig_Labor_Hours_ytd,0),
ORIG_LABOR_HOURS_PTD = NVL(ORIG_LABOR_HOURS_PTD,0) +
NVL(V_Orig_Labor_Hours_ptd,0),
ORIG_LABOR_HOURS_PP = NVL(ORIG_LABOR_HOURS_PP,0) +
NVL(V_Orig_Labor_Hours_pp,0),
BASE_QUANTITY_ITD = NVL(BASE_QUANTITY_ITD,0) +
NVL(V_Base_Quantity_itd,0),
BASE_QUANTITY_YTD = NVL(BASE_QUANTITY_YTD,0) +
NVL(V_Base_Quantity_ytd,0),
BASE_QUANTITY_PTD = NVL(BASE_QUANTITY_PTD,0) +
NVL(V_Base_Quantity_ptd,0),
BASE_QUANTITY_PP = NVL(BASE_QUANTITY_PP,0) +
NVL(V_Base_Quantity_pp,0),
ORIG_QUANTITY_ITD = NVL(ORIG_QUANTITY_ITD,0) +
NVL(V_Orig_Quantity_itd,0),
ORIG_QUANTITY_YTD = NVL(ORIG_QUANTITY_YTD,0) +
NVL(V_Orig_Quantity_ytd,0),
ORIG_QUANTITY_PTD = NVL(ORIG_QUANTITY_PTD,0) +
NVL(V_Orig_Quantity_ptd,0),
ORIG_QUANTITY_PP = NVL(ORIG_QUANTITY_PP,0) +
NVL(V_Orig_Quantity_pp,0),
BASE_REVENUE_ITD = NVL(BASE_REVENUE_ITD,0) +
NVL(V_Base_Revenue_itd,0),
BASE_REVENUE_YTD = NVL(BASE_REVENUE_YTD,0) +
NVL(V_Base_Revenue_ytd,0),
BASE_REVENUE_PTD = NVL(BASE_REVENUE_PTD,0) +
NVL(V_Base_Revenue_ptd,0),
BASE_REVENUE_PP = NVL(BASE_REVENUE_PP,0) +
NVL(V_Base_Revenue_pp,0),
ORIG_REVENUE_ITD = NVL(ORIG_REVENUE_ITD,0) +
NVL(V_Orig_Revenue_itd,0),
ORIG_REVENUE_YTD = NVL(ORIG_REVENUE_YTD,0) +
NVL(V_Orig_Revenue_ytd,0),
ORIG_REVENUE_PTD = NVL(ORIG_REVENUE_PTD,0) +
NVL(V_Orig_Revenue_ptd,0),
ORIG_REVENUE_PP = NVL(ORIG_REVENUE_PP,0) +
NVL(V_Orig_Revenue_pp,0),
BASE_RAW_COST_TOT = NVL(BASE_RAW_COST_TOT,0 ) +
TOT_BASE_RAW_COST,
BASE_BURDENED_COST_TOT = NVL(BASE_BURDENED_COST_TOT,0) +
TOT_BASE_BURDENED_COST,
ORIG_RAW_COST_TOT = NVL(ORIG_RAW_COST_TOT,0) +
TOT_ORIG_RAW_COST,
ORIG_BURDENED_COST_TOT = NVL(ORIG_BURDENED_COST_TOT,0) +
TOT_ORIG_BURDENED_COST,
BASE_REVENUE_TOT = NVL(BASE_REVENUE_TOT,0 ) +
TOT_BASE_REVENUE,
ORIG_REVENUE_TOT = NVL(ORIG_REVENUE_TOT,0 ) +
TOT_ORIG_REVENUE,
BASE_LABOR_HOURS_TOT = NVL(BASE_LABOR_HOURS_TOT,0) +
TOT_BASE_LABOR_HOURS,
ORIG_LABOR_HOURS_TOT = NVL(ORIG_LABOR_HOURS_TOT,0 ) +
TOT_ORIG_LABOR_HOURS,
BASE_QUANTITY_TOT = NVL(BASE_QUANTITY_TOT,0) +
TOT_BASE_QUANTITY,
ORIG_QUANTITY_TOT = NVL(ORIG_QUANTITY_TOT,0) +
TOT_ORIG_QUANTITY,
BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
LAST_UPDATE_DATE = Trunc(Sysdate),
LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login,
FIN_PLAN_TYPE_ID = x_fin_plan_type_id
Where Budget_Type_Code = x_Budget_type_code
And PAB.Project_Accum_id = v_accum_id;
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,
FIN_PLAN_TYPE_ID
)
Values
(
V_Accum_id,x_budget_type_code,
V_BASE_RAW_COST_ITD,V_BASE_RAW_COST_YTD,
V_BASE_RAW_COST_PP, V_BASE_RAW_COST_PTD,
V_BASE_BURDENED_COST_ITD,V_BASE_BURDENED_COST_YTD,
V_BASE_BURDENED_COST_PP,V_BASE_BURDENED_COST_PTD,
V_ORIG_RAW_COST_ITD,V_ORIG_RAW_COST_YTD,
V_ORIG_RAW_COST_PP, V_ORIG_RAW_COST_PTD,
V_ORIG_BURDENED_COST_ITD,V_ORIG_BURDENED_COST_YTD,
V_ORIG_BURDENED_COST_PP,V_ORIG_BURDENED_COST_PTD,
V_BASE_QUANTITY_ITD,V_BASE_QUANTITY_YTD,V_BASE_QUANTITY_PP,
V_BASE_QUANTITY_PTD,
V_ORIG_QUANTITY_ITD,V_ORIG_QUANTITY_YTD,V_ORIG_QUANTITY_PP,
V_ORIG_QUANTITY_PTD,
V_BASE_LABOR_HOURS_ITD,V_BASE_LABOR_HOURS_YTD,V_BASE_LABOR_HOURS_PP,
V_BASE_LABOR_HOURS_PTD,
V_ORIG_LABOR_HOURS_ITD,V_ORIG_LABOR_HOURS_YTD,V_ORIG_LABOR_HOURS_PP,
V_ORIG_LABOR_HOURS_PTD,
V_BASE_REVENUE_ITD,V_BASE_REVENUE_YTD,V_BASE_REVENUE_PP,V_BASE_REVENUE_PTD,
V_ORIG_REVENUE_ITD,V_ORIG_REVENUE_YTD,V_ORIG_REVENUE_PP,V_ORIG_REVENUE_PTD,
X_BASE_UNIT_OF_MEASURE,X_ORIG_UNIT_OF_MEASURE,
TOT_BASE_RAW_COST,TOT_BASE_BURDENED_COST,TOT_ORIG_RAW_COST,
TOT_ORIG_BURDENED_COST,TOT_BASE_REVENUE,TOT_ORIG_REVENUE,
TOT_BASE_LABOR_HOURS,TOT_ORIG_LABOR_HOURS,TOT_BASE_QUANTITY,
TOT_ORIG_QUANTITY,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login,
x_fin_plan_type_id
);
Select project_accum_id into V_Accum_id
from pa_project_accum_headers
where project_id = x_project_id
and task_id = x_task_id
and resource_list_member_id = x_resource_list_member_id;
Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
from sys.dual;
Insert into PA_PROJECT_ACCUM_HEADERS
(PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
REQUEST_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN )
Values (v_accum_id,X_project_id,x_task_id,
x_current_period,
x_resource_id,x_resource_list_id,
x_resource_list_assignment_id,x_resource_list_Member_id,
pa_proj_accum_main.x_last_updated_by,
Trunc(sysdate),pa_proj_accum_main.x_request_id,
trunc(sysdate),
pa_proj_accum_main.x_created_by,
pa_proj_accum_main.x_last_update_login );
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,
FIN_PLAN_TYPE_ID
)
Values
(
V_Accum_id,x_budget_type_code,
Tsk_BASE_RAW_COST_ITD,Tsk_BASE_RAW_COST_YTD,
Tsk_BASE_RAW_COST_PP, Tsk_BASE_RAW_COST_PTD,
Tsk_BASE_BURDENED_COST_ITD,Tsk_BASE_BURDENED_COST_YTD,
Tsk_BASE_BURDENED_COST_PP,Tsk_BASE_BURDENED_COST_PTD,
Tsk_ORIG_RAW_COST_ITD,Tsk_ORIG_RAW_COST_YTD,
Tsk_ORIG_RAW_COST_PP, Tsk_ORIG_RAW_COST_PTD,
Tsk_ORIG_BURDENED_COST_ITD,Tsk_ORIG_BURDENED_COST_YTD,
Tsk_ORIG_BURDENED_COST_PP,Tsk_ORIG_BURDENED_COST_PTD,
Tsk_BASE_QUANTITY_ITD,Tsk_BASE_QUANTITY_YTD,Tsk_BASE_QUANTITY_PP,
Tsk_BASE_QUANTITY_PTD,
Tsk_ORIG_QUANTITY_ITD,Tsk_ORIG_QUANTITY_YTD,Tsk_ORIG_QUANTITY_PP,
Tsk_ORIG_QUANTITY_PTD,
Tsk_BASE_LABOR_HOURS_ITD,Tsk_BASE_LABOR_HOURS_YTD,Tsk_BASE_LABOR_HOURS_PP,
Tsk_BASE_LABOR_HOURS_PTD,
Tsk_ORIG_LABOR_HOURS_ITD,Tsk_ORIG_LABOR_HOURS_YTD,Tsk_ORIG_LABOR_HOURS_PP,
Tsk_ORIG_LABOR_HOURS_PTD,
Tsk_BASE_REVENUE_ITD,Tsk_BASE_REVENUE_YTD,Tsk_BASE_REVENUE_PP,Tsk_BASE_REVENUE_PTD,
Tsk_ORIG_REVENUE_ITD,Tsk_ORIG_REVENUE_YTD,Tsk_ORIG_REVENUE_PP,Tsk_ORIG_REVENUE_PTD,
X_BASE_UNIT_OF_MEASURE,X_ORIG_UNIT_OF_MEASURE,
Tsk_BASE_RAW_COST,Tsk_BASE_BURDENED_COST,Tsk_ORIG_RAW_COST,
Tsk_ORIG_BURDENED_COST,Tsk_BASE_REVENUE,Tsk_ORIG_REVENUE,
Tsk_BASE_LABOR_HOURS,Tsk_ORIG_LABOR_HOURS,Tsk_BASE_QUANTITY,
Tsk_ORIG_QUANTITY,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login,
x_fin_plan_type_id
);
Update PA_PROJECT_ACCUM_BUDGETS PAB SET
BASE_RAW_COST_ITD = NVL(BASE_RAW_COST_ITD,0) +
NVL(Tsk_Base_Raw_Cost_itd,0),
BASE_RAW_COST_YTD = NVL(BASE_RAW_COST_YTD,0) +
NVL(Tsk_Base_Raw_Cost_ytd,0),
BASE_RAW_COST_PTD = NVL(BASE_RAW_COST_PTD,0) +
NVL(Tsk_Base_Raw_Cost_ptd,0),
BASE_RAW_COST_PP = NVL(BASE_RAW_COST_PP,0) +
NVL(Tsk_Base_Raw_Cost_pp,0),
ORIG_RAW_COST_ITD = NVL(ORIG_RAW_COST_ITD,0) +
NVL(Tsk_Orig_Raw_Cost_itd,0),
ORIG_RAW_COST_YTD = NVL(ORIG_RAW_COST_YTD,0) +
NVL(Tsk_Orig_Raw_Cost_ytd,0),
ORIG_RAW_COST_PTD = NVL(ORIG_RAW_COST_PTD,0) +
NVL(Tsk_Orig_Raw_Cost_ptd,0),
ORIG_RAW_COST_PP = NVL(ORIG_RAW_COST_PP,0) +
NVL(Tsk_Orig_Raw_Cost_pp,0),
BASE_BURDENED_COST_ITD = NVL(BASE_BURDENED_COST_ITD,0) +
NVL(Tsk_Base_Burdened_Cost_itd,0),
BASE_BURDENED_COST_YTD = NVL(BASE_BURDENED_COST_YTD,0) +
NVL(Tsk_Base_Burdened_Cost_ytd,0),
BASE_BURDENED_COST_PTD = NVL(BASE_BURDENED_COST_PTD,0) +
NVL(Tsk_Base_Burdened_Cost_ptd,0),
BASE_BURDENED_COST_PP = NVL(BASE_BURDENED_COST_PP,0) +
NVL(Tsk_Base_Burdened_Cost_pp,0),
ORIG_BURDENED_COST_ITD = NVL(ORIG_BURDENED_COST_ITD,0) +
NVL(Tsk_Orig_Burdened_Cost_itd,0),
ORIG_BURDENED_COST_YTD = NVL(ORIG_BURDENED_COST_YTD,0) +
NVL(Tsk_Orig_Burdened_Cost_ytd,0),
ORIG_BURDENED_COST_PTD = NVL(ORIG_BURDENED_COST_PTD,0) +
NVL(Tsk_Orig_Burdened_Cost_ptd,0),
ORIG_BURDENED_COST_PP = NVL(ORIG_BURDENED_COST_PP,0) +
NVL(Tsk_Orig_Burdened_Cost_pp,0),
BASE_LABOR_HOURS_ITD = NVL(BASE_LABOR_HOURS_ITD,0) +
NVL(Tsk_Base_Labor_Hours_itd,0),
BASE_LABOR_HOURS_YTD = NVL(BASE_LABOR_HOURS_YTD,0) +
NVL(Tsk_Base_Labor_Hours_ytd,0),
BASE_LABOR_HOURS_PTD = NVL(BASE_LABOR_HOURS_PTD,0) +
NVL(Tsk_Base_Labor_Hours_ptd,0),
BASE_LABOR_HOURS_PP = NVL(BASE_LABOR_HOURS_PP,0) +
NVL(Tsk_Base_Labor_Hours_pp,0),
ORIG_LABOR_HOURS_ITD = NVL(ORIG_LABOR_HOURS_ITD,0) +
NVL(Tsk_Orig_Labor_Hours_itd,0),
ORIG_LABOR_HOURS_YTD = NVL(ORIG_LABOR_HOURS_YTD,0) +
NVL(Tsk_Orig_Labor_Hours_ytd,0),
ORIG_LABOR_HOURS_PTD = NVL(ORIG_LABOR_HOURS_PTD,0) +
NVL(Tsk_Orig_Labor_Hours_ptd,0),
ORIG_LABOR_HOURS_PP = NVL(ORIG_LABOR_HOURS_PP,0) +
NVL(Tsk_Orig_Labor_Hours_pp,0),
BASE_QUANTITY_ITD = NVL(BASE_QUANTITY_ITD,0) +
NVL(Tsk_Base_Quantity_itd,0),
BASE_QUANTITY_YTD = NVL(BASE_QUANTITY_YTD,0) +
NVL(Tsk_Base_Quantity_ytd,0),
BASE_QUANTITY_PTD = NVL(BASE_QUANTITY_PTD,0) +
NVL(Tsk_Base_Quantity_ptd,0),
BASE_QUANTITY_PP = NVL(BASE_QUANTITY_PP,0) +
NVL(Tsk_Base_Quantity_pp,0),
ORIG_QUANTITY_ITD = NVL(ORIG_QUANTITY_ITD,0) +
NVL(Tsk_Orig_Quantity_itd,0),
ORIG_QUANTITY_YTD = NVL(ORIG_QUANTITY_YTD,0) +
NVL(Tsk_Orig_Quantity_ytd,0),
ORIG_QUANTITY_PTD = NVL(ORIG_QUANTITY_PTD,0) +
NVL(Tsk_Orig_Quantity_ptd,0),
ORIG_QUANTITY_PP = NVL(ORIG_QUANTITY_PP,0) +
NVL(Tsk_Orig_Quantity_pp,0),
BASE_REVENUE_ITD = NVL(BASE_REVENUE_ITD,0) +
NVL(Tsk_Base_Revenue_itd,0),
BASE_REVENUE_YTD = NVL(BASE_REVENUE_YTD,0) +
NVL(Tsk_Base_Revenue_ytd,0),
BASE_REVENUE_PTD = NVL(BASE_REVENUE_PTD,0) +
NVL(Tsk_Base_Revenue_ptd,0),
BASE_REVENUE_PP = NVL(BASE_REVENUE_PP,0) +
NVL(Tsk_Base_Revenue_pp,0),
ORIG_REVENUE_ITD = NVL(ORIG_REVENUE_ITD,0) +
NVL(Tsk_Orig_Revenue_itd,0),
ORIG_REVENUE_YTD = NVL(ORIG_REVENUE_YTD,0) +
NVL(Tsk_Orig_Revenue_ytd,0),
ORIG_REVENUE_PTD = NVL(ORIG_REVENUE_PTD,0) +
NVL(Tsk_Orig_Revenue_ptd,0),
ORIG_REVENUE_PP = NVL(ORIG_REVENUE_PP,0) +
NVL(Tsk_Orig_Revenue_pp,0),
BASE_RAW_COST_TOT = NVL(BASE_RAW_COST_TOT,0 ) +
Tsk_BASE_RAW_COST,
BASE_BURDENED_COST_TOT = NVL(BASE_BURDENED_COST_TOT,0) +
Tsk_BASE_BURDENED_COST,
ORIG_RAW_COST_TOT = NVL(ORIG_RAW_COST_TOT,0) +
Tsk_ORIG_RAW_COST,
ORIG_BURDENED_COST_TOT = NVL(ORIG_BURDENED_COST_TOT,0) +
Tsk_ORIG_BURDENED_COST,
BASE_REVENUE_TOT = NVL(BASE_REVENUE_TOT,0 ) +
Tsk_BASE_REVENUE,
ORIG_REVENUE_TOT = NVL(ORIG_REVENUE_TOT,0 ) +
Tsk_ORIG_REVENUE,
BASE_LABOR_HOURS_TOT = NVL(BASE_LABOR_HOURS_TOT,0) +
Tsk_BASE_LABOR_HOURS,
ORIG_LABOR_HOURS_TOT = NVL(ORIG_LABOR_HOURS_TOT,0 ) +
Tsk_ORIG_LABOR_HOURS,
BASE_QUANTITY_TOT = NVL(BASE_QUANTITY_TOT,0) +
Tsk_BASE_QUANTITY,
ORIG_QUANTITY_TOT = NVL(ORIG_QUANTITY_TOT,0) +
Tsk_ORIG_QUANTITY,
BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
LAST_UPDATE_DATE = Trunc(Sysdate),
LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login,
FIN_PLAN_TYPE_ID = x_fin_plan_type_id
Where Budget_Type_Code = x_Budget_type_code
And PAB.Project_Accum_id = v_accum_id;
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,
FIN_PLAN_TYPE_ID
)
Values
(
V_Accum_id,x_budget_type_code,
Tsk_BASE_RAW_COST_ITD,Tsk_BASE_RAW_COST_YTD,
Tsk_BASE_RAW_COST_PP, Tsk_BASE_RAW_COST_PTD,
Tsk_BASE_BURDENED_COST_ITD,Tsk_BASE_BURDENED_COST_YTD,
Tsk_BASE_BURDENED_COST_PP,Tsk_BASE_BURDENED_COST_PTD,
Tsk_ORIG_RAW_COST_ITD,Tsk_ORIG_RAW_COST_YTD,
Tsk_ORIG_RAW_COST_PP, Tsk_ORIG_RAW_COST_PTD,
Tsk_ORIG_BURDENED_COST_ITD,Tsk_ORIG_BURDENED_COST_YTD,
Tsk_ORIG_BURDENED_COST_PP,Tsk_ORIG_BURDENED_COST_PTD,
Tsk_BASE_QUANTITY_ITD,Tsk_BASE_QUANTITY_YTD,Tsk_BASE_QUANTITY_PP,
Tsk_BASE_QUANTITY_PTD,
Tsk_ORIG_QUANTITY_ITD,Tsk_ORIG_QUANTITY_YTD,Tsk_ORIG_QUANTITY_PP,
Tsk_ORIG_QUANTITY_PTD,
Tsk_BASE_LABOR_HOURS_ITD,Tsk_BASE_LABOR_HOURS_YTD,Tsk_BASE_LABOR_HOURS_PP,
Tsk_BASE_LABOR_HOURS_PTD,
Tsk_ORIG_LABOR_HOURS_ITD,Tsk_ORIG_LABOR_HOURS_YTD,Tsk_ORIG_LABOR_HOURS_PP,
Tsk_ORIG_LABOR_HOURS_PTD,
Tsk_BASE_REVENUE_ITD,Tsk_BASE_REVENUE_YTD,Tsk_BASE_REVENUE_PP,Tsk_BASE_REVENUE_PTD,
Tsk_ORIG_REVENUE_ITD,Tsk_ORIG_REVENUE_YTD,Tsk_ORIG_REVENUE_PP,Tsk_ORIG_REVENUE_PTD,
X_BASE_UNIT_OF_MEASURE,X_ORIG_UNIT_OF_MEASURE,
Tsk_BASE_RAW_COST,Tsk_BASE_BURDENED_COST,Tsk_ORIG_RAW_COST,
Tsk_ORIG_BURDENED_COST,Tsk_BASE_REVENUE,Tsk_ORIG_REVENUE,
Tsk_BASE_LABOR_HOURS,Tsk_ORIG_LABOR_HOURS,Tsk_BASE_QUANTITY,
Tsk_ORIG_QUANTITY,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login,
x_fin_plan_type_id
);
Select project_accum_id into V_Accum_id
from pa_project_accum_headers
where project_id = x_project_id
and task_id = x_task_id
and resource_list_member_id = x_resource_list_member_id;
Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
from sys.dual;
Insert into PA_PROJECT_ACCUM_HEADERS
(PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
REQUEST_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN )
Values (v_accum_id,X_project_id,x_task_id,
x_current_period,
x_resource_id,x_resource_list_id,
x_resource_list_assignment_id,x_resource_list_Member_id,
pa_proj_accum_main.x_last_updated_by,
Trunc(sysdate),pa_proj_accum_main.x_request_id,
trunc(sysdate),
pa_proj_accum_main.x_created_by,
pa_proj_accum_main.x_last_update_login );
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,
FIN_PLAN_TYPE_ID
)
Values
(
V_Accum_id,x_budget_type_code,
Prj_BASE_RAW_COST_ITD,Prj_BASE_RAW_COST_YTD,
Prj_BASE_RAW_COST_PP, Prj_BASE_RAW_COST_PTD,
Prj_BASE_BURDENED_COST_ITD,Prj_BASE_BURDENED_COST_YTD,
Prj_BASE_BURDENED_COST_PP,Prj_BASE_BURDENED_COST_PTD,
Prj_ORIG_RAW_COST_ITD,Prj_ORIG_RAW_COST_YTD,
Prj_ORIG_RAW_COST_PP, Prj_ORIG_RAW_COST_PTD,
Prj_ORIG_BURDENED_COST_ITD,Prj_ORIG_BURDENED_COST_YTD,
Prj_ORIG_BURDENED_COST_PP,Prj_ORIG_BURDENED_COST_PTD,
Prj_BASE_QUANTITY_ITD,Prj_BASE_QUANTITY_YTD,Prj_BASE_QUANTITY_PP,
Prj_BASE_QUANTITY_PTD,
Prj_ORIG_QUANTITY_ITD,Prj_ORIG_QUANTITY_YTD,Prj_ORIG_QUANTITY_PP,
Prj_ORIG_QUANTITY_PTD,
Prj_BASE_LABOR_HOURS_ITD,Prj_BASE_LABOR_HOURS_YTD,Prj_BASE_LABOR_HOURS_PP,
Prj_BASE_LABOR_HOURS_PTD,
Prj_ORIG_LABOR_HOURS_ITD,Prj_ORIG_LABOR_HOURS_YTD,Prj_ORIG_LABOR_HOURS_PP,
Prj_ORIG_LABOR_HOURS_PTD,
Prj_BASE_REVENUE_ITD,Prj_BASE_REVENUE_YTD,Prj_BASE_REVENUE_PP,Prj_BASE_REVENUE_PTD,
Prj_ORIG_REVENUE_ITD,Prj_ORIG_REVENUE_YTD,Prj_ORIG_REVENUE_PP,Prj_ORIG_REVENUE_PTD,
X_BASE_UNIT_OF_MEASURE,X_ORIG_UNIT_OF_MEASURE,
Prj_BASE_RAW_COST,Prj_BASE_BURDENED_COST,Prj_ORIG_RAW_COST,
Prj_ORIG_BURDENED_COST,Prj_BASE_REVENUE,Prj_ORIG_REVENUE,
Prj_BASE_LABOR_HOURS,Prj_ORIG_LABOR_HOURS,Prj_BASE_QUANTITY,
Prj_ORIG_QUANTITY,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login,
x_fin_plan_type_id
);
Update PA_PROJECT_ACCUM_BUDGETS PAB SET
BASE_RAW_COST_ITD = NVL(BASE_RAW_COST_ITD,0) +
NVL(Prj_Base_Raw_Cost_itd,0),
BASE_RAW_COST_YTD = NVL(BASE_RAW_COST_YTD,0) +
NVL(Prj_Base_Raw_Cost_ytd,0),
BASE_RAW_COST_PTD = NVL(BASE_RAW_COST_PTD,0) +
NVL(Prj_Base_Raw_Cost_ptd,0),
BASE_RAW_COST_PP = NVL(BASE_RAW_COST_PP,0) +
NVL(Prj_Base_Raw_Cost_pp,0),
ORIG_RAW_COST_ITD = NVL(ORIG_RAW_COST_ITD,0) +
NVL(Prj_Orig_Raw_Cost_itd,0),
ORIG_RAW_COST_YTD = NVL(ORIG_RAW_COST_YTD,0) +
NVL(Prj_Orig_Raw_Cost_ytd,0),
ORIG_RAW_COST_PTD = NVL(ORIG_RAW_COST_PTD,0) +
NVL(Prj_Orig_Raw_Cost_ptd,0),
ORIG_RAW_COST_PP = NVL(ORIG_RAW_COST_PP,0) +
NVL(Prj_Orig_Raw_Cost_pp,0),
BASE_BURDENED_COST_ITD = NVL(BASE_BURDENED_COST_ITD,0) +
NVL(Prj_Base_Burdened_Cost_itd,0),
BASE_BURDENED_COST_YTD = NVL(BASE_BURDENED_COST_YTD,0) +
NVL(Prj_Base_Burdened_Cost_ytd,0),
BASE_BURDENED_COST_PTD = NVL(BASE_BURDENED_COST_PTD,0) +
NVL(Prj_Base_Burdened_Cost_ptd,0),
BASE_BURDENED_COST_PP = NVL(BASE_BURDENED_COST_PP,0) +
NVL(Prj_Base_Burdened_Cost_pp,0),
ORIG_BURDENED_COST_ITD = NVL(ORIG_BURDENED_COST_ITD,0) +
NVL(Prj_Orig_Burdened_Cost_itd,0),
ORIG_BURDENED_COST_YTD = NVL(ORIG_BURDENED_COST_YTD,0) +
NVL(Prj_Orig_Burdened_Cost_ytd,0),
ORIG_BURDENED_COST_PTD = NVL(ORIG_BURDENED_COST_PTD,0) +
NVL(Prj_Orig_Burdened_Cost_ptd,0),
ORIG_BURDENED_COST_PP = NVL(ORIG_BURDENED_COST_PP,0) +
NVL(Prj_Orig_Burdened_Cost_pp,0),
BASE_LABOR_HOURS_ITD = NVL(BASE_LABOR_HOURS_ITD,0) +
NVL(Prj_Base_Labor_Hours_itd,0),
BASE_LABOR_HOURS_YTD = NVL(BASE_LABOR_HOURS_YTD,0) +
NVL(Prj_Base_Labor_Hours_ytd,0),
BASE_LABOR_HOURS_PTD = NVL(BASE_LABOR_HOURS_PTD,0) +
NVL(Prj_Base_Labor_Hours_ptd,0),
BASE_LABOR_HOURS_PP = NVL(BASE_LABOR_HOURS_PP,0) +
NVL(Prj_Base_Labor_Hours_pp,0),
ORIG_LABOR_HOURS_ITD = NVL(ORIG_LABOR_HOURS_ITD,0) +
NVL(Prj_Orig_Labor_Hours_itd,0),
ORIG_LABOR_HOURS_YTD = NVL(ORIG_LABOR_HOURS_YTD,0) +
NVL(Prj_Orig_Labor_Hours_ytd,0),
ORIG_LABOR_HOURS_PTD = NVL(ORIG_LABOR_HOURS_PTD,0) +
NVL(Prj_Orig_Labor_Hours_ptd,0),
ORIG_LABOR_HOURS_PP = NVL(ORIG_LABOR_HOURS_PP,0) +
NVL(Prj_Orig_Labor_Hours_pp,0),
BASE_QUANTITY_ITD = NVL(BASE_QUANTITY_ITD,0) +
NVL(Prj_Base_Quantity_itd,0),
BASE_QUANTITY_YTD = NVL(BASE_QUANTITY_YTD,0) +
NVL(Prj_Base_Quantity_ytd,0),
BASE_QUANTITY_PTD = NVL(BASE_QUANTITY_PTD,0) +
NVL(Prj_Base_Quantity_ptd,0),
BASE_QUANTITY_PP = NVL(BASE_QUANTITY_PP,0) +
NVL(Prj_Base_Quantity_pp,0),
ORIG_QUANTITY_ITD = NVL(ORIG_QUANTITY_ITD,0) +
NVL(Prj_Orig_Quantity_itd,0),
ORIG_QUANTITY_YTD = NVL(ORIG_QUANTITY_YTD,0) +
NVL(Prj_Orig_Quantity_ytd,0),
ORIG_QUANTITY_PTD = NVL(ORIG_QUANTITY_PTD,0) +
NVL(Prj_Orig_Quantity_ptd,0),
ORIG_QUANTITY_PP = NVL(ORIG_QUANTITY_PP,0) +
NVL(Prj_Orig_Quantity_pp,0),
BASE_REVENUE_ITD = NVL(BASE_REVENUE_ITD,0) +
NVL(Prj_Base_Revenue_itd,0),
BASE_REVENUE_YTD = NVL(BASE_REVENUE_YTD,0) +
NVL(Prj_Base_Revenue_ytd,0),
BASE_REVENUE_PTD = NVL(BASE_REVENUE_PTD,0) +
NVL(Prj_Base_Revenue_ptd,0),
BASE_REVENUE_PP = NVL(BASE_REVENUE_PP,0) +
NVL(Prj_Base_Revenue_pp,0),
ORIG_REVENUE_ITD = NVL(ORIG_REVENUE_ITD,0) +
NVL(Prj_Orig_Revenue_itd,0),
ORIG_REVENUE_YTD = NVL(ORIG_REVENUE_YTD,0) +
NVL(Prj_Orig_Revenue_ytd,0),
ORIG_REVENUE_PTD = NVL(ORIG_REVENUE_PTD,0) +
NVL(Prj_Orig_Revenue_ptd,0),
ORIG_REVENUE_PP = NVL(ORIG_REVENUE_PP,0) +
NVL(Prj_Orig_Revenue_pp,0),
BASE_RAW_COST_TOT = NVL(BASE_RAW_COST_TOT,0 ) +
Prj_BASE_RAW_COST,
BASE_BURDENED_COST_TOT = NVL(BASE_BURDENED_COST_TOT,0) +
Prj_BASE_BURDENED_COST,
ORIG_RAW_COST_TOT = NVL(ORIG_RAW_COST_TOT,0) +
Prj_ORIG_RAW_COST,
ORIG_BURDENED_COST_TOT = NVL(ORIG_BURDENED_COST_TOT,0) +
Prj_ORIG_BURDENED_COST,
BASE_REVENUE_TOT = NVL(BASE_REVENUE_TOT,0 ) +
Prj_BASE_REVENUE,
ORIG_REVENUE_TOT = NVL(ORIG_REVENUE_TOT,0 ) +
Prj_ORIG_REVENUE,
BASE_LABOR_HOURS_TOT = NVL(BASE_LABOR_HOURS_TOT,0) +
Prj_BASE_LABOR_HOURS,
ORIG_LABOR_HOURS_TOT = NVL(ORIG_LABOR_HOURS_TOT,0 ) +
Prj_ORIG_LABOR_HOURS,
BASE_QUANTITY_TOT = NVL(BASE_QUANTITY_TOT,0) +
Prj_BASE_QUANTITY,
ORIG_QUANTITY_TOT = NVL(ORIG_QUANTITY_TOT,0) +
Prj_ORIG_QUANTITY,
BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
LAST_UPDATE_DATE = Trunc(Sysdate),
LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login,
FIN_PLAN_TYPE_ID = x_fin_plan_type_id
Where Budget_Type_Code = x_Budget_type_code
And PAB.Project_Accum_id = v_accum_id;
Insert into PA_PROJECT_ACCUM_BUDGETS (
PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
BASE_QUANTITY_PTD,
ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
ORIG_QUANTITY_PTD,
BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
BASE_LABOR_HOURS_PTD,
ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
ORIG_LABOR_HOURS_PTD,
BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
ORIG_QUANTITY_TOT,
REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,
FIN_PLAN_TYPE_ID
)
Values
(
V_Accum_id,x_budget_type_code,
Prj_BASE_RAW_COST_ITD,Prj_BASE_RAW_COST_YTD,
Prj_BASE_RAW_COST_PP, Prj_BASE_RAW_COST_PTD,
Prj_BASE_BURDENED_COST_ITD,Prj_BASE_BURDENED_COST_YTD,
Prj_BASE_BURDENED_COST_PP,Prj_BASE_BURDENED_COST_PTD,
Prj_ORIG_RAW_COST_ITD,Prj_ORIG_RAW_COST_YTD,
Prj_ORIG_RAW_COST_PP, Prj_ORIG_RAW_COST_PTD,
Prj_ORIG_BURDENED_COST_ITD,Prj_ORIG_BURDENED_COST_YTD,
Prj_ORIG_BURDENED_COST_PP,Prj_ORIG_BURDENED_COST_PTD,
Prj_BASE_QUANTITY_ITD,Prj_BASE_QUANTITY_YTD,Prj_BASE_QUANTITY_PP,
Prj_BASE_QUANTITY_PTD,
Prj_ORIG_QUANTITY_ITD,Prj_ORIG_QUANTITY_YTD,Prj_ORIG_QUANTITY_PP,
Prj_ORIG_QUANTITY_PTD,
Prj_BASE_LABOR_HOURS_ITD,Prj_BASE_LABOR_HOURS_YTD,Prj_BASE_LABOR_HOURS_PP,
Prj_BASE_LABOR_HOURS_PTD,
Prj_ORIG_LABOR_HOURS_ITD,Prj_ORIG_LABOR_HOURS_YTD,Prj_ORIG_LABOR_HOURS_PP,
Prj_ORIG_LABOR_HOURS_PTD,
Prj_BASE_REVENUE_ITD,Prj_BASE_REVENUE_YTD,Prj_BASE_REVENUE_PP,Prj_BASE_REVENUE_PTD,
Prj_ORIG_REVENUE_ITD,Prj_ORIG_REVENUE_YTD,Prj_ORIG_REVENUE_PP,Prj_ORIG_REVENUE_PTD,
X_BASE_UNIT_OF_MEASURE,X_ORIG_UNIT_OF_MEASURE,
Prj_BASE_RAW_COST,Prj_BASE_BURDENED_COST,Prj_ORIG_RAW_COST,
Prj_ORIG_BURDENED_COST,Prj_BASE_REVENUE,Prj_ORIG_REVENUE,
Prj_BASE_LABOR_HOURS,Prj_ORIG_LABOR_HOURS,Prj_BASE_QUANTITY,
Prj_ORIG_QUANTITY,
pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login,
x_fin_plan_type_id
);