1099 reports: the devil is in the details

January 20, 2012

It's certainly that time of the year and if you haven't sent out those 1099's you are running out of time. I was working on a case recently at one of my clients who was having all sort of issues printing 1099 reports. Apparently, reconciling their calendar year wasn't helping the issue either. It did not really matter what they tried - short of manually adjusting the 1099 amounts for each vendor - nothing seemed to make the correct numbers print on the report.

We reviewed all 10,000 vendor cards to make sure they had the proper 1099 type and box number selected - we caught a few mishaps here too.

After inspecting a few of the problem reports (and vendors), we narrowed down the issue to a number of payable invoices not having the proper 1099 amount and box type associated to the transactions. So to fix this issue, I wrote a few scripts which I hope you find useful.

Fix1099.sql

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

-- Update the 1099 amount, type, and box number on open payable records
UPDATE a SET a.TEN99AMNT = a.DOCAMNT
 , a.TEN99TYPE = b.TEN99TYPE
 , a.TEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM20000 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID) 
WHERE (b.TEN99TYPE <> 1) AND (a.DOCTYPE IN (1, 4, 5));

-- Update the 1099 amount, type, and box number on history payable records
UPDATE a SET a.TEN99AMNT = a.DOCAMNT
 , a.TEN99TYPE = b.TEN99TYPE
 , a.TEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM30200 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (b.TEN99TYPE <> 1) AND (a.DOCTYPE IN (1, 4, 5));

-- Update the 1099 amount, type, and box number on history apply records
UPDATE a SET a.TEN99AMNT = a.APPLDAMT
 , a.DEFTEN99TYPE = b.TEN99TYPE
 , a.DEFTEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM30300 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (b.TEN99TYPE <> 1) AND (a.APTODCTY IN (1, 4, 5));

Once the scripts are executed against the company database, you can then proceed to reconcile the 1099 amounts for the current calendar year - Microsoft Dynamics GP | Tools | Utilities | Purchasing | Reconcile. Note that the update is restricted to invoices (1), returns (4), and credit memos (5) document types.

Once we went through all of the above (including making sure the vendor cards were accurate), the reports printed fine, with the correct amounts, and in the correct boxes.

A few things to remember:

1. Execute all of the above procedures in a test environment, prior to executing in production. To create a test company from your production company follow the steps outlined in Microsoft Support KB article 871973.

2. The above steps and scripts specifically solved the issues with my client environment. You may have specifics within your environment that may prevent these scripts from being effective - I can't think of anything, but then again, test!

3. See the disclaimers on the right frame of this blog.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Comments

RLMcVicar said…

Though this worked for one vendor: finding if this update script would work to fix a fendor that seemed to be off: 1099 buckets not right. It did not work for others. My script below. Any thought or input from your end?

--------
USE [TEMPDB]
if object_id('#temp1099a') is not null drop table #temp1099a
if object_id('#temp1099b') is not null drop table #temp1099b
if object_id('#temp1099c') is not null drop table #temp1099c
USE [NREG]

-- Update the 1099 amount, type, and box number on open payable records
SELECT a.VENDORID, SUM(a.TEN99AMNT) AS TEN99AMNT, SUM(DOCAMNT) AS DOCAMNT
FROM PM20000 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (a.VENDORID IN ('207557') AND a.DOCTYPE IN (1, 4, 5) AND (b.TEN99TYPE <> 1))
GROUP BY a.VENDORID

-- Update the 1099 amount, type, and box number on history payable records
SELECT a.VENDORID, SUM(a.TEN99AMNT) AS TEN99AMNT, SUM(a.DOCAMNT) AS DOCAMNT
INTO #temp1099a
FROM PM30200 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (a.VENDORID IN ('207557') AND a.DOCTYPE IN (1, 4, 5) AND (b.TEN99TYPE <> 1))
GROUP BY a.VENDORID

-- Update the 1099 amount, type, and box number on history apply records
SELECT a.VENDORID, SUM(a.TEN99AMNT) AS TEN99AMNT, SUM(a.APPLDAMT)AS DOCAMNT
INTO #temp1099b
FROM PM30300 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (b.TEN99TYPE <> 1) AND (a.APTODCTY IN (1, 4, 5)) AND a.VENDORID IN ('207557')
GROUP BY a.VENDORID

SELECT A.VENDORID, A.DOCAMNT, B.DOCAMNT, A.TEN99AMNT, B.TEN99AMNT,
(CONVERT(MONEY,(B.TEN99AMNT - A.TEN99AMNT)*-1) +
CONVERT(MONEY,(B.DOCAMNT - A.DOCAMNT)*-1)) AS 'TOTAL'

FROM #temp1099a AS A
LEFT JOIN #temp1099b AS B ON A.VENDORID = B.VENDORID

December 14, 2012 at 11:43 AM