mouseguy

SQL view for Payables GL distributions in Dynamics GP


Here is a view that will return the General Ledger distributions for all posted or unposted payables transactions.  Unposted is also called ‘work’ and posted includes any open or historical transactions.  If you’re going to use this with SmartList Buidler, make sure to include the section at the end to grant permissions to DYNGRP.


CREATE VIEW view_AP_Distributions
AS

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--view_AP_Distributions
--Shows GL distributions for all AP transactions
--Created Nov 28 2008 by Victoria Yudin, Flexible Solutions
--For updates see http://victoriayudin.com/gp-reports/
--Does not exclude voided transactions
--Transactions with no MC information will show 
--   an exchange rate of 0
--Updated Feb 11, 2009 to correct link to GL account number
--Updated May 13, 2009 to add distribution reference
--Updated Nov 10, 2009 to add GP posting date and more 
--   user friendly column names
--Updated Jan 22, 2010 to add Document Date
--Updated Mar 10, 2010 to add Batch ID, Trx Description, 
--   and include unposted transactions
--Updated May 23, 2010 to add Voucher Number
--Updated Jun 25, 2010 to add additional distribution types, 
--   currency, exchange rate and originating amounts
--Updated Jun 27, 2010 to fix copying issue
--Updated Jun 30, 2010 to add Voided field
--Updated Dec 17, 2010 to add Vendor Name & GL Account Name
--Updated Jan 31, 2011 to add ROUND distribution type
--Updated Mar 8, 2011 to add PM10300 and PM10400 tables
--Updated Dec 20, 2011 to add PO Number
--Updated Oct 14, 2014 to fix GL posting date for Work trx
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT  D.VENDORID Vendor_ID,
	N.VENDNAME Vendor_Name,
	K.DOCNUMBR Document_Number,
	T.PSTGDATE GL_Posting_Date,
	K.DOCDATE Document_Date,
	CASE K.DOCTYPE
	     WHEN 1 THEN 'Invoice'
	     WHEN 2 THEN 'Finance Charge'
	     WHEN 3 THEN 'Misc Charge'
	     WHEN 4 THEN 'Return'
	     WHEN 5 THEN 'Credit Memo'
	     WHEN 6 THEN 'Payment'
	     END Document_Type,
	G.ACTNUMST Account_Number,
	A.ACTDESCR Account_Name,
	CASE D.DISTTYPE
	     WHEN 1 THEN 'Cash'
	     WHEN 2 THEN 'Payable'
	     WHEN 3 THEN 'Discount Available'
	     WHEN 4 THEN 'Discount Taken'
	     WHEN 5 THEN 'Finance Charge'
	     WHEN 6 THEN 'Purchase'
	     WHEN 7 THEN 'Trade Disc.'
	     WHEN 8 THEN 'Misc. Charge'
	     WHEN 9 THEN 'Freight'
	     WHEN 10 THEN 'Taxes'
	     WHEN 11 THEN 'Writeoffs'
	     WHEN 12 THEN 'Other'
	     WHEN 13 THEN 'GST Disc'
	     WHEN 14 THEN 'PPS Amount'
	     WHEN 16 THEN 'Round'
	     WHEN 17 THEN 'Realized Gain'
	     WHEN 18 THEN 'Realized Loss'
	     WHEN 19 THEN 'Due To'
	     WHEN 20 THEN 'Due From'
	     END Distribution_Type,
	D.DEBITAMT Debit_Amount,
	D.CRDTAMNT Credit_Amount,
	D.DistRef Distribution_Reference,
	T.BACHNUMB Batch_ID,
	T.TRXDSCRN Trx_Description,
	T.STAT Trx_Status,
	D.VCHRNMBR Voucher_Number,
	D.CURNCYID Currency_ID,
	coalesce(D.XCHGRATE,0) Exchange_Rate,
	D.ORDBTAMT Originating_Debit_Amount,
	D.ORCRDAMT Originating_Credit_Amount,
	CASE T.VOIDED
	     WHEN 0 THEN 'No'
	     WHEN 1 THEN 'Yes'
	     END Voided,
	T.PORDNMBR PO_Number

