CDS Part 9. Usage of Built-In Functions in CDS – II

Core Data Service (CDS) is the game changer in SAP ABAP for HANA. We have been continuously updating our knowledge about CDS and sharing them with you all. In Part XXV we talked about the Built-In Functions in CDS. But, there has been a long gap and quite a considerable wait for the second part on Built-In Functions in CDS. We apologize for the delay and at the same time, we can guarantee a BLOCKBUSTER edition of insights with respect to the world of CDS.

Today we would cover:

1. Unit Conversion Functions
2. Currency Conversion Functions
3. Decimal Shift Functions
4. Date and Time Conversion Functions – which decide the fate of everything in the world, sometimes good and sometimes otherwise.

A. Unit Conversion Functions

Seldom we see these being used. But then application of knowledge should not be seldom.

Unit Conversion function has 3 parameters:

  • First parameter MUST and should be a quantity field.
  • Second and Third parameters MUST and should be unit fields.

Point to remember: Never run the CDS where there is absolutely NO relation between Source and Target Field. Being adventurous has it’s own risk. Here, DUMP.

Code Snippet:

<code>@AbapCatalog.sqlViewName: 'ZUNIT_FN_V' 
@AbapCatalog.compiler.compareFilter: true 
@AbapCatalog.preserveKey: true 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
@EndUserText.label: 'Test Unit Funtions'
 define view ZUNIT_FN 
 as select distinct from mara as a
 {
    key a.matnr as Material,
     a.brgew as MatQuan,
     a.meins as SrcUnit,
     a.gewei as TgtUnit, 
     unit_conversion( quantity => brgew, 
                   source_unit => meins, 
                   target_unit => gewei ) as ConvF1
 } 
 where a.matnr > 'CH-6000'
   and a.matnr < 'CH-6600';</code>

Creating example was an uphill task.

Reason: Finding test data and executing the scenario was the most challenging part of this entire blog. I am glad and relived to overcome anxiety.

Output:

CDS View code fetching Material, corresponding unit for conversion and converted field.

Look at material CH-6020 where there is a true conversion (pound to kilogram) and CH-6220 where is there is none (why would there be a need for pound to pound conversion?).

Validation:

Google helps us do this.

B. Currency Conversion Functions:

As the name says, the currency of the amount/price field is converted from source (as stored at the database) currency to end user/input currency.

Pretty straight forward to implement. 4 parameters – Mark it “All are mandatory“. No escape.

Code Snippet:

In the above example do observe that there are two parameters namely, currency and conversion date.

Now, let’s give two target currencies. First USD and other INR.

There is some absurd and strange thing. Let’s apply some common sense. The exchange rates keep fluctuating as a pendulum of the clock. I gave a future exchange date (Note : 31-01-2019).

Astonishing as it may sound, SAP has been able to predict even the market exchange price for a FUTURE DATE (31-01-2019 on Jan 28, 2019) !!! Not sure how it is getting picked. Anyone who is aware please comment and let us and the rest of the world know.

Forgetting that bug, lets check the next set:

Exchange rates between EUR to INR on 01st Jan 2019 :

Go back to the previous picture. Now for very first record the exchange rate roughly translates to about 71.714. Verify against the above chart ( says – 79.724).

Author’s take: Take this function with a pinch of salt. For precision (till someone demystifies this) go with a custom code.

C. Decimal Shift:

Some background. For certain currencies like JPY, HUF, KRW, COP there is a difference how the external world stores value than internal.

In TCURX table, JPY is set with 0 decimal. EUR doesn’t exist in the table, that is, default to 2 decimal.

Code Snippet:

<code>@AbapCatalog.sqlViewName: 'ZDEC_SHFT_V'
 @AbapCatalog.compiler.compareFilter: true
 @AbapCatalog.preserveKey: false
 @AccessControl.authorizationCheck: #NOT_REQUIRED
 @EndUserText.label: 'Test Currency Conversion'
 define view ZDEC_SHFT
 with parameters p_amt : abap.curr( 5, 2 )
 as select distinct 
 from sflight as a{
     key a.carrid as FlgID,
     key a.connid as FlgConID,
     key a.fldate as FlgDat,
     a.currency,
     decimal_shift( amount => :p_amt, currency => a.currency ) as DEC_SHFT
 }</code>

Question: How many parameters are there in the above DECIMAL_SHFIT Function?

From the above two pictures it is quite evident that the input : 21.34 was interpreted differently in accordance with the corresponding currencies.

We would like to end this article here. Yes, we know, we promised four points. For the last bullet point, we would prepare a detailed tutorial on Date and Time Functions in our next blog. So, please stay tuned.

Till then enjoy your new found love in the form of ABAP Programming for S/4HANA and tune back to us for some explanation or the curious currency conversion function.