[Home] [Help]
220:
221: CURSOR cur_elem_ver
222: IS
223: SELECT ppe.name, ppe.element_number, ppev.parent_structure_version_id, ppe.project_id
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';
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:
233: CURSOR cur_elem_ver_stru( p_version_id NUMBER )
234: IS
235: SELECT ppevs.version_number, ppevs.name, ppe.element_number, ppe.name, ppe.proj_element_id, ppe.project_id
237: pa_proj_elements ppe
238: WHERE ppevs.element_version_id = p_version_id
239: AND ppe.proj_element_id = ppevs.proj_element_id
240: AND ppevs.project_id = (select project_id
241: from pa_proj_element_versions
242: where element_version_id = p_version_id) ;
243:
244: CURSOR cur_lookups
245: IS
378: c.pev_structure_id,
379: c.element_version_id,
380: c.name,
381: c.record_version_number
382: FROM pa_proj_element_versions b,
383: pa_proj_elem_ver_schedule a,
384: pa_proj_elem_ver_structure c,
385: pa_structure_types d ,
386: pa_proj_structure_types e
613: ) RETURN VARCHAR2 IS
614: CURSOR cur_obj_rel
615: IS
616: SELECT 'x'
617: FROM pa_object_relationships por, pa_proj_elements ppe, pa_proj_element_versions ppev
618: WHERE object_id_from1 = p_structure_version_id
619: AND relationship_type = 'S'
620: AND por.object_id_to1 = ppev.element_version_id
621: AND ppe.proj_element_id = ppev.proj_element_id
824: and f.user_id = p_user_id;
825:
826: cursor get_lock_user(p_person_id NUMBER) IS
827: select '1'
828: from pa_proj_element_versions v, pa_proj_elem_ver_structure s
829: where v.element_version_id = p_parent_structure_ver_id
830: and v.project_id = s.project_id
831: and v.element_version_id = s.element_version_id
832: and (locked_by_person_id IS NULL
841:
842: CURSOR cur_chk_last_ver
843: IS
844: SELECT 'x'
845: FROM pa_proj_element_versions
846: WHERE proj_element_id = ( SELECT proj_element_id
847: FROM pa_proj_element_versions
848: WHERE element_version_id = p_task_version_id )
849: AND element_version_id <> p_task_version_id;
843: IS
844: SELECT 'x'
845: FROM pa_proj_element_versions
846: WHERE proj_element_id = ( SELECT proj_element_id
847: FROM pa_proj_element_versions
848: WHERE element_version_id = p_task_version_id )
849: AND element_version_id <> p_task_version_id;
850:
851: CURSOR cur_proj_elem_ver
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
858: -- Bug 3933576 : Added cursor cur_wp_proj_elem_ver and variable l_wp_proj_elem_id
859: CURSOR cur_wp_proj_elem_ver
860: IS
861: SELECT ppev.proj_element_id
862: FROM pa_proj_element_versions ppev
863: WHERE ppev.element_version_id = p_task_version_id
864: ;
865:
866:
1109:
1110: CURSOR cur_proj_elem
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 )
1116:
1117: CURSOR cur_struc_type( x_structure_version_id NUMBER )
1118: IS
1119: SELECT 'Y'
1120: FROM pa_proj_element_versions ppev
1121: ,pa_proj_structure_types ppst
1122: ,pa_structure_types pst
1123: WHERE ppev.element_version_id = x_structure_version_id
1124: AND ppev.proj_element_id = ppst.proj_element_id
1181: FROM DUAL
1182: WHERE EXISTS
1183: (SELECT NULL
1184: FROM pa_object_relationships por,
1185: pa_proj_element_versions pev,
1186: pa_proj_elements pe
1187: WHERE por.object_id_from1 = p_element_version_id
1188: AND por.object_type_from ='PA_TASKS'
1189: AND por.relationship_type = 'S'
1205: FROM DUAL
1206: WHERE EXISTS
1207: (SELECT NULL
1208: FROM pa_object_relationships por,
1209: pa_proj_element_versions pev,
1210: pa_proj_elements pe
1211: WHERE por.object_id_from1 = p_element_version_id
1212: AND por.object_type_from ='PA_TASKS'
1213: AND por.relationship_type = 'S'
1397: IF (p_task_version_id IS NOT NULL) THEN
1398: IF (p_check_id_flag = 'Y') THEN
1399: select element_version_id
1400: into x_task_version_id
1401: from pa_proj_element_versions
1402: where element_version_id = p_task_version_id;
1403: ELSE
1404: x_task_version_id := p_task_version_id;
1405: END IF;
1405: END IF;
1406: ELSE
1407: select element_version_id
1408: into x_task_version_id
1409: from pa_proj_elements ppe, pa_proj_element_versions ppev
1410: where ppe.proj_element_id = ppev.proj_element_id
1411: AND ppe.name = p_task_name
1412: AND ppev.parent_structure_version_id = p_structure_version_id;
1413: null;
1464: IS
1465: CURSOR TASK_INFO_CSR(c_task_id NUMBER)
1466: IS
1467: SELECT rel.object_id_from1 parent_task_id, pev.wbs_number
1468: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1469: WHERE pev.element_version_id = c_task_id
1470: AND pev.object_type = 'PA_TASKS'
1471: AND rel.object_id_to1 = pev.element_version_id
1472: AND rel.relationship_type = 'S'
1474:
1475: CURSOR UPDATE_MASKED_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER, c_mask VARCHAR2)
1476: IS
1477: SELECT element_version_id task_id, wbs_number, display_sequence
1478: FROM PA_PROJ_ELEMENT_VERSIONS
1479: WHERE parent_structure_version_id = c_parent_structure_ver_id
1480: AND object_type = 'PA_TASKS'
1481: AND abs(display_sequence) >= abs(c_display_seq)
1482: AND display_sequence <> c_display_seq
1486:
1487: CURSOR UPDATE_TASKS_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1488: IS
1489: SELECT element_version_id task_id, wbs_number, display_sequence
1490: FROM PA_PROJ_ELEMENT_VERSIONS
1491: WHERE parent_structure_version_id = c_parent_structure_ver_id
1492: AND object_type = 'PA_TASKS'
1493: AND abs(display_sequence) >= abs(c_display_seq)
1494: AND display_sequence <> c_display_seq
1496:
1497: CURSOR GET_TASK_CSR (c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1498: IS
1499: SELECT element_version_id task_id
1500: FROM PA_PROJ_ELEMENT_VERSIONS
1501: WHERE parent_structure_version_id = c_parent_structure_ver_id
1502: AND object_type = 'PA_TASKS'
1503: AND display_sequence = c_display_seq;
1504:
1504:
1505: CURSOR GET_PREV_PEER_TASK_CSR(c_parent_structure_ver_id NUMBER, c_parent_task_id NUMBER, c_display_seq NUMBER)
1506: IS
1507: SELECT element_version_id task_id
1508: FROM PA_PROJ_ELEMENT_VERSIONS
1509: WHERE parent_structure_version_id = c_parent_structure_ver_id
1510: AND object_type = 'PA_TASKS'
1511: AND display_sequence =
1512: (SELECT max(pev.display_sequence)
1509: WHERE parent_structure_version_id = c_parent_structure_ver_id
1510: AND object_type = 'PA_TASKS'
1511: AND display_sequence =
1512: (SELECT max(pev.display_sequence)
1513: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1514: WHERE rel.object_type_from = 'PA_TASKS'
1515: AND rel.object_id_from1 = c_parent_task_id
1516: AND rel.relationship_type = 'S'
1517: AND rel.object_type_to = 'PA_TASKS'
1533:
1534: CURSOR GET_PREV_TOP_PEER_TASK_CSR(c_parent_structure_ver_id NUMBER, c_display_seq NUMBER)
1535: IS
1536: SELECT element_version_id task_id
1537: FROM PA_PROJ_ELEMENT_VERSIONS
1538: WHERE parent_structure_version_id = c_parent_structure_ver_id
1539: AND object_type = 'PA_TASKS'
1540: AND display_sequence =
1541: (SELECT max(pev.display_sequence)
1538: WHERE parent_structure_version_id = c_parent_structure_ver_id
1539: AND object_type = 'PA_TASKS'
1540: AND display_sequence =
1541: (SELECT max(pev.display_sequence)
1542: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1543: WHERE rel.object_type_from = 'PA_STRUCTURES'
1544: AND rel.object_id_from1 = c_parent_structure_ver_id
1545: AND rel.relationship_type = 'S'
1546: AND rel.object_type_to = 'PA_TASKS'
1597: -- Added the leading hint below for bug 3416314
1598: -- Smukka Merging branch 40 as of now with main branch
1599: SELECT /*+ LEADING (rel) */ count(pev.element_version_id)
1600: INTO l_count
1601: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1602: WHERE pev.parent_structure_version_id = p_parent_structure_ver_id
1603: AND pev.object_type = 'PA_TASKS'
1604: AND abs(pev.display_sequence) <= abs(p_display_seq)
1605: AND rel.object_id_to1 = pev.element_version_id
1663: end if;
1664: end if; -- ig(l_task_rec.parent_task_id = p_parent_structure_ver_id) then
1665:
1666: -- Update the WBS number for the inserted task
1667: UPDATE PA_PROJ_ELEMENT_VERSIONS
1668: SET wbs_number = l_wbs_number
1669: WHERE element_version_id = p_task_id;
1670:
1671: -- Loop through tasks that have a greater display seq than the current
1678: --Bug 13895419 start
1679: --Fetch parent_task_id and ref_parent_task_id for source task and target task respectively from db.
1680: begin
1681: SELECT rel.object_id_from1 INTO l_parent_task_id
1682: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1683: WHERE pev.element_version_id = p_task_version_id
1684: AND pev.object_type = 'PA_TASKS'
1685: AND rel.object_id_to1 = pev.element_version_id
1686: AND rel.relationship_type = 'S'
1686: AND rel.relationship_type = 'S'
1687: AND rel.object_type_from in ('PA_TASKS', 'PA_STRUCTURES');
1688:
1689: SELECT rel.object_id_from1 INTO l_ref_parent_task_id
1690: FROM PA_PROJ_ELEMENT_VERSIONS pev, PA_OBJECT_RELATIONSHIPS rel
1691: WHERE pev.element_version_id = p_ref_task_version_id
1692: AND pev.object_type = 'PA_TASKS'
1693: AND rel.object_id_to1 = pev.element_version_id
1694: AND rel.relationship_type = 'S'
1734:
1735: end if;
1736:
1737: -- Update the WBS number
1738: UPDATE PA_PROJ_ELEMENT_VERSIONS
1739: SET wbs_number = l_wbs_number
1740: WHERE element_version_id = l_update_task_rec.task_id;
1741: END LOOP;
1742: END if;
1784:
1785: --dbms_output.put_line('L_WBS_NUMBER: ' || l_wbs_number);
1786:
1787: -- Update the WBS number for the indented task
1788: UPDATE PA_PROJ_ELEMENT_VERSIONS
1789: SET wbs_number = l_wbs_number
1790: WHERE element_version_id = p_task_id;
1791:
1792: -- Find l_mask
1870:
1871: end if;
1872:
1873: -- Update the WBS number
1874: UPDATE PA_PROJ_ELEMENT_VERSIONS
1875: SET wbs_number = l_loop_wbs_number
1876: WHERE element_version_id = l_update_task_rec.task_id;
1877: END LOOP;
1878:
1939: l_mask2 := l_prev_task_rec.wbs_number;
1940: --dbms_output.put_line('L_MASK2: ' || l_mask2);
1941:
1942: -- Update the WBS number for the outdented task
1943: UPDATE PA_PROJ_ELEMENT_VERSIONS
1944: SET wbs_number = l_wbs_number
1945: WHERE element_version_id = p_task_id;
1946:
1947: --dbms_output.put_line('L_WBS_NUMBER: ' || l_wbs_number);
2052:
2053: end if;
2054:
2055: -- Update the WBS number
2056: UPDATE PA_PROJ_ELEMENT_VERSIONS
2057: SET wbs_number = l_loop_wbs_number
2058: WHERE element_version_id = l_update_task_rec.task_id;
2059: END LOOP;
2060:
2121: end if;
2122: end if;
2123:
2124: -- Update the WBS number
2125: UPDATE PA_PROJ_ELEMENT_VERSIONS
2126: SET wbs_number = l_wbs_number
2127: WHERE element_version_id = l_update_task_rec.task_id;
2128: END LOOP;
2129:
2164:
2165: CURSOR cur_ppev
2166: IS
2167: SELECT 'x'
2168: FROM pa_proj_element_versions
2169: WHERE element_version_id = p_task_version_id
2170: AND parent_structure_version_id = p_structure_version_id;
2171: l_dummy_char VARCHAR2(1);
2172: BEGIN
2249: p_task_version_id NUMBER)
2250: RETURN VARCHAR2 IS
2251: CURSOR c1 IS
2252: select '1'
2253: from pa_proj_element_versions
2254: where p_task_version_id = element_version_id
2255: and p_structure_version_id = parent_structure_version_id;
2256:
2257: l_dummy VARCHAR2(1);
2301:
2302: -- CURSOR c1 IS
2303: -- select 1
2304: -- from pa_percent_completes ppc,
2305: -- pa_proj_element_versions pev
2306: -- where pev.element_version_id = p_element_version_id
2307: -- and pev.project_id = ppc.project_id
2308: -- and pev.proj_element_id = ppc.task_id
2309: -- and ppc.submitted_flag = 'Y'
2329: IS
2330: CURSOR c1 IS
2331: select scheduled_start_date
2332: from pa_proj_elem_ver_schedule sch,
2333: pa_proj_element_versions ev
2334: where p_element_version_id = ev.element_version_id
2335: and ev.element_version_id = sch.element_version_id
2336: and ev.project_id = sch.project_id;
2337: l_date DATE;
2355: IS
2356: CURSOR c1 IS
2357: select scheduled_finish_date
2358: from pa_proj_elem_ver_schedule sch,
2359: pa_proj_element_versions ev
2360: where p_element_version_id = ev.element_version_id
2361: and ev.element_version_id = sch.element_version_id
2362: and ev.project_id = sch.project_id;
2363: l_date DATE;
2381: IS
2382: CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2383: select sch.scheduled_start_date
2384: from pa_proj_elem_ver_schedule sch,
2385: pa_proj_element_versions pev,
2386: pa_proj_element_versions pev2
2387: where pev.project_id = c_project_id
2388: and pev.parent_structure_version_id = c_structure_version_id
2389: and pev.element_version_id = sch.element_version_id
2382: CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2383: select sch.scheduled_start_date
2384: from pa_proj_elem_ver_schedule sch,
2385: pa_proj_element_versions pev,
2386: pa_proj_element_versions pev2
2387: where pev.project_id = c_project_id
2388: and pev.parent_structure_version_id = c_structure_version_id
2389: and pev.element_version_id = sch.element_version_id
2390: and pev.project_id = sch.project_id
2394:
2395: CURSOR c2 IS
2396: select str.project_id, str.element_version_id
2397: from pa_proj_elem_ver_structure str,
2398: pa_proj_element_versions pev
2399: where pev.element_version_id = p_parent_structure_version_id
2400: and pev.project_id = str.project_id
2401: and pev.proj_element_id = str.proj_element_id
2402: and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
2427: IS
2428: CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2429: select sch.scheduled_finish_date
2430: from pa_proj_elem_ver_schedule sch,
2431: pa_proj_element_versions pev,
2432: pa_proj_element_versions pev2
2433: where pev.project_id = c_project_id
2434: and pev.parent_structure_version_id = c_structure_version_id
2435: and pev.element_version_id = sch.element_version_id
2428: CURSOR c1(c_project_id NUMBER, c_structure_version_id NUMBER) IS
2429: select sch.scheduled_finish_date
2430: from pa_proj_elem_ver_schedule sch,
2431: pa_proj_element_versions pev,
2432: pa_proj_element_versions pev2
2433: where pev.project_id = c_project_id
2434: and pev.parent_structure_version_id = c_structure_version_id
2435: and pev.element_version_id = sch.element_version_id
2436: and pev.project_id = sch.project_id
2440:
2441: CURSOR c2 IS
2442: select str.project_id, str.element_version_id
2443: from pa_proj_elem_ver_structure str,
2444: pa_proj_element_versions pev
2445: where pev.element_version_id = p_parent_structure_version_id
2446: and pev.project_id = str.project_id
2447: and pev.proj_element_id = str.proj_element_id
2448: and str.LATEST_EFF_PUBLISHED_FLAG = 'Y';
2676: ) return NUMBER
2677: IS
2678: CURSOR c1(c_structure_version_id NUMBER) IS
2679: select ppev.element_version_id
2680: from pa_proj_element_versions ppev
2681: where parent_structure_version_id = c_structure_version_id
2682: and project_id = p_project_id
2683: and proj_element_id = p_task_id;
2684: l_structure_version_id NUMBER;
2748: p_task_id IN NUMBER
2749: ) RETURN NUMBER
2750: IS
2751:
2752: l_element_version_id pa_proj_element_versions.element_version_id%TYPE;
2753:
2754: CURSOR c1(p_task_id IN NUMBER) IS
2755: SELECT project_id
2756: FROM pa_tasks
2759: v_c1 c1%ROWTYPE;
2760:
2761: CURSOR c2 (p_proj_element_id IN NUMBER) IS
2762: SELECT element_version_id
2763: FROM pa_proj_element_versions
2764: WHERE proj_element_id = p_proj_element_id;
2765:
2766: v_c2 c2%ROWTYPE;
2767:
2766: v_c2 c2%ROWTYPE;
2767:
2768: CURSOR c3 (p_element_version_id IN NUMBER) IS
2769: SELECT display_sequence
2770: FROM pa_proj_element_versions
2771: WHERE element_version_id = p_element_version_id;
2772:
2773: v_c3 c3%ROWTYPE;
2774:
2907: CURSOR get_status IS
2908: select ppe.project_id, ppe.proj_element_id
2909: from pa_project_statuses pps,
2910: pa_proj_elements ppe,
2911: pa_proj_element_versions ppev
2912: where ppev.element_version_id = p_parent_task_ver_id
2913: and ppe.project_id = ppev.project_id
2914: and ppe.proj_element_id = ppev.proj_element_id
2915: and ppe.status_code = pps.project_status_code
2919: l_proj_element_id NUMBER;
2920:
2921: CURSOR get_task_info IS
2922: select ppev.project_id, ppev.proj_element_id
2923: from pa_proj_element_versions ppev
2924: where ppev.element_version_id = p_parent_task_ver_id;
2925:
2926: CURSOR get_schedule_info IS
2927: select 1
2925:
2926: CURSOR get_schedule_info IS
2927: select 1
2928: from pa_proj_elem_ver_schedule ppvsch,
2929: pa_proj_element_versions ppev
2930: where ppev.element_version_id = p_parent_task_ver_id
2931: and ppev.project_id = ppvsch.project_id
2932: and ppev.proj_element_id = ppvsch.proj_element_id
2933: and ppev.element_version_id = ppvsch.element_version_id
3162: IS
3163: CURSOR l_exist_non_top_task_csr IS
3164: select 'N'
3165: from pa_proj_elements ppe,
3166: pa_proj_element_versions ppev
3167: where ppe.proj_element_id = p_proj_element_id
3168: and ppe.proj_element_id = ppev.proj_element_id
3169: and nvl(ppev.wbs_level, 0) <> 1;
3170:
3245: IS
3246: CURSOR c1 is
3247: select 1 from
3248: pa_proj_elements a,
3249: pa_proj_element_versions b,
3250: pa_proj_elem_ver_structure c
3251: where a.proj_element_id = p_task_id
3252: and a.project_id = p_project_id
3253: and a.project_id = b.project_id
3302: CURSOR get_status IS
3303: select '1'
3304: from pa_project_statuses pps,
3305: pa_proj_elements ppe,
3306: pa_proj_element_versions ppev
3307: where ppev.element_version_id = p_task_ver_id
3308: and ppe.project_id = ppev.project_id
3309: and ppe.proj_element_id = ppev.proj_element_id
3310: and ppe.status_code = pps.project_status_code
3359: )
3360: IS
3361: CURSOR c1 IS
3362: select b.project_id, b.proj_element_id
3363: from pa_proj_element_versions a,
3364: pa_proj_elem_ver_structure b
3365: where a.element_version_id = p_task_version_id
3366: and a.parent_structure_version_id = b.element_version_id
3367: and a.project_id = b.project_id;
3371: l_versioned VARCHAR2(1);
3372:
3373: CURSOR c2(c_project_id NUMBER, c_structure_id NUMBER) IS
3374: select distinct ppe.proj_element_id
3375: from pa_proj_element_versions ppe
3376: where ppe.element_version_id IN (
3377: select object_id_to1
3378: from pa_object_relationships
3379: where relationship_type = 'S'
3378: from pa_object_relationships
3379: where relationship_type = 'S'
3380: start with object_id_from1 IN (
3381: select a.element_version_id
3382: from pa_proj_element_versions a,
3383: pa_proj_elem_ver_structure b
3384: where b.project_id = c_project_id
3385: and b.proj_element_id = c_structure_id
3386: and b.status_code <> 'STRUCTURE_PUBLISHED'
3391: and object_type_from = prior object_type_to
3392: and relationship_type = prior relationship_type)
3393: minus
3394: select ppe.proj_element_id
3395: from pa_proj_element_versions ppe
3396: where ppe.element_version_id IN (
3397: select object_id_to1
3398: from pa_object_relationships
3399: where relationship_type = 'S'
3405: and relationship_type = prior relationship_type);
3406:
3407: CURSOR c3(c_project_id NUMBER, c_structure_id NUMBER, c_xtra_task_id NUMBER) IS
3408: select ppev.element_version_id, ppev.TASK_UNPUB_VER_STATUS_CODE
3409: from pa_proj_element_Versions ppev,
3410: pa_proj_elem_ver_structure ppevs
3411: where ppev.proj_element_id = c_xtra_task_id
3412: and ppev.project_id = c_project_id
3413: and ppev.parent_structure_version_id = ppevs.element_version_id
3585: l_element_version_Id NUMBER;
3586:
3587: cursor c_previous_task_id( c_project_id NUMBER, c_struct_version_id NUMBER, c_display_seq NUMBER) IS
3588: select element_version_id
3589: from pa_proj_element_versions
3590: where project_id = c_project_id
3591: and parent_structure_version_id = c_struct_version_id
3592: and display_sequence = (
3593: select max(display_sequence)
3590: where project_id = c_project_id
3591: and parent_structure_version_id = c_struct_version_id
3592: and display_sequence = (
3593: select max(display_sequence)
3594: from pa_proj_element_versions
3595: where project_id = c_project_id
3596: and parent_structure_version_id = c_struct_version_id
3597: and display_sequence < c_display_seq
3598: );
3598: );
3599:
3600: cursor c_next_task_id( c_project_id NUMBER, c_struct_version_id NUMBER, c_display_seq NUMBER) IS
3601: select element_version_id
3602: from pa_proj_element_versions
3603: where project_id = c_project_id
3604: and parent_structure_version_id = c_struct_version_id
3605: and display_sequence = (
3606: select min(display_sequence)
3603: where project_id = c_project_id
3604: and parent_structure_version_id = c_struct_version_id
3605: and display_sequence = (
3606: select min(display_sequence)
3607: from pa_proj_element_versions
3608: where project_id = c_project_id
3609: and parent_structure_version_id = c_struct_version_id
3610: and display_sequence > c_display_seq
3611: );
3652: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
3653: ,p_structure_id IN pa_proj_elements.proj_element_id%TYPE
3654: ,p_is_wp_separate_from_fn IN VARCHAR2
3655: ,p_is_wp_versioning_enabled IN VARCHAR2
3656: ,x_structure_version_id OUT NOCOPY pa_proj_element_versions.element_version_id%TYPE -- 4537865
3657: ,x_task_unpub_ver_status_code OUT NOCOPY pa_proj_element_versions.task_unpub_ver_status_code%TYPE -- 4537865
3658: ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3659: ,x_msg_count OUT NOCOPY NUMBER -- 4537865
3660: ,x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
3653: ,p_structure_id IN pa_proj_elements.proj_element_id%TYPE
3654: ,p_is_wp_separate_from_fn IN VARCHAR2
3655: ,p_is_wp_versioning_enabled IN VARCHAR2
3656: ,x_structure_version_id OUT NOCOPY pa_proj_element_versions.element_version_id%TYPE -- 4537865
3657: ,x_task_unpub_ver_status_code OUT NOCOPY pa_proj_element_versions.task_unpub_ver_status_code%TYPE -- 4537865
3658: ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3659: ,x_msg_count OUT NOCOPY NUMBER -- 4537865
3660: ,x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
3661: AS
3664: -- Cursors used in this API.
3665: CURSOR cur_struc_ver_wp(c_project_id number,c_structure_type varchar2,c_status_code varchar2)
3666: IS
3667: SELECT c.element_version_id
3668: FROM pa_proj_element_versions c,
3669: pa_structure_types a,
3670: pa_proj_structure_types b
3671: ,pa_proj_elem_ver_structure d
3672: WHERE c.project_id = c_project_id
3679:
3680: CURSOR cur_struc_ver_fin(c_project_id number,c_structure_type varchar2)
3681: IS
3682: SELECT c.element_version_id
3683: FROM pa_proj_element_versions c,
3684: pa_structure_types a,
3685: pa_proj_structure_types b,
3686: pa_proj_elem_ver_structure d
3687: WHERE c.project_id = c_project_id
3891: -- Purpose : This procedure will check whether the task has transaction or not.This API will be
3892: -- : called from Set_Financial_task_API.
3893: -- Note : Check whether it is a financial task or workplan task.
3894: -- : Fetch the parent_structure_version_id for the passed proj_element_Id from
3895: -- : PA_PROJ_ELEMENT_VERSIONS and pass to API PA_PROJ_ELEMENTS_UTILS.structure_type
3896:
3897: -- Assumptions : Only called for Financial task
3898:
3899: -- Parameters Type Required Description and Purpose
3937: --This cursor will select the parent structure version id for the passed proj_elem_id, here it is used for task
3938: --CURSOR c_get_parent_str_ver_id (task_id NUMBER)
3939: --IS
3940: --SELECT PARENT_STRUCTURE_VERSION_ID
3941: --FROM PA_PROJ_ELEMENT_VERSIONS
3942: --WHERE PROJ_ELEMENT_ID = task_id;
3943:
3944: --Bug 3735089
3945: l_user_id NUMBER;
4358:
4359: /*
4360: intersect
4361: select a.object_id_to1
4362: from pa_object_relationships a, pa_proj_element_versions b
4363: where b.element_version_id = p_element_version_id
4364: and b.parent_structure_version_id = a.object_id_from1
4365: and a.relationship_type = 'S';
4366: */
4379:
4380: function GET_TOP_TASK_ID(p_element_version_id IN number) return number
4381: is
4382: cursor C1 (evid number) is
4383: select proj_element_id from pa_proj_element_versions
4384: where element_version_id IN (
4385: /*select object_id_from1 --bug 4043647
4386: from pa_object_relationships
4387: where relationship_type='S'
4404:
4405: /*
4406: intersect
4407: select a.object_id_to1
4408: from pa_object_relationships a, pa_proj_element_versions b
4409: where b.element_version_id = p_element_version_id
4410: and b.parent_structure_version_id = a.object_id_from1
4411: and a.relationship_type = 'S');
4412: */
4452:
4453: l_dummy NUMBER;
4454:
4455: CURSOR c3(evid NUMBER) IS
4456: select 1 from pa_proj_element_versions
4457: where element_version_id = evid
4458: and object_type = 'PA_STRUCTURES';
4459:
4460: begin
4511:
4512: Parameters Description Type Of Parameter
4513: ========== =============== ===================
4514: p_project_id The Project ID PA_PROJECTS_ALL.PROJECT_ID%TYPE
4515: p_proj_element_id The ProjElementID PA_PROJ_ELEMENT_VERSIONS.PROJ_ELEMENT_ID%TYPE
4516:
4517: Return Value of this function :
4518: ===============================
4519: 'T' -> Top Task , 'M' -> Middle Task , 'L' -> Lowest Task , 'X' -> Not Financial Task
4524: In this case,There will not be any entry for those 'pure' workplan tasks in PA_TASKS table.
4525: Hence,for such passed Proj_element_id's this API will return 'X'
4526: */
4527: FUNCTION GET_TASK_LEVEL(p_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4528: p_proj_element_id PA_PROJ_ELEMENT_VERSIONS.PROJ_ELEMENT_ID%TYPE) RETURN VARCHAR2
4529: IS
4530: l_task_level VARCHAR2(1) :='L';
4531:
4532: l_dummy NUMBER;
4634: function GET_PARENT_TASK_ID(p_element_version_id IN number) return number
4635: is
4636: CURSOR c1 IS
4637: SELECT proj_element_id
4638: FROM pa_object_relationships, pa_proj_element_versions
4639: WHERE object_id_to1 = p_element_version_id
4640: AND object_type_from='PA_TASKS'
4641: AND object_id_from1 = element_version_id
4642: AND relationship_type = 'S'; -- added for bug 16170622 to eliminate 'D' dependency records
4681: ,p_task_id IN NUMBER) return NUMBER
4682: is
4683: l_task_version_id NUMBER;
4684: begin
4685: select b.element_version_id into l_task_version_id from pa_proj_elements a, pa_proj_element_versions b
4686: where a.proj_element_id = b.proj_element_id
4687: and a.proj_element_id = p_task_id
4688: and b.parent_structure_version_id = p_structure_version_id;
4689:
4717: IS
4718: --
4719: CURSOR cur_check_deliv (cp_task_version_id number) IS
4720: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4721: FROM pa_proj_element_versions ppev,
4722: pa_proj_elements ppe
4723: WHERE ppe.project_id = ppev.project_id
4724: AND ppe.proj_element_id = ppev.proj_element_id
4725: AND ppev.object_type = 'PA_TASKS'
4762: RETURN VARCHAR2
4763: IS
4764: CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
4765: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4766: FROM pa_proj_element_versions ppev,
4767: pa_proj_elements ppe
4768: WHERE ppe.project_id = ppev.project_id
4769: AND ppe.proj_element_id = ppev.proj_element_id
4770: AND ppev.object_type = 'PA_TASKS'
4781: AND PRIOR RELATIONSHIP_TYPE = RELATIONSHIP_TYPE);
4782:
4783: CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
4784: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4785: FROM pa_proj_element_versions ppev,
4786: pa_proj_elements ppe
4787: WHERE ppe.project_id = ppev.project_id
4788: AND ppe.proj_element_id = ppev.proj_element_id
4789: AND ppev.object_type = 'PA_TASKS'
4838: IS
4839: /* This cursor get all the leaf nodes for a given structure*/
4840: CURSOR get_leaf_node_cur(cp_structure_elem_id NUMBER) IS
4841: SELECT object_id_to1
4842: FROM pa_proj_element_versions ppev,
4843: pa_object_relationships rel1
4844: WHERE ppev.parent_structure_version_id = cp_structure_elem_id --19671
4845: AND rel1.relationship_type = 'S'
4846: AND ppev.element_version_id = rel1.object_id_to1
4851:
4852: /* This cursor goes from leaf node to top of the branch*/
4853: CURSOR check_for_deliv_cur (cp_task_version_id number) IS
4854: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4855: FROM pa_proj_element_versions ppev,
4856: pa_proj_elements ppe
4857: WHERE ppe.project_id = ppev.project_id
4858: AND ppe.proj_element_id = ppev.proj_element_id
4859: AND ppev.object_type = 'PA_TASKS'
5081: RETURN NUMBER
5082: IS
5083: CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
5084: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5085: FROM pa_proj_element_versions ppev,
5086: pa_proj_elements ppe
5087: WHERE ppe.project_id = ppev.project_id
5088: AND ppe.proj_element_id = ppev.proj_element_id
5089: AND ppev.object_type = 'PA_TASKS'
5100: AND PRIOR relationship_type = RELATIONSHIP_TYPE);
5101:
5102: CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
5103: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5104: FROM pa_proj_element_versions ppev,
5105: pa_proj_elements ppe
5106: WHERE ppe.project_id = ppev.project_id
5107: AND ppe.proj_element_id = ppev.proj_element_id
5108: AND ppev.object_type = 'PA_TASKS'
5197: IS
5198: l_task_id NUMBER;
5199: begin
5200: select b.proj_element_id into l_task_id
5201: from pa_proj_element_versions b
5202: where b.element_version_id = p_task_version_id
5203: and b.project_id = p_project_id
5204: and b.parent_structure_version_id = p_structure_version_id;
5205:
5220: cursor cur_fin_task(c_project_id NUMBER
5221: , c_task_version_id NUMBER)
5222: is
5223: select ppev.financial_task_flag
5224: from pa_proj_element_versions ppev
5225: where ppev.project_id = c_project_id
5226: and ppev.element_version_id = c_task_version_id;
5227:
5228: l_financial_task_flag VARCHAR2(1) := null;
5232: , c_include_sub_proj_flag VARCHAR2) -- Fix for Bug # 4290042.
5233: is
5234: -- This query checks if the task version has a financial sub-task.
5235: select 'N'
5236: from pa_object_relationships por1, pa_proj_element_versions ppev1
5237: where por1.object_id_to1 = ppev1.element_version_id
5238: and por1.relationship_type = 'S'
5239: and ppev1.project_id = c_project_id
5240: and por1.object_id_from1 = c_task_version_id
5242: union all
5243: -- This query checks if the task version has a linking sub-task that has a financial link to
5244: -- a sub-project if the input p_include_sub_proj_flag = 'Y'.
5245: select 'N'
5246: from pa_object_relationships por2, pa_proj_element_versions ppev2
5247: where por2.object_id_to1 = ppev2.element_version_id
5248: and por2.relationship_type = 'S'
5249: and ppev2.project_id = c_project_id
5250: and por2.object_id_from1 = c_task_version_id
6138: l_dummy number;
6139: cursor child_exist IS
6140: select 1 from dual where exists(
6141: select 1
6142: from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elements ppe
6143: where por.object_type_from = 'PA_TASKS'
6144: and por.object_id_from1 = p_task_version_id
6145: and por.relationship_type = 'S'
6146: and por.object_id_to1 = ppev.element_version_id