Oracle® Business Intelligence Applications Installation and Configuration Guide > Configuring Common Components of the Oracle Business Analytics Warehouse > Handling European Monetary Union (EMU) Currencies >
About the EXP_CURR_CONVERSION_TRANSFORM Expression Transformation to Supply EMU Exchange Rates
The Expression transformation contains currency conversion logic for converting any combination of EMU currency (including individual EMU member currencies as well as the Euro) and other currencies (OTH) outside of the EMU. This logic applies to the following combinations: - EMU to EMU. This is the conversion from the individual currency of one EMU member country to another.
- EUR to EMU. This is the conversion from the joint EMU currency, the Euro, to the individual currency of an EMU member.
- EMU to EUR. This is the conversion from the individual currency of an EMU member to the joint EMU currency, the Euro.
- OTH to EUR. This is the conversion from any other currency outside the EMU to the Euro.
- EUR to OTH. This is the conversion from the Euro to any other currency outside the EMU.
- EMU to OTH. This is the conversion from the individual currency of an EMU member country to another currency outside the EMU.
- OTH to EMU. This is the conversion from any other currency outside the EMU to the individual currency of an EMU member.
Unlike the flat file method, the transformation handles conversions between EMU currencies and currencies for countries outside the EMU. In addition, this method conforms to the rounding rules set by the European Monetary Union. The transformation performs the conversion in different ways, depending on the types of input and output currencies. In the transformation, there are ten sets of amount input fields; each set consists of a document amount, local amount, and group amount field. In this discussion, document currency is referred to as the From currency and global and local currencies as the To currencies. Source-supplied document amounts are output as the same value. However, if the local and global amounts are not supplied, then a particular currency conversion process occurs. There are six different conversion processes that could occur. They are described in the list that follows:
To use an Expression transformation, you must add the transformation to every load mapping that requires any of the previously listed types of currency conversions. See the M_F_EURO_TRIANG_EXP_USAGE_EXAMPLE mapping for an example of how to incorporate an Expression transformation. This mapping is located in the Configuration for Universal Source folder. To add the transformation to a load mapping
- Open the applicable source system configuration folder.
- Select Tools > Transformation Developer.
- Open the Oracle BI folder and drag and drop the
EXP_CURR_CONVERSION_TRANSFORM Expression transformation to create a shortcut.
You can place the shortcut in the Transformations folder contained in the applicable source system configuration folder.
- Select Mapplet Designer.
- Open the applicable Source Adapter mapplet.
- Add
LKP_XRATES from the Transformations folder.
NOTE: LKP_XRATES is a lookup to the W_EXCH_RATE_G table. If the Transformations folder does not have a lookup to W_EXCH_RATE_G , you must make a shortcut to LKP_XRATE in the Siebel Analytics Enterprise Applications folder.
- Add the shortcut to the
EXP_CURR_CONVERSION_TRANSFORM Expression transformation to the Source Adapter mapplet.
Place the transformation between the load mapping's existing Expression transformation and MAPO, as shown in the following figure.
- Reconnect the ports as necessary so that the data flows through the transformation and into the MAPO.
Table 43 lists all of the prepackaged exchange rates used by the transformation. Table 43 only lists exchange rates from EMU currency to Euro. Using these exchange rates, the transform can calculate any of the five scenarios (EMU to EMU, EUR to EMU, EMU to EUR, EMU to OTH, and OTH to EMU). For EMU to OTH, OTH to EMU, and EMU to EMU, the transformation converts the From Currency to the Euro and then from the Euro to the To Currency. For that reason, Table 43 only has EMU currency to the Euro conversion rates.
Table 43. Prepackaged Exchange Rates in Expression Transformation
|
|
|
Conversion Rate for One Euro |
BEF |
Belgian Francs |
01/01/1999 |
40.3399 |
DEM |
German Marks |
01/01/1999 |
1.95583 |
ESP |
Spanish Pesetas |
01/01/1999 |
166.386 |
FRF |
French Francs |
01/01/1999 |
6.55957 |
IEP |
Irish Púnt |
01/01/1999 |
.787564 |
ITL |
Italian Lira |
01/01/1999 |
1936.27 |
LUF |
Luxembourg Francs |
01/01/1999 |
40.3399 |
NLG |
Dutch Guilders |
01/01/1999 |
2.20371 |
ATS |
Austrian Schillings |
01/01/1999 |
13.7603 |
PTE |
Portuguese Escudo |
01/01/1999 |
200.482 |
FIM |
Finnish Markka |
01/01/1999 |
5.94573 |
You can incorporate additional EMU currency conversion rates into the transformation. When doing so, you must consider whether or not the conversion rate is effective before January 1, 1999 or on/after January 1, 1999. (January 1, 1999 is the date the Euro was enacted.) To add a new EMU currency with the same effectivity date (January 1, 1999)
- Edit the decode statement for the columns
EMU_TO_EURO_CONV_FACT_DOC , EMU_TO_EURO_CONV_FACT_LOC , and EMU_TO_EURO_CONV_FACT_GRP , to add the new currency and its conversion rate.
For example, if you are adding EMU1, where the Euro-to-EMU1 conversion rate is 2.34567, then you would modify the EMU_TO_EURO_CONV_FACT_DOC column definition as follows:
EMU_TO_EURO_CONV_FACT_DOC = IIF (ISNULL (EXT_XRATE_LKP_DT) OR TO_NUMBER(TO_CHAR(EXT_XRATE_LKP_DT,'J')) >= TO_NUMBER(TO_CHAR(TO_DATE('01/01/1999', 'MM/DD/YYYY'),'J')), DECODE(EXT_DOC_CURR_CODE, 'BEF', 40.3399000000, 'NLG', 2.2037100000, 'ATS', 13.7603000000, 'PTE', 200.4820000000, 'FIM', 5.9457300000, 'IEP', 0.7875640000, 'LUF', 40.3399000000, 'FRF', 6.5595700000, 'ITL',1936.2700000000, 'DEM', 1.9558300000, 'ESP', 166.3860000000, 'EMU1',2.3456700000, 'EUR', 1.0000000000, NULL), NULL)
To add a new EMU currency with a different effectivity date than January 1, 1999
- Edit the decode statement for the columns
EMU_TO_EURO_CONV_FACT_DOC , EMU_TO_EURO_CONV_FACT_LOC , and EMU_TO_EURO_CONV_FACT_GRP, to add the new currency, its conversion rate, and the effectivity date logic.
For example, if you are adding EMU2, where the Euro-to-EMU2 conversion rate is 3.45678, and the exchange rate effectivity date is 01/01/2001, then you would modify the EMU_TO_EURO_CONV_FACT_DOC column definition as follows:
EMU_TO_EURO_CONV_FACT_DOC = IIF (ISNULL (EXT_XRATE_LKP_DT) OR(TO_NUMBER(TO_CHAR(EXT_XRATE_LKP_DT,'J')) >= TO_NUMBER(TO_CHAR(TO_DATE('01/01/1999', 'MM/DD/YYYY'),'J')) AND EXT_DOC_CURR_CODE!= 'EMU2') OR TO_NUMBER(TO_CHAR(EXT_XRATE_LKP_DT,'J')) >= TO_NUMBER(TO_CHAR(TO_DATE('01/01/2001', 'MM/DD/YYYY'),'J')),DECODE(EXT_DOC_CURR_CODE, 'BEF', 40.3399000000, 'NLG', 2.2037100000, 'ATS', 13.7603000000, 'PTE',200.4820000000, 'FIM', 5.9457300000, 'IEP', 0.7875640000, 'LUF', 40.3399000000, 'FRF', 6.5595700000, 'ITL',1936.2700000000, 'DEM', 1.9558300000, 'ESP',166.3860000000, 'EMU2', 3.4567800000, 'EUR', 1.0000000000, NULL), NULL)
|