Payables SQL Views


 Dynamics GP Payables SQL Views

For additional reporting tips and SQL code, please visit my GP Reports page.

58 Responses to “Payables SQL Views”

  1. Hi Victoria,

    I have some customers that are asking for an AP HATB report in which they could filter by AP account number (to tie to the GL). I don’t know if this could be done with a SQL View or if this needs to be a custom report but I was wondering if you knew of a 3rd party that offers something similar or if this is a request you have gotten and how you resolved it. Any advice you could offer is appreciated. Thanks!

    -Amanda

    Like

    • Hi Amanda,

      I have created reports similar to this as consulting projects in the past. It’s very difficult to talk about this generically – every company I have worked with on this wants something different. If you are interested in discussing this in more detail, let me know and I will email you directly.

      -Victoria

      Like

      • i need the same thing. Payables HATB by Project. just a different roll-up (I think), but I’m getting lost trying to tie the tables from PM back to POP (where the JobNumber is)

        Like

        • btw….this whole HATB for payables is like a Russian nesting doll….a stored proc inside a stored proc, inside a….you get the idea.

          it appears the base SP’s are:
          pmPrintHATBGetDocuments
          pmPrintHATBGetApplyHistory
          pmPrintHATBGetApplyWorkOpen

          and primarily these use the PM 30300 for history and PM10200 for work open. I just can’t find how to join in a table that has the darn JobNumber.

          Like

          • LOL George!

            Love the Russian nesting doll analogy. I personally prefer to write my own stored procedure instead of trying to use what’s in GP already for the HATB. I find the GP ones overly complicated for what I typically want to do.

            If you’re interested in talking about this some more, let me know and I will email you directly to set up a time to chat.

            -Victoria

            Like

  2. Hi Victoria,
    I’m trying to get a detail list of what prints on the 1099 forms. 1099s are based on what is paid in the calendar year. I know it uses PM30200 but what is confusing me is that I want all payments (doctype=6) and I want the 1099amnt for 2013 calendar year. But the ten99amnt is stored with the voucher not the check. Since they are in the same file I’m not sure how to link back and get the 1099amnt from the voucher when I’m really pulling check data. I can’t take the entire check amount because sometimes there is something paid on the check that is not 1099 so I really need that 1099amnt field. I tried editing your ‘view_payables_apply_detail’ view but since it is pulling just the payments I can’t get that field since it’s on the voucher. Any hints how to get a full detail report of what is in summary 1099 #?
    Thanks,
    Laura

    Like

  3. Hi Victoria….
    I’m using your all ..transactions query… and the data is in history at PM 2000 & PM 30200..right? ( hope that’s right as I’m working off my faulty memory of tables.

    Like

    • Hi Jack,

      The check number will be the Document Number for payments made with a check. And the amount will be the Document Amount. Please let me know if I am misunderstanding what you’re asking for.

      -Victoria

      Like

  4. Hi Victoria,
    Thank you so much for posting these queries. They are most helpful. I’ve learned a lot
    I was wondering if you could show us how to add 2 more fields to this great query
    in history check number and amount..
    thanks in advance!
    Jack

    Like

  5. Victoria,

    Is there any way to tie a cost from an Accounts Payable entry to a customer invoice in Accounts Receivable?

    Like

  6. Victoria,
    I was looking at your Vendor Totals by Year SQL view. That view provides most of the information that was requested by one of my PM users. However, they would also like to add the Vendor Class ID (VNDCLSID) and the Payment Terms ID. Can you help with the code to add those two parameters to your Vendor Totals by Year SQL?

    Thank you,

    Henry

    Like

  7. Hi Victoria,

    Have you ever received a request to produce a report or SQL view that shows all items included on a Remittance or Check stub? The SQL view that you have available works awesome to put in a Check Number and see the Invoices being paid off but I would like to also see the Credit Memos that are paying off Invoices that were included on the Remittance at the time the check is printed. Any guidance you can provide would be very helpful. Thanks!

    Like

    • Hi Amanda,

      While I have had this request many times, this is not possible until GP 2013 because GP does not store this information. Starting with GP 2013, you should be able to get this out-of-the-box. If you’re on an earlier version of GP, the only way to find this information is to keep a copy of the check stub/remittance.

      -Victoria

      Like

  8. Hi Victoria – I am looking for a historical aged trial balance report that would include specific modifications. Is this something you already have code for or is it something that you could be commissioned to do?
    Thank you,
    Erin

    Like

    • Hi Erin,

      It’s not something I have code published for, but I have done a number of historical trial balance reports, and is something I can definitely help with as a consulting project. I will email you with my contact information so we can talk about this in more detail.

      Thanks,
      -Victoria

      Like

  9. Hi Victoria,
    Thank you so much for posting these queries. They are most helpful. I just have a question regarding the Vendor Monthly Totals. When I run this for a specific period I cant’ seem to tie it to the PM Transaction History Summary Report in GP. Can you please tell me what this query is pulling from or what amounts it is including? I need to reconcile it so I’m sure it’s pulling all that I need.
    Thank you!
    Rita

    Like

    • Hi Rita,

      The results for my Vendor Monthly Totals code should correspond to the Vendor Period Summary Inquiry window (Inquiry | Purchasing | Vendor Period) with the Display selected as Calendar. Looking at the tables used on the PM Transaction History Summary Report, I would actually not expect those numbers to match for active vendors, mainly because the GP report is only looking at the transaction detail for historical transactions and not including posted but unpaid transactions. So unless you’re looking at very old data or a vendor that has nothing but historical transactions, these results should not match.

      Another reason that the results could be off is that my code is pulling from summary tables, which might be incorrect. Once in a while, when a posting process gets stuck, the summary tables do not get updated properly. To fix this you can use the Reconcile Utility in payables.

      -Victoria

      Like

      • Thank you very much for your quick response.
        I’m looking for the easiest way to run a report that provides payables invoices by month by vendor, not including payments. Subtotaled by Vendor if possible. Can you suggest which report may provide that?
        Thank you

        Like

        • Rita,

          I don’t think I have anything exactly like that. And if you want a list of invoices plus a subtotal for each vendor, then you will need to use a reporting tool of some kind to display it that way. Or are you talking about only showing the subtotals per vendor, without the details?

          -Victoria

          Like

          • Victoria,
            I would need the transactions, then the subtotal per vendor. I’m thinking a reporting tool is going to be needed. I’m going to Convergence next week. I’ll look for something. Do you have any suggestion? Again, thank you very much for your assistance.
            Rita

            Like

            • Rita,

              In my experience the 2 most used tools for this kind of reporting are SQL Server Reporting Services (SSRS) and Crystal Reports. Both have pros and cons, depending on many different factors beyond the need for just one report like this. If you have specific questions, let me know and I will try to help. If you’re looking for someone to help create this for you and/or teach you how to do it, that is something my company offers.

              -Victoria

              Like

  10. Hi Victoria,
    Thank you for all your helpful queries and break downs. I do have a question…
    I need to create a sql view to show all invoices for a batch of checks. How can I go about?

    Like

  11. Victoria,

    Thanks for the queires do you have any queries where the output can be tied out to the Aging report

    Farman

    Like

  12. Hi Victoria,

    I changed the tax type for a particular vendor class from ‘Not a 1099 Vendor’ to ‘Miscellaneous’. I now need to populate the 1099 amounts in the ‘Cards > Purchasing > 1099 Details’ module for hundreds of vendors. I’m guessing the only tables needed are PM00200 and PM00202, joined by VENDORID, and populating TEN99ALIF with AMTPDLIF for the aforementioned tax type. Will this work and if so, will this be sufficient for the printing of 1099’s as well?

    We are running GP 10.0.

    Please let me know if I’m close or way off.

    Thank you!

    – Bob

    Like

  13. Hi, Looking for a script that would change an AP document number in all applicable tables after it is posted and paid. Assume it hits MANY tables. Do you have such a script?

    Thanks!

    Like

    • Hi Lisa,

      Sorry, I do not have a script for this. You are right that it would hit MANY tables and not just in the AP module. I would be very careful getting a script from someone for something like this, especially if you have additional modules or 3rd party products that integrate with AP.

      -Victoria

      Like

  14. Hi Victoria,

    I have an issue with the Payables Transaction Edit List. The batch total does not match the purchase amounts. The purchase amount is the correct number. My question is where does GP obtain the batch total amount (which is located at the top on the Payables Transaction Edit List report). What could account for the difference? Thank you.

    Like

    • Kevin,

      Batch totals can be incorrect for many reasons (like multiple people entering or viewing transactions in a batch at the same time, or imports). This should not normally cause a problem unless you have turned on batch verification in Posting Setup. When you post a batch, each transaction is looked at individually, batch totals are not used for any processing or calculations.

      -Victoria

      Like

  15. Hi Victoria,

    I am trying to write a SQL script for AP Historical Trial Balance. We have over 30 companies in GP and would like to run 1 consolidated TB report. Do you have SQL script which can accomplish this?
    I believe I’ve almost there(98% of the vendors balance match), its the final tweeking thats getting really difficult.

    Any help is will really appreciated.
    Thanks,
    Nisha

    Like

  16. Hi Victoria,

    Do you have any SQL scripts that would allow me to upload Payables Receivings Transactions? I would love if we could purchase either Smart Connect or the Scribe product but it’s just not in our budget right now.

    Thanks!

    Like

  17. Victoria – some of your stuff is SO close to what I want – maybe I am just missing the one I DO want. Essentially I want to pull GL entires for 2010 (or a selectable year) and for an account range – then have that link up to which Purchase Orders the payments go with so that I can provide the users with how much has been spent against which POs (and then other info about the PO & Vendor of course) – I was looking at linking GL20000 with POP10500 using the RCTnums… thoughts?

    Like

    • Jim,

      If all you want is information about PO’s and which were paid, etc., why involve the GL at all? Have you looked at my What Check Paid a Purchase Order view? Perhaps that’s a good starting point?

      If that’s not what you’re after, then you will have to give me more detail. At first, don’t worry so much about what tables to use or how to link them – what’s more important is understanding exactly what the purpose of the report is and what columns of data are needed.

      -Victoria

      Like

      • WONDERFUL start – Thanks!

        our GL accounts are set up so that Capitol accounts are in one range – departments in another range (in actuality another part of the key) – and am thinking a bit long term with a view / query & an SSRS report. with a number of parameters.

        SO – I am thinking I will link your query/view up with the POP30310 to get the detail and the account index – then I have detail totals AND can link all the way back to the GL – how far off am I? Granted I would need to rename a few of your fields since they are more header than detail, but it looks like a good idea for someone that does not do a bunch in the PO tables… I appreciate the thoughts!

        Like

  18. Hi Jason,

    Unfortunately, GP does not have any way of applying a payment to a Purchase Order. This is definitely something that I have seen asked before, but there is just no way to do it out-of-the-box. Payments can only be applies to posted Payables transactions. If you need the system to actually reflect this in the PO total, you’re going to need to look at a customization. If you simply need to be able to see that there was a payment, or print it on a report, perhaps a custom PO report would be enough.

    -Victoria

    Like

  19. Victoria,

    Hello. You have great information on your site.

    I have one question. We are trying to apply a partial payment to a purchase order. We pay most of our vendors 10% when they start the work and 90% when they ship it. We need to be able to apply that 10% payment to the purchase order when we pay it and have it deduct off the total outstanding PO total. Is there a way to do this?

    Any information that you can give me would be greatly appreaciated.

    Thanks,

    Jason

    Like

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP vendors with last activity date | Victoria Yudin - July 17, 2014

    […] Payables SQL Views […]

    Like

  2. SQL view to show AP apply information in GP | Victoria Yudin - October 3, 2013

    […] Payables SQL Views […]

    Like

  3. SQL view for current Payables aging detail in Dynamics GP - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - April 24, 2013

    [...] Payables SQL views [...]

    Like

  4. SQL view for current Payables aging detail in Dynamics GP | Victoria Yudin - April 24, 2013

    [...] Payables SQL Views [...]

    Like

  5. SQL view for vendor yearly totals in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 4, 2012

    [...] in the next few days, so keep an eye out. For more Dynamics GP Payables code, take a look at my Payables SQL Views page. Or check out my GP Reports page for views in other modules and additional report writing and [...]

    Like

  6. SQL view for vendor yearly totals in Dynamics GP | Victoria Yudin - January 4, 2012

    [...] Payables SQL Views [...]

    Like

  7. SQL view to show monthly totals for Dynamics GP Vendors - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - December 9, 2011

    [...] more Dynamics GP Payables code, take a look at my Payables SQL Views page. Or check out my GP Reports page for views in other modules and additional report writing and [...]

    Like

  8. SQL view to show monthly totals for Dynamics GP Vendors | Victoria Yudin - December 9, 2011

    [...] Payables SQL Views [...]

    Like

  9. SQL view for Payables apply detail and GL distributions in Dynamics GP « Victoria Yudin - September 23, 2010

    [...] can find other Dynamics GP Payables views here or check out my GP Reports page for other views and reporting [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,566 other followers

%d bloggers like this: