[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_wip_comp_fact_incr (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER);
update_wip_comp_fact_incr (errbuf, retcode);
current log table have been updated successfully using the Common Module
API etl_report_success.
WIP Completions ETL needs to extract two types of transactions from
MMT:
44 - WIP completion transaction increases the quantity/value
of WIP completions
17 - Assembly return transaction decreases the quantity/value of
of WIP completions.
The WIP valuation account has an accounting line type of 7 in MTA, but
the WIP valuation account decreases on WIP completions and increases
on assembly returns. Thus for every transaction, the corresponding
value we pick is -1 * (sum of all accouting line type 7) because this
ETL must report increases completion value on completions and decreased
value on returns.
Parameters:
p_global_start_date - global start date for DBI collection.
Expect this to be trunc'ed
Date Author Action
04/23/2003 Dinkar Gupta Wrote procedure
*/
PROCEDURE collect_incr_opi_wip_comp (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_comp';
INSERT /*+ append */
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
compl.organization_id,
compl.inventory_item_id,
compl.trx_date,
sum (compl.mmt_quantity),
sum (compl.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) use_nl(log mmt) index(log, OPI_DBI_RUN_LOG_CURR_N1) index(mmt, mtl_material_transactions_u1) */
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_COMPLETION_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 (44, 17)
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) compl
WHERE msi.organization_id = compl.organization_id
AND msi.inventory_item_id = compl.inventory_item_id
GROUP BY
compl.organization_id,
compl.inventory_item_id,
compl.trx_date,
msi.primary_uom_code,
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y');
/* update_wip_comp_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/23/2003 Dinkar Gupta Wrote procedure
08/25/2004 Dinkar Gupta Secondary Currency Support
*/
PROCEDURE update_wip_comp_fact_incr (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'update_wip_comp_fact_incr';
(SELECT /*+ use_nl(stg, conv) */
stg.organization_id,
stg.inventory_item_id,
stg.transaction_date,
sum (stg.completion_quantity) completion_qty,
sum (stg.completion_value_b) completion_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_comp_stg stg,
opi_dbi_wip_comp_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.completion_value_b = base.completion_value_b +
new.completion_val,
base.completion_quantity = base.completion_quantity +
new.completion_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,
completion_quantity,
completion_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.completion_qty,
new.completion_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_comp_fact_incr;