CDS VIEW ENTITY - CURR / QUAN calculated field gives activation error

[bharatbajaj]

Dear community members,

I need to create a CDS view entity with a calculated filed as CURR / QUAN to get the per unit amount.

For Example: CDS VIEW ENTITY on BSEG which should have ( DMBTR(type CURR) / MENGE(type QUAN) ) AS UNIT_PRICE

Sounds like a very simple requirement but I am facing multiple issue with this.

To start with, I get below error message:

"Data type CURR is not supported at this position, see long text"

then I tried with casting CURR field as DEC(23.2), but then I got below error :

"Element <XXXXX> : missing CALC-reference annotation Semantics.quantity.unitOfMeasure"

it was surprising to see that it prompted to user UOM reference, instead of Currency reference, but nevertheless, I tried with the UoM reference, but then again a different error :

"<XXXXX>: Reference Field MEINS has a wrong data type"

then I tried to CAST the MEINS as abap.char(3) to use it as reference for calculated field, but then a different error this time, and this is really annoying this it didn't allow casting a text value into CHAR.

I also tried to google this error message and found some conversion function but then I get different errors:

1. Using function : GET_NUMERIC_VALUE

"Element DMBTR has an invalid reference field"

2. using function : CURR_TO_DECFLOAT_AMOUNT 

"Element DMBTR has an invalid reference field"

I tried few more ways but none of them is working for me and I really wonder how can this be so challanging to achieve AMOUNT / QUANTITY in CDS VIEW ENTITY, which is a very basic arithmetic operation.

Same arithmetic calculation works perfectly well in ABAP code (as we have done that for decades), so now I am thinking to use Virtual elememt in CDS, but I want to achieve with the CDS approach first, without making use of ABAP layer.

Any tips to solve this ?

regards,

Bharat Bajaj

[bharatbajaj · Accepted solution]

So I have finally managed to solve this problem by using the CAST expressions on both the AMOUNT and QUANTITY fields. That ofcouse needs the refernece to the Currency Code as well.

Here is the correct CAST expression I used to achieve the Amount per Unit:

@Semantics.amount.currencyCode: 'rfccur'
cast( cast(dmbtr as abap.dec( 23, 2 )) / menge as abap.dec( 23, 2 )) as dmbtr_per_menge

Explanation :

1. The DMBTR is of type predefined data type CURR (length 23, decimals 2)
2. I have used CAST expression to convert DMBTR into normal DEC type with same length and decimals
3. Then I used this in an arithemetic expression with the Quantity field MENGE
4. Then I used another CAST expression on this overall calculation to get the final amount
5. For the currency I was able to use the existing currency field RFCCUR and no separate calculated unit was needed

Note : if I didn't use the 2nd CAST expression (in point 4 above), then I would be getting the below error :

That could of course be resolved using the CalculatedUnit as reference, but I preferred not to use that option.

Here you can see the Active CDS view using this expression and the final output:

I found this approach much simpler and cleaner, compared to using the other CDS Functions and the CalculatedUnit, which anyway didn't work for me.

Hope this would help others who have similar requirement.

Regards,

Bharat Bajaj

[bheemeshRao]

Hello @bharatbajaj  ,

if you want to use View entity seems like the only solution is to have a calculated column for each computation.

Alternatively, if its allowed then use define view instead of view entity.

Define view would not require semantic.amount.currency code annotation

[bharatbajaj]

Hi Bheemesh,

Thanks for the suggestion. I tried this, but this gives amount in FLTP, with large number of decimals.

I needed the amount in 2 decimals, so I used the abap.dec(23,2) in the cast expression, instead of abap.fltp

that worked well and you can see that in my other comment which I marked as solution.

[Pradeep_Reddy]

HI,

Could you please try with the below.

@Semantics.quantity.unitOfMeasure: 'rfccr'

CURR_TO_DECFLOAT_AMOUNT( bseg.dmbtr  ) * GET_NUMERIC_VALUE( menge ) as dmbtr_per_menge.

Regards,

Pradeep.

[bharatbajaj]

Hi Pradeep,

I tried that too.. but I get the below error :

However, I have found a simpler solution, which I will post as a seprate comment to my original post.

Regards,

Bharat

[Pradeep_Reddy]

Could you please change the annotation from @Semantics.quantity.unitOfMeasure: 'rfccr' to @Semantics.quantity.unitOfMeasure: 'rfccur'

[RaymondGiuseppi]

Did you look for a blog such as ABAP CDS Cheat Sheet: Amounts and Quantities in ABAP CDS or online help such as CDS DDL - DDIC-Based View, Unit and Currency Conversion Functions ?

[bharatbajaj]

Yes I did already. and I also tried some of those and you can see the issue that I still got in my original post, as well as the comment I made on Martin's Reply below.

[RaymondGiuseppi]

NB: You will require the currency codes in the view: bkpf-waers and t001-waers

NB: field rfccur is related to amount fcsl, not dmbtr or wrbtr

[bharatbajaj]

Hi Raymond,

The field rfccur is a currency fields which can be used as a reference for any Amount field in BSEG (type CURR) such as DMBTR, WRBTR etc.

[MKreitlein]

Hello @bharatbajaj 

In S/4HANA you can find a Demo Package in the package tree with hundreds of CDS View example.

You can check it out here... I found this example. Maybe it helps.

BR, Martin

[bharatbajaj]

Hi Martin,

Thanks for sharing this, I already tried this approach of using  CURR_TO_DECFLOAT_AMOUNT, but then I get annoying error :

Also, I tried another apporach by casting DMBTR as Dec(23,2), that finally worked:

However, now I have another problem. I want to do the same for another amount field WRBTR, and i want to use the same 'calculatedUnit' for this fields as well. but then it cries that the 'calculatedUnit' can be used only once (see below). This is illogical in my opinion as why would the system not allow to use a unit as reference for multiple fields?

That means, for each Amount field calculation, I need a separate calculatedUnit (having the same or different value).

(honestly I don't understand why we even need this calculatedUnit and can't use the CURR reference instead, as the calculated value (dmbtr_per_menge) is an amount and not a quantity)

Just to emphasis the challange here, my real use case it to convert atleast 15 cost fields from KEPH table (say KST001...KST015 ) to get per unit cost for materials in business report. So with this approach, I need to create 15 'calculatedUnit' fields in the CDS having the same value (say EUR / KG).

This is not CLEAN ABAP in my opinion, and I am still looking for a better and simpler way of achieving this.

Regards,

Bharat Bajaj