SAP B1 Total invoice report including credit memos

[tats-olivo]

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 !!

[azizelmir · Accepted solution]

Yes Sure!

SELECT 
		T0.CardCode,
		T0.CardName, 
		SUM (T0.DocTotal) - SUM (T0.VatSum) As 'Net Total', 
		SUM (T0.DocTotal) AS 'Gross Total', 
		T5.Country, 
		T3.GroupName,
		(select SUM(Q1.DocTotal) from ORIN Q1 where Q1.Cardcode=t0.CardCode AND (Q1.DocDate>='[%0]' AND Q1.DocDate<='[%1]')) As 'Debit Notes Total'
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, T0.CardCode

[tats-olivo · Accepted solution]

Awww you're the best !! Thank you so much ! x

[azizelmir]

Hi Tatiana,

You can test this Query:

SELECT 
		T0.CardName, 
		SUM (T0.DocTotal) - SUM (T0.VatSum) As 'Net Total', 
		SUM (T0.DocTotal) AS 'Gross Total', 
		T5.Country, 
		T3.GroupName,
		(select SUM(Q1.DocTotal) from ORIN Q1 where Q1.Cardcode=t0.CardCode AND (Q1.DocDate>='[%0]' AND Q1.DocDate<='[%1]')) As 'Debit Notes Total'
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, T0.CardCode

Thank you,

Aziz

[tats-olivo]

Hi Aziz !

Thank you so much! It worked... is there any chance I could bring the CardCode too?