Skip to Content
0
Jun 24, 2015 at 11:09 AM

AR Invoices and Credit Memos cancellation

355 Views

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