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