We received several requests just yesterday for a listing of total vendor payments in the prior calendar year. Below is a view that will show the total payments in the prior year for all 1099 vendors. This is not necessarily meant to match the report that comes out from the Print 1099 window, rather this is total payments for each vendor for the purposes of confirming that the 1099 report is correct. For vendors that may have only part of their invoices be ‘1099-able’, you may need to do something a bit more complicated to confirm your numbers.
Some additional resources:
create view view_1099_vendor_payment_totals as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- Created Jan 20, 2015 by Victoria Yudin -- Flexible Solutions, Inc. -- For other code, please visit http://victoriayudin.com -- Shows total of prior year payments for each 1099 vendor -- Assumes all payments for a 1099 vendor are included -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select v.VENDORID Vendor_ID, v.VENDNAME Vendor_Name, coalesce(hp.Pmts,0) + coalesce(op.Pmts,0) Total_Payments, v.TXIDNMBR Tax_ID, v.ADDRESS1 Address_1, v.ADDRESS2 Address_2, v.CITY City, v.[STATE] [State], v.ZIPCODE Zip, case v.PHNUMBR1 when '' then '' when '00000000000000' then '' else left(v.PHNUMBR1,3) + '-' + substring(v.PHNUMBR1,4,3) + '-' + substring(v.PHNUMBR1,7,4) + case substring(v.PHNUMBR1,11,4) when '0000' then '' else ' Ext ' + substring(v.PHNUMBR1,11,4) end end Phone, case TEN99TYPE when 1 then 'Not a 1099 Vendor' when 2 then 'Dividend' when 3 then 'Interest' when 4 then 'Miscellaneous' when 5 then 'Withholding' end [1099_Type] from PM00200 v left outer join (select VENDORID, sum(DOCAMNT) Pmts from PM30200 where DOCTYPE = 6 and VOIDED = 0 and year(DOCDATE) = year(getdate())-1 group by VENDORID) hp --historical pmts on v.VENDORID = hp.VENDORID left outer join (select VENDORID, sum(DOCAMNT) Pmts from PM20000 where DOCTYPE = 6 and VOIDED = 0 and year(DOCDATE) = year(getdate())-1 group by VENDORID) op --open payments on v.VENDORID = op.VENDORID where v.TEN99TYPE <> 1 and coalesce(hp.Pmts,0) + coalesce(op.Pmts,0) <> 0 go grant select on view_1099_vendor_payment_totals to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.