[Home] [Help]
1321: l_count := 0;
1322:
1323: if (l_extraction_type = 'FULL') then
1324:
1325: insert into PJI_XBS_DENORM den_i
1326: (
1327: STRUCT_TYPE,
1328: PRG_GROUP,
1329: STRUCT_VERSION_ID,
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) */
1374: 1
1375: from PJI_XBS_DENORM pji
1376: where pji.STRUCT_TYPE = 'PRG' and
1377: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
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) */
1374: 1
1375: from PJI_XBS_DENORM pji
1376: where pji.STRUCT_TYPE = 'PRG' and
1377: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
1378: pji.SUP_PROJECT_ID = pa.SUP_PROJECT_ID);
1379:
1380: l_count := l_count + sql%rowcount;
1381:
1382: elsif (l_extraction_type = 'INCREMENTAL') then
1383:
1384: insert into PJI_XBS_DENORM den_i
1385: (
1386: STRUCT_TYPE,
1387: PRG_GROUP,
1388: STRUCT_VERSION_ID,
1430: map.WORKER_ID = p_worker_id and
1431: pa.SUP_PROJECT_ID = map.PROJECT_ID and
1432: pa.STRUCT_TYPE = 'PRG' and
1433: pa.SUB_EMT_ID = pa.SUB_ROLLUP_ID and
1434: not exists (select /*+ index(pji, PJI_XBS_DENORM_N3) */
1435: 1
1436: from PJI_XBS_DENORM pji
1437: where pji.STRUCT_TYPE = 'PRG' and
1438: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
1432: pa.STRUCT_TYPE = 'PRG' and
1433: pa.SUB_EMT_ID = pa.SUB_ROLLUP_ID and
1434: not exists (select /*+ index(pji, PJI_XBS_DENORM_N3) */
1435: 1
1436: from PJI_XBS_DENORM pji
1437: where pji.STRUCT_TYPE = 'PRG' and
1438: pji.SUB_EMT_ID = pji.SUB_ROLLUP_ID and
1439: pji.SUP_PROJECT_ID = pa.SUP_PROJECT_ID);
1440:
2540: l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
2541:
2542: if (l_extraction_type = 'INCREMENTAL') then
2543:
2544: insert into PJI_XBS_DENORM_DELTA delta_i
2545: (
2546: WORKER_ID,
2547: EVENT_ID,
2548: STRUCT_TYPE,
2633: 'X' SUB_LEAF_FLAG,
2634: 'X' RELATIONSHIP_TYPE,
2635: -1 SIGN
2636: from
2637: PJI_XBS_DENORM wbs,
2638: PJI_PA_PROJ_EVENTS_LOG log
2639: where
2640: log.WORKER_ID = p_worker_id and
2641: log.EVENT_TYPE in ('WBS_CHANGE', 'WBS_PUBLISH') and
2693: 'X' SUB_LEAF_FLAG,
2694: prg.RELATIONSHIP_TYPE,
2695: -1 SIGN
2696: from
2697: PJI_XBS_DENORM prg,
2698: PJI_PA_PROJ_EVENTS_LOG log
2699: where
2700: log.WORKER_ID = p_worker_id and
2701: log.EVENT_TYPE = 'PRG_CHANGE' and
2815: 'N' ATTRIBUTE18, -- PROG_REP_USAGE_FLAG
2816: null ATTRIBUTE19, -- update header only
2817: 'NEW_CONVERTED_RBS_PRG_EVENT' ATTRIBUTE20 -- flg converted events
2818: from
2819: PJI_XBS_DENORM_DELTA prg,
2820: PJI_PJP_RBS_HEADER sup_rbs_hdr,
2821: PA_PROJ_ELEMENT_VERSIONS sub_ver,
2822: PA_RBS_VERSIONS_B hdr
2823: where
2971: else -- online mode
2972:
2973: -- get WBS delta for online processing
2974:
2975: insert into PJI_XBS_DENORM_DELTA_T
2976: (
2977: WORKER_ID,
2978: STRUCT_TYPE,
2979: PRG_GROUP,
3056: 'X' SUB_LEAF_FLAG,
3057: 'X' RELATIONSHIP_TYPE,
3058: -1 SIGN
3059: from
3060: PJI_XBS_DENORM wbs
3061: where
3062: wbs.STRUCT_TYPE = 'WBS' and
3063: wbs.SUP_PROJECT_ID = g_project_id and
3064: wbs.STRUCT_VERSION_ID in (g_old_struct_version)
7611: STEP_NAME like 'ROLLUP_FPR_WBS%';
7612:
7613: if (l_count = 0) then
7614:
7615: select /*+ ordered index(den PJI_XBS_DENORM_N3) */
7616: nvl(max(den.SUP_LEVEL), 0)
7617: into
7618: l_level
7619: from
7617: into
7618: l_level
7619: from
7620: PJI_PJP_PROJ_BATCH_MAP map,
7621: PJI_XBS_DENORM den
7622: where
7623: map.WORKER_ID = p_worker_id and
7624: den.STRUCT_TYPE = 'PRG' and
7625: den.SUB_LEVEL = den.SUP_LEVEL and
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
8478: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
8623: pjp1.CUSTOM15
8624: from
8625: PJI_FP_AGGR_PJP1 pjp1,
8626: PJI_PJP_WBS_HEADER wbs_hdr,
8627: PJI_XBS_DENORM prg,
8628: (
8629: select 'Y' CB_FLAG,
8630: 'N' CO_FLAG,
8631: -3 PLAN_VERSION_ID
8774: wbs.SIGN * fpr.CUSTOM15 CUSTOM15
8775: from
8776: PJI_PA_PROJ_EVENTS_LOG log,
8777: PJI_PJP_WBS_HEADER wbs_hdr,
8778: PJI_XBS_DENORM_DELTA wbs,
8779: PJI_XBS_DENORM prg,
8780: PJI_FP_XBS_ACCUM_F fpr,
8781: (
8782: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
8775: from
8776: PJI_PA_PROJ_EVENTS_LOG log,
8777: PJI_PJP_WBS_HEADER wbs_hdr,
8778: PJI_XBS_DENORM_DELTA wbs,
8779: PJI_XBS_DENORM prg,
8780: PJI_FP_XBS_ACCUM_F fpr,
8781: (
8782: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
8783: from DUAL union all
8863: 'LF', 'N') WP_FLAG,
8864: 'Y' PUSHUP_FLAG
8865: from
8866: PJI_PJP_PROJ_BATCH_MAP map,
8867: PJI_XBS_DENORM prg,
8868: (
8869: select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
8870: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
8871: select 'A' INVERT_ID, 'LF' INVERT_VALUE from dual union all
8870: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
8871: select 'A' INVERT_ID, 'LF' INVERT_VALUE from dual union all
8872: select 'A' INVERT_ID, 'LW' INVERT_VALUE from dual
8873: ) invert,
8874: PJI_XBS_DENORM_DELTA prg_delta
8875: where
8876: prg.STRUCT_TYPE = 'PRG' and
8877: prg.SUB_ROLLUP_ID is not null and
8878: prg.SUB_LEVEL = l_level and
9032: else -- online mode
9033:
9034: -- rollup just WBS for online processing
9035:
9036: select /*+ ordered use_nl(den) index(den PJI_XBS_DENORM_N3) */
9037: nvl(max(den.SUP_LEVEL), 0)
9038: into
9039: l_level
9040: from
9038: into
9039: l_level
9040: from
9041: PJI_FM_EXTR_PLNVER3_T ver3,
9042: PJI_XBS_DENORM den
9043: where
9044: den.STRUCT_TYPE = 'PRG' and
9045: den.SUB_LEVEL = den.SUP_LEVEL and
9046: den.SUP_PROJECT_ID = ver3.PROJECT_ID;
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
9784: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
9929: pjp1.CUSTOM15
9930: from
9931: PJI_FP_AGGR_PJP1_T pjp1,
9932: PJI_PJP_WBS_HEADER wbs_hdr,
9933: PJI_XBS_DENORM prg,
9934: (
9935: select 'Y' CB_FLAG,
9936: 'N' CO_FLAG,
9937: -3 PLAN_VERSION_ID
10081: wbs.SIGN * fpr.CUSTOM15 CUSTOM15
10082: from
10083: PA_PJI_PROJ_EVENTS_LOG log,
10084: PJI_PJP_WBS_HEADER wbs_hdr,
10085: PJI_XBS_DENORM_DELTA_T wbs,
10086: PJI_XBS_DENORM prg,
10087: PJI_FP_XBS_ACCUM_F fpr,
10088: (
10089: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
10082: from
10083: PA_PJI_PROJ_EVENTS_LOG log,
10084: PJI_PJP_WBS_HEADER wbs_hdr,
10085: PJI_XBS_DENORM_DELTA_T wbs,
10086: PJI_XBS_DENORM prg,
10087: PJI_FP_XBS_ACCUM_F fpr,
10088: (
10089: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
10090: from DUAL union all
10511: from
10512: PA_PJI_PROJ_EVENTS_LOG log,
10513: PJI_PJP_WBS_HEADER wbs_hdr,
10514: -- Changes done for bug 7354982.
10515: --PJI_XBS_DENORM prg,
10516: PJI_FP_XBS_ACCUM_F fpr,
10517: PJI_XBS_DENORM prg
10518: where
10519: prg.STRUCT_TYPE = 'PRG' and
10513: PJI_PJP_WBS_HEADER wbs_hdr,
10514: -- Changes done for bug 7354982.
10515: --PJI_XBS_DENORM prg,
10516: PJI_FP_XBS_ACCUM_F fpr,
10517: PJI_XBS_DENORM prg
10518: where
10519: prg.STRUCT_TYPE = 'PRG' and
10520: prg.SUP_LEVEL = l_level and
10521: prg.SUB_LEVEL = l_level and
10905: from
10906: PA_PJI_PROJ_EVENTS_LOG log,
10907: PJI_PJP_WBS_HEADER wbs_hdr,
10908: -- Changes done for bug 7354982.
10909: --PJI_XBS_DENORM prg,
10910: PJI_FP_XBS_ACCUM_F fpr,
10911: PJI_XBS_DENORM prg
10912: where
10913: prg.STRUCT_TYPE = 'PRG' and
10907: PJI_PJP_WBS_HEADER wbs_hdr,
10908: -- Changes done for bug 7354982.
10909: --PJI_XBS_DENORM prg,
10910: PJI_FP_XBS_ACCUM_F fpr,
10911: PJI_XBS_DENORM prg
10912: where
10913: prg.STRUCT_TYPE = 'PRG' and
10914: prg.SUP_LEVEL = l_level and
10915: prg.SUB_LEVEL = l_level and
10938: prg.struct_version_id is NULL -- added for bug 5882260
10939: ) pjp,
10940: (
10941: select /*+ ordered
10942: index(prg PJI_XBS_DENORM_N3)
10943: index(prj PA_PROJECTS_U1) */
10944: prg.SUP_PROJECT_ID,
10945: prj.ORG_ID SUP_PROJECT_ORG_ID,
10946: prj.CARRYING_OUT_ORGANIZATION_ID SUP_PROJECT_ORGANIZATION_ID,
10955: 'LW', 'Y',
10956: 'LF', 'N') WP_FLAG,
10957: 'Y' PUSHUP_FLAG
10958: from
10959: PJI_XBS_DENORM prg,
10960: PA_PROJECTS_ALL prj,
10961: (
10962: select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
10963: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
12078: STEP_NAME like 'ROLLUP_ACR_WBS%';
12079:
12080: if (l_count = 0) then
12081:
12082: select /*+ ordered index(den PJI_XBS_DENORM_N3) */
12083: nvl(max(den.SUP_LEVEL), 0)
12084: into
12085: l_level
12086: from
12084: into
12085: l_level
12086: from
12087: PJI_PJP_PROJ_BATCH_MAP map,
12088: PJI_XBS_DENORM den
12089: where
12090: map.WORKER_ID = p_worker_id and
12091: den.STRUCT_TYPE = 'PRG' and
12092: den.SUB_LEVEL = den.SUP_LEVEL and
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,
12422: 'PRJ' INVERT_ID
12509: pjp1.CUSTOM15
12510: from
12511: PJI_AC_AGGR_PJP1 pjp1,
12512: PJI_PJP_WBS_HEADER wbs_hdr,
12513: PJI_XBS_DENORM prg
12514: where
12515: prg.STRUCT_TYPE = 'PRG' and
12516: prg.SUP_LEVEL = l_level and
12517: prg.SUB_LEVEL = l_level and
12602: wbs.SIGN * acr.CUSTOM15 CUSTOM15
12603: from
12604: PJI_PA_PROJ_EVENTS_LOG log,
12605: PJI_PJP_WBS_HEADER wbs_hdr,
12606: PJI_XBS_DENORM_DELTA wbs,
12607: PJI_XBS_DENORM prg,
12608: PJI_AC_XBS_ACCUM_F acr,
12609: (
12610: select 1 WBS_SUP_LEVEL,
12603: from
12604: PJI_PA_PROJ_EVENTS_LOG log,
12605: PJI_PJP_WBS_HEADER wbs_hdr,
12606: PJI_XBS_DENORM_DELTA wbs,
12607: PJI_XBS_DENORM prg,
12608: PJI_AC_XBS_ACCUM_F acr,
12609: (
12610: select 1 WBS_SUP_LEVEL,
12611: 1 WBS_SUB_LEVEL,
12659: prg.RELATIONSHIP_TYPE RELATIONSHIP_TYPE,
12660: 'Y' PUSHUP_FLAG
12661: from
12662: PJI_PJP_PROJ_BATCH_MAP map,
12663: PJI_XBS_DENORM prg,
12664: PJI_XBS_DENORM_DELTA prg_delta
12665: where
12666: prg.STRUCT_TYPE = 'PRG' and
12667: prg.RELATIONSHIP_TYPE = 'LF' and
12660: 'Y' PUSHUP_FLAG
12661: from
12662: PJI_PJP_PROJ_BATCH_MAP map,
12663: PJI_XBS_DENORM prg,
12664: PJI_XBS_DENORM_DELTA prg_delta
12665: where
12666: prg.STRUCT_TYPE = 'PRG' and
12667: prg.RELATIONSHIP_TYPE = 'LF' and
12668: prg.SUB_ROLLUP_ID is not null and
12763: else -- online mode
12764:
12765: -- rollup just WBS for online processing
12766:
12767: select /*+ ordered use_nl(den) index(den PJI_XBS_DENORM_N3) */
12768: nvl(max(den.SUP_LEVEL), 0)
12769: into
12770: l_level
12771: from
12769: into
12770: l_level
12771: from
12772: PJI_FM_EXTR_PLNVER3_T ver3,
12773: PJI_XBS_DENORM den
12774: where
12775: den.STRUCT_TYPE = 'PRG' and
12776: den.SUB_LEVEL = den.SUP_LEVEL and
12777: den.SUP_PROJECT_ID = ver3.PROJECT_ID;
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,
13053: 'PRJ' INVERT_ID
13140: pjp1.CUSTOM15
13141: from
13142: PJI_AC_AGGR_PJP1_T pjp1,
13143: PJI_PJP_WBS_HEADER wbs_hdr,
13144: PJI_XBS_DENORM prg
13145: where
13146: prg.STRUCT_TYPE = 'PRG' and
13147: prg.SUP_LEVEL = l_level and
13148: prg.SUB_LEVEL = l_level and
13234: wbs.SIGN * acr.CUSTOM15 CUSTOM15
13235: from
13236: PA_PJI_PROJ_EVENTS_LOG log,
13237: PJI_PJP_WBS_HEADER wbs_hdr,
13238: PJI_XBS_DENORM_DELTA_T wbs,
13239: PJI_XBS_DENORM prg,
13240: PJI_AC_XBS_ACCUM_F acr,
13241: (
13242: select 1 WBS_SUP_LEVEL,
13235: from
13236: PA_PJI_PROJ_EVENTS_LOG log,
13237: PJI_PJP_WBS_HEADER wbs_hdr,
13238: PJI_XBS_DENORM_DELTA_T wbs,
13239: PJI_XBS_DENORM prg,
13240: PJI_AC_XBS_ACCUM_F acr,
13241: (
13242: select 1 WBS_SUP_LEVEL,
13243: 1 WBS_SUB_LEVEL,
13290: prg.SUB_ROLLUP_ID,
13291: prg.RELATIONSHIP_TYPE RELATIONSHIP_TYPE,
13292: 'Y' PUSHUP_FLAG
13293: from
13294: PJI_XBS_DENORM prg,
13295: PA_PROJECTS_ALL map
13296: where
13297: l_level > 1 and
13298: prg.STRUCT_TYPE = 'PRG' and
13428: nvl(max(den.SUP_LEVEL), 0)
13429: into
13430: l_level
13431: from
13432: PJI_XBS_DENORM_DELTA den
13433: where
13434: den.WORKER_ID = p_worker_id and
13435: den.STRUCT_TYPE = 'PRG';
13436:
13459: (
13460: select
13461: SUP_LEVEL
13462: from
13463: PJI_XBS_DENORM_DELTA
13464: where
13465: worker_id = p_worker_id and
13466: STRUCT_TYPE = 'PRG'
13467: union all
13480: (
13481: select
13482: 1
13483: from
13484: PJI_XBS_DENORM_DELTA
13485: where
13486: WORKER_ID = p_worker_id and
13487: ROWNUM = 1
13488: )
14004: prg.SIGN * fpr.CUSTOM13 CUSTOM13,
14005: prg.SIGN * fpr.CUSTOM14 CUSTOM14,
14006: prg.SIGN * fpr.CUSTOM15 CUSTOM15
14007: from
14008: PJI_XBS_DENORM_DELTA prg,
14009: PA_PROJ_ELEMENTS prj_emt,
14010: PJI_PJP_WBS_HEADER wbs_hdr,
14011: PJI_FP_XBS_ACCUM_F fpr,
14012: PJI_PJP_WBS_HEADER sup_wbs_hdr,
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,
14416: (
14666: nvl(max(den.SUP_LEVEL), 0)
14667: into
14668: l_level
14669: from
14670: PJI_XBS_DENORM_DELTA den
14671: where
14672: den.WORKER_ID = p_worker_id and
14673: den.STRUCT_TYPE = 'PRG';
14674:
14697: (
14698: select
14699: SUP_LEVEL
14700: from
14701: PJI_XBS_DENORM_DELTA
14702: where
14703: worker_id = p_worker_id and
14704: STRUCT_TYPE = 'PRG'
14705: union all
14718: (
14719: select
14720: 1
14721: from
14722: PJI_XBS_DENORM_DELTA
14723: where
14724: WORKER_ID = p_worker_id and
14725: ROWNUM = 1
14726: )
14940: prg.SIGN * acr.CUSTOM13 CUSTOM13,
14941: prg.SIGN * acr.CUSTOM14 CUSTOM14,
14942: prg.SIGN * acr.CUSTOM15 CUSTOM15
14943: from
14944: PJI_XBS_DENORM_DELTA prg,
14945: PA_PROJ_ELEMENTS prj_emt,
14946: PJI_PJP_WBS_HEADER wbs_hdr,
14947: PJI_AC_XBS_ACCUM_F acr
14948: where
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
15028: nvl(prg.SUB_ROLLUP_ID,
16399: END;
16400:
16401:
16402:
16403: delete from PJI_XBS_DENORM
16404: where SUP_PROJECT_ID in
16405: (select
16406: ver.PROJECT_ID
16407: from
16425: log.EVENT_OBJECT <> -1));
16426:
16427: l_count := l_count + sql%rowcount;
16428:
16429: delete from PJI_XBS_DENORM
16430: where SUP_PROJECT_ID in (select log.ATTRIBUTE1
16431: from PJI_PA_PROJ_EVENTS_LOG log
16432: where log.WORKER_ID = p_worker_id and
16433: log.EVENT_TYPE = 'PRG_CHANGE' and
16434: log.EVENT_OBJECT = -1);
16435:
16436: l_count := l_count + sql%rowcount;
16437:
16438: delete from PJI_XBS_DENORM
16439: where STRUCT_TYPE in ('WBS', 'XBS') and
16440: STRUCT_VERSION_ID in (select
16441: decode(invert.INVERT_ID,
16442: 1, log.EVENT_OBJECT,
16453: 'WBS_PUBLISH'));
16454:
16455: l_count := l_count + sql%rowcount;
16456:
16457: insert into PJI_XBS_DENORM
16458: (
16459: STRUCT_TYPE,
16460: PRG_GROUP,
16461: STRUCT_VERSION_ID,
16580: WHEN NO_DATA_FOUND then null;
16581: END;
16582:
16583: delete
16584: from PJI_XBS_DENORM
16585: where SUP_PROJECT_ID in (select map.PROJECT_ID
16586: from PJI_PJP_PROJ_BATCH_MAP map
16587: where WORKER_ID = p_worker_id);
16588:
16587: where WORKER_ID = p_worker_id);
16588:
16589: l_count := l_count + sql%rowcount;
16590:
16591: insert into PJI_XBS_DENORM
16592: (
16593: STRUCT_TYPE,
16594: PRG_GROUP,
16595: STRUCT_VERSION_ID,
16674: -- online mode, refresh denorm table for a single WBS version ID
16675:
16676: l_count := 0;
16677:
16678: delete /*+ index(den PJI_XBS_DENORM_N2) */
16679: from PJI_XBS_DENORM den
16680: where den.STRUCT_TYPE in ('WBS', 'XBS') and
16681: den.SUP_PROJECT_ID = g_project_id and
16682: den.STRUCT_VERSION_ID in (g_old_struct_version,
16675:
16676: l_count := 0;
16677:
16678: delete /*+ index(den PJI_XBS_DENORM_N2) */
16679: from PJI_XBS_DENORM den
16680: where den.STRUCT_TYPE in ('WBS', 'XBS') and
16681: den.SUP_PROJECT_ID = g_project_id and
16682: den.STRUCT_VERSION_ID in (g_old_struct_version,
16683: g_new_struct_version);
16683: g_new_struct_version);
16684:
16685: l_count := l_count + sql%rowcount;
16686:
16687: delete /*+ index(den PJI_XBS_DENORM_N3) */
16688: from PJI_XBS_DENORM den
16689: where den.STRUCT_TYPE = 'PRG' and
16690: den.SUP_PROJECT_ID in (select prg.SUP_PROJECT_ID
16691: from PJI_FP_AGGR_XBS_T prg
16684:
16685: l_count := l_count + sql%rowcount;
16686:
16687: delete /*+ index(den PJI_XBS_DENORM_N3) */
16688: from PJI_XBS_DENORM den
16689: where den.STRUCT_TYPE = 'PRG' and
16690: den.SUP_PROJECT_ID in (select prg.SUP_PROJECT_ID
16691: from PJI_FP_AGGR_XBS_T prg
16692: where prg.STRUCT_TYPE = 'PRG');
16692: where prg.STRUCT_TYPE = 'PRG');
16693:
16694: l_count := l_count + sql%rowcount;
16695:
16696: insert into PJI_XBS_DENORM
16697: (
16698: STRUCT_TYPE,
16699: PRG_GROUP,
16700: STRUCT_VERSION_ID,
16780: -- g_new_struct_version);
16781:
16782: end if;
16783:
16784: delete from PJI_XBS_DENORM_DELTA_T;
16785:
16786: end if;
16787:
16788: end UPDATE_XBS_DENORM;
18036: 'PARTITION',
18037: 'P' || p_worker_id);
18038:
18039: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
18040: 'PJI_XBS_DENORM_DELTA',
18041: 'PARTITION',
18042: 'P' || p_worker_id);
18043:
18044: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
18065: commit;
18066:
18067: else -- online mode
18068:
18069: delete from PJI_XBS_DENORM_DELTA_T;
18070:
18071: delete from PJI_FP_AGGR_PJP1_T;
18072:
18073: delete from PJI_AC_AGGR_PJP1_T;