[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(MIN(from_date), global_start_date) INTO l_from_date
FROM (SELECT tst.gl_trans_date from_date
FROM opi_dbi_cogs_log l,
gl_subr_tst tst
WHERE l.extraction_type = 'COGS_SUBLEDGER'
AND tst.subledger_id = l.transaction_id
UNION
SELECT tst.gl_trans_date from_date
FROM opi_dbi_cogs_log l,
gl_subr_led tst
WHERE l.extraction_type = 'COGS_SUBLEDGER'
AND tst.subledger_id = l.transaction_id
UNION
SELECT aid.accounting_date from_date
FROM opi_dbi_cogs_log l,
ap_invoice_distributions_all aid
WHERE l.extraction_type = 'COGS_AP'
AND aid.invoice_distribution_id = l.transaction_id
);
select distinct cogs_currency_code,
decode(cogs_conversion_rate, -3, to_Date('01/01/1999', 'MM/DD/YYYY'), cogs_date) cogs_date
from opi_dbi_cogs_stg
where NVL(cogs_conversion_rate,-99) < 0 ;
SELECT 'Y' INTO l_ici_flag
FROM mtl_intercompany_parameters mip
WHERE mip.ship_organization_id = p_ship_ou_id
AND mip.sell_organization_id = p_sell_ou_id ;
insert /*+ append */ into opi_dbi_opm_cogstst_current
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
)
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,sum(COGS_VAL_B)
,max(COGS_DATE)
,COGS_CURRENCY_CODE
,fii_currency.get_global_rate_primary
(cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from
( select
lines.inventory_item_id INVENTORY_ITEM_ID ,
whse.mtl_organization_id ORGANIZATION_ID ,
tran.oe_order_line_id ORDER_LINE_ID ,
Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, ou.ship_ou_id, -- RO, ICI
0, lines.org_id) -- RO, No ICI
) ) MARGIN_OU_ID ,
tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
trunc(GL_TRANS_DATE) COGS_DATE ,
tst.currency_base COGS_CURRENCY_CODE ,
ou.ship_ou_id SHIP_OU_ID ,
lines.org_id SELL_OU_ID ,
Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
'Y' ) TURNS_COGS_FLAG ,
Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, 'OPM_RO_ICI',
0, 'OPM_RO_NOICI', 'OPM') ) ) ) SOURCE
from gl_subr_tst tst,
oe_order_lines_all lines,
ic_whse_mst whse,
(
SELECT hou.organization_id organization_id,
gsob.currency_code currency_code,
to_number(HOI.org_information3) ship_ou_id
FROM hr_all_organization_units hou,
hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hou.organization_id = hoi.organization_id
AND ( hoi.org_information_context || '') ='Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id)
) OU,
(
select tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran,
rcv_transactions rcv
where doc_type = 'PORC'
and completed_ind = 1
and gl_posted_ind = 0
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran,
rcv_transactions rcv
where doc_type = 'PORC'
and gl_posted_ind = 0
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran
where doc_type = 'OMSO'
and completed_ind = 1
and gl_posted_ind = 0
group by doc_type, line_id, line_id, orgn_code, whse_code
union all
select tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran
where doc_type = 'OMSO'
and gl_posted_ind = 0
group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
where tst.doc_type in ( 'OMSO', 'PORC' )
and tst.acct_ttl_type = 5200
and lines.line_id = tran.oe_order_line_id
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
and whse.whse_code = tran.whse_code
AND whse.mtl_organization_id = ou.organization_id
)
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_CURRENCY_CODE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE ;
insert /*+ append */ into opi_dbi_opm_cogsled_current
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
)
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,sum(COGS_VAL_B)
,max(COGS_DATE)
,COGS_CURRENCY_CODE
,fii_currency.get_global_rate_primary
(cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from
( select
lines.inventory_item_id INVENTORY_ITEM_ID ,
whse.mtl_organization_id ORGANIZATION_ID ,
tran.oe_order_line_id ORDER_LINE_ID ,
Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, ou.ship_ou_id, -- RO, ICI
0, lines.org_id) -- RO, No ICI
) ) MARGIN_OU_ID ,
tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
trunc(GL_TRANS_DATE) COGS_DATE ,
tst.currency_base COGS_CURRENCY_CODE ,
ou.ship_ou_id SHIP_OU_ID ,
lines.org_id SELL_OU_ID ,
Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
'Y' ) TURNS_COGS_FLAG ,
Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
Decode(check_ici(ou.ship_ou_id, lines.org_id), 1, 'OPM_RO_ICI',
0, 'OPM_RO_NOICI', 'OPM') ) ) ) SOURCE
from gl_subr_led tst,
oe_order_lines_all lines,
ic_whse_mst whse,
(
SELECT hou.organization_id organization_id,
gsob.currency_code currency_code,
to_number(HOI.org_information3) ship_ou_id
FROM hr_all_organization_units hou,
hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hou.organization_id = hoi.organization_id
AND ( hoi.org_information_context || '') ='Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id)
) OU,
(
select tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran,
rcv_transactions rcv
where doc_type = 'PORC'
and completed_ind = 1
and gl_posted_ind = 1
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran,
rcv_transactions rcv
where doc_type = 'PORC'
and gl_posted_ind = 1
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran
where doc_type = 'OMSO'
and completed_ind = 1
and gl_posted_ind = 1
group by doc_type, line_id, line_id, orgn_code, whse_code
union all
select tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran
where doc_type = 'OMSO'
and gl_posted_ind = 1
group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
where tst.doc_type in ( 'OMSO', 'PORC' )
and tst.acct_ttl_type = 5200
and lines.line_id = tran.oe_order_line_id
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
and whse.whse_code = tran.whse_code
AND whse.mtl_organization_id = ou.organization_id
and tst.subledger_id between p_last_id and p_newest_id
)
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_CURRENCY_CODE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE ;
insert /*+ append */ INTO opi_dbi_cogs_stg
(inventory_item_id,
organization_id,
order_line_id,
margin_ou_id,
cogs_val_b, cogs_date,
cogs_currency_code,
cogs_conversion_rate,
ship_ou_id,
sell_ou_id,
turns_cogs_flag,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,sum(cogs_val_b) COGS_VAL_B
,max(cogs_date) COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
, g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
FROM
(select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogstst_current
union all
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,-COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogstst_prior
union all
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogsled_current
)
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
, g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
;
insert /*+ append */ into opi_dbi_opm_cogstst_prior
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
)
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogstst_current;
using ( SELECT
pl.inventory_item_id top_model_item_id,
pl.line_id top_model_order_line_id,
pl.org_id sell_ou_id,
to_number(HOI.org_information3) ship_ou_id,
pl.org_id margin_ou_id,
'N' turns_cogs_flag,
trunc( max(aid.accounting_date)) cogs_date,
gsob.currency_code currency_code,
fii_currency.get_global_rate_primary
(gsob.currency_code, trunc( max(aid.accounting_date)) ) cogs_conversion_rate,
SUM( Nvl(aid.base_amount, aid.amount) ) cogs_val_b
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
ra_customer_trx_lines_all rcl,
oe_order_lines_all l,
oe_order_lines_all pl,
hr_organization_information hoi,
gl_sets_of_books gsob,
hr_organization_information hoi2
WHERE aid.invoice_distribution_id >= p_last_dist_id
AND aid.invoice_distribution_id < p_new_dist_id
AND ai.invoice_id = aid.invoice_id
AND ai.source = 'Intercompany'
and ai.org_id = aid.org_id
and aid.line_type_lookup_code = 'ITEM'
and translate( lower(aid.REFERENCE_1), 'abcdefghijklmnopqrstuvwxyz_ -+0123456789',
'abcdefghijklmnopqrstuvwxyz_ -+') is null
and rcl.CUSTOMER_TRX_LINE_ID = to_number(aid.REFERENCE_1)
and l.line_id = rcl.interface_line_attribute6
and pl.line_id = nvl(l.top_model_line_id, l.line_id)
AND hoi.organization_id = pl.org_id
AND ( hoi.org_information_context || '') ='Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id)
AND hoi2.organization_id = rcl.interface_line_attribute3
AND ( hoi.org_information_context || '') ='Accounting Information'
group by pl.line_id, pl.inventory_item_id, pl.org_id, hoi.org_information3, gsob.currency_code ) c
ON ( m.order_line_id = c.top_model_order_line_id
AND m.margin_ou_id = c.margin_ou_id )
WHEN matched THEN UPDATE SET
m.cogs_val_b = Nvl(m.cogs_val_b,0) + Nvl(c.cogs_val_b,0),
m.cogs_date= Greatest( Nvl(m.cogs_date,c.cogs_date), c.cogs_date),
m.cogs_conversion_rate = Decode(Sign(c.cogs_date - m.cogs_date),
1, c.cogs_conversion_rate,m.cogs_conversion_rate),
m.cogs_currency_code = Decode( Sign(c.cogs_conversion_rate), -1, c.currency_code, NULL),
m.source = 'OPI_AP',
m.last_update_date = Sysdate,
m.last_updated_by = g_user_id,
m.last_update_login = g_login_id
WHEN NOT matched THEN
INSERT (m.inventory_item_id, m.organization_id, m.order_line_id,
m.margin_ou_id, m.cogs_val_b, m.cogs_date, m.cogs_conversion_rate,
m.cogs_currency_code,
m.ship_ou_id, m.sell_ou_id, m.turns_cogs_flag,
m.source, m.creation_date, m.last_update_date,
m.created_by, m.last_updated_by, m.last_update_login)
VALUES (c.top_model_item_id, null, c.top_model_order_line_id,
c.margin_ou_id, c.cogs_val_b, c.cogs_date, c.cogs_conversion_rate,
Decode( Sign(c.cogs_conversion_rate), -1, c.currency_code, NULL),
c.ship_ou_id, c.sell_ou_id, c.turns_cogs_flag,
'OPI_AP', Sysdate, Sysdate,
g_user_id, g_user_id, g_login_id)
;
insert /*+ APPEND PARALLEL(F) */
into opi_dbi_opm_cogstst_current F
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
)
select /*+ PARALLEL(COGS) PARALLEL(MIP) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,Decode (margin_ou_id,
0, Decode(mip.sell_organization_id,
NULL, sell_ou_id, -- NULL indicates no mip row set up, therefore no ici
ship_ou_id -- else mip row set up, therefore ici
),
margin_ou_id
) MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,decode (SOURCE, 'OPM_CHECK_ICI',
Decode(mip.sell_organization_id,
NULL, 'OPM_RO_NOICI', -- RO, NO ICI
'OPM_RO_ICI'), -- RO, ICI
SOURCE ) SOURCE
from
(
select /*+ PARALLEL(A) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,sum(COGS_VAL_B) COGS_VAL_B
,max(COGS_DATE) COGS_DATE
,COGS_CURRENCY_CODE
, fii_currency.get_global_rate_primary
(cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from
( select /*+ FULL(tst) PARALLEL(TST) PARALLEL(LINES) PARALLEL(WHSE) PARALLEL(OU) PARALLEL(TRAN) */
lines.inventory_item_id INVENTORY_ITEM_ID ,
whse.mtl_organization_id ORGANIZATION_ID ,
tran.oe_order_line_id ORDER_LINE_ID ,
Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
0 -- if need to check ici, set OU in outer query
) ) MARGIN_OU_ID ,
tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
trunc(GL_TRANS_DATE) COGS_DATE ,
tst.currency_base COGS_CURRENCY_CODE ,
ou.ship_ou_id SHIP_OU_ID ,
lines.org_id SELL_OU_ID ,
Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
'Y' ) TURNS_COGS_FLAG ,
Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
'OPM_CHECK_ICI' ) ) ) SOURCE
from gl_subr_tst tst,
oe_order_lines_all lines,
ic_whse_mst whse,
(
SELECT /*+ PARALLEL(HOU) PARALLEL(HOI) PARALLEL(GSOB) */
hou.organization_id organization_id,
gsob.currency_code currency_code,
to_number(HOI.org_information3) ship_ou_id
FROM hr_all_organization_units hou,
hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hou.organization_id = hoi.organization_id
AND ( hoi.org_information_context || '') ='Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id)
) OU,
(
select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran,
rcv_transactions rcv
where doc_type = 'PORC'
and completed_ind = 1
and gl_posted_ind = 0
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran,
rcv_transactions rcv
where doc_type = 'PORC'
and gl_posted_ind = 0
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select /*+ PARALLEL(TRAN) */
tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran
where doc_type = 'OMSO'
and completed_ind = 1
and gl_posted_ind = 0
group by doc_type, line_id, line_id, orgn_code, whse_code
union all
select /*+ PARALLEL(TRAN) */
tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran
where doc_type = 'OMSO'
and gl_posted_ind = 0
group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
where tst.doc_type in ( 'OMSO', 'PORC' )
and tst.acct_ttl_type = 5200
and lines.line_id = tran.oe_order_line_id
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
and whse.whse_code = tran.whse_code
AND whse.mtl_organization_id = ou.organization_id
) A
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_CURRENCY_CODE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
) cogs,
mtl_intercompany_parameters mip
where mip.ship_organization_id(+) = cogs.ship_ou_id
AND mip.sell_organization_id(+) = cogs.sell_ou_id
;
insert /*+ APPEND PARALLEL(F) */
into opi_dbi_opm_cogsled_current F
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
)
select /*+ PARALLEL(COGS) PARALLEL(MIP) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,Decode (margin_ou_id,
0, Decode(mip.sell_organization_id,
NULL, sell_ou_id, -- NULL indicates no mip row set up, therefore no ici
ship_ou_id -- else mip row set up, therefore ici
),
margin_ou_id
) MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,decode (SOURCE, 'OPM_CHECK_ICI',
Decode(mip.sell_organization_id,
NULL, 'OPM_RO_NOICI', -- RO, NOICI
'OPM_RO_ICI'), -- RO, ICI
SOURCE ) SOURCE
from
(
select /*+ PARALLEL(A) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,sum(COGS_VAL_B) COGS_VAL_B
,max(COGS_DATE) COGS_DATE
,COGS_CURRENCY_CODE
,fii_currency.get_global_rate_primary
(cogs_currency_code, max(cogs_date)) COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from
( select /*+ FULL(tst) PARALLEL(TST) PARALLEL(LINES) PARALLEL(WHSE) PARALLEL(OU) PARALLEL(TRAN) */
lines.inventory_item_id INVENTORY_ITEM_ID ,
whse.mtl_organization_id ORGANIZATION_ID ,
tran.oe_order_line_id ORDER_LINE_ID ,
Decode(lines.source_type_code, 'EXTERNAL', lines.org_id, --drop ship
Decode(ou.ship_ou_id, lines.org_id, lines.org_id, -- Same OU,
0 -- if need to check ici, set OU in outer query
) ) MARGIN_OU_ID ,
tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
trunc(GL_TRANS_DATE) COGS_DATE ,
tst.currency_base COGS_CURRENCY_CODE ,
ou.ship_ou_id SHIP_OU_ID ,
lines.org_id SELL_OU_ID ,
Decode(lines.source_type_code, 'EXTERNAL', 'N', --drop ship
'Y' ) TURNS_COGS_FLAG ,
Decode( lines.line_category_code, 'RETURN', 'OPM_RMA',
Decode(lines.source_type_code, 'EXTERNAL', 'OPM_RO_DROP', --drop ship
Decode(ou.ship_ou_id, lines.org_id, 'OPM_RO', -- Same OU,
'OPM_CHECK_ICI' ) ) ) SOURCE
from gl_subr_led tst,
oe_order_lines_all lines,
ic_whse_mst whse,
(
SELECT /*+ PARALLEL(HOU) PARALLEL(HOI) PARALLEL(GSOB) */
hou.organization_id organization_id,
gsob.currency_code currency_code,
to_number(HOI.org_information3) ship_ou_id
FROM hr_all_organization_units hou,
hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hou.organization_id = hoi.organization_id
AND ( hoi.org_information_context || '') ='Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id)
) OU,
(
select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran,
rcv_transactions rcv
where doc_type = 'PORC'
and completed_ind = 1
and gl_posted_ind = 1
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select /*+ PARALLEL(TRAN) PARALLEL(RCV) */
tran.doc_type,
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran,
rcv_transactions rcv
where doc_type = 'PORC'
and gl_posted_ind = 1
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
group by doc_type, rcv.oe_order_line_id, line_id, orgn_code, whse_code
union all
select /*+ PARALLEL(TRAN) */
tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_pnd tran
where doc_type = 'OMSO'
and completed_ind = 1
and gl_posted_ind = 1
group by doc_type, line_id, line_id, orgn_code, whse_code
union all
select /*+ PARALLEL(TRAN) */
tran.doc_type,
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code
from ic_tran_cmp tran
where doc_type = 'OMSO'
and gl_posted_ind = 1
group by doc_type, line_id, line_id, orgn_code, whse_code ) tran
where tst.doc_type in ( 'OMSO', 'PORC' )
and tst.acct_ttl_type = 5200
and lines.line_id = tran.oe_order_line_id
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
and whse.whse_code = tran.whse_code
AND whse.mtl_organization_id = ou.organization_id
and tst.subledger_id between p_last_id and p_newest_id
) A
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_CURRENCY_CODE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
) cogs,
mtl_intercompany_parameters mip
where mip.ship_organization_id(+) = cogs.ship_ou_id
AND mip.sell_organization_id(+) = cogs.sell_ou_id
;
insert /*+ APPEND PARALLEL(F) */
INTO opi_dbi_cogs_stg F
(inventory_item_id,
organization_id,
order_line_id,
margin_ou_id,
cogs_val_b, cogs_date,
cogs_currency_code,
cogs_conversion_rate,
ship_ou_id,
sell_ou_id,
turns_cogs_flag,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT /*+ PARALLEL(A) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,sum(cogs_val_b) COGS_VAL_B
,max(cogs_date) COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
, g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
FROM
(select /*+ PARALLEL(TSTCURR) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogstst_current TSTCURR
union all
select /*+ PARALLEL(TSTPRIOR) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,-COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogstst_prior TSTPRIOR
union all
select /*+ PARALLEL(LED) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogsled_current LED
) A
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
, g_Sysdate, g_Sysdate, g_user_id, g_user_id, g_login_id
;
insert /*+ APPEND PARALLEL(F) */
into opi_dbi_opm_cogstst_prior F
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
)
select /*+ PARALLEL(CURR) FULL(curr) */
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,MARGIN_OU_ID
,COGS_VAL_B
,COGS_DATE
,COGS_CURRENCY_CODE
,COGS_CONVERSION_RATE
,SHIP_OU_ID
,SELL_OU_ID
,TURNS_COGS_FLAG
,SOURCE
from opi_dbi_opm_cogstst_current curr;
SELECT transaction_id INTO l_last_trx_id
FROM opi_dbi_cogs_log
WHERE extraction_type = 'COGS_ICAP';
SELECT Nvl( MIN(invoice_distribution_id), 0) INTO l_last_trx_id
FROM ap_invoice_distributions_all
WHERE accounting_date >= global_start_date;
SELECT ap_invoice_distributions_s.NEXTVAL INTO l_new_trx_id
FROM dual;
using ( SELECT 'COGS_ICAP' extraction_type
FROM dual ) d
ON ( l.extraction_type = d.extraction_type )
WHEN matched THEN UPDATE SET
l.transaction_id = Decode(l_status, 0, l_batch_from_id,
1, l_batch_to_id ),
l.error_message = l_msg,
l.last_update_date = Sysdate,
l.last_updated_by = g_user_id,
l.last_update_login = g_login_id
WHEN NOT matched THEN
INSERT (l.organization_id, l.transaction_id, l.extraction_type,
l.error_message, l.creation_date, l.last_update_date, l.created_by,
l.last_updated_by, l.last_update_login )
VALUES (null,
Decode(l_status, 0, l_batch_from_id,1, l_batch_to_id ) , d.extraction_type,
l_msg, Sysdate, Sysdate, g_user_id,
g_user_id, g_login_id );
SELECT COUNT(*) INTO l_exception_count
FROM opi_dbi_cogs_log
WHERE error_message IS NOT NULL;
using (SELECT *
FROM opi_dbi_cogs_stg ) c
ON ( m.order_line_id = c.order_line_id
AND m.margin_ou_id = c.margin_ou_id )
WHEN matched THEN UPDATE SET
inventory_item_id = c.inventory_item_id,
organization_id = c.organization_id,
margin_date = Greatest( Nvl(margin_date, c.cogs_date), c.cogs_date),
cogs_val_b = Nvl(cogs_val_b,0) + Nvl(c.cogs_val_b,0),
cogs_conversion_rate = Decode(Sign(c.cogs_date - Nvl(cogs_date, c.cogs_date)),
-1, cogs_conversion_rate, c.cogs_conversion_rate),
cogs_date= Greatest( Nvl(cogs_date,c.cogs_date), c.cogs_date),
cogs_source = c.source,
cogs_ship_ou_id = c.ship_ou_id,
cogs_sell_ou_id = c.sell_ou_id,
turns_cogs_flag = c.turns_cogs_flag,
last_update_date = Sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
WHEN NOT matched THEN
INSERT (m.inventory_item_id, m.organization_id, m.order_line_id,
m.margin_date, m.margin_ou_id,
m.cogs_val_b, m.cogs_conversion_rate, m.cogs_date,
m.cogs_source, m.cogs_ship_ou_id, m.cogs_sell_ou_id,
m.turns_cogs_flag,m.creation_date, m.last_update_date,
m.created_by, m.last_updated_by, m.last_update_login)
VALUES ( c.inventory_item_id, c.organization_id, c.order_line_id,
c.cogs_date, c.margin_ou_id,
c.cogs_val_b, c.cogs_conversion_rate, c.cogs_date,
c.source, c.ship_ou_id, c.sell_ou_id,
c.turns_cogs_flag, Sysdate, Sysdate,
g_user_id, g_user_id, g_login_id);
SELECT COUNT(*) INTO l_empty_count
FROM opi_dbi_cogs_stg
Where rownum = 1;
IF l_empty_count > 0 THEN -- not empty, do a master update to remove missing rate
UPDATE opi_dbi_cogs_stg
SET cogs_conversion_rate =
fii_currency.get_global_rate_primary(cogs_currency_code,cogs_date),
last_update_date = Sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
WHERE NVL(cogs_conversion_rate,-99) < 0 ;
SELECT transaction_id INTO l_last_trx_id
FROM opi_dbi_cogs_log
WHERE extraction_type = 'OPM_COGS_SUBLEDGER';
SELECT Nvl(MAX(subledger_id),l_last_trx_id)
INTO l_new_trx_id
from gl_subr_led tst
where tst.doc_type in ( 'OMSO', 'PORC')
and tst.acct_ttl_type = 5200
AND tst.gl_trans_date >= global_start_date
AND tst.subledger_id >= l_last_trx_id;
SELECT Nvl(MIN(subledger_id),0) - 1,
Nvl(MAX(subledger_id),0)
INTO l_last_trx_id,
l_new_trx_id
from gl_subr_led tst
where tst.doc_type in ( 'OMSO', 'PORC')
and tst.acct_ttl_type = 5200
AND tst.gl_trans_date >= global_start_date;
using ( SELECT NULL organization_id,
'OPM_COGS_SUBLEDGER' extraction_type
FROM dual ) d
ON ( l.extraction_type = d.extraction_type )
WHEN matched THEN UPDATE SET
l.organization_id = NULL,
l.transaction_id = Decode(l_status, 0, l_batch_from_id,
1, l_batch_to_id ),
l.error_message = l_msg,
l.last_update_date = Sysdate,
l.last_updated_by = g_user_id,
l.last_update_login = g_login_id
WHEN NOT matched THEN
INSERT (l.organization_id, l.transaction_id, l.extraction_type,
l.error_message, l.creation_date, l.last_update_date, l.created_by,
l.last_updated_by, l.last_update_login )
VALUES (d.organization_id,
Decode(l_status, 0, l_batch_from_id,1, l_batch_to_id ) , d.extraction_type,
l_msg, Sysdate, Sysdate, g_user_id,
g_user_id, g_login_id );
SELECT Nvl(MIN(subledger_id),0),
Nvl(MAX(subledger_id),0)
INTO l_last_trx_id,
l_new_trx_id
from gl_subr_led tst
where tst.doc_type in ( 'OMSO', 'PORC' )
and tst.acct_ttl_type = 5200
AND tst.gl_trans_date >= global_start_date;
using ( SELECT NULL organization_id,
'OPM_COGS_SUBLEDGER' extraction_type
FROM dual ) d
ON ( l.extraction_type = d.extraction_type )
WHEN matched THEN UPDATE SET
l.organization_id = NULL,
l.transaction_id = Decode(l_status, 0, l_batch_from_id,
1, l_batch_to_id ),
l.error_message = l_msg,
l.last_update_date = Sysdate,
l.last_updated_by = g_user_id,
l.last_update_login = g_login_id
WHEN NOT matched THEN
INSERT (l.organization_id, l.transaction_id, l.extraction_type,
l.error_message, l.creation_date, l.last_update_date, l.created_by,
l.last_updated_by, l.last_update_login )
VALUES (d.organization_id,
Decode(l_status, 0, 0 /* if error then write 0 */, 1, l_batch_to_id ) , d.extraction_type,
l_msg, Sysdate, Sysdate, g_user_id,
g_user_id, g_login_id );
/* Deletes removed here and replaced in wrapper */
/* delete from opi_dbi_cogs_log */
/* where extraction_type like 'OPM%'; */
/* delete from opi_dbi_cogs_stg */
/* where source like 'OPM%'; */
/* delete from opi_dbi_margin_f */
/* where cogs_source like 'OPM%'; */