[Home] [Help]
332: RAISE FND_API.G_EXC_ERROR;
333: END IF;
334:
335: --Insert into pa_proj_elements
336: select PA_TASKS_S.NEXTVAL into l_proj_element_id from sys.dual;
337:
338: PA_PROJ_ELEMENTS_PKG.insert_row(
339: X_ROW_ID => l_rowid
340: ,X_PROJ_ELEMENT_ID => l_proj_element_id
1836: and ppevs.element_version_id <> c_structure_version_id
1837: and ppevs.project_id = c_project_id
1838: and ppevs.status_code = 'STRUCTURE_WORKING'
1839: and por2.object_id_to1 = por.object_id_from1 -- Bug # 4556844.
1840: and por2.object_type_from in ('PA_STRUCTURES','PA_TASKS') --Bug 6429275
1841: and por2.object_type_to = 'PA_TASKS' -- Bug 6429275
1842: and por2.relationship_type = 'S'; -- Bug # 4556844.
1843:
1844:
1837: and ppevs.project_id = c_project_id
1838: and ppevs.status_code = 'STRUCTURE_WORKING'
1839: and por2.object_id_to1 = por.object_id_from1 -- Bug # 4556844.
1840: and por2.object_type_from in ('PA_STRUCTURES','PA_TASKS') --Bug 6429275
1841: and por2.object_type_to = 'PA_TASKS' -- Bug 6429275
1842: and por2.relationship_type = 'S'; -- Bug # 4556844.
1843:
1844:
1845: cur_rel_ids_rec cur_relationship_ids%rowtype;
3059: object_type_from, record_version_number
3060: from pa_object_relationships
3061: where relationship_type = 'L'
3062: and object_id_to1 = c_element_version_id
3063: and object_type_to IN ('PA_TASKS','PA_STRUCTURES');
3064: l_from_object_info get_from_id%ROWTYPE;
3065:
3066: cursor get_to_id(c_element_version_id NUMBER) IS
3067: select object_relationship_id, object_id_to1 object_id_to, object_id_to2,
3068: object_type_to, record_version_number
3069: from pa_object_relationships
3070: where relationship_type IN ('LW', 'LF')
3071: and object_id_from1 = c_element_version_id
3072: and object_type_from IN ('PA_TASKS','PA_STRUCTURES');
3073: l_to_object_info get_to_id%ROWTYPE;
3074: l_working_ver_fg VARCHAR2(1);
3075:
3076: cursor get_task_version_info(c_task_version_id NUMBER) IS
3126: a.project_id, b.object_id_from1 parent_element_version_id,
3127: a.TASK_UNPUB_VER_STATUS_CODE, a.parent_structure_version_id
3128: FROM PA_PROJ_ELEMENT_VERSIONS a,
3129: PA_OBJECT_RELATIONSHIPS b
3130: WHERE a.object_type = 'PA_TASKS'
3131: AND a.parent_structure_version_id = c_structure_version_id
3132: AND a.element_version_id = b.object_id_to1
3133: AND b.relationship_type = 'S'
3134: ORDER BY a.display_sequence;
3186: IS
3187: select weighting_percentage
3188: from pa_object_relationships
3189: where object_id_to1 = p_task_ver_id
3190: and object_type_to = 'PA_TASKS'
3191: and object_type_from in ('PA_STRUCTURES','PA_TASKS')
3192: and object_type_to = 'PA_TASKS' -- Bug 6429275
3193: and relationship_type = 'S';
3194: l_weighting_percentage NUMBER;
3187: select weighting_percentage
3188: from pa_object_relationships
3189: where object_id_to1 = p_task_ver_id
3190: and object_type_to = 'PA_TASKS'
3191: and object_type_from in ('PA_STRUCTURES','PA_TASKS')
3192: and object_type_to = 'PA_TASKS' -- Bug 6429275
3193: and relationship_type = 'S';
3194: l_weighting_percentage NUMBER;
3195:
3188: from pa_object_relationships
3189: where object_id_to1 = p_task_ver_id
3190: and object_type_to = 'PA_TASKS'
3191: and object_type_from in ('PA_STRUCTURES','PA_TASKS')
3192: and object_type_to = 'PA_TASKS' -- Bug 6429275
3193: and relationship_type = 'S';
3194: l_weighting_percentage NUMBER;
3195:
3196: --hsiu added for task status
3221: CURSOR get_parent_id(c_task_ver_id NUMBER) IS
3222: select object_id_from1
3223: from pa_object_relationships
3224: where object_id_to1 = c_task_ver_id
3225: and object_type_to = 'PA_TASKS'
3226: and relationship_type = 'S';
3227: l_parent_id NUMBER;
3228: l_parent_ver_id NUMBER;
3229: TYPE t_parentTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3232: --bug 2662139
3233: CURSOR is_summary_elem(c_elem_ver_id NUMBER) IS
3234: SELECT '1'
3235: from pa_object_relationships
3236: where object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
3237: and object_id_from1 = c_elem_ver_id
3238: and relationship_type = 'S';
3239:
3240: --bug
3264: cursor get_all_new_childs(c_new_struc_ver_id NUMBER) IS
3265: select element_version_id
3266: from pa_proj_element_versions
3267: where parent_structure_version_id = c_new_struc_ver_id
3268: and object_type = 'PA_TASKS';
3269: --end bug 3047602
3270:
3271: l_rowid VARCHAR2(255);
3272: CURSOR cur_elem_ver_seq IS
3743: -- CLOSE get_structure_version_info;
3744: -- --dbms_output.put_line('4b');
3745: --
3746: --
3747: -- ELSIF (l_from_object_info.object_type_from = 'PA_TASKS') THEN
3748: -- --get element information, then update
3749: -- --dbms_output.put_line('5');
3750: --dbms_output.put_line('open get_task_version_info');
3751: -- OPEN get_task_version_info(l_from_object_info.object_id_from);
3843: --dbms_output.put_line('close get_structure_version_info');
3844: -- CLOSE get_structure_version_info;
3845: -- --dbms_output.put_line('7b');
3846: --
3847: -- ELSIF(l_to_object_info.object_type_to = 'PA_TASKS') THEN
3848: -- --dbms_output.put_line('8');
3849: --dbms_output.put_line('open get_task_version_info');
3850: -- OPEN get_task_version_info(l_to_object_info.object_id_to);
3851: -- FETCH get_task_version_info INTO l_info_task_ver_rec;
3858: -- ,p_object_id_from2 => NULL
3859: -- ,p_object_id_from3 => NULL
3860: -- ,p_object_id_from4 => NULL
3861: -- ,p_object_id_from5 => NULL
3862: -- ,p_object_type_to => 'PA_TASKS'
3863: -- ,p_object_id_to1 => l_info_task_ver_rec.task_version_id
3864: -- ,p_object_id_to2 => NULL
3865: -- ,p_object_id_to3 => NULL
3866: -- ,p_object_id_to4 => NULL
3937: where a.element_version_id = b.object_id_from1
3938: and a.object_type = b.object_type_from
3939: and relationship_type = 'S'
3940: and object_id_to1 = l_task_versions_rec.element_version_id
3941: and object_type_to = 'PA_TASKS';
3942:
3943: --If it is financial task, check if this task should be created
3944:
3945: -- PA_PROJ_ELEMENTS_UTILS.Check_create_subtask_ok(
4254: --dbms_output.put_line('Incoming: From '||l_info_struc_ver_rec.structure_version_id||' To '||l_task_version_id);
4255: --dbms_output.put_line('close get_structure_version_info');
4256: -- CLOSE get_structure_version_info;
4257: --
4258: -- ELSIF (l_from_object_info.object_type_from = 'PA_TASKS') THEN
4259: --dbms_output.put_line('get_task_version_info');
4260: -- OPEN get_task_version_info(l_from_object_info.object_id_from);
4261: -- FETCH get_task_version_info INTO l_info_task_ver_rec;
4262: -- PA_RELATIONSHIP_PVT.Update_Relationship(
4317: --dbms_output.put_line(l_project_id||','||l_struc_ver_rec.proj_element_id||','||l_new_struct_ver_id||','||','||l_task_version_id||', TO: '
4318: --||l_info_struc_ver_rec.project_id||','||l_info_struc_ver_rec.structure_id||','||l_info_struc_ver_rec.structure_version_id);
4319: -- PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
4320: -- p_user_id => FND_GLOBAL.USER_ID
4321: -- ,p_object_type_from => 'PA_TASKS'
4322: -- ,p_object_id_from1 => l_task_version_id
4323: -- ,p_object_id_from2 => NULL
4324: -- ,p_object_id_from3 => NULL
4325: -- ,p_object_id_from4 => NULL
4344: /*****************************/
4345: --dbms_output.put_line('close get_structure_version_info');
4346: -- CLOSE get_structure_version_info;
4347:
4348: -- ELSIF (l_to_object_info.object_type_to = 'PA_TASKS') THEN
4349: --dbms_output.put_line('open get_task_version_info');
4350: -- OPEN get_task_version_info(l_to_object_info.object_id_to);
4351: -- FETCH get_task_version_info INTO l_info_task_ver_rec;
4352: /*****************************/
4352: /*****************************/
4353: --dbms_output.put_line('creating rel: task out going links for tasks');
4354: -- PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
4355: -- p_user_id => FND_GLOBAL.USER_ID
4356: -- ,p_object_type_from => 'PA_TASKS'
4357: -- ,p_object_id_from1 => l_task_version_id
4358: -- ,p_object_id_from2 => NULL
4359: -- ,p_object_id_from3 => NULL
4360: -- ,p_object_id_from4 => NULL
4358: -- ,p_object_id_from2 => NULL
4359: -- ,p_object_id_from3 => NULL
4360: -- ,p_object_id_from4 => NULL
4361: -- ,p_object_id_from5 => NULL
4362: -- ,p_object_type_to => 'PA_TASKS'
4363: -- ,p_object_id_to1 => l_info_task_ver_rec.task_version_id
4364: -- ,p_object_id_to2 => NULL
4365: -- ,p_object_id_to3 => NULL
4366: -- ,p_object_id_to4 => NULL
4755: FROM ( SELECT * from pa_proj_element_versions ppev2
4756: --,pa_proj_elements ppe --bug 4573340 commenting out this for bug 4578813
4757: WHERE --bug#3094283 ppev2.project_id = l_project_id
4758: ppev2.parent_structure_version_id = p_structure_version_id
4759: and ppev2.object_type = 'PA_TASKS'
4760: /*
4761: --bug 4573340
4762: and ppe.project_id = ppev2.project_id
4763: and ppe.proj_element_id = ppev2.proj_element_id
4772: /* --cant write order by directly.
4773: FROM pa_proj_element_versions ppev
4774: WHERE ppev.project_id = l_project_id
4775: and ppev.parent_structure_version_id = p_structure_version_id
4776: and ppev.object_type = 'PA_TASKS'
4777: and PA_PROJECT_STRUCTURE_PVT1.copy_task_version( p_structure_version_id,
4778: ppev.element_version_id ) = 'Y'
4779: order by ppev.display_sequence
4780: ;
4958: where ppev1.attribute15 = ppevs.element_version_id
4959: and ppevs.project_id = l_project_id
4960: and ppev1.project_id = l_project_id
4961: and ppev1.parent_structure_version_id = l_new_struct_ver_id
4962: and ppev1.object_type = 'PA_TASKS';
4963:
4964: ---------------------------------------------- FP_M changes: Begin
4965: -- Refer to tracking bug 3305199
4966: -- Populate the old and new task version ID in PL/SQL tables
4968: Select Element_Version_ID, ATTRIBUTE15 Bulk Collect
4969: INTO l_New_Task_Versions_Tab, l_Old_Task_Versions_Tab
4970: From pa_proj_element_versions
4971: Where parent_structure_version_id = l_new_struct_ver_id
4972: and object_type = 'PA_TASKS'
4973: and PA_PROJECT_STRUCTURE_PVT1.copy_task_version( l_new_struct_ver_id, element_version_id ) = 'Y'
4974: order by display_sequence;
4975:
4976: --bug 4019845
5984: object_type_from, record_version_number
5985: from pa_object_relationships
5986: where relationship_type = 'L'
5987: and object_id_to1 = c_element_version_id
5988: and object_type_to IN ('PA_TASKS','PA_STRUCTURES');
5989: l_from_object_info get_from_id%ROWTYPE;
5990:
5991: cursor get_task_version_info(c_task_version_id NUMBER) IS
5992: select v1.project_id project_id, v2.proj_element_id structure_id,
6064: IF p_orig_task_ver_id IS NULL THEN
6065: l_object_type := 'PA_STRUCTURES';
6066: l_orig_element_version_id := p_orig_struc_ver_id;
6067: ELSE
6068: l_object_type := 'PA_TASKS';
6069: l_orig_element_version_id := p_orig_task_ver_id;
6070: END IF;
6071:
6072: -- error_msg('in update latest pub links');
6090: END IF;
6091: l_latest_parent_struc_ver := l_latest_elem_ver;
6092: --dbms_output.put_line('b, Close get_latest_struc_ver');
6093: CLOSE get_latest_struc_ver;
6094: ELSIF (l_object_type = 'PA_TASKS') THEN
6095: --dbms_output.put_line('b, Open get_latest_task_ver');
6096: OPEN get_latest_task_ver(l_orig_element_version_id);
6097: FETCH get_latest_task_ver INTO l_latest_elem_ver,
6098: l_latest_parent_struc_ver;
6156: );
6157: --dbms_output.put_line('b, Close get_structure_version_info');
6158: CLOSE get_structure_version_info;
6159:
6160: ELSIF (l_from_object_info.object_type_from = 'PA_TASKS') THEN
6161: --dbms_output.put_line('b, Open get_task_version_info');
6162: OPEN get_task_version_info(l_from_object_info.object_id_from);
6163: FETCH get_task_version_info INTO l_info_task_ver_rec;
6164: PA_RELATIONSHIP_PVT.Update_Relationship(
6277: b.object_id_from1 parent_element_version_id,
6278: a.TASK_UNPUB_VER_STATUS_CODE
6279: FROM PA_PROJ_ELEMENT_VERSIONS a,
6280: PA_OBJECT_RELATIONSHIPS b
6281: WHERE a.object_type = 'PA_TASKS'
6282: AND a.parent_structure_version_id = c_structure_version_id
6283: AND a.element_version_id = b.object_id_to1
6284: AND b.relationship_type = 'S'
6285: AND b.object_type_from in ('PA_STRUCTURES','PA_TASKS') -- Bug 6429275
6281: WHERE a.object_type = 'PA_TASKS'
6282: AND a.parent_structure_version_id = c_structure_version_id
6283: AND a.element_version_id = b.object_id_to1
6284: AND b.relationship_type = 'S'
6285: AND b.object_type_from in ('PA_STRUCTURES','PA_TASKS') -- Bug 6429275
6286: AND b.object_type_to = 'PA_TASKS'
6287: ORDER BY a.display_sequence;
6288:
6289: l_task_versions_rec l_get_task_versions_csr%ROWTYPE;
6282: AND a.parent_structure_version_id = c_structure_version_id
6283: AND a.element_version_id = b.object_id_to1
6284: AND b.relationship_type = 'S'
6285: AND b.object_type_from in ('PA_STRUCTURES','PA_TASKS') -- Bug 6429275
6286: AND b.object_type_to = 'PA_TASKS'
6287: ORDER BY a.display_sequence;
6288:
6289: l_task_versions_rec l_get_task_versions_csr%ROWTYPE;
6290: l_ref_task_ver_id NUMBER;
6355: object_type_to, record_version_number
6356: from pa_object_relationships
6357: where relationship_type = 'L'
6358: and object_id_from1 = c_element_version_id
6359: and object_type_from IN ('PA_TASKS','PA_STRUCTURES');
6360: l_to_object_info get_to_id%ROWTYPE;
6361:
6362: cursor get_task_version_info(c_task_version_id NUMBER) IS
6363: select v1.project_id project_id, v2.proj_element_id structure_id,
6377: CURSOR get_cur_task_ver_weighting(c_ver_id NUMBER) IS
6378: select WEIGHTING_PERCENTAGE
6379: from pa_object_relationships
6380: where object_id_to1 = c_ver_id
6381: and object_type_to = 'PA_TASKS'
6382: and relationship_type = 'S';
6383: l_weighting NUMBER(17,2);
6384:
6385: --end task weighting changes
6527: IF (p_debug_mode = 'Y') THEN
6528: pa_debug.debug('Create Relationship return status: ' || l_return_status);
6529: pa_debug.debug('l_new_obj_rel_id: ' || l_new_obj_rel_id);
6530: END IF;
6531: ELSIF(l_to_object_info.object_type_to = 'PA_TASKS') THEN
6532: OPEN get_task_version_info(l_to_object_info.object_id_to);
6533: FETCH get_task_version_info INTO l_info_task_ver_rec;
6534:
6535: SELECT pst.structure_type_class_code
6907: FETCH l_get_structure_ver_csr INTO l_structure_ver_to_rec;
6908:
6909: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
6910: p_user_id => FND_GLOBAL.USER_ID
6911: ,p_object_type_from => 'PA_TASKS'
6912: ,p_object_id_from1 => l_task_version_id
6913: ,p_object_id_from2 => NULL
6914: ,p_object_id_from3 => NULL
6915: ,p_object_id_from4 => NULL
6960: */
6961: /* hsiu: bug 2800553: commented for performance improvement
6962: CLOSE l_get_structure_ver_csr;
6963:
6964: ELSIF (l_to_object_info.object_type_to = 'PA_TASKS') THEN
6965: OPEN get_task_version_info(l_to_object_info.object_id_to);
6966: FETCH get_task_version_info INTO l_info_task_ver_rec;
6967:
6968: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
6966: FETCH get_task_version_info INTO l_info_task_ver_rec;
6967:
6968: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
6969: p_user_id => FND_GLOBAL.USER_ID
6970: ,p_object_type_from => 'PA_TASKS'
6971: ,p_object_id_from1 => l_task_version_id
6972: ,p_object_id_from2 => NULL
6973: ,p_object_id_from3 => NULL
6974: ,p_object_id_from4 => NULL
6972: ,p_object_id_from2 => NULL
6973: ,p_object_id_from3 => NULL
6974: ,p_object_id_from4 => NULL
6975: ,p_object_id_from5 => NULL
6976: ,p_object_type_to => 'PA_TASKS'
6977: ,p_object_id_to1 => l_info_task_ver_rec.task_version_id
6978: ,p_object_id_to2 => NULL
6979: ,p_object_id_to3 => NULL
6980: ,p_object_id_to4 => NULL
7191: b.object_id_from1 parent_element_version_id
7192: FROM PA_PROJ_ELEMENT_VERSIONS a,
7193: PA_OBJECT_RELATIONSHIPS b,
7194: pa_proj_elements c
7195: WHERE a.object_type = 'PA_TASKS'
7196: AND a.parent_structure_version_id = c_structure_version_id
7197: AND a.element_version_id = b.object_id_to1
7198: AND b.relationship_type = 'S'
7199: and c.link_task_flag <> 'Y'
7206: IS
7207: select weighting_percentage
7208: from pa_object_relationships
7209: where object_id_to1 = p_task_ver_id
7210: and object_type_to = 'PA_TASKS'
7211: and relationship_type = 'S';
7212: l_weighting_percentage NUMBER;
7213:
7214: l_ref_task_ver_id NUMBER;
7240: CURSOR l_get_tasks_csr(c_project_id NUMBER, c_proj_element_id NUMBER)
7241: IS
7242: SELECT a.*, b.task_number PA_TASK_NUMBER
7243: FROM PA_PROJ_ELEMENTS a,
7244: PA_TASKS b
7245: WHERE a.object_type = 'PA_TASKS'
7246: AND a.project_id = c_project_id
7247: AND a.proj_element_id = c_proj_element_id
7248: AND a.LINK_TASK_FLAG <> 'Y'
7241: IS
7242: SELECT a.*, b.task_number PA_TASK_NUMBER
7243: FROM PA_PROJ_ELEMENTS a,
7244: PA_TASKS b
7245: WHERE a.object_type = 'PA_TASKS'
7246: AND a.project_id = c_project_id
7247: AND a.proj_element_id = c_proj_element_id
7248: AND a.LINK_TASK_FLAG <> 'Y'
7249: AND a.proj_element_id = b.task_id(+);
7247: AND a.proj_element_id = c_proj_element_id
7248: AND a.LINK_TASK_FLAG <> 'Y'
7249: AND a.proj_element_id = b.task_id(+);
7250:
7251: CURSOR l_get_pa_tasks_csr(c_project_id NUMBER, c_task_number VARCHAR2)
7252: IS
7253: SELECT task_id
7254: FROM PA_TASKS
7255: WHERE project_id = c_project_id
7250:
7251: CURSOR l_get_pa_tasks_csr(c_project_id NUMBER, c_task_number VARCHAR2)
7252: IS
7253: SELECT task_id
7254: FROM PA_TASKS
7255: WHERE project_id = c_project_id
7256: AND task_number = c_task_number;
7257:
7258: l_tasks_rec l_get_tasks_csr%ROWTYPE;
7263: --Bug No 3634334 Commented for performance reasons and rewritten the query.
7264: /*CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7265: IS
7266: select a.task_id
7267: from pa_tasks a, pa_tasks b, pa_proj_elements c
7268: where a.project_id = c_dest_project_id
7269: and b.project_id = c_src_project_id
7270: and a.task_number = b.task_number
7271: and b.task_id = c.proj_element_id
7272: and c.link_task_flag = 'Y';*/
7273:
7274: CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7275: IS
7276: select /*+ INDEX(a PA_TASKS_U2) */ a.task_id --Bug No 3634334
7277: from pa_tasks a, pa_tasks b, pa_proj_elements c
7278: where a.project_id = c_dest_project_id
7279: and b.project_id = c_src_project_id
7280: and a.task_number = b.task_number
7273:
7274: CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7275: IS
7276: select /*+ INDEX(a PA_TASKS_U2) */ a.task_id --Bug No 3634334
7277: from pa_tasks a, pa_tasks b, pa_proj_elements c
7278: where a.project_id = c_dest_project_id
7279: and b.project_id = c_src_project_id
7280: and a.task_number = b.task_number
7281: and c.project_id = c_src_project_id --Bug No 3634334
7279: and b.project_id = c_src_project_id
7280: and a.task_number = b.task_number
7281: and c.project_id = c_src_project_id --Bug No 3634334
7282: and b.task_id = c.proj_element_id
7283: and c.object_type = 'PA_TASKS' -- Bug No. 3968095
7284: and c.link_task_flag = 'Y';
7285:
7286: l_task_delete NUMBER;
7287: l_task_delete_rvn NUMBER;
7830:
7831: -- CREATE_TASK
7832: PA_TASK_PUB1.CREATE_TASK
7833: ( p_validate_only => FND_API.G_FALSE
7834: ,p_object_type => 'PA_TASKS'
7835: ,p_project_id => p_dest_project_id
7836: ,p_structure_id => l_structure_id
7837: ,p_task_number => l_tasks_rec.element_number
7838: ,p_task_name => l_tasks_rec.name
8050: -- If (l_is_workplan = 'Y') AND (l_is_billing = 'N') AND (l_is_costing = 'N') THEN
8051: l_task_id := NULL;
8052: IF (l_structure_type = 'WORKPLAN') THEN
8053: --get new id
8054: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
8055: ELSE
8056: --id exists in pa_tasks. Need to find matching id by using task_number
8057: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
8058: FETCH l_get_pa_tasks_csr into l_task_id;
8052: IF (l_structure_type = 'WORKPLAN') THEN
8053: --get new id
8054: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
8055: ELSE
8056: --id exists in pa_tasks. Need to find matching id by using task_number
8057: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
8058: FETCH l_get_pa_tasks_csr into l_task_id;
8059: CLOSE l_get_pa_tasks_csr;
8060: END IF;
8053: --get new id
8054: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
8055: ELSE
8056: --id exists in pa_tasks. Need to find matching id by using task_number
8057: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
8058: FETCH l_get_pa_tasks_csr into l_task_id;
8059: CLOSE l_get_pa_tasks_csr;
8060: END IF;
8061:
8054: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
8055: ELSE
8056: --id exists in pa_tasks. Need to find matching id by using task_number
8057: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
8058: FETCH l_get_pa_tasks_csr into l_task_id;
8059: CLOSE l_get_pa_tasks_csr;
8060: END IF;
8061:
8062: IF (p_debug_mode = 'Y') THEN
8055: ELSE
8056: --id exists in pa_tasks. Need to find matching id by using task_number
8057: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
8058: FETCH l_get_pa_tasks_csr into l_task_id;
8059: CLOSE l_get_pa_tasks_csr;
8060: END IF;
8061:
8062: IF (p_debug_mode = 'Y') THEN
8063: pa_debug.debug('inserting into task with id'||l_task_id);
8070: PA_PROJ_ELEMENTS_PKG.Insert_Row(
8071: X_ROW_ID => l_rowid
8072: ,X_PROJ_ELEMENT_ID => l_task_id
8073: ,X_PROJECT_ID => p_dest_project_id
8074: ,X_OBJECT_TYPE => 'PA_TASKS'
8075: ,X_ELEMENT_NUMBER => l_tasks_rec.ELEMENT_NUMBER
8076: ,X_NAME => l_tasks_rec.NAME
8077: ,X_DESCRIPTION => l_tasks_rec.DESCRIPTION
8078: ,X_STATUS_CODE => l_init_status_code
8279:
8280: END LOOP;
8281: CLOSE l_get_task_versions_csr;
8282:
8283: --Delete linking tasks in PA_TASKS if this is a financial structure
8284: IF (l_structure_type = 'FINANCIAL' or l_structure_type IS NULL) THEN
8285: OPEN l_linking_tasks_csr(p_src_project_id, p_dest_project_id);
8286: LOOP
8287: FETCH l_linking_tasks_csr into l_task_delete;
8288: EXIT WHEN l_linking_tasks_csr%NOTFOUND;
8289:
8290: select a.record_version_number, 0
8291: INTO l_task_delete_rvn, l_task_delete_wbs_rvn
8292: from pa_tasks a
8293: where a.task_id = l_task_delete;
8294:
8295: PA_TASKS_MAINT_PUB.DELETE_TASK(
8296: p_init_msg_list => FND_API.G_FALSE
8291: INTO l_task_delete_rvn, l_task_delete_wbs_rvn
8292: from pa_tasks a
8293: where a.task_id = l_task_delete;
8294:
8295: PA_TASKS_MAINT_PUB.DELETE_TASK(
8296: p_init_msg_list => FND_API.G_FALSE
8297: ,p_commit => FND_API.G_FALSE
8298: ,p_project_id => p_dest_project_id
8299: ,p_task_id => l_task_delete
8482: SCHEDULED_FINISH_DATE, ELEMENT_VERSION_ID, DURATION, WBS_NUMBER,
8483: OLD_LEVEL , DISPLAY_SEQUENCE, LEVEL WBS_LEVEL
8484: FROM
8485: (
8486: select /*+ ordered use_nl(b a t) index(t pa_tasks_n8) index(a PA_PROJ_ELEMENT_VERSIONS_N1) index(t PA_PROJ_ELEM_VER_SCHEDULE_U2) */
8487: /* t.project_id , t.task_id , t.parent_task_id , a.display_sequence ,
8488: a.level_sequence, a.parent_structure_version_id , B.PROJ_ELEMENT_ID,
8489: B.SCHEDULED_START_DATE, B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID, DURATION,
8490: A.WBS_NUMBER, A.WBS_LEVEL OLD_LEVEL
8488: a.level_sequence, a.parent_structure_version_id , B.PROJ_ELEMENT_ID,
8489: B.SCHEDULED_START_DATE, B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID, DURATION,
8490: A.WBS_NUMBER, A.WBS_LEVEL OLD_LEVEL
8491: from
8492: PA_TASKS T, PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B
8493: where
8494: T.PROJECT_ID = p_project_id AND A.PROJ_ELEMENT_ID = T.TASK_ID
8495: AND A.PROJECT_ID = T.PROJECT_ID --AND A.PARENT_STRUCTURE_VERSION_ID = :B1
8496: AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID
8580: delete PA_PROJ_ELEMENTS_TMP;
8581:
8582: INSERT INTO PA_PROJ_ELEMENTS_TMP(PROJECT_ID, TASK_ID, PARENT_TASK_ID, DISPLAY_SEQUENCE, LEVEL_SEQUENCE, PARENT_STRUCTURE_VERSION_ID,
8583: PROJ_ELEMENT_ID, SCHEDULED_START_DATE, SCHEDULED_FINISH_DATE, ELEMENT_VERSION_ID, DURATION, WBS_NUMBER, OLD_LEVEL, DEFER_CODE, TOP_TASK_ID) /* Modified insert statement for bug#14765516 */
8584: SELECT /*+ ordered use_nl(b a t) index(t pa_tasks_n8) index(a PA_PROJ_ELEMENT_VERSIONS_N1) index(t PA_PROJ_ELEM_VER_SCHEDULE_U2) */
8585: t.project_id , t.task_id , t.parent_task_id , a.display_sequence ,
8586: a.level_sequence, a.parent_structure_version_id , B.PROJ_ELEMENT_ID,
8587: B.SCHEDULED_START_DATE, B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID, DURATION,
8588: A.WBS_NUMBER, A.WBS_LEVEL OLD_LEVEL,a.defer_code, t.top_task_id
8586: a.level_sequence, a.parent_structure_version_id , B.PROJ_ELEMENT_ID,
8587: B.SCHEDULED_START_DATE, B.SCHEDULED_FINISH_DATE, A.ELEMENT_VERSION_ID, DURATION,
8588: A.WBS_NUMBER, A.WBS_LEVEL OLD_LEVEL,a.defer_code, t.top_task_id
8589: from
8590: PA_TASKS T, PA_PROJ_ELEMENT_VERSIONS A, PA_PROJ_ELEM_VER_SCHEDULE B
8591: where
8592: T.PROJECT_ID = p_project_id AND A.PROJ_ELEMENT_ID = T.TASK_ID
8593: AND A.PROJECT_ID = T.PROJECT_ID --AND A.PARENT_STRUCTURE_VERSION_ID = :B1
8594: AND A.ELEMENT_VERSION_ID = B.ELEMENT_VERSION_ID;
10012:
10013:
10014: cursor sel_task_dates(c_task_id NUMBER) IS
10015: select start_date, completion_date
10016: from pa_tasks
10017: where task_id = c_task_id;
10018:
10019: --hsiu added for bug 2634029
10020: cursor sel_target_dates IS
10703: -- l_completion_date := sysdate;
10704:
10705: --If it is lowest task, get dates
10706: --hsiu: commented for performance
10707: -- IF (l_object_type = 'PA_TASKS' AND
10708: -- PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(l_element_version_id) = 'Y') THEN
10709: -- OPEN sel_task_dates(l_task_id);
10710: -- FETCH sel_task_dates into l_start_date, l_completion_date;
10711: -- CLOSE sel_task_dates;
10776: 0,
10777: FND_GLOBAL.LOGIN_ID,
10778: PPEV.project_id,
10779: 'PA_PROJECTS'
10780: FROM PA_TASKS PT,
10781: PA_PROJ_ELEMENT_VERSIONS PPEV
10782: WHERE
10783: PPEV.parent_structure_version_id = l_structure_version_id
10784: AND PPEV.proj_element_id = PT.task_id (+);
11380: IS
11381: SELECT 'x' from pa_proj_element_versions
11382: WHERE project_id = c_project_id
11383: AND parent_structure_version_id = c_structure_version_id
11384: AND object_type = 'PA_TASKS'
11385: ;
11386: l_dummy VARCHAR2(1);
11387: --end bug 4263266
11388:
13073: , wbs_level
13074: FROM pa_proj_element_versions
13075: WHERE project_id = p_project_id
13076: AND parent_structure_version_id = p_structure_version_id
13077: AND object_type = 'PA_TASKS';
13078:
13079: CURSOR check_progress_allowed(c_element_version_id NUMBER) IS
13080: SELECT ptt.prog_entry_enable_flag
13081: FROM pa_task_types ptt
13082: , pa_proj_element_versions ppev
13083: , pa_proj_elements ppe
13084: WHERE ppev.element_version_id = c_element_version_id
13085: AND ppev.proj_element_id = ppe.proj_element_id
13086: AND ptt.object_type ='PA_TASKS' /* bug 3279978 FP M Enhancement */
13087: AND ppe.TYPE_ID = ptt.task_type_id;
13088:
13089: CURSOR get_parent(c_element_version_id NUMBER) IS
13090: SELECT object_id_from1
13089: CURSOR get_parent(c_element_version_id NUMBER) IS
13090: SELECT object_id_from1
13091: FROM pa_object_relationships
13092: WHERE object_id_to1 = c_element_version_id
13093: AND object_type_to = 'PA_TASKS'
13094: AND relationship_type = 'S'
13095: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
13096:
13097: CURSOR get_summed_duration(c_parent_element_version_id NUMBER) IS
13091: FROM pa_object_relationships
13092: WHERE object_id_to1 = c_element_version_id
13093: AND object_type_to = 'PA_TASKS'
13094: AND relationship_type = 'S'
13095: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
13096:
13097: CURSOR get_summed_duration(c_parent_element_version_id NUMBER) IS
13098: SELECT sum(ppevs.duration)
13099: FROM pa_proj_elem_ver_schedule ppevs
13101: , pa_proj_element_versions ppev
13102: , pa_proj_elements ppe
13103: , pa_task_types ptt
13104: WHERE por.object_id_from1 = c_parent_element_version_id
13105: AND por.object_type_to = 'PA_TASKS'
13106: AND por.relationship_type = 'S'
13107: AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13108: AND por.object_id_to1 = ppevs.element_version_id
13109: AND por.object_id_to1 = ppev.element_version_id
13103: , pa_task_types ptt
13104: WHERE por.object_id_from1 = c_parent_element_version_id
13105: AND por.object_type_to = 'PA_TASKS'
13106: AND por.relationship_type = 'S'
13107: AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13108: AND por.object_id_to1 = ppevs.element_version_id
13109: AND por.object_id_to1 = ppev.element_version_id
13110: AND ppev.proj_element_id = ppe.proj_element_id
13111: AND ppevs.project_id = ppe.project_id
13109: AND por.object_id_to1 = ppev.element_version_id
13110: AND ppev.proj_element_id = ppe.proj_element_id
13111: AND ppevs.project_id = ppe.project_id
13112: AND ppe.TYPE_ID = ptt.task_type_id
13113: AND ptt.object_type ='PA_TASKS' /* bug 3279978 FP M Enhancement */
13114: AND ptt.prog_entry_enable_flag = 'Y';
13115:
13116: CURSOR get_task_duration(c_element_version_id NUMBER, c_project_id NUMBER) IS
13117: SELECT duration
13122: CURSOR get_existing_weights(c_parent_element_version_id NUMBER) IS
13123: SELECT sum(weighting_percentage)
13124: FROM PA_OBJECT_RELATIONSHIPS
13125: WHERE object_id_from1 = c_parent_element_version_id
13126: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13127: AND object_type_to = 'PA_TASKS'
13128: AND relationship_type = 'S';
13129:
13130:
13123: SELECT sum(weighting_percentage)
13124: FROM PA_OBJECT_RELATIONSHIPS
13125: WHERE object_id_from1 = c_parent_element_version_id
13126: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13127: AND object_type_to = 'PA_TASKS'
13128: AND relationship_type = 'S';
13129:
13130:
13131: l_element_version_id NUMBER;
13164: UPDATE PA_OBJECT_RELATIONSHIPS
13165: SET weighting_percentage = 0
13166: WHERE object_id_from1 = l_parent_element_version_id
13167: AND object_id_to1 = l_element_version_id
13168: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13169: AND object_type_to = 'PA_TASKS'
13170: AND relationship_type = 'S';
13171: ELSE
13172:
13165: SET weighting_percentage = 0
13166: WHERE object_id_from1 = l_parent_element_version_id
13167: AND object_id_to1 = l_element_version_id
13168: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13169: AND object_type_to = 'PA_TASKS'
13170: AND relationship_type = 'S';
13171: ELSE
13172:
13173: IF l_durations.exists(l_parent_element_version_id) then
13205: UPDATE PA_OBJECT_RELATIONSHIPS
13206: SET weighting_percentage = l_task_weight
13207: WHERE object_id_from1 = l_parent_element_version_id
13208: AND object_id_to1 = l_element_version_id
13209: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13210: AND object_type_to = 'PA_TASKS'
13211: AND relationship_type = 'S';
13212: END IF;
13213: END LOOP;
13206: SET weighting_percentage = l_task_weight
13207: WHERE object_id_from1 = l_parent_element_version_id
13208: AND object_id_to1 = l_element_version_id
13209: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
13210: AND object_type_to = 'PA_TASKS'
13211: AND relationship_type = 'S';
13212: END IF;
13213: END LOOP;
13214:
13705: and ppe.project_id = ppev2.project_id
13706: and ppe.proj_element_id = ppev2.proj_element_id
13707: and ppe.link_task_flag = 'N'
13708: --bug 4573340
13709: and ppev2.object_type = 'PA_TASKS'
13710: order by ppev2.display_sequence ) ppev
13711: ;
13712: -- Bug 4205167 : Added hint to use Hash Join
13713: INSERT INTO PA_OBJECT_RELATIONSHIPS (
13887: where ppev1.attribute15 = ppevs.element_version_id
13888: and ppevs.project_id = l_project_id
13889: and ppev1.project_id = l_project_id
13890: and ppev1.parent_structure_version_id = l_new_struct_ver_id
13891: and ppev1.object_type = 'PA_TASKS';
13892:
13893: -----------------------------------------FP_M Changes : Begin
13894: -- Refer to tracking bug 3305199
13895: --
14379:
14380:
14381: cursor sel_task_dates(c_task_id NUMBER) IS
14382: select start_date, completion_date
14383: from pa_tasks
14384: where task_id = c_task_id;
14385:
14386: --hsiu added for bug 2634029
14387: cursor sel_target_dates IS
15486: 0,
15487: FND_GLOBAL.LOGIN_ID,
15488: PPEV.project_id,
15489: 'PA_PROJECTS'
15490: FROM PA_TASKS PT,
15491: PA_PROJ_ELEMENT_VERSIONS PPEV
15492: WHERE
15493: PPEV.parent_structure_version_id = l_structure_version_id
15494: AND PPEV.proj_element_id = PT.task_id (+);
16028: --set financial task flag to Y for all tasks
16029: update pa_proj_element_versions
16030: set financial_task_flag = 'Y'
16031: where parent_structure_version_id = l_structure_version_id
16032: and object_type = 'PA_TASKS'
16033: and proj_element_id NOT IN
16034: (select proj_element_id
16035: from pa_proj_elements
16036: where project_id = p_project_id
16033: and proj_element_id NOT IN
16034: (select proj_element_id
16035: from pa_proj_elements
16036: where project_id = p_project_id
16037: and object_type = 'PA_TASKS'
16038: and link_task_flag = 'Y');
16039: END IF;
16040:
16041: --need to call sync up API
16038: and link_task_flag = 'Y');
16039: END IF;
16040:
16041: --need to call sync up API
16042: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN
16043: (p_patask_record_version_number => NULL
16044: ,p_parent_task_version_id => NULL
16045: ,p_project_id => p_project_id
16046: ,p_syncup_all_tasks => 'Y'
16423: select element_version_id
16424: from pa_proj_element_versions
16425: where project_id = p_project_id --Bug No 3634334
16426: and parent_structure_version_id = l_keep_structure_ver_id
16427: and object_type IN ('PA_TASKS', 'PA_STRUCTURES'));
16428:
16429: --delete relationships
16430: --Bug No 3634334 Commented for performance tuning and rewritten the query.
16431: /* delete from pa_object_relationships rel
16444: select OBJECT_RELATIONSHIP_ID
16445: from pa_object_relationships rel,
16446: pa_proj_element_versions
16447: where rel.relationship_type IN ('D','S')
16448: and object_type_to = 'PA_TASKS'
16449: and rel.object_id_to1 = element_version_id
16450: and parent_structure_version_id = l_keep_structure_ver_id
16451: and financial_task_flag = 'N'
16452: UNION
16453: select OBJECT_RELATIONSHIP_ID
16454: from pa_object_relationships rel,
16455: pa_proj_element_versions
16456: where rel.relationship_type IN ('D','S')
16457: and object_type_from = 'PA_TASKS'
16458: and rel.object_id_from1 = element_version_id
16459: and parent_structure_version_id = l_keep_structure_ver_id
16460: and financial_task_flag = 'N');
16461:
16475: where proj_element_ID in (
16476: select proj_element_id
16477: from pa_proj_element_versions
16478: where parent_structure_version_id = l_keep_structure_ver_id
16479: and object_type = 'PA_TASKS'
16480: and financial_task_flag = 'N');
16481:
16482: --delete element versions
16483: Delete from pa_proj_element_versions
16481:
16482: --delete element versions
16483: Delete from pa_proj_element_versions
16484: where parent_structure_version_id = l_keep_structure_ver_id
16485: and object_type = 'PA_TASKS'
16486: and financial_task_flag = 'N';
16487:
16488: --delete from pa_proj_structure_types
16489: DELETe FROM pa_proj_structure_types
16724:
16725: update pa_proj_element_versions
16726: set financial_task_flag = 'Y'
16727: where parent_structure_version_id = l_structure_version_id
16728: and object_type = 'PA_TASKS'
16729: and proj_element_id NOT IN
16730: (select proj_element_id
16731: from pa_proj_elements
16732: where project_id = p_project_id
16729: and proj_element_id NOT IN
16730: (select proj_element_id
16731: from pa_proj_elements
16732: where project_id = p_project_id
16733: and object_type = 'PA_TASKS'
16734: and link_task_flag = 'Y');
16735:
16736: --need to call sync up API
16737: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN
16733: and object_type = 'PA_TASKS'
16734: and link_task_flag = 'Y');
16735:
16736: --need to call sync up API
16737: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN
16738: (p_patask_record_version_number => NULL
16739: ,p_parent_task_version_id => NULL
16740: ,p_project_id => p_project_id
16741: ,p_syncup_all_tasks => 'Y'
16958: select element_version_id
16959: from pa_proj_element_versions
16960: where project_id =p_project_id --Bug No 3634334
16961: and parent_structure_version_id = l_keep_structure_ver_id
16962: and object_type IN ('PA_TASKS', 'PA_STRUCTURES'));
16963:
16964: --delete from pa_proj_structure_types
16965: DELETe FROM pa_proj_structure_types
16966: where proj_element_id = l_structure_id
17475: 0,
17476: FND_GLOBAL.LOGIN_ID,
17477: PPEV.project_id,
17478: 'PA_PROJECTS'
17479: FROM PA_TASKS PT,
17480: PA_PROJ_ELEMENT_VERSIONS PPEV
17481: WHERE
17482: PPEV.parent_structure_version_id = l_structure_version_id
17483: AND PPEV.proj_element_id = PT.task_id (+);
17867: 0,
17868: FND_GLOBAL.LOGIN_ID,
17869: PPEV.project_id,
17870: 'PA_PROJECTS'
17871: FROM PA_TASKS PT,
17872: PA_PROJ_ELEMENT_VERSIONS PPEV
17873: WHERE
17874: PPEV.parent_structure_version_id = l_structure_version_id
17875: AND PPEV.proj_element_id = PT.task_id (+);