Technical Articles
Currency and Unit conversion in ABAP CDS views
Recently, I’ve been checking some examples of unit and currency conversions based on ABAP CDS views and I noticed is quite common to find people applying manual conversions in the formulas.
A simple example of a days to years conversion is conceived with a multiplication of the original value by 365, for more complex situations like an expansion of this same conversion from days to months and years a CASE statement is implemented to adjust the output of the value.
But what happens when you have conversion of an amount to different currencies or a unit conversion based on dynamic values?
In these cases is impossible to apply a fixed calculation in the code due to the variations of the currency or unit, but what most part of the consultants don’t know is that ABAP CDS views provide support to set of functions conceived specifically for this kind of scenario:
- UNIT_CONVERSION
- CURRENCY_CONVERSION
You can find more information in the official documentation through the link below:
But how these functions work?
Unit conversion has 3 mandatory parameters + 2 optional parameters to support client verification and error handling. The specification is available below:
Formal Parameter | Optional | Data Type |
quantity | – | QUAN, DEC, INT1, INT2, INT4, FLTP |
source_unit | – | UNIT |
target_unit | – | UNIT |
client | X, – | CLNT |
error_handling | X | CHAR with length 20 |
Currency conversion has 4 mandatory parameters + 6 optional parameters to support fine adjustments in the output, client verification and error handling. The specification is available below:
Formal Parameter | Optional | Data Type |
amount | – | CURR |
source_currency | – | CUKY |
target_currency | – | CUKY |
exchange_rate_date | – | DATS |
exchange_rate_type | X | CHAR with length 4 |
client | X, – | CLNT |
round | X | CHAR |
decimal_shift | X | CHAR |
decimal_shift_back | X | CHAR |
error_handling | X | CHAR with length 20 |
In this short post we are going to create a demo exploring both functions based on two different entities from the Flights demo tables:
- SAPLANE
- SFLIGHT
In the diagram above you can find the connection between both tables. Notice that SAPLANE holds the data of the planes and SFLIGHT stores data of the flights.
Example #1: Unit Conversion
For the unit conversion we will use one of the fields from SAPLANE table.
There are 3 valid options based on the Currency/Quantity Fields:
For this example, I am using a conversion based on Maximum fuel capacity (TANKCAP). The objective is to demonstrate multiple conversions based on the same dimension.
To check the valid options to convert this field you can open the table T006 and search units based on the Volume dimension.
Checking the data available in the table we can notice TANKCAP is usually referred by Liters (L). Let’s try to create conversions to Cubic Meters (M3) and Cubic Yards (YD3) via this standard function.
The code and delivery are pretty simple, create a new Data Definition via HANA Studio with the name of ZCDS_UNIT_CONVERSION and include the following code.
@AbapCatalog.sqlViewName: 'ZCDSUNITCONV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS Demo by Felipe Rodrigues'
define view ZCDS_UNIT_CONVERSION
as select from saplane
{
@EndUserText.label: 'Plane Type'
key planetype as PlaneType,
@EndUserText.label: 'Tank Cap'
tankcap as TankCapacity,
@EndUserText.label: 'Tank Cap (UOM)'
cap_unit as CapacityUnit,
@EndUserText.label: 'Tank Cap (in Cubic Meters)'
unit_conversion(
quantity => tankcap,
source_unit => cap_unit,
target_unit => cast('M3' as abap.unit)
) as TankCapacityInM3,
@EndUserText.label: 'Tank Cap (in Cubic Yard)'
unit_conversion(
quantity => tankcap,
source_unit => cap_unit,
target_unit => cast('YD3' as abap.unit)
) as TankCapacityInYD3
}
Run the Data Preview and check the result:
Let’s re-conciliate the data from the first record via google assistant.
Liters (L) to Cubic Meters (M3)
Liters (L) to Cubic Yards (YD3)
Important Note: Due to a small difference in the number of decimal places in the conversion factor used by SAP and Google you can notice also a small difference between the results, just remember the conversion factor can be adjusted in SAP depending on your needs.
Example #2: Currency Conversion
For the currency conversion we are going to use some of the fields from SFLIGHT table.
For this example, I am using Airfare (PRICE) and converting the value to Australian Dollars (AUD).
This is a pretty interesting scenario because the prices usually vary depending depending on the flight and company, with the standard function we are able to convert the values dynamically.
To achieve this result let’s create a new Data Definition via HANA Studio with the name of ZCDS_CURRENCY_CONVERSION and include the following code.
@AbapCatalog.sqlViewName: 'ZCDSCURRCONV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS Demo by Felipe Rodrigues'
define view ZCDS_CURRENCY_CONVERSION
as select from sflight
{
@EndUserText.label: 'Airline Code'
key carrid as AirlineCode,
@EndUserText.label: 'Connection No.'
key connid as ConnectionNumber,
@EndUserText.label: 'Flight Date'
key fldate as FlightDate,
@EndUserText.label: 'Price'
price as Price,
@EndUserText.label: 'Currency'
currency as Currency,
@EndUserText.label: 'Price (in Australian Dollars)'
currency_conversion(
amount => price,
source_currency => currency,
target_currency => cast('AUD' as abap.cuky),
exchange_rate_date => fldate
) as PriceInAUD
}
Notice the CURRENCY_CONVERSION has a mandatory parameter named EXCHANGE_RATE_DATE, for this scenario we need to assign the Flight Date for the exchange date, the system is going to analyse the options available in the table TCURR automatically and provide the proper conversion rate based on the date the customer acquired the flight ticket.
Run the Data Preview and check the result below:
Important Note: The accuracy of your currency conversion will depend on how often you update the Exchange Rate in your SAP system. In my case the conversion is not accurate because this data is from a development environment but if you have updated conversion rates in your system you will find the proper values in the output of this CDS view.
For more information about this topic you can find some quick explanation about Exchange Rates configuration in SAP through the following link:
And this is the end! I hope you enjoyed the content and see you next time. 🙂
Amazing as always my friend, this one is gonna be very usefull!
Hey Wagner Dias de Assis,
Hope everything is okay and thank you for the message my friend.
All the best!
Cheers,
Felipe
Nice blog, thanks for sharing!
Well formatted and a good explanation too.
Thank you for the good feedback, greatly appreciated 🙂
Well done Felipe !! Really appreciate the way , it is explained 🙂
Thanks mate!
hi Felipe de Mello Rodrigues,
thank you for sharing very useful information. i have some confusion can you please help me out.
how can i restrict no of decimal places in quantity converted (target) field to two decimal places?
The way you have used images to portray examples made me understand things easily. Looking forward for some more interesting information like this.
Currently, I am working with PureSoftware.com
I work with Arttha and the information seems really interesting to me.
Hi Felipe de Mello Rodrigues,
Do we have any blogs specifying the actual logic/code written for currency conversion inside CURRENCY_CONVERSION function?
Thank You.
Hi!
Thanks for sharing! Out of curiosity, is there any casting or conversion available for data element /SCWM/DE_MATID? it has a conversion routine MDLPD to have the material number and not the GUID, do you know if it's possible? I've tried with AMDP function or casting without success, thanks!
Hi Felipe,
Very simplified and crisp 🙂
How about dynamic currency conversion ? How do you handle it when a user wishes to enter the currency manually in Fiori reporting.
I see that you are using below :
Some standard apps uses P_currency for dynamic currency conversion. I am exploring on that area 🙂
Thanks
Tejas
Excellent POST
Thanks!
Jayanta
I just encountered an interesting pitfall. In my CDS, I tried the following lines:
However, the compiler tells me it does not know the field BillingCurrency!
It seems the CDS functions are processed before the resolution of the "as" aliases. (Or put another way: the mapping to aliases might be the last step.)
So for the code to work, I (unfortunately) need to duplicate the access path:
I tried using $projection but that does not seem to be suitable for this case.
Have you tried defining a view entity instead of a regular view?
This should allow nesting. But you need SAP 2020 or newer I think.
If not, then yes you are required to make many layered views for things such as calculations or field renaming etc.
Yes, I have tried this with view entities. (Actually, I have not tried it with an "old" CDS view.)