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
--For updates see https://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
--Updated Feb 17, 2015 to add 1099 amount
--Updated Sep 14, 2017 to change DOCDATE to pull from
-- transaction tables and to clean up the code a bit
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select
D.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
T.PSTGDATE GL_Posting_Date,
T.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,
T.TEN99AMNT [1099_Amount]
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 MC020103 M -- historical exchange rate
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/doc dates and trx description
left outer join
(select VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, DOCDATE,
STAT = 'Work', VOIDED = 0, PORDNMBR, PSTGDATE, TEN99AMNT
from PM10000
union
select VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, DOCDATE,
STAT = 'Open', VOIDED, PORDNMBR, PSTGDATE, TEN99AMNT
from PM20000
union
select VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, DOCDATE,
STAT = 'History', VOIDED, PORDNMBR, PSTGDATE, TEN99AMNT
from PM30200
union
select PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
DOCDATE, STAT = 'Work', VOIDED = 0, PORDNMBR = '',
PSTGDATE, 0
from PM10300
union
select PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN, DOCDATE,
STAT = 'Work', VOIDED = 0, PORDNMBR = '', PSTGDATE, 0
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.
Victoria Yudin 
Hi Vitoria
I’m try to get MID of GL account
How can I use SELECT MID in this script?
it’s giving me error
SELECT MID(D.DSTINDX, 6, 3) AS ExtractString
From GL00105
LikeLike
Nihad,
You can use SUBSTRING….like this:
select SUBSTRING(ACTNUMST,6,3) from GL00105
-Victoria
LikeLike
I ran this but it does not seem to pull paid transactions…any ideas as to why? Seems this would pull all transactions since it says it will pull work, open & historical.
LikeLike
Hi Jason,
When I run this against my Dynamics GP data, it definitely brings back paid transactions. Can you check to see if you have data in the PM30200 table and the PM30600 table? If both of those have data, this view should pull it. If that’s not working, please check to make sure there were no changes to the code on my blog.
-Victoria
LikeLike
Hi Victoria,
I really liked the idea of creating this view. But once I ran opened the view, I received an error:
“Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.”
We are running GP 18.2 – just upgraded a couple of weeks ago.
Thoughts?
Thanks
LikeLike
Hi John,
I’ve not seen an issue with this view before, so I am not sure what is causing the error you’re seeing. Can you please give me some more detail on what you are doing?
You created the view in SQL and that didn’t give you any errors?
Then what exactly are you doing and where?
Also, what version of SQL are you using?
-Victoria
LikeLike
This is fantastic. Is there a way to add vendor class id to it? I tried adding it to the bottom:
–add vendor name
left outer join PM00200 N
on N.VENDORID = D.VENDORID
and N.vndclsid = D.vndclsid
and added N.vndclsid to the beginning list of columns, but it is giving me an error on the bottom line.
LikeLike
Hi VHC,
Try removing it from the join logic at the bottom and just leave the “N.vndclsid” at the top in the list of fields – that should do it.
-Victoria
LikeLike
It does, thanks!
LikeLike
Do you have a version that doesn’t use MC020103? That table is not active in our database.
LikeLike
Hi Terri,
By “not active” do you mean it is not there? Or just not used?
-Victoria
LikeLike
Please disregard this question
LikeLike
When I put this in, I get an error that says line level comments cannot be entered in SQL scripts.
LikeLike
Hi Nicole,
Where exactly are you putting this in?
-Victoria
LikeLike
I am just getting around to getting back to this.
I am putting it in the smartlist builder SQL script. Is this not the right place? I am fairly new to smartlist builder.
LikeLike
Hi Nicole,
You would need to create the view in SQL first, then you can use that view in SmartList Builder.
-Victoria
LikeLike
Hi Victoria, I’ve created a modified view based on your view and am using it in SmartList Designer. All is fine except formatting of the dates. I tried several formatting options in the view itself which work visually but not in the search function and in fact even when I leave the date with no formatting I still cannot search and retrieve a range. Is there a trick or something that I’m missing?
Thanks,
Debi
LikeLike
Hi Debi,
I have not seen this be an issue, so I am not sure what the problem might be without looking at it. Are you maybe using an older version/build of SmartList Designer? I know if was not so great in the first few versions.
If you want to send me your code and let me know what version/build of GP, I can try to test it to see if I get the same problem.
-Victoria
LikeLike
Victoria, I was able to figure the date issue out and I did have the wrong Convert syntax. Thanks for your time!
LikeLike
Hi am new to the smartlist ,getting error while exporting in to excel.i guess coalesce & isnull is not used may be.Any guess?can you please post the same query with removing special character in colomns
LikeLike
Siddhu,
What is the exact error that you are getting? What version of GP? What version of Excel?
-Victoria
LikeLike
Victoria – I always come to your site for GP table info and just had not paid attention to some of the other things – today I found this beauty (deployed it) and come out looking like a ROCK STAR when it is YOU that ROCKS! Thanks SO much… now to open my eyes a bit while on the site.
LikeLike
Thanks Jim!
It is very gratifying to know that this stuff actually helps others. Thank you for your kind words!
-Victoria
LikeLike
We are using this query and now I have a request to add in the trxdate from the gl10000 table but have no idea how this query can link to that table. Can you help please?
LikeLike
Tim,
The GL_Posting_Date (which is already in the code) in the payables transactions becomes the TRXDATE in the GL tables.
-Victoria
LikeLike
I was hoping you would say that…Thank you very much.
LikeLike
Hey Victoria, how would I modify this view so that I only see all posted payables transactions with GL Distributions, no work?
LikeLike
Hi Bill,
Here is a modified select script taking out the unposted transactions:
select
D.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
T.PSTGDATE GL_Posting_Date,
T.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,
T.TEN99AMNT [1099_Amount]
from
(select VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE, CURNCYID,
ORDBTAMT, ORCRDAMT, XCHGRATE
from PM10100
where PSTGSTUS = 1
union all
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 MC020103 M -- historical exchange rate
on P.VCHRNMBR = M.VCHRNMBR
and P.DOCTYPE = M.DOCTYPE) D
left outer join PM00400 K
on D.VCHRNMBR = K.CNTRLNUM
and D.CNTRLTYP = K.CNTRLTYP
left outer join GL00105 G
on D.DSTINDX = G.ACTINDX
left outer join
(select VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, DOCDATE,
STAT = 'Open', VOIDED, PORDNMBR, PSTGDATE, TEN99AMNT
from PM20000
union
select VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, DOCDATE,
STAT = 'History', VOIDED, PORDNMBR, PSTGDATE, TEN99AMNT
from PM30200) T
on T.VCHRNMBR = D.VCHRNMBR
and T.CNTRLTYP = D.CNTRLTYP
left outer join GL00100 A
on A.ACTINDX = D.DSTINDX
left outer join PM00200 N
on N.VENDORID = D.VENDORID
-Victoria
LikeLike
Hi Victoria,
Thanks for your helpful solutions! We’ve installed this code and the smartlist is very helpful. Can this be tweaked to do the same thing for the Sales module?
LikeLike
Hi Matthew,
I think this is what you’re looking for: SQL view with all GL distributions for AR transactions
-Victoria
LikeLike
Have you ever had success with splitting the distributions and the amounts out into lines?
I was thinking of adding a Case Statement, but it assigns the distributions to multiple rows instead of 1 line.
Something like this:
(Case when t.doctype = 1 and d.DSTSQNUM = 16384 and d.DEBITAMT >0 then d.DEBITAMT
when d.DSTSQNUM = 16384 and d.CRDTAMNT >0 then d.CRDTAMNT-1 else 0 end) as Dist_1,
(Case when t.doctype = 1 and d.DSTSQNUM = 32768 and d.DEBITAMT <>0 then d.DEBITAMT
when d.DSTSQNUM = 32768 and d.CRDTAMNT <>0 then d.CRDTAMNT-1 else 0 end) as Dist_2,
(Case when t.doctype = 1 and d.DSTSQNUM = 49152 and d.DEBITAMT <>0 then d.DEBITAMT
when d.DSTSQNUM = 49152 and d.CRDTAMNT <>0 then d.CRDTAMNT*-1 else 0 end) as Dist_3,
(Case when d.DSTSQNUM = 16384 then G.ACTNUMST else ” end) as Dist_1,
(Case when d.DSTSQNUM = 32768 then G.ACTNUMST else ” end) as Dist_2,
(Case when d.DSTSQNUM = 49152 then G.ACTNUMST else ” end) as Dist_3
LikeLike
Jessica,
Unfortunately, you cannot rely on the sequence numbers always being the same. If someone deletes a distribution line or inserts one, the numbers will be different. So I would not advise hard-coding them. I am not quite sure what you’re trying to do – can you give an example of your desired results?
Thanks,
-Victoria
LikeLike
They want to be able to push this Payable information into 1 file and 1 transaction line.
Batch ID
Voucher Number
Vendor ID
Vendor Name
Document Date
Document Number
Invoice Total Amount
Description
Payment Terms
Check Number (EFT)
Check Date
Receipt Number
1 Distribution Account Field
1 Distribution Amount Field
1 Distribution Description Field
2 Distribution Account Field
2 Distribution Amount Field
2 Distribution Description Field
3 Distribution Account Field
3 Distribution Amount Field
3 Distribution Description Field
LikeLike
What if there are 10 distributions on one transaction?
LikeLike
I have a question. I had run a batch of checks and noticed the information was different and corrected information in the vendor before I had posted the checks. The batch failed and required recovery only when you try to recover the batch it will not let you. It still says there is an error and to use the batch recovery window. When I brought the batch up the total of the batch was the amount of the vendor correction invoice, so I figure this must be why it is failing to post. I don’t know what to do to get that to post.
LikeLike
Hi Julie,
As a general rule, once a check batch is created, any changes made to the vendor will not update the check batch. You would need to delete that particular check (or void it, if it has already been printed), then fix the vendor, then re-create the check.
In your particular case, this could be causing the issue you’re seeing or there might be something else going on. I am not sure it’s possible to fix without looking at your data. I would recommend reaching out to your GP partner or Microsoft support for help.
-Victoria
LikeLike
Hi Victoria –
Where exactly do I insert the line to exclude transactions in History? you said in one of your replies to add this line: select * from view_AP_Distributions where Trx_Status = ‘Open’.
Thanks!
LikeLike
Oumar,
Once you create the view using my code, you can then use that line in a new query window.
-Victoria
LikeLike
Hi Victoria,
I am attempting to create a similar/identical view, however, with a join to the GL20000 and GL30000 tables. The above seems to stay within the PM set of tables. Is there a reason for this? Is my approach at to join to GL20000/GL30000 flawed? I have found some inconsistences in my data, mainly when GL20000.ORCTRNUM <> PM30200.VCHRNMBR. Also, Some of my GL20000.ORTRXTTYP = 0 when originating from AP.
But possible values for this field I recognize
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’
Not sure how this field could ever get to 0 from PMTRX. Any advice you could provide would be much appreciated, thanks!
LikeLike
Frank, without seeing your code and possibly your data, it is pretty difficult to give advice on code.
Looking at our data, the ORTRXTYP column is 0 in GL20000 for the intercompany portion of the payables distributions. The TRXSORCE in this case will start with ICTRX.
-Victoria
LikeLike
Hi Victoria, thanks for the quick response! Understood it’s tough to provide guidance without the code. Any speculation on when GL20000.ORCTRNUM <> PM30200.VCHRNMBR when originating source is PM? It’s so few entries this is the case so I feel it’s some kind of an anomaly, but nonetheless causing me issues.
Again, this is much appreciated!! 🙂
LikeLike
Frank,
Is the GL20000.ORCTRNUM blank? Or does it have a value that is incorrect? If it’s blank, is it for the same records that have ORTRXTYP = 0? If so, again, this will be the intercompany records. If it’s not blank, have you checked the PM00400 table? Is the ORCTRNUM in there in the CNTRLNUM column, if yes, what is the DCSTATUS?
-Victoria
LikeLike
Hi Victoria,
GL20000.ORCTRNUM does indeed have a value. Checking PM00400, I do see the ORCTRNUM in there as CNTRLNUM. There are actually two rows, which I understand because CNTRLNUM + CNTRLTYP/DOCTYPE make up the composite key. Both DCSTATUS = 3. The invoice, DOCTYPE = 1, is the CNTRLNUM I’d expect to find in PM30200, however It does not exist there.
LikeLike
Hi Frank,
DCSTATUS = 3 means it should be in PM30200. One reason it would not be there is that data has been deleted. 😦
-Victoria
LikeLike
deleted… oh no!!! is this possible through the user interface? cannot imagine someone running SQL deletes on these tables.
Also, looking again at some of the other cases – not all ORCTRNUM from PM exist in PM00400 in CNTRLNUM. What would this mean?
Thanks again for all your help!
LikeLike
Frank,
Yes, there are utilities that would let data be deleted through the user interface. I recommend against these as a general rule and have not used them myself. However if a utility was used in GP to clear the data, I think it would have also removed the record from PM00400. You would have to do some testing on that to confirm. So this would most likely explain the records where the control number is also not in the PM00400 table.
The ones that are in PM00400 but not in PM30200, it’s possible that either something went wrong during posting, or these were deleted in the database directly. I am assuming you have already checked related tables like PM20000 and PM10000 and they are not in there?
If these are recent transactions you could try to restore a recent backup and see if they are there…
-Victoria
LikeLike
Can you give me a script to run so I can update all invoices with a documenttype 3 and multistore 001 and from a specific date ? I am not sure how to include History Header ?
LikeLike
Mansfield,
Without looking at the actual data, I feel that giving someone a script to update their transactional data at the SQL level can be pretty dangerous. One small mistake and you can corrupt your data. I would recommend that you work with your GP Partner or Microsoft Support to help you with this if it cannot be done in the GP user interface.
-Victoria
LikeLike
Hi Victoria,
Would it be possible to link the AP Distributions and AP Apply Detail with GL Distributions views? I need to create an report via excel report builder in GP that includes the AP Apply Detail with GL Distributions (mainly check number), but I also need the reference (description) field from the AP transaction entry field that’s tied to the entry.
Thank you in advance!
Bob
LikeLike
Hi Bob,
I think that is here: https://victoriayudin.com/2010/09/23/sql-view-for-payables-apply-detail-and-gl-distributions-in-dynamics-gp/.
-Victoria
LikeLike
Hi Victoria, I did check that view but it’s missing the TRXDSCRN Trx_Description data.
~ Bob
LikeLike
Bob,
Why not just ask me to add the description to that view? 🙂 I just did that for you.
-Victoria
LikeLike
Thank you Victoria!!!
FYI – in the section WHERE DOCTYPE = 6 AND DOCAMNT <> 0 AND VOIDED = 0 the “<>” came over as “<>” so you just need to do a quick correction on what was updated.
~ Bob
LikeLike
Thanks Bob,
That gets me every month or so…it’s not every time, so I forget to check. 🙂 Should be fixed now.
-Victoria
LikeLike
Hi Victoria,
I am new to GP. I want to create customize report for how long invoices from each customer have been outstanding. Can you please help regarding this.
LikeLike
Hi Swati,
My SQL view for current Receivables aging detail in Dynamics GP should be able to help you with this.
-Victoria
LikeLike
Hi Victoria,
Thank you so much for posting your examples – they are awesome!
The Payables GL Distributions is great – I just need to add in the TEN99AMNT which I see are in PM10000, PM20000 and PM30200. I see the select lines to add the element but I’m not sure of what table prefix it should have…. D.TEN99AMNT or T.TEN99AMNT?
Thank you very much. Patti Switts
LikeLike
Hi Patti,
Great idea, I think that would be useful to add. It’s a little more complicated than just choosing the right prefix, since both D and T are comprised of unions of data from multiple tables. I just updated the code to include this – if you wanted to see the detail of how it was done, I added the TEN99AMNT field to lines 118, 122 and 126, and a corresponding 0 (since there is no 1099 amount on unposted payment transactions) on lines 130 and 134. Then I could add the T.TEN99AMNT to the select section at the top (line 84).
Thanks for the idea!
-Victoria
LikeLike
That is perfect, Thank you !
LikeLike
On line 84, I put a ‘T’ on the column heading to eliminate the syntax msg – T.TEN99AMNT T1099_Amount
Thank you.
LikeLike
Ah, sorry about that. Another way to get around that is to put brackets around it. I fixed it in the code.
Thanks for catching that!
-Victoria
LikeLike
Thanks much, Victoria, for all the great code. Is there a easy way to join in the Journal Entry numbers for these posted AP transactions?
Thanks, John
LikeLike
Hi John,
If you are posting to the GL in detail (meaning every payables transaction creates a new GL transaction), you can link the tables using something like the following:
GL20000.ORTRXTYP = PM20000.DOCTYPE and
GL20000.ORCTRNUM = PM20000.VCHRNMBR
-Victoria
LikeLike
Hi Victoria – I am looking for this same script for just open payables. do you have something? I tried stripping out the historical in the view but didn’t have much luck
LikeLike
Hi Wendi,
You could just create the view as is, then use the following script:
select * from view_AP_Distributions where Trx_Status = ‘Open’
Let me know if that does not work for you.
-Victoria
LikeLike
Victoria,
I used the view in Smartlist Designer and I need to modify the view to add the Due Date and I only want to show a Trx_Status of Open. Can the view be modified to make these changes so I can use it in Designer?
LikeLike
Hi William,
Try this:
alter 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 https://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
--Updated Feb 17, 2015 to add 1099 amount
--Updated Sep 14, 2017 to change DOCDATE to pull from
-- transaction tables and to clean up the code a bit
--Updated Nov 13, 2017 to remove all trx except OPEN
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select
D.VENDORID Vendor_ID,
N.VENDNAME Vendor_Name,
K.DOCNUMBR Document_Number,
T.PSTGDATE GL_Posting_Date,
T.DOCDATE Document_Date,
T.DUEDATE Due_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,
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,
T.TEN99AMNT [1099_Amount]
from PM10100 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/doc dates and trx description
left outer join PM20000 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
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thank you for this. It’s great. How would I go about adding the Intercompany ID for any intercompany transactions?
Thanks, Kathie
LikeLike
Hi Kathie,
For posted transactions I do not see an easy way to do this – once posted the entries only show the ‘local’ company distributions.
-Victoria
LikeLike
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
LikeLike
John,
That’s great, thank you for sharing this with us!
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
Is there a way to add the check cut date to this view? Thanks,
LikeLike
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
LikeLike
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.
LikeLike
Larry,
For Payments, the Document Date is the date printed on the checks.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
Hi Amber,
Yes, it will pull in everything. If you want to limit it, once you create the view, you can run it with a restriction. For example, to limit it to document dates in August of 2013:
-Victoria
LikeLike
Thank you!
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria,
Thanks so much as always for taking the time to explain the details
This is very helpful
-Iqbal
LikeLike
Perfect view which saved lots of my time……… Could do amazing reconciliations with different criteria. Keep going………..
LikeLike
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
LikeLike
Hi Judy,
That question is so great, I even have a blog post for it already! 🙂 This should hopefully answer the questions you have: https://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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: https://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
LikeLike
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
LikeLike
Thanks for your help, Victoria!
Judy
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Hi Danny,
I just updated the script to add this for you.
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
Hi Victoria
Yes! I’ve met this monster-of-an-AA 🙂
Thanks as always for replying.
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
Woohoooooo Victoria!
Its working well. Thanks a stack! Your help is always appreciated
LikeLike
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
LikeLike
Hi Lulu,
Do you know what DISTTYPE number corresponds to the ROUND distribution type?
-Victoria
LikeLike
Hello again Victoria
I checked the transation in the PM30600 and th DISTTYPE = 16 for ROUND.
Regards,
Lulu
LikeLike
Hi Lulu,
Thanks for looking that up. I have added it to the code above.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
OK, thanks Victoria, I will keep you posted.
LikeLike
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
LikeLike
While we’re at it… GL Account Name (or Description) from GL00100, too. 🙂
LikeLike
And done!
Happy Holidays!
-Victoria
LikeLike
You rock! Thanks so much.
Happy Holidays to you and yours as well!!!
LikeLike
Gordon,
Done!
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
Victoria,
As always, this is nothing short of amazing!
Leslie
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
Lulu,
You got it. I added a Voided column at the end.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Lulu,
That’s a great idea, thanks. I just added Voucher Number to the end.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
Hi Victoria,
I love this view! Is there any chance that you can add a posting date field to it?
Thanks
LikeLike
Ross,
You got it. I just updated the view to include it and also add some more user friendly column names.
-Victoria
LikeLike
Wonderful! Thanks Victoria.
LikeLike
Wondering if you had just a simple list of the different doctype ID’s for AP? I need to create custom AP reports. Thanks.
LikeLike
Ethan,
Take a look at my PM Tables page, that should have everything you need.
-Victoria
LikeLike
Хорошая работа!
LikeLike
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
LikeLike
i have solved the seqnumbr in rm10101.
how do i get the disttype
LikeLike
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
LikeLike
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
LikeLike
Thanks Victoria!!!! That did the tick. And your cautions are duly noted.
LikeLike
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
LikeLike
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
LikeLike
Victoria,
This view is very helpful for a report I am writing! How would I exclude voided transactions?
LikeLike
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
LikeLike
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!
LikeLike
Jessica,
You are absolutely right. Thank you very much for the correction! I have updated the code above to incorporate this.
–Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
I was wondering if you have a SQL query for historical AP Aging?
LikeLike