SQL Query to fetch Exchange Rates/Daily Rates (using GL_DAILY_RATES and GL_DAILY_CONVERSION_TYPES) in Oracle Fusion Financials

In Oracle Fusion, GL_DAILY_RATES contains the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table available in Oracle Apps. 

GL_DAILY_RATES contains the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched. 

For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997. 

GL_DAILY_CONVERSION_TYPES contains daily conversion rate types and their descriptions. This table corresponds to the Conversion Rate Types form.

Use the below query to fetch Exchange Rates/Daily Rates in Oracle Fusion Financials

 

SELECT rate.from_currency

	,rate.to_currency

	,rate.conversion_date

	,rate.conversion_type

	,type.user_conversion_type

	,rate.conversion_rate conversion_rate

	,rate.status_code

FROM GL_DAILY_RATES rate

	,GL_DAILY_CONVERSION_TYPES type

WHERE rate.CONVERSION_TYPE            = type.CONVERSION_TYPE 

AND (

        TRUNC(rate.last_update_date)  >= TRUNC(NVL(:P_LAST_RUN_DATE,rate.last_update_date))

        OR

        TRUNC(type.last_update_date)  >= TRUNC(NVL(:P_LAST_RUN_DATE,type.last_update_date))

    )

 

Source: https://docs.oracle.com