Checks for a Vendor with General Ledger Distribution

It is fairly easy to pull a list of checks for a vendor within AP Inquiry in Elite Enterprise. However, sometimes you just need a little more information. The following query will pull a list of checks along with the GL accounts affected by those checks.

A couple things to note:

• Due to the GL distribution piece, there may be more than 1 row per check.
• The cash portion of the GL distribution has been excluded (take out the udtrtp line if you would like that included)
• The glnum and glrc1 are combined into one number
• The user indicated is the user who created the batch. More than one user can post items to a batch.

SELECT ap.apnum AS 'Vendor #'
,ap.apname AS 'Vendor Name'
,csddt.cknum AS 'Check #'
,csddt.ckline AS 'Line'
,csddt.baid AS 'Bank'
,csddt.ckdate AS 'Date'
,csddt.vo_id AS 'Voucher #'
,csddt.glnum+'-'+csddt.glrc1 AS 'GL #'
,gldes AS 'GL Description'
,dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS 'Description'
,csddt.amt AS 'Amount'
,fmsbatch.opuserid AS 'User'
FROM csddt
INNER JOIN ap ON csddt.apnum=ap.apnum
INNER JOIN fmsbatch ON csddt.cdbtid=fmsbtid AND fmsbatch.trtype='CK'
INNER JOIN gl ON csddt.glnum=gl.glnum AND csddt.glrc1=gl.glrc1
INNER JOIN csd ON csd.baid=csddt.baid AND csd.cknum=csddt.cknum
WHERE csddt.ckdate >= '1/1/2009'
AND ap.apnum = '123456'
AND udtrtp <> 'CA'
AND csd.voiddt IS NULL
ORDER BY csddt.ckdate

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