FROM
-- all open trx distributions
(SELECT  VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, 
         DSTINDX, DISTTYPE, DistRef, PSTGDATE, CURNCYID, 
         ORDBTAMT, ORCRDAMT, XCHGRATE
         FROM PM10100
UNION ALL
-- all historical trx distributions
 SELECT  P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, 
         P.CRDTAMNT, P.DSTINDX, P.DISTTYPE, P.DistRef, 
         P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, 
         M.XCHGRATE
         FROM PM30600 P
         LEFT OUTER JOIN  -- historical exchange rate
	   MC020103 M
	   ON P.VCHRNMBR = M.VCHRNMBR
	   AND P.DOCTYPE = M.DOCTYPE) D

-- add document number and type
LEFT OUTER JOIN
        PM00400 K
        ON D.VCHRNMBR = K.CNTRLNUM
        AND D.CNTRLTYP = K.CNTRLTYP

--add GL account number
LEFT OUTER JOIN
        GL00105 G
        ON D.DSTINDX = G.ACTINDX

--add status, batch, GL date and trx description
LEFT OUTER JOIN
     (SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'Work', VOIDED = 0, PORDNMBR, PSTGDATE 
      FROM PM10000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'Open', VOIDED, PORDNMBR, PSTGDATE
      FROM PM20000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'History', VOIDED, PORDNMBR, PSTGDATE
      FROM PM30200
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', PSTGDATE
      FROM PM10300
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN,
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', PSTGDATE
      FROM PM10400) T
     ON T.VCHRNMBR = D.VCHRNMBR
     AND T.CNTRLTYP = D.CNTRLTYP

--add GL account name
LEFT OUTER JOIN
        GL00100 A
        ON A.ACTINDX = D.DSTINDX

--add vendor name
LEFT OUTER JOIN
        PM00200 N
        ON N.VENDORID = D.VENDORID

--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_Distributions 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.

