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