Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Link Incoming Payment# with Invoice# &Credit memo#

Former Member
0 Kudos
213
  • SAP Managed Tags:

Hi all,

I really need your help on this please.

I need to create the query to show invoice# and credit memo# for each Incoming Payment#. However, there are transactions with reconciled and non-reconciled so it would not show invoice# and credit memo#. I have done some research and someone recommended to link with OITR and ITR1 and I already try but no success at all. Please see below for what I have now.

SELECT IsNull(T2.DocNum, T4.DocNum) AS 'Invoice/Credit#', T2.[NumAtCard] AS 'PO#', T0.[DocNum] AS 'Incoming payment#', T0.[CardCode], T0.[CardName], T2.[DocTotal] As 'Invoice amount', T2.[GrosProfit], (T2.[GrosProfit] / T2.[DocTotal]) * 100 AS 'Profit %', T1.[Dcount] AS 'Terms%', T1.[DcntSum], CASE WHEN T2.DocNum>0 then 1 else -1 end * T1.SumApplied AS 'Amount Paid', T0.[CreateDate] AS 'Incoming payment creation date', IsNull(T2.CreateDate, T4.CreateDate) AS 'Invoice/Credit creation date', T5.[SlpName] FROM ORCT T0 LEFT JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum LEFT JOIN dbo.OINV T2 ON T1.[DocEntry] =T2.[DocEntry] AND T1.Invtype = 13 LEFT JOIN dbo.ORIN T4 ON T1.[DocEntry] =T4.[DocEntry] AND T1.Invtype = 14 LEFT JOIN dbo.OCRD T3 ON T0.CardCode = T3.CardCode Left JOIN OSLP T5 ON T2.[SlpCode] = T5.[SlpCode] LEFT JOIN RCT1 T6 ON T0.DocEntry = T6.DocNum LEFT JOIN RCT3 T7 ON T0.DocEntry = T7.DocNum LEFT JOIN RCT4 T8 ON T0.DocEntry = T8.DocNum WHERE (T0.[CreateDate] >=[%0] AND T0.[CreateDate] <=[%1]) Order by T0.[CreateDate], T0.[DocNum], T4.[DocNum]

Please advise and I really appreciate all your helps! Thank you very much.

0 REPLIES 0