1099 Review Data

At my firm, we have not used Elite's native 1099 reporting app within Enterprise for several years. Instead, we opt to export information from Enterprise and import into a third party 1099 application, which in our case is Convey. As such, we use a SQL based report to review all our transactions for correct flagging. This is done in two parts: 1) a summary by vendor where the vendor, voucher or address 1099 flag is not "N", and 2) a listing of all new vendors.

Part 1

WITH trust AS
(
SELECT trvendor
,trvendoraddr
,tr1099flag
,SUM(-tramount/cdrate) AS 'tramt'
FROM trcheck
INNER JOIN trsttran ON trcheck.trindex=trsttran.trindex
INNER JOIN tracct ON trsttran.tracct=tracct.tracct
INNER JOIN matter ON trmatter=mmatter
INNER JOIN currates ON trcurcd=curcode AND trdate bETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE YEAR(trdate)='2014'
AND tr1099flag IS NOT NULL
AND tr1099flag <> 'N'
AND trsttran.trflag IS NULL
GROUP BY trvendor,trvendoraddr,tr1099flag
), apamt AS
(
SELECT
csddt.apnum
,csddt.apaddid
,csddt.ap1099
,SUM(csddt.amt/currates.cdrate) AS 'apamt'
FROM
csddt
INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid
INNER JOIN currates ON csddt.currency=currates.curcode AND csddt.ckdate BETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE
YEAR(csddt.ckdate)='2014'
AND csddt.ap1099 <> 'N'
GROUP BY csddt.apnum,csddt.apaddid,csddt.ap1099
)
SELECT
apaddr.apnum
,apaddr.apaddid
,CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END AS 'TIN'
,ap.apname AS 'Vendor Name'
,apaddr.appynm AS 'Payee'
,apaddr.appya1 AS 'Address 1'
,apaddr.appya2 AS 'Address 2'
,apaddr.appyct AS 'City'
,apaddr.appyst AS 'State'
,apaddr.appyzp AS 'Zip Code'
,ap.apct1 AS 'Vendor Type'
,ap.ap1099 AS 'Vendor 1099'
,apaddr.ap1099 AS 'Addr 1099'
,CASE WHEN apamt.ap1099 IS NULL THEN tr1099flag ELSE apamt.ap1099 END AS 'Tran 1099'
,ISNULL(apamt,0)+ISNULL(tramt,0) AS 'amt'
FROM apaddr
INNER JOIN ap ON apaddr.apnum=ap.apnum
LEFT OUTER JOIN apamt ON apaddr.apnum=apamt.apnum AND apaddr.apaddid=apamt.apaddid
LEFT OUTER JOIN trust ON apaddr.apnum=trvendor AND apaddr.apaddid=trvendoraddr
WHERE (ISNULL(apamt,0)+ISNULL(tramt,0))<>0
ORDER BY CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END
,apaddr.apnum
,apaddr.apaddid

Part 2

