[Home] [Help]
632: null, null,
633: 'MARK_AS_PROG_REP') ATTRIBUTE19, -- update header only
634: 'CONVERTED_RBS_PRG_EVENT' ATTRIBUTE20 -- flg converted events
635: from
636: PA_XBS_DENORM prg,
637: PA_PROJ_ELEMENTS emt,
638: PJI_PJP_RBS_HEADER rbs_hdr,
639: PJI_PA_PROJ_EVENTS_LOG log
640: where
1365: l_created_by,
1366: l_last_update_login
1367: from
1368: PJI_PJP_PROJ_BATCH_MAP map,
1369: PA_XBS_DENORM pa
1370: where
1371: map.WORKER_ID = p_worker_id and
1372: pa.SUP_PROJECT_ID = map.PROJECT_ID and
1373: not exists (select /*+ index(pji, PJI_XBS_DENORM_N3) */
1402: CREATION_DATE,
1403: CREATED_BY,
1404: LAST_UPDATE_LOGIN
1405: )
1406: select /*+ ordered index(pa PA_XBS_DENORM_N3) */
1407: pa.STRUCT_TYPE,
1408: pa.PRG_GROUP,
1409: pa.STRUCT_VERSION_ID,
1410: pa.SUP_PROJECT_ID,
1424: l_created_by,
1425: l_last_update_login
1426: from
1427: PJI_PJP_PROJ_BATCH_MAP map,
1428: PA_XBS_DENORM pa
1429: where
1430: map.WORKER_ID = p_worker_id and
1431: pa.SUP_PROJECT_ID = map.PROJECT_ID and
1432: pa.STRUCT_TYPE = 'PRG' and
1850: 'N' FP_USAGE_FLAG
1851: from
1852: PJI_PJP_PROJ_BATCH_MAP map,
1853: PA_RBS_PRJ_ASSIGNMENTS asg,
1854: PA_XBS_DENORM den1,
1855: PA_XBS_DENORM den2
1856: where
1857: map.WORKER_ID = p_worker_id and
1858: asg.PROJECT_ID = map.PROJECT_ID and
1851: from
1852: PJI_PJP_PROJ_BATCH_MAP map,
1853: PA_RBS_PRJ_ASSIGNMENTS asg,
1854: PA_XBS_DENORM den1,
1855: PA_XBS_DENORM den2
1856: where
1857: map.WORKER_ID = p_worker_id and
1858: asg.PROJECT_ID = map.PROJECT_ID and
1859: asg.PROG_REP_USAGE_FLAG = 'Y' and
2603: 'X' SUB_LEAF_FLAG,
2604: 'X' RELATIONSHIP_TYPE,
2605: +1 SIGN
2606: from
2607: PA_XBS_DENORM wbs,
2608: PJI_PA_PROJ_EVENTS_LOG log
2609: where
2610: log.WORKER_ID = p_worker_id and
2611: log.EVENT_TYPE in ('WBS_CHANGE', 'WBS_PUBLISH') and
2663: 'X' SUB_LEAF_FLAG,
2664: prg.RELATIONSHIP_TYPE,
2665: +1 SIGN
2666: from
2667: PA_XBS_DENORM prg,
2668: PJI_PA_PROJ_EVENTS_LOG log
2669: where
2670: log.WORKER_ID = p_worker_id and
2671: log.EVENT_TYPE = 'PRG_CHANGE' and
3031: 'X' SUB_LEAF_FLAG,
3032: 'X' RELATIONSHIP_TYPE,
3033: +1 SIGN
3034: from
3035: PA_XBS_DENORM wbs
3036: where
3037: wbs.STRUCT_TYPE = 'WBS' and
3038: wbs.SUP_PROJECT_ID = g_project_id and
3039: wbs.STRUCT_VERSION_ID in (g_new_struct_version)
8355: sum(pjp.CUSTOM14) CUSTOM14,
8356: sum(pjp.CUSTOM15) CUSTOM15
8357: from
8358: (
8359: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
8360: -- get incremental task level amounts from source and
8361: -- program rollup amounts from interim
8362: to_char(null) LINE_TYPE,
8363: wbs_hdr.WBS_VERSION_ID,
8469: pjp1.CUSTOM15
8470: from
8471: PJI_FP_AGGR_PJP1 pjp1,
8472: PJI_PJP_WBS_HEADER wbs_hdr,
8473: PA_XBS_DENORM wbs,
8474: PJI_XBS_DENORM prg,
8475: (
8476: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
8477: from DUAL union all
9661: sum(pjp.CUSTOM14) CUSTOM14,
9662: sum(pjp.CUSTOM15) CUSTOM15
9663: from
9664: (
9665: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
9666: -- get incremental task level amounts from source and
9667: -- program rollup amounts from interim
9668: to_char(null) LINE_TYPE,
9669: wbs_hdr.WBS_VERSION_ID,
9775: pjp1.CUSTOM15
9776: from
9777: PJI_FP_AGGR_PJP1_T pjp1,
9778: PJI_PJP_WBS_HEADER wbs_hdr,
9779: PA_XBS_DENORM wbs,
9780: PJI_XBS_DENORM prg,
9781: (
9782: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
9783: from DUAL union all
11271: sum(pjp1.CUSTOM14) CUSTOM14,
11272: sum(pjp1.CUSTOM15) CUSTOM15
11273: from
11274: (
11275: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */ -- smart slices
11276: decode(pjp1.RBS_AGGR_LEVEL ||
11277: decode(top_slice.INVERT_ID,
11278: 'PRJ', 'Y',
11279: decode(wbs.SUP_EMT_ID,
11490: pjp1.PLAN_VERSION_ID,
11491: pjp1.PLAN_TYPE_ID,
11492: pjp1.PLAN_TYPE_CODE
11493: ) pjp1,
11494: PA_XBS_DENORM wbs,
11495: (
11496: select 1 WBS_SUP_LEVEL,
11497: 'PRJ' INVERT_ID
11498: from DUAL
11726: sum(pjp1.CUSTOM14) CUSTOM14,
11727: sum(pjp1.CUSTOM15) CUSTOM15
11728: from
11729: (
11730: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */ -- smart slices
11731: decode(pjp1.RBS_AGGR_LEVEL ||
11732: decode(top_slice.INVERT_ID,
11733: 'PRJ', 'Y',
11734: decode(wbs.SUP_EMT_ID,
11945: pjp1.PLAN_VERSION_ID,
11946: pjp1.PLAN_TYPE_ID,
11947: pjp1.PLAN_TYPE_CODE
11948: ) pjp1,
11949: PA_XBS_DENORM wbs,
11950: (
11951: select 1 WBS_SUP_LEVEL,
11952: 'PRJ' INVERT_ID
11953: from DUAL
12343: sum(pjp.CUSTOM14) CUSTOM14,
12344: sum(pjp.CUSTOM15) CUSTOM15
12345: from
12346: (
12347: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
12348: -- get incremental task level amounts from source and
12349: -- program rollup amounts from interim
12350: wbs_hdr.WBS_VERSION_ID,
12351: 'LF' RELATIONSHIP_TYPE,
12413: pjp1.CUSTOM15
12414: from
12415: PJI_AC_AGGR_PJP1 pjp1,
12416: PJI_PJP_WBS_HEADER wbs_hdr,
12417: PA_XBS_DENORM wbs,
12418: PJI_XBS_DENORM prg,
12419: (
12420: select 1 WBS_SUP_LEVEL,
12421: 1 WBS_SUB_LEVEL,
12974: sum(pjp.CUSTOM14) CUSTOM14,
12975: sum(pjp.CUSTOM15) CUSTOM15
12976: from
12977: (
12978: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
12979: -- get incremental task level amounts from source and
12980: -- program rollup amounts from interim
12981: wbs_hdr.WBS_VERSION_ID,
12982: 'LF' RELATIONSHIP_TYPE,
13044: pjp1.CUSTOM15
13045: from
13046: PJI_AC_AGGR_PJP1_T pjp1,
13047: PJI_PJP_WBS_HEADER wbs_hdr,
13048: PA_XBS_DENORM wbs,
13049: PJI_XBS_DENORM prg,
13050: (
13051: select 1 WBS_SUP_LEVEL,
13052: 1 WBS_SUB_LEVEL,
13467: union all
13468: select
13469: SUP_LEVEL
13470: from
13471: PA_XBS_DENORM den,
13472: PJI_PJP_PROJ_BATCH_MAP map
13473: where
13474: map.WORKER_ID = p_worker_id and
13475: den.STRUCT_TYPE = 'PRG' and
14407: from
14408: PJI_FP_AGGR_PJP1 pjp1,
14409: PJI_PJP_WBS_HEADER wbs_hdr,
14410: PA_PROJ_ELEMENTS prj_emt,
14411: PA_XBS_DENORM prg,
14412: PJI_XBS_DENORM_DELTA prg_delta,
14413: PJI_PJP_WBS_HEADER sup_wbs_hdr,
14414: PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
14415: PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
14507: ) pjp,
14508: PJI_PJP_PROJ_BATCH_MAP map,
14509: PA_PROJ_WORKPLAN_ATTR sup_wpa,
14510: PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
14511: PA_XBS_DENORM wbs,
14512: (
14513: select 1 WBS_SUP_LEVEL,
14514: 'PRJ' INVERT_ID
14515: from DUAL
14705: union all
14706: select
14707: SUP_LEVEL
14708: from
14709: PA_XBS_DENORM den,
14710: PJI_PJP_PROJ_BATCH_MAP map
14711: where
14712: map.WORKER_ID = p_worker_id and
14713: den.STRUCT_TYPE = 'PRG' and
15019: from
15020: PJI_AC_AGGR_PJP1 pjp1,
15021: PJI_PJP_WBS_HEADER wbs_hdr,
15022: PA_PROJ_ELEMENTS prj_emt,
15023: PA_XBS_DENORM prg,
15024: PJI_XBS_DENORM_DELTA prg_delta
15025: where
15026: prg.STRUCT_TYPE = 'PRG' and
15027: prg.SUP_LEVEL = l_level and
15056: ) pjp,
15057: PJI_PJP_PROJ_BATCH_MAP map,
15058: PA_PROJ_WORKPLAN_ATTR sup_wpa,
15059: PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
15060: PA_XBS_DENORM wbs,
15061: (
15062: select 1 WBS_SUP_LEVEL,
15063: 'PRJ' INVERT_ID
15064: from DUAL
16496: l_creation_date,
16497: l_created_by,
16498: l_last_update_login
16499: from
16500: PA_XBS_DENORM den
16501: where
16502: den.SUP_PROJECT_ID in
16503: (select
16504: ver.PROJECT_ID
16548: l_creation_date,
16549: l_created_by,
16550: l_last_update_login
16551: from
16552: PA_XBS_DENORM den,
16553: (
16554: select
16555: distinct
16556: EVENT_OBJECT,
16631: l_created_by,
16632: l_last_update_login
16633: from
16634: PJI_PJP_PROJ_BATCH_MAP map,
16635: PA_XBS_DENORM den
16636: where
16637: map.WORKER_ID = p_worker_id and
16638: den.SUP_PROJECT_ID = map.PROJECT_ID;
16639:
16714: CREATION_DATE,
16715: CREATED_BY,
16716: LAST_UPDATE_LOGIN
16717: )
16718: select /*+ index(den PA_XBS_DENORM_N2) */
16719: den.STRUCT_TYPE,
16720: den.PRG_GROUP,
16721: den.STRUCT_VERSION_ID,
16722: den.SUP_PROJECT_ID,
16735: l_creation_date,
16736: l_created_by,
16737: l_last_update_login
16738: from
16739: PA_XBS_DENORM den
16740: where
16741: den.STRUCT_TYPE in ('WBS', 'XBS') and
16742: den.SUP_PROJECT_ID = g_project_id and
16743: den.STRUCT_VERSION_ID in (g_old_struct_version,
16742: den.SUP_PROJECT_ID = g_project_id and
16743: den.STRUCT_VERSION_ID in (g_old_struct_version,
16744: g_new_struct_version)
16745: union all
16746: select /*+ index(den PA_XBS_DENORM_N3) */
16747: den.STRUCT_TYPE,
16748: den.PRG_GROUP,
16749: den.STRUCT_VERSION_ID,
16750: den.SUP_PROJECT_ID,
16763: l_creation_date,
16764: l_created_by,
16765: l_last_update_login
16766: from
16767: PA_XBS_DENORM den
16768: where
16769: den.STRUCT_TYPE = 'PRG' and
16770: den.SUP_PROJECT_ID in (select prg.SUP_PROJECT_ID
16771: from PJI_FP_AGGR_XBS_T prg