[Home] [Help]
687: l_process_step := 'CLEANUP_CACHED_RATES';
688: -- LOG: FII_RECONV_EXIT_PROCESS_STEP || time stamp
689: FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
690: 'FII_RECONV_CLEANUP_STEP', NULL));
691: -- Delete cached rates from FII_RECONV_RATES for this request and commit
692: DELETE FROM FII_RECONV_RATES
693: WHERE request_id = l_conc_req_id;
694: COMMIT;
695:
688: -- LOG: FII_RECONV_EXIT_PROCESS_STEP || time stamp
689: FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
690: 'FII_RECONV_CLEANUP_STEP', NULL));
691: -- Delete cached rates from FII_RECONV_RATES for this request and commit
692: DELETE FROM FII_RECONV_RATES
693: WHERE request_id = l_conc_req_id;
694: COMMIT;
695:
696: -- ===================
782: -- cache_rates()
783: --
784: -- DESCRIPTION:
785: -- It will insert global conversion rates (from set of books currencies to
786: -- global currencies) into FII_RECONV_RATES for the specified date range.
787: --
788: -- The return value can be:
789: -- 'C' - all the required rates are cached
790: -- 'M' - there are missing rates
825: FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
826: 'FII_RECONV_CACHE_RATE_STEP', NULL
827: , 'PROD_NAME', p_gl_name));
828:
829: -- Insert rates to FII_RECONV_RATES based on the transactions of
830: -- FII_GL_JE_SUMMARY_B within the passed date range
831: INSERT INTO FII_RECONV_RATES
832: ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
833: , PRIMARY_RATE, SECONDARY_RATE)
827: , 'PROD_NAME', p_gl_name));
828:
829: -- Insert rates to FII_RECONV_RATES based on the transactions of
830: -- FII_GL_JE_SUMMARY_B within the passed date range
831: INSERT INTO FII_RECONV_RATES
832: ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
833: , PRIMARY_RATE, SECONDARY_RATE)
834: (SELECT DISTINCT
835: p_request_id, t.report_date_julian, t.report_date, sob.currency_code
872: FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
873: 'FII_RECONV_CACHE_RATE_STEP', NULL
874: , 'PROD_NAME', p_ap_name));
875:
876: -- Insert rates to FII_RECONV_RATES based on the transactions of
877: -- FII_AP_INV_B within the passed date range and they are not yet cached
878: INSERT INTO FII_RECONV_RATES
879: ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
880: , PRIMARY_RATE, SECONDARY_RATE)
874: , 'PROD_NAME', p_ap_name));
875:
876: -- Insert rates to FII_RECONV_RATES based on the transactions of
877: -- FII_AP_INV_B within the passed date range and they are not yet cached
878: INSERT INTO FII_RECONV_RATES
879: ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
880: , PRIMARY_RATE, SECONDARY_RATE)
881: (SELECT DISTINCT
882: p_request_id, t.report_date_julian, t.report_date, sob.currency_code
892: FROM FII_AP_INV_B ap
893: WHERE ap.account_date_id = t.report_date_julian
894: AND ap.ledger_id = sob.ledger_id)
895: AND NOT EXISTS (SELECT 'This rate has been cached'
896: FROM FII_RECONV_RATES r
897: WHERE r.request_id = p_request_id
898: AND r.rate_date_id = t.report_date_julian
899: AND r.from_currency = sob.currency_code));
900:
921: FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
922: 'FII_RECONV_CACHE_RATE_STEP', NULL
923: , 'PROD_NAME', p_ar_name));
924:
925: -- Insert rates to FII_RECONV_RATES based on the transactions of
926: -- FII_AR_REVENUE_B within the passed date range and they are not yet cached
927: INSERT INTO FII_RECONV_RATES
928: ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
929: , PRIMARY_RATE, SECONDARY_RATE)
923: , 'PROD_NAME', p_ar_name));
924:
925: -- Insert rates to FII_RECONV_RATES based on the transactions of
926: -- FII_AR_REVENUE_B within the passed date range and they are not yet cached
927: INSERT INTO FII_RECONV_RATES
928: ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
929: , PRIMARY_RATE, SECONDARY_RATE)
930: (SELECT DISTINCT
931: p_request_id, t.report_date_julian, t.report_date, sob.currency_code
941: FROM FII_AR_REVENUE_B ar
942: WHERE ar.gl_date_id = t.report_date_julian
943: AND ar.ledger_id = sob.ledger_id)
944: AND NOT EXISTS (SELECT 'This rate has been cached'
945: FROM FII_RECONV_RATES r
946: WHERE r.request_id = p_request_id
947: AND r.rate_date_id = t.report_date_julian
948: AND r.from_currency = sob.currency_code));
949:
967: -- Check if there are any missing/invalid rates (i.e. rate <= 0)
968: -- only when there are cached rates
969: l_process_step := 'CHECK_MISSING_RATES';
970: SELECT count(*) INTO l_missing_rate_count
971: FROM FII_RECONV_RATES
972: WHERE request_id = p_request_id
973: AND (NVL(primary_rate, 1) <= 0 OR NVL(secondary_rate, 1) <= 0);
974: FII_UTIL.debug_line(' ');
975: FII_UTIL.debug_line(TO_CHAR(l_missing_rate_count)
1189:
1190: -- FROM clause
1191: l_process_step := 'BUILD_DAILY_SQL_FROM';
1192: i := i + 1;
1193: l_processSQL(i) := ' FROM FII_GL_JE_SUMMARY_B g, FII_RECONV_RATES r';
1194:
1195: -- WHERE clauses
1196: l_process_step := 'BUILD_DAILY_SQL_WHERE';
1197: i := i + 1;
1901: , DECODE(p_secondary_currency, NULL, ap.sec_amount_g
1902: , ROUND((ap.amount_b*r.secondary_rate)
1903: /p_secondary_mau)*p_secondary_mau)
1904: , sysdate, p_user_id, p_user_id
1905: FROM FII_RECONV_RATES r, GL_LEDGERS_PUBLIC_V sob
1906: WHERE r.request_id = p_request_id
1907: AND r.rate_date_id = ap.account_date_id
1908: AND r.from_currency = sob.currency_code
1909: AND sob.ledger_id = ap.ledger_id)
1912: AND ( (p_primary_currency IS NOT NULL
1913: AND ap.prim_amount_g <>
1914: (SELECT ROUND((ap.amount_b*r2.primary_rate)
1915: /p_primary_mau)*p_primary_mau
1916: FROM FII_RECONV_RATES r2, GL_LEDGERS_PUBLIC_V sob2
1917: WHERE r2.request_id = p_request_id
1918: AND r2.rate_date_id = ap.account_date_id
1919: AND r2.from_currency = sob2.currency_code
1920: AND sob2.ledger_id = ap.ledger_id))
1921: OR (p_secondary_currency IS NOT NULL
1922: AND ap.sec_amount_g <>
1923: (SELECT ROUND((ap.amount_b*r3.secondary_rate)
1924: /p_secondary_mau)*p_secondary_mau
1925: FROM FII_RECONV_RATES r3, GL_LEDGERS_PUBLIC_V sob3
1926: WHERE r3.request_id = p_request_id
1927: AND r3.rate_date_id = ap.account_date_id
1928: AND r3.from_currency = sob3.currency_code
1929: AND sob3.ledger_id = ap.ledger_id)));
2029: , DECODE(p_secondary_currency, NULL, ar.sec_amount_g
2030: , ROUND((ar.amount_b*r.secondary_rate)
2031: /p_secondary_mau)*p_secondary_mau)
2032: , sysdate, p_user_id, p_user_id
2033: FROM FII_RECONV_RATES r
2034: WHERE r.request_id = p_request_id
2035: AND r.rate_date_id = ar.gl_date_id
2036: AND r.from_currency = ar.functional_currency
2037: )
2040: AND ( (p_primary_currency IS NOT NULL
2041: AND ar.prim_amount_g <>
2042: (SELECT ROUND((ar.amount_b*r2.primary_rate)
2043: /p_primary_mau)*p_primary_mau
2044: FROM FII_RECONV_RATES r2
2045: WHERE r2.request_id = p_request_id
2046: AND r2.rate_date_id = ar.gl_date_id
2047: AND r2.from_currency = ar.functional_currency))
2048: OR (p_secondary_currency IS NOT NULL
2048: OR (p_secondary_currency IS NOT NULL
2049: AND ar.sec_amount_g <>
2050: (SELECT ROUND((ar.amount_b*r3.secondary_rate)
2051: /p_secondary_mau)*p_secondary_mau
2052: FROM FII_RECONV_RATES r3
2053: WHERE r3.request_id = p_request_id
2054: AND r3.rate_date_id = ar.gl_date_id
2055: AND r3.from_currency = ar.functional_currency)));
2056: l_processed_rows := SQL%ROWCOUNT;
2126: || ' ' || TO_CHAR(rate_date, 'DD-MON-YYYY') line_text
2127: FROM (SELECT from_currency sob_curr
2128: , p_primary_currency global_curr
2129: , rate_date
2130: FROM FII_RECONV_RATES
2131: WHERE p_primary_currency IS NOT NULL
2132: AND request_id = p_request_id
2133: AND primary_rate <= 0
2134: UNION
2134: UNION
2135: SELECT from_currency sob_curr
2136: , p_secondary_currency global_curr
2137: , rate_date
2138: FROM FII_RECONV_RATES
2139: WHERE p_secondary_currency IS NOT NULL
2140: AND request_id = p_request_id
2141: AND secondary_rate <= 0) Q
2142: ORDER BY sob_curr, global_curr, rate_date;
2151: || ' ' || LPAD(DECODE(SIGN(secondary_rate), +1
2152: , TO_CHAR( ROUND(secondary_rate, 15)
2153: , 'FM999999D999999999999999')
2154: , ' '), 22, ' ') line_text
2155: FROM FII_RECONV_RATES
2156: WHERE request_id = p_request_id
2157: AND (NVL(primary_rate, -1) > 0 OR NVL(secondary_rate, -1) > 0)
2158: ORDER BY from_currency, rate_date;
2159: