SQL view to show AP apply information in GP
October 22, 2008 38 Comments
Here is a corollary to the view I posted last week. The view below will show you what payments/credit memos were applied to payables transactions in GP. If you have SmartList Builder you can easily incorporate this SQL view into a new SmartList. [Update: You can also use this in Crystal with the changes I made on May 27, 2009.]
~~~~~
CREATE VIEW view_AP_Apply AS /******************************************************************* view_AP_Apply Created on Oct. 10 2008 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 transactions applied to by more than one check, credit memo or return. Updated on Jan. 6 2009 to include Vendor Name Updated on Feb. 20 2009 to include transactions not fully applied Updated on Mar. 20 2009 to include Payment Date Updated on Mar. 30 2009 to include Apply Date Updated on Apr. 28 2009 to include Apply GL Posting Date Updated on May 27 2009 to add Payment Status, take space out of column names and eliminate duplicates for payments that are not fully applied *******************************************************************/ SELECT P.VENDORID Vendor_ID, PM.VENDNAME Vendor_Name, CASE P.DOCTYPE WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' END Document_Type, P.DOCDATE Document_Date, P.VCHRNMBR Voucher_Number, P.DOCNUMBR Document_Number, P.DOCAMNT Document_Amount, coalesce(PA.APFRMAPLYAMT,0) Applied_Amount, coalesce(PA.VCHRNMBR,'') Payment_Voucher_Number, coalesce(P2.DOCNUMBR,'') Payment_Doc_Number, coalesce(P2.DOCDATE,'1/1/1900') Payment_Date, coalesce(PA.DATE1,'1/1/1900') Apply_Date, coalesce(PA.GLPOSTDT, '1/1/1900') Apply_GL_Posting_Date, CASE PA.DOCTYPE WHEN 4 THEN 'Return' WHEN 5 THEN 'Credit Memo' WHEN 6 THEN 'Payment' ELSE '' END Payment_Type, coalesce(PA.POSTED, 'Unpaid') Payment_Status FROM (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED FROM PM30200 UNION ALL SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED FROM PM20000) P LEFT OUTER JOIN (SELECT VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT, CASE POSTED WHEN 0 THEN 'Unposted' ELSE 'Posted' END POSTED FROM PM10200 UNION SELECT VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT, 'Posted' POSTED FROM PM30300) PA ON P.VCHRNMBR = PA.APTVCHNM AND P.VENDORID = PA.VENDORID AND P.DOCTYPE = PA.APTODCTY LEFT OUTER JOIN (SELECT VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE FROM PM20000 UNION ALL SELECT VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE FROM PM30200) P2 ON P2.VCHRNMBR = PA.VCHRNMBR AND P2.DOCTYPE = PA.DOCTYPE LEFT OUTER JOIN PM00200 PM ON P.VENDORID = PM.VENDORID WHERE P.DOCTYPE in (1,2,3) 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_AP_Apply 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.
Hi Victoria,
Thanks for posting this view. I always find your posts very helpful. Now I have a question. Do you know if it’s possible in either Report Writer or in a SQL view to get all the invoices applied to a particular check into one row? For instance, if one check paid three invoices, we want just one row for that check showing separate columns for each invoice number and amount paid.
Hi Deborah,
Thanks for the kind words!
I believe this can be done in SQL, but not without some serious effort. If you knew upfront how many columns you would need for the apply information, this might be a little easier, otherwise, you would have to build the columns dynamically in your code which is pretty difficult. If it can be done in SQL it is possible that you could then use the results in Report Writer, but I think that would add yet another level of complexity. Creating something like this in RW would probably force you to either concatenate the apply data into one field or force a maximum allowed. Either way not pretty.
-Victoria
Hi,
Per this conversation about credit in GP. I understand if you print checks the applied documents can appear on the stub. What happens if you pay electronically the vendor? how would they know???
JHM,
If you are paying the vendor electronically and they do not already know about the credits, then you would have to find some way of letting them know. The method of doing that will depend greatly on your exact work flow – I would recommend talking to your GP Partner about the best way for your specific situation. You may be able to print the remittance information from GP or it may be better/easier to create a custom report.
-Victoria
Hello Victoria,
Thank you for all of your work published here on your web site.
I am a novice with the GP database so I have a lot to learn:
I have a question in regards to the SQL view to show AP apply information in GP:
1) How come you are not using PM30600 table? what is the difference between PM30600 and the other PM tables?
2) I need to write a crystal report to show all the expenses including the journal entries, credit memo…
I found some of the data in PM30600 and the journal entries are in the GL20000 table. What other tables do I need to include in order to get all the expenses?
3) I guess I have to create a view like the example you had above and then I can use Crystal report to display the data.
I really appreciate your help.
Kim Hoang
Hi Kim,
PM30600 holds the GL Distributions for historical payables transactions. Since this particular view is only showing apply information (what invoice was paid with what check, credit memo, etc.) there was no reason to show the GL distributions here. You may want to take a look at my view for Payables GL Distributions – I think that would help get you started on your report.
-Victoria
Thanks for all this Victoria.
Is the term “Invoice” synonymous with the items that have a Doc Type of 1 and the corresponding Doc Number?
Jason,
Yes, Doc Type of 1 = “Invoice” in payables.
-Victoria
I am not even sure how to ask this question. I am an IT guy, not an accountant. We just started using Dynamics GP a couple of months ago. An incident came up recently and the Accounting folks are asking me to come up with some form of solution for them. The issue is that a vendor called in saying that we didn’t pay an invoice.
Normally they would go in to our old system and put in the invoice number and be able to see what check number we paid that invoice on. Using your query above this works only some of the times in Dynamics. The instance they brought to me when I use your query to search for the Document Number, the document is returned but for payment type it shows Credit Memo. I am having no luck finding a way to trace that invoice number back to the check number that was used to pay it. So far the only way we have found to find the check number is to go back to the hard copy filed away and look at the remittance sheet to find the check number.
Hopefully I have explained the problem well enough.
Is there any help you can offer?
Keith,
If the results of this view bring back Credit Memo, then the invoice was not paid with a check. In GP, for both payables and receivables, the concept is that any credit transaction (return, credit memo or payment) can be applied to any debit transaction (invoice, debit memo, finance charge). So if I have an invoice for $50 and a credit memo for $50 and I apply the credit memo to the invoice, there is no check involved, technically the credit memo ‘paid’ the invoice.
Whether the fact that you have applied a credit memo to an invoice appears on a check stub or not the next time you cut a check for this vendor will very much depend on the settings you have for payables and how checks are cut. If you happen to print this information on the check stubs and save your copy of the check stub, then you can go back to the paper files to see this, but it will not be saved anywhere in GP. The reason for this is that the check had nothing to do with paying the invoice, the credit memo did. If it does show on the check stub, it is ‘informational only’ and a check stub can bet set to show any apply information since the last time a check was cut.
Typically it should be enough for a vendor to get the number of their credit memo that you have applied to their invoice. This is sometimes a little bit of a different concept for accounting users who have migrated from other systems and the terminology may take a little getting used to. If your users need more clarification on this, it may be a good idea to get some training on these concepts from your GP Partner.
-Victoria
Victoria,
Thanks for the response. It was the response we figured we would get but thought we would ask the question.
When I broke the news to my accounting people, they responded with this inquiry:
“It sounds like there is no way to change a setting in Dynamics that would allow the credits to not be applied. In other words, don’t distinguish between a negative invoice and a positive invoice. Just list the invoices (positive or negative) on the check without applying them to anything. This would permit us to inquiry about an invoice or credit and determine what check number they were paid on or deducted on without having to go to the paper check copy.”
Is there a setting in Dynamics to do what they are asking?
Thanks again for all your help. We have not been too impressed with our local partner. Unfortunately, they are the only one local that we have been able to find.
Keith
Keith,
There is no such concept as a ‘negative invoice’ in GP. And no setting to turn something like that on either. While this question comes up periodically in the GP newsgroups, this kind of change to the underlying logic of GP would be a tremendous effort and I do not believe you will see that in any new releases in the near future (if ever).
-Victoria
Thanks for your help.
Hi Victoria,
Thanks for posting these types of tools that makes our lives easier. =)
This view is showing payments that were manually applied but not posted. So the Applied Amount is filled but the Payment Doc number is blank. When I checked the Payment was not posted yet. Is there a way to filter posted and unposted payments using this view?
Thanks Victoria! All the best!
honeylyn
Honeylyn,
That is a great question! i just altered the view to include a Payment Status. I also changed the column names so that this view can be used in Crystal Reports with no issues.
-Victoria
Thank you so much Victoria!
I’m actually using this in my Crystal Report so what you did on changing the columns helped a lot!
Btw, I am checking my output using your view and found out that when the payment amount was not fully applied, it display 2 lines with the same information. For example:
Payment – 2000
Invoice 1 = 1000
Invoice 2 = 900
In the output, it’ll display
Invoice 1 = 1000
Invoice 1 = 1000
Invoice 2 = 900
Invoice 2 = 900
I’m so sorry for all these questions. I’m not well versed in SQL so I can’t modify your view.
Thanks again!
Holeylyn,
Great catch – thanks! If the payment is not fully applied, the apply information may be in both the PM10200 and PM30300 table at the same time. Since I was doing a UNION ALL it was bringing in both sets of records. I just updated the view to have UNION instead, which will take out any doubles.
-Victoria
This is great Victoria!! Thanks, I am all set. =)
Thanks Victoria,
I am not really a SQL guru so you dont happen to have anything that shows the accounts, credits, debits and Distribution references as well do you??
Thanks
Nev
Nev,
How about my Payables GL Distributions view? I just added the distribution reference to it.
-Victoria
Hi again Victoria,
I am getting the following error when trying to use a view in smartlist.
GPS Error: 58
SQL Error: 156 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword ‘and’.
ODBC Error: 37000
The view I am runnning is as follows and is fine in SQL Server.
The error occurrs when I am trying to access the key fields. Any idea’s??
SELECT P.VENDORID AS [Vendor ID], PM.VENDNAME AS [Vendor Name],
CASE P.DOCTYPE WHEN 1 THEN ‘Invoice’ WHEN 2 THEN ‘Finance Charge’ WHEN 3 THEN ‘Misc Charge’ END AS [Document Type],
P.DOCDATE AS [Document Date], P.VCHRNMBR AS [Voucher Number], P.DOCNUMBR AS [Document Number], P.DOCAMNT AS [Document Amount],
COALESCE (PA.APFRMAPLYAMT, 0) AS [Applied Amount], COALESCE (PA.VCHRNMBR, ”) AS [Payment Voucher Number], COALESCE (P2.DOCNUMBR, ”)
AS [Payment Doc Number], COALESCE (P2.DOCDATE, ’1/1/1900′) AS [Payment Date], COALESCE (PA.DATE1, ’1/1/1900′) AS [Apply Date],
COALESCE (PA.GLPOSTDT, ’1/1/1900′) AS [Apply GL Posting Date], OPENDIST.CRDTAMNT AS [OPENDIST Credit Amount],
OPENDIST.DEBITAMT AS [OPENDIST Debit Amount], OPENDIST.DistRef AS [OPENDIST Dist Ref], HISTDIST.CRDTAMNT AS [HISTDIST Credit Amount],
HISTDIST.DEBITAMT AS [HISTDIST Debit Amount], HISTDIST.DistRef AS [HISTDIST Dist Ref], ACCDETAILSOPEN.ACTNUMST AS [In Open Acc],
ACCDETAILSHIST.ACTNUMST AS [In Hist Acc],
CASE PA.DOCTYPE WHEN 4 THEN ‘Return’ WHEN 5 THEN ‘Credit Memo’ WHEN 6 THEN ‘Payment’ ELSE ” END AS [Payment Type]
FROM (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED
FROM dbo.PM30200
UNION ALL
SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED
FROM dbo.PM20000) AS P LEFT OUTER JOIN
(SELECT VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT
FROM dbo.PM10200
UNION ALL
SELECT VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT
FROM dbo.PM30300) AS PA ON P.VCHRNMBR = PA.APTVCHNM AND P.VENDORID = PA.VENDORID AND
P.DOCTYPE = PA.APTODCTY LEFT OUTER JOIN
(SELECT VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
FROM dbo.PM20000 AS PM20000_1
UNION ALL
SELECT VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
FROM dbo.PM30200 AS PM30200_1) AS P2 ON P2.VCHRNMBR = PA.VCHRNMBR AND P2.DOCTYPE = PA.DOCTYPE LEFT OUTER JOIN
dbo.PM00200 AS PM ON P.VENDORID = PM.VENDORID LEFT OUTER JOIN
dbo.PM10100 AS OPENDIST ON P.VCHRNMBR = OPENDIST.VCHRNMBR LEFT OUTER JOIN
dbo.GL00105 AS ACCDETAILSOPEN ON OPENDIST.DSTINDX = ACCDETAILSOPEN.ACTINDX LEFT OUTER JOIN
dbo.PM30600 AS HISTDIST ON P2.VCHRNMBR = HISTDIST.VCHRNMBR LEFT OUTER JOIN
dbo.GL00105 AS ACCDETAILSHIST ON HISTDIST.DSTINDX = ACCDETAILSHIST.ACTINDX
WHERE (P.DOCTYPE IN (1, 2, 3)) AND (P.VOIDED = 0)
Nev,
I added the following lines at the top of your query:
CREATE VIEW view_Nev_testAS
and the following at the bottom:
GOGRANT SELECT on view_Nev_test to DYNGRP
and created a view. Then I created a SmartList based on that view in GP 10.0 SP 3 and it works just fine for me.
You will want to add code to replace any NULLs possible with a 0 for numbers and a ” for strings, otherwise you’re going to get garbage back on some of the columns you’ve added. I typically use COALESCE for that – take a look in my code above for examples on that.
However, the error that you’re getting is caused by something else. At what step of creating the SmartList are you getting that? Also, what versions of GP and SQL and who are you logged into GP as?
-Victoria
Hi Victoria,
Can you include the Apply Posting Date as well, when we print Historical Aging by GL posting date, the Apply Posting date is the date used to reconcile against GL and we can’t view the Apply Date/Apply Posting Date when drill down to the source Apply Information window.
Mun Kit,
You got it, Apply GL Posting Date is now in there.
-Victoria
Thanks, Victoria. I’ll check again.
Victoria,
Thanks so much – I can’t tell you what a help this has been. I’m just beginning to get familiar with the Dynamics table structure.
My folks are asking me for all items entered into A/P with payment info just for the ones paid. I’m figuring I need an outer join somewhere but am not familiar enough with the tables. Is there a quick way to give them that?
Thanks!!
Kathy,
It sounds like the view on this page should give you what you’re looking for. Any AP transactions with no payment will just show blanks for the payment information and $0 for the Applied Amount. Please let me know if I am misunderstanding what you’re looking for.
-Victoria
I have smartlist builder installed but I’m not sure how to incorporate this script into it. Is there a process doc or something that I can find that can guide me through this?
Boris,
I have been asked this question a lot recently, I will try to put together a step by step guide sometime soon. In the meantime, here is the brief version:
This script can be used in SQL Server Management Studio to create a SQL View. Just copy it into a New Query window, choose your database and execute the script. (Warning: if you’re not comfortable working in SQL Server, please ask for help from someone in your organization who is.) Once the view is created, you can point to it in SmartList Builder, there are instructions and an explanation in the SmartList Builder manual for using a SQL table or view to create a SmartList.
-Victoria
Boris,
Take a look at my How to use a SQL view in SmartList Builder post.
-Victoria
Thanks a bunch. That looks great!
Hello Victoria,
Thanks for sharing this useful view!. Is it not needed to use the PM20100 table to show all transactions that are not fully applied?
Vito,
Transactions that are not fully applied will be in the PM20000 table and the apply information will be in the PM10200 table. So I don’t see why PM20100 would be needed. As a general rule, when you see ‘Temporary’ in the name of the table you’re not going to need it.
-Victoria
Thanks a lot Victoria!
Hi Victoria, Thank you so much for the useful information!
One question… is there a way to modify the sql to retrieve the same info for transactions that have not been full applied? If not, do you know of another way to do this?
Anthony,
I have updated the script to include transactions from the PM20000 table that are not fully applied. Please test this with your data to make sure it’s doing what you want.
-Victoria
Please include vendor name
Corazon, you got it!
-Victoria