What do you think about using the following:
---------------------------------------------------------- ---------------------------------------------------------- ----- Use for Payments and VAT ---------- ---------------------------------------------------------- ----------------------------------------------------------
SELECT P.[INVID],P.VENDORID Vendor_ID,P.DOCTYPE, V.VENDNAME Vendor_Name, V.VNDCHKNM Vendor_Check_Name, V.[VNDCLSID] as VendorType, CASE P.PYENTTYP WHEN 0 THEN 'Check' WHEN 1 THEN 'Cash' WHEN 2 THEN 'Credit Card' WHEN 3 THEN 'EFT' ELSE 'Other' END Payment_Type, CASE WHEN P.PYENTTYP in (0,1,3) THEN P.CHEKBKID ELSE '' END Checkbook_ID, CASE P.PYENTTYP WHEN 2 THEN P.CARDNAME ELSE '' END Credit_Card_ID, P.DOCDATE Payment_Date, P.PSTGDATE Payment_GL_Date, P.VCHRNMBR Payment_Voucher_Number, P.DOCNUMBR Payment_Document_Number, P.DOCAMNT Payment_Functional_Amount, coalesce(PA.APTVCHNM,'') Apply_To_Voucher_Number, CASE PA.APTODCTY WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' ELSE '' END Apply_To_Doc_Type, coalesce(PA.APTODCNM,'') Apply_To_Doc_Number, coalesce(PA.APTODCDT,'1/1/1900') Apply_To_Doc_Date, coalesce(PA.ApplyToGLPostDate,'1/1/1900') Apply_To_GL_Date, coalesce(PA.APPLDAMT,0) Applied_Amount, coalesce(G.ACTNUMST,'') GL_Account_Number, coalesce(G2.ACTDESCR,'') GL_Account_Name, coalesce(G2.ACTINDX,'') GL_Account_INDX, CASE D.DISTTYPE WHEN 1 THEN 'Cash' WHEN 2 THEN 'Payable' WHEN 3 THEN 'Discount Available' WHEN 4 THEN 'Discount Taken' WHEN 5 THEN 'Finance Charge' WHEN 6 THEN 'Purchase' WHEN 7 THEN 'Trade Disc.' WHEN 8 THEN 'Misc. Charge' WHEN 9 THEN 'Freight' WHEN 10 THEN 'Taxes' WHEN 11 THEN 'Writeoffs' WHEN 12 THEN 'Other' WHEN 13 THEN 'GST Disc' WHEN 14 THEN 'PPS Amount' ELSE '' END Distribution_Type, coalesce(D.DEBITAMT,0) Debit, coalesce(D.CRDTAMNT,0) Credit into Invoice_Split FROM (SELECT [INVID],VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME FROM PM30200 UNION SELECT [INVID],VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME FROM PM20000) P
INNER JOIN PM00200 V ON P.VENDORID = V.VENDORID
LEFT OUTER JOIN (SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT FROM PM10200 UNION SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT FROM PM30300) PA ON P.VCHRNMBR = PA.VCHRNMBR AND P.VENDORID = PA.VENDORID AND P.DOCTYPE = PA.DOCTYPE
LEFT OUTER JOIN (SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef, PSTGDATE FROM PM10100 WHERE PSTGSTUS = 1 AND CNTRLTYP = 0 UNION SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef, PSTGDATE FROM PM30600 WHERE CNTRLTYP = 0) D ON PA.VENDORID = D.VENDORID AND PA.APTVCHNM = D.VCHRNMBR
LEFT OUTER JOIN GL00105 G ON D.DSTINDX = G.ACTINDX
LEFT OUTER JOIN GL00100 G2 ON D.DSTINDX = G2.ACTINDX
WHERE P.DOCTYPE = 6 AND P.DOCAMNT <> 0 AND P.VOIDED = 0 --and (P.VCHRNMBR = '00000000000001443 ') --or -- and PA.APTODCNM = '05/09/4596B ' and CASE D.DISTTYPE WHEN 1 THEN 'Cash' WHEN 2 THEN 'Payable' WHEN 3 THEN 'Discount Available' WHEN 4 THEN 'Discount Taken' WHEN 5 THEN 'Finance Charge' WHEN 6 THEN 'Purchase' WHEN 7 THEN 'Trade Disc.' WHEN 8 THEN 'Misc. Charge' WHEN 9 THEN 'Freight' WHEN 10 THEN 'Taxes' WHEN 11 THEN 'Writeoffs' WHEN 12 THEN 'Other' WHEN 13 THEN 'GST Disc' WHEN 14 THEN 'PPS Amount' ELSE '' END <> 'Purchase' order by coalesce(PA.APTODCDT,'1/1/1900'),coalesce(PA.APTODCNM,'')
then filter for taxes? |