AR Invoices and Credit Memos cancellation

[former_member216196]

i am trying to get a query to display AR invoices and Credit note and the cancellations should appear as negative for invoice and positive for credit notes

currently i have this query.

select   distinct   T0.prjname ,T2.CardCode,T2.CardName ,
  'Invoice' as Description ,t2.DocDate,t2.DocNum,t2.JrnlMemo,t2.DocTotal*-1 as 'doc total' from oprj T0
inner join   INV1 T1  on  T0.PrjCode=  T1.project
inner join   OINV T2  on  T2.DocEntry=  T1.DocEntry
  union all
select  distinct   T0.prjname ,T2.CardCode,T2.CardName ,
  'Credit Note' as Description ,t2.DocDate,t2.DocNum,t2.JrnlMemo, t2.DocTotal   from oprj T0
inner join   RIN1 T1  on  T0.PrjCode=  T1.project
inner join   ORIN T2  on  T2.DocEntry=  T1.DocEntry

[Former Member · Accepted solution]

When you choose Cancel in a invoice, SAP B1 really go to create a new document. Then, when you click Add/Create button in document Windows, the original document is closed (DocStatus = 'C') and the new document is showed as Cancelled status (DocStatus = 'N').

So, you should modify your query, adding a CASE clause for numerical fields (doctotal, quantity, linetotal, etc.): when docstatus = 'N' then values is negative, and when 'C' then positive.

Regards,

JC.

[former_member216196 · Accepted solution]

hello Juan,

Can you kindly modify the Query please?

regards,

Joel

[former_member212181 · Accepted solution]

Hi Joel,

Please try below query and give your feedback.

--------------

select T0.prjname ,T2.CardCode,T2.CardName ,

  'Invoice' as Description ,t2.DocDate,t2.DocNum,t2.JrnlMemo

,Case When T2.Canceled <>'C' then  t2.DocTotal else  -t2.DocTotal end as 'doc total'

from OINV T2 

inner join INV1 T1  on  T2.DocEntry=  T1.DocEntry

Left Outer join OPRJ T0 on  T0.PrjCode=  T1.project

Where T2.DocDate >=[%0] and T2.DocDate <=[%1]

Group By  T0.prjname ,T2.CardCode,T2.CardName , t2.DocDate,t2.DocNum,t2.JrnlMemo,T2.Canceled,t2.DocTotal

union all

select  distinct   T0.prjname ,T2.CardCode,T2.CardName ,

  'Credit Note' as Description ,t2.DocDate,t2.DocNum,t2.JrnlMemo

,Case When T2.Canceled <>'C' then  -t2.DocTotal else  t2.DocTotal end as 'doc total'

From   ORIN T2 

inner join RIN1 T1  on  T2.DocEntry=  T1.DocEntry

Left Outer join OPRJ T0 on  T0.PrjCode=  T1.project

Where T2.DocDate >=[%0] and T2.DocDate <=[%1]

Group By  T0.prjname ,T2.CardCode,T2.CardName , t2.DocDate,t2.DocNum,t2.JrnlMemo,T2.Canceled,t2.DocTotal

--------------

Thanks & Regards

Unnikrishnan