1. Implementation Guide
  2. Data File Generation
  3. Dimension Files
  4. Exchange Rates File

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 21000101 if the rate should be effective indefinitely.

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:

  1. 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).

  2. 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.

  3. 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.