Skip to Content
0

Incoming Payments Summary

Feb 14, 2017 at 11:40 PM

132

avatar image

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.

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

2 Answers

Best Answer
Taseeb Saeed Feb 15, 2017 at 08:23 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Deepak Magar (SG) Feb 15, 2017 at 09:57 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded