Skip to Content
0
Nov 17, 2016 at 12:27 AM

SAP Business One - Query - AR Invoices and Credit Memos cancellation - Solved

1214 Views Last edit Nov 21, 2016 at 06:22 PM 3 rev

Hello SAP Gurus,

I found the answer, but I need help adding it to my query.

The problem is Credit Memo Cancellations not showing (positive) value.

Here is my query:

Query#1

_______

SELECT T3.SlpName

, T0.CardName

, 'Invoice'

, T0.DocDate as 'Posting Date'

, T5.ReconDate as 'Pay / Credit Date'

, CASE WHEN T5.InitObjTyp = '24'

THEN 'Payment'

ELSE CASE WHEN T5.InitObjTyp = '14'

THEN 'Credit'

ELSE 'OTHER'

END

END [Recon Type]

, T0.DocNum as 'Doc Number'

,(T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum) [Subtotal]

, T0.GrosProfit

FROM OINV T0

LEFT OUTER JOIN ITR1 T4 ON T0.DocEntry = T4.SrcObjAbs AND T4.SrcObjTyp = '13' LEFT OUTER JOIN OITR T5 ON T4.ReconNum = T5.ReconNum

INNER Join OSLP T3 ON T3.SlpCode= T0.SlpCode

where (T0.DocDate BETWEEN '[%1]' AND '[%2]')

UNION ALL

SELECT T3.SlpName

, T0.CardName

, 'Credit Memo'

, T0.DocDate as 'Posting Date'

, T5.ReconDate as 'Pay / Credit Date'

, CASE WHEN T5.InitObjTyp = '24'

THEN 'Payment'

ELSE CASE WHEN T5.InitObjTyp = '14'

THEN 'Credit'

ELSE 'OTHER'

END

END [Recon Type]

, T0.DocNum as 'Doc Number'

,(T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum)*-1 [Subtotal]

, T0.GrosProfit *-1

FROM ORIN T0

LEFT OUTER JOIN ITR1 T4 ON T0.DocEntry = T4.SrcObjAbs AND T4.SrcObjTyp = '14' LEFT OUTER JOIN OITR T5 ON T4.ReconNum = T5.ReconNum

INNER Join OSLP T3 ON T3.SlpCode= T0.SlpCode

where (T0.DocDate BETWEEN '[%1]' AND '[%2]')

_________

Here is the query I found that shows the correct values for Credit Memos (Cancellations)

Here is the query I found:

Query#2

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

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

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

Please if you can take what is needed from Query #2 and add it to Query #1 to fix issue with Credit Memos, I would be much appreciated:

Kind regards,

Larry T.