Skip Headers
Oracle® Retail Data Model Implementation and Operations Guide
Release 11.3.2

Part Number E20363-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

7 Multi-Currency Support and Configuration

This chapter includes the following topics:

Multi-Currency Overview

Oracle Retail Data Model supports four currency types:

You configure and setup these currencies in the DWL_CRNCY_CONF table.

Base Currency is the Standard or default currency for the Oracle Retail Data Model installation. The currencies: Reporting1/2/3 are the three available Reporting Currencies available through the Oracle Retail Data Model Analytical Layer.

In addition, Oracle Retail Data Model supports Local (Lcl) and Transactional (Txn) currencies to store other aspects of the business. These currencies need to be stored in the table DWL_CRNCY. It is expected that a full set of currencies would be defined in this table. This table contains the Oracle Retail Data Model Base and Reporting1/2/3 currencies as well as any other currencies which are used by the Retailer. For example, if the Retailer has operations in ten countries with ten different currencies and if the Base and Reporting1/2/3 currencies are a subset of these ten currencies, then you need to configure the four mandatory currencies in DWL_CRNCY_CONF and also setup the ten currencies in the DWL_CRNCY table (including the four mandatory and the six additional currencies).

The exchange rates, calculated with respect to the Base currency, should be stored in table DWB_EXCHANG_RATE_CRNCY_DAY. You populate this table for enabling currency conversions during Intra-ETL process, from any of the ten defined transaction currencies to the four mandatory currencies. This table can also be used for dynamically converting Base currency figures to any other currency within the Reporting Layer.

The Intra-ETL performs currency conversions at the Base Layer and populates the Base, Reporting 1/2/3 Currencies in respective AMT (amount) columns in the Analytical Layer (Derived/Aggregate).

From a Reporting perspective, certain reports can be built which are enabled for multi-currency analysis. Reports enabled for multi-currency analysis can show a drop down containing Base or one of the three Reporting Currencies and any other currency available in the DWL_CRNCY table. Reports delivered in Base and Reporting 1/2/3 currencies are available in a pre-calculated mode. The Reports (requests) do not need to perform any conversions while requesting a currency from within this group. For any other currency, the reports will need to perform currency conversion dynamically and the converted results would be displayed in the report (performance will be worse when compared to a report request involving a mandatory currency).

Note:

Most of the currency conversion reports (especially the non-mandatory currencies) are relational in nature. They would be built to run against the relational source tables in Oracle Retail Data Model. The OLAP component as well as OLAP Reports can support only the mandatory Base and Reporting 1/2/3 currencies.

Multi-Currency Data Field Naming Conventions

The naming conventions for the various currency related fields in Oracle Retail Data Model model are as follows:

Multi-Currency Data Movement

Oracle Retail Data Model obtains the transaction amount and the currency information from the source system and loads this information into interface tables. The transaction and currency data movement occurs as follows:

Movement from Interface to Base Tables

The movement from Interface to Base tables occurs as follows:

  • The value of the *_AMT column is calculated based on the currency rate and the rate is picked up from the table DWB_EXCHNG_RATE_CRNCY_DAY.

  • The value of the *_AMT_LCL column is calculated based on the currency rate and the rate is picked up from the table DWB_EXCHNG_RATE_CRNCY_DAY.

  • The value of the *_AMT_TXN column is the same as the transaction amount and the corresponding currency code is placed in the new column.

  • The value of the *_AMT_RPT column is calculated based on currency rate and the rate is picked up from the table DWB_EXCHNG_RATE_CRNCY_DAY.

  • The value of the *_AMT_RPT2 column is calculated based on the currency rate and the rate is picked up from the table DWB_EXCHNG_RATE_CRNCY_DAY.

  • The value of the *_AMT_RPT3 column is calculated based on the currency rate and the rate is picked up from the table DWB_EXCHNG_RATE_CRNCY_DAY.

Handling Currency at the Base Level

The Interface input file should have the Txn_Cd of the record being loaded. The transactions are converted into Base crncy and loaded in _AMT columns in the DWB_* table. The original (txn) value is loaded into the *_AMT_TXN column in DWB_ tables. Also the _AMT_RPT/2/3 columns contain the default Reporting currency values (converted). The column TXN_CRNCY_CD indicates the TXN currency.

