[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
USING (SELECT txn_currency,
exchange_date,
FII_CURRENCY.get_rate(txn_currency,
g_prim_currency,
exchange_date,
l_rate_type) prim_conversion_rate,
FII_CURRENCY.get_rate(txn_currency,
g_sec_currency,
exchange_date,
l_sec_rate_type) sec_conversion_rate,
FII_CURRENCY.get_rate(txn_currency,
func_currency_code,
exchange_date,
l_func_rate_type) func_conversion_rate,
func_currency_code,
org_id
FROM
(SELECT /*+ no_merge parallel(qhd) use_hash(qhd) */ distinct
qhd.currency_code txn_currency,
trunc (qhd.last_update_date) exchange_date,
fcur.currency_code func_currency_code,
op.organization_id org_id
FROM hr_organization_information op,
gl_sets_of_books fcur,
aso_quote_headers_all qhd
WHERE op.org_information3 = fcur.set_of_books_id(+)
AND qhd.org_id = op.organization_id(+)
AND op.org_information_context(+) = 'Operating Unit Information'
AND qhd.last_update_date between p_from_date and p_to_date
)) trans
ON
( RATES.TXN_CURRENCY = trans.TXN_CURRENCY and
RATES.EXCHANGE_DATE = trans.EXCHANGE_DATE and
RATES.ORG_ID = Trans.ORG_ID )
WHEN MATCHED THEN
UPDATE
SET prim_conversion_rate = trans.prim_conversion_rate,
sec_conversion_rate = trans.sec_conversion_rate,
func_conversion_rate = trans.func_conversion_rate,
func_currency_code = trans.func_currency_code
WHEN NOT MATCHED THEN
INSERT
(rates.txn_currency,
rates.exchange_date,
rates.prim_conversion_rate,
rates.sec_conversion_rate,
rates.func_conversion_rate,
rates.func_currency_code,
rates.org_id
) VALUES(
trans.txn_currency,
trans.exchange_date,
trans.prim_conversion_rate,
trans.sec_conversion_rate,
trans.func_conversion_rate,
trans.func_currency_code,
trans.org_id
);
USING (SELECT txn_currency,
exchange_date,
FII_CURRENCY.get_rate(txn_currency,
g_prim_currency,
exchange_date,
l_rate_type) prim_conversion_rate,
FII_CURRENCY.get_rate(txn_currency,
g_sec_currency,
exchange_date,
l_sec_rate_type) sec_conversion_rate,
FII_CURRENCY.get_rate(txn_currency,
func_currency_code,
exchange_date,
l_func_rate_type) func_conversion_rate,
func_currency_code,
org_id
FROM
(SELECT distinct
qhd.currency_code txn_currency,
trunc (qhd.last_update_date) exchange_date,
fcur.currency_code func_currency_code,
op.organization_id org_id
FROM hr_organization_information op,
gl_sets_of_books fcur,
aso_quote_headers_all qhd
WHERE op.org_information3 = fcur.set_of_books_id(+)
AND qhd.org_id = op.organization_id(+)
AND op.org_information_context(+) = 'Operating Unit Information'
AND qhd.last_update_date between p_from_date and p_to_date
)) trans
ON
( RATES.TXN_CURRENCY = trans.TXN_CURRENCY and
RATES.EXCHANGE_DATE = trans.EXCHANGE_DATE and
RATES.ORG_ID = Trans.ORG_ID )
WHEN MATCHED THEN
UPDATE
SET prim_conversion_rate = trans.prim_conversion_rate,
sec_conversion_rate = trans.sec_conversion_rate,
func_conversion_rate = trans.func_conversion_rate,
func_currency_code = trans.func_currency_code
WHEN NOT MATCHED THEN
INSERT
(rates.txn_currency,
rates.exchange_date,
rates.prim_conversion_rate,
rates.sec_conversion_rate,
rates.func_conversion_rate,
rates.func_currency_code,
rates.org_id
) VALUES(
trans.txn_currency,
trans.exchange_date,
trans.prim_conversion_rate,
trans.sec_conversion_rate,
trans.func_conversion_rate,
trans.func_currency_code,
trans.org_id
);
SELECT txn_currency from_currency,
g_prim_currency to_currency,
exchange_date,
prim_conversion_rate
FROM ASO_BI_CURRENCY_RATES
WHERE (prim_conversion_rate < 0
OR prim_conversion_rate IS NULL)
ORDER BY exchange_date,txn_currency ;
SELECT txn_currency from_currency,
g_prim_currency to_prim_currency,
prim_conversion_rate,
g_sec_currency to_sec_currency,
sec_conversion_rate,
exchange_date
FROM ASO_BI_CURRENCY_RATES
WHERE( (sec_conversion_rate < 0 OR sec_conversion_rate IS NULL)
OR (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL))
ORDER BY exchange_date,txn_currency;
SELECT COUNT(*) INTO l_cnt_miss_rate
FROM ASO_BI_CURRENCY_RATES
WHERE (prim_conversion_rate < 0
OR prim_conversion_rate IS NULL) and rownum < 2;
SELECT COUNT(*) INTO l_cnt_miss_rate
FROM ASO_BI_CURRENCY_RATES
WHERE ((sec_conversion_rate < 0 OR sec_conversion_rate IS NULL)
OR (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)) and rownum < 2;
IS SELECT txn_currency from_currency,
g_prim_currency to_prim_currency,
prim_conversion_rate to_prim_rate,
func_currency_code to_func_currency,
func_conversion_rate to_func_rate,
exchange_date
FROM aso_bi_currency_rates
WHERE (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR
(func_conversion_rate < 0 OR func_conversion_rate IS NULL)
ORDER BY exchange_date,txn_currency;
IS SELECT txn_currency from_currency,
g_prim_currency to_prim_currency,
prim_conversion_rate to_prim_rate,
func_currency_code to_func_currency,
func_conversion_rate to_func_rate,
g_sec_currency to_sec_currency,
sec_conversion_rate to_sec_rate,
exchange_date
FROM ASO_BI_CURRENCY_RATES
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (func_conversion_rate < 0 OR func_conversion_rate IS NULL)
OR (sec_conversion_rate < 0 OR sec_conversion_rate IS NULL))
ORDER BY exchange_date,txn_currency;
SELECT COUNT(*) INTO l_cnt_miss_rate FROM ASO_BI_CURRENCY_RATES
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (func_conversion_rate<0 OR func_conversion_rate IS NULL)) and rownum < 2;
SELECT COUNT(*) INTO l_cnt_miss_rate FROM ASO_BI_CURRENCY_RATES
WHERE (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (func_conversion_rate<0 OR func_conversion_rate IS NULL)
OR (sec_conversion_rate <0 OR sec_conversion_rate IS NULL) and rownum < 2;
SELECT NVL(SUM(DECODE(status,'UNASSIGNED',1,0)),0),
NVL(SUM(DECODE(status,'COMPLETED',1,0)),0),
NVL(SUM(DECODE(status,'IN_PROCESS',1,0)),0),
NVL(SUM(DECODE(status,'FAILED',1,0)),0),
COUNT(*)
INTO l_unassigned_cnt,
l_completed_cnt,
l_inprocess_cnt,
l_failed_cnt,
l_total_cnt
FROM ASO_BI_QUOTE_FACT_JOBS;
BIS_COLLECTION_UTILITIES.deleteLogForObject('ASO_BI_POPULATE_FACTS');
SELECT COUNT(*) INTO l_curr_count
FROM ASO_BI_CURRENCY_RATES
WHERE rownum < 2;
BIS_COLLECTION_UTILITIES.deleteLogForObject('ASO_BI_LINE_FACTS');
SELECT COUNT(*) INTO l_curr_count
FROM ASO_BI_CURRENCY_RATES
WHERE rownum < 2;