1095C Old Employees are Populating in Microsoft Dynamics GP

Payroll in January is the most havoc time of year.  One common problem I see on the help desk in January is the 1095C printing a form for an employee that has not worked this year or has been inactive since before the current year.

To correct this there are two scripts to run. Both of these scripts are from Terry Heley’s ACA post which can be referenced here. In both scripts, please replace XXX for field YEAR1 with the current year.

This script will remove any employee without coverage for the year:

delete from upr10111
where (MonthofCost_1=’0′ AND MonthofCost_2=’0′ AND MonthofCost_3=’0′ AND MonthofCost_4=’0’and
MonthofCost_5=’0′ AND MonthofCost_6=’0′ AND MonthofCost_7=’0′ AND MonthofCost_8=’0′ AND
MonthofCost_9=’0′ AND MonthofCost_10=’0′ and MonthofCost_11=’0′ AND MonthofCost_12=’0′ AND
MonthofCoverage_1=’0′ and MonthofCoverage_2=’0′ and MonthofCoverage_3=’0′ and MonthofCoverage_4=’0’and
MonthofCoverage_5=’0′ AND MonthofCoverage_6=’0′ AND MonthofCoverage_7=’0′ AND MonthofCoverage_8=’0′ AND
MonthofCoverage_9=’0′ AND MonthofCoverage_10=’0′ AND MonthofCoverage_11=’0′ AND MonthofCoverage_12=’0′)
and YEAR1=’XXX’

This script will need to have the year replaced in two places. This will delete users who are older than the current payroll year. For example , n 2016 you would replace the XX with 2016.

AND DEMPINAC <= ‘XXX-01-01 00:00:00.000′) and YEAR1=’XXX’

What these scripts do is update the UPR10111 table where the wage file creates data for the 1095C and 1094C transmittal form.

This information can be found on Terry Heley’s Blog, the payroll ACA information can be found here. Her blog is an amazing source of information and I highly encourage everyone to check it out.

Disclaimer: Always run a new script in your test environment and run a good backup before you run any scripts that will delete or change data.


1095C Form Show Wrong/Incorrect Information

One of my clients had an interesting issue that prompted me to write this article.  They have been tracking their ACA information religiously throughout the year.  However, when we created the wage file and printed the 1095C to screen, all of the figures were wrong.  The last code that they should have used was in 2015 and according to the documentation for the ACA, it should have rolled forward.

They utilize three benefit codes.  One for their Buy up plan, one for Dental, and one for Vision.  The buy-up plan is updated in August and the Dental and Vision plans are updated in February.  The change on the 1095C was in February and marked as none, none and 0.00.

We checked the codes on the each of the benefits and they were correct.  If you do not want the codes to apply to the ACA you are supposed to mark them as ‘None,’ ‘None,’ and leave the cost blank or 0.00.  The Buy-Up code was assigned the appropriate ACA reporting.

Then the next step would be to look into the SQL tables.  The following tables are the corresponding tables for the ACA:

UPR00904 – uprMstrDependentACA
UPR00905 – uprMstrEmployeeACA

These can also be found under my Table Reference page. The ACA-specific information is held in the UPR00905 (ACA Employee) /UPR00904 (ACA Dependent) tables and these tables can be updated to remove employees that are not eligible for the ACA health benefit prior to creating the year-end wage file, which will remove them from 1095-C reporting also.

ACA reporting is only based on the dates the employee was added to HR and not the hire/active dates on the PR side.  For example, one employee may start on February 2 and their benefits start 60 from their start date (In April).   If you do want to add a health benefit to an HR employee insure the GP system date is set to the date that you want the ACA reporting to start for the employee in the current year.

In this case, when a benefit is changed in August, it applies a record to the UPR00905 table.  You can also backdate the records by changing your user date.  For example, if you are adding a health benefit to an employee today but you want the ACA to list the date that health care was offered to be in February, you would change your user date in GP to ’02/01/2016′ prior to creating the record.

It is important to note how the 1095C pulls data.  From the table UPR00905 it will only select the last date of the month.  Meaning if you made a change on 2/12/2016 and then 3 months later backdate to 2/1/2016 to make a correction, it will pull the information from 2/12/2016.

Additionally, it will only record one benefit per month.  If you have two hospitalization plans, it will take the last record made for the month as the reporting record.  In our case we ran into the issue that the Dental, even though it was marked ‘None,’ ‘None,’ and 0.00, it was recording in February.  We corrected this by running a delete statement for the specific benefit codes that should not have been used in 2016.  Please note that we first tested the script in their test database with a fresh restore. 

“Delete UPR00905 where Year1=’2016’ and BENEFIT=‘benefitcode’”

 We then used a third party tool from Integrity Data that allowed users to manually adjust the UPR00905 table.  Integrity Data is a leader in innovative payroll solutions that greatly reduce the burden of the Affordable Care Act on employers.  Tom Franz, their Client Engagement Manager, has made their free utility available to me.  For more information on the free utility, email Tom at tfranz@integrity-data.com to request your free copy. In addition, their website (www.integrity-data.com/aca) is a great place for ACA-related information and also talks about Integrity Data’s full-featured ACA solution.

 I first extensively tested the tool in our test company to verify that it worked how we needed it to.  If you choose to use this tool, please complete testing within your test environment.  Straightforward Dynamic GP is not liable if you choose not to test and verify that this solution works for your specific need.

I used this 3rd party tool to only make corrections to past records.  It is not designed to update the information moving forward in Microsoft Dynamic GP.  After the correcting entries are made then you will want to utilize the proper method to making changes within Microsoft Dynamic GP to make any new entries.  Here are the steps I took:

  1. We will be using a 3rd party software from Integrity Data to fix the older entries. There is no built-in method through Great Plains.  This solution has been tested.
    1. Navigate to CARDS>>PAYROLL>>EMPLOYEE
    2. Select an employee
    3. Then select additional at the top
    4. Select Edit Employee ACA Information
  1. You will see all the codes from the beginning of HR. You can make changes to the employee and their dependents.post3-1
  1. To delete a row. Select the Year within the row you would like to delete and then the delete row button.post3-2


  1. To add a row go to the last item on the record and type the correcting entry. You will able to place an entry for earlier in the year.


Note:  I was not paid to promote any item from Integrity Data.


  1. Only use this window to make corrections to past records and never use it to make new entries moving forward. After these items are made then you will want to change your user date and update the Health Insurance through your normal process.
  2. Before trying this solution, it is HIGHLY recommended to make a backup. Then make a copy of your live database into your test environment and test all changes that you want to make before applying the changes to your live data. After testing in your TEST environment and it does what you want it to do, please make an additional backup before you apply the solution in your live database.
  3. Some companies monitor the 1095-C content throughout the year by creating a TEST company from a current copy of the LIVE database and creating the Year-End Wage file in TEST to review.


Resources used for creating this post:

Terry Heley’s Microsoft Dynamics GP Blog – She works for Microsoft and is the most knowledgeable person about Payroll and HR.  Each year she produces a guide for the ACA on her blog and includes troubleshooting steps.  I highly recommend following her blog.

Integrity Data – Integrity Data is a leader in the application of technology to improve business processes around payroll and Affordable Care Act tracking and reporting. Celebrating 20 years in business and serving over 8,000 organizations, Integrity Data strives to ensure that employers maintain IRS compliance around the Affordable Care Act and that their most valuable asset, their employees, are paid accurately and efficiently.