SQL view for Payables payment apply detail in Dynamics GP
October 22, 2009 26 Comments
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
Victoria, as usual, this was EXTREMELY helpful.
Victoria –
What does it mean when you have rows in PM30300 which have values for VCHRNMBR that don’t appear anywhere else? In particular there is no join to PM30200 or PM20000.
I think it may correspond to payments in an unposted batch that Great Plains allowed us to apply anyway – which has left something of a mess I’m trying to come to grips with!
Tony
Tony,
Another possibility could be that history tracking was not turned on or history has been purged for the vouchers you are seeing in PM30300 but not anywhere else. If you suspect a data issue, I would recommend working with your GP Partner or GP Support to clear it up. Most likely it’s going to keep causing issues for you on a regular basis.
-Victoria
Victoria…thanks so much for this script. I had this request from our payables department a few weeks ago. I sat down to start writing this and thought I might had already seen it here. Sure enough, I did and it works perfectly.
Thanks!
Victoria,
Extremely useful collection of information!
I really appreciate for this and effort you’ve put into sharing information.
Regards,
Santosh
How can I connect GL20000 (or GL30000) to any of the PM and/or POP tables?
Lukas,
Unfortunately, there is no one answer for this, as each module will be different. It also may not be possible for every subledger transaction depending on GP setup, how transactions are entered and a number of other variables.
A lot of times when faced with a request like this, I like to ask the users why exactly they need a report like this. Perhaps the actual need or issue can be solved in a different way…
-Victoria
Victoria,
I understand that it will not always be possible. The majority of our transactions start in Workplace. Eventually they become invoices that are paid with checks.
As long as I can get the relevant fields together for those entries and don’t exclude the GL20000 entries that do not the users will be happy.
Right now they have an SSRS report relying on a stored procedure that returns an incorrect check# and date for the majority of the transactions. The stored procedure has over 500 lines of sql attempting to backfill the invoice# invoice date, check# and check Date into a table that began with GL20000 (and GL30000 even though it is empty) data.
Thank you for taking the time to reply,
Lukas
Lukas,
What is Workplace?
-Victoria
Dear Victoria
Thanks a lot for your reply. I will post this requirement to Microsoft
Dear Vitoria
First of all I want to congratulate you on the latest MVP award. Wish you that you will be awarded this and more in the coming years.
Regarding GP 10 Security I have 2 concerns. Hope in your busy schedules you will be able to look in to this and give me some answer.
One is that I want to restrict the users from voiding bank transaction. I tried many ways, but I could not. How I can achieve this?
Number 2 is that I created and assigned a user the security roll for Professional Services Tools. But still when he click on any tool like combine accounts, he gets the message that “you must be logged in as ‘sa’ to utilize this utility”. How I can solve this?
Thanks for your time
Abdul,
For the Professional Tools question – there are a number of them that are hard-coded to only work for ‘sa’. No matter what permissions you use, they will still require ‘sa’ to run them. These is no way around this until Microsoft changes this. If this is important to you, I would recommend contacting them about this, as the more people that request a particular change, the more likely it is to get addressed.
For restricting the ability to void bank transactions, since this is done from the same window as entering bank transactions, I don’t see how you would be able to restrict this unless you took away access to the Bank Transaction Entry window entirely. I don’t see a way around this without a customization.
-Victoria
Victoria,
This is great stuff! Your site is on my Favorites bar!
Thanks for all you do!
Leslie Vail
Leslie,
Thank you so much, that really means a lot to me. Happy New Year!
-Victoria
Victoria, this information is so helpful. I really appreciate the time and effort you’ve put into sharing information that is so relevant to helping businesses succeed with the applicable information. Have you considered publishing a SQL view for AR Applied transactions?
Sandy
Sandy,
Thank you for the kind words! Let me look around to see what I have available for AR apply information. For some reason I recall AR being a bit more complicated than AP. I will definitely put it on my ‘wish list’ for future posts.
-Victoria
Hi Sandy,
In case you’re still looking for Apply information, take a look at my latest post: SQL view with AR apply detail.
-Victoria
Thanks for an extremely useful collection of information!
Just want to say you thanks heaps for sharing your valuable knowledge with everyone.
I was looking for this for some time.
Hi Victoria,
Do views work in GP 9.00.0281 for the Smartlist Builder? If so, what is needed? I keep getting the error message
” GPS Error: 58
SQL Error: 7405 [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
ODBC Error: 37000″
when trying to add a SQL view from the company database.
I tried corrected the problem by setting the ANSI_NULLS and ANSI_WARNINGS but still no luck.
Doug,
SQL views absolutely work in all versions of GP 8.0, 9.0 and 10.0 that I have worked with. The only time I recall seeing an error similar to yours is when using linked servers. Are you doing that by any chance? If so, that will not work in a view with SLB (it’s a Dexterity limitation) and you will need to find a different way to get the data into your GP SQL server first.
-Victoria
Victoria,
I am not using linked serves. Should my ANSI NULL default box be selected in the database properties on the options tab?
Doug,
The general rule for GP is that anywhere in settings where you see ANSI options, they should unchecked or set to False.
The only time I have ever seen that error is when trying to access data on a different SQL Server instance than where GP is installed. If that’s not the case here, something else is going on and I would recommend posting your question on one of the Dynamics GP newsgroups or forums to see if you can get some other opinions.
In the meantime, here is a similar question asked on Tek-Tips: http://www.tek-tips.com/viewthread.cfm?qid=1493496&page=10
-Victoria
Just wanted to say thank you again, Victoria! This is exactly what I need right now. I’m very grateful to you for sharing your knowledge with everyone
Pingback: SQL View for All Posted Payables Documents and Apply Info - DynamicAccounting.net
Pingback: uberVU - social comments