[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_login NUMBER;
g_last_updated_by NUMBER;
INSERT /*+ append parallel(opi_dbi_inv_value_f) */ INTO opi_dbi_inv_value_f
(operating_unit_id,
organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
primary_uom,
onhand_value_b,
intransit_value_b,
wip_value_b,
conversion_rate,
sec_conversion_rate,
source,
created_by,
last_update_login,
creation_date,
last_updated_by,
last_update_date
)
SELECT /*+ use_hash(rate, s) parallel(s) parallel(rate) */
NULL operating_unit_id,
s.organization_id,
s.subinventory_code,
s.inventory_item_id,
s.transaction_date,
msi.primary_uom_code,
s.onhand_value_b,
s.intransit_value_b,
s.wip_value_b,
rate.conversion_rate,
rate.sec_conversion_rate,
g_opi_cpcs_source,
g_created_by,
g_last_update_login,
g_sysdate,
g_last_updated_by,
g_sysdate
FROM
(SELECT /*+ parallel(adjustments) */
organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
sum(onhand_value_b) onhand_value_b,
sum(intransit_value_b) intransit_value_b,
sum(wip_value_b) wip_value_b
FROM
(SELECT /*+ parallel(onhand_stg) */
organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
onhand_value_b,
0 intransit_value_b,
0 wip_value_b
FROM opi_dbi_onhand_stg
WHERE source = g_opi_cpcs_source
UNION ALL
SELECT /*+ parallel(intransit_stg) */
organization_id,
NULL,
inventory_item_id,
transaction_date,
0 onhand_value_b,
intransit_value_b,
0 wip_value_b
FROM opi_dbi_intransit_stg
WHERE source = g_opi_cpcs_source
) adjustments
GROUP BY
organization_id,
subinventory_code,
inventory_item_id,
transaction_date
) s,
(SELECT /*+ no_merge parallel(rates) */
organization_id,
transaction_date,
conversion_rate,
sec_conversion_rate
FROM opi_dbi_conversion_rates
) rate,
mtl_system_items_b msi
WHERE s.organization_id = rate.organization_id
AND s.transaction_date = rate.transaction_date
AND s.organization_id = msi.organization_id
AND s.inventory_item_id = msi.inventory_item_id;
UPDATE opi_dbi_inv_value_log
SET uncosted_trx_id = NULL,
from_transaction_date = transaction_date,
transaction_date = NULL
WHERE uncosted_trx_id = -99
AND type = 'PCS'
AND source = g_opi_cpcs_source;
FUNCTION INSERT_ADJUSTMENTS (
errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2
) RETURN NUMBER IS
l_dbilog_rows NUMBER;
SELECT 1
INTO l_dbilog_rows
FROM opi_dbi_conc_prog_run_log log
,mtl_parameters mp
WHERE log.ETL_TYPE = 'INVENTORY'
AND log.load_type = 'INIT'
AND log.driving_table_code = 'MMT'
AND log.bound_level_entity_code = 'ORGANIZATION'
AND log.bound_level_entity_id = mp.organization_id
-- cpcs is only valid for discrete orgs. though log table
-- does not contain records for discrete orgs still putting
-- this additional filter.
AND nvl(mp.process_enabled_flag,'-1') <> 'Y'
AND NOT EXISTS
(SELECT 'x'
FROM opi_dbi_inv_value_log inlog
WHERE inlog.TYPE = 'PCS'
AND inlog.source = g_opi_cpcs_source
AND inlog.organization_id = log.bound_level_entity_id)
AND rownum = 1;
INSERT INTO opi_dbi_inv_value_log
(organization_id,
transaction_id,
transaction_date,
uncosted_trx_id,
type,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT /*+ use_hash(cpcs, oap) parallel(cpcs) parallel(oap) */
cpcs.organization_id,
-99.99 transaction_id,
trunc (min (oap.schedule_close_date)) transaction_date,
-99, -- Indicates that the organization needs lump-sum adjustment processing
'PCS' type,
g_opi_cpcs_source source,
g_sysdate creation_date,
g_sysdate last_update_date,
g_created_by created_by,
g_last_updated_by last_updated_by,
g_last_update_login last_update_login
FROM cst_period_close_summary cpcs,
org_acct_periods oap
WHERE cpcs.acct_period_id = oap.acct_period_id
AND cpcs.organization_id = oap.organization_id
AND oap.schedule_close_date >= g_global_start_date
AND oap.summarized_flag ='Y'
AND NOT EXISTS
(SELECT 'x'
FROM opi_dbi_inv_value_log inlog
WHERE inlog.organization_id = cpcs.organization_id
AND inlog.type = 'PCS'
AND inlog.source = g_opi_cpcs_source)
GROUP BY cpcs.organization_id;
DELETE FROM opi_dbi_inv_value_log
WHERE organization_id IN
(SELECT mmt.organization_id
FROM
mtl_material_transactions mmt,
opi_dbi_inv_value_log log2
WHERE log2.uncosted_trx_id = -99 -- Indicates that the organization needs lump-sum adjustment processing
AND log2.type = 'PCS'
AND log2.source = g_opi_cpcs_source
AND mmt.organization_id = log2.organization_id
-- >= equal to is required because to_bound_id is first uncosted txn and not the last costed txn id
AND mmt.transaction_id >= (select max(log1.to_bound_id)
from opi_dbi_conc_prog_run_log log1
WHERE log1.load_type IN ('INIT','INCR')
AND log1.etl_type = 'INVENTORY'
AND log1.driving_table_code = 'MMT'
AND log1.bound_level_entity_code = 'ORGANIZATION'
AND log1.bound_level_entity_id = log2.organization_id)
-- the other condition of mmt.transaction_date <= log1.transaction_date is not required as
-- mmt.transaction_date <= log2.transaction_date is sufficient.
-- no need to add condition for process orgs inv_value_log cannot have it.
-- ... and the backdated transaction lies within the first period close in CPCS
AND mmt.transaction_date <= log2.transaction_date
)
and TYPE = 'PCS' and source = g_opi_cpcs_source;
INSERT /*+ append parallel(opi_dbi_onhand_stg)
parallel(opi_dbi_intransit_stg) */
ALL
WHEN onhand_value_lump_Sum <> 0
THEN INTO opi_dbi_onhand_stg
(organization_id, inventory_item_id, transaction_date,
onhand_value_b, source, subinventory_code,
creation_date, last_update_date, created_by,
last_updated_by, last_update_login)
VALUES
(organization_id, inventory_item_id, transaction_date,
onhand_value_lump_sum, source, subinventory_code,
creation_date, last_update_date, created_by,
last_updated_by, last_update_login)
WHEN intransit_value_lump_sum <> 0
THEN INTO OPI_DBI_INTRANSIT_STG
(organization_id, inventory_item_id, transaction_date,
intransit_value_b, source,
creation_date, last_update_date, created_by,
last_updated_by, last_update_login)
VALUES
(organization_id, inventory_item_id, transaction_date,
intransit_value_lump_sum, source,
creation_date, last_update_date, created_by,
last_updated_by, last_update_login)
SELECT /*+ use_hash(cpcs_rbk, dbi_itd)
parallel(pcs_rbk) parallel(dbi_itd) */
dbi_itd.organization_id,
dbi_itd.subinventory_code,
dbi_itd.inventory_item_id,
dbi_itd.transaction_date,
nvl (cpcs_onhand_value_b, 0) - dbi_onhand_value_b
onhand_value_lump_sum, -- Onhand First Lump-Sum adjustment
nvl(cpcs_intransit_value_b, 0) - dbi_intransit_value_b
intransit_value_lump_sum, -- Intransit First Lump-Sum adjustment
g_opi_cpcs_source source,
g_sysdate creation_date,
g_sysdate last_update_date,
g_created_by created_by,
g_last_updated_by last_updated_by,
g_last_update_login last_update_login
FROM
(
SELECT /*+ use_hash(cpcs, oap, invlog)
parallel(cpcs) parallel(oap) parallel(invlog) */
cpcs.organization_id,
nvl(cpcs.subinventory_code, -1) subinventory_code,
cpcs.inventory_item_id,
trunc(oap.schedule_close_date) transaction_date,
sum(rollback_onhand_value) cpcs_onhand_value_b,
sum(rollback_intransit_value) cpcs_intransit_value_b
FROM
cst_period_close_summary cpcs,
org_acct_periods oap,
opi_dbi_inv_value_log invlog
WHERE cpcs.acct_period_id = oap.acct_period_id
AND cpcs.organization_id = oap.organization_id
AND oap.summarized_flag ='Y'
AND cpcs.organization_id = invlog.organization_id
AND oap.schedule_close_date = invlog.transaction_date
AND invlog.uncosted_trx_id = -99 -- Indicates that the organization needs lump-sum adjustment processing
AND invlog.type = 'PCS'
AND invlog.source = g_opi_cpcs_source
GROUP BY
cpcs.organization_id,
nvl(cpcs.subinventory_code, -1),
cpcs.inventory_item_id,
TRUNC(oap.SCHEDULE_CLOSE_DATE)
) cpcs_rbk,
(
SELECT /*+ use_hash(f, invlog) parallel(f) parallel(invlog) */
f.organization_id,
nvl(f.subinventory_code, -1) subinventory_code,
f.inventory_item_id,
invlog.transaction_date transaction_date,
sum(onhand_value_b) dbi_onhand_value_b,
sum(intransit_value_b) dbi_intransit_value_b
FROM
opi_dbi_inv_value_f f,
opi_dbi_inv_value_log invlog
WHERE f.organization_id = invlog.organization_id
AND f.transaction_date < invlog.transaction_date + 1 -- include all txns on CPCSD First Period close date too
AND invlog.uncosted_trx_id = -99 -- Indicates that the organization needs lump-sum adjustment processing
AND invlog.TYPE = 'PCS'
AND invlog.source = g_opi_cpcs_source
GROUP BY
f.organization_id,
nvl(f.subinventory_code, -1),
f.inventory_item_id,
invlog.transaction_date
) dbi_itd
WHERE cpcs_rbk.organization_id (+) = dbi_itd.organization_id /* Outer join for items that have a balance in DBI but not in CPCS */
AND cpcs_rbk.subinventory_code (+) = dbi_itd.subinventory_code
AND cpcs_rbk.inventory_item_id (+) = dbi_itd.inventory_item_id
AND cpcs_rbk.transaction_date (+) = dbi_itd.transaction_date
AND (nvl(cpcs_onhand_value_b, 0) - dbi_onhand_value_b
<> 0
OR
nvl(cpcs_intransit_value_b, 0) - dbi_intransit_value_b
<> 0);
BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' First Period Close Adjustment rows have been inserted into staging tables.');
UPDATE opi_dbi_inv_value_log
SET uncosted_trx_id = null,
from_transaction_date = transaction_date,
transaction_date = NULL
WHERE uncosted_trx_id = -99
AND type = 'PCS'
AND source = g_opi_cpcs_source;
SELECT
cpcs.Organization_id,
-99.99 transaction_id,
trunc(max(oap.schedule_close_date)) transaction_date, -- To period end date
trunc(min(oap.schedule_close_date)) from_transaction_date, -- From period end date
'PCS' type,
g_opi_cpcs_source source,
g_sysdate creation_date,
g_sysdate last_update_date,
g_created_by created_by,
g_last_updated_by last_updated_by,
g_last_update_login last_update_login
FROM
cst_period_close_summary cpcs,
org_acct_periods oap,
OPI_DBI_INV_VALUE_LOG invlog
WHERE
cpcs.acct_period_id = oap.acct_period_id
AND cpcs.organization_id = oap.organization_id
AND oap.summarized_flag ='Y'
AND cpcs.organization_id = invlog.organization_id (+)
AND oap.schedule_close_date > nvl(invlog.from_transaction_date, oap.schedule_close_date - 1) -- periods after the last period processed
AND invlog.type = 'PCS'
AND invlog.source = g_opi_cpcs_source
GROUP BY cpcs.Organization_id
) stg
ON
(log.organization_id = stg.organization_id
and log.type = stg.type
and log.source = stg.source)
WHEN MATCHED THEN
UPDATE SET
log.transaction_date = stg.transaction_date,
log.from_transaction_date = stg.from_transaction_date,
log.last_update_date = stg.last_update_date,
log.last_updated_by = stg.last_updated_by,
log.last_update_login = stg.last_update_login,
log.uncosted_trx_id = -99 -- Indicates Indicates that the organization needs regular adjustment processing
WHEN NOT MATCHED THEN
INSERT
(organization_id,
transaction_id,
transaction_date,
from_transaction_date,
uncosted_trx_id,
type,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
VALUES
(
stg.organization_id,
stg.transaction_id,
stg.transaction_date,
stg.from_transaction_date,
-99, -- indicates that the organization needs regular adjustment processing
stg.type,
stg.source,
stg.creation_date,
stg.last_update_date,
stg.created_by,
stg.last_updated_by,
stg.last_update_login
);
SELECT count (*)
INTO l_dbilog_rows
FROM opi_dbi_inv_value_log
WHERE type = 'PCS'
AND source = g_opi_cpcs_source
AND uncosted_trx_id = -99;
INSERT ALL
WHEN onhand_value_b <> 0
THEN INTO opi_dbi_onhand_stg
(organization_id, inventory_item_id, transaction_date,
onhand_value_b, source, subinventory_code,
creation_date, last_update_date, created_by,
last_updated_by, last_update_login)
VALUES
(organization_id, inventory_item_id, transaction_date,
onhand_value_b, source, subinventory_code,
creation_date, last_update_date, created_by,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
WHEN intransit_value_b <> 0
THEN INTO opi_dbi_intransit_stg
(organization_id, inventory_item_id, transaction_date,
intransit_value_b, source,
creation_date, last_update_date, created_by,
last_updated_by, last_update_login)
VALUES
(organization_id, inventory_item_id, transaction_date,
intransit_value_b, source,
creation_date, last_update_date, created_by,
last_updated_by, last_update_login)
SELECT /*+ use_hash(cpcs, oap, invlog)
parallel(cpcs) parallel(oap) parallel(invlog) */
cpcs.organization_id,
nvl(cpcs.subinventory_code, -1) subinventory_code,
cpcs.inventory_item_id,
trunc(oap.SCHEDULE_CLOSE_DATE) transaction_date,
sum(rollback_onhand_value - accounted_onhand_value)
onhand_value_b, -- Onhand adjustment
sum(rollback_intransit_value - accounted_intransit_value)
INTRANSIT_VALUE_B, -- Intransit adjustment
g_opi_cpcs_source source,
g_sysdate creation_date,
g_sysdate last_update_date,
g_created_by created_by,
g_last_updated_by last_updated_by,
g_last_update_login last_update_login
FROM
cst_period_close_summary cpcs,
org_acct_periods oap,
opi_dbi_inv_value_log invlog
WHERE cpcs.acct_period_id = oap.acct_period_id
AND cpcs.organization_id = oap.organization_id
AND oap.summarized_flag ='Y'
AND cpcs.organization_id = invlog.organization_id
AND oap.schedule_close_date >= invlog.from_transaction_date
AND oap.schedule_close_date <= invlog.transaction_date
AND invlog.type = 'PCS'
AND invlog.uncosted_trx_id = -99 -- Indicates that the organization needs regular adjustment processing
AND invlog.source = g_opi_cpcs_source
AND ( rollback_onhand_value - accounted_onhand_value <> 0
OR rollback_intransit_value - accounted_intransit_value <> 0)
GROUP BY
cpcs.organization_id,
nvl(cpcs.SUBINVENTORY_CODE, -1),
cpcs.inventory_item_id,
trunc(oap.SCHEDULE_CLOSE_DATE)
HAVING
sum(rollback_onhand_value - accounted_onhand_value) <> 0
or
sum(rollback_intransit_value - accounted_intransit_value) <>0;
BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' Period Close Regular Adjustment rows have been inserted into staging tables.');
l_err_msg := 'OPI_DBI_INV_CPCS_PKG.INSERT_ADJUSTMENTS - Error at statement ('
|| to_char(l_stmt_num)
|| '): '
|| substr(SQLERRM, 1,200);
END INSERT_ADJUSTMENTS;
g_last_update_login := fnd_global.login_id;
g_last_updated_by := fnd_global.user_id;
SELECT BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE
INTO g_global_start_date
FROM DUAL;
IF (Insert_Adjustments(errbuf, retcode) = g_error) THEN
BIS_COLLECTION_UTILITIES.put_line('Failed to collect adjustments into staging tables.');
INSERT INTO opi_dbi_inv_value_log
(organization_id, transaction_id, transaction_date, type,
source, creation_date, last_update_date, created_by,
last_updated_by, last_update_login
)
VALUES
(-1, -1, g_sysdate, 'ERR',
g_opi_cpcs_source, g_sysdate, g_sysdate, g_created_by,
g_last_updated_by, g_last_update_login);
DELETE
FROM opi_dbi_inv_value_log
WHERE type = 'ERR'
AND source = g_opi_cpcs_source;
BIS_COLLECTION_UTILITIES.put_line('Please run the concurrent program: Initial Load - Update Inventory Value and Turns Base Summary, after fixing missing currency rates.');
BIS_COLLECTION_UTILITIES.put_line('Please run the concurrent program: Update Inventory Value and Turns Base Summary, to try finishing this process.');
BIS_COLLECTION_UTILITIES.put_line('Warning: If you decide to run the Initial Load - Update Inventory Value and Turns Base Summary again, the entire process will start over again.');
INSERT INTO opi_dbi_inv_value_log
(organization_id, transaction_id, transaction_date, type,
source, creation_date, last_update_date, created_by, last_updated_by,
last_update_login
)
VALUES
(-1, -1, g_sysdate, 'CLR', 1, g_sysdate, g_sysdate, g_created_by,
g_last_updated_by, g_last_update_login);
BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(l_rows1) || ' rows have been inserted into fact table from discrete/manufacturing organizations.');