January 20, 2015 
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.
Here is a sample of the results (please click on the image to see it bigger):
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.
Victoria Yudin 
Victoria, Thank you for this great code. I was new to GP a couple years ago and your views have been extremely helpful in getting me moving quickly on some report out of the gate.
I am not sure if anyone is interested in an additional part of the view but I added in which box the 1099 is going into. This has helped me because we have quite a few businesses and using this to quickly see which box is selected has been helpful. Here is what I added.
case
–NOT A 1099 Vendor
when ten99type = 1 and ten99boxnumber = 0 then ‘Not a 1099 Vendor’
— 1099 Dividend
when ten99type = 2 and ten99boxnumber = 0 then ‘Not a 1099 Vendor’
when ten99type = 2 and ten99boxnumber = 1 then ‘1a Ordinary Dividends’
when ten99type = 2 and ten99boxnumber = 2 then ‘1b Qualified Dividends’
when ten99type = 2 and ten99boxnumber = 3 then ‘2a Capital Gain Dist.’
when ten99type = 2 and ten99boxnumber = 4 then ‘2b Unrecap. 1250 Gain’
when ten99type = 2 and ten99boxnumber = 5 then ‘2c Section 1202 Gain’
when ten99type = 2 and ten99boxnumber = 6 then ‘2d 28% Rate Gain’
when ten99type = 2 and ten99boxnumber = 7 then ‘3 Nontaxable Dist.’
when ten99type = 2 and ten99boxnumber = 8 then ‘4 Federal Tax Withheld’
when ten99type = 2 and ten99boxnumber = 9 then ‘5 Investment Expense’
when ten99type = 2 and ten99boxnumber = 10 then ‘6 Foreign Tax Paid’
when ten99type = 2 and ten99boxnumber = 11 then ‘8 Cash’
when ten99type = 2 and ten99boxnumber = 12 then ‘Noncash’
–1099 Int
when ten99type = 3 and ten99boxnumber = 0 then ‘Not a 1099 Vendor’
when ten99type = 3 and ten99boxnumber = 1 then ‘1 Interest’
when ten99type = 3 and ten99boxnumber = 2 then ‘2 Early Withdrawal’
when ten99type = 3 and ten99boxnumber = 3 then ‘3 U.S. Savings Bonds Int.’
when ten99type = 3 and ten99boxnumber = 4 then ‘4 Federal Tax Withheld’
when ten99type = 3 and ten99boxnumber = 5 then ‘5 Investment Expense’
when ten99type = 3 and ten99boxnumber = 6 then ‘6 Foreign Tax Paid’
when ten99type = 3 and ten99boxnumber = 7 then ‘8 Tax-Exempt Interest’
when ten99type = 3 and ten99boxnumber = 8 then ‘9 Private Activity Bond Int.’
–1099 Misc
when ten99type = 4 and ten99boxnumber = 0 then ‘Not a 1099 Vendor’
when ten99type = 4 and ten99boxnumber = 1 then ‘1 Rents’
when ten99type = 4 and ten99boxnumber = 2 then ‘2 Royalties’
when ten99type = 4 and ten99boxnumber = 3 then ‘3 Other Income’
when ten99type = 4 and ten99boxnumber = 4 then ‘4 Federal Tax Withheld’
when ten99type = 4 and ten99boxnumber = 5 then ‘5 Fishing Boat Proceeds’
when ten99type = 4 and ten99boxnumber = 6 then ‘6 Medical Payments’
when ten99type = 4 and ten99boxnumber = 7 then ‘7 Nonemployee Compensation’
when ten99type = 4 and ten99boxnumber = 8 then ‘8 Substitute Payments’
when ten99type = 4 and ten99boxnumber = 9 then ’10 Crop Insurance’
when ten99type = 4 and ten99boxnumber = 10 then ’13 Golden Parachute’
when ten99type = 4 and ten99boxnumber = 11 then ’14 Attorney Proceeds’
when ten99type = 4 and ten99boxnumber = 12 then ’15a Section 409A Deferrals’
when ten99type = 4 and ten99boxnumber = 13 then ’15b Section 409A Income’
when ten99type = 4 and ten99boxnumber = 14 then ’16 State Tax Withheld’
end [1099_Box]
LikeLike
Jeremie,
Thanks – this is great!!
-Victoria
LikeLike
I was just about to type all of this out, when I came across your post – Thank You for saving me all that time!
LikeLike
Thanks Victoria – this will be really useful! You are the best.
LikeLike