[Home] [Help]
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,
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
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
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,
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
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,
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,
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
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,
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
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,
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,
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;