Do you feel like there is too much time and effort being put into A/P data entry? Have you heard the sound of money getting fainter as it slips away from you? Would you like to see reporting from the Dynamics GP system to show you what’s going on in Payables Management?
In Microsoft dynamics GP, Payables Management supports the processing of A/P invoices and their payment. Supplier invoices come in, they get recorded as vouchers in the system and eventually get paid. Payment can be made by check or electronic funds transfer. In a number of cases, we’ve seen clients use third-party tools to automate A/P processing, specifically the intake of incoming supplier invoices.
Nonetheless, there is still a need for some amount of manual data entry. This can come in the form of complete data entry of the supplier invoice or a correction of automated voucher processing. At some of our clients, we noticed that there was a need to measure the efficiency of the personnel entering A/P vouchers.
In general, metrics are important. As I am often reminded that you can’t improve what you can’t measure. Therefore, is there a fast way to analyze and report the number of A/P and credit memos processed by each user over time?
A few solutions come to mind including:
- Creating a custom table and capturing data at batch posting
- Utilizing data in a third-party application to report invoices and credit memos entered
- Joining tables and creating a SmartList or refreshable Excel Report
Here’s a fast, simple way using the power of MS-SQL to quickly calculate the number of invoices and credit memos process by each user. Simply add the following query to Dynamics GP database:
Drop table #wkTable – Delete the temporary table
select userid, — Select applicable data from table PM00400
datepart(year,docdate) as Year,
datepart(week,docdate) as Week,
case when doctype = 1 then COUNT(DOCNUMBR) else 0 end as Inv, — Count number of invoices
case when doctype = 5 then COUNT(DOCNUMBR) else 0 end as CM — Count number of credit memos
into #wkTable from PM00400 – Insert into a temporary table
where docdate >= getdate() – 180 – Limit reporting to the most recent 180 days
and doctype in (1,5) — Invoices or Credit Memos only
group by userid,docdate,DOCTYPE – Insert 1 record per user ID, document date, document type
order by userid, docdate,DOCTYPE – Sort by user ID, document date, and document type
— Report results from the temporary table
select userid,
str(year) + str(week) as YearWeek,
sum(inv) as NumofInvoice,
sum(CM) as NumOfCM,
(sum(inv) + sum(CM)) as TotalKeyed
from #wkTable
group by userid,year,week
order by Userid,year,week
Once done, it can be used as a data source for an Excel spreadsheet or other ODBC-compliant reporting tool.
For more information about how SSG can help your organization, please contact SSG at (310) 539-4645 or via the contact form today!