[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:
1790: --
1791: -- *** This API assumes that the following tables exist and that they are
1792: -- properly populated (no cycles, correct relationships, etc)
1793: --
1794: -- PA_PROJ_ELEMENT_VERSIONS
1795: -- PA_OBJECT_RELATIONSHIPS
1796: --
1797: -- Then, this API populates output values in the following existing
1798: -- table:
1878: ver.parent_structure_version_id,
1879: ver.prg_group
1880: from
1881: PJI_PJP_PROJ_BATCH_MAP map,
1882: PA_PROJ_ELEMENT_VERSIONS ver
1883: where
1884: p_extraction_type in ('FULL', 'UPGRADE') and
1885: ver.object_type = 'PA_STRUCTURES' and
1886: ver.prg_group is null and
1902: select /*+ ordered */
1903: distinct
1904: ver.prg_group
1905: from
1906: PA_PROJ_ELEMENT_VERSIONS ver,
1907: PJI_PJP_PROJ_BATCH_MAP map
1908: where
1909: p_extraction_type in ('FULL', 'UPGRADE') and
1910: ver.object_type = 'PA_STRUCTURES' and
1912: map.worker_id = p_worker_id and
1913: map.PJI_PROJECT_STATUS is null and
1914: ver.project_id = map.project_id
1915: ) batch_map,
1916: PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
1917: where
1918: p_extraction_type in ('FULL', 'UPGRADE') and
1919: pvt_nodes1.object_type = 'PA_STRUCTURES' and
1920: pvt_nodes1.prg_group is not null and
1927: pvt_nodes2.proj_element_id,
1928: pvt_nodes2.element_version_id,
1929: pvt_nodes2.parent_structure_version_id,
1930: pvt_nodes2.prg_group
1931: from PA_PROJ_ELEMENT_VERSIONS pvt_nodes2,
1932: (
1933: select
1934: distinct
1935: decode( invert.id,
1962: pvt_nodes3.proj_element_id,
1963: pvt_nodes3.element_version_id,
1964: pvt_nodes3.parent_structure_version_id,
1965: pvt_nodes3.prg_group
1966: from PA_PROJ_ELEMENT_VERSIONS pvt_nodes3,
1967: (
1968: select
1969: distinct
1970: decode( invert.id,
2142: prt_parent.object_id_from1,
2143: prt_parent.relationship_type,
2144: ver.prg_level
2145: from PA_OBJECT_RELATIONSHIPS prt_parent,
2146: PA_PROJ_ELEMENT_VERSIONS ver
2147: where 1=1
2148: and prt_parent.object_id_to1 = PRG_NODE.element_version_id
2149: and prt_parent.object_type_from = 'PA_TASKS'
2150: and prt_parent.object_type_to = 'PA_STRUCTURES'
2161: pvt_parent1.proj_element_id
2162: into l_prg_temp_parent,
2163: l_prj_temp_parent,
2164: l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
2165: from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
2166: where 1=1
2167: and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2168:
2169: -- l_prg_dummy_task_flag -- ###dummy###
2183: /*
2184: select dt_ver1.proj_element_id
2185: into l_prg_temp_rollup
2186: from pa_object_relationships dt_rel,
2187: pa_proj_element_versions dt_ver1,
2188: pa_proj_element_versions dt_ver2
2189: where 1=1
2190: and dt_ver1.element_version_id = dt_rel.object_id_from1
2191: and dt_rel.object_type_from = 'PA_TASKS'
2184: select dt_ver1.proj_element_id
2185: into l_prg_temp_rollup
2186: from pa_object_relationships dt_rel,
2187: pa_proj_element_versions dt_ver1,
2188: pa_proj_element_versions dt_ver2
2189: where 1=1
2190: and dt_ver1.element_version_id = dt_rel.object_id_from1
2191: and dt_rel.object_type_from = 'PA_TASKS'
2192: and dt_rel.object_type_to = 'PA_TASKS'
2197: -- Bug 3838523
2198: select dt_ver1.proj_element_id
2199: into l_prg_temp_rollup
2200: from pa_object_relationships dt_rel,
2201: pa_proj_element_versions dt_ver1
2202: where 1=1
2203: and dt_ver1.element_version_id = dt_rel.object_id_from1
2204: and dt_rel.object_type_from = 'PA_TASKS'
2205: and dt_rel.object_type_to = 'PA_TASKS'
2209:
2210: -- l_prg_temp_sup_emt --
2211: select pvt_parent4.proj_element_id
2212: into l_prg_temp_sup_emt
2213: from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
2214: where 1=1
2215: and pvt_parent4.element_version_id = l_prg_temp_parent;
2216:
2217: -- l_prg_temp_sub_emt --
2216:
2217: -- l_prg_temp_sub_emt --
2218: select pvt_parent5.proj_element_id
2219: into l_prg_temp_sub_emt
2220: from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
2221: where 1=1
2222: and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
2223:
2224: -- l_prg_leaf_flag --
2321:
2322: -- l_prj_temp_parent --
2323: select pvt_child1.project_id
2324: into l_prj_temp_parent
2325: from PA_PROJ_ELEMENT_VERSIONS pvt_child1
2326: where 1=1
2327: and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2328:
2329: -- l_prg_temp_sup_emt --
2328:
2329: -- l_prg_temp_sup_emt --
2330: select pvt_child2.proj_element_id
2331: into l_prg_temp_sup_emt
2332: from PA_PROJ_ELEMENT_VERSIONS pvt_child2
2333: where 1=1
2334: and pvt_child2.element_version_id = l_prg_temp_parent;
2335:
2336: -- l_prg_temp_sub_emt --
2335:
2336: -- l_prg_temp_sub_emt --
2337: select pvt_child3.proj_element_id
2338: into l_prg_temp_sub_emt
2339: from PA_PROJ_ELEMENT_VERSIONS pvt_child3
2340: where 1=1
2341: and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
2342:
2343: -- l_prg_leaf_flag --
2422: -- that don't exist or call more than once.
2423:
2424: select count(*)
2425: into l_prg_element_version_count
2426: from PA_PROJ_ELEMENT_VERSIONS cv
2427: where cv.parent_structure_version_id = PRG_NODE.element_version_id
2428: and rownum = 1;
2429:
2430: if (
2516: --
2517: -- *** This API assumes that the following tables exist and that they are
2518: -- properly populated (no cycles, correct relationships, etc)
2519: --
2520: -- PA_PROJ_ELEMENT_VERSIONS
2521: -- PA_OBJECT_RELATIONSHIPS
2522: --
2523: -- Then, this API populates output values in the following existing
2524: -- table:
2586: begin
2587: select projects.structure_sharing_code,projects.project_id
2588: into l_sharing_code,l_project_id
2589: from pa_projects_all projects,
2590: pa_proj_element_versions versions
2591: where 1=1
2592: and projects.project_id = versions.project_id
2593: and versions.object_type = 'PA_STRUCTURES'
2594: and versions.element_version_id = P_WBS_VERSION_ID;
2620: wvt_nodes.proj_element_id,
2621: wvt_nodes.element_version_id,
2622: wvt_nodes.parent_structure_version_id,
2623: wvt_nodes.financial_task_flag -- ###financial###
2624: from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
2625: where 1=1
2626: and (
2627: P_EXTRACTION_TYPE = 'FULL'
2628: or
2765:
2766: -- l_wbs_temp_sup_emt --
2767: select wvt_parent1.proj_element_id
2768: into l_wbs_temp_sup_emt
2769: from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
2770: where 1=1
2771: and wvt_parent1.element_version_id = l_wbs_temp_parent;
2772:
2773: -- l_wbs_temp_sub_emt --
2772:
2773: -- l_wbs_temp_sub_emt --
2774: select wvt_parent2.proj_element_id
2775: into l_wbs_temp_sub_emt
2776: from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
2777: where 1=1
2778: and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
2779:
2780: -- l_wbs_leaf_flag --
2879:
2880: -- l_wbs_temp_sup_emt --
2881: select wvt_child1.proj_element_id
2882: into l_wbs_temp_sup_emt
2883: from PA_PROJ_ELEMENT_VERSIONS wvt_child1
2884: where 1=1
2885: and wvt_child1.element_version_id = l_wbs_temp_parent;
2886:
2887: -- l_wbs_temp_sub_emt --
2886:
2887: -- l_wbs_temp_sub_emt --
2888: select wvt_child2.proj_element_id
2889: into l_wbs_temp_sub_emt
2890: from PA_PROJ_ELEMENT_VERSIONS wvt_child2
2891: where 1=1
2892: and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
2893:
2894: -- l_wbs_leaf_flag --
3085: --
3086: -- *** This API assumes that the following tables exist and that they are
3087: -- properly populated (no cycles, correct relationships, etc)
3088: --
3089: -- PA_PROJ_ELEMENT_VERSIONS
3090: -- PA_OBJECT_RELATIONSHIPS
3091: --
3092: -- Then, this API populates output values in the following existing
3093: -- table:
3152: then
3153:
3154: select max(pvt_level.prg_level)
3155: into l_prg_level_id
3156: from PA_PROJ_ELEMENT_VERSIONS pvt_level
3157: where 1=1
3158: and pvt_level.object_type = 'PA_STRUCTURES'
3159: and pvt_level.prg_group IS NOT NULL /* 4904076 */
3160: and prg_group = p_prg_group_id;
3203: pvt_nodes1.element_version_id,
3204: pvt_nodes1.parent_structure_version_id,
3205: pvt_nodes1.prg_group,
3206: pvt_nodes1.prg_level /*4625702*/
3207: from PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
3208: where 1=1
3209: and pvt_nodes1.object_type = 'PA_STRUCTURES'
3210: and pvt_nodes1.element_version_id = p_wbs_version_id
3211: ) LOOP
3330: prt_parent.object_id_from1,
3331: prt_parent.relationship_type,
3332: ver.prg_level
3333: from PA_OBJECT_RELATIONSHIPS prt_parent,
3334: PA_PROJ_ELEMENT_VERSIONS ver
3335: where 1=1
3336: and prt_parent.object_id_to1 = PRG_NODE.element_version_id
3337: and prt_parent.object_type_from = 'PA_TASKS'
3338: and prt_parent.object_type_to = 'PA_STRUCTURES'
3352: pvt_parent1.proj_element_id
3353: into l_prg_temp_parent,
3354: l_prj_temp_parent,
3355: l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
3356: from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
3357: where 1=1
3358: and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3359:
3360: -- l_prg_dummy_task_flag -- ###dummy###
3373: else
3374: select dt_ver1.proj_element_id
3375: into l_prg_temp_rollup
3376: from pa_object_relationships dt_rel,
3377: pa_proj_element_versions dt_ver1
3378: /* commented for bug 3838523 pa_proj_element_versions dt_ver2*/
3379: where 1=1
3380: and dt_ver1.element_version_id = dt_rel.object_id_from1
3381: and dt_rel.object_type_from = 'PA_TASKS'
3374: select dt_ver1.proj_element_id
3375: into l_prg_temp_rollup
3376: from pa_object_relationships dt_rel,
3377: pa_proj_element_versions dt_ver1
3378: /* commented for bug 3838523 pa_proj_element_versions dt_ver2*/
3379: where 1=1
3380: and dt_ver1.element_version_id = dt_rel.object_id_from1
3381: and dt_rel.object_type_from = 'PA_TASKS'
3382: and dt_rel.object_type_to = 'PA_TASKS'
3389:
3390: -- l_prg_temp_sup_emt --
3391: select pvt_parent4.proj_element_id
3392: into l_prg_temp_sup_emt
3393: from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
3394: where 1=1
3395: and pvt_parent4.element_version_id = l_prg_temp_parent;
3396:
3397: -- l_prg_temp_sub_emt --
3396:
3397: -- l_prg_temp_sub_emt --
3398: select pvt_parent5.proj_element_id
3399: into l_prg_temp_sub_emt
3400: from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
3401: where 1=1
3402: and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
3403:
3404: -- l_prg_leaf_flag --
3503:
3504: -- l_prj_temp_parent --
3505: select pvt_child1.project_id
3506: into l_prj_temp_parent
3507: from PA_PROJ_ELEMENT_VERSIONS pvt_child1
3508: where 1=1
3509: and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3510:
3511: -- l_prg_temp_sup_emt --
3510:
3511: -- l_prg_temp_sup_emt --
3512: select pvt_child2.proj_element_id
3513: into l_prg_temp_sup_emt
3514: from PA_PROJ_ELEMENT_VERSIONS pvt_child2
3515: where 1=1
3516: and pvt_child2.element_version_id = l_prg_temp_parent;
3517:
3518: -- l_prg_temp_sub_emt --
3517:
3518: -- l_prg_temp_sub_emt --
3519: select pvt_child3.proj_element_id
3520: into l_prg_temp_sub_emt
3521: from PA_PROJ_ELEMENT_VERSIONS pvt_child3
3522: where 1=1
3523: and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
3524:
3525: -- l_prg_leaf_flag --
3654: --
3655: -- *** This API assumes that the following tables exist and that they are
3656: -- properly populated (no cycles, correct relationships, etc)
3657: --
3658: -- PA_PROJ_ELEMENT_VERSIONS
3659: -- PA_OBJECT_RELATIONSHIPS
3660: --
3661: -- Then, this API populates output values in the following existing
3662: -- table:
3710: -- get a node count
3711:
3712: select count(*)
3713: into l_wbs_count
3714: from PA_PROJ_ELEMENT_VERSIONS wvt_count
3715: where 1=1
3716: and wvt_count.object_type = 'PA_TASKS'
3717: and wvt_count.proj_element_id in -- ###dummy###
3718: (
3748: -- 1) ONLINE
3749:
3750: select max(wvt_level.wbs_level)
3751: into l_wbs_level_id
3752: from PA_PROJ_ELEMENT_VERSIONS wvt_level
3753: where 1=1
3754: and wvt_level.object_type = 'PA_TASKS'
3755: and wvt_level.proj_element_id in -- ###dummy###
3756: (
3800: begin
3801: select structure_sharing_code
3802: into l_sharing_code
3803: from pa_projects_all projects,
3804: pa_proj_element_versions versions
3805: where 1=1
3806: and projects.project_id = versions.project_id
3807: and versions.object_type = 'PA_STRUCTURES'
3808: and versions.element_version_id = P_WBS_VERSION_ID;
3843: wvt_nodes.proj_element_id,
3844: wvt_nodes.element_version_id,
3845: wvt_nodes.parent_structure_version_id,
3846: wvt_nodes.financial_task_flag -- ###financial###
3847: from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
3848: where 1=1
3849: and wvt_nodes.object_type = 'PA_TASKS'
3850: and wvt_nodes.proj_element_id in -- ###dummy###
3851: (
3971: into l_wbs_test_node
3972: from PA_OBJECT_RELATIONSHIPS rel,
3973: (
3974: select element_version_id
3975: from PA_PROJ_ELEMENT_VERSIONS
3976: where 1=1
3977: and WBS_LEVEL > 1
3978: and element_version_id = WBS_NODE.element_version_id
3979: ) ver
4004:
4005: -- l_wbs_temp_sup_emt --
4006: select wvt_parent1.proj_element_id
4007: into l_wbs_temp_sup_emt
4008: from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
4009: where 1=1
4010: and wvt_parent1.element_version_id = l_wbs_temp_parent;
4011:
4012: -- l_wbs_temp_sub_emt --
4011:
4012: -- l_wbs_temp_sub_emt --
4013: select wvt_parent2.proj_element_id
4014: into l_wbs_temp_sub_emt
4015: from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
4016: where 1=1
4017: and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
4018:
4019: -- l_wbs_leaf_flag --
4118:
4119: -- l_wbs_temp_sup_emt --
4120: select wvt_child1.proj_element_id
4121: into l_wbs_temp_sup_emt
4122: from PA_PROJ_ELEMENT_VERSIONS wvt_child1
4123: where 1=1
4124: and wvt_child1.element_version_id = l_wbs_temp_parent;
4125:
4126: -- l_wbs_temp_sub_emt --
4125:
4126: -- l_wbs_temp_sub_emt --
4127: select wvt_child2.proj_element_id
4128: into l_wbs_temp_sub_emt
4129: from PA_PROJ_ELEMENT_VERSIONS wvt_child2
4130: where 1=1
4131: and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
4132:
4133: -- l_wbs_leaf_flag --
4223: ELSE
4224:
4225: select count(*)
4226: into l_struct_emt_id_count
4227: from pa_proj_element_versions
4228: where 1=1
4229: and element_version_id = P_WBS_VERSION_ID
4230: and rownum = 1;
4231:
4236: select
4237: distinct
4238: proj_element_id
4239: into l_struct_emt_id
4240: from pa_proj_element_versions
4241: where 1=1
4242: and element_version_id = P_WBS_VERSION_ID;
4243:
4244: