[Home] [Help]
36: ,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
37:
38: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
39: select nvl(max(version_number),0)+1
40: from pa_proj_elem_ver_structure
41: where project_id = c_project_id
42: and proj_element_id = c_proj_element_id
43: and status_code <> 'STRUCTURE_PUBLISHED';
44:
179: CLOSE get_working_ver_num;
180: l_status_code := 'STRUCTURE_WORKING';
181:
182:
183: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
184: X_ROWID => x_row_id
185: , X_PEV_STRUCTURE_ID => x_pev_structure_id
186: , X_ELEMENT_VERSION_ID => x_struc_version_id
187: , X_VERSION_NUMBER => l_struc_ver_number
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
888: AND a.structure_type = p_structure_type
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
906: AND a.structure_type = p_structure_type
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
977: AND a.structure_type = p_structure_type
1101: CLOSE cur_proj_wrk_attr;
1102: IF NVL( l_auto_pub_flag,'N' ) = 'Y' AND
1103: NVL( IS_WP_VERSIONING_ENABLED(p_project_id), 'N' ) = 'Y'
1104: THEN
1105: UPDATE pa_proj_elem_ver_structure
1106: set status_code = 'STRUCTURE_WORKING'
1107: where project_id = p_project_id
1108: and proj_element_id = l_structure_id;
1109: END IF;
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
1129: AND a.structure_type = p_structure_type
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
2281: AND a.structure_type = p_structure_type
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
2300: AND a.structure_type = p_structure_type
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
2406: AND a.structure_type = p_structure_type
2421:
2422: l_tasks_out pa_project_pub.task_out_tbl_type ; --dummy variables. --bug 2856033
2423: l_process_code VARCHAR2(30);
2424:
2425: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE; -- Bug 3091798
2426:
2427: /* Added for bug 3290037 */
2428:
2429: CURSOR cur_task_attr IS
3746: PA_PROJ_ELEMENTS_PKG.DELETE_ROW( cur_proj_elems_rec.rowid );
3747: END LOOP;
3748:
3749: DELETE FROM pa_proj_elem_ver_schedule where project_id = p_project_id;
3750: DELETE FROM pa_proj_elem_ver_structure where project_id = p_project_id;
3751:
3752: /* Code to delete project records from tables: pa_progress_rollup, pa_proj_progress_attr
3753: and pa_proj_workplan_attr. */
3754:
3816: ) RETURN VARCHAR2 IS
3817:
3818: CURSOR cur_pub_versions is
3819: select 'Y'
3820: from pa_proj_elem_ver_structure ppevs,
3821: pa_proj_structure_types ppst,
3822: pa_structure_types pst
3823: where ppevs.project_id = p_project_id
3824: and ppevs.STATUS_CODE = 'STRUCTURE_PUBLISHED'
3866: AND split_cost_from_workplan_flag = 'N';
3867:
3868: CURSOR cur_pub_versions is
3869: select 'Y'
3870: from pa_proj_elem_ver_structure
3871: where project_id = p_project_id
3872: and STATUS_CODE = 'STRUCTURE_PUBLISHED';
3873:
3874: l_approve VARCHAR2(1);
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
4741: from pa_proj_elem_ver_structure
4742: where project_id = c_project_id
4743: and proj_element_id = c_proj_element_id
4744: and status_code <> 'STRUCTURE_PUBLISHED';
4745:
4876: l_latest_eff_published_flag := 'Y';
4877: END IF;
4878: --end FPM bug 3301192
4879:
4880: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
4881: X_ROWID => x_row_id
4882: , X_PEV_STRUCTURE_ID => x_pev_structure_id
4883: , X_ELEMENT_VERSION_ID => x_structure_version_id
4884: , X_VERSION_NUMBER => l_struc_ver_number
4962: )
4963: IS
4964: cursor c1 is
4965: select pev_structure_id, name, record_version_number
4966: from pa_proj_elem_ver_structure
4967: where project_id = p_project_id
4968: and element_version_id = p_structure_version_id;
4969:
4970: l_name VARCHAR2(240);
4999: from pa_proj_workplan_attr
5000: where project_id = p_project_id;
5001:
5002: cursor c4(c_proj_elem_id NUMBER) is
5003: select '1' from pa_proj_elem_ver_structure
5004: where project_id = p_project_id
5005: and proj_element_id = c_proj_elem_id
5006: and status_code <> 'STRUCTURE_PUBLISHED';
5007:
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
5014: from pa_proj_elem_ver_structure
5015: where project_id = c_project_id
5016: and proj_element_id = c_proj_element_id
5017: and status_code <> 'STRUCTURE_PUBLISHED';
5018:
5136: OPEN get_working_ver_num(p_project_id, l_proj_element_id);
5137: FETCH get_working_ver_num INTO l_struc_ver_number;
5138: CLOSE get_working_ver_num;
5139:
5140: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
5141: X_ROWID => x_row_id
5142: , X_PEV_STRUCTURE_ID => x_pev_structure_id
5143: , X_ELEMENT_VERSION_ID => x_structure_version_id
5144: , X_VERSION_NUMBER => l_struc_ver_number
5280: END IF;
5281: --end FPM bug 3301192
5282: CLOSE get_working_ver_num;
5283:
5284: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
5285: X_ROWID => x_row_id
5286: , X_PEV_STRUCTURE_ID => x_pev_structure_id
5287: , X_ELEMENT_VERSION_ID => x_structure_version_id
5288: , X_VERSION_NUMBER => l_struc_ver_number
5684:
5685: CURSOR cur_pa_work_ver
5686: IS
5687: SELECT element_version_id
5688: FROM pa_proj_elem_ver_structure
5689: WHERE project_id = p_project_id
5690: AND status_code = 'STRUCTURE_WORKING';
5691:
5692: l_structure_version_id NUMBER;
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
6273: and c.status_code <> 'STRUCTURE_PUBLISHED'
6274: and b.parent_structure_version_id = c.element_version_id
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
6287: AND a.proj_element_id (+) = b.proj_element_id
6288: AND b.parent_structure_version_id = c.element_version_id
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
6359: AND ppevs.project_id = p_src_project_id
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
6370: AND ppevs.project_id = p_src_project_id
6589: Pa_Debug.WRITE(G_PKG_NAME,'dest_latest_eff_pub_flag : '||dest_latest_eff_pub_flag, l_debug_level3);
6590: Pa_Debug.WRITE(G_PKG_NAME,'dest_current_flag : '||dest_current_flag, l_debug_level3);
6591: END IF;
6592:
6593: --Copy entry into pa_proj_elem_ver_structure for financial version in the source project
6594: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
6595: PEV_STRUCTURE_ID
6596: ,ELEMENT_VERSION_ID
6597: ,VERSION_NUMBER
6590: Pa_Debug.WRITE(G_PKG_NAME,'dest_current_flag : '||dest_current_flag, l_debug_level3);
6591: END IF;
6592:
6593: --Copy entry into pa_proj_elem_ver_structure for financial version in the source project
6594: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
6595: PEV_STRUCTURE_ID
6596: ,ELEMENT_VERSION_ID
6597: ,VERSION_NUMBER
6598: ,NAME
6627: ,source_object_id
6628: ,source_object_type
6629: )
6630: SELECT
6631: pa_proj_elem_ver_structure_s.nextval
6632: ,ppev.element_version_id
6633: ,ppevs.version_number
6634: ,ppevs.name
6635: ,p_dest_project_id
6664: ,decode(p_shared,'N',ppevs.current_working_flag,'Y') --CURRENT_WROKING_FLAG
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
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
6775: AND ppevs.project_id = p_src_project_id
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
6786: AND ppevs.project_id = p_src_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
7221: AND ppev.proj_element_id = ppe.attribute15
7668: AND element_version_id = c_struc_ver_id;
7669:
7670: CURSOR cur_get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
7671: SELECT nvl(max(version_number),0)+1
7672: FROM pa_proj_elem_ver_structure
7673: WHERE project_id = c_project_id
7674: AND proj_element_id = c_proj_element_id
7675: AND status_code <> 'STRUCTURE_PUBLISHED';
7676:
7799: --bug 3912603, Satish, end
7800:
7801: cursor get_published_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
7802: select nvl(max(version_number),0)+1
7803: from pa_proj_elem_ver_structure
7804: where project_id = c_project_id
7805: and proj_element_id = c_proj_element_id
7806: and status_code = 'STRUCTURE_PUBLISHED';
7807:
7806: and status_code = 'STRUCTURE_PUBLISHED';
7807:
7808: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
7809: select nvl(max(version_number),0)+1
7810: from pa_proj_elem_ver_structure
7811: where project_id = c_project_id
7812: and proj_element_id = c_proj_element_id
7813: and status_code <> 'STRUCTURE_PUBLISHED';
7814: --bug 3907862, end
8543:
8544: --Gets the current working version of the source project ONLY if it has been SELECTED for copying
8545: CURSOR get_current_working_selected IS
8546: SELECT ppevs.element_version_id
8547: FROM pa_proj_elem_ver_structure ppevs,
8548: pa_project_copy_options_tmp ppcot
8549: WHERE ppevs.current_working_flag = 'Y'
8550: AND ppevs.status_code = 'STRUCTURE_WORKING'
8551: AND ppevs.project_id = p_src_project_id
8556: --copying and is NOT Publish Upon Creation
8557: CURSOR get_latest_working_version IS
8558: SELECT element_version_id FROM
8559: ( SELECT ppevs.element_version_id
8560: FROM pa_proj_elem_ver_structure ppevs,
8561: pa_project_copy_options_tmp ppcot
8562: WHERE ppevs.element_version_id = ppcot.version_id
8563: AND ppevs.project_id = p_src_project_id
8564: AND ppevs.status_code = 'STRUCTURE_WORKING'
8570: --Gets the LATEST PUBLISHED VERSION from the source project, which is SELECTED for copying and is
8571: --NOT Publish Upon Creation
8572: CURSOR get_latest_published IS
8573: SELECT ppevs.element_version_id
8574: FROM pa_proj_elem_ver_structure ppevs,
8575: pa_project_copy_options_tmp ppcot
8576: WHERE ppevs.latest_eff_published_flag = 'Y'
8577: AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
8578: AND ppevs.element_version_id = ppcot.version_id
8584: --NOT selected for PUBLISH UPON CREATION
8585: CURSOR get_last_published IS
8586: SELECT element_version_id FROM
8587: ( SELECT ppevs.element_version_id
8588: FROM pa_proj_elem_ver_structure ppevs,
8589: pa_project_copy_options_tmp ppcot
8590: WHERE ppevs.element_version_id = ppcot.version_id
8591: AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
8592: AND ppevs.project_id = p_src_project_id
8749: l_debug_level3);
8750: END IF;
8751:
8752:
8753: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
8754: PEV_STRUCTURE_ID
8755: ,ELEMENT_VERSION_ID
8756: ,VERSION_NUMBER
8757: ,NAME
8786: ,source_object_id
8787: ,source_object_type
8788: )
8789: SELECT
8790: pa_proj_elem_ver_structure_s.nextval
8791: ,rec_dest_wp_struc_versions.element_version_id
8792: --,ppevs.version_number --bug 3907862, insert the new version number, intead of coying the old.
8793: ,l_struct_version_number
8794: ,ppevs.name
8823: ,decode(l_versioning_enabled,'N','Y',l_dest_current_working_flag)
8824: --If versioning is disabled, both LATEST PUBLISHED AND CURRENT WORKING FLAGS are 'Y'
8825: ,p_dest_project_id
8826: ,'PA_PROJECTS'
8827: FROM pa_proj_elem_ver_structure ppevs,
8828: pa_proj_elements ppe
8829: WHERE ppevs.project_id = p_src_project_id
8830: AND ppevs.element_version_id = rec_dest_wp_struc_versions.attribute15
8831: AND ppevs.proj_element_id = ppe.attribute15
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(
8928: PEV_STRUCTURE_ID
8929: ,ELEMENT_VERSION_ID
8930: ,VERSION_NUMBER
8931: ,NAME
8960: ,source_object_id
8961: ,source_object_type
8962: )
8963: SELECT
8964: pa_proj_elem_ver_structure_s.nextval
8965: ,l_new_wp_ver_id
8966: ,ppevs.version_number
8967: ,substr(l_prefix||ppevs.name, 1, 240)
8968: ,ppevs.project_id
8994: ,ppevs.process_update_wbs_flag
8995: ,'Y'
8996: ,ppevs.project_id
8997: ,'PA_PROJECTS'
8998: FROM pa_proj_elem_ver_structure ppevs,
8999: pa_proj_elements ppe
9000: WHERE ppevs.project_id = p_dest_project_id
9001: AND ppevs.element_version_id = l_shared_make_working_copy
9002: AND ppevs.proj_element_id = ppe.proj_element_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
9390: AND ppev.project_id = p_src_project_id
9830: IF 'N' = p_dest_template_flag AND 'N' = l_versioning_enabled THEN
9831: l_default_lat_pub_and_cw_flag := 'Y';
9832: END IF;
9833:
9834: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
9835: PEV_STRUCTURE_ID
9836: ,ELEMENT_VERSION_ID
9837: ,VERSION_NUMBER
9838: ,NAME
9858: ,CURRENT_WORKING_FLAG
9859: ,SOURCE_OBJECT_ID
9860: ,source_object_type)
9861: VALUES (
9862: pa_proj_elem_ver_structure_s.nextval
9863: ,l_new_wp_ver_id
9864: ,l_struc_ver_number
9865: ,substr(p_dest_project_name||l_append||l_suffix, 1, 240)
9866: ,p_dest_project_id
10015: AND ppevs.proj_element_id = ppe.proj_element_id
10016: AND ppev.proj_element_id = ppe.proj_element_id
10017: AND ppev.parent_structure_version_id =
10018: ( SELECT ppevst.element_version_id --Changed the inner query for bug 4215666.
10019: FROM pa_proj_elem_ver_structure ppevst,
10020: pa_proj_structure_types struc,
10021: pa_structure_types stype
10022: WHERE ppevst.project_id = p_dest_project_id
10023: AND ppevst.current_flag = 'Y'
10611:
10612: CURSOR l_get_structure_attr_csr
10613: IS
10614: /* SELECT *
10615: FROM PA_PROJ_ELEM_ver_structure ppevs
10616: WHERE project_id = p_src_project_id;*//*Commented below query and following criteria by SMUKKA */
10617: select ppevs.*
10618: from pa_structure_types pst,
10619: pa_proj_structure_types ppst,
10616: WHERE project_id = p_src_project_id;*//*Commented below query and following criteria by SMUKKA */
10617: select ppevs.*
10618: from pa_structure_types pst,
10619: pa_proj_structure_types ppst,
10620: pa_proj_elem_ver_structure ppevs
10621: where ppevs.project_id = p_src_project_id
10622: and ppevs.proj_element_id= ppst.proj_element_id
10623: and ppst.structure_type_id = pst.structure_type_id
10624: and pst.structure_type in ('WORKPLAN','FINANCIAL');
10626: CURSOR l_get_structure_attr_csr2
10627: IS
10628: SELECT ppevs.element_version_id, ppevs.status_code, ppevs.LATEST_EFF_PUBLISHED_FLAG, ppevs.current_flag,
10629: ppst.structure_type_id
10630: /* FROM PA_PROJ_ELEM_ver_structure ppevs,
10631: pa_proj_structure_types ppst
10632: WHERE project_id = p_src_project_id
10633: AND ppst.proj_element_id(+) = ppevs.proj_element_id;*//*Commented below query and following criteria by SMUKKA */
10634: from pa_proj_elem_ver_structure ppevs,
10630: /* FROM PA_PROJ_ELEM_ver_structure ppevs,
10631: pa_proj_structure_types ppst
10632: WHERE project_id = p_src_project_id
10633: AND ppst.proj_element_id(+) = ppevs.proj_element_id;*//*Commented below query and following criteria by SMUKKA */
10634: from pa_proj_elem_ver_structure ppevs,
10635: pa_proj_structure_types ppst,
10636: pa_structure_types pst
10637: where project_id = p_src_project_id
10638: and ppst.proj_element_id(+) = ppevs.proj_element_id
11429:
11430:
11431: --bug 3010538
11432: --added PROCESS_UPDATE_WBS_FLAG
11433: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
11434: PEV_STRUCTURE_ID
11435: ,ELEMENT_VERSION_ID
11436: ,VERSION_NUMBER
11437: ,NAME
11466: ,source_object_id
11467: ,source_object_type
11468: )
11469: SELECT
11470: pa_proj_elem_ver_structure_s.nextval
11471: ,l_struc_version_id
11472: ,1
11473: --,ppevs.name, rtarway, 3655698, replaced ppevs.name with ppe.name
11474: ,ppe.name
11502: ,'N'
11503: ,ppevs.current_working_flag /* FPM bug 3301192 */
11504: ,p_dest_project_id
11505: ,'PA_PROJECTS'
11506: /*FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
11507: WHERE ppevs.project_id = p_src_project_id
11508: AND ppe.project_id = p_dest_project_id
11509: and ppevs.proj_element_id = ppe.attribute15
11510: AND ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11507: WHERE ppevs.project_id = p_src_project_id
11508: AND ppe.project_id = p_dest_project_id
11509: and ppevs.proj_element_id = ppe.attribute15
11510: AND ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11511: FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
11512: WHERE ppevs.project_id = p_src_project_id
11513: AND ppe.project_id = p_dest_project_id
11514: and ppevs.proj_element_id = ppe.attribute15
11515: AND ppe.object_type = 'PA_STRUCTURES'
12532:
12533: --bug 3010538
12534: --added PROCESS_UPDATE_WBS_FLAG
12535:
12536: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
12537: PEV_STRUCTURE_ID
12538: ,ELEMENT_VERSION_ID
12539: ,VERSION_NUMBER
12540: ,NAME
12569: ,source_object_id
12570: ,source_object_type
12571: )
12572: SELECT
12573: pa_proj_elem_ver_structure_s.nextval
12574: ,l_struc_version_id
12575: ,ppevs.version_number --replaced k with source version_number bug 2829385
12576: --,ppevs.name 3717026, replaced ppevs.name with ppe.name
12577: ,ppe.name
12604: ,'N'
12605: ,ppevs.current_working_flag /* FPM bug 3301192 */
12606: ,p_dest_project_id
12607: ,'PA_PROJECTS'
12608: FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
12609: WHERE ppevs.project_id = p_src_project_id
12610: AND ppe.project_id = p_dest_project_id
12611: and ppevs.element_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12612: and ppevs.proj_element_id = ppe.attribute15
12790: BASELINE_DURATION ) =
12791: ( Select ppevs.scheduled_start_date, ppevs.scheduled_finish_date,
12792: ppevs.duration
12793: from pa_proj_elem_ver_schedule ppevs,
12794: pa_proj_elem_ver_structure ppevst
12795: WHERE ppevs.project_id = p_dest_project_id
12796: and ppevst.project_id = p_dest_project_id
12797: and ppevst.current_flag = 'Y' --assuming this is the flag used to identify the baselined structure ver.
12798: and ppevst.STATUS_CODE = 'STRUCTURE_PUBLISHED'
12954: FOR cur_strs_n_types_rec in cur_strs_n_types LOOP
12955: IF cur_strs_n_types_rec.structure_type_id = 1 --'WORKPLAN' structure
12956: THEN
12957: l_wp_str_id := cur_strs_n_types_rec.proj_element_id;
12958: UPDATE pa_proj_elem_ver_structure
12959: SET PROCESS_UPDATE_WBS_FLAG = 'Y'
12960: WHERE proj_element_id = cur_strs_n_types_rec.proj_element_id
12961: AND project_id = p_dest_project_id
12962: AND PROCESS_UPDATE_WBS_FLAG = 'N'
13273: ,p_create_task_version_only IN VARCHAR2
13274: ,p_wp_str_exists IN VARCHAR2
13275: ,p_is_wp_separate_from_fn IN VARCHAR2
13276: ,p_is_wp_versioning_enabled IN VARCHAR2
13277: ,p_structure_version_id IN pa_proj_elem_ver_structure.element_version_id%TYPE --IUP: Populated only in case of Update_project context
13278: -- Included NOCOPY for the following parameter.
13279: -- PA L Changes 3010538
13280: ,p_process_mode IN VARCHAR2
13281: -- Bug 3075609. To identify create task version only context.
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;
13446: l_amg_segment1 pa_projects_all.segment1%TYPE;
13447:
13448: -- Bug 3075609
13449: l_maintain_weightages VARCHAR2(1); -- We need not maintain weightages during code flow always. This var will track it.
13536: l_progressable_task VARCHAR2(1); --bug 3035902 maansari
13537: --end bug 3010538
13538:
13539: -- Bug 3091798
13540: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
13541:
13542: -- Variables added for bug 3057575 :
13543: row_task_ver_id cur_all_task_ver_id%ROWTYPE;
13544: task_parent_id_tbl task_parent_id_tbl_type;
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
13663: AND a.structure_type = c_structure_type
14075: nvl(p_is_wp_versioning_enabled,'N') = 'Y' and
14076: l_source_template_flag = 'Y' --ADUT
14077: THEN
14078:
14079: UPDATE pa_proj_elem_ver_structure
14080: set status_code = 'STRUCTURE_WORKING'
14081: where project_id = p_project_id
14082: and proj_element_id = l_structure_id;
14083:
17506:
17507: --Bug# 5973460
17508: CURSOR c_get_process_code(c_structure_version_id NUMBER) IS
17509: SELECT process_code
17510: FROM pa_proj_elem_ver_structure
17511: WHERE element_version_id = c_structure_version_id;
17512: --Bug# 5973460
17513:
17514: l_msg_count NUMBER := 0;
17521: l_debug_level3 CONSTANT NUMBER := 3;
17522: l_debug_level4 CONSTANT NUMBER := 4;
17523: l_debug_level5 CONSTANT NUMBER := 5;
17524:
17525: l_process_code pa_proj_elem_ver_structure.process_code%TYPE; --Bug# 5973460
17526:
17527: BEGIN
17528:
17529: --Bug# 5973460
17770: and pst.proj_element_id = ver.proj_element_id
17771: and pst.structure_type_id = st.structure_type_id
17772: and st.structure_type = c_structure_type;
17773:
17774: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
17775: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
17776: l_structure_type_flag VARCHAR2(1);
17777: --Bug 3053920.
17778: l_task_weighting_basis pa_proj_progress_attr.task_weight_basis_code%TYPE;
17771: and pst.structure_type_id = st.structure_type_id
17772: and st.structure_type = c_structure_type;
17773:
17774: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
17775: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
17776: l_structure_type_flag VARCHAR2(1);
17777: --Bug 3053920.
17778: l_task_weighting_basis pa_proj_progress_attr.task_weight_basis_code%TYPE;
17779:
17797:
17798: -- Bug 4589289 : Begin
17799: CURSOR c_get_process_code(c_structure_version_id NUMBER) IS
17800: SELECT process_code
17801: FROM pa_proj_elem_ver_structure
17802: WHERE element_version_id = c_structure_version_id
17803: and project_id = p_project_id -- Bug # 4868867.
17804: ;
17805:
17802: WHERE element_version_id = c_structure_version_id
17803: and project_id = p_project_id -- Bug # 4868867.
17804: ;
17805:
17806: l_process_code pa_proj_elem_ver_structure.process_code%TYPE;
17807: l_rerun_flag VARCHAR2(1):=null;
17808:
17809: -- Bug 4589289 : End
17810:
17975:
17976: /*
17977:
17978: -- Update the structure version record with the request id and the status code.
17979: update pa_proj_elem_ver_structure
17980: set process_code = 'WUP',
17981: conc_request_id = l_request_id
17982: where element_version_id = p_structure_version_id
17983: and project_id = p_project_id;
18120: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
18121: )
18122: AS
18123:
18124: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
18125:
18126: l_return_status VARCHAR2(1);
18127: l_msg_count NUMBER := 0;
18128: l_data VARCHAR2(2000);
18205:
18206: -- Update to success if the call was successful.
18207: -- It has been decided to update null as the process_code if the
18208: -- process has completed successfully.
18209: update pa_proj_elem_ver_structure
18210: set process_code = NULL,
18211: conc_request_id = l_request_id
18212: where element_version_id = p_structure_version_id
18213: and project_id = p_project_id;
18219:
18220: -- Mark the newly created published structure record also with the process code
18221: -- as null.
18222:
18223: update pa_proj_elem_ver_structure
18224: set process_code = NULL
18225: , conc_request_id = l_request_id
18226: where element_version_id = p_pub_struc_ver_id
18227: and project_id = p_project_id;
18263: -- Begin fix for Bug # 4373055.
18264:
18265: /*
18266:
18267: update pa_proj_elem_ver_structure
18268: set process_code = 'WUE',
18269: conc_request_id = l_request_id
18270: where element_version_id = p_structure_version_id
18271: and project_id = p_project_id;
18347: -- Begin fix for Bug # 4373055.
18348:
18349: /*
18350:
18351: update pa_proj_elem_ver_structure
18352: set process_code = 'WUE',
18353: conc_request_id = l_request_id
18354: where element_version_id = p_structure_version_id
18355: and project_id = p_project_id;
18465: AND pst.proj_element_id = ver.proj_element_id
18466: AND pst.structure_type_id = st.structure_type_id
18467: AND st.structure_type = c_structure_type;
18468:
18469: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
18470: l_structure_type_flag VARCHAR2(1);
18471: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
18472:
18473: l_msg_count NUMBER := 0;
18467: AND st.structure_type = c_structure_type;
18468:
18469: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
18470: l_structure_type_flag VARCHAR2(1);
18471: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
18472:
18473: l_msg_count NUMBER := 0;
18474: l_data VARCHAR2(2000);
18475: l_msg_data VARCHAR2(2000);
18490:
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
18516: WHERE project_id = c_project_id;
18517:
18518: CURSOR get_struc_id(c_project_id NUMBER, c_struc_ver_id NUMBER) IS
18519: select pevs.proj_element_id
18520: from pa_proj_elem_ver_structure pevs
18521: where pevs.element_version_id = c_struc_ver_id
18522: and pevs.project_id = c_project_id;
18523:
18524: --bug 4472071
18929:
18930: --bug 4019845
18931: --set current published as working for edit
18932:
18933: UPDATE pa_proj_elem_ver_structure
18934: SET status_code = 'STRUCTURE_WORKING',
18935: LOCKED_BY_PERSON_ID = (select locked_by_person_id
18936: from pa_proj_elem_ver_structure
18937: where project_id = l_project_id
18932:
18933: UPDATE pa_proj_elem_ver_structure
18934: SET status_code = 'STRUCTURE_WORKING',
18935: LOCKED_BY_PERSON_ID = (select locked_by_person_id
18936: from pa_proj_elem_ver_structure
18937: where project_id = l_project_id
18938: and element_version_id = p_structure_version_id),
18939: LOCK_STATUS_CODE = 'LOCKED'
18940: WHERE project_id = l_project_id
18960:
18961: --bug 4019845
18962: --set current published as working for edit
18963:
18964: UPDATE pa_proj_elem_ver_structure
18965: SET status_code = 'STRUCTURE_WORKING',
18966: LOCKED_BY_PERSON_ID = (select locked_by_person_id
18967: from pa_proj_elem_ver_structure
18968: where project_id = l_project_id
18963:
18964: UPDATE pa_proj_elem_ver_structure
18965: SET status_code = 'STRUCTURE_WORKING',
18966: LOCKED_BY_PERSON_ID = (select locked_by_person_id
18967: from pa_proj_elem_ver_structure
18968: where project_id = l_project_id
18969: and element_version_id = p_structure_version_id),
18970: LOCK_STATUS_CODE = 'LOCKED'
18971: WHERE project_id = l_project_id
19348: --bug 4555289 moved here to avoid multiple working versions.
19349: --if there was a failure in PJI then the following code pieces were not
19350: --getting executed hence there were multiple working verisons.
19351: --set published version back to published status
19352: UPDATE pa_proj_elem_ver_structure
19353: SET status_code = 'STRUCTURE_PUBLISHED',
19354: LOCKED_BY_PERSON_ID = NULL,
19355: LOCK_STATUS_CODE = 'UNLOCKED'
19356: WHERE project_id = l_project_id
19844: --end bug 4019845, publishing changes
19845:
19846: -- 5029112 (4925192)
19847: if l_pub_wp_ok = 'Y' then
19848: UPDATE pa_proj_elem_ver_structure
19849: set date_prog_applied_on_wver = sysdate
19850: WHERE project_id = l_project_id
19851: AND element_version_id = p_structure_version_id;
19852: end if;
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
20895: AS
20952: p_msg_name => 'PA_INV_PARAM_PASSED');
20953: RAISE Invalid_Arg_Exc_WP;
20954: END IF;
20955:
20956: Update pa_proj_elem_ver_structure
20957: Set process_update_wbs_flag = p_update_wbs_flag
20958: Where project_id = p_project_id
20959: And element_version_id = p_structure_version_id;
20960:
21168: THEN
21169: --no need to update the flag in case of EFFORT or Manual bcoz
21170: --the onlyu thing affected from quick entry is DURATRION thru dates..
21171:
21172: UPDATE pa_proj_elem_ver_structure
21173: SET PROCESS_UPDATE_WBS_FLAG = 'Y'
21174: WHERE proj_element_id = cur_strs_n_types_rec.proj_element_id
21175: AND project_id = p_dest_project_id
21176: AND PROCESS_UPDATE_WBS_FLAG = 'N'