Skip to Content
0

Different information showing on my query report

Oct 21, 2016 at 03:00 AM

18

avatar image

I have a query report which will show the A/R invoice info. and the Incoming payments info. per line items together.But, I noticed that some of the output are showing wrong data. Like, A/R Invoice belonging to wrong Incoming payment.

Below is my query:

SELECT DISTINCT T0.DOCDATE AS "INVOICE DATE", T0.CARDNAME AS "CUSTOMER NAME", T0.DOCNUM AS "SYSTEM AR NO.", T5.SLPNAME AS "SALES EMPLOYEE", T2.COUNTERREF AS "MANUAL OR NO.", T1.DOCNUM AS "MANUAL OR NO.", T0.U_Invoice_No AS "MANUAL AR INVOICE NO.", T0.DocTotal AS "INVOICE AMOUNT", case when t1.wtappld = 0 then 0 else T3.Rate end AS "WT RATE" , T3.WTAmnt AS " INVOICE WT AMOUNT", T1.WtAppld[INCOMING WT AMOUNT], T1.SumApplied AS "AMOUNT COLLECTED", T2.NoDocSum AS "PAYMENT ON ACCOUNT", T2.CASHSUM, T2.CHECKSUM, T2.TrsfrSum,

CASE WHEN T2.CheckSum !=0 THEN (CASE WHEN T2.CashSum !=0 THEN 'MIXED' ELSE (CASE WHEN T2.trsfrsum !=0 THEN 'MIXED' ELSE 'CHECK' END)END) ELSE (CASE WHEN T2.CashSum !=0 THEN (CASE WHEN T2.trsfrsum !=0 THEN 'MIXED' ELSE 'CASH' END) ELSE 'BANK TRANSFER' END) END AS 'PAYMENT TYPE', T4.CHECKNUM, T4.DUEDATE, T2.[TaxDate], T2.U_BIR2307 AS "CWT RECEIVED/NOT", t2.comments, T2.[JrnlMemo]

FROM ((OINV T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DOCENTRY) INNER JOIN ORCT T2 ON T1.DocNum = T2.DOCNUM) LEFT JOIN INV5 T3 ON T1.Docentry = T3.AbsEntry LEFT JOIN RCT1 T4 ON T2.DOCENTRY = T4.DOCNUM LEFT JOIN OSLP T5 ON T0.SlpCode = T5.SlpCode

WHERE T2.DOCDATE >= [%0] AND T2.DOCDATE <= [%1] AND T0.[CANCELED] = 'N' AND T2.[Canceled] = 'N' ORDER BY T0.CARDNAME ASC

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

0 Answers