107 Responses to “SQL view for Payables GL distributions in Dynamics GP”

  1. Hi Victoria,

    This view is great for our needs. However, I’m having a problem with “work” batches not pulling in the posting date. The date shows as 1.1.1900 in the work back when our settings are to post by transaction as opposed to by batch. I usually have several months of late invoices flowing into our daily batches, so I need to see what their post date should be based on what was entered in the transaction entry window. The date shows up fine on the printed transaction edit list, but not on your view above.

    Any thoughts?

    Thanks
    Andrew

    Like

    • Hi Andrew,

      Thanks for catching that. I just updated this to take the posting date from a more reliable place (hopefully). :-) Please let me know if you find any other issues with this.

      -Victoria

      Like

      • Thanks! The dates show up correctly now.

        Do you know how to change the settings so that when I export the smartlist to excel, the debit and credit columns are automatically recognized as number format? This happens automatically with the default smartlists, but it has not with any of your views that I’ve added.

        It appears to think these columns are text format and I have to individually text-to-column each one in order to get excel to recognize it in number format.

        Any help is appreciated!

        Like

        • Andrew,

          Hmmm… I’ve not had the problem that you’re describing with any SmartLists I have created from my views. It has always correctly identified the columns correctly as numbers and exported just fine to Excel. If yours are being identified as text, I wonder if the issue is with SmartList Builder, or the export to Excel. If you look at the SmartList setup, what are the field options for those columns? Also, what version and SP of Dynamics GP are you on? And does this happen on all computers or just some?

          -Victoria

          Like

          • Victoria,

            I don’t even know how to get my “view” folders to show up on other users great plain logins! So I don’t know how to check on another computer.

            The field options are correct as currency.

            GP 2013 R2
            I don’t know what SP is, but we don’t own smartlist builder module. I just use the “modify” button right within SmartList window and that is where I go to add your views.

            Thanks
            Andrew

            Like

            • Andrew,

              It sounds like you may be using SmartList Designer, which is fairly new and was created by Microsoft as sort of a lower-end (and free) replacement for SmartList Builder. What you’re seeing may be a limitation of SmartList Designer, I have not worked with it enough to tell, sorry. You may want to check with your GP partner or Microsoft Support to see if they can help with that.

              -Victoria

              Like

  2. Thank you for this. It’s great. How would I go about adding the Intercompany ID for any intercompany transactions?
    Thanks, Kathie

    Like

  3. I don’t know if anyone else needed this but I know it helped me a lot. We use are accounting segments as sites/locations. So, Segment 1 would mean a specific ‘customer’ or building of ours. Adding in the segments allowed me to break down this AP aging by site. Just thought I’d share!

    I added this after the “G.ACTNUMST Account_Number,” code.

    G.ACTNUMBR_1 Segment_1,
    G.ACTNUMBR_2 Segment_2,
    G.ACTNUMBR_3 Segment_3,

    Thanks,
    John Olley

    Like

    • John,

      That’s great, thank you for sharing this with us!

      -Victoria

      Like

      • Hi Victoria – is it possible/relatively easy to add extra PO information to this view? Like PO receipt #, Item # from PO receipt, item description? Great work.

        Like

        • Hi Jay,

          It’s possible, but not necessarily easy. Most of the complexity is going to come from the fact that there is not necessarily a one-to-one relationship between items on a PO receipt and GL distributions on the related invoice. You can also have multiple PO receipts linked to one invoice. If you simply link the information together, you are going to get a lot of duplicate data. You would want to determine exactly how and what you want to show before even looking at coding this.

          -Victoria

          Like

  4. Is there a way to add the check cut date to this view? Thanks,

    Like

    • Hi Larry,

      GP does not actually store something called ‘check cut date’. There is a modified date and a posted date, but there is no reason that either of these should be the date the check was actually cut.

      -Victoria

      Like

      • The Document Date is the invoice date, and the other date field that I see is a GL Post Date which isn’t the date the check was printed.

        Like

        • Larry,

          For Payments, the Document Date is the date printed on the checks.

          -Victoria

          Like

          • You may want to double check that. When I did my validation testing using that field, it was determined that ‘Document Date’ field is, in fact, the ‘Invoice date’ entered for the ‘Purchase’ Distribution. The date is correct for the ‘Cash’ and ‘Payable’ distribution lines.

            Like

            • Hi Larry,

              Sorry, maybe we are not talking about the same thing. This view only shows payables transactions (all of them) and their details, including GL distributions. It does not link payments to invoices or anything like that. So…if you run this view, for any transaction with a Document Type of Payment, the Document Date will be the Check Date.

              -Victoria

              Like

  5. Hi Victoria,

    Does this query run the entire AP database? Is there a way to put in a date restriction – say for 1 month?
    I want to be able to take this query and turn it into a report so that a user may select any date range.

    Thanks so much!

    Like

  6. Hi Victoria,
    We have 4 Companies in Dynamics GP 2010. I added the view_AP_Distributions to one Company Database (Company A) and created a SmartList in SmartListBuilder. When I access that SmartList from any Company A B, C or D, I get Data from Company One. Do I have to add this view to all four company Databases or it’s just a security setting. Please advise. Any help is appreciated.
    Thanks

    Like

    • Hi Iqbal,

      Yes, the view has to be created in each company database in SQL. Additionally, you would need to set up the SmartList security to allow it access to the view in each company and you would need to make sure that your SmartList has the Use Company Database checkbox selected so that it automatically uses the view from the company you are logged into.

      -Victoria

      Like

  7. Perfect view which saved lots of my time……… Could do amazing reconciliations with different criteria. Keep going………..

    Like

  8. Hi Victoria
    Thanks for posting this SmartList Builder code. I’m a Newbie here (maybe even newer than a Newbie……) This SmartList report sounds exactly like what I’m looking for but I am very inexperienced at SmartList Builder, I’ve only done really simple things and did not use SQL because I don’t really know anything about it. So my question is, how do I take the code you wrote and get it into SmartList Builder so that I have a SmartList I can run?
    Thanks in advance for any help you can give me and my apologies if this isn’t a “smart” question!
    Judy

    Like

    • Hi Judy,

      That question is so great, I even have a blog post for it already! :-) This should hopefully answer the questions you have: http://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/.

      -Victoria

      Like

      • Victoria,
        Thank you for sharing this script to creat this View. My supervisor has been bugging me to create somethign like this, and this was exactly what I needed. How can I add the GP Database ID or Company name to this view?

        Like

        • Hi Chad,

          Are you looking to create a multi-company view from this? If so, and if you do not have too large of a number of databases, take a look at this example for creating a multi-company view.

          If you’re looking to do something else, please give me some more details and I will try to help.

          -Victoria

          Like

    • Hi Victoria, Thanks for your reply:

      Hi Judy, That question is so great, I even have a blog post for it already! :-) This should hopefully answer the questions you have: http://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/. -Victoria

      My issue is that I do not have access to the SQL Server Management Studio so cannot create an SQL view. Is there another way to build this SmartList using SmartList Builder and selecting either Microsoft Dynamics GP Table or SQL Server Table?
      Thanks
      Judy

      Like

      • Hi Judy,

        There is no other way to do this. If you do not have access to SQL Server Management Studio, then you need to work on this with someone that does have access.

        -Victoria

        Like

        • Thanks for your help, Victoria!
          Judy

          Like

          • Hi Victoria,
            Sorry to bother you again …. our IT department is reluctant to allow access to the SQL Server because of the potential of really messing something up. Are there different levels of security that can be set? My current need is to get the AP Distribution view script that you’ve posted into SmartList Builder. Is it possible to get limited security access to the SQL Server Management Studio to do only that, or is that not how SQL works? (I don’t really have knowledge of SQL, obviously). I’m am a bit afraid of having too much access in case I inadvertently mess up badly, but I’d really like to be able to create SmartLists, as needed, to get information & reports out of GP. Any thoughts or suggestions you might have would be welcome.
            At present, only our IT people have access but if I can avoid bothering them every time I need something from SmartList Builder that requires code be put into SQL, it would be good!
            Thanks, Judy

            Like

            • Judy,

              If you have never user SQL Server and SQL Server Management Studio before, I would not recommend doing so without any training or help from someone that is more comfortable with it. And I agree with your IT department and their reluctance. It would take about a minute of their time to create this view for you on the SQL Server. That’s all they need to do and then you can do the rest in GP. I would recommend that you ask them to do this for you.

              -Victoria

              Like

  9. Hi Victoria,

    I am looking to add PORDNMBR from the already existing tables PM10000, PM20000, and PM30200 within the script. What is the best way to update the script to include PORDNMBR?

    Thanks in advance.

    Like

  10. Hi Victoria,
    I see this is for all posting from payables but have a question. If I want to get all payments made within my company so I can find out where the money goes, would this view include all that? So, would it include all payments made for purchasing raw goods, salaries, utilities, etc? If not, can you recommend from which tables (and modules groups) I would get that from?
    Thanks so much!

    Like

    • Hi Sherry,

      What you’re asking for sounds like a Cash Flow Statement, and usually this is done on a GL basis using something like FRx or Management Reporter. I am not sure if I would be looking for this in the tables, unless you want something very specific from this?

      -Victoria

      Like

  11. Victoria-

    I think you answered this, but I am unclear. If you have PM trx that are marked as IC how do you get the distributions that post to the other company? ie- payable is entered in company A with dist to company B. we have a smartllist similar to your view (going to scrap and use yours) but it appears to pull the acct index based on company A. what is the link to get the correct IC acct?

    Like

    • Hi Tom,

      Gordon asked about this in January, so that you don’t have to search for it, I am copying my answer to him below:

      “That would require a lot of additional coding. Off the top of my head, I would think you would first need to link from the payables transactions that are marked as intercompany to the GL in that company, then link to the GL of the other companies to get the distributions.

      I don’t anticipate that this is something I would be posting any time soon, but if you start coding this and run into issues, let me know and I will try to help.”

      -Victoria

      Like

  12. Hi Victoria

    Have you dabbled with the AA tables yet?
    I’m looking to include the AA Transaction dimension and the AA dimension code info to our Paybles reports. We’re using this script very succesfully each month and just started using AA hence now the requirement to report Payables info with the AA code.

    Please let me know if its possbile to include it?

    Like

    • Hi Lulu,

      AA is a monster module in terms of table as well as options available, so I am not sure how easy it would be to do something generic, like the other code I have been publishing on my blog. Something like what you are looking for would most likely need to be a custom report created for you specifically.

      -Victoria

      Like

  13. Hi Victoria

    I’ve discovered something with this view relating to payments.

    When creating a supplier’s check either through a Manual payment or a Computer check the following fields do not update for Work transactions. The fields are:

    1. Batch_ID
    2. Trx_Description
    3. Trx_Status
    4. Voided

    Currently, the field is displayed as NULL. It only happens with payment transactions. Other supplier document types are working fine.

    It is possible to update these fields please?

    Like

    • Hi Lulu,

      This view does not actually link to the unposted payment tables (either computer checks or manual payments), that’s why you’re seeing the results you are describing.

      I originally added the PM10000 table (which holds unposted transactions, but not payments) as a request from Lisa about a year ago. I will take a look to see if I can add the unposted payment tables. I’ll post an update when I have a chance to look at this.

      -Victoria

      Like

  14. Hello Victoria

    I’ve discovered that the ROUND distribution type is missing from the SQL view. Is it possible to add it please?

    Thanks in advance,
    Lulu

    Like

  15. Hi Victoria,

    I have a client that has a number retail stores in their distribution chain. This client pays vendors directly for product purchased for all stores. The stores are identified uniquely by the first segment of the account number (4 bytes).

    They want a report that displays open vendor payables by store. I chose your view_AP_Distributions as it had the account numbers I needed to identify the stores. I assumed that comparing to the AP Trial Balance would be a good check. I selected totals from the view by vendor and found almost all of them equal to the ‘Due‘ amount in the ‘Vendor Totals’ line of the summary trial balance. That made me think I was on the right track to select totals by store and vendor until I found 6 vendors where my totals did not match the trial balance total.

    I snooped around a found the PM10200 table. In the table I found transactions for 5 vendors that when added to my script totals balance out to the trial balance. However, this did not work for the 6th vendor.
    Is there another table that I can bring in to find the difference for the 6th vendor?

    Thank you very much for your reply and this great site.

    Like

    • Rodney,

      One thing to note is that this view is including unposted transactions, which a trial balance normally should not be doing.

      Typically, to match to a trial balance, I would get all the open (unpaid) transactions, then link to the distributions from there. I think starting with the distributions is what is throwing me.

      -Victoria

      Like

  16. The view_AP_Distributions worked great for me. I was hoping to tie out to the Payables Trial Balance and did for 375 of my 381 vendors. There were 5 vendors that I was then able to tie out to after bringing in the PM10200 table. Only 1 vendor am I not able to tie out to the penny. Of course it would happen to be my largest vendor. Any suggestions on other tables I should look at to get my last vendor to balance with the PTB? Thank you very much.

    Like

    • Hi Rodney,

      I am not quite clear on how exactly you are using the distribution information to “tie out” to your AP trial balance. Can you please give some more detail on specifically what you are comparing?

      -Victoria

      Like

  17. Hi Victoria,

    Is there a way this SQL View can display intercompany transactions? I have it set up for a multi-company situation, but it only gives the GL distributions for the orginating company and not the receiving company.

    For instance, our Parent Company often pays bills for the 3 “child” companies. The way this view is set up, it will display the GL account distributions (which is usually just A/P and the Intercompany account) for the Parent, but will not display what the distributions are on the Child company’s books.

    Thanks and regards,

    Gordon

    Like

    • Hi Gordon,

      That would require a lot of additional coding. Off the top of my head, I would think you would first need to link from the payables transactions that are marked as intercompany to the GL in that company, then link to the GL of the other companies to get the distributions.

      I don’t anticipate that this is something I would be posting any time soon, but if you start coding this and run into issues, let me know and I will try to help.

      -Victoria

      Like

  18. Hi Victoria,

    Hope you are having a happy and healthy holiday season.

    I finally, after all of this time, have a need for this SQL View. It has almost all of the info I need. However, I would like to be able to add Vendor Name from table PM00200 and have tried to do it myself, but unsuccessfully.

    How would I go about doing that?

    Thanks and regards,

    Gordon

    Like

  19. This is fantastic – very helpful. Would you also be able to show the AP check number and apply date and apply amount with another join? I’m in desperate need of that if you can do that! Thanks so much!

    Like

  20. Victoria,

    As always, this is nothing short of amazing!

    Leslie

    Like

  21. Hi Victoria – I have been looking at your sql views relating to ap transactions – invoices and checks that paid them, and the gl distributions. I am looking for a view that shows the invoice, check that paid it and the distributions – but to further complicate it I need to show the breakdown of the fixed allocation account. So I’m not really interested in seeing the fixed allocation account but woiuld like to see the actual distribution accounts and amounts. Kind of like the Account Transaction Smartlist but with the vendor info/check number. Do you have anything like that.

    Like

  22. Hi Victoria,

    Thanks so much. The view was extremely helpful. I have a quick question…I used this for Smartlist Builder and it seems to be returning the same data no matter what company I run it for. The company database seems to be hardcoded in the sql statement. Do you know how we can copy and paste this into different company databases and it recognize the correct company? Thanks for your help.

    Like

  23. Hi Victoria

    I have a few more required fields for this very helpful view you created. Is it possible to add the following fields please:
    Currency ID
    Exchange rate
    Originating Debit Amount
    Origination Credit Amount

    Thanks in advance. Your help is much appreciated!

    Like

    • Lulu,

      You got it – the code above has been updated. Please make sure to test this with your data to verify that it works as expected.

      -Victoria

      Like

      • Hello again Victoria

        Thanks for the updated script. It worked fine and my data tests proved correct.

        Can I ask for one more addition please, please, please—-VOID STATUS.

        I promise this will be the last request for awhile :-) You’ve been a great help.

        Lulu

        Like

    • Hi Victoria

      Thanks for the updated. Unfortunately, I cannot create the view successfully. Below I’ve copied & pasted the error messages from the sql query

      Msg 156, Level 15, State 1, Procedure view_AP_Distributions, Line 71
      Incorrect syntax near the keyword ‘SELECT’.
      Msg 102, Level 15, State 1, Procedure view_AP_Distributions, Line 86
      Incorrect syntax near ‘)’.
      Msg 102, Level 15, State 1, Procedure view_AP_Distributions, Line 110
      Incorrect syntax near ‘T’.
      Msg 15151, Level 16, State 1, Line 1
      Cannot find the object ‘view_AP_Distributions’, because it does not exist or you do not have permission.

      Are you able to have a look at it please.

      Thanks,
      Louella

      Like

  24. Hey Victoria

    Great website & thanks for posting this. Is it possible to add the VCHRNMBR field to the view itelf so that it displays in the results.

    Thanks in advance,
    Lulu

    Like

  25. Hi Victoria,

    I love this view, but one question I do have is what if I need to include the Batch Name and/or the Transaction Description from the PM10000 or PM30200, depending on if the transaction is posted or unposted.

    I see you use a select UNION ALL statement to create a single table for the details tables. I tried to do the same for the header tables, but I must be missing something in my UNION ALL query statement for the header tables because it is erroring out on me.

    Like

    • Hi Lisa,

      This view originally did not include unposted transactions. I have changed it to include them and also to pull in a batch ID, transaction description and show a status of ‘work’, ‘open’ or ‘history’.

      Hope this helps with what you’re looking for.

      -Victoria

      Like

  26. Hi Victoria,

    I love this view! Is there any chance that you can add a posting date field to it?

    Thanks

    Like

  27. Wondering if you had just a simple list of the different doctype ID’s for AP? I need to create custom AP reports. Thanks.

    Like

  28. Хорошая работа!

    Like

  29. Hi Victoria,

    I wrote a similar query to include the transactions date from the header file so that my user can put in a range of dates.

    Didn’t see this until a collegue pointed me to this. I could have saved an hour digging around time!

    Glad to see I was on the right track.

    S

    Like

  30. i have solved the seqnumbr in rm10101.
    how do i get the disttype

    Like

  31. 1) which table stores the disttype for rm distribution on table rm10101

    2) where does GP get the SeqNmbr for table rm10101 during distribution. i have searched to no avail

    Like

  32. Thanks Victoria!!!! That did the tick. And your cautions are duly noted.

    Like

  33. Hi,
    Thank you for the query
    I am working on something similar where need to extract the associated purchase distribution account from the invoice(s) applied on a check and then group by for example (Account Category) from
    GL Account table

    Can you offer guidance on how to modify view and achieve those results?

    Thank you

    Like

  34. Victoria,

    This view is very helpful for a report I am writing! How would I exclude voided transactions?

    Like

    • Nel,

      Voided transactions simply show the GL distributions of the void on additional lines, so by not excluding them you’re getting the original transaction plus the void, which should net out to zero. There are 2 reasons that you may want to leave the voided distributions: 1) the void might be on a different date and 2) the void might have gone to different accounts that the original transaction. Leaving them in would allow you to see that. However, if you wanted to take all distributions for voided transactions completely out, change the section that is under “– all historical trx distributions” to this:

      SELECT PD.VENDORID, PD.VCHRNMBR, PD.CNTRLTYP, PD.DEBITAMT,
      PD.CRDTAMNT, PD.DSTINDX, PD.DISTTYPE
      FROM PM30600 PD
      LEFT OUTER JOIN PM30200 PH
      ON PH.VCHRNMBR = PD.VCHRNMBR
      AND PH.CNTRLTYP = PD.CNTRLTYP
      WHERE PH.VOIDED = 0) D

      -Victoria

      Like

  35. Hi Victoria,

    Thanks for the query, this is very helpful for an SSRS report I’m creating. I found one small correction, though. In the last statement you should join GL00105 to the PM tables on DSTINDX rather than DISTTYPE.

    Thanks!

    Like

  36. Robert,

    That’s a pretty difficult one and I do not have it yet. The AP Historical Aging is on our list of reports to develop for sale as part of our GP Reports series.

    –Victoria

    Like

    • I know this is really old, but did you ever develop one that also includes AP Historical Aging? I would definitely be interested in buying the GP report series if so. Is there a list of what reports are in this pack? Where do I buy it?? :)

      Thanks,

      -John Olley

      Like

      • Hi John,

        The GP Reports series never really panned out – it seems no one wants canned reports, everyone wants something a bit different. :-) However, I have created a number of AP Historical Aging reports over the last few years, so if you are interested in one created for you, please let me know and I will email you directly.

        Thanks,
        -Victoria

        Like

  37. I was wondering if you have a SQL query for historical AP Aging?

    Like

