We came across the post during one of our ETL projects. We are extracting data from SAP using Data360.
Introduction
We pulled the header and item data from the billing and sales tables. Extracted the appropriate totals and multiplied them by the exchange rate. By the client’s request, we had to then divide the total by 10. Not sure why we had to at the time but the base currencies matched the sales data provided to us.
All seemed well until we started reporting on other businesses in the company which deal with other currencies. After many hours of testing the data, we determined that somewhere some additional decimal manipulation must take place. That’s when we discovered this article on the SAP forum: https://community.sap.com/t5/enterprise-resource-planning-blogs-by-members/decimal-shift-decimal-shift-back-in-currency-conversion-an-example/ba-p/13344156
The Problem
Our client has many businesses in many different countries. They record their sales in their respective currencies and then it’s converted to the statistics currency. We pull all these numbers from a table named ‘Billing Table’ (This is a combination of VBRP and VBRK).
We multiply Subtotal 1 (KZWI1) with the Exchange Rate (KURSK) to create a column we call ‘Final Total’. As mentioned above we divide ‘Final Total’ by 10 (as per the client’s suggestion).
This worked for the base currencies that required no conversion changes but not for the others that do and we’ll explain why below.
The Solution
After reviewing the article above we noticed that the decimals of the Exchange Rate need to shift based on additional logic. The calculation for the decimal shift is as follows:
Exchange Rate x (10 ^ 2 – The Number of Decimals of the Base Currency)
The Number of Decimals of the Base Currency can be found in the TCURX table in the ‘CURRDEC’ column. We used the ‘CURRKEY’ column to match with the ‘WAERK’ in the ‘Billing Table’ (A combination of VBRP and VBRK). This gave us the decimal places for the currencies.
Next, we edited the Exchange rates with the above logic. In our example, we are converting BHR (Bahrainy Reyal) to KWD (Kuwaiti Dinar).
Our Subtotal was 25,779.60.
The exchange rate at the time of the sale for BHR/KWD was 0.81120.
The CURRDEC or The Number of Decimals of the Base Currency for BHD is 3
To obtain the correct total we need to do the following math:
Exchange Rate x (10 ^ 2 – The Number of Decimals of the Base Currency)
0.81120 x (10 ^ 2 – 3)
= 0.81120 x (10 ^ -1)
= 0.81120 x (0.1)
= 0.08112
Now we multiply Subtotal 1 by the new exchange rate to obtain the Final Total
25,779.60 x 0.08112
= 2,091.24
Once we applied these steps to our data flows, we started seeing our gross sales numbers align. Please also note that this conversion needs to be applied to your cost and discount calculations as well.
You might find that your TCURX table does not have all the currencies you work with in SAP. Then the above logic does not apply to that currency.
An example in our case was a USD to KWD conversion. Our TCURX table does not have USD so no ‘CURRDEC’ value was supplied.
Our Subtotal was 5,724.75
The Exchange rate at the time of the sale was 0.30575
The CURRDEC was empty
Our Final Total is
5,724.75 x 0.30575
= 1,750.34
The Conclusion
Should be struggling with validating sales numbers with multiple currencies in SAP consider looking into the TCURX table and the decimal shifts that happen between currencies. If you need further assistance, consider contacting Data Active to assist you with your ETL and Reporting.