Skip to Content
0
Former Member
Aug 17, 2017 at 02:08 AM

incoming payment report including ar credit memo query

519 Views

Hi Experts,

My Client wants me to create a query that generates incoming payment payment including CM they what it the amount to be negative but when i create the report the CM is Positive I don't how can I make it. this my query below hope you can help me.

SELECT T2.[CardCode] AS 'Customer/Vendor Code', T2.[CardName] AS 'Customer/Vendor Name', T2.[DocNum] AS 'Document Number', T1.[SumApplied] AS 'Paid to Invoice', T2.[DocDate] AS 'Payment Date', T0.[NumAtCard] AS 'Document Number', T0.[DocDate] AS 'DR Date' ,T3.[SlpName]

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[RCT2] T1 ON T1.[DocEntry] = T0.[DocEntry] INNER JOIN [dbo].[ORCT] T2 ON T2.[DocNum] = T1.[DocNum] INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode] WHERE T2.[DocDate] >= [%0] AND T2.[DocDate] <= [%1]

GROUP BY T2.[CardCode] , T2.[CardName] , T2.[DocNum] , T1.[SumApplied] , T2.[DocDate] , T0.[NumAtCard] , T0.[DocDate],T3.[SlpName]

thanks in advance