Skip to Content
avatar image
Former Member

incoming payment report including ar credit memo query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 17, 2017 at 05:59 AM

    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]
    Add comment
    10|10000 characters needed characters exceeded