Incoming Payments Summary

[former_member390199]

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.

[Former Member · Accepted solution]

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

[Former Member]

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