Celonis Product Documentation

Currency Conversion - SAP ECC

On this page, the App Store Team provides you with a short overview of what Currency Conversion looks like in SAP ECC and how it is implemented in our standard scripts.

Content

Related SAP Tables

The main tables that are needed to convert from one currency to another are:

TCURR

Contains the absolute exchange rates from one currency to another in a specific timeframe

→ Most important field: UKURS

To avoid the necessity to round exchange rates, SAP stores values in negative numbers, which means, that the inverse of that value explains the exchange rate (see example below).

Example:Converting US Dollars to Euro. Currenct exchange rate: 1\$ = 0,87719 €.

Solution: To increase accurancy (only 5 decimal places are possible!) and to avoid rounding errors SAP stores the inverse. E.g. -1,14, which means 1/1,14 = 0,8771929824561...

The exchange rates itself contained in table TCURR cannot be interpreted, since they always refer to a data record in factor table TCURF.

Attribute

Explanation

MANDT

SAP Mandant

KURST

Exchange Rate Type. Can differ from Customer to Customer The standard script uses KURST = 'M', which is defined as the Average exchange rate type

FCURR

FROM Currency - The initial currency you want to convert from

TCURR

TO Currency - The output currency you want to convert to

GDATU

The date, on which the exchange rate is valid

UKURS

The absolute exchange rate on the specific date (GDATU); The UKURS values are stored in the numerical format XXXX,XXXXX, so the exchange rates can have values from 0,00001 to 9999,99999.

FFACT*

Ratio for the "From" Currency Units

TFACT*

Ratio for the "To" Currency Units

* The correct values for FFACT und TFACT are usually taken from the table TCURF.

TCURF

Contains the conversion of currencies in ratios; needed to provide a more accurate conversion

→ Most important fields: TFACT, FFACT

The UKURS values are stored in the numerical format XXXX,XXXXX, so the exchange rates can have values from 0,00001 to 9999,99999. To increase the range, TCURF.TFACT/FFACT add a factor to the UKURS (see example below)

Example: Converting Indonesian Rupiahs to Euro. The current exchange rate is around 1€ = 0,000079... IDR. As a result of the maximum number of decimal places, TCURR.UKURS would only contain the value 0,00008, which is not very accurate.

Solution:

1. To avoid rounding errors, SAP stores the inverse, e.g. -12,54 (taken from real Customer Data), resulting in 1/12,54 = 0,07974481658692...

As the real exchange rate is way smaller than the one stored in TCURR.UKURS (0,000079 vs. 0,079745), an additional factor is needed. That is where TCURF steps in:

2. TCURF.FFACT (IDR) = 1000 → 0,079745/1000 = 0,000079745

With the help of the FFACT, we are able to get the exchange rate we actually need.

Attribute

Explanation

MANDT

SAP Mandant

KURST

Exchange Rate Type. Can differ from Customer to Customer The standard script uses KURST = 'M', which is defined as the Average exchange rate type

FCURR

FROM Currency - The initial currency you want to convert from

TCURR

TO Currency - The output currency you want to convert to

GDATU

The date, on which the exchange rate is valid

FFACT

Ratio for the "From" Currency Units

TFACT

Ratio for the "To" Currency Units

ABWCT*

Alternative exchange rate type

ABWGA*

Date from which the alternative exchange rate type is valid

* not taken into account

TCURX

Contains information about the number of decimal places of different currencies.

→ Most important field: CURRDEC

Example: The Japanese Yen (JPY) does not have any decimal places → TCURX.CURRDEC = 0, SAP stores every value as a result of the following formula: Stored value = actual value / ( 10 (2 – CURRDEC) )

When 100 JPY is entered in the SAP GUI, the value that is stored in the backend table is 100 JPY / (10(2-0) ) = 1,00 because TCURX.CURRDEC (JPY) = 0

The Tunesian Dinar (TND) has 3 decimal places → TCURX.CURRDEC = 3

When 100 TND (Tunesian Dinar) is entered in the SAP GUI, the value that is stored in the backend table is 100 TND / (10(2-3)) = 1000 because TCURX.CURRDEC (TND) = 3

Solution: To get the real value back, we need to use the information stored in TCURX.CURRDEC. This is done with the calculated TDEC value

