[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO l_installed_flag
FROM FND_PRODUCT_INSTALLATIONS
WHERE application_id = 101;
SELECT application_name
INTO l_gl_name
FROM FND_APPLICATION_TL
WHERE application_id = 101;
SELECT 'Y' INTO l_installed_flag
FROM FND_PRODUCT_INSTALLATIONS
WHERE application_id = 200;
SELECT application_name INTO l_ap_name
FROM FND_APPLICATION_TL
WHERE application_id = 200;
SELECT 'Y' INTO l_installed_flag
FROM FND_PRODUCT_INSTALLATIONS
WHERE application_id = 222;
SELECT application_name INTO l_ar_name
FROM FND_APPLICATION_TL
WHERE application_id = 222;
SELECT report_date_julian INTO l_from_date_id
FROM FII_TIME_DAY
WHERE report_date = l_from_date;
SELECT report_date_julian INTO l_to_date_id
FROM FII_TIME_DAY
WHERE report_date = l_to_date;
DELETE FROM FII_RECONV_RATES
WHERE request_id = l_conc_req_id;
INSERT INTO FII_RECONV_RATES
( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
, PRIMARY_RATE, SECONDARY_RATE)
(SELECT DISTINCT
p_request_id, t.report_date_julian, t.report_date, sob.currency_code
, DECODE( p_primary_currency, sob.currency_code, 1, NULL, NULL
, FII_CURRENCY.GET_RATE( sob.currency_code, p_primary_currency
, t.report_date, p_primary_rate_type))
, DECODE( p_secondary_currency, sob.currency_code, 1, NULL, NULL
, FII_CURRENCY.GET_RATE( sob.currency_code, p_secondary_currency
, t.report_date, p_secondary_rate_type))
FROM FII_TIME_DAY t, GL_LEDGERS_PUBLIC_V sob
WHERE t.report_date_julian BETWEEN p_from_date_id and p_to_date_id
AND EXISTS (SELECT 'This date has GL transaction'
FROM FII_GL_JE_SUMMARY_B gl
WHERE gl.time_id = t.report_date_julian
-- Bug fix 4637659: Added to check PERIOD_TYPE_ID since it is the first column
-- of the changed FII_GL_JE_SUMMARY_B_U1
AND gl.period_type_id = 1
AND gl.ledger_id = sob.ledger_id));
INSERT INTO FII_RECONV_RATES
( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
, PRIMARY_RATE, SECONDARY_RATE)
(SELECT DISTINCT
p_request_id, t.report_date_julian, t.report_date, sob.currency_code
, DECODE( p_primary_currency, sob.currency_code, 1, NULL, NULL
, FII_CURRENCY.GET_RATE( sob.currency_code, p_primary_currency
, t.report_date, p_primary_rate_type))
, DECODE( p_secondary_currency, sob.currency_code, 1, NULL, NULL
, FII_CURRENCY.GET_RATE( sob.currency_code, p_secondary_currency
, t.report_date, p_secondary_rate_type))
FROM FII_TIME_DAY t, GL_LEDGERS_PUBLIC_V sob
WHERE t.report_date_julian BETWEEN p_from_date_id and p_to_date_id
AND EXISTS (SELECT /*+ parallel(ap) */ 'This date has AP transaction'
FROM FII_AP_INV_B ap
WHERE ap.account_date_id = t.report_date_julian
AND ap.ledger_id = sob.ledger_id)
AND NOT EXISTS (SELECT 'This rate has been cached'
FROM FII_RECONV_RATES r
WHERE r.request_id = p_request_id
AND r.rate_date_id = t.report_date_julian
AND r.from_currency = sob.currency_code));
INSERT INTO FII_RECONV_RATES
( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
, PRIMARY_RATE, SECONDARY_RATE)
(SELECT DISTINCT
p_request_id, t.report_date_julian, t.report_date, sob.currency_code
, DECODE( p_primary_currency, sob.currency_code, 1, NULL, NULL
, FII_CURRENCY.GET_RATE( sob.currency_code, p_primary_currency
, t.report_date, p_primary_rate_type))
, DECODE( p_secondary_currency, sob.currency_code, 1, NULL, NULL
, FII_CURRENCY.GET_RATE( sob.currency_code, p_secondary_currency
, t.report_date, p_secondary_rate_type))
FROM FII_TIME_DAY t, GL_LEDGERS_PUBLIC_V sob
WHERE t.report_date_julian BETWEEN p_from_date_id and p_to_date_id
AND EXISTS (SELECT /*+ parallel(ar) */ 'This date has AR transaction'
FROM FII_AR_REVENUE_B ar
WHERE ar.gl_date_id = t.report_date_julian
AND ar.ledger_id = sob.ledger_id)
AND NOT EXISTS (SELECT 'This rate has been cached'
FROM FII_RECONV_RATES r
WHERE r.request_id = p_request_id
AND r.rate_date_id = t.report_date_julian
AND r.from_currency = sob.currency_code));
SELECT count(*) INTO l_missing_rate_count
FROM FII_RECONV_RATES
WHERE request_id = p_request_id
AND (NVL(primary_rate, 1) <= 0 OR NVL(secondary_rate, 1) <= 0);
l_processSQL(1) := 'SELECT company_id, cost_center_id, fin_category_id';
l_process_step := 'BUILD_DAILY_SQL_INSERT';
l_processSQL(1) := 'INSERT INTO FII_RECONV_GL_ROLLUP_GT';
l_process_step := 'BUILD_DAILY_SQL_SELECT';
l_processSQL(6) := '(SELECT /*+ use_hash(g) parallel(g) parallel(r) */';
'Insert daily differences SQL:', l_processSQL, i);
l_process_step := 'BUILD_WEEKLY_SQL_SELECT';
l_processSQL(6) := '(SELECT /*+ parallel(g) parallel(t) use_hash(g) */';
l_process_step := 'BUILD_OTHER_SQL_SELECT';
l_process_step := 'DELETE_SQL_BUFFER';
l_processSQL.delete;
l_process_step := 'BUILD_UPDATE_SQL';
l_processSQL(i) := 'UPDATE /*+ parallel(g) full(g) */ FII_GL_JE_SUMMARY_B g';
' , OTHER_AMOUNT_PRIM, SEC_AMOUNT_G, LAST_UPDATE_DATE';
l_processSQL(i) := ' , LAST_UPDATED_BY, LAST_UPDATE_LOGIN) = ';
l_process_step := 'BUILD_UPDATE_SQL_SELECT';
'(SELECT NVL(g.prim_amount_g, 0) + r.primary_difference';
l_process_step := 'BUILD_UPDATE_SQL_FROM';
l_process_step := 'BUILD_UPDATE_SQL_WHERE1';
l_process_step := 'BUILD_UPDATE_SQL_WHERE2';
' IN (SELECT r2.time_id, r2.chart_of_accounts_id, r2.cost_center_id';
' IN (SELECT r2.time_id, r2.chart_of_accounts_id, r2.cost_center';
l_process_step := 'PRINT_UPDATE_SQL';
'Update GL differences SQL:', l_processSQL, i);
l_process_step := 'OPEN_UPDATE_SQL';
l_process_step := 'PARSE_UPDATE_SQL';
l_process_step := 'BIND_UPDATE_SQL';
l_process_step := 'RUN_UPDATE_SQL';
l_process_step := 'CLOSE_UPDATE_SQL';
l_message := FII_MESSAGE.get_message('FII_RECONV_UPDATED_ROWS', NULL,
'NUM_ROWS', l_processed_rows,
'PROD_NAME', p_product_name);
l_process_step := 'UPDATE_AP_B';
UPDATE /*+ parallel(ap) */ FII_AP_INV_B ap
SET ( PRIM_AMOUNT_G, SEC_AMOUNT_G, LAST_UPDATE_DATE
, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) =
(SELECT DECODE(p_primary_currency, NULL, ap.prim_amount_g
, ROUND((ap.amount_b*r.primary_rate)
/p_primary_mau)*p_primary_mau)
, DECODE(p_secondary_currency, NULL, ap.sec_amount_g
, ROUND((ap.amount_b*r.secondary_rate)
/p_secondary_mau)*p_secondary_mau)
, sysdate, p_user_id, p_user_id
FROM FII_RECONV_RATES r, GL_LEDGERS_PUBLIC_V sob
WHERE r.request_id = p_request_id
AND r.rate_date_id = ap.account_date_id
AND r.from_currency = sob.currency_code
AND sob.ledger_id = ap.ledger_id)
WHERE ap.amount_b <> 0
AND ap.account_date_id BETWEEN p_from_date_id AND p_to_date_id
AND ( (p_primary_currency IS NOT NULL
AND ap.prim_amount_g <>
(SELECT ROUND((ap.amount_b*r2.primary_rate)
/p_primary_mau)*p_primary_mau
FROM FII_RECONV_RATES r2, GL_LEDGERS_PUBLIC_V sob2
WHERE r2.request_id = p_request_id
AND r2.rate_date_id = ap.account_date_id
AND r2.from_currency = sob2.currency_code
AND sob2.ledger_id = ap.ledger_id))
OR (p_secondary_currency IS NOT NULL
AND ap.sec_amount_g <>
(SELECT ROUND((ap.amount_b*r3.secondary_rate)
/p_secondary_mau)*p_secondary_mau
FROM FII_RECONV_RATES r3, GL_LEDGERS_PUBLIC_V sob3
WHERE r3.request_id = p_request_id
AND r3.rate_date_id = ap.account_date_id
AND r3.from_currency = sob3.currency_code
AND sob3.ledger_id = ap.ledger_id)));
l_message := FII_MESSAGE.get_message('FII_RECONV_UPDATED_ROWS', NULL,
'NUM_ROWS', NVL(l_processed_rows, 0),
'PROD_NAME', p_product_name);
l_process_step := 'UPDATE_AR_B';
UPDATE /*+ parallel(ar) */ FII_AR_REVENUE_B ar
SET ( PRIM_AMOUNT_G, SEC_AMOUNT_G, LAST_UPDATE_DATE
, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) =
(SELECT DECODE(p_primary_currency, NULL, ar.prim_amount_g
, ROUND((ar.amount_b*r.primary_rate)
/p_primary_mau)*p_primary_mau)
, DECODE(p_secondary_currency, NULL, ar.sec_amount_g
, ROUND((ar.amount_b*r.secondary_rate)
/p_secondary_mau)*p_secondary_mau)
, sysdate, p_user_id, p_user_id
FROM FII_RECONV_RATES r
WHERE r.request_id = p_request_id
AND r.rate_date_id = ar.gl_date_id
AND r.from_currency = ar.functional_currency
)
WHERE ar.amount_b <> 0
AND ar.gl_date_id BETWEEN p_from_date_id AND p_to_date_id
AND ( (p_primary_currency IS NOT NULL
AND ar.prim_amount_g <>
(SELECT ROUND((ar.amount_b*r2.primary_rate)
/p_primary_mau)*p_primary_mau
FROM FII_RECONV_RATES r2
WHERE r2.request_id = p_request_id
AND r2.rate_date_id = ar.gl_date_id
AND r2.from_currency = ar.functional_currency))
OR (p_secondary_currency IS NOT NULL
AND ar.sec_amount_g <>
(SELECT ROUND((ar.amount_b*r3.secondary_rate)
/p_secondary_mau)*p_secondary_mau
FROM FII_RECONV_RATES r3
WHERE r3.request_id = p_request_id
AND r3.rate_date_id = ar.gl_date_id
AND r3.from_currency = ar.functional_currency)));
l_message := FII_MESSAGE.get_message('FII_RECONV_UPDATED_ROWS', NULL,
'NUM_ROWS', NVL(l_processed_rows, 0),
'PROD_NAME', p_product_name);
SELECT RPAD(sob_curr, 15, ' ')
|| ' ' || RPAD(global_curr, 15, ' ')
|| ' ' || TO_CHAR(rate_date, 'DD-MON-YYYY') line_text
FROM (SELECT from_currency sob_curr
, p_primary_currency global_curr
, rate_date
FROM FII_RECONV_RATES
WHERE p_primary_currency IS NOT NULL
AND request_id = p_request_id
AND primary_rate <= 0
UNION
SELECT from_currency sob_curr
, p_secondary_currency global_curr
, rate_date
FROM FII_RECONV_RATES
WHERE p_secondary_currency IS NOT NULL
AND request_id = p_request_id
AND secondary_rate <= 0) Q
ORDER BY sob_curr, global_curr, rate_date;
SELECT RPAD(from_currency, 15, ' ')
|| ' ' || TO_CHAR(rate_date, 'DD-MON-YYYY')
|| ' ' || LPAD(DECODE(SIGN(primary_rate), +1
, TO_CHAR( ROUND(primary_rate, 15)
, 'FM999999D999999999999999')
, ' '), 22 , ' ')
|| ' ' || LPAD(DECODE(SIGN(secondary_rate), +1
, TO_CHAR( ROUND(secondary_rate, 15)
, 'FM999999D999999999999999')
, ' '), 22, ' ') line_text
FROM FII_RECONV_RATES
WHERE request_id = p_request_id
AND (NVL(primary_rate, -1) > 0 OR NVL(secondary_rate, -1) > 0)
ORDER BY from_currency, rate_date;