CREATE OR REPLACE FUNCTION "CURR_RATE" (x_from_curr varchar2,x_to_curr varchar2,x_conv_date date) return number IS
x_rate number;
x_max_date date;
BEGIN
if x_from_curr<>x_to_curr then
select max(conversion_rate) into x_rate from gl_daily_rates
where from_currency=x_from_curr and to_currency=x_to_curr
and conversion_date=to_date(to_char(x_conv_date,'DD-MON-YYYY'),'DD-MON-YYYY');
if x_rate is null then
select max(conversion_date) into x_max_date from gl_daily_rates
where from_currency=x_from_curr and to_currency=x_to_curr;
select max(conversion_rate) into x_rate from gl_daily_rates
where from_currency=x_from_curr and to_currency=x_to_curr
and conversion_date= to_date(to_char(x_max_date,'DD-MON-YYYY'),'DD-MON-YYYY');
end if;
end if;
return(nvl(x_rate,1));
END CURR_RATE;
/
x_rate number;
x_max_date date;
BEGIN
if x_from_curr<>x_to_curr then
select max(conversion_rate) into x_rate from gl_daily_rates
where from_currency=x_from_curr and to_currency=x_to_curr
and conversion_date=to_date(to_char(x_conv_date,'DD-MON-YYYY'),'DD-MON-YYYY');
if x_rate is null then
select max(conversion_date) into x_max_date from gl_daily_rates
where from_currency=x_from_curr and to_currency=x_to_curr;
select max(conversion_rate) into x_rate from gl_daily_rates
where from_currency=x_from_curr and to_currency=x_to_curr
and conversion_date= to_date(to_char(x_max_date,'DD-MON-YYYY'),'DD-MON-YYYY');
end if;
end if;
return(nvl(x_rate,1));
END CURR_RATE;
/
thank you for the post. It is really help full. click here
ReplyDelete