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 https://victoriayudin.com/gp-reports/
Shows all posted Payables payments in Functional Currency only
Tables used:
     PM00200 - Vendor Master
     PM20000 – Open Transactions
     PM30200 - Historical Transactions
Updated on Jan 18, 2011 to add Currency ID, User Who Posted and 
     User Who Modified
****************************************************************/

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,
	P.CURNCYID Currency_ID,
	P.PTDUSRID User_Who_Posted,
	P.MDFUSRID User_Who_Modified

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

13 Responses to “SQL view for all posted Payables payments in Dynamics GP”

  1. Just snagged this code (with voids) for a “must have now” request from my boss. Thanks!

    Like

  2. Victoria!!! Hello, hello there. I hope you’re well.

    Is it possible to please add the void date to this SQL view please?
    I found the PM30200.VOIDPDATE which is the exact field that I need but nothing similiar in the PM20000 which makes sense. Please let me what can be done.

    Thanks in advance

    Like

    • Lulu,

      There are a number of things that need to be changed to accomplish that. Here is code that should work for you:

      CREATE VIEW view_Payables_Payments
      AS
      /*************************************
      view_Payables_Payments
      Created June 15, 2011 by Victoria Yudin - Flexible Solutions, Inc.
      For updates see https://victoriayudin.com/gp-reports/
      Shows all posted Payables payments in Functional Currency only
      Tables used:
      PM00200 - Vendor Master
      PM20000 – Open Transactions
      PM30200 - Historical Transactions
      Includes voided 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,
      P.CURNCYID Currency_ID,
      P.PTDUSRID User_Who_Posted,
      P.MDFUSRID User_Who_Modified,
      P.VOIDED Voided,
      P.VOIDPDATE Void_Date
      FROM
      (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
      DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
      PYENTTYP, CARDNAME, CURNCYID, PTDUSRID, MDFUSRID,
      VOIDPDATE
      FROM PM30200
      UNION ALL
      SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
      DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
      PYENTTYP, CARDNAME, CURNCYID, PTDUSRID, MDFUSRID,
      VOIDPDATE = '1/1/1900'
      FROM PM20000) P
      INNER JOIN
      PM00200 V ON P.VENDORID = V.VENDORID
      WHERE P.DOCTYPE = 6
      /** 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

      -Victoria

      Like

  3. Ms.Victoria

    Any intention to add un-posted payments to this view, as well as, the multicurrency side of things?

    Lulu
    PS: I should change my pseudonym to “Ms.Multicurrency” 🙂

    Like

    • Hi Ms. Multicurrency,

      Not at this time. As you know, adding multicurrency information is a pretty cumbersome undertaking. 🙂

      If you need something like this, we can create it for you as a consulting project.

      -Victoria

      Like

  4. Hello Victoria

    Is it possible to kindly add the following fields to the view. I’ve checked that the fields exist in both tables. Our internal auditors have requested this information which might be of use to others.

    1. Currency ID (CURNCYID)
    2. Void Status (VOIDED)
    3. User Who Posted (PTUSRID)
    4. User Who Modified (MDFUSRID)

    Thanks in advance for your help as always

    Like

    • Hi Lulu,

      I have added Currency ID, User Who Posted and User Who Modified for you. I didn’t add Void Status, as I am already excluding all voided transactions. If you prefer not to have that excluded and to show a column for it instead, you can remove the following from the WHERE clause at the bottom:
      AND P.VOIDED = 0

      and add the following above the first FROM:
      ,P.VOIDED Void_Status

      -Victoria

      Like

  5. Nice post!

    I have a habit of looking through the db for info when trying to solve issues. This is a nice tip.

    Adam

    Like

  6. wow! you are the best
    this view is exactly like the report i was consulting you
    thanks a lot

    Like

  7. Thanks for posting nice article….

    Like

Trackbacks/Pingbacks

  1. SQL view for Payables payment apply detail in Dynamics GP - Victoria Yudin - October 22, 2009

    […] 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 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: