[Home] [Help]
1468: AND hd9.wp_flag = 'N'
1469: AND hd9.cb_flag || hd9.co_flag LIKE '%Y%'
1470: AND hd9.project_id = map.project_id
1471: AND not exists(select null
1472: from pji_fp_xbs_accum_f fact
1473: where fact.project_id = hd9.project_id
1474: and fact.plan_version_id = hd9.plan_version_id
1475: and fact.plan_type_id = hd9.plan_type_id
1476: and fact.rbs_version_id = ev.event_object)
1506: AND TO_NUMBER(ev.attribute1) = map.project_id
1507: AND hd1.project_id = map.project_id
1508: AND hd9.plan_type_id = hd1.plan_type_id
1509: AND not exists(select null
1510: from pji_fp_xbs_accum_f fact
1511: where fact.project_id = hd9.project_id
1512: and fact.plan_version_id = hd9.plan_version_id
1513: and fact.plan_type_id = hd9.plan_type_id
1514: and fact.rbs_version_id = ev.event_object)
5542: degree => PJI_UTILS.GET_DEGREE_OF_PARALLELISM());
5543:
5544: -- gather statistics for PJI metadata tables
5545: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
5546: tabname => 'PJI_FP_XBS_ACCUM_F',
5547: percent => 5,
5548: degree => PJI_UTILS.GET_DEGREE_OF_PARALLELISM());
5549:
5550:
5549:
5550:
5551: Forall i in x_row_id.first..x_row_id.last
5552: */
5553: INSERT /*+ append parallel(rl) */ INTO pji_fp_xbs_accum_f rl
5554: (
5555: PROJECT_ID
5556: , PROJECT_ORG_ID
5557: , PROJECT_ORGANIZATION_ID
5921: , rl.CUSTOM15
5922: , g_default_prg_level
5923: , rl.PLAN_TYPE_CODE
5924: FROM
5925: pji_fp_xbs_accum_f rl
5926: , pa_budget_versions bv -- @pjdev115 bv
5927: , pa_proj_fp_options fpo -- @pjdev115 fpo
5928: WHERE
5929: rl.project_id = bv.project_id
6622: worker_id
6623: , rl_rowid
6624: , tmp_rowid
6625: )
6626: SELECT /*+ ORDERED full(tmp) index(rl,PJI_FP_XBS_ACCUM_F_N1) */
6627: g_worker_id WORKER_ID
6628: , rl.ROWID rl_rowid
6629: , tmp.ROWID TMP_ROWID
6630: FROM
6628: , rl.ROWID rl_rowid
6629: , tmp.ROWID TMP_ROWID
6630: FROM
6631: pji_fp_aggr_pjp1 tmp
6632: , pji_fp_xbs_accum_f rl
6633: WHERE 1 = 1
6634: -- Removed outer join for bug 5927368
6635: -- AND tmp.WORKER_ID = g_worker_id
6636: -- AND tmp.PROJECT_ID = rl.PROJECT_ID (+)
6694:
6695:
6696:
6697: UPDATE /*+ ordered use_nl(rl) rowid(rl) */
6698: pji_fp_xbs_accum_f rl
6699: SET (
6700: rl.RAW_COST
6701: , rl.BRDN_COST
6702: , rl.REVENUE
6936: nvl(RECORD_TYPE, 'X') <> 'U';
6937: commit;
6938: --End of addition for bug 5927368
6939:
6940: INSERT INTO pji_fp_xbs_accum_f fact
6941: (
6942: PROJECT_ID
6943: , PROJECT_ORG_ID
6944: , PROJECT_ORGANIZATION_ID
7951: , CUSTOM14
7952: , CUSTOM15
7953: , g_default_prg_level
7954: ,PLAN_TYPE_CODE
7955: FROM pji_fp_xbs_accum_f
7956: WHERE plan_version_id = p_pln_ver_id
7957: );
7958:
7959: EXCEPTION
11392: ,g_default_prg_level PRG_LEVEL
11393: , fact.plan_type_code plan_type_code
11394: -- Bug 13107651 Reverted fix for bug 8510978
11395: FROM
11396: pji_fp_xbs_accum_f fact,
11397: pji_fm_extr_plnver4 ver
11398: WHERE fact.plan_version_id=ver.plan_version_id
11399: AND fact.plan_type_code = ver.plan_type_code /*4471527 */
11400: AND fact.project_id = ver.project_id
12651:
12652: l_process := PJI_PJP_SUM_MAIN.g_process || g_worker_id; /* Added for bug 7551819 */
12653:
12654: select count(*) into l_rec_exist -- Start for bug 14636607
12655: from pji_fp_xbs_accum_f
12656: where rownum=1;
12657:
12658: If l_rec_exist > 0 then -- End for bug 14636607
12659:
12695: if l_from_launch = 'N' then
12696: IF nvl(FND_PROFILE.VALUE('PJI_GATHERSTATS_FACT'),'N') = 'Y' THEN -- Added for bug 11789673 Base bug 9824355
12697: -- gather statistics for PJI metadata tables
12698: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
12699: tabname => 'PJI_FP_XBS_ACCUM_F',
12700: percent => 5,
12701: degree => PJI_UTILS.GET_DEGREE_OF_PARALLELISM());
12702: END IF;
12703: end if;
12722: /*Bug Fix 9212516*/
12723:
12724: WHILE (l_any_rows_to_process) LOOP
12725:
12726: MERGE INTO PJI_FP_XBS_ACCUM_F fact
12727: USING ( SELECT /*+ leading(tmp) rowid(tmp)*/
12728: tmp.worker_id
12729: , tmp.PROJECT_ID
12730: , tmp.PROJECT_ORG_ID
13160: -- Disable indexes on fact table
13161: FOR ind_lp IN (SELECT table_owner||'.'||index_name ind_name
13162: , partitioned
13163: FROM dba_indexes
13164: WHERE table_name = 'PJI_FP_XBS_ACCUM_F'
13165: AND table_owner = PJI_UTILS.GET_PJI_SCHEMA_NAME
13166: AND status <> 'UNUSABLE') LOOP
13167:
13168: IF ind_lp.partitioned = 'YES' THEN
13178: -- Put the indexes back
13179: FOR ind_lp IN (SELECT table_owner||'.'||index_name ind_name
13180: , partitioned
13181: FROM dba_indexes
13182: WHERE table_name = 'PJI_FP_XBS_ACCUM_F'
13183: AND table_owner = PJI_UTILS.GET_PJI_SCHEMA_NAME) LOOP
13184:
13185: IF ind_lp.partitioned = 'YES' THEN
13186: EXECUTE IMMEDIATE 'ALTER INDEX '||ind_lp.ind_name||' REBUILD PARTITION P'||g_worker_id||' PARALLEL';
13203: -- Put the indexes back
13204: FOR ind_lp IN (SELECT table_owner||'.'||index_name ind_name
13205: , partitioned
13206: FROM dba_indexes
13207: WHERE table_name = 'PJI_FP_XBS_ACCUM_F'
13208: AND table_owner = PJI_UTILS.GET_PJI_SCHEMA_NAME) LOOP
13209:
13210: IF ind_lp.partitioned = 'YES' THEN
13211: EXECUTE IMMEDIATE 'ALTER INDEX '||ind_lp.ind_name||' REBUILD PARTITION P'||g_worker_id||' PARALLEL';
13246:
13247: If x_row_id.count > 0 then
13248:
13249: Forall i in x_row_id.first..x_row_id.last
13250: INSERT into pji_fp_xbs_accum_f fact
13251: (
13252: fact.PROJECT_ID
13253: , fact.PROJECT_ORG_ID
13254: , fact.PROJECT_ORGANIZATION_ID
13573: close c1;
13574:
13575: /* Gather stats since we have inserted data into fact table */
13576: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
13577: tabname => 'PJI_FP_XBS_ACCUM_F',
13578: percent => 5,
13579: degree => PJI_UTILS.GET_DEGREE_OF_PARALLELISM());
13580:
13581: EXCEPTION
13614: BEGIN
13615:
13616: l_process := PJI_PJP_SUM_MAIN.g_process || g_worker_id; /* Added for bug 8416116 */
13617:
13618: MERGE INTO PJI_FP_XBS_ACCUM_F fact
13619: USING ( SELECT
13620: tmp.worker_id
13621: , tmp.PROJECT_ID
13622: , tmp.PROJECT_ORG_ID