The madness of exchange rate fraud in SAP

“Two things can drive a person mad: jealousy and the study of exchange rates.”

Frank Pöpsel, German journalist (“Capital”) (Translated from the German)

SAP remains very true to the spirit of Frank Pöpsel’s words by surprising us with a particular scenario relating to exchange rates that we would like to present to you here today. In this post, the focus will be on erroneous exchange rates, used, for example, to circumvent order release limits.

When you create a purchase order in foreign currency, exchange rates are taken from the “TCURR” table in SAP to convert the purchase order value into local currency. However, this predefined value can be overwritten manually by the user – the amount of the purchase order in local currency is then deliberately displayed as too low (or too high). Assuming a valid exchange rate of 1 EURO to 1.23456 USD, our example is based on 2 scenarios:

1. Creation of an order with an exchange rate of 1 Euro to 111 USD and subsequent change of this exchange rate from 1 Euro to 555 USD. An order for an amount of 500,000 USD is then stored as “only” 901 EURO in the system instead of 405,003 EURO.
2. Creation of an order with an exchange rate of 1 Euro to 333 USD without any subsequent change. This purchase order for an amount of 500,000 USD is stored in the system as “only” 1,502 EURO.

This raises the question of where this deviation from the exchange rates stored in the system has come from.

First assumption – these adjustments can be seen in the change tables. It should be noted that the exchange rate used is stored in the “WKURS” data field in the “EKKO” table. The following SQL query displays such changes:

SELECT OBJECTCLAS, OBJECTID, TABNAME, FNAME, CHNGIND, VALUE_NEW, VALUE_OLD FROM CDPOS WHERE MANDANT = 800 AND TABNAME LIKE 'EKKO' AND FNAME LIKE 'WKURS'

The “CHNGIND” data field with the specification “U” indicates that the purchase order with the number 4500022429 was subsequently changed – that is, the original exchange rate 111 (VALUE_OLD data field) was changed to 555 (VALUE_NEW) when it was created.

This analysis procedure allows us to see the example described in scenario a but is unfortunately incomplete as a result. An incorrect exchange rate that is entered directly when the purchase order is created – that is, scenario b – is not shown. Such entries are displayed in the change document tables as INSERT (CHNGIND = I). This is logical in so far as the purchase order was created and not subsequently changed.

With this in mind, in the next step, the content of the “EKKO” table in the “WKURS” data field is compared with the content of the “TCURR” table. The following statement shows an example of the contents of the “TCURR” table:

SELECT KURST, FCURR, TCURR, GDATU, UKURS FROM TCURR

2 points are noticeable immediately:

1. The “GDATU” data field (date from which the exchange rate is valid) is obviously not a date. The “TCURR” table works with an inverted format as a 9’s complement. The calculation is very simple and reads:

99999999 – (GDATU) = date in the format yyyymmdd – thus for the first entry in the above table 99999999 – 79949781 = 20050218, which equates to 18/02/2005, and for the second entry in the table, 19/02/2005.

2. The values in the “UKURS” data field (exchange rate) differ in format. The trailing “-” in the field “UKURS” indicates an indirect quotation, a missing “-” indicates a price-quoted exchange rate. Indirect quotation means that, for a local currency EURO and a business transaction in USD in the “UKURS” data field in the “TCURR” table, there is a value of “1.23456-” (1 EUR = 1.23456 USD). For a price quotation and a rate of 1 USD = 0.89012 EUR, it is exactly this value which is stored in the “UKURS” data field in the “TCURR” table. In indirect quotation, the corresponding entry would be “1.12344-“.

The following SQL statement compares the “EKKO” table with the “TCURR” table:

SELECT EKKO.AEDAT, EKKO.EBELN, EKKO.WKURS, EKKO.WAERS, T001.WAERS, TCURR.UKURS, 
99999999-TO_INTEGER(CONCAT(GDATU,'0')/10) FROM EKKO 
LEFT JOIN T001 ON (T001.MANDT = EKKO.MANDT AND T001.BUKRS=EKKO.BUKRS) 
LEFT JOIN TCURR ON (T001.MANDT LIKE TCURR.MANDT AND T001.WAERS LIKE TCURR.TCURR AND EKKO.WAERS LIKE TCURR.FCURR)
WHERE 
EKKO.WAERS NOT LIKE T001.WAERS AND KURST LIKE 'EURX' AND (99999999-EKKO.AEDAT)<GDATU AND NOT EXISTS (SELECT * FROM TCURR T2 WHERE T2.MANDT=TCURR.MANDT and TCURR.FCURR=T2.FCURR and TCURR.KURST=T2.KURST AND (99999999-TO_INTEGER(CONCAT(T2.GDATU,'0')/10))<EKKO.AEDAT AND (99999999-TO_INTEGER(CONCAT(T2.GDATU,'0')/10))>(99999999-TO_INTEGER(CONCAT(TCURR.GDATU,'0')/10))) AND EKKO.WKURS NOT LIKE TCURR.UKURS
ORDER BY EKKO.AEDAT DESC

How is the query structured?

First, you define which fields are output with “SELECT”:

SELECT EKKO.AEDAT, EKKO.EBELN, EKKO.WKURS, EKKO.WAERS, T001.WAERS AS T001_WAERS, TCURR.UKURS, 99999999-TO_INTEGER(CONCAT(GDATU,'0')/10) AS TCURR_GDATU FROM EKKO

The expression “9999999999-TO_INTEGER(CONCAT(GDATU,’0′)/10)” may seem somewhat cryptic here. The background to this is the conversion of the inverted date into a readable format yyyymmdd on a SAP HANA database as described above. The conversion of this field does not work properly with standard methods in SQL. As a trick, a “0” is appended to the value in the “GDATU” data field, and this value is then divided by 10.

“JOIN” enriches the set of results with data from the company code table (T001) and the exchange rate table (TCURR).

LEFT JOIN T001 ON (T001.MANDT = EKKO.MANDT AND T001.BUKRS=EKKO.BUKRS)
LEFT JOIN TCURR ON (T001.MANDT LIKE TCURR.MANDT AND T001.WAERS LIKE TCURR.TCURR AND EKKO.WAERS LIKE TCURR.FCURR)

Specifically, the table “T001” and the company codes stored there as well as table “TCURR” are used to determine which exchange rates are to be used for purchase orders in a foreign currency.

Finally, the “WHERE” clause focuses on transactions in a foreign currency (EKKO.WAERS NOT LIKE T001.WAERS). The rest of this clause ensures that only the exchange rate valid at the time of ordering is used as the comparison value.

The result shows the purchase orders created for this blog post:

The result shows the relevant purchase order numbers (EBELN data field), the exchange rate entered manually in the purchase order (WKURS data field), the exchange rate stored in the system with the corresponding valid from date (UKURS and TCURR-GDATU fields) as well as the currency used in the purchase order (WAERS) and the local currency stored for the company code (T001_WAERS). It is noticeable that the exchange rate stored in the purchase order of 555.00000 or 333.00000 differs significantly from the standard exchange rate of 1.12145 specified in the system. In addition, exchange rates from 08/05/2015 (field TCURR_GDATU) are supposed to be used for orders placed on 30/31 August 2018 (AEDAT data field). In short – there are 2 questions to be answered – why have the exchange rates stored in the system not been used and how has the process for maintaining exchange rates in the SAP system been set up?

Why not let zap Audit do the hard work for you?

If this all sounds too technical for you: zap Audit already contains an indicator in beta status for the type of exchange rate cases described above. Why not try it out for yourself? For small company codes, zap Audit is free of charge and we will be happy to show you in advance how to obtain the analysis results you are looking for.