Skip to Content
avatar image
Former Member

Incoming Payments Summary

My client wants to see the summary of all the incoming payments made for a specific range of time, with the following required details:

Incoming Payment No.

Collection Date

Customer Name

Amount total

Related Invoice No.

Posting Date of the Related Invoice No.

This is my initial query on this:

SELECT ORCT.DocEntry as 'Incoming Payment No', ORCT.DocDate as 'Posting Date', ORCT.DocTotal, isnull(ORCT.Comments,'') as Comments,isnull(RCT4.Descrip,'') as Description, isnull(RCT2.DocEntry,'') AS 'AR Invoice No', CASE when isnull(CardName,'') = '' then CardCode else CardName end 'Customer Name' FROM ORCT LEFT OUTER JOIN RCT2 ON ORCT.DocNum = RCT2.DocNum LEFT OUTER JOIN RCT4 ON ORCT.DocNum = RCT4.DocNum

With this query I can't seem to get the posting date of the related invoice no. for each of the incoming payment made.

Anyone who can help me how to include that detail on my query?

Thanks.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Feb 15, 2017 at 08:23 AM

    Hello,

    Try this

    SELECT ORCT.DocEntry AS 'Incoming Payment No'
    	,ORCT.DocDate AS 'Posting Date'
    	,ORCT.DocTotal
    	,isnull(ORCT.Comments, '') AS Comments
    	,isnull(RCT4.Descrip, '') AS Description
    	,isnull(RCT2.DocEntry, '') AS 'AR Invoice No'
    	,CASE 
    		WHEN isnull(ORCT.CardName, '') = ''
    			THEN ORCT.CardCode
    		ELSE ORCT.CardName
    		END 'Customer Name'
    		,dbo.OINV.DocDate [Invoice Posintg Date]
    		
    FROM ORCT
    LEFT JOIN RCT2 ON ORCT.DocNum = RCT2.DocNum
    LEFT JOIN RCT4 ON ORCT.DocNum = RCT4.DocNum
    LEFT OUTER JOIN dbo.OINV ON RCT2.DocEntry = dbo.OINV.DocEntry
    
    
    

    Thanks,

    Engr. Taseeb Saeed

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 15, 2017 at 09:57 AM

    Hello Mylen,

    Try this

    SELECT T0.[DocNum], T0.[DocDate], T0.[CardName],T0.[CheckSum], T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal], T2.[DocNum] as 'Invoice no.', T2.[DocDate] as 'Invoice Date', T2.[CardName] FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum left join OINV T2 on t1.docentry = t2.docentry INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry WHERE T0.[DocDate] between [%0] and [%1] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName], T0.[CheckSum], T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal], T2.[DocNum], T2.[DocDate], T2.[CardName], T3.[ItemCode], T3.[Quantity], T3.[LineStatus]

    Thanks

    DM

    Add comment
    10|10000 characters needed characters exceeded