SQL view for all posted Payables payments in Dynamics GP

This view is in response to more than a few inquiries I have had for a vendor check report. This view brings in all payments, not just checks, so if only checks are needed, add the following to the end of the WHERE clause:

AND P.PYENTTYP = 0

For other SQL code, please visit my GP Reports page.

~~~~~

CREATE VIEW view_Payables_Payments
AS

/****************************************************************
view_Payables_Payments
Created Sep 11, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Shows all posted Payables payments in Functional Currency only
Tables used:
     PM00200 - Vendor Master
     PM20000 – Open Transactions
     PM30200 - Historical Transactions
****************************************************************/

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 Document_Date,
	P.PSTGDATE GL_Posting_Date,
	P.VCHRNMBR Voucher_Number,
	P.DOCNUMBR Document_Number,
	P.DOCAMNT Functional_Amount,
	P.TRXSORCE Transaction_Source

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

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_Payments 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