[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_login NUMBER;
g_last_updated_by NUMBER;
SELECT 1
FROM opi_dbi_conversion_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,
f_currency_code
FROM (
SELECT DISTINCT
s_global_curr_code curr_code,
s_global_rate_type rate_type,
1 report_order, -- ordering global currency first
mp.organization_code,
decode (conv.conversion_rate,
C_EURO_MISSING_AT_START, C_EURO_START_DATE,
conv.transaction_date) transaction_date,
conv.f_currency_code
FROM opi_dbi_conversion_rates conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_onhand_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_onhand_stg
UNION
SELECT /*+ parallel (opi_dbi_intransit_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_intransit_stg
UNION
SELECT /*+ parallel (opi_dbi_wip_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_stg
UNION
SELECT /*+ parallel (opi_dbi_inv_beg_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_inv_beg_stg
UNION
SELECT /*+ parallel (opi_dbi_onh_qty_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_onh_qty_stg
WHERE transaction_source ='MMT'
UNION
SELECT /*+ parallel (opi_dbi_opm_inv_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_opm_inv_stg) to_conv -- Only change
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
s_secondary_curr_code curr_code,
s_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,
C_EURO_MISSING_AT_START, C_EURO_START_DATE,
conv.transaction_date) transaction_date,
conv.f_currency_code
FROM opi_dbi_conversion_rates conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_onhand_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_onhand_stg
UNION
SELECT /*+ parallel (opi_dbi_intransit_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_intransit_stg
UNION
SELECT /*+ parallel (opi_dbi_wip_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_stg
UNION
SELECT /*+ parallel (opi_dbi_inv_beg_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_inv_beg_stg
UNION
SELECT /*+ parallel (opi_dbi_onh_qty_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_onh_qty_stg
WHERE transaction_source ='MMT'
UNION
SELECT /*+ parallel (opi_dbi_opm_inv_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_opm_inv_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,
f_currency_code;
g_last_update_login := fnd_global.login_id;
g_last_updated_by := fnd_global.user_id;
INSERT /*+ append parallel (opi_dbi_conversion_rates) */
INTO opi_dbi_conversion_rates (
organization_id,
f_currency_code,
transaction_date,
conversion_rate,
sec_conversion_rate,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT /*+ parallel (to_conv) parallel (curr_codes) */
to_conv.organization_id,
curr_codes.currency_code f_currency_code,
to_conv.transaction_date,
decode (curr_codes.currency_code,
s_global_curr_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
to_conv.transaction_date) )
conversion_rate,
decode (s_secondary_curr_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)))
sec_conversion_rate,
sysdate,
sysdate,
g_created_by,
g_last_updated_by,
g_last_update_login
FROM
(SELECT /*+ parallel (opi_dbi_onhand_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_onhand_stg
UNION
SELECT /*+ parallel (opi_dbi_intransit_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_intransit_stg
UNION
SELECT /*+ parallel (opi_dbi_wip_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_wip_stg
UNION
SELECT /*+ parallel (opi_dbi_inv_beg_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_inv_beg_stg
UNION
SELECT /*+ parallel (opi_dbi_onh_qty_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_onh_qty_stg
WHERE transaction_source ='MMT'
UNION
SELECT /*+ parallel (opi_dbi_opm_inv_stg) */
DISTINCT organization_id, transaction_date
FROM opi_dbi_opm_inv_stg
) to_conv,
(SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
parallel (hoi) parallel (gsob)*/
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;
commit; -- due to insert+append
UPDATE /*+ parallel (opi_dbi_conversion_rates) */
opi_dbi_conversion_rates
SET sec_conversion_rate = conversion_rate;
select 1
from mtl_interorg_parameters
where ((TO_ORGANIZATION_ID = x_org_id)
or (FROM_ORGANIZATION_ID = x_org_id))
and NVL(FOB_POINT,-99) in (1,2)
and rownum = 1;*/