WITH trust AS
(
SELECT trvendor
,trvendoraddr
,tr1099flag
,SUM(-tramount/cdrate) AS 'tramt'
FROM trcheck
INNER JOIN trsttran ON trcheck.trindex=trsttran.trindex
INNER JOIN tracct ON trsttran.tracct=tracct.tracct
INNER JOIN matter ON trmatter=mmatter
INNER JOIN currates ON trcurcd=curcode AND trdate bETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE YEAR(trdate)='2014'
--AND tr1099flag IS NOT NULL
--AND tr1099flag <> 'N'
AND trsttran.trflag IS NULL
GROUP BY trvendor,trvendoraddr,tr1099flag
), apamt AS
(
SELECT
csddt.apnum
,csddt.apaddid
,csddt.ap1099
,SUM(csddt.amt/currates.cdrate) AS 'apamt'
FROM
csddt
INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid
INNER JOIN currates ON csddt.currency=currates.curcode AND csddt.ckdate BETWEEN cddate1 AND cddate2 AND currates.trtype='A'
WHERE
YEAR(csddt.ckdate)='2014'
AND csddt.trtype<>'CA'
--AND csddt.ap1099 <> 'N'
GROUP BY csddt.apnum,csddt.apaddid,csddt.ap1099
)
SELECT
apaddr.apnum
,apaddr.apaddid
,CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END AS 'TIN'
,ap.apname AS 'Vendor Name'
,apaddr.appynm AS 'Payee'
,apaddr.appya1 AS 'Address 1'
,apaddr.appya2 AS 'Address 2'
,apaddr.appyct AS 'City'
,apaddr.appyst AS 'State'
,apaddr.appyzp AS 'Zip Code'
,ap.apopendt AS 'Open Date'
,ap.apct1 AS 'Vendor Type'
,ap.ap1099 AS 'Vendor 1099'
,apaddr.ap1099 AS 'Addr 1099'
,CASE WHEN apamt.ap1099 IS NULL THEN tr1099flag ELSE apamt.ap1099 END AS 'Tran 1099'
,ISNULL(apamt,0)+ISNULL(tramt,0) AS 'amt'
FROM apaddr
INNER JOIN ap ON apaddr.apnum=ap.apnum
LEFT OUTER JOIN apamt ON apaddr.apnum=apamt.apnum AND apaddr.apaddid=apamt.apaddid
LEFT OUTER JOIN trust ON apaddr.apnum=trvendor AND apaddr.apaddid=trvendoraddr
WHERE (ISNULL(apamt,0)+ISNULL(tramt,0))<>0
AND YEAR(ap.apopendt)='2014'
ORDER BY CASE WHEN apaddr.apfed IS NULL THEN 'zNoTIN'+ap.apnum ELSE apaddr.apfed END
,apaddr.apnum
,apaddr.apaddid

Accounts Payable Annual Activity and Balance

Here's a quick and dirty little query that you can run at the beginning of the year to see a summary of the prior year's accounts payable activity and current balances by vendor. Transactions are converted to firm currency using the average method for the month of the transaction, and balance is converted to firm currency using the daily rate as of the end of the year. Note that the balance table is outer joined, so not all balances will be returned, just ones with prior payment activity. If you want both, you would need to do some fancy footwork with a full outer join and some coalescing, which is beyond the scope of this post =).

SELECT
ap.apnum
,ap.apname
,SUM(CASE WHEN (apyr=2013) THEN apntpr/trancur.cdrate ELSE 0 END) AS '2013'
,SUM(CASE WHEN (apyr=2012) THEN apntpr/trancur.cdrate ELSE 0 END) AS '2012'
,ISNULL(apbal.apbal/balcur.cdrate,0) AS 'Balance'
,apamt.currency AS 'Currency'
FROM ap
INNER JOIN apamt ON ap.apnum=apamt.apnum
INNER JOIN currates trancur ON DATEADD(mm,apper-1,DATEADD(yy,apyr-1900,0)) BETWEEN trancur.cddate1 AND trancur.cddate2
 AND trancur.trtype='A'
 AND trancur.curcode=apamt.currency
LEFT OUTER JOIN apbal ON ap.apnum=apbal.apnum AND apbal.currency=apamt.currency
LEFT OUTER JOIN currates balcur ON '12/31/2013' BETWEEN balcur.cddate1 AND balcur.cddate2
 AND balcur.trtype='D' AND balcur.curcode=apbal.currency
GROUP BY ap.apnum,ap.apname,apbal.apbal,apamt.currency,balcur.cdrate
ORDER BY SUM(CASE WHEN (apyr=2013) THEN apntpr ELSE 0 END) DESC

General Ledger Transactions for a Range of Accounts

The following stored procedure will imitate a general ledger transaction report from within Elite, with easy use in Excel. The concept is faily simple with the first SELECT statement pulling Accounts Payable entries and the second SELECT statement pulling non-AP general journal entries. This also uses the GJ entry description user defined function. There are two variables passed to the stored procedure: beginning and ending dates. The GL accounts are hard coded, but could easily be changed to variables.

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'usp_GL_Expenses' 
)
   DROP PROCEDURE dbo.usp_GL_Expenses
GO

CREATE PROCEDURE dbo.usp_GL_Expenses
 @startdate DATETIME = NULL, 
 @enddate DATETIME = NULL
AS

