Dynamics GP View - Payables-Receiving Distribution Detail

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