[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_wip_comp_fact_init (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER);
update_wip_comp_fact_init (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_init_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_init_opi_wip_comp';
INSERT /*+ append parallel(opi_dbi_wip_comp_stg) */
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 /*+ use_hash(msi) use_hash(compl) parallel(msi) parallel(compl) */
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 /*+ 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_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_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/23/2003 Dinkar Gupta Wrote procedure
*/
PROCEDURE update_wip_comp_fact_init (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'update_wip_comp_fact_init';
INSERT /*+ append parallel(opi_dbi_wip_comp_f) */
INTO opi_dbi_wip_comp_f (
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)
SELECT /*+ use_hash(stg conv) parallel(stg) parallel(conv) */
stg.organization_id,
stg.inventory_item_id,
stg.transaction_date,
sum (stg.completion_quantity),
sum (stg.completion_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_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;
END update_wip_comp_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/23/2003 Dinkar Gupta Wrote procedure
04/28/2003 Dinkar Gupta In the check for missing currency
rates, actually cross checking with
all org-date pairs in the staging
table, in case the functional
currency code is missing for some org.
06/03/2003 Dinkar Gupta Added OPI schema as parameter
08/25/2004 Dinkar Gupta Modified to provide secondary
currency support.
*/
PROCEDURE compute_wip_comp_conv_rates (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_opi_schema IN VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'compute_wip_comp_conv_rates';
SELECT 1
FROM opi_dbi_wip_comp_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_comp_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ index_ffs(opi_dbi_wip_comp_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_comp_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_comp_conv_rates conv,
mtl_parameters mp,
(SELECT /*+ index_ffs(opi_dbi_wip_comp_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_comp_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_comp_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_comp_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_comp_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_comp_conv_rates) */
opi_dbi_wip_comp_conv_rates
SET sec_conversion_rate = conversion_rate;