Skip to Content
0

incoming payment report including ar credit memo query

Aug 17, 2017 at 02:08 AM

82

avatar image
Former Member

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

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

1 Answer

Best Answer
Balaji Selvaraj Aug 17, 2017 at 05:59 AM
0

Hi Socrates,

Below query will provide credit memo amounts with negative sign.

SELECT T2.CardCode AS 'Customer Code', T2.CardName AS 'Customer Name',


T2.DocNum AS 'Payment Number', case when T0.DocNum>0 then 1 else -1 end * T1.SumApplied AS 'Paid to Invoice',


T2.DocDate AS 'Payment Date', Isnull(T0.DocNum, T3.DocNum) AS 'Document Number',  Isnull(T0.NumAtCard, T3.NumAtCard) AS 'Customer Ref No',


IsNull(T0.DocDate, T3.DocDate) AS 'DR Date', T4.[SlpName]


FROM dbo.ORCT T2 INNER JOIN dbo.RCT2 T1 ON T2.DocNum = T1.DocNum


LEFT JOIN dbo.OINV T0 ON T1.DocEntry = T0.DocEntry AND T1.Invtype = 13


LEFT JOIN dbo.ORIN T3 ON T1.DocEntry = T3.DocEntry AND T1.Invtype = 14


Left  JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]


WHERE T2.[DocDate] >= [%0] AND T2.[DocDate] <= [%1]


Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank You Very Much Sir :) I owe you really save my butt, today is my deadline for this report

0
Former Member

But sir I have some concern the CREDIT MEMO has no SALES EMPLOYEE sir?

0
SELECT T2.CardCode AS 'Customer Code', T2.CardName AS 'Customer Name',
T2.DocNum AS 'Payment Number', case when T0.DocNum>0 then 1 else -1 end * T1.SumApplied AS 'Paid to Invoice',
T2.DocDate AS 'Payment Date', Isnull(T0.DocNum, T3.DocNum) AS 'Document Number',  Isnull(T0.NumAtCard, T3.NumAtCard) AS 'Customer Ref No',IsNull(T0.DocDate, T3.DocDate) AS 'DR Date',IsNull (T0.[SlpCode],T3.[SlpCode]), IsNull(T4.[SlpName],T5.[SlpName]) as 'Sale Employee'
FROM dbo.ORCT T2 INNER JOIN dbo.RCT2 T1 ON T2.DocNum = T1.DocNum
LEFT JOIN dbo.OINV T0 ON T1.DocEntry = T0.DocEntry AND T1.Invtype = 13
LEFT JOIN dbo.ORIN T3 ON T1.DocEntry = T3.DocEntry AND T1.Invtype = 14
Left JOIN OSLP T4 ON T4.[SlpCode] = T0.[SlpCode] 
Left JOIN OSLP T5 ON T5.[SlpCode] = T3.[SlpCode] 
WHERE T2.[DocDate] >= [%0] AND T2.[DocDate] <= [%1]

0
Former Member

Thank very much

0