Skip to Content
0
Jul 17, 2019 at 08:59 AM

SAP B1 Total invoice report including credit memos

157 Views

Hi guys!

I already have a report that brings total invoice value (SUM) by BP and country, but I need to include any debit notes (SUM) into the same date range. Can someone help me joining the debit note total sum.

SELECT T0.CardName, SUM (T0.DocTotal) - SUM (T0.VatSum) As 'Net Total', SUM (T0.DocTotal) AS 'Gross Total', T5.Country, T3.GroupName
FROM OINV T0
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
INNER JOIN OUSR T4 ON T0.UserSign = T4.INTERNAL_K
INNER JOIN CRD1 T5 ON T2.CardCode = T5.CardCode and T2.BillToDef = T5.Address and T5.AdresType = 'B' WHERE T0.DocDate >= '[%0]' AND T0.DocDate <= '[%1]'
GROUP BY T0.CardName, T5.Country, T3.GroupName

Thank you !!