eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_SCHEDULE_OBJECTS_PVT dependencies on PA_PROJ_ROLLUP_BULK_TEMP

Line 707: SELECT /+ index(PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_U1) / --added hint for bug 13109678

703: dirty_flag6_tmp VARCHAR2(1);
704: /* Ends Added for the bug#6185523 */
705:
706: CURSOR Processed_Data_Structure(C_Process_Number NUMBER) IS
707: SELECT /*+ index(PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_U1) */ --added hint for bug 13109678
708: OBJECT_TYPE,
709: OBJECT_ID,
710: PARENT_OBJECT_TYPE,
711: PARENT_OBJECT_ID,

Line 879: FROM PA_PROJ_ROLLUP_BULK_TEMP

875: PERC_COMP_DERIVATIVE_CODE4,
876: PERC_COMP_DERIVATIVE_CODE5,
877: PERC_COMP_DERIVATIVE_CODE6,
878: SUMMARY_OBJECT_FLAG -- 4370746
879: FROM PA_PROJ_ROLLUP_BULK_TEMP
880: WHERE PROCESS_NUMBER = C_Process_Number
881: order by Object_Type, Object_ID;
882:
883: -- Bug 4218507 : Added Processed_Data_Structure_rollup

Line 885: SELECT /+ index(PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_U1) / --added hint for bug 13109678

881: order by Object_Type, Object_ID;
882:
883: -- Bug 4218507 : Added Processed_Data_Structure_rollup
884: CURSOR Processed_Data_Structure_rol(C_Process_Number NUMBER) IS
885: SELECT /*+ index(PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_U1) */ --added hint for bug 13109678
886: OBJECT_TYPE,
887: OBJECT_ID,
888: PARENT_OBJECT_TYPE,
889: PARENT_OBJECT_ID,

Line 1057: FROM PA_PROJ_ROLLUP_BULK_TEMP

1053: PERC_COMP_DERIVATIVE_CODE4,
1054: PERC_COMP_DERIVATIVE_CODE5,
1055: PERC_COMP_DERIVATIVE_CODE6,
1056: SUMMARY_OBJECT_FLAG -- 4370746
1057: FROM PA_PROJ_ROLLUP_BULK_TEMP
1058: WHERE PROCESS_NUMBER = C_Process_Number
1059: order by calendar_id;
1060:
1061: CURSOR lowest_dirty_object_list_dates IS

Line 1067: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1063: start_date4, start_date5, start_date6, finish_date1,finish_date2, finish_date3,
1064: finish_date4, finish_date5, finish_date6, duration1, duration2, duration3, duration4,
1065: duration5, duration6, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
1066: , 'N', 'N', 'N', 'N', 'N', 'N'
1067: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1068: WHERE PROCESS_NUMBER = l_process_number
1069: -- 4218507 : This could be made dependenct on dirty_flag1='Y' only. Not sure whethere this will improve performance
1070: AND (dirty_flag1='Y' OR dirty_flag2='Y' OR dirty_flag3='Y' OR dirty_flag4='Y' OR dirty_flag5='Y'
1071: OR dirty_flag6='Y')