SELECT
CAST(csddt.cknum AS VARCHAR(12)) AS 'Check #/GJ ID'
,csddt.ckline AS 'GJ Line'
,csddt.ckdate AS 'Date'
,MONTH(csddt.ckdate) AS 'Month'
,csddt.apnum AS 'Vendor #'
,csddt.apaddid AS 'Vendor Sub'
,ap.apname AS 'Vendor Name'
,apaddr.appynm AS 'Payee'
,csddt.vo_id AS 'Voucher'
,csddt.glnum AS 'GL Acct'
,csddt.glrc1 AS 'GL Dept'
,gl.gldes AS 'GL Description'
,gl.glnum + ' ' + gl.gldes AS 'GL Acct Description'
,csddt.amt AS 'Amount'
,ap.apct1 AS 'Vendor Type'
,voiddt AS 'Void Date'
,dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS 'Transaction Description'
FROM
csddt
INNER JOIN ap ON csddt.apnum = ap.apnum
INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid
INNER JOIN apaddr ON csddt.apnum = apaddr.apnum AND csddt.apaddid=apaddr.apaddid
INNER JOIN gl ON csddt.glnum = gl.glnum AND csddt.glrc1=gl.glrc1
WHERE
(csddt.glnum BETWEEN '62500' AND '63320'
OR csddt.glnum = '53100'
OR gl.glnum BETWEEN '84100' AND '84199'
OR gl.glnum BETWEEN '84400' AND '84499'
OR csddt.glnum = '93145')
AND csddt.ckdate BETWEEN @startdate AND @enddate

UNION

SELECT
gjid
,gjline
,gjtrdt
,MONTH(gjtrdt)
,NULL
,NULL
,NULL
,NULL
,NULL
,gj.glnum
,gj.glrc1
,gl.gldes
,gl.glnum + ' ' + gl.gldes AS 'glnumdes'
,ISNULL(dbamt,0)-ISNULL(cramt,0)
,NULL
,NULL
,des + ' ' + dbo.udfGJDes(gjid,gjline)
FROM gj
INNER JOIN gl ON gj.glnum=gl.glnum AND gj.glrc1=gl.glrc1
WHERE gjtrdt BETWEEN @startdate AND @enddate
AND (gl.glnum BETWEEN '62500' AND '63320'
OR gl.glnum = '53100'
OR gl.glnum BETWEEN '84100' AND '84199'
OR gl.glnum BETWEEN '84400' AND '84499'
OR gl.glnum = '93145')

GO

To execute the stored procedure, use this statement

EXECUTE dbo.usp_GL_Expenses '1/1/2010','12/31/2010'
GO

Make sure to grant execute to any other users that need access to this stored procedure.

GRANT EXECUTE ON dbo.usp_GL_Expenses TO elitereadonly;
GO

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

Volumes - Vouchers

The following is a simple query to assess the volume of vouchers being processed each month. This includes all voucher types.

SELECT
YEAR(votrdt) AS 'Year',
MONTH(votrdt) AS 'Month',
COUNT(vo_id) AS '# Vouchers',
SUM(voamt) AS 'Voucher Amt'
FROM apvo
WHERE
YEAR(votrdt) >= '2005'
GROUP BY YEAR(votrdt),MONTH(votrdt)
ORDER BY YEAR(votrdt),MONTH(votrdt)

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

Check Line Description User Defined Function

Ok, by now you've probably figured out that I'm a huge fan of user defined functions. Well, I am. Just this past week I had the need for yet another concatenation of rows thanks to Elite's bizarre handling of description fields. This one relates to check descriptions. Each GL line of a check has a description attached to it that can span multiple lines in the csddes table. So, the following udf will combine all the description lines for each bank / check / check line combination.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfCsdDes]
(
@baid VARCHAR(8),
@cknum VARCHAR(8),
@ckline INT
)
RETURNS VARCHAR(4800) AS
BEGIN
 DECLARE @description VARCHAR(4800)

 SELECT @description = ''

 SELECT @description = @description + ' ' + des FROM csddes WHERE baid = @baid AND cknum = @cknum AND ckline = @ckline ORDER BY dsline

 RETURN LTRIM(RTRIM(@description))
END

You can call this udf in any query by passing the bankid, check number and check line variable, typically from the csddt table. Example will follow in the next post.