Skip to Content
avatar image
Former Member

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 30, 2015 at 06:06 PM

    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]

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 29, 2015 at 08:35 AM

    Hi Erwin

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

    With Regards

    Balaji Sampath

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 29, 2015 at 11:17 PM

    OINV ,and ORIN

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

    Frank

    Add comment
    10|10000 characters needed characters exceeded