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?
