14 Jul Foreign Currency in Oracle E-Business Suite – Period Rates
How are Period Rates used?
Daily Rates are used in E-Business Suite (EBS) to convert foreign currency transactions to the functional currency of your set of books/ledger.
Period Rates are using within the General Ledger module (GL) for specific accounting processes run at the end of the period:
- Translation – re-state accounts in another currency, often as a step towards group consolidation
- Revaluation in Release 11i – to revalue accounts at the latest exchange rate, usually balance sheet accounts. In Release 12, Revaluation uses Daily Rates.
Note that Translation here refers to the process equivalent to ‘Balance-level Reporting Currencies’. A separate post is coming on the differences between this and Journal/Subledger-level Reporting Currencies.
Period End and Period Average Rates
Period End rates are usually the currency exchange rates for the last day of the period, for each currency pair.
Period Average rates are usually an average of rates across all dates in the period.
Revaluation in 11i uses Period End rates to revalue balances.
Translation uses Period End rates to translate balance sheet accounts and Period Average rates to translate P&L (income statement) accounts. The P&L account behaviour can be changed to use Period End rates via a profile option. Note also that Historic Rates can be used to override these for certain accounts, most often Owners’ Equity.
Period Rates in Release 11i
Up to Release 11.5.10, Period Rates are entered independently for every Set of Books in Oracle GL.
The From Currency does not need to be entered; it is always the Set of Books functional currency. So rates are entered for each To Currency needed for Translation or Revaluation, by Balance Type and Period.
Note that the Revaluation rate is always the inverse of the Period End rate.
This is a long process for those using many Sets of Books. If you are using many Sets of Books with the same Functional Currency, then you end up entering the same rates multiple times.
Period Rates in 11i are stored in the table GL_TRANSLATION_RATES.
Oracle do not provide an open interface to load Period Rates. However, FXLoader do have a product to automatically create Period Rates from Daily Rates in 11i.
Fortunately, Oracle made things easier in Release 12.
Period Rates in Release 12
In Release 12, Period Rates are no longer stored separately, but are stored in the same place as Daily Rates, under different rate types.
Thus the table GL_TRANSLATION_RATES is no longer used (though it still exists in the database). All rates are stored in GL_DAILY_RATES.
When you set up a Ledger in Release 12, you enter the Rate Type to use for Period End and Period Average rates.
So now you can use the same Rate Types across all your Ledgers and just enter them once.
The Period End Rate Type can be the same as the Daily Rates you use for converting foreign currency transactions, if you load that every so it is always up to date. Otherwise, you can use a separate Rate Type ‘Period End’ and load the latest rates into it on the last day of the period.
Unfortunately, Oracle does not calculate average rates for you. So the Period Average Rate Type needs to refer to Daily Rates that already have the average rates for the period loaded into it.
FXLoader has a product for Release 12 to calculate average rates and load them into a separate Rate Type.
Period Rates When Upgrading to Release 12
The upgrade process creates multiple new Daily Rate types in Release 12 and stores your Period Rates from Release 11i in the Daily Rates table against those Rate Types.
The name of these Rate Types follows the format:
- Period End99
- Period Average99
where 99 is the Set of Books ID that those rates were stored against in Release 11i.
This means each Ledger in Release 12 will have its own Period End and Period Average Rate Types. This represents duplication as you only really need two Rate Types – one for Period End and one for Period Average.
Oracle have to do this of course, as they don’t know if you have used the same rates in each Set of Books in Release 11i.
So the best approach is to use just two Rate Types and change all Ledgers to use those. You may not even need a separate Rate Type for Period End rates, as you may already have up to date rates in your Rate Type used for converting foreign currency transactions, e.g. Corporate.