[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO opi_dbi_opm_wip_led_current
( orgn_code,
item_id,
gl_trans_date,
trans_qty,
amount_base)
SELECT
t.orgn_code,
t.item_id,
led.gl_trans_date,
sum (t.trans_qty),
sum (led.amount_base)
FROM
( SELECT
doc_type,
doc_id,
line_id,
TRUNC(trans_date) trans_date,
orgn_code,
item_id,
SUM(trans_qty) trans_qty
FROM
ic_tran_pnd
WHERE
doc_type = 'PROD'
AND line_type IN (1,2)
AND completed_ind = 1
AND gl_posted_ind = 1
AND trans_date >= p_global_start_date
GROUP BY
doc_type,
doc_id,
line_id,
TRUNC(trans_date),
orgn_code,
item_id
) t,
( SELECT
sub.doc_type,
sub.doc_id,
sub.line_id,
TRUNC(sub.gl_trans_date) gl_trans_date,
SUM(sub.amount_base * sub.debit_credit_sign) amount_base
FROM
gl_subr_led sub,
opi_dbi_run_log_curr log
WHERE
sub.gl_trans_date >= p_global_start_date
AND sub.acct_ttl_type = 1500
AND sub.doc_type = 'PROD'
AND log.source = OPM_SOURCE
AND log.etl_id = WIP_COMPLETION_ETL
AND log.organization_id IS NULL
AND sub.subledger_id >= log.start_txn_id
AND sub.subledger_id < log.next_start_txn_id
GROUP BY
sub.doc_type,
sub.doc_id,
sub.line_id,
TRUNC(sub.gl_trans_date)
) led
WHERE
t.doc_type = led.doc_type
AND t.doc_id = led.doc_id
AND t.line_id = led.line_id
AND t.trans_date = led.gl_trans_date
GROUP BY
t.orgn_code,
t.item_id,
led.gl_trans_date;
select stop_reason_code into l_prior_populated -- get state of prior table from log
from opi_dbi_run_log_curr
where etl_id = 1
and source = 2;
INSERT INTO opi_dbi_opm_wip_tst_prior
(orgn_code, item_id, gl_trans_date, trans_qty, amount_base)
SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
FROM opi_dbi_opm_wip_tst_current;
update opi_dbi_run_log_curr -- state = prior populated and current truncated
set
stop_reason_code = 9999, -- flag to indicate prior tst table has been populated
last_update_date = sysdate,
last_updated_by = s_user_id,
last_update_login = s_login_id
where etl_id = 1
and source = 2;
INSERT INTO opi_dbi_opm_wip_tst_current
( orgn_code,
item_id,
gl_trans_date,
trans_qty,
amount_base)
SELECT
t.orgn_code,
t.item_id,
led.gl_trans_date,
sum (t.trans_qty),
sum (led.amount_base)
FROM
( SELECT
doc_type,
doc_id,
line_id,
TRUNC(trans_date) trans_date,
orgn_code,
item_id,
SUM(trans_qty) trans_qty
FROM
ic_tran_pnd
WHERE
doc_type = 'PROD'
AND line_type IN (1,2)
AND completed_ind = 1
AND gl_posted_ind = 0
AND trans_date >= p_global_start_date
GROUP BY
doc_type,
doc_id,
line_id,
TRUNC(trans_date),
orgn_code,
item_id
) t,
( SELECT
doc_type,
doc_id,
line_id,
TRUNC(gl_trans_date) gl_trans_date,
SUM(amount_base * debit_credit_sign) amount_base
FROM
gl_subr_tst
WHERE
gl_trans_date >= p_global_start_date
AND acct_ttl_type = 1500
AND doc_type = 'PROD'
GROUP BY
doc_type,
doc_id,
line_id,
TRUNC(gl_trans_date)
) led
WHERE
t.doc_type = led.doc_type
AND t.doc_id = led.doc_id
AND t.line_id = led.line_id
AND t.trans_date = led.gl_trans_date
GROUP BY
t.orgn_code,
t.item_id,
led.gl_trans_date;
INSERT INTO OPI_DBI_WIP_COMP_STG (
organization_id,
inventory_item_id,
transaction_date,
completion_quantity,
completion_value_b,
uom_code,
source,
planned_item,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT
msi.organization_id,
msi.inventory_item_id,
t.gl_trans_date,
sum (t.trans_qty),
sum (t.amount_base),
msi.primary_uom_code,
OPM_SOURCE, -- this is only for OPI orgs
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y'),
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id
FROM
sy_orgn_mst_b org,
ic_whse_msT w,
ic_item_mst_b iim,
mtl_system_items_b msi,
(
SELECT orgn_code, item_id, gl_trans_date,
SUM(trans_qty) trans_qty, SUM(amount_base) amount_base
FROM
(
SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
FROM opi_dbi_opm_wip_led_current
UNION ALL
SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
FROM opi_dbi_opm_wip_tst_current
)
GROUP BY orgn_code, item_id, gl_trans_date
HAVING SUM(trans_qty) <> 0 OR SUM(amount_base) <> 0
) t
WHERE
org.orgn_code = t.orgn_code
AND w.whse_code = org.resource_whse_code
AND iim.item_id = t.item_id
AND msi.organization_id = w.mtl_organization_id
AND msi.segment1 = iim.item_no
GROUP BY
msi.organization_id,
msi.inventory_item_id,
t.gl_trans_date,
msi.primary_uom_code,
msi.mrp_planning_code;
INSERT INTO OPI_DBI_WIP_COMP_STG (
organization_id,
inventory_item_id,
transaction_date,
completion_quantity,
completion_value_b,
uom_code,
source,
planned_item,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT
msi.organization_id,
msi.inventory_item_id,
t.gl_trans_date,
sum (t.trans_qty),
sum (t.amount_base),
msi.primary_uom_code,
OPM_SOURCE, -- this is only for OPI orgs
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y'),
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id
FROM
sy_orgn_mst_b org,
ic_whse_msT w,
ic_item_mst_b iim,
mtl_system_items_b msi,
(
SELECT orgn_code, item_id, gl_trans_date,
SUM(trans_qty) trans_qty, SUM(amount_base) amount_base
FROM
(
SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
FROM opi_dbi_opm_wip_led_current
UNION ALL
SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
FROM opi_dbi_opm_wip_tst_current
UNION ALL
SELECT orgn_code, item_id, gl_trans_date, -trans_qty, -amount_base
FROM opi_dbi_opm_wip_tst_prior
)
GROUP BY orgn_code, item_id, gl_trans_date
HAVING SUM(trans_qty) <> 0 OR SUM(amount_base) <> 0
) t
WHERE
org.orgn_code = t.orgn_code
AND w.whse_code = org.resource_whse_code
AND iim.item_id = t.item_id
AND msi.organization_id = w.mtl_organization_id
AND msi.segment1 = iim.item_no
GROUP BY
msi.organization_id,
msi.inventory_item_id,
t.gl_trans_date,
msi.primary_uom_code,
msi.mrp_planning_code;
update opi_dbi_run_log_curr -- state = successful
set
stop_reason_code = NULL, -- flag to indicate prior tst table has been populated
last_update_date = sysdate,
last_updated_by = s_user_id,
last_update_login = s_login_id
where etl_id = 1
and source = 2;