This applies to SAP Business Warehouse/Business Intelligence. For more information, visit the EDW homepage.
This document describes the procedure to handle the currencies which has the decimal places other than 2 in the table TCURX.
Author(s): Murali Maripalli
Company: Capgemini India Pvt Ltd
Created on: 2nd July 2012
Murali Maripalli is a Senior SAP BW/BI consultant at Capgemini India Pvt Ltd since 2008 onwards and has worked on multiple Implementations/ Support/ Upgrade projects.
SAP stores all Amount values of different currencies with a fixed interpretation of having two decimal places. There are few exceptions to this rule for the currencies which has no meaning for the decimal places like ‘Chilean Peso’, Japanese Yen, South Korean Won, Paraguayan Guarani and many other currencies which we talk in the below sections. For few Currencies the fractions are needs to be stored more than 2 decimals due to the currency values and this document describes the procedures that needs to be followed to have the right reporting.
About TCURX table:
Currencies which do not have two decimal places must be defined in table TCURX (decimal places for currency codes). The table determines the number of decimal places in the output according to the currency key. If the contents of currency exist in table TCURX as currency key CURRKEY, the system sets the number of decimal places according to the entry for the field CURRDEC in TCURX.
You can maintain this table via the R/3 Implementation Guide (Global Settings -> Currencies, Transaction OY04).
If a currency is not defined in Table TCURX (decimal places for currency codes), this currency is regarded as currency with two decimal places.
Impact of TCURX in BW on data:
Currently in my present system the following currencies were listed in the TCURX Table:
Details of the Currencies
In the above table some of the Currencies are obsolete.
Data load from Source to BW:
In the Source I have the data like below:
For one of the company the total net Sales is present as 675772,43 CLP (Chilean Peso) in the source system for the period Jan’12.
When the data is loaded into BW:
The data in the Source and in the Cube is matching each other.
Data reported in the query looks like below:
The data in the query gets multiplied by 100 which results in 67577243.00 CLP this is due to the fact that when we execute the query, the query looks at the
possible entry of the the currency in the table TCURX. If the entry is available as 0, then it will multiply by 100 as normally as per SAP, all Currencies are stored with two decimal places.
This behavior is due to the fact that the Query always looks at the TCURX table while execution and if there is an entry in TCURX then it will be considered as below:
[Amount Value] * [10 ** (2- (CURRDEC entry in TCURX table))]
In present case as the entry is marked as 0 in TCURX table the result will be like below:
= Amount*10**(2-0) = Amount*10**2 = Amount * 100
This will be corrected as below:
All amount related entries at the data source level should be marked to ‘External Format’.
Earlier the same was like below with the internal format.
In 3.x we have the option to choose the same at the infopackage level with ‘Currency Conversion for External Systems‘:
The above change in the data source will provide the below results:
During data loads:
When we load the data into BW having amount fields in it, it checks the TCURX table for the currency entry. If the currency entry is present in TCURX table, it divides the amount value by 10** (2- (CURRDEC entry in TCURX table)).
The exactly reverse happens while displaying such values in the report output. The amount value will be multiplied by 10** (2- (CURRDEC entry in TCURX table)) while displaying in the report output.
One of below example shows the results as below from the flat file when the format is changed to External.
From Flat file:
Sample content from Flat file
In the Cube:
Output from the Cube for the same entry
Values are divided by 100 as for Currency IDR the entry is marked as ‘ZERO’ in TCURX table for number of Decimal places.
In the Query:
Out of the Query looks like below:
Values the source value matches with the target value where the values of the cube get multiplied by 100.
Points to be noted:
- This procedure is only with the amounts having CURR data type.
- If the amounts are used as FLTP type then this issue can also be avoided.
- All the currencies having entry other than 2 in the field CURRDEC of table TCURX will be affected by this phenomenon.
- The division will be carried out at the time of loading while multiplication will be carried out at the time of report output display.
- Mainly this needs to be considered when dealing with the Flat file data loads which holds the currencies and for the ECC to BW the respective customization needs to be considered into account in the source system side.