Tuesday, September 27, 2011

CURRENCY RATE

     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;
/