November 28, 2008
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, Inc.
--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 and 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
--*************************************************
SELECT D.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
D.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 and trx description
LEFT OUTER JOIN
(SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Work', VOIDED = 0, PORDNMBR
FROM PM10000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'Open', VOIDED, PORDNMBR
FROM PM20000
UNION
SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
STAT = 'History', VOIDED, PORDNMBR
FROM PM30200
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
STAT = 'Work', VOIDED = 0, PORDNMBR = ''
FROM PM10300
UNION
SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN,
STAT = 'Work', VOIDED = 0, PORDNMBR = ''
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.
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.
Hi Danny,
I just updated the script to add this for you.
-Victoria
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!
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
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?
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
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?
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
Hi Victoria
Yes! I’ve met this monster-of-an-AA
Thanks as always for replying.
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?
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
Hi Lulu,
I have made some changes to the view to bring in that additional information for the unposted payments. Please let me know if that fixed the NULLS for you.
-Victoria
Woohoooooo Victoria!
Its working well. Thanks a stack! Your help is always appreciated
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
Hi Lulu,
Do you know what DISTTYPE number corresponds to the ROUND distribution type?
-Victoria
Hello again Victoria
I checked the transation in the PM30600 and th DISTTYPE = 16 for ROUND.
Regards,
Lulu
Hi Lulu,
Thanks for looking that up. I have added it to the code above.
-Victoria
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.
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
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.
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
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
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
OK, thanks Victoria, I will keep you posted.
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
While we’re at it… GL Account Name (or Description) from GL00100, too.
And done!
Happy Holidays!
-Victoria
You rock! Thanks so much.
Happy Holidays to you and yours as well!!!
Gordon,
Done!
-Victoria
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!
Lisa,
This is not the first time I have been asked that, so it sounds like a good idea for the next blog. Give me a little time to come up with that and keep an eye out for new blog posts.
-Victoria
Hi Lisa,
Take a look at my latest post – SQL view for Payables apply detail and GL distributions in Dynamics GP – hopefully this will help with what you’re looking for.
-Victoria
Victoria,
As always, this is nothing short of amazing!
Leslie
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.
Hi Wendi,
I do not have a view with what you’re asking for…to get that you would need to combine this view with one of these:
Apply Information
Payment Apply Detail
You don’t have to worry about seeing any allocation accounts – they don’t actually ever get posted to, only the actual distribution accounts will be in the tables once the transactions are posted.
-Victoria
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.
Hi Yvette,
To use this for multiple GP companies you would need to create the view in all the companies and then set up your SmartList to Use the Company Database. Take a look at my post on using a SQL view in SmartList Builder for step by step instructions. If you have more questions, please let me know.
-Victoria
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!
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
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
Lulu,
You got it. I added a Voided column at the end.
-Victoria
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
Louella,
Sorry about that, something went wrong in the copying process between SQL and the blog post. It should be fixed now, can you please try it again?
Thanks,
-Victoria
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
Lulu,
That’s a great idea, thanks. I just added Voucher Number to the end.
-Victoria
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.
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
Hi Victoria,
I love this view! Is there any chance that you can add a posting date field to it?
Thanks
Ross,
You got it. I just updated the view to include it and also add some more user friendly column names.
-Victoria
Wonderful! Thanks Victoria.
Wondering if you had just a simple list of the different doctype ID’s for AP? I need to create custom AP reports. Thanks.
Ethan,
Take a look at my PM Tables page, that should have everything you need.
-Victoria
Хорошая работа!
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
i have solved the seqnumbr in rm10101.
how do i get the disttype
George,
To my knowledge, the DISTTYPE is not stored in a table. You can get a list of all the DISTTYPE values on the RM Tables page of this blog.
-Victoria
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
Thanks Victoria!!!! That did the tick. And your cautions are duly noted.
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
Michael,
It sounds like you would want to combine code in this view and the view I have posted for AP Apply Information. In fact, I would start with the Apply Information view, to get the list of the documents you need first, then add in code from here to link in the distributions.
-Victoria
Victoria,
This view is very helpful for a report I am writing! How would I exclude voided transactions?
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
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!
Jessica,
You are absolutely right. Thank you very much for the correction! I have updated the code above to incorporate this.
–Victoria
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
I was wondering if you have a SQL query for historical AP Aging?