Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SAP B1 How to Combined Query of AR Invoice and AR Credit Memo

Hi Experts

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.

Former Member
Former Member replied


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]

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question