ABAP CDS VIEW TO FETCH SUM OF QTY FROM MSEG TABLE FOR ALL ITEMS WITH RESPECT TO GIVEN INPUT

[govardan_raj]

Hi Team,

I have a requirement to fetch the summation of Quantity from mseg table, menge is the field for quantity , shkzg is the field considered for Credit / Debit i.e., if it is "H" then we consider that item as Credit and if it is "S" then it is Debit. Please find the below 

define view ZTEST_MSEG_VIEWNAME as select from mseg 
 
{
key matnr as Matnr,
werks as Werks,
lgort   as Lgort,
menge as Quantity,
shkzg as CreditDebitInd,
count(*)   as totalCount,
case 
when shkzg = 'H' then (menge * 1)
when shkzg = 'S' then (menge * -1)
end as creditDebitQuantity
} 
where matnr = 'XXXXXXXXI.' and werks = 'XX4' and lgort = 'XXEN'  
group by matnr,werks,lgort,menge,shkzg

We created one more cds view to take the summation of the creditDebitQuantity as show below 

define view ZINV_MSEG_SUM_VIEWNAME as select from ZTEST_MSEG_VIEWNAME 

{ 
key  Matnr as Matnr,
key  Werks as Plant,
key  Lgort as Location,
 CreditDebitInd as CreditDebit,
sum(creditDebitQuantity ) as TOTAL_QTY
 
} 
where Matnr = 'XXXXXXXX.' and Werks = 'XX4' and Lgort = 'XXEN'

group by Matnr,Werks,Lgort,CreditDebitInd

on Executing the above we are getting summation of quantity as show below 

which shows that total Credit Quantity is 349 and Total Debit Quantity is 71 but this is not in sync with the summation count displayed at the MSEG Table in S/4 HANA SYSTEM. please find the below 

The sum of menge for items having value as "H" in column shkzg is 1065 and the sum of menge for items having value as "S" in column shkzg is 1075.  for given material, plant and lgort. 

please do the need full, as the ABAP CDS View should also display the same output as 1065 & 1075 , but instead it is displaying 71 & 349 respectively  

Thank you

[fedaros]

Hi @govardan_raj,

Running in SE16 you are automatic restricted to current client you logged in. Your CDS doesn't expose the mandt and maybe that is the difference you are facing. Add it and try again.

Regards, Fernando Da Rós