Exchange Rates File
The exchange rates file captures conversion rates between any two currency codes that may appear in your fact data. The standard practice for fact data is to load the source system values in the original currency and allow the platform to convert the amounts to the primary currency. This file facilitates that process and triggers bulk updates in nightly processing any time you wish to change your exchange rates for financial reporting purposes. Adding new rates to the file with an effective date equal to the batch date triggers a mass update to all positional facts, converting all the amounts to the new exchange rate even if the item/location did not otherwise change in the source system.
Note that you do not have to provide exchange rates data for the following scenarios:
-
You are loading your data already converted to primary currency
-
You only use a single currency for your entire business
-
You are only implementing a AI Foundation module and expect to perform those processes in the local currency amounts
Even when exchange rates are not required as a separate file, you must still populate the currency codes (DOC_CURR_CODE
, LOC_CURR_CODE
) in the fact data files with values. Review the scenarios below to understand how to set
these values and provide the associated rates.
Scenario 1 - No Conversion
For this use-case, all data is in the desired currency before sending
it to Oracle. You do not want the platform to convert your data from source currency to primary currency. All fact records
must have LOC_CURR_CODE
= DOC_CURR_CODE
. For example, set both values to USD
for sales in the U.S. and both values to CAD
for sales in Canada that you pre-converted. EXCH_RATE.csv
data is not required or used for records having the same currency code on both columns.
Scenario 2 – Only One Currency
If your business only operates in one region and uses a single currency
code, then you don’t need to provide exchange rate data. All fact records must have LOC_CURR_CODE
= DOC_CURR_CODE
. For example, set both values to USD
on all rows if your primary operating currency
is USD. EXCH_RATE.csv
data is not required or used for records having the same currency code on both columns.
Scenario 3 – Multiple Currencies
When you do plan to provide data in multiple source currencies,
you must also provide the exchange rates into and out of those currencies. Your fact data must have DOC_CURR_CODE
set to the currency of the source system where the transaction was recorded (for example, a sale in Canada has a document
currency of CAD
). The value of LOC_CURR_CODE
will be the same on all records and must be
the primary operating currency of your business (if you operate mainly in the United States then it will be USD
).
Exchange rates should be provided using the standard international rates (for example USD > CAD may be 1.38) but the fact load will perform lookups in reverse. Fact conversions are applied as a division process. For example, “transaction amount / exchange rate” is the formula to convert from document currency to primary currency; so when converting from CAD > USD the system will look up the value for USD > CAD and divide by that number to get the final value.
Table 8-6 Exchange Rate File Required Fields
Column Header | Usage |
---|---|
START_DT |
Contains the effective start date of the exchange rate. Set to the current business date to trigger new rate conversions. |
END_DT |
Contains the effective end date of the exchange rate. Default to |
EXCHANGE_RATE |
Contains the exchange rate for the specified currency/type/effective date combination. |
FROM_CURRENCY_CODE |
Code of the currency to be exchanged. |
TO_CURRENCY_CODE |
Code of the currency to which a currency is exchanged. |
Sample data for the EXCH_RATE.csv file columns above:
START_DT,END_DT,EXCHANGE_RATE,FROM_CURRENCY_CODE,TO_CURRENCY_CODE
20180514,21000101,0.8640055,CAD,USD
20180514,21000101,0.1233959,CNY,USD
The exchange rates data must also satisfy the following criteria if you are loading data for use in Retail Insights reporting:
-
Rates must be provided in both directions for every combination of currencies that can occur in your dataset (for example, USD > CAD and CAD > USD).
-
Dates must provide complete coverage of your entire timeframe in the dataset, both for historical and current data. The current effective records for all rates can use 2100-01-01 as the end date. Dates cannot overlap, only a single rate must be effective per day.
-
Rates should not change more often than absolutely necessary based on the business requirements. If you are implementing RI with positional data, a rate change triggers a complete recalculation of the stock on hand cost/retail amounts for the entire business across all pre-calculated aggregate tables. When RI is not used for financial reporting you might only change the rates once each fiscal year, to maintain a single constant currency for analytical purposes.