[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_login NUMBER;
g_last_updated_by NUMBER;
'INSERT INTO opi_dbi_opm_inv_led_current
(
whse_code,
item_id,
transaction_date,
onhand_qty,
onhand_value_b
)
SELECT
t.whse_code,
t.item_id,
t.trans_date,
SUM(whse_line_trans_qty),
SUM(DECODE(line_trans_qty, 0, 0,
led.amount_base*(whse_line_trans_qty/line_trans_qty))) activity_val_b
FROM
(
SELECT ' || lv_led_hint || '
led.doc_type, led.doc_id, led.line_id,
TRUNC(led.gl_trans_date) gl_trans_date,
SUM(led.amount_base * led.debit_credit_sign) amount_base
FROM gl_subr_led led
WHERE
led.acct_ttl_type = 1500
AND led.subledger_id BETWEEN :1 AND :2
AND led.gl_trans_date >= :3
GROUP BY doc_type, doc_id, line_id, TRUNC(gl_trans_date)
) led,
(
SELECT
doc_type, doc_id, line_id,
trans_date, item_id, whse_code,
SUM(trans_qty) whse_line_trans_qty,
SUM(SUM(trans_qty))
OVER (PARTITION BY doc_type, doc_id, line_id) line_trans_qty
FROM
(SELECT doc_type, doc_id, line_id, item_id, whse_code,
trunc(trans_date) trans_date, trans_qty
FROM ic_tran_pnd
WHERE completed_ind = 1 AND gl_posted_ind = decode(doc_type, ''RECV'', gl_posted_ind, 1)
AND trans_date >= :4
UNION ALL
SELECT doc_type, doc_id, line_id, item_id, whse_code,
trunc(trans_date) trans_date, trans_qty
FROM ic_tran_cmp
WHERE gl_posted_ind = 1
AND trans_date >= :5
)
GROUP BY
doc_type, doc_id, line_id, trans_date, item_id, whse_code
HAVING SUM(trans_qty) <> 0
) t
WHERE
led.doc_type = t.doc_type
AND led.doc_id = DECODE(led.doc_type, ''XFER'', t.doc_id, led.doc_id)
AND led.gl_trans_date = t.trans_date
AND led.line_id = t.line_id
GROUP BY
t.whse_code,
t.item_id,
t.trans_date';
'INSERT INTO opi_dbi_opm_inv_led_current
(
whse_code,
item_id,
transaction_date,
onhand_qty,
onhand_value_b
)
SELECT
whse.whse_code whse_code,
led.line_id item_id,
led.gl_doc_date + 1 transaction_date, -- Add one to date to prevent transaction from
-- being counted for morning of last day of period
0 onhand_qty, -- Transaction did not affect quantities, only value
-sum(led.amount_base) onhand_value_b
FROM
(
SELECT ' || lv_led_hint || '
led.doc_id,
led.line_id,
TRUNC(led.gl_doc_date) gl_doc_date,
SUM(led.amount_base * led.debit_credit_sign) amount_base
FROM gl_subr_led led
WHERE
led.acct_ttl_type = 6250 -- IRV ACCT
AND led.subledger_id BETWEEN :1 AND :2
AND led.gl_doc_date >= :3
AND DOC_TYPE = ''RVAL''
GROUP BY doc_id, line_id, TRUNC(gl_doc_date)
) led,
IC_WHSE_MST WHSE
WHERE
led.doc_id = whse.mtl_organization_id
GROUP BY
whse.whse_code,
led.line_id, --item_id from subledger line_id column
led.gl_doc_date';
'INSERT INTO opi_dbi_opm_inv_led_current
(
whse_code,
item_id,
transaction_date,
intransit_qty,
intransit_value_b
)
(SELECT whse_code, item_id, trans_date, sum (qty) qty, sum (val) val
FROM (
SELECT ' || lv_led_hint || '
whse_mst.whse_code,
ic_item.item_id,
trunc (led.gl_trans_date) trans_date,
pnd.trans_qty qty,
sum (led.debit_credit_sign * led.amount_base *
decode (pnd.line_id, -- check if need exchange rate
1, 1, -- shipment does not need exchange rate,
-- so default to 1
2, decode (nvl (led.mul_div_sign, 0),
-- if there is no mul_div_sign,
-- there will be no exchange rate,
-- so we return 1 i.e. no exchange
-- rate.
-- Else if mul_div_sign = 0,
-- multiply by exchange rate
0, nvl(led.exchange_rate, 1),
-- if mul_div_sign = 2, divide
-- by exchange rate
1, 1/(nvl (led.exchange_rate, 1))))
) val
FROM
-- there can be a many to many mapping between the
-- ic_tran and subledger, so we sum up all quantities
-- for a given doc and line id before matching it
-- up to subledger.
-- Note the -ve sign on the quantities
(SELECT doc_type, doc_id, line_id, whse_code, item_id,
sum (-1 * trans_qty) trans_qty,
trans_um, gl_posted_ind,
completed_ind
FROM ic_tran_pnd
WHERE doc_type = ''XFER''
AND completed_ind = 1 -- completed transaction
AND gl_posted_ind = 1 -- and posted to perm ledger
GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
trans_um, gl_posted_ind, completed_ind
UNION ALL
SELECT doc_type, doc_id, line_id, whse_code, item_id,
sum (-1 * trans_qty) trans_qty, trans_um,
gl_posted_ind,
1 -- all transactions are completed in tran_cmp
FROM ic_tran_cmp
WHERE doc_type = ''XFER''
AND gl_posted_ind = 1 -- completed+posted transaction
GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
trans_um, gl_posted_ind, 1
) pnd,
gl_subr_led led,
ic_whse_mst whse_mst,
mtl_system_items_b msi,
ic_item_mst_b ic_item,
ic_xfer_mst xfer
WHERE pnd.doc_id = led.doc_id
AND pnd.doc_type = led.doc_type
AND pnd.doc_id = xfer.transfer_id
AND led.acct_ttl_type = 1570 -- intransit account
-- ignore acct. type 1500 entries
AND pnd.line_id = led.line_id
AND led.subledger_id BETWEEN :1
AND :2
AND trunc(led.gl_trans_date) >= :3
AND xfer.from_warehouse = whse_mst.whse_code -- for org_id
-- fob = receipt
AND ic_item.item_id = pnd.item_id -- for inventory_item_id
AND msi.segment1 = ic_item.item_no
AND msi.organization_id = whse_mst.mtl_organization_id
GROUP BY whse_mst.whse_code, ic_item.item_id,
trunc (led.gl_trans_date), pnd.trans_qty,
pnd.doc_id, pnd.line_id
-- grouping by line_id and doc_id
-- important if we are summing up
-- multiple lines in ic_tran with same
-- doc_id
) inv_intransit_led_led
GROUP BY whse_code, item_id, trans_date)';
'INSERT INTO opi_dbi_opm_inv_led_current
(
whse_code,
item_id,
transaction_date,
intransit_qty,
intransit_value_b
)
SELECT whse_code, item_id, trans_date, sum (qty) qty, sum (val) val
FROM
-- to get the from and to organizations depending on what the FOB
-- is, we need to join back to the purchase order requisition lines
-- table, po_requisitions_lines_all.
-- For shipments (doc_type = OMSO) this is achieved through the
-- oe_order_lines_all table.
-- For receipts (doc_type = PORC) this is achieved through the
-- rcv_transactions table.
(SELECT w.whse_code,
ic_tran.item_id,
led.gl_trans_date trans_date,
ic_tran.trans_qty qty,
sum (led.amount_base * led.debit_credit_sign *
decode (mip.fob_point, -- check if need exchange rate
2, 1, -- FOB = receipt ==> shipping org is
-- owner. Since this is doc_type
-- OMSO, no exchange rate needed.
-- If FOB = shipment, then need
-- exchange rate to get shipping org
-- value
1, decode (nvl (led.mul_div_sign, 0),
-- if there is no mul_div_sign,
-- there will be no exchange rate,
-- so we return 1 i.e. no exchange
-- rate.
-- Else if mul_div_sign = 0,
-- multiply by exchange rate
0, nvl(led.exchange_rate, 1),
-- if mul_div_sign = 2, divide
-- by exchange rate
1, 1/(nvl (led.exchange_rate, 1))))
) val
FROM (SELECT doc_type, doc_id, line_id, whse_code, item_id,
sum(-1 * trans_qty) trans_qty, trans_um,
gl_posted_ind,
completed_ind
FROM ic_tran_pnd
WHERE doc_type = ''OMSO''
AND completed_ind = 1 -- completed and
AND gl_posted_ind = 1 -- posted to perm ledger
GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
trans_um, gl_posted_ind, completed_ind
UNION ALL
-- receipts into different lots can generate two lines
-- in ic_tran with same line_id so collapse based on
-- line_id
SELECT doc_type, doc_id, line_id, whse_code, item_id,
sum (-1 * trans_qty) trans_qty, trans_um,
gl_posted_ind,
1 -- all transactions are completed in tran_cmp
FROM ic_tran_cmp
WHERE doc_type = ''OMSO''
AND gl_posted_ind = 1 -- posted to perm ledger
GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
trans_um, gl_posted_ind, 1
) ic_tran,
oe_order_lines_all oola,
po_requisition_lines_all req,
mtl_interorg_parameters mip,
-- some transactions generate two entries in gl, so
-- make sure to collapse gl by line_id, doc_type ...
(SELECT ' || lv_led_hint || '
trunc (gl_trans_date) gl_trans_date, doc_id,
doc_type, line_id, acct_ttl_type,
amount_base, debit_credit_sign, exchange_rate,
mul_div_sign
FROM gl_subr_led subr
WHERE subledger_id BETWEEN :1
AND :2
AND trunc(subr.gl_trans_date) >= :3
AND acct_ttl_type = 1590
) led,
ic_whse_mst w
WHERE ic_tran.doc_type = led.doc_type
AND ic_tran.line_id = led.line_id
AND ic_tran.line_id = oola.line_id -- get line id details
AND oola.source_document_type_id = 10 --ensure this is internal
--order
AND req.requisition_line_id = oola.source_document_line_id
AND mip.from_organization_id = req.source_organization_id
AND mip.to_organization_id = req.destination_organization_id
AND w.mtl_organization_id =
decode (mip.fob_point, --FOB selects owning org
2, req.source_organization_id,
1, req.destination_organization_id)
GROUP BY w.whse_code,
ic_tran.item_id,
led.gl_trans_date,
ic_tran.trans_qty,
ic_tran.line_id
UNION ALL
SELECT w.whse_code,
ic_tran.item_id,
led.gl_trans_date trans_date,
ic_tran.trans_qty qty,
sum(led.amount_base * led.debit_credit_sign *
decode (mip.fob_point, -- check if need exchange rate
1, 1, -- FOB = shipping ==> receiving org is
-- owner. Since this is doc_type
-- PORC, no exchange rate needed.
-- If FOB = receipt, then need
-- exchange rate to get shipping org
-- value
2, decode (nvl (led.mul_div_sign, 0),
-- if there is no mul_div_sign,
-- there will be no exchange rate,
-- so we return 1 i.e. no exchange
-- rate.
-- Else if mul_div_sign = 0,
-- multiply by exchange rate
0, nvl(led.exchange_rate, 1),
-- if mul_div_sign = 2, divide
-- by exchange rate
1, 1/(nvl (led.exchange_rate, 1))))
) val
FROM (SELECT doc_type, doc_id, line_id, whse_code, item_id,
sum(-1 * trans_qty) trans_qty, trans_um,
gl_posted_ind, completed_ind
FROM ic_tran_pnd
WHERE doc_type = ''PORC''
AND completed_ind = 1 -- completed and
AND gl_posted_ind = 1 -- posted to perm ledger
GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
trans_um, gl_posted_ind, completed_ind
UNION ALL
-- receipts into different lots can generate two lines
-- in ic_tran with same line_id so collapse based on
-- line_id
SELECT doc_type, doc_id, line_id, whse_code, item_id,
sum (-1 * trans_qty) trans_qty, trans_um,
gl_posted_ind,
1 -- all transactions are completed in tran_cmp
FROM ic_tran_cmp
WHERE doc_type = ''PORC''
AND gl_posted_ind = 1 -- posted to perm ledger
GROUP BY doc_type, doc_id, line_id, whse_code, item_id,
trans_um, gl_posted_ind, 1
) ic_tran,
rcv_transactions rcv,
po_requisition_lines_all req,
mtl_interorg_parameters mip,
-- some transactions generate two entries in gl, so
-- make sure to collapse gl by line_id, doc_type ...
(SELECT ' || lv_led_hint || '
trunc (gl_trans_date) gl_trans_date, doc_id,
doc_type, line_id, acct_ttl_type,
amount_base, debit_credit_sign, exchange_rate,
mul_div_sign
FROM gl_subr_led subr
WHERE subledger_id BETWEEN :4
AND :5
AND trunc(subr.gl_trans_date) >= :6
AND acct_ttl_type = 1590 -- internal order account
) led,
ic_whse_mst w
WHERE ic_tran.doc_type = led.doc_type
AND ic_tran.line_id = led.line_id
AND rcv.transaction_id = led.line_id
AND req.requisition_line_id = rcv.requisition_line_id
AND req.destination_type_code = ''INVENTORY'' -- internal order
AND req.source_type_code = ''INVENTORY'' -- has source and
-- dest type as
-- INVENTORY
AND mip.from_organization_id = req.source_organization_id
AND mip.to_organization_id = req.destination_organization_id
AND w.mtl_organization_id =
decode (mip.fob_point, --FOB selects owning org
2, req.source_organization_id,
1, req.destination_organization_id)
GROUP BY w.whse_code,
ic_tran.item_id,
led.gl_trans_date,
ic_tran.trans_qty,
ic_tran.line_id
) int_order_in_transit
GROUP BY whse_code, item_id, trans_date';
'INSERT INTO opi_dbi_opm_inv_led_current
(
whse_code,
item_id,
transaction_date,
wip_value_b
)
SELECT
led.whse_code,
gmd.item_id,
led.transaction_date,
SUM(led.amount_base * gmd.cost_alloc) wip_val_b
FROM
(
SELECT ' || lv_led_hint || '
doc_id, h.wip_whse_code whse_code,
TRUNC(l.gl_trans_date) transaction_date,
SUM(l.amount_base * l.debit_credit_sign) amount_base
FROM gl_subr_led l, gme_batch_header h
WHERE
l.doc_type = ''PROD''
AND l.acct_ttl_type = 1530
AND l.doc_id = h.batch_id
AND l.subledger_id BETWEEN :1 AND :2
AND trunc(l.gl_trans_date) >= :3
GROUP BY l.doc_id, h.wip_whse_code, TRUNC(l.gl_trans_date)
) led,
gme_material_details gmd
WHERE
led.doc_id = gmd.batch_id
AND gmd.line_type = 1
GROUP BY
led.whse_code,
gmd.item_id,
led.transaction_date';
insert into OPI_DBI_OPM_INV_STG
(
organization_id,
/* csheu added the following line */
subinventory_code,
inventory_item_id,
transaction_date,
onhand_qty,
intransit_qty,
primary_uom,
onhand_value_b,
intransit_value_b,
wip_value_b
)
SELECT
whse.mtl_organization_id,
/* csheu added the following line */
/* stg.whse_code, */
/* cdaly replaced itwith the following line */
'-1', /* Key for Subinventory named Unassigned */
msi.inventory_item_id,
stg.transaction_date,
stg.onhand_qty,
stg.intransit_qty,
msi.primary_uom_code,
stg.onhand_value_b,
stg.intransit_value_b,
stg.wip_value_b
FROM
(
SELECT
item_id item_id,
whse_code whse_code,
transaction_date transaction_date,
SUM(onhand_qty) onhand_qty,
SUM(intransit_qty) intransit_qty,
SUM(onhand_value_b) onhand_value_b,
SUM(intransit_value_b) intransit_value_b,
SUM(wip_value_b) wip_value_b
FROM
(
SELECT
item_id,
whse_code,
transaction_date,
onhand_qty,
intransit_qty,
onhand_value_b,
intransit_value_b,
wip_value_b
FROM
opi_dbi_opm_inv_tst_current c
-- UNION ALL
-- SELECT
-- item_id,
-- whse_code,
-- transaction_date,
-- -onhand_qty,
-- -intransit_qty,
-- -onhand_value_b,
-- -intransit_value_b,
-- -wip_value_b
-- FROM
-- opi_dbi_opm_inv_tst_prior p
UNION ALL
SELECT
item_id,
whse_code,
transaction_date,
onhand_qty,
intransit_qty,
onhand_value_b,
intransit_value_b,
wip_value_b
FROM
opi_dbi_opm_inv_led_current led
)
GROUP BY
item_id,
whse_code,
transaction_date
HAVING
SUM(onhand_qty) <> 0
OR SUM(intransit_qty) <> 0
OR SUM(onhand_value_b) <> 0
OR SUM(intransit_value_b) <> 0
OR SUM(wip_value_b) <> 0
) stg,
ic_item_mst_b iim,
ic_whse_mst whse,
mtl_system_items_b msi
WHERE
iim.item_id = stg.item_id
AND whse.whse_code = stg.whse_code
AND msi.organization_id = whse.mtl_organization_id
AND msi.segment1 = iim.item_no
;
/* SELECT
log.transaction_id + 1 from_transaction_id,
led.to_transaction_id
INTO
l_from_transaction_id,
l_to_transaction_id
FROM
(
SELECT /*+ NO_MERGE MAX(subledger_id) to_transaction_id
FROM gl_subr_led
) led,
opi_dbi_inv_value_log log
WHERE
log.type = 'GSL'
AND log.organization_id = 0
AND log.source = 2;*/
select MAX(subledger_id),MIN(subledger_id) into l_to_transaction_id , l_from_transaction_id
FROM gl_subr_led
where gl_trans_date > g_global_start_date;
g_last_update_login := fnd_global.login_id;
g_last_updated_by := fnd_global.user_id;
select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into g_global_start_date from DUAL;
bis_collection_utilities.put_line(TO_CHAR(l_rows1 + l_rows2) || ' rows have been inserted from OPM');