You’re offline. This is a read only version of the page.
55
I am looking for ways to print a report showing the names of all those A/P vendors that have 3 payments or more during a year. Any suggestions? Thanks!
*This post is locked for comments
Stella,
If you have access to SQL (or can work with someone that does), you can get this information by running the following query against your GP company database:
select v.VENDNAME Vendor_Name, p.Num_of_Payments
from
(select VENDORID, count(*) Num_of_Payments
from PM30200
where DOCTYPE = 6 and VOIDED = 0
and year(DOCDATE) = 2012 --change this if you need a different year
group by VENDORID
having count(*) > 3) p
inner join PM00200 v
on v.VENDORID = p.VENDORID
This is only taking into consideration fully applied payments, which is typical in AP. If you have a lot of unapplied or partially applied payments, this would need to be adjusted to also include table PM20000.
There are three fields you could add to the Vendor Smart list object that might get you close enough to what you want. Select the Vendors Smart list object and add the columns Number of Invoices YTD (year to date), Number of Invoices LYR (last year), Number of Invoices LIFE (life time).
These are Invoices (vouchers) not really payments as you probably group invoices when you pay your vendors but perhaps that is what you meant with your question and not the checks you cut.
A caveat here is that the totals for YTD and LYR get reset when you do the year end close for the AP module. If you are not timely on that process then the those totals might not be accurately represented in the correct bucket (best practice is to close AP as close to Jan 1 as possible regardless of GL close timing). The LIFE column will be accurate regardless though.
Easy enough to then set a restriction and sort by number of invoices.
Hope this helps,
Tim
I don't know of a report to show more than 3, but a smartlist via the Payables Transactions object would get the raw data. You would then have to get to excel and use a pivot table or other sorting to satisfy your over 3 payments request.
Dan Liebl, CMA CPIM | Senior Consultant | OTT,Inc | DLiebl@OTT-inc.com
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (