[Home] [Help]
252: ,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
253:
254: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
255: select nvl(max(version_number),0)+1
256: from pa_proj_elem_ver_structure
257: where project_id = c_project_id
258: and proj_element_id = c_proj_element_id
259: and status_code <> 'STRUCTURE_PUBLISHED';
260:
395: CLOSE get_working_ver_num;
396: l_status_code := 'STRUCTURE_WORKING';
397:
398:
399: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
400: X_ROWID => x_row_id
401: , X_PEV_STRUCTURE_ID => x_pev_structure_id
402: , X_ELEMENT_VERSION_ID => x_struc_version_id
403: , X_VERSION_NUMBER => l_struc_ver_number
1096: CURSOR cur_struc_ver_fin
1097: IS
1098: SELECT c.element_version_id
1099: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1100: ,pa_proj_elem_ver_structure d
1101: WHERE c.project_id = p_project_id
1102: AND a.structure_type_id = b.structure_type_id
1103: AND b.proj_element_id = c.proj_element_id
1104: AND a.structure_type = p_structure_type
1114: CURSOR cur_struc_ver_wp
1115: IS
1116: SELECT c.element_version_id
1117: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1118: ,pa_proj_elem_ver_structure d
1119: WHERE c.project_id = p_project_id
1120: AND a.structure_type_id = b.structure_type_id
1121: AND b.proj_element_id = c.proj_element_id
1122: AND a.structure_type = p_structure_type
1185: CURSOR cur_struc_ver_wp2
1186: IS
1187: SELECT c.element_version_id
1188: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1189: ,pa_proj_elem_ver_structure d
1190: WHERE c.project_id = p_project_id
1191: AND a.structure_type_id = b.structure_type_id
1192: AND b.proj_element_id = c.proj_element_id
1193: AND a.structure_type = p_structure_type
1317: CLOSE cur_proj_wrk_attr;
1318: IF NVL( l_auto_pub_flag,'N' ) = 'Y' AND
1319: NVL( IS_WP_VERSIONING_ENABLED(p_project_id), 'N' ) = 'Y'
1320: THEN
1321: UPDATE pa_proj_elem_ver_structure
1322: set status_code = 'STRUCTURE_WORKING'
1323: where project_id = p_project_id
1324: and proj_element_id = l_structure_id;
1325: END IF;
1337:
1338: CURSOR c1 is
1339: SELECT c.element_version_id
1340: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
1341: ,pa_proj_elem_ver_structure d
1342: WHERE c.project_id = p_project_id
1343: AND a.structure_type_id = b.structure_type_id
1344: AND b.proj_element_id = c.proj_element_id
1345: AND a.structure_type = p_structure_type
2490: CURSOR cur_struc_ver_fin
2491: IS
2492: SELECT c.element_version_id
2493: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2494: ,pa_proj_elem_ver_structure d
2495: WHERE c.project_id = p_project_id
2496: AND a.structure_type_id = b.structure_type_id
2497: AND b.proj_element_id = c.proj_element_id
2498: AND a.structure_type = p_structure_type
2509: CURSOR cur_struc_ver_wp
2510: IS
2511: SELECT c.element_version_id
2512: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2513: ,pa_proj_elem_ver_structure d
2514: WHERE c.project_id = p_project_id
2515: AND a.structure_type_id = b.structure_type_id
2516: AND b.proj_element_id = c.proj_element_id
2517: AND a.structure_type = p_structure_type
2615: CURSOR cur_struc_ver_wp2
2616: IS
2617: SELECT c.element_version_id
2618: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
2619: ,pa_proj_elem_ver_structure d
2620: WHERE c.project_id = p_project_id
2621: AND a.structure_type_id = b.structure_type_id
2622: AND b.proj_element_id = c.proj_element_id
2623: AND a.structure_type = p_structure_type
2638:
2639: l_tasks_out pa_project_pub.task_out_tbl_type ; --dummy variables. --bug 2856033
2640: l_process_code VARCHAR2(30);
2641:
2642: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE; -- Bug 3091798
2643:
2644: /* Added for bug 3290037 */
2645:
2646: CURSOR cur_task_attr IS
3967: PA_PROJ_ELEMENTS_PKG.DELETE_ROW( cur_proj_elems_rec.rowid );
3968: END LOOP;
3969:
3970: DELETE FROM pa_proj_elem_ver_schedule where project_id = p_project_id;
3971: DELETE FROM pa_proj_elem_ver_structure where project_id = p_project_id;
3972:
3973: /* Code to delete project records from tables: pa_progress_rollup, pa_proj_progress_attr
3974: and pa_proj_workplan_attr. */
3975:
4037: ) RETURN VARCHAR2 IS
4038:
4039: CURSOR cur_pub_versions is
4040: select 'Y'
4041: from pa_proj_elem_ver_structure ppevs,
4042: pa_proj_structure_types ppst,
4043: pa_structure_types pst
4044: where ppevs.project_id = p_project_id
4045: and ppevs.STATUS_CODE = 'STRUCTURE_PUBLISHED'
4087: AND split_cost_from_workplan_flag = 'N';
4088:
4089: CURSOR cur_pub_versions is
4090: select 'Y'
4091: from pa_proj_elem_ver_structure
4092: where project_id = p_project_id
4093: and STATUS_CODE = 'STRUCTURE_PUBLISHED';
4094:
4095: l_approve VARCHAR2(1);
4958: FROM sys.dual;
4959:
4960: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
4961: select nvl(max(version_number),0)+1
4962: from pa_proj_elem_ver_structure
4963: where project_id = c_project_id
4964: and proj_element_id = c_proj_element_id
4965: and status_code <> 'STRUCTURE_PUBLISHED';
4966:
5097: l_latest_eff_published_flag := 'Y';
5098: END IF;
5099: --end FPM bug 3301192
5100:
5101: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
5102: X_ROWID => x_row_id
5103: , X_PEV_STRUCTURE_ID => x_pev_structure_id
5104: , X_ELEMENT_VERSION_ID => x_structure_version_id
5105: , X_VERSION_NUMBER => l_struc_ver_number
5183: )
5184: IS
5185: cursor c1 is
5186: select pev_structure_id, name, record_version_number
5187: from pa_proj_elem_ver_structure
5188: where project_id = p_project_id
5189: and element_version_id = p_structure_version_id;
5190:
5191: l_name VARCHAR2(240);
5220: from pa_proj_workplan_attr
5221: where project_id = p_project_id;
5222:
5223: cursor c4(c_proj_elem_id NUMBER) is
5224: select '1' from pa_proj_elem_ver_structure
5225: where project_id = p_project_id
5226: and proj_element_id = c_proj_elem_id
5227: and status_code <> 'STRUCTURE_PUBLISHED';
5228:
5231: FROM sys.dual;
5232:
5233: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
5234: select nvl(max(version_number),0)+1
5235: from pa_proj_elem_ver_structure
5236: where project_id = c_project_id
5237: and proj_element_id = c_proj_element_id
5238: and status_code <> 'STRUCTURE_PUBLISHED';
5239:
5357: OPEN get_working_ver_num(p_project_id, l_proj_element_id);
5358: FETCH get_working_ver_num INTO l_struc_ver_number;
5359: CLOSE get_working_ver_num;
5360:
5361: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
5362: X_ROWID => x_row_id
5363: , X_PEV_STRUCTURE_ID => x_pev_structure_id
5364: , X_ELEMENT_VERSION_ID => x_structure_version_id
5365: , X_VERSION_NUMBER => l_struc_ver_number
5501: END IF;
5502: --end FPM bug 3301192
5503: CLOSE get_working_ver_num;
5504:
5505: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
5506: X_ROWID => x_row_id
5507: , X_PEV_STRUCTURE_ID => x_pev_structure_id
5508: , X_ELEMENT_VERSION_ID => x_structure_version_id
5509: , X_VERSION_NUMBER => l_struc_ver_number
5905:
5906: CURSOR cur_pa_work_ver
5907: IS
5908: SELECT element_version_id
5909: FROM pa_proj_elem_ver_structure
5910: WHERE project_id = p_project_id
5911: AND status_code = 'STRUCTURE_WORKING';
5912:
5913: l_structure_version_id NUMBER;
6515: CURSOR c1 IS
6516: select MAX(a.last_update_date)
6517: from pa_proj_element_versions b,
6518: pa_proj_elem_ver_schedule a,
6519: pa_proj_elem_ver_structure c
6520: where p_structure_version_id = c.element_version_id
6521: and l_project_id = c.project_id
6522: and c.status_code <> 'STRUCTURE_PUBLISHED'
6523: and b.parent_structure_version_id = c.element_version_id
6529: CURSOR c1 IS
6530: SELECT MAX(a.last_update_date)
6531: FROM pa_proj_element_versions b,
6532: pa_proj_elem_ver_schedule a,
6533: pa_proj_elem_ver_structure c
6534: WHERE a.element_version_id (+)= b.element_version_id
6535: AND a.project_id (+) = b.project_id
6536: AND a.proj_element_id (+) = b.proj_element_id
6537: AND b.parent_structure_version_id = c.element_version_id
6600: --This cursor retrieves the LATEST PUBLISHED WP version from the source project
6601: CURSOR cur_get_src_fin_version_id IS
6602: SELECT ppev.element_version_id
6603: FROM pa_proj_element_versions ppev,
6604: pa_proj_elem_ver_structure ppevs
6605: WHERE ppev.project_id = p_src_project_id
6606: AND ppev.object_type = 'PA_STRUCTURES'
6607: AND ppev.element_version_id = ppevs.element_version_id
6608: AND ppevs.project_id = p_src_project_id
6611: --This cursor retrieves the CURRENT WORKING WP version from the source project
6612: CURSOR cur_get_src_fin_version_id2 IS
6613: SELECT ppev.element_version_id
6614: FROM pa_proj_element_versions ppev,
6615: pa_proj_elem_ver_structure ppevs
6616: WHERE ppev.project_id = p_src_project_id
6617: AND ppev.object_type = 'PA_STRUCTURES'
6618: AND ppev.element_version_id = ppevs.element_version_id
6619: AND ppevs.project_id = p_src_project_id
6838: Pa_Debug.WRITE(G_PKG_NAME,'dest_latest_eff_pub_flag : '||dest_latest_eff_pub_flag, l_debug_level3);
6839: Pa_Debug.WRITE(G_PKG_NAME,'dest_current_flag : '||dest_current_flag, l_debug_level3);
6840: END IF;
6841:
6842: --Copy entry into pa_proj_elem_ver_structure for financial version in the source project
6843: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
6844: PEV_STRUCTURE_ID
6845: ,ELEMENT_VERSION_ID
6846: ,VERSION_NUMBER
6839: Pa_Debug.WRITE(G_PKG_NAME,'dest_current_flag : '||dest_current_flag, l_debug_level3);
6840: END IF;
6841:
6842: --Copy entry into pa_proj_elem_ver_structure for financial version in the source project
6843: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
6844: PEV_STRUCTURE_ID
6845: ,ELEMENT_VERSION_ID
6846: ,VERSION_NUMBER
6847: ,NAME
6876: ,source_object_id
6877: ,source_object_type
6878: )
6879: SELECT
6880: pa_proj_elem_ver_structure_s.nextval
6881: ,ppev.element_version_id
6882: ,ppevs.version_number
6883: ,ppevs.name
6884: ,p_dest_project_id
6913: ,decode(p_shared,'N',ppevs.current_working_flag,'Y') --CURRENT_WROKING_FLAG
6914: --If SPLIT and fin enabled case, copy cw from source, else populate it as Y
6915: ,p_dest_project_id
6916: ,'PA_PROJECTS'
6917: FROM pa_proj_elem_ver_structure ppevs,
6918: pa_proj_element_versions ppev
6919: WHERE ppevs.project_id = p_src_project_id
6920: AND ppevs.element_version_id = l_src_fin_version_id
6921: AND ppev.attribute15 = ppevs.element_version_id
7016: --This cursor retrieves the LATEST PUBLISHED WP version from the source project
7017: CURSOR cur_get_src_fin_version_id IS
7018: SELECT ppev.element_version_id
7019: FROM pa_proj_element_versions ppev,
7020: pa_proj_elem_ver_structure ppevs
7021: WHERE ppev.project_id = p_src_project_id
7022: AND ppev.object_type = 'PA_STRUCTURES'
7023: AND ppev.element_version_id = ppevs.element_version_id
7024: AND ppevs.project_id = p_src_project_id
7027: --This cursor retrieves the CURRENT WORKING WP version from the source project
7028: CURSOR cur_get_src_fin_version_id2 IS
7029: SELECT ppev.element_version_id
7030: FROM pa_proj_element_versions ppev,
7031: pa_proj_elem_ver_structure ppevs
7032: WHERE ppev.project_id = p_src_project_id
7033: AND ppev.object_type = 'PA_STRUCTURES'
7034: AND ppev.element_version_id = ppevs.element_version_id
7035: AND ppevs.project_id = p_src_project_id
7462: ,'PA_PROJECTS'
7463: FROM pa_proj_element_versions ppev,
7464: pa_proj_elements ppe,
7465: pa_proj_element_versions ppev2,
7466: pa_proj_elem_ver_structure ppevs
7467: WHERE ppev.project_id = p_src_project_id
7468: AND ppev.object_type = 'PA_TASKS'
7469: AND ppev.parent_structure_version_id = l_src_fin_version_id
7470: AND ppev.proj_element_id = ppe.attribute15
7917: AND element_version_id = c_struc_ver_id;
7918:
7919: CURSOR cur_get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
7920: SELECT nvl(max(version_number),0)+1
7921: FROM pa_proj_elem_ver_structure
7922: WHERE project_id = c_project_id
7923: AND proj_element_id = c_proj_element_id
7924: AND status_code <> 'STRUCTURE_PUBLISHED';
7925:
8048: --bug 3912603, Satish, end
8049:
8050: cursor get_published_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
8051: select nvl(max(version_number),0)+1
8052: from pa_proj_elem_ver_structure
8053: where project_id = c_project_id
8054: and proj_element_id = c_proj_element_id
8055: and status_code = 'STRUCTURE_PUBLISHED';
8056:
8055: and status_code = 'STRUCTURE_PUBLISHED';
8056:
8057: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
8058: select nvl(max(version_number),0)+1
8059: from pa_proj_elem_ver_structure
8060: where project_id = c_project_id
8061: and proj_element_id = c_proj_element_id
8062: and status_code <> 'STRUCTURE_PUBLISHED';
8063: --bug 3907862, end
8154:
8155: -- gboomina added for bug 9659119 - start
8156: cursor get_record_version_number(c_project_id NUMBER, c_structure_version_id NUMBER) IS
8157: select record_version_number
8158: from pa_proj_elem_ver_structure
8159: where project_id = c_project_id
8160: and element_version_id = c_structure_version_id;
8161:
8162: l_record_version_number NUMBER;
8810:
8811: --Gets the current working version of the source project ONLY if it has been SELECTED for copying
8812: CURSOR get_current_working_selected IS
8813: SELECT ppevs.element_version_id
8814: FROM pa_proj_elem_ver_structure ppevs,
8815: pa_project_copy_options_tmp ppcot
8816: WHERE ppevs.current_working_flag = 'Y'
8817: AND ppevs.status_code = 'STRUCTURE_WORKING'
8818: AND ppevs.project_id = p_src_project_id
8823: --copying and is NOT Publish Upon Creation
8824: CURSOR get_latest_working_version IS
8825: SELECT element_version_id FROM
8826: ( SELECT ppevs.element_version_id
8827: FROM pa_proj_elem_ver_structure ppevs,
8828: pa_project_copy_options_tmp ppcot
8829: WHERE ppevs.element_version_id = ppcot.version_id
8830: AND ppevs.project_id = p_src_project_id
8831: AND ppevs.status_code = 'STRUCTURE_WORKING'
8837: --Gets the LATEST PUBLISHED VERSION from the source project, which is SELECTED for copying and is
8838: --NOT Publish Upon Creation
8839: CURSOR get_latest_published IS
8840: SELECT ppevs.element_version_id
8841: FROM pa_proj_elem_ver_structure ppevs,
8842: pa_project_copy_options_tmp ppcot
8843: WHERE ppevs.latest_eff_published_flag = 'Y'
8844: AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
8845: AND ppevs.element_version_id = ppcot.version_id
8851: --NOT selected for PUBLISH UPON CREATION
8852: CURSOR get_last_published IS
8853: SELECT element_version_id FROM
8854: ( SELECT ppevs.element_version_id
8855: FROM pa_proj_elem_ver_structure ppevs,
8856: pa_project_copy_options_tmp ppcot
8857: WHERE ppevs.element_version_id = ppcot.version_id
8858: AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
8859: AND ppevs.project_id = p_src_project_id
9016: l_debug_level3);
9017: END IF;
9018:
9019:
9020: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
9021: PEV_STRUCTURE_ID
9022: ,ELEMENT_VERSION_ID
9023: ,VERSION_NUMBER
9024: ,NAME
9053: ,source_object_id
9054: ,source_object_type
9055: )
9056: SELECT
9057: pa_proj_elem_ver_structure_s.nextval
9058: ,rec_dest_wp_struc_versions.element_version_id
9059: --,ppevs.version_number --bug 3907862, insert the new version number, intead of coying the old.
9060: ,l_struct_version_number
9061: ,ppevs.name
9090: ,decode(l_versioning_enabled,'N','Y',l_dest_current_working_flag)
9091: --If versioning is disabled, both LATEST PUBLISHED AND CURRENT WORKING FLAGS are 'Y'
9092: ,p_dest_project_id
9093: ,'PA_PROJECTS'
9094: FROM pa_proj_elem_ver_structure ppevs,
9095: pa_proj_elements ppe
9096: WHERE ppevs.project_id = p_src_project_id
9097: AND ppevs.element_version_id = rec_dest_wp_struc_versions.attribute15
9098: AND ppevs.proj_element_id = ppe.attribute15
9215: FROM pa_proj_element_versions ppev
9216: WHERE ppev.project_id = p_dest_project_id
9217: AND ppev.element_version_id = l_shared_make_working_copy ;
9218:
9219: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
9220: PEV_STRUCTURE_ID
9221: ,ELEMENT_VERSION_ID
9222: ,VERSION_NUMBER
9223: ,NAME
9252: ,source_object_id
9253: ,source_object_type
9254: )
9255: SELECT
9256: pa_proj_elem_ver_structure_s.nextval
9257: ,l_new_wp_ver_id
9258: ,ppevs.version_number
9259: ,substr(l_prefix||ppevs.name, 1, 240)
9260: ,ppevs.project_id
9286: ,ppevs.process_update_wbs_flag
9287: ,'Y'
9288: ,ppevs.project_id
9289: ,'PA_PROJECTS'
9290: FROM pa_proj_elem_ver_structure ppevs,
9291: pa_proj_elements ppe
9292: WHERE ppevs.project_id = p_dest_project_id
9293: AND ppevs.element_version_id = l_shared_make_working_copy
9294: AND ppevs.proj_element_id = ppe.proj_element_id
9674: ,'PA_PROJECTS'
9675: FROM pa_proj_elements ppe,
9676: pa_proj_element_versions ppev,
9677: pa_proj_element_versions ppev2,
9678: pa_proj_elem_ver_structure ppevs
9679: WHERE ppe.project_id = p_dest_project_id
9680: AND ppe.object_type = 'PA_TASKS'
9681: AND ppe.attribute15 = ppev.proj_element_id
9682: AND ppev.project_id = p_src_project_id
10124: IF 'N' = p_dest_template_flag AND 'N' = l_versioning_enabled THEN
10125: l_default_lat_pub_and_cw_flag := 'Y';
10126: END IF;
10127:
10128: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
10129: PEV_STRUCTURE_ID
10130: ,ELEMENT_VERSION_ID
10131: ,VERSION_NUMBER
10132: ,NAME
10152: ,CURRENT_WORKING_FLAG
10153: ,SOURCE_OBJECT_ID
10154: ,source_object_type)
10155: VALUES (
10156: pa_proj_elem_ver_structure_s.nextval
10157: ,l_new_wp_ver_id
10158: ,l_struc_ver_number
10159: ,substr(p_dest_project_name||l_append||l_suffix, 1, 240)
10160: ,p_dest_project_id
10309: AND ppevs.proj_element_id = ppe.proj_element_id
10310: AND ppev.proj_element_id = ppe.proj_element_id
10311: AND ppev.parent_structure_version_id =
10312: ( SELECT ppevst.element_version_id --Changed the inner query for bug 4215666.
10313: FROM pa_proj_elem_ver_structure ppevst,
10314: pa_proj_structure_types struc,
10315: pa_structure_types stype
10316: WHERE ppevst.project_id = p_dest_project_id
10317: AND ppevst.current_flag = 'Y'
10926:
10927: CURSOR l_get_structure_attr_csr
10928: IS
10929: /* SELECT *
10930: FROM PA_PROJ_ELEM_ver_structure ppevs
10931: WHERE project_id = p_src_project_id;*//*Commented below query and following criteria by SMUKKA */
10932: select ppevs.*
10933: from pa_structure_types pst,
10934: pa_proj_structure_types ppst,
10931: WHERE project_id = p_src_project_id;*//*Commented below query and following criteria by SMUKKA */
10932: select ppevs.*
10933: from pa_structure_types pst,
10934: pa_proj_structure_types ppst,
10935: pa_proj_elem_ver_structure ppevs
10936: where ppevs.project_id = p_src_project_id
10937: and ppevs.proj_element_id= ppst.proj_element_id
10938: and ppst.structure_type_id = pst.structure_type_id
10939: and pst.structure_type in ('WORKPLAN','FINANCIAL');
10941: CURSOR l_get_structure_attr_csr2
10942: IS
10943: SELECT ppevs.element_version_id, ppevs.status_code, ppevs.LATEST_EFF_PUBLISHED_FLAG, ppevs.current_flag,
10944: ppst.structure_type_id
10945: /* FROM PA_PROJ_ELEM_ver_structure ppevs,
10946: pa_proj_structure_types ppst
10947: WHERE project_id = p_src_project_id
10948: AND ppst.proj_element_id(+) = ppevs.proj_element_id;*//*Commented below query and following criteria by SMUKKA */
10949: from pa_proj_elem_ver_structure ppevs,
10945: /* FROM PA_PROJ_ELEM_ver_structure ppevs,
10946: pa_proj_structure_types ppst
10947: WHERE project_id = p_src_project_id
10948: AND ppst.proj_element_id(+) = ppevs.proj_element_id;*//*Commented below query and following criteria by SMUKKA */
10949: from pa_proj_elem_ver_structure ppevs,
10950: pa_proj_structure_types ppst,
10951: pa_structure_types pst
10952: where project_id = p_src_project_id
10953: and ppst.proj_element_id(+) = ppevs.proj_element_id
11744:
11745:
11746: --bug 3010538
11747: --added PROCESS_UPDATE_WBS_FLAG
11748: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
11749: PEV_STRUCTURE_ID
11750: ,ELEMENT_VERSION_ID
11751: ,VERSION_NUMBER
11752: ,NAME
11781: ,source_object_id
11782: ,source_object_type
11783: )
11784: SELECT
11785: pa_proj_elem_ver_structure_s.nextval
11786: ,l_struc_version_id
11787: ,1
11788: --,ppevs.name, rtarway, 3655698, replaced ppevs.name with ppe.name
11789: ,ppe.name
11817: ,'N'
11818: ,ppevs.current_working_flag /* FPM bug 3301192 */
11819: ,p_dest_project_id
11820: ,'PA_PROJECTS'
11821: /*FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
11822: WHERE ppevs.project_id = p_src_project_id
11823: AND ppe.project_id = p_dest_project_id
11824: and ppevs.proj_element_id = ppe.attribute15
11825: AND ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11822: WHERE ppevs.project_id = p_src_project_id
11823: AND ppe.project_id = p_dest_project_id
11824: and ppevs.proj_element_id = ppe.attribute15
11825: AND ppe.object_type = 'PA_STRUCTURES';*//*Commented below query and following criteria by SMUKKA */
11826: FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
11827: WHERE ppevs.project_id = p_src_project_id
11828: AND ppe.project_id = p_dest_project_id
11829: and ppevs.proj_element_id = ppe.attribute15
11830: AND ppe.object_type = 'PA_STRUCTURES'
12859:
12860: --bug 3010538
12861: --added PROCESS_UPDATE_WBS_FLAG
12862:
12863: INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE(
12864: PEV_STRUCTURE_ID
12865: ,ELEMENT_VERSION_ID
12866: ,VERSION_NUMBER
12867: ,NAME
12896: ,source_object_id
12897: ,source_object_type
12898: )
12899: SELECT
12900: pa_proj_elem_ver_structure_s.nextval
12901: ,l_struc_version_id
12902: ,ppevs.version_number --replaced k with source version_number bug 2829385
12903: --,ppevs.name 3717026, replaced ppevs.name with ppe.name
12904: ,ppe.name
12931: ,'N'
12932: ,ppevs.current_working_flag /* FPM bug 3301192 */
12933: ,p_dest_project_id
12934: ,'PA_PROJECTS'
12935: FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
12936: WHERE ppevs.project_id = p_src_project_id
12937: AND ppe.project_id = p_dest_project_id
12938: and ppevs.element_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12939: and ppevs.proj_element_id = ppe.attribute15
13119: BASELINE_DURATION ) =
13120: ( Select ppevs.scheduled_start_date, ppevs.scheduled_finish_date,
13121: ppevs.duration
13122: from pa_proj_elem_ver_schedule ppevs,
13123: pa_proj_elem_ver_structure ppevst
13124: WHERE ppevs.project_id = p_dest_project_id
13125: and ppevst.project_id = p_dest_project_id
13126: and ppevst.current_flag = 'Y' --assuming this is the flag used to identify the baselined structure ver.
13127: and ppevst.STATUS_CODE = 'STRUCTURE_PUBLISHED'
13283: FOR cur_strs_n_types_rec in cur_strs_n_types LOOP
13284: IF cur_strs_n_types_rec.structure_type_id = 1 --'WORKPLAN' structure
13285: THEN
13286: l_wp_str_id := cur_strs_n_types_rec.proj_element_id;
13287: UPDATE pa_proj_elem_ver_structure
13288: SET PROCESS_UPDATE_WBS_FLAG = 'Y'
13289: WHERE proj_element_id = cur_strs_n_types_rec.proj_element_id
13290: AND project_id = p_dest_project_id
13291: AND PROCESS_UPDATE_WBS_FLAG = 'N'
13616: ,p_create_task_version_only IN VARCHAR2
13617: ,p_wp_str_exists IN VARCHAR2
13618: ,p_is_wp_separate_from_fn IN VARCHAR2
13619: ,p_is_wp_versioning_enabled IN VARCHAR2
13620: ,p_structure_version_id IN pa_proj_elem_ver_structure.element_version_id%TYPE --IUP: Populated only in case of Update_project context
13621: -- Included NOCOPY for the following parameter.
13622: -- PA L Changes 3010538
13623: ,p_process_mode IN VARCHAR2
13624: -- Bug 3075609. To identify create task version only context.
13788: l_task_unpub_ver_status_code pa_proj_element_versions.task_unpub_ver_status_code%TYPE;
13789: l_add_task_security VARCHAR2(1);
13790: l_default_task_type_id pa_proj_elements.type_id%TYPE;
13791: l_update_task_security VARCHAR2(1);
13792: l_str_status_code pa_proj_elem_ver_structure.status_code%TYPE;
13793: l_amg_segment1 pa_projects_all.segment1%TYPE;
13794:
13795: -- Bug 3075609
13796: l_maintain_weightages VARCHAR2(1); -- We need not maintain weightages during code flow always. This var will track it.
13883: l_progressable_task VARCHAR2(1); --bug 3035902 maansari
13884: --end bug 3010538
13885:
13886: -- Bug 3091798
13887: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
13888:
13889: -- Variables added for bug 3057575 :
13890: row_task_ver_id cur_all_task_ver_id%ROWTYPE;
13891: task_parent_id_tbl task_parent_id_tbl_type;
14017: FROM
14018: pa_projects_all ppa,
14019: pa_proj_elements ppe,
14020: pa_proj_element_versions ppv,
14021: pa_proj_elem_ver_structure ppvs,
14022: pa_proj_workplan_attr ppwa,
14023: pa_progress_rollup ppr,
14024: pji_xbs_plans_v pxpv
14025: WHERE
14044: SELECT Nvl(MAX(ppr2.as_of_date), Trunc(SYSDATE))
14045: FROM
14046: pa_progress_rollup ppr2,
14047: pa_proj_element_versions ppev,
14048: pa_proj_elem_ver_structure ppevs
14049: WHERE
14050: ppr2.object_id = ppv.proj_element_id
14051: AND ppr2.proj_element_id = ppv.proj_element_id
14052: AND ppr2.project_id = ppv.project_id
14088: FROM
14089: pa_projects_all ppa,
14090: pa_proj_elements ppe,
14091: pa_proj_element_versions ppv,
14092: pa_proj_elem_ver_structure ppvs,
14093: pa_progress_rollup ppr,
14094: pji_xbs_plans_v pxpv
14095: WHERE
14096: ppa.project_id = ppv.project_id
14124: CURSOR cur_struc_ver_wp(c_project_id pa_projects_all.project_id%TYPE, c_structure_type pa_structure_types.structure_type%TYPE)
14125: IS
14126: SELECT c.element_version_id
14127: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
14128: ,pa_proj_elem_ver_structure d
14129: WHERE c.project_id = c_project_id
14130: AND a.structure_type_id = b.structure_type_id
14131: AND b.proj_element_id = c.proj_element_id
14132: AND a.structure_type = c_structure_type
14580: nvl(p_is_wp_versioning_enabled,'N') = 'Y' and
14581: l_source_template_flag = 'Y' --ADUT
14582: THEN
14583:
14584: UPDATE pa_proj_elem_ver_structure
14585: set status_code = 'STRUCTURE_WORKING'
14586: where project_id = p_project_id
14587: and proj_element_id = l_structure_id;
14588:
18241:
18242: --Bug# 5973460
18243: CURSOR c_get_process_code(c_structure_version_id NUMBER) IS
18244: SELECT process_code
18245: FROM pa_proj_elem_ver_structure
18246: WHERE element_version_id = c_structure_version_id
18247: and project_id = p_project_id; --Bug 11684912
18248: --Bug# 5973460
18249:
18257: l_debug_level3 CONSTANT NUMBER := 3;
18258: l_debug_level4 CONSTANT NUMBER := 4;
18259: l_debug_level5 CONSTANT NUMBER := 5;
18260:
18261: l_process_code pa_proj_elem_ver_structure.process_code%TYPE; --Bug# 5973460
18262:
18263: BEGIN
18264:
18265: --Bug# 5973460
18506: and pst.proj_element_id = ver.proj_element_id
18507: and pst.structure_type_id = st.structure_type_id
18508: and st.structure_type = c_structure_type;
18509:
18510: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
18511: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
18512: l_structure_type_flag VARCHAR2(1);
18513: --Bug 3053920.
18514: l_task_weighting_basis pa_proj_progress_attr.task_weight_basis_code%TYPE;
18507: and pst.structure_type_id = st.structure_type_id
18508: and st.structure_type = c_structure_type;
18509:
18510: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
18511: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
18512: l_structure_type_flag VARCHAR2(1);
18513: --Bug 3053920.
18514: l_task_weighting_basis pa_proj_progress_attr.task_weight_basis_code%TYPE;
18515:
18533:
18534: -- Bug 4589289 : Begin
18535: CURSOR c_get_process_code(c_structure_version_id NUMBER) IS
18536: SELECT process_code
18537: FROM pa_proj_elem_ver_structure
18538: WHERE element_version_id = c_structure_version_id
18539: and project_id = p_project_id -- Bug # 4868867.
18540: ;
18541:
18538: WHERE element_version_id = c_structure_version_id
18539: and project_id = p_project_id -- Bug # 4868867.
18540: ;
18541:
18542: l_process_code pa_proj_elem_ver_structure.process_code%TYPE;
18543: l_rerun_flag VARCHAR2(1):=null;
18544:
18545: -- Bug 4589289 : End
18546:
18711:
18712: /*
18713:
18714: -- Update the structure version record with the request id and the status code.
18715: update pa_proj_elem_ver_structure
18716: set process_code = 'WUP',
18717: conc_request_id = l_request_id
18718: where element_version_id = p_structure_version_id
18719: and project_id = p_project_id;
18856: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
18857: )
18858: AS
18859:
18860: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
18861:
18862: l_return_status VARCHAR2(1);
18863: l_msg_count NUMBER := 0;
18864: l_data VARCHAR2(2000);
18883: l_record_version_number NUMBER;
18884:
18885: cursor c2(c_project_id NUMBER, c_structure_version_id NUMBER) IS
18886: select record_version_number
18887: from pa_proj_elem_ver_structure
18888: where project_id = c_project_id
18889: and element_version_id = c_structure_version_id;
18890:
18891: /* Bug 8347243 - End */
18953:
18954: -- Update to success if the call was successful.
18955: -- It has been decided to update null as the process_code if the
18956: -- process has completed successfully.
18957: update pa_proj_elem_ver_structure
18958: set process_code = NULL,
18959: conc_request_id = l_request_id
18960: where element_version_id = p_structure_version_id
18961: and project_id = p_project_id;
18967:
18968: -- Mark the newly created published structure record also with the process code
18969: -- as null.
18970:
18971: update pa_proj_elem_ver_structure
18972: set process_code = NULL
18973: , conc_request_id = l_request_id
18974: where element_version_id = p_pub_struc_ver_id
18975: and project_id = p_project_id;
19056: -- Begin fix for Bug # 4373055.
19057:
19058: /*
19059:
19060: update pa_proj_elem_ver_structure
19061: set process_code = 'WUE',
19062: conc_request_id = l_request_id
19063: where element_version_id = p_structure_version_id
19064: and project_id = p_project_id;
19140: -- Begin fix for Bug # 4373055.
19141:
19142: /*
19143:
19144: update pa_proj_elem_ver_structure
19145: set process_code = 'WUE',
19146: conc_request_id = l_request_id
19147: where element_version_id = p_structure_version_id
19148: and project_id = p_project_id;
19258: AND pst.proj_element_id = ver.proj_element_id
19259: AND pst.structure_type_id = st.structure_type_id
19260: AND st.structure_type = c_structure_type;
19261:
19262: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
19263: l_structure_type_flag VARCHAR2(1);
19264: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
19265:
19266: l_msg_count NUMBER := 0;
19260: AND st.structure_type = c_structure_type;
19261:
19262: l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
19263: l_structure_type_flag VARCHAR2(1);
19264: l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
19265:
19266: l_msg_count NUMBER := 0;
19267: l_data VARCHAR2(2000);
19268: l_msg_data VARCHAR2(2000);
19283:
19284: --bug 3762882
19285: CURSOR get_prev_published_ver(c_struc_ver_id NUMBER) IS
19286: select a.element_version_id
19287: from pa_proj_elem_ver_structure a,
19288: pa_proj_element_versions b
19289: WHERE a.project_id = p_project_id
19290: AND a.proj_element_id = b.proj_element_id
19291: AND b.element_version_id = c_struc_ver_id
19309: WHERE project_id = c_project_id;
19310:
19311: CURSOR get_struc_id(c_project_id NUMBER, c_struc_ver_id NUMBER) IS
19312: select pevs.proj_element_id
19313: from pa_proj_elem_ver_structure pevs
19314: where pevs.element_version_id = c_struc_ver_id
19315: and pevs.project_id = c_project_id;
19316:
19317: --bug 4472071
19722:
19723: --bug 4019845
19724: --set current published as working for edit
19725:
19726: UPDATE pa_proj_elem_ver_structure
19727: SET status_code = 'STRUCTURE_WORKING',
19728: LOCKED_BY_PERSON_ID = (select locked_by_person_id
19729: from pa_proj_elem_ver_structure
19730: where project_id = l_project_id
19725:
19726: UPDATE pa_proj_elem_ver_structure
19727: SET status_code = 'STRUCTURE_WORKING',
19728: LOCKED_BY_PERSON_ID = (select locked_by_person_id
19729: from pa_proj_elem_ver_structure
19730: where project_id = l_project_id
19731: and element_version_id = p_structure_version_id),
19732: LOCK_STATUS_CODE = 'LOCKED'
19733: WHERE project_id = l_project_id
19753:
19754: --bug 4019845
19755: --set current published as working for edit
19756:
19757: UPDATE pa_proj_elem_ver_structure
19758: SET status_code = 'STRUCTURE_WORKING',
19759: LOCKED_BY_PERSON_ID = (select locked_by_person_id
19760: from pa_proj_elem_ver_structure
19761: where project_id = l_project_id
19756:
19757: UPDATE pa_proj_elem_ver_structure
19758: SET status_code = 'STRUCTURE_WORKING',
19759: LOCKED_BY_PERSON_ID = (select locked_by_person_id
19760: from pa_proj_elem_ver_structure
19761: where project_id = l_project_id
19762: and element_version_id = p_structure_version_id),
19763: LOCK_STATUS_CODE = 'LOCKED'
19764: WHERE project_id = l_project_id
20183: --bug 4555289 moved here to avoid multiple working versions.
20184: --if there was a failure in PJI then the following code pieces were not
20185: --getting executed hence there were multiple working verisons.
20186: --set published version back to published status
20187: UPDATE pa_proj_elem_ver_structure
20188: SET status_code = 'STRUCTURE_PUBLISHED',
20189: LOCKED_BY_PERSON_ID = NULL,
20190: LOCK_STATUS_CODE = 'UNLOCKED'
20191: WHERE project_id = l_project_id
20639: --end bug 4019845, publishing changes
20640:
20641: -- 5029112 (4925192)
20642: if l_pub_wp_ok = 'Y' then
20643: UPDATE pa_proj_elem_ver_structure
20644: set date_prog_applied_on_wver = sysdate
20645: WHERE project_id = l_project_id
20646: AND element_version_id = p_structure_version_id;
20647: end if;
21682: ,p_init_msg_list IN VARCHAR2
21683: ,p_calling_context IN VARCHAR2
21684: ,p_project_id IN pa_projects_all.project_id%TYPE
21685: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21686: ,p_update_wbs_flag IN pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE
21687: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21688: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21689: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21690: AS
21748: RAISE Invalid_Arg_Exc_WP;
21749: END IF;
21750: --Defer the Structure Update when G_Rollup_Defer is Y.
21751: IF (NVL(PA_PROJECT_PUB.G_ROLLUP_DEFER,'N')='N') THEN
21752: Update pa_proj_elem_ver_structure
21753: Set process_update_wbs_flag = p_update_wbs_flag
21754: Where project_id = p_project_id
21755: And element_version_id = p_structure_version_id;
21756:
21964: THEN
21965: --no need to update the flag in case of EFFORT or Manual bcoz
21966: --the onlyu thing affected from quick entry is DURATRION thru dates..
21967:
21968: UPDATE pa_proj_elem_ver_structure
21969: SET PROCESS_UPDATE_WBS_FLAG = 'Y'
21970: WHERE proj_element_id = cur_strs_n_types_rec.proj_element_id
21971: AND project_id = p_dest_project_id
21972: AND PROCESS_UPDATE_WBS_FLAG = 'N'