[Home] [Help]
282: l_append VARCHAR2(250);
283:
284: CURSOR cur_elem_ver_seq
285: IS
286: SELECT pa_proj_element_versions_s.nextval
287: FROM sys.dual;
288:
289: BEGIN
290:
346: l_new_struc_version_id := x_struc_version_id ;
347:
348: -- End 4657794
349:
350: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
351: X_ROW_ID => X_row_id
352: --,X_ELEMENT_VERSION_ID => x_struc_version_id * Commented for Bug Fix: 4537865
353: ,X_ELEMENT_VERSION_ID =>l_new_struc_version_id -- added for Bug fix: 4537865
354: ,X_PROJ_ELEMENT_ID => x_structure_id
521:
522: CURSOR cur_pa_proj_elem_vers2( c_strcu_ver_id NUMBER )
523: IS
524: SELECT 'x'
525: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
526: WHERE c.project_id = p_project_id
527: AND a.structure_type_id = b.structure_type_id
528: AND b.proj_element_id = c.proj_element_id
529: AND a.structure_type = 'WORKPLAN'
574:
575: CURSOR cur_proj_elem_vers
576: IS
577: SELECT ppev.element_version_id
578: FROM pa_proj_element_versions ppev,
579: pa_proj_structure_types ppst,
580: pa_structure_types pst
581: WHERE ppev.project_id = p_project_id
582: AND ppev.object_type = 'PA_STRUCTURES'
959: from pa_projects_all
960: where project_id = p_project_id
961: and template_flag = 'Y';
962: l_template_flag VARCHAR2(1);
963: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
964: l_parent_struc_ver_id NUMBER;
965:
966: --end changes
967:
967:
968: CURSOR cur_proj_elem_vers( c_structure_version_id NUMBER )
969: IS
970: SELECT element_version_id
971: FROM pa_proj_element_versions
972: WHERE project_id = p_project_id
973: AND object_type = 'PA_TASKS'
974: AND parent_structure_version_id = c_structure_version_id
975: AND ( ( proj_element_id = ( select top_task_id
979: )
980: --bug 2852891
981: UNION
982: SELECT element_version_id
983: FROM pa_proj_element_versions
984: WHERE project_id = p_project_id
985: AND object_type = 'PA_TASKS'
986: AND parent_structure_version_id = c_structure_version_id
987: AND p_structure_type = 'WORKPLAN'
985: AND object_type = 'PA_TASKS'
986: AND parent_structure_version_id = c_structure_version_id
987: AND p_structure_type = 'WORKPLAN'
988: AND display_sequence = ( SELECT max( display_sequence )
989: FROM pa_proj_element_versions
990: WHERE project_id = p_project_id
991: AND object_type = 'PA_TASKS'
992: AND parent_structure_version_id = c_structure_version_id
993: AND wbs_level = 1 --Since there is no parent task id passed therefore its always created as top task
991: AND object_type = 'PA_TASKS'
992: AND parent_structure_version_id = c_structure_version_id
993: AND wbs_level = 1 --Since there is no parent task id passed therefore its always created as top task
994: AND display_sequence <= ( SELECT display_sequence
995: FROM pa_proj_element_versions
996: WHERE project_id = p_project_id
997: AND object_type = 'PA_TASKS'
998: AND parent_structure_version_id = c_structure_version_id
999: AND proj_element_id = p_ref_task_id )
1003:
1004: CURSOR cur_ref_task_ver_id( c_structure_version_id NUMBER )
1005: IS
1006: SELECT element_version_id
1007: FROM pa_proj_element_versions
1008: WHERE project_id = p_project_id
1009: AND object_type = 'PA_TASKS'
1010: AND parent_structure_version_id = c_structure_version_id
1011: AND proj_element_id = p_ref_task_id
1014: --AMG bug
1015: CURSOR cur_ref_task_ver_id2( c_structure_version_id NUMBER, c_parent_task_version_id NUMBER )
1016: IS
1017: SELECT element_version_id
1018: FROM pa_proj_element_versions
1019: WHERE project_id = p_project_id
1020: AND object_type = 'PA_TASKS'
1021: AND parent_structure_version_id = c_structure_version_id
1022: and element_version_id in ( SELECT object_id_to1
1024: WHERE object_id_from1 = c_parent_task_version_id
1025: and relationship_type = 'S'
1026: )
1027: AND display_sequence = ( SELECT max( display_sequence )
1028: FROM pa_proj_element_versions
1029: WHERE project_id = p_project_id
1030: AND object_type = 'PA_TASKS'
1031: AND parent_structure_version_id = c_structure_version_id
1032: and element_version_id in ( SELECT object_id_to1
1041:
1042: CURSOR cur_pa_proj_elem_vers2( c_task_version_id NUMBER )
1043: IS
1044: SELECT 'x'
1045: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1046: WHERE c.project_id = p_project_id
1047: AND a.structure_type_id = b.structure_type_id
1048: AND b.proj_element_id = c.proj_element_id
1049: AND a.structure_type = 'WORKPLAN'
1047: AND a.structure_type_id = b.structure_type_id
1048: AND b.proj_element_id = c.proj_element_id
1049: AND a.structure_type = 'WORKPLAN'
1050: AND element_version_id = ( SELECT parent_structure_version_id
1051: FROM pa_proj_element_versions
1052: WHERE element_version_id = c_task_version_id );
1053:
1054:
1055: --The above sql will return the element_version id that will be served as a
1057:
1058: CURSOR cur_proj_elem_vers2( c_structure_version_id NUMBER, c_task_id NUMBER )
1059: IS
1060: SELECT element_version_id
1061: FROM pa_proj_element_versions
1062: WHERE project_id = p_project_id
1063: AND proj_element_id = c_task_id
1064: AND parent_structure_version_id = c_structure_version_id
1065: AND object_type = 'PA_TASKS';
1095:
1096: CURSOR cur_struc_ver_fin
1097: IS
1098: SELECT c.element_version_id
1099: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1100: ,pa_proj_elem_ver_structure d
1101: WHERE c.project_id = p_project_id
1102: AND a.structure_type_id = b.structure_type_id
1103: AND b.proj_element_id = c.proj_element_id
1113:
1114: CURSOR cur_struc_ver_wp
1115: IS
1116: SELECT c.element_version_id
1117: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1118: ,pa_proj_elem_ver_structure d
1119: WHERE c.project_id = p_project_id
1120: AND a.structure_type_id = b.structure_type_id
1121: AND b.proj_element_id = c.proj_element_id
1153: --proj conn
1154: CURSOR c1( c_element_version_id NUMBER )
1155: IS
1156: SELECT wbs_level
1157: FROM pa_proj_element_versions
1158: WHERE element_version_id = c_element_version_id;
1159:
1160: CURSOR c2( c_structure_version_id NUMBER, c_element_version_id NUMBER, c_wbs_level NUMBER )
1161: IS
1159:
1160: CURSOR c2( c_structure_version_id NUMBER, c_element_version_id NUMBER, c_wbs_level NUMBER )
1161: IS
1162: SELECT element_version_id
1163: FROM pa_proj_element_versions
1164: WHERE display_sequence = ( SELECT max( display_sequence) from pa_proj_element_versions
1165: WHERE parent_structure_version_id = c_structure_version_id
1166: AND display_sequence < ( SELECT display_sequence from pa_proj_element_versions
1167: WHERE element_version_id = c_element_version_id )
1160: CURSOR c2( c_structure_version_id NUMBER, c_element_version_id NUMBER, c_wbs_level NUMBER )
1161: IS
1162: SELECT element_version_id
1163: FROM pa_proj_element_versions
1164: WHERE display_sequence = ( SELECT max( display_sequence) from pa_proj_element_versions
1165: WHERE parent_structure_version_id = c_structure_version_id
1166: AND display_sequence < ( SELECT display_sequence from pa_proj_element_versions
1167: WHERE element_version_id = c_element_version_id )
1168: AND wbs_level = c_wbs_level )
1162: SELECT element_version_id
1163: FROM pa_proj_element_versions
1164: WHERE display_sequence = ( SELECT max( display_sequence) from pa_proj_element_versions
1165: WHERE parent_structure_version_id = c_structure_version_id
1166: AND display_sequence < ( SELECT display_sequence from pa_proj_element_versions
1167: WHERE element_version_id = c_element_version_id )
1168: AND wbs_level = c_wbs_level )
1169: AND parent_structure_version_id = c_structure_version_id;
1170: l_wbs_level NUMBER;
1184: /* Bug 2736392 Begin */
1185: CURSOR cur_struc_ver_wp2
1186: IS
1187: SELECT c.element_version_id
1188: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1189: ,pa_proj_elem_ver_structure d
1190: WHERE c.project_id = p_project_id
1191: AND a.structure_type_id = b.structure_type_id
1192: AND b.proj_element_id = c.proj_element_id
1336: join in the following cursor*/
1337:
1338: CURSOR c1 is
1339: SELECT c.element_version_id
1340: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1341: ,pa_proj_elem_ver_structure d
1342: WHERE c.project_id = p_project_id
1343: AND a.structure_type_id = b.structure_type_id
1344: AND b.proj_element_id = c.proj_element_id
1879:
1880: --hsiu added task status rollup
1881: select parent_structure_version_id
1882: into l_parent_struc_ver_id
1883: from pa_proj_element_versions
1884: where element_version_id = l_task_version_id;
1885:
1886: IF (PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled(p_project_id) = 'Y') AND
1887: -- Bug 4067757 used l_versioned instead of calling Check_Wp_Versioning_Enabled multiple times
2489:
2490: CURSOR cur_struc_ver_fin
2491: IS
2492: SELECT c.element_version_id
2493: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2494: ,pa_proj_elem_ver_structure d
2495: WHERE c.project_id = p_project_id
2496: AND a.structure_type_id = b.structure_type_id
2497: AND b.proj_element_id = c.proj_element_id
2508:
2509: CURSOR cur_struc_ver_wp
2510: IS
2511: SELECT c.element_version_id
2512: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2513: ,pa_proj_elem_ver_structure d
2514: WHERE c.project_id = p_project_id
2515: AND a.structure_type_id = b.structure_type_id
2516: AND b.proj_element_id = c.proj_element_id
2529:
2530: CURSOR cur_pa_task_ver( c_structure_version_id NUMBER, c_task_id NUMBER )
2531: IS
2532: SELECT element_version_id, record_version_number
2533: FROM pa_proj_element_versions
2534: WHERE project_id = p_project_id
2535: AND proj_element_id = c_task_id
2536: AND parent_structure_version_id = c_structure_version_id
2537: AND object_type = 'PA_TASKS';
2538:
2539: CURSOR cur_pa_peer_task_above( c_structure_version_id NUMBER, c_task_version_id NUMBER )
2540: IS
2541: SELECT element_version_id
2542: FROM pa_proj_element_versions
2543: WHERE project_id = p_project_id
2544: AND parent_structure_version_id = c_structure_version_id
2545: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2546: WHERE parent_structure_version_id = c_structure_version_id
2541: SELECT element_version_id
2542: FROM pa_proj_element_versions
2543: WHERE project_id = p_project_id
2544: AND parent_structure_version_id = c_structure_version_id
2545: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2546: WHERE parent_structure_version_id = c_structure_version_id
2547: AND display_sequence < ( SELECT display_sequence FROM pa_proj_element_versions
2548: WHERE element_version_id = c_task_version_id )
2549: AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
2543: WHERE project_id = p_project_id
2544: AND parent_structure_version_id = c_structure_version_id
2545: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2546: WHERE parent_structure_version_id = c_structure_version_id
2547: AND display_sequence < ( SELECT display_sequence FROM pa_proj_element_versions
2548: WHERE element_version_id = c_task_version_id )
2549: AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
2550: WHERE element_version_id = c_task_version_id )
2551: )
2545: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2546: WHERE parent_structure_version_id = c_structure_version_id
2547: AND display_sequence < ( SELECT display_sequence FROM pa_proj_element_versions
2548: WHERE element_version_id = c_task_version_id )
2549: AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
2550: WHERE element_version_id = c_task_version_id )
2551: )
2552: AND object_type = 'PA_TASKS';
2553:
2568:
2569: CURSOR cur_pa_proj_elem_ver( c_task_version_id NUMBER )
2570: IS
2571: SELECT proj_element_id
2572: FROM pa_proj_element_versions
2573: WHERE project_id = p_project_id
2574: AND element_version_id = c_task_version_id
2575: AND object_type = 'PA_TASKS';
2576:
2576:
2577: CURSOR cur_pa_proj_elem_vers2( c_task_version_id NUMBER )
2578: IS
2579: SELECT 'x'
2580: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2581: WHERE c.project_id = p_project_id
2582: AND a.structure_type_id = b.structure_type_id
2583: AND b.proj_element_id = c.proj_element_id
2584: AND a.structure_type = 'WORKPLAN'
2582: AND a.structure_type_id = b.structure_type_id
2583: AND b.proj_element_id = c.proj_element_id
2584: AND a.structure_type = 'WORKPLAN'
2585: AND element_version_id = ( SELECT parent_structure_version_id
2586: FROM pa_proj_element_versions
2587: WHERE element_version_id = c_task_version_id );
2588:
2589: -- xxlu added for DFF attributes for calling update_schedule_version
2590: CURSOR cur_ver_sch_attr_rec(c_element_version_id IN NUMBER) IS
2614: /* Bug 2736392 Begin */
2615: CURSOR cur_struc_ver_wp2
2616: IS
2617: SELECT c.element_version_id
2618: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2619: ,pa_proj_elem_ver_structure d
2620: WHERE c.project_id = p_project_id
2621: AND a.structure_type_id = b.structure_type_id
2622: AND b.proj_element_id = c.proj_element_id
2692: ,ppevs.actual_start_date
2693: ,ppevs.actual_finish_date
2694: FROM
2695: pa_proj_elem_ver_schedule ppevs
2696: ,pa_proj_element_versions ppv
2697: WHERE
2698: ppevs.project_id = ppv.project_id
2699: and ppevs.proj_element_id = ppv.proj_element_id
2700: and ppv.object_type = 'PA_TASKS'
3734: API_ERROR EXCEPTION;
3735:
3736: CURSOR cur_task_ver_id IS
3737: SELECT element_version_id, record_version_number
3738: FROM pa_proj_element_versions ppev
3739: WHERE proj_element_id = p_task_id
3740: AND object_type = 'PA_TASKS'
3741: AND parent_structure_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID( p_project_id ); -- bug 2765115
3742:
3741: AND parent_structure_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID( p_project_id ); -- bug 2765115
3742:
3743: CURSOR cur_task_ver_id2 IS
3744: SELECT record_version_number
3745: FROM pa_proj_element_versions ppev
3746: WHERE element_version_id = p_task_version_id
3747: AND object_type = 'PA_TASKS';
3748:
3749: -- bug 2765115
3754: l_template_flag VARCHAR2(1) := 'N';
3755:
3756: CURSOR cur_task_ver_id3 IS
3757: SELECT element_version_id, record_version_number
3758: FROM pa_proj_element_versions ppev
3759: WHERE proj_element_id = p_task_id
3760: AND object_type = 'PA_TASKS'
3761: AND project_id = p_project_id;
3762: -- bug 2765115
3878:
3879: CURSOR cur_str_ver
3880: IS
3881: SELECT rowid, element_version_id
3882: FROM pa_proj_element_versions
3883: WHERE OBJECT_TYPE = 'PA_STRUCTURES'
3884: AND project_id = p_project_id;
3885:
3886: CURSOR cur_task_ver( c_parent_structure_version_id NUMBER )
3885:
3886: CURSOR cur_task_ver( c_parent_structure_version_id NUMBER )
3887: IS
3888: SELECT rowid, element_version_id
3889: FROM pa_proj_element_versions
3890: WHERE OBJECT_TYPE = 'PA_TASKS'
3891: AND project_id = p_project_id
3892: AND parent_structure_version_id = c_parent_structure_version_id;
3893:
3931: WHERE relationship_type = 'S' AND
3932: (object_id_from1 = cur_task_ver_rec.element_version_id OR
3933: object_id_to1 = cur_task_ver_rec.element_version_id);
3934:
3935: PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW( cur_task_ver_rec.rowid );
3936:
3937:
3938: END LOOP;
3939:
3955: WHERE relationship_type = 'S' AND
3956: (object_id_from1 = cur_str_ver_rec.element_version_id OR
3957: object_id_to1 = cur_str_ver_rec.element_version_id);
3958:
3959: PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW( cur_str_ver_rec.rowid );
3960:
3961: END LOOP;
3962:
3963: FOR cur_proj_elems_rec IN cur_proj_elems LOOP
4204:
4205: CURSOR cur_pa_proj_elem_vers( c_structure_id NUMBER )
4206: IS
4207: SELECT element_version_id, record_version_number
4208: FROM pa_proj_element_versions
4209: WHERE project_id = p_project_id
4210: AND object_type = 'PA_STRUCTURES'
4211: AND proj_element_id = c_structure_id;
4212:
4218:
4219: CURSOR cur_pa_proj_elem_vers2
4220: IS
4221: SELECT element_version_id
4222: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
4223: WHERE c.project_id = p_project_id
4224: AND a.structure_type_id = b.structure_type_id
4225: AND b.proj_element_id = c.proj_element_id
4226: AND a.structure_type = 'WORKPLAN';
4227:
4228: CURSOR cur_pa_proj_elem_vers3( c_parent_struc_ver_id NUMBER )
4229: IS
4230: SELECT element_version_id
4231: FROM pa_proj_element_versions
4232: WHERE project_id = p_project_id
4233: AND parent_structure_version_id = c_parent_struc_ver_id
4234: AND object_type = 'PA_TASKS';
4235:
4626:
4627: CURSOR cur_pa_proj_elem_vers
4628: IS
4629: SELECT element_version_id
4630: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
4631: WHERE c.project_id = p_project_id
4632: AND a.structure_type_id = b.structure_type_id
4633: AND b.proj_element_id = c.proj_element_id
4634: AND a.structure_type = 'FINANCIAL';
4635:
4636: CURSOR cur_task_vers( c_parent_str_ver_id NUMBER )
4637: IS
4638: SELECT element_version_id, proj_element_id
4639: FROM pa_proj_element_versions
4640: WHERE project_id = p_project_id
4641: AND parent_structure_version_id = c_parent_str_ver_id
4642: AND object_type = 'PA_TASKS';
4643:
4663:
4664: --it is possible that COSTING and WORKPLAN str are same. so delete from schedule versions as well.
4665: DELETE FROM pa_proj_elem_ver_schedule WHERE element_version_id = cur_task_vers_rec.element_version_id and project_id = p_project_id;
4666:
4667: DELETE FROM pa_proj_element_versions WHERE element_version_id = cur_task_vers_rec.element_version_id;
4668:
4669: DELETE FROM pa_proj_elements WHERE proj_element_id = cur_task_vers_rec.proj_element_id;
4670:
4671: END LOOP;
4953: select segment1, name from pa_projects_all
4954: where project_id = p_project_id;
4955:
4956: CURSOR cur_elem_ver_seq IS
4957: SELECT pa_proj_element_versions_s.nextval
4958: FROM sys.dual;
4959:
4960: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
4961: select nvl(max(version_number),0)+1
5038: l_new_structure_version_id := x_structure_version_id ;
5039:
5040: -- End 4657794
5041:
5042: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
5043: X_ROW_ID => X_row_id
5044: --,X_ELEMENT_VERSION_ID => x_structure_version_id * commented got Bug Fix: 4537865
5045: ,X_ELEMENT_VERSION_ID => l_new_structure_version_id --added for Bug Fix: 4537865
5046: ,X_PROJ_ELEMENT_ID => x_structure_id
5226: and proj_element_id = c_proj_elem_id
5227: and status_code <> 'STRUCTURE_PUBLISHED';
5228:
5229: CURSOR cur_elem_ver_seq IS
5230: SELECT pa_proj_element_versions_s.nextval
5231: FROM sys.dual;
5232:
5233: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
5234: select nvl(max(version_number),0)+1
5310: l_new_structure_version_id := x_structure_version_id ;
5311:
5312: -- End 4657794
5313:
5314: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
5315: X_ROW_ID => X_row_id
5316: --,X_ELEMENT_VERSION_ID => x_structure_version_id * Commented for Bug Fix: 4537865
5317: ,X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for Bug fix: 4537865
5318: ,X_PROJ_ELEMENT_ID => l_proj_element_id
5445: l_new_structure_version_id := x_structure_version_id ;
5446:
5447: -- End 4657794
5448:
5449: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
5450: X_ROW_ID => X_row_id
5451: --,X_ELEMENT_VERSION_ID => x_structure_version_id * commented for Bug fix: 4537865
5452: , X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for Bug fix: 4537865
5453: ,X_PROJ_ELEMENT_ID => l_proj_element_id
5896:
5897: CURSOR cur_proj_elems( c_parent_struc_ver_id NUMBER )
5898: IS
5899: SELECT element_version_id, parent_structure_version_id
5900: FROM pa_proj_element_versions
5901: WHERE project_id = p_project_id
5902: AND proj_element_id = p_task_id
5903: AND parent_structure_version_id = c_parent_struc_ver_id
5904: AND object_type = 'PA_TASKS';
6308:
6309: CURSOR cur_pa_proj_elem_ver( c_task_ver_id NUMBER )
6310: IS
6311: SELECT wbs_level
6312: FROM pa_proj_element_versions
6313: WHERE element_version_id = c_task_ver_id;
6314:
6315: i NUMBER;
6316: j NUMBER := 1;
6507: IS
6508: --bug 3074706
6509: CURSOR c2 IS
6510: select project_id
6511: from pa_proj_element_versions
6512: where element_version_id = p_structure_version_id;
6513: l_project_id NUMBER;
6514:
6515: CURSOR c1 IS
6513: l_project_id NUMBER;
6514:
6515: CURSOR c1 IS
6516: select MAX(a.last_update_date)
6517: from pa_proj_element_versions b,
6518: pa_proj_elem_ver_schedule a,
6519: pa_proj_elem_ver_structure c
6520: where p_structure_version_id = c.element_version_id
6521: and l_project_id = c.project_id
6527: and a.proj_element_id (+) = b.proj_element_id;
6528: /*
6529: CURSOR c1 IS
6530: SELECT MAX(a.last_update_date)
6531: FROM pa_proj_element_versions b,
6532: pa_proj_elem_ver_schedule a,
6533: pa_proj_elem_ver_structure c
6534: WHERE a.element_version_id (+)= b.element_version_id
6535: AND a.project_id (+) = b.project_id
6599:
6600: --This cursor retrieves the LATEST PUBLISHED WP version from the source project
6601: CURSOR cur_get_src_fin_version_id IS
6602: SELECT ppev.element_version_id
6603: FROM pa_proj_element_versions ppev,
6604: pa_proj_elem_ver_structure ppevs
6605: WHERE ppev.project_id = p_src_project_id
6606: AND ppev.object_type = 'PA_STRUCTURES'
6607: AND ppev.element_version_id = ppevs.element_version_id
6610:
6611: --This cursor retrieves the CURRENT WORKING WP version from the source project
6612: CURSOR cur_get_src_fin_version_id2 IS
6613: SELECT ppev.element_version_id
6614: FROM pa_proj_element_versions ppev,
6615: pa_proj_elem_ver_structure ppevs
6616: WHERE ppev.project_id = p_src_project_id
6617: AND ppev.object_type = 'PA_STRUCTURES'
6618: AND ppev.element_version_id = ppevs.element_version_id
6621:
6622: --This cursor retrieves the version for the financial structure form the source project
6623: CURSOR cur_get_split_fin_ver IS
6624: SELECT ppev.element_version_id
6625: FROM pa_proj_element_versions ppev,
6626: pa_proj_structure_types ppst,
6627: pa_structure_types pst
6628: WHERE ppev.project_id = p_src_project_id
6629: AND ppev.object_type = 'PA_STRUCTURES'
6731: IF l_debug_mode = 'Y' THEN
6732: Pa_Debug.WRITE(G_PKG_NAME,'Source financial version id : '||l_src_fin_version_id, l_debug_level3);
6733: END IF;
6734:
6735: --Copy entry into pa_proj_element_versions for the financial version in the source project
6736: INSERT INTO pa_proj_element_versions(
6737: ELEMENT_VERSION_ID
6738: ,PROJ_ELEMENT_ID
6739: ,OBJECT_TYPE
6732: Pa_Debug.WRITE(G_PKG_NAME,'Source financial version id : '||l_src_fin_version_id, l_debug_level3);
6733: END IF;
6734:
6735: --Copy entry into pa_proj_element_versions for the financial version in the source project
6736: INSERT INTO pa_proj_element_versions(
6737: ELEMENT_VERSION_ID
6738: ,PROJ_ELEMENT_ID
6739: ,OBJECT_TYPE
6740: ,PROJECT_ID
6769: ,source_object_id
6770: ,source_object_type
6771: )
6772: SELECT
6773: pa_proj_element_versions_s.nextval
6774: ,p_dest_fn_structure_id --This would be the WP/FIN structure id in shared case and FIN structure id in split case
6775: ,ppev.object_type
6776: ,p_dest_project_id
6777: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
6773: pa_proj_element_versions_s.nextval
6774: ,p_dest_fn_structure_id --This would be the WP/FIN structure id in shared case and FIN structure id in split case
6775: ,ppev.object_type
6776: ,p_dest_project_id
6777: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
6778: ,ppev.DISPLAY_SEQUENCE
6779: ,ppev.WBS_LEVEL
6780: ,ppev.WBS_NUMBER
6781: ,SYSDATE ------CREATION_DATE
6803: ,ppev.TASK_UNPUB_VER_STATUS_CODE
6804: ,ppev.FINANCIAL_TASK_FLAG
6805: ,p_dest_project_id
6806: ,'PA_PROJECTS'
6807: FROM pa_proj_element_versions ppev
6808: WHERE ppev.project_id = p_src_project_id
6809: AND ppev.element_version_id = l_src_fin_version_id ;
6810:
6811: OPEN cur_get_wp_attr;
6914: --If SPLIT and fin enabled case, copy cw from source, else populate it as Y
6915: ,p_dest_project_id
6916: ,'PA_PROJECTS'
6917: FROM pa_proj_elem_ver_structure ppevs,
6918: pa_proj_element_versions ppev
6919: WHERE ppevs.project_id = p_src_project_id
6920: AND ppevs.element_version_id = l_src_fin_version_id
6921: AND ppev.attribute15 = ppevs.element_version_id
6922: AND ppev.project_id = p_dest_project_id ;
7015: ) IS
7016: --This cursor retrieves the LATEST PUBLISHED WP version from the source project
7017: CURSOR cur_get_src_fin_version_id IS
7018: SELECT ppev.element_version_id
7019: FROM pa_proj_element_versions ppev,
7020: pa_proj_elem_ver_structure ppevs
7021: WHERE ppev.project_id = p_src_project_id
7022: AND ppev.object_type = 'PA_STRUCTURES'
7023: AND ppev.element_version_id = ppevs.element_version_id
7026:
7027: --This cursor retrieves the CURRENT WORKING WP version from the source project
7028: CURSOR cur_get_src_fin_version_id2 IS
7029: SELECT ppev.element_version_id
7030: FROM pa_proj_element_versions ppev,
7031: pa_proj_elem_ver_structure ppevs
7032: WHERE ppev.project_id = p_src_project_id
7033: AND ppev.object_type = 'PA_STRUCTURES'
7034: AND ppev.element_version_id = ppevs.element_version_id
7037:
7038: --This cursor retrieves the version for the financial structure form the source project
7039: CURSOR cur_get_split_fin_ver IS
7040: SELECT ppev.element_version_id
7041: FROM pa_proj_element_versions ppev,
7042: pa_proj_structure_types ppst,
7043: pa_structure_types pst
7044: WHERE ppev.project_id = p_src_project_id
7045: AND ppev.object_type = 'PA_STRUCTURES'
7131: START WITH object_id_from1 = c_src_fin_version_id
7132: AND RELATIONSHIP_TYPE = 'S'
7133: CONNECT BY object_id_from1 = PRIOR object_id_to1
7134: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7135: pa_proj_element_versions ppev1,
7136: pa_proj_element_versions ppev2
7137: WHERE ppev1.attribute15 = pobj.object_id_from1
7138: AND ppev2.attribute15 = pobj.object_id_to1
7139: AND ppev1.project_id = c_dest_project_id
7132: AND RELATIONSHIP_TYPE = 'S'
7133: CONNECT BY object_id_from1 = PRIOR object_id_to1
7134: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7135: pa_proj_element_versions ppev1,
7136: pa_proj_element_versions ppev2
7137: WHERE ppev1.attribute15 = pobj.object_id_from1
7138: AND ppev2.attribute15 = pobj.object_id_to1
7139: AND ppev1.project_id = c_dest_project_id
7140: AND ppev2.project_id = c_dest_project_id ;
7374: ,p_dest_project_id
7375: ,'PA_PROJECTS'
7376: FROM pa_task_types ptt,
7377: pa_proj_elements ppe,
7378: pa_proj_element_versions ppev,
7379: pa_tasks pt
7380: WHERE pt.project_id = p_dest_project_id
7381: AND ppe.project_id = p_src_project_id --Added by Sunkalya For the fix of Bug#4600825. This change is done for R12
7382: --Merging of code fix done thru Bug#4589176(115.315) for performance issues.
7388: AND ppe.parent_structure_id = ppev.proj_element_id
7389: AND ppev.element_version_id = l_src_fin_version_id ;
7390:
7391: --Copy tasks versions belonging to the single financial version in the source project
7392: INSERT INTO pa_proj_element_versions(
7393: ELEMENT_VERSION_ID
7394: ,PROJ_ELEMENT_ID
7395: ,OBJECT_TYPE
7396: ,PROJECT_ID
7425: ,source_object_id
7426: ,source_object_type
7427: )
7428: SELECT
7429: pa_proj_element_versions_s.nextval
7430: ,ppe.proj_element_id
7431: ,ppev.object_type
7432: ,p_dest_project_id
7433: ,ppev2.element_version_id
7459: ,decode( ppevs.status_code, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
7460: ,ppev.FINANCIAL_TASK_FLAG
7461: ,p_dest_project_id
7462: ,'PA_PROJECTS'
7463: FROM pa_proj_element_versions ppev,
7464: pa_proj_elements ppe,
7465: pa_proj_element_versions ppev2,
7466: pa_proj_elem_ver_structure ppevs
7467: WHERE ppev.project_id = p_src_project_id
7461: ,p_dest_project_id
7462: ,'PA_PROJECTS'
7463: FROM pa_proj_element_versions ppev,
7464: pa_proj_elements ppe,
7465: pa_proj_element_versions ppev2,
7466: pa_proj_elem_ver_structure ppevs
7467: WHERE ppev.project_id = p_src_project_id
7468: AND ppev.object_type = 'PA_TASKS'
7469: AND ppev.parent_structure_version_id = l_src_fin_version_id
7609: START WITH object_id_from1 = l_src_fin_version_id
7610: AND RELATIONSHIP_TYPE = 'S'
7611: CONNECT BY object_id_from1 = PRIOR object_id_to1
7612: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7613: pa_proj_element_versions ppev1,
7614: pa_proj_element_versions ppev2
7615: WHERE ppev1.attribute15 = pobj.object_id_from1
7616: AND ppev2.attribute15 = pobj.object_id_to1
7617: AND ppev1.project_id = p_dest_project_id
7610: AND RELATIONSHIP_TYPE = 'S'
7611: CONNECT BY object_id_from1 = PRIOR object_id_to1
7612: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7613: pa_proj_element_versions ppev1,
7614: pa_proj_element_versions ppev2
7615: WHERE ppev1.attribute15 = pobj.object_id_from1
7616: AND ppev2.attribute15 = pobj.object_id_to1
7617: AND ppev1.project_id = p_dest_project_id
7618: AND ppev2.project_id = p_dest_project_id ;
7740: ,ppevs.ATTRIBUTE15
7741: ,p_dest_project_id
7742: ,'PA_PROJECTS'
7743: FROM pa_proj_elem_ver_schedule ppevs,
7744: pa_proj_element_versions ppev1,
7745: pa_proj_element_versions ppev2
7746: WHERE ppev1.project_id = p_dest_project_id
7747: AND ppev1.attribute15 = ppevs.element_version_id
7748: AND ppevs.project_id = p_src_project_id
7741: ,p_dest_project_id
7742: ,'PA_PROJECTS'
7743: FROM pa_proj_elem_ver_schedule ppevs,
7744: pa_proj_element_versions ppev1,
7745: pa_proj_element_versions ppev2
7746: WHERE ppev1.project_id = p_dest_project_id
7747: AND ppev1.attribute15 = ppevs.element_version_id
7748: AND ppevs.project_id = p_src_project_id
7749: AND ppev1.attribute15 = ppev2.element_version_id
7901: CURSOR cur_get_dest_wp_struc_versions(c_dest_wp_struc_id IN NUMBER) IS
7902: SELECT ppev.element_version_id
7903: ,ppev.attribute15
7904: ,ppcot.flag
7905: FROM pa_proj_element_versions ppev,
7906: pa_project_copy_options_tmp ppcot
7907: WHERE ppev.project_id = p_dest_project_id
7908: AND ppev.proj_element_id = c_dest_wp_struc_id
7909: AND ppev.attribute15 = ppcot.version_id
7934:
7935: --This cursor will retrieve the single version id created for the shared structure
7936: CURSOR cur_get_shared_ver_id IS
7937: SELECT ppev.element_version_id
7938: FROM pa_proj_element_versions ppev
7939: WHERE ppev.project_id = p_dest_project_id
7940: AND ppev.object_type = 'PA_STRUCTURES'
7941: AND PA_PROJ_ELEMENTS_UTILS.check_fin_or_wp_structure( ppev.proj_element_id ) = 'Y' ;
7942:
7965: WHERE project_id = l_prj_id ;
7966:
7967: CURSOR c_only_wp_ver_id(l_proj_id IN NUMBER) IS
7968: SELECT ppv.element_version_id
7969: FROM pa_proj_element_versions ppv ,
7970: pa_proj_structure_types pps,
7971: pa_structure_types pst
7972: WHERE ppv.proj_element_id = pps.proj_element_id
7973: AND ppv.object_type = 'PA_STRUCTURES'
7975: AND pst.structure_type = 'WORKPLAN'
7976: AND ppv.project_id = l_proj_id ;
7977:
7978: l_template_flag PA_PROJECTS_ALL.template_flag%TYPE ;
7979: l_only_wp_ver_id PA_PROJ_ELEMENT_VERSIONS.element_version_id%TYPE ;
7980:
7981: /*End of declaration included by avaithia for Bug 3922019*/
7982:
7983: l_old_proj_org_id NUMBER;
8142: START WITH object_id_from1 = c_src_str_version_id
8143: AND RELATIONSHIP_TYPE = 'S'
8144: CONNECT BY object_id_from1 = PRIOR object_id_to1
8145: AND RELATIONSHIP_TYPE = 'S' ) pobj,
8146: pa_proj_element_versions ppev1,
8147: pa_proj_element_versions ppev2
8148: WHERE ppev1.attribute15 = pobj.object_id_from1
8149: AND ppev2.attribute15 = pobj.object_id_to1
8150: AND ppev1.parent_structure_version_id = c_struc_version_id
8143: AND RELATIONSHIP_TYPE = 'S'
8144: CONNECT BY object_id_from1 = PRIOR object_id_to1
8145: AND RELATIONSHIP_TYPE = 'S' ) pobj,
8146: pa_proj_element_versions ppev1,
8147: pa_proj_element_versions ppev2
8148: WHERE ppev1.attribute15 = pobj.object_id_from1
8149: AND ppev2.attribute15 = pobj.object_id_to1
8150: AND ppev1.parent_structure_version_id = c_struc_version_id
8151: AND ppev2.parent_structure_version_id = c_struc_version_id ;
8283: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
8284: RAISE FND_API.G_EXC_ERROR;
8285: END IF;
8286:
8287: --Update back the attributes column in pa_proj_elements and pa_proj_element_versions with actual data from source project
8288: UPDATE pa_proj_elements ppe1
8289: SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
8290: WHERE ppe2.project_id = p_src_project_id
8291: AND ppe2.proj_element_id = ppe1.attribute15 )
8290: WHERE ppe2.project_id = p_src_project_id
8291: AND ppe2.proj_element_id = ppe1.attribute15 )
8292: WHERE project_id = p_dest_project_id ;
8293:
8294: UPDATE pa_proj_element_versions ppevs1
8295: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
8296: WHERE ppevs2.project_id = p_src_project_id
8297: AND ppevs2.element_version_id = ppevs1.attribute15 )
8298: WHERE project_id = p_dest_project_id ;
8291: AND ppe2.proj_element_id = ppe1.attribute15 )
8292: WHERE project_id = p_dest_project_id ;
8293:
8294: UPDATE pa_proj_element_versions ppevs1
8295: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
8296: WHERE ppevs2.project_id = p_src_project_id
8297: AND ppevs2.element_version_id = ppevs1.attribute15 )
8298: WHERE project_id = p_dest_project_id ;
8299:
8704: Pa_Debug.WRITE(G_PKG_NAME, 'Inserting versions and tasks for selected WP version(s)', l_debug_level3);
8705: END IF;
8706:
8707: --Copy SELECTED workplan versions
8708: INSERT INTO pa_proj_element_versions(
8709: ELEMENT_VERSION_ID
8710: ,PROJ_ELEMENT_ID
8711: ,OBJECT_TYPE
8712: ,PROJECT_ID
8744: ,source_object_id
8745: ,source_object_type
8746: )
8747: SELECT
8748: pa_proj_element_versions_s.nextval
8749: ,l_dest_wp_structure_id
8750: ,ppev.object_type
8751: ,p_dest_project_id
8752: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
8748: pa_proj_element_versions_s.nextval
8749: ,l_dest_wp_structure_id
8750: ,ppev.object_type
8751: ,p_dest_project_id
8752: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
8753: ,ppev.DISPLAY_SEQUENCE
8754: ,ppev.WBS_LEVEL
8755: ,ppev.WBS_NUMBER
8756: ,SYSDATE ------CREATION_DATE
8781: ,NULL --bug 4261419
8782: ,NULL --bug 4261419
8783: ,p_dest_project_id
8784: ,'PA_PROJECTS'
8785: FROM pa_proj_element_versions ppev,
8786: pa_project_copy_options_tmp ppcot
8787: WHERE ppcot.context = 'WORKPLAN'
8788: AND ppev.element_version_id = ppcot.version_id
8789: AND ppev.project_id = p_src_project_id
9126:
9127: --If it is a shared structure and the only WP version selected is Publish Upon Creation, then
9128: --create a working copy of the Published version
9129: IF l_shared_make_working_copy IS NOT NULL THEN
9130: SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
9131: FROM dual;
9132:
9133: SELECT meaning INTO l_prefix
9134: FROM pa_lookups
9140: l_debug_level3);
9141: END IF;
9142:
9143: --Make a copy of the published version
9144: INSERT INTO pa_proj_element_versions(
9145: ELEMENT_VERSION_ID
9146: ,PROJ_ELEMENT_ID
9147: ,OBJECT_TYPE
9148: ,PROJECT_ID
9211: ,ppev.TASK_UNPUB_VER_STATUS_CODE
9212: ,ppev.FINANCIAL_TASK_FLAG
9213: ,ppev.project_id
9214: ,'PA_PROJECTS'
9215: FROM pa_proj_element_versions ppev
9216: WHERE ppev.project_id = p_dest_project_id
9217: AND ppev.element_version_id = l_shared_make_working_copy ;
9218:
9219: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
9442: AND pt.task_number (+) = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
9443: AND pt.project_id (+) = p_dest_project_id
9444: AND ppe.proj_element_id IN
9445: ( SELECT distinct ppev.proj_element_id
9446: FROM PA_PROJ_ELEMENT_VERSIONS ppev,
9447: PA_PROJECT_COPY_OPTIONS_TMP ppcot
9448: WHERE ppcot.CONTEXT = 'WORKPLAN'
9449: AND ppev.parent_structure_version_id = ppcot.version_id
9450: AND ppev.project_id = p_src_project_id
9591: AND ppe.type_id = ptt.task_type_id
9592: AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
9593: AND ppe.proj_element_id IN
9594: ( SELECT distinct ppev.proj_element_id
9595: FROM PA_PROJ_ELEMENT_VERSIONS ppev,
9596: PA_PROJECT_COPY_OPTIONS_TMP ppcot
9597: WHERE ppcot.CONTEXT = 'WORKPLAN'
9598: AND ppev.parent_structure_version_id = ppcot.version_id
9599: AND ppev.project_id = p_src_project_id
9600: AND ppev.object_type = 'PA_TASKS' ) ;
9601: END IF;
9602:
9603: --Copy tasks versions for tasks belonging to the selected wp versions
9604: INSERT INTO pa_proj_element_versions(
9605: ELEMENT_VERSION_ID
9606: ,PROJ_ELEMENT_ID
9607: ,OBJECT_TYPE
9608: ,PROJECT_ID
9637: ,source_object_id
9638: ,source_object_type
9639: )
9640: SELECT
9641: pa_proj_element_versions_s.nextval
9642: ,ppe.proj_element_id
9643: ,ppev.object_type
9644: ,p_dest_project_id
9645: ,ppev2.element_version_id
9672: ,ppev.FINANCIAL_TASK_FLAG --Check if this is correct ?
9673: ,p_dest_project_id
9674: ,'PA_PROJECTS'
9675: FROM pa_proj_elements ppe,
9676: pa_proj_element_versions ppev,
9677: pa_proj_element_versions ppev2,
9678: pa_proj_elem_ver_structure ppevs
9679: WHERE ppe.project_id = p_dest_project_id
9680: AND ppe.object_type = 'PA_TASKS'
9673: ,p_dest_project_id
9674: ,'PA_PROJECTS'
9675: FROM pa_proj_elements ppe,
9676: pa_proj_element_versions ppev,
9677: pa_proj_element_versions ppev2,
9678: pa_proj_elem_ver_structure ppevs
9679: WHERE ppe.project_id = p_dest_project_id
9680: AND ppe.object_type = 'PA_TASKS'
9681: AND ppe.attribute15 = ppev.proj_element_id
9826: START WITH object_id_from1 = rec_dest_wp_struc_versions.attribute15
9827: AND RELATIONSHIP_TYPE = 'S'
9828: CONNECT BY object_id_from1 = PRIOR object_id_to1
9829: AND RELATIONSHIP_TYPE = 'S' ) pobj,
9830: pa_proj_element_versions ppev1,
9831: pa_proj_element_versions ppev2
9832: WHERE ppev1.attribute15 = pobj.object_id_from1
9833: AND ppev2.attribute15 = pobj.object_id_to1
9834: AND ppev1.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
9827: AND RELATIONSHIP_TYPE = 'S'
9828: CONNECT BY object_id_from1 = PRIOR object_id_to1
9829: AND RELATIONSHIP_TYPE = 'S' ) pobj,
9830: pa_proj_element_versions ppev1,
9831: pa_proj_element_versions ppev2
9832: WHERE ppev1.attribute15 = pobj.object_id_from1
9833: AND ppev2.attribute15 = pobj.object_id_to1
9834: AND ppev1.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
9835: AND ppev2.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id ;
9974: ,LEVEL_ASSIGNMENTS_FLAG
9975: ,EXT_ACT_DURATION
9976: ,EXT_REMAIN_DURATION
9977: ,EXT_SCH_DURATION --Bug 3762437
9978: FROM pa_proj_element_versions ppev,
9979: pa_proj_elem_ver_schedule ppevs
9980: WHERE ppev.project_id = p_dest_project_id
9981: AND ppev.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
9982: AND ppev.attribute15 = ppevs.element_version_id
10076: --bug 3912603, Satish, this flag is for using this same condition below.
10077: check_str_sel_flag := 'Y';
10078:
10079: --Create new default WP structure version
10080: SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
10081: FROM dual;
10082:
10083: INSERT INTO pa_proj_element_versions(
10084: ELEMENT_VERSION_ID
10079: --Create new default WP structure version
10080: SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
10081: FROM dual;
10082:
10083: INSERT INTO pa_proj_element_versions(
10084: ELEMENT_VERSION_ID
10085: ,PROJ_ELEMENT_ID
10086: ,OBJECT_TYPE
10087: ,PROJECT_ID
10300: ) = ( SELECT ppevs.scheduled_start_date
10301: ,ppevs.scheduled_finish_date
10302: ,ppevs.duration
10303: FROM pa_proj_elem_ver_schedule ppevs,
10304: pa_proj_element_versions ppev
10305: WHERE ppevs.project_id = p_dest_project_id
10306: AND ppevs.proj_element_id = ppev.proj_element_id
10307: AND ppevs.element_version_id = ppev.element_version_id
10308: AND ppev.project_id = p_dest_project_id
10479: l_wp_inter_proj_depend_flag VARCHAR2(1);
10480: BEGIN
10481: SELECT Element_Version_ID, attribute15 BULK COLLECT
10482: INTO New_Versions_Tab, Old_Versions_Tab
10483: FROM PA_Proj_Element_Versions
10484: WHERE Project_ID = p_dest_project_id;
10485:
10486: PA_Relationship_Pvt.Copy_Intra_Dependency (
10487: P_Source_Ver_Tbl => Old_Versions_Tab,
10531: BEGIN
10532:
10533: SELECT Element_Version_ID, attribute15 BULK COLLECT
10534: INTO dest_versions_tab, src_Versions_Tab
10535: FROM PA_Proj_Element_Versions
10536: WHERE Project_ID = p_dest_project_id
10537: AND ( PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(element_version_id, 'WORKPLAN') = 'Y')
10538: AND object_type = 'PA_STRUCTURES';
10539:
10647: END IF;
10648: END;
10649:
10650: /* This code is moved to Copy_Project API for bug 4200168 so that BF can use attribute15 to access source task id in copy finplan API*/
10651: /* Now update back the attributes column in pa_proj_elements and pa_proj_element_versions with actual data from source project
10652: UPDATE pa_proj_elements ppe1
10653: SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
10654: WHERE ppe2.project_id = p_src_project_id
10655: AND ppe2.proj_element_id = ppe1.attribute15 )
10654: WHERE ppe2.project_id = p_src_project_id
10655: AND ppe2.proj_element_id = ppe1.attribute15 )
10656: WHERE project_id = p_dest_project_id ;
10657:
10658: UPDATE pa_proj_element_versions ppevs1
10659: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
10660: WHERE ppevs2.project_id = p_src_project_id
10661: AND ppevs2.element_version_id = ppevs1.attribute15 )
10662: WHERE project_id = p_dest_project_id ; */
10655: AND ppe2.proj_element_id = ppe1.attribute15 )
10656: WHERE project_id = p_dest_project_id ;
10657:
10658: UPDATE pa_proj_element_versions ppevs1
10659: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
10660: WHERE ppevs2.project_id = p_src_project_id
10661: AND ppevs2.element_version_id = ppevs1.attribute15 )
10662: WHERE project_id = p_dest_project_id ; */
10663:
10955: and pst.structure_type in ('WORKPLAN','FINANCIAL');
10956:
10957: CURSOR cur_elem_ver_seq
10958: IS
10959: SELECT pa_proj_element_versions_s.nextval
10960: FROM sys.dual;
10961:
10962: l_get_structure_attr_csr_rec l_get_structure_attr_csr%ROWTYPE;
10963: l_get_structure_attr_csr_rec2 l_get_structure_attr_csr2%ROWTYPE;
11039: start with object_id_from1 = c_src_str_version_id
11040: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
11041: connect by object_id_from1 = prior object_id_to1
11042: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
11043: pa_proj_element_versions ppev1,
11044: pa_proj_element_versions ppev2
11045: WHERE
11046: --bug#3094283 ppev1.project_id = p_dest_project_id
11047: ppev1.attribute15 = pobj.object_id_from1
11040: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
11041: connect by object_id_from1 = prior object_id_to1
11042: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
11043: pa_proj_element_versions ppev1,
11044: pa_proj_element_versions ppev2
11045: WHERE
11046: --bug#3094283 ppev1.project_id = p_dest_project_id
11047: ppev1.attribute15 = pobj.object_id_from1
11048: --bug#3094283 AND ppev2.project_id = p_dest_project_id
11126:
11127: --Bug 2955589.
11128: l_structure_ver_name pa_projects_all.name%TYPE;
11129: l_working_ver_count NUMBER;
11130: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
11131: -- The following two flags will indicate if the respective version has to be
11132: -- created when versioning is enabled and no working version exists for the
11133: -- source project.
11134: l_create_fin_version VARCHAR2(1);
11627:
11628:
11629: --First copy structure version and then copy all task versions
11630: --We cannot perform both with the same sql bcoz we need parent_structure version id for tasks.
11631: INSERT INTO pa_proj_element_versions(
11632: ELEMENT_VERSION_ID
11633: ,PROJ_ELEMENT_ID
11634: ,OBJECT_TYPE
11635: ,PROJECT_ID
11696: ,ppev.ATTRIBUTE14
11697: ,ppev.TASK_UNPUB_VER_STATUS_CODE
11698: ,p_dest_project_id
11699: ,'PA_PROJECTS'
11700: /*from pa_proj_element_versions ppev,
11701: pa_proj_elements ppe
11702: where ppev.project_id = p_src_project_id
11703: and ppe.project_id = p_dest_project_id
11704: and ppev.proj_element_id = ppe.attribute15
11703: and ppe.project_id = p_dest_project_id
11704: and ppev.proj_element_id = ppe.attribute15
11705: and ppe.object_type = 'PA_STRUCTURES'
11706: and ppev.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11707: from pa_proj_element_Versions ppev,
11708: pa_proj_elements ppe
11709: where ppev.project_id = p_src_project_id
11710: and ppe.project_id = p_dest_project_id
11711: and ppev.proj_element_id = ppe.attribute15
11725:
11726:
11727:
11728: SELECT element_version_id INTO l_src_str_version_id
11729: /*FROM pa_proj_element_versions
11730: WHERE project_id = p_src_project_id
11731: AND object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11732: FROM pa_proj_element_versions
11733: WHERE project_id = p_src_project_id
11728: SELECT element_version_id INTO l_src_str_version_id
11729: /*FROM pa_proj_element_versions
11730: WHERE project_id = p_src_project_id
11731: AND object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11732: FROM pa_proj_element_versions
11733: WHERE project_id = p_src_project_id
11734: AND object_type = 'PA_STRUCTURES'
11735: and proj_element_id IN (select ppe2.proj_element_id
11736: from pa_proj_elements ppe2,
12522: FETCH cur_elem_ver_seq INTO l_struc_version_id;
12523: CLOSE cur_elem_ver_seq;
12524: --First copy structure version and then copy all task versions
12525: --We cannot perform both with the same sql bcoz we need parent_structure version id for tasks.
12526: INSERT INTO pa_proj_element_versions(
12527: ELEMENT_VERSION_ID
12528: ,PROJ_ELEMENT_ID
12529: ,OBJECT_TYPE
12530: ,PROJECT_ID
12591: ,ppev.ATTRIBUTE14
12592: ,ppev.TASK_UNPUB_VER_STATUS_CODE
12593: ,p_dest_project_id
12594: ,'PA_PROJECTS'
12595: FROM pa_proj_element_versions ppev,
12596: pa_proj_elements ppe
12597: WHERE ppev.project_id = p_src_project_id
12598: and ppe.project_id = p_dest_project_id
12599: and ppev.element_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12609: AND pa_project_pub.G_structure_in_rec.structure_type = 'WORKPLAN'
12610: THEN
12611: --Copy financial tasks
12612:
12613: INSERT INTO pa_proj_element_versions(
12614: ELEMENT_VERSION_ID
12615: ,PROJ_ELEMENT_ID
12616: ,OBJECT_TYPE
12617: ,PROJECT_ID
12646: ,source_object_type
12647: ,financial_task_flag --Bug no 3651255
12648: )
12649: SELECT
12650: pa_proj_element_versions_s.nextval
12651: ,ppe.proj_element_id
12652: ,ppev.object_type
12653: ,p_dest_project_id
12654: ,l_struc_version_id --from previous sql
12680: ,decode( l_dest_str_versions_tbl(k).dest_str_status, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
12681: ,p_dest_project_id
12682: ,'PA_PROJECTS'
12683: ,ppev.financial_task_flag --Bug no 3651255
12684: FROM pa_proj_element_versions ppev,
12685: pa_proj_elements ppe
12686: WHERE ppev.project_id = p_src_project_id
12687: and ppe.project_id = p_dest_project_id
12688: and ppev.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12815: start with object_id_from1 = l_dest_str_versions_tbl(k).src_str_version_id
12816: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 Added this condition */
12817: /* connect by object_id_from1 = prior object_id_to1
12818: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 Added this condition */
12819: /* pa_proj_element_versions ppev1,
12820: pa_proj_element_versions ppev2
12821: WHERE
12822: --bug#3094283 ppev1.project_id = p_dest_project_id
12823: ppev1.attribute15 = pobj.object_id_from1
12816: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 Added this condition */
12817: /* connect by object_id_from1 = prior object_id_to1
12818: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 Added this condition */
12819: /* pa_proj_element_versions ppev1,
12820: pa_proj_element_versions ppev2
12821: WHERE
12822: --bug#3094283 ppev1.project_id = p_dest_project_id
12823: ppev1.attribute15 = pobj.object_id_from1
12824: --bug#3094283 ppev2.project_id = p_dest_project_id
13080: ,EXT_ACT_DURATION
13081: ,EXT_REMAIN_DURATION
13082: ,EXT_SCH_DURATION --Bug 3762437
13083: FROM pa_proj_elem_ver_schedule ppevs,
13084: pa_proj_element_versions ppev1,
13085: pa_proj_element_versions ppev2
13086: where ppev1.attribute15 = ppevs.element_version_id
13087: and ppevs.project_id = p_src_project_id
13088: and ppev2.project_id = p_src_project_id
13081: ,EXT_REMAIN_DURATION
13082: ,EXT_SCH_DURATION --Bug 3762437
13083: FROM pa_proj_elem_ver_schedule ppevs,
13084: pa_proj_element_versions ppev1,
13085: pa_proj_element_versions ppev2
13086: where ppev1.attribute15 = ppevs.element_version_id
13087: and ppevs.project_id = p_src_project_id
13088: and ppev2.project_id = p_src_project_id
13089: and ppev2.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
13198: dest_versions_tab SYSTEM.pa_num_tbl_type;
13199: Begin
13200: Select Element_Version_ID, attribute15 BULK COLLECT
13201: INTO dest_versions_tab, src_Versions_Tab
13202: From PA_Proj_Element_Versions
13203: Where Project_ID = p_dest_project_id
13204: AND ( PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(element_version_id, 'WORKPLAN') = 'Y')
13205: AND object_type = 'PA_STRUCTURES';
13206:
13239: and ppe2.proj_element_id = ppe1.attribute15 )
13240: where project_id = p_dest_project_id
13241: ;
13242:
13243: update pa_proj_element_versions ppevs1
13244: set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
13245: where ppevs2.project_id = p_src_project_id
13246: and ppevs2.element_version_id = ppevs1.attribute15 )
13247: where project_id = p_dest_project_id
13240: where project_id = p_dest_project_id
13241: ;
13242:
13243: update pa_proj_element_versions ppevs1
13244: set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
13245: where ppevs2.project_id = p_src_project_id
13246: and ppevs2.element_version_id = ppevs1.attribute15 )
13247: where project_id = p_dest_project_id
13248: ;
13264:
13265: CURSOR cur_str_vers( c_structure_id NUMBER )
13266: IS
13267: SELECT ppevs.element_version_id
13268: FROM pa_proj_element_versions ppevs
13269: WHERE ppevs.project_id = p_dest_project_id
13270: AND ppevs.object_type = 'PA_STRUCTURES'
13271: AND ppevs.proj_element_id = c_structure_id
13272: ;
13431: ) IS
13432: CURSOR cur_str_ver_id1
13433: IS
13434: SELECT parent_structure_version_id
13435: FROM pa_proj_element_versions
13436: WHERE element_version_id = p_task_version_id;
13437:
13438: CURSOR cur_str_ver_id2
13439: IS
13437:
13438: CURSOR cur_str_ver_id2
13439: IS
13440: SELECT element_version_id, parent_structure_version_id
13441: FROM pa_proj_element_versions
13442: WHERE proj_element_id = p_task_id
13443: AND project_id = p_project_id
13444: ;
13445:
13471:
13472: CURSOR cur_task_ver_id
13473: IS
13474: SELECT element_version_id
13475: FROM pa_proj_element_versions
13476: WHERE proj_element_id = p_task_id
13477: AND parent_structure_version_id = p_structure_version_id
13478: AND project_id = p_project_id;
13479:
13687: WHERE proj_element_id = c_task_id
13688: AND object_type = 'PA_TASKS';
13689:
13690: --Cursor to get the task version id when task id and structure version id is known
13691: CURSOR cur_pa_task_ver( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE, c_task_id pa_tasks.task_id%TYPE )
13692: IS
13693: SELECT element_version_id, record_version_number
13694: FROM pa_proj_element_versions
13695: WHERE project_id = p_project_id
13690: --Cursor to get the task version id when task id and structure version id is known
13691: CURSOR cur_pa_task_ver( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE, c_task_id pa_tasks.task_id%TYPE )
13692: IS
13693: SELECT element_version_id, record_version_number
13694: FROM pa_proj_element_versions
13695: WHERE project_id = p_project_id
13696: AND proj_element_id = c_task_id
13697: AND parent_structure_version_id = c_structure_version_id
13698: AND object_type = 'PA_TASKS';
13726: WHERE proj_element_id = c_task_id
13727: AND project_id = p_project_id;
13728:
13729: --Cursor to get the task id if task element version id is known
13730: CURSOR cur_elem_ver_to_task_id( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13731: IS
13732: SELECT proj_element_id
13733: FROM pa_proj_element_versions
13734: WHERE project_id = p_project_id
13729: --Cursor to get the task id if task element version id is known
13730: CURSOR cur_elem_ver_to_task_id( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13731: IS
13732: SELECT proj_element_id
13733: FROM pa_proj_element_versions
13734: WHERE project_id = p_project_id
13735: AND element_version_id = c_task_version_id;
13736:
13737:
13736:
13737:
13738: --Cursor added for bug 3057575 :
13739: --Cursor to get all the task ver id for a particular Structure version
13740: CURSOR cur_all_task_ver_id( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE )
13741: IS
13742: Select element_version_id
13743: from pa_proj_element_versions
13744: where parent_structure_version_id = c_structure_version_id
13739: --Cursor to get all the task ver id for a particular Structure version
13740: CURSOR cur_all_task_ver_id( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE )
13741: IS
13742: Select element_version_id
13743: from pa_proj_element_versions
13744: where parent_structure_version_id = c_structure_version_id
13745: and object_type = 'PA_TASKS'
13746: -- rbruno added for bug 9484327 to get task which are not marked for deletion - start
13747: and task_unpub_ver_status_code <> 'TO_BE_DELETED';
13749:
13750:
13751: --Cursor added for bug 3057575 :
13752: --Cursor to get the task number from task ver id
13753: CURSOR cur_ver_id_to_task_num( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13754: IS
13755: SELECT p1.element_number
13756: FROM pa_proj_elements p1, pa_proj_element_versions pv1
13757: WHERE pv1.element_version_id = c_task_version_id
13752: --Cursor to get the task number from task ver id
13753: CURSOR cur_ver_id_to_task_num( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13754: IS
13755: SELECT p1.element_number
13756: FROM pa_proj_elements p1, pa_proj_element_versions pv1
13757: WHERE pv1.element_version_id = c_task_version_id
13758: AND p1.proj_element_id = pv1.proj_element_id;
13759:
13760: -- End Cursors Definition
13759:
13760: -- End Cursors Definition
13761:
13762: -- Definition for the table types used in this API
13763: TYPE display_sequence_tbl IS TABLE OF pa_proj_element_versions.display_sequence%TYPE
13764: INDEX BY BINARY_INTEGER;
13765: TYPE wbs_level_tbl IS TABLE OF pa_proj_element_versions.wbs_level%TYPE
13766: INDEX BY BINARY_INTEGER;
13767: TYPE wbs_number_tbl IS TABLE OF pa_proj_element_versions.wbs_number%TYPE
13761:
13762: -- Definition for the table types used in this API
13763: TYPE display_sequence_tbl IS TABLE OF pa_proj_element_versions.display_sequence%TYPE
13764: INDEX BY BINARY_INTEGER;
13765: TYPE wbs_level_tbl IS TABLE OF pa_proj_element_versions.wbs_level%TYPE
13766: INDEX BY BINARY_INTEGER;
13767: TYPE wbs_number_tbl IS TABLE OF pa_proj_element_versions.wbs_number%TYPE
13768: INDEX BY BINARY_INTEGER;
13769: TYPE child_count_tbl IS TABLE OF NUMBER
13763: TYPE display_sequence_tbl IS TABLE OF pa_proj_element_versions.display_sequence%TYPE
13764: INDEX BY BINARY_INTEGER;
13765: TYPE wbs_level_tbl IS TABLE OF pa_proj_element_versions.wbs_level%TYPE
13766: INDEX BY BINARY_INTEGER;
13767: TYPE wbs_number_tbl IS TABLE OF pa_proj_element_versions.wbs_number%TYPE
13768: INDEX BY BINARY_INTEGER;
13769: TYPE child_count_tbl IS TABLE OF NUMBER
13770: INDEX BY BINARY_INTEGER;
13771: TYPE weighting_percentage_tbl IS TABLE OF pa_object_relationships.weighting_percentage%TYPE
13769: TYPE child_count_tbl IS TABLE OF NUMBER
13770: INDEX BY BINARY_INTEGER;
13771: TYPE weighting_percentage_tbl IS TABLE OF pa_object_relationships.weighting_percentage%TYPE
13772: INDEX BY BINARY_INTEGER;
13773: TYPE task_version_id_tbl IS TABLE OF pa_proj_element_versions.element_version_id%TYPE
13774: INDEX BY BINARY_INTEGER;
13775:
13776: ----Table Type added for bug 3057575 :
13777: TYPE task_parent_id_tbl_type IS TABLE OF pa_tasks.task_id%TYPE
13776: ----Table Type added for bug 3057575 :
13777: TYPE task_parent_id_tbl_type IS TABLE OF pa_tasks.task_id%TYPE
13778: INDEX BY BINARY_INTEGER;
13779: ----Table Type added for bug 3057575 :
13780: TYPE task_ver_id_passed_tbl_type IS TABLE OF pa_proj_element_versions.element_version_id%TYPE
13781: INDEX BY BINARY_INTEGER;
13782:
13783: -- End of Definition for the table types
13784:
13783: -- End of Definition for the table types
13784:
13785: -- Header level variables common to all tasks required for update/create task/project cases.
13786: l_structure_id pa_proj_elements.proj_element_id%TYPE;
13787: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
13788: l_task_unpub_ver_status_code pa_proj_element_versions.task_unpub_ver_status_code%TYPE;
13789: l_add_task_security VARCHAR2(1);
13790: l_default_task_type_id pa_proj_elements.type_id%TYPE;
13791: l_update_task_security VARCHAR2(1);
13784:
13785: -- Header level variables common to all tasks required for update/create task/project cases.
13786: l_structure_id pa_proj_elements.proj_element_id%TYPE;
13787: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
13788: l_task_unpub_ver_status_code pa_proj_element_versions.task_unpub_ver_status_code%TYPE;
13789: l_add_task_security VARCHAR2(1);
13790: l_default_task_type_id pa_proj_elements.type_id%TYPE;
13791: l_update_task_security VARCHAR2(1);
13792: l_str_status_code pa_proj_elem_ver_structure.status_code%TYPE;
13807:
13808: -- proj element level variables
13809: l_task_id pa_tasks.task_id%TYPE; -- task id of the current task being processed in loop
13810: l_parent_task_id pa_tasks.parent_task_id%TYPE; -- parent task id of the current task being processed in loop
13811: l_task_version_id pa_proj_element_versions.element_version_id%TYPE; -- stores version id of current task being processed
13812:
13813: -- version level variables required only for update task case.
13814: l_proj_elem_ver_rvn pa_proj_element_versions.record_version_number%TYPE; -- record version number of proj element table
13815: l_proj_elem_ver_rowid VARCHAR2(255); -- rowid of the task being updated.
13810: l_parent_task_id pa_tasks.parent_task_id%TYPE; -- parent task id of the current task being processed in loop
13811: l_task_version_id pa_proj_element_versions.element_version_id%TYPE; -- stores version id of current task being processed
13812:
13813: -- version level variables required only for update task case.
13814: l_proj_elem_ver_rvn pa_proj_element_versions.record_version_number%TYPE; -- record version number of proj element table
13815: l_proj_elem_ver_rowid VARCHAR2(255); -- rowid of the task being updated.
13816: l_relationship_id pa_object_relationships.object_relationship_id%TYPE;
13817: l_obj_rel_rvn NUMBER;
13818:
13816: l_relationship_id pa_object_relationships.object_relationship_id%TYPE;
13817: l_obj_rel_rvn NUMBER;
13818:
13819: -- flexfield attributes used for updating/creating element version and schedule versions table.
13820: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13821: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13822: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13817: l_obj_rel_rvn NUMBER;
13818:
13819: -- flexfield attributes used for updating/creating element version and schedule versions table.
13820: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13821: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13822: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13818:
13819: -- flexfield attributes used for updating/creating element version and schedule versions table.
13820: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13821: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13822: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13819: -- flexfield attributes used for updating/creating element version and schedule versions table.
13820: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13821: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13822: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13820: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13821: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13822: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13821: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13822: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13822: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13823: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13824: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13825: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13833: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13826: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13833: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13834: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13827: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13833: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13834: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13835: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13828: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13833: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13834: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13835: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13836: --Bug 6153503
13829: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13833: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13834: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13835: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13836: --Bug 6153503
13837: l_return_msg varchar2(2000);
13830: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13833: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13834: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13835: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13836: --Bug 6153503
13837: l_return_msg varchar2(2000);
13838: l_validate_status varchar2(1);
13831: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13832: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13833: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13834: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13835: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13836: --Bug 6153503
13837: l_return_msg varchar2(2000);
13838: l_validate_status varchar2(1);
13839:
14016: Nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
14017: FROM
14018: pa_projects_all ppa,
14019: pa_proj_elements ppe,
14020: pa_proj_element_versions ppv,
14021: pa_proj_elem_ver_structure ppvs,
14022: pa_proj_workplan_attr ppwa,
14023: pa_progress_rollup ppr,
14024: pji_xbs_plans_v pxpv
14043: (
14044: SELECT Nvl(MAX(ppr2.as_of_date), Trunc(SYSDATE))
14045: FROM
14046: pa_progress_rollup ppr2,
14047: pa_proj_element_versions ppev,
14048: pa_proj_elem_ver_structure ppevs
14049: WHERE
14050: ppr2.object_id = ppv.proj_element_id
14051: AND ppr2.proj_element_id = ppv.proj_element_id
14087: Nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) physical_percent_complete
14088: FROM
14089: pa_projects_all ppa,
14090: pa_proj_elements ppe,
14091: pa_proj_element_versions ppv,
14092: pa_proj_elem_ver_structure ppvs,
14093: pa_progress_rollup ppr,
14094: pji_xbs_plans_v pxpv
14095: WHERE
14123: -- Bug 6717386
14124: CURSOR cur_struc_ver_wp(c_project_id pa_projects_all.project_id%TYPE, c_structure_type pa_structure_types.structure_type%TYPE)
14125: IS
14126: SELECT c.element_version_id
14127: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
14128: ,pa_proj_elem_ver_structure d
14129: WHERE c.project_id = c_project_id
14130: AND a.structure_type_id = b.structure_type_id
14131: AND b.proj_element_id = c.proj_element_id
14133: AND d.project_id = c.project_id
14134: AND d.element_version_id = c.element_version_id
14135: AND d.status_code = 'STRUCTURE_WORKING';
14136:
14137: l_struct_ver_id pa_proj_element_versions.element_version_id%TYPE;
14138: l_status_code pa_proj_elements.status_code%type;
14139: -- Bug 6717386
14140:
14141: -- Start of addition for bug 8301015
14169: pt.completion_date
14170: FROM pa_tasks pt
14171: WHERE pt.task_id = c_Task_id
14172: AND NOT EXISTS --Added for Bug15958106
14173: ( select ppev.proj_element_id from pa_proj_element_versions ppev
14174: where ppev.proj_element_id=pt.task_id and ppev.TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED' );
14175:
14176: BEGIN
14177: l_tcnt := 0; -- added for bug 8301015
15115: -- no parent task information provided for the current task.
15116: -- get parent information from database
15117: DECLARE
15118:
15119: l_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
15120: l_dummy_number NUMBER;
15121: l_db_parent_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
15122: l_relationship_id_tmp pa_object_relationships.object_relationship_id%TYPE;
15123:
15117: DECLARE
15118:
15119: l_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
15120: l_dummy_number NUMBER;
15121: l_db_parent_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
15122: l_relationship_id_tmp pa_object_relationships.object_relationship_id%TYPE;
15123:
15124: BEGIN
15125:
15733: -- This cursor identified the number of progressible tasks under a parent.
15734: CURSOR get_child_count(c_parent_element_version_id NUMBER)
15735: IS
15736: SELECT count(rel.object_id_to1)
15737: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
15738: WHERE rel.object_id_from1 = c_parent_element_version_id
15739: AND rel.object_type_to = 'PA_TASKS'
15740: AND rel.relationship_type = 'S'
15741: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
15745: AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
15746: AND tt.prog_entry_enable_flag = 'Y';
15747:
15748: l_child_task_count NUMBER;
15749: l_parent_version_id pa_proj_element_versions.element_version_id%TYPE;
15750: l_task_type_id pa_task_types.task_type_id%TYPE;
15751: BEGIN
15752: l_existing_task_set_max_wt := 'N';
15753: IF l_maintain_weightages = 'N' THEN -- no need to maintain weightages.
15961: --Bug 6153503
15962: ELSE -- update task case
15963: DECLARE
15964: --Cursor to get the versioned information of the task
15965: CURSOR cur_proj_element_version_info(c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE, c_task_id pa_tasks.task_id%TYPE )
15966: IS
15967: SELECT rowid,element_version_id,object_type, project_id, proj_element_id, record_version_number,
15968: display_sequence, wbs_number, wbs_level, parent_structure_version_id,
15969: attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
15968: display_sequence, wbs_number, wbs_level, parent_structure_version_id,
15969: attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
15970: attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12,
15971: attribute13, attribute14, attribute15, TASK_UNPUB_VER_STATUS_CODE
15972: FROM pa_proj_element_versions
15973: WHERE project_id = p_project_id
15974: AND proj_element_id = c_task_id
15975: AND parent_structure_version_id = c_structure_version_id
15976: AND object_type = 'PA_TASKS';
16136: l_proj_elem_ver_rowid := l_cur_proj_elem_ver_info_rec.rowid;
16137: l_proj_elem_ver_rvn := l_cur_proj_elem_ver_info_rec.record_version_number;
16138:
16139: END;
16140: END IF; -- before calling PA_PROJ_ELEMENT_VERSIONS_PKG Insert_Row or Update_Row
16141:
16142: -- In the following block do following validations:
16143: -- for create task case check if its ok to create subtask under the parent task.
16144: -- for update task check if the task is being moved. If moved check if move task is ok.
16175: END IF; --l_parent_task_id IS NOT NULL
16176:
16177: ELSE
16178: DECLARE
16179: l_db_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
16180: l_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
16181:
16182: -- Bug 3075609. This will hold the weightage of the task version currently in database.
16183: l_db_task_weightage pa_object_relationships.weighting_percentage%TYPE;
16176:
16177: ELSE
16178: DECLARE
16179: l_db_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
16180: l_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
16181:
16182: -- Bug 3075609. This will hold the weightage of the task version currently in database.
16183: l_db_task_weightage pa_object_relationships.weighting_percentage%TYPE;
16184: BEGIN
16260: END;
16261: END IF;
16262:
16263: -- call insert row or update row
16264: -- Set the following local variables after calling PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row
16265: -- l_task_version_id_tbl(l_task_id)
16266: -- px_tasks_out_tbl(i).task_version_id
16267:
16268: IF l_task_in_rec.pa_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR
16270: THEN
16271:
16272: -- Call the table handler to create a task version.
16273: IF l_debug_mode = 'Y' THEN
16274: pa_debug.g_err_stage := 'Calling API PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row';
16275: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level4);
16276: END IF;
16277: --added for 4059962 by rtarway
16278: --Derive financial task flag
16321: END IF;
16322: /*
16323: End of Bug Fix 5263429
16324: */
16325: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row
16326: (
16327: X_ROW_ID => l_proj_elem_ver_rowid
16328: ,X_ELEMENT_VERSION_ID => l_task_version_id
16329: ,X_PROJ_ELEMENT_ID => l_task_id
16386: ELSE -- in update task case
16387: -- check for locking
16388: BEGIN
16389: SELECT 'x' INTO l_dummy_char
16390: FROM PA_PROJ_ELEMENT_VERSIONS
16391: WHERE element_version_id = l_task_version_id
16392: AND record_version_number = l_proj_elem_ver_rvn
16393: FOR UPDATE OF record_version_number NOWAIT;
16394: EXCEPTION
16411: END;
16412:
16413: -- Call the table handler to create a task version.
16414: IF l_debug_mode = 'Y' THEN
16415: pa_debug.g_err_stage := 'Calling API PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row';
16416: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level4);
16417: END IF;
16418:
16419: -- Bug 3075609. Since atleast one task is being updated, this structure version is an
16420: -- existing one. Let us maintain a flag to track this so that if this flag is Y we will
16421: -- not recalculate the task weightages if the basis is MANUAL.
16422: l_new_structure_version := 'N';
16423:
16424: PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
16425: X_ROW_ID => l_proj_elem_ver_rowid
16426: ,X_ELEMENT_VERSION_ID => l_task_version_id
16427: ,X_PROJ_ELEMENT_ID => l_task_id
16428: ,X_OBJECT_TYPE => 'PA_TASKS'
16458: pa_debug.g_err_stage := 'Task Version id : ' || l_task_version_id ;
16459: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
16460: END IF;
16461:
16462: END IF; /* After calling PA_PROJ_ELEMENT_VERSIONS_PKG Insert_Row or Update_Row*/
16463:
16464: -- Added for bug 3057575
16465: -- Here we will poulate the task_ver_id_passed_tbl with all the task version Id processed while
16466: -- Updating a project .This is not done while creating a project as we
16472:
16473: -- In following code we will be creating or updating the records in object relationship tables.
16474:
16475: DECLARE
16476: l_structure_version_id_from pa_proj_element_versions.element_version_id%TYPE;
16477: l_task_version_id_from pa_proj_element_versions.element_version_id%TYPE;
16478: l_relationship_subtype pa_object_relationships.relationship_subtype%TYPE;
16479:
16480: BEGIN
16473: -- In following code we will be creating or updating the records in object relationship tables.
16474:
16475: DECLARE
16476: l_structure_version_id_from pa_proj_element_versions.element_version_id%TYPE;
16477: l_task_version_id_from pa_proj_element_versions.element_version_id%TYPE;
16478: l_relationship_subtype pa_object_relationships.relationship_subtype%TYPE;
16479:
16480: BEGIN
16481:
17177: -- reassign the task weightages for the affected peer tasks.
17178: CURSOR cur_get_child_info(c_parent_element_version_id NUMBER)
17179: IS
17180: SELECT rel.OBJECT_RELATIONSHIP_ID,tt.prog_entry_enable_flag
17181: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
17182: WHERE rel.object_id_from1 = c_parent_element_version_id
17183: AND rel.object_type_to = 'PA_TASKS'
17184: AND rel.relationship_type = 'S'
17185: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
17190:
17191: TYPE obj_rel_id_tbl is table of pa_object_relationships.object_relationship_id%TYPE
17192: INDEX BY BINARY_INTEGER;
17193:
17194: l_parent_version_id pa_proj_element_versions.element_version_id%TYPE;
17195: l_prog_entry_enable_flag_tbl PA_PLSQL_DATATYPES.Char1TabTyp;
17196: l_obj_rel_id_tbl obj_rel_id_tbl;
17197: l_plsql_max_array_size CONSTANT NUMBER := 200; -- limiting the max fetch size
17198: l_task_weightage_tbl weighting_percentage_tbl;
18218: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18219: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18220: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18221: ,p_project_id IN pa_projects_all.project_id%TYPE
18222: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18223: ,p_pub_struc_ver_id IN NUMBER := NULL
18224: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18225: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18226: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
18230: -- Cursors used in this Procedure.
18231:
18232: -- This cursor obtains the count of the task versions in the structure version.
18233: CURSOR cur_task_count(c_project_id pa_projects_all.project_id%TYPE,
18234: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
18235: IS
18236: Select count(element_version_id)
18237: From pa_proj_element_versions
18238: Where project_id = c_project_id
18233: CURSOR cur_task_count(c_project_id pa_projects_all.project_id%TYPE,
18234: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
18235: IS
18236: Select count(element_version_id)
18237: From pa_proj_element_versions
18238: Where project_id = c_project_id
18239: And object_type = 'PA_TASKS'
18240: And parent_structure_version_id = c_structure_version_id;
18241:
18479: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18480: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18481: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18482: ,p_project_id IN pa_projects_all.project_id%TYPE
18483: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18484: ,p_pub_struc_ver_id IN NUMBER := NULL
18485: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18486: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18487: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
18493: -- This cursor is used to decide if the structure version corresponds to a particular
18494: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
18495: -- api will be called.
18496: Cursor cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
18497: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
18498: c_structure_type pa_structure_types.structure_type%TYPE)
18499: IS
18500: select 'Y'
18501: from pa_proj_element_versions ver,
18497: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
18498: c_structure_type pa_structure_types.structure_type%TYPE)
18499: IS
18500: select 'Y'
18501: from pa_proj_element_versions ver,
18502: pa_proj_structure_types pst,
18503: pa_structure_types st
18504: where ver.element_version_id = c_structure_version_id
18505: and ver.project_id = c_project_id
18849: ( errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18850: ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18851: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18852: ,p_project_id IN pa_projects_all.project_id%TYPE
18853: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18854: ,p_pub_struc_ver_id IN NUMBER := NULL
18855: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18856: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
18857: )
19230: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
19231: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
19232: ,p_calling_context IN VARCHAR2 := 'UPDATE'
19233: ,p_project_id IN pa_projects_all.project_id%TYPE
19234: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
19235: ,p_pub_struc_ver_id IN NUMBER := NULL
19236: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
19237: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
19238: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
19245: -- This cursor is used to decide if the structure version corresponds to a particular
19246: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
19247: -- api will be called.
19248: CURSOR cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
19249: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
19250: c_structure_type pa_structure_types.structure_type%TYPE)
19251: IS
19252: SELECT 'Y'
19253: FROM pa_proj_element_versions ver,
19249: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
19250: c_structure_type pa_structure_types.structure_type%TYPE)
19251: IS
19252: SELECT 'Y'
19253: FROM pa_proj_element_versions ver,
19254: pa_proj_structure_types pst,
19255: pa_structure_types st
19256: WHERE ver.element_version_id = c_structure_version_id
19257: AND ver.project_id = c_project_id
19284: --bug 3762882
19285: CURSOR get_prev_published_ver(c_struc_ver_id NUMBER) IS
19286: select a.element_version_id
19287: from pa_proj_elem_ver_structure a,
19288: pa_proj_element_versions b
19289: WHERE a.project_id = p_project_id
19290: AND a.proj_element_id = b.proj_element_id
19291: AND b.element_version_id = c_struc_ver_id
19292: AND a.LATEST_EFF_PUBLISHED_FLAG <> 'Y'
20940: DECLARE
20941: CURSOR cur_all_str_ver
20942: IS
20943: SELECT ppevs2.element_version_id
20944: FROM pa_proj_element_versions ppevs1, pa_proj_element_versions ppevs2
20945: WHERE ppevs1.proj_element_id = ppevs2.proj_element_id
20946: AND ppevs1.project_id = p_project_id
20947: AND ppevs2.project_id = p_project_id
20948: AND ppevs1.element_version_id = p_structure_version_id
21136: ,p_commit IN VARCHAR2
21137: ,p_init_msg_list IN VARCHAR2
21138: ,p_calling_context IN VARCHAR2
21139: ,p_project_id IN pa_projects_all.project_id%TYPE
21140: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21141: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21142: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21143: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21144: AS
21167:
21168: TYPE object_rel_id_tbl IS TABLE OF pa_object_relationships.object_relationship_id%TYPE
21169: INDEX BY BINARY_INTEGER;
21170:
21171: TYPE parent_task_ver_id_tbl IS TABLE OF pa_object_relationships.object_id_from1%TYPE --pa_proj_element_versions.element_version_id%TYPE 3746912
21172: INDEX BY BINARY_INTEGER;
21173:
21174: TYPE duration_tbl IS TABLE OF pa_proj_elem_ver_schedule.duration%TYPE
21175: INDEX BY BINARY_INTEGER;
21186: -- This cursor fetches the duration, effort, relationship id between the task and its parent
21187: -- and progress entry enable flag for the task type associated with the task version. This
21188: -- info is necessary for calculating the task weightage of the tasks.
21189: CURSOR cur_task_info(c_project_id pa_projects_all.project_id%TYPE,
21190: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE
21191: ,c_plan_version_id NUMBER --bug 4726889
21192: )
21193: IS
21194: Select rel.object_relationship_id,
21196: -- NVL(pfxat.labor_hours,0) + NVL(pfxat.equipment_hours,0),
21197: NVL(pxpv.labor_effort,0) + NVL(pxpv.equipment_effort,0),
21198: sch.duration,
21199: ptt.prog_entry_enable_flag
21200: from pa_proj_element_versions ver,
21201: pa_object_relationships rel,
21202: pa_proj_elem_ver_schedule sch,
21203: pa_proj_elements ele,
21204: pa_task_types ptt,
21227:
21228: -- TWUT
21229: -- This cursor is when fetched will lock all the task versions of the structure version.
21230: CURSOR cur_task_ver_lock(c_project_id pa_projects_all.project_id%TYPE,
21231: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
21232: IS
21233: select 'X'
21234: from pa_proj_element_versions ver,
21235: pa_proj_elements ele,
21230: CURSOR cur_task_ver_lock(c_project_id pa_projects_all.project_id%TYPE,
21231: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
21232: IS
21233: select 'X'
21234: from pa_proj_element_versions ver,
21235: pa_proj_elements ele,
21236: pa_task_types ptt
21237: where ver.project_id = c_project_id
21238: and ver.parent_structure_version_id = c_structure_version_id
21354: END IF;
21355: RETURN;
21356: END IF;
21357:
21358: -- Lock the structure version record in pa_proj_element_versions table.
21359: -- NEED TO REVISIT THIS LOGIC.
21360: -- TWUT. Previously had a select to lock - but it lead to exact fetch
21361: -- returned more than one row exception. Changed to cursor.
21362: DECLARE
21413: l_prog_enable_flag_fetch_tbl prog_enable_flag_tbl;
21414:
21415: l_plsql_max_array_size CONSTANT NUMBER := 200; -- limiting the max fetch size
21416: l_task_count NUMBER := 0;
21417: l_parent_id pa_proj_element_versions.element_version_id%TYPE;
21418: BEGIN
21419: OPEN cur_task_info(p_project_id,p_structure_version_id
21420: ,l_plan_version_id); --4726889
21421: LOOP
21483: -- Use them and calculate the weightage for each of the task.
21484: DECLARE
21485: l_task_weightage_tbl task_weightage_tbl;
21486:
21487: l_parent_id pa_proj_element_versions.element_version_id%TYPE;
21488: l_task_weightage pa_object_relationships.weighting_percentage%TYPE;
21489:
21490: --Bug 3053472 - remaining weightage is no more required.
21491: --l_remaining_weightage pa_object_relationships.weighting_percentage%TYPE;
21681: ,p_commit IN VARCHAR2
21682: ,p_init_msg_list IN VARCHAR2
21683: ,p_calling_context IN VARCHAR2
21684: ,p_project_id IN pa_projects_all.project_id%TYPE
21685: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21686: ,p_update_wbs_flag IN pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE
21687: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21688: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21689: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21825: -- can not be reffered .
21826:
21827: PROCEDURE PROCESS_WBS_UPDATES_WRP_FORM
21828: ( p_project_id IN pa_projects_all.project_id%TYPE
21829: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21830: ,p_pub_struc_ver_id IN NUMBER := NULL
21831: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21832: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21833: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21923:
21924: CURSOR cur_str_vers( c_structure_id NUMBER )
21925: IS
21926: SELECT ppevs.element_version_id
21927: FROM pa_proj_element_versions ppevs
21928: WHERE ppevs.project_id = p_dest_project_id
21929: AND ppevs.object_type = 'PA_STRUCTURES'
21930: AND ppevs.proj_element_id = c_structure_id
21931: ;
22356: --bug 3613766
22357: cursor get_pred_id(c_pred_ref_id VARCHAR2) IS
22358: select ppev1.project_id, ppev1.element_version_id
22359: from pa_proj_elements ppe,
22360: pa_proj_element_versions ppev1,
22361: pa_proj_element_versions ppev2
22362: where ppev2.element_version_id = P_Task_Version_Id
22363: and ppev2.parent_structure_version_id = ppev1.parent_structure_version_id
22364: and ppev1.proj_element_id = ppe.proj_element_id
22357: cursor get_pred_id(c_pred_ref_id VARCHAR2) IS
22358: select ppev1.project_id, ppev1.element_version_id
22359: from pa_proj_elements ppe,
22360: pa_proj_element_versions ppev1,
22361: pa_proj_element_versions ppev2
22362: where ppev2.element_version_id = P_Task_Version_Id
22363: and ppev2.parent_structure_version_id = ppev1.parent_structure_version_id
22364: and ppev1.proj_element_id = ppe.proj_element_id
22365: and ppe.pm_source_reference = c_pred_ref_id;
22530: POR.Relationship_Subtype SubType,
22531: decode(sign(NVL(POR.Lag_Day,0)),
22532: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/* bug#8583608(10*8*60)*/ LagDays --bug 4348156
22533: From PA_Proj_Elements PPE,
22534: PA_Proj_Element_Versions PEV,
22535: PA_Object_Relationships POR,
22536: PA_PROJ_ELEMENT_VERSIONS PEV2
22537: Where PPE.project_id = PEV.project_id
22538: and PPE.proj_element_id = PEV.proj_element_id
22532: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/* bug#8583608(10*8*60)*/ LagDays --bug 4348156
22533: From PA_Proj_Elements PPE,
22534: PA_Proj_Element_Versions PEV,
22535: PA_Object_Relationships POR,
22536: PA_PROJ_ELEMENT_VERSIONS PEV2
22537: Where PPE.project_id = PEV.project_id
22538: and PPE.proj_element_id = PEV.proj_element_id
22539: and PEV.element_version_id = POR.object_id_to1
22540: and PEV2.ELEMENT_VERSION_ID = POR.object_id_from1
22560: POR.Relationship_Subtype SubType,
22561: decode(sign(NVL(POR.Lag_Day,0)),
22562: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/* bug#8583608 (10*8*60)*/ LagDays --bug 4348156
22563: From PA_Proj_Elements PPE,
22564: PA_Proj_Element_Versions PEV,
22565: PA_Object_Relationships POR,
22566: PA_PROJ_ELEMENT_VERSIONS PEV2
22567: Where PPE.project_id = PEV.project_id
22568: and PPE.proj_element_id = PEV.proj_element_id
22562: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/* bug#8583608 (10*8*60)*/ LagDays --bug 4348156
22563: From PA_Proj_Elements PPE,
22564: PA_Proj_Element_Versions PEV,
22565: PA_Object_Relationships POR,
22566: PA_PROJ_ELEMENT_VERSIONS PEV2
22567: Where PPE.project_id = PEV.project_id
22568: and PPE.proj_element_id = PEV.proj_element_id
22569: and PEV.element_version_id = POR.object_id_to1
22570: and PEV2.ELEMENT_VERSION_ID = POR.object_id_from1
22618: END IF;
22619: BEGIN
22620: SELECT object_type
22621: INTO l_object_type
22622: FROM pa_proj_element_versions
22623: WHERE ELEMENT_VERSION_ID = p_element_version_id;
22624: EXCEPTION
22625: WHEN NO_DATA_FOUND THEN
22626: fnd_message.set_name('PA','PA_RECORD_NOT_FOUND');
22653: BEGIN
22654: DELETE FROM pa_object_relationships por1
22655: WHERE por1.object_relationship_id IN
22656: (SELECT por.object_relationship_id
22657: FROM pa_proj_element_versions ppev,
22658: pa_object_relationships por
22659: WHERE relationship_type ='D'
22660: AND ppev.parent_structure_version_id = p_element_version_id
22661: AND ppev.object_type = 'PA_TASKS'