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

[Former Member]

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 · Accepted solution]

Hi,

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]

[frank_wang6]

OINV ,and ORIN

basically u will need to use sql UNION to combine the result.

Frank

[Former Member]

Hi sir Wang

Can you give me a sample code of UNION to combine the result of query

Thank you Best regards.

[Former Member]

Hi sir Balaji

How can i link Open Qty in INV1 table?

Thank you best regards.

[former_member188586]

Hi

use this Condition ...

OINV.[DocStatus] ='O'

[former_member205766]

Hi Erwin

Link Open Qty in INV1 table then check the query result.

With Regards

Balaji Sampath