Skip to Content

Applies to:

This applies to SAP Business Warehouse/Business Intelligence. For more information, visit the EDW homepage.

 

Summary

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

Author Bio:

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.

Overview

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:

/wp-content/uploads/2012/07/1_116350.jpg

Details of the Currencies

3.JPG

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.

  2.JPG

When the data is loaded into BW:

  4.JPG

The data in the Source and in the Cube is matching each other.

Data reported in the query looks like below:

5.JPG

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.

 

Solution:

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’.

7.JPG

Earlier the same was like below with the internal format.

6.JPG

In 3.x we have the option to choose the same at the infopackage level with ‘Currency Conversion for External Systems‘:

11.JPG

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)).

Query Output:

 

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

8.JPG

 

In the Cube:

Output from the Cube for the same entry

9.JPG

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:

10.JPG

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.

References:

http://help.sap.com/saphelp_nw70/helpdata/en/9f/dba1ef35c111d1829f0000e829fbfe/content.htm

http://help.sap.com/saphelp_tm80/helpdata/en/25/a2813aa3872b69e10000000a114084/content.htm

To report this post you need to login first.

37 Comments

You must be Logged on to comment or reply to a post.

  1. VICTOR ERNESTO MACHUCA RIVERA

    I’m working with BW 7.0 and this option is not available.

    Seems that this procedure only works on datasources base on the 3.x schema and not on the RSDS used by bw 7.0.

    what am I doing wrong? I’m having this problem with currencies from centeramerica, all the amounts are being divided by 10 on the final query.

    (0) 
    1. Murali M Post author

      at the data source level you should be able to see the same to choose the option either to use the external or internal. It works both in 3.x and also in 7.x version. Do let me know if you have the issue further!!

      (0) 
  2. Srinivas V

    Good post.

    Do you know how it works when currencies such as IDR are loaded from a external BW system to a BW system using 7.0 version data source? Does the values are still multiplied by 100 when data is loaded to PSA? assuming that the entry in TCURX table is available in both the systems as “0”.

    (0) 
    1. Murali M Post author

      Thanks.

      On your question, then consider using the ‘Internal Format’ so that the same data will be loaded to BW again and at the query level it behaves likes the old BW reports. Output as per the entries from the TCURX table.

      (0) 
  3. Manna Das

    In most of the BI projects we face this currency handling issues. This post will definitely help BI folks. Thanks for sharing this.

    (0) 
  4. P K

    Hi Murali,

    Thank you for sharing this information.

    In my case in BI7 (RSDS data source).

    There is data coming from R3 into one DSO from from different different data sources (Sales, Billing, Purchasing etc)

    Here Reporting data is not matching with R3 data.

    For Ex:

    R3 value:71.230,00 KWD

    Cube value:71.230,00 KWD

    Report value: 7.123,000 KWD


    BW report is showing discrepancy for KWD, CLP (TCURX) currencies for Actual amount in LC key figure.

    Few data sources are standard like LIS and few are Z data sources. Can you please explain how to handle fix here?

    In your document you have mentioned that ‘for the ECC to BW the respective customization needs to be considered into account in the source system side.‘ So do we have to ask R3 team to fix this issue for exceptional currencies on R3 side. And any particular information that we will have to share with R3 team to get this corrected?

    Please share you inputs.

    Thank You.

    (0) 
    1. Murali M Post author

      Hi.. Please check whether in BW side at data source level for this Currency the external is applied or not?

      If the external format is not applied then apply the same and then validate the results.

      Regards

      Murali

      (0) 
      1. P K

        Hi Murali,

        Thank you for your reply. Standard data sources also need to change for key fields of type amount. And for give 3-4 data sources if we change all key figures of type amount to External format, does it going to have any negative impact on data loads?

        On R3 side we can not make any changes to handle this situation?

        Thank You.

        (0) 
        1. Murali M Post author

          There will not be any negative impact on the data loads. But what you need to make sure is that this effect has to be reflected to the entire data set present in the targets.

          So in BW at datasource section, you need to change the option to External format so that the required calculations are reflected for the currencies where the decimals are marked as Zero.

          (0) 
          1. P K

            Hi Murali,

            As I have already mentioned above in my case there is data coming from R3 into BW which we called as FACT data. And at the same time data is coming into BW from Kewill through flat file (3rd part tool) which in turn take data from R3. So finally BW we compare both data i.e. data from Kewill and data from FACT and ideally they should match because for both source is R3 only. But I am getting miss match in exception currency values from Kewill and FACT. (As per your document for TCURX currencies data source key fields should be in “External Format” )

            1. 1. Initially I thought this could be issue at Kewill end and key fields in BW Kewill flat file data source need to change to “External” format. But found they were already set to External format. Hence no changes expected on Kewill end.
            2. 2. Then it looks like issue was at FACT end, because key field used to load data in BW FACT did not have “External” format set.
            3. 3. But when I have checked miss match data after few days, I found most of the mismatch errors were resolved. BW FACT values were remained unchanged but kewill values were changed and they matched with FACT data, see below:

            Initially see values marked in red are reported as mismatch in FACT and kewill: “1” in the last column shows miss match. If no miss match it shows “0”.

            /wp-content/uploads/2014/04/1_427513.jpg

            Again I have refreshed same values after few days and out of 11 9 miss matches were fixed.

            Kewill values were changed and they matched with FACT value. Whereas Kewill data source alredy have External Format set for key figures. And in FACT for ‘Z’ key figures External format is not set:

            /wp-content/uploads/2014/04/2_427550.jpg

            Below is one more example where one job is matching and rest 3 are not:

            Could you please help to share your inputs on this miss match. From above It does not looks like an issue with TCURX currencies.


            Thank you!

            /wp-content/uploads/2014/04/3_427551.jpg




            (0) 
            1. Murali M Post author

              Hi PK,

              I see that at the initial stage you can see that the values are multiplied with 100 which has actually resulted in the gap.

              I do not know what has made the correction but I can prefer to do the selective deletion and selective upload for the mismatched records which might correct the issue.

              Please validate the source data and then check the results in the target post updates.

              Regards

              Murali

              (0) 
              1. P K

                Hi Murali,

                Thank you for your reply.

                Do you mean selective deletion and reload at R3 -> BW FACT load or at Flat file -> BW load?

                (0) 
                1. Murali M Post author

                  This has to be done in BW and then reload the data from the respective source from where the documents are posted earlier.

                  (0) 
  5. Abhishek Bhattacharya

    Hi Murali ,

    Thanks for this very informative document .

    Can you please tell me that is there any relationship between TCURX table and TCURF table.

    If there is no relationship betweenn them, why is table TCURF used for ?

    Thanks in advance.

    Abhishek bhattacharya

    (0) 
    1. Murali M Post author

      TCURX is used to specify the Decimal pointers to the currencies where as TCURF is used to specify the factors with which the currency has to multiplied…

      (0) 
  6. sridevisaranya K

    Hi Murali,

    You mean to say, if we select Internal as a format in datasource then only it will look into TCURX table and populate the result during data load as “Value = actual value / ( 10 ^ (2 – CURRDEC) )” else (for external) it will populate as it is from ECC ?

    (0) 
      1. sridevisaranya K

        Hi Murali,

        Thanks for your reply.

        Could you please answer my below query,

        Say eg: my condition data is mainatined in ECC as 2000 Amount for Currency IDR. In condition table KONP itself I could see the data is divided by 100. So it is displaying as 20.00 in ECC table KONP.

        While extracting to BW, the same data (20.00 IDR) is available in PSA.

        I could see the logic is getting different between dataload to Cube & DSO. While loading to DSO the data is getting multiplied by 100 (2000 IDR). But for the cube it is matching with the table and it display (20.00 IDR).

        Also I have noticed if I view the data of DSO in Active table it is showing as (2000 IDR) and when I am checking in LISTCUBE t-code it is (20.00 IDR).

        Kindly provide your input on this.

        Thanks in advance…

        Sridevi.

        (0) 
  7. Naresh K

    Hello Murali,

    Thanks for Nice Document.

    Do we need clear the PSA in test system before sending the change to target system (test)?.

    (0) 
  8. Srinivas V

    Good document. We had this issue in our flat file upload to a direct update dso without a flat file data source. We handled it in the routine to store the decimal places based on the TCURX table.

    (0) 

Leave a Reply