The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_wip_scrap_fact_incr (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER);
update_wip_scrap_fact_incr (errbuf, retcode);
current log table have been updated successfully using the Common Module
API etl_report_success.
WIP Scrap ETL needs to extract two types of transactions from
MMT:
90 - WIP scrap transaction increases the quantity/value
of WIP scrap
91 - Return from scrap transaction decreases the quantity/value of
of WIP scrap.
The WIP valuation account has an accounting line type of 7 in MTA, but
the WIP valuation account decreases on WIP scrap and increases
on return from scrap. Thus for every transaction, the corresponding
value we pick is -1 * (sum of all accouting line type 7) because this
ETL must report increased scrap value on scrapping and decreased
value on scrap returns.
Parameters:
p_global_start_date - global start date for DBI collection.
Expect this to be trunc'ed
Date Author Action
04/29/2003 Dinkar Gupta Wrote procedure
*/
PROCEDURE collect_incr_opi_wip_scrap (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_global_start_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'collect_incr_opi_wip_scrap';
INSERT /*+ append */
INTO opi_dbi_wip_scrap_stg (
organization_id,
inventory_item_id,
transaction_date,
scrap_quantity,
scrap_value_b,
uom_code,
source,
planned_item,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT
scrap.organization_id,
scrap.inventory_item_id,
scrap.trx_date,
sum (scrap.mmt_quantity),
sum (scrap.mta_value),
msi.primary_uom_code,
OPI_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 mtl_system_items_b msi,
(SELECT /*+ leading(log) index(mmt, MTL_MATERIAL_TRANSACTIONS_U1) use_nl(log mmt) */
mmt.organization_id,
mmt.inventory_item_id,
trunc (mmt.transaction_date) trx_date,
mmt.primary_quantity mmt_quantity,
-1 * sum (nvl (mta.base_transaction_value, 0)) mta_value
FROM mtl_material_transactions mmt,
mtl_transaction_accounts mta,
wip_entities we,
wip_discrete_jobs wdj,
opi_dbi_run_log_curr log
WHERE log.source = OPI_SOURCE
AND log.etl_id = WIP_SCRAP_ETL
AND mmt.organization_id = log.organization_id
AND mmt.transaction_id >= log.start_txn_id
AND mmt.transaction_id < log.next_start_txn_id
AND mmt.transaction_date >= p_global_start_date -- (date trunc'ed)
AND mmt.transaction_type_id IN (90, 91)
AND mta.transaction_id(+) = mmt.transaction_id
AND nvl (mta.accounting_line_type, WIP_VALUATION_ACCT) =
WIP_VALUATION_ACCT
AND we.wip_entity_id = mmt.transaction_source_id
AND we.entity_type IN (WIP_DISCRETE_JOB,
WIP_REPETITIVE_ASSEMBLY_JOB,
WIP_CLOSED_DISCRETE_JOB,
WIP_FLOW_SCHEDULE_JOB)
AND wdj.wip_entity_id(+) = we.wip_entity_id
AND nvl (wdj.job_type, WIP_DISCRETE_STANDARD_JOB) =
WIP_DISCRETE_STANDARD_JOB
GROUP BY mmt.organization_id,
mmt.inventory_item_id,
trunc (mmt.transaction_date),
mmt.primary_quantity,
mmt.transaction_id) scrap
WHERE msi.organization_id = scrap.organization_id
AND msi.inventory_item_id = scrap.inventory_item_id
GROUP BY
scrap.organization_id,
scrap.inventory_item_id,
scrap.trx_date,
msi.primary_uom_code,
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y');
/* update_wip_scrap_fact_incr
MERGE data from the staging table to the fact table since the fact
table already has some data in it.
The granularity of the staging table will item-org-transaction_date
and implicitly the source, since an org is never discrete and
process at the same time.
The item-org-date key will be unique at the fact level.
THIS FUNCTION WILL NOT COMMIT ANY DATA, SINCE THE WRAPPER IS
TAKING RESPONSIBILITY FOR COMMITTING DATA TO THE FACT TABLE.
Date Author Action
04/29/2003 Dinkar Gupta Wrote procedure
08/24/2004 Dinkar Gupta Added secondary currency support.
*/
PROCEDURE update_wip_scrap_fact_incr (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'update_wip_scrap_fact_incr';
(SELECT /*+ use_nl(stg, conv) */
stg.organization_id,
stg.inventory_item_id,
stg.transaction_date,
sum (stg.scrap_quantity) scrap_qty,
sum (stg.scrap_value_b) scrap_val,
stg.uom_code,
conv.conversion_rate,
conv.sec_conversion_rate,
stg.source,
stg.planned_item,
sysdate creation_date,
sysdate update_date,
s_user_id creator,
s_user_id updator,
s_login_id update_login
FROM opi_dbi_wip_scrap_stg stg,
opi_dbi_wip_scrap_conv_rates conv
WHERE stg.organization_id = conv.organization_id
AND stg.transaction_date = conv.transaction_date
GROUP BY stg.organization_id,
stg.inventory_item_id,
stg.transaction_date,
stg.uom_code,
conv.conversion_rate,
conv.sec_conversion_rate,
stg.source,
stg.planned_item) new
ON
( base.organization_id = new.organization_id
AND base.inventory_item_id = new.inventory_item_id
AND base.transaction_date = new.transaction_date
AND base.source = new.source)
WHEN MATCHED THEN UPDATE
SET base.scrap_value_b = base.scrap_value_b +
new.scrap_val,
base.scrap_quantity = base.scrap_quantity +
new.scrap_qty,
base.last_update_date = new.update_date,
base.last_updated_by = new.updator,
base.last_update_login = new.update_login
WHEN NOT MATCHED THEN INSERT(
organization_id,
inventory_item_id,
transaction_date,
scrap_quantity,
scrap_value_b,
uom_code,
conversion_rate,
sec_conversion_rate,
source,
planned_item,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
VALUES (
new.organization_id,
new.inventory_item_id,
new.transaction_date,
new.scrap_qty,
new.scrap_val,
new.uom_code,
new.conversion_rate,
new.sec_conversion_rate,
new.source,
new.planned_item,
new.creation_date,
new.update_date,
new.creator,
new.updator,
new.update_login);
END update_wip_scrap_fact_incr;