Check & Voucher Totals by Vendor

It is always interesting to look at year-end stats, and this query is no exception. This will show you the number and dollar amount of vouchers and checks by vendor that your Accounts Payable department processed in a given year, with the focus being on value of checks. Note, as it is currently written, this will not return any vendors with vouchers but no checks.

SELECT 
ap.apnum AS 'Vendor #',
apname AS 'Vendor Name',
csd.apaddid AS 'Address #',
apaddr.appynm AS 'Payee Name',
COUNT(cknum) AS '# Checks',
SUM(ckamt) AS '$ Checks',
nvo.numvoucher AS '# Vouchers',
nvo.dolvoucher AS '$ Vouchers'
FROM ap
INNER JOIN csd ON ap.apnum = csd.apnum
INNER JOIN apaddr ON csd.apaddid = apaddr.apaddid AND csd.apnum = apaddr.apnum
LEFT OUTER JOIN (
 SELECT
 apvo.apnum,
 apvo.apaddid,
 COUNT(vo_id) AS numvoucher,
 SUM(voamt) AS dolvoucher
 FROM apvo
 WHERE YEAR(votrdt)='2009'
 GROUP BY apvo.apnum,apvo.apaddid) AS nvo ON csd.apnum=nvo.apnum AND csd.apaddid = nvo.apaddid
WHERE YEAR(ckdate) = '2009'
AND (voiddt IS NULL OR ckstat <> 'V')
GROUP BY ap.apnum,apname,csd.apaddid,apaddr.appynm,numvoucher, dolvoucher
HAVING SUM(ckamt) > 0
ORDER BY SUM(ckamt) DESC

Checks with Descriptions for Specific Bank Account

The following will provide a list of checks disbursed on a specific bank account. You can add date qualifications or any other criteria as well. It will return a line for each 'check line', so check numbers and amounts will repeat for checks with multiple lines. This was originally developed for an Extend event, but we ended up going a different direction. This uses the check line description user defined function.

SELECT
csd.cknum,
csd.ckdate,
csd.apnum,
csd.ckamt,
csd.appynm,
csddt.ckline,
csddt.vo_id,
csddt.glnum,
csddt.glrc1,
csddt.amt,
dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS ckdes,
apvo.voinv,
apvo.voindt,
'********'+RIGHT(ap.apaccount,4) AS acctno
FROM
csd
INNER JOIN csddt ON csd.cknum=csddt.cknum AND csd.baid=csddt.baid
INNER JOIN apvo ON csddt.vo_id = apvo.vo_id
INNER JOIN ap ON csd.apnum=ap.apnum
WHERE csd.baid = 'BANKIACH'
AND voiddt IS NULL

Vendors with No Activity

It is often a good idea to sift through records to determine active records that have had no activity to see if they need to be made inactive. Below is a query to identify active vendor records that have had no activity.

SELECT
ap.apnum,
ap.apname,
ap.*
FROM ap
WHERE ap.lckdate IS NULL
AND NOT EXISTS (SELECT * FROM apamt WHERE ap.apnum=apamt.apnum)
AND NOT EXISTS (SELECT * FROM apvo WHERE ap.apnum=apvo.apnum)
AND NOT EXISTS (SELECT * FROM trcheck WHERE ap.apnum=trcheck.trvendor)
AND NOT EXISTS (SELECT * FROM csd WHERE ap.apnum=csd.apnum)
AND NOT EXISTS (SELECT * FROM rapvo WHERE ap.apnum=rapvo.apnum)

While the ap.lckdate (last check date) is an obvious criteria here, vendors can have activity other than checks that needs to be searched for. Therefore, we include the voucher table, repeating voucher table, vendor summary totals, trust check table and the check detail table. If the vendor doesn't appear in any of these, then it's definitely an inactive vendor.