FUNCTION Get_rate(from_currency_code IN VARCHAR2, 
                  to_currency_code   IN VARCHAR2, 
                  conversion_type    IN VARCHAR2, 
                  conversion_date    IN DATE) 
RETURN NUMBER 
IS 
  v_rate NUMBER; 
  CURSOR c1 IS 
    SELECT conversion_rate 
    FROM   gl_daily_rates 
    WHERE  from_currency = from_currency_code 
           AND to_currency = to_currency_code 
           AND conversion_type = conversion_type 
           AND conversion_date = conversion_date; 
BEGIN 
    dbms_output.Put_line(from_currency_code ||to_currency_code ||conversion_type ||To_char(conversion_date, 'DD-MON-YYYY')); 
  
    FOR c1rec IN c1 LOOP 
        dbms_output.Put_line(c1rec.conversion_rate); 
        v_conversion_rate := c1rec.conversion_rate; 
    END LOOP; 

    RETURN v_rate ; 
EXCEPTION 
  WHEN OTHERS THEN 
             dbms_output.Put_line(SQLERRM); 

             v_rate := 1; 

             RETURN v_rate ; 
END Get_rate; 

select * from gl_daily_rates
where from_currency = 'RMB' 
 and conversion_type = 'Corporate' 
and conversion_date = '13-AUG-14' 
and to_currency = 'USD';

output
------
0.16344

select Pkg.Get_rate('RMB','USD','Corporate' , '13-AUG-14'  ) from dual  
output
------
0.16258  

SQL> ed
Wrote file afiedt.buf

  1  create or replace  FUNCTION Get_rate(from_currency_code IN VARCHAR2, to_currency_code
  2                                        conversion_type IN VARCHAR2,conversi
  3  v_rate NUMBER;
  4  CURSOR c1 IS
  5    SELECT conversion_rate
  7     FROM GL_DAILY_RATES
  8     WHERE from_currency     = from_currency_code
  9     AND to_currency     = to_currency_code
 10     AND conversion_type     = conversion_type
 11    AND conversion_date     = conversion_date;
 12  BEGIN
 13   dbms_output.put_line(from_currency_code ||to_currency_code||conversion_type||TO_CHAR(conversio
 14  FOR c1rec IN c1 LOOP
 15   dbms_output.put_line(c1rec.conversion_rate);
 16   v_rate := c1rec.conversion_rate;
 17  END LOOP;
 18   RETURN v_rate ;
 19   EXCEPTION
 20     WHEN OTHERS THEN
 21    dbms_output.put_line(SQLERRM);
 22     v_rate := 1;
 23      RETURN v_rate ;
 24* END   Get_rate;
 25  /

Function created.


SQL> select CONVERSION_RATE from gl_daily_rates
  2  where from_currency = 'RMB' 
  3   and conversion_type = 'Corporate' 
  4  and conversion_date = '13-AUG-14' 
  5  and to_currency = 'USD';

CONVERSION_RATE
---------------
         .16344

 AND conversion_type     = conversion_type

Select * from your_table where 1 = 1

16   v_rate := c1rec.conversion_rate;
 17  END LOOP;
 18   RETURN v_rate ;

Create or replace 
FUNCTION Get_rate(from_currency_code IN VARCHAR2, 
                  to_currency_code   IN VARCHAR2, 
                  p_conversion_type    IN VARCHAR2, 
                  p_conversion_date    IN DATE) 
RETURN NUMBER 
IS 
  v_rate NUMBER; 
  CURSOR c1 IS 
    SELECT conversion_rate 
    FROM   gl_daily_rates 
    WHERE  from_currency = from_currency_code 
           AND to_currency = to_currency_code 
           AND conversion_type = p_conversion_type 
           AND conversion_date = p_conversion_date; 
BEGIN 
    dbms_output.Put_line(from_currency_code ||to_currency_code ||p_conversion_type ||To_char(p_conversion_date, 'DD-MON-YYYY')); 
    FOR c1rec IN c1 LOOP 
        dbms_output.Put_line(c1rec.conversion_rate); 
        v_rate := c1rec.conversion_rate; 
    END LOOP; 
    RETURN v_rate ; 
EXCEPTION 
  WHEN OTHERS THEN 
             dbms_output.Put_line(SQLERRM); 
             v_rate := 1; 
             RETURN v_rate ; 
END Get_rate; 

select Get_rate('RMB','USD','Corporate' , '13-AUG-14'  )  from dual  

--conversion_type IN VARCHAR2, 
--conversion_date IN DATE      
p_conversion_type IN VARCHAR2, 
p_conversion_date IN DATE      

And all other is same

Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION Get_rate (from_currency_code   IN VARCHAR2,
  2                                       to_currency_code     IN VARCHAR2,
  3                                       p_conversion_type    IN VARCHAR2,
  4                                       p_conversion_date    IN DATE)
  5     RETURN NUMBER
  6  IS
  7     v_rate   NUMBER;
  8     CURSOR c1
  9     IS
 10        SELECT conversion_rate
 11          FROM gl_daily_rates
 12         WHERE     from_currency = from_currency_code
 13               AND to_currency = to_currency_code
 14               AND conversion_type = p_conversion_type
 15               AND conversion_date = p_conversion_date;
 16  BEGIN
 17     DBMS_OUTPUT.Put_line (
 18           from_currency_code
 19        || to_currency_code
 20        || p_conversion_type
 21        || TO_CHAR (p_conversion_date, 'DD-MON-YYYY'));
 22     FOR c1rec IN c1
 23     LOOP
 24        DBMS_OUTPUT.Put_line (c1rec.conversion_rate);
 25        v_rate := c1rec.conversion_rate;
 26     END LOOP;
 27     RETURN v_rate;
 28  EXCEPTION
 29     WHEN OTHERS
 30     THEN
 31        DBMS_OUTPUT.Put_line (SQLERRM);
 32        v_rate := 1;
 33        RETURN v_rate;
 34* END Get_rate;
SQL> /

Function created.

SQL> select Get_rate('RMB','USD','Corporate' , '13-AUG-14'  )  from dual  
  2  /

GET_RATE('RMB','USD','CORPORATE','13-AUG-14')
---------------------------------------------
                                       .16235

SQL> select CONVERSION_RATE from gl_daily_rates
  2  where from_currency = 'RMB' 
  3   and conversion_type = 'Corporate' 
  4  and To_char(conversion_date, 'DD-MON-YYYY') = '13-AUG-2014' 
  5  and to_currency = 'USD';

CONVERSION_RATE
---------------
         .16235

but in the select Statement 
output
0.16258

select 
,f.primary_uom_code     Attribute5   
,PKG.get_rate(ooh.transactional_curr_code,'USD',ooh.conversion_type_code,TRUNC(ooh.ordered_date))
.
.
.
from tables