[Home] [Help]
163: , 'N') -- Fix for Bug # 4319137.
164: <> FND_API.G_TRUE) THEN
165: IF p_element_id is NOT NULL THEN
166: IF (l_is_task_manager_flag = 'Y') THEN --bug 4239490 replaced the is_task_manager api call with the variable.
167: IF (PA_SECURITY_PVT.check_user_privilege('PA_TASKS_UPDATE_DETAILS', 'PA_PROJECTS', p_project_id
168: , 'N') -- Fix for Bug # 4319137.
169: <> FND_API.G_TRUE) THEN
170: -- Bug 4533152
171: IF p_add_error_flag = 'Y' THEN
224: FROM pa_proj_element_versions ppev,
225: pa_proj_elements ppe
226: WHERE ppe.proj_element_id = ppev.proj_element_id
227: AND ppev.element_version_id = p_element_version_id
228: AND ppev.object_type = 'PA_TASKS';
229:
230: /* Bug 2680486 -- Performance changes -- Added one more condition to compare project_id in the following cursor.
231: The project_id is got from pa_proj_element_versions*/
232:
655:
656: cursor child_exist IS
657: select 1
658: from pa_object_relationships
659: where object_type_from = 'PA_TASKS'
660: and object_id_from1 = p_task_version_id
661: and relationship_type = 'S';
662:
663: BEGIN
685: -- Parameters
686: -- p_element_name IN VARCHAR2
687: -- p_element_id IN NUMBER
688: -- p_project_id IN NUMBER
689: -- p_object_type IN VARCHAR2 := 'PA_TASKS'
690: --
691: -- History
692: --
693: -- 01-NOV-01 MAANSARI -Created
699: p_element_number IN VARCHAR2
700: ,p_element_id IN NUMBER
701: ,p_project_id IN NUMBER
702: ,p_structure_id IN NUMBER
703: ,p_object_type IN VARCHAR2 := 'PA_TASKS'
704: ) return VARCHAR2
705: IS
706: cursor c1 is
707: select 1 from pa_proj_elements
850:
851: CURSOR cur_proj_elem_ver
852: IS
853: SELECT ppev.proj_element_id
854: FROM pa_proj_element_versions ppev, pa_tasks pt
855: WHERE ppev.element_version_id = p_task_version_id
856: and ppev.proj_element_id = pt.task_id;
857:
858: -- Bug 3933576 : Added cursor cur_wp_proj_elem_ver and variable l_wp_proj_elem_id
917: --dbms_output.put_line( 'Before if 1 ');
918:
919: --hsiu: added for bug 2682805
920: -- Bug 3933576 : proj_element_id shd come from cur_wp_proj_elem_ver rather than cur_proj_elem_ver
921: -- because CI, EGO Items and progress check will happen, even if entry is not there in pa_tasks.
922:
923: -- OPEN cur_proj_elem_ver;
924: -- FETCH cur_proj_elem_ver INTO l_proj_element_id;
925: -- CLOSE cur_proj_elem_ver;
1015: x_error_message_code := 'PA_PS_TASK_HAS_PROG';
1016: raise FND_API.G_EXC_ERROR;
1017: END IF;
1018:
1019: IF pa_progress_utils.progress_record_exists( p_task_version_id, 'PA_TASKS'
1020: ,p_project_id) = 'Y' -- Fixed bug # 3688901.
1021: THEN
1022: x_return_status := FND_API.G_RET_STS_ERROR;
1023: x_error_message_code := 'PA_PS_CANT_DELETE_TASK_VER';
1111: IS
1112: SELECT parent_structure_version_id
1113: FROM pa_proj_element_versions
1114: WHERE element_version_id = p_task_version_id
1115: AND object_type = 'PA_TASKS';
1116:
1117: CURSOR cur_struc_type( x_structure_version_id NUMBER )
1118: IS
1119: SELECT 'Y'
1159: WHERE object_id_from1 = p_element_version_id
1160: --hsiu: bug 2800553: performance
1161: and rownum < 2
1162: --start with object_id_from1 = p_element_version_id
1163: AND object_type_from = 'PA_TASKS'
1164: AND relationship_type = 'S'
1165: And object_id_to1 NOT IN (
1166: select b.object_id_from1
1167: from pa_object_relationships a,
1182: FROM pa_object_relationships por,
1183: pa_proj_element_versions pev,
1184: pa_proj_elements pe
1185: WHERE por.object_id_from1 = p_element_version_id
1186: AND por.object_type_from ='PA_TASKS'
1187: AND por.relationship_type = 'S'
1188: AND por.object_id_to1=pev.element_version_id
1189: AND pe.proj_element_id=pev.proj_element_id
1190: AND NVL(pe.link_task_flag,'N') <> 'Y'
1422: IS
1423: SELECT rel.object_id_from1 parent_task_id, pev.wbs_number
1424: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1425: WHERE pev.element_version_id = c_task_id
1426: AND pev.object_type = 'PA_TASKS'
1427: AND rel.object_id_to1 = pev.element_version_id
1428: AND rel.relationship_type = 'S'
1429: AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
1430:
1425: WHERE pev.element_version_id = c_task_id
1426: AND pev.object_type = 'PA_TASKS'
1427: AND rel.object_id_to1 = pev.element_version_id
1428: AND rel.relationship_type = 'S'
1429: AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
1430:
1431: CURSOR UPDATE_MASKED_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER, c_mask VARCHAR2)
1432: IS
1433: SELECT element_version_id task_id, wbs_number, display_sequence
1432: IS
1433: SELECT element_version_id task_id, wbs_number, display_sequence
1434: FROM PA_PROJ_ELEMENT_VERSIONS
1435: WHERE parent_structure_version_id = c_parent_structure_ver_id
1436: AND object_type = 'PA_TASKS'
1437: AND abs(display_sequence) >= abs(c_display_seq)
1438: AND display_sequence <> c_display_seq
1439: -- AND substr(wbs_number, 1, length(c_mask)) = c_mask Commented for bug 3581030
1440: AND substr(wbs_number, 1, length(c_mask)+1) = c_mask||'.' -- Added for bug 3581030
1444: IS
1445: SELECT element_version_id task_id, wbs_number, display_sequence
1446: FROM PA_PROJ_ELEMENT_VERSIONS
1447: WHERE parent_structure_version_id = c_parent_structure_ver_id
1448: AND object_type = 'PA_TASKS'
1449: AND abs(display_sequence) >= abs(c_display_seq)
1450: AND display_sequence <> c_display_seq
1451: ORDER BY abs(display_sequence);
1452:
1454: IS
1455: SELECT element_version_id task_id
1456: FROM PA_PROJ_ELEMENT_VERSIONS
1457: WHERE parent_structure_version_id = c_parent_structure_ver_id
1458: AND object_type = 'PA_TASKS'
1459: AND display_sequence = c_display_seq;
1460:
1461: CURSOR GET_PREV_PEER_TASK_CSR(c_parent_structure_ver_id NUMBER, c_parent_task_id NUMBER, c_display_seq NUMBER)
1462: IS
1462: IS
1463: SELECT element_version_id task_id
1464: FROM PA_PROJ_ELEMENT_VERSIONS
1465: WHERE parent_structure_version_id = c_parent_structure_ver_id
1466: AND object_type = 'PA_TASKS'
1467: AND display_sequence =
1468: (SELECT max(pev.display_sequence)
1469: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1470: WHERE rel.object_type_from = 'PA_TASKS'
1466: AND object_type = 'PA_TASKS'
1467: AND display_sequence =
1468: (SELECT max(pev.display_sequence)
1469: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1470: WHERE rel.object_type_from = 'PA_TASKS'
1471: AND rel.object_id_from1 = c_parent_task_id
1472: AND rel.relationship_type = 'S'
1473: AND rel.object_type_to = 'PA_TASKS'
1474: AND rel.object_id_to1 = pev.element_version_id
1469: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1470: WHERE rel.object_type_from = 'PA_TASKS'
1471: AND rel.object_id_from1 = c_parent_task_id
1472: AND rel.relationship_type = 'S'
1473: AND rel.object_type_to = 'PA_TASKS'
1474: AND rel.object_id_to1 = pev.element_version_id
1475: AND pev.parent_structure_version_id = c_parent_structure_ver_id
1476: AND pev.display_sequence < c_display_seq);
1477:
1479: IS
1480: SELECT element_version_id task_id
1481: FROM PA_PROJ_ELEMENT_VERSIONS
1482: WHERE parent_structure_version_id = c_parent_structure_ver_id
1483: AND object_type = 'PA_TASKS'
1484: AND display_sequence =
1485: (SELECT max(pev.display_sequence)
1486: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1487: WHERE rel.object_type_from = 'PA_STRUCTURES'
1486: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1487: WHERE rel.object_type_from = 'PA_STRUCTURES'
1488: AND rel.object_id_from1 = c_parent_structure_ver_id
1489: AND rel.relationship_type = 'S'
1490: AND rel.object_type_to = 'PA_TASKS'
1491: AND rel.object_id_to1 = pev.element_version_id
1492: AND pev.parent_structure_version_id = c_parent_structure_ver_id
1493: AND display_sequence < c_display_seq);
1494:
1539: SELECT /*+ LEADING (rel) */ count(pev.element_version_id)
1540: INTO l_count
1541: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1542: WHERE pev.parent_structure_version_id = p_parent_structure_ver_id
1543: AND pev.object_type = 'PA_TASKS'
1544: AND abs(pev.display_sequence) <= abs(p_display_seq)
1545: AND rel.object_id_to1 = pev.element_version_id
1546: AND rel.relationship_type = 'S'
1547: AND rel.object_type_from = 'PA_STRUCTURES'
1545: AND rel.object_id_to1 = pev.element_version_id
1546: AND rel.relationship_type = 'S'
1547: AND rel.object_type_from = 'PA_STRUCTURES'
1548: AND rel.object_id_from1 = p_parent_structure_ver_id
1549: AND rel.object_type_to = 'PA_TASKS'; --Added for Bug 6430953
1550:
1551: l_wbs_number := l_count;
1552: l_mask := 'NONE';
1553: else
2378: RETURN VARCHAR2
2379: IS
2380: cursor c1 IS
2381: SELECT 1
2382: FROM PA_TASKS
2383: WHERE task_id = p_proj_element_id;
2384: l_dummy NUMBER;
2385: BEGIN
2386: OPEN c1;
2548: select 'xyz'
2549: from PA_PROJ_ELEMENTS
2550: where type_id = p_task_type_id
2551: AND project_id > -1
2552: AND object_type = 'PA_TASKS'
2553: );
2554:
2555: l_ret_val VARCHAR2(1);
2556: BEGIN
2659: l_element_version_id pa_proj_element_versions.element_version_id%TYPE;
2660:
2661: CURSOR c1(p_task_id IN NUMBER) IS
2662: SELECT project_id
2663: FROM pa_tasks
2664: WHERE task_id = p_task_id;
2665:
2666: v_c1 c1%ROWTYPE;
2667:
2718: IS
2719: CURSOR c1 IS
2720: select object_Id_to1
2721: from pa_object_relationships
2722: where object_type_to = 'PA_TASKS'
2723: and relationship_type = 'S'
2724: start with object_id_from1 = p_task_version_id
2725: and object_type_from = 'PA_TASKS'
2726: and relationship_type = 'S'
2721: from pa_object_relationships
2722: where object_type_to = 'PA_TASKS'
2723: and relationship_type = 'S'
2724: start with object_id_from1 = p_task_version_id
2725: and object_type_from = 'PA_TASKS'
2726: and relationship_type = 'S'
2727: connect by prior object_id_to1 = object_id_from1
2728: and prior object_type_to = object_type_from
2729: and relationship_type = prior relationship_type; --Bug 3792616
2949: AND PA_PROGRESS_UTILS.check_object_has_prog(
2950: p_project_id => l_project_id
2951: ,p_proj_element_id => l_proj_element_id
2952: ,p_object_id => l_proj_element_id
2953: ,p_object_type => 'PA_TASKS'
2954: ,p_structure_type => 'WORKPLAN'
2955: ) = 'Y'
2956: AND PA_PROGRESS_UTILS.check_ta_has_prog(
2957: p_project_id => l_project_id
3164: and c.status_code = 'STRUCTURE_PUBLISHED';
3165:
3166: CURSOR c2 IS
3167: select 1 from
3168: pa_tasks a
3169: where a.task_id = p_task_id;
3170:
3171: l_dummy NUMBER;
3172:
3304: select object_id_to1
3305: from pa_object_relationships
3306: where relationship_type = 'S'
3307: start with object_id_from1 = p_task_version_id
3308: and object_type_from = 'PA_TASKS'
3309: and relationship_type = 'S'
3310: connect by object_id_from1 = prior object_id_to1
3311: and object_type_from = prior object_type_to
3312: and relationship_type = prior relationship_type);
4242: /*cursor C1 is
4243: select object_id_from1
4244: from pa_object_relationships
4245: where relationship_type='S'
4246: and object_type_from='PA_TASKS'
4247: connect by prior object_id_from1 = object_id_to1
4248: and prior relationship_type = relationship_type
4249: start with object_id_to1 = p_element_version_id
4250: and relationship_type = 'S'
4255: select object_id_to1
4256: from pa_object_relationships
4257: where relationship_type='S'
4258: and object_type_from='PA_STRUCTURES'
4259: and object_type_to='PA_TASKS'
4260: connect by prior object_id_from1 = object_id_to1
4261: start with object_id_to1 = p_element_version_id
4262: and relationship_type = 'S';
4263:
4291: where element_version_id IN (
4292: /*select object_id_from1 --bug 4043647
4293: from pa_object_relationships
4294: where relationship_type='S'
4295: and object_type_from='PA_TASKS'
4296: connect by prior object_id_from1 = object_id_to1
4297: and prior relationship_type = relationship_type
4298: start with object_id_to1 = p_element_version_id
4299: and relationship_type = 'S'
4302: select object_id_to1
4303: from pa_object_relationships
4304: where relationship_type='S'
4305: and object_type_from='PA_STRUCTURES'
4306: and object_type_to='PA_TASKS'
4307: connect by prior object_id_from1 = object_id_to1
4308: start with object_id_to1 = p_element_version_id
4309: and relationship_type = 'S');
4310:
4427:
4428: Note that it is very much possible that (say) the case of Partially Shared Structures,
4429: Some of the tasks may be both workplan and financial tasks,whereas some tasks are only workplan tasks.
4430:
4431: In this case,There will not be any entry for those 'pure' workplan tasks in PA_TASKS table.
4432: Hence,for such passed Proj_element_id's this API will return 'X'
4433: */
4434: FUNCTION GET_TASK_LEVEL(p_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4435: p_proj_element_id PA_PROJ_ELEMENT_VERSIONS.PROJ_ELEMENT_ID%TYPE) RETURN VARCHAR2
4442:
4443: CURSOR c_task_exists
4444: IS
4445: select 1
4446: from pa_tasks
4447: where task_id = p_proj_element_id
4448: and project_id = p_project_id
4449: ;
4450:
4453: select 1
4454: from dual
4455: where exists
4456: (select 1
4457: from pa_tasks
4458: where nvl(parent_task_id,-9999) = p_proj_element_id
4459: and project_id = p_project_id
4460: ) ;
4461:
4460: ) ;
4461:
4462: CURSOR c_is_mid_task
4463: IS
4464: select 1 from pa_tasks
4465: where parent_task_id is not null
4466: and task_id = p_proj_element_id ;
4467:
4468: BEGIN
4472: CLOSE c_task_exists;
4473:
4474: IF nvl(l_dummy,0) = 0
4475: THEN
4476: return 'X' ; -- as the task doesnt exist in PA_TASKS table
4477: END IF;
4478:
4479: --At this point the task exists
4480:
4543: CURSOR c1 IS
4544: SELECT proj_element_id
4545: FROM pa_object_relationships, pa_proj_element_versions
4546: WHERE object_id_to1 = p_element_version_id
4547: AND object_type_from='PA_TASKS'
4548: AND object_id_from1 = element_version_id;
4549:
4550: l_parent_task_id NUMBER := NULL;
4551: begin
4568: CURSOR c1 IS
4569: SELECT object_id_from1
4570: FROM pa_object_relationships
4571: WHERE object_id_to1 = p_element_version_id
4572: AND object_type_from='PA_TASKS'
4573: AND relationship_type = 'S';
4574:
4575: begin
4576: OPEN c1;
4627: FROM pa_proj_element_versions ppev,
4628: pa_proj_elements ppe
4629: WHERE ppe.project_id = ppev.project_id
4630: AND ppe.proj_element_id = ppev.proj_element_id
4631: AND ppev.object_type = 'PA_TASKS'
4632: AND ppe.object_type = 'PA_TASKS'
4633: AND ppev.element_version_id IN (
4634: SELECT object_id_to1
4635: FROM pa_object_relationships
4628: pa_proj_elements ppe
4629: WHERE ppe.project_id = ppev.project_id
4630: AND ppe.proj_element_id = ppev.proj_element_id
4631: AND ppev.object_type = 'PA_TASKS'
4632: AND ppe.object_type = 'PA_TASKS'
4633: AND ppev.element_version_id IN (
4634: SELECT object_id_to1
4635: FROM pa_object_relationships
4636: WHERE relationship_type = 'S'
4634: SELECT object_id_to1
4635: FROM pa_object_relationships
4636: WHERE relationship_type = 'S'
4637: START WITH object_id_to1 = cp_task_version_id --24628
4638: AND object_type_to = 'PA_TASKS'
4639: and relationship_type = 'S'
4640: CONNECT BY PRIOR object_id_from1 = object_id_to1
4641: AND PRIOR object_type_from = object_type_to
4642: AND PRIOR relationship_type = relationship_type);
4672: FROM pa_proj_element_versions ppev,
4673: pa_proj_elements ppe
4674: WHERE ppe.project_id = ppev.project_id
4675: AND ppe.proj_element_id = ppev.proj_element_id
4676: AND ppev.object_type = 'PA_TASKS'
4677: AND ppe.object_type = 'PA_TASKS'
4678: AND ppev.element_version_id IN (
4679: SELECT object_id_to1
4680: FROM pa_object_relationships
4673: pa_proj_elements ppe
4674: WHERE ppe.project_id = ppev.project_id
4675: AND ppe.proj_element_id = ppev.proj_element_id
4676: AND ppev.object_type = 'PA_TASKS'
4677: AND ppe.object_type = 'PA_TASKS'
4678: AND ppev.element_version_id IN (
4679: SELECT object_id_to1
4680: FROM pa_object_relationships
4681: WHERE relationship_type = 'S'
4679: SELECT object_id_to1
4680: FROM pa_object_relationships
4681: WHERE relationship_type = 'S'
4682: START WITH object_id_to1 = cp_target_task_version_id
4683: AND object_type_to = 'PA_TASKS'
4684: and relationship_type = 'S'
4685: CONNECT BY PRIOR object_id_from1 = object_id_to1
4686: AND PRIOR object_type_from = object_type_to
4687: AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4691: FROM pa_proj_element_versions ppev,
4692: pa_proj_elements ppe
4693: WHERE ppe.project_id = ppev.project_id
4694: AND ppe.proj_element_id = ppev.proj_element_id
4695: AND ppev.object_type = 'PA_TASKS'
4696: AND ppe.object_type = 'PA_TASKS'
4697: AND ppev.element_version_id IN (
4698: SELECT object_id_to1
4699: FROM pa_object_relationships
4692: pa_proj_elements ppe
4693: WHERE ppe.project_id = ppev.project_id
4694: AND ppe.proj_element_id = ppev.proj_element_id
4695: AND ppev.object_type = 'PA_TASKS'
4696: AND ppe.object_type = 'PA_TASKS'
4697: AND ppev.element_version_id IN (
4698: SELECT object_id_to1
4699: FROM pa_object_relationships
4700: WHERE relationship_type = 'S'
4698: SELECT object_id_to1
4699: FROM pa_object_relationships
4700: WHERE relationship_type = 'S'
4701: START WITH object_id_to1 = cp_task_version_id
4702: AND object_type_to = 'PA_TASKS'
4703: and relationship_type = 'S'
4704: CONNECT BY object_id_from1 = prior object_id_to1
4705: AND object_type_from = prior object_type_to
4706: AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4761: FROM pa_proj_element_versions ppev,
4762: pa_proj_elements ppe
4763: WHERE ppe.project_id = ppev.project_id
4764: AND ppe.proj_element_id = ppev.proj_element_id
4765: AND ppev.object_type = 'PA_TASKS'
4766: AND ppe.object_type = 'PA_TASKS'
4767: AND ppev.element_version_id IN (
4768: SELECT object_id_to1
4769: FROM pa_object_relationships
4762: pa_proj_elements ppe
4763: WHERE ppe.project_id = ppev.project_id
4764: AND ppe.proj_element_id = ppev.proj_element_id
4765: AND ppev.object_type = 'PA_TASKS'
4766: AND ppe.object_type = 'PA_TASKS'
4767: AND ppev.element_version_id IN (
4768: SELECT object_id_to1
4769: FROM pa_object_relationships
4770: WHERE relationship_type = 'S'
4768: SELECT object_id_to1
4769: FROM pa_object_relationships
4770: WHERE relationship_type = 'S'
4771: START WITH object_id_to1 = cp_task_version_id --24628
4772: AND object_type_to = 'PA_TASKS'
4773: and relationship_type = 'S'
4774: CONNECT BY PRIOR object_id_from1 = object_id_to1
4775: AND PRIOR object_type_from = object_type_to
4776: AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4968: CURSOR c1 IS
4969: SELECT object_id_to1
4970: FROM pa_object_relationships
4971: WHERE object_id_from1 = p_task_version_id
4972: AND object_type_to='PA_TASKS'
4973: AND relationship_type = 'S';
4974: --
4975: begin
4976: OPEN c1;
4991: FROM pa_proj_element_versions ppev,
4992: pa_proj_elements ppe
4993: WHERE ppe.project_id = ppev.project_id
4994: AND ppe.proj_element_id = ppev.proj_element_id
4995: AND ppev.object_type = 'PA_TASKS'
4996: AND ppe.object_type = 'PA_TASKS'
4997: AND ppev.element_version_id IN (
4998: SELECT object_id_to1
4999: FROM pa_object_relationships
4992: pa_proj_elements ppe
4993: WHERE ppe.project_id = ppev.project_id
4994: AND ppe.proj_element_id = ppev.proj_element_id
4995: AND ppev.object_type = 'PA_TASKS'
4996: AND ppe.object_type = 'PA_TASKS'
4997: AND ppev.element_version_id IN (
4998: SELECT object_id_to1
4999: FROM pa_object_relationships
5000: WHERE relationship_type = 'S'
4998: SELECT object_id_to1
4999: FROM pa_object_relationships
5000: WHERE relationship_type = 'S'
5001: START WITH object_id_to1 = cp_target_task_version_id
5002: AND object_type_to = 'PA_TASKS'
5003: and relationship_type = 'S'
5004: CONNECT BY PRIOR object_id_from1 = object_id_to1
5005: AND PRIOR object_type_from = object_type_to
5006: AND PRIOR relationship_type = RELATIONSHIP_TYPE);
5010: FROM pa_proj_element_versions ppev,
5011: pa_proj_elements ppe
5012: WHERE ppe.project_id = ppev.project_id
5013: AND ppe.proj_element_id = ppev.proj_element_id
5014: AND ppev.object_type = 'PA_TASKS'
5015: AND ppe.object_type = 'PA_TASKS'
5016: AND ppev.element_version_id IN (
5017: SELECT object_id_to1
5018: FROM pa_object_relationships
5011: pa_proj_elements ppe
5012: WHERE ppe.project_id = ppev.project_id
5013: AND ppe.proj_element_id = ppev.proj_element_id
5014: AND ppev.object_type = 'PA_TASKS'
5015: AND ppe.object_type = 'PA_TASKS'
5016: AND ppev.element_version_id IN (
5017: SELECT object_id_to1
5018: FROM pa_object_relationships
5019: WHERE relationship_type = 'S'
5017: SELECT object_id_to1
5018: FROM pa_object_relationships
5019: WHERE relationship_type = 'S'
5020: START WITH object_id_to1 = cp_task_version_id
5021: AND object_type_to = 'PA_TASKS'
5022: and relationship_type = 'S'
5023: CONNECT BY object_id_from1 = prior object_id_to1
5024: AND object_type_from = prior object_type_to
5025: AND PRIOR relationship_type = RELATIONSHIP_TYPE);
5307:
5308:
5309: /*CURSOR c_tasks_in_hierarchy IS --Commented the following cursor as this is not needed after perf bug fix Bug#4964992
5310: SELECT TASK_ID
5311: FROM PA_TASKS
5312: CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
5313: AND PROJECT_ID = p_project_id
5314: START WITH TASK_ID = p_TASK_ID
5315: AND PROJECT_ID = p_project_id; */
5802: -- passed task_id.
5803:
5804: -- Note that START WITH - CONNECT BY CLAUSE cant be avoided as we want the task passed and all its children
5805: -- in the hierarchy
5806: -- This is known to cause FTS on pa_tasks . But,this cant be avoided.
5807:
5808: -- (ie) if the hierarchy is :
5809: -- 1
5810: -- 11
5835: CURSOR task_hierarchy IS
5836: SELECT
5837: task_rec(task_name,task_id)
5838: FROM
5839: pa_tasks
5840: CONNECT BY PRIOR
5841: task_id = parent_task_id AND
5842: project_id = p_project_id
5843: START WITH
5877: cursor child_exist IS
5878: select 1 from dual where exists(
5879: select 1
5880: from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elements ppe
5881: where por.object_type_from = 'PA_TASKS'
5882: and por.object_id_from1 = p_task_version_id
5883: and por.relationship_type = 'S'
5884: and por.object_id_to1 = ppev.element_version_id
5885: and ppe.PROJ_ELEMENT_ID = ppev.PROJ_ELEMENT_ID