[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG from inception date less than gsd';
UPDATE OPI_DBI_INV_BEG_STG fact
SET (onhand_value_b ,intransit_value_b) =
(SELECT /*+ ordered use_hash(csc2, csc) parallel(csc2) parallel(csc)*/
-- ideally max is not required as standard cost
-- revision date is timestamp.
max(csc.standard_cost) *onhand_qty onhand_value_b,
max(csc.standard_cost) *intransit_qty intransit_value_b
FROM (
SELECT /*+ use_hash(p csc) parallel(csc)
parallel(mtl_parameters) */
csc.organization_id,
csc.inventory_item_id,
-- this is a a timestamp hence max would
-- give unique record.
max(standard_cost_revision_date) standard_cost_revision_date,
p.primary_cost_method cost_method,
NULL cost_group_id -- RS: Bug fix 5219487 p.default_cost_group_id cost_group_id
FROM mtl_parameters p,
cst_standard_costs csc
-- not using <= below because txns are
-- collected from GSD onward. hence if there is
-- any cost update as of GSD additional 24 txns
-- will come in separately.
WHERE standard_cost_revision_date < g_global_start_date
AND p.primary_cost_method = 1
AND p.organization_id = csc.organization_id
GROUP BY csc.organization_id,
csc.inventory_item_id,
p.primary_cost_method,
p.default_cost_group_id
) csc2,
cst_standard_costs csc
WHERE csc.organization_id = csc2.organization_id
AND csc.inventory_item_id = csc2.inventory_item_id
AND csc.standard_cost_revision_date = csc2.standard_cost_revision_date
and fact.organization_id = csc2.organization_id
and fact.inventory_item_id =csc2.inventory_item_id
-- and fact.cost_group_id =csc2.cost_group_id -- RS: Bug fix 5219487
-- and fact.cost_method =csc.cost_method
GROUP BY csc.organization_id,
csc.inventory_item_id,
csc2.cost_method,
csc2.cost_group_id)
where ( nvl(fact.onhand_qty,0) <> 0 or nvl(fact.intransit_qty,0) <> 0 );
l_debug_msg := 'Updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Updated staging table OPI_DBI_INV_BEG_STG from inception date greater than gsd';
UPDATE OPI_DBI_INV_BEG_STG fact
SET (onhand_value_b ,intransit_value_b) =
(SELECT /*+ NO_MERGE, leading(mmt1) */
mmt2.prior_cost * onhand_qty onhand_value_b,
mmt2.prior_cost *intransit_qty intransit_value_b
FROM (
SELECT /*+ leading (stg1) */
mmt.organization_id,
mmt.inventory_item_id,
NULL cost_group_id, --RS: Bug fix 5219487 nvl (mmt.cost_group_id, p.default_cost_group_id) cost_group_id,
min(mmt.transaction_id) trx_id
FROM mtl_material_transactions mmt,
OPI_DBI_INV_BEG_STG stg,
mtl_parameters p
WHERE primary_cost_method = 1
AND stg.organization_id = p.organization_id
AND stg.inventory_item_id = mmt.inventory_item_id
AND stg.organization_id = mmt.organization_id
AND mmt.transaction_date >=g_global_start_date
And mmt.transaction_type_id not in (73,25,26,90,91,92,55,56,57,58,87,88,89)
AND nvl(mmt.logical_transaction,0) <> 1
AND nvl(mmt.owning_tp_type, 2) = 2
AND mmt.organization_id = NVL (mmt.owning_organization_id,
mmt.organization_id)
and mmt.costed_flag is null
AND new_cost is not null
AND ((stg.onhand_value_b is null and nvl(stg.onhand_qty,0) <> 0 )
OR (stg.intransit_value_b is null and nvl(stg.intransit_qty,0) <> 0 ))
and exists
(select 1 from cst_standard_costs csc
where stg.inventory_item_id = csc.inventory_item_id
AND stg.organization_id = csc.organization_id
and standard_cost_revision_date >= g_global_start_date)
GROUP BY mmt.organization_id,
mmt.inventory_item_id -- ,
-- nvl (mmt.cost_group_id, p.default_cost_group_id), --RS: Bug fix 5219487
-- p.primary_cost_method
) mmt1,
mtl_material_transactions mmt2
WHERE mmt2.transaction_id = mmt1.trx_id
and fact.organization_id = mmt1.organization_id
and fact.inventory_item_id =mmt1.inventory_item_id
-- and fact.cost_group_id =mmt1.cost_group_id -- RS: Bug fix 5219487
and ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ))
)
where ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
l_debug_msg := 'updated data into OPI_DBI_INV_BEG_STG for standard costing organization';
UPDATE OPI_DBI_INV_BEG_STG fact
SET (onhand_value_b ,intransit_value_b) =
(SELECT /*+ ordered use_hash(csc2, csc) parallel(csc2) parallel(csc)*/
nvl(csc.item_cost,0) *onhand_qty onhand_value_b,
nvl(csc.item_cost,0) *intransit_qty intransit_value_b
FROM cst_item_costs csc
WHERE csc.organization_id = fact.organization_id
AND csc.inventory_item_id = fact.inventory_item_id
And csc.cost_type_id =1 )
WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
UPDATE OPI_DBI_INV_BEG_STG fact
SET (onhand_value_b ,intransit_value_b) =
(SELECT /*+ NO_MERGE, leading(mmt1) */
mmt2.prior_cost * onhand_qty onhand_value_b,
mmt2.prior_cost *intransit_qty intransit_value_b
FROM (
SELECT /*+ leading (stg1) */
mmt.organization_id,
mmt.inventory_item_id,
nvl (mmt.cost_group_id, p.default_cost_group_id) cost_group_id,
p.primary_cost_method cost_method,
min(mmt.transaction_id) trx_id
FROM mtl_material_transactions mmt,
OPI_DBI_INV_BEG_STG stg,
mtl_parameters p
WHERE primary_cost_method <> 1
AND stg.organization_id = p.organization_id
AND stg.inventory_item_id = mmt.inventory_item_id
AND stg.organization_id = mmt.organization_id
AND mmt.transaction_date >= g_global_start_date
AND mmt.organization_id = NVL (mmt.owning_organization_id,mmt.organization_id)
AND nvl(mmt.owning_tp_type, 2) = 2
AND new_cost is not null
AND ((stg.onhand_value_b is null and nvl(stg.onhand_qty,0) <> 0 )
OR (stg.intransit_value_b is null and nvl(stg.intransit_qty,0) <> 0 ))
GROUP BY mmt.organization_id,
mmt.inventory_item_id,
nvl (mmt.cost_group_id, p.default_cost_group_id),
p.primary_cost_method
) mmt1,
mtl_material_transactions mmt2
WHERE mmt2.transaction_id = mmt1.trx_id
and fact.organization_id = mmt1.organization_id
and fact.inventory_item_id =mmt1.inventory_item_id
and fact.cost_group_id =mmt1.cost_group_id
--and fact.cost_method =mmt2.cost_method
and ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ))
)
WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
UPDATE OPI_DBI_INV_BEG_STG fact
SET (onhand_value_b ,intransit_value_b) =
(SELECT
nvl(cst.item_cost,0) * onhand_qty onhand_value_b,
nvl(cst.item_cost,0) * intransit_qty intransit_value_b
FROM cst_quantity_layers cst,
mtl_parameters mp
WHERE mp.organization_id = fact.organization_id
AND primary_cost_method <> 1
AND cst.organization_id = fact.organization_id
AND cst.cost_group_id = fact.cost_group_id
AND cst.inventory_item_id = fact.inventory_item_id
AND ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0 )
OR (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ))
)
WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0 )
OR (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
SELECT distinct
fact.organization_id,
fact.inventory_item_id,
fact.transaction_date
FROM OPI_DBI_INV_BEG_STG fact,
mtl_parameters p
WHERE fact.organization_id = p.organization_id
AND p.process_enabled_flag ='Y'
ORDER BY fact.organization_id ,
fact.inventory_item_id,
fact.transaction_date;
l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG for Process costing organization';
/*UPDATE OPI_DBI_INV_BEG_STG fact
SET (onhand_value_b ,intransit_value_b, COST_FOUND_FLAG) =
(SELECT onhand_qty * GET_OPM_ITEM_COST(fact.organization_id, fact.inventory_item_id,fact.transaction_date) onhand_value_b,
intransit_qty * GET_OPM_ITEM_COST(fact.organization_id, fact.inventory_item_id,fact.transaction_date) intransit_value_b,
NULL
FROM dual, mtl_parameters mp
WHERE mp.organization_id =fact .organization_id
AND mp.process_enabled_flag ='Y'
);*/
UPDATE OPI_DBI_INV_BEG_STG fact
SET onhand_value_b =onhand_qty * l_opm_cost
,intransit_value_b = intransit_qty * l_opm_cost
where fact.organization_id =opm_org_cost_info.organization_id
AND fact.inventory_item_id =opm_org_cost_info.inventory_item_id;
l_debug_msg := 'Updated staging table OPI_DBI_INV_BEG_STG for OPM - ' || l_row_count || ' rows. ';
l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
INSERT INTO OPI_DBI_INV_BEG_STG
(organization_id
,subinventory_code
,cost_group_id
,inventory_item_id
,transaction_date
,onhand_qty
)
SELECT balance.organization_id
,balance.subinventory_code
,balance.cost_group_id
,balance.inventory_item_id
,g_global_start_date
,sum(balance.onhand_qty)
FROM (SELECT stg1.organization_id
,stg1.subinventory_code
,stg1.cost_group_id
,stg1.inventory_item_id
-- beginning balance = current balance from moq - all activities from MMT
-- it is assumed that there are no draft quantities as of GSD
,decode(stg1.transaction_source,'MOQ',stg1.onhand_qty
,'MMT',-1*(stg1.onhand_qty+stg1.onhand_qty_draft)
,0) onhand_qty
FROM opi_dbi_onh_qty_stg stg1
WHERE transaction_source IN ('MMT','MOQ')
UNION ALL
SELECT stg2.organization_id
,stg2.subinventory_code
,-1 cost_group_id -- there is no cost_group_id required for opm items for finding cost
,stg2.inventory_item_id
,-1*stg2.onhand_qty
FROM opi_dbi_opm_inv_stg stg2) balance
GROUP BY balance.organization_id
,balance.subinventory_code
,balance.cost_group_id
,balance.inventory_item_id;
l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Insert Data into opi_dbi_intr_mip_tmp ';
INSERT /*+append parallel(opi_dbi_intr_mip_tmp) */
INTO opi_dbi_intr_mip_tmp (
from_organization_id,
to_organization_id,
owning_organization_id,
owning_org_process_flag,
transaction_action_id,
fob_point)
select from_organization_id,
to_organization_id,
owning_organization_id,
-- Because of wrong setup value 1 is there for some of the discrete org,
-- Customers might have this too
DECODE(mp.process_enabled_flag,'1','N',mp.process_enabled_flag),
transaction_action_id, -- intransit shipment
fob_point
from MTL_PARAMETERS mp,
(SELECT /*+ parallel(mip) */
from_organization_id,
to_organization_id,
DECODE(fob_point,1,to_organization_id,
2,from_organization_id) owning_organization_id,
21 transaction_action_id, -- intransit shipment
fob_point
FROM MTL_INTERORG_PARAMETERS mip
WHERE NVL(fob_point,-1) in (1,2)
UNION ALL
SELECT /*+ parallel(mip) */
to_organization_id,
from_organization_id,
DECODE(fob_point,1,to_organization_id,
2,from_organization_id) owning_organization_id,
12 transaction_action_id, -- intransit receipt
fob_point
FROM MTL_INTERORG_PARAMETERS mip
WHERE NVL(fob_point,-1) in (1,2)) mip_outer
WHERE mip_outer.owning_organization_id =mp.organization_id
;
l_debug_msg := 'Inserted into staging table opi_dbi_intr_mip_tmp - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Insert Data into opi_dbi_intr_mmt_tmp. ';
INSERT /*append parallel(opi_dbi_intr_mmt_tmp) */
INTO OPI_DBI_INTR_MMT_TMP (
transaction_id,
organization_id,
organization_process_flag,
transfer_organization_id,
transfer_org_process_flag,
inventory_item_id,
transaction_action_id,
cost_group_id,
transfer_cost_group_id,
primary_quantity,
transaction_date)
SELECT /*+ use_hash(mmt) use_hash(p) use_hash(p1) parallel(mmt) parallel(p) parallel(p1)*/
mmt.transaction_id,
mmt.organization_id,
-- Setup issue some discrete organization can have value 1.
DECODE(p.process_enabled_flag
,'1','N',
p.process_enabled_flag),
transfer_organization_id,
DECODE(p1.process_enabled_flag,
'1','N',
p1.process_enabled_flag),
inventory_item_id,
transaction_action_id,
NVL (mmt.cost_group_id,
p.default_cost_group_id) cost_group_id,
NVL (mmt.transfer_cost_group_id,
p1.default_cost_group_id) transfer_cost_group_id,
DECODE (mmt.transaction_action_id,
24, 0,
mmt.primary_quantity),
transaction_date
FROM MTL_MATERIAL_TRANSACTIONS mmt,
MTL_PARAMETERS p,
MTL_PARAMETERS p1
WHERE mmt.transaction_action_id in (12,21,24,15,22)
AND mmt.transaction_date >= g_global_start_date
AND p.organization_id = mmt.organization_id
AND p1.organization_id = mmt.transfer_organization_id
--AND mmt.transaction_type_id IN (12,21,61,62,24,80,26,28) -- Removed in r12
;
l_debug_msg := 'Inserted into staging table opi_dbi_intr_mmt_tmp - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Insert into opi_dbi_intr_sup_tmp. ';
INSERT /*append parallel(opi_dbi_intr_sup_tmp) */
INTO OPI_DBI_INTR_SUP_TMP (
intransit_owning_org_id,
from_organization_id,
to_organization_id,
qty,
item_id,
cost_group_id)
SELECT /*+ ordered use_hash(sup) parallel(sup)*/
intransit_owning_org_id,
from_organization_id,
to_organization_id,
sum (DECODE (intransit_owning_org_id,
sup.from_organization_id, NVL(inv_convert.inv_um_convert(sup.item_id,
5,sup.quantity,
um.uom_code,msi_fm.primary_uom_code,
NULL,NULL), 0),
NVL(to_org_primary_quantity, 0))) qty,
item_id,
cost_group_id
FROM MTL_SUPPLY sup,MTL_SYSTEM_ITEMS msi_fm,mtl_units_of_measure um
WHERE supply_type_code in ('SHIPMENT' , 'RECEIVING')
AND intransit_owning_org_id IS NOT NULL
AND msi_fm.organization_id = sup.from_organization_id
AND msi_fm.inventory_item_id = sup.item_id
AND um.unit_of_measure = sup.unit_of_measure
GROUP BY
intransit_owning_org_id,
from_organization_id,
to_organization_id,
item_id,
cost_group_id;
/*SELECT
intransit_owning_org_id,
from_organization_id,
to_organization_id,
sum (DECODE (intransit_owning_org_id,
sup.from_organization_id, NVL(quantity, 0),
NVL(to_org_primary_quantity, 0))) qty,
item_id,
cost_group_id
FROM MTL_SUPPLY sup
WHERE supply_type_code in ('SHIPMENT' , 'RECEIVING')
AND intransit_owning_org_id IS NOT NULL
GROUP BY
intransit_owning_org_id,
from_organization_id,
to_organization_id,
item_id,
cost_group_id;
l_debug_msg :='Inserted into staging table opi_dbi_intr_sup_tmp - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG for Intransit Inception load ';
INSERT /*+ append parallel(opi_dbi_intransit_stg) */
INTO OPI_DBI_INV_BEG_STG
( organization_id ,
inventory_item_id,
subinventory_code,
Cost_group_id,
intransit_qty ,
transaction_date
) --Gets inventory_item, org_id, cost group combos with qty sums.
SELECT
xy.organization_id,
xy.inventory_item_id,
NULL subinventory_code,
xy.cost_group_id,
sum(xy.tot_prim_qty) tot_prim_qty,
g_global_start_date
FROM (
SELECT
mip.owning_organization_id organization_id,
mmt1.inventory_item_id inventory_item_id,
-- intransit balance = current intransit - activities.
-- here quantities are not negated because intransit
-- sign is already reverse on MMT w.r.t intransit
-- quantity.
sum (decode (msi_fm.primary_uom_code,
msi_to.primary_uom_code,
decode(mmt1.transaction_action_id,
22, mmt1.primary_quantity, --??Not needed
15, -1 * mmt1.primary_quantity,
mmt1.primary_quantity), --Bug 4878458
decode (mmt1.transaction_action_id,
21,decode (mip.fob_point,
2, mmt1.primary_quantity,
OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code)),
12,decode (mip.fob_point,
2,OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id, mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code),
mmt1.primary_quantity)))) tot_prim_qty,
decode(Mip.owning_org_process_flag -- this need only for cost group id, As For OPM it is -1
,'N',decode (mmt1.transaction_action_id,
21,decode (mip.fob_point,
2, mmt1.cost_group_id,
mmt1.transfer_cost_group_id),
12,decode (mip.fob_point,
2,mmt1.transfer_cost_group_id,
mmt1.cost_group_id)
),-1) cost_group_id
FROM OPI_DBI_INTR_MMT_TMP mmt1,
OPI_DBI_INTR_MIP_TMP mip,
MTL_PARAMETERS mp,
MTL_SYSTEM_ITEMS msi_fm,
MTL_SYSTEM_ITEMS msi_to
WHERE mmt1.organization_id = mip.from_organization_id
AND mmt1.transfer_organization_id = mip.to_organization_id
AND mmt1.transaction_action_id = mip.transaction_action_id
-- not collecting action id 24
-- as we are only collecting quantities
-- not looking at logical txns as well as looking at only
-- quantity and it comes correct from all physical txns
-- alone.
AND mmt1.transaction_action_id in (21,12)
AND mip.owning_organization_id = mp.organization_id
AND msi_fm.organization_id = mip.from_organization_id
AND msi_fm.inventory_item_id = mmt1.inventory_item_id
AND msi_to.organization_id = mip.to_organization_id
AND msi_to.inventory_item_id = mmt1.inventory_item_id
GROUP BY mip.owning_organization_id,
mmt1.inventory_item_id,
decode(Mip.owning_org_process_flag
,'N', mp.primary_cost_method,-1),
decode (mip.fob_point,
2,decode (mip.transaction_action_id,
21, msi_fm.primary_uom_code,
msi_to.primary_uom_code),
decode (mip.transaction_action_id,
12,msi_to.primary_uom_code,
msi_fm.primary_uom_code)),
decode(Mip.owning_org_process_flag -- this need only for cost group id, As For OPM it is -1
,'N',decode (mmt1.transaction_action_id,
21,decode (mip.fob_point,
2, mmt1.cost_group_id,
mmt1.transfer_cost_group_id),
12,decode (mip.fob_point,
2,mmt1.transfer_cost_group_id,
mmt1.cost_group_id)
),-1)
UNION ALL
SELECT sup.intransit_owning_org_id
organization_id,
sup.item_id inventory_item_id,
sum(sup.qty) tot_prim_qty,
nvl (sup.cost_group_id, p.default_cost_group_id)
cost_group_id
FROM OPI_DBI_INTR_SUP_TMP sup,
MTL_SYSTEM_ITEMS msi,
MTL_PARAMETERS p
WHERE sup.intransit_owning_org_id = msi.organization_id
AND p.organization_id = msi.organization_id
AND sup.item_id = msi.inventory_item_id
GROUP BY sup.intransit_owning_org_id,
sup.item_id,
p.primary_cost_method,
nvl (sup.cost_group_id, p.default_cost_group_id),'N'
UNION ALL
SELECT organization_id,
inventory_item_id,
-1 * intransit_qty tot_prim_qty,
-1 cost_group_id
FROM OPI_DBI_OPM_INV_STG) xy
GROUP BY inventory_item_id,
organization_id,
cost_group_id,
g_global_start_date
HAVING SUM(xy.tot_prim_qty) <>0 ;
l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
INSERT INTO OPI_DBI_INV_BEG_STG
(organization_id
,subinventory_code
,cost_group_id
,inventory_item_id
,transaction_date
,wip_value_b
)
SELECT balance.organization_id
,balance.subinventory_code
,balance.cost_group_id
,balance.inventory_item_id
,g_global_start_date
,sum(balance.wip_value_b)
FROM (SELECT stg1.organization_id
,stg1.subinventory_code
,-1 cost_group_id -- as wip is value and not qty no costing done. hence no cost group id required.
,stg1.inventory_item_id
,sum((decode(stg1.transaction_source,'WTA',-1*stg1.wip_value_b
,'MTA',-1*stg1.wip_value_b
,'GTV',-1*(nvl(wip_value_b,0)+nvl(wip_value_b_draft,0))
,'WPB',wip_value_b
,'OPJ',nvl(wip_value_b,0)+nvl(wip_value_b_draft,0)
,0))) wip_value_b
FROM opi_dbi_onhand_stg stg1
WHERE transaction_source IN ('WTA','MTA','GTV','WPB','OPJ')
group by stg1.organization_id
,stg1.subinventory_code
,-1
,stg1.inventory_item_id
-- for preR12 data. it is already grouped by org, sub,
-- item hence no additional group by here.
UNION ALL
SELECT stg2.organization_id
,stg2.subinventory_code
,-1 cost_group_id
,stg2.inventory_item_id
,-1*stg2.wip_value_b
FROM opi_dbi_opm_inv_stg stg2) balance
GROUP BY balance.organization_id
,balance.subinventory_code
,balance.cost_group_id
,balance.inventory_item_id;
l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data Into opi_dbi_intransit_stg for ODM ';
INSERT /*+ append */ INTO OPI_DBI_INTRANSIT_STG(
organization_id,
inventory_item_id,
transaction_date,
intransit_qty,
primary_uom,
intransit_value_b,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT /*+ use_hash(mta1) use_hash(mmt1) */
organization_id,
inventory_item_id,
decode (sign (trx_date - g_global_start_date),-1, g_global_start_date,trx_date) transaction_date,
SUM (qty) intransit_qty,
primary_uom_code primary_uom,
SUM(value)intransit_value_b,
decode(process_flag,'N',1,2), --Discrete/Process Bug fix: 5362465
g_sysdate,
g_sysdate,
g_user_id,
g_user_id,
g_login_id
FROM
(SELECT
mip.owning_organization_id organization_id,
mmt1.inventory_item_id,
-- logical txn no need of conversion as they are always against the owning org.
-- logical always contain qty in right sign there is -1 outside as well
-- -1 is done outside as txn quantity is always reverse sign of the txn qty.
-- for intransit across process and discrete orgs only logical txns are considered
-1 * SUM (
DECODE(
msi_fm.primary_uom_code,
msi_to.primary_uom_code,
decode(mmt1.transaction_action_id,
22, mmt1.primary_quantity, -- Absolute value fix
15, -1 * mmt1.primary_quantity,
mmt1.primary_quantity), --Bug 4878458
DECODE(
mmt1.transaction_action_id,
21, DECODE( -- intransit shipment
mip.fob_point, -- FOB 2 is receipt
2, mmt1.primary_quantity,
OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code)
),
12, DECODE ( -- inransit receipt
mip.fob_point,
2, OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code),
mmt1.primary_quantity),
22, mmt1.primary_quantity, -- Absolute value fix
15, -1 * mmt1.primary_quantity))) qty,
SUM (base_transaction_value) value,
DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
TRUNC (mmt1.transaction_date) trx_date,
mip.owning_org_process_flag process_flag -- Bug fix: 5362465
FROM opi_dbi_intr_mip_tmp mip,
opi_dbi_intr_mmt_tmp mmt1,
(select
transaction_id,
sum (base_transaction_value) base_transaction_value
from mtl_transaction_accounts
where accounting_line_type = 14 -- Accounting line for Inransit in MTA
group by transaction_id) mta,
mtl_system_items msi_fm,
mtl_system_items msi_to,
opi_dbi_conc_prog_run_log col
WHERE mmt1.organization_id = mip.from_organization_id
AND mmt1.transfer_organization_id = mip.to_organization_id
AND mmt1.transaction_action_id in (15,12,22,21)
AND decode(mmt1.transaction_action_id,15,12,22,21,mmt1.transaction_action_id) = mip.transaction_action_id
-- condition below avoids getting 1 of the physical txns across process and discrete orgs
-- as the process flag is different for owning org and txn organization
-- for more detail refer to DLD test cases
and mmt1.organization_process_flag = mip.owning_org_process_flag
AND mip.from_organization_id = col.bound_level_entity_id
AND mta.transaction_id (+)= mmt1.transaction_id -- outer join is required to collect expense item
-- As some of them might not have row in MMT.
AND msi_fm.inventory_item_id = mmt1.inventory_item_id
AND msi_fm.organization_id = mip.from_organization_id
and msi_to.inventory_item_id = mmt1.inventory_item_id
AND msi_to.organization_id = mip.to_organization_id
AND mmt1.transaction_id >= col.from_bound_id
AND mmt1.transaction_id < col.to_bound_id
AND col. driving_table_code= 'MMT'
AND col.etl_type = 'INVENTORY'
AND col.load_type= 'INIT'
GROUP BY
mip.owning_organization_id,
mip.owning_org_process_flag, -- Bug fix: 5362465
mmt1.inventory_item_id,
decode (mip.fob_point,2, decode (mip.transaction_action_id,21, msi_fm.primary_uom_code,
msi_to.primary_uom_code),decode (mip.transaction_action_id,
12, msi_to.primary_uom_code,msi_fm.primary_uom_code)) ,
trunc(mmt1.transaction_date),
DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
,msi_to.organization_id, msi_to.primary_uom_code)
--UNION ALL to collect cost update data related to Intransit.
UNION ALL
SELECT
mmt1.organization_id organization_id,
mmt1.inventory_item_id,
0 qty,
sum (base_transaction_value) value,
msi_fm.primary_uom_code,
trunc(mmt1.transaction_date) trx_date,
'N' process_flag -- Bug fix: 5362465, source is only discrete here
FROM opi_dbi_intr_mmt_tmp mmt1,
mtl_transaction_accounts mta,
mtl_system_items msi_fm,
OPI_DBI_CONC_PROG_RUN_LOG col
WHERE mmt1.transaction_action_id = 24
AND mta.transaction_id = mmt1.transaction_id
AND mmt1.organization_id = mta.organization_id
AND mta.accounting_line_type = 14
AND msi_fm.inventory_item_id = mmt1.inventory_item_id
AND msi_fm.organization_id = mmt1.organization_id
AND mmt1.organization_id = col.BOUND_LEVEL_ENTITY_ID
AND mmt1.transaction_id >= col.FROM_BOUND_ID
AND mmt1.transaction_id < col.TO_BOUND_ID
And col. DRIVING_TABLE_CODE= 'MMT'
And col.ETL_TYPE = 'INVENTORY'
And col.LOAD_TYPE= 'INIT'
GROUP BY
mmt1.organization_id,
mmt1.inventory_item_id,
msi_fm.primary_uom_code,
trunc(mmt1.transaction_date))
GROUP BY
organization_id,
inventory_item_id,
primary_uom_code,
trx_date,
decode(process_flag,'N',1,2) -- Bug fix: 5362465
HAVING sum(value) <> 0 or sum(qty) <> 0;
l_debug_msg := 'Inserted into staging table opi_dbi_intransit_stg - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data Into opi_dbi_intransit_stg for OPM Post R12 ';
INSERT /*+ APPEND */ INTO OPI_DBI_INTRANSIT_STG(
organization_id,
inventory_item_id,
transaction_date,
intransit_qty,
intransit_qty_draft,
primary_uom,
intransit_value_b,
intransit_value_draft_b,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT /*+ use_hash(mta1) use_hash(mmt1) */
organization_id,
inventory_item_id,
DECODE (SIGN (trx_date - g_global_start_date),
-1, g_global_start_date,
trx_date) transaction_date,
SUM (qty) intransit_qty,
SUM(draft_qty) intransit_qty_draft,
primary_uom_code primary_uom,
SUM (value) intransit_value_b,
SUM(draft_value) intransit_value_draft_b,
decode(process_flag,'N',1,2), -- 1 - Discrete/ 2 - Process -- Bug fix: 5362465
g_sysdate,
g_sysdate,
g_user_id,
g_user_id,
g_login_id
FROM
(SELECT
mip.owning_organization_id organization_id,
mip.owning_org_process_flag process_flag, -- Bug fix: 5362465
mmt1.inventory_item_id,
-1 * sum (DECODE(gtv.accounted_flag, --
-- -1 * sum (DECODE('D',
'D',0,DECODE (msi_fm.primary_uom_code,
msi_to.primary_uom_code,
decode(mmt1.transaction_action_id,
22, mmt1.primary_quantity, -- Absolute value fix
15, -1 * mmt1.primary_quantity, --bug 4878458
mmt1.primary_quantity),
DECODE (mmt1.transaction_action_id,
21, DECODE (mip.fob_point,
2, mmt1.primary_quantity,
OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code)
),
12, DECODE (mip.fob_point,
2,OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id, mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code),
mmt1.primary_quantity),
22, mmt1.primary_quantity ,-- Absolute value fix ,no need of conversion ??
15, -1 * mmt1.primary_quantity
)))) qty,-- Bug 4901338, removed ,0
-1 * sum (DECODE(gtv.accounted_flag,
-- -1 * sum (DECODE('D',
'D',DECODE (msi_fm.primary_uom_code,
msi_to.primary_uom_code,
decode(mmt1.transaction_action_id,
22, mmt1.primary_quantity, --Absolute value fix
15, -1 * mmt1.primary_quantity, --bug 4878458
mmt1.primary_quantity),
DECODE (mmt1.transaction_action_id,
21, DECODE (mip.fob_point,
2, mmt1.primary_quantity,
OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id, mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code)),
12, DECODE (mip.fob_point,
2, OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id, mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code),
mmt1.primary_quantity),
22, mmt1.primary_quantity ,-- Absolute value fix no need of conversion ??
15, -1 * mmt1.primary_quantity
)),0)) Draft_qty,
SUM(DECODE(gtv.accounted_flag,'D',base_transaction_value)) Draft_Value,
SUM(DECODE(gtv.accounted_flag,'D',0,base_transaction_value)) Value, --
--SUM(DECODE('D','D',base_transaction_value)) Draft_Value,
--SUM(DECODE('D','D',0,base_transaction_value)) Value,
DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
TRUNC (mmt1.transaction_date) trx_date
FROM opi_dbi_intr_mip_tmp mip,
opi_dbi_intr_mmt_tmp mmt1,
(SELECT transaction_id,
sum(txn_base_value) base_transaction_value
,accounted_flag
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp, --BUG 4768058
opi_dbi_conc_prog_run_log col
WHERE --gtv.transaction_source = 'INVENTORY' AND --bug 4870029
gtv.journal_line_type = 'ITR'
and col.driving_table_code='GTV'
and col.etl_type= 'INVENTORY'
and col.load_type= 'INIT'
and gtv.ledger_id = tmp.ledger_id --BUG 4768058
and gtv.legal_entity_id = tmp.legal_entity_id
and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
and gtv.organization_id = tmp. organization_id
AND gtv.transaction_date >= g_global_start_date
AND (( gtv.accounted_flag IS NULL and gtv.final_posting_date < col.TO_BOUND_DATE )
OR (gtv.accounted_flag ='D')) --
GROUP BY transaction_id, accounted_flag
) gtv,
mtl_system_items msi_fm,
mtl_system_items msi_to
WHERE mmt1.organization_id = mip.from_organization_id
AND mmt1.transfer_organization_id = mip.to_organization_id
AND mmt1.transaction_action_id in (15,12,22,21)
AND decode(mmt1.transaction_action_id,15,12,22,21,
mmt1.transaction_action_id) = mip.transaction_action_id
and mmt1.ORGANIZATION_PROCESS_FLAG = mip.owning_org_process_flag--make sure only logical collected incase of D-> P and P->D
AND gtv.transaction_id = mmt1.transaction_id -- No outer join is required in case of OPM
AND msi_fm.inventory_item_id = mmt1.inventory_item_id
AND msi_fm.organization_id = mip.from_organization_id
AND msi_to.inventory_item_id = mmt1.inventory_item_id
AND msi_to.organization_id = mip.to_organization_id
GROUP BY
mip.owning_organization_id,
mip.owning_org_process_flag, -- Bug fix: 5362465
mmt1.inventory_item_id,
DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
,msi_to.organization_id, msi_to.primary_uom_code),
decode (mip.fob_point,
2, decode (mip.transaction_action_id,21, msi_fm.primary_uom_code,msi_to.primary_uom_code),
decode (mip.transaction_action_id,12, msi_to.primary_uom_code,msi_fm.primary_uom_code)) ,
trunc(mmt1.transaction_date))
GROUP BY
organization_id,
inventory_item_id,
primary_uom_code,
trx_date,
decode(process_flag,'N',1,2) -- Bug fix: 5362465
HAVING sum(value) <> 0 or sum(qty) <> 0 OR sum(draft_value) <> 0 OR sum(draft_qty) <> 0 ;
l_debug_msg := 'Inserted into staging table opi_dbi_intransit_stg for OPM Post R12 - ' || SQL%ROWCOUNT || ' rows. ';
update and Extraction of quantity in this step.
It is assumed that database takes snapshot of underlying tables at the start of this step.
MMT and MTA data is in synch as data is extracted for same transaction id ranges.
MMT and GTV data is in synch as there is no bound on MMT and from GTV data is taken for all
draft records and final_posted_date < timestamp as of stamping the log tables.
Now it is possible there are some new transactions coming in between log table
1.2.2 and 1.2.5 and also getting draft posted.
These transactions will be picked up in step 1.2.5. However the next incremental load will
take care of it by collecting quantity for those records as draft records are always reprocessed.
*/
l_stmt_num :=10;
l_debug_msg := 'Inserting data into OPI_DBI_ONH_QTY_STG ';
INSERT /*+ append parallel(OPI_DBI_ONH_QTY_STG) */
INTO OPI_DBI_ONH_QTY_STG
(organization_id,
subinventory_code,
cost_group_id,
inventory_item_id,
transaction_date,
onhand_qty,
onhand_qty_draft,
push_to_fact_flag,
source,
transaction_source)
-- staging tables do not have who columns
(SELECT /*+ use_hash(mta1) use_hash(mmt1) */
mmt.organization_id,
nvl(mmt.subinventory_code,-1),
NVL(mmt.cost_group_id, mp.default_cost_group_id),
mmt.inventory_item_id,
TRUNC(mmt.transaction_date) transaction_date,
-- if its process org then consider final accounted quantity for discrete consider all
SUM(DECODE(mp.process_enabled_flag,'Y'
,DECODE(mmt.opm_costed_flag --
--,DECODE('D'
,null,mmt.primary_quantity,0)
,mmt.primary_quantity)) onhand_qty,
-- if its process org then consider draft accounted quantity. for discrete consider its always zero
SUM(DECODE(mp.process_enabled_flag,'Y'
,DECODE(mmt.opm_costed_flag,'D',
primary_quantity,'N', primary_quantity, 0),0)) onhand_qty_draft, --Qty extracted for opm_costed_flag 'N' and 'D'
CASE WHEN mp.process_enabled_flag = 'Y' THEN -- if its a process org
DECODE(mmt.opm_costed_flaG,'N','N','Y') -- if its costed then push to fact else Not --
--DECODE('D','N','N','Y') -- if its costed then push to fact else Not
WHEN mmt.transaction_id < prl.to_bound_id THEN 'Y'
ELSE 'N'
END push_to_fact_flag,
DECODE(mp.process_enabled_flag,'N',1,'Y',2,1) source,
'MMT' transaction_source
FROM MTL_MATERIAL_TRANSACTIONS MMT
-- ,MTL_SYSTEM_ITEMS MSI
,OPI_DBI_CONC_PROG_RUN_LOG PRL
,MTL_PARAMETERS MP
WHERE prl.driving_table_code (+)= 'MMT' --bug 4704813
AND prl.etl_type (+)= 'INVENTORY'
AND prl.load_type (+)= 'INIT'
AND mmt.transaction_id >= nvl(prl.from_bound_id,0) --bug 4704813
-- there is no condition on to_bound as we get all quantity from mmt but push to fact flag is set to No based
-- on to_bound_id in select clause
AND mmt.organization_id = prl.bound_level_entity_id (+) -- outer join as process orgs are not available in log table
AND mmt.transaction_date >= g_global_start_date -- to avoid any backdated transactions
AND mmt.organization_id = mp.organization_id
-- AND mmt.organization_id = msi.organization_id
-- AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id)
AND NVL(mmt.owning_tp_type,2) = 2
AND NVL(mmt.logical_transaction,0) <> 1
AND mmt.transaction_type_id not in (73,25,26,90,91,92,55,56,57,58,87,88,89,24,28,80) --
AND MMT.TRANSACTION_ACTION_ID NOT IN (24)
-- or should we use AND MMT.TRANSACTION_ACTION_ID NOT IN (5,30,24,40,41,42,50,51,52)
-- 82,83,84 inventory lot split/merge/translate are getting excluded by second condition
GROUP BY
mmt.organization_id,
mmt.subinventory_code,
NVL(mmt.cost_group_id, mp.default_cost_group_id),
mmt.inventory_item_id,
TRUNC(mmt.transaction_date) ,
mp.process_enabled_flag
, mmt.opm_costed_flag
,mmt.transaction_id
,prl.to_bound_id
,'MMT'
UNION ALL
SELECT /*+ use_hash(mta1) use_hash(mmt1) */
moq.organization_id,
nvl(moq.subinventory_code,-1) subinventory_code,
nvl(moq.cost_group_id, mp.default_cost_group_id),
moq.inventory_item_id,
null transaction_date,
sum(moq.transaction_quantity) onhand_qty,
null onhand_qty_draft,
'N' push_to_fact_flag,
NULL source,
'MOQ' transaction_source
FROM MTL_ONHAND_QUANTITIES MOQ
,MTL_PARAMETERS MP
,MTL_SYSTEM_ITEMS MSI
WHERE moq.organization_id = mp.organization_id
AND moq.organization_id = msi.organization_id
AND moq.inventory_item_id = msi.inventory_item_id
GROUP BY moq.organization_id,
NVL(moq.subinventory_code,-1),
NVL(moq.cost_group_id, mp.default_cost_group_id),
moq.inventory_item_id);
l_debug_msg := 'Inserted into staging table OPI_DBI_ONH_QTY_STG - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data into OPI_DBI_ONHAND_STG ';
INSERT /*+ append parallel(OPI_DBI_ONH_QTY_STG) */
INTO OPI_DBI_ONHAND_STG
(organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
onhand_value_b_draft,
onhand_value_b,
wip_value_b_draft,
wip_value_b,
source,
push_to_fact_flag,
transaction_source)
-- note: staging tables do not have who columns
(SELECT
mta.organization_id,
decode(mmt.transaction_action_id,
2,decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,mmt.transfer_subinventory),
3, decode(sign(mta.primary_quantity), sign(mmt.primary_quantity), mmt.subinventory_code,
decode(sign(mta.primary_quantity),-1,mmt.subinventory_code, mmt.transfer_subinventory)), -- Bug 5490217
28,decode(sign(mta.primary_quantity), -1, mmt.subinventory_code, mmt.transfer_subinventory),
24, nvl(mmt.subinventory_code,'-1'),mmt.subinventory_code) subinventory_code,
-- in case its wip related record then
decode(mta.accounting_line_type,7,nvl(we.primary_item_id,-1),mta.inventory_item_id) inventory_item_id,
trunc(mta.transaction_date) transaction_date,
0 onhand_value_b_draft,
sum(decode(mta.accounting_line_type,1,mta.base_transaction_value,0)) onhand_value_b,
0 wip_value_b_draft,
sum(decode(mta.accounting_line_type,'7',
decode(we.entity_type,1,mta.base_transaction_value,
2,mta.base_transaction_value,
3,mta.base_transaction_value,
4,mta.base_transaction_value,
5,mta.base_transaction_value,
8,mta.base_transaction_value,0),0)) wip_value_b,
1 source,
case when mta.transaction_id < prl.to_bound_id then 'Y'
else 'N'
end push_to_fact_flag,
'MTA' transaction_source
FROM mtl_transaction_accounts mta
,mtl_material_transactions mmt
,wip_entities we
,opi_dbi_conc_prog_run_log prl
WHERE prl.driving_table_code = 'MMT'
AND prl.etl_type = 'INVENTORY'
AND prl.load_type = 'INIT'
AND mta.transaction_id >= prl.from_bound_id -- changing bound to MTA bug 4576545
-- there is no condition on to_bound as we get all value from mmt-mta but push to fact flag is set to No based
-- on to_bound_id in select clause
AND mta.organization_id = prl.bound_level_entity_id (+) -- outer join as process orgs are not available in log table
-- changing bound to MTA bug 4576545
AND mmt.transaction_date >= g_global_start_date -- to avoid any backdated transactions
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type in (1,7)
-- in case of transaction source type id is 5 then join with wip entities table to get the wip assembly id
AND decode(mta.accounting_line_type,7,mta.transaction_source_id,null) = we.wip_entity_id(+)
GROUP BY
mta.organization_id,
decode(mmt.transaction_action_id,
2,decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,mmt.transfer_subinventory),
3, decode(sign(mta.primary_quantity), sign(mmt.primary_quantity), mmt.subinventory_code,
decode(sign(mta.primary_quantity),-1,mmt.subinventory_code, mmt.transfer_subinventory)), -- Bug 5490217
28,decode(sign(mta.primary_quantity), -1, mmt.subinventory_code, mmt.transfer_subinventory),
24, nvl(mmt.subinventory_code,'-1'),mmt.subinventory_code),
-- in case its wip related record then
decode(mta.accounting_line_type,7,nvl(we.primary_item_id,-1),mta.inventory_item_id),
trunc(mta.transaction_date),
case when mta.transaction_id < prl.to_bound_id then 'Y'
else 'N'
end
UNION ALL
/* if we implement the commented code to get OPM open job balance here itself there are some changes required
to get_wip_balance api */
-- WIP value decodes
--1. WIP value is shown against the product and not the ingredient
--2. In OPM one job can yield multiple products.
--So cost allocation has to be done. GTV is joined with gmdtl only
--for WIP records. For INV records the query should nto join
--with gmdtl but still return 1 records and hence the outer join.
--gmdlt can have multiple records for a doc id based on how many
--main product the job can yield. So for jobs where multiple products
--are yielded there is a cartesian product.
--gtv.line_type is 1 for product yields, -1 for ingredient
--issues and 2 for co-products.
--For REsources? so when there are multiple products
--and gtv.line_type is other than 1 its multiplied by
--cost allocation factor.
--for product lines its multiplied by 1.
--when gtv.line_type =1 and gtv.inventory_item_id is not same
--as gmdtl then its multiplied by zero to net affect the cartesian
SELECT
gtv.organization_id,
nvl(gtv.subinventory_code,-1) subinventory_code,
decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id) inventory_item_id,
trunc(gtv.transaction_date) transaction_date,
sum(onhand_val_b_draft),
sum(onhand_val_b),
sum(wip_val_b_draft*(decode(gtv.line_type,1,
decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
-1,gmdtl.cost_alloc,
2,gmdtl.cost_alloc,
gmdtl.cost_alloc)))
wip_value_b_draft,
sum(wip_val_b*(decode(gtv.line_type,1,
decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
-1,gmdtl.cost_alloc,
2,gmdtl.cost_alloc,
gmdtl.cost_alloc)))
wip_value_b,
2 source,
CASE WHEN gtv.final_posting_date < prl.to_bound_date
OR gtv.final_posting_date IS NULL /* for draft posted -- recs */
THEN 'Y' ELSE 'N' END push_to_fact_flag, --
--'Y' push_to_fact_flag, --
'GTV' transaction_source
-- decode(gbh,gl_posted_ind,0,'OPJ','GTV') transaction_source
FROM (select gtv.organization_id,
decode(gtv.journal_line_type,'INV',gtv.subinventory_code,NULL) subinventory_code,
gtv.line_type, -- amit has added this
gtv.inventory_item_id,
trunc(gtv.transaction_date) transaction_date,
decode(gtv.journal_line_type,'WIP',gtv.doc_id,NULL)
doc_id,--Gtv.doc_id is populated in inner select only when journal_line_type is WIP.
gtv.journal_line_type,
gtv.event_class_code,
gtv.final_posting_date,
sum(decode(journal_line_type,'INV',
decode(gtv.accounted_flag,'D',txn_base_value,0),0)) --
--decode('D','D',txn_base_value,0),0))
onhand_val_b_draft,
sum(decode(journal_line_type,'INV',
decode(gtv.accounted_flag,NULL,txn_base_value,0),0)) --
--decode('D',NULL,txn_base_value,0),0))
onhand_val_b,
sum(decode(journal_line_type,'WIP',
decode(gtv.accounted_flag,'D',txn_base_value,0),0)) --
--decode('D','D',txn_base_value,0),0))
wip_val_b_draft,
sum(decode(journal_line_type,'WIP',
decode(gtv.accounted_flag,NULL,txn_base_value,0),0)) --
--decode('D',NULL,txn_base_value,0),0))
wip_val_b
from gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp --bug 4768058
where --gtv.final_posting_date >= g_global_start_date -- Not required
--and --
gtv.journal_line_type IN ('WIP','INV')
and gtv.transaction_date >= g_global_start_date
and gtv.ledger_id = tmp.ledger_id --bug 4768058
and gtv.legal_entity_id = tmp.legal_entity_id
and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
and gtv.organization_id = tmp. organization_id
group by gtv.organization_id,
decode(gtv.journal_line_type,'INV',gtv.subinventory_code,NULL),
gtv.line_type,
gtv.inventory_item_id,
trunc(gtv.transaction_date),
decode(gtv.journal_line_type,'WIP',gtv.doc_id,NULL),
gtv.journal_line_type,
gtv.event_class_code,
gtv.final_posting_date) gtv,
gme_material_details gmdtl,
opi_dbi_conc_prog_run_log prl
-- gme_batch_headers gbh
WHERE gtv.doc_id = gmdtl.batch_id(+)
AND nvl(gmdtl.line_type,1) = 1 -- (MK) identified issue during UT. Need to be reviewed with Vikas/David
AND prl.driving_table_code = 'GTV'
AND prl.etl_type = 'INVENTORY'
AND prl.load_type = 'INIT'
-- AND gtv.document_id = gbh.batch_id(+)
GROUP BY gtv.organization_id,
decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id),
gtv.transaction_date,
CASE WHEN gtv.final_posting_date < prl.to_bound_date --
OR gtv.final_posting_date IS NULL /* for draft posted
recs */ THEN 'Y' ELSE 'N' END,
--'Y', --
nvl(gtv.subinventory_code,-1)
UNION ALL
SELECT wta.organization_id,
'-1' subinventory_code,
nvl(we.primary_item_id, -1) inventory_item_id,
trunc(wta.transaction_date) transaction_date,
null onhand_value_b_draft,
null onhand_value_b,
null wip_value_b_draft,
sum(nvl(wta.base_transaction_value,0)) wip_value_b,
1 source,
'Y' push_to_fact_flag,
'WTA' transaction_source
FROM wip_transaction_accounts wta,
wip_entities we,
opi_dbi_conc_prog_run_log prl
WHERE prl.etl_type = 'INVENTORY'
AND prl.driving_table_code = 'WTA'
AND prl.load_type = 'INIT'
AND wta.accounting_line_type = 7
AND wta.transaction_id >= prl.from_bound_id
AND wta.transaction_id < prl.to_bound_id
AND wta.transaction_date >= g_global_start_date -- to avoid any backdated txns before GSD
AND we.wip_entity_id = wta.wip_entity_id
AND we.entity_type in (1, 2, 3, 4, 5, 8)
GROUP BY
wta.organization_id,
we.primary_item_id,
wta.transaction_date
HAVING sum(wta.base_transaction_value) <> 0
UNION ALL
SELECT
wpb.organization_id organization_id,
'-1' subinventory_code,
we.primary_item_id inventory_item_id, -- rows with item_id null are not selected.
g_global_start_date transaction_date,
null onhand_value_b_draft,
null onhand_value_b,
null wip_value_b_draft,
sum(nvl(tl_resource_in,0)
+ nvl(tl_overhead_in,0)
+ nvl(tl_outside_processing_in,0)
+ nvl(pl_material_in,0)
+ nvl(pl_material_overhead_in,0)
+ nvl(pl_resource_in,0)
+ nvl(pl_overhead_in,0)
+ nvl(pl_outside_processing_in,0)
- nvl(tl_material_out,0)
- nvl(tl_material_overhead_out,0)
- nvl(tl_resource_out,0)
- nvl(tl_overhead_out,0)
- nvl(tl_outside_processing_out,0)
- nvl(pl_material_out,0)
- nvl(pl_material_overhead_out,0)
- nvl(pl_resource_out,0)
- nvl(pl_overhead_out,0)
- nvl(pl_outside_processing_out,0)
- nvl(tl_material_var,0)
- nvl(tl_material_overhead_var,0)
- nvl(tl_resource_var,0)
- nvl(tl_outside_processing_var,0)
- nvl(tl_overhead_var,0)
- nvl(pl_material_var,0)
- nvl(pl_material_overhead_var,0)
- nvl(pl_resource_var,0)
- nvl(pl_overhead_var,0)
- nvl(pl_outside_processing_var,0)) wip_value_b,
1,
'N',
'WPB' transaction_source
FROM wip_period_balances wpb,
wip_entities we
WHERE wpb.wip_entity_id = we.wip_entity_id
AND we.entity_type in (1, 2, 3, 4, 5, 8)
AND we.primary_item_id IS NOT NULL
GROUP BY
wpb.organization_id ,
we.primary_item_id
UNION ALL
--the query should be driven by gbh with index on * gl_posted_ind.
--Otherwise it may end up doing FTS of gtv * which may be expensive
SELECT gtv.organization_id,
'-1' subinventory_code,
--gmdtl.inventory_item_id,
decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id) inventory_item_id,
trunc(gtv.transaction_date) transaction_date,
0 onhand_value_b_draft ,
0 onhand_value_b,
sum (decode(accounted_flag,'D',gtv.txn_base_value *
decode(gtv.line_type,1,decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
-1, gmdtl.cost_alloc,
2, gmdtl.cost_alloc,
gmdtl.cost_alloc),0)) wip_value_b_draft,
sum (decode(accounted_flag, 'D', 0, gtv.txn_base_value *
decode(gtv.line_type,1,decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
-1, gmdtl.cost_alloc,
2, gmdtl.cost_alloc,
gmdtl.cost_alloc))) wip_value_b,
2 source,
'N' push_to_fact_flag,
'OPJ' transaction_source
FROM gme_batch_header gbh,
gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp, --Bug 4768058
gme_material_details gmdtl
WHERE gtv.journal_line_type = 'WIP'
AND nvl(gtv.accounted_flag,'F') <> 'N'
AND gtv.transaction_date >= g_global_start_date
AND gtv.doc_id = gmdtl.batch_id
AND gmdtl.line_type = 1
AND gbh.batch_id = gtv.doc_id
AND gbh.gl_posted_ind = 0
and gtv.ledger_id = tmp.ledger_id --bug 4768058
and gtv.legal_entity_id = tmp.legal_entity_id
and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
and gtv.organization_id = tmp. organization_id
GROUP BY
gtv.organization_id,
decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id),
trunc(gtv.transaction_date));
l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
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,
onhand_qty,
intransit_qty,
primary_uom,
onhand_value_b,
intransit_value_b,
wip_value_b,
conversion_rate,
sec_conversion_rate,
ONHAND_QTY_DRAFT,
INTRANSIT_QTY_DRAFT,
ONHAND_VALUE_B_DRAFT,
INTRANSIT_VALUE_B_DRAFT,
WIP_VALUE_B_DRAFT,
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,
nvl(s.subinventory_code,-1),
s.inventory_item_id,
s.transaction_date,
s.onhand_qty + s.ONHAND_QTY_DRAFT onhand_qty ,
s.intransit_qty + s.INTRANSIT_QTY_DRAFT intransit_qty,
s.primary_uom,
s.onhand_value_b +s.ONHAND_VALUE_B_DRAFT onhand_value_b,
s.intransit_value_b + s.INTRANSIT_VALUE_B_DRAFT intransit_value_b ,
s.wip_value_b + s.WIP_VALUE_B_DRAFT wip_value_b,
rate.conversion_rate,
rate.sec_conversion_rate,
s.ONHAND_QTY_DRAFT,
s.INTRANSIT_QTY_DRAFT,
s.ONHAND_VALUE_B_DRAFT,
s.INTRANSIT_VALUE_B_DRAFT,
s.WIP_VALUE_B_DRAFT,
source,
g_user_id,
g_login_id,
sysdate,
g_user_id,
sysdate
FROM
(SELECT /*+ parallel(activity) */
activity.organization_id,
activity.subinventory_code,
activity.inventory_item_id,
activity.transaction_date,
nvl(SUM(onhand_qty),0) onhand_qty,
nvl(SUM(intransit_qty),0) intransit_qty,
MIN(msi.primary_uom_code) primary_uom,
nvl(SUM(onhand_value_b),0) onhand_value_b,
nvl(SUM(intransit_value_b),0) intransit_value_b,
nvl(SUM(wip_value_b),0) wip_value_b,
nvl(SUM(onhand_qty_draft),0) onhand_qty_draft,
nvl(SUM(INTRANSIT_QTY_DRAFT),0) INTRANSIT_QTY_DRAFT,
nvl(SUM(ONHAND_VALUE_B_DRAFT),0) ONHAND_VALUE_B_DRAFT,
nvl(SUM(INTRANSIT_VALUE_B_DRAFT),0) INTRANSIT_VALUE_B_DRAFT,
nvl(SUM(WIP_VALUE_B_DRAFT),0) WIP_VALUE_B_DRAFT,
activity.source
FROM
(SELECT /*+ parallel(opi_dbi_onhand_stg) */
organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
0 onhand_qty,
0 intransit_qty,
primary_uom,
onhand_value_b,
0 intransit_value_b,
wip_value_b,
0 onhand_qty_draft,
0 intransit_qty_draft,
onhand_value_b_draft onhand_value_b_draft,
0 intransit_value_b_draft,
wip_value_b_draft wip_value_b_draft,
source
FROM OPI_DBI_ONHAND_STG
where push_to_fact_flag = 'Y'
UNION ALL
select /*+ parallel(OPI_DBI_ONH_QTY_STG) */
fact.organization_id,
fact.subinventory_code,
fact.inventory_item_id,
fact.transaction_date,
onhand_qty,
0 intransit_qty,
NULL primary_uom,
0 onhand_value_b,
0 intransit_value_b,
0 wip_value_b,
onhand_qty_draft onhand_qty_draft,
0 intransit_qty_draft,
0 onhand_value_b_draft,
0 intransit_value_b_draft,
0 wip_value_b_draft,
source
FROM OPI_DBI_ONH_QTY_STG fact
WHERE push_to_fact_flag = 'Y'
UNION All
select /*+ parallel(opi_dbi_intransit_stg) */
organization_id,
NULL,
inventory_item_id,
transaction_date,
0 onhand_qty,
intransit_qty,
primary_uom,
0 onhand_value_b,
intransit_value_b,
0 wip_value_b,
0 onhand_qty_draft,
intransit_qty_draft intransit_qty_draft,
0 onhand_value_b_draft,
intransit_value_draft_b intransit_value_b_draft,
0 wip_value_b_draft,
source
from OPI_DBI_INTRANSIT_STG
union all
select /*+ parallel(OPI_DBI_INV_BEG_STG) */
fact.organization_id,
fact.subinventory_code,
fact.inventory_item_id,
fact.transaction_date,
onhand_qty,
intransit_qty,
NULL primary_uom,
onhand_value_b,
intransit_value_b,
wip_value_b,
0 onhand_qty_draft,
0 intransit_qty_draft,
0 onhand_value_b_draft,
0 intransit_value_b_draft,
0 wip_value_b_draft,
decode(mp.process_enabled_flag,'Y',2,1) source
FROM OPI_DBI_INV_BEG_STG fact,
mtl_parameters mp
WHERE fact.organization_id =mp.organization_id
union all
select /*+ parallel(OPI_DBI_OPM_INV_STG) */
organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
onhand_qty,
intransit_qty,
primary_uom,
onhand_value_b,
intransit_value_b,
wip_value_b,
0 onhand_qty_draft,
0 intransit_qty_draft,
0 onhand_value_b_draft,
0 intransit_value_b_draft,
0 wip_value_b_draft,
3 source
FROM opi_dbi_opm_inv_stg
) activity,
mtl_system_items msi
WHERE activity.organization_id = msi.organization_id
AND activity.inventory_item_id =msi.inventory_item_id
group by
activity.organization_id,
activity.subinventory_code,
activity.inventory_item_id,
activity.transaction_date,
activity.source
having
nvl(SUM(onhand_qty),0) <> 0
OR nvl(SUM(intransit_qty),0) <> 0
OR nvl(SUM(onhand_value_b),0) <>0
OR nvl(SUM(intransit_value_b),0) <> 0
OR nvl(SUM(wip_value_b),0) <> 0
OR nvl(SUM(onhand_qty_draft),0) <> 0
OR nvl(SUM(INTRANSIT_QTY_DRAFT),0) <> 0
OR nvl(SUM(ONHAND_VALUE_B_DRAFT),0) <> 0
OR nvl(SUM(INTRANSIT_VALUE_B_DRAFT),0) <> 0
OR nvl(SUM(WIP_VALUE_B_DRAFT),0)<> 0
) s,
(select /*+ no_merge parallel(rates) */
organization_id,
transaction_date,
conversion_rate,
sec_conversion_rate
from OPI_DBI_CONVERSION_RATES
) rate
where s.organization_id = rate.organization_id
and s.transaction_date = rate.transaction_date;
l_debug_msg := 'Inserted into staging table OPI_DBI_INV_VALUE_F - ' || SQL%ROWCOUNT || ' rows. ';
SELECT 1
FROM OPI_DBI_INV_TYPE_CODES
WHERE rownum < 2;
l_debug_msg := 'Inserting type codes. ';
INSERT INTO opi_dbi_inv_type_codes (inventory_type) VALUES ('ONH');
INSERT INTO opi_dbi_inv_type_codes (inventory_type) VALUES ('INT');
INSERT INTO opi_dbi_inv_type_codes (inventory_type) VALUES ('WIP');
OPI_DBI_BOUNDS_PKG.write (g_pkg_name, l_proc_name,l_stmt_num,'Please run the concurrent program: Initial Load - Update Inventory Value and Turns Base Summary, after fixing missing currency rates.');
BIS_COLLECTION_UTILITIES.wrapup( -- updates the log
p_status => TRUE,
p_count => 0, -- for 5.0 only. will put meaningful number in 6.0
p_message => 'Successfully loaded inventory value base table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
);