Technical Articles
Dynamic Handling of Decimal Places for Amounts
Authors: Vipul Khullar & Aayush Chawla
Introduction
Currency in SAP is largely handled extensively with the currency functionality and TCURC and TCURX tables. However, every now and then we come across a requirement where we need to extend this to accommodate other requirements.
In this blog we would like to share our experience of a requirement we came across in our project where the number of decimal places for a currency needed to be increased in order to attain more precision.
The requirement for this extension is usually when the user needs more precision in calculations based on an amount. This can be a rate charged per unit quantity, this rate (with higher decimal places) when multiplied with a large total quantity ends up giving a huge difference in net amount (as compared to normal currency of USD/ JPY etc.)
Thus, to cater to this requirement we provided the user with an option to set the number of decimal places dynamically at run time without changing the currency from the standard ISO currencies.
Problem Statement:
Suppose that the customer requirement is to use up to 5 decimal places for a ‘RATE’ field for all currencies irrespective of the total number of decimal places allowed for the currency as per the TCURC and TCURX tables.
The user can choose the number of decimal places for a currency based on the scenario and all the subsequent calculations for that scenario will take place based on the chosen number of decimal places.
The challenge here is that ‘RATE’ field is always associated to a reference ‘CURRENCY’ field in the structure/table. This reference puts a stringent check on the number of decimal places allowed for a specific currency. This number is derived from the TCURX and TCURC table and the customer is bound to use the same decimal places for the currency for all the scenarios.
Although there is a standard approach for maintaining different decimal places for the same currency using the TCURC and TCURX tables, but with the standard approach the user has to do so for all the currencies in use. In case we need to use 20 Currencies with up to 5 decimal place, the user will have to maintain 100 entries in both the tables.
Also, with the standard approach we would see a different currency such as USDN with the different decimal places. Although this USDN would in turn be linked to the ISO currency( USD in this case), the user will see USDN instead of USD on the UI which is not always desirable.
Solution Approach:
Our solution approach (described below) intends to reduce the manual effort in maintaining several records for different currencies in TCURC and TCURCX table. Also, the end user always sees a consistent currency on the UI irrespective of the number of decimal places. (no nuances such as ‘USDN’ etc).
Suppose that the database table/structure contains two fields ‘RATE’ and ‘CURRENCY’ (as a reference currency field for the ‘RATE’ field)
The following changes must be made to the database table:
- Create a new field ‘CURRENCY_EXT’ with the data type as ‘WAERS’ in the same table/structure.
2. Add the CURRRENCY field as a REFERENCE FIELD for the ‘RATE’ field.
3. Do the same process of referencing the CURRENCY field and adding CURRENCY_EXT field in all relevant structures.
4. We would now populate the CURRENCY field with dummy currencies which have a decimal value as per our requirement.
- These dummy currencies can be fetched from a customizing based on the number of decimal places, or in case the requirement is for a fixed decimal place it can be filled directly.
- These Dummy currencies must be maintained by the customer in TCURX and TCURC tables. The procedure for the same is explained below.
- The method GET_CURRENCY_DECIMALS is used to fetch the currency by reading from the customizing that we have maintained and then fetching the details from the TCURX and TCURC tables.
- Note: We check the TCURX table to just to ensure that the currency maintained in the customizing is also maintained in the standard currency tables.
- At all places on the UI we will display the CURRENCY_EXT field to the end user. Whereas the CURRENCY field would be hidden from display. This in turn would display the rate in the required decimal format as the CURRENCY field stores the internal currency and is referenced to the rate.
Example:
2 Decimals
3-Decimals
5-Decimal
Conclusion:
The need for varying decimal places for a currency in ABAP can be handled with help of standard TCURC and TCURX tables.
However, the approach explained above is useful when there is a need for a consistent user experience with regards to the currency with varying decimal places. Also, this approach reduces the manual effort for maintenance of records in TCURC and TCURX tables for separate currencies for different decimal places.
A few points to note:
- We must reference the CURRENCY_INT field to the rate in all structures and not just in UI structures. This is to ensure that the calculations are done with the right decimal place.
- For currency conversion calculations we would still use CURRENCY field as it contains the ISO currencies as maintained by the end user.
So at a guess you are using this for the decimal rates that can't be calculated. It would seem to me that you would be "modifying" SAP tables and structures. Am I wrong? You would have to do that so standard SAP code would pick up the correct number of decimal places - correct?
Something like the following would not need to be changed.
Thanks in advance to the clarification.
Hi Michelle,
Thanks for your comment.
There is no need to change the standard tables.
We finally convert the total amount calculated to the allowed number of decimal places for the ISO currency, and then update the standard tables.
eg.
Allowed number of decimal places for USD is 2 as per the TCURC and TCURX table.
Now, for a particular scenario, the required number of decimal places are 3.
User enters the value 33.333 USD per 1 kg for the rate field.
The quantity is 5555 kg.
Now the total amount comes out to be: rate*quantity = 185164.815 USD
Now before storing this value in the standard tables, we will convert this value to 185164.82 USD ( since only 2 decimal places are allowed for USD )
Had the user been allowed only 2 decimal places, the user would have entered 33.33 USD per 1 kg in the rate field.
Now, the total amount comes out to be 185148.15 USD and the same is stored in standard tables.
There is a clear difference of 16.67 USD in the two values.
Does this solve your query?
It sure does. Interesting way of doing things. One of the 1000s of ways of doing things. Different perspective and great blog. I love reading the comments.
How you handle currency conversion between currencies? say USD to EURO and still consider all decimal places in the conversion? are you able to use all the decimal places in the standard currency conversion function modules?.
Does the enhanced method applies to all standard functionalities?
How do you store an amount in the EKPO/VBAP tables that need to be expressed with higher decimal precision?
Hi Alejandro,
Thanks for your comment.
As discussed already in the blog in the section ‘A few points to note’, for currency conversion calculations we would still use CURRENCY field as it contains the ISO currencies as maintained by the end user.
This method is only for solving the problem of decimal places, however, we would still use the standard ISO currency for the other standard functionality. ( We already have that stored as a separate currency field )
We finally convert the total amount calculated to the allowed number of decimal places for the ISO currency, and then update the standard tables.
eg.
Allowed number of decimal places for USD is 2 as per the TCURC and TCURX table.
Now, for a particular scenario, the required number of decimal places are 3.
User enters the value 33.333 USD per 1 kg for the rate field.
The quantity is 5555 kg.
Now the total amount comes out to be: rate*quantity = 185164.815 USD
Now before storing this value in the standard tables, we will convert this value to 185164.82 USD ( since only 2 decimal places are allowed for USD )
Had the user been allowed only 2 decimal places, the user would have entered 33.33 USD per 1 kg in the rate field.
Now, the total amount comes out to be 185148.15 USD and the same is stored in standard tables.
There is a clear difference of 16.67 USD in the two values.
Maybe I'm missing something but it seems that in this case a regular numeric field with decimals could've been used. Why even use amount/currency type in declaration?
It's also not clear what business scenario exactly would require this. It says it could be a calculated value, e.g. "per unit". But normally we don't store such values in DB, those are calculated as needed. So if you had, say, an ALV report you'd just add a numeric field with no currency reference and then a separate currency column, problem solved.
Sorry, I don't get it.
Hi Jelena,
Thanks for your comment.
We did consider going ahead with the approach you mentioned above, however, there is the following drawback with same
Decimal validations:
Changing the field type to numeric would remove all the validations with respect to the decimal places. We require the decimal places to be dynamic, however we still want it to be same for a particular scenario. Removing the decimal places validation completely would mean that all the checks that are provided by standard SAP for decimal places would have to be re-written for the numeric field.
Example:
In master data the decimal places are set as follows:
Scenario 1 (for company code: abc): the decimal places should always be set as 2 for USD.
Scenario 2 (for company code: xyz): the decimal places should always be set as 3 for USD.
So, now if use a numeric field, there is no standard validation available to check that the number of decimal places are always 2 for scenario 1 and always 3 for scenario 2.
It seems to me that you are simply reinventing an existing "functionality" in many modules, which is the price per number of units. No need to increase the decimal precision of the price, just define a factor for the number of units, i.e. price 10.99 USD for 10 units, or to increase the precision 109.92 USD for 100 units. The only thing you have to do is during calculations, you'll have to divide the final unit price by the number of units. Please give a real example, with more useful prices than the current ones, that would help people to understand better what you're trying to achieve.
Hi Sandra,
Thanks for your comment.
We had already discussed this approach with our customers and found that although it logically seems to be correct, it is not user friendly.
We are expecting the user to enter the rate manually, so it will be a cumbersome task for the user to do the prior calculations ( multiply/divide by a factor of 10*n ) before updating the price/rate field.
Scenario 1 (for company code: abc): the decimal places should always be set as 2 for USD.
Here, the user is only allowed to enter 2 decimal places for USD -> 33.33 is an acceptable value, however 33.333 is not acceptable. This will be a standard check.
Scenario 2 (for company code: xyz): the decimal places should always be set as 3 for USD
Here, the value 33.33 entered by the user would automatically become 33.330 and 33.333 is also an acceptable value. However, 33.3333 is not applicable.
Aha! That's it. It's a business customer requirement. I can see someone asking for this. I'm not sure it would be approved to do.
Unfortunately this does not really offer a better explanation. What is the business purpose of such requirement? What exactly was the customer trying to achieve?
Since we are talking about the actual money here, obviously it's not possible to have a real amount with more than 2 decimals. If there are items that have such small price per unit then, as Sandra said, they really need to be priced per 10 or other multiples. E.g. previously I worked for a company that also sold very small items like nuts and bolts and they were priced per 100. This pricing has never come up as an issue and that company had a lot of odd requirements otherwise.
Even if you price something at 3.333 per piece then how are you going to sell it? Obviously you can't sell 1 unit for 3.333 because such amount does not exist in real life. Customers can't pay a bill with 3 decimals. IRS does not require financial reports with 3 decimals (tax declarations are actually rounded to a nearest dollar).
Sorry but I'm having trouble envisioning a valid business case for this. "User convenience", while understandable, can't be the sole driver behind such changes. Why not just create a program that would take 3.333 input from the user and recalculate it as 33.33 per 10 items? Now that would be a good application of ABAP that fulfills SAP's mandate "keep the Core clean" and offers something helpful to the users.
Hi Jelena
In the mining industry, upt to 5 decimal places are often necessary to put value on the raw ores/metals.
Copper in the LME (London) the price is defined as USD per Metric Ton, but on COMEX is USC per Pound, and still the raw copper concentrate is sold as MT (and in very large quantities).
Often copper also has a bit of gold and silver attached to it, and those are sold in Troy Ounce and Grams respectively. Since there are plenty of unit conversions, losing decimal precision often can lead to very noticeable deviations.
So managing higher decimal places is a requirement in such business transactions, currently the proposal for SAP is to create parallel currency types that handle 5 decimals, but often fall short when dealing with very large quantities (such as 30.000 mt tons of ore being sold). Not sure the explanation here solves the issue, but sounds promising.
Well, this definitely sounds like a valid use case. That's why we keep pushing OP for more explanation. 🙂
I'm guessing SAP would need to offer some better solution for this since, as you've mentioned in the comment above, many standard tables can't handle more decimals. Curious if this is somehow solved in S4.
Thanks for the comment!