Technical Articles
CDS View: Add days to Date
Summary:
This blog is about how to use DATS_ADD_DAYS(date,days,on_error) function when “days” field is not of type INT4.
Scenario
As part of S/4 HANA Central Finance implementation project, we were converting one of the ECC ABAP program into CDS view and in the ABAP program there was a call to FM NET_DUE_DATE_GET which basically adds days to a date. Here is the calling syntax of this FM:
CALL FUNCTION ‘NET_DUE_DATE_GET’
EXPORTING
i_zfbdt = LS_BSID-ZFBDT
i_zbd1t = LS_BSID-ZBD1T
i_zbd2t = LS_BSID-ZBD2T
i_zbd3t = 0
i_shkzg = ‘ ‘
i_rebzg = ‘ ‘
i_koart = ‘D’
IMPORTING
e_faedt = Lv_due_date.
Ex-1: LS_BSID-ZFBDT = 13.08.2018, LS_BSID-ZBD1T = 14, LS_BSID-ZBD2T = 0.
Output: 27.08.2018
Ex-2: LS_BSID-ZFBDT = 13.08.2018, LS_BSID-ZBD1T = 14, LS_BSID-ZBD2T = 20.
Output: 02.09.2018 (if both i_zbd1t and i_zbd2t are passed then FM ignores i_zbd1t and uses i_zbd2t for calculation)
Solution
There is a standard CDS function which adds days to passed date DATS_ADD_DAYS(date,days,on_error), unfortunately in this case Days fields i.e. ZBD1T & ZBD2T are not of data type INT4 which is required data type for Date function, Data type of these fields are Dec(3). So if I try to use CDS function on these fields, I will get below error:
So to overcome this Data Type error we need to convert Dec to INT4 and the good thing is CDS has Cast functions which can be used to change the data type of a variable. Ex: cast(gross_amount as abap.fltp) but the not so good thing is Dec cannot be converted into INT4 directly. Well ‘directly’ is the key word here and I found out an indirect way of converting DEC to INT4.
I converted DEC->CHAR->NUMC->INT4 and below table by SAP helped in coming up with this route:
Link where you can find above table:
https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abencds_f1_cast_expression.htm
Here is the final CDS code:
Result:
If you have a better solution please share in the comment, suggestions are always welcomed!
BTW if you are looking for CDS date function then here is the link:
Thanks for the Tip!!
https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abencds_f1_sql_functions_numeric.htm
Check ceil function, it returns int4, it may save you a bit.
Thanks Jun! This works too and it indeed saved a lot of efforts:
New Syntex:
dats_add_days(zfbdt, ceil(zbd1t), ‘INITIAL’) as NEW_DATE
Cheers,
Abhishek
Excellent Blog, very useful!
Hi Abhishek Agrawal - Thank you very much for this excellent blogs. No words to express the happiness I had when I achieved this logic. CDS views are not my core skills but my recent projects working in SAC & CDS combo and struggling to get this payment due date and the example you explained is as it is matches my requirement.
Thanks to Jun Wu - learned many other functions through the link you shared.
Another logic I was struggling to get something is - Total amount * Discount / 100 this may be simple for core ABAPer but in my case in need to check if discount 2 is null then discout2 . below is the code which i used in separate CDS view then got it in Interface view. will explain why I put in separate CDS in next step.
******************************************************
dmbtr as amount,
zbd1p as discount,
zbd2p as discount2,
case
when zbd2p is null then
cast(dmbtr as abap.fltp) * (cast(zbd1p as abap.fltp) / 100.00)
else
cast(dmbtr as abap.fltp) * (cast(zbd2p as abap.fltp) / 100.00)
end as TotalDisc,
**********************************************
Above CASE statement is enough for my requirement however when try to use this CDS in SAC story it through a error that float / integers cannot be used in row or something else. So, that forced me to add in separate CDS and in Interface view added below statement.
fltp_to_dec(D.TotalDisc as abap.dec( 23, 2 )) as TotalDisc.
With this all good for my SAC story and I am done. There may be some other ways to get these logic but I struggled a lot to get in and this blog helped me a lot......so though of sharing my experience which may help others like me... Happy Learning!!!!