[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_run_log l,
gl_subr_tst tst
WHERE l.source = OPM_SOURCE
AND tst.subledger_id = l.start_txn_id
UNION
SELECT tst.gl_trans_date from_date
FROM opi_dbi_cogs_run_log l,
gl_subr_led tst
WHERE l.source = OPM_SOURCE
AND tst.subledger_id = l.start_txn_id
);
insert into opi_dbi_cogs_fstg
(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,TOP_MODEL_LINE_ID
,TOP_MODEL_ITEM_ID
,TOP_MODEL_ITEM_UOM
,TOP_MODEL_ORG_ID
,CUSTOMER_ID
,COGS_VAL_B
,COGS_DATE
,SOURCE
,TURNS_COGS_FLAG
)
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,ORDER_LINE_ID
,INVENTORY_ITEM_ID
,TOP_MODEL_ITEM_UOM
,ORGANIZATION_ID
,SOLD_TO_ORG_ID
,sum(COGS_VAL_B)
,max(COGS_DATE)
,SOURCE
,TURNS_COGS_FLAG
from
( select /*+ leading(whse) use_nl(msi,cust_acct) */
lines.inventory_item_id INVENTORY_ITEM_ID,
whse.mtl_organization_id ORGANIZATION_ID,
tran.oe_order_line_id ORDER_LINE_ID,
msi.primary_uom_code TOP_MODEL_ITEM_UOM,
tran.cogs_val_b COGS_VAL_B,
trunc(tran.gl_trans_date) COGS_DATE,
nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID,
Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS) TURNS_COGS_FLAG,
OPM_SOURCE SOURCE
from oe_order_lines_all lines,
hz_cust_accounts cust_acct,
ic_whse_mst whse,
mtl_system_items_b msi,
(select /*+ leading(tst) index(tran,IC_TRAN_PNDI2) use_nl(tran) */ rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code,
tst.gl_trans_date,
avg(tst.cogs_val_b) COGS_VAL_B
from ic_tran_pnd tran,
rcv_transactions rcv,
(select /*+index(tst,gl_subr_tst_n2) */
tst.line_id, tst.doc_type, tst.gl_trans_date,
sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
from gl_subr_tst tst
where tst.doc_type = 'PORC'
and tst.acct_ttl_type = 5200
and tst.gl_trans_date >= global_start_date
group by tst.line_id, tst.doc_type, tst.gl_trans_date) tst
where tran.completed_ind = 1
and tran.gl_posted_ind = 0
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
group by rcv.oe_order_line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date
union all
select /*+ leading(tst) index(tran,IC_TRAN_PNDI2) use_nl(tran) */ tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code,
tst.gl_trans_date,
avg(tst.cogs_val_b) COGS_VAL_B
from ic_tran_pnd tran,
(select /*+index(tst,gl_subr_tst_n2) */
tst.line_id, tst.doc_type, tst.gl_trans_date,
sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
from gl_subr_tst tst
where tst.doc_type = 'OMSO'
and tst.acct_ttl_type = 5200
and tst.gl_trans_date >= global_start_date
group by tst.line_id, tst.doc_type, tst.gl_trans_date) tst
where tran.completed_ind = 1
and tran.gl_posted_ind = 0
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
group by tran.line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date) tran
where lines.line_id = tran.oe_order_line_id
and lines.sold_to_org_id = cust_acct.cust_account_id(+)
and whse.whse_code = tran.whse_code
and msi.inventory_item_id=lines.inventory_item_id
and msi.organization_id=lines.ship_from_org_id)
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TOP_MODEL_ITEM_UOM
,SOLD_TO_ORG_ID
,ORDER_LINE_ID
,TURNS_COGS_FLAG
,SOURCE ;
insert into opi_dbi_cogs_fstg
(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,TOP_MODEL_LINE_ID
,TOP_MODEL_ITEM_ID
,TOP_MODEL_ITEM_UOM
,TOP_MODEL_ORG_ID
,CUSTOMER_ID
,COGS_VAL_B
,COGS_DATE
,SOURCE
,TURNS_COGS_FLAG
)
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,ORDER_LINE_ID
,INVENTORY_ITEM_ID
,TOP_MODEL_ITEM_UOM
,ORGANIZATION_ID
,SOLD_TO_ORG_ID
,sum(COGS_VAL_B)
,max(COGS_DATE)
,SOURCE
,TURNS_COGS_FLAG
from
( select /*+ index(cust_acct, HZ_CUST_ACCOUNTS_U1) use_nl(cust_acct) */
lines.inventory_item_id INVENTORY_ITEM_ID,
whse.mtl_organization_id ORGANIZATION_ID,
tran.oe_order_line_id ORDER_LINE_ID,
msi.primary_uom_code TOP_MODEL_ITEM_UOM,
tran.cogs_val_b COGS_VAL_B,
trunc(tran.gl_trans_date) COGS_DATE,
nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID,
Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS) TURNS_COGS_FLAG,
OPM_SOURCE SOURCE
from oe_order_lines_all lines,
hz_cust_accounts cust_acct,
ic_whse_mst whse,
mtl_system_items_b msi,
(select
rcv.oe_order_line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code,
tst.gl_trans_date,
avg(tst.cogs_val_b) COGS_VAL_B
from ic_tran_pnd tran,
rcv_transactions rcv,
(select /*+ index(tst,GL_SUBR_LED_PK) */
tst.line_id, tst.doc_type, tst.gl_trans_date,
sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
from gl_subr_led tst
where tst.doc_type = 'PORC'
and tst.acct_ttl_type = 5200
and tst.subledger_id between p_last_id and p_newest_id
and tst.GL_TRANS_DATE >= global_start_date
group by tst.line_id, tst.doc_type, tst.gl_trans_date
) tst
where tran.completed_ind = 1
and tran.gl_posted_ind = 1
and tran.line_id = rcv.transaction_id
and rcv.oe_order_line_id is NOT NULL
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
group by rcv.oe_order_line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date
union all
select
tran.line_id oe_order_line_id,
tran.line_id,
tran.orgn_code,
tran.whse_code,
tst.gl_trans_date,
avg(tst.cogs_val_b) COGS_VAL_B
from ic_tran_pnd tran,
(select /*+ index(tst,GL_SUBR_LED_PK) */
tst.line_id, tst.doc_type, tst.gl_trans_date,
sum(tst.debit_credit_sign*tst.amount_base) COGS_VAL_B
from gl_subr_led tst
where tst.doc_type = 'OMSO'
and tst.acct_ttl_type = 5200
and tst.subledger_id between p_last_id and p_newest_id
and tst.GL_TRANS_DATE >= global_start_date
group by tst.line_id, tst.doc_type, tst.gl_trans_date
) tst
where tran.completed_ind = 1
and tran.gl_posted_ind = 1
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
group by tran.line_id, tran.line_id, tran.orgn_code, tran.whse_code, tst.gl_trans_date
) tran
where lines.line_id = tran.oe_order_line_id
and lines.sold_to_org_id = cust_acct.cust_account_id(+)
and whse.whse_code = tran.whse_code
and msi.inventory_item_id=lines.inventory_item_id
and msi.organization_id=lines.ship_from_org_id
)
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TOP_MODEL_ITEM_UOM
,SOLD_TO_ORG_ID
,ORDER_LINE_ID
,TURNS_COGS_FLAG
,SOURCE ;
insert /*+ append parallel(opi_dbi_cogs_fstg) */ into opi_dbi_cogs_fstg
(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,TOP_MODEL_LINE_ID
,TOP_MODEL_ITEM_ID
,TOP_MODEL_ITEM_UOM
,TOP_MODEL_ORG_ID
,CUSTOMER_ID
,COGS_VAL_B
,COGS_DATE
,SOURCE
,TURNS_COGS_FLAG
)
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,ORDER_LINE_ID
,INVENTORY_ITEM_ID
,TOP_MODEL_ITEM_UOM
,ORGANIZATION_ID
,SOLD_TO_ORG_ID
,COGS_VAL_B
,COGS_DATE
,SOURCE
,TURNS_COGS_FLAG
from
(
select
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,TOP_MODEL_ITEM_UOM
,SOLD_TO_ORG_ID
,sum(COGS_VAL_B) COGS_VAL_B
,max(COGS_DATE) COGS_DATE
,TURNS_COGS_FLAG
,SOURCE
from
( select /*+ use_hash(whse,lines,cust_acct,msi) parallel(tst) parallel(lines) parallel(cust_acct) parallel(msi) parallel(whse) */
lines.inventory_item_id INVENTORY_ITEM_ID ,
whse.mtl_organization_id ORGANIZATION_ID ,
tran.oe_order_line_id ORDER_LINE_ID ,
msi.primary_uom_code TOP_MODEL_ITEM_UOM ,
tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
trunc(GL_TRANS_DATE) COGS_DATE ,
nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID ,
Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS ) TURNS_COGS_FLAG ,
OPM_SOURCE SOURCE
from gl_subr_tst tst,
oe_order_lines_all lines,
hz_cust_accounts cust_acct,
ic_whse_mst whse,
mtl_system_items_b msi,
(
select /*+ full(tran) full(rcv) use_hash(tran) 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) */
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
) tran
where tst.doc_type in ( 'OMSO', 'PORC' )
and tst.acct_ttl_type = 5200
and lines.line_id = tran.oe_order_line_id
and lines.sold_to_org_id = cust_acct.cust_account_id(+)
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
and whse.whse_code = tran.whse_code
and msi.inventory_item_id=lines.inventory_item_id
and msi.organization_id=lines.ship_from_org_id
and tst.GL_TRANS_DATE >= global_start_date
) A
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TOP_MODEL_ITEM_UOM
,SOLD_TO_ORG_ID
,ORDER_LINE_ID
,TURNS_COGS_FLAG
,SOURCE
)
;
insert /*+ append parallel(opi_dbi_cogs_fstg) */ into opi_dbi_cogs_fstg
(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ORDER_LINE_ID
,TOP_MODEL_LINE_ID
,TOP_MODEL_ITEM_ID
,TOP_MODEL_ITEM_UOM
,TOP_MODEL_ORG_ID
,CUSTOMER_ID
,COGS_VAL_B
,COGS_DATE
,SOURCE
,TURNS_COGS_FLAG
)
(select
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ORDER_LINE_ID,
ORDER_LINE_ID,
INVENTORY_ITEM_ID,
TOP_MODEL_ITEM_UOM,
ORGANIZATION_ID,
SOLD_TO_ORG_ID,
sum(COGS_VAL_B),
max(COGS_DATE),
SOURCE,
TURNS_COGS_FLAG
from
( select
/*+ full(tst) use_hash(tst, lines,cust_acct,msi,whse) parallel(tst) parallel(lines) parallel(cust_acct) parallel(msi) parallel(whse) */
lines.inventory_item_id INVENTORY_ITEM_ID,
whse.mtl_organization_id ORGANIZATION_ID ,
tran.oe_order_line_id ORDER_LINE_ID,
msi.primary_uom_code TOP_MODEL_ITEM_UOM ,
tst.debit_credit_sign*tst.amount_base COGS_VAL_B ,
trunc(GL_TRANS_DATE) COGS_DATE ,
nvl(cust_acct.party_id, -1) SOLD_TO_ORG_ID ,
Decode(lines.source_type_code, 'EXTERNAL', DO_NOT_INCLUDE_FOR_TURNS,INCLUDE_FOR_TURNS) TURNS_COGS_FLAG ,
OPM_SOURCE SOURCE
from gl_subr_led tst,
(select /*+ full(tran) full(rcv) use_hash(tran) 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 /*+ full(tran) 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) tran,
oe_order_lines_all lines,
hz_cust_accounts cust_acct,
mtl_system_items_b msi,
ic_whse_mst whse
where tst.doc_type in ( 'OMSO', 'PORC' )
and tst.acct_ttl_type = 5200
and lines.line_id = tran.oe_order_line_id
and lines.sold_to_org_id = cust_acct.cust_account_id(+)
and tran.doc_type = tst.doc_type
and tran.line_id = tst.line_id
and whse.whse_code = tran.whse_code
and msi.inventory_item_id=lines.inventory_item_id
and msi.organization_id=lines.ship_from_org_id
and tst.subledger_id >= p_last_id and tst.subledger_id +0 <= p_newest_id
and tst.GL_TRANS_DATE >= global_start_date
)
group by
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TOP_MODEL_ITEM_UOM
,SOLD_TO_ORG_ID
,ORDER_LINE_ID
,TURNS_COGS_FLAG
,SOURCE );
SELECT start_txn_id INTO l_last_trx_id
FROM opi_dbi_cogs_run_log
WHERE source = OPM_SOURCE;
SELECT NVL(MAX(subledger_id),l_last_trx_id)
INTO l_new_trx_id
from gl_subr_led led
where led.doc_type in ( 'OMSO', 'PORC')
and led.acct_ttl_type = 5200
AND led.gl_trans_date >= global_start_date
AND led.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 led
where led.doc_type in ( 'OMSO', 'PORC')
and led.acct_ttl_type = 5200
AND led.gl_trans_date >= global_start_date;
using ( SELECT NULL organization_id,
OPM_SOURCE extraction_type
FROM dual ) d
ON ( l.source = d.extraction_type )
WHEN matched THEN UPDATE SET
l.organization_id = NULL,
l.start_txn_id = l_batch_from_id,
l.next_start_txn_id = l_batch_to_id
WHEN NOT matched THEN
INSERT ( l.ORGANIZATION_ID
,l.SOURCE
,l.LAST_COLLECTION_DATE
,l.INIT_TXN_ID
,l.START_TXN_ID
,l.NEXT_START_TXN_ID
,l.STOP_REASON_CODE
,l.LAST_TRANSACTION_DATE)
VALUES (d.organization_id,
OPM_SOURCE,
null,
Decode(l_status, 0, 0 ,l_batch_from_id),
Decode(l_status, 0, 0 ,l_batch_from_id),
Decode(l_status, 0, 0 ,l_batch_to_id) ,
null,
null);
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_SOURCE extraction_type
FROM dual ) d
ON ( l.source = d.extraction_type )
WHEN matched THEN UPDATE SET
l.organization_id = NULL,
l.start_txn_id = l_batch_to_id,
l.next_start_txn_id = NULL
WHEN NOT matched THEN
INSERT ( l.ORGANIZATION_ID
,l.SOURCE
,l.LAST_COLLECTION_DATE
,l.INIT_TXN_ID
,l.START_TXN_ID
,l.NEXT_START_TXN_ID
,l.STOP_REASON_CODE
,l.LAST_TRANSACTION_DATE)
VALUES (d.organization_id,
OPM_SOURCE,
null,
Decode(l_status, 0, 0 ,l_batch_from_id),
Decode(l_status, 0, 0 ,l_batch_to_id),
Decode(l_status, 0, 0 ,NULL) ,
null,
null);