[Home] [Help]
634: 'CONVERTED_RBS_PRG_EVENT' ATTRIBUTE20 -- flg converted events
635: from
636: PA_XBS_DENORM prg,
637: PA_PROJ_ELEMENTS emt,
638: PJI_PJP_RBS_HEADER rbs_hdr,
639: PJI_PA_PROJ_EVENTS_LOG log
640: where
641: prg.SUP_PROJECT_ID = c.PROJECT_ID and
642: (prg.SUB_EMT_ID = prg.SUB_ROLLUP_ID or
1769:
1770: elsif (l_extraction_type = 'RBS') then
1771:
1772: delete
1773: from PJI_PJP_RBS_HEADER
1774: where RBS_VERSION_ID in (select distinct
1775: log.EVENT_OBJECT
1776: from PJI_PA_PROJ_EVENTS_LOG log
1777: where log.WORKER_ID = p_worker_id and
1797: rbs_hdr.PROJECT_ID,
1798: rbs_hdr.PLAN_VERSION_ID,
1799: rbs_hdr.RBS_VERSION_ID
1800: from PJI_PA_PROJ_EVENTS_LOG log,
1801: PJI_PJP_RBS_HEADER rbs_hdr
1802: where log.WORKER_ID = p_worker_id and
1803: log.EVENT_TYPE = 'RBS_PUSH' and
1804: rbs_hdr.RBS_VERSION_ID =
1805: log.ATTRIBUTE2
1808: rbs_hdr.PROJECT_ID,
1809: rbs_hdr.PLAN_VERSION_ID,
1810: rbs_hdr.RBS_VERSION_ID
1811: from PJI_PA_PROJ_EVENTS_LOG log,
1812: PJI_PJP_RBS_HEADER rbs_hdr
1813: where log.WORKER_ID = p_worker_id and
1814: log.EVENT_TYPE = 'RBS_DELETE' and
1815: rbs_hdr.RBS_VERSION_ID =
1816: log.EVENT_OBJECT);
1999: l_project_id_tbl.delete;
2000:
2001: end if;
2002:
2003: insert into PJI_PJP_RBS_HEADER
2004: (
2005: PROJECT_ID,
2006: PLAN_VERSION_ID,
2007: RBS_VERSION_ID,
2035: (
2036: select
2037: 1
2038: from
2039: PJI_PJP_RBS_HEADER rbs_hdr
2040: where
2041: rbs_hdr.PROJECT_ID = c.PROJECT_ID and
2042: rbs_hdr.PLAN_VERSION_ID = -1 and
2043: rbs_hdr.RBS_VERSION_ID = c.RBS_VERSION_ID
2053: l_return_status);
2054:
2055: end if;
2056:
2057: insert into PJI_PJP_RBS_HEADER
2058: (
2059: PROJECT_ID,
2060: PLAN_VERSION_ID,
2061: RBS_VERSION_ID,
2085: 'A'
2086: from
2087: PJI_PJP_PROJ_BATCH_MAP map,
2088: PA_RBS_PRJ_ASSIGNMENTS asg,
2089: PJI_PJP_RBS_HEADER rbs_hdr
2090: where
2091: map.WORKER_ID = p_worker_id and
2092: asg.PROJECT_ID = map.PROJECT_ID and
2093: asg.REPORTING_USAGE_FLAG = 'Y' and
2095: -1 = rbs_hdr.PLAN_VERSION_ID (+) and
2096: asg.RBS_VERSION_ID = rbs_hdr.RBS_VERSION_ID (+) and
2097: rbs_hdr.PROJECT_ID is null;
2098:
2099: insert into PJI_PJP_RBS_HEADER
2100: (
2101: PROJECT_ID,
2102: PLAN_VERSION_ID,
2103: RBS_VERSION_ID,
2173: end if;
2174:
2175: end if;
2176:
2177: update PJI_PJP_RBS_HEADER
2178: set RBS_VERSION_ID = c.RBS_VERSION_ID,
2179: REPORTING_USAGE_FLAG = c.REPORTING_USAGE_FLAG,
2180: PROG_REP_USAGE_FLAG = c.PROG_REP_USAGE_FLAG,
2181: LAST_UPDATE_DATE = l_last_update_date,
2187: c.UPDATE_HEADER_ONLY_FLAG = 'MARK_AS_PROG_REP';
2188:
2189: if (sql%rowcount = 0) then
2190:
2191: insert into PJI_PJP_RBS_HEADER
2192: (
2193: PROJECT_ID,
2194: PLAN_VERSION_ID,
2195: RBS_VERSION_ID,
2293: l_project_id_tbl.delete;
2294:
2295: end if;
2296:
2297: update PJI_PJP_RBS_HEADER
2298: set RBS_VERSION_ID = c.RBS_VERSION_ID,
2299: LAST_UPDATE_DATE = l_last_update_date,
2300: LAST_UPDATED_BY = l_last_updated_by,
2301: LAST_UPDATE_LOGIN = l_last_update_login
2304: PLAN_VERSION_ID = -1;
2305:
2306: if (sql%rowcount = 0) then
2307:
2308: insert into PJI_PJP_RBS_HEADER
2309: (
2310: PROJECT_ID,
2311: PLAN_VERSION_ID,
2312: RBS_VERSION_ID,
2403: l_project_id_tbl.delete;
2404:
2405: end if;
2406:
2407: update PJI_PJP_RBS_HEADER
2408: set RBS_VERSION_ID = c.RBS_VERSION_ID,
2409: LAST_UPDATE_DATE = l_last_update_date,
2410: LAST_UPDATED_BY = l_last_updated_by,
2411: LAST_UPDATE_LOGIN = l_last_update_login
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
2824: prg.STRUCT_TYPE = 'PRG' and
2829: sup_rbs_hdr.PROG_REP_USAGE_FLAG = 'Y' and
2830: sub_ver.ELEMENT_VERSION_ID = prg.SUB_ID and
2831: hdr.RBS_VERSION_ID = sup_rbs_hdr.RBS_VERSION_ID
2832: ) evt,
2833: PJI_PJP_RBS_HEADER sub_rbs_hdr,
2834: PJI_PA_PROJ_EVENTS_LOG log
2835: where
2836: evt.ATTRIBUTE1 = sub_rbs_hdr.PROJECT_ID (+) and
2837: -1 = sub_rbs_hdr.PLAN_VERSION_ID (+) and
2896: l_project_id_tbl.delete;
2897:
2898: end if;
2899:
2900: insert into PJI_PJP_RBS_HEADER
2901: (
2902: PROJECT_ID,
2903: PLAN_VERSION_ID,
2904: RBS_VERSION_ID,
3749: bal.BILL_QUANTITY
3750: from
3751: PJI_PJP_PROJ_BATCH_MAP map,
3752: PJI_FP_TXN_ACCUM bal,
3753: PJI_PJP_RBS_HEADER rbs_hdr
3754: where
3755: l_extraction_type in ('FULL', 'PARTIAL') and
3756: -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
3757: -- l_plan_type_id IS NULL and -- Bug#5099574
3829: to_number(null) BILL_QUANTITY
3830: from
3831: PJI_PJP_PROJ_BATCH_MAP map,
3832: PJI_FP_TXN_ACCUM1 bal,
3833: PJI_PJP_RBS_HEADER rbs_hdr
3834: where
3835: l_extraction_type in ('FULL', 'PARTIAL') and
3836: -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
3837: -- l_plan_type_id IS NULL and -- Bug#5099574
3909: tmp7.BILL_QUANTITY
3910: from
3911: PJI_PJP_RMAP_FPR tmp7_r,
3912: PJI_FM_AGGR_FIN7 tmp7,
3913: PJI_PJP_RBS_HEADER rbs_hdr
3914: where
3915: l_extraction_type = 'INCREMENTAL' and
3916: tmp7_r.WORKER_ID = p_worker_id and
3917: tmp7_r.STG_ROWID = tmp7.ROWID and
11438: sum(pjp1.CUSTOM15) CUSTOM15
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
11893: sum(pjp1.CUSTOM15) CUSTOM15
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