[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_wip_scrap_fact_init (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER);
update_wip_scrap_fact_init (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 - Wip 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 scrap transactions and increases
on returns 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 scrap transactions 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_init_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_init_opi_wip_scrap';
INSERT /*+ append parallel(opi_dbi_wip_scrap_stg) */
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 /*+ use_hash(msi) use_hash(scrap) parallel(msi) parallel(scrap) */
scrap.organization_id,
scrap.inventory_item_id,
scrap.trx_date,
sum (scrap.mmt_quantity),
sum (scrap.mta_value),
msi.primary_uom_code,
OPI_SOURCE, -- Scrap 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 /*+ use_hash(mmt) use_hash(mta) use_hash(we) use_hash(wdj) use_hash(log)
parallel(mmt) parallel(mta) parallel(we) parallel(wdj) parallel(log) */
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_init
Merge data from the staging table to the fact table. For the
initial load, we are guaranteed that the fact table is empty,
so the update of the fact table is actually a simple insert.
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.
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_init (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'update_wip_scrap_fact_init';
INSERT /*+ append parallel (opi_dbi_wip_scrap_f) */
INTO opi_dbi_wip_scrap_f (
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)
SELECT /*+ use_hash(stg conv) parallel(stg) parallel(conv) */
stg.organization_id,
stg.inventory_item_id,
stg.transaction_date,
sum (stg.scrap_quantity),
sum (stg.scrap_value_b),
stg.uom_code,
conv.conversion_rate,
conv.sec_conversion_rate,
stg.source,
stg.planned_item,
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id
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;
END update_wip_scrap_fact_init;
do not call the secondary currency API. Instead update the secondary
rates from the primary.
If the secondary currency has not been set up, set the conversion rate
to null.
If any primary conversion rates are missing, throw an exception.
If any secondary currency rates are missing (after the secondary
currency has been set up) throw an exception.
Need to commit data here due to insert+append.
Date Author Action
04/29/2003 Dinkar Gupta Wrote procedure
06/03/2003 Dinkar Gupta Added OPI schema parameter
*/
PROCEDURE compute_wip_scrap_conv_rates (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_opi_schema IN VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'compute_wip_scrap_conv_rates';
SELECT 1
FROM opi_dbi_wip_scrap_conv_rates
WHERE ( nvl (conversion_rate, -999) < 0
OR nvl (sec_conversion_rate, 999) < 0)
AND rownum < 2;
SELECT DISTINCT
report_order,
curr_code,
rate_type,
transaction_date,
func_currency_code
FROM (
SELECT DISTINCT
p_global_currency_code curr_code,
p_global_rate_type rate_type,
1 report_order, -- ordering global currency first
mp.organization_code,
decode (conv.conversion_rate,
EURO_MISSING_AT_START, EURO_START_DATE,
conv.transaction_date) transaction_date,
conv.base_currency_code func_currency_code
FROM opi_dbi_wip_scrap_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ index_ffs(opi_dbi_wip_scrap_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_scrap_stg) to_conv
WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.transaction_date
AND conv.organization_id (+) = to_conv.organization_id
UNION ALL
SELECT DISTINCT
p_secondary_currency_code curr_code,
p_secondary_rate_type rate_type,
decode (p_pri_sec_curr_same,
1, 1,
2) report_order, --ordering secondary currency next
mp.organization_code,
decode (conv.sec_conversion_rate,
EURO_MISSING_AT_START, EURO_START_DATE,
conv.transaction_date) transaction_date_date,
conv.base_currency_code func_currency_code
FROM opi_dbi_wip_scrap_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ index_ffs(opi_dbi_wip_scrap_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_scrap_stg) to_conv
WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.transaction_date
AND conv.organization_id (+) = to_conv.organization_id)
ORDER BY
report_order ASC,
transaction_date,
func_currency_code;
INSERT /*+ append */
INTO opi_dbi_wip_scrap_conv_rates rates (
organization_id,
base_currency_code,
transaction_date,
conversion_rate,
sec_conversion_rate,
last_update_date,
creation_date,
created_by,
last_updated_by,
last_update_login)
SELECT
to_conv.organization_id,
curr_codes.currency_code,
to_conv.transaction_date,
decode (curr_codes.currency_code,
l_global_currency_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
to_conv.transaction_date) ),
decode (l_secondary_currency_code,
NULL, NULL,
curr_codes.currency_code, 1,
decode (l_pri_sec_curr_same,
1, C_PRI_SEC_CURR_SAME_MARKER,
fii_currency.get_global_rate_secondary (
curr_codes.currency_code,
to_conv.transaction_date))),
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id
FROM
(SELECT /*+ index_ffs(opi_dbi_wip_scrap_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_scrap_stg) to_conv,
(SELECT
DISTINCT hoi.organization_id, gsob.currency_code
FROM hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id))
curr_codes
WHERE curr_codes.organization_id = to_conv.organization_id;
UPDATE /*+ parallel (opi_dbi_wip_scrap_conv_rates) */
opi_dbi_wip_scrap_conv_rates
SET sec_conversion_rate = conversion_rate;