3d small people - roll dollars

SQL view for total payments to 1099 vendors in the prior year


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):
1099 sample

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.

8 Responses to “SQL view for total payments to 1099 vendors in the prior year”

  1. 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]

    Like

  2. Thanks Victoria – this will be really useful! You are the best.

    Like

Trackbacks/Pingbacks

  1. SQL view for total payments to 1099 vendors in the prior year | Victoria Yudin - Microsoft Dynamics GP Community - January 22, 2015

    […] Victoria Yudin gives us a SQL view for total payments to 1099 vendors in the prior year […]

    Like

  2. DynamicAccounting.net | SQL view for total payments to 1099 vendors in the prior year | Victoria YudinSQL view for total payments to 1099 vendors in the prior year | Victoria Yudin - DynamicAccounting.net - January 22, 2015

    […] Victoria Yudin gives us a SQL view for total payments to 1099 vendors in the prior year […]

    Like

  3. SQL view for total payments to 1099 vendors in the prior year – 1/20, Victoria Yudin | - January 20, 2015

    […] Continue reading on Source Blog […]

    Like

  4. SQL view for total payments to 1099 vendors in the prior year : Interesting Findings & Knowledge Sharing - January 20, 2015

    […] View article: SQL view for total payments to 1099 vendors in the prior year […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: