October 22, 2008 
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.]
More Dynamics GP Payables SQL views are available on my Payables SQL Views page.
For additional Dynamics GP code, please check out my GP Reports page.
create view view_AP_Apply as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- view_AP_Apply -- Created on Oct 10 2008 by Victoria Yudin, Flexible Solutions -- For updates see https://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 Jan 6 2009 to include Vendor Name -- Updated Feb 20 2009 to include trx not fully applied -- Updated Mar 20 2009 to include Payment Date -- Updated Mar 30 2009 to include Apply Date -- Updated Apr 28 2009 to include Apply GL Posting Date -- Updated May 27 2009 to add Payment Status, take space -- out of column names and eliminate duplicates for -- payment that are not fully applied -- Updated Jan 29 2014 to add 1099 columns -- Updated Apr 10, 2015 to use APPLDAMT instead of APFRMAPLYAMT -- Updated Jun 12, 2019 to add PO Number -- Updated Sep 27, 2019 to add trx description -- and improve performance -- Updated Jan 20, 2021 to add batch numbers and checkbook ID -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 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.PORDNMBR PO_Number, p.BACHNUMB Batch_Number, p.TRXDSCRN Trx_Description, P.DOCAMNT Document_Amount, P.TEN99AMNT [1099_Amount], P.TEN99BOXNUMBER [1099_Box], case P.TEN99TYPE when 1 then 'Not a 1099 Vendor' when 2 then 'Dividend' when 3 then 'Interest' when 4 then 'Miscellaneous' when 5 then 'Withholding' else '' end [1099_Type], coalesce(PA.APPLDAMT,0) Applied_Amount, coalesce(PA.VCHRNMBR,'') Payment_Voucher_Number, coalesce(P2.DOCNUMBR,'') Payment_Doc_Number, coalesce(P2.BACHNUMB,'') Payment_Batch_Number, coalesce(P2.CHEKBKID,'') Checkbook_ID, 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, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, TRXDSCRN, BACHNUMB from PM30200 where DOCTYPE in (1,2,3) and VOIDED = 0 union select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, TRXDSCRN, BACHNUMB from PM20000 where DOCTYPE in (1,2,3) and VOIDED = 0) P left outer join (select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, case POSTED when 0 then 'Unposted' else 'Posted' end POSTED from PM10200 union select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, 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, BACHNUMB, CHEKBKID from PM20000 where DOCTYPE > 3 and VOIDED = 0 union all select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE, BACHNUMB, CHEKBKID from PM30200 where DOCTYPE > 3 and VOIDED = 0) P2 on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE left outer join PM00200 PM on P.VENDORID = PM.VENDORID -- add permissions for DYNGRP 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.
Victoria Yudin 
Scott,
That should be in the results as the Payment Doc Number – is it not bringing in the right data for you?
-Victoria
LikeLike
Great site! Is there a way to add the paying branch to the results? Thanks.
LikeLike
Thanks Scott!
“Paying branch” is not a standard GP term – what does that mean in your business?
-Victoria
LikeLike
Thank you for responding. I’m trying to add the branch chosen when a payment is made. I hope that makes sense
LikeLike
Hi Scott,
Dynamics GP does not have anything called “branch”. This could be either something internal that your company is calling “branch” but is called something else in GP, or it could be something provided by a 3rd party product integrating with your GP, or it could be a customization in your GP. There are many possibilities. In any of these cases, it’s not something generic that I can add to my code without knowing the specifics of how your company is using Dynamics GP. Are you able to attach a screenshot showing where you see “branch” in your GP? That might help us figure out what it is.
-Victoria
LikeLike
https://ibb.co/tCMBfMJ
Here’s a link to the screenshot. Thanks.
LikeLike
Hi Scott,
I believe that is from Binary Stream’s MEM (Multi Entity Management) – can you please confirm that you are using that?
-Victoria
LikeLike
Yes, you are correct! Is B3920200 the table I need?
LikeLike
Hi Scott,
There are 2 tables: B3920200 and B3920001.
You can union them into a subquery using the code below, then link to that as if it’s another table.
(select VCHRNMBR, BSSI_Facility_ID, DOCTYPE from B3920001
union
select VCHRNMBR, BSSI_Facility_ID, DOCTYPE from B3920200 )
-Victoria
LikeLike
You are awesome! Thanks so much. I might have more questions later.
LikeLike
broken record here – thanks so much for this site – I was able to take pieces from all over and produce a SP & view that will allow users to look up a vendor; see all of the receipts that came up from our WMS system thru econnect; then when the receipt had an invoice entered or if that receipt was enter/matched; then the check or EFT that it left on … all on one line…. soooo sweet and I could not have done it without Victoria in my pocket (sounds creepy but not meant to be)
LikeLike
LOL! Thank you Jim!
-Victoria
LikeLike
Thanks So Much victoria this is great. If I want to throw this into a SQL view and combine 3 or 4 databases for a comprehensive look, how do I do that?
LikeLike
Hi Andrew,
Here is a blog post with an example of how to do that: https://victoriayudin.com/2008/11/10/sql-view-for-crystal-reports-to-show-open-ap-invoices-from-multiple-companies-in-gp/
-Victoria
LikeLike
Wondering if there was an easy way to add the batch IDs for the payment and the checkbook ID to this view. I was able to find the invoice batch ID pretty easily and got that added.
LikeLike
Hi Kim,
I have updated the view to add batch numbers for both the invoice and payment side and also add the checkbook ID. thanks for the suggestions.
-Victoria
LikeLike
Hi Victoria,
Thank you much for the useful information.
Our current requirement needs to include the Check Number along with all these fields for all corresponding transactions. Can you please lead me through this.
LikeLike
Hi Yash,
The Payment_Doc_Number will hold the check number if the payment was made with a check.
-Victoria
LikeLike
Hi Victoria.
Thank you for been so helpful for lot of us!
Could you please clarify a little bit confusion:
view_AP_Apply
has join
… PA
on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID
and P.DOCTYPE = PA.APTODCTY
but more detailed
view_Payables_Apply_Detail
… PA
on P.VCHRNMBR = PA.VCHRNMBR and P.VENDORID = PA.VENDORID
and P.DOCTYPE = PA.DOCTYPE
Thank you.
Alex
LikeLike
Hi Alexander,
In view_Payables_Apply_Detail – I am taking Payments/Credits only (P) and linking from there to the apply data (PA) on the Payment Voucher Number from P = Voucher Number from PA. In the apply tables, VCHRNMBR is the “apply from” voucher number, aka the payment.
In view_AP_Apply – I am taking Invoices/Debits only (P) and linking from there to the apply data (PA) on the Invoice Voucher Number from P = Apply To Voucher Number from PA.
The way applying documents works in GP is: you can only apply FROM credits (payments, credit memos and returns) TO debits (invoices, misc charges, finance charges). So the payments/credits are always “apply from” and the invoices/debits are always “apply to”, It might have seemed confusing because of the P and PA naming in both views, but they are not the same data. Hope that helps to explain it. If I was writing this code today, I would probably optimize it a bit by bringing the “where” clause into the subquery for P. It would have probably made it more clear for you, as well. 🙂
-Victoria
LikeLike
I really appreciate your explanation. Naming aliases were not confusing, but type joining of documents types.
Thank you a lot, Victoria!
LikeLike
Hi Victoria,
Awesome view as always! I am having trouble trying to add the PO Number to this view. Can you assist me with that?
Much thanks,
Donald Wisch
LikeLike
Hi Donald,
Thanks for this suggestion, I think it might be helpful to others, as well. I just added the PO Number.
-Victoria
LikeLike
Awesome… much thanks!
LikeLike
Hi Victoria,
How would I change the SQL code in order to have Vendor Class ID as a column?
Thanks!
Rudy
LikeLike
Hi Rudy,
You can insert the following in the select part, for example between line 25 and 26:
PM.VNDCLSID Vendor_Class,
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
Does this SQL view, show more than one payment to an invoice? Vice Versa if there are two invoices the payment is applied to, would it show as a duplicate line?
Thanks
LikeLike
Hi Luke,
Yes and yes.
-Victoria
LikeLike
Hi Luke,
Yes, please check out this view: https://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/.
-Victoria
LikeLike
Hi this seems to duplicate for partially paid invoices. Any thoughts?
LikeLike
Victoria,
That is by design. If an invoice is applied to from multiple payments/credits/returns, it will show on multiple lines. If that’s not what’s going on, please let me know.
-Victoria
LikeLike
Hi Victoria-
Is there any way we could add “Voided” column to this query?
I tried it and I got the overstack error.
LikeLike
Hi Mario,
I am explicitly excluding voids from this code because you cannot apply a voided transaction to anything. So even if you were able to add a voided column, it would show that everything is not voided. If you wanted to add voided transactions you can use the code below:
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,
P.TEN99AMNT [1099_Amount],
P.TEN99BOXNUMBER [1099_Box],
case P.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Divident'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
when 5 then 'Withholding'
else ''
end [1099_Type],
coalesce(PA.APPLDAMT,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,
case p.VOIDED when 0 then 'No' else 'Yes' end Voided
from
(select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE
from PM30200
union all
select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE
from PM20000) P
left outer join
(select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,
DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT,
case POSTED when 0 then 'Unposted' else 'Posted' end POSTED
from PM10200
union
select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,
DOCDATE, APPLDAMT, 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)
LikeLike
Hi Victoria – I’m looking for a list of invoices and credit memos that were taken during a check run. I just need a simple list that shows Vendor ID, Vendor Name, Inv\CN #, Date, Amount (with Credits shown as -).. Will probably need to be an SRS report. Do you have anything like this?
Thanks
LikeLike
Hi Tammy,
I don’t have anything like this. It’s difficult to show the credits because technically there is no way to apply a payment to a credit, so while they may have been done at the same time as the check run, the data is not stored the same way in SQL. You may be able to use whatever GP uses to allow the re-create check stub feature in the newer GP versions, but I am not sure how accurate that is – I’ve seen that not always work properly.
-Victoria
LikeLike
I am new to all of this. Where do I paste the code above to get the report?
Victoria do you have a page for some step by step for some of us newbies?
LikeLike
Hi Martha,
This might help: https://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/. If not, you may need to get some help from either your IT department or your GP Parnter to get you started with this.
-Victoria
LikeLike
Hi Victoria –
This query has been a great help to me several times. Just wanted to share as it may help others. I usually end up adding a “Payment_Doc_Amount” by adding DOCAMNT to P2, and adding coalesce(p2.DOCAMNT,”) to the select to show the full payment/check amount.
Thanks again!
LikeLike
HI Brand, I know this was from a long time ago, but if you happen to see this can you let me know. I have a question on your comment. I am using this view and need to add what you said but I am a little confused.
Thanks,
LikeLike
Hi Victoria, this query have a bug, I recomend to change this sentences “coalesce(PA.APFRMAPLYAMT,0) Applied_Amount” for “coalesce(PA.APPLDAMT,0) Applied_Amount”.
LikeLike
Maximo,
I think you are right. If you’re only using functional currency, those are always the same, but for some multicurrency transactions, the APPLDAMT looks more correct. I have updated the code – thanks!
-Victoria
LikeLike
Thank you Victoria, are helpful your contributions.
LikeLike
Hi Victoria,
There’s a manual payment transaction that has been applied to an invoice
but when the payables transaction report is printed,
the apply information of the payment to the invoice does not show. The invoice that has been applied has an “OPEN” status.
What could be the reason? Further, is there any way, through SQL, that the apply information of a certain invoice can be viewed?
Thanks!
LikeLike
Hi Victoria,
There’s a manual payment transaction that has been applied to an invoice
but when the payables transaction report is printed, the apply information of the payment to the invoice does not show. What could be the reason? Further,the invoice that has been applied has an “OPEN” status.
Another question, is there any way, through SQL, that the apply information of a certain invoice can be viewed?
Thanks!
LikeLike
Hi Kim,
Unless something goes wrong during the payment entry or apply process, an invoice with a status of “OPEN” is not fully applied.
If you go to Inquiry | Purchasing | Transaction by Vendor and pull up the invoice, then click on it to highlight it and click the Amount Remaining hyperlink, does it show the manual payment? Also, what is the Amount Remaining shown for the invoice?
To your other question – this view is one of the ways to view the apply information in SQL. If the invoice is not showing in the results when you run this, then it does not have any payments or credits applied to it.
-Victoria
LikeLike
Victoria,
Your script is great but I’m trying to create a report that shows all documents on a check and their related expense accounts for a specific check run. I’ve got all documents (including credits and returns applied) captured except invoices that have been fully applied by credit memos or returns. They do not appear in the PM30300, the apply table.
Any thoughts?
LikeLike
Hi Dave,
Technically, an invoice that is fully applied by a credit memo or a return really has nothing to do with a check run. If you’re seeing these on the check stub, it’s because you have a setting in the payables module enabled to show all records applied since the last check run. Once printed on the check stub, this information is no longer stored by GP. 😦 At least until version 2013.
Reprinting the remittance/check stub exactly how it appeared has been a very popular request on many forums, so in GP 2013 there is new functionality added to reprint a payables check stub/remittance and show not only what invoices were paid, but also what credits were applied and shown on the original check stub. I have not experimented with this yet, so I would recommend some testing, but by the description it sounds like this will address what you’re looking to do. An important note on this is that you cannot go backwards – this will only work for new checks printed with GP 2013.
Hope this helps,
-Victoria
LikeLike
I founded it Victoria. no need to respond. By the way bought your book when it first came out and loved it. Good job.
LikeLike
Victoria is there a way to tell how an invoice was paid? Your view just shows Payment but I need to know if it was a cheque, cash, EFT, etc.
Thanks.
LikeLike
Hi Tom,
Yep, I show how to do that in my Payment Apply Detail view.
-Victoria
LikeLike
At the risk of sounding totally without a clue (which I am), why is there an “Apply” table for Open Transactions? Is this for batches that haven’t been posted yet or are otherwise still in some stage of transition? Our’s (PM10200) has only one record in it at the moment. For most analysis work, could I just join the PM30300 to the GL Transactions (GL20000) to match cash expenditures withe their associated GL coding?
LikeLike
Hi Jeanette,
First a little terminology for payables transactions:
As soon as a payables transaction is fully applied/paid, it moves to History. The PM30300 table only has apply information for historical transactions. Any apply information for transactions that are not fully applied (open) or are unposted (work) will be in the PM10200 table.
While right now in your company there may only be one partially applied transaction, that does not mean this will always be the case. So to make sure that you are reporting on all your data, I would recommend including the PM10200 table, as I show in this view. If you want to exclude unposted apply transactions you can check the POSTED field in PM10200 (0 = Unposted).
Hope that helps,
-Victoria
LikeLike
Victoria,
I used this sql view a while back and added it to each company with no issue. Now, we’ve upgraded to GP 2010, and for some reason, I cannot see the option under one of our newer companies. I see it under all of the other companies. I’ve even tried adding it again (repaeating the steaps in SQL Mgmt Studio, but I cannot add it in security. I am logged in as SA. Is there something that you know of that is causing this now that we are on aGP 2010> I love these SQL views and want to add more that you have shared. Thanks.
LikeLike
Hi Julie,
As a first step, in SQL Server Management Studio, can you check that all of your company databases have the view? You can simply run the following statement against each of your databases to both confirm this and also make sure you have granted SQL permissions to it:
GRANT SELECT ON view_AP_Apply TO DYNGRP
Any company that does not have the view will come back with an error. For any companies that do not have the view, please create it with the script on this page.
Once that is done, go into SmartList Builder SQL Security and make sure that each of your companies has this view marked.
If you’re still having a problem, please write back with more details.
-Victoria
LikeLike
Hi Victoria
is there any sql script available to check if i have duplicate vendors or is there any script to check if payment was duplicated
Regards
Logan
LikeLike
Logan,
I don’t have anything like that posted. Part of the problem with writing something generic for what you are asking is that the definition or criteria for duplicates may vary by company. First step in identifying duplicates would be determining the logic for what would constitute a duplicate for you.
-Victoria
LikeLike
Hello Victoria,
I am trying get see not only applied payments but also applied CM,
I added last line to your applied pymts scripts but it is still only returining invoices.
CASE PA.APTODCTY
WHEN 1 THEN ‘Invoice’
WHEN 2 THEN ‘Finance Charge’
WHEN 3 THEN ‘Misc Charge’
WHEN 5 THEN ‘Credit Memo’
Additionally I do not want to see any cks taht had been voided, can you please tell me how to do this?
Thanks!
LikeLike
Tami,
Your change will not work because you cannot apply from a payment to a credit memo in GP, as they are both ‘credit’ transactions. GP only allows you to apply FROM a ‘credit’ transaction (payment, credit memo, return) TO a ‘debit’ transaction (invoice, finance charge, misc charge). They way I have set up this view, the credit transactions will be listed as ‘payments’, so you should see Credit Memo in the Payment_Type column without having to make any changes to my original code. Voided checks should not be showing up in the results, since they are not applied to anything and I am only showing applied credit transactions. Please let me know if you are seeing voided checks in your results and I will double check my code.
-Victoria
LikeLike
Hello, Victoria,
Thanks for the view. I replaced APFRMAPLYAMT with ActualApplyToAmount for payment to display the orginal apply amount in multi currency (Z-NZD). It works fine.
1. I need the same for Document amount of invoices. I can not.
2. I need Originating Currecy ( CURNCYID or FROMCURR ) as column for invoice appy amount
Appriciate your help.
– Siddeek –
LikeLike
Hi Siddeek,
Adding Multicurrency information to this requires a lot more work and linking in at least one additional table (MC020103) for the multicurrency amounts. This is not something I am planning on posting on my blog, at least in the foreseeable future, however, it may be something we can create as a consulting project. Please let me know if this is something you would be interested in.
-Victoria
LikeLike
Hi, Vic,
Thanks for the tip. This is good idea for future. Actually, I just want to create a smart list by smart list builder with originating invoice amount in a column by removing currency symbol and add as a column for currency using the tables in your view.
– Siddeek
LikeLike
Victoria,
I am an auditor for a company that uses great plains. I need a listing of all invoices that were open as of a certain date (the audit date). This total should equal the total in the accounts payable account on there G/L. Currently it does not. Will this report help them? And how do they create it.
LikeLike
Hi Preston,
No, this will not help reconcile the AP subledger to the General Ledger. If your client in on GP 10.0 or higher, there is a Reconcile to GL feature inside GP that they can use to help find the difference. It’s not always 100%, so if that does not help, they need to either get someone knowledgeable to help them search through their data, or, if this is a recurring problem, they may be better off purchasing a product called The Closer that will give them a report of what is causing the differences. You may also find Frank Hamelly’s answer in this thread helpful: http://community.dynamics.com/product/gp/f/32/p/32268/55524.aspx. There are a lot of other discussions online about this, but you may need to create logins for them. Here is an example: http://groups.google.com/group/microsoft.public.greatplains/browse_thread/thread/c04a0be3c210ce6d.
-Victoria
LikeLike
Hello again Victoria
I was wondering if it was at all possible to update this view with the originating currency information please? This view is perfect for my clients request to create a check payment document – however, we are always reporting in Originating & Functional currency. I’ve noted the fields for the multicurrency reporting purposes below:
1. Currency ID
2. Exchange rate
3. Originating Check amount
4. Originating applied amount
5. Checkbook ID
Thank you & regards
LikeLike
Hi Lulu,
Adding multicurrency details to this is quite involved and will bring up a number of additional issues/questions to be resolved. This is not something I am planning on posting on my blog, at least in the foreseeable future, however, it may be something we can create as a consulting project. Please let me know if this is something you would be interested in.
-Victoria
LikeLike
Thanks your reply Victoria.
Unfortunately right now I don’t have the budget to consider a consulting project. I’ll have an in-depth look at my requirements again and what I can do about the multicurrency transactions.
I am using SQL for some other reporting so already that’s been a big help 🙂
LikeLike
Hello Victoria
We’ve been using your view to create the one for our multicurrency payments requirements. We’re getting there, slowly!
I noticed that your view does not include the currency ID field. I understand why you did it – it wasn’t written for multicurrency purposes – but I thought I’d let you know that it is still a nice field to have since I’ve been working with it somewhat 🙂
Take care
LikeLike
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.
LikeLike
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
LikeLike
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???
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
Jason,
Yes, Doc Type of 1 = “Invoice” in payables.
-Victoria
LikeLike
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? 🙂
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks for your help.
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
This is great Victoria!! Thanks, I am all set. =)
LikeLike
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
LikeLike
Nev,
How about my Payables GL Distributions view? I just added the distribution reference to it.
-Victoria
LikeLike
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)
LikeLike
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
LikeLike
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.
LikeLike
Mun Kit,
You got it, Apply GL Posting Date is now in there.
-Victoria
LikeLike
Thanks, Victoria. I’ll check again.
LikeLike
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!!
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
Boris,
Take a look at my How to use a SQL view in SmartList Builder post.
-Victoria
LikeLike
Thanks a bunch. That looks great!
LikeLike
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?
LikeLike
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
LikeLike
Thanks a lot Victoria!
LikeLike
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?
LikeLike
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
LikeLike
Please include vendor name
LikeLike
Corazon, you got it!
-Victoria
LikeLike