Viewing General Ledger (GL) Daily Currency Conversion Rates

NOTE: This SQL works in version 11.5.10.2, your version might be different!

Many companies like to have "one version of the truth" (I say "like to" as we all know how hard this is to actually achieve!). One of the ways this can be achieved is a standardised set of currency conversion rates across an organisation.

The SQL in this blog post gives you a quick and easy report showing the currency conversion rates currently being used in the GL.


If your company is using Oracle Finance software (i.e. General Ledger) and the currency conversion rates are being loaded into the system (either automatically or manually) then it makes sense to publish this information out so that other parts of the company can use it.

The SQL below will show currency conversion rates (or a specified type) between two currencies and between two dates with the latest date conversion rate entered at the top.

/* Formatted on 9/26/2014 4:44:43 PM (QP5 v5.115.810.9015) */
  SELECT   FROM_CURRENCY,
           TO_CURRENCY,
           TO_CHAR (CONVERSION_DATE, 'DD-MON-YYYY') COVERSION_DATE,
           SHOW_CONVERSION_RATE,
           SHOW_INVERSE_CON_RATE
    FROM   GL_DAILY_RATES_V
   WHERE       status_code != 'D'
           AND (FROM_CURRENCY = :FROM_CURRENCY)
           AND (TO_CURRENCY = :TO_CURRENCY)
           AND (CONVERSION_DATE >= TO_DATE (:START_DATE, 'DD-MON-YYYY')
                AND CONVERSION_DATE < TO_DATE (:END_DATE, 'DD-MON-YYYY') + 1)
           AND (USER_CONVERSION_TYPE = :USER_CONVERSION_TYPE)
ORDER BY   from_currency,
           to_currency,
           conversion_date DESC,
           user_conversion_type

In order to run this SQL you need to specify five parameters;

FROM_CURRENCY: The currency you wish to convert from (i.e. EUR)
TO_CURRENCY: The currency code you wish to convert to (i.e. GBP)
START_DATE: This is the first date you want to see in the report in the format DD-MON-YYYY
END_DATE: This is the last date you want to see in the report in the format DD-MON-YYYY
USER_CONVERSION_TYPE: This will be dependant on your system, I'd recommend you look in the GL_DAILY_RATES_V view and find out the values used at your site and then plug one of those in.