[Home] [Help]
149: select
150: distinct
151: prg_group
152: into l_prg_group_id
153: from pa_proj_element_versions
154: where element_version_id = p_wbs_version_id;
155:
156: exception
157: when no_data_found
400: (
401: select
402: distinct
403: project_id
404: from pa_proj_element_versions
405: where 1=1
406: and object_type = 'PA_STRUCTURES'
407: and prg_group IS NOT NULL /* 4904076 */
408: and prg_group = PRG_DELETE_NODE.event_object_id
453: (
454: select
455: distinct
456: project_id
457: from pa_proj_element_versions
458: where 1=1
459: and object_type = 'PA_STRUCTURES'
460: and prg_group IS NULL /* 4904076 */
461: and project_id = PRG_2_DELETE_NODE.attribute1
640:
641: begin
642: select proj_element_id ,project_id
643: into l_top_proj_element_id ,l_target_project_id
644: from PA_PROJ_ELEMENT_VERSIONS
645: where parent_structure_version_id= p_wbs_version_id_to
646: and object_type='PA_STRUCTURES';
647: exception
648: when no_data_found then null;
665: ,temp.sub_leaf_flag, temp.sup_level, temp.sub_level,temp.relationship_type
666: , decode( temp.struct_type,'WBS',l_top_proj_element_id,null) struct_emt_id
667: ,temp.sub_rollup_id,0,0,0,temp.sub_element_number,temp.subro_element_number
668: from pa_xbs_denorm_temp temp
669: ,PA_PROJ_ELEMENT_VERSIONS projv1s
670: ,pa_proj_elements proje1s
671: where proje1s.element_number= temp.sup_element_number
672: and proje1s.proj_element_id =projv1s.proj_element_id
673: and projv1s.parent_structure_version_id=temp.struct_version_id
716: where proje.proj_element_id= xbs.sup_emt_id ) sup_element_number
717: ,(select projec.element_number from pa_proj_elements projec
718: where projec.proj_element_id= xbs.subro_id) subro_element_number
719: from pa_xbs_denorm xbs
720: ,PA_PROJ_ELEMENT_VERSIONS projsup
721: where xbs.sup_emt_id =projsup.proj_element_id
722: and xbs.sup_id=projsup.element_version_id
723: and xbs.struct_version_id =p_wbs_version_id_from
724: and projsup.parent_structure_version_id=xbs.struct_version_id;
800: ,temp.sup_level, temp.sub_level
801: ,temp.relationship_type ,temp.sub_leaf_flag
802: ,temp.struct_emt_id,temp.sub_rollup_id
803: ,(select proje1.proj_element_id from pa_proj_elements proje1
804: ,PA_PROJ_ELEMENT_VERSIONS projv1
805: WHERE PROJE1.PROJECT_ID=TEMP.SUP_PROJECT_ID
806: AND PROJE1.OBJECT_TYPE='PA_TASKS'
807: AND PROJE1.ELEMENT_NUMBER= TEMP.SUBRO_ELEMENT_NUMBER
808: AND PROJE1.PROJ_ELEMENT_ID= PROJV1.PROJ_ELEMENT_ID
809: AND PROJV1.PARENT_STRUCTURE_VERSION_ID= TEMP.STRUCT_VERSION_ID
810: AND PROJV1.PARENT_STRUCTURE_VERSION_ID=p_wbs_version_id_to
811: ) subro_id
812: from pa_xbs_denorm_temp temp
813: ,PA_PROJ_ELEMENT_VERSIONS projv1b
814: ,pa_proj_elements proje1b
815: where proje1b.element_number= temp.sub_element_number
816: and proje1b.proj_element_id =projv1b.proj_element_id
817: and projv1b.parent_structure_version_id=temp.struct_version_id
1054: select xbs.struct_type,xbs.prg_group,
1055: to_number(p_wbs_version_id_to) wstruct_version_id,xbs.sup_project_id,
1056: xbs.sup_emt_id,xbs.sub_emt_id,
1057: projsup.element_version_id sup_id,
1058: (select projsub.element_version_id from PA_PROJ_ELEMENT_VERSIONS projsub
1059: where projsub.proj_element_id= xbs.sub_emt_id
1060: and projsup.parent_structure_version_id=projsub.parent_structure_version_id) sub_id,
1061: xbs.sup_level,xbs.sub_level,
1062: xbs.relationship_type,xbs.sub_leaf_flag,
1062: xbs.relationship_type,xbs.sub_leaf_flag,
1063: xbs.struct_emt_id,xbs.sub_rollup_id,
1064: xbs.subro_id
1065: from pa_xbs_denorm xbs,
1066: PA_PROJ_ELEMENT_VERSIONS projsup
1067: where projsup.parent_structure_version_id= p_wbs_version_id_to
1068: and projsup.proj_element_id= xbs.sup_emt_id
1069: and xbs.struct_version_id= p_wbs_version_id_from;
1070:
1850: --
1851: -- *** This API assumes that the following tables exist and that they are
1852: -- properly populated (no cycles, correct relationships, etc)
1853: --
1854: -- PA_PROJ_ELEMENT_VERSIONS
1855: -- PA_OBJECT_RELATIONSHIPS
1856: --
1857: -- Then, this API populates output values in the following existing
1858: -- table:
1938: ver.parent_structure_version_id,
1939: ver.prg_group
1940: from
1941: PJI_PJP_PROJ_BATCH_MAP map,
1942: PA_PROJ_ELEMENT_VERSIONS ver
1943: where
1944: p_extraction_type in ('FULL', 'UPGRADE') and
1945: ver.object_type = 'PA_STRUCTURES' and
1946: ver.prg_group is null and
1962: select /*+ ordered */
1963: distinct
1964: ver.prg_group
1965: from
1966: PA_PROJ_ELEMENT_VERSIONS ver,
1967: PJI_PJP_PROJ_BATCH_MAP map
1968: where
1969: p_extraction_type in ('FULL', 'UPGRADE') and
1970: ver.object_type = 'PA_STRUCTURES' and
1972: map.worker_id = p_worker_id and
1973: map.PJI_PROJECT_STATUS is null and
1974: ver.project_id = map.project_id
1975: ) batch_map,
1976: PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
1977: where
1978: p_extraction_type in ('FULL', 'UPGRADE') and
1979: pvt_nodes1.object_type = 'PA_STRUCTURES' and
1980: pvt_nodes1.prg_group is not null and
1987: pvt_nodes2.proj_element_id,
1988: pvt_nodes2.element_version_id,
1989: pvt_nodes2.parent_structure_version_id,
1990: pvt_nodes2.prg_group
1991: from PA_PROJ_ELEMENT_VERSIONS pvt_nodes2,
1992: (
1993: select
1994: distinct
1995: decode( invert.id,
2022: pvt_nodes3.proj_element_id,
2023: pvt_nodes3.element_version_id,
2024: pvt_nodes3.parent_structure_version_id,
2025: pvt_nodes3.prg_group
2026: from PA_PROJ_ELEMENT_VERSIONS pvt_nodes3,
2027: (
2028: select
2029: distinct
2030: decode( invert.id,
2202: prt_parent.object_id_from1,
2203: prt_parent.relationship_type,
2204: ver.prg_level
2205: from PA_OBJECT_RELATIONSHIPS prt_parent,
2206: PA_PROJ_ELEMENT_VERSIONS ver
2207: where 1=1
2208: and prt_parent.object_id_to1 = PRG_NODE.element_version_id
2209: and prt_parent.object_type_from = 'PA_TASKS'
2210: and prt_parent.object_type_to = 'PA_STRUCTURES'
2221: pvt_parent1.proj_element_id
2222: into l_prg_temp_parent,
2223: l_prj_temp_parent,
2224: l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
2225: from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
2226: where 1=1
2227: and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2228:
2229: -- l_prg_dummy_task_flag -- ###dummy###
2243: /*
2244: select dt_ver1.proj_element_id
2245: into l_prg_temp_rollup
2246: from pa_object_relationships dt_rel,
2247: pa_proj_element_versions dt_ver1,
2248: pa_proj_element_versions dt_ver2
2249: where 1=1
2250: and dt_ver1.element_version_id = dt_rel.object_id_from1
2251: and dt_rel.object_type_from = 'PA_TASKS'
2244: select dt_ver1.proj_element_id
2245: into l_prg_temp_rollup
2246: from pa_object_relationships dt_rel,
2247: pa_proj_element_versions dt_ver1,
2248: pa_proj_element_versions dt_ver2
2249: where 1=1
2250: and dt_ver1.element_version_id = dt_rel.object_id_from1
2251: and dt_rel.object_type_from = 'PA_TASKS'
2252: and dt_rel.object_type_to = 'PA_TASKS'
2257: -- Bug 3838523
2258: select dt_ver1.proj_element_id
2259: into l_prg_temp_rollup
2260: from pa_object_relationships dt_rel,
2261: pa_proj_element_versions dt_ver1
2262: where 1=1
2263: and dt_ver1.element_version_id = dt_rel.object_id_from1
2264: and dt_rel.object_type_from = 'PA_TASKS'
2265: and dt_rel.object_type_to = 'PA_TASKS'
2269:
2270: -- l_prg_temp_sup_emt --
2271: select pvt_parent4.proj_element_id
2272: into l_prg_temp_sup_emt
2273: from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
2274: where 1=1
2275: and pvt_parent4.element_version_id = l_prg_temp_parent;
2276:
2277: -- l_prg_temp_sub_emt --
2276:
2277: -- l_prg_temp_sub_emt --
2278: select pvt_parent5.proj_element_id
2279: into l_prg_temp_sub_emt
2280: from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
2281: where 1=1
2282: and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
2283:
2284: -- l_prg_leaf_flag --
2381:
2382: -- l_prj_temp_parent --
2383: select pvt_child1.project_id
2384: into l_prj_temp_parent
2385: from PA_PROJ_ELEMENT_VERSIONS pvt_child1
2386: where 1=1
2387: and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2388:
2389: -- l_prg_temp_sup_emt --
2388:
2389: -- l_prg_temp_sup_emt --
2390: select pvt_child2.proj_element_id
2391: into l_prg_temp_sup_emt
2392: from PA_PROJ_ELEMENT_VERSIONS pvt_child2
2393: where 1=1
2394: and pvt_child2.element_version_id = l_prg_temp_parent;
2395:
2396: -- l_prg_temp_sub_emt --
2395:
2396: -- l_prg_temp_sub_emt --
2397: select pvt_child3.proj_element_id
2398: into l_prg_temp_sub_emt
2399: from PA_PROJ_ELEMENT_VERSIONS pvt_child3
2400: where 1=1
2401: and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
2402:
2403: -- l_prg_leaf_flag --
2482: -- that don't exist or call more than once.
2483:
2484: select count(*)
2485: into l_prg_element_version_count
2486: from PA_PROJ_ELEMENT_VERSIONS cv
2487: where cv.parent_structure_version_id = PRG_NODE.element_version_id
2488: and rownum = 1;
2489:
2490: if (
2576: --
2577: -- *** This API assumes that the following tables exist and that they are
2578: -- properly populated (no cycles, correct relationships, etc)
2579: --
2580: -- PA_PROJ_ELEMENT_VERSIONS
2581: -- PA_OBJECT_RELATIONSHIPS
2582: --
2583: -- Then, this API populates output values in the following existing
2584: -- table:
2646: begin
2647: select projects.structure_sharing_code,projects.project_id
2648: into l_sharing_code,l_project_id
2649: from pa_projects_all projects,
2650: pa_proj_element_versions versions
2651: where 1=1
2652: and projects.project_id = versions.project_id
2653: and versions.object_type = 'PA_STRUCTURES'
2654: and versions.element_version_id = P_WBS_VERSION_ID;
2680: wvt_nodes.proj_element_id,
2681: wvt_nodes.element_version_id,
2682: wvt_nodes.parent_structure_version_id,
2683: wvt_nodes.financial_task_flag -- ###financial###
2684: from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
2685: where 1=1
2686: and (
2687: P_EXTRACTION_TYPE = 'FULL'
2688: or
2830:
2831: -- l_wbs_temp_sup_emt --
2832: select wvt_parent1.proj_element_id
2833: into l_wbs_temp_sup_emt
2834: from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
2835: where 1=1
2836: and wvt_parent1.element_version_id = l_wbs_temp_parent;
2837:
2838: -- l_wbs_temp_sub_emt --
2837:
2838: -- l_wbs_temp_sub_emt --
2839: select wvt_parent2.proj_element_id
2840: into l_wbs_temp_sub_emt
2841: from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
2842: where 1=1
2843: and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
2844:
2845: -- l_wbs_leaf_flag --
2944:
2945: -- l_wbs_temp_sup_emt --
2946: select wvt_child1.proj_element_id
2947: into l_wbs_temp_sup_emt
2948: from PA_PROJ_ELEMENT_VERSIONS wvt_child1
2949: where 1=1
2950: and wvt_child1.element_version_id = l_wbs_temp_parent;
2951:
2952: -- l_wbs_temp_sub_emt --
2951:
2952: -- l_wbs_temp_sub_emt --
2953: select wvt_child2.proj_element_id
2954: into l_wbs_temp_sub_emt
2955: from PA_PROJ_ELEMENT_VERSIONS wvt_child2
2956: where 1=1
2957: and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
2958:
2959: -- l_wbs_leaf_flag --
3150: --
3151: -- *** This API assumes that the following tables exist and that they are
3152: -- properly populated (no cycles, correct relationships, etc)
3153: --
3154: -- PA_PROJ_ELEMENT_VERSIONS
3155: -- PA_OBJECT_RELATIONSHIPS
3156: --
3157: -- Then, this API populates output values in the following existing
3158: -- table:
3217: then
3218:
3219: select max(pvt_level.prg_level)
3220: into l_prg_level_id
3221: from PA_PROJ_ELEMENT_VERSIONS pvt_level
3222: where 1=1
3223: and pvt_level.object_type = 'PA_STRUCTURES'
3224: and pvt_level.prg_group IS NOT NULL /* 4904076 */
3225: and prg_group = p_prg_group_id;
3268: pvt_nodes1.element_version_id,
3269: pvt_nodes1.parent_structure_version_id,
3270: pvt_nodes1.prg_group,
3271: pvt_nodes1.prg_level /*4625702*/
3272: from PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
3273: where 1=1
3274: and pvt_nodes1.object_type = 'PA_STRUCTURES'
3275: and pvt_nodes1.element_version_id = p_wbs_version_id
3276: ) LOOP
3395: prt_parent.object_id_from1,
3396: prt_parent.relationship_type,
3397: ver.prg_level
3398: from PA_OBJECT_RELATIONSHIPS prt_parent,
3399: PA_PROJ_ELEMENT_VERSIONS ver
3400: where 1=1
3401: and prt_parent.object_id_to1 = PRG_NODE.element_version_id
3402: and prt_parent.object_type_from = 'PA_TASKS'
3403: and prt_parent.object_type_to = 'PA_STRUCTURES'
3417: pvt_parent1.proj_element_id
3418: into l_prg_temp_parent,
3419: l_prj_temp_parent,
3420: l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
3421: from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
3422: where 1=1
3423: and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3424:
3425: -- l_prg_dummy_task_flag -- ###dummy###
3438: else
3439: select dt_ver1.proj_element_id
3440: into l_prg_temp_rollup
3441: from pa_object_relationships dt_rel,
3442: pa_proj_element_versions dt_ver1
3443: /* commented for bug 3838523 pa_proj_element_versions dt_ver2*/
3444: where 1=1
3445: and dt_ver1.element_version_id = dt_rel.object_id_from1
3446: and dt_rel.object_type_from = 'PA_TASKS'
3439: select dt_ver1.proj_element_id
3440: into l_prg_temp_rollup
3441: from pa_object_relationships dt_rel,
3442: pa_proj_element_versions dt_ver1
3443: /* commented for bug 3838523 pa_proj_element_versions dt_ver2*/
3444: where 1=1
3445: and dt_ver1.element_version_id = dt_rel.object_id_from1
3446: and dt_rel.object_type_from = 'PA_TASKS'
3447: and dt_rel.object_type_to = 'PA_TASKS'
3454:
3455: -- l_prg_temp_sup_emt --
3456: select pvt_parent4.proj_element_id
3457: into l_prg_temp_sup_emt
3458: from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
3459: where 1=1
3460: and pvt_parent4.element_version_id = l_prg_temp_parent;
3461:
3462: -- l_prg_temp_sub_emt --
3461:
3462: -- l_prg_temp_sub_emt --
3463: select pvt_parent5.proj_element_id
3464: into l_prg_temp_sub_emt
3465: from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
3466: where 1=1
3467: and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
3468:
3469: -- l_prg_leaf_flag --
3568:
3569: -- l_prj_temp_parent --
3570: select pvt_child1.project_id
3571: into l_prj_temp_parent
3572: from PA_PROJ_ELEMENT_VERSIONS pvt_child1
3573: where 1=1
3574: and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3575:
3576: -- l_prg_temp_sup_emt --
3575:
3576: -- l_prg_temp_sup_emt --
3577: select pvt_child2.proj_element_id
3578: into l_prg_temp_sup_emt
3579: from PA_PROJ_ELEMENT_VERSIONS pvt_child2
3580: where 1=1
3581: and pvt_child2.element_version_id = l_prg_temp_parent;
3582:
3583: -- l_prg_temp_sub_emt --
3582:
3583: -- l_prg_temp_sub_emt --
3584: select pvt_child3.proj_element_id
3585: into l_prg_temp_sub_emt
3586: from PA_PROJ_ELEMENT_VERSIONS pvt_child3
3587: where 1=1
3588: and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
3589:
3590: -- l_prg_leaf_flag --
3719: --
3720: -- *** This API assumes that the following tables exist and that they are
3721: -- properly populated (no cycles, correct relationships, etc)
3722: --
3723: -- PA_PROJ_ELEMENT_VERSIONS
3724: -- PA_OBJECT_RELATIONSHIPS
3725: --
3726: -- Then, this API populates output values in the following existing
3727: -- table:
3775: -- get a node count
3776:
3777: select count(*)
3778: into l_wbs_count
3779: from PA_PROJ_ELEMENT_VERSIONS wvt_count
3780: where 1=1
3781: and wvt_count.object_type = 'PA_TASKS'
3782: and wvt_count.proj_element_id in -- ###dummy###
3783: (
3813: -- 1) ONLINE
3814:
3815: select max(wvt_level.wbs_level)
3816: into l_wbs_level_id
3817: from PA_PROJ_ELEMENT_VERSIONS wvt_level
3818: where 1=1
3819: and wvt_level.object_type = 'PA_TASKS'
3820: and wvt_level.proj_element_id in -- ###dummy###
3821: (
3865: begin
3866: select structure_sharing_code
3867: into l_sharing_code
3868: from pa_projects_all projects,
3869: pa_proj_element_versions versions
3870: where 1=1
3871: and projects.project_id = versions.project_id
3872: and versions.object_type = 'PA_STRUCTURES'
3873: and versions.element_version_id = P_WBS_VERSION_ID;
3908: wvt_nodes.proj_element_id,
3909: wvt_nodes.element_version_id,
3910: wvt_nodes.parent_structure_version_id,
3911: wvt_nodes.financial_task_flag -- ###financial###
3912: from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
3913: where 1=1
3914: and wvt_nodes.object_type = 'PA_TASKS'
3915: and wvt_nodes.proj_element_id in -- ###dummy###
3916: (
4036: into l_wbs_test_node
4037: from PA_OBJECT_RELATIONSHIPS rel,
4038: (
4039: select element_version_id
4040: from PA_PROJ_ELEMENT_VERSIONS
4041: where 1=1
4042: and WBS_LEVEL > 1
4043: and element_version_id = WBS_NODE.element_version_id
4044: ) ver
4069:
4070: -- l_wbs_temp_sup_emt --
4071: select wvt_parent1.proj_element_id
4072: into l_wbs_temp_sup_emt
4073: from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
4074: where 1=1
4075: and wvt_parent1.element_version_id = l_wbs_temp_parent;
4076:
4077: -- l_wbs_temp_sub_emt --
4076:
4077: -- l_wbs_temp_sub_emt --
4078: select wvt_parent2.proj_element_id
4079: into l_wbs_temp_sub_emt
4080: from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
4081: where 1=1
4082: and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
4083:
4084: -- l_wbs_leaf_flag --
4184:
4185: -- l_wbs_temp_sup_emt --
4186: select wvt_child1.proj_element_id
4187: into l_wbs_temp_sup_emt
4188: from PA_PROJ_ELEMENT_VERSIONS wvt_child1
4189: where 1=1
4190: and wvt_child1.element_version_id = l_wbs_temp_parent;
4191:
4192: -- l_wbs_temp_sub_emt --
4191:
4192: -- l_wbs_temp_sub_emt --
4193: select wvt_child2.proj_element_id
4194: into l_wbs_temp_sub_emt
4195: from PA_PROJ_ELEMENT_VERSIONS wvt_child2
4196: where 1=1
4197: and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
4198:
4199: -- l_wbs_leaf_flag --
4289: ELSE
4290:
4291: select count(*)
4292: into l_struct_emt_id_count
4293: from pa_proj_element_versions
4294: where 1=1
4295: and element_version_id = P_WBS_VERSION_ID
4296: and rownum = 1;
4297:
4302: select
4303: distinct
4304: proj_element_id
4305: into l_struct_emt_id
4306: from pa_proj_element_versions
4307: where 1=1
4308: and element_version_id = P_WBS_VERSION_ID;
4309:
4310: