[Home] [Help]
36: and f.user_id = p_user_id;
37:
38: cursor get_lock_user(p_person_id NUMBER) IS
39: select '1'
40: from pa_proj_element_versions v, pa_proj_elem_ver_structure s
41: where v.element_version_id = p_structure_version_id
42: and v.project_id = s.project_id
43: and v.element_version_id = s.element_version_id
44: and (locked_by_person_id IS NULL
405:
406: cursor c1 is
407: select '1'
408: from pa_proj_structure_types p, pa_proj_elements e,
409: pa_structure_types t, pa_proj_element_versions v
410: where v.element_version_id = p_structure_version_id
411: and v.proj_element_id = e.proj_element_id
412: and e.proj_element_id = p.proj_element_id
413: and p.structure_type_id = t.structure_type_id
463: IS
464: CURSOR get_linked_element_parent IS
465: select v1.project_id, v1.parent_structure_version_id
466: from pa_object_relationships r,
467: pa_proj_element_versions v1,
468: pa_proj_element_versions v2
469: where r.relationship_type = 'L'
470: and r.object_type_from IN ('PA_TASKS','PA_STRUCTURES')
471: and r.object_id_from1 = v2.element_version_id
464: CURSOR get_linked_element_parent IS
465: select v1.project_id, v1.parent_structure_version_id
466: from pa_object_relationships r,
467: pa_proj_element_versions v1,
468: pa_proj_element_versions v2
469: where r.relationship_type = 'L'
470: and r.object_type_from IN ('PA_TASKS','PA_STRUCTURES')
471: and r.object_id_from1 = v2.element_version_id
472: and r.object_id_to1 = v1.element_version_id
471: and r.object_id_from1 = v2.element_version_id
472: and r.object_id_to1 = v1.element_version_id
473: and v2.parent_structure_version_id = p_structure_version_id;
474:
475: l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
476: l_struc_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
477:
478:
479: cursor get_other_unpub_ver IS
472: and r.object_id_to1 = v1.element_version_id
473: and v2.parent_structure_version_id = p_structure_version_id;
474:
475: l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
476: l_struc_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
477:
478:
479: cursor get_other_unpub_ver IS
480: select b.element_version_id
477:
478:
479: cursor get_other_unpub_ver IS
480: select b.element_version_id
481: from pa_proj_element_versions a,
482: pa_proj_element_versions b,
483: pa_proj_elem_ver_structure c
484: where a.element_version_id = p_structure_version_id
485: and a.project_id = b.project_id
478:
479: cursor get_other_unpub_ver IS
480: select b.element_version_id
481: from pa_proj_element_versions a,
482: pa_proj_element_versions b,
483: pa_proj_elem_ver_structure c
484: where a.element_version_id = p_structure_version_id
485: and a.project_id = b.project_id
486: and a.proj_element_id = b.proj_element_id
603: l_dummy VARCHAR2(1);
604:
605: cursor getname is
606: select s.name, v.project_id, v.proj_element_id
607: from pa_proj_element_versions v, pa_proj_elem_ver_structure s
608: where v.element_version_id = p_structure_version_id and
609: s.project_id = v.project_id and
610: s.element_version_id = v.element_version_id;
611:
739: IF (p_structure_version_id IS NOT NULL) THEN
740: IF (p_check_id_flag = 'Y') THEN
741: select pev.element_version_id
742: into x_structure_version_id
743: from pa_proj_element_versions pev
744: -- pa_proj_elem_ver_structure pevs,
745: -- pa_proj_elements pe
746: where
747: -- pe.proj_element_id = p_structure_id
896: and sysdate between p.effective_start_date and p.effective_end_date;
897:
898: cursor get_lock_user IS
899: select s.locked_by_person_id
900: from pa_proj_element_versions v,
901: pa_proj_elem_ver_structure s
902: where v.element_version_id = p_structure_version_id
903: and v.project_id = s.project_id
904: and v.element_version_id = s.element_version_id;
961: -- N is not locked,
962: -- O is locked by other user.
963: -- Parameters
964: -- p_user_id fnd_user.user_id%TYPE
965: -- p_structure_version_id pa_proj_element_versions.element_version_id%TYPE
966: --
967: -- History
968: --
969: -- 20-may-03 mrajput -Created
970: -- Added For bug 2964237
971:
972:
973: function IS_STRUC_VER_LOCKED_BY_USER1(p_user_id fnd_user.user_id%TYPE,
974: p_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
975: return VARCHAR2
976: IS
977:
978: --Cursor to get the persion id from the user id passed to thia api
986:
987: --Cursor to get the locked by person id for a structure version
988: cursor get_lock_user IS
989: select s.locked_by_person_id
990: from pa_proj_element_versions v,
991: pa_proj_elem_ver_structure s
992: where v.element_version_id = p_structure_version_id
993: and v.project_id = s.project_id
994: and v.element_version_id = s.element_version_id;
1298: CURSOR c1 IS
1299: select min(a.scheduled_start_date)
1300: from pa_proj_elem_ver_schedule a,
1301: pa_object_relationships b,
1302: pa_proj_element_versions c
1303: where relationship_type IN ('S', 'L')
1304: and b.object_id_from1 = p_element_version_id
1305: and b.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
1306: and b.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
1311: CURSOR c2 IS
1312: select max(a.scheduled_finish_date)
1313: from pa_proj_elem_ver_schedule a,
1314: pa_object_relationships b,
1315: pa_proj_element_versions c
1316: where relationship_type IN ('S', 'L')
1317: and b.object_id_from1 = p_element_version_id
1318: and b.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
1319: and b.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
1324: CURSOR c3 IS
1325: select '1'
1326: from pa_object_relationships a,
1327: pa_proj_elements b,
1328: pa_proj_element_versions c
1329: where a.object_id_from1 = p_element_version_id
1330: and a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
1331: and a.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
1332: and a.object_id_to1 = c.element_version_id
1334: and c.project_id = b.project_id
1335: and b.link_task_flag = 'Y';
1336:
1337: --bug 3074706
1338: --add pa_proj_element_versions and add condition project_id to use index
1339: CURSOR c4 IS
1340: select max(a.last_update_date)
1341: from pa_proj_elem_ver_schedule a,
1342: pa_object_relationships b,
1339: CURSOR c4 IS
1340: select max(a.last_update_date)
1341: from pa_proj_elem_ver_schedule a,
1342: pa_object_relationships b,
1343: pa_proj_element_versions c
1344: where a.element_version_id = c.element_version_id
1345: and a.project_id = c.project_id
1346: and c.element_version_id = b.object_id_to1
1347: and b.object_type_to = 'PA_TASKS'
1414: IS
1415: CURSOR c1 IS
1416: /* Commented the following query for Performance Bug fix : 3968091
1417: select c.element_version_id
1418: from pa_proj_element_versions c,
1419: pa_proj_elements b,
1420: pa_proj_structure_types a,
1421: pa_structure_types d
1422: where d.structure_type_class_code = 'WORKPLAN'
1427: and b.project_id = c.project_id
1428: and c.object_type = 'PA_STRUCTURES';
1429: */
1430: select c.element_version_id /*New Query with improved Performance : 3968091 */
1431: from pa_proj_element_versions c,
1432: pa_proj_structure_types a,
1433: pa_structure_types d
1434: where d.structure_type_class_code = 'WORKPLAN'
1435: and d.structure_type_id = a.structure_type_id
1480: IS
1481: CURSOR c1 IS
1482: /*Commented the following Query for Performance Bug fix : 3968091
1483: select c.element_version_id
1484: from pa_proj_element_versions c,
1485: pa_proj_elements b,
1486: pa_proj_structure_types a,
1487: pa_structure_types d
1488: where d.structure_type_class_code = 'FINANCIAL'
1493: and b.project_id = c.project_id
1494: and c.object_type = 'PA_STRUCTURES';
1495: */
1496: select c.element_version_id /*New Query with improved Performance : 3968091 */
1497: from pa_proj_element_versions c,
1498: pa_proj_structure_types a,
1499: pa_structure_types d
1500: where d.structure_type_class_code = 'FINANCIAL'
1501: and d.structure_type_id = a.structure_type_id
1546: IS
1547: CURSOR cur_proj_elem_ver(c_element_version_id NUMBER)
1548: IS
1549: SELECT project_id, proj_element_id
1550: FROM pa_proj_element_versions
1551: WHERE element_version_id = c_element_version_id;
1552:
1553: CURSOR get_latest_pub_ver IS
1554: select b.element_version_id
1551: WHERE element_version_id = c_element_version_id;
1552:
1553: CURSOR get_latest_pub_ver IS
1554: select b.element_version_id
1555: from pa_proj_element_versions a,
1556: pa_proj_elem_ver_structure b
1557: where a.project_id = b.project_id
1558: and a.element_version_id = p_structure_version_id
1559: and a.proj_element_id = b.proj_element_id
1564: avoid Hash Join*/
1565:
1566: /* CURSOR get_missing_tasks(c_struc_ver_id NUMBER, c_latest_struc_ver_id NUMBER) IS
1567: select project_id, element_version_id
1568: from pa_proj_element_versions
1569: where parent_structure_version_id = c_latest_struc_ver_id
1570: and proj_element_id IN (
1571: select proj_element_id
1572: from pa_proj_element_versions
1568: from pa_proj_element_versions
1569: where parent_structure_version_id = c_latest_struc_ver_id
1570: and proj_element_id IN (
1571: select proj_element_id
1572: from pa_proj_element_versions
1573: where parent_structure_version_id = c_latest_struc_ver_id
1574: minus
1575: select proj_element_id
1576: from pa_proj_element_versions
1572: from pa_proj_element_versions
1573: where parent_structure_version_id = c_latest_struc_ver_id
1574: minus
1575: select proj_element_id
1576: from pa_proj_element_versions
1577: where parent_structure_version_id = c_struc_ver_id);
1578: */
1579: CURSOR get_missing_tasks(c_struc_ver_id NUMBER, c_latest_struc_ver_id NUMBER) IS
1580: select a.project_id, a.element_version_id
1577: where parent_structure_version_id = c_struc_ver_id);
1578: */
1579: CURSOR get_missing_tasks(c_struc_ver_id NUMBER, c_latest_struc_ver_id NUMBER) IS
1580: select a.project_id, a.element_version_id
1581: from pa_proj_element_versions a
1582: where a.parent_structure_version_id = c_latest_struc_ver_id
1583: and EXISTS (
1584: select b.proj_element_id
1585: from pa_proj_element_versions b
1581: from pa_proj_element_versions a
1582: where a.parent_structure_version_id = c_latest_struc_ver_id
1583: and EXISTS (
1584: select b.proj_element_id
1585: from pa_proj_element_versions b
1586: where b.parent_structure_version_id = c_latest_struc_ver_id
1587: and b.proj_element_id = a.proj_element_id
1588: minus
1589: select c.proj_element_id
1586: where b.parent_structure_version_id = c_latest_struc_ver_id
1587: and b.proj_element_id = a.proj_element_id
1588: minus
1589: select c.proj_element_id
1590: from pa_proj_element_versions c
1591: where c.parent_structure_version_id = c_struc_ver_id
1592: and c.proj_element_id = a.proj_element_id);
1593:
1594: CURSOR get_task_name_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
1971: where project_id = p_project_id
1972: and object_type = 'PA_TASKS'
1973: and proj_element_id IN (
1974: select distinct ppev1.proj_element_id
1975: from pa_proj_element_versions ppev1,
1976: pa_proj_element_versions ppev2
1977: where ppev2.object_type = 'PA_STRUCTURES'
1978: and ppev2.project_id = p_project_id
1979: and ppev2.proj_element_id = l_structure_id
1972: and object_type = 'PA_TASKS'
1973: and proj_element_id IN (
1974: select distinct ppev1.proj_element_id
1975: from pa_proj_element_versions ppev1,
1976: pa_proj_element_versions ppev2
1977: where ppev2.object_type = 'PA_STRUCTURES'
1978: and ppev2.project_id = p_project_id
1979: and ppev2.proj_element_id = l_structure_id
1980: and ppev1.parent_structure_version_id = ppev2.element_version_id);*/
1980: and ppev1.parent_structure_version_id = ppev2.element_version_id);*/
1981:
1982: CURSOR get_tasks IS
1983: select distinct ppev1.proj_element_id
1984: from pa_proj_element_versions ppev1,
1985: pa_proj_element_versions ppev2
1986: where ppev2.object_type = 'PA_STRUCTURES'
1987: and ppev2.project_id = p_project_id
1988: and ppev2.proj_element_id = l_structure_id
1981:
1982: CURSOR get_tasks IS
1983: select distinct ppev1.proj_element_id
1984: from pa_proj_element_versions ppev1,
1985: pa_proj_element_versions ppev2
1986: where ppev2.object_type = 'PA_STRUCTURES'
1987: and ppev2.project_id = p_project_id
1988: and ppev2.proj_element_id = l_structure_id
1989: and ppev1.object_type = 'PA_TASKS'
2286: and str.CURRENT_WORKING_FLAG = 'Y';
2287: /*
2288: CURSOR c1 IS
2289: SELECT MAX(a.last_update_date), b.parent_structure_version_id
2290: FROM pa_proj_element_versions b,
2291: pa_proj_elem_ver_schedule a,
2292: pa_proj_elem_ver_structure c
2293: WHERE a.element_version_id (+)= b.element_version_id
2294: AND a.project_id (+) = b.project_id
2694: ) return VARCHAR2
2695: IS
2696: CURSOR get_parent_structure IS
2697: select a.proj_element_id
2698: from pa_proj_element_versions a,
2699: pa_proj_element_versions b
2700: where b.proj_element_id = p_proj_element_id
2701: and b.project_id = p_project_id
2702: and b.parent_structure_version_id = a.element_version_id
2695: IS
2696: CURSOR get_parent_structure IS
2697: select a.proj_element_id
2698: from pa_proj_element_versions a,
2699: pa_proj_element_versions b
2700: where b.proj_element_id = p_proj_element_id
2701: and b.project_id = p_project_id
2702: and b.parent_structure_version_id = a.element_version_id
2703: and a.project_id = p_project_id;
2775: l_count NUMBER;
2776:
2777: CURSOR c1 IS
2778: select project_id, proj_element_id
2779: from pa_proj_element_versions
2780: where element_version_id = p_structure_version_id;
2781:
2782: CURSOR c2 IS
2783: select count(1)
2829: IS
2830: --cursor to select summary tasks
2831: CURSOR c1 IS
2832: select ppev.proj_element_id, ppev.element_version_id
2833: from pa_proj_element_versions ppev,
2834: pa_object_relationships por
2835: where ppev.parent_structure_version_id = p_structure_version_id
2836: and ppev.object_type = 'PA_TASKS'
2837: and ppev.element_version_id = por.object_id_from1
2847:
2848: --bug 4068736
2849: CURSOR c2(c_task_ver_id number) IS
2850: select 1
2851: from pa_proj_element_versions ppev,
2852: pa_object_relationships por
2853: where por.object_id_from1 = c_task_ver_id
2854: and por.relationship_type = 'S'
2855: and por.object_id_to1 = ppev.element_version_id
2963: )
2964: IS
2965: CURSOR c1 IS
2966: select ppev.proj_element_id, ppev.element_version_id
2967: from pa_proj_element_versions ppev,
2968: pa_object_relationships por
2969: where ppev.parent_structure_version_id = p_structure_version_id
2970: and ppev.object_type = 'PA_TASKS'
2971: and ppev.element_version_id = por.object_id_from1
2986: CURSOR c3(c_parent_task_ver_id NUMBER) IS
2987: select pps.project_system_status_code
2988: from pa_project_statuses pps,
2989: pa_proj_elements ppe,
2990: pa_proj_element_versions ppev,
2991: pa_object_relationships por
2992: where por.object_id_from1 = c_parent_task_ver_id
2993: and por.object_type_from = 'PA_TASKS'
2994: and por.relationship_type = 'S'
3090: from pa_proj_elem_ver_structure
3091: where project_id = p_project_id
3092: and proj_element_id =
3093: (select proj_element_id
3094: from pa_proj_element_versions
3095: where element_version_id = p_structure_ver_id)
3096: and status_code <> 'STRUCTURE_PUBLISHED';
3097: l_cnt NUMBER;
3098: BEGIN
3146: ) return VARCHAR2 IS
3147: cursor c1 IS
3148: select ppa.project_id, ppa.template_flag
3149: from pa_projects_all ppa,
3150: pa_proj_element_versions ppev
3151: where ppev.element_version_id = p_structure_version_id
3152: and ppev.project_id = ppa.project_id;
3153: l_project_id NUMBER;
3154: l_template_flag VARCHAR2(1);
3250: -- Bug 3010538
3251: -- This function will return the process_update_wbs_flag of the structure version.
3252: FUNCTION GET_UPDATE_WBS_FLAG(
3253: p_project_id IN pa_projects_all.project_id%TYPE
3254: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
3255: )
3256: return VARCHAR2 IS
3257: -- This cursor gets the process_update_wbs_flag for the structure version.
3258: cursor cur_update_wbs_flag(c_project_id pa_projects_all.project_id%TYPE,
3255: )
3256: return VARCHAR2 IS
3257: -- This cursor gets the process_update_wbs_flag for the structure version.
3258: cursor cur_update_wbs_flag(c_project_id pa_projects_all.project_id%TYPE,
3259: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
3260: IS
3261: select process_update_wbs_flag
3262: from pa_proj_elem_ver_structure
3263: where project_id = c_project_id
3277: -- particular structure version. If the process code is null, the function returns
3278: -- null and the caller has to do suitable null handling.
3279: FUNCTION GET_PROCESS_STATUS_CODE(
3280: p_project_id IN pa_projects_all.project_id%TYPE
3281: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
3282: )
3283: return VARCHAR2 IS
3284: -- This cursor fetches the process code for the structure version.
3285: cursor cur_process_code(c_project_id pa_projects_all.project_id%TYPE,
3282: )
3283: return VARCHAR2 IS
3284: -- This cursor fetches the process code for the structure version.
3285: cursor cur_process_code(c_project_id pa_projects_all.project_id%TYPE,
3286: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
3287: is
3288: select process_code
3289: from pa_proj_elem_ver_structure
3290: where project_id = c_project_id
3325: SELECT 1
3326: FROM dual
3327: WHERE EXISTS (SELECT LOG.event_id
3328: FROM pji_pa_proj_events_log LOG,
3329: pa_proj_element_versions ver
3330: WHERE LOG.event_type='PRG_CHANGE'
3331: AND LOG.event_object =TO_CHAR(ver.prg_group)
3332: AND ver.project_id=p_project_id
3333: UNION ALL
3332: AND ver.project_id=p_project_id
3333: UNION ALL
3334: SELECT LOG.event_id
3335: FROM pa_pji_proj_events_log LOG,
3336: pa_proj_element_versions ver
3337: WHERE LOG.event_type='PRG_CHANGE'
3338: AND LOG.event_object =TO_CHAR(ver.prg_group)
3339: AND ver.project_id=p_project_id);
3340: -- End Bug # 4611527.
3434: l_struc_ver_id NUMBER;
3435: cursor getFinOnlyStrucVer(c_project_id NUMBER) IS
3436: /*Commented the Following SQL Query for Performance Bug fix : 3968091
3437: select pev.element_version_id
3438: from pa_proj_element_versions pev, pa_proj_elements pe,
3439: pa_proj_structure_types pst
3440: where pe.project_id = c_project_id
3441: and pe.project_id = pev.project_id
3442: and pe.proj_element_id = pev.proj_element_id
3444: and pe.proj_element_id = pst.proj_element_id
3445: and pst.structure_type_id = 6;
3446: */
3447: select pev.element_version_id /*New Query with improved Performance : 3968091 */
3448: from pa_proj_element_versions pev,
3449: pa_proj_structure_types pst
3450: where pev.project_id = c_project_id
3451: and pev.object_type = 'PA_STRUCTURES'
3452: and pev.proj_element_id = pst.proj_element_id
3712:
3713: --Below function is added for bug#3049157
3714:
3715: FUNCTION GET_STRUCT_CONC_ID(
3716: p_structure_version_id IN pa_proj_element_versions.parent_structure_version_id%TYPE
3717: ,p_project_id IN pa_projects_all.project_id%TYPE -- Included for Performance Fix : 3968091
3718: )
3719: return NUMBER IS
3720: -- Returns the conc. request id for the structure version id .
3717: ,p_project_id IN pa_projects_all.project_id%TYPE -- Included for Performance Fix : 3968091
3718: )
3719: return NUMBER IS
3720: -- Returns the conc. request id for the structure version id .
3721: cursor cur_request_details(c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE )
3722: IS
3723: select vs.conc_request_id
3724: from pa_proj_elem_ver_structure vs
3725: where vs.element_version_id = c_structure_version_id
3828: l_Exist_Flag varchar2(2) := 'N';
3829:
3830: BEGIN
3831: For Rec IN ( select a.ELEMENT_VERSION_ID
3832: from pa_proj_element_versions a
3833: -- where a.project_id = 1027
3834: -- This line is not reqd. Its used only for testing
3835: where a.Parent_Structure_Version_ID = P_Version_ID
3836: and a.Element_Version_ID IN (
3944: ) return NUMBER
3945: IS
3946: CURSOR c1 IS
3947: SELECT MAX(a.last_update_date), b.parent_structure_version_id
3948: FROM pa_proj_element_versions b,
3949: pa_proj_elem_ver_schedule a,
3950: pa_proj_elem_ver_structure c
3951: WHERE a.element_version_id (+)= b.element_version_id
3952: AND a.project_id (+) = b.project_id
3970: p_structure_version_id NUMBER
3971: ) return VARCHAR2
3972: IS
3973: CURSOR c1 IS
3974: select project_id from pa_proj_element_versions
3975: where element_version_id = p_structure_version_id;
3976: l_proj_id NUMBER;
3977:
3978: CURSOR c2(c_project_id NUMBER) IS
4004: --Bug No 3692992 Performance fix
4005: /* CURSOR get_baseline_wp_ver(cp_proj_id NUMBER) IS
4006: SELECT ppev.element_version_id
4007: FROM pa_proj_elements ppe,
4008: pa_proj_element_versions ppev,
4009: pa_proj_elem_ver_structure ppevs,
4010: pa_proj_structure_types ppst,
4011: pa_structure_types pst
4012: WHERE ppe.proj_element_id = ppev.proj_element_id
4021:
4022: CURSOR get_baseline_wp_ver(cp_proj_id NUMBER) IS
4023: SELECT ppev.element_version_id
4024: FROM pa_proj_elements ppe,
4025: pa_proj_element_versions ppev,
4026: pa_proj_elem_ver_structure ppevs,
4027: pa_proj_structure_types ppst,
4028: pa_structure_types pst
4029: WHERE ppe.proj_element_id = ppev.proj_element_id
4104: IS
4105: --bug 4087964: changed sql
4106: CURSOR check_subproj_exists IS
4107: SELECT '1'
4108: FROM pa_proj_element_Versions ppev,
4109: pa_object_relationships por
4110: where ppev.parent_structure_version_id = p_structure_ver_id
4111: and ppev.element_version_id = por.object_id_from1
4112: and ppev.project_id = p_project_id
4115:
4116: --bug 4541039
4117: CURSOR check_subproj_exists_wp IS
4118: SELECT '1'
4119: FROM pa_proj_element_Versions ppev,
4120: pa_object_relationships por
4121: where ppev.parent_structure_version_id = p_structure_ver_id
4122: and ppev.element_version_id = por.object_id_from1
4123: and ppev.project_id = p_project_id
4124: and por.relationship_type = 'LW';
4125:
4126: CURSOR check_subproj_exists_fn IS
4127: SELECT '1'
4128: FROM pa_proj_element_Versions ppev,
4129: pa_object_relationships por
4130: where ppev.parent_structure_version_id = p_structure_ver_id
4131: and ppev.element_version_id = por.object_id_from1
4132: and ppev.project_id = p_project_id
4137:
4138: /*
4139: CURSOR chk_lnk_task(cp_structure_version_id NUMBER,cp_project_id NUMBER) IS
4140: SELECT '1'
4141: FROM pa_proj_element_versions ppev,
4142: pa_proj_elements ppe
4143: WHERE ppev.element_version_id = cp_structure_version_id
4144: AND ppev.project_id = cp_project_id
4145: AND ppev.project_id = ppe.project_id
4212: IS
4213: CURSOR chk_structure_ver(cp_project_id NUMBER,cp_struct_ver_id NUMBER)
4214: IS
4215: SELECT 'Y'
4216: FROM pa_proj_element_versions
4217: WHERE project_id = cp_project_id
4218: AND element_version_id = cp_struct_ver_id
4219: AND object_type = 'PA_STRUCTURES';
4220: --
4263:
4264: PROCEDURE GET_PROCESS_STATUS_MSG(
4265: p_project_id IN pa_projects_all.project_id%TYPE
4266: , p_structure_type IN pa_structure_types.structure_type%TYPE := NULL
4267: , p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE := NULL
4268: , p_context IN VARCHAR2 := NULL
4269: , x_message_name OUT NOCOPY VARCHAR2 -- 4537865
4270: , x_message_type OUT NOCOPY VARCHAR2 -- 4537865
4271: , x_structure_version_id OUT NOCOPY NUMBER -- 4537865
4291: and st.structure_type = c_structure_type
4292: and vs.process_code is not null; -- Fix for Bug # 4373055.
4293:
4294: cursor cur_process_code(c_project_id pa_projects_all.project_id%TYPE
4295: , c_structure_version_id pa_proj_element_versions.element_version_id%TYPE) is
4296: select process_code, element_version_id, conc_request_id
4297: from pa_proj_elem_ver_structure
4298: where project_id = c_project_id
4299: and element_version_id = c_structure_version_id;
4994: CURSOR link_exists_cur IS
4995: SELECT 1 FROM DUAL WHERE EXISTS (
4996: SELECT por.object_relationship_id
4997: FROM pa_object_relationships por,
4998: pa_proj_element_versions ppev
4999: WHERE por.object_id_from1 = ppev.element_version_id
5000: AND por.relationship_type in ('LW', 'LF')
5001: AND ppev.parent_structure_version_id = p_structure_version_id
5002: UNION ALL
5001: AND ppev.parent_structure_version_id = p_structure_version_id
5002: UNION ALL
5003: SELECT por. object_relationship_id
5004: FROM pa_object_relationships por,
5005: pa_proj_element_versions ppev
5006: WHERE por.object_id_to1 = ppev.element_version_id
5007: AND por.relationship_type in ('LW', 'LF')
5008: AND ppev.parent_structure_version_id = p_structure_version_id);
5009:
5087: CURSOR c1 IS
5088: select 1
5089: from dual
5090: where exists (select log.event_id
5091: from pji_pa_proj_events_log log,pa_proj_element_versions ver
5092: where log.event_type='PRG_CHANGE'
5093: and log.event_object =to_char(ver.prg_group)
5094: and ver.project_id=p_project_id
5095: union all
5093: and log.event_object =to_char(ver.prg_group)
5094: and ver.project_id=p_project_id
5095: union all
5096: select log.event_id
5097: from pa_pji_proj_events_log log,pa_proj_element_versions ver
5098: where log.event_type='PRG_CHANGE'
5099: and log.event_object =to_char(ver.prg_group)
5100: and ver.project_id=p_project_id);
5101: