SQL view for Payables payment apply detail in Dynamics GP

I have published SQL views that return all posted Payables payments in Dynamics GP and Payables apply information to help identify what payments (or credits) paid off an invoice, however another frequent request is for a list of payments and their apply information.  Below is a view that returns a list of all posted payments with details on how they were applied.  Any payment that was applied to more than one transaction will show up as multiple lines.

For other SQL views on GP data, please visit my GP Reports page.

~~~~~

CREATE VIEW view_Payables_Apply_Detail
AS

/*******************************************************************
view_Payables_Apply_Detail
Created on Oct 22 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Does not take Multicurrency into account.
Will return multiple lines for payments that were applied to
     more than one transaction.
Tables Used:
     PM20000 – Open/Posted Transactions
     PM30200 – Historical/Paid Transactions
     PM00200 – Vendor Master
     PM10200 – Apply To Work/Open
     PM30300 – Apply To History
*******************************************************************/

SELECT	P.VENDORID Vendor_ID,
	V.VENDNAME Vendor_Name,
	V.VNDCHKNM Vendor_Check_Name,
	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

FROM
     (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
	    DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
	    PYENTTYP, CARDNAME
      FROM PM30200
          UNION ALL
      SELECT 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

WHERE P.DOCTYPE = 6 AND P.VOIDED = 0

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Payables_Apply_Detail TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone

Posted in Crystal Reports, Dynamics GP, GP Reports, GP SQL scripts, SmartList Builder Tagged: Crystal Reports, Dynamics GP, GP Reports, GP SQL view, Payables, SmartList Builder, SQL code