-->
Daily maintaining of currency rates can be boring task and things can go worse if you operate in multi time zones.
I provided simple example script populating GL interface table with daily rates for all enabled currencies, from Russian central bank.
It can be esily tailored to you needs by chanching part responsible for parsing XML stuff provided by central bank of you country. If you in Europe for instanse, go here http://www.ecb.int/stats/exchange/eurofxref/html/index.en.html.
I used this script on the number of my projects as basis to сustom concurent programs which scheduled to run on daily basis whithout any human involvment.
declare
l_date date;
l_rate number;
x_rate number;
l_currency varchar2(20);
BEGIN
-- cycle all enabled currencies except funcional, SATAT and other special currencies not neded xchange rates mantained
for i in (select currency_code from FND_CURRENCIES_VL where enabled_flag='Y' and currency_code not in ('STAT', 'RUB', 'XDR', 'UEG', 'UEE', 'UED')) loop
l_currency := i.currency_code;
-- load xchange rate from central bank site
SELECT to_number(VALUE(p).EXTRACT('Valute/Value/text()').getStringVal(), '99999999990d9999', 'NLS_NUMERIC_CHARACTERS = '',.''') / VALUE(p) .EXTRACT('Valute/Nominal/text()').getNumberVal()
INTO l_rate
FROM TABLE(XMLSEQUENCE(HTTPURITYPE('http://212.40.192.49/scripts/xml_daily.asp?date_req='
|| TO_CHAR(l_date, 'dd.mm.yyyy')) .getxml().EXTRACT('/ValCurs/Valute'))) p
WHERE VALUE(p).EXTRACT('Valute/CharCode/text()') .getStringVal() = l_currency;
begin
-- upload only rates not already been uploaded
select conversion_rate into x_rate from gl_daily_rates_interface
where from_currency=l_currency
and to_currency='RUB' -- functional currency here
and from_conversion_date=l_date
and user_conversion_type='ЦБРФ'; -- conversion type of you choice
exception
WHEN no_data_found THEN
INSERT
INTO gl_daily_rates_interface
(
from_currency,
to_currency,
from_conversion_date,
to_conversion_date,
user_conversion_type,
conversion_rate,
mode_flag
)
VALUES
(
l_currency,
'RUB', -- functional currency here
l_date,
l_date,
'ЦБРФ', -- conversion type of you choice
l_rate,
'I'
);
end;
end loop;
END;
I provided simple example script populating GL interface table with daily rates for all enabled currencies, from Russian central bank.
It can be esily tailored to you needs by chanching part responsible for parsing XML stuff provided by central bank of you country. If you in Europe for instanse, go here http://www.ecb.int/stats/exchange/eurofxref/html/index.en.html.
I used this script on the number of my projects as basis to сustom concurent programs which scheduled to run on daily basis whithout any human involvment.
declare
l_date date;
l_rate number;
x_rate number;
l_currency varchar2(20);
BEGIN
-- cycle all enabled currencies except funcional, SATAT and other special currencies not neded xchange rates mantained
for i in (select currency_code from FND_CURRENCIES_VL where enabled_flag='Y' and currency_code not in ('STAT', 'RUB', 'XDR', 'UEG', 'UEE', 'UED')) loop
l_currency := i.currency_code;
-- load xchange rate from central bank site
SELECT to_number(VALUE(p).EXTRACT('Valute/Value/text()').getStringVal(), '99999999990d9999', 'NLS_NUMERIC_CHARACTERS = '',.''') / VALUE(p) .EXTRACT('Valute/Nominal/text()').getNumberVal()
INTO l_rate
FROM TABLE(XMLSEQUENCE(HTTPURITYPE('http://212.40.192.49/scripts/xml_daily.asp?date_req='
|| TO_CHAR(l_date, 'dd.mm.yyyy')) .getxml().EXTRACT('/ValCurs/Valute'))) p
WHERE VALUE(p).EXTRACT('Valute/CharCode/text()') .getStringVal() = l_currency;
begin
-- upload only rates not already been uploaded
select conversion_rate into x_rate from gl_daily_rates_interface
where from_currency=l_currency
and to_currency='RUB' -- functional currency here
and from_conversion_date=l_date
and user_conversion_type='ЦБРФ'; -- conversion type of you choice
exception
WHEN no_data_found THEN
INSERT
INTO gl_daily_rates_interface
(
from_currency,
to_currency,
from_conversion_date,
to_conversion_date,
user_conversion_type,
conversion_rate,
mode_flag
)
VALUES
(
l_currency,
'RUB', -- functional currency here
l_date,
l_date,
'ЦБРФ', -- conversion type of you choice
l_rate,
'I'
);
end;
end loop;
END;