Skip to Content
0
Former Member
Jun 25, 2008 at 07:47 AM

Query Print Layout - Incoming Payment

66 Views

Hi All,

I have done the query print layout for incoming payment. but it cannot show the correct A/R invoice: document number. When I only make the payment for 1 invoice it seems to be correct, but when I choose 2 invoices to be paid, then the A/R invoice: document number for the second row is wrong ( if follows the first row).

Below is the query:

SELECT T0.DocNum, T0.DocDate, T0.DocCur, T0.DocTotal,

T0.DocTotalFC, T1.DocNum, T1.CardCode, T1.CardName,

T1.DocDate, T1.Comments,T1.TrsfrRef as 'Cheque No',

T2.BankCode' 'T2.AcctNum as 'Bank Code' , T0.Address , T3.Phone1 , T3.CntctPrsn , T0.PaidToDate, T0.PaidFc , T4.AppliedSys , T4.AppliedFc

FROM [dbo].[OINV] T0 INNER JOIN ORCT T1

ON T0.ReceiptNum = T1.DocEntry LEFT JOIN RCT1 T2

ON T1.DocNum = T2.DocNum LEFT JOIN RCT2 T4

ON T4.DocNum = T2.DocNum LEFT JOIN OCRD T3

ON T1.CardCode = T3.CardCode

WHERE T1.DocNum = [%0]

ORDER BY T1.DocNum

the fields that i want to display in repetitive area are: A/R invoice: Document date , A/R invoice: document number, Amount that they have paid ( partial payment / full payment) i used this database: Incoming payment - a/r invoice: paid, outstanding amount i used formula ( total document - paid to date), and document currency

Thank you! hope you can help.

Pauline