[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;
3029: object_type_from, record_version_number
3030: from pa_object_relationships
3031: where relationship_type = 'L'
3032: and object_id_to1 = c_element_version_id
3033: and object_type_to IN ('PA_TASKS','PA_STRUCTURES');
3034: l_from_object_info get_from_id%ROWTYPE;
3035:
3036: cursor get_to_id(c_element_version_id NUMBER) IS
3037: select object_relationship_id, object_id_to1 object_id_to, object_id_to2,
3038: object_type_to, record_version_number
3039: from pa_object_relationships
3040: where relationship_type IN ('LW', 'LF')
3041: and object_id_from1 = c_element_version_id
3042: and object_type_from IN ('PA_TASKS','PA_STRUCTURES');
3043: l_to_object_info get_to_id%ROWTYPE;
3044: l_working_ver_fg VARCHAR2(1);
3045:
3046: cursor get_task_version_info(c_task_version_id NUMBER) IS
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
3103: AND b.relationship_type = 'S'
3104: ORDER BY a.display_sequence;
3156: IS
3157: select weighting_percentage
3158: from pa_object_relationships
3159: where object_id_to1 = p_task_ver_id
3160: and object_type_to = 'PA_TASKS'
3161: and object_type_from in ('PA_STRUCTURES','PA_TASKS')
3162: and object_type_to = 'PA_TASKS' -- Bug 6429275
3163: and relationship_type = 'S';
3164: l_weighting_percentage NUMBER;
3157: select weighting_percentage
3158: from pa_object_relationships
3159: where object_id_to1 = p_task_ver_id
3160: and object_type_to = 'PA_TASKS'
3161: and object_type_from in ('PA_STRUCTURES','PA_TASKS')
3162: and object_type_to = 'PA_TASKS' -- Bug 6429275
3163: and relationship_type = 'S';
3164: l_weighting_percentage NUMBER;
3165:
3158: from pa_object_relationships
3159: where object_id_to1 = p_task_ver_id
3160: and object_type_to = 'PA_TASKS'
3161: and object_type_from in ('PA_STRUCTURES','PA_TASKS')
3162: and object_type_to = 'PA_TASKS' -- Bug 6429275
3163: and relationship_type = 'S';
3164: l_weighting_percentage NUMBER;
3165:
3166: --hsiu added for task status
3191: CURSOR get_parent_id(c_task_ver_id NUMBER) IS
3192: select object_id_from1
3193: from pa_object_relationships
3194: where object_id_to1 = c_task_ver_id
3195: and object_type_to = 'PA_TASKS'
3196: and relationship_type = 'S';
3197: l_parent_id NUMBER;
3198: l_parent_ver_id NUMBER;
3199: TYPE t_parentTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3202: --bug 2662139
3203: CURSOR is_summary_elem(c_elem_ver_id NUMBER) IS
3204: SELECT '1'
3205: from pa_object_relationships
3206: where object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
3207: and object_id_from1 = c_elem_ver_id
3208: and relationship_type = 'S';
3209:
3210: --bug
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:
3241: l_rowid VARCHAR2(255);
3242: CURSOR cur_elem_ver_seq IS
3683: -- CLOSE get_structure_version_info;
3684: -- --dbms_output.put_line('4b');
3685: --
3686: --
3687: -- ELSIF (l_from_object_info.object_type_from = 'PA_TASKS') THEN
3688: -- --get element information, then update
3689: -- --dbms_output.put_line('5');
3690: --dbms_output.put_line('open get_task_version_info');
3691: -- OPEN get_task_version_info(l_from_object_info.object_id_from);
3783: --dbms_output.put_line('close get_structure_version_info');
3784: -- CLOSE get_structure_version_info;
3785: -- --dbms_output.put_line('7b');
3786: --
3787: -- ELSIF(l_to_object_info.object_type_to = 'PA_TASKS') THEN
3788: -- --dbms_output.put_line('8');
3789: --dbms_output.put_line('open get_task_version_info');
3790: -- OPEN get_task_version_info(l_to_object_info.object_id_to);
3791: -- FETCH get_task_version_info INTO l_info_task_ver_rec;
3798: -- ,p_object_id_from2 => NULL
3799: -- ,p_object_id_from3 => NULL
3800: -- ,p_object_id_from4 => NULL
3801: -- ,p_object_id_from5 => NULL
3802: -- ,p_object_type_to => 'PA_TASKS'
3803: -- ,p_object_id_to1 => l_info_task_ver_rec.task_version_id
3804: -- ,p_object_id_to2 => NULL
3805: -- ,p_object_id_to3 => NULL
3806: -- ,p_object_id_to4 => NULL
3877: where a.element_version_id = b.object_id_from1
3878: and a.object_type = b.object_type_from
3879: and relationship_type = 'S'
3880: and object_id_to1 = l_task_versions_rec.element_version_id
3881: and object_type_to = 'PA_TASKS';
3882:
3883: --If it is financial task, check if this task should be created
3884:
3885: -- PA_PROJ_ELEMENTS_UTILS.Check_create_subtask_ok(
4194: --dbms_output.put_line('Incoming: From '||l_info_struc_ver_rec.structure_version_id||' To '||l_task_version_id);
4195: --dbms_output.put_line('close get_structure_version_info');
4196: -- CLOSE get_structure_version_info;
4197: --
4198: -- ELSIF (l_from_object_info.object_type_from = 'PA_TASKS') THEN
4199: --dbms_output.put_line('get_task_version_info');
4200: -- OPEN get_task_version_info(l_from_object_info.object_id_from);
4201: -- FETCH get_task_version_info INTO l_info_task_ver_rec;
4202: -- PA_RELATIONSHIP_PVT.Update_Relationship(
4257: --dbms_output.put_line(l_project_id||','||l_struc_ver_rec.proj_element_id||','||l_new_struct_ver_id||','||','||l_task_version_id||', TO: '
4258: --||l_info_struc_ver_rec.project_id||','||l_info_struc_ver_rec.structure_id||','||l_info_struc_ver_rec.structure_version_id);
4259: -- PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
4260: -- p_user_id => FND_GLOBAL.USER_ID
4261: -- ,p_object_type_from => 'PA_TASKS'
4262: -- ,p_object_id_from1 => l_task_version_id
4263: -- ,p_object_id_from2 => NULL
4264: -- ,p_object_id_from3 => NULL
4265: -- ,p_object_id_from4 => NULL
4284: /*****************************/
4285: --dbms_output.put_line('close get_structure_version_info');
4286: -- CLOSE get_structure_version_info;
4287:
4288: -- ELSIF (l_to_object_info.object_type_to = 'PA_TASKS') THEN
4289: --dbms_output.put_line('open get_task_version_info');
4290: -- OPEN get_task_version_info(l_to_object_info.object_id_to);
4291: -- FETCH get_task_version_info INTO l_info_task_ver_rec;
4292: /*****************************/
4292: /*****************************/
4293: --dbms_output.put_line('creating rel: task out going links for tasks');
4294: -- PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
4295: -- p_user_id => FND_GLOBAL.USER_ID
4296: -- ,p_object_type_from => 'PA_TASKS'
4297: -- ,p_object_id_from1 => l_task_version_id
4298: -- ,p_object_id_from2 => NULL
4299: -- ,p_object_id_from3 => NULL
4300: -- ,p_object_id_from4 => NULL
4298: -- ,p_object_id_from2 => NULL
4299: -- ,p_object_id_from3 => NULL
4300: -- ,p_object_id_from4 => NULL
4301: -- ,p_object_id_from5 => NULL
4302: -- ,p_object_type_to => 'PA_TASKS'
4303: -- ,p_object_id_to1 => l_info_task_ver_rec.task_version_id
4304: -- ,p_object_id_to2 => NULL
4305: -- ,p_object_id_to3 => NULL
4306: -- ,p_object_id_to4 => NULL
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'
4700: /*
4701: --bug 4573340
4702: and ppe.project_id = ppev2.project_id
4703: and ppe.proj_element_id = ppev2.proj_element_id
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,
4718: ppev.element_version_id ) = 'Y'
4719: order by ppev.display_sequence
4720: ;
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
4902: and ppev1.object_type = 'PA_TASKS';
4903:
4904: ---------------------------------------------- FP_M changes: Begin
4905: -- Refer to tracking bug 3305199
4906: -- Populate the old and new task version ID in PL/SQL tables
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;
4915:
4916: --bug 4019845
5924: object_type_from, record_version_number
5925: from pa_object_relationships
5926: where relationship_type = 'L'
5927: and object_id_to1 = c_element_version_id
5928: and object_type_to IN ('PA_TASKS','PA_STRUCTURES');
5929: l_from_object_info get_from_id%ROWTYPE;
5930:
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,
6004: IF p_orig_task_ver_id IS NULL THEN
6005: l_object_type := 'PA_STRUCTURES';
6006: l_orig_element_version_id := p_orig_struc_ver_id;
6007: ELSE
6008: l_object_type := 'PA_TASKS';
6009: l_orig_element_version_id := p_orig_task_ver_id;
6010: END IF;
6011:
6012: -- error_msg('in update latest pub links');
6030: END IF;
6031: l_latest_parent_struc_ver := l_latest_elem_ver;
6032: --dbms_output.put_line('b, Close get_latest_struc_ver');
6033: CLOSE get_latest_struc_ver;
6034: ELSIF (l_object_type = 'PA_TASKS') THEN
6035: --dbms_output.put_line('b, Open get_latest_task_ver');
6036: OPEN get_latest_task_ver(l_orig_element_version_id);
6037: FETCH get_latest_task_ver INTO l_latest_elem_ver,
6038: l_latest_parent_struc_ver;
6096: );
6097: --dbms_output.put_line('b, Close get_structure_version_info');
6098: CLOSE get_structure_version_info;
6099:
6100: ELSIF (l_from_object_info.object_type_from = 'PA_TASKS') THEN
6101: --dbms_output.put_line('b, Open get_task_version_info');
6102: OPEN get_task_version_info(l_from_object_info.object_id_from);
6103: FETCH get_task_version_info INTO l_info_task_ver_rec;
6104: PA_RELATIONSHIP_PVT.Update_Relationship(
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
6224: AND b.relationship_type = 'S'
6225: AND b.object_type_from in ('PA_STRUCTURES','PA_TASKS') -- Bug 6429275
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
6224: AND b.relationship_type = 'S'
6225: AND b.object_type_from in ('PA_STRUCTURES','PA_TASKS') -- Bug 6429275
6226: AND b.object_type_to = 'PA_TASKS'
6227: ORDER BY a.display_sequence;
6228:
6229: l_task_versions_rec l_get_task_versions_csr%ROWTYPE;
6222: AND a.parent_structure_version_id = c_structure_version_id
6223: AND a.element_version_id = b.object_id_to1
6224: AND b.relationship_type = 'S'
6225: AND b.object_type_from in ('PA_STRUCTURES','PA_TASKS') -- Bug 6429275
6226: AND b.object_type_to = 'PA_TASKS'
6227: ORDER BY a.display_sequence;
6228:
6229: l_task_versions_rec l_get_task_versions_csr%ROWTYPE;
6230: l_ref_task_ver_id NUMBER;
6295: object_type_to, record_version_number
6296: from pa_object_relationships
6297: where relationship_type = 'L'
6298: and object_id_from1 = c_element_version_id
6299: and object_type_from IN ('PA_TASKS','PA_STRUCTURES');
6300: l_to_object_info get_to_id%ROWTYPE;
6301:
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,
6317: CURSOR get_cur_task_ver_weighting(c_ver_id NUMBER) IS
6318: select WEIGHTING_PERCENTAGE
6319: from pa_object_relationships
6320: where object_id_to1 = c_ver_id
6321: and object_type_to = 'PA_TASKS'
6322: and relationship_type = 'S';
6323: l_weighting NUMBER(17,2);
6324:
6325: --end task weighting changes
6467: IF (p_debug_mode = 'Y') THEN
6468: pa_debug.debug('Create Relationship return status: ' || l_return_status);
6469: pa_debug.debug('l_new_obj_rel_id: ' || l_new_obj_rel_id);
6470: END IF;
6471: ELSIF(l_to_object_info.object_type_to = 'PA_TASKS') THEN
6472: OPEN get_task_version_info(l_to_object_info.object_id_to);
6473: FETCH get_task_version_info INTO l_info_task_ver_rec;
6474:
6475: SELECT pst.structure_type_class_code
6847: FETCH l_get_structure_ver_csr INTO l_structure_ver_to_rec;
6848:
6849: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
6850: p_user_id => FND_GLOBAL.USER_ID
6851: ,p_object_type_from => 'PA_TASKS'
6852: ,p_object_id_from1 => l_task_version_id
6853: ,p_object_id_from2 => NULL
6854: ,p_object_id_from3 => NULL
6855: ,p_object_id_from4 => NULL
6900: */
6901: /* hsiu: bug 2800553: commented for performance improvement
6902: CLOSE l_get_structure_ver_csr;
6903:
6904: ELSIF (l_to_object_info.object_type_to = 'PA_TASKS') THEN
6905: OPEN get_task_version_info(l_to_object_info.object_id_to);
6906: FETCH get_task_version_info INTO l_info_task_ver_rec;
6907:
6908: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
6906: FETCH get_task_version_info INTO l_info_task_ver_rec;
6907:
6908: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
6909: p_user_id => FND_GLOBAL.USER_ID
6910: ,p_object_type_from => 'PA_TASKS'
6911: ,p_object_id_from1 => l_task_version_id
6912: ,p_object_id_from2 => NULL
6913: ,p_object_id_from3 => NULL
6914: ,p_object_id_from4 => NULL
6912: ,p_object_id_from2 => NULL
6913: ,p_object_id_from3 => NULL
6914: ,p_object_id_from4 => NULL
6915: ,p_object_id_from5 => NULL
6916: ,p_object_type_to => 'PA_TASKS'
6917: ,p_object_id_to1 => l_info_task_ver_rec.task_version_id
6918: ,p_object_id_to2 => NULL
6919: ,p_object_id_to3 => NULL
6920: ,p_object_id_to4 => NULL
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
7137: AND a.element_version_id = b.object_id_to1
7138: AND b.relationship_type = 'S'
7139: and c.link_task_flag <> 'Y'
7146: IS
7147: select weighting_percentage
7148: from pa_object_relationships
7149: where object_id_to1 = p_task_ver_id
7150: and object_type_to = 'PA_TASKS'
7151: and relationship_type = 'S';
7152: l_weighting_percentage NUMBER;
7153:
7154: l_ref_task_ver_id NUMBER;
7180: CURSOR l_get_tasks_csr(c_project_id NUMBER, c_proj_element_id NUMBER)
7181: IS
7182: SELECT a.*, b.task_number PA_TASK_NUMBER
7183: FROM PA_PROJ_ELEMENTS a,
7184: PA_TASKS b
7185: WHERE a.object_type = 'PA_TASKS'
7186: AND a.project_id = c_project_id
7187: AND a.proj_element_id = c_proj_element_id
7188: AND a.LINK_TASK_FLAG <> 'Y'
7181: IS
7182: SELECT a.*, b.task_number PA_TASK_NUMBER
7183: FROM PA_PROJ_ELEMENTS a,
7184: PA_TASKS b
7185: WHERE a.object_type = 'PA_TASKS'
7186: AND a.project_id = c_project_id
7187: AND a.proj_element_id = c_proj_element_id
7188: AND a.LINK_TASK_FLAG <> 'Y'
7189: AND a.proj_element_id = b.task_id(+);
7187: AND a.proj_element_id = c_proj_element_id
7188: AND a.LINK_TASK_FLAG <> 'Y'
7189: AND a.proj_element_id = b.task_id(+);
7190:
7191: CURSOR l_get_pa_tasks_csr(c_project_id NUMBER, c_task_number VARCHAR2)
7192: IS
7193: SELECT task_id
7194: FROM PA_TASKS
7195: WHERE project_id = c_project_id
7190:
7191: CURSOR l_get_pa_tasks_csr(c_project_id NUMBER, c_task_number VARCHAR2)
7192: IS
7193: SELECT task_id
7194: FROM PA_TASKS
7195: WHERE project_id = c_project_id
7196: AND task_number = c_task_number;
7197:
7198: l_tasks_rec l_get_tasks_csr%ROWTYPE;
7203: --Bug No 3634334 Commented for performance reasons and rewritten the query.
7204: /*CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7205: IS
7206: select a.task_id
7207: from pa_tasks a, pa_tasks b, pa_proj_elements c
7208: where a.project_id = c_dest_project_id
7209: and b.project_id = c_src_project_id
7210: and a.task_number = b.task_number
7211: and b.task_id = c.proj_element_id
7212: and c.link_task_flag = 'Y';*/
7213:
7214: CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7215: IS
7216: select /*+ INDEX(a PA_TASKS_U2) */ a.task_id --Bug No 3634334
7217: from pa_tasks a, pa_tasks b, pa_proj_elements c
7218: where a.project_id = c_dest_project_id
7219: and b.project_id = c_src_project_id
7220: and a.task_number = b.task_number
7213:
7214: CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7215: IS
7216: select /*+ INDEX(a PA_TASKS_U2) */ a.task_id --Bug No 3634334
7217: from pa_tasks a, pa_tasks b, pa_proj_elements c
7218: where a.project_id = c_dest_project_id
7219: and b.project_id = c_src_project_id
7220: and a.task_number = b.task_number
7221: and c.project_id = c_src_project_id --Bug No 3634334
7219: and b.project_id = c_src_project_id
7220: and a.task_number = b.task_number
7221: and c.project_id = c_src_project_id --Bug No 3634334
7222: and b.task_id = c.proj_element_id
7223: and c.object_type = 'PA_TASKS' -- Bug No. 3968095
7224: and c.link_task_flag = 'Y';
7225:
7226: l_task_delete NUMBER;
7227: l_task_delete_rvn NUMBER;
7770:
7771: -- CREATE_TASK
7772: PA_TASK_PUB1.CREATE_TASK
7773: ( p_validate_only => FND_API.G_FALSE
7774: ,p_object_type => 'PA_TASKS'
7775: ,p_project_id => p_dest_project_id
7776: ,p_structure_id => l_structure_id
7777: ,p_task_number => l_tasks_rec.element_number
7778: ,p_task_name => l_tasks_rec.name
7990: -- If (l_is_workplan = 'Y') AND (l_is_billing = 'N') AND (l_is_costing = 'N') THEN
7991: l_task_id := NULL;
7992: IF (l_structure_type = 'WORKPLAN') THEN
7993: --get new id
7994: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
7995: ELSE
7996: --id exists in pa_tasks. Need to find matching id by using task_number
7997: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
7998: FETCH l_get_pa_tasks_csr into l_task_id;
7992: IF (l_structure_type = 'WORKPLAN') THEN
7993: --get new id
7994: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
7995: ELSE
7996: --id exists in pa_tasks. Need to find matching id by using task_number
7997: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
7998: FETCH l_get_pa_tasks_csr into l_task_id;
7999: CLOSE l_get_pa_tasks_csr;
8000: END IF;
7993: --get new id
7994: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
7995: ELSE
7996: --id exists in pa_tasks. Need to find matching id by using task_number
7997: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
7998: FETCH l_get_pa_tasks_csr into l_task_id;
7999: CLOSE l_get_pa_tasks_csr;
8000: END IF;
8001:
7994: select PA_TASKS_S.NEXTVAL into l_task_id from sys.dual;
7995: ELSE
7996: --id exists in pa_tasks. Need to find matching id by using task_number
7997: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
7998: FETCH l_get_pa_tasks_csr into l_task_id;
7999: CLOSE l_get_pa_tasks_csr;
8000: END IF;
8001:
8002: IF (p_debug_mode = 'Y') THEN
7995: ELSE
7996: --id exists in pa_tasks. Need to find matching id by using task_number
7997: OPEN l_get_pa_tasks_csr(p_dest_project_id, l_tasks_rec.PA_TASK_NUMBER);
7998: FETCH l_get_pa_tasks_csr into l_task_id;
7999: CLOSE l_get_pa_tasks_csr;
8000: END IF;
8001:
8002: IF (p_debug_mode = 'Y') THEN
8003: pa_debug.debug('inserting into task with id'||l_task_id);
8010: PA_PROJ_ELEMENTS_PKG.Insert_Row(
8011: X_ROW_ID => l_rowid
8012: ,X_PROJ_ELEMENT_ID => l_task_id
8013: ,X_PROJECT_ID => p_dest_project_id
8014: ,X_OBJECT_TYPE => 'PA_TASKS'
8015: ,X_ELEMENT_NUMBER => l_tasks_rec.ELEMENT_NUMBER
8016: ,X_NAME => l_tasks_rec.NAME
8017: ,X_DESCRIPTION => l_tasks_rec.DESCRIPTION
8018: ,X_STATUS_CODE => l_init_status_code
8219:
8220: END LOOP;
8221: CLOSE l_get_task_versions_csr;
8222:
8223: --Delete linking tasks in PA_TASKS if this is a financial structure
8224: IF (l_structure_type = 'FINANCIAL' or l_structure_type IS NULL) THEN
8225: OPEN l_linking_tasks_csr(p_src_project_id, p_dest_project_id);
8226: LOOP
8227: FETCH l_linking_tasks_csr into l_task_delete;
8228: EXIT WHEN l_linking_tasks_csr%NOTFOUND;
8229:
8230: select a.record_version_number, 0
8231: INTO l_task_delete_rvn, l_task_delete_wbs_rvn
8232: from pa_tasks a
8233: where a.task_id = l_task_delete;
8234:
8235: PA_TASKS_MAINT_PUB.DELETE_TASK(
8236: p_init_msg_list => FND_API.G_FALSE
8231: INTO l_task_delete_rvn, l_task_delete_wbs_rvn
8232: from pa_tasks a
8233: where a.task_id = l_task_delete;
8234:
8235: PA_TASKS_MAINT_PUB.DELETE_TASK(
8236: p_init_msg_list => FND_API.G_FALSE
8237: ,p_commit => FND_API.G_FALSE
8238: ,p_project_id => p_dest_project_id
8239: ,p_task_id => l_task_delete
9640:
9641:
9642: cursor sel_task_dates(c_task_id NUMBER) IS
9643: select start_date, completion_date
9644: from pa_tasks
9645: where task_id = c_task_id;
9646:
9647: --hsiu added for bug 2634029
9648: cursor sel_target_dates IS
10331: -- l_completion_date := sysdate;
10332:
10333: --If it is lowest task, get dates
10334: --hsiu: commented for performance
10335: -- IF (l_object_type = 'PA_TASKS' AND
10336: -- PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(l_element_version_id) = 'Y') THEN
10337: -- OPEN sel_task_dates(l_task_id);
10338: -- FETCH sel_task_dates into l_start_date, l_completion_date;
10339: -- CLOSE sel_task_dates;
10404: 0,
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 (+);
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: ;
11014: l_dummy VARCHAR2(1);
11015: --end bug 4263266
11016:
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:
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 */
12715: AND ppe.TYPE_ID = ptt.task_type_id;
12716:
12717: CURSOR get_parent(c_element_version_id NUMBER) IS
12718: SELECT object_id_from1
12717: CURSOR get_parent(c_element_version_id NUMBER) IS
12718: SELECT object_id_from1
12719: FROM pa_object_relationships
12720: WHERE object_id_to1 = c_element_version_id
12721: AND object_type_to = 'PA_TASKS'
12722: AND relationship_type = 'S'
12723: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
12724:
12725: CURSOR get_summed_duration(c_parent_element_version_id NUMBER) IS
12719: FROM pa_object_relationships
12720: WHERE object_id_to1 = c_element_version_id
12721: AND object_type_to = 'PA_TASKS'
12722: AND relationship_type = 'S'
12723: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
12724:
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
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'
12734: AND por.relationship_type = 'S'
12735: AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12736: AND por.object_id_to1 = ppevs.element_version_id
12737: AND por.object_id_to1 = ppev.element_version_id
12731: , pa_task_types ptt
12732: WHERE por.object_id_from1 = c_parent_element_version_id
12733: AND por.object_type_to = 'PA_TASKS'
12734: AND por.relationship_type = 'S'
12735: AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12736: AND por.object_id_to1 = ppevs.element_version_id
12737: AND por.object_id_to1 = ppev.element_version_id
12738: AND ppev.proj_element_id = ppe.proj_element_id
12739: AND ppevs.project_id = ppe.project_id
12737: AND por.object_id_to1 = ppev.element_version_id
12738: AND ppev.proj_element_id = ppe.proj_element_id
12739: AND ppevs.project_id = ppe.project_id
12740: AND ppe.TYPE_ID = ptt.task_type_id
12741: AND ptt.object_type ='PA_TASKS' /* bug 3279978 FP M Enhancement */
12742: AND ptt.prog_entry_enable_flag = 'Y';
12743:
12744: CURSOR get_task_duration(c_element_version_id NUMBER, c_project_id NUMBER) IS
12745: SELECT duration
12750: CURSOR get_existing_weights(c_parent_element_version_id NUMBER) IS
12751: SELECT sum(weighting_percentage)
12752: FROM PA_OBJECT_RELATIONSHIPS
12753: WHERE object_id_from1 = c_parent_element_version_id
12754: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12755: AND object_type_to = 'PA_TASKS'
12756: AND relationship_type = 'S';
12757:
12758:
12751: SELECT sum(weighting_percentage)
12752: FROM PA_OBJECT_RELATIONSHIPS
12753: WHERE object_id_from1 = c_parent_element_version_id
12754: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12755: AND object_type_to = 'PA_TASKS'
12756: AND relationship_type = 'S';
12757:
12758:
12759: l_element_version_id NUMBER;
12792: UPDATE PA_OBJECT_RELATIONSHIPS
12793: SET weighting_percentage = 0
12794: WHERE object_id_from1 = l_parent_element_version_id
12795: AND object_id_to1 = l_element_version_id
12796: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12797: AND object_type_to = 'PA_TASKS'
12798: AND relationship_type = 'S';
12799: ELSE
12800:
12793: SET weighting_percentage = 0
12794: WHERE object_id_from1 = l_parent_element_version_id
12795: AND object_id_to1 = l_element_version_id
12796: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12797: AND object_type_to = 'PA_TASKS'
12798: AND relationship_type = 'S';
12799: ELSE
12800:
12801: IF l_durations.exists(l_parent_element_version_id) then
12833: UPDATE PA_OBJECT_RELATIONSHIPS
12834: SET weighting_percentage = l_task_weight
12835: WHERE object_id_from1 = l_parent_element_version_id
12836: AND object_id_to1 = l_element_version_id
12837: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12838: AND object_type_to = 'PA_TASKS'
12839: AND relationship_type = 'S';
12840: END IF;
12841: END LOOP;
12834: SET weighting_percentage = l_task_weight
12835: WHERE object_id_from1 = l_parent_element_version_id
12836: AND object_id_to1 = l_element_version_id
12837: AND object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
12838: AND object_type_to = 'PA_TASKS'
12839: AND relationship_type = 'S';
12840: END IF;
12841: END LOOP;
12842:
13333: and ppe.project_id = ppev2.project_id
13334: and ppe.proj_element_id = ppev2.proj_element_id
13335: and ppe.link_task_flag = 'N'
13336: --bug 4573340
13337: and ppev2.object_type = 'PA_TASKS'
13338: order by ppev2.display_sequence ) ppev
13339: ;
13340: -- Bug 4205167 : Added hint to use Hash Join
13341: INSERT INTO PA_OBJECT_RELATIONSHIPS (
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
13519: and ppev1.object_type = 'PA_TASKS';
13520:
13521: -----------------------------------------FP_M Changes : Begin
13522: -- Refer to tracking bug 3305199
13523: --
14005:
14006:
14007: cursor sel_task_dates(c_task_id NUMBER) IS
14008: select start_date, completion_date
14009: from pa_tasks
14010: where task_id = c_task_id;
14011:
14012: --hsiu added for bug 2634029
14013: cursor sel_target_dates IS
15110: 0,
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 (+);
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
15658: (select proj_element_id
15659: from pa_proj_elements
15660: where project_id = p_project_id
15657: and proj_element_id NOT IN
15658: (select proj_element_id
15659: from pa_proj_elements
15660: where project_id = p_project_id
15661: and object_type = 'PA_TASKS'
15662: and link_task_flag = 'Y');
15663: END IF;
15664:
15665: --need to call sync up API
15662: and link_task_flag = 'Y');
15663: END IF;
15664:
15665: --need to call sync up API
15666: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN
15667: (p_patask_record_version_number => NULL
15668: ,p_parent_task_version_id => NULL
15669: ,p_project_id => p_project_id
15670: ,p_syncup_all_tasks => 'Y'
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:
16053: --delete relationships
16054: --Bug No 3634334 Commented for performance tuning and rewritten the query.
16055: /* delete from pa_object_relationships rel
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
16084: and financial_task_flag = 'N');
16085:
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:
16106: --delete element versions
16107: Delete from pa_proj_element_versions
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:
16112: --delete from pa_proj_structure_types
16113: DELETe FROM pa_proj_structure_types
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
16354: (select proj_element_id
16355: from pa_proj_elements
16356: where project_id = p_project_id
16353: and proj_element_id NOT IN
16354: (select proj_element_id
16355: from pa_proj_elements
16356: where project_id = p_project_id
16357: and object_type = 'PA_TASKS'
16358: and link_task_flag = 'Y');
16359:
16360: --need to call sync up API
16361: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN
16357: and object_type = 'PA_TASKS'
16358: and link_task_flag = 'Y');
16359:
16360: --need to call sync up API
16361: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN
16362: (p_patask_record_version_number => NULL
16363: ,p_parent_task_version_id => NULL
16364: ,p_project_id => p_project_id
16365: ,p_syncup_all_tasks => 'Y'
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:
16588: --delete from pa_proj_structure_types
16589: DELETe FROM pa_proj_structure_types
16590: where proj_element_id = l_structure_id
17099: 0,
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 (+);
17491: 0,
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 (+);