| Oracle® Retail Data Model Implementation and Operations Guide Release 11.3.1 Part Number E20363-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Retail Data Model Implementation and Operations Guide Release 11.3.1 Part Number E20363-02 |
|
|
PDF · Mobi · ePub |
This chapter includes the following topics:
Oracle Retail Data Model supports four currency types:
Base
Reporting1
Reporting2
Reporting3
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.The naming conventions for the various currency related fields in Oracle Retail Data Model model are as follows:
Base Currency: *_AMT: Represents the Base/Standard currency amount for Oracle Retail Data Model.
Local Currency: *_AMT_LCL: Represents the local currency amount for an organization. This column can be summed / added up to organization level of Org Hierarchy. The local currency is defined in the Organization in field DWR_ORG_BSNS_UNIT. PRMRY_CRNCY_ISO_CD.
Transaction Currency: *_AMT_TXN: Represents the transactional amount coming from source into this column. As a transaction can occur in many currencies, you need to store the currency code along with the Transaction record details. The column TXN_CRNCY_CD in the base fact table stores the transactional currency code.
Reporting Currency1: *_AMT_RPT: This column contains the amount in Reporting Currency 1. This currency code is as configured/set up in table DWC_CRNCY_CONF. This is the value in column CRNCY_CD_VAL corresponding to CRNCY_CD_TYP value GLBL_CRNCY_RPT_CD.
Reporting Currency2: *_AMT_RPT2: This column contains the amount in Reporting Currency 2. This currency code is as configured/set up in table DWC_CRNCY_CONF. This is the value in column CRNCY_CD_VAL corresponding to CRNCY_CD_TYP value GLBL_CRNCY_RPT2_CD.
Reporting Currency3: *_AMT_RPT3: This column contains the amount in Reporting Currency 3. This currency code is as configured/set up in table DWC_CRNCY_CONF. This is the value in column CRNCY_CD_VAL corresponding to CRNCY_CD_TYP value GLBL_CRNCY_RPT3_CD.
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:
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.
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.
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.
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.
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.
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
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.
|
 Copyright © 2011, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
