[Home] [Help]
233:
234:
235: -- rollup project hiearchy
236:
237: insert into PJI_FP_AGGR_PJP1
238: (
239: WORKER_ID,
240: RECORD_TYPE,
241: PRG_LEVEL,
1018: pjp1.CUSTOM13,
1019: pjp1.CUSTOM14,
1020: pjp1.CUSTOM15
1021: from
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,
1177: pjp1.CUSTOM13,
1178: pjp1.CUSTOM14,
1179: pjp1.CUSTOM15
1180: from
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,
1625: STEP_SEQ = lv_level_seq;
1626:
1627: -- rollup project hiearchy
1628:
1629: insert into PJI_FP_AGGR_PJP1
1630: (
1631: WORKER_ID,
1632: RECORD_TYPE,
1633: PRG_LEVEL,
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,
2411: pjp1.CUSTOM13,
2412: pjp1.CUSTOM14,
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: (
2476: wbs_hdr.CO_FLAG = fin_plan.CO (+) and
2477: wbs.SUP_LEVEL = top_slice.WBS_SUP_LEVEL (+) and
2478: wbs.SUB_LEVEL <> top_slice.WBS_SUB_LEVEL (+)
2479: union all
2480: select /*+ leading(prg, pjp1, wbs_hdr) use_nl(prg pjp1 wbs_hdr) index(pjp1 pji_fp_aggr_pjp1_n4)*/ -- KPN change bug#12731239
2481: -- get incremental project level amounts from source
2482: to_char(null) LINE_TYPE,
2483: wbs_hdr.WBS_VERSION_ID,
2484: decode(wbs_hdr.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE,
2565: pjp1.CUSTOM13,
2566: pjp1.CUSTOM14,
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,
8544:
8545: end if; -- Partial Refresh Performance Improvement
8546:
8547:
8548: insert into PJI_FP_AGGR_PJP1 pjp1_i
8549: (
8550: WORKER_ID,
8551: RECORD_TYPE,
8552: PRG_LEVEL,
10451: end if;
10452:
10453: l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
10454:
10455: insert /*+ parallel(pjp1_i) */ into PJI_FP_AGGR_PJP1 pjp1_i /* Added for bug 13030627 */
10456: (
10457: WORKER_ID,
10458: RECORD_TYPE,
10459: PRG_LEVEL,
10758: sum(pjp1.CUSTOM13) CUSTOM13,
10759: sum(pjp1.CUSTOM14) CUSTOM14,
10760: sum(pjp1.CUSTOM15) CUSTOM15
10761: from
10762: PJI_FP_AGGR_PJP1 pjp1,
10763: (
10764: select
10765: distinct
10766: to_number(log.EVENT_OBJECT) RBS_VERSION_ID,
10871: sum(pjp1.CUSTOM13) CUSTOM13,
10872: sum(pjp1.CUSTOM14) CUSTOM14,
10873: sum(pjp1.CUSTOM15) CUSTOM15
10874: from
10875: PJI_FP_AGGR_PJP1 pjp1,
10876: PJI_FM_EXTR_PLNVER4 ver,
10877: (
10878: select
10879: distinct
11161: while (l_level > 0) loop
11162:
11163: -- rollup project hiearchy
11164:
11165: insert into PJI_FP_AGGR_PJP1_T
11166: (
11167: WORKER_ID,
11168: RECORD_TYPE,
11169: PRG_LEVEL,
11886: pjp1.CUSTOM13,
11887: pjp1.CUSTOM14,
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: (
12040: pjp1.CUSTOM13,
12041: pjp1.CUSTOM14,
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,
13231: end if;
13232:
13233: l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
13234:
13235: insert into PJI_FP_AGGR_PJP1 pjp1_i
13236: (
13237: WORKER_ID,
13238: RECORD_TYPE,
13239: PRG_LEVEL,
13552: sum(pjp1.CUSTOM13) CUSTOM13,
13553: sum(pjp1.CUSTOM14) CUSTOM14,
13554: sum(pjp1.CUSTOM15) CUSTOM15
13555: from
13556: PJI_FP_AGGR_PJP1 pjp1,
13557: PJI_ROLLUP_LEVEL_STATUS ss,
13558: PJI_PJP_RBS_HEADER rbs_hdr,
13559: PJI_PJP_WBS_HEADER wbs_hdr,
13560: PA_RBS_DENORM rbs
13685: commit;
13686:
13687: else -- online mode
13688:
13689: insert into PJI_FP_AGGR_PJP1_T pjp1_i
13690: (
13691: WORKER_ID,
13692: RECORD_TYPE,
13693: PRG_LEVEL,
14007: sum(pjp1.CUSTOM13) CUSTOM13,
14008: sum(pjp1.CUSTOM14) CUSTOM14,
14009: sum(pjp1.CUSTOM15) CUSTOM15
14010: from
14011: PJI_FP_AGGR_PJP1_T pjp1,
14012: PJI_ROLLUP_LEVEL_STATUS ss,
14013: PJI_PJP_RBS_HEADER rbs_hdr,
14014: PJI_PJP_WBS_HEADER wbs_hdr,
14015: PA_RBS_DENORM rbs
15646: l_level);
15647:
15648: /* Commented for bug 7551819 */
15649: -- insert /*+ parallel(pjp1_in)
15650: -- noappend(pjp1_in) */ into PJI_FP_AGGR_PJP1 pjp1_in -- changed for bug 5927368
15651: /* ( WORKER_ID,
15652: RECORD_TYPE,
15653: PRG_LEVEL,
15654: LINE_TYPE,
16528: pjp1.CUSTOM13,
16529: pjp1.CUSTOM14,
16530: pjp1.CUSTOM15
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,
17639: PJI_PROCESS_UTIL.EXECUTE_AGGREGATE_PLAN_DATA(p_worker_id);
17640:
17641: /* Commented for bug 7551819 */
17642: -- insert /*+ parallel(pjp1_in)
17643: -- noappend(pjp1_in) */ into PJI_FP_AGGR_PJP1 pjp1_in -- changed for bug 5927368
17644: /* (
17645: WORKER_ID,
17646: RECORD_TYPE,
17647: PRG_LEVEL,
17794: sum(pjp1.CUSTOM13) CUSTOM13,
17795: sum(pjp1.CUSTOM14) CUSTOM14,
17796: sum(pjp1.CUSTOM15) CUSTOM15
17797: from
17798: PJI_FP_AGGR_PJP1 pjp1
17799: where
17800: pjp1.WORKER_ID = p_worker_id
17801: group by
17802: pjp1.WORKER_ID,
18082: TYPE row_id_tab_type IS TABLE OF rowid index by binary_integer;
18083: x_row_id row_id_tab_type;
18084:
18085: cursor c1 is
18086: select rowid from pji_fp_aggr_pjp1
18087: where worker_id = p_worker_id
18088: and nvl(LINE_TYPE, 'X') <> 'AGGR_PLAN';
18089: -- end: bug 7146014
18090: begin
18115: fetch c1 bulk collect into x_row_id limit 500000;
18116:
18117: If x_row_id.count > 0 then
18118: Forall j in x_row_id.first..x_row_id.last
18119: delete from pji_fp_aggr_pjp1
18120: where worker_id = p_worker_id
18121: and rowid = x_row_id(j);
18122:
18123: commit;
18134: -- End: bug 7146014
18135:
18136: /* Commented for bug 7146014
18137: delete
18138: from PJI_FP_AGGR_PJP1 pjp1
18139: where pjp1.WORKER_ID = p_worker_id and
18140: nvl(pjp1.LINE_TYPE, 'X') <> 'AGGR_PLAN';
18141: */
18142: delete
19397:
19398: /* Added for bug 13897252 starts */
19399: IF PJI_UTILS.GET_PARAMETER('PJP_SIN_PRG') = 'Y' then
19400: PJI_PJP_PRG_PERF_ALL.GATHER_TEMP_STATS(p_ownname => l_pji_schema
19401: ,p_tabname => 'PJI_FP_AGGR_PJP1'
19402: ,p_partname => 'P'||p_worker_id
19403: ,p_percent => 10);
19404:
19405: END IF; /* Added for bug 13897252 ends */
19626: where WORKER_ID = p_worker_id and
19627: PJI_PROJECT_STATUS is null and
19628: PROJECT_ID in (select distinct
19629: PROJECT_ID
19630: from PJI_FP_AGGR_PJP1
19631: where WORKER_ID = p_worker_id
19632: and PLAN_VERSION_ID = -1);
19633:
19634: end if;
20273: l_tab17 VARCHAR2(1) := 'N';
20274: l_tab18 VARCHAR2(1) := 'N'; -- Added for bug 13897252
20275: l_tab19 VARCHAR2(1) := 'N'; -- Added for bug 13897252
20276: l_debugdate VARCHAR2(20);
20277: l_pji_fp_aggr_pjp1_n1 dba_indexes.status%TYPE := 'VALID';
20278: /* Added for bug#11789673 , ends here */
20279:
20280: begin
20281:
20345: l_tab7 := 'Y';
20346: end if;
20347: if l_tab8 = 'N' then
20348: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
20349: 'PJI_FP_AGGR_PJP1',
20350: 'PARTITION',
20351: 'P' || p_worker_id);
20352: l_tab8 := 'Y';
20353: end if;
20407: 'P' || p_worker_id);
20408: l_tab16 := 'Y';
20409: end if;
20410: if l_tab17 = 'N' then
20411: select status into l_pji_fp_aggr_pjp1_n1 from sys.dba_indexes
20412: where index_name = 'PJI_FP_AGGR_PJP1_N1';
20413: if l_pji_fp_aggr_pjp1_n1 <> 'VALID' then
20414: /* Added for bug 9676993 */
20415: execute immediate 'alter index '||l_schema||'.'||'PJI_FP_AGGR_PJP1_N1 rebuild';
20408: l_tab16 := 'Y';
20409: end if;
20410: if l_tab17 = 'N' then
20411: select status into l_pji_fp_aggr_pjp1_n1 from sys.dba_indexes
20412: where index_name = 'PJI_FP_AGGR_PJP1_N1';
20413: if l_pji_fp_aggr_pjp1_n1 <> 'VALID' then
20414: /* Added for bug 9676993 */
20415: execute immediate 'alter index '||l_schema||'.'||'PJI_FP_AGGR_PJP1_N1 rebuild';
20416: /* Added for bug 9676993 */
20409: end if;
20410: if l_tab17 = 'N' then
20411: select status into l_pji_fp_aggr_pjp1_n1 from sys.dba_indexes
20412: where index_name = 'PJI_FP_AGGR_PJP1_N1';
20413: if l_pji_fp_aggr_pjp1_n1 <> 'VALID' then
20414: /* Added for bug 9676993 */
20415: execute immediate 'alter index '||l_schema||'.'||'PJI_FP_AGGR_PJP1_N1 rebuild';
20416: /* Added for bug 9676993 */
20417: end if;
20411: select status into l_pji_fp_aggr_pjp1_n1 from sys.dba_indexes
20412: where index_name = 'PJI_FP_AGGR_PJP1_N1';
20413: if l_pji_fp_aggr_pjp1_n1 <> 'VALID' then
20414: /* Added for bug 9676993 */
20415: execute immediate 'alter index '||l_schema||'.'||'PJI_FP_AGGR_PJP1_N1 rebuild';
20416: /* Added for bug 9676993 */
20417: end if;
20418: l_tab17 := 'Y';
20419: end if;
20445: END LOOP;
20446: /* Modified for bug#11789673 , ends here */
20447:
20448: /* Added for bug 9736812 */
20449: /* execute immediate 'alter index '||l_schema||'.'||'PJI_FP_AGGR_PJP1_N1 rebuild'; */ /* commented for bug#11789673 */
20450: /* Added for bug 9736812 */
20451:
20452: PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_PJP_SUM_ROLLUP.CLEANUP(p_worker_id);'); -- Bug#5171542
20453: -- commented below bcoz the global index is modified to local for bug15983044
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;
20464:
20465: end if;