SAP B1 How to Combined Query of AR Invoice and AR Credit Memo
I'am trying to combined query of AR Invoice and AR Credit Memo to less the item cancelled because when I generate my report of AR invoice all Invoice that link in AR Credit Memo are appearing in the report. Example in my AR invoice there are 5 qty of Monitor the customer cancelled the 3 of them, then i cancelled the 3 in the system so I'm expecting to see in my report is 2 qty only but when I generate a report from AR invoice 5 qty are appearing in the report.
Thank you & Best Regards.
Try this query
select v.DocNum ,v.DocDate ,v.CardCode ,v.CardName ,v1.ItemCode ,v1.Dscription ,v1.Quantity 'Invoice Qty' ,isnull(r1.Quantity,0) 'Credit Memo Qty'
,v1.Quantity -isnull(r1.Quantity,0) 'Balance Qty',v1.LineTotal 'Invoice Line Total'
,isnull(r1.LineTotal ,0)'Credit Memo Line Total', v1.LineTotal-isnull(r1.LineTotal ,0)'Balance Total'
from oinv v inner join inv1 v1 on v.DocEntry =v1.DocEntry left outer join RIN1 r1 on r1.BaseEntry =v1.DocEntry
and r1.BaseType='13' and r1.BaseLine=v1.LineNum
where v.CANCELED='N' and v.DocDate >=[%0] and v.DocDate <=[%1]