[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_debug_msg := 'Inserting data Into OPI_DBI_ONHAND_STG ';
INSERT /*+ append */ INTO OPI_DBI_ONHAND_STG
(organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
onhand_qty_draft,
onhand_qty,
onhand_value_b_draft,
onhand_value_b,
wip_value_b_draft,
wip_value_b,
source,
push_to_fact_flag,
transaction_source)
SELECT mmt1.ORGANIZATION_ID,
mmt1.SUBINVENTORY_CODE,
mmt1.INVENTORY_ITEM_ID,
/* backdated transactions prior to GSD are bucketed on GSD in incremental load */
decode(sign(mmt1.transaction_date - g_global_start_date), -1,
g_global_start_date, mmt1.transaction_date) transaction_date,
0 onhand_qty_draft, -- draft qty is applicable to process orgs only.
nvl(mmt1.onhand_qty,0) onhand_qty,
0 onhand_value_b_draft, -- draft value is applicable to process orgs only.
nvl(mta1.base_transaction_value,0) onhand_value_b,
null wip_value_b_draft, -- wip value in this table is populated only in initial load
null wip_value_b, -- in incr load wip incr extracts are separate
1 source,
null push_to_fact_flag, -- used only in initial load
'MTA' transaction_source -- used only in initial load
FROM
(SELECT mta.organization_id,
decode(mmt3.transaction_action_id,
2,decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code,mmt3.transfer_subinventory),
3, decode(sign(mta.primary_quantity), sign(mmt3.primary_quantity), mmt3.subinventory_code,
decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code, mmt3.transfer_subinventory)), -- Bug 5490217
28,decode(sign(mta.primary_quantity), -1, mmt3.subinventory_code, mmt3.transfer_subinventory),
24,nvl(mmt3.subinventory_code,'-1'),mmt3.subinventory_code) subinventory_code,
mta.inventory_item_id,
trunc(mta.transaction_date) transaction_date,
sum(mta.base_transaction_value) base_transaction_value
FROM mtl_transaction_accounts mta,
mtl_material_transactions mmt3,
opi_dbi_conc_prog_run_log prl,
mtl_parameters mp
WHERE mta.accounting_line_type = 1
AND mta.transaction_id >= prl.from_bound_id
AND mta.transaction_id < to_bound_id
AND prl.etl_type = 'INVENTORY'
AND prl.load_type = 'INCR'
AND prl.driving_table_code = 'MMT'
AND prl.bound_level_entity_id = mta.organization_id
AND mmt3.transaction_id = mta.transaction_id
AND prl.bound_level_entity_id = mp.organization_id
AND nvl(mp.process_enabled_flag,'N') <> 'Y'
GROUP BY mta.inventory_item_id,
decode(mmt3.transaction_action_id,
2,decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code,mmt3.transfer_subinventory),
3, decode(sign(mta.primary_quantity), sign(mmt3.primary_quantity), mmt3.subinventory_code,
decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code, mmt3.transfer_subinventory)), -- Bug 5490217
28, decode(sign(mta.primary_quantity), -1, mmt3.subinventory_code, mmt3.transfer_subinventory),
24, nvl(mmt3.subinventory_code,'-1'),mmt3.subinventory_code),
mta.organization_id,
trunc(mta.transaction_date)
) mta1,
(
-- csheu 3/31/2003. Filter out consigned Inventory transactions
-- Added the hint to fix bug #3223207
SELECT /*+ index(mmt, MTL_MATERIAL_TRANSACTIONS_U1) */
mmt.organization_id,
decode(mmt.transaction_action_id,24,nvl(mmt.subinventory_code,-1),mmt.subinventory_code) subinventory_code,
mmt.inventory_item_id,
trunc(mmt.transaction_date) transaction_date,
nvl(sum(decode(mmt.transaction_action_id,24,0,mmt.primary_quantity)),0) onhand_qty
FROM mtl_material_transactions mmt,
mtl_parameters mp,
opi_dbi_conc_prog_run_log prl
WHERE prl.etl_type = 'INVENTORY'
AND prl.load_type = 'INCR'
AND prl.driving_table_code = 'MMT'
AND mmt.organization_id = prl.bound_level_entity_id
AND mmt.transaction_type_id not in (73, 25, 26, 90, 91, 92,55, 56, 57, 58, 87, 88, 89)
AND mmt.organization_id = nvl(mmt.owning_organization_id,mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2 -- exclude consigned inventory
AND nvl(mmt.logical_transaction,-99) <> 1 -- 11.5.10 changes exclude logical txns
AND mmt.transaction_id >= prl.from_bound_id
AND mmt.transaction_id < prl.to_bound_id
AND prl.bound_level_entity_id = mp.organization_id
AND nvl(mp.process_enabled_flag,'N') <> 'Y' -- only discrete orgs
GROUP BY mmt.organization_id,
decode(mmt.transaction_action_id,24,nvl(mmt.subinventory_code,-1),mmt.subinventory_code) ,
mmt.inventory_item_id,
trunc(mmt.transaction_date)
--, msi.primary_uom_code
) mmt1
WHERE mta1.organization_id(+) = mmt1.organization_id -- expense item txns dont have recs in mta
AND mta1.inventory_item_id(+) = mmt1.inventory_item_id
AND mta1.transaction_date(+) = mmt1.transaction_date
AND mta1.subinventory_code(+) = mmt1.subinventory_code
AND (mmt1.onhand_qty <> 0 or mta1.base_transaction_value <> 0);
l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data Into OPI_DBI_ONHAND_STG ';
INSERT /*+ append */ INTO OPI_DBI_ONHAND_STG
(organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
onhand_qty_draft,
onhand_qty,
onhand_value_b_draft,
onhand_value_b,
wip_value_b_draft,
wip_value_b,
source,
push_to_fact_flag,
transaction_source
)
SELECT mmt.organization_id,
gtv.subinventory_code,
mmt.inventory_item_id,
-- backdated transactions prior to GSD are bucketed on GSD in incremental load
decode(sign(trunc(mmt.transaction_date) - g_global_start_date), -1,g_global_start_date, trunc(mmt.transaction_date)) transaction_date,
sum(case when gtv.accounted_flag = 'D' then -- changed mmt.opm_costed_flag to gtv.accounted_flag
decode(mmt.transaction_action_id,2
,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
,28
,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
,mmt.primary_quantity)
else 0 end) onhand_qty_draft, --Bug 4704689
-- sum(case when mmt.opm_costed_flag IS NULL then mmt.primary_quantity else 0 end) onhand_qty,
sum(case when gtv.accounted_flag IS NULL then -- changed mmt.opm_costed_flag to gtv.accounted_flag
decode(mmt.transaction_action_id,2
,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
,28
,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
,mmt.primary_quantity)
else 0 end) onhand_qty, --BUG 4704689
sum(gtv.onhand_value_b_draft) onhand_value_b_draft,
sum(gtv.onhand_value_b) onhand_value_b,
null wip_value_b_draft, -- wip value in this table is populated only in initial load
null wip_value_b, -- in incr load wip incr extracts are separate
2 source, -- 1 for discrete 2 for process orgs 3 for old opm data
null push_to_fact_flag, -- used only in initial load
'GTV' transaction_source -- used only in initial load
FROM
(SELECT gtv.transaction_id,
nvl(gtv.subinventory_code,-1) subinventory_code,
sum(txn_base_value) onhand_value_b,
0 onhand_value_b_draft,
gtv.accounted_flag
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp, --Bug 4768058
opi_dbi_conc_prog_run_log prl
WHERE prl.driving_table_code = 'GTV'
AND prl.load_type = 'INCR'
AND prl.etl_type = 'INVENTORY'
AND gtv.journal_line_type = 'INV'
--AND gtv.transaction_source = 'INVENTORY' --bug 4870029
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.final_posting_date >= prl.from_bound_date
AND gtv.final_posting_date < prl.to_bound_date
AND gtv.accounted_flag IS NULL
GROUP BY gtv.transaction_id,
nvl(gtv.subinventory_code,-1),
gtv.accounted_flag
UNION ALL
-- union all is being done here assuming that both the union alls are driven by
-- separate indexes on GTV. one by final_posted_date and other by accounted_flag
SELECT gtv.transaction_id,
nvl(gtv.subinventory_code,-1) subinventory_code,
0 onhand_value_b,
sum(txn_base_value) onhand_value_b_draft,
gtv.accounted_flag
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp --Bug 4768058
WHERE gtv.journal_line_type IN ('INV')
--AND gtv.transaction_source = 'INVENTORY' --bug 4870029
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.accounted_flag = 'D' --
GROUP BY gtv.transaction_id,
nvl(gtv.subinventory_code,-1),
gtv.accounted_flag
) gtv,
mtl_material_transactions mmt,
mtl_system_items msi
WHERE mmt.transaction_id = gtv.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
GROUP BY mmt.organization_id,
gtv.subinventory_code,
mmt.inventory_item_id,
-- backdated transactions prior to GSD are bucketed on GSD in incremental load
decode(sign(trunc(mmt.transaction_date) - g_global_start_date), -1,g_global_start_date, trunc(mmt.transaction_date));
l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Selecting from_mta bound ';
select min(FROM_BOUND_ID) into from_mta_id --USED IN DISCRETE QUERY
from OPI_DBI_CONC_PROG_RUN_LOG log
where log.driving_table_code= 'MMT'
And log.etl_type = 'INVENTORY'
and FROM_BOUND_ID IS NOT NULL
And log.load_type= 'INCR';
l_debug_msg := 'Selecting from_mta bound ';
select Max(TO_BOUND_ID) into to_mta_id -- USED IN DISCRETE QUERY
from OPI_DBI_CONC_PROG_RUN_LOG log
where log.driving_table_code= 'MMT'
And log.etl_type = 'INVENTORY'
And log.load_type= 'INCR';
l_debug_msg := 'Insert Data into opi_dbi_intr_mip_tmp ';
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
sysdate,
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,
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(
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
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
FROM opi_dbi_intr_mip_tmp mip,
mtl_material_transactions mmt1,
(select
transaction_id,
sum (base_transaction_value) base_transaction_value
from mtl_transaction_accounts
where accounting_line_type = 14
AND transaction_id >= from_mta_id --BOUNDS COLLECTED ABOVE
ANd transaction_id < to_mta_id
group by transaction_id) mta,
mtl_system_items msi_fm,
mtl_system_items msi_to,
OPI_DBI_CONC_PROG_RUN_LOG col,
MTL_PARAMETERS mp
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 mp.organization_id =mmt1.organization_id
and mp.process_enabled_flag = mip.owning_org_process_flag --make sure only logical collected incase of D-> P and P->D
AND mip.from_organization_id = col.bound_level_entity_id
AND mta.transaction_id (+)= mmt1.transaction_id -- to collect expense item
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= 'INCR'
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
SELECT
mmt1.organization_id organization_id,
'N' process_flag, -- Bug fix: 5362465, source is only discrete here
mmt1.inventory_item_id,
0 qty,
sum (base_transaction_value) value,
msi_fm.primary_uom_code,
trunc(mmt1.transaction_date) trx_date
FROM mtl_material_transactions 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 --col.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= 'INCR'
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
sysdate,
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,
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),
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,
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),
22, mmt1.primary_quantity ,-- Absolute value fix, no need of conversion ??
15, -1 * mmt1.primary_quantity
)),0)) Draft_qty,
SUM(DECODE(gtv.accounted_flag --
--SUM(DECODE('D'
,'D',base_transaction_value)) Draft_Value,
SUM(DECODE(gtv.accounted_flag --
--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,
mtl_material_transactions 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 4760483
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= 'INCR'
and gtv.ledger_id = tmp.ledger_id --Bug 4760483
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.final_posting_date >= col.FROM_BOUND_DATE --Bug 4968995
And gtv.final_posting_date < col.TO_BOUND_DATE -- Bug 4968995
--And col.FROM_BOUND_DATE >=gtv.final_posting_date
--And col.TO_BOUND_DATE < gtv.final_posting_date
Group by transaction_id, accounted_flag
UNION ALL
select transaction_id,
sum(txn_base_value) base_transaction_value ,
accounted_flag
from gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp --Bug 4760483
where --gtv.transaction_source = 'INVENTORY' and --bug 4870029
gtv.journal_line_type = 'ITR'
and gtv.ledger_id = tmp.ledger_id --Bug 4760483
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.accounted_flag ='D' --
Group by transaction_id, accounted_flag
) gtv,
mtl_system_items msi_fm,
mtl_system_items msi_to,
mtl_parameters mp
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_id=mp.organization_id
and mp.process_enabled_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
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
OR (sum(draft_value) = 0 AND sum(draft_qty) = 0); -- Bug 4968293
l_debug_msg := 'Inserted into staging table opi_dbi_intransit_stg for OPM Post R12 - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data Into OPI_DBI_WIP_STG for ODM';
INSERT /*+ append */
INTO OPI_DBI_WIP_STG (
organization_id,
inventory_item_id,
transaction_date,
primary_uom,
wip_value_b,
wip_value_b_draft,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT
wip_activity.organization_id,
wip_activity.inventory_item_id,
decode (sign (transaction_date - g_global_start_date),
-1, g_global_start_date,
transaction_date) transaction_date,
msi.primary_uom_code,
sum (wip_value) wip_value_b,
0 wip_value_b_draft,
1,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
FROM
(-- Added the hint to fix bug #3223207
SELECT /*+ index(mta, mtl_transaction_accounts_n1) */
mta.organization_id organization_id,
we.primary_item_id inventory_item_id,
trunc (mta.transaction_date) transaction_date,
sum (nvl (mta.base_transaction_value,0)) wip_value
FROM
mtl_transaction_accounts mta,
Opi_dbi_conc_prog_run_log prl,
wip_entities we
WHERE
prl.driving_table_code = 'MMT'
AND prl.load_type = 'INCR'
AND prl.etl_type = 'INVENTORY'
AND prl.bound_level_entity_code = 'ORGANIZATION'
AND prl.bound_level_entity_id = mta.organization_id
AND mta.transaction_source_type_id = 5
AND mta.accounting_line_type = 7
AND mta.transaction_id >= prl.from_bound_id
AND mta.transaction_id < to_bound_id
AND we.wip_entity_id = mta.transaction_source_id
AND we.entity_type in (1, 2, 3, 4, 5, 8)
AND we.primary_item_id is not null
GROUP BY
mta.organization_id,
we.primary_item_id,
mta.transaction_date
UNION ALL
SELECT
wta.organization_id organization_id,
we.primary_item_id inventory_item_id,
trunc (wta.transaction_date) transaction_date,
sum (nvl (wta.base_transaction_value,0)) wip_value
FROM
wip_transaction_accounts wta,
Opi_dbi_conc_prog_run_log prl,
wip_entities we
WHERE
prl.driving_table_code = 'WTA'
AND prl.load_type = 'INCR'
AND prl.etl_type = 'INVENTORY'
AND wta.accounting_line_type = 7
--AND prl.bound_level_entity_code = 'ORGANIZATION'
--AND prl.bound_level_entity_id = wta.organization_id
AND wta.transaction_id >= prl.from_bound_id
AND wta.transaction_id < prl.to_bound_id
AND we.wip_entity_id = wta.wip_entity_id
AND we.entity_type in (1, 2, 3, 4, 5, 8)
AND we.primary_item_id is not null
GROUP BY
wta.organization_id,
we.primary_item_id,
wta.transaction_date
) wip_activity,
mtl_system_items_b msi
WHERE msi.organization_id = wip_activity.organization_id
AND wip_activity.inventory_item_id = msi.inventory_item_id
GROUP BY
wip_activity.organization_id,
wip_activity.inventory_item_id,
transaction_date,
msi.primary_uom_code
HAVING sum (wip_value) <> 0;
l_debug_msg := 'Inserted into staging table OPI_DBI_WIP_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data Into OPI_DBI_WIP_STG for OPM';
INSERT /*+ append */
INTO OPI_DBI_WIP_STG (
organization_id,
inventory_item_id,
transaction_date,
primary_uom,
wip_value_b,
wip_value_b_draft,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT
wip_activity.organization_id,
wip_activity.inventory_item_id,
decode (sign (transaction_date - g_global_start_date),
-1, g_global_start_date,
transaction_date) transaction_date,
msi.primary_uom_code,
sum (wip_value_b) wip_value_b,
sum (wip_value_b_draft) wip_value_b_draft,
2,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
FROM
(
SELECT
gmdtl.organization_id,
gmdtl.inventory_item_id,
trunc (gtv.transaction_date) transaction_date,
sum (gtv.txn_base_value * decode (gtv.line_type,
1, decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0),
-1, gmdtl.cost_alloc,
2, gmdtl.cost_alloc,
gmdtl.cost_alloc)) wip_value_b,
0 wip_value_b_draft
FROM
gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp,--Bug 4768058
gme_material_details gmdtl,
opi_dbi_conc_prog_run_log prl
WHERE prl.driving_table_code = 'GTV'
AND prl.load_type = 'INCR'
AND prl.etl_type = 'INVENTORY'
AND gtv.journal_line_type = 'WIP'
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.final_posting_date >= prl.from_bound_date
AND gtv.final_posting_date < prl.to_bound_date
AND gtv.accounted_flag IS NULL
AND gtv.doc_id = gmdtl.batch_id
AND gmdtl.line_type = 1
GROUP BY
gmdtl.organization_id,
gmdtl.inventory_item_id,
trunc (gtv.transaction_date)
UNION ALL
SELECT
gmdtl.organization_id,
gmdtl.inventory_item_id,
trunc (gtv.transaction_date) transaction_date,
0 wip_value_b,
sum (gtv.txn_base_value * decode (gtv.line_type,
1, decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0),
-1, gmdtl.cost_alloc,
2, gmdtl.cost_alloc,
gmdtl.cost_alloc)) wip_value_b_draft
FROM
gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp,--Bug 4760483
gme_material_details gmdtl
WHERE gtv.journal_line_type = 'WIP'
AND gtv.accounted_flag = 'D' --
AND gtv.doc_id = gmdtl.batch_id
AND gmdtl.line_type = 1
and gtv.ledger_id = tmp.ledger_id --Bug 4760483
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
gmdtl.organization_id,
gmdtl.inventory_item_id,
trunc (gtv.transaction_date)
) wip_activity,
mtl_system_items_b msi
WHERE msi.organization_id = wip_activity.organization_id
AND wip_activity.inventory_item_id = msi.inventory_item_id
GROUP BY
wip_activity.organization_id,
wip_activity.inventory_item_id,
transaction_date,
msi.primary_uom_code
HAVING sum (wip_value_b) <> 0
or sum (wip_value_b_draft) <> 0;
l_debug_msg := 'Inserted into staging table OPI_DBI_WIP_STG for OPM - ' || SQL%ROWCOUNT || ' rows. ';
l_debug_msg := 'Inserting data into OPI_DBI_INV_VALUE_F';
(SELECT /*+ index (rate OPI_DBI_CONVERSION_RATES_N2) */
NULL operating_unit,
s.organization_id,
s.subinventory_code,
s.inventory_item_id,
s.transaction_date,
s.onhand_qty,
s.intransit_qty,
s.primary_uom,
s.onhand_value_b,
s.intransit_value_b,
s.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
FROM
(SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_U1) */
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
organization_id,
subinventory_code,
inventory_item_id,
transaction_date,
onhand_qty,
0 intransit_qty,
primary_uom,
onhand_value_b,
0 intransit_value_b,
0 wip_value_b,
onhand_qty_draft,
0 intransit_qty_draft,
onhand_value_b_draft onhand_value_b_draft,
0 intransit_value_b_draft,
0 wip_value_b_draft,
source
FROM OPI_DBI_ONHAND_STG
UNION ALL
select
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
organization_id,
NULL,
inventory_item_id,
transaction_date,
0 onhand_qty,
0 intransit_qty,
primary_uom,
0 onhand_value_b,
0 intransit_value_b,
wip_value_b,
0 onhand_qty_draft,
0 intransit_qty_draft,
0 onhand_value_b_draft,
0 intransit_value_b_draft,
wip_value_b_draft,
source
from opi_dbi_wip_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
) s,
(select
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
) stg
ON
(base.organization_id = stg.organization_id and
base.inventory_item_id = stg.inventory_item_id and
base.transaction_date = stg.transaction_date and
nvl(base.subinventory_code,-1) = nvl(stg.subinventory_code,-1) and
base.source = stg.source
)
WHEN matched THEN
update set
base.onhand_qty = base.onhand_qty + stg.onhand_qty - base.onhand_qty_draft + stg.onhand_qty_draft,
base.intransit_qty = base.intransit_qty + stg.intransit_qty - base.intransit_qty_draft + stg.intransit_qty_draft,
base.onhand_value_b = base.onhand_value_b + stg.onhand_value_b - base.onhand_value_b_draft + stg.onhand_value_b_draft,
base.intransit_value_b = base.intransit_value_b + stg.intransit_value_b - base.intransit_value_b_draft + stg.intransit_value_b_draft,
base.wip_value_b = base.wip_value_b + stg.wip_value_b - base.wip_value_b_draft + stg.wip_value_b_draft,
base.onhand_qty_draft = stg.onhand_qty_draft ,
base.intransit_qty_draft = stg.intransit_qty_draft,
base.onhand_value_b_draft= stg.onhand_value_b_draft,
base.intransit_value_b_draft= stg.intransit_value_b_draft ,
base.wip_value_b_draft = stg.wip_value_b_draft,
base.last_update_date = sysdate,
base.last_update_login = g_login_id,
base.last_updated_by = g_user_id
WHEN not matched THEN
insert (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
)
values (stg.operating_unit,
stg.organization_id,
nvl(stg.subinventory_code,-1),
stg.inventory_item_id,
stg.transaction_date,
stg.onhand_qty+stg.onhand_qty_draft,
stg.intransit_qty+stg.intransit_qty_draft,
stg.primary_uom,
stg.onhand_value_b+stg.onhand_value_b_draft,
stg.intransit_value_b+stg.intransit_value_b_draft,
stg.wip_value_b+stg.wip_value_b_draft,
stg.conversion_rate,
stg.sec_conversion_rate,
stg.onhand_qty_draft,
stg.intransit_qty_draft,
stg.onhand_value_b_draft,
stg.intransit_value_b_draft,
stg.wip_value_b_draft,
stg.Source,
g_user_id,
g_login_id,
sysdate,
g_user_id,
sysdate
);
l_debug_msg := 'Inserted into staging table OPI_DBI_INV_VALUE_F - ' || SQL%ROWCOUNT || ' rows. ';