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.
