[Home] [Help]
74: from PJI_PJP_WBS_HEADER a
75: where a.project_id in ( select b.project_id from PJI_PJP_PROJ_BATCH_MAP b
76: where b.worker_id = p_worker_id );
77:
78: insert into PJI_PA_XBS_DENORM_T
79: (STRUCT_TYPE,
80: PRG_GROUP,
81: STRUCT_VERSION_ID,
82: SUP_PROJECT_ID,
116: CREATED_BY,
117: LAST_UPDATE_LOGIN,
118: STRUCT_EMT_ID,
119: RELATIONSHIP_TYPE
120: from PA_XBS_DENORM a
121: -- Bug 12763391 - a.struct_type join should be outside the bracket
122: -- join to PJI_PJP_PROJ_BATCH_MAP is not needed since PJI_PJP_WBS_HEADER_T has
123: -- the rows filtered based on join to PJI_PJP_PROJ_BATCH_MAP and the worker_id
124: where a.STRUCT_VERSION_ID in ( select b.wbs_VERSION_ID
170: STRUCT_EMT_ID,
171: RELATIONSHIP_TYPE
172: from PJI_XBS_DENORM a
173: where a.SUP_PROJECT_ID in ( select distinct b.SUP_PROJECT_ID
174: from PJI_PA_XBS_DENORM_T b, PJI_PJP_PROJ_BATCH_MAP c
175: where c.worker_id = p_worker_id
176: and b.sup_project_id = c.project_id);
177:
178: insert into PJI_XBS_DENORM_T
906: sum(pjp.CUSTOM14) CUSTOM14,
907: sum(pjp.CUSTOM15) CUSTOM15
908: from
909: (
910: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
911: -- get incremental task level amounts from source and
912: -- program rollup amounts from interim
913: to_char(null) LINE_TYPE,
914: wbs_hdr.WBS_VERSION_ID,
1022: PJI_FP_AGGR_PJP1 pjp1,
1023: -- Commenting and adding code for testing 0802
1024: --PJI_PJP_WBS_HEADER wbs_hdr,
1025: PJI_PJP_WBS_HEADER_T wbs_hdr,
1026: --PA_XBS_DENORM wbs,
1027: PJI_PA_XBS_DENORM_T wbs,
1028: --PJI_XBS_DENORM prg,
1029: PJI_XBS_DENORM_T prg,
1030: -- End of Commenting and adding code for testing 0802
1023: -- Commenting and adding code for testing 0802
1024: --PJI_PJP_WBS_HEADER wbs_hdr,
1025: PJI_PJP_WBS_HEADER_T wbs_hdr,
1026: --PA_XBS_DENORM wbs,
1027: PJI_PA_XBS_DENORM_T wbs,
1028: --PJI_XBS_DENORM prg,
1029: PJI_XBS_DENORM_T prg,
1030: -- End of Commenting and adding code for testing 0802
1031: (
2298: sum(pjp.CUSTOM14) CUSTOM14,
2299: sum(pjp.CUSTOM15) CUSTOM15
2300: from
2301: (
2302: select /*+ leading(prg, wbs, top_slice, pjp1, wbs_hdr ) use_nl(prg, wbs, top_slice, pjp1, wbs_hdr) index(pjp1 pji_fp_aggr_pjp1_n4) index(wbs pa_xbs_denorm_n5)
2303: index(wbs_hdr PJI_PJP_WBS_HEADER_N2) */ -- KPN change bug#12731239
2304: -- get incremental task level amounts from source and
2305: -- program rollup amounts from interim
2306: to_char(null) LINE_TYPE,
2413: pjp1.CUSTOM15
2414: from
2415: PJI_FP_AGGR_PJP1 pjp1,
2416: PJI_PJP_WBS_HEADER wbs_hdr,
2417: PA_XBS_DENORM wbs,
2418: PJI_XBS_DENORM prg,
2419: (
2420: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
2421: from DUAL union all
3612: null, null,
3613: 'MARK_AS_PROG_REP') ATTRIBUTE19, -- update header only
3614: 'CONVERTED_RBS_PRG_EVENT' ATTRIBUTE20 -- flg converted events
3615: from
3616: PA_XBS_DENORM prg,
3617: PA_PROJ_ELEMENTS emt,
3618: PJI_PJP_RBS_HEADER rbs_hdr,
3619: PJI_PA_PROJ_EVENTS_LOG log
3620: where
4515: l_created_by,
4516: l_last_update_login
4517: from
4518: PJI_PJP_PROJ_BATCH_MAP map,
4519: PA_XBS_DENORM pa
4520: where
4521: map.WORKER_ID = p_worker_id and
4522: pa.SUP_PROJECT_ID = map.PROJECT_ID and
4523: not exists (select /*+ index(pji, PJI_XBS_DENORM_N3) */
4552: CREATION_DATE,
4553: CREATED_BY,
4554: LAST_UPDATE_LOGIN
4555: )
4556: select /*+ ordered index(pa PA_XBS_DENORM_N3) use_hash(PA) */ /* Modified for Bug 7669026 */
4557: pa.STRUCT_TYPE,
4558: pa.PRG_GROUP,
4559: pa.STRUCT_VERSION_ID,
4560: pa.SUP_PROJECT_ID,
4574: l_created_by,
4575: l_last_update_login
4576: from
4577: PJI_PJP_PROJ_BATCH_MAP map,
4578: PA_XBS_DENORM pa
4579: where
4580: map.WORKER_ID = p_worker_id and
4581: pa.SUP_PROJECT_ID = map.PROJECT_ID and
4582: pa.STRUCT_TYPE = 'PRG' and
5044: 'N' FP_USAGE_FLAG
5045: from
5046: PJI_PJP_PROJ_BATCH_MAP map,
5047: PA_RBS_PRJ_ASSIGNMENTS asg,
5048: PA_XBS_DENORM den1,
5049: PA_XBS_DENORM den2
5050: where
5051: map.WORKER_ID = p_worker_id and
5052: asg.PROJECT_ID = map.PROJECT_ID and
5045: from
5046: PJI_PJP_PROJ_BATCH_MAP map,
5047: PA_RBS_PRJ_ASSIGNMENTS asg,
5048: PA_XBS_DENORM den1,
5049: PA_XBS_DENORM den2
5050: where
5051: map.WORKER_ID = p_worker_id and
5052: asg.PROJECT_ID = map.PROJECT_ID and
5053: asg.PROG_REP_USAGE_FLAG = 'Y' and
5797: 'X' SUB_LEAF_FLAG,
5798: 'X' RELATIONSHIP_TYPE,
5799: +1 SIGN
5800: from
5801: PA_XBS_DENORM wbs,
5802: PJI_PA_PROJ_EVENTS_LOG log
5803: where
5804: log.WORKER_ID = p_worker_id and
5805: log.EVENT_TYPE in ('WBS_CHANGE', 'WBS_PUBLISH') and
5836: wbs.STRUCT_TYPE = 'WBS' and
5837: wbs.SUP_PROJECT_ID = log.ATTRIBUTE1 and
5838: wbs.STRUCT_VERSION_ID = log.ATTRIBUTE2
5839: union all
5840: (select /*+ LEADING(LOG) USE_NL(LOG, PRG) INDEX(PRG PA_XBS_DENORM_N6) */ -- Bug 15983044
5841: distinct
5842: -1 EVENT_ID,
5843: prg.STRUCT_TYPE,
5844: -1 PRG_GROUP,
5857: 'X' SUB_LEAF_FLAG,
5858: prg.RELATIONSHIP_TYPE,
5859: +1 SIGN
5860: from
5861: PA_XBS_DENORM prg,
5862: PJI_PA_PROJ_EVENTS_LOG log
5863: where
5864: log.WORKER_ID = p_worker_id and
5865: log.EVENT_TYPE = 'PRG_CHANGE' and
5866: log.EVENT_OBJECT <> -1 and
5867: prg.STRUCT_TYPE = 'PRG' and
5868: prg.PRG_GROUP = log.EVENT_OBJECT -- Bug 15983044
5869: union
5870: select /*+ LEADING(LOG) USE_NL(LOG, PRG) INDEX(PRG PA_XBS_DENORM_N6) */ -- Bug 15983044
5871: distinct
5872: -1 EVENT_ID,
5873: prg.STRUCT_TYPE,
5874: -1 PRG_GROUP,
5887: 'X' SUB_LEAF_FLAG,
5888: prg.RELATIONSHIP_TYPE,
5889: +1 SIGN
5890: from
5891: PA_XBS_DENORM prg,
5892: PJI_PA_PROJ_EVENTS_LOG log
5893: where
5894: log.WORKER_ID = p_worker_id and
5895: log.EVENT_TYPE = 'PRG_CHANGE' and
6285: 'X' SUB_LEAF_FLAG,
6286: 'X' RELATIONSHIP_TYPE,
6287: +1 SIGN
6288: from
6289: PA_XBS_DENORM wbs
6290: where
6291: wbs.STRUCT_TYPE = 'WBS' and
6292: wbs.SUP_PROJECT_ID = g_project_id and
6293: wbs.STRUCT_VERSION_ID in (g_new_struct_version)
11774: sum(pjp.CUSTOM14) CUSTOM14,
11775: sum(pjp.CUSTOM15) CUSTOM15
11776: from
11777: (
11778: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
11779: -- get incremental task level amounts from source and
11780: -- program rollup amounts from interim
11781: to_char(null) LINE_TYPE,
11782: wbs_hdr.WBS_VERSION_ID,
11888: pjp1.CUSTOM15
11889: from
11890: PJI_FP_AGGR_PJP1_T pjp1,
11891: PJI_PJP_WBS_HEADER wbs_hdr,
11892: PA_XBS_DENORM wbs,
11893: PJI_XBS_DENORM prg,
11894: (
11895: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
11896: from DUAL union all
13387: sum(pjp1.CUSTOM14) CUSTOM14,
13388: sum(pjp1.CUSTOM15) CUSTOM15
13389: from
13390: (
13391: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */ -- smart slices
13392: decode(pjp1.RBS_AGGR_LEVEL ||
13393: decode(top_slice.INVERT_ID,
13394: 'PRJ', 'Y',
13395: decode(wbs.SUP_EMT_ID,
13606: pjp1.PLAN_VERSION_ID,
13607: pjp1.PLAN_TYPE_ID,
13608: pjp1.PLAN_TYPE_CODE
13609: ) pjp1,
13610: PA_XBS_DENORM wbs,
13611: (
13612: select 1 WBS_SUP_LEVEL,
13613: 'PRJ' INVERT_ID
13614: from DUAL
13842: sum(pjp1.CUSTOM14) CUSTOM14,
13843: sum(pjp1.CUSTOM15) CUSTOM15
13844: from
13845: (
13846: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */ -- smart slices
13847: decode(pjp1.RBS_AGGR_LEVEL ||
13848: decode(top_slice.INVERT_ID,
13849: 'PRJ', 'Y',
13850: decode(wbs.SUP_EMT_ID,
14061: pjp1.PLAN_VERSION_ID,
14062: pjp1.PLAN_TYPE_ID,
14063: pjp1.PLAN_TYPE_CODE
14064: ) pjp1,
14065: PA_XBS_DENORM wbs,
14066: (
14067: select 1 WBS_SUP_LEVEL,
14068: 'PRJ' INVERT_ID
14069: from DUAL
14463: sum(pjp.CUSTOM14) CUSTOM14,
14464: sum(pjp.CUSTOM15) CUSTOM15
14465: from
14466: (
14467: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
14468: -- get incremental task level amounts from source and
14469: -- program rollup amounts from interim
14470: /*wbs_hdr.WBS_VERSION_ID,
14471: 'LF' RELATIONSHIP_TYPE,
14533: pjp1.CUSTOM15
14534: from
14535: PJI_AC_AGGR_PJP1 pjp1,
14536: PJI_PJP_WBS_HEADER wbs_hdr,
14537: PA_XBS_DENORM wbs,
14538: PJI_XBS_DENORM prg,
14539: (
14540: select 1 WBS_SUP_LEVEL,
14541: 1 WBS_SUB_LEVEL,
15094: sum(pjp.CUSTOM14) CUSTOM14,
15095: sum(pjp.CUSTOM15) CUSTOM15
15096: from
15097: (
15098: select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
15099: -- get incremental task level amounts from source and
15100: -- program rollup amounts from interim
15101: wbs_hdr.WBS_VERSION_ID,
15102: 'LF' RELATIONSHIP_TYPE,
15164: pjp1.CUSTOM15
15165: from
15166: PJI_AC_AGGR_PJP1_T pjp1,
15167: PJI_PJP_WBS_HEADER wbs_hdr,
15168: PA_XBS_DENORM wbs,
15169: PJI_XBS_DENORM prg,
15170: (
15171: select 1 WBS_SUP_LEVEL,
15172: 1 WBS_SUB_LEVEL,
15587: union all
15588: select
15589: SUP_LEVEL
15590: from
15591: PA_XBS_DENORM den,
15592: PJI_PJP_PROJ_BATCH_MAP map
15593: where
15594: map.WORKER_ID = p_worker_id and
15595: den.STRUCT_TYPE = 'PRG' and
16531: from
16532: PJI_FP_AGGR_PJP1 pjp1,
16533: PJI_PJP_WBS_HEADER wbs_hdr,
16534: PA_PROJ_ELEMENTS prj_emt,
16535: PA_XBS_DENORM prg,
16536: PJI_XBS_DENORM_DELTA prg_delta,
16537: PJI_PJP_WBS_HEADER sup_wbs_hdr,
16538: PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
16539: PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
16631: ) pjp,
16632: PJI_PJP_PROJ_BATCH_MAP map,
16633: PA_PROJ_WORKPLAN_ATTR sup_wpa,
16634: PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
16635: PA_XBS_DENORM wbs,
16636: (
16637: select 1 WBS_SUP_LEVEL,
16638: 'PRJ' INVERT_ID
16639: from DUAL
16829: union all
16830: select
16831: SUP_LEVEL
16832: from
16833: PA_XBS_DENORM den,
16834: PJI_PJP_PROJ_BATCH_MAP map
16835: where
16836: map.WORKER_ID = p_worker_id and
16837: den.STRUCT_TYPE = 'PRG' and
17148: from
17149: PJI_AC_AGGR_PJP1 pjp1,
17150: PJI_PJP_WBS_HEADER wbs_hdr,
17151: PA_PROJ_ELEMENTS prj_emt,
17152: PA_XBS_DENORM prg,
17153: PJI_XBS_DENORM_DELTA prg_delta
17154: where
17155: prg.STRUCT_TYPE = 'PRG' and
17156: prg.SUP_LEVEL = l_level and
17185: ) pjp,
17186: PJI_PJP_PROJ_BATCH_MAP map,
17187: PA_PROJ_WORKPLAN_ATTR sup_wpa,
17188: PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
17189: PA_XBS_DENORM wbs,
17190: (
17191: select 1 WBS_SUP_LEVEL,
17192: 'PRJ' INVERT_ID
17193: from DUAL
18676: l_creation_date,
18677: l_created_by,
18678: l_last_update_login
18679: from
18680: PA_XBS_DENORM den
18681: where
18682: den.SUP_PROJECT_ID in
18683: (select
18684: ver.PROJECT_ID
18728: l_creation_date,
18729: l_created_by,
18730: l_last_update_login
18731: from
18732: PA_XBS_DENORM den,
18733: (
18734: select
18735: distinct
18736: EVENT_OBJECT,
18811: l_created_by,
18812: l_last_update_login
18813: from
18814: PJI_PJP_PROJ_BATCH_MAP map,
18815: PA_XBS_DENORM den
18816: where
18817: map.WORKER_ID = p_worker_id and
18818: den.SUP_PROJECT_ID = map.PROJECT_ID;
18819:
18895: CREATION_DATE,
18896: CREATED_BY,
18897: LAST_UPDATE_LOGIN
18898: )
18899: select /*+ index(den PA_XBS_DENORM_N2) */
18900: den.STRUCT_TYPE,
18901: den.PRG_GROUP,
18902: den.STRUCT_VERSION_ID,
18903: den.SUP_PROJECT_ID,
18916: l_creation_date,
18917: l_created_by,
18918: l_last_update_login
18919: from
18920: PA_XBS_DENORM den
18921: where
18922: den.STRUCT_TYPE in ('WBS', 'XBS') and
18923: den.SUP_PROJECT_ID = g_project_id and
18924: den.STRUCT_VERSION_ID in (g_old_struct_version,
18923: den.SUP_PROJECT_ID = g_project_id and
18924: den.STRUCT_VERSION_ID in (g_old_struct_version,
18925: g_new_struct_version)
18926: union all
18927: select /*+ index(den PA_XBS_DENORM_N4) */ /* modified the hint to use PA_XBS_DENORM_N4 instead of PA_XBS_DENORM_N3 */
18928: den.STRUCT_TYPE,
18929: den.PRG_GROUP,
18930: den.STRUCT_VERSION_ID,
18931: den.SUP_PROJECT_ID,
18944: l_creation_date,
18945: l_created_by,
18946: l_last_update_login
18947: from
18948: PA_XBS_DENORM den
18949: where
18950: den.STRUCT_TYPE = 'PRG' and
18951: den.SUP_PROJECT_ID in (select prg.SUP_PROJECT_ID
18952: from PJI_FP_AGGR_XBS_T prg