Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Common Components of the Siebel Customer-Centric Enterprise 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 group and local currencies as the To currencies. Source-supplied document amounts are output as the same value. However, if the local and group 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:

• Non-EMU to Non-EMU. If the From Currency and To Currency are not EMU currencies, the following logic is used:
  • The supplied exchange rate is used to compute the new amount.
  • If the exchange rate is not supplied as an input to the transformation, the exchange rate is retrieved using a lookup to the IA_XRATES table. The new amount is then calculated using the looked up exchange rate and the From Currency amount.
• EMU to EMU. If the From Currency and To Currency are EMU currencies, the following logic is used:
  • Euro-triangulation is used to derive the new amount. Please note that the Euro-triangulation logic only applies if the exchange rate date is later than or equal to the Euro effective date.
  • If the exchange rate date is earlier than the Euro effective date, this case is treated as a Non EMU to Non EMU currency conversion case.

    The Euro-triangulation logic is used to convert one EMU currency to another EMU currency (EMU to EMU). Again, this logic is only applicable if the exchange rate date is equal to or later than the Euro effective date. The Euro-triangulation logic is as follows:

  • Convert from one national denomination (EMU_DOC) to its Euro equivalent (EUR). For example, EUR = EMU_DOC/(EUR-TO-EMU_DOC conversion rate).
  • Round the previous step's result to the nearest three decimal points. The default rounding is to three decimal points, but this is configurable.
  • Convert the Euro equivalent into the resulting national denomination (EMU_LOC). For example:

  EMU_LOC=(Result from previous step) * (EUR-TO-EMU_LOC conversion rate)

• EMU to OTH. If the From Currency is EMU and To Currency is OTH, the following logic is used in the given order:
  • The supplied exchange rate is used to compute the amount.
  • If the exchange rate is not supplied and the NONEMU_EMU_TRI_FLAG = Y, then the two-step conversion method is used to derive the amount. The two-step conversion method is defined as follows:
    • First, the From Currency amount is converted to the Euro Currency amount using the fixed EMU conversion rates supplied by the transformation's logic.
    • Second, the Euro Currency amount is converted to the To Currency amount using the appropriate exchange rate.

      NOTE:  The two-step conversion method is only used if the exchange rate date is greater than or equal to the Euro effective date and the NONEMU_EMU_TRI_FLAG is set to Y.

  • If the NONEMU_EMU_TRI_FLAG is set to N, this is treated as a Non EMU to Non EMU currency conversion case.
• OTH to EMU. If the From Currency is OTH and the To Currency is EMU, then the following logic is used:
  • The supplied exchange rate is used to compute the To Currency amount.
  • If the exchange rate is not supplied and the NONEMU_EMU_TRI_FLAG = Y, then the two-step conversion method is used to derive the amount. The two-step conversion consists of the following two steps:
    • First, the From Currency amount (OTH) is converted to the Euro Currency amount using the exchange rate from the IA_XRATES table.
    • Second, the Euro Currency amount is converted to the EMU Currency amount using the fixed Euro conversion rate available in the transformation. Please note that the two-step conversion method only applies if the NONEMU_EMU_TRI_FLAG = Y.
  • If the NONEMU_EMU_TRI_FLAG = N, this is treated as a Non EMU to Non EMU currency conversion case.
• EUR to EMU. If the From Currency is EUR and the To Currency is EMU, the following logic is used:
  • If the exchange rate date is greater than or equal to the Euro effective date, then the From Currency amount is converted to the To Currency amount using the fixed Euro conversion rates supplied by the transformation's logic.
  • In all other cases, this is treated as a Non-EMU to Non-EMU currency conversion case.
• EMU to EUR. If the From Currency is EMU and the To Currency is EUR, the following logic is used:
  • If the exchange rate date is greater than or equal to the Euro effective date, the From Currency amount is converted to the To Currency amount using the fixed Euro conversion rates supplied by the transformation's logic.
  • If the exchange rate date is less than the Euro effective date, this is treated as a Non EMU to Non EMU currency conversion case.

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

1. Open the applicable source system configuration folder.
2. Select Tools > Transformation Developer.
3. Open the Siebel Business Analytics 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.

4. Select Mapplet Designer.
5. Open the applicable Source Adapter mapplet.
6. Add LKP_XRATES from the Transformations folder.

   NOTE:  LKP_XRATES is a lookup to the IA_XRATES table. If the Transformations folder does not have a lookup to IA_XRATES, you must make a shortcut to LKP_XRATE in the Siebel Analytics Enterprise Applications folder.

7. 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.
8. Reconnect the ports as necessary so that the data flows through the transformation and into the MAPO.

Table 21 lists all of the prepackaged exchange rates used by the transformation. Table 21 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 21 only has EMU currency to the Euro conversion rates.

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)