[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_flag_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
l_child_update_required VARCHAR2(1);
l_parent_update_required VARCHAR2(1);
l_update_date_flag_tab1 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_date_flag_tab2 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_date_flag_tab3 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_date_flag_tab4 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_date_flag_tab5 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_date_flag_tab6 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_requ_flag_tab1 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_requ_flag_tab2 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_requ_flag_tab3 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_requ_flag_tab4 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_requ_flag_tab5 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_requ_flag_tab6 SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
l_update_required VARCHAR2(1):= 'N';
SELECT
OBJECT_TYPE,
OBJECT_ID,
PARENT_OBJECT_TYPE,
PARENT_OBJECT_ID,
CALENDAR_ID,
CONSTRAINT_TYPE_CODE,
CONSTRAINT_DATE,
WBS_LEVEL,
START_DATE1,
START_DATE_OVERRIDE1,
FINISH_DATE1,
DURATION1,
TASK_STATUS1,
PROGRESS_STATUS_WEIGHT1,
PROGRESS_OVERRIDE1,
REMAINING_EFFORT1,
PERCENT_COMPLETE1,
PERCENT_OVERRIDE1,
TASK_WEIGHT1,
NUMBER_FIELD1,
ROLLUP_NODE1,
DIRTY_FLAG1,
ETC_Cost1,
PPL_ETC_COST1,
EQPMT_ETC_COST1,
PPL_UNPLAND_EFFORT1,
EQPMT_ETC_EFFORT1,
SUB_PRJ_ETC_COST1,
SUB_PRJ_PPL_ETC_COST1,
SUB_PRJ_EQPMT_ETC_COST1,
SUB_PRJ_PPL_ETC_EFFORT1,
SUB_PRJ_EQPMT_ETC_EFFORT1,
EARNED_VALUE1,
BAC_VALUE1,
START_DATE2,
START_DATE_OVERRIDE2,
FINISH_DATE2,
DURATION2,
TASK_STATUS2,
PROGRESS_STATUS_WEIGHT2,
PROGRESS_OVERRIDE2,
REMAINING_EFFORT2,
PERCENT_COMPLETE2,
PERCENT_OVERRIDE2,
TASK_WEIGHT2,
NUMBER_FIELD2,
ROLLUP_NODE2,
DIRTY_FLAG2,
ETC_Cost2,
PPL_ETC_COST2,
EQPMT_ETC_COST2,
PPL_UNPLAND_EFFORT2,
EQPMT_ETC_EFFORT2,
SUB_PRJ_ETC_COST2,
SUB_PRJ_PPL_ETC_COST2,
SUB_PRJ_EQPMT_ETC_COST2,
SUB_PRJ_PPL_ETC_EFFORT2,
SUB_PRJ_EQPMT_ETC_EFFORT2,
EARNED_VALUE2,
BAC_VALUE2,
START_DATE3,
START_DATE_OVERRIDE3,
FINISH_DATE3,
DURATION3,
TASK_STATUS3,
PROGRESS_STATUS_WEIGHT3,
PROGRESS_OVERRIDE3,
REMAINING_EFFORT3,
PERCENT_COMPLETE3,
PERCENT_OVERRIDE3,
TASK_WEIGHT3,
NUMBER_FIELD3,
ROLLUP_NODE3,
DIRTY_FLAG3,
ETC_Cost3,
PPL_ETC_COST3,
EQPMT_ETC_COST3,
PPL_UNPLAND_EFFORT3,
EQPMT_ETC_EFFORT3,
SUB_PRJ_ETC_COST3,
SUB_PRJ_PPL_ETC_COST3,
SUB_PRJ_EQPMT_ETC_COST3,
SUB_PRJ_PPL_ETC_EFFORT3,
SUB_PRJ_EQPMT_ETC_EFFORT3,
EARNED_VALUE3,
BAC_VALUE3,
START_DATE4,
START_DATE_OVERRIDE4,
FINISH_DATE4,
DURATION4,
TASK_STATUS4,
PROGRESS_STATUS_WEIGHT4,
PROGRESS_OVERRIDE4,
REMAINING_EFFORT4,
PERCENT_COMPLETE4,
PERCENT_OVERRIDE4,
TASK_WEIGHT4,
NUMBER_FIELD4,
ROLLUP_NODE4,
DIRTY_FLAG4,
ETC_Cost4,
PPL_ETC_COST4,
EQPMT_ETC_COST4,
PPL_UNPLAND_EFFORT4,
EQPMT_ETC_EFFORT4,
SUB_PRJ_ETC_COST4,
SUB_PRJ_PPL_ETC_COST4,
SUB_PRJ_EQPMT_ETC_COST4,
SUB_PRJ_PPL_ETC_EFFORT4,
SUB_PRJ_EQPMT_ETC_EFFORT4,
EARNED_VALUE4,
BAC_VALUE4,
START_DATE5,
START_DATE_OVERRIDE5,
FINISH_DATE5,
DURATION5,
TASK_STATUS5,
PROGRESS_STATUS_WEIGHT5,
PROGRESS_OVERRIDE5,
REMAINING_EFFORT5,
PERCENT_COMPLETE5,
PERCENT_OVERRIDE5,
TASK_WEIGHT5,
NUMBER_FIELD5,
ROLLUP_NODE5,
DIRTY_FLAG5,
ETC_Cost5,
PPL_ETC_COST5,
EQPMT_ETC_COST5,
PPL_UNPLAND_EFFORT5,
EQPMT_ETC_EFFORT5,
SUB_PRJ_ETC_COST5,
SUB_PRJ_PPL_ETC_COST5,
SUB_PRJ_EQPMT_ETC_COST5,
SUB_PRJ_PPL_ETC_EFFORT5,
SUB_PRJ_EQPMT_ETC_EFFORT5,
EARNED_VALUE5,
BAC_VALUE5,
START_DATE6,
START_DATE_OVERRIDE6,
FINISH_DATE6,
DURATION6,
TASK_STATUS6,
PROGRESS_STATUS_WEIGHT6,
PROGRESS_OVERRIDE6,
REMAINING_EFFORT6,
PERCENT_COMPLETE6,
PERCENT_OVERRIDE6,
TASK_WEIGHT6,
NUMBER_FIELD6,
ROLLUP_NODE6,
DIRTY_FLAG6,
ETC_Cost6,
PPL_ETC_COST6,
EQPMT_ETC_COST6,
PPL_UNPLAND_EFFORT6,
EQPMT_ETC_EFFORT6,
SUB_PRJ_ETC_COST6,
SUB_PRJ_PPL_ETC_COST6,
SUB_PRJ_EQPMT_ETC_COST6,
SUB_PRJ_PPL_ETC_EFFORT6,
SUB_PRJ_EQPMT_ETC_EFFORT6,
EARNED_VALUE6,
BAC_VALUE6,
PERC_COMP_DERIVATIVE_CODE1,
PERC_COMP_DERIVATIVE_CODE2,
PERC_COMP_DERIVATIVE_CODE3,
PERC_COMP_DERIVATIVE_CODE4,
PERC_COMP_DERIVATIVE_CODE5,
PERC_COMP_DERIVATIVE_CODE6,
SUMMARY_OBJECT_FLAG -- 4370746
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = C_Process_Number
order by Object_Type, Object_ID;
SELECT
OBJECT_TYPE,
OBJECT_ID,
PARENT_OBJECT_TYPE,
PARENT_OBJECT_ID,
CALENDAR_ID,
CONSTRAINT_TYPE_CODE,
CONSTRAINT_DATE,
WBS_LEVEL,
START_DATE1,
START_DATE_OVERRIDE1,
FINISH_DATE1,
DURATION1,
TASK_STATUS1,
PROGRESS_STATUS_WEIGHT1,
PROGRESS_OVERRIDE1,
REMAINING_EFFORT1,
PERCENT_COMPLETE1,
PERCENT_OVERRIDE1,
TASK_WEIGHT1,
NUMBER_FIELD1,
ROLLUP_NODE1,
DIRTY_FLAG1,
ETC_Cost1,
PPL_ETC_COST1,
EQPMT_ETC_COST1,
PPL_UNPLAND_EFFORT1,
EQPMT_ETC_EFFORT1,
SUB_PRJ_ETC_COST1,
SUB_PRJ_PPL_ETC_COST1,
SUB_PRJ_EQPMT_ETC_COST1,
SUB_PRJ_PPL_ETC_EFFORT1,
SUB_PRJ_EQPMT_ETC_EFFORT1,
EARNED_VALUE1,
BAC_VALUE1,
START_DATE2,
START_DATE_OVERRIDE2,
FINISH_DATE2,
DURATION2,
TASK_STATUS2,
PROGRESS_STATUS_WEIGHT2,
PROGRESS_OVERRIDE2,
REMAINING_EFFORT2,
PERCENT_COMPLETE2,
PERCENT_OVERRIDE2,
TASK_WEIGHT2,
NUMBER_FIELD2,
ROLLUP_NODE2,
DIRTY_FLAG2,
ETC_Cost2,
PPL_ETC_COST2,
EQPMT_ETC_COST2,
PPL_UNPLAND_EFFORT2,
EQPMT_ETC_EFFORT2,
SUB_PRJ_ETC_COST2,
SUB_PRJ_PPL_ETC_COST2,
SUB_PRJ_EQPMT_ETC_COST2,
SUB_PRJ_PPL_ETC_EFFORT2,
SUB_PRJ_EQPMT_ETC_EFFORT2,
EARNED_VALUE2,
BAC_VALUE2,
START_DATE3,
START_DATE_OVERRIDE3,
FINISH_DATE3,
DURATION3,
TASK_STATUS3,
PROGRESS_STATUS_WEIGHT3,
PROGRESS_OVERRIDE3,
REMAINING_EFFORT3,
PERCENT_COMPLETE3,
PERCENT_OVERRIDE3,
TASK_WEIGHT3,
NUMBER_FIELD3,
ROLLUP_NODE3,
DIRTY_FLAG3,
ETC_Cost3,
PPL_ETC_COST3,
EQPMT_ETC_COST3,
PPL_UNPLAND_EFFORT3,
EQPMT_ETC_EFFORT3,
SUB_PRJ_ETC_COST3,
SUB_PRJ_PPL_ETC_COST3,
SUB_PRJ_EQPMT_ETC_COST3,
SUB_PRJ_PPL_ETC_EFFORT3,
SUB_PRJ_EQPMT_ETC_EFFORT3,
EARNED_VALUE3,
BAC_VALUE3,
START_DATE4,
START_DATE_OVERRIDE4,
FINISH_DATE4,
DURATION4,
TASK_STATUS4,
PROGRESS_STATUS_WEIGHT4,
PROGRESS_OVERRIDE4,
REMAINING_EFFORT4,
PERCENT_COMPLETE4,
PERCENT_OVERRIDE4,
TASK_WEIGHT4,
NUMBER_FIELD4,
ROLLUP_NODE4,
DIRTY_FLAG4,
ETC_Cost4,
PPL_ETC_COST4,
EQPMT_ETC_COST4,
PPL_UNPLAND_EFFORT4,
EQPMT_ETC_EFFORT4,
SUB_PRJ_ETC_COST4,
SUB_PRJ_PPL_ETC_COST4,
SUB_PRJ_EQPMT_ETC_COST4,
SUB_PRJ_PPL_ETC_EFFORT4,
SUB_PRJ_EQPMT_ETC_EFFORT4,
EARNED_VALUE4,
BAC_VALUE4,
START_DATE5,
START_DATE_OVERRIDE5,
FINISH_DATE5,
DURATION5,
TASK_STATUS5,
PROGRESS_STATUS_WEIGHT5,
PROGRESS_OVERRIDE5,
REMAINING_EFFORT5,
PERCENT_COMPLETE5,
PERCENT_OVERRIDE5,
TASK_WEIGHT5,
NUMBER_FIELD5,
ROLLUP_NODE5,
DIRTY_FLAG5,
ETC_Cost5,
PPL_ETC_COST5,
EQPMT_ETC_COST5,
PPL_UNPLAND_EFFORT5,
EQPMT_ETC_EFFORT5,
SUB_PRJ_ETC_COST5,
SUB_PRJ_PPL_ETC_COST5,
SUB_PRJ_EQPMT_ETC_COST5,
SUB_PRJ_PPL_ETC_EFFORT5,
SUB_PRJ_EQPMT_ETC_EFFORT5,
EARNED_VALUE5,
BAC_VALUE5,
START_DATE6,
START_DATE_OVERRIDE6,
FINISH_DATE6,
DURATION6,
TASK_STATUS6,
PROGRESS_STATUS_WEIGHT6,
PROGRESS_OVERRIDE6,
REMAINING_EFFORT6,
PERCENT_COMPLETE6,
PERCENT_OVERRIDE6,
TASK_WEIGHT6,
NUMBER_FIELD6,
ROLLUP_NODE6,
DIRTY_FLAG6,
ETC_Cost6,
PPL_ETC_COST6,
EQPMT_ETC_COST6,
PPL_UNPLAND_EFFORT6,
EQPMT_ETC_EFFORT6,
SUB_PRJ_ETC_COST6,
SUB_PRJ_PPL_ETC_COST6,
SUB_PRJ_EQPMT_ETC_COST6,
SUB_PRJ_PPL_ETC_EFFORT6,
SUB_PRJ_EQPMT_ETC_EFFORT6,
EARNED_VALUE6,
BAC_VALUE6,
PERC_COMP_DERIVATIVE_CODE1,
PERC_COMP_DERIVATIVE_CODE2,
PERC_COMP_DERIVATIVE_CODE3,
PERC_COMP_DERIVATIVE_CODE4,
PERC_COMP_DERIVATIVE_CODE5,
PERC_COMP_DERIVATIVE_CODE6,
SUMMARY_OBJECT_FLAG -- 4370746
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = C_Process_Number
order by calendar_id;
SELECT object_id, object_type, start_date1, start_date2, start_date3,
start_date4, start_date5, start_date6, finish_date1,finish_date2, finish_date3,
finish_date4, finish_date5, finish_date6, duration1, duration2, duration3, duration4,
duration5, duration6, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
, 'N', 'N', 'N', 'N', 'N', 'N'
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE PROCESS_NUMBER = l_process_number
-- 4218507 : This could be made dependenct on dirty_flag1='Y' only. Not sure whethere this will improve performance
AND (dirty_flag1='Y' OR dirty_flag2='Y' OR dirty_flag3='Y' OR dirty_flag4='Y' OR dirty_flag5='Y'
OR dirty_flag6='Y')
AND Not Exists (select * from PA_PROJ_ROLLUP_BULK_TEMP t2
where t2.PROCESS_NUMBER = l_process_number
and t2.parent_object_id = t1.object_id
AND ROWNUM < 2);
SELECT object_id, object_type, start_date1, start_date2, start_date3,
start_date4, start_date5, start_date6, finish_date1,finish_date2, finish_date3,
finish_date4, finish_date5, finish_date6, duration1, duration2, duration3, duration4,
duration5, duration6, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6,
start_date_override1, start_date_override2, start_date_override3, start_date_override4,
start_date_override5, start_date_override6
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
-- 4218507 : This could be made dependent on dirty_flag1=Y only
AND (dirty_flag1='Y' OR dirty_flag2='Y' OR dirty_flag3='Y' OR dirty_flag4='Y' OR dirty_flag5='Y' OR
dirty_flag6='Y')
AND object_id <> c_object_id
START WITH object_id = c_object_ID
CONNECT BY object_id = PRIOR parent_object_id
order by wbs_level desc;
SELECT object_id, object_type,REMAINING_EFFORT1,REMAINING_EFFORT2, REMAINING_EFFORT3, REMAINING_EFFORT4
, REMAINING_EFFORT5, REMAINING_EFFORT6
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE process_number = l_process_number
AND OBJECT_TYPE <> 'PA_DELIVERABLES'
AND Exists (SELECT * from PA_PROJ_ROLLUP_BULK_TEMP t2
WHERE t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
AND t2.parent_object_id = t1.object_id
AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
AND process_number = l_process_number
AND ROWNUM < 2)
Order By WBS_LEVEL DESC;
SELECT Object_ID, object_type, PROGRESS_STATUS_WEIGHT1, PROGRESS_STATUS_WEIGHT2, PROGRESS_STATUS_WEIGHT3
, PROGRESS_STATUS_WEIGHT4, PROGRESS_STATUS_WEIGHT5, PROGRESS_STATUS_WEIGHT6
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
--WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES')
WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
AND process_number = l_process_number
AND Exists (Select * From PA_PROJ_ROLLUP_BULK_TEMP t2
-- Where t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES', 'PA_SUBPROJECTS')
Where t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_SUBPROJECTS')
AND t2.parent_object_id = t1.object_id
AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
AND process_number = l_process_number
AND ROWNUM < 2)
Order By WBS_LEVEL DESC;
SELECT object_id, object_type, task_status1, task_status2, task_status3, task_status4, task_status5, task_status6
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
AND Exists (Select * From PA_PROJ_ROLLUP_BULK_TEMP t2
Where t2.PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
AND t2.parent_object_id = t1.object_id
AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
AND RowNum < 2)
Order By WBS_LEVEL DESC;
SELECT decode(rollup_node1, 'Y', TASK_STATUS1, -1)
, decode(rollup_node2, 'Y', TASK_STATUS2, -1)
, decode(rollup_node3, 'Y', TASK_STATUS3, -1)
, decode(rollup_node4, 'Y', TASK_STATUS4, -1)
, decode(rollup_node5, 'Y', TASK_STATUS5, -1)
, decode(rollup_node6, 'Y', TASK_STATUS6, -1)
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
AND parent_object_id = p_parent_id
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
SELECT TASK_STATUS1, TASK_STATUS2, TASK_STATUS3, TASK_STATUS4, TASK_STATUS5, TASK_STATUS6
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
AND parent_object_id = p_parent_id
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
SELECT object_id, OBJECT_TYPE, Perc_Comp_Derivative_Code1, BAC_VALUE1, BAC_VALUE2, BAC_VALUE3, BAC_VALUE4,
BAC_VALUE5, BAC_VALUE6, PERCENT_COMPLETE1, PERCENT_COMPLETE2, PERCENT_COMPLETE3,
PERCENT_COMPLETE4, PERCENT_COMPLETE5, PERCENT_COMPLETE6, PERCENT_OVERRIDE1, PERCENT_OVERRIDE2,
PERCENT_OVERRIDE3, PERCENT_OVERRIDE4, PERCENT_OVERRIDE5, PERCENT_OVERRIDE6, EARNED_VALUE1, EARNED_VALUE2
, EARNED_VALUE3, EARNED_VALUE4, EARNED_VALUE5, EARNED_VALUE6
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
AND nvl(SUMMARY_OBJECT_FLAG, 'N') NOT IN ('Y', 'L') -- 4370746 -- 4586449 : Added 'L'
AND not Exists (Select * From PA_PROJ_ROLLUP_BULK_TEMP t2
Where t2.PROCESS_NUMBER = l_process_number
AND t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
And t2.parent_object_id = t1.object_id
AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
AND ROWNUM < 2);
SELECT object_id, OBJECT_TYPE, Perc_Comp_Derivative_Code1, BAC_VALUE1, BAC_VALUE2, BAC_VALUE3, BAC_VALUE4,
BAC_VALUE5, BAC_VALUE6, PERCENT_COMPLETE1, PERCENT_COMPLETE2, PERCENT_COMPLETE3,
PERCENT_COMPLETE4, PERCENT_COMPLETE5, PERCENT_COMPLETE6, PERCENT_OVERRIDE1, PERCENT_OVERRIDE2,
PERCENT_OVERRIDE3, PERCENT_OVERRIDE4, PERCENT_OVERRIDE5, PERCENT_OVERRIDE6, EARNED_VALUE1, EARNED_VALUE2
, EARNED_VALUE3, EARNED_VALUE4, EARNED_VALUE5, EARNED_VALUE6
, summary_object_flag -- 4587517
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
-- 4530036 : SUMMARY_OBJECT_FLAG is added so that
-- earned value gets recalculated even if no childs are passed to summary level
AND( nvl(SUMMARY_OBJECT_FLAG, 'N') IN ('Y','L') -- 4586449 : Added 'L'
OR Exists (Select * From PA_PROJ_ROLLUP_BULK_TEMP t2
Where t2.PROCESS_NUMBER = l_process_number
AND t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
And t2.parent_object_id = t1.object_id
AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
AND ROWNUM < 2)
)
Order By WBS_LEVEL DESC;
SELECT object_id, object_type, ETC_Cost1, ETC_Cost2, ETC_Cost3, ETC_Cost4, ETC_Cost5, ETC_Cost6
, PPL_ETC_COST1, PPL_ETC_COST2, PPL_ETC_COST3, PPL_ETC_COST4, PPL_ETC_COST5, PPL_ETC_COST6
, EQPMT_ETC_COST1, EQPMT_ETC_COST2, EQPMT_ETC_COST3, EQPMT_ETC_COST4, EQPMT_ETC_COST5, EQPMT_ETC_COST6
, PPL_UNPLAND_EFFORT1, PPL_UNPLAND_EFFORT2, PPL_UNPLAND_EFFORT3, PPL_UNPLAND_EFFORT4, PPL_UNPLAND_EFFORT5, PPL_UNPLAND_EFFORT6
, EQPMT_ETC_EFFORT1, EQPMT_ETC_EFFORT2, EQPMT_ETC_EFFORT3, EQPMT_ETC_EFFORT4, EQPMT_ETC_EFFORT5, EQPMT_ETC_EFFORT6
, SUB_PRJ_ETC_COST1, SUB_PRJ_ETC_COST2, SUB_PRJ_ETC_COST3, SUB_PRJ_ETC_COST4, SUB_PRJ_ETC_COST5, SUB_PRJ_ETC_COST6
, SUB_PRJ_PPL_ETC_COST1, SUB_PRJ_PPL_ETC_COST2, SUB_PRJ_PPL_ETC_COST3, SUB_PRJ_PPL_ETC_COST4, SUB_PRJ_PPL_ETC_COST5, SUB_PRJ_PPL_ETC_COST6
, SUB_PRJ_EQPMT_ETC_COST1, SUB_PRJ_EQPMT_ETC_COST2, SUB_PRJ_EQPMT_ETC_COST3, SUB_PRJ_EQPMT_ETC_COST4, SUB_PRJ_EQPMT_ETC_COST5, SUB_PRJ_EQPMT_ETC_COST6
, SUB_PRJ_PPL_ETC_EFFORT1, SUB_PRJ_PPL_ETC_EFFORT2, SUB_PRJ_PPL_ETC_EFFORT3, SUB_PRJ_PPL_ETC_EFFORT4, SUB_PRJ_PPL_ETC_EFFORT5, SUB_PRJ_PPL_ETC_EFFORT6
, SUB_PRJ_EQPMT_ETC_EFFORT1, SUB_PRJ_EQPMT_ETC_EFFORT2, SUB_PRJ_EQPMT_ETC_EFFORT3, SUB_PRJ_EQPMT_ETC_EFFORT4, SUB_PRJ_EQPMT_ETC_EFFORT5, SUB_PRJ_EQPMT_ETC_EFFORT6
, REMAINING_EFFORT1, REMAINING_EFFORT2, REMAINING_EFFORT3, REMAINING_EFFORT4, REMAINING_EFFORT5, REMAINING_EFFORT6
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE NOT IN ( 'PA_DELIVERABLES', 'PA_ASSIGNMENTS', 'PA_SUBPROJECTS')
AND Exists (SELECT * from PA_PROJ_ROLLUP_BULK_TEMP t2
WHERE t2.PROCESS_NUMBER = l_process_number
AND t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
AND t2.parent_object_id = t1.object_id
AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
AND ROWNUM < 2)
Order By WBS_LEVEL DESC;
SELECT object_id, object_type, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE <> 'PA_DELIVERABLES'
AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
OR Dirty_flag6 ='Y')
AND Exists (select 1 from PA_PROJ_ROLLUP_BULK_TEMP t2
where t2.PROCESS_NUMBER = l_process_number
and t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
and t2.parent_object_id = t1.object_id
AND ROWNUM < 2
);
SELECT t.Object_ID, t.object_type, t.dirty_flag1, t.dirty_flag2, t.dirty_flag3
, t.dirty_flag4, t.dirty_flag5, t.dirty_flag6, c_dirty_flag1 ref_dirty_flag1
, c_dirty_flag2 ref_dirty_flag2, c_dirty_flag3 ref_dirty_flag3, c_dirty_flag4 ref_dirty_flag4
, c_dirty_flag5 ref_dirty_flag5, c_dirty_flag6 ref_dirty_flag6
FROM
(SELECT t2.Object_ID, t2.object_type, t2.dirty_flag1, t2.dirty_flag2, t2.dirty_flag3
, t2.dirty_flag4, t2.dirty_flag5, t2.dirty_flag6,t2.wbs_level order_by_clause
FROM PA_PROJ_ROLLUP_BULK_TEMP T2
WHERE T2.PROCESS_NUMBER = l_process_number
AND T2.Object_ID <> c_object_id
START WITH T2.PROCESS_NUMBER = l_process_number
AND T2.Object_ID = c_object_id
CONNECT BY T2.PROCESS_NUMBER = l_process_number
AND T2.Parent_Object_ID = PRIOR T2.Object_ID) t
order by order_by_clause desc;
SELECT object_id, object_type, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
FROM PA_PROJ_ROLLUP_BULK_TEMP t1
WHERE PROCESS_NUMBER = l_process_number
AND OBJECT_TYPE <> 'PA_DELIVERABLES'
AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
OR Dirty_flag6 ='Y')
AND NOT Exists (select 1 from PA_PROJ_ROLLUP_BULK_TEMP t2
where t2.PROCESS_NUMBER = l_process_number
and t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
and t2.parent_object_id = t1.object_id
AND ROWNUM < 2
);
SELECT t.Object_ID, t.object_type, t.dirty_flag1, t.dirty_flag2, t.dirty_flag3
, t.dirty_flag4, t.dirty_flag5, t.dirty_flag6, c_dirty_flag1 ref_dirty_flag1
, c_dirty_flag2 ref_dirty_flag2, c_dirty_flag3 ref_dirty_flag3, c_dirty_flag4 ref_dirty_flag4
, c_dirty_flag5 ref_dirty_flag5, c_dirty_flag6 ref_dirty_flag6
FROM
( SELECT t2.Object_ID, t2.object_type, t2.dirty_flag1, t2.dirty_flag2, t2.dirty_flag3
, t2.dirty_flag4, t2.dirty_flag5, t2.dirty_flag6, t2.wbs_level order_by_clause
FROM PA_PROJ_ROLLUP_BULK_TEMP T2
WHERE T2.PROCESS_NUMBER = l_process_number
AND T2.Object_ID <> c_object_id
START WITH T2.PROCESS_NUMBER = l_process_number
AND T2.Object_ID = c_object_id
CONNECT BY T2.PROCESS_NUMBER = l_process_number
AND T2.Object_ID = PRIOR T2.Parent_Object_ID) t
order by t.order_by_clause asc;
SELECT object_id, object_type, start_date1, start_date2, start_date3,
start_date4, start_date5, start_date6, finish_date1,finish_date2, finish_date3,
finish_date4, finish_date5, finish_date6, duration1, duration2, duration3, duration4,
duration5, duration6, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6,
start_date_override1, start_date_override2, start_date_override3, start_date_override4,
start_date_override5, start_date_override6, 'N', 'N', 'N', 'N', 'N', 'N' , 'N', 'N', 'N', 'N', 'N', 'N'
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND (dirty_flag1='Y' OR dirty_flag2='Y' OR dirty_flag3='Y' OR dirty_flag4='Y' OR dirty_flag5='Y' OR
dirty_flag6='Y')
order by wbs_level desc;
SELECT PA_PROJ_ROLLUP_BULK_TEMP_S.nextval
INTO l_Process_Number FROM dual;
INSERT INTO PA_PROJ_ROLLUP_BULK_TEMP(
PROCESS_NUMBER,
OBJECT_TYPE,
OBJECT_ID,
PARENT_OBJECT_TYPE,
PARENT_OBJECT_ID,
CALENDAR_ID,
CONSTRAINT_TYPE_CODE,
CONSTRAINT_DATE,
WBS_LEVEL,
START_DATE1,
START_DATE_OVERRIDE1,
FINISH_DATE1,
DURATION1,
TASK_STATUS1,
PROGRESS_STATUS_WEIGHT1,
PROGRESS_OVERRIDE1,
REMAINING_EFFORT1,
PERCENT_COMPLETE1,
PERCENT_OVERRIDE1,
TASK_WEIGHT1,
NUMBER_FIELD1,
ROLLUP_NODE1,
DIRTY_FLAG1,
ETC_Cost1,
PPL_ETC_COST1,
EQPMT_ETC_COST1,
PPL_UNPLAND_EFFORT1,
EQPMT_ETC_EFFORT1,
SUB_PRJ_ETC_COST1,
SUB_PRJ_PPL_ETC_COST1,
SUB_PRJ_EQPMT_ETC_COST1,
SUB_PRJ_PPL_ETC_EFFORT1,
SUB_PRJ_EQPMT_ETC_EFFORT1,
EARNED_VALUE1,
BAC_VALUE1,
PERC_COMP_DERIVATIVE_CODE1,
START_DATE2,
START_DATE_OVERRIDE2,
FINISH_DATE2,
DURATION2,
TASK_STATUS2,
PROGRESS_STATUS_WEIGHT2,
PROGRESS_OVERRIDE2,
REMAINING_EFFORT2,
PERCENT_COMPLETE2,
PERCENT_OVERRIDE2,
TASK_WEIGHT2,
NUMBER_FIELD2,
ROLLUP_NODE2,
DIRTY_FLAG2,
ETC_Cost2,
PPL_ETC_COST2,
EQPMT_ETC_COST2,
PPL_UNPLAND_EFFORT2,
EQPMT_ETC_EFFORT2,
SUB_PRJ_ETC_COST2,
SUB_PRJ_PPL_ETC_COST2,
SUB_PRJ_EQPMT_ETC_COST2,
SUB_PRJ_PPL_ETC_EFFORT2,
SUB_PRJ_EQPMT_ETC_EFFORT2,
EARNED_VALUE2,
BAC_VALUE2,
PERC_COMP_DERIVATIVE_CODE2,
START_DATE3,
START_DATE_OVERRIDE3,
FINISH_DATE3,
DURATION3,
TASK_STATUS3,
PROGRESS_STATUS_WEIGHT3,
PROGRESS_OVERRIDE3,
REMAINING_EFFORT3,
PERCENT_COMPLETE3,
PERCENT_OVERRIDE3,
TASK_WEIGHT3,
NUMBER_FIELD3,
ROLLUP_NODE3,
DIRTY_FLAG3,
ETC_Cost3,
PPL_ETC_COST3,
EQPMT_ETC_COST3,
PPL_UNPLAND_EFFORT3,
EQPMT_ETC_EFFORT3,
SUB_PRJ_ETC_COST3,
SUB_PRJ_PPL_ETC_COST3,
SUB_PRJ_EQPMT_ETC_COST3,
SUB_PRJ_PPL_ETC_EFFORT3,
SUB_PRJ_EQPMT_ETC_EFFORT3,
EARNED_VALUE3,
BAC_VALUE3,
PERC_COMP_DERIVATIVE_CODE3,
START_DATE4,
START_DATE_OVERRIDE4,
FINISH_DATE4,
DURATION4,
TASK_STATUS4,
PROGRESS_STATUS_WEIGHT4,
PROGRESS_OVERRIDE4,
REMAINING_EFFORT4,
PERCENT_COMPLETE4,
PERCENT_OVERRIDE4,
TASK_WEIGHT4,
NUMBER_FIELD4,
ROLLUP_NODE4,
DIRTY_FLAG4,
ETC_Cost4,
PPL_ETC_COST4,
EQPMT_ETC_COST4,
PPL_UNPLAND_EFFORT4,
EQPMT_ETC_EFFORT4,
SUB_PRJ_ETC_COST4,
SUB_PRJ_PPL_ETC_COST4,
SUB_PRJ_EQPMT_ETC_COST4,
SUB_PRJ_PPL_ETC_EFFORT4,
SUB_PRJ_EQPMT_ETC_EFFORT4,
EARNED_VALUE4,
BAC_VALUE4,
PERC_COMP_DERIVATIVE_CODE4,
START_DATE5,
START_DATE_OVERRIDE5,
FINISH_DATE5,
DURATION5,
TASK_STATUS5,
PROGRESS_STATUS_WEIGHT5,
PROGRESS_OVERRIDE5,
REMAINING_EFFORT5,
PERCENT_COMPLETE5,
PERCENT_OVERRIDE5,
TASK_WEIGHT5,
NUMBER_FIELD5,
ROLLUP_NODE5,
DIRTY_FLAG5,
ETC_Cost5,
PPL_ETC_COST5,
EQPMT_ETC_COST5,
PPL_UNPLAND_EFFORT5,
EQPMT_ETC_EFFORT5,
SUB_PRJ_ETC_COST5,
SUB_PRJ_PPL_ETC_COST5,
SUB_PRJ_EQPMT_ETC_COST5,
SUB_PRJ_PPL_ETC_EFFORT5,
SUB_PRJ_EQPMT_ETC_EFFORT5,
EARNED_VALUE5,
BAC_VALUE5,
PERC_COMP_DERIVATIVE_CODE5,
START_DATE6,
START_DATE_OVERRIDE6,
FINISH_DATE6,
DURATION6,
TASK_STATUS6,
PROGRESS_STATUS_WEIGHT6,
PROGRESS_OVERRIDE6,
REMAINING_EFFORT6,
PERCENT_COMPLETE6,
PERCENT_OVERRIDE6,
TASK_WEIGHT6,
NUMBER_FIELD6,
ROLLUP_NODE6,
DIRTY_FLAG6,
ETC_Cost6,
PPL_ETC_COST6,
EQPMT_ETC_COST6,
PPL_UNPLAND_EFFORT6,
EQPMT_ETC_EFFORT6,
SUB_PRJ_ETC_COST6,
SUB_PRJ_PPL_ETC_COST6,
SUB_PRJ_EQPMT_ETC_COST6,
SUB_PRJ_PPL_ETC_EFFORT6,
SUB_PRJ_EQPMT_ETC_EFFORT6,
EARNED_VALUE6,
BAC_VALUE6,
PERC_COMP_DERIVATIVE_CODE6,
SUMMARY_OBJECT_FLAG -- 4370746
) VALUES (
l_process_number,
l_OBJECT_TYPE(i),
l_OBJECT_ID(i),
l_PARENT_OBJECT_TYPE(i),
l_PARENT_OBJECT_ID(i),
l_CALENDAR_ID(i),
l_CONSTRAINT_TYPE_CODE(i),
l_CONSTRAINT_DATE(i),
l_WBS_LEVEL(i),
l_START_DATE1(i),
l_START_DATE_OVERRIDE1(i),
l_FINISH_DATE1(i),
l_DURATION1(i),
l_TASK_STATUS1(i),
l_PROGRESS_STATUS_WEIGHT1(i),
l_PROGRESS_OVERRIDE1(i),
l_REMAINING_EFFORT1(i),
l_PERCENT_COMPLETE1(i),
l_PERCENT_OVERRIDE1(i),
l_TASK_WEIGHT1(i),
l_NUMBER_FIELD1(i),
l_ROLLUP_NODE1(i),
l_DIRTY_FLAG1(i),
l_Etc_Cost1(i),
l_PPL_ETC_Cost1(i),
l_EQPMT_ETC_COST1(i),
l_PPL_UNPLAND_EFFORT1(i),
l_EQPMT_ETC_EFFORT1(i),
l_SUB_PRJ_ETC_COST1(i),
l_SUB_PRJ_PPL_ETC_COST1(i),
l_SUB_PRJ_EQPMT_ETC_COST1(i),
l_SUB_PRJ_PPL_ETC_EFFORT1(i),
l_SUB_PRJ_EQPMT_ETC_EFFORT1(i),
l_EARNED_VALUE1(i),
l_BAC_VALUE1(i),
l_Perc_Comp_Deriv_Code1(i),
l_START_DATE2(i),
l_START_DATE_OVERRIDE2(i),
l_FINISH_DATE2(i),
l_DURATION2(i),
l_TASK_STATUS2(i),
l_PROGRESS_STATUS_WEIGHT2(i),
l_PROGRESS_OVERRIDE2(i),
l_REMAINING_EFFORT2(i),
l_PERCENT_COMPLETE2(i),
l_PERCENT_OVERRIDE2(i),
l_TASK_WEIGHT2(i),
l_NUMBER_FIELD2(i),
l_ROLLUP_NODE2(i),
l_DIRTY_FLAG2(i),
l_Etc_Cost2(i),
l_PPL_ETC_Cost2(i),
l_EQPMT_ETC_COST2(i),
l_PPL_UNPLAND_EFFORT2(i),
l_EQPMT_ETC_EFFORT2(i),
l_SUB_PRJ_ETC_COST2(i),
l_SUB_PRJ_PPL_ETC_COST2(i),
l_SUB_PRJ_EQPMT_ETC_COST2(i),
l_SUB_PRJ_PPL_ETC_EFFORT2(i),
l_SUB_PRJ_EQPMT_ETC_EFFORT2(i),
l_EARNED_VALUE2(i),
l_BAC_VALUE2(i),
l_Perc_Comp_Deriv_Code2(i),
l_START_DATE3(i),
l_START_DATE_OVERRIDE3(i),
l_FINISH_DATE3(i),
l_DURATION3(i),
l_TASK_STATUS3(i),
l_PROGRESS_STATUS_WEIGHT3(i),
l_PROGRESS_OVERRIDE3(i),
l_REMAINING_EFFORT3(i),
l_PERCENT_COMPLETE3(i),
l_PERCENT_OVERRIDE3(i),
l_TASK_WEIGHT3(i),
l_NUMBER_FIELD3(i),
l_ROLLUP_NODE3(i),
l_DIRTY_FLAG3(i),
l_Etc_Cost3(i),
l_PPL_ETC_Cost3(i),
l_EQPMT_ETC_COST3(i),
l_PPL_UNPLAND_EFFORT3(i),
l_EQPMT_ETC_EFFORT3(i),
l_SUB_PRJ_ETC_COST3(i),
l_SUB_PRJ_PPL_ETC_COST3(i),
l_SUB_PRJ_EQPMT_ETC_COST3(i),
l_SUB_PRJ_PPL_ETC_EFFORT3(i),
l_SUB_PRJ_EQPMT_ETC_EFFORT3(i),
l_EARNED_VALUE3(i),
l_BAC_VALUE3(i),
l_Perc_Comp_Deriv_Code3(i),
l_START_DATE4(i),
l_START_DATE_OVERRIDE4(i),
l_FINISH_DATE4(i),
l_DURATION4(i),
l_TASK_STATUS4(i),
l_PROGRESS_STATUS_WEIGHT4(i),
l_PROGRESS_OVERRIDE4(i),
l_REMAINING_EFFORT4(i),
l_PERCENT_COMPLETE4(i),
l_PERCENT_OVERRIDE4(i),
l_TASK_WEIGHT4(i),
l_NUMBER_FIELD4(i),
l_ROLLUP_NODE4(i),
l_DIRTY_FLAG4(i),
l_Etc_Cost4(i),
l_PPL_ETC_Cost4(i),
l_EQPMT_ETC_COST4(i),
l_PPL_UNPLAND_EFFORT4(i),
l_EQPMT_ETC_EFFORT4(i),
l_SUB_PRJ_ETC_COST4(i),
l_SUB_PRJ_PPL_ETC_COST4(i),
l_SUB_PRJ_EQPMT_ETC_COST4(i),
l_SUB_PRJ_PPL_ETC_EFFORT4(i),
l_SUB_PRJ_EQPMT_ETC_EFFORT4(i),
l_EARNED_VALUE4(i),
l_BAC_VALUE4(i),
l_Perc_Comp_Deriv_Code4(i),
l_START_DATE5(i),
l_START_DATE_OVERRIDE5(i),
l_FINISH_DATE5(i),
l_DURATION5(i),
l_TASK_STATUS5(i),
l_PROGRESS_STATUS_WEIGHT5(i),
l_PROGRESS_OVERRIDE5(i),
l_REMAINING_EFFORT5(i),
l_PERCENT_COMPLETE5(i),
l_PERCENT_OVERRIDE5(i),
l_TASK_WEIGHT5(i),
l_NUMBER_FIELD5(i),
l_ROLLUP_NODE5(i),
l_DIRTY_FLAG5(i),
l_Etc_Cost5(i),
l_PPL_ETC_Cost5(i),
l_EQPMT_ETC_COST5(i),
l_PPL_UNPLAND_EFFORT5(i),
l_EQPMT_ETC_EFFORT5(i),
l_SUB_PRJ_ETC_COST5(i),
l_SUB_PRJ_PPL_ETC_COST5(i),
l_SUB_PRJ_EQPMT_ETC_COST5(i),
l_SUB_PRJ_PPL_ETC_EFFORT5(i),
l_SUB_PRJ_EQPMT_ETC_EFFORT5(i),
l_EARNED_VALUE5(i),
l_BAC_VALUE5(i),
l_Perc_Comp_Deriv_Code5(i),
l_START_DATE6(i),
l_START_DATE_OVERRIDE6(i),
l_FINISH_DATE6(i),
l_DURATION6(i),
l_TASK_STATUS6(i),
l_PROGRESS_STATUS_WEIGHT6(i),
l_PROGRESS_OVERRIDE6(i),
l_REMAINING_EFFORT6(i),
l_PERCENT_COMPLETE6(i),
l_PERCENT_OVERRIDE6(i),
l_TASK_WEIGHT6(i),
l_NUMBER_FIELD6(i),
l_ROLLUP_NODE6(i),
l_DIRTY_FLAG6(i),
l_Etc_Cost6(i),
l_PPL_ETC_Cost6(i),
l_EQPMT_ETC_COST6(i),
l_PPL_UNPLAND_EFFORT6(i),
l_EQPMT_ETC_EFFORT6(i),
l_SUB_PRJ_ETC_COST6(i),
l_SUB_PRJ_PPL_ETC_COST6(i),
l_SUB_PRJ_EQPMT_ETC_COST6(i),
l_SUB_PRJ_PPL_ETC_EFFORT6(i),
l_SUB_PRJ_EQPMT_ETC_EFFORT6(i),
l_EARNED_VALUE6(i),
l_BAC_VALUE6(i),
l_Perc_Comp_Deriv_Code6(i),
l_SUMMARY_OBJECT_FLAG(i) -- 4370746
);
pa_debug.write(x_Module=>'PA_SCHEDULE_OBJECTS_PVT.GENERATE_SCHEDULE', x_Msg => 'After insertion into table', x_Log_Level=> 3);
l_temp_object_ids_tab.delete;
l_temp_object_types_tab.delete;
l_temp_dirty_flag_tab1.delete;
l_temp_dirty_flag_tab2.delete;
l_temp_dirty_flag_tab3.delete;
l_temp_dirty_flag_tab4.delete;
l_temp_dirty_flag_tab5.delete;
l_temp_dirty_flag_tab6.delete;
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_dirty_flag_tab1.delete;
l_dirty_flag_tab2.delete;
l_dirty_flag_tab3.delete;
l_dirty_flag_tab4.delete;
l_dirty_flag_tab5.delete;
l_dirty_flag_tab6.delete;
l_int_object_ids_tab.delete;
l_int_object_types_tab.delete;
l_int_dirty_flag_tab1.delete;
l_int_dirty_flag_tab2.delete;
l_int_dirty_flag_tab3.delete;
l_int_dirty_flag_tab4.delete;
l_int_dirty_flag_tab5.delete;
l_int_dirty_flag_tab6.delete;
l_int_ref_dirty_flag_tab1.delete;
l_int_ref_dirty_flag_tab2.delete;
l_int_ref_dirty_flag_tab3.delete;
l_int_ref_dirty_flag_tab4.delete;
l_int_ref_dirty_flag_tab5.delete;
l_int_ref_dirty_flag_tab6.delete;
UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.DIRTY_FLAG1=l_dirty_flag_tab1(i)
, T1.DIRTY_FLAG2=l_dirty_flag_tab2(i)
, T1.DIRTY_FLAG3=l_dirty_flag_tab3(i)
, T1.DIRTY_FLAG4=l_dirty_flag_tab4(i)
, T1.DIRTY_FLAG5=l_dirty_flag_tab5(i)
, T1.DIRTY_FLAG6=l_dirty_flag_tab6(i)
WHERE T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
AND process_number = l_Process_Number
;
l_temp_object_ids_tab.delete;
l_temp_object_types_tab.delete;
l_temp_dirty_flag_tab1.delete;
l_temp_dirty_flag_tab2.delete;
l_temp_dirty_flag_tab3.delete;
l_temp_dirty_flag_tab4.delete;
l_temp_dirty_flag_tab5.delete;
l_temp_dirty_flag_tab6.delete;
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_dirty_flag_tab1.delete;
l_dirty_flag_tab2.delete;
l_dirty_flag_tab3.delete;
l_dirty_flag_tab4.delete;
l_dirty_flag_tab5.delete;
l_dirty_flag_tab6.delete;
l_int_object_ids_tab.delete;
l_int_object_types_tab.delete;
l_int_dirty_flag_tab1.delete;
l_int_dirty_flag_tab2.delete;
l_int_dirty_flag_tab3.delete;
l_int_dirty_flag_tab4.delete;
l_int_dirty_flag_tab5.delete;
l_int_dirty_flag_tab6.delete;
l_int_ref_dirty_flag_tab1.delete;
l_int_ref_dirty_flag_tab2.delete;
l_int_ref_dirty_flag_tab3.delete;
l_int_ref_dirty_flag_tab4.delete;
l_int_ref_dirty_flag_tab5.delete;
l_int_ref_dirty_flag_tab6.delete;
UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.DIRTY_FLAG1=l_dirty_flag_tab1(i)
, T1.DIRTY_FLAG2=l_dirty_flag_tab2(i)
, T1.DIRTY_FLAG3=l_dirty_flag_tab3(i)
, T1.DIRTY_FLAG4=l_dirty_flag_tab4(i)
, T1.DIRTY_FLAG5=l_dirty_flag_tab5(i)
, T1.DIRTY_FLAG6=l_dirty_flag_tab6(i)
WHERE T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
AND process_number = l_Process_Number
;
, l_update_date_flag_tab1
, l_update_date_flag_tab2
, l_update_date_flag_tab3
, l_update_date_flag_tab4
, l_update_date_flag_tab5
, l_update_date_flag_tab6
, l_update_requ_flag_tab1
, l_update_requ_flag_tab2
, l_update_requ_flag_tab3
, l_update_requ_flag_tab4
, l_update_requ_flag_tab5
, l_update_requ_flag_tab6
;
l_parent_update_required := 'N';
SELECT
MIN(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', start_date1, null), start_date1))
, MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', finish_date1, null), finish_date1))
, MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', DECODE(finish_date1,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', start_date2, null), start_date2))
, MAX(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', finish_date2, null), finish_date2))
, MAX(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', DECODE(finish_date2,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', start_date3, null), start_date3))
, MAX(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', finish_date3, null), finish_date3))
, MAX(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', DECODE(finish_date3,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', start_date4, null), start_date4))
, MAX(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', finish_date4, null), finish_date4))
, MAX(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', DECODE(finish_date4,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', start_date5, null), start_date5))
, MAX(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', finish_date5, null), finish_date5))
, MAX(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', DECODE(finish_date5,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', start_date6, null), start_date6))
, MAX(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', finish_date6, null), finish_date6))
, MAX(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', DECODE(finish_date6,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', 1, 0), 1))
, MAX(DECODE(OBJECT_TYPE,'PA_TASKS',1,0))
INTO
l_new_start_date1
, l_new_completion_date1
, l_null_flag1
, l_task_count1
, l_new_start_date2
, l_new_completion_date2
, l_null_flag2
, l_task_count2
, l_new_start_date3
, l_new_completion_date3
, l_null_flag3
, l_task_count3
, l_new_start_date4
, l_new_completion_date4
, l_null_flag4
, l_task_count4
, l_new_start_date5
, l_new_completion_date5
, l_null_flag5
, l_task_count5
, l_new_start_date6
, l_new_completion_date6
, l_null_flag6
, l_task_count6
, l_task_flag
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND parent_object_id = l_parent_object_ids_tab(j)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
SELECT
MIN(start_date1)
, MAX(finish_date1)
, MAX(DECODE(finish_date1,NULL,1,0))
, MAX(1)
, MIN(start_date2)
, MAX(finish_date2)
, MAX(DECODE(finish_date2,NULL,1,0))
, MAX(1)
, MIN(start_date3)
, MAX(finish_date3)
, MAX(DECODE(finish_date3,NULL,1,0))
, MAX(1)
, MIN(start_date4)
, MAX(finish_date4)
, MAX(DECODE(finish_date4,NULL,1,0))
, MAX(1)
, MIN(start_date5)
, MAX(finish_date5)
, MAX(DECODE(finish_date5,NULL,1,0))
, MAX(1)
, MIN(start_date6)
, MAX(finish_date6)
, MAX(DECODE(finish_date6,NULL,1,0))
, MAX(1)
, MAX(DECODE(OBJECT_TYPE,'PA_TASKS',1,0))
INTO
l_new_start_date1
, l_new_completion_date1
, l_null_flag1
, l_task_count1
, l_new_start_date2
, l_new_completion_date2
, l_null_flag2
, l_task_count2
, l_new_start_date3
, l_new_completion_date3
, l_null_flag3
, l_task_count3
, l_new_start_date4
, l_new_completion_date4
, l_null_flag4
, l_task_count4
, l_new_start_date5
, l_new_completion_date5
, l_null_flag5
, l_task_count5
, l_new_start_date6
, l_new_completion_date6
, l_null_flag6
, l_task_count6
, l_task_flag
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND parent_object_id = l_parent_object_ids_tab(j)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
l_update_requ_flag_tab1(j) := 'N';
l_update_requ_flag_tab1(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab1(j) := 'Y';
l_update_date_flag_tab1(j) := 'N';
l_update_date_flag_tab1(j) := 'Y';
l_update_date_flag_tab1(j) := 'N';
l_update_date_flag_tab1(j) := 'Y';
l_update_date_flag_tab1(j) := 'Y';
l_update_date_flag_tab1(j) := 'Y';
l_update_requ_flag_tab1(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab1(j) := 'Y';
l_update_date_flag_tab1(j) :='Y';
l_update_date_flag_tab1(j) :='N';
l_update_date_flag_tab1(j) :='Y';
l_update_requ_flag_tab2(j) := 'N';
l_update_requ_flag_tab2(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab2(j) := 'Y';
l_update_date_flag_tab2(j) := 'N';
l_update_date_flag_tab2(j) := 'Y';
l_update_date_flag_tab2(j) := 'N';
l_update_date_flag_tab2(j) := 'Y';
l_update_date_flag_tab2(j) := 'Y';
l_update_date_flag_tab2(j) := 'Y';
l_update_requ_flag_tab2(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab2(j) := 'Y';
l_update_date_flag_tab2(j) :='Y';
l_update_date_flag_tab2(j) :='N';
l_update_date_flag_tab2(j) :='Y';
l_update_requ_flag_tab3(j) := 'N';
l_update_requ_flag_tab3(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab3(j) := 'Y';
l_update_date_flag_tab3(j) := 'N';
l_update_date_flag_tab3(j) := 'Y';
l_update_date_flag_tab3(j) := 'N';
l_update_date_flag_tab3(j) := 'Y';
l_update_date_flag_tab3(j) := 'Y';
l_update_date_flag_tab3(j) := 'Y';
l_update_requ_flag_tab3(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab3(j) := 'Y';
l_update_date_flag_tab3(j) :='Y';
l_update_date_flag_tab3(j) :='N';
l_update_date_flag_tab3(j) :='Y';
l_update_requ_flag_tab4(j) := 'N';
l_update_requ_flag_tab4(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab4(j) := 'Y';
l_update_date_flag_tab4(j) := 'N';
l_update_date_flag_tab4(j) := 'Y';
l_update_date_flag_tab4(j) := 'N';
l_update_date_flag_tab4(j) := 'Y';
l_update_date_flag_tab4(j) := 'Y';
l_update_date_flag_tab4(j) := 'Y';
l_update_requ_flag_tab4(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab4(j) := 'Y';
l_update_date_flag_tab4(j) :='Y';
l_update_date_flag_tab4(j) :='N';
l_update_date_flag_tab4(j) :='Y';
l_update_requ_flag_tab5(j) := 'N';
l_update_requ_flag_tab5(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab5(j) := 'Y';
l_update_date_flag_tab5(j) := 'N';
l_update_date_flag_tab5(j) := 'Y';
l_update_date_flag_tab5(j) := 'N';
l_update_date_flag_tab5(j) := 'Y';
l_update_date_flag_tab5(j) := 'Y';
l_update_date_flag_tab5(j) := 'Y';
l_update_requ_flag_tab5(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab5(j) := 'Y';
l_update_date_flag_tab5(j) :='Y';
l_update_date_flag_tab5(j) :='N';
l_update_date_flag_tab5(j) :='Y';
l_update_requ_flag_tab6(j) := 'N';
l_update_requ_flag_tab6(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab6(j) := 'Y';
l_update_date_flag_tab6(j) := 'N';
l_update_date_flag_tab6(j) := 'Y';
l_update_date_flag_tab6(j) := 'N';
l_update_date_flag_tab6(j) := 'Y';
l_update_date_flag_tab6(j) := 'Y';
l_update_date_flag_tab6(j) := 'Y';
l_update_requ_flag_tab6(j) := 'N';
l_parent_update_required := 'Y';
l_update_requ_flag_tab6(j) := 'Y';
l_update_date_flag_tab6(j) :='Y';
l_update_date_flag_tab6(j) :='N';
l_update_date_flag_tab6(j) :='Y';
IF (l_update_requ_flag_tab1(j) = 'Y') THEN
IF (l_update_date_flag_tab1(j) = 'Y') THEN
l_parent_start_date1_tmp := l_parent_start_date_tab1(j);
IF (l_update_requ_flag_tab2(j) = 'Y') THEN
IF (l_update_date_flag_tab2(j) = 'Y') THEN
l_parent_start_date2_tmp := l_parent_start_date_tab2(j);
IF (l_update_requ_flag_tab3(j) = 'Y') THEN
IF (l_update_date_flag_tab3(j) = 'Y') THEN
l_parent_start_date3_tmp := l_parent_start_date_tab3(j);
IF (l_update_requ_flag_tab4(j) = 'Y') THEN
IF (l_update_date_flag_tab4(j) = 'Y') THEN
l_parent_start_date4_tmp := l_parent_start_date_tab4(j);
IF (l_update_requ_flag_tab5(j) = 'Y') THEN
IF (l_update_date_flag_tab5(j) = 'Y') THEN
l_parent_start_date5_tmp := l_parent_start_date_tab5(j);
IF (l_update_requ_flag_tab6(j) = 'Y') THEN
IF (l_update_date_flag_tab6(j) = 'Y') THEN
l_parent_start_date6_tmp := l_parent_start_date_tab6(j);
IF l_parent_update_required = 'Y' THEN
/* Commented the following update statement and introduced a new one for bug#6185523
UPDATE + INDEX( SchTmp PA_PROJ_ROLLUP_BULK_TEMP_U1) PA_PROJ_ROLLUP_BULK_TEMP SchTmp SET
start_date1 = decode(l_update_requ_flag_tab1(j), 'Y', decode(l_update_date_flag_tab1(j), 'Y', l_parent_start_date_tab1(j), start_date1), start_date1)
, finish_date1 = decode(l_update_requ_flag_tab1(j), 'Y', decode(l_update_date_flag_tab1(j), 'Y', l_parent_finish_date_tab1(j),finish_date1),finish_date1)
, duration1 = decode(l_update_requ_flag_tab1(j), 'Y', decode(l_update_date_flag_tab1(j), 'Y', l_parent_duration_tab1(j), duration1), duration1)
, start_date2 = decode(l_update_requ_flag_tab2(j), 'Y', decode(l_update_date_flag_tab2(j), 'Y', l_parent_start_date_tab2(j), start_date2), start_date2)
, finish_date2 = decode(l_update_requ_flag_tab2(j), 'Y', decode(l_update_date_flag_tab2(j), 'Y', l_parent_finish_date_tab2(j), finish_date2), finish_date2)
, duration2 = decode(l_update_requ_flag_tab2(j), 'Y', decode(l_update_date_flag_tab2(j), 'Y', l_parent_duration_tab2(j), duration2), duration2)
, start_date3 = decode(l_update_requ_flag_tab3(j), 'Y', decode(l_update_date_flag_tab3(j), 'Y', l_parent_start_date_tab3(j), start_date3), start_date3)
, finish_date3 = decode(l_update_requ_flag_tab3(j), 'Y', decode(l_update_date_flag_tab3(j), 'Y', l_parent_finish_date_tab3(j), finish_date3), finish_date3)
, duration3 = decode(l_update_requ_flag_tab3(j), 'Y', decode(l_update_date_flag_tab3(j), 'Y', l_parent_duration_tab3(j), duration3), duration3)
, start_date4 = decode(l_update_requ_flag_tab4(j), 'Y', decode(l_update_date_flag_tab4(j), 'Y', l_parent_start_date_tab4(j), start_date4), start_date4)
, finish_date4 = decode(l_update_requ_flag_tab4(j), 'Y', decode(l_update_date_flag_tab4(j), 'Y', l_parent_finish_date_tab4(j), finish_date4), finish_date4)
, duration4 = decode(l_update_requ_flag_tab4(j), 'Y', decode(l_update_date_flag_tab4(j), 'Y', l_parent_duration_tab4(j), duration4), duration4)
, start_date5 = decode(l_update_requ_flag_tab5(j), 'Y', decode(l_update_date_flag_tab5(j), 'Y', l_parent_start_date_tab5(j), start_date5), start_date5)
, finish_date5 = decode(l_update_requ_flag_tab5(j), 'Y', decode(l_update_date_flag_tab5(j), 'Y', l_parent_finish_date_tab5(j), finish_date5), finish_date5)
, duration5 = decode(l_update_requ_flag_tab5(j), 'Y', decode(l_update_date_flag_tab5(j), 'Y', l_parent_duration_tab5(j), duration5), duration5)
, start_date6 = decode(l_update_requ_flag_tab6(j), 'Y', decode(l_update_date_flag_tab6(j), 'Y', l_parent_start_date_tab6(j), start_date6), start_date6)
, finish_date6 = decode(l_update_requ_flag_tab6(j), 'Y', decode(l_update_date_flag_tab6(j), 'Y', l_parent_finish_date_tab6(j), finish_date6), finish_date6)
, duration6 = decode(l_update_requ_flag_tab6(j), 'Y', decode(l_update_date_flag_tab6(j), 'Y', l_parent_duration_tab6(j), duration6), duration6)
, dirty_flag1 = decode(l_update_requ_flag_tab1(j), 'Y', 'N',dirty_flag1)
, dirty_flag2 = decode(l_update_requ_flag_tab2(j), 'Y', 'N',dirty_flag2)
, dirty_flag3 = decode(l_update_requ_flag_tab3(j), 'Y', 'N',dirty_flag3)
, dirty_flag4 = decode(l_update_requ_flag_tab4(j), 'Y', 'N',dirty_flag4)
, dirty_flag5 = decode(l_update_requ_flag_tab5(j), 'Y', 'N',dirty_flag5)
, dirty_flag6 = decode(l_update_requ_flag_tab6(j), 'Y', 'N',dirty_flag6)
WHERE object_id = l_parent_object_ids_tab(j)
AND object_type = l_parent_object_types_tab(j)
and process_number = l_process_number;
Ends commented code for bug#6185523 And added the below update*/
UPDATE /*+ INDEX( SchTmp PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP SchTmp SET
start_date1 = nvl(l_parent_start_date1_tmp, start_date1)
, finish_date1 = nvl(l_parent_finish_date1_tmp, finish_date1)
, duration1 = nvl(l_parent_duration1_tmp, duration1)
, start_date2 = nvl(l_parent_start_date2_tmp, start_date2)
, finish_date2 = nvl(l_parent_finish_date2_tmp, finish_date2)
, duration2 = nvl(l_parent_duration2_tmp, duration2)
, start_date3 = nvl(l_parent_start_date3_tmp, start_date3)
, finish_date3 = nvl(l_parent_finish_date3_tmp, finish_date3)
, duration3 = nvl(l_parent_duration3_tmp, duration3)
, start_date4 = nvl(l_parent_start_date4_tmp, start_date4)
, finish_date4 = nvl(l_parent_finish_date4_tmp, finish_date4)
, duration4 = nvl(l_parent_duration4_tmp, duration4)
, start_date5 = nvl(l_parent_start_date5_tmp, start_date5)
, finish_date5 = nvl(l_parent_finish_date5_tmp, finish_date5)
, duration5 = nvl(l_parent_duration5_tmp, duration5)
, start_date6 = nvl(l_parent_start_date6_tmp, start_date6)
, finish_date6 = nvl(l_parent_finish_date6_tmp, finish_date6)
, duration6 = nvl(l_parent_duration6_tmp, duration6)
, dirty_flag1 = nvl(dirty_flag1_tmp, dirty_flag1)
, dirty_flag2 = nvl(dirty_flag2_tmp, dirty_flag2)
, dirty_flag3 = nvl(dirty_flag3_tmp, dirty_flag3)
, dirty_flag4 = nvl(dirty_flag4_tmp, dirty_flag4)
, dirty_flag5 = nvl(dirty_flag5_tmp, dirty_flag5)
, dirty_flag6 = nvl(dirty_flag6_tmp, dirty_flag6)
WHERE object_id = l_parent_object_ids_tab(j)
AND object_type = l_parent_object_types_tab(j)
and process_number = l_process_number;
l_child_update_required := 'N';
l_child_update_required := 'N';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
l_child_update_required := 'Y';
IF l_child_update_required = 'Y' THEN
FORALL i in 1..l_child_object_ids_tab.count
UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.start_date1=decode(l_child_upd_req_flag_tab1(i), 'Y', l_child_start_date_tab1(i), T1.start_date1)
, T1.start_date2=decode(l_child_upd_req_flag_tab2(i), 'Y', l_child_start_date_tab2(i), T1.start_date2)
, T1.start_date3=decode(l_child_upd_req_flag_tab3(i), 'Y', l_child_start_date_tab3(i), T1.start_date3)
, T1.start_date4=decode(l_child_upd_req_flag_tab4(i), 'Y', l_child_start_date_tab4(i), T1.start_date4)
, T1.start_date5=decode(l_child_upd_req_flag_tab5(i), 'Y', l_child_start_date_tab5(i), T1.start_date5)
, T1.start_date6=decode(l_child_upd_req_flag_tab6(i), 'Y', l_child_start_date_tab6(i), T1.start_date6)
, T1.finish_date1=decode(l_child_upd_req_flag_tab1(i), 'Y', l_child_finish_date_tab1(i), T1.finish_date1)
, T1.finish_date2=decode(l_child_upd_req_flag_tab2(i), 'Y', l_child_finish_date_tab2(i), T1.finish_date2)
, T1.finish_date3=decode(l_child_upd_req_flag_tab3(i), 'Y', l_child_finish_date_tab3(i), T1.finish_date3)
, T1.finish_date4=decode(l_child_upd_req_flag_tab4(i), 'Y', l_child_finish_date_tab4(i), T1.finish_date4)
, T1.finish_date5=decode(l_child_upd_req_flag_tab5(i), 'Y', l_child_finish_date_tab5(i), T1.finish_date5)
, T1.finish_date6=decode(l_child_upd_req_flag_tab6(i), 'Y', l_child_finish_date_tab6(i), T1.finish_date6)
, T1.duration1=decode(l_child_upd_req_flag_tab1(i), 'Y', l_child_duration_tab1(i), T1.duration1)
, T1.duration2=decode(l_child_upd_req_flag_tab2(i), 'Y', l_child_duration_tab2(i), T1.duration2)
, T1.duration3=decode(l_child_upd_req_flag_tab3(i), 'Y', l_child_duration_tab3(i), T1.duration3)
, T1.duration4=decode(l_child_upd_req_flag_tab4(i), 'Y', l_child_duration_tab4(i), T1.duration4)
, T1.duration5=decode(l_child_upd_req_flag_tab5(i), 'Y', l_child_duration_tab5(i), T1.duration5)
, T1.duration6=decode(l_child_upd_req_flag_tab6(i), 'Y', l_child_duration_tab6(i), T1.duration6)
, T1.DIRTY_FLAG1=decode(l_child_upd_req_flag_tab1(i), 'Y', 'N', T1.DIRTY_FLAG1)
, T1.DIRTY_FLAG2=decode(l_child_upd_req_flag_tab2(i), 'Y', 'N', T1.DIRTY_FLAG2)
, T1.DIRTY_FLAG3=decode(l_child_upd_req_flag_tab3(i), 'Y', 'N', T1.DIRTY_FLAG3)
, T1.DIRTY_FLAG4=decode(l_child_upd_req_flag_tab4(i), 'Y', 'N', T1.DIRTY_FLAG4)
, T1.DIRTY_FLAG5=decode(l_child_upd_req_flag_tab5(i), 'Y', 'N', T1.DIRTY_FLAG5)
, T1.DIRTY_FLAG6=decode(l_child_upd_req_flag_tab6(i), 'Y', 'N', T1.DIRTY_FLAG6)
WHERE T1.object_id = l_child_object_ids_tab(i)
AND T1.object_type = l_child_object_types_tab(i)
AND process_number = l_Process_Number;
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_parent_object_ids_tab.delete;
l_parent_object_types_tab.delete;
l_parent_start_date_tab1.delete;
l_parent_start_date_tab2.delete;
l_parent_start_date_tab3.delete;
l_parent_start_date_tab4.delete;
l_parent_start_date_tab5.delete;
l_parent_start_date_tab6.delete;
l_parent_finish_date_tab1.delete;
l_parent_finish_date_tab2.delete;
l_parent_finish_date_tab3.delete;
l_parent_finish_date_tab4.delete;
l_parent_finish_date_tab5.delete;
l_parent_finish_date_tab6.delete;
l_parent_duration_tab1.delete;
l_parent_duration_tab2.delete;
l_parent_duration_tab3.delete;
l_parent_duration_tab4.delete;
l_parent_duration_tab5.delete;
l_parent_duration_tab6.delete;
l_parent_dirty_flag_tab1.delete;
l_parent_dirty_flag_tab2.delete;
l_parent_dirty_flag_tab3.delete;
l_parent_dirty_flag_tab4.delete;
l_parent_dirty_flag_tab5.delete;
l_parent_dirty_flag_tab6.delete;
l_parent_start_date_or_tab1.delete;
l_parent_start_date_or_tab2.delete;
l_parent_start_date_or_tab3.delete;
l_parent_start_date_or_tab4.delete;
l_parent_start_date_or_tab5.delete;
l_parent_start_date_or_tab6.delete;
-- We have two approached to update dates at parent level
-- 1. Do FORALL Bulk Update. But this will require to do Connect BY at each node to drill down all its child, grand child ...
-- Currently it is just looking its childs and not grand ones
-- 2. Update at each node level in database and do not do bulk update.
-- Currently implementing this approach as it seems more performant.
For j IN 1..l_parent_object_ids_tab.count LOOP
BEGIN
l_count1 := 1;
SELECT MAX(decode(p_partial_dates_flag1, 'Y',decode(rollup_node1, 'Y', decode(dirty_flag1,'Y',1,0), 0),decode(dirty_flag1,'Y',1,0)))
, MAX(decode(p_partial_dates_flag2, 'Y',decode(rollup_node2, 'Y', decode(dirty_flag2,'Y',1,0), 0),decode(dirty_flag2,'Y',1,0)))
, MAX(decode(p_partial_dates_flag3, 'Y',decode(rollup_node3, 'Y', decode(dirty_flag3,'Y',1,0), 0),decode(dirty_flag3,'Y',1,0)))
, MAX(decode(p_partial_dates_flag4, 'Y',decode(rollup_node4, 'Y', decode(dirty_flag4,'Y',1,0), 0),decode(dirty_flag4,'Y',1,0)))
, MAX(decode(p_partial_dates_flag5, 'Y',decode(rollup_node5, 'Y', decode(dirty_flag5,'Y',1,0), 0),decode(dirty_flag5,'Y',1,0)))
, MAX(decode(p_partial_dates_flag6, 'Y',decode(rollup_node6, 'Y', decode(dirty_flag6,'Y',1,0), 0),decode(dirty_flag6,'Y',1,0)))
INTO l_count1, l_count2, l_count3, l_count4, l_count5, l_count6
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND parent_object_id = l_parent_object_ids_tab(j)
AND (DIRTY_FLAG1 = 'Y' OR DIRTY_FLAG2 = 'Y' OR DIRTY_FLAG3 = 'Y'
OR DIRTY_FLAG4 = 'Y' OR DIRTY_FLAG5 = 'Y' OR DIRTY_FLAG6 = 'Y')
;
-- This is kept as it is so in future if want to move to FOR ALL BULK UPDATE
l_object_ids_tab.extend(1);
l_update_date_flag_tab1.extend(1);
l_update_date_flag_tab2.extend(1);
l_update_date_flag_tab3.extend(1);
l_update_date_flag_tab4.extend(1);
l_update_date_flag_tab5.extend(1);
l_update_date_flag_tab6.extend(1);
l_update_requ_flag_tab1.extend(1);
l_update_requ_flag_tab2.extend(1);
l_update_requ_flag_tab3.extend(1);
l_update_requ_flag_tab4.extend(1);
l_update_requ_flag_tab5.extend(1);
l_update_requ_flag_tab6.extend(1);
l_parent_update_required := 'N';
SELECT
MIN(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', start_date1, null), start_date1))
, MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', finish_date1, null), finish_date1))
, MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', DECODE(finish_date1,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', start_date2, null), null))
, MAX(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', finish_date2, null), null))
, MAX(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', DECODE(finish_date2,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag2, 'Y', decode(rollup_node2, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', start_date3, null), null))
, MAX(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', finish_date3, null), null))
, MAX(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', DECODE(finish_date3,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag3, 'Y', decode(rollup_node3, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', start_date4, null), null))
, MAX(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', finish_date4, null), null))
, MAX(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', DECODE(finish_date4,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag4, 'Y', decode(rollup_node4, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', start_date5, null), null))
, MAX(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', finish_date5, null), null))
, MAX(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', DECODE(finish_date5,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag5, 'Y', decode(rollup_node5, 'Y', 1, 0), 1))
, MIN(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', start_date6, null), null))
, MAX(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', finish_date6, null), null))
, MAX(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', DECODE(finish_date6,NULL,1,0), 0), 0))
, MAX(decode(p_partial_dates_flag6, 'Y', decode(rollup_node6, 'Y', 1, 0), 1))
, MAX(DECODE(OBJECT_TYPE,'PA_TASKS',1,0))
INTO
l_new_start_date1(l_counter)
, l_new_completion_date1(l_counter)
, l_null_flag1
, l_task_count1
, l_new_start_date2(l_counter)
, l_new_completion_date2(l_counter)
, l_null_flag2
, l_task_count2
, l_new_start_date3(l_counter)
, l_new_completion_date3(l_counter)
, l_null_flag3
, l_task_count3
, l_new_start_date4(l_counter)
, l_new_completion_date4(l_counter)
, l_null_flag4
, l_task_count4
, l_new_start_date5(l_counter)
, l_new_completion_date5(l_counter)
, l_null_flag5
, l_task_count5
, l_new_start_date6(l_counter)
, l_new_completion_date6(l_counter)
, l_null_flag6
, l_task_count6
, l_task_flag
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND parent_object_id = l_parent_object_ids_tab(j);
l_update_requ_flag_tab1(l_counter) := 'N';
l_update_date_flag_tab1(l_counter) :='Y';
l_update_date_flag_tab1(l_counter) :='N';
l_update_date_flag_tab1(l_counter) :='Y';
l_update_requ_flag_tab1(l_counter) := 'N';
l_update_requ_flag_tab1(l_counter) := 'Y';
l_parent_update_required := 'Y';
l_update_requ_flag_tab2(l_counter) := 'N';
l_update_date_flag_tab2(l_counter) :='Y';
l_update_date_flag_tab2(l_counter) :='N';
l_update_date_flag_tab2(l_counter) :='Y';
l_update_requ_flag_tab2(l_counter) := 'N';
l_update_requ_flag_tab2(l_counter) := 'Y';
l_parent_update_required := 'Y';
l_update_requ_flag_tab3(l_counter) := 'N';
l_update_date_flag_tab3(l_counter) :='Y';
l_update_date_flag_tab3(l_counter) :='N';
l_update_date_flag_tab3(l_counter) :='Y';
l_update_requ_flag_tab3(l_counter) := 'N';
l_update_requ_flag_tab3(l_counter) := 'Y';
l_parent_update_required := 'Y';
l_update_requ_flag_tab4(l_counter) := 'N';
l_update_date_flag_tab4(l_counter) :='Y';
l_update_date_flag_tab4(l_counter) :='N';
l_update_date_flag_tab4(l_counter) :='Y';
l_update_requ_flag_tab4(l_counter) := 'N';
l_update_requ_flag_tab4(l_counter) := 'Y';
l_parent_update_required := 'Y';
l_update_requ_flag_tab5(l_counter) := 'N';
l_update_date_flag_tab5(l_counter) :='Y';
l_update_date_flag_tab5(l_counter) :='N';
l_update_date_flag_tab5(l_counter) :='Y';
l_update_requ_flag_tab5(l_counter) := 'N';
l_update_requ_flag_tab5(l_counter) := 'Y';
l_parent_update_required := 'Y';
l_update_requ_flag_tab6(l_counter) := 'N';
l_update_date_flag_tab6(l_counter) :='Y';
l_update_date_flag_tab6(l_counter) :='N';
l_update_date_flag_tab6(l_counter) :='Y';
l_update_requ_flag_tab6(l_counter) := 'N';
l_update_requ_flag_tab6(l_counter) := 'Y';
l_parent_update_required := 'Y';
-- We have two approached to update dates at parent level
-- 1. Do FORALL Bulk Update. But this will require to do Connect BY at each node to drill down all its child, grand child ...
-- Currently it is just looking its childs and not grand ones
-- 2. Update at each node level in database and do not do bulk update.
-- Currently implementing this approach as it seems more performant.
IF l_parent_update_required = 'Y' THEN
UPDATE PA_PROJ_ROLLUP_BULK_TEMP SET
start_date1 = decode(l_update_requ_flag_tab1(l_counter), 'Y', decode(l_update_date_flag_tab1(l_counter), 'Y', l_new_start_date1(l_counter), start_date1),start_date1)
, finish_date1 = decode(l_update_requ_flag_tab1(l_counter), 'Y', decode(l_update_date_flag_tab1(l_counter), 'Y', l_new_completion_date1(l_counter),finish_date1),finish_date1)
, duration1 = decode(l_update_requ_flag_tab1(l_counter), 'Y', decode(l_update_date_flag_tab1(l_counter), 'Y', l_new_duration1(l_counter), duration1), duration1)
, start_date2 = decode(l_update_requ_flag_tab2(l_counter), 'Y', decode(l_update_date_flag_tab2(l_counter), 'Y', l_new_start_date2(l_counter), start_date2), start_date2)
, finish_date2 = decode(l_update_requ_flag_tab2(l_counter), 'Y', decode(l_update_date_flag_tab2(l_counter), 'Y', l_new_completion_date2(l_counter), finish_date2), finish_date2)
, duration2 = decode(l_update_requ_flag_tab2(l_counter), 'Y', decode(l_update_date_flag_tab2(l_counter), 'Y', l_new_duration2(l_counter), duration2), duration2)
, start_date3 = decode(l_update_requ_flag_tab3(l_counter), 'Y', decode(l_update_date_flag_tab3(l_counter), 'Y', l_new_start_date3(l_counter), start_date3), start_date3)
, finish_date3 = decode(l_update_requ_flag_tab3(l_counter), 'Y', decode(l_update_date_flag_tab3(l_counter), 'Y', l_new_completion_date3(l_counter), finish_date3), finish_date3)
, duration3 = decode(l_update_requ_flag_tab3(l_counter), 'Y', decode(l_update_date_flag_tab3(l_counter), 'Y', l_new_duration3(l_counter), duration3), duration3)
, start_date4 = decode(l_update_requ_flag_tab4(l_counter), 'Y', decode(l_update_date_flag_tab4(l_counter), 'Y', l_new_start_date4(l_counter), start_date4), start_date4)
, finish_date4 = decode(l_update_requ_flag_tab4(l_counter), 'Y', decode(l_update_date_flag_tab4(l_counter), 'Y', l_new_completion_date4(l_counter), finish_date4), finish_date4)
, duration4 = decode(l_update_requ_flag_tab4(l_counter), 'Y', decode(l_update_date_flag_tab4(l_counter), 'Y', l_new_duration4(l_counter), duration4), duration4)
, start_date5 = decode(l_update_requ_flag_tab5(l_counter), 'Y', decode(l_update_date_flag_tab5(l_counter), 'Y', l_new_start_date5(l_counter), start_date5), start_date5)
, finish_date5 = decode(l_update_requ_flag_tab5(l_counter), 'Y', decode(l_update_date_flag_tab5(l_counter), 'Y', l_new_completion_date5(l_counter), finish_date5), finish_date5)
, duration5 = decode(l_update_requ_flag_tab5(l_counter), 'Y', decode(l_update_date_flag_tab5(l_counter), 'Y', l_new_duration5(l_counter), duration5), duration5)
, start_date6 = decode(l_update_requ_flag_tab6(l_counter), 'Y', decode(l_update_date_flag_tab6(l_counter), 'Y', l_new_start_date6(l_counter), start_date6), start_date6)
, finish_date6 = decode(l_update_requ_flag_tab6(l_counter), 'Y', decode(l_update_date_flag_tab6(l_counter), 'Y', l_new_completion_date6(l_counter), finish_date6), finish_date6)
, duration6 = decode(l_update_requ_flag_tab6(l_counter), 'Y', decode(l_update_date_flag_tab6(l_counter), 'Y', l_new_duration6(l_counter), duration6), duration6)
, dirty_flag1 = decode(l_update_requ_flag_tab1(l_counter), 'Y', 'N', dirty_flag1)
, dirty_flag2 = decode(l_update_requ_flag_tab2(l_counter), 'Y', 'N', dirty_flag2)
, dirty_flag3 = decode(l_update_requ_flag_tab3(l_counter), 'Y', 'N', dirty_flag3)
, dirty_flag4 = decode(l_update_requ_flag_tab4(l_counter), 'Y', 'N', dirty_flag4)
, dirty_flag5 = decode(l_update_requ_flag_tab5(l_counter), 'Y', 'N', dirty_flag5)
, dirty_flag6 = decode(l_update_requ_flag_tab6(l_counter), 'Y', 'N', dirty_flag6)
WHERE object_id = l_object_ids_tab(l_counter)
AND object_type = l_object_types_tab(l_counter)
and process_number = l_process_number;
-- We have two approached to update dates at parent level
-- 1. Do FORALL Bulk Update. But this will require to do Connect BY at each node to drill down all its child, grand child ...
-- Currently it is just looking its childs and not grand ones
-- 2. Update at each node level in database and do not do bulk update.
-- Currently implementing this approach as it seems more performant.
/* Do not remove
IF l_parent_update_required = 'Y' THEN
FORALL k in 1..l_object_ids_tab.count
UPDATE PA_PROJ_ROLLUP_BULK_TEMP SET
start_date1 = decode(l_update_requ_flag_tab1(k), 'Y', decode(l_update_date_flag_tab1(k), 'Y', l_new_start_date1(k), start_date1),start_date1)
, finish_date1 = decode(l_update_requ_flag_tab1(k), 'Y', decode(l_update_date_flag_tab1(k), 'Y', l_new_completion_date1(k),finish_date1),finish_date1)
, duration1 = decode(l_update_requ_flag_tab1(k), 'Y', decode(l_update_date_flag_tab1(k), 'Y', l_new_duration1(k), duration1), duration1)
, start_date2 = decode(l_update_requ_flag_tab2(k), 'Y', decode(l_update_date_flag_tab2(k), 'Y', l_new_start_date2(k), start_date2), start_date2)
, finish_date2 = decode(l_update_requ_flag_tab2(k), 'Y', decode(l_update_date_flag_tab2(k), 'Y', l_new_completion_date2(k), finish_date2), finish_date2)
, duration2 = decode(l_update_requ_flag_tab2(k), 'Y', decode(l_update_date_flag_tab2(k), 'Y', l_new_duration2(k), duration2), duration2)
, start_date3 = decode(l_update_requ_flag_tab3(k), 'Y', decode(l_update_date_flag_tab3(k), 'Y', l_new_start_date3(k), start_date3), start_date3)
, finish_date3 = decode(l_update_requ_flag_tab3(k), 'Y', decode(l_update_date_flag_tab3(k), 'Y', l_new_completion_date3(k), finish_date3), finish_date3)
, duration3 = decode(l_update_requ_flag_tab3(k), 'Y', decode(l_update_date_flag_tab3(k), 'Y', l_new_duration3(k), duration3), duration3)
, start_date4 = decode(l_update_requ_flag_tab4(k), 'Y', decode(l_update_date_flag_tab4(k), 'Y', l_new_start_date4(k), start_date4), start_date4)
, finish_date4 = decode(l_update_requ_flag_tab4(k), 'Y', decode(l_update_date_flag_tab4(k), 'Y', l_new_completion_date4(k), finish_date4), finish_date4)
, duration4 = decode(l_update_requ_flag_tab4(k), 'Y', decode(l_update_date_flag_tab4(k), 'Y', l_new_duration4(k), duration4), duration4)
, start_date5 = decode(l_update_requ_flag_tab5(k), 'Y', decode(l_update_date_flag_tab5(k), 'Y', l_new_start_date5(k), start_date5), start_date5)
, finish_date5 = decode(l_update_requ_flag_tab5(k), 'Y', decode(l_update_date_flag_tab5(k), 'Y', l_new_completion_date5(k), finish_date5), finish_date5)
, duration5 = decode(l_update_requ_flag_tab5(k), 'Y', decode(l_update_date_flag_tab5(k), 'Y', l_new_duration5(k), duration5), duration5)
, start_date6 = decode(l_update_requ_flag_tab6(k), 'Y', decode(l_update_date_flag_tab6(k), 'Y', l_new_start_date6(k), start_date6), start_date6)
, finish_date6 = decode(l_update_requ_flag_tab6(k), 'Y', decode(l_update_date_flag_tab6(k), 'Y', l_new_completion_date6(k), finish_date6), finish_date6)
, duration6 = decode(l_update_requ_flag_tab6(k), 'Y', decode(l_update_date_flag_tab6(k), 'Y', l_new_duration6(k), duration6), duration6)
, dirty_flag1 = decode(l_update_requ_flag_tab1(k), 'Y', 'N', dirty_flag1)
, dirty_flag2 = decode(l_update_requ_flag_tab2(k), 'Y', 'N', dirty_flag2)
, dirty_flag3 = decode(l_update_requ_flag_tab3(k), 'Y', 'N', dirty_flag3)
, dirty_flag4 = decode(l_update_requ_flag_tab4(k), 'Y', 'N', dirty_flag4)
, dirty_flag5 = decode(l_update_requ_flag_tab5(k), 'Y', 'N', dirty_flag5)
, dirty_flag6 = decode(l_update_requ_flag_tab6(k), 'Y', 'N', dirty_flag6)
WHERE object_id = l_object_ids_tab(k)
AND object_type = l_object_types_tab(k)
and process_number = l_process_number;
END IF; -- l_parent_update_required = 'Y'
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_sum_tab1.delete;
l_sum_tab2.delete;
l_sum_tab3.delete;
l_sum_tab4.delete;
l_sum_tab5.delete;
l_sum_tab6.delete;
l_update_required := 'N';
SELECT MAX(decode(rollup_node1, 'Y', nvl(PROGRESS_OVERRIDE1, nvl(PROGRESS_STATUS_WEIGHT1, -99)), -99))
, MAX(decode(rollup_node2, 'Y', nvl(PROGRESS_OVERRIDE2, nvl(PROGRESS_STATUS_WEIGHT2, -99)), -99))
, MAX(decode(rollup_node3, 'Y', nvl(PROGRESS_OVERRIDE3, nvl(PROGRESS_STATUS_WEIGHT3, -99)), -99))
, MAX(decode(rollup_node4, 'Y', nvl(PROGRESS_OVERRIDE4, nvl(PROGRESS_STATUS_WEIGHT4, -99)), -99))
, MAX(decode(rollup_node5, 'Y', nvl(PROGRESS_OVERRIDE5, nvl(PROGRESS_STATUS_WEIGHT5, -99)), -99))
, MAX(decode(rollup_node6, 'Y', nvl(PROGRESS_OVERRIDE6, nvl(PROGRESS_STATUS_WEIGHT6, -99)), -99))
, count(*)
INTO l_weight1, l_weight2, l_weight3, l_weight4, l_weight5, l_weight6, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND Parent_Object_ID = l_object_ids_tab(i)
--AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES', 'PA_SUBPROJECTS')
-- 4366733 : Deliverable Progress Status should not rollup
AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_SUBPROJECTS')
;
SELECT MAX(nvl(PROGRESS_OVERRIDE1, nvl(PROGRESS_STATUS_WEIGHT1, -99)))
, MAX(nvl(PROGRESS_OVERRIDE2, nvl(PROGRESS_STATUS_WEIGHT2, -99)))
, MAX(nvl(PROGRESS_OVERRIDE3, nvl(PROGRESS_STATUS_WEIGHT3, -99)))
, MAX(nvl(PROGRESS_OVERRIDE4, nvl(PROGRESS_STATUS_WEIGHT4, -99)))
, MAX(nvl(PROGRESS_OVERRIDE5, nvl(PROGRESS_STATUS_WEIGHT5, -99)))
, MAX(nvl(PROGRESS_OVERRIDE6, nvl(PROGRESS_STATUS_WEIGHT6, -99)))
, count(*)
INTO l_weight1, l_weight2, l_weight3, l_weight4, l_weight5, l_weight6, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE PROCESS_NUMBER = l_process_number
AND Parent_Object_ID = l_object_ids_tab(i)
--AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES','PA_SUBPROJECTS')
-- 4366733 : Deliverable Progress Status should not rollup
AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_SUBPROJECTS')
;
l_update_required := 'Y';
IF l_update_required = 'Y' THEN
/* Added the hint to force the unique index for bug#6185523 */
UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.PROGRESS_STATUS_WEIGHT1=l_sum_tab1(i)
, T1.PROGRESS_STATUS_WEIGHT2=l_sum_tab2(i)
, T1.PROGRESS_STATUS_WEIGHT3=l_sum_tab3(i)
, T1.PROGRESS_STATUS_WEIGHT4=l_sum_tab4(i)
, T1.PROGRESS_STATUS_WEIGHT5=l_sum_tab5(i)
, T1.PROGRESS_STATUS_WEIGHT6=l_sum_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
-- Note that Bulk Update is not implemented due to the reasons mentioned in Date Rollup section
/* Please Do no remove
IF l_update_required = 'Y' THEN
FORALL i in 1..l_object_ids_tab.count
UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.PROGRESS_STATUS_WEIGHT1=l_sum_tab1(i)
, T1.PROGRESS_STATUS_WEIGHT2=l_sum_tab2(i)
, T1.PROGRESS_STATUS_WEIGHT3=l_sum_tab3(i)
, T1.PROGRESS_STATUS_WEIGHT4=l_sum_tab4(i)
, T1.PROGRESS_STATUS_WEIGHT5=l_sum_tab5(i)
, T1.PROGRESS_STATUS_WEIGHT6=l_sum_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
END IF; -- l_update_required = 'Y' THEN
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_sum_tab1.delete;
l_sum_tab2.delete;
l_sum_tab3.delete;
l_sum_tab4.delete;
l_sum_tab5.delete;
l_sum_tab6.delete;
l_update_required := 'N';
l_update_required := 'N';
l_task_status_tab1.delete;
l_task_status_tab2.delete;
l_task_status_tab3.delete;
l_task_status_tab4.delete;
l_task_status_tab5.delete;
l_task_status_tab6.delete;
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
IF l_update_required = 'Y' THEN
/* Added the hint to force the unique index for bug#6185523 */
UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.TASK_STATUS1= l_sum_tab1(i)
, T1.TASK_STATUS2=l_sum_tab2(i)
, T1.TASK_STATUS3=l_sum_tab3(i)
, T1.TASK_STATUS4=l_sum_tab4(i)
, T1.TASK_STATUS5=l_sum_tab5(i)
, T1.TASK_STATUS6=l_sum_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i);
-- Note that Bulk Update is not implemented due to the reasons mentioned in Date Rollup section
/* Please Do no remove
IF l_update_required = 'Y' THEN
FORALL i in 1..l_object_ids_tab.count
UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.TASK_STATUS1= l_sum_tab1(i)
, T1.TASK_STATUS2=l_sum_tab2(i)
, T1.TASK_STATUS3=l_sum_tab3(i)
, T1.TASK_STATUS4=l_sum_tab4(i)
, T1.TASK_STATUS5=l_sum_tab5(i)
, T1.TASK_STATUS6=l_sum_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
END IF; -- l_update_required = 'Y' THEN
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_perc_comp_deriv_code_tab.delete;
l_bac_value_tab1.delete;
l_bac_value_tab2.delete;
l_bac_value_tab3.delete;
l_bac_value_tab4.delete;
l_bac_value_tab5.delete;
l_bac_value_tab6.delete;
l_percent_complete_tab1.delete;
l_percent_complete_tab2.delete;
l_percent_complete_tab3.delete;
l_percent_complete_tab4.delete;
l_percent_complete_tab5.delete;
l_percent_complete_tab6.delete;
l_percent_override_tab1.delete;
l_percent_override_tab2.delete;
l_percent_override_tab3.delete;
l_percent_override_tab4.delete;
l_percent_override_tab5.delete;
l_percent_override_tab6.delete;
l_earned_value_tab1.delete;
l_earned_value_tab2.delete;
l_earned_value_tab3.delete;
l_earned_value_tab4.delete;
l_earned_value_tab5.delete;
l_earned_value_tab6.delete;
l_update_required := 'N';
SELECT sum(nvl(percent_override1, nvl(percent_complete1, 0))*nvl(task_weight1, 0))/100,
sum(nvl(task_weight1, 0)),
sum(nvl(percent_override2, nvl(percent_complete2, 0))*nvl(task_weight2, 0))/100,
sum(nvl(task_weight2, 0)),
sum(nvl(percent_override3, nvl(percent_complete3, 0))*nvl(task_weight3, 0))/100,
sum(nvl(task_weight3, 0)),
sum(nvl(percent_override4, nvl(percent_complete4, 0))*nvl(task_weight4, 0))/100,
sum(nvl(task_weight4, 0)),
sum(nvl(percent_override5, nvl(percent_complete5, 0))*nvl(task_weight5, 0))/100,
sum(nvl(task_weight5, 0)),
sum(nvl(percent_override6, nvl(percent_complete6, 0))*nvl(task_weight6, 0))/100,
sum(nvl(task_weight6, 0)),
count(*)
INTO l_actual_duration1, l_pc_duration1
, l_actual_duration2, l_pc_duration2
, l_actual_duration3, l_pc_duration3
, l_actual_duration4, l_pc_duration4
, l_actual_duration5, l_pc_duration5
, l_actual_duration6, l_pc_duration6
, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE = 'PA_DELIVERABLES'
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
select sum(nvl(earned_value1, 0)), sum(nvl(bac_value1, 0))
, sum(nvl(earned_value2, 0)), sum(nvl(bac_value2, 0))
, sum(nvl(earned_value3, 0)), sum(nvl(bac_value3, 0))
, sum(nvl(earned_value4, 0)), sum(nvl(bac_value4, 0))
, sum(nvl(earned_value5, 0)), sum(nvl(bac_value5, 0))
, sum(nvl(earned_value6, 0)), sum(nvl(bac_value6, 0))
, count(*)
INTO l_actual_duration1, l_pc_duration1
, l_actual_duration2, l_pc_duration2
, l_actual_duration3, l_pc_duration3
, l_actual_duration4, l_pc_duration4
, l_actual_duration5, l_pc_duration5
, l_actual_duration6, l_pc_duration6
, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE IN ('PA_ASSIGNMENTS', 'PA_SUBPROJECTS')
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
IF l_update_required = 'Y' THEN
FORALL i in 1..l_object_ids_tab.count
/* Added the hint to force the unique index for bug#6185523 */
UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
SET t1.percent_complete1=l_percent_complete_tab1(i)
, t1.percent_complete2=l_percent_complete_tab2(i)
, t1.percent_complete3=l_percent_complete_tab3(i)
, t1.percent_complete4=l_percent_complete_tab4(i)
, t1.percent_complete5=l_percent_complete_tab5(i)
, t1.percent_complete6=l_percent_complete_tab6(i)
, t1.earned_value1=l_earned_value_tab1(i)
, t1.earned_value2=l_earned_value_tab2(i)
, t1.earned_value3=l_earned_value_tab3(i)
, t1.earned_value4=l_earned_value_tab4(i)
, t1.earned_value5=l_earned_value_tab5(i)
, t1.earned_value6=l_earned_value_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
END IF; -- l_update_required = 'Y' THEN
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_perc_comp_deriv_code_tab.delete;
l_bac_value_tab1.delete;
l_bac_value_tab2.delete;
l_bac_value_tab3.delete;
l_bac_value_tab4.delete;
l_bac_value_tab5.delete;
l_bac_value_tab6.delete;
l_percent_complete_tab1.delete;
l_percent_complete_tab2.delete;
l_percent_complete_tab3.delete;
l_percent_complete_tab4.delete;
l_percent_complete_tab5.delete;
l_percent_complete_tab6.delete;
l_percent_override_tab1.delete;
l_percent_override_tab2.delete;
l_percent_override_tab3.delete;
l_percent_override_tab4.delete;
l_percent_override_tab5.delete;
l_percent_override_tab6.delete;
l_earned_value_tab1.delete;
l_earned_value_tab2.delete;
l_earned_value_tab3.delete;
l_earned_value_tab4.delete;
l_earned_value_tab5.delete;
l_earned_value_tab6.delete;
l_summ_obj_flag_tab.delete; -- 4587517
l_update_required := 'N';
l_update_required := 'N';
SELECT sum(nvl(percent_override1, nvl(percent_complete1, 0))*nvl(task_weight1, 0))/100,
sum(nvl(task_weight1, 0)),
sum(nvl(percent_override2, nvl(percent_complete2, 0))*nvl(task_weight2, 0))/100,
sum(nvl(task_weight2, 0)),
sum(nvl(percent_override3, nvl(percent_complete3, 0))*nvl(task_weight3, 0))/100,
sum(nvl(task_weight3, 0)),
sum(nvl(percent_override4, nvl(percent_complete4, 0))*nvl(task_weight4, 0))/100,
sum(nvl(task_weight4, 0)),
sum(nvl(percent_override5, nvl(percent_complete5, 0))*nvl(task_weight5, 0))/100,
sum(nvl(task_weight5, 0)),
sum(nvl(percent_override6, nvl(percent_complete6, 0))*nvl(task_weight6, 0))/100,
sum(nvl(task_weight6, 0)),
count(*)
INTO l_actual_duration1, l_pc_duration1
, l_actual_duration2, l_pc_duration2
, l_actual_duration3, l_pc_duration3
, l_actual_duration4, l_pc_duration4
, l_actual_duration5, l_pc_duration5
, l_actual_duration6, l_pc_duration6
, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE = 'PA_DELIVERABLES'
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
SELECT sum(nvl(earned_value1, 0)), sum(nvl(bac_value1, 0))
, sum(nvl(earned_value2, 0)), sum(nvl(bac_value2, 0))
, sum(nvl(earned_value3, 0)), sum(nvl(bac_value3, 0))
, sum(nvl(earned_value4, 0)), sum(nvl(bac_value4, 0))
, sum(nvl(earned_value5, 0)), sum(nvl(bac_value5, 0))
, sum(nvl(earned_value6, 0)), sum(nvl(bac_value6, 0))
, count(*)
INTO l_actual_duration1_a, l_duration1_a
, l_actual_duration2_a, l_duration2_a
, l_actual_duration3_a, l_duration3_a
, l_actual_duration4_a, l_duration4_a
, l_actual_duration5_a, l_duration5_a
, l_actual_duration6_a, l_duration6_a
, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE = 'PA_ASSIGNMENTS'
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type = 'PA_TASKS'
;
SELECT sum(nvl(percent_override1, nvl(percent_complete1, 0))*nvl(bac_value1, 0)/100)
, sum(nvl(bac_value1, 0))
, sum(nvl(percent_override2, nvl(percent_complete2, 0))*nvl(bac_value2, 0)/100)
, sum(nvl(bac_value2, 0))
, sum(nvl(percent_override3, nvl(percent_complete3, 0))*nvl(bac_value3, 0)/100)
, sum(nvl(bac_value3, 0))
, sum(nvl(percent_override4, nvl(percent_complete4, 0))*nvl(bac_value4, 0)/100)
, sum(nvl(bac_value4, 0))
, sum(nvl(percent_override5, nvl(percent_complete5, 0))*nvl(bac_value5, 0)/100)
, sum(nvl(bac_value5, 0))
, sum(nvl(percent_override6, nvl(percent_complete6, 0))*nvl(bac_value6, 0)/100)
, sum(nvl(bac_value6, 0))
, count(*)
INTO l_actual_duration1_t, l_duration1_t
, l_actual_duration2_t, l_duration2_t
, l_actual_duration3_t, l_duration3_t
, l_actual_duration4_t, l_duration4_t
, l_actual_duration5_t, l_duration5_t
, l_actual_duration6_t, l_duration6_t
, l_count1
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE = 'PA_SUBPROJECTS'
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type = 'PA_TASKS'
;
SELECT sum(nvl(earned_value1, 0)), sum(nvl(bac_value1, 0))
, sum(nvl(earned_value2, 0)), sum(nvl(bac_value2, 0))
, sum(nvl(earned_value3, 0)), sum(nvl(bac_value3, 0))
, sum(nvl(earned_value4, 0)), sum(nvl(bac_value4, 0))
, sum(nvl(earned_value5, 0)), sum(nvl(bac_value5, 0))
, sum(nvl(earned_value6, 0)), sum(nvl(bac_value6, 0))
, count(*)
INTO l_actual_duration1_a, l_duration1_a
, l_actual_duration2_a, l_duration2_a
, l_actual_duration3_a, l_duration3_a
, l_actual_duration4_a, l_duration4_a
, l_actual_duration5_a, l_duration5_a
, l_actual_duration6_a, l_duration6_a
, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE = 'PA_ASSIGNMENTS'
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
SELECT sum(nvl(percent_override1, nvl(percent_complete1, 0))*nvl(bac_value1, 0)/100)
, sum(nvl(bac_value1, 0))
, sum(nvl(percent_override2, nvl(percent_complete2, 0))*nvl(bac_value2, 0)/100)
, sum(nvl(bac_value2, 0))
, sum(nvl(percent_override3, nvl(percent_complete3, 0))*nvl(bac_value3, 0)/100)
, sum(nvl(bac_value3, 0))
, sum(nvl(percent_override4, nvl(percent_complete4, 0))*nvl(bac_value4, 0)/100)
, sum(nvl(bac_value4, 0))
, sum(nvl(percent_override5, nvl(percent_complete5, 0))*nvl(bac_value5, 0)/100)
, sum(nvl(bac_value5, 0))
, sum(nvl(percent_override6, nvl(percent_complete6, 0))*nvl(bac_value6, 0)/100)
, sum(nvl(bac_value6, 0))
, count(*) -- Bug 4343615
INTO l_actual_duration1_t, l_duration1_t
, l_actual_duration2_t, l_duration2_t
, l_actual_duration3_t, l_duration3_t
, l_actual_duration4_t, l_duration4_t
, l_actual_duration5_t, l_duration5_t
, l_actual_duration6_t, l_duration6_t
, l_count1 -- Bug 4343615
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
--AND OBJECT_TYPE = 'PA_TASKS' --4582956
AND OBJECT_TYPE IN ('PA_TASKS','PA_SUBPROJECTS') --4582956 : Added PA_SUBPROJECTS
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
SELECT sum(nvl(percent_override1, nvl(percent_complete1, 0))*nvl(task_weight1, 0))/100
, sum(nvl(task_weight1, 0))
, sum(nvl(percent_override2, nvl(percent_complete2, 0))*nvl(task_weight2, 0))/100
, sum(nvl(task_weight2, 0))
, sum(nvl(percent_override3, nvl(percent_complete3, 0))*nvl(task_weight3, 0))/100
, sum(nvl(task_weight3, 0))
, sum(nvl(percent_override4, nvl(percent_complete4, 0))*nvl(task_weight4, 0))/100
, sum(nvl(task_weight4, 0))
, sum(nvl(percent_override5, nvl(percent_complete5, 0))*nvl(task_weight5, 0))/100
, sum(nvl(task_weight5, 0))
, sum(nvl(percent_override6, nvl(percent_complete6, 0))*nvl(task_weight6, 0))/100
, sum(nvl(task_weight6, 0))
, count(*)
INTO l_actual_duration1, l_pc_duration1
, l_actual_duration2, l_pc_duration2
, l_actual_duration3, l_pc_duration3
, l_actual_duration4, l_pc_duration4
, l_actual_duration5, l_pc_duration5
, l_actual_duration6, l_pc_duration6
, l_count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE = 'PA_TASKS'
AND Parent_Object_ID = l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
IF l_update_required = 'Y' THEN
/* Added the hint to force the unique index for bug#6185523 */
UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.PERCENT_COMPLETE1=l_PERCENT_COMPLETE_tab1(i)
, T1.PERCENT_COMPLETE2=l_PERCENT_COMPLETE_tab2(i)
, T1.PERCENT_COMPLETE3=l_PERCENT_COMPLETE_tab3(i)
, T1.PERCENT_COMPLETE4=l_PERCENT_COMPLETE_tab4(i)
, T1.PERCENT_COMPLETE5=l_PERCENT_COMPLETE_tab5(i)
, T1.PERCENT_COMPLETE6=l_PERCENT_COMPLETE_tab6(i)
, T1.EARNED_VALUE1=l_EARNED_VALUE_tab1(i)
, T1.EARNED_VALUE2=l_EARNED_VALUE_tab2(i)
, T1.EARNED_VALUE3=l_EARNED_VALUE_tab3(i)
, T1.EARNED_VALUE4=l_EARNED_VALUE_tab4(i)
, T1.EARNED_VALUE5=l_EARNED_VALUE_tab5(i)
, T1.EARNED_VALUE6=l_EARNED_VALUE_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
-- Note that Bulk Update is not implemnted due to reason mentioned in Dates Rollup section
/* Please do not remove
IF l_update_required = 'Y' THEN
FORALL i in 1..l_object_ids_tab.count
UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.PERCENT_COMPLETE1=l_PERCENT_COMPLETE_tab1(i)
, T1.PERCENT_COMPLETE2=l_PERCENT_COMPLETE_tab2(i)
, T1.PERCENT_COMPLETE3=l_PERCENT_COMPLETE_tab3(i)
, T1.PERCENT_COMPLETE4=l_PERCENT_COMPLETE_tab4(i)
, T1.PERCENT_COMPLETE5=l_PERCENT_COMPLETE_tab5(i)
, T1.PERCENT_COMPLETE6=l_PERCENT_COMPLETE_tab6(i)
, T1.EARNED_VALUE1=l_EARNED_VALUE_tab1(i)
, T1.EARNED_VALUE2=l_EARNED_VALUE_tab2(i)
, T1.EARNED_VALUE3=l_EARNED_VALUE_tab3(i)
, T1.EARNED_VALUE4=l_EARNED_VALUE_tab4(i)
, T1.EARNED_VALUE5=l_EARNED_VALUE_tab5(i)
, T1.EARNED_VALUE6=l_EARNED_VALUE_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
END IF; -- l_update_required = 'Y' THEN
l_object_ids_tab.delete;
l_object_types_tab.delete;
l_update_required := 'N';
l_sum_tab1.delete;
l_sum_tab2.delete;
l_sum_tab3.delete;
l_sum_tab4.delete;
l_sum_tab5.delete;
l_sum_tab6.delete;
l_update_required := 'N';
SELECT Sum(decode(rollup_node1, 'Y', NVL(ETC_Cost1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(ETC_Cost2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(ETC_Cost3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(ETC_Cost4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(ETC_Cost5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(ETC_Cost6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(PPL_ETC_COST1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(PPL_ETC_COST2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(PPL_ETC_COST3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(PPL_ETC_COST4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(PPL_ETC_COST5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(PPL_ETC_COST6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(EQPMT_ETC_COST1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(EQPMT_ETC_COST2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(EQPMT_ETC_COST3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(EQPMT_ETC_COST4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(EQPMT_ETC_COST5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(EQPMT_ETC_COST6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(PPL_UNPLAND_EFFORT1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(PPL_UNPLAND_EFFORT2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(PPL_UNPLAND_EFFORT3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(PPL_UNPLAND_EFFORT4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(PPL_UNPLAND_EFFORT5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(PPL_UNPLAND_EFFORT6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(EQPMT_ETC_EFFORT1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(EQPMT_ETC_EFFORT2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(EQPMT_ETC_EFFORT3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(EQPMT_ETC_EFFORT4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(EQPMT_ETC_EFFORT5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(EQPMT_ETC_EFFORT6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(SUB_PRJ_ETC_COST1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(SUB_PRJ_ETC_COST2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(SUB_PRJ_ETC_COST3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(SUB_PRJ_ETC_COST4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(SUB_PRJ_ETC_COST5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(SUB_PRJ_ETC_COST6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(SUB_PRJ_PPL_ETC_COST1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(SUB_PRJ_PPL_ETC_COST2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(SUB_PRJ_PPL_ETC_COST3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(SUB_PRJ_PPL_ETC_COST4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(SUB_PRJ_PPL_ETC_COST5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(SUB_PRJ_PPL_ETC_COST6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(SUB_PRJ_EQPMT_ETC_COST1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(SUB_PRJ_EQPMT_ETC_COST2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(SUB_PRJ_EQPMT_ETC_COST3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(SUB_PRJ_EQPMT_ETC_COST4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(SUB_PRJ_EQPMT_ETC_COST5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(SUB_PRJ_EQPMT_ETC_COST6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(SUB_PRJ_PPL_ETC_EFFORT1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(SUB_PRJ_PPL_ETC_EFFORT2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(SUB_PRJ_PPL_ETC_EFFORT3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(SUB_PRJ_PPL_ETC_EFFORT4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(SUB_PRJ_PPL_ETC_EFFORT5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(SUB_PRJ_PPL_ETC_EFFORT6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(SUB_PRJ_EQPMT_ETC_EFFORT1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(SUB_PRJ_EQPMT_ETC_EFFORT2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(SUB_PRJ_EQPMT_ETC_EFFORT3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(SUB_PRJ_EQPMT_ETC_EFFORT4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(SUB_PRJ_EQPMT_ETC_EFFORT5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(SUB_PRJ_EQPMT_ETC_EFFORT6,0),0)),
Sum(decode(rollup_node1, 'Y', NVL(REMAINING_EFFORT1,0),0)),
Sum(decode(rollup_node2, 'Y', NVL(REMAINING_EFFORT2,0),0)),
Sum(decode(rollup_node3, 'Y', NVL(REMAINING_EFFORT3,0),0)),
Sum(decode(rollup_node4, 'Y', NVL(REMAINING_EFFORT4,0),0)),
Sum(decode(rollup_node5, 'Y', NVL(REMAINING_EFFORT5,0),0)),
Sum(decode(rollup_node6, 'Y', NVL(REMAINING_EFFORT6,0),0)),
Count(*)
INTO l_ETC_Cost_tab1(i),
l_ETC_Cost_tab2(i),
l_ETC_Cost_tab3(i),
l_ETC_Cost_tab4(i),
l_ETC_Cost_tab5(i),
l_ETC_Cost_tab6(i),
l_PPL_ETC_COST_tab1(i),
l_PPL_ETC_COST_tab2(i),
l_PPL_ETC_COST_tab3(i),
l_PPL_ETC_COST_tab4(i),
l_PPL_ETC_COST_tab5(i),
l_PPL_ETC_COST_tab6(i),
l_EQPMT_ETC_COST_tab1(i),
l_EQPMT_ETC_COST_tab2(i),
l_EQPMT_ETC_COST_tab3(i),
l_EQPMT_ETC_COST_tab4(i),
l_EQPMT_ETC_COST_tab5(i),
l_EQPMT_ETC_COST_tab6(i),
l_PPL_UNPLAND_EFFORT_tab1(i),
l_PPL_UNPLAND_EFFORT_tab2(i),
l_PPL_UNPLAND_EFFORT_tab3(i),
l_PPL_UNPLAND_EFFORT_tab4(i),
l_PPL_UNPLAND_EFFORT_tab5(i),
l_PPL_UNPLAND_EFFORT_tab6(i),
l_EQPMT_ETC_EFFORT_tab1(i),
l_EQPMT_ETC_EFFORT_tab2(i),
l_EQPMT_ETC_EFFORT_tab3(i),
l_EQPMT_ETC_EFFORT_tab4(i),
l_EQPMT_ETC_EFFORT_tab5(i),
l_EQPMT_ETC_EFFORT_tab6(i),
l_SUB_PRJ_ETC_COST_tab1(i),
l_SUB_PRJ_ETC_COST_tab2(i),
l_SUB_PRJ_ETC_COST_tab3(i),
l_SUB_PRJ_ETC_COST_tab4(i),
l_SUB_PRJ_ETC_COST_tab5(i),
l_SUB_PRJ_ETC_COST_tab6(i),
l_SUB_PRJ_PPL_ETC_COST_tab1(i),
l_SUB_PRJ_PPL_ETC_COST_tab2(i),
l_SUB_PRJ_PPL_ETC_COST_tab3(i),
l_SUB_PRJ_PPL_ETC_COST_tab4(i),
l_SUB_PRJ_PPL_ETC_COST_tab5(i),
l_SUB_PRJ_PPL_ETC_COST_tab6(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab1(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab2(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab3(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab4(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab5(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab6(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab1(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab2(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab3(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab4(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab5(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab6(i),
L_SP_EQPMT_ETC_EFFORT_TAB1(i),
L_SP_EQPMT_ETC_EFFORT_TAB2(i),
L_SP_EQPMT_ETC_EFFORT_TAB3(i),
L_SP_EQPMT_ETC_EFFORT_TAB4(i),
L_SP_EQPMT_ETC_EFFORT_TAB5(i),
L_SP_EQPMT_ETC_EFFORT_TAB6(i),
l_sum_tab1(i),
l_sum_tab2(i),
l_sum_tab3(i),
l_sum_tab4(i),
l_sum_tab5(i),
l_sum_tab6(i),
l_Count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE <> 'PA_DELIVERABLES'
AND Parent_Object_ID =l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
SELECT Sum(NVL(ETC_Cost1,0)),
Sum(NVL(ETC_Cost2,0)),
Sum(NVL(ETC_Cost3,0)),
Sum(NVL(ETC_Cost4,0)),
Sum(NVL(ETC_Cost5,0)),
Sum(NVL(ETC_Cost6,0)),
Sum(NVL(PPL_ETC_COST1,0)),
Sum(NVL(PPL_ETC_COST2,0)),
Sum(NVL(PPL_ETC_COST3,0)),
Sum(NVL(PPL_ETC_COST4,0)),
Sum(NVL(PPL_ETC_COST5,0)),
Sum(NVL(PPL_ETC_COST6,0)),
Sum(NVL(EQPMT_ETC_COST1,0)),
Sum(NVL(EQPMT_ETC_COST2,0)),
Sum(NVL(EQPMT_ETC_COST3,0)),
Sum(NVL(EQPMT_ETC_COST4,0)),
Sum(NVL(EQPMT_ETC_COST5,0)),
Sum(NVL(EQPMT_ETC_COST6,0)),
Sum(NVL(PPL_UNPLAND_EFFORT1,0)),
Sum(NVL(PPL_UNPLAND_EFFORT2,0)),
Sum(NVL(PPL_UNPLAND_EFFORT3,0)),
Sum(NVL(PPL_UNPLAND_EFFORT4,0)),
Sum(NVL(PPL_UNPLAND_EFFORT5,0)),
Sum(NVL(PPL_UNPLAND_EFFORT6,0)),
Sum(NVL(EQPMT_ETC_EFFORT1,0)),
Sum(NVL(EQPMT_ETC_EFFORT2,0)),
Sum(NVL(EQPMT_ETC_EFFORT3,0)),
Sum(NVL(EQPMT_ETC_EFFORT4,0)),
Sum(NVL(EQPMT_ETC_EFFORT5,0)),
Sum(NVL(EQPMT_ETC_EFFORT6,0)),
Sum(NVL(SUB_PRJ_ETC_COST1,0)),
Sum(NVL(SUB_PRJ_ETC_COST2,0)),
Sum(NVL(SUB_PRJ_ETC_COST3,0)),
Sum(NVL(SUB_PRJ_ETC_COST4,0)),
Sum(NVL(SUB_PRJ_ETC_COST5,0)),
Sum(NVL(SUB_PRJ_ETC_COST6,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_COST1,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_COST2,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_COST3,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_COST4,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_COST5,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_COST6,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_COST1,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_COST2,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_COST3,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_COST4,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_COST5,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_COST6,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_EFFORT1,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_EFFORT2,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_EFFORT3,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_EFFORT4,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_EFFORT5,0)),
Sum(NVL(SUB_PRJ_PPL_ETC_EFFORT6,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_EFFORT1,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_EFFORT2,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_EFFORT3,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_EFFORT4,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_EFFORT5,0)),
Sum(NVL(SUB_PRJ_EQPMT_ETC_EFFORT6,0)),
Sum(NVL(REMAINING_EFFORT1,0)),
Sum(NVL(REMAINING_EFFORT2,0)),
Sum(NVL(REMAINING_EFFORT3,0)),
Sum(NVL(REMAINING_EFFORT4,0)),
Sum(NVL(REMAINING_EFFORT5,0)),
Sum(NVL(REMAINING_EFFORT6,0)),
Count(*)
INTO l_ETC_Cost_tab1(i),
l_ETC_Cost_tab2(i),
l_ETC_Cost_tab3(i),
l_ETC_Cost_tab4(i),
l_ETC_Cost_tab5(i),
l_ETC_Cost_tab6(i),
l_PPL_ETC_COST_tab1(i),
l_PPL_ETC_COST_tab2(i),
l_PPL_ETC_COST_tab3(i),
l_PPL_ETC_COST_tab4(i),
l_PPL_ETC_COST_tab5(i),
l_PPL_ETC_COST_tab6(i),
l_EQPMT_ETC_COST_tab1(i),
l_EQPMT_ETC_COST_tab2(i),
l_EQPMT_ETC_COST_tab3(i),
l_EQPMT_ETC_COST_tab4(i),
l_EQPMT_ETC_COST_tab5(i),
l_EQPMT_ETC_COST_tab6(i),
l_PPL_UNPLAND_EFFORT_tab1(i),
l_PPL_UNPLAND_EFFORT_tab2(i),
l_PPL_UNPLAND_EFFORT_tab3(i),
l_PPL_UNPLAND_EFFORT_tab4(i),
l_PPL_UNPLAND_EFFORT_tab5(i),
l_PPL_UNPLAND_EFFORT_tab6(i),
l_EQPMT_ETC_EFFORT_tab1(i),
l_EQPMT_ETC_EFFORT_tab2(i),
l_EQPMT_ETC_EFFORT_tab3(i),
l_EQPMT_ETC_EFFORT_tab4(i),
l_EQPMT_ETC_EFFORT_tab5(i),
l_EQPMT_ETC_EFFORT_tab6(i),
l_SUB_PRJ_ETC_COST_tab1(i),
l_SUB_PRJ_ETC_COST_tab2(i),
l_SUB_PRJ_ETC_COST_tab3(i),
l_SUB_PRJ_ETC_COST_tab4(i),
l_SUB_PRJ_ETC_COST_tab5(i),
l_SUB_PRJ_ETC_COST_tab6(i),
l_SUB_PRJ_PPL_ETC_COST_tab1(i),
l_SUB_PRJ_PPL_ETC_COST_tab2(i),
l_SUB_PRJ_PPL_ETC_COST_tab3(i),
l_SUB_PRJ_PPL_ETC_COST_tab4(i),
l_SUB_PRJ_PPL_ETC_COST_tab5(i),
l_SUB_PRJ_PPL_ETC_COST_tab6(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab1(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab2(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab3(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab4(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab5(i),
l_SUB_PRJ_EQPMT_ETC_COST_tab6(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab1(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab2(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab3(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab4(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab5(i),
l_SUB_PRJ_PPL_ETC_EFFORT_tab6(i),
L_SP_EQPMT_ETC_EFFORT_TAB1(i),
L_SP_EQPMT_ETC_EFFORT_TAB2(i),
L_SP_EQPMT_ETC_EFFORT_TAB3(i),
L_SP_EQPMT_ETC_EFFORT_TAB4(i),
L_SP_EQPMT_ETC_EFFORT_TAB5(i),
L_SP_EQPMT_ETC_EFFORT_TAB6(i),
l_sum_tab1(i),
l_sum_tab2(i),
l_sum_tab3(i),
l_sum_tab4(i),
l_sum_tab5(i),
l_sum_tab6(i),
l_Count
FROM PA_PROJ_ROLLUP_BULK_TEMP
WHERE Process_Number = l_Process_Number
AND OBJECT_TYPE <> 'PA_DELIVERABLES'
AND Parent_Object_ID =l_object_ids_tab(i)
AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
;
l_update_required := 'Y';
UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.ETC_Cost1=l_ETC_Cost_tab1(i)
, T1.ETC_Cost2=l_ETC_Cost_tab2(i)
, T1.ETC_Cost3=l_ETC_Cost_tab3(i)
, T1.ETC_Cost4=l_ETC_Cost_tab4(i)
, T1.ETC_Cost5=l_ETC_Cost_tab5(i)
, T1.ETC_Cost6=l_ETC_Cost_tab6(i)
, T1.PPL_ETC_COST1=l_PPL_ETC_COST_tab1(i)
, T1.PPL_ETC_COST2=l_PPL_ETC_COST_tab2(i)
, T1.PPL_ETC_COST3=l_PPL_ETC_COST_tab3(i)
, T1.PPL_ETC_COST4=l_PPL_ETC_COST_tab4(i)
, T1.PPL_ETC_COST5=l_PPL_ETC_COST_tab5(i)
, T1.PPL_ETC_COST6=l_PPL_ETC_COST_tab6(i)
, T1.EQPMT_ETC_COST1=l_EQPMT_ETC_COST_tab1(i)
, T1.EQPMT_ETC_COST2=l_EQPMT_ETC_COST_tab2(i)
, T1.EQPMT_ETC_COST3=l_EQPMT_ETC_COST_tab3(i)
, T1.EQPMT_ETC_COST4=l_EQPMT_ETC_COST_tab4(i)
, T1.EQPMT_ETC_COST5=l_EQPMT_ETC_COST_tab5(i)
, T1.EQPMT_ETC_COST6=l_EQPMT_ETC_COST_tab6(i)
, T1.PPL_UNPLAND_EFFORT1=l_PPL_UNPLAND_EFFORT_tab1(i)
, T1.PPL_UNPLAND_EFFORT2=l_PPL_UNPLAND_EFFORT_tab2(i)
, T1.PPL_UNPLAND_EFFORT3=l_PPL_UNPLAND_EFFORT_tab3(i)
, T1.PPL_UNPLAND_EFFORT4=l_PPL_UNPLAND_EFFORT_tab4(i)
, T1.PPL_UNPLAND_EFFORT5=l_PPL_UNPLAND_EFFORT_tab5(i)
, T1.PPL_UNPLAND_EFFORT6=l_PPL_UNPLAND_EFFORT_tab6(i)
, T1.EQPMT_ETC_EFFORT1=l_EQPMT_ETC_EFFORT_tab1(i)
, T1.EQPMT_ETC_EFFORT2=l_EQPMT_ETC_EFFORT_tab2(i)
, T1.EQPMT_ETC_EFFORT3=l_EQPMT_ETC_EFFORT_tab3(i)
, T1.EQPMT_ETC_EFFORT4=l_EQPMT_ETC_EFFORT_tab4(i)
, T1.EQPMT_ETC_EFFORT5=l_EQPMT_ETC_EFFORT_tab5(i)
, T1.EQPMT_ETC_EFFORT6=l_EQPMT_ETC_EFFORT_tab6(i)
, T1.SUB_PRJ_ETC_COST1=l_SUB_PRJ_ETC_COST_tab1(i)
, T1.SUB_PRJ_ETC_COST2=l_SUB_PRJ_ETC_COST_tab2(i)
, T1.SUB_PRJ_ETC_COST3=l_SUB_PRJ_ETC_COST_tab3(i)
, T1.SUB_PRJ_ETC_COST4=l_SUB_PRJ_ETC_COST_tab4(i)
, T1.SUB_PRJ_ETC_COST5=l_SUB_PRJ_ETC_COST_tab5(i)
, T1.SUB_PRJ_ETC_COST6=l_SUB_PRJ_ETC_COST_tab6(i)
, T1.SUB_PRJ_PPL_ETC_COST1=l_SUB_PRJ_PPL_ETC_COST_tab1(i)
, T1.SUB_PRJ_PPL_ETC_COST2=l_SUB_PRJ_PPL_ETC_COST_tab2(i)
, T1.SUB_PRJ_PPL_ETC_COST3=l_SUB_PRJ_PPL_ETC_COST_tab3(i)
, T1.SUB_PRJ_PPL_ETC_COST4=l_SUB_PRJ_PPL_ETC_COST_tab4(i)
, T1.SUB_PRJ_PPL_ETC_COST5=l_SUB_PRJ_PPL_ETC_COST_tab5(i)
, T1.SUB_PRJ_PPL_ETC_COST6=l_SUB_PRJ_PPL_ETC_COST_tab6(i)
, T1.SUB_PRJ_EQPMT_ETC_COST1=l_SUB_PRJ_EQPMT_ETC_COST_tab1(i)
, T1.SUB_PRJ_EQPMT_ETC_COST2=l_SUB_PRJ_EQPMT_ETC_COST_tab2(i)
, T1.SUB_PRJ_EQPMT_ETC_COST3=l_SUB_PRJ_EQPMT_ETC_COST_tab3(i)
, T1.SUB_PRJ_EQPMT_ETC_COST4=l_SUB_PRJ_EQPMT_ETC_COST_tab4(i)
, T1.SUB_PRJ_EQPMT_ETC_COST5=l_SUB_PRJ_EQPMT_ETC_COST_tab5(i)
, T1.SUB_PRJ_EQPMT_ETC_COST6=l_SUB_PRJ_EQPMT_ETC_COST_tab6(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT1=l_SUB_PRJ_PPL_ETC_EFFORT_tab1(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT2=l_SUB_PRJ_PPL_ETC_EFFORT_tab2(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT3=l_SUB_PRJ_PPL_ETC_EFFORT_tab3(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT4=l_SUB_PRJ_PPL_ETC_EFFORT_tab4(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT5=l_SUB_PRJ_PPL_ETC_EFFORT_tab5(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT6=l_SUB_PRJ_PPL_ETC_EFFORT_tab6(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT1=L_SP_EQPMT_ETC_EFFORT_TAB1(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT2=L_SP_EQPMT_ETC_EFFORT_TAB2(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT3=L_SP_EQPMT_ETC_EFFORT_TAB3(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT4=L_SP_EQPMT_ETC_EFFORT_TAB4(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT5=L_SP_EQPMT_ETC_EFFORT_TAB5(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT6=L_SP_EQPMT_ETC_EFFORT_TAB6(i)
, T1.REMAINING_EFFORT1 = l_sum_tab1(i)
, T1.REMAINING_EFFORT2 = l_sum_tab2(i)
, T1.REMAINING_EFFORT3 = l_sum_tab3(i)
, T1.REMAINING_EFFORT4 = l_sum_tab4(i)
, T1.REMAINING_EFFORT5 = l_sum_tab5(i)
, T1.REMAINING_EFFORT6 = l_sum_tab6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
-- NOte that Bulk Update is not done due to problem mentioned in Dates Rollup Section
/* Please do not remove
IF l_update_required = 'Y' THEN
FORALL i in 1..l_object_ids_tab.count
UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
SET T1.ETC_Cost1=l_ETC_Cost_tab1(i)
, T1.ETC_Cost2=l_ETC_Cost_tab2(i)
, T1.ETC_Cost3=l_ETC_Cost_tab3(i)
, T1.ETC_Cost4=l_ETC_Cost_tab4(i)
, T1.ETC_Cost5=l_ETC_Cost_tab5(i)
, T1.ETC_Cost6=l_ETC_Cost_tab6(i)
, T1.PPL_ETC_COST1=l_PPL_ETC_COST_tab1(i)
, T1.PPL_ETC_COST2=l_PPL_ETC_COST_tab2(i)
, T1.PPL_ETC_COST3=l_PPL_ETC_COST_tab3(i)
, T1.PPL_ETC_COST4=l_PPL_ETC_COST_tab4(i)
, T1.PPL_ETC_COST5=l_PPL_ETC_COST_tab5(i)
, T1.PPL_ETC_COST6=l_PPL_ETC_COST_tab6(i)
, T1.EQPMT_ETC_COST1=l_EQPMT_ETC_COST_tab1(i)
, T1.EQPMT_ETC_COST2=l_EQPMT_ETC_COST_tab2(i)
, T1.EQPMT_ETC_COST3=l_EQPMT_ETC_COST_tab3(i)
, T1.EQPMT_ETC_COST4=l_EQPMT_ETC_COST_tab4(i)
, T1.EQPMT_ETC_COST5=l_EQPMT_ETC_COST_tab5(i)
, T1.EQPMT_ETC_COST6=l_EQPMT_ETC_COST_tab6(i)
, T1.PPL_UNPLAND_EFFORT1=l_PPL_UNPLAND_EFFORT_tab1(i)
, T1.PPL_UNPLAND_EFFORT2=l_PPL_UNPLAND_EFFORT_tab2(i)
, T1.PPL_UNPLAND_EFFORT3=l_PPL_UNPLAND_EFFORT_tab3(i)
, T1.PPL_UNPLAND_EFFORT4=l_PPL_UNPLAND_EFFORT_tab4(i)
, T1.PPL_UNPLAND_EFFORT5=l_PPL_UNPLAND_EFFORT_tab5(i)
, T1.PPL_UNPLAND_EFFORT6=l_PPL_UNPLAND_EFFORT_tab6(i)
, T1.EQPMT_ETC_EFFORT1=l_EQPMT_ETC_EFFORT_tab1(i)
, T1.EQPMT_ETC_EFFORT2=l_EQPMT_ETC_EFFORT_tab2(i)
, T1.EQPMT_ETC_EFFORT3=l_EQPMT_ETC_EFFORT_tab3(i)
, T1.EQPMT_ETC_EFFORT4=l_EQPMT_ETC_EFFORT_tab4(i)
, T1.EQPMT_ETC_EFFORT5=l_EQPMT_ETC_EFFORT_tab5(i)
, T1.EQPMT_ETC_EFFORT6=l_EQPMT_ETC_EFFORT_tab6(i)
, T1.SUB_PRJ_ETC_COST1=l_SUB_PRJ_ETC_COST_tab1(i)
, T1.SUB_PRJ_ETC_COST2=l_SUB_PRJ_ETC_COST_tab2(i)
, T1.SUB_PRJ_ETC_COST3=l_SUB_PRJ_ETC_COST_tab3(i)
, T1.SUB_PRJ_ETC_COST4=l_SUB_PRJ_ETC_COST_tab4(i)
, T1.SUB_PRJ_ETC_COST5=l_SUB_PRJ_ETC_COST_tab5(i)
, T1.SUB_PRJ_ETC_COST6=l_SUB_PRJ_ETC_COST_tab6(i)
, T1.SUB_PRJ_PPL_ETC_COST1=l_SUB_PRJ_PPL_ETC_COST_tab1(i)
, T1.SUB_PRJ_PPL_ETC_COST2=l_SUB_PRJ_PPL_ETC_COST_tab2(i)
, T1.SUB_PRJ_PPL_ETC_COST3=l_SUB_PRJ_PPL_ETC_COST_tab3(i)
, T1.SUB_PRJ_PPL_ETC_COST4=l_SUB_PRJ_PPL_ETC_COST_tab4(i)
, T1.SUB_PRJ_PPL_ETC_COST5=l_SUB_PRJ_PPL_ETC_COST_tab5(i)
, T1.SUB_PRJ_PPL_ETC_COST6=l_SUB_PRJ_PPL_ETC_COST_tab6(i)
, T1.SUB_PRJ_EQPMT_ETC_COST1=l_SUB_PRJ_EQPMT_ETC_COST_tab1(i)
, T1.SUB_PRJ_EQPMT_ETC_COST2=l_SUB_PRJ_EQPMT_ETC_COST_tab2(i)
, T1.SUB_PRJ_EQPMT_ETC_COST3=l_SUB_PRJ_EQPMT_ETC_COST_tab3(i)
, T1.SUB_PRJ_EQPMT_ETC_COST4=l_SUB_PRJ_EQPMT_ETC_COST_tab4(i)
, T1.SUB_PRJ_EQPMT_ETC_COST5=l_SUB_PRJ_EQPMT_ETC_COST_tab5(i)
, T1.SUB_PRJ_EQPMT_ETC_COST6=l_SUB_PRJ_EQPMT_ETC_COST_tab6(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT1=l_SUB_PRJ_PPL_ETC_EFFORT_tab1(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT2=l_SUB_PRJ_PPL_ETC_EFFORT_tab2(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT3=l_SUB_PRJ_PPL_ETC_EFFORT_tab3(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT4=l_SUB_PRJ_PPL_ETC_EFFORT_tab4(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT5=l_SUB_PRJ_PPL_ETC_EFFORT_tab5(i)
, T1.SUB_PRJ_PPL_ETC_EFFORT6=l_SUB_PRJ_PPL_ETC_EFFORT_tab6(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT1=L_SP_EQPMT_ETC_EFFORT_TAB1(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT2=L_SP_EQPMT_ETC_EFFORT_TAB2(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT3=L_SP_EQPMT_ETC_EFFORT_TAB3(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT4=L_SP_EQPMT_ETC_EFFORT_TAB4(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT5=L_SP_EQPMT_ETC_EFFORT_TAB5(i)
, T1.SUB_PRJ_EQPMT_ETC_EFFORT6=L_SP_EQPMT_ETC_EFFORT_TAB6(i)
WHERE T1.Process_Number = l_Process_Number
AND T1.object_id = l_object_ids_tab(i)
AND T1.object_type = l_object_types_tab(i)
;
END IF; -- l_update_required = 'Y' THEN
-- Bug 4289748 : removed rollback, used delete instead
--ROLLBACK TO SAVEPOINT GENERATE_SCHEDULE_SP;
delete from PA_PROJ_ROLLUP_BULK_TEMP where process_number = l_process_number;