October 12, 2009 
Payables Transactions have been a popular request for reports lately. Below is a view that lists all posted Payables transactions in functional currency. Voided transactions are not excluded.
For other SQL views on Dynamics GP data, please visit my GP Reports page.
~~~~~
CREATE VIEW view_PM_Transactions AS /*************************************************************** view_PM_Transactions Created Oct 12 2009 by Victoria Yudin - Flexible Solutions, Inc. For updates see https://victoriayudin.com/gp-reports/ Shows all posted Payables transactions with Functional amounts. Does not exclude voided transactions. Tables used: PM00200 - Vendor Master PM20000 - Open/Posted Transactions PM30200 - Historical/Paid Transactions ***************************************************************/ SELECT P.VENDORID Vendor_ID, V.VENDNAME Vendor_Name, P.VCHRNMBR Voucher, CASE P.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, P.DOCDATE Document_Date, P.PSTGDATE GL_Posting_Date, P.DUEDATE Due_Date, P.DOCNUMBR Document_Number, P.DOCAMNT Document_Amount, P.CURTRXAM Unapplied_Amount, P.TRXDSCRN [Description], CASE P.VOIDED WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END Voided FROM (SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE, DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED FROM PM20000 UNION ALL SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE, DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED FROM PM30200) P INNER JOIN PM00200 V ON V.VENDORID = P.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_PM_Transactions 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 
I am looking to find the date that an invoice was actually entered into the system. I see a Posteddt field in both the PM20000 table and the PM30200 table, but when I try to validate these do not seem like the correct field. Where might I find the date entered?
Thanks in advance
LikeLike
Hi Kelli,
POSTEDDT will be the date an invoice is posted, not when it’s entered. There is no date that will definitively store the original date an invoice was entered in the PM20000 and PM30200 tables. If this is important, you could create a SQL trigger and a custom table to track this.
-Victoria
LikeLike
Hi Victoria – I love this view. How can I include PO Receipt transactions? Ideally, I’d like this view to allow for: POP Type, Vendor Doc #, Receipt Date, Amount, Vendor Name & ID, Currency ID, POP Receipt #, Batch Number, Line description (not the transaction’s reference at the header)…in addition to what you’re already providing above. I’m just not good with when to use inner or outter joins, so hoping you can guide me.
LikeLike
Hi Kristie,
What you’re asking for is not necessarily so easy, as there can be multiple POP receipts for each payables invoice. And each receipt can have multiple line items. What is the goal of the view? How will you be using it?
-Victoria
LikeLike
We plan to use it to confirm data entry. I’ve constructed a SmartList query for the group using the data available in the general ledger, but as you know, this data (using Finanical >> Account Transactions sub-folder) does not include the invoice’s date and for the receipts, you get the receipt number but not the corresponding invoice number. The group’s ultimate goal is to be able to reconcile these entries back to another internal application.
LikeLike
Hi Kristie,
Thanks for the additional detail. This is probably doable, but not easily with this view. Since you’re looking in the General Ledger, I would possibly start with that and link back to the POP and Payables tables from there. Maybe start with my Posted GL Transactions view, filter it on POP and PM transactions and then link back to the appropriate tables depending on the transaction type.
This is probably not something I would be posting on my blog… If you’re interested in having this created for you as a consulting project (or working together to learn how to do this), let me know.
-Victoria
LikeLike
Hi Victoria,
How would I join in GL00105 or another table that has the GL Account Number(ACTNUMST) with PM20000 or PM 30200?
LikeLike
Hi Joel,
The PM20000 and PM30200 tables do not have the GL distribution details, so you would need to add some more tables into the mix. I actually have a view already posted for this: https://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/.
-Victoria
LikeLike
I need to identify all vendors that have had no activity in the last two years.
Can I use your “view_PM_Transactions” view as a place to start?
LikeLike
Hi David,
Yes, that can be a good start. If you’re using the POP module, you may also want to check the POP10100 table for any new POs for vendors.
-Victoria
LikeLike
Actually I went with some other code you posted a few years ago.
“…So if you want the last payment date, regardless of the type of payment, and assuming you’re only looking at posted payments, you could use the following code:…”
SELECT VENDORID AS [Vendor_ID], max(DOCDATE) AS [Last_Payment]
FROM (SELECT VENDORID,DOCDATE FROM PM30200 –historical trx
— only look at payments and ignore voids 6 = Payment
WHERE PM30200.DOCTYPE = 6 AND PM30200.VOIDED = ‘0’
UNION
SELECT VENDORID ,DOCDATE FROM PM20000 –posted but open trx
— only look at payments and ignore voids
WHERE DOCTYPE = 6 AND VOIDED = ‘0’
) a
GROUP BY VENDORID
ORDER BY [Last_Payment]
Do you think I still need to check POP also? Hmmm, how would I add that to the above? Or do I need to with the posted payment assumption above?
LikeLike
David,
I don’t know if I would look at payments only. You might have a vendor just set up last month that you have invoices for, but no payments yet. Similarly, you may have a new vendor that you just issued a PO to, but do not have invoices in payables yet.
Try something like the following instead:
-Victoria
LikeLike
I think that’s got it. I put it together with vendor Info and will let the powers that be decide the cut-off point and inactivate the oldies.
Thanks!
LikeLike
Can some one help me with the following issue. I posted Inv with PO attached to accrued purchases. I forgot to take a discount and voided the payable. When I tryed to readd the payable I could not fimd my PO number. Is there any way to readd or reopen that PO and book the AP through Accrued Purchases account?
Thanks for all the help
LikeLike
Elena,
I am not aware of any way to do this. You would need to re-enter a manual Payables invoice at this point, you can manually type in the PO number, but it will not be ‘linked’ to the actual PO or receipt.
-Victoria
LikeLike
hi Victoria,
I’m trying enter a manual payment entry. When i select the vendor which has a currency of GBP the Apply button is grayed out. The functional current for this company is USD. Any help would be greatly appreciated. Thanks.
LikeLike
Kevin,
This is because of the different currencies. You need to post the payment first, then you will be able to apply it as a separate step.
-Victoria
LikeLike
Thanks Victoria!! But pardon for my questions (i’m still a rookie) I would post the entry in the manual payment entry screen but where do i apply the entry? and how? Thanks again
LikeLike
Kevin,
Transactions > Purchasing > Apply Payables Documents. You can click on the Help button on the window to get specific instructions once there.
-Victoria
LikeLike
Many thanks again!! I have another questions regarding Bank transfers. I’m encountering the following error message when i attempt to transfer funds from one checkbook to another. “You do not have authorization to perform transaction posting”. I’ve checked the users account and he has access to all posting options. Both checkbooks are the same currency which is the functional currency. Not sure where the problem lies. Thanks again.
LikeLike
Kevin,
Have you determined that this is a GP user specific issue? Security is completely different depending on what version of GP you are using, so it is hard to give you a generic test. If you are on GP 10.0 or 2010, can you log into GP as a user with the POWERUSER role and see if that user can post a Bank Transfer?
-Victoria
LikeLike
Hi Victoria,
I’ve checked his security settings and everything is fine. He should be able to perform bank transfers since that option is checked under his security settings. Is there anything else outside of the security settings that can cause this? Thanks.
LikeLike
Kevin,
I think you may have misunderstood what I am asking. Do you have another GP user that can perform a bank transfer with no error? Also, what version of GP are you using?
-Victoria
LikeLike
Yes, others are able to perform bank transfers with no issues. We are on GP V8.00g19.
LikeLike
Ok, then next step is to compare settings from a user than can post a bank transfer to the one that cannot – what are the differences?
-Victoria
LikeLike
There are no differences. Which is why it’s so weird.
LikeLike
Kevin,
Are you checking ALL the settings or just the settings for posting Bank Transfers? If you are using Advanced Security, you should have an option to copy security settings from one user to another (it’s there in GP 9.0, I am pretty sure it was there in 8.0)…as a test, you could create a new user, copy all the security settings from the user without the issue and test – if it works, copy all the settings from the user that is getting the error (make sure to check ‘revert security’ when copying). If it now does not work, you know there is a difference. It might be some other related permission that is needed…
-Victoria
LikeLike
Thank you very much Victoria!
LikeLike
Hello again Victoria
I was wondering if it was at all possible to update this view with the originating currency information please? This view is perfect for my clients request to create a check payment document – however, we are always reporting in Originating & Functional currency. I’ve noted the fields for the multicurrency reporting purposes below:
1. Currency ID
2. Exchange rate
3. Originating Check amount
4. Originating applied amount
5. Checkbook ID
Thank you & regards
LikeLike
Hi Lulu,
Adding multicurrency details to this is quite involved and will bring up a number of additional issues/questions to be resolved. This is not something I am planning on posting on my blog, at least in the foreseeable future, however, it may be something we can create as a consulting project. Please let me know if this is something you would be interested in.
-Victoria
LikeLike
Victoria,
This is a WONDERFUL website – thank you sooooooooooooo much. I’m bookmarking it!!!!
Lisa
LikeLike
Victoria,
In your case for the PYENTTYP you have 3 for EFT. I am working on doing a custom app for our company to write checks and process EFT. So if I just put in the PYENTTYP = 3 will the system automatically pick it up as an EFT? Or is there more I will need to do to get the system to recognize 3 as EFT?
LikeLike
Stanley,
If the payment is entered as an EFT type payment, it will have PYENTTYP = 3.
-Victoria
LikeLike
Hi Victoria,
Thanks for all the great GP reports and info. This site is amazing!
I am new to GP, and just started working with Smartlist Builder and SQL Views a couple weeks ago. My company needs a clean downloadable AP aging and I have just about got it right, except I am finding it difficult to create a total for the aging buckets.
I have created a view, originally because I wanted to create an aging date that would be the docdate if the open item wasn’t an invoice, and the due date if it was an invoice so that I could then use the aging date to calculate which bucket the open invoice belonged to.
Then I created 5 buckets for the aging days and have the invoice amts totalling correctly in those buckets.
Now I just want to create a total for all the buckets and SLB doesn’t allow you to use calculated fields inside calculated fields. And I can’t figure out an easy way to total the buckets in the view I created because it doesn’t seem to allow me to use a newly created field either.
Here’s my view and a couple of the bucket fields. I would really appreciate your help with creating a total for all the bucket fields.
Thanks,
Laura
CREATE VIEW [LAW_payables_aging_date] AS
SELECT
CASE DOCTYPE
WHEN 1 THEN DUEDATE
WHEN 2 THEN DOCDATE
WHEN 3 THEN DOCDATE
WHEN 4 THEN DOCDATE
WHEN 5 THEN DOCDATE
WHEN 6 THEN DOCDATE
ELSE DOCDATE
END Aging_Date,
VCHRNMBR,DOCTYPE,DOCDATE,DOCNUMBR,DUEDATE,
PORDNMBR,WROFAMNT,VOIDED,DOCAMNT, T1.VENDORID, VENDNAME
from dbo.PM20000 T1
INNER JOIN dbo.PM00200 T2
on
T2.[VENDORID] = T1.[VENDORID]
2 of the BUCKETS which were created in calculations in SLB:
CURRENT BUCKET
CASE
WHEN (DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date} , GETDATE() ) < '0' and
{*LAW_payables_aging_date:Document Type} = 5) THEN ({*LAW_payables_aging_date:Document Amount} * -1)
WHEN (DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date} , GETDATE() ) ‘0’ and DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date}, GETDATE() ) ‘0’ and DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date}, GETDATE() ) < '31') and
{*LAW_payables_aging_date:Document Type} = 1 THEN
{*LAW_payables_aging_date:Document Amount}
ELSE NULL
END
I want to add the current bucket to 1 – 30 days and 31-60 days and 61-90 days, etc.
LikeLike
Laura,
Thanks for the kind words!
I am not sure I follow – are you looking to add a column with a total of all the buckets? If so, that should just be the Current Trx Amount (CURTRXAM), why add the calculations? Also, I would suggest 2 changes to your code:
Hope that helps. Please write back if you need additional help.
-Victoria
LikeLike