[Home] [Help]
664: CURSOR getid is select project_id from pa_proj_elements
665: where proj_element_id = p_structure_id;
666:
667: CURSOR cur_elem_ver_seq IS
668: SELECT pa_proj_element_versions_s.nextval
669: FROM sys.dual;
670:
671: BEGIN
672: IF (p_debug_mode = 'Y') THEN
767: -- End 4657794
768:
769: -- error_msg(x_structure_version_id||' new structure version id, '||p_structure_id||', '||l_project_id);
770: --Insert
771: PA_PROJ_ELEMENT_VERSIONS_PKG.INSERT_ROW(
772: X_ROW_ID => l_rowid
773: --,X_ELEMENT_VERSION_ID => x_structure_version_id * Commenmted for Bug Fix: 4537865
774: ,X_ELEMENT_VERSION_ID => l_new_structure_version_id -- added for bug bug Fix: 4537865
775: ,X_PROJ_ELEMENT_ID => p_structure_id
802: -- added for bug bug Fix: 4537865
803: x_structure_version_id := l_new_structure_version_id;
804: -- added for bug bug Fix: 4537865
805:
806: select element_version_id into l_dummy from pa_proj_element_versions where element_version_id = x_structure_version_id;
807: -- error_msg('element_version_id = '||l_dummy);
808: -- error_msg('rowid = '||l_rowid);
809: -- Added by skannoji
810: -- added for doosan customer to add the planning transaction
960: where f.employee_id = p.person_id
961: and sysdate between p.effective_start_date and p.effective_end_date
962: and f.user_id = p_user_id;
963:
964: cursor getids is select project_id, proj_element_id from pa_proj_element_versions
965: where element_version_id = p_structure_version_id;
966:
967: cursor get_published_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
968: select nvl(max(version_number),0)+1
1824:
1825: cursor cur_relationship_ids(c_structure_version_id NUMBER, c_project_id NUMBER)
1826: is select por.object_relationship_id, por.record_version_number
1827: , por2.object_id_from1, por.object_id_from2, por.comments, ppa.name -- Bug # 4556844.
1828: from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs
1829: , pa_projects_all ppa, pa_object_relationships por2 -- Bug # 4556844.
1830: where ppevs.element_version_id = ppev.parent_structure_version_id
1831: and ppevs.project_id = ppev.project_id
1832: and ppev.element_version_id = por.object_id_to1
2519: IF (p_validate_only <> FND_API.G_TRUE) THEN
2520: BEGIN
2521: --lock
2522: select rowid into l_rowid
2523: from pa_proj_element_versions
2524: where element_version_id = p_structure_version_id
2525: and record_version_number = p_record_version_number
2526: for update of record_version_number NOWAIT;
2527: EXCEPTION
2544: END;
2545: ELSE
2546: BEGIN
2547: select rowid into l_rowid
2548: from pa_proj_element_versions
2549: where element_version_id = p_structure_version_id
2550: and record_version_number = p_record_version_number;
2551: EXCEPTION
2552: when NO_DATA_FOUND then
2603: END;
2604: -- Added for FP_M Changes -- Bhumesh
2605: BEGIN
2606: SELECT project_id INTO l_Project_ID
2607: FROM pa_proj_element_versions
2608: WHERE element_version_id = p_structure_version_id and rownum < 2;
2609:
2610: PA_PROGRESS_PUB.delete_working_wp_progress (
2611: P_Project_ID => l_Project_ID
2674: end loop;
2675:
2676: -- End fix for Bug # 4483222.
2677:
2678: PA_PROJ_ELEMENT_VERSIONS_PKG.DELETE_ROW(
2679: X_ROW_ID => l_rowid
2680: );
2681:
2682: x_return_status := FND_API.G_RET_STS_SUCCESS;
3046: cursor get_task_version_info(c_task_version_id NUMBER) IS
3047: select v1.project_id project_id, v2.proj_element_id structure_id,
3048: v1.parent_structure_version_id structure_version_id,
3049: v1.element_version_id task_version_id
3050: from pa_proj_element_versions v1,
3051: pa_proj_element_versions v2
3052: where v1.element_version_id = c_task_version_id
3053: and v1.parent_structure_version_id = v2.element_version_id;
3054: l_info_task_ver_rec get_task_version_info%ROWTYPE;
3047: select v1.project_id project_id, v2.proj_element_id structure_id,
3048: v1.parent_structure_version_id structure_version_id,
3049: v1.element_version_id task_version_id
3050: from pa_proj_element_versions v1,
3051: pa_proj_element_versions v2
3052: where v1.element_version_id = c_task_version_id
3053: and v1.parent_structure_version_id = v2.element_version_id;
3054: l_info_task_ver_rec get_task_version_info%ROWTYPE;
3055:
3055:
3056: cursor get_structure_version_info(c_structure_version_id NUMBER) IS
3057: select v1.project_id project_id, v1.proj_element_id structure_id,
3058: v1.element_version_id structure_version_id
3059: from pa_proj_element_versions v1
3060: where v1.element_version_id = c_structure_version_id;
3061: l_info_struc_ver_rec get_structure_version_info%ROWTYPE;
3062:
3063:
3064: cursor get_struc_ver_name IS
3065: select pevs.name, pevs.project_id, pevs.proj_element_id,
3066: pevs.element_version_id, pevs.pev_structure_id
3067: from pa_proj_elem_ver_structure pevs,
3068: pa_proj_element_versions pev
3069: where pev.element_version_id = p_structure_version_id
3070: and pevs.project_id = pev.project_id
3071: and pevs.element_version_id = pev.element_version_id;
3072:
3071: and pevs.element_version_id = pev.element_version_id;
3072:
3073: CURSOR get_structure_ver_csr(c_structure_version_id NUMBER) IS
3074: SELECT *
3075: FROM PA_PROJ_ELEMENT_VERSIONS
3076: WHERE element_version_id = c_structure_version_id;
3077: l_struc_ver_rec get_structure_ver_csr%ROWTYPE;
3078:
3079: CURSOR get_structure_ver_attr_csr(c_structure_version_id NUMBER, c_project_id NUMBER) IS
3094: CURSOR get_task_versions_csr(c_structure_version_id NUMBER) IS
3095: SELECT a.element_version_id, a.proj_element_id, a.display_sequence, a.wbs_level,
3096: a.project_id, b.object_id_from1 parent_element_version_id,
3097: a.TASK_UNPUB_VER_STATUS_CODE, a.parent_structure_version_id
3098: FROM PA_PROJ_ELEMENT_VERSIONS a,
3099: PA_OBJECT_RELATIONSHIPS b
3100: WHERE a.object_type = 'PA_TASKS'
3101: AND a.parent_structure_version_id = c_structure_version_id
3102: AND a.element_version_id = b.object_id_to1
3106: l_task_versions_rec get_task_versions_csr%ROWTYPE;
3107:
3108: Cursor get_linking_tasks IS
3109: select a.element_version_id
3110: from pa_proj_element_versions a,
3111: pa_proj_elements b
3112: where a.proj_element_id = b.proj_element_id
3113: and a.parent_structure_version_id = p_structure_version_id
3114: and b.link_task_flag = 'Y';
3137:
3138: --hsiu added for advanced structure changes
3139: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
3140: select b.element_version_id, b.record_version_number
3141: from pa_proj_element_versions a,
3142: pa_proj_element_versions b,
3143: pa_proj_elem_ver_structure c
3144: where a.element_version_id = c_keep_struc_ver_id
3145: and a.project_id = b.project_id
3138: --hsiu added for advanced structure changes
3139: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
3140: select b.element_version_id, b.record_version_number
3141: from pa_proj_element_versions a,
3142: pa_proj_element_versions b,
3143: pa_proj_elem_ver_structure c
3144: where a.element_version_id = c_keep_struc_ver_id
3145: and a.project_id = b.project_id
3146: and a.proj_element_id = b.proj_element_id
3183:
3184: CURSOR get_tbd_tasks_info(c_task_ver_id NUMBER) IS
3185: select parent_structure_version_id, element_version_id,
3186: record_version_number
3187: from pa_proj_element_versions
3188: where element_version_id = c_task_ver_id;
3189: l_tbd_tasks_info_rec get_tbd_tasks_info%ROWTYPE;
3190:
3191: CURSOR get_parent_id(c_task_ver_id NUMBER) IS
3232: --bug 3047602
3233: l_task_ver_ids_tbl PA_STRUCT_TASK_ROLLUP_PUB.pa_element_version_id_tbl_typ;
3234: cursor get_all_new_childs(c_new_struc_ver_id NUMBER) IS
3235: select element_version_id
3236: from pa_proj_element_versions
3237: where parent_structure_version_id = c_new_struc_ver_id
3238: and object_type = 'PA_TASKS';
3239: --end bug 3047602
3240:
3239: --end bug 3047602
3240:
3241: l_rowid VARCHAR2(255);
3242: CURSOR cur_elem_ver_seq IS
3243: SELECT pa_proj_element_versions_s.nextval
3244: FROM sys.dual;
3245:
3246: l_last_pub_str_ver_id NUMBER; --Bug No. 3450684 Smukka 01/03/2004
3247: l_chk_deliverable VARCHAR2(80); --Summuka For checking deliverables
3282: l_financial_type := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(p_structure_version_id, 'FINANCIAL');
3283:
3284: select project_id
3285: into l_project_id
3286: from pa_proj_element_versions
3287: where element_version_id = p_structure_version_id;
3288:
3289: --bug 3840509
3290: IF 'Y' = nvl(PA_PROJECT_STRUCTURE_UTILS.Get_Sch_Dirty_fl(l_project_id,
3537: l_tmp_struct_ver_id := l_new_struct_ver_id ;
3538:
3539: -- End 4657794
3540:
3541: PA_PROJ_ELEMENT_VERSIONS_PKG.INSERT_ROW(
3542: X_ROW_ID => l_rowid
3543: --,X_ELEMENT_VERSION_ID => l_new_struct_ver_id * commented for Bug Fix: 4537865
3544: ,X_ELEMENT_VERSION_ID => l_tmp_struct_ver_id -- added for Bug fix: 4537865
3545: ,X_PROJ_ELEMENT_ID => l_struc_ver_rec.proj_element_id
3871:
3872: --Removing due to changes in publishing
3873: select a.proj_element_id, b.object_id_from1
3874: into l_parent_id, l_parent_ver_id
3875: from pa_proj_element_versions a,
3876: pa_object_relationships b
3877: where a.element_version_id = b.object_id_from1
3878: and a.object_type = b.object_type_from
3879: and relationship_type = 'S'
3958: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3959: LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
3960: where proj_element_id = l_task_versions_rec.proj_element_id;
3961:
3962: update pa_proj_element_versions
3963: set TASK_UNPUB_VER_STATUS_CODE = 'PUBLISHED'
3964: where element_version_id = l_task_versions_rec.element_version_id;
3965:
3966: -- 3955848 Added code to delete task to dlvr association in publishing flow , version enabled case
4023: l_parent_tbl(l_parent_ver_id) := l_parent_ver_id;
4024: END IF;
4025: END IF;
4026: ELSE --copy
4027: update pa_proj_element_versions
4028: set TASK_UNPUB_VER_STATUS_CODE = 'PUBLISHED'
4029: where element_version_id = l_task_versions_rec.element_version_id;
4030: END IF;
4031: END IF;
4620: --maansari
4621: --clear up global array
4622: PA_STRUCT_UPGR_PUB.clear_globals;
4623:
4624: INSERT INTO pa_proj_element_versions(
4625: ELEMENT_VERSION_ID
4626: ,PROJ_ELEMENT_ID
4627: ,OBJECT_TYPE
4628: ,PROJECT_ID
4657: ,source_object_type
4658: ,financial_task_flag
4659: )
4660: SELECT
4661: pa_proj_element_versions_s.nextval
4662: ,ppev.proj_element_id
4663: ,ppev.object_type
4664: ,l_project_id
4665: ,l_new_struct_ver_id
4691: ,ppev.element_version_id
4692: ,l_project_id
4693: ,'PA_PROJECTS'
4694: ,ppev.financial_task_flag
4695: FROM ( SELECT * from pa_proj_element_versions ppev2
4696: --,pa_proj_elements ppe --bug 4573340 commenting out this for bug 4578813
4697: WHERE --bug#3094283 ppev2.project_id = l_project_id
4698: ppev2.parent_structure_version_id = p_structure_version_id
4699: and ppev2.object_type = 'PA_TASKS'
4709: order by ppev2.display_sequence ) ppev
4710: ;
4711:
4712: /* --cant write order by directly.
4713: FROM pa_proj_element_versions ppev
4714: WHERE ppev.project_id = l_project_id
4715: and ppev.parent_structure_version_id = p_structure_version_id
4716: and ppev.object_type = 'PA_TASKS'
4717: and PA_PROJECT_STRUCTURE_PVT1.copy_task_version( p_structure_version_id,
4761: start with object_id_from1 = p_structure_version_id
4762: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
4763: connect by object_id_from1 = prior object_id_to1
4764: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
4765: pa_proj_element_versions ppev1,
4766: pa_proj_element_versions ppev2
4767: WHERE
4768: --bug#3094283 ppev1.project_id = l_project_id
4769: ppev1.attribute15 = pobj.object_id_from1
4762: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
4763: connect by object_id_from1 = prior object_id_to1
4764: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
4765: pa_proj_element_versions ppev1,
4766: pa_proj_element_versions ppev2
4767: WHERE
4768: --bug#3094283 ppev1.project_id = l_project_id
4769: ppev1.attribute15 = pobj.object_id_from1
4770: --bug#3094283 AND ppev2.project_id = l_project_id
4893: ,ppevs.EXT_REMAIN_DURATION
4894: ,ppevs.EXT_SCH_DURATION
4895: ,ppevs.DEF_SCH_TOOL_TSK_TYPE_CODE -- 4295770 Added
4896: FROM pa_proj_elem_ver_schedule ppevs,
4897: pa_proj_element_versions ppev1
4898: where ppev1.attribute15 = ppevs.element_version_id
4899: and ppevs.project_id = l_project_id
4900: and ppev1.project_id = l_project_id
4901: and ppev1.parent_structure_version_id = l_new_struct_ver_id
4906: -- Populate the old and new task version ID in PL/SQL tables
4907:
4908: Select Element_Version_ID, ATTRIBUTE15 Bulk Collect
4909: INTO l_New_Task_Versions_Tab, l_Old_Task_Versions_Tab
4910: From pa_proj_element_versions
4911: Where parent_structure_version_id = l_new_struct_ver_id
4912: and object_type = 'PA_TASKS'
4913: and PA_PROJECT_STRUCTURE_PVT1.copy_task_version( l_new_struct_ver_id, element_version_id ) = 'Y'
4914: order by display_sequence;
4991: --------------------------------------------- FP_M changes: End
4992: */
4993: --end bug 4019845
4994:
4995: update pa_proj_element_versions ppevs1
4996: set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
4997: where ppevs2.project_id = l_project_id
4998: and parent_structure_version_id = p_structure_version_id
4999: and ppevs2.element_version_id = ppevs1.attribute15
4992: */
4993: --end bug 4019845
4994:
4995: update pa_proj_element_versions ppevs1
4996: set attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
4997: where ppevs2.project_id = l_project_id
4998: and parent_structure_version_id = p_structure_version_id
4999: and ppevs2.element_version_id = ppevs1.attribute15
5000: )
5931: cursor get_task_version_info(c_task_version_id NUMBER) IS
5932: select v1.project_id project_id, v2.proj_element_id structure_id,
5933: v1.parent_structure_version_id structure_version_id,
5934: v1.element_version_id task_version_id
5935: from pa_proj_element_versions v1,
5936: pa_proj_element_versions v2
5937: where v1.element_version_id = c_task_version_id
5938: and v1.parent_structure_version_id = v2.element_version_id;
5939: l_info_task_ver_rec get_task_version_info%ROWTYPE;
5932: select v1.project_id project_id, v2.proj_element_id structure_id,
5933: v1.parent_structure_version_id structure_version_id,
5934: v1.element_version_id task_version_id
5935: from pa_proj_element_versions v1,
5936: pa_proj_element_versions v2
5937: where v1.element_version_id = c_task_version_id
5938: and v1.parent_structure_version_id = v2.element_version_id;
5939: l_info_task_ver_rec get_task_version_info%ROWTYPE;
5940:
5940:
5941: cursor get_structure_version_info(c_structure_version_id NUMBER) IS
5942: select v1.project_id project_id, v1.proj_element_id structure_id,
5943: v1.element_version_id structure_version_id
5944: from pa_proj_element_versions v1
5945: where v1.element_version_id = c_structure_version_id;
5946: l_info_struc_ver_rec get_structure_version_info%ROWTYPE;
5947:
5948: l_return_status VARCHAR2(1);
5955: l_object_type VARCHAR2(30);
5956:
5957: cursor get_latest_struc_ver(c_struc_ver_id NUMBER) IS
5958: select pevs.element_version_id
5959: from pa_proj_element_versions pev,
5960: pa_proj_elem_ver_structure pevs
5961: where pev.element_version_id = c_struc_ver_id
5962: and pev.project_id = pevs.project_id
5963: and pev.proj_element_id = pevs.proj_element_id
5965:
5966: cursor get_latest_task_ver(c_task_ver_id NUMBER) IS
5967: select pev2.element_version_id task_version_id,
5968: pev2.parent_structure_version_id parent_structure_version_id
5969: from pa_proj_element_versions pev,
5970: pa_proj_element_versions pev1,
5971: pa_proj_elem_ver_structure pevs,
5972: pa_proj_element_versions pev2
5973: where pev.element_version_id = c_task_ver_id
5966: cursor get_latest_task_ver(c_task_ver_id NUMBER) IS
5967: select pev2.element_version_id task_version_id,
5968: pev2.parent_structure_version_id parent_structure_version_id
5969: from pa_proj_element_versions pev,
5970: pa_proj_element_versions pev1,
5971: pa_proj_elem_ver_structure pevs,
5972: pa_proj_element_versions pev2
5973: where pev.element_version_id = c_task_ver_id
5974: and pev.parent_structure_version_id = pev1.element_version_id
5968: pev2.parent_structure_version_id parent_structure_version_id
5969: from pa_proj_element_versions pev,
5970: pa_proj_element_versions pev1,
5971: pa_proj_elem_ver_structure pevs,
5972: pa_proj_element_versions pev2
5973: where pev.element_version_id = c_task_ver_id
5974: and pev.parent_structure_version_id = pev1.element_version_id
5975: and pev1.project_id = pevs.project_id
5976: and pev1.proj_element_id = pevs.proj_element_id
5981:
5982: cursor can_update(c_element_version_id NUMBER) IS
5983: select '1'
5984: from pa_proj_elem_ver_structure pevs,
5985: pa_proj_element_versions pev
5986: where pev.element_version_id = c_element_version_id
5987: and pev.parent_structure_version_id = pevs.element_version_id
5988: and pev.project_id = pevs.project_id
5989: and pevs.status_code IN ('STRUCTURE_WORKING', 'STRUCTURE_REJECTED');
6184: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
6185: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
6186: ,x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
6187: IS
6188: l_new_struct_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
6189: l_return_status VARCHAR2(1);
6190: l_msg_count NUMBER;
6191: l_msg_data VARCHAR2(250);
6192: l_pev_structure_id NUMBER;
6193:
6194: CURSOR l_get_structure_ver_csr(c_structure_version_id NUMBER)
6195: IS
6196: SELECT *
6197: FROM PA_PROJ_ELEMENT_VERSIONS
6198: WHERE element_version_id = c_structure_version_id;
6199:
6200: l_structure_ver_rec l_get_structure_ver_csr%ROWTYPE;
6201: l_structure_ver_to_rec l_get_structure_ver_csr%ROWTYPE;
6203: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
6204: IS
6205: SELECT a.*
6206: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
6207: PA_PROJ_ELEMENT_VERSIONS b
6208: WHERE b.element_version_id = c_structure_version_id
6209: AND b.project_id = a.project_id
6210: AND b.element_version_id = a.project_id;
6211:
6215: IS
6216: SELECT a.element_version_id, a.proj_element_id, a.display_sequence, a.wbs_level,
6217: b.object_id_from1 parent_element_version_id,
6218: a.TASK_UNPUB_VER_STATUS_CODE
6219: FROM PA_PROJ_ELEMENT_VERSIONS a,
6220: PA_OBJECT_RELATIONSHIPS b
6221: WHERE a.object_type = 'PA_TASKS'
6222: AND a.parent_structure_version_id = c_structure_version_id
6223: AND a.element_version_id = b.object_id_to1
6233: CURSOR l_get_ver_schedule_attr_csr(c_element_version_id NUMBER)
6234: IS
6235: SELECT a.*
6236: FROM PA_PROJ_ELEM_VER_SCHEDULE a,
6237: PA_PROJ_ELEMENT_VERSIONS b
6238: WHERE b.element_version_id = c_element_version_id
6239: AND b.project_id = a.project_id
6240: AND b.element_version_id = a.element_version_id;
6241:
6264: CURSOR l_get_structure_type_csr(c_structure_version_id NUMBER)
6265: IS
6266: SELECT pst.structure_type_class_code
6267: FROM PA_STRUCTURE_TYPES pst,
6268: PA_PROJ_ELEMENT_VERSIONS ppev,
6269: PA_PROJ_STRUCTURE_TYPES ppst
6270: WHERE ppev.element_version_id = c_structure_version_id
6271: AND ppev.proj_element_id = ppst.proj_element_id
6272: AND ppst.structure_type_id = pst.structure_type_id;
6275:
6276: CURSOR l_check_working_versions_csr(c_structure_version_id NUMBER)
6277: IS
6278: SELECT 'Y'
6279: FROM PA_PROJ_ELEMENT_VERSIONS ppev
6280: WHERE ppev.element_version_id = c_structure_version_id
6281: AND EXISTS
6282: (SELECT 'Y'
6283: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
6279: FROM PA_PROJ_ELEMENT_VERSIONS ppev
6280: WHERE ppev.element_version_id = c_structure_version_id
6281: AND EXISTS
6282: (SELECT 'Y'
6283: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
6284: PA_PROJ_ELEM_VER_STRUCTURE ppevs
6285: WHERE ppev2.proj_element_id = ppev.proj_element_id
6286: AND ppev2.project_id = ppev.project_id
6287: AND ppevs.project_id = ppev2.project_id
6302: cursor get_task_version_info(c_task_version_id NUMBER) IS
6303: select v1.project_id project_id, v2.proj_element_id structure_id,
6304: v1.parent_structure_version_id structure_version_id,
6305: v1.element_version_id task_version_id
6306: from pa_proj_element_versions v1,
6307: pa_proj_element_versions v2
6308: where v1.element_version_id = c_task_version_id
6309: and v1.parent_structure_version_id = v2.element_version_id;
6310: l_info_task_ver_rec get_task_version_info%ROWTYPE;
6303: select v1.project_id project_id, v2.proj_element_id structure_id,
6304: v1.parent_structure_version_id structure_version_id,
6305: v1.element_version_id task_version_id
6306: from pa_proj_element_versions v1,
6307: pa_proj_element_versions v2
6308: where v1.element_version_id = c_task_version_id
6309: and v1.parent_structure_version_id = v2.element_version_id;
6310: l_info_task_ver_rec get_task_version_info%ROWTYPE;
6311:
7089:
7090: CURSOR l_get_structure_ver_csr(c_structure_version_id NUMBER)
7091: IS
7092: SELECT *
7093: FROM PA_PROJ_ELEMENT_VERSIONS
7094: WHERE element_version_id = c_structure_version_id;
7095:
7096: l_structure_ver_rec l_get_structure_ver_csr%ROWTYPE;
7097:
7099: -- CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER, c_pub_status VARCHAR2)
7100: -- IS
7101: -- SELECT a.*
7102: -- FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7103: -- PA_PROJ_ELEMENT_VERSIONS b
7104: -- WHERE b.element_version_id = c_structure_version_id
7105: -- AND b.element_version_id = a.element_version_id
7106: -- AND b.project_id = a.project_id
7107: -- AND (a.STATUS_CODE <> 'STRUCTURE_PUBLISHED' AND c_pub_status = 'N')
7107: -- AND (a.STATUS_CODE <> 'STRUCTURE_PUBLISHED' AND c_pub_status = 'N')
7108: -- UNION
7109: -- SELECT a.*
7110: -- FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7111: -- PA_PROJ_ELEMENT_VERSIONS b
7112: -- WHERE b.element_version_id = c_structure_version_id
7113: -- AND b.element_version_id = a.element_version_id
7114: -- AND b.project_id = a.project_id
7115: -- AND (a.STATUS_CODE = 'STRUCTURE_PUBLISHED' AND c_pub_status = 'Y' and a.LATEST_EFF_PUBLISHED_FLAG='Y');
7117: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
7118: IS
7119: SELECT a.*
7120: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7121: PA_PROJ_ELEMENT_VERSIONS b
7122: WHERE b.element_version_id = c_structure_version_id
7123: AND b.element_version_id = a.element_version_id
7124: AND b.proj_element_id = a.proj_element_id
7125: AND b.project_id = a.project_id;
7128: CURSOR l_get_task_versions_csr(c_structure_version_id NUMBER)
7129: IS
7130: SELECT a.element_version_id, a.proj_element_id, a.display_sequence, a.wbs_level,
7131: b.object_id_from1 parent_element_version_id
7132: FROM PA_PROJ_ELEMENT_VERSIONS a,
7133: PA_OBJECT_RELATIONSHIPS b,
7134: pa_proj_elements c
7135: WHERE a.object_type = 'PA_TASKS'
7136: AND a.parent_structure_version_id = c_structure_version_id
7157: CURSOR l_get_ver_schedule_attr_csr(c_element_version_id NUMBER)
7158: IS
7159: SELECT a.*
7160: FROM PA_PROJ_ELEM_VER_SCHEDULE a,
7161: PA_PROJ_ELEMENT_VERSIONS b
7162: WHERE b.element_version_id = c_element_version_id
7163: AND b.element_version_id = a.element_version_id
7164: AND b.project_id = a.project_id;
7165:
7350: l_proj_record_ver_number NUMBER;
7351: l_struc_scheduled_start_date DATE;
7352: l_struc_scheduled_finish_date DATE;
7353: --hsiu added for task version status
7354: l_task_unpub_ver_status_code pa_proj_element_versions.TASK_UNPUB_VER_STATUS_CODE%TYPE;
7355: --end task version status changes
7356:
7357: --hsiu: bug 2667527
7358: CURSOR get_init_task_stat(c_task_type_id NUMBER) IS
8406: IS
8407: CURSOR c1( c_project_id NUMBER ) IS
8408: SELECT B.PROJ_ELEMENT_ID, B.SCHEDULED_START_DATE,
8409: B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID , duration
8410: FROM PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B
8411: WHERE A.PARENT_STRUCTURE_VERSION_ID = p_structure_version_id
8412: AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID
8413: AND A.PROJECT_ID = B.PROJECT_ID
8414: AND A.project_id = c_project_id;
8434: l_duration NUMBER;
8435: l_duration_days NUMBER;
8436: cursor get_cal_id IS
8437: select a.calendar_id
8438: from pa_projects_all a, pa_proj_element_versions b
8439: where a.project_id = b.project_id
8440: and b.element_version_id = p_structure_version_id;
8441: BEGIN
8442: IF (p_debug_mode = 'Y') THEN
8448: END IF;
8449:
8450: SELECT PROJECT_ID
8451: INTO l_project_id
8452: FROM PA_PROJ_ELEMENT_VERSIONS
8453: WHERE ELEMENT_VERSION_ID = p_structure_version_id;
8454:
8455: UPDATE PA_PROJ_ELEMENTS
8456: SET BASELINE_START_DATE= NULL ,
8841:
8842: --check if delete unpublished ok
8843: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
8844: select b.element_version_id
8845: from pa_proj_element_versions a,
8846: pa_proj_element_versions b
8847: where a.element_version_id = c_keep_struc_ver_id
8848: and a.project_id = b.project_id
8849: and a.proj_element_id = b.proj_element_id
8842: --check if delete unpublished ok
8843: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
8844: select b.element_version_id
8845: from pa_proj_element_versions a,
8846: pa_proj_element_versions b
8847: where a.element_version_id = c_keep_struc_ver_id
8848: and a.project_id = b.project_id
8849: and a.proj_element_id = b.proj_element_id
8850: and b.element_version_id <> c_keep_struc_ver_id
9560: and c.structure_type_class_code = 'WORKPLAN';
9561:
9562: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
9563: select b.element_version_id, b.record_version_number
9564: from pa_proj_element_versions a,
9565: pa_proj_element_versions b
9566: where a.element_version_id = c_keep_struc_ver_id
9567: and a.project_id = b.project_id
9568: and a.proj_element_id = b.proj_element_id
9561:
9562: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
9563: select b.element_version_id, b.record_version_number
9564: from pa_proj_element_versions a,
9565: pa_proj_element_versions b
9566: where a.element_version_id = c_keep_struc_ver_id
9567: and a.project_id = b.project_id
9568: and a.proj_element_id = b.proj_element_id
9569: and b.element_version_id <> c_keep_struc_ver_id
9570: and b.object_type = 'PA_STRUCTURES';
9571:
9572: cursor sel_all_wp_structure_ver(c_struc_id NUMBER) IS
9573: select a.element_version_id, a.record_version_number
9574: from pa_proj_element_versions a,
9575: pa_proj_elements b
9576: where a.proj_element_id = b.proj_element_id
9577: and a.project_id = b.project_id
9578: and b.proj_element_id = c_struc_id;
9614: where structure_type_class_code = 'WORKPLAN';
9615:
9616: cursor sel_struc_ver(c_structure_id NUMBER) IS
9617: select element_version_id
9618: from pa_proj_element_versions
9619: where project_id = p_project_id
9620: and proj_element_id = c_structure_id
9621: and object_type = 'PA_STRUCTURES';
9622:
9622:
9623: --hsiu: commented for performance
9624: -- cursor sel_struc_and_task_vers(c_struc_ver_id NUMBER) IS
9625: -- select object_type, proj_element_id, element_version_id
9626: -- from pa_proj_element_versions
9627: -- where parent_structure_version_id = c_struc_ver_id;
9628: cursor sel_struc_and_task_vers(c_struc_ver_id NUMBER) IS
9629: select pev.object_type, pev.proj_element_id, pev.element_version_id
9630: from pa_proj_element_versions pev, pa_object_relationships rel
9626: -- from pa_proj_element_versions
9627: -- where parent_structure_version_id = c_struc_ver_id;
9628: cursor sel_struc_and_task_vers(c_struc_ver_id NUMBER) IS
9629: select pev.object_type, pev.proj_element_id, pev.element_version_id
9630: from pa_proj_element_versions pev, pa_object_relationships rel
9631: where pev.parent_structure_version_id = c_struc_ver_id
9632: and rel.object_id_to1 = pev.element_version_id
9633: and rel.relationship_type = 'S'
9634: and NOT EXISTS (
9651: where project_id = p_project_id;
9652:
9653: CURSOR get_top_tasks(c_structure_version_id NUMBER) IS
9654: select v.element_version_id
9655: from pa_proj_element_versions v,
9656: pa_object_relationships r
9657: where v.element_version_id = r.object_id_to1
9658: and r.object_id_from1 = c_structure_version_id
9659: and r.object_type_from = 'PA_STRUCTURES';
10405: FND_GLOBAL.LOGIN_ID,
10406: PPEV.project_id,
10407: 'PA_PROJECTS'
10408: FROM PA_TASKS PT,
10409: PA_PROJ_ELEMENT_VERSIONS PPEV
10410: WHERE
10411: PPEV.parent_structure_version_id = l_structure_version_id
10412: AND PPEV.proj_element_id = PT.task_id (+);
10413: -- anlee end of bulk insert
10977:
10978: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
10979: select a.PEV_STRUCTURE_ID, a.record_version_number
10980: from pa_proj_elem_ver_structure a,
10981: pa_proj_element_versions b
10982: where b.project_id = a.project_id
10983: and b.element_version_id = c_struc_ver_id
10984: and a.element_version_id = b.element_version_id;
10985:
10984: and a.element_version_id = b.element_version_id;
10985:
10986: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
10987: select b.element_version_id, b.record_version_number
10988: from pa_proj_element_versions a,
10989: pa_proj_element_versions b
10990: where a.element_version_id = c_keep_struc_ver_id
10991: and a.project_id = b.project_id
10992: and a.proj_element_id = b.proj_element_id
10985:
10986: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
10987: select b.element_version_id, b.record_version_number
10988: from pa_proj_element_versions a,
10989: pa_proj_element_versions b
10990: where a.element_version_id = c_keep_struc_ver_id
10991: and a.project_id = b.project_id
10992: and a.proj_element_id = b.proj_element_id
10993: and b.element_version_id <> c_keep_struc_ver_id
10996: --bug 3125813
10997: cursor sel_one_struc_ver(c_structure_id NUMBER) IS
10998: select b.project_id, b.element_version_id
10999: from pa_proj_elements a,
11000: pa_proj_element_versions b
11001: where a.proj_element_id = c_structure_id
11002: and a.project_id = b.project_id
11003: and a.proj_element_id = b.proj_element_id;
11004: --end bug 3125813
11005:
11006: --bug 4263266
11007: CURSOR cur_chk_tasks(c_project_id NUMBER, c_structure_version_id NUMBER)
11008: IS
11009: SELECT 'x' from pa_proj_element_versions
11010: WHERE project_id = c_project_id
11011: AND parent_structure_version_id = c_structure_version_id
11012: AND object_type = 'PA_TASKS'
11013: ;
11699: ppvsch.scheduled_start_date, ppvsch.scheduled_finish_date,
11700: ppvsch.estimated_start_date, ppvsch.estimated_finish_date,
11701: ppvsch.actual_start_date, ppvsch.actual_finish_date
11702: from pa_proj_elem_ver_schedule ppvsch,
11703: pa_proj_element_versions ppv
11704: where ppv.parent_structure_version_id = p_structure_version_id
11705: and ppv.project_id = ppvsch.project_id
11706: and ppv.proj_element_id = ppvsch.proj_element_id
11707: and ppv.element_version_id = ppvsch.element_version_id;
11905: pevs.pev_structure_id, pevs.record_version_number,
11906: pevsh.pev_schedule_id, pevsh.record_version_number,
11907: pevsh.rowid
11908: from pa_proj_elements pe,
11909: pa_proj_element_versions pev,
11910: pa_proj_elem_ver_structure pevs,
11911: pa_proj_elem_ver_schedule pevsh
11912: where pev.element_version_id = p_structure_version_id and
11913: pev.proj_element_id = pe.proj_element_id and
11917: pev.element_version_id = pevsh.element_version_id (+);
11918:
11919: CURSOR is_last_version(p_structure_id NUMBER) IS
11920: select 'N'
11921: from pa_proj_element_versions
11922: where proj_element_id = p_structure_id;
11923:
11924: CURSOR get_top_tasks IS
11925: select v.element_version_id
11922: where proj_element_id = p_structure_id;
11923:
11924: CURSOR get_top_tasks IS
11925: select v.element_version_id
11926: from pa_proj_element_versions v,
11927: pa_object_relationships r
11928: where v.element_version_id = r.object_id_to1
11929: and r.object_id_from1 = p_structure_version_id
11930: and r.object_type_from = 'PA_STRUCTURES';
11933: select record_version_number
11934: from pa_proj_workplan_attr
11935: where proj_element_id = c_proj_element_id;
11936:
11937: l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
11938: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
11939: l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
11940: l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11941: l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11936:
11937: l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
11938: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
11939: l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
11940: l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11941: l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11942: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
11943: l_pevs_rvn PA_PROJ_ELEM_VER_STRUCTURE.RECORD_VERSION_NUMBER%TYPE;
11944: l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
11937: l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
11938: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
11939: l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
11940: l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11941: l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11942: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
11943: l_pevs_rvn PA_PROJ_ELEM_VER_STRUCTURE.RECORD_VERSION_NUMBER%TYPE;
11944: l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
11945: l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
11944: l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
11945: l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
11946: l_pevsh_rowid VARCHAR2(255);
11947:
11948: l_task_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11949: l_task_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11950: l_wp_attr_rvn PA_PROJ_WORKPLAN_ATTR.RECORD_VERSION_NUMBER%TYPE;
11951:
11952: l_parent_struc_ver_id PA_PROJ_ELEMENT_VERSIONS.PARENT_STRUCTURE_VERSION_ID%TYPE;
11945: l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
11946: l_pevsh_rowid VARCHAR2(255);
11947:
11948: l_task_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11949: l_task_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11950: l_wp_attr_rvn PA_PROJ_WORKPLAN_ATTR.RECORD_VERSION_NUMBER%TYPE;
11951:
11952: l_parent_struc_ver_id PA_PROJ_ELEMENT_VERSIONS.PARENT_STRUCTURE_VERSION_ID%TYPE;
11953:
11948: l_task_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11949: l_task_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11950: l_wp_attr_rvn PA_PROJ_WORKPLAN_ATTR.RECORD_VERSION_NUMBER%TYPE;
11951:
11952: l_parent_struc_ver_id PA_PROJ_ELEMENT_VERSIONS.PARENT_STRUCTURE_VERSION_ID%TYPE;
11953:
11954: BEGIN
11955: IF (p_debug_mode = 'Y') THEN
11956: pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUC_VER_WO_VAL begin');
11996: --Get record version number for task, as it will change everytime
11997: --a task is deleted.
11998: select record_version_number, parent_structure_version_id
11999: into l_task_rvn, l_parent_struc_ver_id
12000: from pa_proj_element_versions
12001: where element_version_id = l_task_version_id;
12002:
12003:
12004: PA_TASK_PVT1.DELETE_TASK_VER_WO_VAL(p_commit => 'N',
12266: CURSOR get_struct_ver_info IS
12267: select a.name, a.version_number, c.scheduled_start_date,
12268: c.scheduled_finish_date, b.project_id
12269: from pa_proj_elem_ver_structure a,
12270: pa_proj_element_versions b,
12271: pa_proj_elem_ver_schedule c
12272: where b.element_version_id = p_structure_version_id
12273: and b.project_id = a.project_id
12274: and b.proj_element_id = a.proj_element_id
12698:
12699: CURSOR c_get_task_ver IS
12700: SELECT element_version_id
12701: , wbs_level
12702: FROM pa_proj_element_versions
12703: WHERE project_id = p_project_id
12704: AND parent_structure_version_id = p_structure_version_id
12705: AND object_type = 'PA_TASKS';
12706:
12706:
12707: CURSOR check_progress_allowed(c_element_version_id NUMBER) IS
12708: SELECT ptt.prog_entry_enable_flag
12709: FROM pa_task_types ptt
12710: , pa_proj_element_versions ppev
12711: , pa_proj_elements ppe
12712: WHERE ppev.element_version_id = c_element_version_id
12713: AND ppev.proj_element_id = ppe.proj_element_id
12714: AND ptt.object_type ='PA_TASKS' /* bug 3279978 FP M Enhancement */
12725: CURSOR get_summed_duration(c_parent_element_version_id NUMBER) IS
12726: SELECT sum(ppevs.duration)
12727: FROM pa_proj_elem_ver_schedule ppevs
12728: , pa_object_relationships por
12729: , pa_proj_element_versions ppev
12730: , pa_proj_elements ppe
12731: , pa_task_types ptt
12732: WHERE por.object_id_from1 = c_parent_element_version_id
12733: AND por.object_type_to = 'PA_TASKS'
12901: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
12902: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
12903: ,x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
12904: IS
12905: l_new_struct_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
12906: -- added for Bug Fix: 4537865
12907: l_tmp_struct_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
12908: -- added for Bug Fix: 4537865
12909: l_return_status VARCHAR2(1);
12903: ,x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
12904: IS
12905: l_new_struct_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
12906: -- added for Bug Fix: 4537865
12907: l_tmp_struct_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
12908: -- added for Bug Fix: 4537865
12909: l_return_status VARCHAR2(1);
12910: l_msg_count NUMBER;
12911: l_msg_data VARCHAR2(250);
12915:
12916: CURSOR l_get_structure_ver_csr(c_structure_version_id NUMBER)
12917: IS
12918: SELECT *
12919: FROM PA_PROJ_ELEMENT_VERSIONS
12920: WHERE element_version_id = c_structure_version_id;
12921:
12922: l_structure_ver_rec l_get_structure_ver_csr%ROWTYPE;
12923: l_structure_ver_to_rec l_get_structure_ver_csr%ROWTYPE;
12925: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
12926: IS
12927: SELECT a.*
12928: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
12929: PA_PROJ_ELEMENT_VERSIONS b
12930: WHERE b.element_version_id = c_structure_version_id
12931: AND b.project_id = a.project_id
12932: AND b.element_version_id = a.project_id;
12933:
12939: CURSOR l_get_ver_schedule_attr_csr(c_element_version_id NUMBER)
12940: IS
12941: SELECT a.*
12942: FROM PA_PROJ_ELEM_VER_SCHEDULE a,
12943: PA_PROJ_ELEMENT_VERSIONS b
12944: WHERE b.element_version_id = c_element_version_id
12945: AND b.project_id = a.project_id
12946: AND b.element_version_id = a.element_version_id;
12947:
12954: CURSOR l_get_structure_type_csr(c_structure_version_id NUMBER)
12955: IS
12956: SELECT pst.structure_type_class_code
12957: FROM PA_STRUCTURE_TYPES pst,
12958: PA_PROJ_ELEMENT_VERSIONS ppev,
12959: PA_PROJ_STRUCTURE_TYPES ppst
12960: WHERE ppev.element_version_id = c_structure_version_id
12961: AND ppev.proj_element_id = ppst.proj_element_id
12962: AND ppst.structure_type_id = pst.structure_type_id;
12965:
12966: CURSOR l_check_working_versions_csr(c_structure_version_id NUMBER)
12967: IS
12968: SELECT 'Y'
12969: FROM PA_PROJ_ELEMENT_VERSIONS ppev
12970: WHERE ppev.element_version_id = c_structure_version_id
12971: AND EXISTS
12972: (SELECT 'Y'
12973: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
12969: FROM PA_PROJ_ELEMENT_VERSIONS ppev
12970: WHERE ppev.element_version_id = c_structure_version_id
12971: AND EXISTS
12972: (SELECT 'Y'
12973: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
12974: PA_PROJ_ELEM_VER_STRUCTURE ppevs
12975: WHERE ppev2.proj_element_id = ppev.proj_element_id
12976: AND ppev2.project_id = ppev.project_id
12977: AND ppevs.project_id = ppev2.project_id
12992: l_user_id number;
12993: l_login_id number;
12994:
12995: CURSOR cur_elem_ver_seq IS
12996: SELECT pa_proj_element_versions_s.nextval
12997: FROM sys.dual;
12998:
12999: l_wp_struc VARCHAR2(1);
13000: l_fin_struc VARCHAR2(1);
13029: l_tmp_struct_ver_id := l_new_struct_ver_id ;
13030:
13031: -- End 4657794
13032:
13033: PA_PROJ_ELEMENT_VERSIONS_PKG.INSERT_ROW(
13034: X_ROW_ID => l_rowid
13035: --,X_ELEMENT_VERSION_ID => l_new_struct_ver_id * commented for Bug Fix: 453786
13036: ,X_ELEMENT_VERSION_ID => l_tmp_struct_ver_id -- added for bug Fix: 4537865
13037: ,X_PROJ_ELEMENT_ID => l_structure_ver_rec.proj_element_id
13253:
13254: l_user_id := FND_GLOBAl.user_id;
13255: l_login_id := FND_GLOBAl.login_id;
13256:
13257: INSERT INTO pa_proj_element_versions(
13258: ELEMENT_VERSION_ID
13259: ,PROJ_ELEMENT_ID
13260: ,OBJECT_TYPE
13261: ,PROJECT_ID
13290: ,source_object_id
13291: ,source_object_type
13292: )
13293: SELECT
13294: pa_proj_element_versions_s.nextval
13295: ,ppev.proj_element_id
13296: ,ppev.object_type
13297: ,l_project_id
13298: ,l_new_struct_ver_id
13324: ,ppev.FINANCIAL_TASK_FLAG
13325: ,ppev.element_version_id
13326: ,l_project_id
13327: ,'PA_PROJECTS'
13328: FROM ( SELECT ppev2.* from pa_proj_element_versions ppev2
13329: ,pa_proj_elements ppe --bug 4573340
13330: WHERE -- bug#3094283 ppev2.project_id = l_project_id
13331: ppev2.parent_structure_version_id = p_structure_version_id
13332: --bug 4573340
13378: start with object_id_from1 = p_structure_version_id
13379: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
13380: connect by object_id_from1 = prior object_id_to1
13381: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
13382: pa_proj_element_versions ppev1,
13383: pa_proj_element_versions ppev2
13384: WHERE
13385: --bug#3094283 ppev1.project_id = l_project_id
13386: ppev1.attribute15 = pobj.object_id_from1
13379: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
13380: connect by object_id_from1 = prior object_id_to1
13381: and RELATIONSHIP_TYPE = 'S' ) pobj, /* Bug 2881667 - Added this condition */
13382: pa_proj_element_versions ppev1,
13383: pa_proj_element_versions ppev2
13384: WHERE
13385: --bug#3094283 ppev1.project_id = l_project_id
13386: ppev1.attribute15 = pobj.object_id_from1
13387: --bug#3094283 AND ppev2.project_id = l_project_id
13510: ,ppevs.EXT_REMAIN_DURATION
13511: ,ppevs.EXT_SCH_DURATION
13512: ,ppevs.DEF_SCH_TOOL_TSK_TYPE_CODE -- Fix For Bug # 4321287.
13513: FROM pa_proj_elem_ver_schedule ppevs,
13514: pa_proj_element_versions ppev1
13515: where ppev1.attribute15 = ppevs.element_version_id
13516: and ppevs.project_id = l_project_id
13517: and ppev1.project_id = l_project_id
13518: and ppev1.parent_structure_version_id = l_new_struct_ver_id
13530: New_Versions_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); /* Venky */
13531: Begin
13532: Select Element_Version_ID, attribute15 BULK COLLECT
13533: INTO New_Versions_Tab, Old_Versions_Tab
13534: From PA_Proj_Element_Versions
13535: Where Project_ID = l_project_id
13536: AND parent_structure_version_id = l_new_struct_ver_id;
13537:
13538: PA_Relationship_Pvt.Copy_Intra_Dependency (
13672: -- till here by skannoji
13673:
13674: -----------------------------------------FP_M Changes : End
13675:
13676: UPDATE pa_proj_element_versions ppevs1
13677: SET attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
13678: where ppevs2.project_id = l_project_id
13679: and parent_structure_version_id = p_structure_version_id
13680: and ppevs2.element_version_id = ppevs1.attribute15
13673:
13674: -----------------------------------------FP_M Changes : End
13675:
13676: UPDATE pa_proj_element_versions ppevs1
13677: SET attribute15 = ( select attribute15 from pa_proj_element_versions ppevs2
13678: where ppevs2.project_id = l_project_id
13679: and parent_structure_version_id = p_structure_version_id
13680: and ppevs2.element_version_id = ppevs1.attribute15
13681: )
13733: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
13734:
13735: --Bug No 3634334
13736: --Commented the following query for performance reason and rewritten the same.
13737: --Getting the project_id from pa_proj_element_versions table instead of getting
13738: --it from pa_proj_elem_ver_structure table, in this optimizer will be using the
13739: --unique index on element_version_id and functionally the process is fetching
13740: --project_id
13741: /* CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
13744: WHERE element_version_id = cp_structure_version_id;*/
13745:
13746: CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
13747: SELECT project_id
13748: FROM pa_proj_element_versions
13749: WHERE element_version_id = cp_structure_version_id;
13750: l_proj_id NUMBER;
13751: BEGIN
13752:
13922: and c.structure_type_class_code = l_wp_structure_code;
13923:
13924: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
13925: select b.element_version_id, b.record_version_number
13926: from pa_proj_element_versions a,
13927: pa_proj_element_versions b
13928: where a.element_version_id = c_keep_struc_ver_id
13929: and a.project_id = b.project_id
13930: and a.proj_element_id = b.proj_element_id
13923:
13924: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
13925: select b.element_version_id, b.record_version_number
13926: from pa_proj_element_versions a,
13927: pa_proj_element_versions b
13928: where a.element_version_id = c_keep_struc_ver_id
13929: and a.project_id = b.project_id
13930: and a.proj_element_id = b.proj_element_id
13931: and b.element_version_id <> c_keep_struc_ver_id
13932: and b.object_type = 'PA_STRUCTURES';
13933:
13934: cursor sel_all_wp_structure_ver(c_struc_id NUMBER) IS
13935: select a.element_version_id, a.record_version_number
13936: from pa_proj_element_versions a,
13937: pa_proj_elements b
13938: where a.proj_element_id = b.proj_element_id
13939: and a.project_id = b.project_id
13940: and b.proj_element_id = c_struc_id;
13979: where structure_type_class_code = 'WORKPLAN';
13980:
13981: cursor sel_struc_ver(c_structure_id NUMBER) IS
13982: select element_version_id
13983: from pa_proj_element_versions
13984: where project_id = p_project_id
13985: and proj_element_id = c_structure_id
13986: and object_type = 'PA_STRUCTURES';
13987:
13987:
13988: --hsiu: commented for performance
13989: -- cursor sel_struc_and_task_vers(c_struc_ver_id NUMBER) IS
13990: -- select object_type, proj_element_id, element_version_id
13991: -- from pa_proj_element_versions
13992: -- where parent_structure_version_id = c_struc_ver_id;
13993: cursor sel_struc_and_task_vers(c_struc_ver_id NUMBER) IS
13994: select pev.object_type, pev.proj_element_id, pev.element_version_id
13995: from pa_proj_element_versions pev, pa_object_relationships rel
13991: -- from pa_proj_element_versions
13992: -- where parent_structure_version_id = c_struc_ver_id;
13993: cursor sel_struc_and_task_vers(c_struc_ver_id NUMBER) IS
13994: select pev.object_type, pev.proj_element_id, pev.element_version_id
13995: from pa_proj_element_versions pev, pa_object_relationships rel
13996: where pev.parent_structure_version_id = c_struc_ver_id
13997: and rel.object_id_to1 = pev.element_version_id
13998: and rel.relationship_type = 'S'
13999: and NOT EXISTS (
14016: where project_id = p_project_id;
14017:
14018: CURSOR get_top_tasks(c_structure_version_id NUMBER) IS
14019: select v.element_version_id
14020: from pa_proj_element_versions v,
14021: pa_object_relationships r
14022: where v.element_version_id = r.object_id_to1
14023: and r.object_id_from1 = c_structure_version_id
14024: and r.object_type_from = 'PA_STRUCTURES';
14088: , por1.record_version_number rec_ver_number
14089: from pa_object_relationships por1
14090: , pa_object_relationships por2
14091: , pa_projects_all ppa
14092: , pa_proj_element_versions ppev
14093: , pa_proj_elem_ver_structure ppevs
14094: where por1.object_id_to2 = c_project_id
14095: and por1.relationship_type in ('LW', 'LF')
14096: and por1.object_id_from1 = por2.object_id_to1
14116: , por1.record_version_number rec_ver_number
14117: from pa_object_relationships por1
14118: , pa_object_relationships por2
14119: , pa_projects_all ppa
14120: , pa_proj_element_versions ppev
14121: , pa_proj_elem_ver_structure ppevs
14122: where por1.object_id_from2 = c_project_id
14123: and por1.relationship_type in ('LW', 'LF')
14124: and por1.object_id_from1 = por2.object_id_to1
15111: FND_GLOBAL.LOGIN_ID,
15112: PPEV.project_id,
15113: 'PA_PROJECTS'
15114: FROM PA_TASKS PT,
15115: PA_PROJ_ELEMENT_VERSIONS PPEV
15116: WHERE
15117: PPEV.parent_structure_version_id = l_structure_version_id
15118: AND PPEV.proj_element_id = PT.task_id (+);
15119:
15649: IF (p_sharing_option_code = 'SHARE_PARTIAL') THEN
15650: NULL;
15651: ELSIF (p_sharing_option_code = 'SHARE_FULL') THEN
15652: --set financial task flag to Y for all tasks
15653: update pa_proj_element_versions
15654: set financial_task_flag = 'Y'
15655: where parent_structure_version_id = l_structure_version_id
15656: and object_type = 'PA_TASKS'
15657: and proj_element_id NOT IN
16044: delete from pa_proj_elem_ver_schedule
16045: where project_id =p_project_id --Bug No 3634334
16046: and element_version_id IN (
16047: select element_version_id
16048: from pa_proj_element_versions
16049: where project_id = p_project_id --Bug No 3634334
16050: and parent_structure_version_id = l_keep_structure_ver_id
16051: and object_type IN ('PA_TASKS', 'PA_STRUCTURES'));
16052:
16054: --Bug No 3634334 Commented for performance tuning and rewritten the query.
16055: /* delete from pa_object_relationships rel
16056: where rel.relationship_type IN ('D','S')
16057: and EXISTS (
16058: select 1 from pa_proj_element_versions
16059: where (rel.object_id_from1 = element_version_id OR
16060: rel.object_id_to1 = element_version_id ) and
16061: parent_structure_version_id = l_keep_structure_ver_id and
16062: financial_task_flag = 'N'); */
16066: delete from pa_object_relationships rel
16067: where OBJECT_RELATIONSHIP_ID IN (
16068: select OBJECT_RELATIONSHIP_ID
16069: from pa_object_relationships rel,
16070: pa_proj_element_versions
16071: where rel.relationship_type IN ('D','S')
16072: and object_type_to = 'PA_TASKS'
16073: and rel.object_id_to1 = element_version_id
16074: and parent_structure_version_id = l_keep_structure_ver_id
16075: and financial_task_flag = 'N'
16076: UNION
16077: select OBJECT_RELATIONSHIP_ID
16078: from pa_object_relationships rel,
16079: pa_proj_element_versions
16080: where rel.relationship_type IN ('D','S')
16081: and object_type_from = 'PA_TASKS'
16082: and rel.object_id_from1 = element_version_id
16083: and parent_structure_version_id = l_keep_structure_ver_id
16097: --delete elements
16098: delete from pa_proj_elements ppe
16099: where proj_element_ID in (
16100: select proj_element_id
16101: from pa_proj_element_versions
16102: where parent_structure_version_id = l_keep_structure_ver_id
16103: and object_type = 'PA_TASKS'
16104: and financial_task_flag = 'N');
16105:
16103: and object_type = 'PA_TASKS'
16104: and financial_task_flag = 'N');
16105:
16106: --delete element versions
16107: Delete from pa_proj_element_versions
16108: where parent_structure_version_id = l_keep_structure_ver_id
16109: and object_type = 'PA_TASKS'
16110: and financial_task_flag = 'N';
16111:
16118:
16119: /* Bug 3906015
16120: Moved this code to above (i.e) before deleting the elements (Before deleting task)
16121: This is necessary because : delete_dlv_associations API has been modified
16122: in such a way that it retrives the tasks from pa_proj_element_versions table for the passed project_id
16123: and then for those values ,it performs deliverable related validations for Workplan Task Deletion
16124:
16125: If this call,is after delete statement on pa_proj_element_versions tables,the logic written
16126: in delete_dlv_associations APi will fail .Hence moved the code up.
16121: This is necessary because : delete_dlv_associations API has been modified
16122: in such a way that it retrives the tasks from pa_proj_element_versions table for the passed project_id
16123: and then for those values ,it performs deliverable related validations for Workplan Task Deletion
16124:
16125: If this call,is after delete statement on pa_proj_element_versions tables,the logic written
16126: in delete_dlv_associations APi will fail .Hence moved the code up.
16127:
16128: -- NYU
16129: -- delete deliverable associations
16345: l_keep_structure_ver_id := l_structure_version_id;
16346: END IF;
16347: CLOSE sel_latest_pub_ver;
16348:
16349: update pa_proj_element_versions
16350: set financial_task_flag = 'Y'
16351: where parent_structure_version_id = l_structure_version_id
16352: and object_type = 'PA_TASKS'
16353: and proj_element_id NOT IN
16579: delete from pa_proj_elem_ver_schedule
16580: where project_id =p_project_id --Bug No 3634334
16581: and element_version_id IN (
16582: select element_version_id
16583: from pa_proj_element_versions
16584: where project_id =p_project_id --Bug No 3634334
16585: and parent_structure_version_id = l_keep_structure_ver_id
16586: and object_type IN ('PA_TASKS', 'PA_STRUCTURES'));
16587:
17100: FND_GLOBAL.LOGIN_ID,
17101: PPEV.project_id,
17102: 'PA_PROJECTS'
17103: FROM PA_TASKS PT,
17104: PA_PROJ_ELEMENT_VERSIONS PPEV
17105: WHERE
17106: PPEV.parent_structure_version_id = l_structure_version_id
17107: AND PPEV.proj_element_id = PT.task_id (+);
17108:
17492: FND_GLOBAL.LOGIN_ID,
17493: PPEV.project_id,
17494: 'PA_PROJECTS'
17495: FROM PA_TASKS PT,
17496: PA_PROJ_ELEMENT_VERSIONS PPEV
17497: WHERE
17498: PPEV.parent_structure_version_id = l_structure_version_id
17499: AND PPEV.proj_element_id = PT.task_id (+);
17500: