[Home] [Help]
126: )
127: and a.struct_type||'' = 'WBS'||'';
128: -- End Bug 12763391
129:
130: insert into PJI_XBS_DENORM_T
131: (STRUCT_TYPE,
132: PRG_GROUP,
133: STRUCT_VERSION_ID,
134: SUP_PROJECT_ID,
168: CREATED_BY,
169: LAST_UPDATE_LOGIN,
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);
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
179: (STRUCT_TYPE,
180: PRG_GROUP,
181: STRUCT_VERSION_ID,
182: SUP_PROJECT_ID,
216: CREATED_BY,
217: LAST_UPDATE_LOGIN,
218: STRUCT_EMT_ID,
219: RELATIONSHIP_TYPE
220: from PJI_XBS_DENORM a
221: where a.SUP_PROJECT_ID in ( select b.PROJECT_ID
222: from PJI_PJP_PROJ_BATCH_MAP b
223: where b.worker_id = p_worker_id )
224: and not exists ( select 'x' from PJI_PJP_PROJ_BATCH_MAP c
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: (
1032: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
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: (
1032: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
1033: from DUAL union all
1181: PJI_FP_AGGR_PJP1 pjp1,
1182: -- Commenting and adding code for testing 0802
1183: --PJI_PJP_WBS_HEADER wbs_hdr,
1184: PJI_PJP_WBS_HEADER_T wbs_hdr,
1185: PJI_XBS_DENORM_T prg,
1186: --PJI_XBS_DENORM prg,
1187: -- End of Commenting and adding code for testing 0802
1188: (
1189: select 'Y' CB_FLAG,
1182: -- Commenting and adding code for testing 0802
1183: --PJI_PJP_WBS_HEADER wbs_hdr,
1184: PJI_PJP_WBS_HEADER_T wbs_hdr,
1185: PJI_XBS_DENORM_T prg,
1186: --PJI_XBS_DENORM prg,
1187: -- End of Commenting and adding code for testing 0802
1188: (
1189: select 'Y' CB_FLAG,
1190: 'N' CO_FLAG,
1336: PJI_PA_PROJ_EVENTS_LOG log,
1337: -- Commenting and adding code for testing 0802
1338: PJI_PJP_WBS_HEADER_T wbs_hdr,
1339: --PJI_PJP_WBS_HEADER wbs_hdr,
1340: PJI_XBS_DENORM_DELTA wbs,
1341: --PJI_XBS_DENORM prg,
1342: PJI_XBS_DENORM_t prg,
1343: -- End of Commenting and adding code for testing 0802
1344: PJI_FP_XBS_ACCUM_F fpr,
1337: -- Commenting and adding code for testing 0802
1338: PJI_PJP_WBS_HEADER_T wbs_hdr,
1339: --PJI_PJP_WBS_HEADER wbs_hdr,
1340: PJI_XBS_DENORM_DELTA wbs,
1341: --PJI_XBS_DENORM prg,
1342: PJI_XBS_DENORM_t prg,
1343: -- End of Commenting and adding code for testing 0802
1344: PJI_FP_XBS_ACCUM_F fpr,
1345: (
1338: PJI_PJP_WBS_HEADER_T wbs_hdr,
1339: --PJI_PJP_WBS_HEADER wbs_hdr,
1340: PJI_XBS_DENORM_DELTA wbs,
1341: --PJI_XBS_DENORM prg,
1342: PJI_XBS_DENORM_t prg,
1343: -- End of Commenting and adding code for testing 0802
1344: PJI_FP_XBS_ACCUM_F fpr,
1345: (
1346: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
1428: 'Y' PUSHUP_FLAG
1429: from
1430: PJI_PJP_PROJ_BATCH_MAP map,
1431: -- Commenting and adding code for testing 0802
1432: PJI_XBS_DENORM_T prg,
1433: --PJI_XBS_DENORM prg,
1434: -- End of Commenting and adding code for testing 0802
1435: (
1436: select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
1429: from
1430: PJI_PJP_PROJ_BATCH_MAP map,
1431: -- Commenting and adding code for testing 0802
1432: PJI_XBS_DENORM_T prg,
1433: --PJI_XBS_DENORM prg,
1434: -- End of Commenting and adding code for testing 0802
1435: (
1436: select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
1437: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
1437: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
1438: select 'A' INVERT_ID, 'LF' INVERT_VALUE from dual union all
1439: select 'A' INVERT_ID, 'LW' INVERT_VALUE from dual
1440: ) invert,
1441: PJI_XBS_DENORM_DELTA prg_delta
1442: where
1443: prg.STRUCT_TYPE = 'PRG' and
1444: prg.SUB_ROLLUP_ID is not null and
1445: prg.SUB_LEVEL = lv_level and
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
2422: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
2567: pjp1.CUSTOM15
2568: from
2569: PJI_FP_AGGR_PJP1 pjp1,
2570: PJI_PJP_WBS_HEADER wbs_hdr,
2571: PJI_XBS_DENORM prg,
2572: (
2573: select 'Y' CB_FLAG,
2574: 'N' CO_FLAG,
2575: -3 PLAN_VERSION_ID
2718: wbs.SIGN * fpr.CUSTOM15 CUSTOM15
2719: from
2720: PJI_PA_PROJ_EVENTS_LOG log,
2721: PJI_PJP_WBS_HEADER wbs_hdr,
2722: PJI_XBS_DENORM_DELTA wbs,
2723: PJI_XBS_DENORM prg,
2724: PJI_FP_XBS_ACCUM_F fpr,
2725: (
2726: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
2719: from
2720: PJI_PA_PROJ_EVENTS_LOG log,
2721: PJI_PJP_WBS_HEADER wbs_hdr,
2722: PJI_XBS_DENORM_DELTA wbs,
2723: PJI_XBS_DENORM prg,
2724: PJI_FP_XBS_ACCUM_F fpr,
2725: (
2726: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
2727: from DUAL union all
2790: (wbs.SUP_LEVEL = 1 and
2791: wbs.SUB_LEVEL = 1))
2792: ) pjp,
2793: (
2794: select /*+ leading( prg, map) USE_NL(prg_delta) use_nl(invert) INDEX(prg_delta PJI_XBS_DENORM_DELTA_N1) */ -- KPN change bug#12731239
2795: prg.SUP_PROJECT_ID,
2796: map.PROJECT_ORG_ID SUP_PROJECT_ORG_ID,
2797: map.PROJECT_ORGANIZATION_ID SUP_PROJECT_ORGANIZATION_ID,
2798: prg.SUP_ID,
2807: 'LF', 'N') WP_FLAG,
2808: 'Y' PUSHUP_FLAG
2809: from
2810: PJI_PJP_PROJ_BATCH_MAP map,
2811: PJI_XBS_DENORM prg,
2812: (
2813: select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
2814: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
2815: select 'A' INVERT_ID, 'LF' INVERT_VALUE from dual union all
2814: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
2815: select 'A' INVERT_ID, 'LF' INVERT_VALUE from dual union all
2816: select 'A' INVERT_ID, 'LW' INVERT_VALUE from dual
2817: ) invert,
2818: PJI_XBS_DENORM_DELTA prg_delta
2819: where
2820: prg.STRUCT_TYPE = 'PRG' and
2821: prg.SUB_ROLLUP_ID is not null and
2822: prg.SUB_LEVEL = lv_level and
4471: l_count := 0;
4472:
4473: if (l_extraction_type = 'FULL') then
4474:
4475: insert into PJI_XBS_DENORM den_i
4476: (
4477: STRUCT_TYPE,
4478: PRG_GROUP,
4479: STRUCT_VERSION_ID,
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) */
4524: 1
4525: from PJI_XBS_DENORM pji
4526: where pji.STRUCT_TYPE = 'PRG' and
4527: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
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) */
4524: 1
4525: from PJI_XBS_DENORM pji
4526: where pji.STRUCT_TYPE = 'PRG' and
4527: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
4528: pji.SUP_PROJECT_ID = pa.SUP_PROJECT_ID);
4529:
4530: l_count := l_count + sql%rowcount;
4531:
4532: elsif (l_extraction_type = 'INCREMENTAL') then
4533:
4534: insert into PJI_XBS_DENORM den_i
4535: (
4536: STRUCT_TYPE,
4537: PRG_GROUP,
4538: STRUCT_VERSION_ID,
4580: map.WORKER_ID = p_worker_id and
4581: pa.SUP_PROJECT_ID = map.PROJECT_ID and
4582: pa.STRUCT_TYPE = 'PRG' and
4583: pa.SUB_EMT_ID = pa.SUB_ROLLUP_ID and
4584: not exists (select /*+ index(pji, PJI_XBS_DENORM_N3) use_hash(PJI) */ /* Modified for Bug 7669026 */
4585: 1
4586: from PJI_XBS_DENORM pji
4587: where pji.STRUCT_TYPE = 'PRG' and
4588: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
4582: pa.STRUCT_TYPE = 'PRG' and
4583: pa.SUB_EMT_ID = pa.SUB_ROLLUP_ID and
4584: not exists (select /*+ index(pji, PJI_XBS_DENORM_N3) use_hash(PJI) */ /* Modified for Bug 7669026 */
4585: 1
4586: from PJI_XBS_DENORM pji
4587: where pji.STRUCT_TYPE = 'PRG' and
4588: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
4589: pji.SUP_PROJECT_ID = pa.SUP_PROJECT_ID);
4590:
5734: l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
5735:
5736: if (l_extraction_type = 'INCREMENTAL') then
5737:
5738: insert into PJI_XBS_DENORM_DELTA delta_i
5739: (
5740: WORKER_ID,
5741: EVENT_ID,
5742: STRUCT_TYPE,
5827: 'X' SUB_LEAF_FLAG,
5828: 'X' RELATIONSHIP_TYPE,
5829: -1 SIGN
5830: from
5831: PJI_XBS_DENORM wbs,
5832: PJI_PA_PROJ_EVENTS_LOG log
5833: where
5834: log.WORKER_ID = p_worker_id and
5835: log.EVENT_TYPE in ('WBS_CHANGE', 'WBS_PUBLISH') and
5896: log.EVENT_OBJECT <> -1 and
5897: prg.STRUCT_TYPE = 'PRG' and
5898: prg.PRG_GROUP = log.ATTRIBUTE1 ) -- Bug 15983044
5899: union all
5900: ( select /*+ LEADING(LOG) USE_NL(LOG, PRG) INDEX(PRG PJI_XBS_DENORM_N9) */ -- Bug 15983044
5901: distinct
5902: -1 EVENT_ID,
5903: prg.STRUCT_TYPE,
5904: -1 PRG_GROUP,
5917: 'X' SUB_LEAF_FLAG,
5918: prg.RELATIONSHIP_TYPE,
5919: -1 SIGN
5920: from
5921: PJI_XBS_DENORM prg,
5922: PJI_PA_PROJ_EVENTS_LOG log
5923: where
5924: log.WORKER_ID = p_worker_id and
5925: log.EVENT_TYPE = 'PRG_CHANGE' and
5926: log.EVENT_OBJECT <> -1 and
5927: prg.STRUCT_TYPE = 'PRG' and
5928: prg.PRG_GROUP = log.EVENT_OBJECT
5929: union
5930: select /*+ LEADING(LOG) USE_NL(LOG, PRG) INDEX(PRG PJI_XBS_DENORM_N9) */ -- Bug 15983044
5931: distinct
5932: -1 EVENT_ID,
5933: prg.STRUCT_TYPE,
5934: -1 PRG_GROUP,
5947: 'X' SUB_LEAF_FLAG,
5948: prg.RELATIONSHIP_TYPE,
5949: -1 SIGN
5950: from
5951: PJI_XBS_DENORM prg,
5952: PJI_PA_PROJ_EVENTS_LOG log
5953: where
5954: log.WORKER_ID = p_worker_id and
5955: log.EVENT_TYPE = 'PRG_CHANGE' and
6069: 'N' ATTRIBUTE18, -- PROG_REP_USAGE_FLAG
6070: null ATTRIBUTE19, -- update header only
6071: 'NEW_CONVERTED_RBS_PRG_EVENT' ATTRIBUTE20 -- flg converted events
6072: from
6073: PJI_XBS_DENORM_DELTA prg,
6074: PJI_PJP_RBS_HEADER sup_rbs_hdr,
6075: PA_PROJ_ELEMENT_VERSIONS sub_ver,
6076: PA_RBS_VERSIONS_B hdr
6077: where
6225: else -- online mode
6226:
6227: -- get WBS delta for online processing
6228:
6229: insert into PJI_XBS_DENORM_DELTA_T
6230: (
6231: WORKER_ID,
6232: STRUCT_TYPE,
6233: PRG_GROUP,
6310: 'X' SUB_LEAF_FLAG,
6311: 'X' RELATIONSHIP_TYPE,
6312: -1 SIGN
6313: from
6314: PJI_XBS_DENORM wbs
6315: where
6316: wbs.STRUCT_TYPE = 'WBS' and
6317: wbs.SUP_PROJECT_ID = g_project_id and
6318: wbs.STRUCT_VERSION_ID in (g_old_struct_version)
11036: STEP_NAME like 'ROLLUP_FPR_WBS%';
11037:
11038: if (l_count = 0) then
11039:
11040: select /*+ ordered index(den PJI_XBS_DENORM_N3) use_hash(den) */ /* Modified for Bug 7669026 */
11041: nvl(max(den.SUP_LEVEL), 0)
11042: into
11043: l_level
11044: from
11042: into
11043: l_level
11044: from
11045: PJI_PJP_PROJ_BATCH_MAP map,
11046: PJI_XBS_DENORM den
11047: where
11048: map.WORKER_ID = p_worker_id and
11049: den.STRUCT_TYPE = 'PRG' and
11050: den.SUB_LEVEL = den.SUP_LEVEL and
11151: into
11152: l_level
11153: from
11154: PJI_FM_EXTR_PLNVER3_T ver3,
11155: PJI_XBS_DENORM den
11156: where
11157: den.STRUCT_TYPE = 'PRG' and
11158: den.SUB_LEVEL = den.SUP_LEVEL and
11159: den.SUP_PROJECT_ID = ver3.PROJECT_ID;
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
11897: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
11951: wbs_hdr.CO_FLAG = fin_plan.CO (+) and
11952: wbs.SUP_LEVEL = top_slice.WBS_SUP_LEVEL (+) and
11953: wbs.SUB_LEVEL <> top_slice.WBS_SUB_LEVEL (+)
11954: union all
11955: select /*+ ordered index(prg pji_xbs_denorm_n3) */ -- Bug 10632614
11956: -- get incremental project level amounts from source
11957: to_char(null) LINE_TYPE,
11958: wbs_hdr.WBS_VERSION_ID,
11959: decode(wbs_hdr.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE,
12042: pjp1.CUSTOM15
12043: from
12044: PJI_FP_AGGR_PJP1_T pjp1,
12045: PJI_PJP_WBS_HEADER wbs_hdr,
12046: PJI_XBS_DENORM prg,
12047: (
12048: select 'Y' CB_FLAG,
12049: 'N' CO_FLAG,
12050: -3 PLAN_VERSION_ID
12095: union all
12096: select /*+ ordered
12097: index(log PA_PJI_PROJ_EVENTS_LOG_N2)
12098: index(fpr PJI_FP_XBS_ACCUM_F_N1)
12099: index(prg pji_xbs_denorm_n3) */ -- Bug 10632614
12100: -- get delta task level amounts from Reporting Lines
12101: to_char(null) LINE_TYPE,
12102: wbs_hdr.WBS_VERSION_ID,
12103: decode(wbs_hdr.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE,
12195: wbs.SIGN * fpr.CUSTOM15 CUSTOM15
12196: from
12197: PA_PJI_PROJ_EVENTS_LOG log,
12198: PJI_PJP_WBS_HEADER wbs_hdr,
12199: PJI_XBS_DENORM_DELTA_T wbs,
12200: PJI_XBS_DENORM prg,
12201: PJI_FP_XBS_ACCUM_F fpr,
12202: (
12203: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
12196: from
12197: PA_PJI_PROJ_EVENTS_LOG log,
12198: PJI_PJP_WBS_HEADER wbs_hdr,
12199: PJI_XBS_DENORM_DELTA_T wbs,
12200: PJI_XBS_DENORM prg,
12201: PJI_FP_XBS_ACCUM_F fpr,
12202: (
12203: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
12204: from DUAL union all
12269: union all
12270: select /*+ ordered
12271: index(log PA_PJI_PROJ_EVENTS_LOG_N2)
12272: index(fpr PJI_FP_XBS_ACCUM_F_N1)
12273: index(prg pji_xbs_denorm_n3) */ -- Bug 10632614
12274: -- Baseline reversals and fact amounts (-3 slice)
12275: -- Current Original reversals and fact amounts (-4 slice)
12276: -- Part 1
12277: to_char(null) LINE_TYPE,
12626: from
12627: PA_PJI_PROJ_EVENTS_LOG log,
12628: PJI_PJP_WBS_HEADER wbs_hdr,
12629: -- Changes done for bug 7354982.
12630: --PJI_XBS_DENORM prg,
12631: PJI_FP_XBS_ACCUM_F fpr,
12632: PJI_XBS_DENORM prg
12633: where
12634: prg.STRUCT_TYPE = 'PRG' and
12628: PJI_PJP_WBS_HEADER wbs_hdr,
12629: -- Changes done for bug 7354982.
12630: --PJI_XBS_DENORM prg,
12631: PJI_FP_XBS_ACCUM_F fpr,
12632: PJI_XBS_DENORM prg
12633: where
12634: prg.STRUCT_TYPE = 'PRG' and
12635: prg.SUP_LEVEL = l_level and
12636: prg.SUB_LEVEL = l_level and
12664: union all
12665: select /*+ ordered
12666: index(log PA_PJI_PROJ_EVENTS_LOG_N2)
12667: index(fpr PJI_FP_XBS_ACCUM_F_N1)
12668: index(prg pji_xbs_denorm_n3) */ -- Bug 10632614
12669: -- Baseline reversals and fact amounts (-3 slice)
12670: -- Current Original reversals and fact amounts (-4 slice)
12671: -- Part 2
12672: to_char(null) LINE_TYPE,
13021: from
13022: PA_PJI_PROJ_EVENTS_LOG log,
13023: PJI_PJP_WBS_HEADER wbs_hdr,
13024: -- Changes done for bug 7354982.
13025: --PJI_XBS_DENORM prg,
13026: PJI_FP_XBS_ACCUM_F fpr,
13027: PJI_XBS_DENORM prg
13028: where
13029: prg.STRUCT_TYPE = 'PRG' and
13023: PJI_PJP_WBS_HEADER wbs_hdr,
13024: -- Changes done for bug 7354982.
13025: --PJI_XBS_DENORM prg,
13026: PJI_FP_XBS_ACCUM_F fpr,
13027: PJI_XBS_DENORM prg
13028: where
13029: prg.STRUCT_TYPE = 'PRG' and
13030: prg.SUP_LEVEL = l_level and
13031: prg.SUB_LEVEL = l_level and
13054: prg.struct_version_id is NULL -- added for bug 5882260
13055: ) pjp,
13056: (
13057: select /*+ ordered
13058: index(prg PJI_XBS_DENORM_N3)
13059: index(prj PA_PROJECTS_U1) */
13060: prg.SUP_PROJECT_ID,
13061: prj.ORG_ID SUP_PROJECT_ORG_ID,
13062: prj.CARRYING_OUT_ORGANIZATION_ID SUP_PROJECT_ORGANIZATION_ID,
13071: 'LW', 'Y',
13072: 'LF', 'N') WP_FLAG,
13073: 'Y' PUSHUP_FLAG
13074: from
13075: PJI_XBS_DENORM prg,
13076: PA_PROJECTS_ALL prj,
13077: (
13078: select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
13079: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
14194: STEP_NAME like 'ROLLUP_ACR_WBS%';
14195:
14196: if (l_count = 0) then
14197:
14198: select /*+ ordered index(den PJI_XBS_DENORM_N3) use_hash(den) */ /* Modified for Bug 7669026 */
14199: nvl(max(den.SUP_LEVEL), 0)
14200: into
14201: l_level
14202: from
14200: into
14201: l_level
14202: from
14203: PJI_PJP_PROJ_BATCH_MAP map,
14204: PJI_XBS_DENORM den
14205: where
14206: map.WORKER_ID = p_worker_id and
14207: den.STRUCT_TYPE = 'PRG' and
14208: den.SUB_LEVEL = den.SUP_LEVEL and
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,
14542: 'PRJ' INVERT_ID
14629: pjp1.CUSTOM15
14630: from
14631: PJI_AC_AGGR_PJP1 pjp1,
14632: PJI_PJP_WBS_HEADER wbs_hdr,
14633: PJI_XBS_DENORM prg
14634: where
14635: prg.STRUCT_TYPE = 'PRG' and
14636: prg.SUP_LEVEL = l_level and
14637: prg.SUB_LEVEL = l_level and
14722: wbs.SIGN * acr.CUSTOM15 CUSTOM15
14723: from
14724: PJI_PA_PROJ_EVENTS_LOG log,
14725: PJI_PJP_WBS_HEADER wbs_hdr,
14726: PJI_XBS_DENORM_DELTA wbs,
14727: PJI_XBS_DENORM prg,
14728: PJI_AC_XBS_ACCUM_F acr,
14729: (
14730: select 1 WBS_SUP_LEVEL,
14723: from
14724: PJI_PA_PROJ_EVENTS_LOG log,
14725: PJI_PJP_WBS_HEADER wbs_hdr,
14726: PJI_XBS_DENORM_DELTA wbs,
14727: PJI_XBS_DENORM prg,
14728: PJI_AC_XBS_ACCUM_F acr,
14729: (
14730: select 1 WBS_SUP_LEVEL,
14731: 1 WBS_SUB_LEVEL,
14779: prg.RELATIONSHIP_TYPE RELATIONSHIP_TYPE,
14780: 'Y' PUSHUP_FLAG
14781: from
14782: PJI_PJP_PROJ_BATCH_MAP map,
14783: PJI_XBS_DENORM prg,
14784: PJI_XBS_DENORM_DELTA prg_delta
14785: where
14786: prg.STRUCT_TYPE = 'PRG' and
14787: prg.RELATIONSHIP_TYPE = 'LF' and
14780: 'Y' PUSHUP_FLAG
14781: from
14782: PJI_PJP_PROJ_BATCH_MAP map,
14783: PJI_XBS_DENORM prg,
14784: PJI_XBS_DENORM_DELTA prg_delta
14785: where
14786: prg.STRUCT_TYPE = 'PRG' and
14787: prg.RELATIONSHIP_TYPE = 'LF' and
14788: prg.SUB_ROLLUP_ID is not null and
14889: into
14890: l_level
14891: from
14892: PJI_FM_EXTR_PLNVER3_T ver3,
14893: PJI_XBS_DENORM den
14894: where
14895: den.STRUCT_TYPE = 'PRG' and
14896: den.SUB_LEVEL = den.SUP_LEVEL and
14897: den.SUP_PROJECT_ID = ver3.PROJECT_ID;
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,
15173: 'PRJ' INVERT_ID
15260: pjp1.CUSTOM15
15261: from
15262: PJI_AC_AGGR_PJP1_T pjp1,
15263: PJI_PJP_WBS_HEADER wbs_hdr,
15264: PJI_XBS_DENORM prg
15265: where
15266: prg.STRUCT_TYPE = 'PRG' and
15267: prg.SUP_LEVEL = l_level and
15268: prg.SUB_LEVEL = l_level and
15354: wbs.SIGN * acr.CUSTOM15 CUSTOM15
15355: from
15356: PA_PJI_PROJ_EVENTS_LOG log,
15357: PJI_PJP_WBS_HEADER wbs_hdr,
15358: PJI_XBS_DENORM_DELTA_T wbs,
15359: PJI_XBS_DENORM prg,
15360: PJI_AC_XBS_ACCUM_F acr,
15361: (
15362: select 1 WBS_SUP_LEVEL,
15355: from
15356: PA_PJI_PROJ_EVENTS_LOG log,
15357: PJI_PJP_WBS_HEADER wbs_hdr,
15358: PJI_XBS_DENORM_DELTA_T wbs,
15359: PJI_XBS_DENORM prg,
15360: PJI_AC_XBS_ACCUM_F acr,
15361: (
15362: select 1 WBS_SUP_LEVEL,
15363: 1 WBS_SUB_LEVEL,
15410: prg.SUB_ROLLUP_ID,
15411: prg.RELATIONSHIP_TYPE RELATIONSHIP_TYPE,
15412: 'Y' PUSHUP_FLAG
15413: from
15414: PJI_XBS_DENORM prg,
15415: PA_PROJECTS_ALL map
15416: where
15417: l_level > 1 and
15418: prg.STRUCT_TYPE = 'PRG' and
15548: nvl(max(den.SUP_LEVEL), 0)
15549: into
15550: l_level
15551: from
15552: PJI_XBS_DENORM_DELTA den
15553: where
15554: den.WORKER_ID = p_worker_id and
15555: den.STRUCT_TYPE = 'PRG';
15556:
15579: (
15580: select
15581: SUP_LEVEL
15582: from
15583: PJI_XBS_DENORM_DELTA
15584: where
15585: worker_id = p_worker_id and
15586: STRUCT_TYPE = 'PRG'
15587: union all
15600: (
15601: select
15602: 1
15603: from
15604: PJI_XBS_DENORM_DELTA
15605: where
15606: WORKER_ID = p_worker_id and
15607: ROWNUM = 1
15608: )
16128: prg.SIGN * fpr.CUSTOM13 CUSTOM13,
16129: prg.SIGN * fpr.CUSTOM14 CUSTOM14,
16130: prg.SIGN * fpr.CUSTOM15 CUSTOM15
16131: from
16132: PJI_XBS_DENORM_DELTA prg,
16133: PA_PROJ_ELEMENTS prj_emt,
16134: PJI_PJP_WBS_HEADER wbs_hdr,
16135: PJI_FP_XBS_ACCUM_F fpr,
16136: PJI_PJP_WBS_HEADER sup_wbs_hdr,
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,
16540: (
16790: nvl(max(den.SUP_LEVEL), 0)
16791: into
16792: l_level
16793: from
16794: PJI_XBS_DENORM_DELTA den
16795: where
16796: den.WORKER_ID = p_worker_id and
16797: den.STRUCT_TYPE = 'PRG';
16798:
16821: (
16822: select
16823: SUP_LEVEL
16824: from
16825: PJI_XBS_DENORM_DELTA
16826: where
16827: worker_id = p_worker_id and
16828: STRUCT_TYPE = 'PRG'
16829: union all
16842: (
16843: select
16844: 1
16845: from
16846: PJI_XBS_DENORM_DELTA
16847: where
16848: WORKER_ID = p_worker_id and
16849: ROWNUM = 1
16850: )
17069: prg.SIGN * acr.CUSTOM13 CUSTOM13,
17070: prg.SIGN * acr.CUSTOM14 CUSTOM14,
17071: prg.SIGN * acr.CUSTOM15 CUSTOM15
17072: from
17073: PJI_XBS_DENORM_DELTA prg,
17074: PA_PROJ_ELEMENTS prj_emt,
17075: PJI_PJP_WBS_HEADER wbs_hdr,
17076: PJI_AC_XBS_ACCUM_F acr
17077: where
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
17157: nvl(prg.SUB_ROLLUP_ID,
18579: END;
18580:
18581:
18582:
18583: delete from PJI_XBS_DENORM
18584: where SUP_PROJECT_ID in
18585: (select
18586: ver.PROJECT_ID
18587: from
18605: log.EVENT_OBJECT <> -1));
18606:
18607: l_count := l_count + sql%rowcount;
18608:
18609: delete from PJI_XBS_DENORM
18610: where SUP_PROJECT_ID in (select log.ATTRIBUTE1
18611: from PJI_PA_PROJ_EVENTS_LOG log
18612: where log.WORKER_ID = p_worker_id and
18613: log.EVENT_TYPE = 'PRG_CHANGE' and
18614: log.EVENT_OBJECT = -1);
18615:
18616: l_count := l_count + sql%rowcount;
18617:
18618: delete from PJI_XBS_DENORM
18619: where STRUCT_TYPE in ('WBS', 'XBS') and
18620: STRUCT_VERSION_ID in (select
18621: decode(invert.INVERT_ID,
18622: 1, log.EVENT_OBJECT,
18633: 'WBS_PUBLISH'));
18634:
18635: l_count := l_count + sql%rowcount;
18636:
18637: insert into PJI_XBS_DENORM
18638: (
18639: STRUCT_TYPE,
18640: PRG_GROUP,
18641: STRUCT_VERSION_ID,
18760: WHEN NO_DATA_FOUND then null;
18761: END;
18762:
18763: delete
18764: from PJI_XBS_DENORM
18765: where SUP_PROJECT_ID in (select map.PROJECT_ID
18766: from PJI_PJP_PROJ_BATCH_MAP map
18767: where WORKER_ID = p_worker_id);
18768:
18767: where WORKER_ID = p_worker_id);
18768:
18769: l_count := l_count + sql%rowcount;
18770:
18771: insert into PJI_XBS_DENORM
18772: (
18773: STRUCT_TYPE,
18774: PRG_GROUP,
18775: STRUCT_VERSION_ID,
18856:
18857: l_count := 0;
18858: /* For 14040849 removed the hint in the below delete */
18859: delete
18860: from PJI_XBS_DENORM den
18861: where den.STRUCT_TYPE in ('WBS', 'XBS') and
18862: den.SUP_PROJECT_ID = g_project_id and
18863: den.STRUCT_VERSION_ID in (g_old_struct_version,
18864: g_new_struct_version);
18864: g_new_struct_version);
18865:
18866: l_count := l_count + sql%rowcount;
18867:
18868: delete /*+ index(den PJI_XBS_DENORM_N4) */ /* modified the hint to use PJI_XBS_DENORM_N4 instead of PJI_XBS_DENORM_N3 */
18869: from PJI_XBS_DENORM den
18870: where den.STRUCT_TYPE = 'PRG' and
18871: den.SUP_PROJECT_ID in (select prg.SUP_PROJECT_ID
18872: from PJI_FP_AGGR_XBS_T prg
18865:
18866: l_count := l_count + sql%rowcount;
18867:
18868: delete /*+ index(den PJI_XBS_DENORM_N4) */ /* modified the hint to use PJI_XBS_DENORM_N4 instead of PJI_XBS_DENORM_N3 */
18869: from PJI_XBS_DENORM den
18870: where den.STRUCT_TYPE = 'PRG' and
18871: den.SUP_PROJECT_ID in (select prg.SUP_PROJECT_ID
18872: from PJI_FP_AGGR_XBS_T prg
18873: where prg.STRUCT_TYPE = 'PRG');
18873: where prg.STRUCT_TYPE = 'PRG');
18874:
18875: l_count := l_count + sql%rowcount;
18876:
18877: insert into PJI_XBS_DENORM
18878: (
18879: STRUCT_TYPE,
18880: PRG_GROUP,
18881: STRUCT_VERSION_ID,
18962: -- g_new_struct_version);
18963:
18964: end if; --Uncommented for bug 14571067
18965:
18966: delete from PJI_XBS_DENORM_DELTA_T;
18967:
18968: end if;
18969:
18970: end UPDATE_XBS_DENORM;
20373: l_tab11 := 'Y';
20374: end if;
20375: if l_tab12 = 'N' then
20376: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
20377: 'PJI_XBS_DENORM_DELTA',
20378: 'PARTITION',
20379: 'P' || p_worker_id);
20380: l_tab12 := 'Y';
20381: end if;
20455: commit;
20456:
20457: else -- online mode
20458:
20459: delete from PJI_XBS_DENORM_DELTA_T;
20460:
20461: delete from PJI_FP_AGGR_PJP1_T;
20462:
20463: delete from PJI_AC_AGGR_PJP1_T;