The SAP standard is 2 decimal places, so you will not find information for all currencies here.

Attribute

Explanation

CURRKEY

Currency Abbreviation

CURRDEC

Number of Decimals the currency contains

Currency Conversion in the App Store Standard Scripts

The currency conversion in the standard scripts is calculated as follows:

Currency Conversion SAP - Example: P2P Standard, Transformation Script -> Table: Cases

....
,CASE
WHEN E.WAERS = '<%=currency%>' THEN E.NETWR*ISNULL(TCURX.TDEC,1)
WHEN TCURR_CC.UKURS < 0 THEN (E.NETWR*ISNULL(TCURX.TDEC,1))*(1/ABS(TCURR_CC.UKURS)/(CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END))*(CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
WHEN TCURR_CC.UKURS > 0 THEN (E.NETWR*ISNULL(TCURX.TDEC,1))*(TCURR_CC.UKURS/CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END*CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
END AS NETWR_CONVERTED
...
...
FROM
...
JOIN ...
LEFT JOIN (
SELECT
TCURX.CURRKEY
,CAST(TCURX.CURRDEC AS INT) AS CURRDEC
,POWER(CAST(10 AS FLOAT),(2-CURRDEC)) AS TDEC
FROM
TCURX) AS TCURX ON 1=1
AND TCURR_CC.FCURR = E.WAERS
Prerequisite: TDEC

See Lines 3, 4, 5, 16 in SQL Script above

SAP stores every value according to its decimal places (e.g. 100 JPY will be stored as 1,00 JPY = 1 JPY, see above). To calculate these numbers back to the real value, we need TDEC.

TDEC is calculated as follows:

As the standard amount of decimal places is 2, we calculate TDEC with 2 minus the number of decimal places to get back the correct amount.

Example:

100 JPY → SAP: 1,00 JPY = 1 JPY → 1 · 10(2 - 0) = 1 JPY · 100 = 100 JPY

100 TND → SAP: 1000 TND = 1000 · 10(2 - 3) = 1000 TND · 0,1 = 100 TND

CASE 1 - The currency we want to convert already fits the desired output currency

See Line 3 in SQL Script above

Scenario: FROM currency = TO currency

The conversion is calculated as follows:

Example 1: From EUR to EUR,

Given Data:

Value = 100 €

TCURX.CURRDEC (EUR) = NULL → For common currencies with 2 decimal places, the TCURX usually does not contain values!

→ TDEC: ISNULL(10(2 - NULL )) == true → ISNULL(TCURX.TDEC, 1) = 1

NETWR_CONVERTED: 100 € → SAP: 100 €, as EUR has 2 decimal places anyway → 100€ · 1 = 100 €

Example 2: From JPY to JPY,

Given Data:

Value = 100

TCURX.CURRDEC (JPY) = 0

→ TDEC: ISNULL(10(2 - 0 )) == false → ISNULL(TCURX.TDEC, 1) = 10(2 - 0) = 100

→ NETWR_CONVERTED: 100 JPY → SAP: 1,00 JPY = 1 JPY → 1 · 10(2 - 0) = 1 JPY · 100 = 100 JPY

CASE 2 - The currency we want to convert does not fit the desired output currency + the exchange rate (TCURR.UKURS) is < 0

See Line 4 in SQL Script above

Scenario: FROM currency != TO currency, TCURR.UKURS < 0

What does UKURS < 0 in SAP mean? If the exchange rate is < 0 SAP uses the inverse of that rate, meaning that UKURS = -0,003 is used as 1/0,003. That is the reason why we have to make a difference between cases with positive and negative exchange rates.

We also need to take into account that the conversion factors might be different for the FROM and the TO currency. Therefore we divide the FROM currency by the FROM factor and multiply the result by the TO factor.

For negative values, the conversion is calculated as follows:

Example 1:From USD to EUR,

Given Data:

Value = 100 USD

TCURX.CURRDEC (USD) = NULL

TCURR.UKURS = -1,15002

TCURF.FFACT = 1

TCURF.TFACT = 1

TDEC: 10(2 - NULL ) = NULL → ISNULL(TCURX.TDEC, 1) = 1

Example 2:From Indonesian Rupiahs (IDR) to Euro (EUR)

Given Data:

Value = 100 IDR

TCURX.CURRDEC (IDR) = 0

TCURR.UKURS = -12,54

TCURF.FFACT = 1000

TCURF.TFACT = 1

TDEC: 10(2 - 0 ) = 100 → ISNULL(TCURX.TDEC, 1) = 100

CASE 3 - The currency we want to convert does not fit the desired output currency + the exchange rate (TCURR.UKURS) is > 0

See Line 5 in SQL Script above

Scenario: FROM currency != TO currency, TCURR.UKURS > 0

We need to take into account that the conversion factors might be different for the FROM and the TO currency. Therefore we divide the FROM currency by the FROM factor and multiply the result by the TO factor.

For negative values, the conversion is calculated as follows:

Example 1: From British Pounds (GBP) to Euro (EUR)

Given Data:

Value = 100 GBP

TCURX.CURRDEC (IDR) = NULL

TCURR.UKURS = 1,39751

TCURF.FFACT = 1

TCURF.TFACT = 1

TDEC: 10(2 - NULL ) = NULL → ISNULL(TCURX.TDEC, 1) = 1

Example 2: From Indonesian Rupiahs (IDR) to Euro (EUR)

Given Data:

Value = 100 IDR

TCURX.CURRDEC (IDR) = 0

TCURR.UKURS = 0,1073

TCURF.FFACT = 1000

TCURF.TFACT = 1

TDEC: 10(2 - 0 ) = 100 → ISNULL(TCURX.TDEC, 1) = 100

Q + A

Q: After I ran the transformations, the NETWR_CONVERTED does not show any data for some/most of the conversions, especially for one or some specific currencies, what is wrong?

A: This can have several reasons. In the last months, the following methods fixed conversion problems:

1. The exchange rate type (KURST = 'M') used in the standard script does not fit the customer's standard exchange rate type → Ask the customer which KURST is used and change the parameter in the EMS Data Integration! Make sure to rerun the transformations afterwards and to completely reload the datamodel to apply the changes.

2. The date of the currency exchange is wrong / For the specific date, there is no data in the conversion tables → This problem is fixed by using a date range instead of a specific date. That is one of the main reasons on why we add VALID_START and VALID_END in the Currency Conversion Tables (TCURR_CC and TCURF_CC). If there are still problems, let us know!

Q: In the scripts we talk about TCURR_CC and TCURF_CC, whereas in the article we talk about TCURR and TCURF. Where is the difference?

A: TCURR_CC and TCURF_CC are created manually by us. They are based on the SAP source tables TCURR and TCURF and are enriched with additional data, like valid start and valid end of the exchange rates, or a ranking of the exchange rates when there is more than one possibility. However, for the sake of simplicity, the article uses the SAP standard tables for explanations. The logic is the same, as the _CC tables contain the same fields than source tables

Q: Why don't we use other fields - like EKKO.WKURS - to calculate the exchange rate?

A: The SAP standard way for converting currencies is the one that is realized in the App Store Scripts. In some rare cases, customers used the EKKO.WKURS field to convert currencies. We are not a 100% sure on how valid the data stored in this field is. However, we believe that the data of WKURS might come from a manual input during the purchasing process. Herewith the data may not represent the correct exchange rate

Q: Why is TCURF needed, FFACT and TFACT also exist in TCURR?

A: The two fields in TCURR usually do not have any importance. Anyway, to handle eventualities, we included them in the standard scripts as the "second choice" for adding the factor to the UKURS.

Q: What does UKURS < 0 in SAP mean?

A: If the exchange rate is < 0 SAP uses the inverse of that rate, meaning that UKURS = -0,003 is used as 1/0,003. That is the reason why we have to make a difference between cases with positive and negative exchange rates.

Q: The absolute exchange rates (column UKURS) in the table TCURR contain negative values - is that an indicator for bad data?

A: No. Negative exchange rates in SAP are the representation of the inverse. Example: UKURS = -1,1145 → exchange rate = 1/1,1145

Q: Why does SAP sometimes store exchange rates in negative and sometimes in positive values?

A: Actually, we currently do not know why this differs from case to case. Explanations on that are highly appreciated!