The following view will produce a list of all Check Payments, the Invoices they were applied to, the Receivings Transactions associated with the invoices, the distributions on the receivings transactions, and the line item distribution breakdown of the total distribution.
CREATE VIEW Pay_Rcv_Dist_Dtl
as
SELECT DISTINCT *
FROM
(SELECT
* FROM
(SELECT VENDORID,
CASE DocTYPE
WHEN 6 THEN 'Payment'
END Doc_Type, DOCTYPE,
DOCDATE PMTDATE, VCHRNMBR PMTVCHR, DOCNUMBR CHQNUMBR, DOCAMNT PMTAMT,
VOIDED, TRXSORCE PMTSORCE, CHEKBKID, PSTGDATE PMTPSTGDATE, PYENTTYP, CARDNAME, CURNCYID
FROM PM30200
UNION
SELECT VENDORID,
CASE DocTYPE
WHEN 6 THEN 'Payment'
END Doc_Type, DOCTYPE,
DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME, CURNCYID
FROM PM20000) P
INNER JOIN
(SELECT VENDORID V1, VENDNAME FROM PM00200) V
ON P.VENDORID = V.V1
LEFT OUTER JOIN
(SELECT VENDORID V2, VCHRNMBR VCH1, DOCTYPE DOC1, APTVCHNM, APTODCTY,
APTODCNM INVNUMBR, APTODCDT INVDATE, ApplyToGLPostDate, APPLDAMT
FROM PM10200
UNION
SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
FROM PM30300) PA
ON P.PMTVCHR = PA.VCH1
AND P.VENDORID = PA.V2
AND P.DOCTYPE = PA.DOC1
WHERE (P.DOCTYPE = 6)
) PMT
LEFT OUTER JOIN
--INVOICES
(SELECT
VCHRNMBR INVVCH, DOCAMNT INVAMT FROM
(SELECT VENDORID,
CASE DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END INVTYPE,
DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
FROM PM30200
UNION
SELECT VENDORID,
CASE DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END INVTYPE,
DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
FROM PM20000) P
WHERE (P.DOCTYPE in (1,2,3))
) INV
ON INV.INVVCH = PMT.APTVCHNM
--RECEIVINGS HDR
LEFT OUTER JOIN
(SELECT POPRCTNM, VNDDOCNM, VCHRNMBR RCVVCH
FROM POP30300
) RCVHDR
ON INV.INVVCH = RCVHDR.RCVVCH
--RECEIVINGS DOCUMENT DISTRIBUTIONS
LEFT OUTER JOIN
(SELECT POPRCTNM RCTDIST, CRDTAMNT, DEBITAMT, A.ACTINDX, ACTNUMST, ACTNUMBR_1, ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4,ACTDESCR,
CASE 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'
WHEN 16 THEN 'Round'
WHEN 17 THEN 'Realized Gain'
WHEN 18 THEN 'Realized Loss'
WHEN 19 THEN 'Due To'
WHEN 20 THEN 'Due From'
ELSE ''
END Distribution_Type, DISTTYPE
FROM POP30390 A
INNER JOIN (SELECT ACTINDX, ACTNUMBR_1, ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4, ACTDESCR FROM GL00100) GL ON A.ACTINDX = GL.ACTINDX
INNER JOIN (SELECT ACTINDX, ACTNUMST FROM GL00105) GL2 ON GL.ACTINDX = GL2.ACTINDX
) RCVDIST
ON RCVDIST.RCTDIST = RCVHDR.POPRCTNM
--RECEIVINGS LINE ITEM
LEFT OUTER JOIN
(SELECT POPRCTNM RCTLINE, PONUMBER, ITEMNMBR, EXTDCOST, ITEMDESC, INVINDX
FROM POP30310
) RCVLN
ON RCVHDR.POPRCTNM = RCVLN.RCTLINE
AND RCVLN.INVINDX = RCVDIST.ACTINDX
WHERE VOIDED = 0 and DISTTYPE < 21