Movement from Base to Derived Tables

The movement from Base to derived tables occurs as follows:

  • It is not possible to sum up the values in the *_AMT_TXN columns from the DWB table to the DWD table as the DWD record can encompass many transactional currencies. Thus, the *_AMT_TXN column is not present in the Derived Table.

  • The *_AMT_LCL in DWD table could be summed up at Business Unit level from corresponding column in DWB. If Derived table is at higher level, then this column should not be present in the Derived table.

  • The *_AMT column in DWD table would be summed from the corresponding column in DWB table.

  • The *_AMT_RPT column in DWD table would be summed from the corresponding column in DWB table.

  • The *_AMT_RPT2 column in DWD table would be summed from the corresponding column in DWB table.

  • The *_AMT_RPT3 column in DWD table would be summed from the corresponding column in DWB table.

Movement from Derived to Aggregate Tables

The movement from derived to aggregate tables occurs as follows:

  • The *_AMT_LCL in DWA table could be summed up at Business Unit level from corresponding column in DWB/DWD. If Aggregate table is at higher level, then this column should not be present in the Aggregate table.

  • The *_AMT column in DWA table would be summed up from corresponding column in DWB/DWD table.

  • The *_AMT_RPT column in DWA table would be summed up from corresponding column in DWB/DWD table.

  • The *_AMT_RPT2 column in DWA table would be summed up from corresponding column in DWB/DWD table.

  • The *_AMT_RPT3 column in DWA table would be summed from corresponding column in DWB/DWD table.

Handling Data Movement from Base to Derived and Aggregate Layers

Base information can be summed up and loaded into respective columns in Derived table. Derived table will contain the _AMT (Base) and _AMT_RPT, _AMT_RPT2/3 for RPT1/2/3 currencies. The _AMT_TXN will not be present in DWD table as it does not apply (multiple Txn currencies cannot be rolled up into single value).

Similarly Derived information is summed up and loaded into DWA entities.

Currency Data Flow

Figure 7-1 shows the data flow showing the movement of currency information through the layers of Oracle Retail Data Model.

Figure 7-1 Currency and Transaction Amount Data Flow in Oracle Retail Data Model

Description of Figure 7-1 follows
Description of "Figure 7-1 Currency and Transaction Amount Data Flow in Oracle Retail Data Model"

Currency DWC_CRNCY_CONF Table

Table 7-1 shows the currency configuration table DWC_CRNCY_CONF details. This table stores the Base Currency and the three reporting currencies.

Table 7-1 DWC_CRNCY_CONF Table Details

Column Name Data Type Nullable Remarks

CRNCY_CD_TYP

VARCHAR2 (30)

No

Types of Currency Codes. Possible values:

GLBL_CRNCY_RPT_CD

GLBL_CRNCY_RPT2_CD

GLBL_CRNCY_RPT3_CD

BASE_CRNCY_CD

This is the PK column for this table

CRNCY_CD_VAL

VARCHAR2 (30)

No

Value of corresponding Currency code like USD, GBP, INR, and so on

WID

NUMBER(30)

No

System field


The information in the columns shown in Table 7-1 and should be set up during Oracle Retail Data Model installation and should not be modified or updated subsequently.

For example, the currency configuration table DWC_CRNCY_CONF entries suitable for a customer with a base currency GBP and requires reporting in three currencies: FRC, USD and EUR is shown in Table 7-2.

Table 7-2 DWC_CRNCY_CONF Sample Values

CRNCY_CD_TYP CRNCY_CD_VAL WID

BASE_CRNCY_CD

GBP

1

GLBL_CRNCY_RPT_CD

FRC

1

GLBL_CRNCY_RPT2_CD

USD

1

GLBL_CRNCY_RPT3_CD

EUR

1


Use the table DWB_EXCHNG_RATE_CRNCY_DAY to store the exchange rates for currency conversion.

The table DWL_CRNCY stores all the currencies required by Oracle Retail Data Model. This table should contain the four mandatory configuration currencies as defined in DWC_CRNCY_CONF table. This table must also contain any additional currencies which may be used as Transactional or Local currencies (Transactions) or as additional Reporting currencies. Certain Reports can be built which perform currency conversion dynamically and display results in terms of these additional Reporting Currencies.