SQL view for all posted Payables payments in Dynamics GP

September 11, 2009 by Victoria Yudin 13 Comments

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

Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code

GP Reports code, GP SQL view, Payables, SQL code

What’s with all these dates?

History of Dynamics GP

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

Jim Doak Reply March 17, 2017 at 3:16 pm

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

LikeLike
Lulu Reply June 15, 2011 at 5:14 am

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

LikeLike
- Victoria Yudin Reply June 15, 2011 at 12:51 pm
  
  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
  
  LikeLike
  - Lulu Reply June 19, 2011 at 8:33 am
    
    Thanks a stack Victoria!
    The view is working well 🙂
    
    LikeLike
Lulu Reply March 10, 2011 at 3:07 am

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” 🙂

LikeLike
- Victoria Yudin Reply March 10, 2011 at 4:16 am
  
  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
  
  LikeLike
Lulu Reply January 16, 2011 at 4:17 am

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

LikeLike
- Victoria Yudin Reply January 18, 2011 at 6:55 am
  
  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
  
  LikeLike
  - Lulu Reply January 20, 2011 at 1:05 am
    
    Great!
    
    Thanks so much Victoria. It worked perfectly and is exactly what I’m looking for.
    
    LikeLike
Adam MacIntosh Reply September 24, 2009 at 9:33 am

Nice post!

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

Adam

LikeLike
Fernando Reply September 14, 2009 at 12:18 am

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

LikeLike
Tauqir Abbas Reply September 11, 2009 at 2:42 pm

Thanks for posting nice article….

LikeLike

Trackbacks/Pingbacks

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

LikeLike