[Home] [Help]
1546: l_last_update_login := FND_GLOBAL.LOGIN_ID;
1547:
1548: if (l_extraction_type = 'FULL') then
1549:
1550: insert into PJI_PJP_WBS_HEADER
1551: (
1552: PROJECT_ID,
1553: PLAN_VERSION_ID,
1554: WBS_VERSION_ID,
1600: PROJECT_ID;
1601:
1602: while (SQL%ROWCOUNT > 0) loop
1603:
1604: update PJI_PJP_WBS_HEADER wbs_hdr
1605: set wbs_hdr.WBS_VERSION_ID =
1606: (select /*+ index(log PA_PJI_PROJ_EVENTS_LOG_N1) */
1607: distinct log.ATTRIBUTE2
1608: from PA_PJI_PROJ_EVENTS_LOG log
1624: end loop;
1625:
1626: else
1627:
1628: update PJI_PJP_WBS_HEADER
1629: set WBS_VERSION_ID =
1630: PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID
1631: (PROJECT_ID),
1632: LOCK_FLAG = 'P',
1639: where WORKER_ID = p_worker_id and
1640: EVENT_TYPE in ('WBS_CHANGE',
1641: 'WBS_PUBLISH'));
1642:
1643: insert into PJI_PJP_WBS_HEADER
1644: (
1645: PROJECT_ID,
1646: PLAN_VERSION_ID,
1647: WBS_VERSION_ID,
1683: prj_emt.PROJECT_ID
1684: from
1685: PJI_PA_PROJ_EVENTS_LOG log,
1686: PA_PROJ_ELEMENTS prj_emt,
1687: PJI_PJP_WBS_HEADER hdr
1688: where
1689: log.WORKER_ID = p_worker_id and
1690: log.EVENT_TYPE in ('WBS_CHANGE', 'WBS_PUBLISH') and
1691: prj_emt.OBJECT_TYPE = 'PA_STRUCTURES' and
4260: ) src,
4261: PA_RBS_TXN_ACCUM_MAP rbs,
4262: PJI_PJP_PROJ_BATCH_MAP map,
4263: PJI_ORG_EXTR_INFO info,
4264: PJI_PJP_WBS_HEADER wbs_hdr,
4265: PA_PROJ_ELEMENT_VERSIONS ver,
4266: (
4267: select 1 INVERT_ID from dual
4268: where l_g1_currency_code is not null union all
4956: src.TXN_CURRENCY_CODE
4957: )
4958: ) src,
4959: PJI_PJP_PROJ_BATCH_MAP map,
4960: PJI_PJP_WBS_HEADER wbs_hdr,
4961: PA_PROJ_ELEMENT_VERSIONS ver,
4962: (
4963: select 1 INVERT_ID from dual
4964: where l_g1_currency_code is not null union all
8468: pjp1.CUSTOM14,
8469: pjp1.CUSTOM15
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
8622: pjp1.CUSTOM14,
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,
8773: wbs.SIGN * fpr.CUSTOM14 CUSTOM14,
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: (
8891: prg.RELATIONSHIP_TYPE = prg_delta.RELATIONSHIP_TYPE (+) and
8892: -1 = prg_delta.SIGN (+) and
8893: prg_delta.SUP_PROJECT_ID is null
8894: ) prg,
8895: PJI_PJP_WBS_HEADER wbs_hdr,
8896: PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
8897: PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
8898: PA_PROJ_WORKPLAN_ATTR sup_wpa
8899: where
9774: pjp1.CUSTOM14,
9775: pjp1.CUSTOM15
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
9928: pjp1.CUSTOM14,
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,
10080: wbs.SIGN * fpr.CUSTOM14 CUSTOM14,
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: (
10509: -4, -1)
10510: * fpr.CUSTOM15 CUSTOM15
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
10903: -4, -1)
10904: * fpr.CUSTOM15 CUSTOM15
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
10974: decode(prg.SUB_LEVEL,
10975: prg.SUP_LEVEL, 'A',
10976: prg.RELATIONSHIP_TYPE) = invert.INVERT_ID
10977: ) prg,
10978: PJI_PJP_WBS_HEADER wbs_hdr,
10979: PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
10980: PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
10981: PA_PROJ_WORKPLAN_ATTR sup_wpa
10982: where
11439: from
11440: PJI_FP_AGGR_PJP1 pjp1,
11441: PJI_ROLLUP_LEVEL_STATUS ss,
11442: PJI_PJP_RBS_HEADER rbs_hdr,
11443: PJI_PJP_WBS_HEADER wbs_hdr,
11444: PA_RBS_DENORM rbs
11445: where
11446: l_extraction_type <> 'RBS' and
11447: rbs.STRUCT_VERSION_ID = ss.RBS_VERSION_ID and
11894: from
11895: PJI_FP_AGGR_PJP1_T pjp1,
11896: PJI_ROLLUP_LEVEL_STATUS ss,
11897: PJI_PJP_RBS_HEADER rbs_hdr,
11898: PJI_PJP_WBS_HEADER wbs_hdr,
11899: PA_RBS_DENORM rbs
11900: where
11901: -- l_extraction_type <> 'RBS' and
11902: rbs.STRUCT_VERSION_ID = ss.RBS_VERSION_ID and
12412: pjp1.CUSTOM14,
12413: pjp1.CUSTOM15
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,
12508: pjp1.CUSTOM14,
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
12601: wbs.SIGN * acr.CUSTOM14 CUSTOM14,
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: (
13043: pjp1.CUSTOM14,
13044: pjp1.CUSTOM15
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,
13139: pjp1.CUSTOM14,
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
13233: wbs.SIGN * acr.CUSTOM14 CUSTOM14,
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: (
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,
14013: PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
14014: PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
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,
14013: PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
14014: PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
14015: (
14016: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID
14405: pjp1.CUSTOM14,
14406: pjp1.CUSTOM15
14407: from
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,
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: (
14417: select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID
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
14949: prg.WORKER_ID = p_worker_id and
14950: prg.STRUCT_TYPE = 'PRG' and
15017: pjp1.CUSTOM14,
15018: pjp1.CUSTOM15
15019: from
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
16376: log.EVENT_TYPE = 'PRG_CHANGE' and
16377: log.EVENT_OBJECT <> -1)
16378: union all
16379: select ver.wbs_version_id
16380: from PJI_PA_PROJ_EVENTS_LOG log,Pji_pjp_wbs_header ver
16381: where ver.project_id=to_number(log.ATTRIBUTE1) and
16382: log.WORKER_ID = p_worker_id and
16383: log.EVENT_TYPE = 'PRG_CHANGE' and
16384: log.EVENT_OBJECT = -1
16572:
16573: BEGIN
16574: select distinct ver.wbs_version_id bulk collect
16575: into l_wbs_version_id_tbl
16576: from PJI_PJP_PROJ_BATCH_MAP map,Pji_pjp_wbs_header ver
16577: where map.PROJECT_ID=ver.project_id
16578: and map.WORKER_ID = p_worker_id;
16579: EXCEPTION
16580: WHEN NO_DATA_FOUND then null;
17418: end if;
17419:
17420: l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
17421:
17422: update PJI_PJP_WBS_HEADER
17423: set LOCK_FLAG = null
17424: where LOCK_FLAG is not null
17425: and project_id IN
17426: ( SELECT project_id