on 11-17-2016 12:27 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Markus,
I finally figured it out pulling information from the working query into mine.
Kind regards,
Larry T.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Markus,
Yes, I need to see all documents.
Thank you very much!!
Larry T.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.