[Home] [Help]
66: l_append VARCHAR2(250);
67:
68: CURSOR cur_elem_ver_seq
69: IS
70: SELECT pa_proj_element_versions_s.nextval
71: FROM sys.dual;
72:
73: BEGIN
74:
130: l_new_struc_version_id := x_struc_version_id ;
131:
132: -- End 4657794
133:
134: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
135: X_ROW_ID => X_row_id
136: --,X_ELEMENT_VERSION_ID => x_struc_version_id * Commented for Bug Fix: 4537865
137: ,X_ELEMENT_VERSION_ID =>l_new_struc_version_id -- added for Bug fix: 4537865
138: ,X_PROJ_ELEMENT_ID => x_structure_id
305:
306: CURSOR cur_pa_proj_elem_vers2( c_strcu_ver_id NUMBER )
307: IS
308: SELECT 'x'
309: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
310: WHERE c.project_id = p_project_id
311: AND a.structure_type_id = b.structure_type_id
312: AND b.proj_element_id = c.proj_element_id
313: AND a.structure_type = 'WORKPLAN'
358:
359: CURSOR cur_proj_elem_vers
360: IS
361: SELECT ppev.element_version_id
362: FROM pa_proj_element_versions ppev,
363: pa_proj_structure_types ppst,
364: pa_structure_types pst
365: WHERE ppev.project_id = p_project_id
366: AND ppev.object_type = 'PA_STRUCTURES'
743: from pa_projects_all
744: where project_id = p_project_id
745: and template_flag = 'Y';
746: l_template_flag VARCHAR2(1);
747: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
748: l_parent_struc_ver_id NUMBER;
749:
750: --end changes
751:
751:
752: CURSOR cur_proj_elem_vers( c_structure_version_id NUMBER )
753: IS
754: SELECT element_version_id
755: FROM pa_proj_element_versions
756: WHERE project_id = p_project_id
757: AND object_type = 'PA_TASKS'
758: AND parent_structure_version_id = c_structure_version_id
759: AND ( ( proj_element_id = ( select top_task_id
763: )
764: --bug 2852891
765: UNION
766: SELECT element_version_id
767: FROM pa_proj_element_versions
768: WHERE project_id = p_project_id
769: AND object_type = 'PA_TASKS'
770: AND parent_structure_version_id = c_structure_version_id
771: AND p_structure_type = 'WORKPLAN'
769: AND object_type = 'PA_TASKS'
770: AND parent_structure_version_id = c_structure_version_id
771: AND p_structure_type = 'WORKPLAN'
772: AND display_sequence = ( SELECT max( display_sequence )
773: FROM pa_proj_element_versions
774: WHERE project_id = p_project_id
775: AND object_type = 'PA_TASKS'
776: AND parent_structure_version_id = c_structure_version_id
777: AND wbs_level = 1 --Since there is no parent task id passed therefore its always created as top task
775: AND object_type = 'PA_TASKS'
776: AND parent_structure_version_id = c_structure_version_id
777: AND wbs_level = 1 --Since there is no parent task id passed therefore its always created as top task
778: AND display_sequence <= ( SELECT display_sequence
779: FROM pa_proj_element_versions
780: WHERE project_id = p_project_id
781: AND object_type = 'PA_TASKS'
782: AND parent_structure_version_id = c_structure_version_id
783: AND proj_element_id = p_ref_task_id )
787:
788: CURSOR cur_ref_task_ver_id( c_structure_version_id NUMBER )
789: IS
790: SELECT element_version_id
791: FROM pa_proj_element_versions
792: WHERE project_id = p_project_id
793: AND object_type = 'PA_TASKS'
794: AND parent_structure_version_id = c_structure_version_id
795: AND proj_element_id = p_ref_task_id
798: --AMG bug
799: CURSOR cur_ref_task_ver_id2( c_structure_version_id NUMBER, c_parent_task_version_id NUMBER )
800: IS
801: SELECT element_version_id
802: FROM pa_proj_element_versions
803: WHERE project_id = p_project_id
804: AND object_type = 'PA_TASKS'
805: AND parent_structure_version_id = c_structure_version_id
806: and element_version_id in ( SELECT object_id_to1
808: WHERE object_id_from1 = c_parent_task_version_id
809: and relationship_type = 'S'
810: )
811: AND display_sequence = ( SELECT max( display_sequence )
812: FROM pa_proj_element_versions
813: WHERE project_id = p_project_id
814: AND object_type = 'PA_TASKS'
815: AND parent_structure_version_id = c_structure_version_id
816: and element_version_id in ( SELECT object_id_to1
825:
826: CURSOR cur_pa_proj_elem_vers2( c_task_version_id NUMBER )
827: IS
828: SELECT 'x'
829: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
830: WHERE c.project_id = p_project_id
831: AND a.structure_type_id = b.structure_type_id
832: AND b.proj_element_id = c.proj_element_id
833: AND a.structure_type = 'WORKPLAN'
831: AND a.structure_type_id = b.structure_type_id
832: AND b.proj_element_id = c.proj_element_id
833: AND a.structure_type = 'WORKPLAN'
834: AND element_version_id = ( SELECT parent_structure_version_id
835: FROM pa_proj_element_versions
836: WHERE element_version_id = c_task_version_id );
837:
838:
839: --The above sql will return the element_version id that will be served as a
841:
842: CURSOR cur_proj_elem_vers2( c_structure_version_id NUMBER, c_task_id NUMBER )
843: IS
844: SELECT element_version_id
845: FROM pa_proj_element_versions
846: WHERE project_id = p_project_id
847: AND proj_element_id = c_task_id
848: AND parent_structure_version_id = c_structure_version_id
849: AND object_type = 'PA_TASKS';
879:
880: CURSOR cur_struc_ver_fin
881: IS
882: SELECT c.element_version_id
883: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
884: ,pa_proj_elem_ver_structure d
885: WHERE c.project_id = p_project_id
886: AND a.structure_type_id = b.structure_type_id
887: AND b.proj_element_id = c.proj_element_id
897:
898: CURSOR cur_struc_ver_wp
899: IS
900: SELECT c.element_version_id
901: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
902: ,pa_proj_elem_ver_structure d
903: WHERE c.project_id = p_project_id
904: AND a.structure_type_id = b.structure_type_id
905: AND b.proj_element_id = c.proj_element_id
937: --proj conn
938: CURSOR c1( c_element_version_id NUMBER )
939: IS
940: SELECT wbs_level
941: FROM pa_proj_element_versions
942: WHERE element_version_id = c_element_version_id;
943:
944: CURSOR c2( c_structure_version_id NUMBER, c_element_version_id NUMBER, c_wbs_level NUMBER )
945: IS
943:
944: CURSOR c2( c_structure_version_id NUMBER, c_element_version_id NUMBER, c_wbs_level NUMBER )
945: IS
946: SELECT element_version_id
947: FROM pa_proj_element_versions
948: WHERE display_sequence = ( SELECT max( display_sequence) from pa_proj_element_versions
949: WHERE parent_structure_version_id = c_structure_version_id
950: AND display_sequence < ( SELECT display_sequence from pa_proj_element_versions
951: WHERE element_version_id = c_element_version_id )
944: CURSOR c2( c_structure_version_id NUMBER, c_element_version_id NUMBER, c_wbs_level NUMBER )
945: IS
946: SELECT element_version_id
947: FROM pa_proj_element_versions
948: WHERE display_sequence = ( SELECT max( display_sequence) from pa_proj_element_versions
949: WHERE parent_structure_version_id = c_structure_version_id
950: AND display_sequence < ( SELECT display_sequence from pa_proj_element_versions
951: WHERE element_version_id = c_element_version_id )
952: AND wbs_level = c_wbs_level )
946: SELECT element_version_id
947: FROM pa_proj_element_versions
948: WHERE display_sequence = ( SELECT max( display_sequence) from pa_proj_element_versions
949: WHERE parent_structure_version_id = c_structure_version_id
950: AND display_sequence < ( SELECT display_sequence from pa_proj_element_versions
951: WHERE element_version_id = c_element_version_id )
952: AND wbs_level = c_wbs_level )
953: AND parent_structure_version_id = c_structure_version_id;
954: l_wbs_level NUMBER;
968: /* Bug 2736392 Begin */
969: CURSOR cur_struc_ver_wp2
970: IS
971: SELECT c.element_version_id
972: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
973: ,pa_proj_elem_ver_structure d
974: WHERE c.project_id = p_project_id
975: AND a.structure_type_id = b.structure_type_id
976: AND b.proj_element_id = c.proj_element_id
1120: join in the following cursor*/
1121:
1122: CURSOR c1 is
1123: SELECT c.element_version_id
1124: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1125: ,pa_proj_elem_ver_structure d
1126: WHERE c.project_id = p_project_id
1127: AND a.structure_type_id = b.structure_type_id
1128: AND b.proj_element_id = c.proj_element_id
1663:
1664: --hsiu added task status rollup
1665: select parent_structure_version_id
1666: into l_parent_struc_ver_id
1667: from pa_proj_element_versions
1668: where element_version_id = l_task_version_id;
1669:
1670: IF (PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled(p_project_id) = 'Y') AND
1671: -- Bug 4067757 used l_versioned instead of calling Check_Wp_Versioning_Enabled multiple times
2272:
2273: CURSOR cur_struc_ver_fin
2274: IS
2275: SELECT c.element_version_id
2276: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2277: ,pa_proj_elem_ver_structure d
2278: WHERE c.project_id = p_project_id
2279: AND a.structure_type_id = b.structure_type_id
2280: AND b.proj_element_id = c.proj_element_id
2291:
2292: CURSOR cur_struc_ver_wp
2293: IS
2294: SELECT c.element_version_id
2295: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2296: ,pa_proj_elem_ver_structure d
2297: WHERE c.project_id = p_project_id
2298: AND a.structure_type_id = b.structure_type_id
2299: AND b.proj_element_id = c.proj_element_id
2312:
2313: CURSOR cur_pa_task_ver( c_structure_version_id NUMBER, c_task_id NUMBER )
2314: IS
2315: SELECT element_version_id, record_version_number
2316: FROM pa_proj_element_versions
2317: WHERE project_id = p_project_id
2318: AND proj_element_id = c_task_id
2319: AND parent_structure_version_id = c_structure_version_id
2320: AND object_type = 'PA_TASKS';
2321:
2322: CURSOR cur_pa_peer_task_above( c_structure_version_id NUMBER, c_task_version_id NUMBER )
2323: IS
2324: SELECT element_version_id
2325: FROM pa_proj_element_versions
2326: WHERE project_id = p_project_id
2327: AND parent_structure_version_id = c_structure_version_id
2328: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2329: WHERE parent_structure_version_id = c_structure_version_id
2324: SELECT element_version_id
2325: FROM pa_proj_element_versions
2326: WHERE project_id = p_project_id
2327: AND parent_structure_version_id = c_structure_version_id
2328: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2329: WHERE parent_structure_version_id = c_structure_version_id
2330: AND display_sequence < ( SELECT display_sequence FROM pa_proj_element_versions
2331: WHERE element_version_id = c_task_version_id )
2332: AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
2326: WHERE project_id = p_project_id
2327: AND parent_structure_version_id = c_structure_version_id
2328: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2329: WHERE parent_structure_version_id = c_structure_version_id
2330: AND display_sequence < ( SELECT display_sequence FROM pa_proj_element_versions
2331: WHERE element_version_id = c_task_version_id )
2332: AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
2333: WHERE element_version_id = c_task_version_id )
2334: )
2328: AND display_sequence = ( SELECT max( display_sequence ) FROM pa_proj_element_versions
2329: WHERE parent_structure_version_id = c_structure_version_id
2330: AND display_sequence < ( SELECT display_sequence FROM pa_proj_element_versions
2331: WHERE element_version_id = c_task_version_id )
2332: AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
2333: WHERE element_version_id = c_task_version_id )
2334: )
2335: AND object_type = 'PA_TASKS';
2336:
2351:
2352: CURSOR cur_pa_proj_elem_ver( c_task_version_id NUMBER )
2353: IS
2354: SELECT proj_element_id
2355: FROM pa_proj_element_versions
2356: WHERE project_id = p_project_id
2357: AND element_version_id = c_task_version_id
2358: AND object_type = 'PA_TASKS';
2359:
2359:
2360: CURSOR cur_pa_proj_elem_vers2( c_task_version_id NUMBER )
2361: IS
2362: SELECT 'x'
2363: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2364: WHERE c.project_id = p_project_id
2365: AND a.structure_type_id = b.structure_type_id
2366: AND b.proj_element_id = c.proj_element_id
2367: AND a.structure_type = 'WORKPLAN'
2365: AND a.structure_type_id = b.structure_type_id
2366: AND b.proj_element_id = c.proj_element_id
2367: AND a.structure_type = 'WORKPLAN'
2368: AND element_version_id = ( SELECT parent_structure_version_id
2369: FROM pa_proj_element_versions
2370: WHERE element_version_id = c_task_version_id );
2371:
2372: -- xxlu added for DFF attributes for calling update_schedule_version
2373: CURSOR cur_ver_sch_attr_rec(c_element_version_id IN NUMBER) IS
2397: /* Bug 2736392 Begin */
2398: CURSOR cur_struc_ver_wp2
2399: IS
2400: SELECT c.element_version_id
2401: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2402: ,pa_proj_elem_ver_structure d
2403: WHERE c.project_id = p_project_id
2404: AND a.structure_type_id = b.structure_type_id
2405: AND b.proj_element_id = c.proj_element_id
2475: ,ppevs.actual_start_date
2476: ,ppevs.actual_finish_date
2477: FROM
2478: pa_proj_elem_ver_schedule ppevs
2479: ,pa_proj_element_versions ppv
2480: WHERE
2481: ppevs.project_id = ppv.project_id
2482: and ppevs.proj_element_id = ppv.proj_element_id
2483: and ppv.object_type = 'PA_TASKS'
3513: API_ERROR EXCEPTION;
3514:
3515: CURSOR cur_task_ver_id IS
3516: SELECT element_version_id, record_version_number
3517: FROM pa_proj_element_versions ppev
3518: WHERE proj_element_id = p_task_id
3519: AND object_type = 'PA_TASKS'
3520: AND parent_structure_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID( p_project_id ); -- bug 2765115
3521:
3520: AND parent_structure_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID( p_project_id ); -- bug 2765115
3521:
3522: CURSOR cur_task_ver_id2 IS
3523: SELECT record_version_number
3524: FROM pa_proj_element_versions ppev
3525: WHERE element_version_id = p_task_version_id
3526: AND object_type = 'PA_TASKS';
3527:
3528: -- bug 2765115
3533: l_template_flag VARCHAR2(1) := 'N';
3534:
3535: CURSOR cur_task_ver_id3 IS
3536: SELECT element_version_id, record_version_number
3537: FROM pa_proj_element_versions ppev
3538: WHERE proj_element_id = p_task_id
3539: AND object_type = 'PA_TASKS'
3540: AND project_id = p_project_id;
3541: -- bug 2765115
3657:
3658: CURSOR cur_str_ver
3659: IS
3660: SELECT rowid, element_version_id
3661: FROM pa_proj_element_versions
3662: WHERE OBJECT_TYPE = 'PA_STRUCTURES'
3663: AND project_id = p_project_id;
3664:
3665: CURSOR cur_task_ver( c_parent_structure_version_id NUMBER )
3664:
3665: CURSOR cur_task_ver( c_parent_structure_version_id NUMBER )
3666: IS
3667: SELECT rowid, element_version_id
3668: FROM pa_proj_element_versions
3669: WHERE OBJECT_TYPE = 'PA_TASKS'
3670: AND project_id = p_project_id
3671: AND parent_structure_version_id = c_parent_structure_version_id;
3672:
3710: WHERE relationship_type = 'S' AND
3711: (object_id_from1 = cur_task_ver_rec.element_version_id OR
3712: object_id_to1 = cur_task_ver_rec.element_version_id);
3713:
3714: PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW( cur_task_ver_rec.rowid );
3715:
3716:
3717: END LOOP;
3718:
3734: WHERE relationship_type = 'S' AND
3735: (object_id_from1 = cur_str_ver_rec.element_version_id OR
3736: object_id_to1 = cur_str_ver_rec.element_version_id);
3737:
3738: PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW( cur_str_ver_rec.rowid );
3739:
3740: END LOOP;
3741:
3742: FOR cur_proj_elems_rec IN cur_proj_elems LOOP
3983:
3984: CURSOR cur_pa_proj_elem_vers( c_structure_id NUMBER )
3985: IS
3986: SELECT element_version_id, record_version_number
3987: FROM pa_proj_element_versions
3988: WHERE project_id = p_project_id
3989: AND object_type = 'PA_STRUCTURES'
3990: AND proj_element_id = c_structure_id;
3991:
3997:
3998: CURSOR cur_pa_proj_elem_vers2
3999: IS
4000: SELECT element_version_id
4001: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
4002: WHERE c.project_id = p_project_id
4003: AND a.structure_type_id = b.structure_type_id
4004: AND b.proj_element_id = c.proj_element_id
4005: AND a.structure_type = 'WORKPLAN';
4006:
4007: CURSOR cur_pa_proj_elem_vers3( c_parent_struc_ver_id NUMBER )
4008: IS
4009: SELECT element_version_id
4010: FROM pa_proj_element_versions
4011: WHERE project_id = p_project_id
4012: AND parent_structure_version_id = c_parent_struc_ver_id
4013: AND object_type = 'PA_TASKS';
4014:
4405:
4406: CURSOR cur_pa_proj_elem_vers
4407: IS
4408: SELECT element_version_id
4409: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
4410: WHERE c.project_id = p_project_id
4411: AND a.structure_type_id = b.structure_type_id
4412: AND b.proj_element_id = c.proj_element_id
4413: AND a.structure_type = 'FINANCIAL';
4414:
4415: CURSOR cur_task_vers( c_parent_str_ver_id NUMBER )
4416: IS
4417: SELECT element_version_id, proj_element_id
4418: FROM pa_proj_element_versions
4419: WHERE project_id = p_project_id
4420: AND parent_structure_version_id = c_parent_str_ver_id
4421: AND object_type = 'PA_TASKS';
4422:
4442:
4443: --it is possible that COSTING and WORKPLAN str are same. so delete from schedule versions as well.
4444: DELETE FROM pa_proj_elem_ver_schedule WHERE element_version_id = cur_task_vers_rec.element_version_id and project_id = p_project_id;
4445:
4446: DELETE FROM pa_proj_element_versions WHERE element_version_id = cur_task_vers_rec.element_version_id;
4447:
4448: DELETE FROM pa_proj_elements WHERE proj_element_id = cur_task_vers_rec.proj_element_id;
4449:
4450: END LOOP;
4732: select segment1, name from pa_projects_all
4733: where project_id = p_project_id;
4734:
4735: CURSOR cur_elem_ver_seq IS
4736: SELECT pa_proj_element_versions_s.nextval
4737: FROM sys.dual;
4738:
4739: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
4740: select nvl(max(version_number),0)+1
4817: l_new_structure_version_id := x_structure_version_id ;
4818:
4819: -- End 4657794
4820:
4821: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
4822: X_ROW_ID => X_row_id
4823: --,X_ELEMENT_VERSION_ID => x_structure_version_id * commented got Bug Fix: 4537865
4824: ,X_ELEMENT_VERSION_ID => l_new_structure_version_id --added for Bug Fix: 4537865
4825: ,X_PROJ_ELEMENT_ID => x_structure_id
5005: and proj_element_id = c_proj_elem_id
5006: and status_code <> 'STRUCTURE_PUBLISHED';
5007:
5008: CURSOR cur_elem_ver_seq IS
5009: SELECT pa_proj_element_versions_s.nextval
5010: FROM sys.dual;
5011:
5012: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
5013: select nvl(max(version_number),0)+1
5089: l_new_structure_version_id := x_structure_version_id ;
5090:
5091: -- End 4657794
5092:
5093: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
5094: X_ROW_ID => X_row_id
5095: --,X_ELEMENT_VERSION_ID => x_structure_version_id * Commented for Bug Fix: 4537865
5096: ,X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for Bug fix: 4537865
5097: ,X_PROJ_ELEMENT_ID => l_proj_element_id
5224: l_new_structure_version_id := x_structure_version_id ;
5225:
5226: -- End 4657794
5227:
5228: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
5229: X_ROW_ID => X_row_id
5230: --,X_ELEMENT_VERSION_ID => x_structure_version_id * commented for Bug fix: 4537865
5231: , X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for Bug fix: 4537865
5232: ,X_PROJ_ELEMENT_ID => l_proj_element_id
5675:
5676: CURSOR cur_proj_elems( c_parent_struc_ver_id NUMBER )
5677: IS
5678: SELECT element_version_id, parent_structure_version_id
5679: FROM pa_proj_element_versions
5680: WHERE project_id = p_project_id
5681: AND proj_element_id = p_task_id
5682: AND parent_structure_version_id = c_parent_struc_ver_id
5683: AND object_type = 'PA_TASKS';
6059:
6060: CURSOR cur_pa_proj_elem_ver( c_task_ver_id NUMBER )
6061: IS
6062: SELECT wbs_level
6063: FROM pa_proj_element_versions
6064: WHERE element_version_id = c_task_ver_id;
6065:
6066: i NUMBER;
6067: j NUMBER := 1;
6258: IS
6259: --bug 3074706
6260: CURSOR c2 IS
6261: select project_id
6262: from pa_proj_element_versions
6263: where element_version_id = p_structure_version_id;
6264: l_project_id NUMBER;
6265:
6266: CURSOR c1 IS
6264: l_project_id NUMBER;
6265:
6266: CURSOR c1 IS
6267: select MAX(a.last_update_date)
6268: from pa_proj_element_versions b,
6269: pa_proj_elem_ver_schedule a,
6270: pa_proj_elem_ver_structure c
6271: where p_structure_version_id = c.element_version_id
6272: and l_project_id = c.project_id
6278: and a.proj_element_id (+) = b.proj_element_id;
6279: /*
6280: CURSOR c1 IS
6281: SELECT MAX(a.last_update_date)
6282: FROM pa_proj_element_versions b,
6283: pa_proj_elem_ver_schedule a,
6284: pa_proj_elem_ver_structure c
6285: WHERE a.element_version_id (+)= b.element_version_id
6286: AND a.project_id (+) = b.project_id
6350:
6351: --This cursor retrieves the LATEST PUBLISHED WP version from the source project
6352: CURSOR cur_get_src_fin_version_id IS
6353: SELECT ppev.element_version_id
6354: FROM pa_proj_element_versions ppev,
6355: pa_proj_elem_ver_structure ppevs
6356: WHERE ppev.project_id = p_src_project_id
6357: AND ppev.object_type = 'PA_STRUCTURES'
6358: AND ppev.element_version_id = ppevs.element_version_id
6361:
6362: --This cursor retrieves the CURRENT WORKING WP version from the source project
6363: CURSOR cur_get_src_fin_version_id2 IS
6364: SELECT ppev.element_version_id
6365: FROM pa_proj_element_versions ppev,
6366: pa_proj_elem_ver_structure ppevs
6367: WHERE ppev.project_id = p_src_project_id
6368: AND ppev.object_type = 'PA_STRUCTURES'
6369: AND ppev.element_version_id = ppevs.element_version_id
6372:
6373: --This cursor retrieves the version for the financial structure form the source project
6374: CURSOR cur_get_split_fin_ver IS
6375: SELECT ppev.element_version_id
6376: FROM pa_proj_element_versions ppev,
6377: pa_proj_structure_types ppst,
6378: pa_structure_types pst
6379: WHERE ppev.project_id = p_src_project_id
6380: AND ppev.object_type = 'PA_STRUCTURES'
6482: IF l_debug_mode = 'Y' THEN
6483: Pa_Debug.WRITE(G_PKG_NAME,'Source financial version id : '||l_src_fin_version_id, l_debug_level3);
6484: END IF;
6485:
6486: --Copy entry into pa_proj_element_versions for the financial version in the source project
6487: INSERT INTO pa_proj_element_versions(
6488: ELEMENT_VERSION_ID
6489: ,PROJ_ELEMENT_ID
6490: ,OBJECT_TYPE
6483: Pa_Debug.WRITE(G_PKG_NAME,'Source financial version id : '||l_src_fin_version_id, l_debug_level3);
6484: END IF;
6485:
6486: --Copy entry into pa_proj_element_versions for the financial version in the source project
6487: INSERT INTO pa_proj_element_versions(
6488: ELEMENT_VERSION_ID
6489: ,PROJ_ELEMENT_ID
6490: ,OBJECT_TYPE
6491: ,PROJECT_ID
6520: ,source_object_id
6521: ,source_object_type
6522: )
6523: SELECT
6524: pa_proj_element_versions_s.nextval
6525: ,p_dest_fn_structure_id --This would be the WP/FIN structure id in shared case and FIN structure id in split case
6526: ,ppev.object_type
6527: ,p_dest_project_id
6528: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
6524: pa_proj_element_versions_s.nextval
6525: ,p_dest_fn_structure_id --This would be the WP/FIN structure id in shared case and FIN structure id in split case
6526: ,ppev.object_type
6527: ,p_dest_project_id
6528: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
6529: ,ppev.DISPLAY_SEQUENCE
6530: ,ppev.WBS_LEVEL
6531: ,ppev.WBS_NUMBER
6532: ,SYSDATE ------CREATION_DATE
6554: ,ppev.TASK_UNPUB_VER_STATUS_CODE
6555: ,ppev.FINANCIAL_TASK_FLAG
6556: ,p_dest_project_id
6557: ,'PA_PROJECTS'
6558: FROM pa_proj_element_versions ppev
6559: WHERE ppev.project_id = p_src_project_id
6560: AND ppev.element_version_id = l_src_fin_version_id ;
6561:
6562: OPEN cur_get_wp_attr;
6665: --If SPLIT and fin enabled case, copy cw from source, else populate it as Y
6666: ,p_dest_project_id
6667: ,'PA_PROJECTS'
6668: FROM pa_proj_elem_ver_structure ppevs,
6669: pa_proj_element_versions ppev
6670: WHERE ppevs.project_id = p_src_project_id
6671: AND ppevs.element_version_id = l_src_fin_version_id
6672: AND ppev.attribute15 = ppevs.element_version_id
6673: AND ppev.project_id = p_dest_project_id ;
6766: ) IS
6767: --This cursor retrieves the LATEST PUBLISHED WP version from the source project
6768: CURSOR cur_get_src_fin_version_id IS
6769: SELECT ppev.element_version_id
6770: FROM pa_proj_element_versions ppev,
6771: pa_proj_elem_ver_structure ppevs
6772: WHERE ppev.project_id = p_src_project_id
6773: AND ppev.object_type = 'PA_STRUCTURES'
6774: AND ppev.element_version_id = ppevs.element_version_id
6777:
6778: --This cursor retrieves the CURRENT WORKING WP version from the source project
6779: CURSOR cur_get_src_fin_version_id2 IS
6780: SELECT ppev.element_version_id
6781: FROM pa_proj_element_versions ppev,
6782: pa_proj_elem_ver_structure ppevs
6783: WHERE ppev.project_id = p_src_project_id
6784: AND ppev.object_type = 'PA_STRUCTURES'
6785: AND ppev.element_version_id = ppevs.element_version_id
6788:
6789: --This cursor retrieves the version for the financial structure form the source project
6790: CURSOR cur_get_split_fin_ver IS
6791: SELECT ppev.element_version_id
6792: FROM pa_proj_element_versions ppev,
6793: pa_proj_structure_types ppst,
6794: pa_structure_types pst
6795: WHERE ppev.project_id = p_src_project_id
6796: AND ppev.object_type = 'PA_STRUCTURES'
6882: START WITH object_id_from1 = c_src_fin_version_id
6883: AND RELATIONSHIP_TYPE = 'S'
6884: CONNECT BY object_id_from1 = PRIOR object_id_to1
6885: AND RELATIONSHIP_TYPE = 'S' ) pobj,
6886: pa_proj_element_versions ppev1,
6887: pa_proj_element_versions ppev2
6888: WHERE ppev1.attribute15 = pobj.object_id_from1
6889: AND ppev2.attribute15 = pobj.object_id_to1
6890: AND ppev1.project_id = c_dest_project_id
6883: AND RELATIONSHIP_TYPE = 'S'
6884: CONNECT BY object_id_from1 = PRIOR object_id_to1
6885: AND RELATIONSHIP_TYPE = 'S' ) pobj,
6886: pa_proj_element_versions ppev1,
6887: pa_proj_element_versions ppev2
6888: WHERE ppev1.attribute15 = pobj.object_id_from1
6889: AND ppev2.attribute15 = pobj.object_id_to1
6890: AND ppev1.project_id = c_dest_project_id
6891: AND ppev2.project_id = c_dest_project_id ;
7125: ,p_dest_project_id
7126: ,'PA_PROJECTS'
7127: FROM pa_task_types ptt,
7128: pa_proj_elements ppe,
7129: pa_proj_element_versions ppev,
7130: pa_tasks pt
7131: WHERE pt.project_id = p_dest_project_id
7132: AND ppe.project_id = p_src_project_id --Added by Sunkalya For the fix of Bug#4600825. This change is done for R12
7133: --Merging of code fix done thru Bug#4589176(115.315) for performance issues.
7139: AND ppe.parent_structure_id = ppev.proj_element_id
7140: AND ppev.element_version_id = l_src_fin_version_id ;
7141:
7142: --Copy tasks versions belonging to the single financial version in the source project
7143: INSERT INTO pa_proj_element_versions(
7144: ELEMENT_VERSION_ID
7145: ,PROJ_ELEMENT_ID
7146: ,OBJECT_TYPE
7147: ,PROJECT_ID
7176: ,source_object_id
7177: ,source_object_type
7178: )
7179: SELECT
7180: pa_proj_element_versions_s.nextval
7181: ,ppe.proj_element_id
7182: ,ppev.object_type
7183: ,p_dest_project_id
7184: ,ppev2.element_version_id
7210: ,decode( ppevs.status_code, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
7211: ,ppev.FINANCIAL_TASK_FLAG
7212: ,p_dest_project_id
7213: ,'PA_PROJECTS'
7214: FROM pa_proj_element_versions ppev,
7215: pa_proj_elements ppe,
7216: pa_proj_element_versions ppev2,
7217: pa_proj_elem_ver_structure ppevs
7218: WHERE ppev.project_id = p_src_project_id
7212: ,p_dest_project_id
7213: ,'PA_PROJECTS'
7214: FROM pa_proj_element_versions ppev,
7215: pa_proj_elements ppe,
7216: pa_proj_element_versions ppev2,
7217: pa_proj_elem_ver_structure ppevs
7218: WHERE ppev.project_id = p_src_project_id
7219: AND ppev.object_type = 'PA_TASKS'
7220: AND ppev.parent_structure_version_id = l_src_fin_version_id
7360: START WITH object_id_from1 = l_src_fin_version_id
7361: AND RELATIONSHIP_TYPE = 'S'
7362: CONNECT BY object_id_from1 = PRIOR object_id_to1
7363: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7364: pa_proj_element_versions ppev1,
7365: pa_proj_element_versions ppev2
7366: WHERE ppev1.attribute15 = pobj.object_id_from1
7367: AND ppev2.attribute15 = pobj.object_id_to1
7368: AND ppev1.project_id = p_dest_project_id
7361: AND RELATIONSHIP_TYPE = 'S'
7362: CONNECT BY object_id_from1 = PRIOR object_id_to1
7363: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7364: pa_proj_element_versions ppev1,
7365: pa_proj_element_versions ppev2
7366: WHERE ppev1.attribute15 = pobj.object_id_from1
7367: AND ppev2.attribute15 = pobj.object_id_to1
7368: AND ppev1.project_id = p_dest_project_id
7369: AND ppev2.project_id = p_dest_project_id ;
7491: ,ppevs.ATTRIBUTE15
7492: ,p_dest_project_id
7493: ,'PA_PROJECTS'
7494: FROM pa_proj_elem_ver_schedule ppevs,
7495: pa_proj_element_versions ppev1,
7496: pa_proj_element_versions ppev2
7497: WHERE ppev1.project_id = p_dest_project_id
7498: AND ppev1.attribute15 = ppevs.element_version_id
7499: AND ppevs.project_id = p_src_project_id
7492: ,p_dest_project_id
7493: ,'PA_PROJECTS'
7494: FROM pa_proj_elem_ver_schedule ppevs,
7495: pa_proj_element_versions ppev1,
7496: pa_proj_element_versions ppev2
7497: WHERE ppev1.project_id = p_dest_project_id
7498: AND ppev1.attribute15 = ppevs.element_version_id
7499: AND ppevs.project_id = p_src_project_id
7500: AND ppev1.attribute15 = ppev2.element_version_id
7652: CURSOR cur_get_dest_wp_struc_versions(c_dest_wp_struc_id IN NUMBER) IS
7653: SELECT ppev.element_version_id
7654: ,ppev.attribute15
7655: ,ppcot.flag
7656: FROM pa_proj_element_versions ppev,
7657: pa_project_copy_options_tmp ppcot
7658: WHERE ppev.project_id = p_dest_project_id
7659: AND ppev.proj_element_id = c_dest_wp_struc_id
7660: AND ppev.attribute15 = ppcot.version_id
7685:
7686: --This cursor will retrieve the single version id created for the shared structure
7687: CURSOR cur_get_shared_ver_id IS
7688: SELECT ppev.element_version_id
7689: FROM pa_proj_element_versions ppev
7690: WHERE ppev.project_id = p_dest_project_id
7691: AND ppev.object_type = 'PA_STRUCTURES'
7692: AND PA_PROJ_ELEMENTS_UTILS.check_fin_or_wp_structure( ppev.proj_element_id ) = 'Y' ;
7693:
7716: WHERE project_id = l_prj_id ;
7717:
7718: CURSOR c_only_wp_ver_id(l_proj_id IN NUMBER) IS
7719: SELECT ppv.element_version_id
7720: FROM pa_proj_element_versions ppv ,
7721: pa_proj_structure_types pps,
7722: pa_structure_types pst
7723: WHERE ppv.proj_element_id = pps.proj_element_id
7724: AND ppv.object_type = 'PA_STRUCTURES'
7726: AND pst.structure_type = 'WORKPLAN'
7727: AND ppv.project_id = l_proj_id ;
7728:
7729: l_template_flag PA_PROJECTS_ALL.template_flag%TYPE ;
7730: l_only_wp_ver_id PA_PROJ_ELEMENT_VERSIONS.element_version_id%TYPE ;
7731:
7732: /*End of declaration included by avaithia for Bug 3922019*/
7733:
7734: l_old_proj_org_id NUMBER;
7893: START WITH object_id_from1 = c_src_str_version_id
7894: AND RELATIONSHIP_TYPE = 'S'
7895: CONNECT BY object_id_from1 = PRIOR object_id_to1
7896: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7897: pa_proj_element_versions ppev1,
7898: pa_proj_element_versions ppev2
7899: WHERE ppev1.attribute15 = pobj.object_id_from1
7900: AND ppev2.attribute15 = pobj.object_id_to1
7901: AND ppev1.parent_structure_version_id = c_struc_version_id
7894: AND RELATIONSHIP_TYPE = 'S'
7895: CONNECT BY object_id_from1 = PRIOR object_id_to1
7896: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7897: pa_proj_element_versions ppev1,
7898: pa_proj_element_versions ppev2
7899: WHERE ppev1.attribute15 = pobj.object_id_from1
7900: AND ppev2.attribute15 = pobj.object_id_to1
7901: AND ppev1.parent_structure_version_id = c_struc_version_id
7902: AND ppev2.parent_structure_version_id = c_struc_version_id ;
8022: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
8023: RAISE FND_API.G_EXC_ERROR;
8024: END IF;
8025:
8026: --Update back the attributes column in pa_proj_elements and pa_proj_element_versions with actual data from source project
8027: UPDATE pa_proj_elements ppe1
8028: SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
8029: WHERE ppe2.project_id = p_src_project_id
8030: AND ppe2.proj_element_id = ppe1.attribute15 )
8029: WHERE ppe2.project_id = p_src_project_id
8030: AND ppe2.proj_element_id = ppe1.attribute15 )
8031: WHERE project_id = p_dest_project_id ;
8032:
8033: UPDATE pa_proj_element_versions ppevs1
8034: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
8035: WHERE ppevs2.project_id = p_src_project_id
8036: AND ppevs2.element_version_id = ppevs1.attribute15 )
8037: WHERE project_id = p_dest_project_id ;
8030: AND ppe2.proj_element_id = ppe1.attribute15 )
8031: WHERE project_id = p_dest_project_id ;
8032:
8033: UPDATE pa_proj_element_versions ppevs1
8034: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
8035: WHERE ppevs2.project_id = p_src_project_id
8036: AND ppevs2.element_version_id = ppevs1.attribute15 )
8037: WHERE project_id = p_dest_project_id ;
8038:
8437: Pa_Debug.WRITE(G_PKG_NAME, 'Inserting versions and tasks for selected WP version(s)', l_debug_level3);
8438: END IF;
8439:
8440: --Copy SELECTED workplan versions
8441: INSERT INTO pa_proj_element_versions(
8442: ELEMENT_VERSION_ID
8443: ,PROJ_ELEMENT_ID
8444: ,OBJECT_TYPE
8445: ,PROJECT_ID
8477: ,source_object_id
8478: ,source_object_type
8479: )
8480: SELECT
8481: pa_proj_element_versions_s.nextval
8482: ,l_dest_wp_structure_id
8483: ,ppev.object_type
8484: ,p_dest_project_id
8485: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
8481: pa_proj_element_versions_s.nextval
8482: ,l_dest_wp_structure_id
8483: ,ppev.object_type
8484: ,p_dest_project_id
8485: ,pa_proj_element_versions_s.nextval --nextval occuring twice in the same select returns the same value
8486: ,ppev.DISPLAY_SEQUENCE
8487: ,ppev.WBS_LEVEL
8488: ,ppev.WBS_NUMBER
8489: ,SYSDATE ------CREATION_DATE
8514: ,NULL --bug 4261419
8515: ,NULL --bug 4261419
8516: ,p_dest_project_id
8517: ,'PA_PROJECTS'
8518: FROM pa_proj_element_versions ppev,
8519: pa_project_copy_options_tmp ppcot
8520: WHERE ppcot.context = 'WORKPLAN'
8521: AND ppev.element_version_id = ppcot.version_id
8522: AND ppev.project_id = p_src_project_id
8834:
8835: --If it is a shared structure and the only WP version selected is Publish Upon Creation, then
8836: --create a working copy of the Published version
8837: IF l_shared_make_working_copy IS NOT NULL THEN
8838: SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
8839: FROM dual;
8840:
8841: SELECT meaning INTO l_prefix
8842: FROM pa_lookups
8848: l_debug_level3);
8849: END IF;
8850:
8851: --Make a copy of the published version
8852: INSERT INTO pa_proj_element_versions(
8853: ELEMENT_VERSION_ID
8854: ,PROJ_ELEMENT_ID
8855: ,OBJECT_TYPE
8856: ,PROJECT_ID
8919: ,ppev.TASK_UNPUB_VER_STATUS_CODE
8920: ,ppev.FINANCIAL_TASK_FLAG
8921: ,ppev.project_id
8922: ,'PA_PROJECTS'
8923: FROM pa_proj_element_versions ppev
8924: WHERE ppev.project_id = p_dest_project_id
8925: AND ppev.element_version_id = l_shared_make_working_copy ;
8926:
8927: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
9150: AND pt.task_number (+) = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
9151: AND pt.project_id (+) = p_dest_project_id
9152: AND ppe.proj_element_id IN
9153: ( SELECT distinct ppev.proj_element_id
9154: FROM PA_PROJ_ELEMENT_VERSIONS ppev,
9155: PA_PROJECT_COPY_OPTIONS_TMP ppcot
9156: WHERE ppcot.CONTEXT = 'WORKPLAN'
9157: AND ppev.parent_structure_version_id = ppcot.version_id
9158: AND ppev.project_id = p_src_project_id
9299: AND ppe.type_id = ptt.task_type_id
9300: AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
9301: AND ppe.proj_element_id IN
9302: ( SELECT distinct ppev.proj_element_id
9303: FROM PA_PROJ_ELEMENT_VERSIONS ppev,
9304: PA_PROJECT_COPY_OPTIONS_TMP ppcot
9305: WHERE ppcot.CONTEXT = 'WORKPLAN'
9306: AND ppev.parent_structure_version_id = ppcot.version_id
9307: AND ppev.project_id = p_src_project_id
9308: AND ppev.object_type = 'PA_TASKS' ) ;
9309: END IF;
9310:
9311: --Copy tasks versions for tasks belonging to the selected wp versions
9312: INSERT INTO pa_proj_element_versions(
9313: ELEMENT_VERSION_ID
9314: ,PROJ_ELEMENT_ID
9315: ,OBJECT_TYPE
9316: ,PROJECT_ID
9345: ,source_object_id
9346: ,source_object_type
9347: )
9348: SELECT
9349: pa_proj_element_versions_s.nextval
9350: ,ppe.proj_element_id
9351: ,ppev.object_type
9352: ,p_dest_project_id
9353: ,ppev2.element_version_id
9380: ,ppev.FINANCIAL_TASK_FLAG --Check if this is correct ?
9381: ,p_dest_project_id
9382: ,'PA_PROJECTS'
9383: FROM pa_proj_elements ppe,
9384: pa_proj_element_versions ppev,
9385: pa_proj_element_versions ppev2,
9386: pa_proj_elem_ver_structure ppevs
9387: WHERE ppe.project_id = p_dest_project_id
9388: AND ppe.object_type = 'PA_TASKS'
9381: ,p_dest_project_id
9382: ,'PA_PROJECTS'
9383: FROM pa_proj_elements ppe,
9384: pa_proj_element_versions ppev,
9385: pa_proj_element_versions ppev2,
9386: pa_proj_elem_ver_structure ppevs
9387: WHERE ppe.project_id = p_dest_project_id
9388: AND ppe.object_type = 'PA_TASKS'
9389: AND ppe.attribute15 = ppev.proj_element_id
9534: START WITH object_id_from1 = rec_dest_wp_struc_versions.attribute15
9535: AND RELATIONSHIP_TYPE = 'S'
9536: CONNECT BY object_id_from1 = PRIOR object_id_to1
9537: AND RELATIONSHIP_TYPE = 'S' ) pobj,
9538: pa_proj_element_versions ppev1,
9539: pa_proj_element_versions ppev2
9540: WHERE ppev1.attribute15 = pobj.object_id_from1
9541: AND ppev2.attribute15 = pobj.object_id_to1
9542: AND ppev1.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
9535: AND RELATIONSHIP_TYPE = 'S'
9536: CONNECT BY object_id_from1 = PRIOR object_id_to1
9537: AND RELATIONSHIP_TYPE = 'S' ) pobj,
9538: pa_proj_element_versions ppev1,
9539: pa_proj_element_versions ppev2
9540: WHERE ppev1.attribute15 = pobj.object_id_from1
9541: AND ppev2.attribute15 = pobj.object_id_to1
9542: AND ppev1.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
9543: AND ppev2.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id ;
9680: ,LEVEL_ASSIGNMENTS_FLAG
9681: ,EXT_ACT_DURATION
9682: ,EXT_REMAIN_DURATION
9683: ,EXT_SCH_DURATION --Bug 3762437
9684: FROM pa_proj_element_versions ppev,
9685: pa_proj_elem_ver_schedule ppevs
9686: WHERE ppev.project_id = p_dest_project_id
9687: AND ppev.parent_structure_version_id = rec_dest_wp_struc_versions.element_version_id
9688: AND ppev.attribute15 = ppevs.element_version_id
9782: --bug 3912603, Satish, this flag is for using this same condition below.
9783: check_str_sel_flag := 'Y';
9784:
9785: --Create new default WP structure version
9786: SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
9787: FROM dual;
9788:
9789: INSERT INTO pa_proj_element_versions(
9790: ELEMENT_VERSION_ID
9785: --Create new default WP structure version
9786: SELECT pa_proj_element_versions_s.nextval INTO l_new_wp_ver_id
9787: FROM dual;
9788:
9789: INSERT INTO pa_proj_element_versions(
9790: ELEMENT_VERSION_ID
9791: ,PROJ_ELEMENT_ID
9792: ,OBJECT_TYPE
9793: ,PROJECT_ID
10006: ) = ( SELECT ppevs.scheduled_start_date
10007: ,ppevs.scheduled_finish_date
10008: ,ppevs.duration
10009: FROM pa_proj_elem_ver_schedule ppevs,
10010: pa_proj_element_versions ppev
10011: WHERE ppevs.project_id = p_dest_project_id
10012: AND ppevs.proj_element_id = ppev.proj_element_id
10013: AND ppevs.element_version_id = ppev.element_version_id
10014: AND ppev.project_id = p_dest_project_id
10185: l_wp_inter_proj_depend_flag VARCHAR2(1);
10186: BEGIN
10187: SELECT Element_Version_ID, attribute15 BULK COLLECT
10188: INTO New_Versions_Tab, Old_Versions_Tab
10189: FROM PA_Proj_Element_Versions
10190: WHERE Project_ID = p_dest_project_id;
10191:
10192: PA_Relationship_Pvt.Copy_Intra_Dependency (
10193: P_Source_Ver_Tbl => Old_Versions_Tab,
10237: BEGIN
10238:
10239: SELECT Element_Version_ID, attribute15 BULK COLLECT
10240: INTO dest_versions_tab, src_Versions_Tab
10241: FROM PA_Proj_Element_Versions
10242: WHERE Project_ID = p_dest_project_id
10243: AND ( PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(element_version_id, 'WORKPLAN') = 'Y')
10244: AND object_type = 'PA_STRUCTURES';
10245:
10353: END IF;
10354: END;
10355:
10356: /* 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*/
10357: /* Now update back the attributes column in pa_proj_elements and pa_proj_element_versions with actual data from source project
10358: UPDATE pa_proj_elements ppe1
10359: SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
10360: WHERE ppe2.project_id = p_src_project_id
10361: AND ppe2.proj_element_id = ppe1.attribute15 )
10360: WHERE ppe2.project_id = p_src_project_id
10361: AND ppe2.proj_element_id = ppe1.attribute15 )
10362: WHERE project_id = p_dest_project_id ;
10363:
10364: UPDATE pa_proj_element_versions ppevs1
10365: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
10366: WHERE ppevs2.project_id = p_src_project_id
10367: AND ppevs2.element_version_id = ppevs1.attribute15 )
10368: WHERE project_id = p_dest_project_id ; */
10361: AND ppe2.proj_element_id = ppe1.attribute15 )
10362: WHERE project_id = p_dest_project_id ;
10363:
10364: UPDATE pa_proj_element_versions ppevs1
10365: SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
10366: WHERE ppevs2.project_id = p_src_project_id
10367: AND ppevs2.element_version_id = ppevs1.attribute15 )
10368: WHERE project_id = p_dest_project_id ; */
10369:
10640: and pst.structure_type in ('WORKPLAN','FINANCIAL');
10641:
10642: CURSOR cur_elem_ver_seq
10643: IS
10644: SELECT pa_proj_element_versions_s.nextval
10645: FROM sys.dual;
10646:
10647: l_get_structure_attr_csr_rec l_get_structure_attr_csr%ROWTYPE;
10648: l_get_structure_attr_csr_rec2 l_get_structure_attr_csr2%ROWTYPE;
10724: start with object_id_from1 = c_src_str_version_id
10725: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
10726: connect by object_id_from1 = prior object_id_to1
10727: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
10728: pa_proj_element_versions ppev1,
10729: pa_proj_element_versions ppev2
10730: WHERE
10731: --bug#3094283 ppev1.project_id = p_dest_project_id
10732: ppev1.attribute15 = pobj.object_id_from1
10725: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
10726: connect by object_id_from1 = prior object_id_to1
10727: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
10728: pa_proj_element_versions ppev1,
10729: pa_proj_element_versions ppev2
10730: WHERE
10731: --bug#3094283 ppev1.project_id = p_dest_project_id
10732: ppev1.attribute15 = pobj.object_id_from1
10733: --bug#3094283 AND ppev2.project_id = p_dest_project_id
10811:
10812: --Bug 2955589.
10813: l_structure_ver_name pa_projects_all.name%TYPE;
10814: l_working_ver_count NUMBER;
10815: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
10816: -- The following two flags will indicate if the respective version has to be
10817: -- created when versioning is enabled and no working version exists for the
10818: -- source project.
10819: l_create_fin_version VARCHAR2(1);
11312:
11313:
11314: --First copy structure version and then copy all task versions
11315: --We cannot perform both with the same sql bcoz we need parent_structure version id for tasks.
11316: INSERT INTO pa_proj_element_versions(
11317: ELEMENT_VERSION_ID
11318: ,PROJ_ELEMENT_ID
11319: ,OBJECT_TYPE
11320: ,PROJECT_ID
11381: ,ppev.ATTRIBUTE14
11382: ,ppev.TASK_UNPUB_VER_STATUS_CODE
11383: ,p_dest_project_id
11384: ,'PA_PROJECTS'
11385: /*from pa_proj_element_versions ppev,
11386: pa_proj_elements ppe
11387: where ppev.project_id = p_src_project_id
11388: and ppe.project_id = p_dest_project_id
11389: and ppev.proj_element_id = ppe.attribute15
11388: and ppe.project_id = p_dest_project_id
11389: and ppev.proj_element_id = ppe.attribute15
11390: and ppe.object_type = 'PA_STRUCTURES'
11391: and ppev.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11392: from pa_proj_element_Versions ppev,
11393: pa_proj_elements ppe
11394: where ppev.project_id = p_src_project_id
11395: and ppe.project_id = p_dest_project_id
11396: and ppev.proj_element_id = ppe.attribute15
11410:
11411:
11412:
11413: SELECT element_version_id INTO l_src_str_version_id
11414: /*FROM pa_proj_element_versions
11415: WHERE project_id = p_src_project_id
11416: AND object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11417: FROM pa_proj_element_versions
11418: WHERE project_id = p_src_project_id
11413: SELECT element_version_id INTO l_src_str_version_id
11414: /*FROM pa_proj_element_versions
11415: WHERE project_id = p_src_project_id
11416: AND object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11417: FROM pa_proj_element_versions
11418: WHERE project_id = p_src_project_id
11419: AND object_type = 'PA_STRUCTURES'
11420: and proj_element_id IN (select ppe2.proj_element_id
11421: from pa_proj_elements ppe2,
12195: FETCH cur_elem_ver_seq INTO l_struc_version_id;
12196: CLOSE cur_elem_ver_seq;
12197: --First copy structure version and then copy all task versions
12198: --We cannot perform both with the same sql bcoz we need parent_structure version id for tasks.
12199: INSERT INTO pa_proj_element_versions(
12200: ELEMENT_VERSION_ID
12201: ,PROJ_ELEMENT_ID
12202: ,OBJECT_TYPE
12203: ,PROJECT_ID
12264: ,ppev.ATTRIBUTE14
12265: ,ppev.TASK_UNPUB_VER_STATUS_CODE
12266: ,p_dest_project_id
12267: ,'PA_PROJECTS'
12268: FROM pa_proj_element_versions ppev,
12269: pa_proj_elements ppe
12270: WHERE ppev.project_id = p_src_project_id
12271: and ppe.project_id = p_dest_project_id
12272: and ppev.element_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12282: AND pa_project_pub.G_structure_in_rec.structure_type = 'WORKPLAN'
12283: THEN
12284: --Copy financial tasks
12285:
12286: INSERT INTO pa_proj_element_versions(
12287: ELEMENT_VERSION_ID
12288: ,PROJ_ELEMENT_ID
12289: ,OBJECT_TYPE
12290: ,PROJECT_ID
12319: ,source_object_type
12320: ,financial_task_flag --Bug no 3651255
12321: )
12322: SELECT
12323: pa_proj_element_versions_s.nextval
12324: ,ppe.proj_element_id
12325: ,ppev.object_type
12326: ,p_dest_project_id
12327: ,l_struc_version_id --from previous sql
12353: ,decode( l_dest_str_versions_tbl(k).dest_str_status, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
12354: ,p_dest_project_id
12355: ,'PA_PROJECTS'
12356: ,ppev.financial_task_flag --Bug no 3651255
12357: FROM pa_proj_element_versions ppev,
12358: pa_proj_elements ppe
12359: WHERE ppev.project_id = p_src_project_id
12360: and ppe.project_id = p_dest_project_id
12361: and ppev.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12488: start with object_id_from1 = l_dest_str_versions_tbl(k).src_str_version_id
12489: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 Added this condition */
12490: /* connect by object_id_from1 = prior object_id_to1
12491: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 Added this condition */
12492: /* pa_proj_element_versions ppev1,
12493: pa_proj_element_versions ppev2
12494: WHERE
12495: --bug#3094283 ppev1.project_id = p_dest_project_id
12496: ppev1.attribute15 = pobj.object_id_from1
12489: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 Added this condition */
12490: /* connect by object_id_from1 = prior object_id_to1
12491: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 Added this condition */
12492: /* pa_proj_element_versions ppev1,
12493: pa_proj_element_versions ppev2
12494: WHERE
12495: --bug#3094283 ppev1.project_id = p_dest_project_id
12496: ppev1.attribute15 = pobj.object_id_from1
12497: --bug#3094283 ppev2.project_id = p_dest_project_id
12751: ,EXT_ACT_DURATION
12752: ,EXT_REMAIN_DURATION
12753: ,EXT_SCH_DURATION --Bug 3762437
12754: FROM pa_proj_elem_ver_schedule ppevs,
12755: pa_proj_element_versions ppev1,
12756: pa_proj_element_versions ppev2
12757: where ppev1.attribute15 = ppevs.element_version_id
12758: and ppevs.project_id = p_src_project_id
12759: and ppev2.project_id = p_src_project_id
12752: ,EXT_REMAIN_DURATION
12753: ,EXT_SCH_DURATION --Bug 3762437
12754: FROM pa_proj_elem_ver_schedule ppevs,
12755: pa_proj_element_versions ppev1,
12756: pa_proj_element_versions ppev2
12757: where ppev1.attribute15 = ppevs.element_version_id
12758: and ppevs.project_id = p_src_project_id
12759: and ppev2.project_id = p_src_project_id
12760: and ppev2.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12869: dest_versions_tab SYSTEM.pa_num_tbl_type;
12870: Begin
12871: Select Element_Version_ID, attribute15 BULK COLLECT
12872: INTO dest_versions_tab, src_Versions_Tab
12873: From PA_Proj_Element_Versions
12874: Where Project_ID = p_dest_project_id
12875: AND ( PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(element_version_id, 'WORKPLAN') = 'Y')
12876: AND object_type = 'PA_STRUCTURES';
12877:
12910: and ppe2.proj_element_id = ppe1.attribute15 )
12911: where project_id = p_dest_project_id
12912: ;
12913:
12914: update pa_proj_element_versions ppevs1
12915: set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
12916: where ppevs2.project_id = p_src_project_id
12917: and ppevs2.element_version_id = ppevs1.attribute15 )
12918: where project_id = p_dest_project_id
12911: where project_id = p_dest_project_id
12912: ;
12913:
12914: update pa_proj_element_versions ppevs1
12915: set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
12916: where ppevs2.project_id = p_src_project_id
12917: and ppevs2.element_version_id = ppevs1.attribute15 )
12918: where project_id = p_dest_project_id
12919: ;
12935:
12936: CURSOR cur_str_vers( c_structure_id NUMBER )
12937: IS
12938: SELECT ppevs.element_version_id
12939: FROM pa_proj_element_versions ppevs
12940: WHERE ppevs.project_id = p_dest_project_id
12941: AND ppevs.object_type = 'PA_STRUCTURES'
12942: AND ppevs.proj_element_id = c_structure_id
12943: ;
13102: ) IS
13103: CURSOR cur_str_ver_id1
13104: IS
13105: SELECT parent_structure_version_id
13106: FROM pa_proj_element_versions
13107: WHERE element_version_id = p_task_version_id;
13108:
13109: CURSOR cur_str_ver_id2
13110: IS
13108:
13109: CURSOR cur_str_ver_id2
13110: IS
13111: SELECT element_version_id, parent_structure_version_id
13112: FROM pa_proj_element_versions
13113: WHERE proj_element_id = p_task_id
13114: AND project_id = p_project_id
13115: ;
13116:
13142:
13143: CURSOR cur_task_ver_id
13144: IS
13145: SELECT element_version_id
13146: FROM pa_proj_element_versions
13147: WHERE proj_element_id = p_task_id
13148: AND parent_structure_version_id = p_structure_version_id
13149: AND project_id = p_project_id;
13150:
13344: WHERE proj_element_id = c_task_id
13345: AND object_type = 'PA_TASKS';
13346:
13347: --Cursor to get the task version id when task id and structure version id is known
13348: 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 )
13349: IS
13350: SELECT element_version_id, record_version_number
13351: FROM pa_proj_element_versions
13352: WHERE project_id = p_project_id
13347: --Cursor to get the task version id when task id and structure version id is known
13348: 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 )
13349: IS
13350: SELECT element_version_id, record_version_number
13351: FROM pa_proj_element_versions
13352: WHERE project_id = p_project_id
13353: AND proj_element_id = c_task_id
13354: AND parent_structure_version_id = c_structure_version_id
13355: AND object_type = 'PA_TASKS';
13383: WHERE proj_element_id = c_task_id
13384: AND project_id = p_project_id;
13385:
13386: --Cursor to get the task id if task element version id is known
13387: CURSOR cur_elem_ver_to_task_id( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13388: IS
13389: SELECT proj_element_id
13390: FROM pa_proj_element_versions
13391: WHERE project_id = p_project_id
13386: --Cursor to get the task id if task element version id is known
13387: CURSOR cur_elem_ver_to_task_id( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13388: IS
13389: SELECT proj_element_id
13390: FROM pa_proj_element_versions
13391: WHERE project_id = p_project_id
13392: AND element_version_id = c_task_version_id;
13393:
13394:
13393:
13394:
13395: --Cursor added for bug 3057575 :
13396: --Cursor to get all the task ver id for a particular Structure version
13397: CURSOR cur_all_task_ver_id( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE )
13398: IS
13399: Select element_version_id
13400: from pa_proj_element_versions
13401: where parent_structure_version_id = c_structure_version_id
13396: --Cursor to get all the task ver id for a particular Structure version
13397: CURSOR cur_all_task_ver_id( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE )
13398: IS
13399: Select element_version_id
13400: from pa_proj_element_versions
13401: where parent_structure_version_id = c_structure_version_id
13402: and object_type = 'PA_TASKS';
13403:
13404: --Cursor added for bug 3057575 :
13402: and object_type = 'PA_TASKS';
13403:
13404: --Cursor added for bug 3057575 :
13405: --Cursor to get the task number from task ver id
13406: CURSOR cur_ver_id_to_task_num( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13407: IS
13408: SELECT p1.element_number
13409: FROM pa_proj_elements p1, pa_proj_element_versions pv1
13410: WHERE pv1.element_version_id = c_task_version_id
13405: --Cursor to get the task number from task ver id
13406: CURSOR cur_ver_id_to_task_num( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13407: IS
13408: SELECT p1.element_number
13409: FROM pa_proj_elements p1, pa_proj_element_versions pv1
13410: WHERE pv1.element_version_id = c_task_version_id
13411: AND p1.proj_element_id = pv1.proj_element_id;
13412:
13413: -- End Cursors Definition
13412:
13413: -- End Cursors Definition
13414:
13415: -- Definition for the table types used in this API
13416: TYPE display_sequence_tbl IS TABLE OF pa_proj_element_versions.display_sequence%TYPE
13417: INDEX BY BINARY_INTEGER;
13418: TYPE wbs_level_tbl IS TABLE OF pa_proj_element_versions.wbs_level%TYPE
13419: INDEX BY BINARY_INTEGER;
13420: TYPE wbs_number_tbl IS TABLE OF pa_proj_element_versions.wbs_number%TYPE
13414:
13415: -- Definition for the table types used in this API
13416: TYPE display_sequence_tbl IS TABLE OF pa_proj_element_versions.display_sequence%TYPE
13417: INDEX BY BINARY_INTEGER;
13418: TYPE wbs_level_tbl IS TABLE OF pa_proj_element_versions.wbs_level%TYPE
13419: INDEX BY BINARY_INTEGER;
13420: TYPE wbs_number_tbl IS TABLE OF pa_proj_element_versions.wbs_number%TYPE
13421: INDEX BY BINARY_INTEGER;
13422: TYPE child_count_tbl IS TABLE OF NUMBER
13416: TYPE display_sequence_tbl IS TABLE OF pa_proj_element_versions.display_sequence%TYPE
13417: INDEX BY BINARY_INTEGER;
13418: TYPE wbs_level_tbl IS TABLE OF pa_proj_element_versions.wbs_level%TYPE
13419: INDEX BY BINARY_INTEGER;
13420: TYPE wbs_number_tbl IS TABLE OF pa_proj_element_versions.wbs_number%TYPE
13421: INDEX BY BINARY_INTEGER;
13422: TYPE child_count_tbl IS TABLE OF NUMBER
13423: INDEX BY BINARY_INTEGER;
13424: TYPE weighting_percentage_tbl IS TABLE OF pa_object_relationships.weighting_percentage%TYPE
13422: TYPE child_count_tbl IS TABLE OF NUMBER
13423: INDEX BY BINARY_INTEGER;
13424: TYPE weighting_percentage_tbl IS TABLE OF pa_object_relationships.weighting_percentage%TYPE
13425: INDEX BY BINARY_INTEGER;
13426: TYPE task_version_id_tbl IS TABLE OF pa_proj_element_versions.element_version_id%TYPE
13427: INDEX BY BINARY_INTEGER;
13428:
13429: ----Table Type added for bug 3057575 :
13430: TYPE task_parent_id_tbl_type IS TABLE OF pa_tasks.task_id%TYPE
13429: ----Table Type added for bug 3057575 :
13430: TYPE task_parent_id_tbl_type IS TABLE OF pa_tasks.task_id%TYPE
13431: INDEX BY BINARY_INTEGER;
13432: ----Table Type added for bug 3057575 :
13433: TYPE task_ver_id_passed_tbl_type IS TABLE OF pa_proj_element_versions.element_version_id%TYPE
13434: INDEX BY BINARY_INTEGER;
13435:
13436: -- End of Definition for the table types
13437:
13436: -- End of Definition for the table types
13437:
13438: -- Header level variables common to all tasks required for update/create task/project cases.
13439: l_structure_id pa_proj_elements.proj_element_id%TYPE;
13440: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
13441: l_task_unpub_ver_status_code pa_proj_element_versions.task_unpub_ver_status_code%TYPE;
13442: l_add_task_security VARCHAR2(1);
13443: l_default_task_type_id pa_proj_elements.type_id%TYPE;
13444: l_update_task_security VARCHAR2(1);
13437:
13438: -- Header level variables common to all tasks required for update/create task/project cases.
13439: l_structure_id pa_proj_elements.proj_element_id%TYPE;
13440: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
13441: l_task_unpub_ver_status_code pa_proj_element_versions.task_unpub_ver_status_code%TYPE;
13442: l_add_task_security VARCHAR2(1);
13443: l_default_task_type_id pa_proj_elements.type_id%TYPE;
13444: l_update_task_security VARCHAR2(1);
13445: l_str_status_code pa_proj_elem_ver_structure.status_code%TYPE;
13460:
13461: -- proj element level variables
13462: l_task_id pa_tasks.task_id%TYPE; -- task id of the current task being processed in loop
13463: l_parent_task_id pa_tasks.parent_task_id%TYPE; -- parent task id of the current task being processed in loop
13464: l_task_version_id pa_proj_element_versions.element_version_id%TYPE; -- stores version id of current task being processed
13465:
13466: -- version level variables required only for update task case.
13467: l_proj_elem_ver_rvn pa_proj_element_versions.record_version_number%TYPE; -- record version number of proj element table
13468: l_proj_elem_ver_rowid VARCHAR2(255); -- rowid of the task being updated.
13463: l_parent_task_id pa_tasks.parent_task_id%TYPE; -- parent task id of the current task being processed in loop
13464: l_task_version_id pa_proj_element_versions.element_version_id%TYPE; -- stores version id of current task being processed
13465:
13466: -- version level variables required only for update task case.
13467: l_proj_elem_ver_rvn pa_proj_element_versions.record_version_number%TYPE; -- record version number of proj element table
13468: l_proj_elem_ver_rowid VARCHAR2(255); -- rowid of the task being updated.
13469: l_relationship_id pa_object_relationships.object_relationship_id%TYPE;
13470: l_obj_rel_rvn NUMBER;
13471:
13469: l_relationship_id pa_object_relationships.object_relationship_id%TYPE;
13470: l_obj_rel_rvn NUMBER;
13471:
13472: -- flexfield attributes used for updating/creating element version and schedule versions table.
13473: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13474: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13475: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13470: l_obj_rel_rvn NUMBER;
13471:
13472: -- flexfield attributes used for updating/creating element version and schedule versions table.
13473: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13474: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13475: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13471:
13472: -- flexfield attributes used for updating/creating element version and schedule versions table.
13473: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13474: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13475: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13472: -- flexfield attributes used for updating/creating element version and schedule versions table.
13473: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13474: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13475: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13473: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
13474: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13475: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13474: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
13475: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13475: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13476: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13477: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13478: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13486: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13479: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13486: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13487: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13480: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13486: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13487: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13488: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13481: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13486: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13487: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13488: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13489: --Bug 6153503
13482: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13486: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13487: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13488: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13489: --Bug 6153503
13490: l_return_msg varchar2(2000);
13483: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13486: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13487: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13488: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13489: --Bug 6153503
13490: l_return_msg varchar2(2000);
13491: l_validate_status varchar2(1);
13484: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
13485: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
13486: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
13487: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
13488: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
13489: --Bug 6153503
13490: l_return_msg varchar2(2000);
13491: l_validate_status varchar2(1);
13492:
13654: -- Bug 6717386
13655: CURSOR cur_struc_ver_wp(c_project_id pa_projects_all.project_id%TYPE, c_structure_type pa_structure_types.structure_type%TYPE)
13656: IS
13657: SELECT c.element_version_id
13658: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
13659: ,pa_proj_elem_ver_structure d
13660: WHERE c.project_id = c_project_id
13661: AND a.structure_type_id = b.structure_type_id
13662: AND b.proj_element_id = c.proj_element_id
13664: AND d.project_id = c.project_id
13665: AND d.element_version_id = c.element_version_id
13666: AND d.status_code = 'STRUCTURE_WORKING';
13667:
13668: l_struct_ver_id pa_proj_element_versions.element_version_id%TYPE;
13669: l_status_code pa_proj_elements.status_code%type;
13670: -- Bug 6717386
13671:
13672: BEGIN
14608: -- no parent task information provided for the current task.
14609: -- get parent information from database
14610: DECLARE
14611:
14612: l_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
14613: l_dummy_number NUMBER;
14614: l_db_parent_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
14615: l_relationship_id_tmp pa_object_relationships.object_relationship_id%TYPE;
14616:
14610: DECLARE
14611:
14612: l_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
14613: l_dummy_number NUMBER;
14614: l_db_parent_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
14615: l_relationship_id_tmp pa_object_relationships.object_relationship_id%TYPE;
14616:
14617: BEGIN
14618:
15155: -- This cursor identified the number of progressible tasks under a parent.
15156: CURSOR get_child_count(c_parent_element_version_id NUMBER)
15157: IS
15158: SELECT count(rel.object_id_to1)
15159: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
15160: WHERE rel.object_id_from1 = c_parent_element_version_id
15161: AND rel.object_type_to = 'PA_TASKS'
15162: AND rel.relationship_type = 'S'
15163: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
15167: AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
15168: AND tt.prog_entry_enable_flag = 'Y';
15169:
15170: l_child_task_count NUMBER;
15171: l_parent_version_id pa_proj_element_versions.element_version_id%TYPE;
15172: l_task_type_id pa_task_types.task_type_id%TYPE;
15173: BEGIN
15174: l_existing_task_set_max_wt := 'N';
15175: IF l_maintain_weightages = 'N' THEN -- no need to maintain weightages.
15383: --Bug 6153503
15384: ELSE -- update task case
15385: DECLARE
15386: --Cursor to get the versioned information of the task
15387: 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 )
15388: IS
15389: SELECT rowid,element_version_id,object_type, project_id, proj_element_id, record_version_number,
15390: display_sequence, wbs_number, wbs_level, parent_structure_version_id,
15391: attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
15390: display_sequence, wbs_number, wbs_level, parent_structure_version_id,
15391: attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
15392: attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12,
15393: attribute13, attribute14, attribute15, TASK_UNPUB_VER_STATUS_CODE
15394: FROM pa_proj_element_versions
15395: WHERE project_id = p_project_id
15396: AND proj_element_id = c_task_id
15397: AND parent_structure_version_id = c_structure_version_id
15398: AND object_type = 'PA_TASKS';
15558: l_proj_elem_ver_rowid := l_cur_proj_elem_ver_info_rec.rowid;
15559: l_proj_elem_ver_rvn := l_cur_proj_elem_ver_info_rec.record_version_number;
15560:
15561: END;
15562: END IF; -- before calling PA_PROJ_ELEMENT_VERSIONS_PKG Insert_Row or Update_Row
15563:
15564: -- In the following block do following validations:
15565: -- for create task case check if its ok to create subtask under the parent task.
15566: -- for update task check if the task is being moved. If moved check if move task is ok.
15597: END IF; --l_parent_task_id IS NOT NULL
15598:
15599: ELSE
15600: DECLARE
15601: l_db_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
15602: l_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
15603:
15604: -- Bug 3075609. This will hold the weightage of the task version currently in database.
15605: l_db_task_weightage pa_object_relationships.weighting_percentage%TYPE;
15598:
15599: ELSE
15600: DECLARE
15601: l_db_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
15602: l_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
15603:
15604: -- Bug 3075609. This will hold the weightage of the task version currently in database.
15605: l_db_task_weightage pa_object_relationships.weighting_percentage%TYPE;
15606: BEGIN
15682: END;
15683: END IF;
15684:
15685: -- call insert row or update row
15686: -- Set the following local variables after calling PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row
15687: -- l_task_version_id_tbl(l_task_id)
15688: -- px_tasks_out_tbl(i).task_version_id
15689:
15690: IF l_task_in_rec.pa_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR
15692: THEN
15693:
15694: -- Call the table handler to create a task version.
15695: IF l_debug_mode = 'Y' THEN
15696: pa_debug.g_err_stage := 'Calling API PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row';
15697: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level4);
15698: END IF;
15699: --added for 4059962 by rtarway
15700: --Derive financial task flag
15743: END IF;
15744: /*
15745: End of Bug Fix 5263429
15746: */
15747: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row
15748: (
15749: X_ROW_ID => l_proj_elem_ver_rowid
15750: ,X_ELEMENT_VERSION_ID => l_task_version_id
15751: ,X_PROJ_ELEMENT_ID => l_task_id
15808: ELSE -- in update task case
15809: -- check for locking
15810: BEGIN
15811: SELECT 'x' INTO l_dummy_char
15812: FROM PA_PROJ_ELEMENT_VERSIONS
15813: WHERE element_version_id = l_task_version_id
15814: AND record_version_number = l_proj_elem_ver_rvn
15815: FOR UPDATE OF record_version_number NOWAIT;
15816: EXCEPTION
15833: END;
15834:
15835: -- Call the table handler to create a task version.
15836: IF l_debug_mode = 'Y' THEN
15837: pa_debug.g_err_stage := 'Calling API PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row';
15838: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level4);
15839: END IF;
15840:
15841: -- Bug 3075609. Since atleast one task is being updated, this structure version is an
15842: -- existing one. Let us maintain a flag to track this so that if this flag is Y we will
15843: -- not recalculate the task weightages if the basis is MANUAL.
15844: l_new_structure_version := 'N';
15845:
15846: PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
15847: X_ROW_ID => l_proj_elem_ver_rowid
15848: ,X_ELEMENT_VERSION_ID => l_task_version_id
15849: ,X_PROJ_ELEMENT_ID => l_task_id
15850: ,X_OBJECT_TYPE => 'PA_TASKS'
15880: pa_debug.g_err_stage := 'Task Version id : ' || l_task_version_id ;
15881: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
15882: END IF;
15883:
15884: END IF; /* After calling PA_PROJ_ELEMENT_VERSIONS_PKG Insert_Row or Update_Row*/
15885:
15886: -- Added for bug 3057575
15887: -- Here we will poulate the task_ver_id_passed_tbl with all the task version Id processed while
15888: -- Updating a project .This is not done while creating a project as we
15894:
15895: -- In following code we will be creating or updating the records in object relationship tables.
15896:
15897: DECLARE
15898: l_structure_version_id_from pa_proj_element_versions.element_version_id%TYPE;
15899: l_task_version_id_from pa_proj_element_versions.element_version_id%TYPE;
15900: l_relationship_subtype pa_object_relationships.relationship_subtype%TYPE;
15901:
15902: BEGIN
15895: -- In following code we will be creating or updating the records in object relationship tables.
15896:
15897: DECLARE
15898: l_structure_version_id_from pa_proj_element_versions.element_version_id%TYPE;
15899: l_task_version_id_from pa_proj_element_versions.element_version_id%TYPE;
15900: l_relationship_subtype pa_object_relationships.relationship_subtype%TYPE;
15901:
15902: BEGIN
15903:
16530: -- reassign the task weightages for the affected peer tasks.
16531: CURSOR cur_get_child_info(c_parent_element_version_id NUMBER)
16532: IS
16533: SELECT rel.OBJECT_RELATIONSHIP_ID,tt.prog_entry_enable_flag
16534: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
16535: WHERE rel.object_id_from1 = c_parent_element_version_id
16536: AND rel.object_type_to = 'PA_TASKS'
16537: AND rel.relationship_type = 'S'
16538: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
16543:
16544: TYPE obj_rel_id_tbl is table of pa_object_relationships.object_relationship_id%TYPE
16545: INDEX BY BINARY_INTEGER;
16546:
16547: l_parent_version_id pa_proj_element_versions.element_version_id%TYPE;
16548: l_prog_entry_enable_flag_tbl PA_PLSQL_DATATYPES.Char1TabTyp;
16549: l_obj_rel_id_tbl obj_rel_id_tbl;
16550: l_plsql_max_array_size CONSTANT NUMBER := 200; -- limiting the max fetch size
16551: l_task_weightage_tbl weighting_percentage_tbl;
17483: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17484: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
17485: ,p_calling_context IN VARCHAR2 := 'UPDATE'
17486: ,p_project_id IN pa_projects_all.project_id%TYPE
17487: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
17488: ,p_pub_struc_ver_id IN NUMBER := NULL
17489: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
17490: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17491: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
17495: -- Cursors used in this Procedure.
17496:
17497: -- This cursor obtains the count of the task versions in the structure version.
17498: CURSOR cur_task_count(c_project_id pa_projects_all.project_id%TYPE,
17499: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
17500: IS
17501: Select count(element_version_id)
17502: From pa_proj_element_versions
17503: Where project_id = c_project_id
17498: CURSOR cur_task_count(c_project_id pa_projects_all.project_id%TYPE,
17499: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
17500: IS
17501: Select count(element_version_id)
17502: From pa_proj_element_versions
17503: Where project_id = c_project_id
17504: And object_type = 'PA_TASKS'
17505: And parent_structure_version_id = c_structure_version_id;
17506:
17743: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17744: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
17745: ,p_calling_context IN VARCHAR2 := 'UPDATE'
17746: ,p_project_id IN pa_projects_all.project_id%TYPE
17747: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
17748: ,p_pub_struc_ver_id IN NUMBER := NULL
17749: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
17750: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17751: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
17757: -- This cursor is used to decide if the structure version corresponds to a particular
17758: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
17759: -- api will be called.
17760: Cursor cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
17761: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
17762: c_structure_type pa_structure_types.structure_type%TYPE)
17763: IS
17764: select 'Y'
17765: from pa_proj_element_versions ver,
17761: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
17762: c_structure_type pa_structure_types.structure_type%TYPE)
17763: IS
17764: select 'Y'
17765: from pa_proj_element_versions ver,
17766: pa_proj_structure_types pst,
17767: pa_structure_types st
17768: where ver.element_version_id = c_structure_version_id
17769: and ver.project_id = c_project_id
18113: ( errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18114: ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18115: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18116: ,p_project_id IN pa_projects_all.project_id%TYPE
18117: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18118: ,p_pub_struc_ver_id IN NUMBER := NULL
18119: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18120: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
18121: )
18437: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18438: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18439: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18440: ,p_project_id IN pa_projects_all.project_id%TYPE
18441: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18442: ,p_pub_struc_ver_id IN NUMBER := NULL
18443: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18444: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
18445: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18452: -- This cursor is used to decide if the structure version corresponds to a particular
18453: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
18454: -- api will be called.
18455: CURSOR cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
18456: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
18457: c_structure_type pa_structure_types.structure_type%TYPE)
18458: IS
18459: SELECT 'Y'
18460: FROM pa_proj_element_versions ver,
18456: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
18457: c_structure_type pa_structure_types.structure_type%TYPE)
18458: IS
18459: SELECT 'Y'
18460: FROM pa_proj_element_versions ver,
18461: pa_proj_structure_types pst,
18462: pa_structure_types st
18463: WHERE ver.element_version_id = c_structure_version_id
18464: AND ver.project_id = c_project_id
18491: --bug 3762882
18492: CURSOR get_prev_published_ver(c_struc_ver_id NUMBER) IS
18493: select a.element_version_id
18494: from pa_proj_elem_ver_structure a,
18495: pa_proj_element_versions b
18496: WHERE a.project_id = p_project_id
18497: AND a.proj_element_id = b.proj_element_id
18498: AND b.element_version_id = c_struc_ver_id
18499: AND a.LATEST_EFF_PUBLISHED_FLAG <> 'Y'
20145: DECLARE
20146: CURSOR cur_all_str_ver
20147: IS
20148: SELECT ppevs2.element_version_id
20149: FROM pa_proj_element_versions ppevs1, pa_proj_element_versions ppevs2
20150: WHERE ppevs1.proj_element_id = ppevs2.proj_element_id
20151: AND ppevs1.project_id = p_project_id
20152: AND ppevs2.project_id = p_project_id
20153: AND ppevs1.element_version_id = p_structure_version_id
20341: ,p_commit IN VARCHAR2
20342: ,p_init_msg_list IN VARCHAR2
20343: ,p_calling_context IN VARCHAR2
20344: ,p_project_id IN pa_projects_all.project_id%TYPE
20345: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
20346: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
20347: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
20348: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
20349: AS
20372:
20373: TYPE object_rel_id_tbl IS TABLE OF pa_object_relationships.object_relationship_id%TYPE
20374: INDEX BY BINARY_INTEGER;
20375:
20376: 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
20377: INDEX BY BINARY_INTEGER;
20378:
20379: TYPE duration_tbl IS TABLE OF pa_proj_elem_ver_schedule.duration%TYPE
20380: INDEX BY BINARY_INTEGER;
20391: -- This cursor fetches the duration, effort, relationship id between the task and its parent
20392: -- and progress entry enable flag for the task type associated with the task version. This
20393: -- info is necessary for calculating the task weightage of the tasks.
20394: CURSOR cur_task_info(c_project_id pa_projects_all.project_id%TYPE,
20395: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE
20396: ,c_plan_version_id NUMBER --bug 4726889
20397: )
20398: IS
20399: Select rel.object_relationship_id,
20401: -- NVL(pfxat.labor_hours,0) + NVL(pfxat.equipment_hours,0),
20402: NVL(pxpv.labor_effort,0) + NVL(pxpv.equipment_effort,0),
20403: sch.duration,
20404: ptt.prog_entry_enable_flag
20405: from pa_proj_element_versions ver,
20406: pa_object_relationships rel,
20407: pa_proj_elem_ver_schedule sch,
20408: pa_proj_elements ele,
20409: pa_task_types ptt,
20432:
20433: -- TWUT
20434: -- This cursor is when fetched will lock all the task versions of the structure version.
20435: CURSOR cur_task_ver_lock(c_project_id pa_projects_all.project_id%TYPE,
20436: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
20437: IS
20438: select 'X'
20439: from pa_proj_element_versions ver,
20440: pa_proj_elements ele,
20435: CURSOR cur_task_ver_lock(c_project_id pa_projects_all.project_id%TYPE,
20436: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
20437: IS
20438: select 'X'
20439: from pa_proj_element_versions ver,
20440: pa_proj_elements ele,
20441: pa_task_types ptt
20442: where ver.project_id = c_project_id
20443: and ver.parent_structure_version_id = c_structure_version_id
20559: END IF;
20560: RETURN;
20561: END IF;
20562:
20563: -- Lock the structure version record in pa_proj_element_versions table.
20564: -- NEED TO REVISIT THIS LOGIC.
20565: -- TWUT. Previously had a select to lock - but it lead to exact fetch
20566: -- returned more than one row exception. Changed to cursor.
20567: DECLARE
20618: l_prog_enable_flag_fetch_tbl prog_enable_flag_tbl;
20619:
20620: l_plsql_max_array_size CONSTANT NUMBER := 200; -- limiting the max fetch size
20621: l_task_count NUMBER := 0;
20622: l_parent_id pa_proj_element_versions.element_version_id%TYPE;
20623: BEGIN
20624: OPEN cur_task_info(p_project_id,p_structure_version_id
20625: ,l_plan_version_id); --4726889
20626: LOOP
20688: -- Use them and calculate the weightage for each of the task.
20689: DECLARE
20690: l_task_weightage_tbl task_weightage_tbl;
20691:
20692: l_parent_id pa_proj_element_versions.element_version_id%TYPE;
20693: l_task_weightage pa_object_relationships.weighting_percentage%TYPE;
20694:
20695: --Bug 3053472 - remaining weightage is no more required.
20696: --l_remaining_weightage pa_object_relationships.weighting_percentage%TYPE;
20886: ,p_commit IN VARCHAR2
20887: ,p_init_msg_list IN VARCHAR2
20888: ,p_calling_context IN VARCHAR2
20889: ,p_project_id IN pa_projects_all.project_id%TYPE
20890: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
20891: ,p_update_wbs_flag IN pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE
20892: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
20893: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
20894: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21029: -- can not be reffered .
21030:
21031: PROCEDURE PROCESS_WBS_UPDATES_WRP_FORM
21032: ( p_project_id IN pa_projects_all.project_id%TYPE
21033: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21034: ,p_pub_struc_ver_id IN NUMBER := NULL
21035: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21036: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21037: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21127:
21128: CURSOR cur_str_vers( c_structure_id NUMBER )
21129: IS
21130: SELECT ppevs.element_version_id
21131: FROM pa_proj_element_versions ppevs
21132: WHERE ppevs.project_id = p_dest_project_id
21133: AND ppevs.object_type = 'PA_STRUCTURES'
21134: AND ppevs.proj_element_id = c_structure_id
21135: ;
21548: --bug 3613766
21549: cursor get_pred_id(c_pred_ref_id VARCHAR2) IS
21550: select ppev1.project_id, ppev1.element_version_id
21551: from pa_proj_elements ppe,
21552: pa_proj_element_versions ppev1,
21553: pa_proj_element_versions ppev2
21554: where ppev2.element_version_id = P_Task_Version_Id
21555: and ppev2.parent_structure_version_id = ppev1.parent_structure_version_id
21556: and ppev1.proj_element_id = ppe.proj_element_id
21549: cursor get_pred_id(c_pred_ref_id VARCHAR2) IS
21550: select ppev1.project_id, ppev1.element_version_id
21551: from pa_proj_elements ppe,
21552: pa_proj_element_versions ppev1,
21553: pa_proj_element_versions ppev2
21554: where ppev2.element_version_id = P_Task_Version_Id
21555: and ppev2.parent_structure_version_id = ppev1.parent_structure_version_id
21556: and ppev1.proj_element_id = ppe.proj_element_id
21557: and ppe.pm_source_reference = c_pred_ref_id;
21715: POR.Relationship_Subtype SubType,
21716: decode(sign(NVL(POR.Lag_Day,0)),
21717: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/(10*8*60) LagDays --bug 4348156
21718: From PA_Proj_Elements PPE,
21719: PA_Proj_Element_Versions PEV,
21720: PA_Object_Relationships POR,
21721: PA_PROJ_ELEMENT_VERSIONS PEV2
21722: Where PPE.project_id = PEV.project_id
21723: and PPE.proj_element_id = PEV.proj_element_id
21717: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/(10*8*60) LagDays --bug 4348156
21718: From PA_Proj_Elements PPE,
21719: PA_Proj_Element_Versions PEV,
21720: PA_Object_Relationships POR,
21721: PA_PROJ_ELEMENT_VERSIONS PEV2
21722: Where PPE.project_id = PEV.project_id
21723: and PPE.proj_element_id = PEV.proj_element_id
21724: and PEV.element_version_id = POR.object_id_to1
21725: and PEV2.ELEMENT_VERSION_ID = POR.object_id_from1
21745: POR.Relationship_Subtype SubType,
21746: decode(sign(NVL(POR.Lag_Day,0)),
21747: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/(10*8*60) LagDays --bug 4348156
21748: From PA_Proj_Elements PPE,
21749: PA_Proj_Element_Versions PEV,
21750: PA_Object_Relationships POR,
21751: PA_PROJ_ELEMENT_VERSIONS PEV2
21752: Where PPE.project_id = PEV.project_id
21753: and PPE.proj_element_id = PEV.proj_element_id
21747: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/(10*8*60) LagDays --bug 4348156
21748: From PA_Proj_Elements PPE,
21749: PA_Proj_Element_Versions PEV,
21750: PA_Object_Relationships POR,
21751: PA_PROJ_ELEMENT_VERSIONS PEV2
21752: Where PPE.project_id = PEV.project_id
21753: and PPE.proj_element_id = PEV.proj_element_id
21754: and PEV.element_version_id = POR.object_id_to1
21755: and PEV2.ELEMENT_VERSION_ID = POR.object_id_from1
21803: END IF;
21804: BEGIN
21805: SELECT object_type
21806: INTO l_object_type
21807: FROM pa_proj_element_versions
21808: WHERE ELEMENT_VERSION_ID = p_element_version_id;
21809: EXCEPTION
21810: WHEN NO_DATA_FOUND THEN
21811: fnd_message.set_name('PA','PA_RECORD_NOT_FOUND');
21838: BEGIN
21839: DELETE FROM pa_object_relationships por1
21840: WHERE por1.object_relationship_id IN
21841: (SELECT por.object_relationship_id
21842: FROM pa_proj_element_versions ppev,
21843: pa_object_relationships por
21844: WHERE relationship_type ='D'
21845: AND ppev.parent_structure_version_id = p_element_version_id
21846: AND ppev.object_type = 'PA_TASKS'