[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT table_owner
FROM user_synonyms
WHERE synonym_name = p_table_name;
SELECT sysdate INTO l_to_date FROM dual;
INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_fstg m (
m.inventory_item_id,
m.organization_id,
m.order_line_id,
m.top_model_line_id,
m.top_model_item_id,
m.top_model_item_uom,
m.top_model_org_id,
m.customer_id,
m.cogs_val_b_draft,
m.cogs_val_b,
m.cogs_date,
m.source,
m.turns_cogs_flag,
m.internal_flag )
-- 2 Regular sales order -- 8 Internal Sales Order
SELECT /*+ use_hash(mmt)
parallel(log) parallel(mmt) parallel(mta)
parallel(l) parallel(pl) parallel(h)
parallel(cust_acct) parallel(item)*/
mmt.inventory_item_id,
mmt.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
0 cogs_val_b_draft,
mta.base_transaction_value,
mmt.transaction_date,
OPI_SOURCE,
decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
decode(p_lines.order_source_id,10,1,0)
FROM opi_dbi_conc_prog_run_log log,
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
oe_order_lines_all lines, -- child line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.organization_id = log.bound_level_entity_id
AND log.load_type = 'INIT'
AND log.etl_type = 'COGS'
AND mmt.transaction_source_type_id IN (2,8)
AND mmt.transaction_type_id in (33, 34, 62)
AND mmt.transaction_action_id IN (1, 21)
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type <> 1
AND lines.line_id = mmt.trx_source_line_id
AND lines.order_source_id <> 27 -- retroactive billing
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
UNION ALL
-- 12 RMA
SELECT /*+ index(mmt,MTL_MATERIAL_TRANSACTIONS_N8)
use_nl(mmt,item,mta,h,cust_acct)
parallel(log) parallel(mmt) parallel(mta) parallel(l) parallel(pl)
parallel(cl) parallel(h) parallel(cust_acct) parallel(item)*/
mmt.inventory_item_id,
mmt.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
0 cogs_val_b_draft,
mta.base_transaction_value,
mmt.transaction_date,
OPI_SOURCE,
1,
0
FROM opi_dbi_conc_prog_run_log log,
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
oe_order_lines_all lines, -- child line
oe_order_lines_all l_lines, -- linking line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.organization_id = log.bound_level_entity_id
AND log.load_type = 'INIT'
AND log.etl_type = 'COGS'
AND mmt.transaction_source_type_id = 12
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type <> 1
AND lines.line_id = mmt.trx_source_line_id
AND lines.line_category_code = 'RETURN'
AND lines.order_source_id <> 27 -- retroactive billing
AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
AND p_lines.line_id = nvl(l_lines.top_model_line_id, l_lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
UNION ALL
-- Drop ship
SELECT /*+ leading(log) use_nl(mmt) index(mmt,MTL_MATERIAL_TRANSACTIONS_N9)
parallel(log) parallel(mmt) parallel(mta) parallel(l) parallel(pl)
parallel(h) parallel(cust_acct) parallel(item)*/
mmt.inventory_item_id,
mmt.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
0 cogs_val_b_draft,
mta.base_transaction_value,
mmt.transaction_date,
OPI_SOURCE,
decode(p_mmt.transaction_type_id, 33, 1, 2),
0
FROM opi_dbi_conc_prog_run_log log,
mtl_material_transactions mmt,
mtl_material_transactions p_mmt,
mtl_transaction_accounts mta,
oe_order_lines_all lines,
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.organization_id = log.bound_level_entity_id
AND log.load_type = 'INIT'
AND log.etl_type = 'COGS'
AND mmt.transaction_type_id in (11,30)
AND mmt.transaction_action_id in (7,9)
AND mmt.organization_id = lines.ship_from_org_id
AND p_mmt.transaction_id = mmt.parent_transaction_id
AND ( -- internal drop
(p_mmt.transaction_type_id = 33
and p_mmt.transaction_action_id = 1 )
OR -- external drop
(p_mmt.transaction_type_id = 19
and p_mmt.transaction_action_id = 26 ))
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type <> 1
AND lines.line_id = mmt.trx_source_line_id
AND lines.order_source_id <> 27 -- retroactive billing
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id;
INSERT /*+ append */ INTO opi_dbi_cogs_fstg m (
m.inventory_item_id,
m.organization_id,
m.order_line_id,
m.top_model_line_id,
m.top_model_item_id,
m.top_model_item_uom,
m.top_model_org_id,
m.customer_id,
m.cogs_val_b_draft,
m.cogs_val_b,
m.cogs_date,
m.source,
m.turns_cogs_flag,
m.internal_flag
)
-- 2 Regular sales order -- 8 Internal Sales Order
SELECT /*+ leading(log) index(mmt,MTL_MATERIAL_TRANSACTIONS_U2) use_nl(mmt,item,mta,h,cust_acct) */
mmt.inventory_item_id,
mmt.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
0 cogs_val_b_draft,
mta.base_transaction_value,
mmt.transaction_date,
OPI_SOURCE,
decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
decode(p_lines.order_source_id,10,1,0)
FROM opi_dbi_conc_prog_run_log log,
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
oe_order_lines_all lines, -- child line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.organization_id = log.bound_level_entity_id
AND log.load_type = 'INCR'
AND log.etl_type = 'COGS'
AND mmt.transaction_source_type_id IN (2,8)
and mmt.transaction_type_id in (33, 34, 62)
and mmt.transaction_action_id IN (1, 21)
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type <> 1
and lines.line_id = mmt.trx_source_line_id
and lines.order_source_id <> 27 -- retroactive billing
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
and lines.header_id = header.header_id
and header.sold_to_org_id = cust_acct.cust_account_id(+)
and item.inventory_item_id = p_lines.inventory_item_id
and item.organization_id = p_lines.ship_from_org_id
UNION ALL -- 12 RMA
SELECT /*+ leading(log) use_nl(mmt) */
mmt.inventory_item_id,
mmt.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
0 cogs_val_b_draft,
mta.base_transaction_value,
mmt.transaction_date,
OPI_SOURCE,
1,
0
FROM opi_dbi_conc_prog_run_log log,
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
oe_order_lines_all lines, -- child line
oe_order_lines_all l_lines, -- linking line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.organization_id = log.bound_level_entity_id
AND log.load_type = 'INCR'
AND log.etl_type = 'COGS'
AND mmt.transaction_source_type_id = 12
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type <> 1
AND lines.line_id = mmt.trx_source_line_id
AND lines.order_source_id <> 27 -- retroactive billing
AND lines.line_category_code = 'RETURN'
AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
AND p_lines.line_id = nvl(l_lines.top_model_line_id, l_lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
UNION ALL -- drop ship
SELECT /*+ leading(log) use_nl(mmt) index(mmt,MTL_MATERIAL_TRANSACTIONS_N9) */
mmt.inventory_item_id,
mmt.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
0 cogs_val_b_draft,
mta.base_transaction_value,
mmt.transaction_date,
OPI_SOURCE,
decode(p_mmt.transaction_type_id, 33, 1, 2),
0
FROM opi_dbi_conc_prog_run_log log,
mtl_material_transactions mmt,
mtl_material_transactions p_mmt,
mtl_transaction_accounts mta,
oe_order_lines_all lines,
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.organization_id = log.bound_level_entity_id
AND log.load_type = 'INCR'
AND log.etl_type = 'COGS'
AND mmt.transaction_type_id in (11,30)
AND mmt.transaction_action_id in (7,9)
AND mmt.organization_id = lines.ship_from_org_id
AND p_mmt.transaction_id = mmt.parent_transaction_id
AND ( -- internal drop
(p_mmt.transaction_type_id = 33
and p_mmt.transaction_action_id = 1 )
OR -- external drop
(p_mmt.transaction_type_id = 19
and p_mmt.transaction_action_id = 26 ))
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type <> 1
AND lines.line_id = mmt.trx_source_line_id
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND lines.order_source_id <> 27 -- retroactive billing
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
;
INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_fstg m (
m.inventory_item_id,
m.organization_id,
m.order_line_id,
m.top_model_line_id,
m.top_model_item_id,
m.top_model_item_uom,
m.top_model_org_id,
m.customer_id,
m.cogs_val_b_draft,
m.cogs_val_b,
m.cogs_date,
m.source,
m.turns_cogs_flag,
m.internal_flag
)
-- 33 Sales order issue; 34 Internal order issue; 62 Int Order Intr Ship
SELECT gtv.inventory_item_id,
gtv.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
gtv.draft_value,
gtv.final_value,
gtv.transaction_date,
OPM_SOURCE,
decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
decode(p_lines.order_source_id,10,1,0)
FROM oe_order_lines_all lines, -- child line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item,
(
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
sum(decode(gtv.accounted_flag, 'D', -gtv.txn_base_value, 0)) draft_value,
sum(decode(gtv.accounted_flag, 'D', 0, -gtv.txn_base_value)) final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (33, 34, 62)
AND nvl(gtv.accounted_flag, 'F') <> 'N'
AND nvl(gtv.final_posting_date, p_from_bound_date) >= p_from_bound_date
AND nvl(gtv.final_posting_date, p_from_bound_date) < p_to_bound_date
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date) gtv
WHERE lines.line_id = gtv.line_id
AND lines.order_source_id <> 27 -- retroactive billing
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
UNION ALL
-- 37 RMA Return; 16 Logical RMA Receipt; 15 RMA Receipt
SELECT gtv.inventory_item_id,
gtv.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
gtv.draft_value,
gtv.final_value,
gtv.transaction_date,
OPM_SOURCE,
1,
0
FROM oe_order_lines_all lines, -- child line
oe_order_lines_all l_lines, -- linking line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item,
(
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
sum(decode(gtv.accounted_flag, 'D', -gtv.txn_base_value, 0)) draft_value,
sum(decode(gtv.accounted_flag, 'D', 0, -gtv.txn_base_value)) final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (37, 16, 15)
AND nvl(gtv.accounted_flag, 'F') <> 'N'
AND nvl(gtv.final_posting_date, p_from_bound_date) >= p_from_bound_date
AND nvl(gtv.final_posting_date, p_from_bound_date) < p_to_bound_date
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date) gtv
WHERE lines.line_id = gtv.line_id
AND lines.line_category_code = 'RETURN'
AND lines.order_source_id <> 27 -- retroactive billing
AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
UNION ALL
-- Drop Ship
SELECT gtv.inventory_item_id,
gtv.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
gtv.draft_value,
gtv.final_value,
gtv.transaction_date,
OPM_SOURCE,
1,
0
FROM oe_order_lines_all lines, -- child line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item,
mtl_material_transactions mmt,
mtl_material_transactions p_mmt,
(
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
sum(decode(gtv.accounted_flag, 'D', -gtv.txn_base_value, 0)) draft_value,
sum(decode(gtv.accounted_flag, 'D', 0, -gtv.txn_base_value)) final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (11, 30) -- 11 Logical intercompany sales issue
-- 30 Logical sales order issue
AND nvl(gtv.accounted_flag, 'F') <> 'N'
AND nvl(gtv.final_posting_date, p_from_bound_date) >= p_from_bound_date
AND nvl(gtv.final_posting_date, p_from_bound_date) < p_to_bound_date
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date) gtv
WHERE gtv.transaction_id = mmt.transaction_id
AND p_mmt.transaction_id = mmt.parent_transaction_id
AND (p_mmt.transaction_type_id = 33 -- sales order issue, internal drop
OR p_mmt.transaction_type_id = 19) -- logical PO receipt, external drop
AND gtv.line_id = lines.line_id
AND gtv.organization_id = lines.ship_from_org_id
AND lines.order_source_id <> 27 -- retroactive billing
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id;
SELECT last_run_date
INTO l_r12_mgr_date
FROM opi_dbi_conc_prog_run_log
WHERE etl_type = 'R12_MIGRATION';
SELECT from_bound_date, to_bound_date
INTO l_from_bound_date, l_to_bound_date
FROM opi_dbi_conc_prog_run_log
WHERE etl_type = 'COGS'
AND driving_table_code = 'GTV'
AND load_type = 'INIT';
INSERT /*+ append */ INTO opi_dbi_cogs_fstg m (
m.inventory_item_id,
m.organization_id,
m.order_line_id,
m.top_model_line_id,
m.top_model_item_id,
m.top_model_item_uom,
m.top_model_org_id,
m.customer_id,
m.cogs_val_b_draft,
m.cogs_val_b,
m.cogs_date,
m.source,
m.turns_cogs_flag,
m.internal_flag )
-- 33 Sales order issue; 34 Internal order issue; 62 Int Order Intr Ship
SELECT /*+ ordered use_nl(lines, p_lines, header, cust_acct, item) */
gtv.inventory_item_id,
gtv.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
gtv.draft_value,
gtv.final_value,
gtv.transaction_date,
OPM_SOURCE,
decode(p_lines.source_type_code, 'EXTERNAL', 2, 1 ),
decode(p_lines.order_source_id,10,1,0)
FROM (
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
sum(-gtv.txn_base_value) draft_value,
0 final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (33, 34, 62)
AND gtv.accounted_flag = 'D'
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date
UNION ALL
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
0 draft_value,
sum(-gtv.txn_base_value) final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (33, 34, 62)
AND gtv.accounted_flag is NULL
AND gtv.final_posting_date >= p_from_bound_date
AND gtv.final_posting_date < p_to_bound_date
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date) gtv,
oe_order_lines_all lines, -- child line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE lines.line_id = gtv.line_id
AND lines.order_source_id <> 27 -- retroactive billing
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
UNION ALL
-- 37 RMA Return; 16 Logical RMA Receipt; 15 RMA Receipt
SELECT /*+ ordered use_nl(lines, p_lines, l_lines,header, cust_acct, item) */
gtv.inventory_item_id,
gtv.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
gtv.draft_value,
gtv.final_value,
gtv.transaction_date,
OPM_SOURCE,
1,
0
FROM (
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
sum(-gtv.txn_base_value) draft_value,
0 final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (37, 16, 15)
AND gtv.accounted_flag = 'D'
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date
UNION ALL
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
0 draft_value,
sum(-gtv.txn_base_value) final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (37, 16, 15)
AND gtv.accounted_flag is NULL
AND gtv.final_posting_date >= p_from_bound_date
AND gtv.final_posting_date < p_to_bound_date
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date) gtv,
oe_order_lines_all lines, -- child line
oe_order_lines_all l_lines, -- linking line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE lines.line_id = gtv.line_id
AND lines.line_category_code = 'RETURN'
AND lines.order_source_id <> 27 -- retroactive billing
AND l_lines.line_id = nvl(lines.link_to_line_id, lines.line_id)
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id
UNION ALL
-- Drop Ship
SELECT /*+ ordered use_nl(mmt, p_mmt, lines, p_lines, header, cust_acct, item) index(mmt,mtl_material_transactions_u1)*/
gtv.inventory_item_id,
gtv.organization_id,
lines.line_id,
p_lines.line_id top_model_line_id,
p_lines.inventory_item_id top_model_item_id,
item.primary_uom_code top_model_item_uom,
p_lines.ship_from_org_id top_model_org_id,
nvl(cust_acct.party_id, -1),
gtv.draft_value,
gtv.final_value,
gtv.transaction_date,
OPM_SOURCE,
1,
0
FROM (
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
sum(-gtv.txn_base_value) draft_value,
0 final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (11, 30) -- 11 Logical intercompany sales issue
-- 30 Logical sales order issue
AND gtv.accounted_flag = 'D'
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date
UNION ALL
SELECT gtv.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date,
0 draft_value,
sum(-gtv.txn_base_value) final_value
FROM gmf_transaction_valuation gtv,
opi_dbi_org_le_temp tmp
WHERE gtv.transaction_type_id in (11, 30) -- 11 Logical intercompany sales issue
-- 30 Logical sales order issue
AND gtv.accounted_flag is NULL
AND gtv.final_posting_date >= p_from_bound_date
AND gtv.final_posting_date < p_to_bound_date
AND gtv.transaction_date >= g_global_start_date
AND gtv.journal_line_type = 'INV'
AND gtv.ledger_id = tmp.ledger_id
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.transaction_id,
gtv.inventory_item_id,
gtv.organization_id,
gtv.line_id,
gtv.transaction_date) gtv,
mtl_material_transactions mmt,
mtl_material_transactions p_mmt,
oe_order_lines_all lines, -- child line
oe_order_lines_all p_lines, -- parent line
oe_order_headers_all header,
hz_cust_accounts cust_acct,
mtl_system_items_b item
WHERE gtv.transaction_id = mmt.transaction_id
AND p_mmt.transaction_id = mmt.parent_transaction_id
AND (p_mmt.transaction_type_id = 33 -- sales order issue, internal drop
OR p_mmt.transaction_type_id = 19) -- logical PO receipt, external drop
AND gtv.line_id = lines.line_id
AND gtv.organization_id = lines.ship_from_org_id
AND lines.order_source_id <> 27 -- retroactive billing
AND p_lines.line_id = nvl(lines.top_model_line_id, lines.line_id)
AND lines.header_id = header.header_id
AND header.sold_to_org_id = cust_acct.cust_account_id(+)
AND item.inventory_item_id = p_lines.inventory_item_id
AND item.organization_id = p_lines.ship_from_org_id;
SELECT from_bound_date, to_bound_date
INTO l_from_bound_date, l_to_bound_date
FROM opi_dbi_conc_prog_run_log
WHERE etl_type = 'COGS'
AND driving_table_code = 'GTV'
AND load_type = 'INCR';
SELECT 1
FROM opi_dbi_cogsf_conv_rates
WHERE (nvl (conversion_rate, -999) < 0 OR
nvl (sec_conversion_rate, 999) < 0)
AND rownum < 2;
SELECT /*+ parallel (compare) */
DISTINCT
report_order,
curr_code,
rate_type,
cogs_date,
func_currency_code
FROM (
SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
DISTINCT
g_global_currency_code curr_code,
g_global_rate_type rate_type,
1 report_order, -- ordering global currency first
mp.organization_code,
decode (conv.conversion_rate,
C_EURO_MISSING_AT_START, g_euro_start_date,
conv.transaction_date) cogs_date,
conv.f_currency_code func_currency_code
FROM opi_dbi_cogsf_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_cogs_fstg) */
DISTINCT organization_id, trunc (cogs_date) cogs_date
FROM opi_dbi_cogs_fstg) to_conv
WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.cogs_date
AND conv.organization_id (+) = to_conv.organization_id
UNION ALL
SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
DISTINCT
g_secondary_currency_code curr_code,
g_secondary_rate_type rate_type,
decode (p_pri_sec_curr_same,
1, 1,
2) report_order, --ordering secondary currency next
mp.organization_code,
decode (conv.sec_conversion_rate,
C_EURO_MISSING_AT_START, g_euro_start_date,
conv.transaction_date) cogs_date,
conv.f_currency_code func_currency_code
FROM opi_dbi_cogsf_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_cogs_fstg) */
DISTINCT organization_id, trunc (cogs_date) cogs_date
FROM opi_dbi_cogs_fstg) to_conv
WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.cogs_date
AND conv.organization_id (+) = to_conv.organization_id)
compare
ORDER BY
report_order ASC,
cogs_date,
func_currency_code;
INSERT /*+ append parallel(rates) */
INTO opi_dbi_cogsf_conv_rates rates (
organization_id,
f_currency_code,
transaction_date,
conversion_rate,
sec_conversion_rate)
SELECT /*+ parallel (to_conv) parallel (curr_codes) */
to_conv.organization_id,
curr_codes.currency_code,
to_conv.cogs_date,
decode (curr_codes.currency_code,
g_global_currency_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
to_conv.cogs_date) ),
decode (g_secondary_currency_code,
NULL, NULL,
curr_codes.currency_code, 1,
decode (l_pri_sec_curr_same,
1, C_PRI_SEC_CURR_SAME_MARKER,
fii_currency.get_global_rate_secondary (
curr_codes.currency_code,
to_conv.cogs_date)))
FROM
(SELECT /*+ parallel (opi_dbi_cogs_fstg) */
DISTINCT organization_id, trunc (cogs_date) cogs_date
FROM opi_dbi_cogs_fstg) to_conv,
(SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
parallel (hoi) parallel (gsob)*/
DISTINCT hoi.organization_id, gsob.currency_code
FROM hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id))
curr_codes
WHERE curr_codes.organization_id = to_conv.organization_id;
UPDATE /*+ parallel (opi_dbi_cogsf_conv_rates) */
opi_dbi_cogsf_conv_rates
SET sec_conversion_rate = conversion_rate;
SELECT 1
FROM opi_dbi_cogsf_conv_rates
WHERE ( nvl (conversion_rate, -999) < 0
OR nvl (sec_conversion_rate, 999) < 0)
AND rownum < 2;
SELECT DISTINCT
report_order,
curr_code,
rate_type,
cogs_date,
func_currency_code
FROM (
SELECT DISTINCT
g_global_currency_code curr_code,
g_global_rate_type rate_type,
1 report_order, -- ordering global currency first
mp.organization_code,
decode (conv.conversion_rate,
C_EURO_MISSING_AT_START, g_euro_start_date,
conv.transaction_date) cogs_date,
conv.f_currency_code func_currency_code
FROM opi_dbi_cogsf_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_cogs_fstg) */
DISTINCT organization_id, trunc (cogs_date) cogs_date
FROM opi_dbi_cogs_fstg) to_conv
WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.cogs_date
AND conv.organization_id (+) = to_conv.organization_id
UNION ALL
SELECT DISTINCT
g_secondary_currency_code curr_code,
g_secondary_rate_type rate_type,
decode (p_pri_sec_curr_same,
1, 1,
2) report_order, --ordering secondary currency next
mp.organization_code,
decode (conv.sec_conversion_rate,
C_EURO_MISSING_AT_START, g_euro_start_date,
conv.transaction_date) cogs_date,
conv.f_currency_code func_currency_code
FROM opi_dbi_cogsf_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_cogs_fstg) */
DISTINCT organization_id, trunc (cogs_date) cogs_date
FROM opi_dbi_cogs_fstg) to_conv
WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.cogs_date
AND conv.organization_id (+) = to_conv.organization_id)
ORDER BY
report_order ASC,
cogs_date,
func_currency_code;
INSERT /*+ append */
INTO opi_dbi_cogsf_conv_rates rates (
organization_id,
f_currency_code,
transaction_date,
conversion_rate,
sec_conversion_rate)
SELECT
to_conv.organization_id,
curr_codes.currency_code,
to_conv.cogs_date,
decode (curr_codes.currency_code,
g_global_currency_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
to_conv.cogs_date) ),
decode (g_secondary_currency_code,
NULL, NULL,
curr_codes.currency_code, 1,
decode (l_pri_sec_curr_same,
1, C_PRI_SEC_CURR_SAME_MARKER,
fii_currency.get_global_rate_secondary (
curr_codes.currency_code,
to_conv.cogs_date)))
FROM
(SELECT
DISTINCT organization_id, trunc (cogs_date) cogs_date
FROM opi_dbi_cogs_fstg) to_conv,
(SELECT
DISTINCT hoi.organization_id, gsob.currency_code
FROM hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id))
curr_codes
WHERE curr_codes.organization_id = to_conv.organization_id;
UPDATE /*+ parallel (opi_dbi_cogsf_conv_rates) */
opi_dbi_cogsf_conv_rates
SET sec_conversion_rate = conversion_rate;
INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_f m (
m.inventory_item_id,
m.organization_id,
m.order_line_id,
m.top_model_line_id,
m.top_model_item_id,
m.top_model_item_uom,
m.top_model_org_id,
m.customer_id,
m.cogs_val_b_draft,
m.cogs_val_b,
m.cogs_val_g,
m.cogs_val_sg,
m.cogs_date,
m.source,
m.turns_cogs_flag,
m.internal_flag,
m.creation_date,
m.last_update_date,
m.created_by,
m.last_updated_by,
m.last_updated_login,
m.program_id,
m.program_login_id,
m.program_application_id,
m.request_id)
SELECT /*+ parallel(stg) parallel(rate) */
stg.inventory_item_id,
stg.organization_id,
stg.order_line_id,
stg.top_model_line_id,
stg.top_model_item_id,
stg.top_model_item_uom,
stg.top_model_org_id,
stg.customer_id,
sum(stg.cogs_val_b_draft),
sum(stg.cogs_val_b_draft + stg.cogs_val_b),
sum((stg.cogs_val_b_draft + stg.cogs_val_b) * rate.conversion_rate),
sum((stg.cogs_val_b_draft + stg.cogs_val_b) * rate.sec_conversion_rate),
trunc (stg.cogs_date),
stg.source,
stg.turns_cogs_flag,
stg.internal_flag,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id,
g_program_id,
g_program_login_id,
g_program_application_id,
g_request_id
FROM opi_dbi_cogs_fstg stg,
opi_dbi_cogsf_conv_rates rate
WHERE stg.organization_id = rate.organization_id
AND trunc (stg.cogs_date) = rate.transaction_date
GROUP BY
stg.inventory_item_id,
stg.organization_id,
stg.order_line_id,
stg.top_model_line_id,
stg.top_model_item_id,
stg.top_model_item_uom,
stg.top_model_org_id,
stg.customer_id,
stg.cogs_date,
stg.source,
stg.turns_cogs_flag,
stg.internal_flag;
SELECT
stg.inventory_item_id,
stg.organization_id,
stg.order_line_id,
stg.top_model_line_id,
stg.top_model_item_id,
stg.top_model_item_uom,
stg.top_model_org_id,
stg.customer_id,
sum(stg.cogs_val_b_draft) cogs_val_b_draft,
sum(stg.cogs_val_b) cogs_val_b,
trunc (stg.cogs_date) cogs_date,
stg.source,
stg.turns_cogs_flag,
stg.internal_flag,
min(rate.conversion_rate) conversion_rate,
min(rate.sec_conversion_rate) sec_conversion_rate
FROM opi_dbi_cogs_fstg stg,
opi_dbi_cogsf_conv_rates rate
WHERE stg.organization_id = rate.organization_id
AND trunc (stg.cogs_date) = rate.transaction_date
GROUP BY
stg.inventory_item_id,
stg.organization_id,
stg.order_line_id,
stg.top_model_line_id,
stg.top_model_item_id,
stg.top_model_item_uom,
stg.top_model_org_id,
stg.customer_id,
trunc (stg.cogs_date),
stg.source,
stg.turns_cogs_flag,
stg.internal_flag
) rstg
ON (m.order_line_id = rstg.order_line_id )
WHEN matched THEN UPDATE SET
m.cogs_val_b_draft = rstg.cogs_val_b_draft,
m.cogs_val_b = m.cogs_val_b - m.cogs_val_b_draft + rstg.cogs_val_b_draft + rstg.cogs_val_b,
m.cogs_val_g = (m.cogs_val_b - m.cogs_val_b_draft + rstg.cogs_val_b_draft + rstg.cogs_val_b)
* rstg.conversion_rate,
m.cogs_val_sg = (m.cogs_val_b - m.cogs_val_b_draft + rstg.cogs_val_b_draft + rstg.cogs_val_b)
* rstg.sec_conversion_rate,
m.cogs_date = rstg.cogs_date,
m.last_update_date = sysdate,
m.last_updated_by = g_user_id,
m.last_updated_login = g_login_id
WHEN NOT matched THEN
INSERT (m.inventory_item_id,
m.organization_id,
m.order_line_id,
m.top_model_line_id,
m.top_model_item_id,
m.top_model_item_uom,
m.top_model_org_id,
m.customer_id,
m.cogs_val_b_draft,
m.cogs_val_b,
m.cogs_val_g,
m.cogs_val_sg,
m.cogs_date,
m.source,
m.turns_cogs_flag,
m.internal_flag,
m.creation_date,
m.last_update_date,
m.created_by,
m.last_updated_by,
m.last_updated_login,
m.program_id,
m.program_login_id,
program_application_id,
request_id )
VALUES (rstg.inventory_item_id,
rstg.organization_id,
rstg.order_line_id,
rstg.top_model_line_id,
rstg.top_model_item_id,
rstg.top_model_item_uom,
rstg.top_model_org_id,
rstg.customer_id,
rstg.cogs_val_b_draft,
rstg.cogs_val_b_draft + rstg.cogs_val_b,
(rstg.cogs_val_b_draft + rstg.cogs_val_b) * rstg.conversion_rate,
(rstg.cogs_val_b_draft + rstg.cogs_val_b) * rstg.sec_conversion_rate,
rstg.cogs_date,
rstg.source,
rstg.turns_cogs_flag,
rstg.internal_flag,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id,
g_program_id,
g_program_login_id,
g_program_application_id,
g_request_id );