Trackbacks/Pingbacks

  1. AP aging in Excel thanks to Victoria Yudin in Microsoft Dynamics GP - Belinda, the GP CSI - Microsoft Dynamics GP - Microsoft Dynamics Community - August 5, 2013

    […] If you haven’t visited Victoria’s blog, you should.  It can be found @ http://victoriayudin.com/ and this view in particular @ http://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/. […]

    Like

  2. Victoria Yudin - September 28, 2010

    Updates to SQL view to show all GL distributions for AP transactions…

    I have made a number of updates to my SQL view to show all GL distributions for AP transactions since…

    Like

  3. Victoria Yudin - September 28, 2010

    SQL view for Payables apply detail and GL distributions in Dynamics GP…

    Who doesn’t need yet another view for Payables transactions in Dynamics GP? The view below is a…

    Like

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

    [...] transactions in Dynamics GP?   The view below is a combination of my Payment Apply Detail and GL Distributions for AP Transactions views. It lists all Payments, then shows the transactions they were applied to and the GL [...]

    Like

  5. Updates to SQL view to show all GL distributions for AP transactions - Victoria Yudin - June 25, 2010

    [...] have made a number of updates to my SQL view to show all GL distributions for AP transactions since I first published it. Some of these were in response to comments asking for additional [...]

    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,583 other followers

%d bloggers like this: