[Home] [Help]
319:
320: CURSOR cur_chk_ref_task
321: IS
322: SELECT 'x'
323: FROM pa_proj_element_versions
324: WHERE object_type = 'PA_STRUCTURES'
325: AND element_version_id = p_ref_task_id; --Just making sure ref_task_id does not contain
326: --structure version id.
327:
945: close cur_proj_tmp;
946: if (l_version_enabled_flag <> 'Y' AND nvl(l_template_flag,'N') <> 'Y') then --13551360: Added Condition nvl(l_template_flag,'N') <> 'Y'..end.
947: select element_version_id
948: into l_ref_parent_task_ver_id
949: from pa_proj_element_versions
950: where proj_element_id = p_ref_task_id
951: and parent_structure_version_id =
952: PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(p_project_id);
953: else
952: PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(p_project_id);
953: else
954: select element_version_id
955: into l_ref_parent_task_ver_id
956: from pa_proj_element_versions
957: where proj_element_id = p_ref_task_id
958: and parent_structure_version_id =
959: PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(p_project_id);
960: end if;
1793:
1794: CURSOR cur_struc_id
1795: IS
1796: SELECT a.proj_element_id
1797: FROM pa_proj_element_versions a, pa_proj_element_versions b
1798: WHERE a.element_version_id = b.parent_structure_version_id
1799: AND b.proj_element_id = p_task_id;
1800:
1801: CURSOR get_task_types_attr(c_task_type_id NUMBER) IS
1810: --hsiu task status changes
1811: cursor get_latest_task_ver_id IS
1812: select b.parent_structure_version_id, b.element_version_id
1813: from pa_proj_elements a,
1814: pa_proj_element_versions b,
1815: pa_proj_elem_ver_structure c
1816: where a.proj_element_id = p_task_id
1817: and a.project_id = b.project_id
1818: and a.proj_element_id = b.proj_element_id
1843: --
1844: --Bug 3475920
1845: CURSOR get_task_versions_id(cp_task_id NUMBER,cp_project_id NUMBER) IS
1846: SELECT ppev.element_Version_id
1847: FROM pa_proj_element_versions ppev,
1848: pa_proj_elem_ver_structure ppevs
1849: WHERE ppev.project_id = cp_project_id
1850: and ppev.proj_element_id = cp_task_id
1851: and ppev.project_id = ppevs.project_id
1853: and ppevs.status_code <> 'STRUCTURE_PUBLISHED';
1854: --
1855: CURSOR get_task_versions_id2(cp_task_id NUMBER,cp_project_id NUMBER) IS
1856: SELECT ppev.element_Version_id
1857: FROM pa_proj_element_versions ppev,
1858: pa_proj_elem_ver_structure ppevs
1859: WHERE ppev.project_id = cp_project_id
1860: and ppev.proj_element_id = cp_task_id
1861: and ppev.project_id = ppevs.project_id
3302: l_relationship_id NUMBER;
3303:
3304: l_link_task_flag VARCHAR2(1);
3305:
3306: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
3307: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
3308: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3303:
3304: l_link_task_flag VARCHAR2(1);
3305:
3306: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
3307: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
3308: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3304: l_link_task_flag VARCHAR2(1);
3305:
3306: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
3307: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
3308: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3305:
3306: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
3307: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
3308: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3306: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
3307: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
3308: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3307: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
3308: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3308: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3309: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3310: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3311: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3319: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
3312: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3319: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
3320: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
3313: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3319: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
3320: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
3321: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
3314: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3319: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
3320: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
3321: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
3322: l_weighting_percentage PA_OBJECT_RELATIONSHIPS.weighting_percentage%TYPE;
3315: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3319: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
3320: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
3321: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
3322: l_weighting_percentage PA_OBJECT_RELATIONSHIPS.weighting_percentage%TYPE;
3323:
3316: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3319: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
3320: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
3321: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
3322: l_weighting_percentage PA_OBJECT_RELATIONSHIPS.weighting_percentage%TYPE;
3323:
3324: CURSOR cur_proj_elems
3317: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
3318: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
3319: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
3320: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
3321: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
3322: l_weighting_percentage PA_OBJECT_RELATIONSHIPS.weighting_percentage%TYPE;
3323:
3324: CURSOR cur_proj_elems
3325: IS
3323:
3324: CURSOR cur_proj_elems
3325: IS
3326: SELECT *
3327: FROM pa_proj_element_versions
3328: WHERE element_version_id = p_ref_task_version_id;
3329:
3330: --Pick the max display sequence in the heirarchy of the reference task id.
3331: CURSOR cur_proj_elem_ver
3339: FROM pa_object_relationships
3340: WHERE relationship_type = 'S'
3341: START WITH object_id_from1 = p_ref_task_version_id
3342: CONNECT BY object_id_from1 = PRIOR object_id_to1
3343: AND relationship_type = 'S' ) a, pa_proj_element_versions b
3344: WHERE a.object_id_to1 = b.element_version_id;
3345: */
3346: SELECT max( b.display_sequence )
3347: FROM pa_proj_element_versions b
3343: AND relationship_type = 'S' ) a, pa_proj_element_versions b
3344: WHERE a.object_id_to1 = b.element_version_id;
3345: */
3346: SELECT max( b.display_sequence )
3347: FROM pa_proj_element_versions b
3348: WHERE b.element_version_id IN
3349: ( SELECT object_id_to1
3350: FROM pa_object_relationships
3351: WHERE relationship_type = 'S'
3588:
3589: l_parent_struc_ver_id := v_cur_proj_elems_rec.parent_structure_version_id;
3590:
3591: SELECT proj_element_id INTO l_structure_id_from
3592: FROM pa_proj_element_versions
3593: WHERE element_version_id = v_cur_proj_elems_rec.parent_structure_version_id;
3594:
3595: l_structure_id_to := l_structure_id_from;
3596:
3697:
3698: --my_error_msg( 'In create_task_version pvt API Stage 3' );
3699:
3700:
3701: PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
3702: X_ROW_ID => X_row_id
3703: ,X_ELEMENT_VERSION_ID => x_task_version_id
3704: ,X_PROJ_ELEMENT_ID => p_task_id
3705: ,X_OBJECT_TYPE => 'PA_TASKS'
3744: -- Once all tasks are added, we re-order them just ONCE
3745: IF NVL(PA_PROJECT_PUB.G_MASS_ADD_TASKS,'N') = 'Y' THEN
3746: -- skkkoppul added if block for Huawei bug 13923366
3747: /*IF v_cur_proj_elems_rec.object_type = 'PA_TASKS' THEN
3748: Update PA_PROJ_ELEMENT_VERSIONS
3749: set display_sequence = l_ref_seq_no,
3750: last_updated_by = -ABS(FND_LOG_MESSAGES_S.NEXTVAL)
3751: where element_version_id = x_task_version_id;
3752: END IF;*/
3775: IF l_wbs_level <> 1 THEN
3776:
3777: --This IF/ELSE condition is added for BUg 4046026.
3778: --update the sequnce number
3779: UPDATE pa_proj_element_versions
3780: SET display_sequence = display_sequence + 1
3781: WHERE display_sequence > l_ref_seq_no
3782: AND element_version_id <> x_task_version_id
3783: AND parent_structure_version_id = v_cur_proj_elems_rec.parent_structure_version_id
3786: ;
3787: ELSE
3788: IF p_peer_or_sub = 'PEER' --Bug 4046026
3789: THEN
3790: UPDATE pa_proj_element_versions
3791: SET display_sequence = display_sequence + 1
3792: WHERE display_sequence > l_ref_seq_no
3793: AND element_version_id <> x_task_version_id
3794: AND parent_structure_version_id = v_cur_proj_elems_rec.parent_structure_version_id
4009: l_relationship_id NUMBER;
4010:
4011: l_wbs_level_diff NUMBER;
4012:
4013: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
4014: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
4015: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4010:
4011: l_wbs_level_diff NUMBER;
4012:
4013: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
4014: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
4015: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4011: l_wbs_level_diff NUMBER;
4012:
4013: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
4014: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
4015: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4012:
4013: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
4014: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
4015: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4013: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
4014: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
4015: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4014: l_attribute1 PA_PROJ_ELEMENT_VERSIONS.attribute1%TYPE;
4015: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4015: l_attribute2 PA_PROJ_ELEMENT_VERSIONS.attribute2%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4016: l_attribute3 PA_PROJ_ELEMENT_VERSIONS.attribute3%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4017: l_attribute4 PA_PROJ_ELEMENT_VERSIONS.attribute4%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4018: l_attribute5 PA_PROJ_ELEMENT_VERSIONS.attribute5%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4019: l_attribute6 PA_PROJ_ELEMENT_VERSIONS.attribute6%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4027: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
4020: l_attribute7 PA_PROJ_ELEMENT_VERSIONS.attribute7%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4027: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
4028: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
4021: l_attribute8 PA_PROJ_ELEMENT_VERSIONS.attribute8%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4027: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
4028: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
4029: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
4022: l_attribute9 PA_PROJ_ELEMENT_VERSIONS.attribute9%TYPE;
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4027: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
4028: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
4029: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
4030:
4023: l_attribute10 PA_PROJ_ELEMENT_VERSIONS.attribute10%TYPE;
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4027: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
4028: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
4029: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
4030:
4031: CURSOR cur_ref_task
4024: l_attribute11 PA_PROJ_ELEMENT_VERSIONS.attribute11%TYPE;
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4027: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
4028: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
4029: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
4030:
4031: CURSOR cur_ref_task
4032: IS
4025: l_attribute12 PA_PROJ_ELEMENT_VERSIONS.attribute12%TYPE;
4026: l_attribute13 PA_PROJ_ELEMENT_VERSIONS.attribute13%TYPE;
4027: l_attribute14 PA_PROJ_ELEMENT_VERSIONS.attribute14%TYPE;
4028: l_attribute15 PA_PROJ_ELEMENT_VERSIONS.attribute15%TYPE;
4029: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
4030:
4031: CURSOR cur_ref_task
4032: IS
4033: SELECT *
4030:
4031: CURSOR cur_ref_task
4032: IS
4033: SELECT *
4034: FROM pa_proj_element_versions
4035: WHERE element_version_id = p_ref_task_version_id;
4036:
4037:
4038: CURSOR cur_proj_elems
4041: display_sequence, wbs_number, wbs_level, parent_structure_version_id,
4042: attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
4043: attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12,
4044: attribute13, attribute14, attribute15, TASK_UNPUB_VER_STATUS_CODE
4045: FROM pa_proj_element_versions
4046: WHERE element_version_id = p_task_version_id
4047: AND object_type = 'PA_TASKS';
4048:
4049: CURSOR cur_child_tasks( c_task_version_id NUMBER )
4054: WHERE relationship_type = 'S'
4055: START WITH object_id_from1 = c_task_version_id
4056: CONNECT BY object_id_from1 = PRIOR object_id_to1
4057: AND relationship_type = prior relationship_type --bug 3919266
4058: AND relationship_type = 'S' ) a, pa_proj_element_versions b
4059: WHERE a.object_id_to1 = b.element_version_id
4060: ORDER BY 4;
4061:
4062: --bug 2832807
4061:
4062: --bug 2832807
4063: CURSOR cur_child_tasks2( c_task_version_id NUMBER )
4064: IS SELECT max( display_sequence )
4065: FROM pa_proj_element_versions
4066: WHERE element_version_id in ( SELECT object_id_to1
4067: FROM pa_object_relationships
4068: WHERE relationship_type = 'S'
4069: START WITH object_id_from1 = c_task_version_id
4074:
4075: --bug 2836264
4076: CURSOR cur_child_tasks3( c_task_version_id NUMBER ,c_ref_task_disp_seq NUMBER) -- Bug 6628382 modified existing cursor to restrict the tasks which are being moved to fall between the source and the destination task
4077: IS SELECT max( display_sequence )
4078: FROM pa_proj_element_versions
4079: WHERE element_version_id in ( SELECT object_id_to1
4080: FROM pa_object_relationships
4081: WHERE relationship_type = 'S'
4082: and object_id_to1 <> p_task_version_id
4089:
4090: --Added for Bug 6628382
4091: CURSOR cur_child_tasks4( c_task_version_id NUMBER) -- Bug 6628382. Added another cursor to compute the last child task of any task.
4092: IS SELECT max( display_sequence)
4093: FROM pa_proj_element_versions
4094: WHERE element_version_id in ( SELECT object_id_to1
4095: FROM pa_object_relationships
4096: WHERE relationship_type = 'S'
4097: and object_id_to1 <> p_task_version_id
4114: CURSOR cur_new_child
4115: IS
4116: SELECT por.object_id_to1, ppev.display_sequence, por.record_version_number, por.object_relationship_id
4117: FROM pa_object_relationships por,
4118: pa_proj_element_versions ppev
4119: WHERE object_id_from1 = p_ref_task_version_id
4120: AND object_id_to1 = element_version_id
4121: AND display_sequence > l_display_sequence
4122: AND relationship_type = 'S'
4171: CURSOR check_progress_allowed(c_element_version_id NUMBER)
4172: IS
4173: SELECT ptt.prog_entry_enable_flag
4174: FROM pa_task_types ptt,
4175: pa_proj_element_versions ppev,
4176: pa_proj_elements ppe
4177: WHERE ppev.element_version_id = c_element_version_id
4178: AND ppev.proj_element_id = ppe.proj_element_id
4179: AND ppe.TYPE_ID = ptt.task_type_id;
4197:
4198: BEGIN
4199:
4200: -- 14556729:Huawei code change :get the Sharing code and process below code only if its fully shared
4201: SELECT PROJECT_ID INTO L_PROJECT_ID FROM pa_proj_element_versions WHERE element_version_id = p_task_version_id ;
4202: l_Share_Code := PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(L_PROJECT_ID);
4203: IF (l_Share_Code='SHARE_FULL') THEN
4204: if ( p_action = 'MOVE' ) then
4205: select project_id, wbs_level,display_sequence
4203: IF (l_Share_Code='SHARE_FULL') THEN
4204: if ( p_action = 'MOVE' ) then
4205: select project_id, wbs_level,display_sequence
4206: into l2_project_id , l2_ref_task_wbs_level, l2_ref_task_display_seq
4207: from pa_proj_element_versions
4208: where element_version_id = p_ref_task_version_id;
4209:
4210: select wbs_level
4211: into l2_task_wbs_level
4208: where element_version_id = p_ref_task_version_id;
4209:
4210: select wbs_level
4211: into l2_task_wbs_level
4212: from pa_proj_element_versions
4213: where element_version_id = p_task_version_id;
4214:
4215: end if;
4216:
4249: --Bug 13895419 start
4250: --Fetch parent_task_id and ref_parent_task_id for source task and target task respectively from db.
4251: begin
4252: SELECT rel.object_id_from1 INTO l_parent_task_id
4253: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
4254: WHERE pev.element_version_id = p_task_version_id
4255: AND pev.object_type = 'PA_TASKS'
4256: AND rel.object_id_to1 = pev.element_version_id
4257: AND rel.relationship_type = 'S'
4257: AND rel.relationship_type = 'S'
4258: AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
4259:
4260: SELECT rel.object_id_from1 INTO l_ref_parent_task_id
4261: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
4262: WHERE pev.element_version_id = p_ref_task_version_id
4263: AND pev.object_type = 'PA_TASKS'
4264: AND rel.object_id_to1 = pev.element_version_id
4265: AND rel.relationship_type = 'S'
4535: l_no_of_tasks := 1;
4536:
4537: l_parent_struc_ver_id := v_cur_proj_elems_rec.parent_structure_version_id;
4538:
4539: PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
4540: X_ROW_ID => v_cur_proj_elems_rec.rowid
4541: ,X_ELEMENT_VERSION_ID => p_task_version_id
4542: ,X_PROJ_ELEMENT_ID => v_cur_proj_elems_rec.proj_element_id
4543: ,X_OBJECT_TYPE => v_cur_proj_elems_rec.object_type
4605: --14556729:Huawei Code change for Move Task API. For Fully shared structure this change has better performance in updateing WBS_LEVEL and display sequence .
4606: -- We have called REFRESH_STRUCTURE_VERSION at the end to update Display Sequence and WBS level
4607: IF (l_Share_Code='SHARE_FULL') THEN
4608: if (p_peer_or_sub = 'SUB' and p_action = 'MOVE' ) then
4609: update pa_proj_element_versions b
4610: SET b.display_sequence = l2_ref_task_display_seq,
4611: b.last_updated_by = decode( b.element_version_id,p_task_version_id,0,-1)
4612: where b.element_version_id in ( p_task_version_id,p_ref_task_version_id);
4613: end if;
4611: b.last_updated_by = decode( b.element_version_id,p_task_version_id,0,-1)
4612: where b.element_version_id in ( p_task_version_id,p_ref_task_version_id);
4613: end if;
4614: if (p_peer_or_sub = 'PEER' and p_action = 'MOVE' ) then
4615: update pa_proj_element_versions b
4616: SET b.display_sequence = l2_ref_task_display_seq,
4617: b.last_updated_by = decode( b.element_version_id,p_task_version_id,0,-1)
4618: where b.element_version_id in ( p_task_version_id,l2_peer_task_version_id);
4619: end if;
4638: --14556729:Huawei Code change for Move Task API. For Fully shared structure this change has better performance in updateing WBS_LEVEL and display sequence .
4639: -- We have called REFRESH_STRUCTURE_VERSION at the end to update Display Sequence and WBS level
4640: IF (l_Share_Code='SHARE_FULL') THEN
4641: if ( p_action = 'MOVE' ) then
4642: update pa_proj_element_versions b
4643: SET b.display_sequence = l2_ref_task_display_seq,
4644: b.wbs_level =
4645: decode(p_peer_or_sub,
4646: 'SUB',l2_ref_task_wbs_level+(b.wbs_level- l2_task_wbs_level)+1,
4647: l2_ref_task_wbs_level+(b.wbs_level - l2_task_wbs_level)),
4648: b.last_updated_by = b.display_sequence
4649: where b.element_version_id = cur_child_tasks_rec.object_id_to1;
4650: else
4651: UPDATE pa_proj_element_versions
4652: SET wbs_level = wbs_level + l_wbs_level_diff,
4653: display_sequence = decode( p_action, 'MOVE', l_new_display_sequence, display_sequence )
4654: WHERE element_version_id = cur_child_tasks_rec.object_id_to1;
4655: end if;
4654: WHERE element_version_id = cur_child_tasks_rec.object_id_to1;
4655: end if;
4656: ELSE
4657:
4658: UPDATE pa_proj_element_versions
4659: SET wbs_level = wbs_level + l_wbs_level_diff,
4660: display_sequence = decode( p_action, 'MOVE', l_new_display_sequence, display_sequence )
4661: WHERE element_version_id = cur_child_tasks_rec.object_id_to1;
4662: END IF;
4727: DECLARE
4728: CURSOR cur_update_with_null
4729: IS
4730: SELECT element_version_id, display_sequence
4731: FROM pa_proj_element_versions
4732: WHERE parent_structure_version_id = l_parent_struc_ver_id
4733: AND display_sequence < 0;
4734:
4735: TYPE CurrTasks IS RECORD( task_version_id NUMBER(15), display_sequence NUMBER(15) );
4740:
4741: --update display sequence of the tasks with NULL to update WBS_NUMBER.
4742: FOR cur_update_with_null_rec in cur_update_with_null LOOP
4743:
4744: UPDATE pa_proj_element_versions
4745: SET display_sequence = null
4746: WHERE element_version_id = cur_update_with_null_rec.element_version_id;
4747:
4748: l_TaskTab(i).task_version_id := cur_update_with_null_rec.element_version_id;
4767: ,x_return_status => l_return_status );
4768: END If;
4769: --Bug 13895419 end
4770: FOR k in 1..i-1 LOOP
4771: UPDATE pa_proj_element_versions
4772: SET display_sequence = l_TaskTab(k).display_sequence
4773: WHERE element_version_id = l_TaskTab(k).task_version_id;
4774: END LOOP;
4775:
4777: /*DECLARE
4778: CURSOR cur_print_wbs
4779: IS
4780: SELECT element_version_id, wbs_number
4781: FROM pa_proj_element_versions
4782: WHERE parent_structure_version_id = l_parent_struc_ver_id
4783: ORDER BY display_sequence;
4784: BEGIN
4785: FOR cur_print_wbs_rec in cur_print_wbs LOOP
4991: pev.element_version_id child_ver_id,
4992: parent.element_version_id parent_ver_id,
4993: pev.display_sequence, rel.object_id_to1, rel.object_type_to,
4994: rel.relationship_type, rel.object_id_from1, rel.object_type_from
4995: FROM pa_proj_element_versions pev, pa_object_relationships rel,
4996: pa_proj_element_versions parent
4997: WHERE rel.object_type_from = 'PA_TASKS'
4998: AND rel.relationship_type = 'S'
4999: AND rel.object_type_to = 'PA_TASKS'
4992: parent.element_version_id parent_ver_id,
4993: pev.display_sequence, rel.object_id_to1, rel.object_type_to,
4994: rel.relationship_type, rel.object_id_from1, rel.object_type_from
4995: FROM pa_proj_element_versions pev, pa_object_relationships rel,
4996: pa_proj_element_versions parent
4997: WHERE rel.object_type_from = 'PA_TASKS'
4998: AND rel.relationship_type = 'S'
4999: AND rel.object_type_to = 'PA_TASKS'
5000: AND rel.object_id_from1 = parent.element_version_id
5017: DECLARE
5018: CURSOR cur_print_wbs
5019: IS
5020: SELECT element_version_id, wbs_number
5021: FROM pa_proj_element_versions
5022: WHERE parent_structure_version_id = l_parent_struc_ver_id
5023: ORDER BY display_sequence;
5024: BEGIN
5025: FOR cur_print_wbs_rec in cur_print_wbs LOOP
5039: pev.element_version_id child_ver_id,
5040: parent.element_version_id parent_ver_id,
5041: pev.display_sequence, rel.object_id_to1, rel.object_type_to,
5042: rel.relationship_type, rel.object_id_from1, rel.object_type_from
5043: FROM pa_proj_element_versions pev, pa_object_relationships rel,
5044: pa_proj_element_versions parent
5045: WHERE rel.object_type_from = 'PA_TASKS'
5046: AND rel.relationship_type = 'S'
5047: AND rel.object_type_to = 'PA_TASKS'
5040: parent.element_version_id parent_ver_id,
5041: pev.display_sequence, rel.object_id_to1, rel.object_type_to,
5042: rel.relationship_type, rel.object_id_from1, rel.object_type_from
5043: FROM pa_proj_element_versions pev, pa_object_relationships rel,
5044: pa_proj_element_versions parent
5045: WHERE rel.object_type_from = 'PA_TASKS'
5046: AND rel.relationship_type = 'S'
5047: AND rel.object_type_to = 'PA_TASKS'
5048: AND rel.object_id_from1 = parent.element_version_id
5055: DECLARE
5056: CURSOR cur_affected_tasks
5057: IS
5058: SELECT element_version_id, parent_structure_version_id, display_sequence
5059: FROM pa_proj_element_versions
5060: WHERE parent_structure_version_id = l_parent_struc_ver_id
5061: AND ( ( l_move_direction = 'DOWN' and p_peer_or_sub = 'SUB' and
5062: display_sequence > nvl( l_task_last_child_seq_num, l_task_seq_num ) and display_sequence <= l_ref_task_seq_num ) OR
5063: ( l_move_direction = 'DOWN' and p_peer_or_sub = 'PEER' and
5102:
5103: --dbms_output.put_line( 'Affected tasks old display_sequence '|| cur_affected_tasks_rec.display_sequence );
5104: --dbms_output.put_line( 'Affected tasks l_new_display_sequence '|| l_new_display_sequence );
5105:
5106: UPDATE pa_proj_element_versions
5107: SET display_sequence = l_new_display_sequence
5108: WHERE element_version_id = cur_affected_tasks_rec.element_version_id;
5109:
5110: --Bug 13895419 start
5138: DECLARE
5139: CURSOR cur_actual_tasks
5140: IS
5141: SELECT element_version_id, display_sequence, parent_structure_version_id
5142: FROM pa_proj_element_versions
5143: WHERE parent_structure_version_id = l_parent_struc_ver_id
5144: AND display_sequence < 0
5145: ORDER BY display_sequence desc;
5146: BEGIN
5161: --**
5162:
5163: --dbms_output.put_line( 'TAsk sequence number '||l_new_display_sequence );
5164:
5165: UPDATE pa_proj_element_versions
5166: SET display_sequence = l_new_display_sequence
5167: WHERE element_version_id = cur_actual_tasks_rec.element_version_id;
5168:
5169: --Bug 13895419 start
5196: DECLARE
5197: CURSOR cur_affected_tasks
5198: IS
5199: SELECT element_version_id, parent_structure_version_id, display_sequence
5200: FROM pa_proj_element_versions
5201: WHERE parent_structure_version_id = l_parent_struc_ver_id
5202: AND display_sequence > nvl( l_ref_task_last_child_seq_num, l_ref_task_seq_num )
5203: ORDER BY display_sequence;
5204: --Bug 13895419 start
5204: --Bug 13895419 start
5205: CURSOR c1(c_start_display_seq NUMBER, c_end_display_seq NUMBER)
5206: IS
5207: SELECT element_version_id, parent_structure_version_id, display_sequence
5208: FROM pa_proj_element_versions
5209: WHERE parent_structure_version_id = l_parent_struc_ver_id
5210: AND display_sequence between c_start_display_seq and c_end_display_seq
5211: ORDER BY display_sequence;
5212: l_start_display_seq NUMBER := 0;
5354: WHERE relationship_type = 'S'
5355: START WITH object_id_from1 = p_task_version_id
5356: CONNECT BY object_id_from1 = PRIOR object_id_to1
5357: AND relationship_type = prior relationship_type -- bug 3919266
5358: AND relationship_type = 'S' ) A, pa_proj_element_versions b
5359: ,pa_proj_elements c --bug 4573340
5360: WHERE a.object_id_to1 = b.element_version_id
5361: --bug 4573340
5362: and b.proj_element_id = c.proj_element_id
5365: --bug 4573340
5366: UNION
5367: SELECT a.object_relationship_id, element_version_id object_id_to1,
5368: a.record_version_number, wbs_level, b.display_sequence
5369: FROM pa_object_relationships a, pa_proj_element_versions b
5370: WHERE element_version_id = p_task_version_id
5371: AND object_id_to1 = p_task_version_id
5372: AND element_version_id = object_id_to1
5373: AND relationship_type = 'S'
5376:
5377: CURSOR cur_proj_elem_ver( p_task_id NUMBER )
5378: IS
5379: SELECT rowid,record_version_number, project_id, parent_structure_version_id, proj_element_id
5380: FROM pa_proj_element_versions
5381: WHERE element_version_id = p_task_id;
5382:
5383: CURSOR cur_sch_ver( p_task_id NUMBER, p_project_id NUMBER )
5384: IS
5403:
5404: CURSOR cur_chk_vers( x_proj_element_id NUMBER, x_task_version NUMBER )
5405: IS
5406: SELECT 'X'
5407: FROM pa_proj_element_versions
5408: WHERE proj_element_id = x_proj_element_id
5409: AND element_version_id <> x_task_version;
5410:
5411: CURSOR cur_proj_elems( x_proj_element_id NUMBER )
5431: IS
5432: select b.object_id_to1 object_id_to1
5433: from pa_object_relationships a,
5434: pa_object_relationships b,
5435: pa_proj_element_versions c,
5436: pa_proj_elements d
5437: where a.object_id_to1 = p_task_version_id
5438: and a.object_type_to = 'PA_TASKS'
5439: and a.object_id_from1 = b.object_id_from1
5477:
5478: --hsiu task status changes
5479: cursor get_latest_task_ver_id IS
5480: select b.parent_structure_version_id, b.element_version_id
5481: from pa_proj_element_versions b,
5482: pa_proj_elem_ver_structure c
5483: where b.element_version_id = p_task_version_id
5484: and b.project_id = c.project_id
5485: and b.parent_structure_version_id = c.element_version_id
5573: and prior object_type_to = object_type_from
5574: and prior object_id_to1 = object_id_from1
5575: UNION
5576: select element_version_id task_ver_id
5577: from pa_proj_element_versions
5578: where element_version_id = c_task_version_id;
5579: --end bug 4006401
5580:
5581: --bug 4110957
5579: --end bug 4006401
5580:
5581: --bug 4110957
5582: CURSOR get_tsk_cnt(c_struc_ver_id NUMBER) IS
5583: select count(1) from pa_proj_element_versions
5584: where object_type = 'PA_TASKS'
5585: and parent_structure_version_id = c_struc_ver_id;
5586: l_tsk_ver_cnt NUMBER;
5587: --end bug 4110957
5625: /*
5626: Begin
5627: SELECT project_id
5628: Into l_Project_ID
5629: FROM pa_proj_element_versions
5630: WHERE element_version_id = p_task_version_id and rownum < 2;
5631: End;
5632: */
5633: -- 4201927 end
5633: -- 4201927 end
5634:
5635: -- 4201927 added project_id column selection
5636: select proj_element_id, project_id into l_task_id , l_Project_ID -- 4201927
5637: from pa_proj_element_versions
5638: where element_version_id = p_task_version_id;
5639:
5640: -- 4201927 Retrieving structure sharding code once and using it for further processing
5641: l_structure_sharing_code := PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(l_Project_id); -- dthakker added
5778: IF nvl(l_shared,'N') = 'N' THEN
5779: l_delete_flag := 'Y';
5780: ELSE
5781: SELECT proj_element_id INTO l_structure_id
5782: FROM pa_proj_element_versions
5783: WHERE element_version_id = p_structure_version_id
5784: AND project_id = l_project_id;
5785:
5786: IF PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(
6069: IF nvl(l_shared,'N') = 'N' THEN
6070: l_delete_flag := 'Y';
6071: ELSE
6072: SELECT proj_element_id INTO l_structure_id
6073: FROM pa_proj_element_versions
6074: WHERE element_version_id = x_parent_struc_ver_id
6075: AND project_id = x_project_id;
6076:
6077: IF PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(
6316: END IF;
6317: raise FND_API.G_EXC_ERROR;
6318: END IF;
6319:
6320: PA_PROJ_ELEMENT_VERSIONS_PKG.Delete_Row( x_row_id );
6321:
6322: -- Added condition for Huawei enhancement bug 13923366
6323: -- defering the wbs_numbers update in bulk mode
6324: -- 14556729:Huawei Code change to defer the roll up API when G_ROLLUP_DEFER is set to Y , so that all the roll up is processed once at the end .
6610: -- 14556729:Huawei Code change to defer the roll up API when G_ROLLUP_DEFER is set to Y , so that all the roll up is processed once at the end .
6611: IF NVL(PA_PROJECT_PUB.G_ROLLUP_DEFER,'N') = 'N' THEN
6612:
6613: BEGIN
6614: UPDATE PA_PROJ_ELEMENT_VERSIONS
6615: SET display_sequence = PA_TASKS_MAINT_UTILS.REARRANGE_DISPLAY_SEQ(display_sequence, 0, l_task_cnt, 'DELETE', 'DOWN')
6616: -- record_version_number = record_version_number + 1
6617: WHERE parent_structure_version_id = x_parent_struc_ver_id
6618: AND (display_sequence > l_selected_seq_num)
6980:
6981: CURSOR cur_proj_elem_ver
6982: IS
6983: SELECT rowid, object_type, project_id, proj_element_id
6984: FROM pa_proj_element_versions
6985: WHERE element_version_id = p_element_version_id;
6986:
6987: cur_proj_elem_ver_rec cur_proj_elem_ver%ROWTYPE;
6988: X_Row_id VARCHAR2(255);
6988: X_Row_id VARCHAR2(255);
6989:
6990: cursor get_cal_id IS
6991: select a.calendar_id
6992: from pa_projects_all a, pa_proj_element_versions b
6993: where a.project_id = b.project_id
6994: and b.element_version_id = p_element_version_id;
6995:
6996: /* Bug 2791413 Begin */
6996: /* Bug 2791413 Begin */
6997: CURSOR get_project_id
6998: IS
6999: SELECT project_id,proj_element_id
7000: FROM pa_proj_element_versions
7001: WHERE element_version_id = p_element_version_id;
7002:
7003:
7004: cursor get_task_type_id(c_proj_element_id NUMBER) IS
7593: -- Added for FP_M changes 3305199 Bhumesh
7594: If PA_Project_Structure_Utils.Check_Third_Party_Sch_Flag(l_Project_ID)= 'Y' Then
7595:
7596: Select Parent_Structure_Version_ID INTO l_Structure_Version_ID
7597: From PA_Proj_Element_Versions
7598: Where Element_Version_ID = P_Element_Version_ID;
7599:
7600: PA_PROJECT_STRUCTURE_PVT1.update_sch_dirty_flag(
7601: p_structure_version_id => l_Structure_Version_ID
7912: l_update_flag VARCHAR2(1) := 'N';
7913: l_parent_struc_Ver_id NUMBER;
7914: cursor get_parent_struc_ver_id(c_elem_ver_id NUMBER) IS
7915: select parent_structure_version_id
7916: from pa_proj_element_versions
7917: where element_version_id = c_elem_ver_id;
7918:
7919: --3035902: process update flag changes
7920: l_weighting_basis VARCHAR2(30);
8763: IF (PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(cur_proj_elem_ver_sch_rec.project_id) IN ('SHARE_FULL','SHARE_PARTIAL'))
8764: THEN
8765:
8766: SELECT financial_task_flag into l_financial_task_flag_tmp
8767: FROM pa_proj_element_versions
8768: WHERE element_version_id = cur_proj_elem_ver_sch_rec.element_version_id;
8769:
8770: IF (l_financial_task_flag_tmp = 'Y')
8771: THEN
8779:
8780: IF nvl(l_workplan_version_enable_flag,'N') = 'Y' THEN
8781:
8782: SELECT proj_element_id into l_proj_element_id_temp
8783: FROM pa_proj_element_versions
8784: WHERE element_version_id = PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(cur_proj_elem_ver_sch_rec.project_id);
8785: ELSE
8786: -- Added the code below for Huawei enhancement - bug 13923366
8787: -- Caching structure element ID
8788: IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEN
8789: IF PA_PROJECT_PUB.G_STRUC_ELEMENT_ID IS NULL THEN
8790: SELECT pev.proj_element_id
8791: INTO l_proj_element_id_temp
8792: FROM pa_proj_element_versions pev, pa_proj_structure_types pst
8793: WHERE pev.proj_element_id = pst.proj_element_id
8794: AND pst.structure_type_id = 1
8795: AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
8796:
8801: ELSE
8802: /* Commented and added below caching logic for huawei
8803: SELECT pev.proj_element_id
8804: INTO l_proj_element_id_temp
8805: FROM pa_proj_element_versions pev, pa_proj_structure_types pst
8806: WHERE pev.proj_element_id = pst.proj_element_id
8807: AND pst.structure_type_id = 1
8808: AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
8809: */
8811: PA_PROGRESS_PUB.G_PROJECT_ID <> cur_proj_elem_ver_sch_rec.project_id
8812: THEN
8813: SELECT pev.proj_element_id
8814: INTO l_proj_element_id_temp
8815: FROM pa_proj_element_versions pev, pa_proj_structure_types pst
8816: WHERE pev.proj_element_id = pst.proj_element_id
8817: AND pst.structure_type_id = 1
8818: AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
8819:
8821: ELSE
8822: IF PA_PROGRESS_PUB.G_STRUC_ELEMENT_ID IS NULL OR PA_PROGRESS_PUB.G_STRUC_ELEMENT_ID=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
8823: SELECT pev.proj_element_id
8824: INTO l_proj_element_id_temp
8825: FROM pa_proj_element_versions pev, pa_proj_structure_types pst
8826: WHERE pev.proj_element_id = pst.proj_element_id
8827: AND pst.structure_type_id = 1
8828: AND pev.project_id = cur_proj_elem_ver_sch_rec.project_id;
8829: PA_PROGRESS_PUB.G_STRUC_ELEMENT_ID := l_proj_element_id_temp;
9115: IS
9116: CURSOR get_parent_task_type_attr IS
9117: select c.TYPE_ID , c.WQ_ITEM_CODE, c.WQ_UOM_CODE, c.WQ_ACTUAL_ENTRY_CODE,
9118: c.TASK_PROGRESS_ENTRY_PAGE_ID
9119: from pa_proj_element_versions a,
9120: pa_object_relationships b,
9121: pa_proj_elements c
9122: where b.object_id_to1 = p_task_version_id
9123: and b.object_type_to = 'PA_TASKS'
9349: CURSOR check_progress_allowed(c_element_version_id NUMBER)
9350: IS
9351: SELECT ptt.prog_entry_enable_flag
9352: FROM pa_task_types ptt,
9353: pa_proj_element_versions ppev,
9354: pa_proj_elements ppe
9355: WHERE ppev.element_version_id = c_element_version_id
9356: AND ppev.proj_element_id = ppe.proj_element_id
9357: AND ppe.TYPE_ID = ptt.task_type_id;
9507: and prior object_type_to = object_type_from;
9508:
9509: l_task_version_id NUMBER;
9510: BEGIN
9511: UPDATE pa_proj_element_versions
9512: set TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED',
9513: RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
9514: LAST_UPDATE_DATE = SYSDATE,
9515: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
9520: LOOP
9521: FETCH get_task_versions into l_task_version_id;
9522: EXIT WHEN get_task_versions%NOTFOUND;
9523:
9524: UPDATE pa_proj_element_versions
9525: set TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED',
9526: RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
9527: LAST_UPDATE_DATE = SYSDATE,
9528: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
9590: WHERE relationship_type = 'S'
9591: START WITH object_id_from1 = p_task_version_id
9592: CONNECT BY object_id_from1 = PRIOR object_id_to1
9593: AND relationship_type = PRIOR relationship_type --bug 3919266
9594: AND relationship_type = 'S' ) A, pa_proj_element_versions b
9595: ,pa_proj_elements c --bug 4573340
9596: WHERE a.object_id_to1 = b.element_version_id
9597: --bug 4573340
9598: and b.proj_element_id = c.proj_element_id
9601: --bug 4573340
9602: UNION
9603: SELECT a.object_relationship_id, element_version_id object_id_to1,
9604: a.record_version_number, wbs_level, b.display_sequence
9605: FROM pa_object_relationships a, pa_proj_element_versions b
9606: WHERE element_version_id = p_task_version_id
9607: AND object_id_to1 = p_task_version_id
9608: AND element_version_id = object_id_to1
9609: AND relationship_type = 'S'
9612:
9613: CURSOR cur_proj_elem_ver( p_task_id NUMBER )
9614: IS
9615: SELECT rowid,record_version_number, project_id, parent_structure_version_id, proj_element_id
9616: FROM pa_proj_element_versions
9617: WHERE element_version_id = p_task_id;
9618:
9619: CURSOR cur_sch_ver( p_task_id NUMBER, p_project_id NUMBER )
9620: IS
9639:
9640: CURSOR cur_chk_vers( x_proj_element_id NUMBER, x_task_version NUMBER )
9641: IS
9642: SELECT 'X'
9643: FROM pa_proj_element_versions
9644: WHERE proj_element_id = x_proj_element_id
9645: AND element_version_id <> x_task_version;
9646:
9647: CURSOR cur_proj_elems( x_proj_element_id NUMBER )
9707:
9708: --hsiu task status changes
9709: cursor get_latest_task_ver_id IS
9710: select b.parent_structure_version_id, b.element_version_id
9711: from pa_proj_element_versions b,
9712: pa_proj_elem_ver_structure c
9713: where b.element_version_id = p_task_version_id
9714: and b.project_id = c.project_id
9715: and b.parent_structure_version_id = c.element_version_id
9914: p_structure_version_id => x_parent_struc_ver_id
9915: ,p_structure_type => 'FINANCIAL' ) = 'Y') THEN
9916:
9917: SELECT proj_element_id INTO l_structure_id
9918: FROM pa_proj_element_versions
9919: WHERE element_version_id = x_parent_struc_ver_id
9920: AND project_id = x_project_id;
9921:
9922: IF (PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(
10098: END IF;
10099: raise FND_API.G_EXC_ERROR;
10100: END IF;
10101:
10102: PA_PROJ_ELEMENT_VERSIONS_PKG.Delete_Row( x_row_id );
10103:
10104: --Ansari
10105: --Call update wbs number
10106: --hsiu: bug 2800553: performance changes: not called when deleting structure
10265: -- Hence commenting out the update of RVN here.
10266:
10267:
10268: BEGIN
10269: UPDATE PA_PROJ_ELEMENT_VERSIONS
10270: SET display_sequence = PA_TASKS_MAINT_UTILS.REARRANGE_DISPLAY_SEQ(display_sequence, 0, l_task_cnt, 'DELETE', 'DOWN')
10271: -- record_version_number = record_version_number + 1
10272: WHERE parent_structure_version_id = x_parent_struc_ver_id
10273: AND (display_sequence > l_selected_seq_num)
10439: )
10440: IS
10441: CURSOR get_working_ver(c_option NUMBER) IS
10442: select a.element_version_id, c.object_id_from1
10443: from pa_proj_element_versions a,
10444: pa_proj_elem_ver_structure b,
10445: pa_object_relationships c
10446: where a.parent_structure_version_id = b.element_version_id
10447: and a.project_id = b.project_id
10452: and b.status_code <> 'STRUCTURE_PUBLISHED'
10453: and 1 = c_option
10454: UNION
10455: select a.element_version_id, c.object_id_from1
10456: from pa_proj_element_versions a,
10457: pa_proj_elem_ver_structure b,
10458: pa_object_relationships c
10459: where a.parent_structure_version_id = b.element_version_id
10460: and a.project_id = b.project_id
10546: ) IS
10547: CURSOR cur_task_hier( c_parent_structure_version_id NUMBER )
10548: IS
10549: SELECT proj_element_id, element_version_id
10550: FROM pa_proj_element_versions ppev,
10551: ( SELECT object_id_to1 from pa_object_relationships
10552: WHERE relationship_type = 'S'
10553: start with object_id_from1 = ( SELECT element_version_id
10554: FROM pa_proj_element_versions ppev
10550: FROM pa_proj_element_versions ppev,
10551: ( SELECT object_id_to1 from pa_object_relationships
10552: WHERE relationship_type = 'S'
10553: start with object_id_from1 = ( SELECT element_version_id
10554: FROM pa_proj_element_versions ppev
10555: WHERE ppev.proj_element_id = p_task_id
10556: AND ppev.parent_structure_version_id = c_parent_structure_version_id
10557: AND project_id = p_project_id
10558: AND object_type = 'PA_TASKS')
10570: AND status_code <> 'STRUCTURE_PUBLISHED';
10571:
10572: CURSOR cur_task_ver_ids( c_parent_structure_version_id NUMBER )
10573: IS
10574: SELECT element_version_id FROM pa_proj_element_versions WHERE parent_structure_version_id = c_parent_structure_version_id;
10575:
10576: TYPE task_id_rec_type IS RECORD
10577: (task_id NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
10578: task_version_id NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
10668: wrk_task_ids(j).task_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
10669: wrk_task_ids(j).new_task_flag = 'Y'
10670: THEN
10671: --Update version status for all those tasks that did not exist in the branch in latest pub ver.
10672: UPDATE pa_proj_element_versions
10673: SET TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED'
10674: WHERE element_version_id = wrk_task_ids(j).task_version_id
10675: ;
10676: END IF;
10903: END IF;
10904:
10905: IF p_event_type IN ( 'ADD', 'IDENT','MOVE_SUB' ) THEN
10906: IF ( p_event_type IN ('IDENT','MOVE_SUB' ) ) THEN
10907: SELECT -1 * ( PA_PROJ_ELEMENT_VERSIONS_S.nextval ) INTO t_seq FROM dual;
10908: ELSE
10909: t_seq := -1 * p_elem_ver_id;
10910: END IF;
10911: UPDATE pa_proj_element_versions a
10907: SELECT -1 * ( PA_PROJ_ELEMENT_VERSIONS_S.nextval ) INTO t_seq FROM dual;
10908: ELSE
10909: t_seq := -1 * p_elem_ver_id;
10910: END IF;
10911: UPDATE pa_proj_element_versions a
10912: SET a.display_sequence =
10913: (SELECT b.display_sequence
10914: FROM pa_proj_element_versions b
10915: WHERE b.element_version_id = p_ref_elem_ver_id
10910: END IF;
10911: UPDATE pa_proj_element_versions a
10912: SET a.display_sequence =
10913: (SELECT b.display_sequence
10914: FROM pa_proj_element_versions b
10915: WHERE b.element_version_id = p_ref_elem_ver_id
10916: ),
10917: a.level_sequence = t_seq
10918: WHERE a.element_version_id = p_elem_ver_id ;
10923: SELECT b.display_sequence ,
10924: b.level_sequence
10925: INTO t_disp_seq,
10926: t_level_seq
10927: FROM pa_proj_element_versions b
10928: WHERE b.element_version_id = p_ref_elem_ver_id ;
10929:
10930: UPDATE pa_proj_element_versions a
10931: SET a.display_sequence = t_disp_seq ,
10926: t_level_seq
10927: FROM pa_proj_element_versions b
10928: WHERE b.element_version_id = p_ref_elem_ver_id ;
10929:
10930: UPDATE pa_proj_element_versions a
10931: SET a.display_sequence = t_disp_seq ,
10932: a.level_sequence = t_level_seq
10933: WHERE a.element_version_id = p_elem_ver_id ;
10934:
10952: b.display_sequence ,
10953: b.level_sequence,
10954: DECODE( b.element_version_id, P_ELEM_VER_ID , 1 , 0 )
10955: FROM pa_tasks a,
10956: pa_proj_element_versions b
10957: WHERE a.task_id = b.proj_element_id
10958: AND b.parent_structure_version_id = p_parent_struct_ver_id
10959: AND b.display_sequence = t_disp_seq;
10960:
10960:
10961: t_seq := 0;
10962: FOR c1_rec IN c1
10963: LOOP
10964: UPDATE pa_proj_element_versions a
10965: SET a.level_sequence = t_seq
10966: WHERE a.element_version_id = c1_rec.element_version_id ;
10967: t_seq := t_seq + 1;
10968: END LOOP;
11028: por.object_id_from1 parent_elem_ver_id ,
11029: ppevs.duration ,
11030: ptt.prog_entry_enable_flag prog_flag ,
11031: ppev.object_type
11032: FROM pa_proj_element_versions ppev,
11033: pa_object_relationships por,
11034: pa_proj_elem_ver_schedule ppevs,
11035: pa_proj_elements ppe,
11036: pa_task_types ptt