Line 1072: AND Not Exists (select * from PA_PROJ_ROLLUP_BULK_TEMP t2

1068: WHERE PROCESS_NUMBER = l_process_number
1069: -- 4218507 : This could be made dependenct on dirty_flag1='Y' only. Not sure whethere this will improve performance
1070: AND (dirty_flag1='Y' OR dirty_flag2='Y' OR dirty_flag3='Y' OR dirty_flag4='Y' OR dirty_flag5='Y'
1071: OR dirty_flag6='Y')
1072: AND Not Exists (select * from PA_PROJ_ROLLUP_BULK_TEMP t2
1073: where t2.PROCESS_NUMBER = l_process_number
1074: and t2.parent_object_id = t1.object_id
1075: AND ROWNUM < 2);
1076:

Line 1084: FROM PA_PROJ_ROLLUP_BULK_TEMP

1080: finish_date4, finish_date5, finish_date6, duration1, duration2, duration3, duration4,
1081: duration5, duration6, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6,
1082: start_date_override1, start_date_override2, start_date_override3, start_date_override4,
1083: start_date_override5, start_date_override6
1084: FROM PA_PROJ_ROLLUP_BULK_TEMP
1085: WHERE PROCESS_NUMBER = l_process_number
1086: -- 4218507 : This could be made dependent on dirty_flag1=Y only
1087: AND (dirty_flag1='Y' OR dirty_flag2='Y' OR dirty_flag3='Y' OR dirty_flag4='Y' OR dirty_flag5='Y' OR
1088: dirty_flag6='Y')

Line 1098: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1094:
1095: CURSOR Parent_Objects_List_Effort IS
1096: SELECT object_id, object_type,REMAINING_EFFORT1,REMAINING_EFFORT2, REMAINING_EFFORT3, REMAINING_EFFORT4
1097: , REMAINING_EFFORT5, REMAINING_EFFORT6
1098: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1099: WHERE process_number = l_process_number
1100: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1101: AND Exists (SELECT * from PA_PROJ_ROLLUP_BULK_TEMP t2
1102: WHERE t2.OBJECT_TYPE <> 'PA_DELIVERABLES'

Line 1101: AND Exists (SELECT * from PA_PROJ_ROLLUP_BULK_TEMP t2

1097: , REMAINING_EFFORT5, REMAINING_EFFORT6
1098: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1099: WHERE process_number = l_process_number
1100: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1101: AND Exists (SELECT * from PA_PROJ_ROLLUP_BULK_TEMP t2
1102: WHERE t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
1103: AND t2.parent_object_id = t1.object_id
1104: AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
1105: AND process_number = l_process_number

Line 1111: SELECT /+ INDEX(T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / Object_ID, object_type, PROGRESS_STATUS_WEIGHT1, PROGRESS_STATUS_WEIGHT2, PROGRESS_STATUS_WEIGHT3 -- Added for Bug14040849

1107: Order By WBS_LEVEL DESC;
1108:
1109: -- 4366733 : Deliverable Progress Status should not rollup
1110: CURSOR Parent_Objects_List_prog_sts IS
1111: SELECT /*+ INDEX(T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ Object_ID, object_type, PROGRESS_STATUS_WEIGHT1, PROGRESS_STATUS_WEIGHT2, PROGRESS_STATUS_WEIGHT3 -- Added for Bug14040849
1112: , PROGRESS_STATUS_WEIGHT4, PROGRESS_STATUS_WEIGHT5, PROGRESS_STATUS_WEIGHT6
1113: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1114: --WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES')
1115: WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')

Line 1113: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1109: -- 4366733 : Deliverable Progress Status should not rollup
1110: CURSOR Parent_Objects_List_prog_sts IS
1111: SELECT /*+ INDEX(T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ Object_ID, object_type, PROGRESS_STATUS_WEIGHT1, PROGRESS_STATUS_WEIGHT2, PROGRESS_STATUS_WEIGHT3 -- Added for Bug14040849
1112: , PROGRESS_STATUS_WEIGHT4, PROGRESS_STATUS_WEIGHT5, PROGRESS_STATUS_WEIGHT6
1113: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1114: --WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES')
1115: WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1116: AND process_number = l_process_number
1117: AND Exists (Select /*+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) */ * From PA_PROJ_ROLLUP_BULK_TEMP t2 --14040849

Line 1117: AND Exists (Select /+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) / * From PA_PROJ_ROLLUP_BULK_TEMP t2 --14040849

1113: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1114: --WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES')
1115: WHERE OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1116: AND process_number = l_process_number
1117: AND Exists (Select /*+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) */ * From PA_PROJ_ROLLUP_BULK_TEMP t2 --14040849
1118: -- Where t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES', 'PA_SUBPROJECTS')
1119: Where t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_SUBPROJECTS')
1120: AND t2.parent_object_id = t1.object_id
1121: AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 1128: SELECT /+ INDEX (t1 PA_PROJ_ROLLUP_BULK_TEMP_N3)/ --9796902

1124: Order By WBS_LEVEL DESC;
1125:
1126:
1127: CURSOR parent_objects_list_task_sts IS
1128: SELECT /*+ INDEX (t1 PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
1129: object_id, object_type, task_status1, task_status2, task_status3, task_status4, task_status5, task_status6
1130: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1131: WHERE PROCESS_NUMBER = l_process_number
1132: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'

Line 1130: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1126:
1127: CURSOR parent_objects_list_task_sts IS
1128: SELECT /*+ INDEX (t1 PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
1129: object_id, object_type, task_status1, task_status2, task_status3, task_status4, task_status5, task_status6
1130: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1131: WHERE PROCESS_NUMBER = l_process_number
1132: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
1133: AND Exists (SELECT /*+ INDEX (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
1134: * From PA_PROJ_ROLLUP_BULK_TEMP t2

Line 1133: AND Exists (SELECT /+ INDEX (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3)/ --9796902

1129: object_id, object_type, task_status1, task_status2, task_status3, task_status4, task_status5, task_status6
1130: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1131: WHERE PROCESS_NUMBER = l_process_number
1132: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
1133: AND Exists (SELECT /*+ INDEX (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
1134: * From PA_PROJ_ROLLUP_BULK_TEMP t2
1135: Where t2.PROCESS_NUMBER = l_process_number
1136: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
1137: AND t2.parent_object_id = t1.object_id

Line 1134: * From PA_PROJ_ROLLUP_BULK_TEMP t2

1130: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1131: WHERE PROCESS_NUMBER = l_process_number
1132: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
1133: AND Exists (SELECT /*+ INDEX (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
1134: * From PA_PROJ_ROLLUP_BULK_TEMP t2
1135: Where t2.PROCESS_NUMBER = l_process_number
1136: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
1137: AND t2.parent_object_id = t1.object_id
1138: AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 1149: FROM PA_PROJ_ROLLUP_BULK_TEMP

1145: , decode(rollup_node3, 'Y', TASK_STATUS3, -1)
1146: , decode(rollup_node4, 'Y', TASK_STATUS4, -1)
1147: , decode(rollup_node5, 'Y', TASK_STATUS5, -1)
1148: , decode(rollup_node6, 'Y', TASK_STATUS6, -1)
1149: FROM PA_PROJ_ROLLUP_BULK_TEMP
1150: WHERE PROCESS_NUMBER = l_process_number
1151: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
1152: AND parent_object_id = p_parent_id
1153: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 1157: SELECT /+ INDEX (PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3)/ --9796902

1153: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
1154: ;
1155:
1156: CURSOR Child_Task_Status_full(p_parent_id NUMBER) IS
1157: SELECT /*+ INDEX (PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
1158: TASK_STATUS1, TASK_STATUS2, TASK_STATUS3, TASK_STATUS4, TASK_STATUS5, TASK_STATUS6
1159: FROM PA_PROJ_ROLLUP_BULK_TEMP
1160: WHERE PROCESS_NUMBER = l_process_number
1161: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'

Line 1159: FROM PA_PROJ_ROLLUP_BULK_TEMP

1155:
1156: CURSOR Child_Task_Status_full(p_parent_id NUMBER) IS
1157: SELECT /*+ INDEX (PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
1158: TASK_STATUS1, TASK_STATUS2, TASK_STATUS3, TASK_STATUS4, TASK_STATUS5, TASK_STATUS6
1159: FROM PA_PROJ_ROLLUP_BULK_TEMP
1160: WHERE PROCESS_NUMBER = l_process_number
1161: AND OBJECT_TYPE <> 'PA_ASSIGNMENTS'
1162: AND parent_object_id = p_parent_id
1163: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 1168: SELECT /+ INDEX(T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / object_id, OBJECT_TYPE, Perc_Comp_Derivative_Code1, BAC_VALUE1, BAC_VALUE2, BAC_VALUE3, BAC_VALUE4, -- Added for 14040849

1164: ;
1165:
1166: -- Perc_Comp_Derivative_Code1 serves purpose for all 6 sets
1167: CURSOR lowest_tasks_per_comp IS
1168: SELECT /*+ INDEX(T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ object_id, OBJECT_TYPE, Perc_Comp_Derivative_Code1, BAC_VALUE1, BAC_VALUE2, BAC_VALUE3, BAC_VALUE4, -- Added for 14040849
1169: BAC_VALUE5, BAC_VALUE6, PERCENT_COMPLETE1, PERCENT_COMPLETE2, PERCENT_COMPLETE3,
1170: PERCENT_COMPLETE4, PERCENT_COMPLETE5, PERCENT_COMPLETE6, PERCENT_OVERRIDE1, PERCENT_OVERRIDE2,
1171: PERCENT_OVERRIDE3, PERCENT_OVERRIDE4, PERCENT_OVERRIDE5, PERCENT_OVERRIDE6, EARNED_VALUE1, EARNED_VALUE2
1172: , EARNED_VALUE3, EARNED_VALUE4, EARNED_VALUE5, EARNED_VALUE6

Line 1173: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1169: BAC_VALUE5, BAC_VALUE6, PERCENT_COMPLETE1, PERCENT_COMPLETE2, PERCENT_COMPLETE3,
1170: PERCENT_COMPLETE4, PERCENT_COMPLETE5, PERCENT_COMPLETE6, PERCENT_OVERRIDE1, PERCENT_OVERRIDE2,
1171: PERCENT_OVERRIDE3, PERCENT_OVERRIDE4, PERCENT_OVERRIDE5, PERCENT_OVERRIDE6, EARNED_VALUE1, EARNED_VALUE2
1172: , EARNED_VALUE3, EARNED_VALUE4, EARNED_VALUE5, EARNED_VALUE6
1173: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1174: WHERE PROCESS_NUMBER = l_process_number
1175: AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1176: AND nvl(SUMMARY_OBJECT_FLAG, 'N') NOT IN ('Y', 'L') -- 4370746 -- 4586449 : Added 'L'
1177: AND not Exists (Select /*+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) */ * From PA_PROJ_ROLLUP_BULK_TEMP t2 --Added for Bug14040849

Line 1177: AND not Exists (Select /+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) / * From PA_PROJ_ROLLUP_BULK_TEMP t2 --Added for Bug14040849

1173: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1174: WHERE PROCESS_NUMBER = l_process_number
1175: AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1176: AND nvl(SUMMARY_OBJECT_FLAG, 'N') NOT IN ('Y', 'L') -- 4370746 -- 4586449 : Added 'L'
1177: AND not Exists (Select /*+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) */ * From PA_PROJ_ROLLUP_BULK_TEMP t2 --Added for Bug14040849
1178: Where t2.PROCESS_NUMBER = l_process_number
1179: AND t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1180: And t2.parent_object_id = t1.object_id
1181: AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 1185: SELECT /+ INDEX(T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / object_id, OBJECT_TYPE, Perc_Comp_Derivative_Code1, BAC_VALUE1, BAC_VALUE2, BAC_VALUE3, BAC_VALUE4, -- Added for 14040849

1181: AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
1182: AND ROWNUM < 2);
1183:
1184: CURSOR parent_objects_list_per_comp IS
1185: SELECT /*+ INDEX(T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ object_id, OBJECT_TYPE, Perc_Comp_Derivative_Code1, BAC_VALUE1, BAC_VALUE2, BAC_VALUE3, BAC_VALUE4, -- Added for 14040849
1186: BAC_VALUE5, BAC_VALUE6, PERCENT_COMPLETE1, PERCENT_COMPLETE2, PERCENT_COMPLETE3,
1187: PERCENT_COMPLETE4, PERCENT_COMPLETE5, PERCENT_COMPLETE6, PERCENT_OVERRIDE1, PERCENT_OVERRIDE2,
1188: PERCENT_OVERRIDE3, PERCENT_OVERRIDE4, PERCENT_OVERRIDE5, PERCENT_OVERRIDE6, EARNED_VALUE1, EARNED_VALUE2
1189: , EARNED_VALUE3, EARNED_VALUE4, EARNED_VALUE5, EARNED_VALUE6

Line 1191: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1187: PERCENT_COMPLETE4, PERCENT_COMPLETE5, PERCENT_COMPLETE6, PERCENT_OVERRIDE1, PERCENT_OVERRIDE2,
1188: PERCENT_OVERRIDE3, PERCENT_OVERRIDE4, PERCENT_OVERRIDE5, PERCENT_OVERRIDE6, EARNED_VALUE1, EARNED_VALUE2
1189: , EARNED_VALUE3, EARNED_VALUE4, EARNED_VALUE5, EARNED_VALUE6
1190: , summary_object_flag -- 4587517
1191: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1192: WHERE PROCESS_NUMBER = l_process_number
1193: AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1194: -- 4530036 : SUMMARY_OBJECT_FLAG is added so that
1195: -- earned value gets recalculated even if no childs are passed to summary level

Line 1197: OR Exists (Select /+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) / * From PA_PROJ_ROLLUP_BULK_TEMP t2 -- Added for 14040849

1193: AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1194: -- 4530036 : SUMMARY_OBJECT_FLAG is added so that
1195: -- earned value gets recalculated even if no childs are passed to summary level
1196: AND( nvl(SUMMARY_OBJECT_FLAG, 'N') IN ('Y','L') -- 4586449 : Added 'L'
1197: OR Exists (Select /*+ index(t2 PA_PROJ_ROLLUP_BULK_TEMP_N3 ) */ * From PA_PROJ_ROLLUP_BULK_TEMP t2 -- Added for 14040849
1198: Where t2.PROCESS_NUMBER = l_process_number
1199: AND t2.OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES')
1200: And t2.parent_object_id = t1.object_id
1201: AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 1218: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1214: , 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
1215: , 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
1216: , 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
1217: , REMAINING_EFFORT1, REMAINING_EFFORT2, REMAINING_EFFORT3, REMAINING_EFFORT4, REMAINING_EFFORT5, REMAINING_EFFORT6
1218: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1219: WHERE PROCESS_NUMBER = l_process_number
1220: AND OBJECT_TYPE NOT IN ( 'PA_DELIVERABLES', 'PA_ASSIGNMENTS', 'PA_SUBPROJECTS')
1221: AND Exists (SELECT /*+ index(T2 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ * -- added hint for bug#12945266
1222: from PA_PROJ_ROLLUP_BULK_TEMP t2

Line 1221: AND Exists (SELECT /+ index(T2 PA_PROJ_ROLLUP_BULK_TEMP_N3) / * -- added hint for bug#12945266

1217: , REMAINING_EFFORT1, REMAINING_EFFORT2, REMAINING_EFFORT3, REMAINING_EFFORT4, REMAINING_EFFORT5, REMAINING_EFFORT6
1218: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1219: WHERE PROCESS_NUMBER = l_process_number
1220: AND OBJECT_TYPE NOT IN ( 'PA_DELIVERABLES', 'PA_ASSIGNMENTS', 'PA_SUBPROJECTS')
1221: AND Exists (SELECT /*+ index(T2 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ * -- added hint for bug#12945266
1222: from PA_PROJ_ROLLUP_BULK_TEMP t2
1223: WHERE t2.PROCESS_NUMBER = l_process_number
1224: AND t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
1225: AND t2.parent_object_id = t1.object_id

Line 1222: from PA_PROJ_ROLLUP_BULK_TEMP t2

1218: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1219: WHERE PROCESS_NUMBER = l_process_number
1220: AND OBJECT_TYPE NOT IN ( 'PA_DELIVERABLES', 'PA_ASSIGNMENTS', 'PA_SUBPROJECTS')
1221: AND Exists (SELECT /*+ index(T2 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ * -- added hint for bug#12945266
1222: from PA_PROJ_ROLLUP_BULK_TEMP t2
1223: WHERE t2.PROCESS_NUMBER = l_process_number
1224: AND t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
1225: AND t2.parent_object_id = t1.object_id
1226: AND t2.parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 1231: SELECT /+ index(t1 PA_PROJ_ROLLUP_BULK_TEMP_N3) / -- added hint for bug 13109678

1227: AND ROWNUM < 2)
1228: Order By WBS_LEVEL DESC;
1229:
1230: CURSOR all_dirty_object_list_summary IS
1231: SELECT /*+ index(t1 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ -- added hint for bug 13109678
1232: object_id, object_type, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
1233: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1234: WHERE PROCESS_NUMBER = l_process_number
1235: AND OBJECT_TYPE <> 'PA_DELIVERABLES'

Line 1233: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1229:
1230: CURSOR all_dirty_object_list_summary IS
1231: SELECT /*+ index(t1 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ -- added hint for bug 13109678
1232: object_id, object_type, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
1233: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1234: WHERE PROCESS_NUMBER = l_process_number
1235: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1236: AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
1237: OR Dirty_flag6 ='Y')

Line 1238: AND Exists (select /+ index (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3) / 1 -- added hint for bug 13109678

1234: WHERE PROCESS_NUMBER = l_process_number
1235: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1236: AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
1237: OR Dirty_flag6 ='Y')
1238: AND Exists (select /*+ index (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ 1 -- added hint for bug 13109678
1239: from PA_PROJ_ROLLUP_BULK_TEMP t2
1240: where t2.PROCESS_NUMBER = l_process_number
1241: and t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
1242: and t2.parent_object_id = t1.object_id

Line 1239: from PA_PROJ_ROLLUP_BULK_TEMP t2

1235: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1236: AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
1237: OR Dirty_flag6 ='Y')
1238: AND Exists (select /*+ index (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ 1 -- added hint for bug 13109678
1239: from PA_PROJ_ROLLUP_BULK_TEMP t2
1240: where t2.PROCESS_NUMBER = l_process_number
1241: and t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
1242: and t2.parent_object_id = t1.object_id
1243: -- AND ROWNUM < 2 -- commented for bug 13109678

Line 1256: FROM PA_PROJ_ROLLUP_BULK_TEMP T2

1252: , c_dirty_flag5 ref_dirty_flag5, c_dirty_flag6 ref_dirty_flag6
1253: FROM
1254: (SELECT t2.Object_ID, t2.object_type, t2.dirty_flag1, t2.dirty_flag2, t2.dirty_flag3
1255: , t2.dirty_flag4, t2.dirty_flag5, t2.dirty_flag6,t2.wbs_level order_by_clause
1256: FROM PA_PROJ_ROLLUP_BULK_TEMP T2
1257: WHERE T2.PROCESS_NUMBER = l_process_number
1258: AND T2.Object_ID <> c_object_id
1259: START WITH T2.PROCESS_NUMBER = l_process_number
1260: AND T2.Object_ID = c_object_id

Line 1266: SELECT /+ index(t1 PA_PROJ_ROLLUP_BULK_TEMP_N3) / -- added hint for bug 13109678

1262: AND T2.Parent_Object_ID = PRIOR T2.Object_ID) t
1263: order by order_by_clause desc;
1264:
1265: CURSOR all_dirty_object_list_lowest IS
1266: SELECT /*+ index(t1 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ -- added hint for bug 13109678
1267: object_id, object_type, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
1268: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1269: WHERE PROCESS_NUMBER = l_process_number
1270: AND OBJECT_TYPE <> 'PA_DELIVERABLES'

Line 1268: FROM PA_PROJ_ROLLUP_BULK_TEMP t1

1264:
1265: CURSOR all_dirty_object_list_lowest IS
1266: SELECT /*+ index(t1 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ -- added hint for bug 13109678
1267: object_id, object_type, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6
1268: FROM PA_PROJ_ROLLUP_BULK_TEMP t1
1269: WHERE PROCESS_NUMBER = l_process_number
1270: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1271: AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
1272: OR Dirty_flag6 ='Y')

Line 1273: AND NOT Exists (select /+ index (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3) / 1 -- added hint for bug 13109678

1269: WHERE PROCESS_NUMBER = l_process_number
1270: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1271: AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
1272: OR Dirty_flag6 ='Y')
1273: AND NOT Exists (select /*+ index (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ 1 -- added hint for bug 13109678
1274: from PA_PROJ_ROLLUP_BULK_TEMP t2
1275: where t2.PROCESS_NUMBER = l_process_number
1276: and t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
1277: and t2.parent_object_id = t1.object_id

Line 1274: from PA_PROJ_ROLLUP_BULK_TEMP t2

1270: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
1271: AND (Dirty_flag1 ='Y' OR Dirty_flag2 ='Y' OR Dirty_flag3 ='Y' OR Dirty_flag4 ='Y' OR Dirty_flag5 ='Y'
1272: OR Dirty_flag6 ='Y')
1273: AND NOT Exists (select /*+ index (t2 PA_PROJ_ROLLUP_BULK_TEMP_N3) */ 1 -- added hint for bug 13109678
1274: from PA_PROJ_ROLLUP_BULK_TEMP t2
1275: where t2.PROCESS_NUMBER = l_process_number
1276: and t2.OBJECT_TYPE <> 'PA_DELIVERABLES'
1277: and t2.parent_object_id = t1.object_id
1278: -- AND ROWNUM < 2 -- commented for bug 13109678

Line 1291: FROM PA_PROJ_ROLLUP_BULK_TEMP T2

1287: , c_dirty_flag5 ref_dirty_flag5, c_dirty_flag6 ref_dirty_flag6
1288: FROM
1289: ( SELECT /*+ dynamic_sampling(t2 3) */ t2.Object_ID, t2.object_type, t2.dirty_flag1, t2.dirty_flag2, t2.dirty_flag3 -- Added for 14040849
1290: , t2.dirty_flag4, t2.dirty_flag5, t2.dirty_flag6, t2.wbs_level order_by_clause
1291: FROM PA_PROJ_ROLLUP_BULK_TEMP T2
1292: WHERE T2.PROCESS_NUMBER = l_process_number
1293: AND T2.Object_ID <> c_object_id
1294: START WITH T2.PROCESS_NUMBER = l_process_number
1295: AND T2.Object_ID = c_object_id

Line 1302: SELECT /+ index(pa_proj_rollup_bulk_temp PA_PROJ_ROLLUP_BULK_TEMP_N3) / -- added hint for bug 13109678

1298: order by t.order_by_clause asc;
1299:
1300:
1301: CURSOR dirty_all_List IS
1302: SELECT /*+ index(pa_proj_rollup_bulk_temp PA_PROJ_ROLLUP_BULK_TEMP_N3) */ -- added hint for bug 13109678
1303: object_id, object_type, start_date1, start_date2, start_date3,
1304: start_date4, start_date5, start_date6, finish_date1,finish_date2, finish_date3,
1305: finish_date4, finish_date5, finish_date6, duration1, duration2, duration3, duration4,
1306: duration5, duration6, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6,

Line 1309: FROM PA_PROJ_ROLLUP_BULK_TEMP

1305: finish_date4, finish_date5, finish_date6, duration1, duration2, duration3, duration4,
1306: duration5, duration6, dirty_flag1, dirty_flag2, dirty_flag3, dirty_flag4, dirty_flag5, dirty_flag6,
1307: start_date_override1, start_date_override2, start_date_override3, start_date_override4,
1308: start_date_override5, start_date_override6, 'N', 'N', 'N', 'N', 'N', 'N' , 'N', 'N', 'N', 'N', 'N', 'N'
1309: FROM PA_PROJ_ROLLUP_BULK_TEMP
1310: WHERE PROCESS_NUMBER = l_process_number
1311: AND (dirty_flag1='Y' OR dirty_flag2='Y' OR dirty_flag3='Y' OR dirty_flag4='Y' OR dirty_flag5='Y' OR
1312: dirty_flag6='Y')
1313: order by wbs_level desc;

Line 1331: SELECT PA_PROJ_ROLLUP_BULK_TEMP_S.nextval

1327:
1328: SAVEPOINT GENERATE_SCHEDULE_SP;
1329:
1330: -- Derive the next Process Number
1331: SELECT PA_PROJ_ROLLUP_BULK_TEMP_S.nextval
1332: INTO l_Process_Number FROM dual;
1333:
1334: x_Process_Number := l_Process_Number;
1335:

Line 1723: INSERT INTO PA_PROJ_ROLLUP_BULK_TEMP(

1719: pa_debug.write(x_Module=>'PA_SCHEDULE_OBJECTS_PVT.GENERATE_SCHEDULE', x_Msg => 'After building the table', x_Log_Level=> 3);
1720: END IF;
1721:
1722: FORALL i IN 1..P_Data_Structure.COUNT
1723: INSERT INTO PA_PROJ_ROLLUP_BULK_TEMP(
1724: PROCESS_NUMBER,
1725: OBJECT_TYPE,
1726: OBJECT_ID,
1727: PARENT_OBJECT_TYPE,

Line 2215: UPDATE /+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP T1

2211:
2212:
2213: FORALL i in 1..l_object_ids_tab.count
2214: /* Added the hint to force the unique index for bug#6185523 */
2215: UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
2216: SET T1.DIRTY_FLAG1=l_dirty_flag_tab1(i)
2217: , T1.DIRTY_FLAG2=l_dirty_flag_tab2(i)
2218: , T1.DIRTY_FLAG3=l_dirty_flag_tab3(i)
2219: , T1.DIRTY_FLAG4=l_dirty_flag_tab4(i)

Line 2352: UPDATE /+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP T1

2348: END LOOP; -- FOR i IN 1..l_temp_object_ids_tab.count LOOP
2349:
2350: FORALL i in 1..l_object_ids_tab.count
2351: /* Added the hint to force the unique index for bug#6185523 */
2352: UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
2353: SET T1.DIRTY_FLAG1=l_dirty_flag_tab1(i)
2354: , T1.DIRTY_FLAG2=l_dirty_flag_tab2(i)
2355: , T1.DIRTY_FLAG3=l_dirty_flag_tab3(i)
2356: , T1.DIRTY_FLAG4=l_dirty_flag_tab4(i)

Line 2512: SELECT /+ index(PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3 ) / --Added hint for bug 12945266

2508:
2509: IF l_partial_rollup1 OR l_partial_rollup2 OR l_partial_rollup3
2510: OR l_partial_rollup4 OR l_partial_rollup5 OR l_partial_rollup6
2511: THEN
2512: SELECT /*+ index(PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3 ) */ --Added hint for bug 12945266
2513: MIN(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', start_date1, null), start_date1))
2514: , MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', finish_date1, null), finish_date1))
2515: , MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', DECODE(finish_date1,NULL,1,0), 0), 0))
2516: , MAX(decode(p_partial_dates_flag1, 'Y', decode(rollup_node1, 'Y', 1, 0), 1))

Line 2564: FROM PA_PROJ_ROLLUP_BULK_TEMP

2560: , l_new_completion_date6
2561: , l_null_flag6
2562: , l_task_count6
2563: , l_task_flag
2564: FROM PA_PROJ_ROLLUP_BULK_TEMP
2565: WHERE PROCESS_NUMBER = l_process_number
2566: AND parent_object_id = l_parent_object_ids_tab(j)
2567: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
2568: ;

Line 2570: SELECT /+ INDEX (PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3)/ --9796902

2566: AND parent_object_id = l_parent_object_ids_tab(j)
2567: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
2568: ;
2569: ELSE
2570: SELECT /*+ INDEX (PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3)*/ --9796902
2571: MIN(start_date1)
2572: , MAX(finish_date1)
2573: , MAX(DECODE(finish_date1,NULL,1,0))
2574: , MAX(1)

Line 2622: FROM PA_PROJ_ROLLUP_BULK_TEMP

2618: , l_new_completion_date6
2619: , l_null_flag6
2620: , l_task_count6
2621: , l_task_flag
2622: FROM PA_PROJ_ROLLUP_BULK_TEMP
2623: WHERE PROCESS_NUMBER = l_process_number
2624: AND parent_object_id = l_parent_object_ids_tab(j)
2625: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587
2626: ;

Line 2632: /+ INDEX (PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3)/

2628: --and child tasks actual finish date to summary task even though any of the resource assignment is not having actual finish date
2629:
2630: IF l_null_flag1 =1 OR l_null_flag2=1 THEN
2631: SELECT
2632: /*+ INDEX (PA_PROJ_ROLLUP_BULK_TEMP PA_PROJ_ROLLUP_BULK_TEMP_N3)*/
2633: MAX(DECODE(finish_date1,NULL,1,0)),
2634: MAX(DECODE(finish_date2,NULL,1,0))
2635: INTO l_null_flag1,
2636: l_null_flag2

Line 2637: FROM PA_PROJ_ROLLUP_BULK_TEMP

2633: MAX(DECODE(finish_date1,NULL,1,0)),
2634: MAX(DECODE(finish_date2,NULL,1,0))
2635: INTO l_null_flag1,
2636: l_null_flag2
2637: FROM PA_PROJ_ROLLUP_BULK_TEMP
2638: WHERE PROCESS_NUMBER = l_process_number
2639: AND parent_object_id = l_parent_object_ids_tab(j)
2640: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS')
2641: AND object_type <> 'PA_ASSIGNMENTS';

Line 3120: UPDATE + INDEX( SchTmp PA_PROJ_ROLLUP_BULK_TEMP_U1) PA_PROJ_ROLLUP_BULK_TEMP SchTmp SET

3116: /* Ends Added the following logic for bug#6185523 */
3117:
3118: IF l_parent_update_required = 'Y' THEN
3119: /* Commented the following update statement and introduced a new one for bug#6185523
3120: UPDATE + INDEX( SchTmp PA_PROJ_ROLLUP_BULK_TEMP_U1) PA_PROJ_ROLLUP_BULK_TEMP SchTmp SET
3121: 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)
3122: , 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)
3123: , duration1 = decode(l_update_requ_flag_tab1(j), 'Y', decode(l_update_date_flag_tab1(j), 'Y', l_parent_duration_tab1(j), duration1), duration1)
3124: , 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)

Line 3150: UPDATE /+ INDEX( SchTmp PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP SchTmp SET

3146: AND object_type = l_parent_object_types_tab(j)
3147: and process_number = l_process_number;
3148: Ends commented code for bug#6185523 And added the below update*/
3149:
3150: UPDATE /*+ INDEX( SchTmp PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP SchTmp SET
3151: start_date1 = nvl(l_parent_start_date1_tmp, start_date1)
3152: , finish_date1 = nvl(l_parent_finish_date1_tmp, finish_date1)
3153: , duration1 = nvl(l_parent_duration1_tmp, duration1)
3154: , start_date2 = nvl(l_parent_start_date2_tmp, start_date2)

Line 3455: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1

3451: END LOOP; -- FOR i IN 1..l_child_object_ids_tab.count LOOP
3452:
3453: IF l_child_update_required = 'Y' THEN
3454: FORALL i in 1..l_child_object_ids_tab.count
3455: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
3456: SET T1.start_date1=decode(l_child_upd_req_flag_tab1(i), 'Y', l_child_start_date_tab1(i), T1.start_date1)
3457: , T1.start_date2=decode(l_child_upd_req_flag_tab2(i), 'Y', l_child_start_date_tab2(i), T1.start_date2)
3458: , T1.start_date3=decode(l_child_upd_req_flag_tab3(i), 'Y', l_child_start_date_tab3(i), T1.start_date3)
3459: , T1.start_date4=decode(l_child_upd_req_flag_tab4(i), 'Y', l_child_start_date_tab4(i), T1.start_date4)

Line 3581: FROM PA_PROJ_ROLLUP_BULK_TEMP

3577: , MAX(decode(p_partial_dates_flag4, 'Y',decode(rollup_node4, 'Y', decode(dirty_flag4,'Y',1,0), 0),decode(dirty_flag4,'Y',1,0)))
3578: , MAX(decode(p_partial_dates_flag5, 'Y',decode(rollup_node5, 'Y', decode(dirty_flag5,'Y',1,0), 0),decode(dirty_flag5,'Y',1,0)))
3579: , MAX(decode(p_partial_dates_flag6, 'Y',decode(rollup_node6, 'Y', decode(dirty_flag6,'Y',1,0), 0),decode(dirty_flag6,'Y',1,0)))
3580: INTO l_count1, l_count2, l_count3, l_count4, l_count5, l_count6
3581: FROM PA_PROJ_ROLLUP_BULK_TEMP
3582: WHERE PROCESS_NUMBER = l_process_number
3583: AND parent_object_id = l_parent_object_ids_tab(j)
3584: AND (DIRTY_FLAG1 = 'Y' OR DIRTY_FLAG2 = 'Y' OR DIRTY_FLAG3 = 'Y'
3585: OR DIRTY_FLAG4 = 'Y' OR DIRTY_FLAG5 = 'Y' OR DIRTY_FLAG6 = 'Y')

Line 3748: FROM PA_PROJ_ROLLUP_BULK_TEMP

3744: , l_new_completion_date6(l_counter)
3745: , l_null_flag6
3746: , l_task_count6
3747: , l_task_flag
3748: FROM PA_PROJ_ROLLUP_BULK_TEMP
3749: WHERE PROCESS_NUMBER = l_process_number
3750: AND parent_object_id = l_parent_object_ids_tab(j);
3751: EXCEPTION
3752: WHEN OTHERS THEN

Line 3956: UPDATE PA_PROJ_ROLLUP_BULK_TEMP SET

3952: -- 2. Update at each node level in database and do not do bulk update.
3953: -- Currently implementing this approach as it seems more performant.
3954:
3955: IF l_parent_update_required = 'Y' THEN
3956: UPDATE PA_PROJ_ROLLUP_BULK_TEMP SET
3957: 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)
3958: , 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)
3959: , 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)
3960: , 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)

Line 3996: UPDATE PA_PROJ_ROLLUP_BULK_TEMP SET

3992: -- Currently implementing this approach as it seems more performant.
3993: /* Do not remove
3994: IF l_parent_update_required = 'Y' THEN
3995: FORALL k in 1..l_object_ids_tab.count
3996: UPDATE PA_PROJ_ROLLUP_BULK_TEMP SET
3997: 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)
3998: , 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)
3999: , duration1 = decode(l_update_requ_flag_tab1(k), 'Y', decode(l_update_date_flag_tab1(k), 'Y', l_new_duration1(k), duration1), duration1)
4000: , 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)

Line 4097: FROM PA_PROJ_ROLLUP_BULK_TEMP

4093: , MAX(decode(rollup_node5, 'Y', nvl(PROGRESS_OVERRIDE5, nvl(PROGRESS_STATUS_WEIGHT5, -99)), -99))
4094: , MAX(decode(rollup_node6, 'Y', nvl(PROGRESS_OVERRIDE6, nvl(PROGRESS_STATUS_WEIGHT6, -99)), -99))
4095: , count(*)
4096: INTO l_weight1, l_weight2, l_weight3, l_weight4, l_weight5, l_weight6, l_count
4097: FROM PA_PROJ_ROLLUP_BULK_TEMP
4098: WHERE PROCESS_NUMBER = l_process_number
4099: AND Parent_Object_ID = l_object_ids_tab(i)
4100: --AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES', 'PA_SUBPROJECTS')
4101: -- 4366733 : Deliverable Progress Status should not rollup

Line 4113: FROM PA_PROJ_ROLLUP_BULK_TEMP

4109: , MAX(nvl(PROGRESS_OVERRIDE5, nvl(PROGRESS_STATUS_WEIGHT5, -99)))
4110: , MAX(nvl(PROGRESS_OVERRIDE6, nvl(PROGRESS_STATUS_WEIGHT6, -99)))
4111: , count(*)
4112: INTO l_weight1, l_weight2, l_weight3, l_weight4, l_weight5, l_weight6, l_count
4113: FROM PA_PROJ_ROLLUP_BULK_TEMP
4114: WHERE PROCESS_NUMBER = l_process_number
4115: AND Parent_Object_ID = l_object_ids_tab(i)
4116: --AND OBJECT_TYPE IN ('PA_TASKS', 'PA_STRUCTURES', 'PA_DELIVERABLES','PA_SUBPROJECTS')
4117: -- 4366733 : Deliverable Progress Status should not rollup

Line 4183: UPDATE /+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP T1

4179: END IF;
4180: END IF;
4181: IF l_update_required = 'Y' THEN
4182: /* Added the hint to force the unique index for bug#6185523 */
4183: UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
4184: SET T1.PROGRESS_STATUS_WEIGHT1=l_sum_tab1(i)
4185: , T1.PROGRESS_STATUS_WEIGHT2=l_sum_tab2(i)
4186: , T1.PROGRESS_STATUS_WEIGHT3=l_sum_tab3(i)
4187: , T1.PROGRESS_STATUS_WEIGHT4=l_sum_tab4(i)

Line 4200: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1

4196: -- Note that Bulk Update is not implemented due to the reasons mentioned in Date Rollup section
4197: /* Please Do no remove
4198: IF l_update_required = 'Y' THEN
4199: FORALL i in 1..l_object_ids_tab.count
4200: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
4201: SET T1.PROGRESS_STATUS_WEIGHT1=l_sum_tab1(i)
4202: , T1.PROGRESS_STATUS_WEIGHT2=l_sum_tab2(i)
4203: , T1.PROGRESS_STATUS_WEIGHT3=l_sum_tab3(i)
4204: , T1.PROGRESS_STATUS_WEIGHT4=l_sum_tab4(i)

Line 4453: UPDATE /+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP T1

4449: END IF;
4450:
4451: IF l_update_required = 'Y' THEN
4452: /* Added the hint to force the unique index for bug#6185523 */
4453: UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
4454: SET T1.TASK_STATUS1= l_sum_tab1(i)
4455: , T1.TASK_STATUS2=l_sum_tab2(i)
4456: , T1.TASK_STATUS3=l_sum_tab3(i)
4457: , T1.TASK_STATUS4=l_sum_tab4(i)

Line 4469: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1

4465: -- Note that Bulk Update is not implemented due to the reasons mentioned in Date Rollup section
4466: /* Please Do no remove
4467: IF l_update_required = 'Y' THEN
4468: FORALL i in 1..l_object_ids_tab.count
4469: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
4470: SET T1.TASK_STATUS1= l_sum_tab1(i)
4471: , T1.TASK_STATUS2=l_sum_tab2(i)
4472: , T1.TASK_STATUS3=l_sum_tab3(i)
4473: , T1.TASK_STATUS4=l_sum_tab4(i)

Line 4581: FROM PA_PROJ_ROLLUP_BULK_TEMP

4577: , l_actual_duration4, l_pc_duration4
4578: , l_actual_duration5, l_pc_duration5
4579: , l_actual_duration6, l_pc_duration6
4580: , l_count
4581: FROM PA_PROJ_ROLLUP_BULK_TEMP
4582: WHERE Process_Number = l_Process_Number
4583: AND OBJECT_TYPE = 'PA_DELIVERABLES'
4584: AND Parent_Object_ID = l_object_ids_tab(i)
4585: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 4607: FROM PA_PROJ_ROLLUP_BULK_TEMP

4603: , l_actual_duration4, l_pc_duration4
4604: , l_actual_duration5, l_pc_duration5
4605: , l_actual_duration6, l_pc_duration6
4606: , l_count
4607: FROM PA_PROJ_ROLLUP_BULK_TEMP
4608: WHERE Process_Number = l_Process_Number
4609: AND OBJECT_TYPE IN ('PA_ASSIGNMENTS', 'PA_SUBPROJECTS')
4610: AND Parent_Object_ID = l_object_ids_tab(i)
4611: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 4770: UPDATE /+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP T1

4766:
4767: IF l_update_required = 'Y' THEN
4768: FORALL i in 1..l_object_ids_tab.count
4769: /* Added the hint to force the unique index for bug#6185523 */
4770: UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
4771: SET t1.percent_complete1=l_percent_complete_tab1(i)
4772: , t1.percent_complete2=l_percent_complete_tab2(i)
4773: , t1.percent_complete3=l_percent_complete_tab3(i)
4774: , t1.percent_complete4=l_percent_complete_tab4(i)

Line 4875: FROM PA_PROJ_ROLLUP_BULK_TEMP

4871: , l_actual_duration4, l_pc_duration4
4872: , l_actual_duration5, l_pc_duration5
4873: , l_actual_duration6, l_pc_duration6
4874: , l_count
4875: FROM PA_PROJ_ROLLUP_BULK_TEMP
4876: WHERE Process_Number = l_Process_Number
4877: AND OBJECT_TYPE = 'PA_DELIVERABLES'
4878: AND Parent_Object_ID = l_object_ids_tab(i)
4879: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 4940: FROM PA_PROJ_ROLLUP_BULK_TEMP

4936: , l_actual_duration4_a, l_duration4_a
4937: , l_actual_duration5_a, l_duration5_a
4938: , l_actual_duration6_a, l_duration6_a
4939: , l_count
4940: FROM PA_PROJ_ROLLUP_BULK_TEMP
4941: WHERE Process_Number = l_Process_Number
4942: AND OBJECT_TYPE = 'PA_ASSIGNMENTS'
4943: AND Parent_Object_ID = l_object_ids_tab(i)
4944: AND parent_object_type = 'PA_TASKS'

Line 5037: FROM PA_PROJ_ROLLUP_BULK_TEMP

5033: , l_actual_duration4_t, l_duration4_t
5034: , l_actual_duration5_t, l_duration5_t
5035: , l_actual_duration6_t, l_duration6_t
5036: , l_count1
5037: FROM PA_PROJ_ROLLUP_BULK_TEMP
5038: WHERE Process_Number = l_Process_Number
5039: AND OBJECT_TYPE = 'PA_SUBPROJECTS'
5040: AND Parent_Object_ID = l_object_ids_tab(i)
5041: AND parent_object_type = 'PA_TASKS'

Line 5115: FROM PA_PROJ_ROLLUP_BULK_TEMP

5111: , l_actual_duration4_a, l_duration4_a
5112: , l_actual_duration5_a, l_duration5_a
5113: , l_actual_duration6_a, l_duration6_a
5114: , l_count
5115: FROM PA_PROJ_ROLLUP_BULK_TEMP
5116: WHERE Process_Number = l_Process_Number
5117: AND OBJECT_TYPE = 'PA_ASSIGNMENTS'
5118: AND Parent_Object_ID = l_object_ids_tab(i)
5119: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 5212: FROM PA_PROJ_ROLLUP_BULK_TEMP

5208: , l_actual_duration4_t, l_duration4_t
5209: , l_actual_duration5_t, l_duration5_t
5210: , l_actual_duration6_t, l_duration6_t
5211: , l_count1 -- Bug 4343615
5212: FROM PA_PROJ_ROLLUP_BULK_TEMP
5213: WHERE Process_Number = l_Process_Number
5214: --AND OBJECT_TYPE = 'PA_TASKS' --4582956
5215: AND OBJECT_TYPE IN ('PA_TASKS','PA_SUBPROJECTS') --4582956 : Added PA_SUBPROJECTS
5216: AND Parent_Object_ID = l_object_ids_tab(i)

Line 5271: FROM PA_PROJ_ROLLUP_BULK_TEMP

5267: , l_actual_duration4, l_pc_duration4
5268: , l_actual_duration5, l_pc_duration5
5269: , l_actual_duration6, l_pc_duration6
5270: , l_count
5271: FROM PA_PROJ_ROLLUP_BULK_TEMP
5272: WHERE Process_Number = l_Process_Number
5273: AND OBJECT_TYPE = 'PA_TASKS'
5274: AND Parent_Object_ID = l_object_ids_tab(i)
5275: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 5401: UPDATE /+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP T1

5397: END IF; -- l_count > 0
5398:
5399: IF l_update_required = 'Y' THEN
5400: /* Added the hint to force the unique index for bug#6185523 */
5401: UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
5402: SET T1.PERCENT_COMPLETE1=l_PERCENT_COMPLETE_tab1(i)
5403: , T1.PERCENT_COMPLETE2=l_PERCENT_COMPLETE_tab2(i)
5404: , T1.PERCENT_COMPLETE3=l_PERCENT_COMPLETE_tab3(i)
5405: , T1.PERCENT_COMPLETE4=l_PERCENT_COMPLETE_tab4(i)

Line 5424: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1

5420: -- Note that Bulk Update is not implemnted due to reason mentioned in Dates Rollup section
5421: /* Please do not remove
5422: IF l_update_required = 'Y' THEN
5423: FORALL i in 1..l_object_ids_tab.count
5424: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
5425: SET T1.PERCENT_COMPLETE1=l_PERCENT_COMPLETE_tab1(i)
5426: , T1.PERCENT_COMPLETE2=l_PERCENT_COMPLETE_tab2(i)
5427: , T1.PERCENT_COMPLETE3=l_PERCENT_COMPLETE_tab3(i)
5428: , T1.PERCENT_COMPLETE4=l_PERCENT_COMPLETE_tab4(i)

Line 5637: FROM PA_PROJ_ROLLUP_BULK_TEMP

5633: l_sum_tab4(i),
5634: l_sum_tab5(i),
5635: l_sum_tab6(i),
5636: l_Count
5637: FROM PA_PROJ_ROLLUP_BULK_TEMP
5638: WHERE Process_Number = l_Process_Number
5639: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
5640: AND Parent_Object_ID =l_object_ids_tab(i)
5641: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 5778: FROM PA_PROJ_ROLLUP_BULK_TEMP

5774: l_sum_tab4(i),
5775: l_sum_tab5(i),
5776: l_sum_tab6(i),
5777: l_Count
5778: FROM PA_PROJ_ROLLUP_BULK_TEMP
5779: WHERE Process_Number = l_Process_Number
5780: AND OBJECT_TYPE <> 'PA_DELIVERABLES'
5781: AND Parent_Object_ID =l_object_ids_tab(i)
5782: AND parent_object_type IN ('PA_STRUCTURES','PA_TASKS') -- Bug 4450587

Line 5792: UPDATE /+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) / PA_PROJ_ROLLUP_BULK_TEMP T1

5788: END;
5789: IF (l_count > 0) THEN
5790: l_update_required := 'Y';
5791: /* Added the hint to force the unique index for bug#6185523 */
5792: UPDATE /*+ INDEX( T1 PA_PROJ_ROLLUP_BULK_TEMP_U1) */ PA_PROJ_ROLLUP_BULK_TEMP T1
5793: SET T1.ETC_Cost1=l_ETC_Cost_tab1(i)
5794: , T1.ETC_Cost2=l_ETC_Cost_tab2(i)
5795: , T1.ETC_Cost3=l_ETC_Cost_tab3(i)
5796: , T1.ETC_Cost4=l_ETC_Cost_tab4(i)

Line 5869: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1

5865: -- NOte that Bulk Update is not done due to problem mentioned in Dates Rollup Section
5866: /* Please do not remove
5867: IF l_update_required = 'Y' THEN
5868: FORALL i in 1..l_object_ids_tab.count
5869: UPDATE PA_PROJ_ROLLUP_BULK_TEMP T1
5870: SET T1.ETC_Cost1=l_ETC_Cost_tab1(i)
5871: , T1.ETC_Cost2=l_ETC_Cost_tab2(i)
5872: , T1.ETC_Cost3=l_ETC_Cost_tab3(i)
5873: , T1.ETC_Cost4=l_ETC_Cost_tab4(i)

Line 5978: delete /+ index(pa_proj_rollup_bulk_temp PA_PROJ_ROLLUP_BULK_TEMP_N3) / -- added hint for bug 13109678

5974: END IF;
5975:
5976: -- Bug 4289748 : removed rollback, used delete instead
5977: --ROLLBACK TO SAVEPOINT GENERATE_SCHEDULE_SP;
5978: delete /*+ index(pa_proj_rollup_bulk_temp PA_PROJ_ROLLUP_BULK_TEMP_N3) */ -- added hint for bug 13109678
5979: from PA_PROJ_ROLLUP_BULK_TEMP where process_number = l_process_number;
5980: --4538049 : No need to check for x_msg_count
5981: --x_msg_count := FND_MSG_PUB.Count_Msg;
5982: --IF x_msg_count = 1 THEN

Line 5979: from PA_PROJ_ROLLUP_BULK_TEMP where process_number = l_process_number;

5975:
5976: -- Bug 4289748 : removed rollback, used delete instead
5977: --ROLLBACK TO SAVEPOINT GENERATE_SCHEDULE_SP;
5978: delete /*+ index(pa_proj_rollup_bulk_temp PA_PROJ_ROLLUP_BULK_TEMP_N3) */ -- added hint for bug 13109678
5979: from PA_PROJ_ROLLUP_BULK_TEMP where process_number = l_process_number;
5980: --4538049 : No need to check for x_msg_count
5981: --x_msg_count := FND_MSG_PUB.Count_Msg;
5982: --IF x_msg_count = 1 THEN
5983: -- pa_interface_utils_pub.get_messages (