1099 Year End Edit – SQL View for Dynamics GP

There are many ways to look at 1099 data – here is my recent pass at looking at the actual 1099 Period Detail Table as well as having the 1099 Type and Box information.
Apply this to your company database and bring into SmartLists.

CREATE view [dbo].[TEN99_Audit_List]
as
SELECT A.VENDORID Vendor_ID,
B.VENDNAME Vendor_Name,
B.VNDCHKNM Vendor_Check_Name,
B.VNDCLSID Class_ID,
case B.VENDSTTS
when 1 then 'Active'
when 2 then 'Inactive'
when 3 then 'Temporary'
end Vendor_Status,
case A.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Dividend'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
when 5 then 'Nonemployee Compensation'
end Type, a.ten99type, a.ten99boxnumber,
case --Set 1099 Box Number Values
when A.TEN99TYPE = 1 then 'Not a 1099 Vendor'
when A.TEN99BOXNUMBER = 0 then 'Not a 1099 Vendor'
--1099 Type Dividend Box Numbers
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 1 then '1a - Ordinary Dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 2 then '1b - Qualified Dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 3 then '2a - Capital Gain Dist.'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 4 then '2b - Unrecap. 1250 Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 5 then '2c - Section 1202 Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 6 then '2d - 28% Rate Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 7 then '3 - Nontaxable Dist.'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 8 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 9 then '5 - Section 199A dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 10 then '6 - Investment Expense'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 11 then '7 - Foreign Tax Paid'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 12 then '9 - Cash'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 13 then '10 - Noncash'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 14 then '11 - Exempt Interest Dividend'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 15 then '12 - Specified Private Activity'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 16 then '15 - State Tax Withheld'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 17 then 'XX - Unknown'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 18 then 'XX - Unknown'
--1099 Type Interest Box Numbers
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 1 then '1 - Interest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 2 then '2 - Early Withdrawal'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 3 then '3 - U.S. Savings Bond Int.'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 4 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 5 then '5 - Investment Expense'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 6 then '6 - Foreign Tax Paid'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 7 then '8 - Tax-Exempt Interest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 8 then '9 - Private Activity Bond Int.'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 9 then '10 - Market Discount'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 10 then '11 - Bond Premium'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 11 then '12 - Bond premium on Treasury obligations'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 12 then '13 - Bond premium on Tax-exempt bond'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 13 then '17 - State Tax Withheld'
--1099 Type Miscellaneous Box Numbers
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 1 then '1 - Rents'
When A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 2 then '2 - Royalties'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 3 then '3 - Other Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 4 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 5 then '5 - Fishing Boat Proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 6 then '6 - Medical Payments'
--The 1099 Box 7 is now a checkbox on the form. Update value in: Purchasing > Cards > 1099 Details
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 7 then '8 - Substitute Payments'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 8 then '9 – Crop Insurance'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 9 then '10 - Attorney Proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 10 then '12 - 409A Deferrals'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 11 then '13 - Golden Parachute'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 12 then '14 – Section 409A Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 13 then '15 - State Tax Withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 14 then '18 - State Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 15 then 'XX - Unknown'
--1099 Type NEC Box Numbers
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 1 then '1 - Nonemployee Compensation'
When A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 2 then '2 - Federal Tax Withheld'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 3 then '3 - State Tax'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 4 then '4 - State Income'
end [1099_Box_Number],
B.PYMTRMID Payment_Terms_ID,
A.YEAR1 [Year],
sum(A.TEN99AMNT) Amount_Paid_1099,
--NEW DBA FIELD
B.VENDDBA Vendor_DBA,
B.TXIDNMBR Tax_ID,
B.ADDRESS1 Address_1,
B.ADDRESS2 Address_2,
B.ADDRESS3 Address_3,
B.CITY City,
B.[STATE] [State],
B.ZIPCODE Zip_Code,
B.COUNTRY Country
FROM PM00204 A
INNER JOIN PM00200 B ON A.VENDORID = B.VENDORID
GROUP BY A.VENDORID, B.VENDNAME, B.VNDCLSID,
B.VENDSTTS, A.TEN99TYPE, B.PYMTRMID, A.YEAR1, B.ADDRESS1, B.ADDRESS2,
B.ADDRESS3, B.CITY, B.[STATE], B.ZIPCODE, B.COUNTRY, B.TXIDNMBR, B.VNDCHKNM, A.TEN99BOXNUMBER,B.VENDDBA
GO
grant select on [TEN99_Audit_List] TO DYNGRP

2 thoughts on “1099 Year End Edit – SQL View for Dynamics GP”

  1. Shawn,
    Love this script. I put it to use this week but added one field that some might find helpful. I added the 1099 Type from the Vendor Master. What was happening is that the users were changing the Vendor 1099 type but not changing the 1099 detail. If you go to run your 1099 forms, GP will ignore the vendor setting and customers are perplexed when they see that the 1099 is printing when they turned it off. 🙂

    I added this an inserted it just under the Vendor ID:
    case B.TEN99TYPE
    when 1 then ‘Not a 1099 Vendor’
    when 2 then ‘Dividend’
    when 3 then ‘Interest’
    when 4 then ‘Miscellaneous’
    end Vendor_TaxType,

    Worked like a charm. Again – Thanks.
    Sheila

Leave a Reply