0

Add Payment on Account JE in Incoming Payments reports

Mar 21 at 05:27 AM

9

Goo day!

I am trying to create a report that include "Payment on Account'' transactions in Incoming Payments.

Below is my Query:

SELECT distinct T5.DocType[Type], T0.DOCNUM, T0.CARDNAME, T0.[Comments], T0.U_Invoice_No, T0.DOCDATE, T1.[EQUIPMENT SALES], T1.[PARTS SALES], T1.[RENTAL INCOME], T1.[SERVICE INCOME], T1.[OTHER INCOME], T0.VATSUM,T0.[WTSum], T4.SumApplied[Invoice Amount], T5.[CheckSum],T0.PaidToDate, Balance = T0.DocTotal - T0.PaidToDate, T5.DocNum, (T1.[RENTAL INCOME] + T1.[SERVICE INCOME] +T1.[PARTS SALES] + T1.[EQUIPMENT SALES] +T1.[OTHER INCOME] +T0.[WTSum] + T0.VatSUM), T5.[DocDate] AS 'OR Date', T5.CounterRef[OR No.], T0.[U_ReceivedDate], T5.U_BIR2307 FROM ( ( ( OINV T0 INNER JOIN ( SELECT TRANSID, [4002-002-00000] AS 'RENTAL INCOME', [4002-001-00000] AS 'SERVICE INCOME',[4001-002-00000] AS 'PARTS SALES', [4001-001-00000] AS 'EQUIPMENT SALES', [4002-003-00000] AS 'OTHER INCOME' FROM (SELECT TransId, Account, Credit FROM JDT1 ) ps PIVOT ( SUM (CREDIT) FOR ACCOUNT IN ( [4002-002-00000], [4002-001-00000],[4001-002-00000],[4001-001-00000], [4002-003-00000]) ) AS pvt WHERE [4002-002-00000] IS NOT NULL OR [4002-001-00000] IS NOT NULL OR [4001-002-00000] IS NOT NULL OR [4001-001-00000] IS NOT NULL OR [4002-003-00000] IS NOT NULL ) T1 ON T0.TransId = T1.TransId ) LEFT JOIN INV1 T3 ON T0.DOCNUM = T3.DocEntry )

LEFT JOIN RCT2 T4 ON T0.DocEntry = T4.DocEntry ) LEFT JOIN ORCT T5 ON T5.DocNum = T4.DocNum WHERE T5.[DocDate] >= [%0] AND T5.[DocDate]<=[%1] AND T5.[DocType] = 'C'

I want to add the JE on my report.


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

0 Answers