Skip to Content
0

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

Nov 17, 2016 at 12:27 AM

147

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Markus Schäfer Nov 18, 2016 at 11:20 AM
1

OK,

In the 2nd Query there is this:

..

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

here you can see: case when T2.Canceled <> 'C' THEN T2.DocTotal *-1 (-T2.DocTotal)

Hope that help you.

Best regards

Markus

Share
10 |10000 characters needed characters left characters exceeded
Markus Schäfer Nov 17, 2016 at 12:56 PM
0

Hi Larry,

Do you need to see the canceled Documents ?

If not, you can use.

... Where T0.Canceled = 'N' (T0 = OINV/ORIN)

hope that helps

Markus

Share
10 |10000 characters needed characters left characters exceeded
Larry Tucker Nov 17, 2016 at 04:58 PM
0

Hi Markus,

Yes, I need to see all documents.

Thank you very much!!

Larry T.

Share
10 |10000 characters needed characters left characters exceeded
Larry Tucker Nov 21, 2016 at 06:10 PM
0

Hi Markus,

I finally figured it out pulling information from the working query into mine.

Kind regards,

Larry T.

Share
10 |10000 characters needed characters left characters exceeded