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

on



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 

Comments

Post a Comment