Local Taxes Set Up As Deductions?

It is not unusual, in my experience, for some local taxes to be set up as deductions.  This most commonly occurs with an earnings tax or "head" tax that is based on an income threshold.  For example, Denver has a "head" tax that only applies an individual who earns more than $500 in a month.

As you may already know, GP doesn't have the capability out of the box to calculate a threshold other than per pay period, year to date, or life to date.  So, if we paid an employee weekly, or semimonthly but wanted a threshold to be based on a month...we would be out of luck.

For this reason, you might choose to set up your local tax as a deduction.  This would allow you to include/exclude the deduction in the build, or enter transactions for the head tax.  Of course, there are other ways to approach it including manually entering a local tax code when needed on the payroll transactions or creating a customization.

But for those of you that do use a deduction to track local taxes, it can present an issue at year end.  If you map the deduction (using the W2 Box and Label fields from the Deduction Setup and Employee Deduction Maintenance windows) to the actual local tax field on a W2, the Microsoft Dynamics GP year end closing process for payroll will ignore the deduction.  I assume this is because it knows that the local taxes set up in GP as local taxes (Payroll Local Tax Setup) belong in that field.

If we caught this before year end, we could simply code the deduction to a box and label that is not currently being used and then modify the W2 form in Report Writer so that the field prints in the local tax box instead.  But what happens if we don't catch it in time?

Fortunately, if you can produce a spreadsheet of the employee ID with the tax amount and taxable wage, you can insert it in to the UPR10106 table, which is the Payroll Year End Local Table. Admittedly, I do this sort of thing a little oddly, but here it goes...

I add a column to my excel spreadsheet that says "INSERT INTO UPR10106 (RPTNGYR, EMPLOYID, SEQNUMBR, LOCLCODE, LOCLWGES, LCLINTAX, TAXTYPE) VALUES (".

Then I create a concatenated formula in Excel that appears like this:
=CONCATENATE(F2,"2011,'", A2,"',",1,",'DENVER',",D2,",",C2,",1",")")
To break these components down:
-F2 is the field with the static text mentioned above
-2011 is the hard coded reporting year for the W2
-A2 is the field with the Employee ID
-1 is the hard coded sequence number (I will discuss this more below)
-DENVER is the hard coded W2 Box Label/Local Code to appear on W2
-D2 is the field with the local taxable wages
-C2 is the field with the local tax
-1 is the hard coded tax type of City Income Tax (based on drop down in W2 Local Tax window)

Of course, you could choose to source the hard coded items from columns as well if you want.  The end result of this is a series of INSERT scripts that can be run against the company database to update the UPR10106 by inserting records.

INSERT INTO UPR10106 (RPTNGYR, EMPLOYID, SEQNUMBR, LOCLCODE, LOCLWGES, LCLINTAX, TAXTYPE) VALUES (2011,'ACKE001',1,'DENVER',10000,50.00,1)

One thing to watch out for, though, is the sequence number.  For each employee, for each year, the sequence number is an incremental number.  So, for example, if an employee had two local taxes withheld, the first one would have a sequence number of 1 and the second would have a sequence number of 2.  So if you have more than once local tax, you need to adjust the script accordingly.  And if a local tax is already included in the UPR10106 table for the year you are updating, you would also need to take this in to consideration or you will get a primary key error when attempting to insert the records with a duplicate employee ID, year, and sequence number.

Good luck with year end, and always always always make a backup before attempting these or any other scripts :)


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.