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 http://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
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.
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
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
Kevin,
Transactions > Purchasing > Apply Payables Documents. You can click on the Help button on the window to get specific instructions once there.
-Victoria
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.
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
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.
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
Yes, others are able to perform bank transfers with no issues. We are on GP V8.00g19.
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
There are no differences. Which is why it’s so weird.
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
Thank you very much Victoria!
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
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
Victoria,
This is a WONDERFUL website – thank you sooooooooooooo much. I’m bookmarking it!!!!
Lisa
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?
Stanley,
If the payment is entered as an EFT type payment, it will have PYENTTYP = 3.
-Victoria
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.
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