October 22, 2009 
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 Oct 22 2009 by Victoria Yudin, Flexible Solutions Inc
-- For updates see https://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
-- Updated Nov 19 2013 to add apply to doc due date
-- Updated Feb 13, 2018 to add discount
-- Updated Jun 5, 2018 to add transaction descriptions
-- Updated Apr 16, 2020 to add PO number and optimize code
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
select
rtrim(P.VENDORID) Vendor_ID,
rtrim(V.VENDNAME) Vendor_Name,
rtrim(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 rtrim(P.CHEKBKID)
else ''
end Checkbook_ID,
case P.PYENTTYP
when 2 then rtrim(P.CARDNAME)
else ''
end Credit_Card_ID,
P.DOCDATE Payment_Date,
P.PSTGDATE Payment_GL_Date,
rtrim(P.VCHRNMBR) Payment_Voucher_Number,
rtrim(P.DOCNUMBR) Payment_Document_Number,
P.DOCAMNT Payment_Functional_Amount,
rtrim(P.TRXDSCRN) Payment_Description,
rtrim(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,
rtrim(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(AD.DUEDATE,'1/1/1900') Apply_To_Due_Date,
coalesce(PA.APPLDAMT,0) Applied_Amount,
coalesce(PA.DISTKNAM,0) Discount_Amount,
rtrim(coalesce(AD.TRXDSCRN,'')) Apply_To_Doc_Description,
case when coalesce(AD.PONUMBER,'') = ''
then rtrim(coalesce(AD.PORDNMBR,''))
else rtrim(AD.PONUMBER)
end Apply_To_Doc_PO_Number
from
(select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
PYENTTYP, CARDNAME, TRXDSCRN
from PM30200
where DOCTYPE = 6 and VOIDED = 0
union
select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
PYENTTYP, CARDNAME, TRXDSCRN
from PM20000
where DOCTYPE = 6 and VOIDED = 0) P
inner join PM00200 V
on P.VENDORID = V.VENDORID
left outer join
(select VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT, DISTKNAM
from PM10200
union
select VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT, DISTKNAM
from PM30300) PA
on P.VCHRNMBR = PA.VCHRNMBR
and P.VENDORID = PA.VENDORID
and P.DOCTYPE = PA.DOCTYPE
left outer join -- added for apply doc details
(select DOCTYPE, VCHRNMBR, DUEDATE, TRXDSCRN, PONUMBER, PORDNMBR
from PM30200
where DOCTYPE < 6 and VOIDED = 0
union
select DOCTYPE, VCHRNMBR, DUEDATE, TRXDSCRN, PONUMBER, PORDNMBR
from PM20000
where DOCTYPE < 6 and VOIDED = 0) AD
on PA.APTODCTY = AD.DOCTYPE and PA.APTVCHNM = AD.VCHRNMBR
-- add permissions for DYNGRP
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 Yudin 
Can I use this in smartlist builder and put it in via SQL code option versus going through the SQL server management stuido?
LikeLike
Hi Nicole,
I’ve not tried this one specifically, but my gut feeling is that this may not work directly in SmartList Builder. I’ve always found it better to create the SQL view. If you do end up trying it, please let me know how it went.
Victoria
LikeLike
I was able to put it right in as a SQL script. I struggled a bit as it kept saying invalid and then I deleted the last line about security. Then the next issue was it kept saying something about line comments and I realized there was a comment about added for apply doc details and once I deleted those words, it went through flawlessly and the report is EXACTLY what I need to be able to send to our bank for our integrated payables. Thanks for this as it saved me TONS of time trying to build and put the tables together!!!
LikeLike
Hi Nicole,
That is great – thank you so much for the feedback. It makes sense that all the comments would need to be removed. Glad it’s working for you!
-Victoria
LikeLike
You saved me a significant amount of work tonight! I just thought I should say something — this is VERY MUCH appreciated!
LikeLike
Thank you for this.
LikeLike
can you update this script to show credit memo’s doctype=5 also?
LikeLike
Hi Greg,
This is specifically meant for payments. I have another view that will show invoices and what payments, credits or returns were applied to them, maybe that will get you what you need: https://victoriayudin.com/2008/10/22/sql-view-ap-apply-in-gp/.
-Victoria
LikeLike
Victoria,
Ran the script and got the following message:
Msg 207, Level 16, State 1, Procedure view_Payables_Apply_Detail, Line 96
Invalid column name ‘PONUMBER’.
Msg 207, Level 16, State 1, Procedure view_Payables_Apply_Detail, Line 100
Invalid column name ‘PONUMBER’.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object ‘view_Payables_Apply_Detail’, because it does not exist or you do not have permission.
Any advice you can give me would be appreciated.
Thank you,
Lance
LikeLike
Hi Lance,
I just tested this in my GP and it worked with no errors. Can you please let me know what version and build of Dynamics GP you are on? Maybe there is something version specific going on.
-Victoria
LikeLike
Victoria, Here is the build info:
Version Information
Microsoft Dynamics GP 11.00.1752 (SP2)
Dexterity 11.00.0349.000
SmartList 11.00.1734
Database SQL Server
System Windows Server 2008
0DBC Driver Manager 03.80.0000
0DBC Driver 10501617
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1617.0 (X64)
LikeLike
Thanks Lance, so it looks like you are on GP 2010, which may not have had all the fields that are in the system now. I would make the following changes and see if it works:
– delete lines 95 through 103 above
– delete lines 61 through 65
– delete the comma at the end of line 60
– delete line 58
That should now hopefully work with your version of GP.
-Victoria
LikeLike
Victoria,
New errors?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘1’.
Msg 111, Level 15, State 1, Line 1
‘CREATE VIEW’ must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ‘P’.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near ‘Payment_Type’.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ‘Checkbook_ID’.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ‘Credit_Card_ID’.
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near ‘Apply_To_Doc_Type’.
Msg 102, Level 15, State 1, Line 63
Incorrect syntax near ‘ ‘.
Msg 102, Level 15, State 1, Line 69
Incorrect syntax near ‘ ‘.
Msg 102, Level 15, State 1, Line 79
Incorrect syntax near ‘ ‘.
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near ‘ ‘.
Lance
LikeLike
Hi Lance, these sounds like you might not be copying the entire script or may not be copying it to a new window in SQL.
-Victoria
LikeLike
Good Evening Victoria, can I add to script into SmartList Builder?
Regards
LikeLike
Martin, this might help: https://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/
-Victoria
LikeLike
Thank you Victoria for such a quick answer! That would certainly help! I should have been more precise with my question, Is there any reason why SmartList Builder is giving a “SQL script is invalid” error message when I try to run your script, without the view creation part, while when running it directly on SQL I have no error?
LikeLike
Martin,
Are you trying to just copy my code into SmartList Builder? That will not work, as it’s SQL code, not SmartList Builder code. You must create it as a SQL view, that’s the only way it will work with the code as is.
-Victoria
LikeLike
Correct, this what I was trying to do! Any major differences in between SQL code and SmartList Builder code? I would really like to use SmartList Builder functionality to create view base on your code and deploy it over the 340 DB I’m taking Care!
LikeLike
Martin, you will want to talk to eOne about that, it’s not something I can answer as I never use anything other that SQL code for this.
-Victoria
LikeLike
Off Course, Sorry!
Thanks and have a good day!
PS: Keep doing your excellent work!
LikeLike
Me again!
eOne just answered me a few minutes ago and the only problem with your code was the GO command!
Everything is working fine!
Thanks Again!
LikeLike
Great, thanks for the follow up!
-Victoria
LikeLike
Hi Victoria (I have to apologize….I posted my original comment to the wrong View Script – it was for this one – Payables Apply Detail. I have copied and pasted out other comments here),
I really liked the idea of creating this view. But once I ran opened the view, I received an error: “Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.” We are running GP 18.2 – just upgraded a couple of weeks ago.
Thoughts?
Thanks
Hi John,
I’ve not seen an issue with this view before, so I am not sure what is causing the error you’re seeing. Can you please give me some more detail on what you are doing?
You created the view in SQL and that didn’t give you any errors?
Then what exactly are you doing and where?
Also, what version of SQL are you using?
-Victoria
We are running SQL Server 2016 and there were no errors when the view was created. Only this when I go into Management Studios and run the view: “Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.”
LikeLike
Hi John,
I just tested that view on a GP 18.2 installation and it worked fine. So it seems like there is something else going on. Can you please be more specific – what do you mean by “run the view”? What exactly are you doing and where?
What happens if you open a new SQL Server Management Studio query window, type in the following:
select top 10 * from view_Payables_Apply_Detail
then click Execute?
-Victoria
LikeLike
Victoria – I always appreciate the work that you put into these scripts and always happy to give you full credit. I learn much from what you have written.
I have a question about how I might add the Total Check Amount to this view. The information is of course in the PM30200 and PM20000 tables where the Doc Type = 6 the Doc Amount is the full check amount. But since this report is listing the invoice documents and their applied information, I’m not sure how to go about getting this information onto the report.
I am not SQL expert so I would like to find out how to go about adding this information.
Thank you in advance for your wisdom 🙂
Sheila Jefferson-Ross
LikeLike
Hi Sheila,
Nice to hear from you and thank you for your kind words! I hope you and yours are well.
It looks like I am already bringing in the Total Check Amount into the column called “Payment_Functional_Amount” – that is the DOCAMNT field from PM30200 and PM20000. Not sure why I called it that, but it was soooo long ago – originally written in 2009! Maybe I wanted to make it clear this was functional currency only.
Please let me know if that does not look right or if you need any other changes to this code. Also, if there is a lot of payables data, we can optimize it to make it run a bit faster by changing some of the code around.
-Victoria
LikeLike
Hi Victoria,
All is well here in sunny California except for feeling a bit stir crazy.
I see the Payment_Functional_amount and now see that it is the check amount. The nice thing about this view is it looks at the payment info first & links the invoice information.
I attempted to add the PONUMBER to this view but it ends up blank.
here is what I edited:
coalesce(AD.TRXDSCRN,”) Apply_To_Doc_Description,
rtrim (P.PONUMBER) PO_Number
from
(select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
PYENTTYP, CARDNAME, TRXDSCRN, PONUMBER
from PM30200
union
select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
PYENTTYP, CARDNAME, TRXDSCRN, PONUMBER
from PM20000) P
If I add PO number to your other view called “view_AP_Apply” I can see it without any issue but adding it to this view it doesn’t pull in the information. Any ideas as to why?
Speaking of the ‘view_AP_Apply’ script, I am also reviewing this script to see if I can get all the fields I need but this one does not have the total check amount but I can get the PO and Remit To fields. Maybe you have a suggestion on how I can get the total check amount on the other view.
So sorry to bother you. Truly appreciate your help.
Thanks
Sheila
LikeLike
Hi Sheila,
No problem at all, glad to help. I added the PO number to the code and also optimized the code a bit. There are actually 2 fields that could have a PO number, I believe it depends on where the transaction originated, Payables or POP. I wrote it so that if there is something in the PONUMBER field it will show it, otherwise, it will show the PORDNMBR field. I also added RTRIM to all the strings and changed the logic around slightly to (hopefully) run a little faster.
Hope that gives you what you needed.
-Victoria
LikeLike
Hi Victoria,
Thanks so much for doing that. I’ll run the new script against my data and let you know how it goes.
LikeLike
Hi Victoria,
I’m a happy camper. I used your edited script and I added 3 more fields from the invoice documents – Remit To Address code, Purchase Amount and Trade Discount Amount. Everything is showing up as it should. Again, thanks for your assistance.
-Sheila
LikeLiked by 1 person
Is there a good way to get only the most recent due date on an invoice?
LikeLike
Hi Brian,
I am not sure what you mean – each invoice only has one due date.
-Victoria
LikeLike
Hi, could you add discounts taken on the invoice to this view?
LikeLike
Hi Tammy,
I have added the discount for you.
-Victoria
LikeLike
Victoria, my users are loving this, but have asked to include the Description from the Invoice. What I’m seeing is that the document type 1 (invoice) has the description, but for document type 6 (payment) this field is now blank (TRXDESCRN). This logical file is including only document type 6. Is there a way to include this TRXDESCRN from the invoice?
LikeLike
Hi Teri,
This is a great idea. I have updated this code to add both the transaction description from the payment (called Payment_Description) and the description from the invoice (called Apply_To_Doc_Description, to be more generic). Please let me know if this is not what you meant or if you have any questions on this.
-Victoria
LikeLike
Outstanding! I’ll give this a try right now. Very much appreciated, Victoria!
LikeLike
Hello Victoria,
This worked! Thank you so much for putting the time and effort into making this!
Carman
LikeLike
Hello Victoria,
Is it possible to know WHO/WHICH USER applied documents? I don’t see a user field on the PM30300 table 😦
Thanks!
LikeLike
Hi Shawn,
Unfortunately, GP does not track this information. 😦
If it’s important, something custom can be done…
-Victoria
LikeLike
Hi Victoria
When I ran query in SQL it gives the following message
Msg 208, Level 16, State 1, Procedure view_Payables_Apply_Detail, Line 57
Invalid object name ‘PM30200’.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object ‘view_Payables_Apply_Detail’, because it does not exist or you do not have permission.
How could this be fixed.
Regards
Rashid Anwar
LikeLike
Hi Rashid,
I suspect you may not be running the script against your company database in SQL – can you please check that and try again by running this against the company database?
-Victoria
LikeLike
HI,
I have a transaction on the following tables PM10200 and PM10100 on MDGP13 but the transactions re not available anywhere on the interface to process. Under enquiry they on the creditor the transaction shows as WORK but they are not available for posting in any of the post checks, process check. The checks have been printed and sent out to the client.
LikeLike
Leagok,
Can you see these in the PM10300 table?
-Victoria
LikeLike
Hi Victoria,
I’m trying to use this for filing e-checks. Basically the client needs to enter a range of check numbers and return the apply information. When I restrict by check number I only get the check number information; no apply detail. Can this query be used for what I’m looking for?
Thank you,
Gina
LikeLike
Gina,
I believe this will only work for posted checks. Are they needing something for an unposted check batch?
-Victoria
LikeLike
Hi Victoria, we were looking for posted checks, yes. But the client is flexible. We ended up creating a view using the PM10201 (forgot all about this newer table). Thanks!
LikeLike
Hi Victoria,
Nice script indeed, but can you add the multicurrency info by any chance?
Thanks,
Ben
LikeLike
Hi Ben,
Adding Multicurrency would require considerable work, so it’s something I can do for a fee. If you’re interested in this, please let me know.
-Victoria
LikeLike
How does this script deal with partial payments? Our customer is comparing it to the check distribution report out of Dynamics, and they are not matching. Is there any way to do the same thing that report does, or will it only show the total invoice distribution amounts?
LikeLike
Phil,
This was not meant to replace the Check Distribution report. While I have never compared the two, one difference I know of is that my code will show ALL payments, whereas the GP report will show only checks. Have you tried filtering on the Payment_Type column in my code to include only checks?
That said, I am not sure of the exact logic used by the Check Distribution report, so I am not sure if that is the only difference, if you are finding others and need help tracking them down, please write back with more details.
-Victoria
LikeLike
Hi Victoria,
I was just looking at this query and run into a question:
I believe this query lists all payments with documents they applied to.
If you are listing all payments in PM20000, don’t you have to use PM20100 for apply information instead of PM10200, which is for unposted transactions?
Please tell me if I’m going crazy here.
Thank you!
LikeLike
Hi Takeshi,
PM20100 is a Temporary table, which I try to stay away from when coding reports. PM10200 has the apply information for WORK and OPEN transactions. PM30300 has the apply information for HISTORY transactions. So the combination of PM10200 and PM30300 should be sufficient for the data needed. Are you finding that this view is not giving you correct results?
Out of curiosity, I tested replacing PM10200 with PM20100. In my data set, I get the same number of results with either. However PM20100 has a much larger amount of records than PM10200. So all other things being equal, using a table with less records should make the query faster. I have also seen data issues in the past where records get stuck in PM20100 and need to be cleared out manually for various reasons. Just another reason I would not use this table for reporting if I have other options.
-Victoria
LikeLike
Thanks for the quick response, Victoria.
I guess my assumption about PM10200 was wrong.
I haven’t run it against our live data yet, but I will let you know if I find any incorrect results (hopefully not).
Thanks again!
LikeLike
Victoria
I am using your AP Apply view in SLB and was wondering if you can or have created a GO To for it. I would like to be able to drill into the original AP Transastion entry winow or zoom.
Thanks!
LikeLike
Hi Tami,
I have not tried this myself, but I would think you’d need to add a column for Document Type (to either the payments, or the apply to docs, or both, depending on what you wanted to be able to zoom to) and then use the Payables Transaction Inquiry under Advanced Go Tos.
-Victoria
LikeLike
Hello Victoria,
Thank you for responding back so qucikly. My bad, I said I was using your AP_APPLY script and not getting the intended resuilts. I was actually using the Payables_Apply_Detail script whcih was not returning CM’s. I have swithched to your AP_APPLY script and it’s is giving me both CM’s as well as not returning voided transactions.
Thanks for your time and efforts!!
~Tami
LikeLike
Dear Victoria,
I’m Noraini. This view is so helpful. Thanks. For your information, one of my customer need to show payments which apply to original document invoice amount and original credit memos amount. How do I get this information?
Regards,
Noraini
LikeLike
Hi Noraini,
I think this is what you’re looking for: https://victoriayudin.com/2008/10/22/sql-view-ap-apply-in-gp/.
-Victoria
LikeLike
Hey Victoria,
We have been using the query for some time, and from output of the query we are getting:
Payment_Document_Number = ‘REMIT000000000000002’ with Payment_Document_Type = 6 (where 6 is the checks type).
However, all the other Payment_Document_Type = 6 cases are coming always having numeric ids rather then alphanumeric data.
Do you know what type of activity will reproduce this case?
From looking at the data where the $ amount is zero on that row it look like something it may be ok to ignore.
But I want to be able to replicate it to clearly validate we’re doing the right thing.
Thanks much,
Amir
LikeLike
Hi Amir,
The ‘REMIT’ transactions are generated by Dynamics GP during a check run when a remittance needs to be printed, based on rules set up in GP for the payables subledger. Here is one example of when this might happen:
1. You cut a check for vendor A on 1/2/11.
2. You enter a credit memo for vendor A on 1/3/11.
3. You enter an invoice for vendor A on 1/4/11 and apply the credit memo from step 2 to the invoice.
4. On 1/5/11 you do a check run and include all your vendors, or some vendor range that would include vendor A. In the Payables module setup you have the ‘Print Previously Applied Documents on Remittance’ selected. As part of the check run a remittance document for vendor A will be generated by GP showing apply information since the last time you have cut a check to vendor A. It will not use up a check number, but a ‘transaction’ is created for GP to be able to print the remittance.
These remittance transactions will always have an amount of zero and should be fine to ignore, as you suspect.
Hope that helps,
-Victoria
LikeLike
Thanks Victoria, will check it out.
LikeLike
Victoria,
I need to obtain the voucher number and check information for an invoice that is entered into the Receivings Transaction Entry screen. I know there is information in the PM30300 table, but it doesn’t include the receipt number. Do you have a way to collect this information via the receipt number?
Thanks for all of your posts…they have saved me on a few projects 🙂
Jerri
LikeLike
Jerri,
I believe this view will give you what you are looking for.
-Victoria
LikeLike
Victoria, I have received immense knowledge from your site, and I sincerely thank you for posting this information out there.
I have a question related to Payables Apply. We had a situation where we were applying payments to invoices and received an out of memory error on the database server that is running GP. We were left with a batch that had half applied that we cannot unapply, or recover the batch on.
Now, the batch is no longer available in batch recovery and I cannot find the apply details or distributions in the work, open or historical tables. Is there a way we can unapply this batch so that we can attempt to repost?
LikeLike
Hi Jason,
Thanks for the kind words. I have run into similar situations before. First step is to run check links and reconcile on the purchasing series. Sometimes that will clear up the issue. If that does not help, then you will need to fix this directly in the tables. Since every situation is different, I would advise to get your GP partner or GP Support to help with this, as it can get pretty involved and it’s easy to get undesired results.
-Victoria
LikeLike
Wow! Thanks for the quick follow-up. I thought our Controller had already run Check Links, but after just speaking with him I found he hadn’t. We’re going to try that tonight.
I consider myself to be pretty savvy, but I’m concerned with being directly involved with this issue due to my lack of knowledge of the Purchasing tables. I feel that if I had a little more intimate knowledge I would be able to clear this up if Check Links doesn’t work, but I believe you’re right and we will be calling our GP Partner for assistance with this.
Thanks again!
LikeLike
Amir,
I am not quite sure what you’re asking for here…we have not done a customization like this, so I don’t know what other information I can give you for it.
-Victoria
LikeLike
Hey Victoria,
Thanks much for this post.
We are tying to automate reading back the check cut process data from GP purchasing module. We are on version 9 and are not considering updating it.
We are sending 4 DOCTYPEs of transactions to GP through taUpdateCreateVendorRcd, http://msdn.microsoft.com/en-us/library/bb648406(v=MSDN.9).aspx: 1=Invoice, 3=Misc. Chrg, 4=Return, 5=Credit Memo.
During the check cut/post query there is a report titled: “Checks With Stub on Top” which has exactly the data that we are looking for.
– seller id and name.
– GP check id and check total.
– A list having exactly one line per transaction we sent over. The transactions have a few columns but only 2 matter for us the voucher number (column 1) and the amounts (column 4). For instance on some test data on with respective DOCTYPE 1, 4, 5 and 3 it looks like that (last two columns dropped for brevity):
((our company name)), Inc. 00000000000000000322 ((vendor id)) ((vendor name)) 00000000000000573 9/30/2010 VCH-28824 VMD-100110-28824 10/1/2010 $1,033.08 $48.85 $0.00 VCH-28825 VMD-100110-28825 10/1/2010 ($619.70) $0.00 $0.00 VCH-28826 VMD-100110-28826 10/1/2010 ($364.53) $0.00 $0.00 VCH-28827 VMD-100110-28827 10/1/2010 $210.28 $210.28 $0.00 $259.13 $259.13 $0.00 $0.00 $259.131. Our first preference would have been to read that data back.
Is any GP history preserving that exact output from the GP check cut process?
a. Is there a flag / config to flip for enabling it? (otherwise I see that some of the history and/or reprint tables in http://vstoolsforum.com/blogs/dynamicsgp/archive/2007/04/28/dynamics-gp-payables-management-rm-tables.aspx are empty)
b. Is there a GP reports for that data _after_ the check was cut?
c. Is there an api or GP stored procedure for reading it?
2. If option 1 in not available the next best option would be to use your posted query.
Your query does filter on P.DOCTYPE = 6 in the end. That makes the query show only check types. I have dropped that condition so now it shows both check and transaction data. I am also reporting P.DOCTYPE back as a column so the difference could be tracked by the reading code.
Once we use your query, the data shows how one transaction was applied to another per the vouchers in Payment_Voucher_Number and Apply_To_Voucher_Number. They would show voucher numbers for P.DOCTYPE 6. Then for P.DOCTYPE = 6 Payment_Document_Number would show the check id and Apply_To_Voucher_Number would show the voucher number of on of the invoice transactions.
Now the tricky part which seems to help solve our requirements – we draw a logical “linking” tree of all the voucher numbers (Apply_To_Voucher_Number for checks [=6] and both Apply_To_Voucher_Number and Payment_Voucher_Number for transactions [6]). Then all the transactions on each check’s tree are the ones applied towards that check. And the amounts in Payment_Functional_Amount seem correct and matching our expectations and seems that functionality is similar to what we had in option 1.
Have questions about using this:
i. Is this a reasonable solution, or is there a better way to do it?
ii. What happens if one invoice (doctype=1) is _exactly_ consumed by one or more return (doctype=4). Would those transactions not get connected to the logical tree of the check?
Thanks much,
Amir
LikeLike
Hi Amir,
Unfortunately, GP does not store the data in the way you want to use it. Even though you can apply a credit memo or a return at the time of the check creation process (or between the last check and the current check) and these transactions will show up on the check stub, the exact data that was on the check stub is not stored by GP. Since credit memos and returns are applied to invoices or misc. charges, not checks, there is no relationship created or stored in GP between the checks and the credit memos or returns that appear on that check stub. And, as you correctly point out, if a return or credit memo is the exact amount of an invoice, the two will be applied together and may or may not ever appear on the check stub, depending on other settings.
If you absolutely need to have the same exact data as what is on the check stub, you may need to create a customization to store the data required for this. If it exists at the time of printing the check, you should be able to capture it at that point in custom tables. A less complicated option may be to store physical copies of all the check stubs. If these are used infrequently, this approach may be fine. If this is needed constantly, then a customization may be the better approach.
-Victoria
LikeLike
Hi Victoria,
Out of the options I listed it seems you recommend only looking at no 1. and not 2. Since if some data is lost then there is no way option 2 will cover it.
Then we do need the exact check data and we do need to automate it. Yes I thought about having our financial folks store all the check cuts as files then parsing them back. This is not reliable or solid as you also pointed.
So it appears capturing the data in custom tables at time of check generation is the best way to go.
Would you kindly point where to implement such integration?
Adding a table and insert statements sound simple.
Not clear how come they have not yet added that to the product. Than as long as it’s customisable, that will be ok for us.
Thanks for the answer,
Amir
LikeLike
Amir,
I thought I addressed option 2 by saying it’s not possible to get that data, since it’s not stored. You’ll get the check apply details and the separate credit memo and return apply detail, but you will not be able to duplicate what exactly what was on the check stub.
As far as creating this customization, that’s not something I can help with on a blog comment. In my experience, it’s usually not as simple as it sounds. I would recommend working with your Dynamics GP partner to create this for you.
-Victoria
LikeLike
Hey Victoria,
About creating the customization, would you be able to point us to where such integration applies? Yes I understand your recommendation and won’t expect a full solution on the blog per your point. Would still want to get a good idea of the type of work it requires first, then we can evaluate internally and decide if we want it passed to our Dynamics GP partner.
Thanks for the answers,
Amir
LikeLike
Hey Victoria,
Status update and a quick question.
So we’ve gone with option 2 for now for 2 reasons – first to avoid the GP customization needed. Second since we were already comfortable reading that data. We know it’s not perfect, but also believe it would be rare, then we’ll throw in the fact that we’ll be closely monitoring it (send alerts if it happens) + make it simple to manually fix the data – then it’s a non-perfect but pretty practical solution. (in fact all amounts are being read back, even if amounts exactly consume others. Just that when they exactly consume the explicit association to check id is lost. In case one can guarantee a single check per day per seller we could have been deducting the association based on date+seller)
Then another good news is that we are moving to the latest GP code soon.
In which case – since we know this is a missing the option 1 feature in GP – do you have any recommendation or knowledge how to check / where to look up if it’s planned for an upcoming release?
If not yet planned, I would want to file them a feature request for adding it.
Thanks,
Amir
LikeLike
Amir,
Thanks for the update. To my knowledge there is nothing to address this in the latest version (GP 2010). I cannot speak to plans to future versions, as nothing has been released on that.
You can search and vote on existing product suggestions, or suggest your own, at this site: https://connect.microsoft.com/dynamicssuggestions.
-Victoria
LikeLike
Victoria, as usual, this was EXTREMELY helpful.
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
Victoria,
Extremely useful collection of information!
I really appreciate for this and effort you’ve put into sharing information.
Regards,
Santosh
LikeLike
How can I connect GL20000 (or GL30000) to any of the PM and/or POP tables?
LikeLike
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
LikeLike
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
LikeLike
Lukas,
What is Workplace?
-Victoria
LikeLike
Dear Victoria
Thanks a lot for your reply. I will post this requirement to Microsoft
LikeLike
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
LikeLike
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
LikeLike
Victoria,
This is great stuff! Your site is on my Favorites bar!
Thanks for all you do!
Leslie Vail
LikeLike
Leslie,
Thank you so much, that really means a lot to me. Happy New Year!
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks for an extremely useful collection of information!
LikeLike
Just want to say you thanks heaps for sharing your valuable knowledge with everyone.
I was looking for this for some time.
LikeLike
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.
LikeLike
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
LikeLike
Victoria,
I am not using linked serves. Should my ANSI NULL default box be selected in the database properties on the options tab?
LikeLike
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
LikeLike
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 🙂
LikeLike