Skip to main content

Notifications

How to update the Inactive status for a Benefit/Deduction in mass in Human Resources

Hi Everyone,

Since many new customers are activating Human Resources (HR) at this time, I have been seeing a common question arise for Health Insurance codes especially this question, so I wanted to share it with you:

Q:  When I run the HR Reconcile utility for 'Update Benefit Setup' and "Update Benefit Enrollment', I have noticed that Inactive benefit/deduction codes on the Payroll side are getting created on the HR side as well, but they are marked as Active on the HR side still (and should also be inactive).   In GP I can only mark the code inactive in the HR Enrollment window (ie. one employee at a time).   The 'main' setup for the code does not have an Inactive field that I can roll down.  Therefore, how can I do a mass update to get this code inactive on the HR side?

A:  The reconcile utility will create all codes (even inactive codes), however, since the all the Status codes available on the HR side don't all exist on the Payroll side, the utility does not set this field for you and this is currently the design.   The user should be verifying all the fields are correct.  As mentioned above, you can inactivate the code on the HR side one employee at a time but GP does not have a 'mass' update feature to do this.   Therefore, to do this in mass, you would have to do this in SQL Server Management Studio.  Run the below script against the company database for the respective benefit/deduction code and it will set this code to be inactive for all employees

enrolled in the respective code on the HR side:

Keep in mind that the HR side has more fields than the Payroll side, but it is the Payroll side that is used in a checkrun, so the code will not be used in the checkrun anyway if the Payroll side is inactive.

*Before running any update script, it is always recommended to make a current backup copy of the company database first!


Scenario 1: DEDUCTIONS ONLY: The first script below will 'find' where the status code on the deduction on the HR side does not match the status code on the same code in the Payroll Deduction Master table, so you can view what codes do not match.  Then run the second script to 'update' the status on the HR side and the End Date, to match the status and End Date as stored on the Payroll side. Run these scripts against the company database to affect deduction codes only:

DEDUCTIONS:

*Run this script to find Deductions codes with mismatched statuses between HR and Payroll.
---------------------------------------
Select a.EMPLOYID, a.deducton as 'DEDUCTION CODE',
CASE a.INACTIVE
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
END AS 'PAYROLL',
CASE b.benefitstatus_i
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Inactive'
ELSE 'OTHER'
END AS 'HR',
a.DEDENDDT as 'DED End date'
from UPR00500 a
join BE010130 b
on a.EMPLOYID = b.EMPID_I
and a.DEDUCTON = b.BENEFIT
where (a.inactive = '0' and b.benefitstatus_i <> '1')
or (a.inactive = '1' and b.BENEFITSTATUS_I = '1')
---------------------------------------

*Run this script to update the HR table to have the same status as the Payroll Deduction.  You will get 2 results:  The first results represents how many statues were updated to 'active' in HR and the second results represents how many statuses were updated to 'inactive' in HR. 

---------------------------------------
update a set a.benefitstatus_i = '1', a.BNFENDDT = b.DEDENDDT
from BE010130 a
join UPR00500 b
on b.EMPLOYID = a.EMPID_I
and b.DEDUCTON = a.BENEFIT
where b.INACTIVE ='0'
and a.BENEFITSTATUS_I <> '1'
update a set a.benefitstatus_i = '2', a.BNFENDDT = b.DEDENDDT
from BE010130 a
join UPR00500 b
on b.EMPLOYID = a.EMPID_I
and b.DEDUCTON = a.BENEFIT
where b.INACTIVE ='1'
and a.BENEFITSTATUS_I = '1'
--------------------------------------

IMPORTANT NOTE: The benefit and deduction codes may have the same code ID, and are stored in two different tables on the Payroll side (UPR00500 and UPR00600), however, they are stored together as one record in only one table on the HR side (BE010130). So take note what script you ran last as it will overwrite the status and End Date on the record in HR, and will apply to both the benefit and deduction on the HR side. So if you have any instances where the deduction is inactive, but the benefit is active (or visa versa), you will need to pay attention to what script you ran last. It is recommended for these situations to view the code in the front-end and activate back the one needed in the front-end.  (See script to help find these at the end of the benefits section below.)

Scenario 2: BENEFITS ONLY:  The first script below will 'find' where the status code on the benefit on the HR side does not match the status code on the same code in the Payroll Benefit Master table, so you can view what codes do not match. Then run the second script to 'update' the status on the HR side and the End Date, to match the status and End Date as stored on the Payroll side. Run these scripts against the company database to affect benefit codes only:

BENEFITS:

*Run this script to find Benefit codes with mismatched statuses between HR and Payroll.
---------------------------------------
Select a.EMPLOYID, a.BENEFIT as 'BENEFIT CODE',
CASE a.INACTIVE
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
END AS 'PAYROLL',
CASE b.benefitstatus_i
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Inactive'
ELSE 'OTHER'
END AS 'HR',
a.BNFENDDT as 'BEN End date'
from UPR00600 a
join BE010130 b
on a.EMPLOYID = b.EMPID_I
and a. BENEFIT = b.BENEFIT
where (a.inactive = '0' and b.benefitstatus_i <> '1')
or (a.inactive = '1' and b.BENEFITSTATUS_I = '1')
-----------------------------------------

*Run this script to update the HR table to have the same status as the Payroll Benefit.  You will get 2 results:  The first results represents how many statues were updated to 'active' in HR and the second results represents how many statuses were updated to 'inactive' in HR. 

-----------------------------------------

update a set a.BENEFITSTATUS_I = '1', a.BNFENDDT = b.BNFENDDT
from BE010130 a
join UPR00600 b
on b.EMPLOYID = a.EMPID_I
and b.BENEFIT = a.BENEFIT
where b.INACTIVE ='0'
and a.BENEFITSTATUS_I <> '1'
update a set a.BENEFITSTATUS_I = '2', a.BNFENDDT = b.BNFENDDT
from BE010130 a
join UPR00600 b
on b.EMPLOYID = a.EMPID_I
and b.BENEFIT = a.BENEFIT
where b.INACTIVE ='1'
and a.BENEFITSTATUS_I = '1'

---------------------------------------

IMPORTANT NOTE: The benefit and deduction codes may have the same code ID, and are stored in two different tables on the Payroll side (UPR00500 and UPR00600), however, they are stored together as one record in only one table on the HR side (BE010130).  So if you have a benefit and deduction on the Payroll side, where one is active and the other is not, this may pose a problem on how HR is updated.  Keep in mind that there is only ONE STATUS field on the HR side, so it will be updated with whatever script you ran last (deduction or benefit), so the order you run the scripts will matter.   To catch these, run the script below to show you the benefits and deductions on the payroll side with the same name that have different statuses.  It is recommended to look at each of these codes in the front-end and modify the status as needed.  The paths to use are Cards|Payroll DeductionCards|Payroll|Benefit;  and Cards|Human Resources|Employee -Benefits and select the type and code.  One by one, modify the status for the code in the HR window as needed.

---------------------------
SELECT a.EMPLOYID,a.DEDUCTON AS 'CODE',
CASE b.INACTIVE
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
END AS 'BEN in PR',
CASE a.INACTIVE
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
END AS 'DED in PR'
FROM   UPR00500 a
       JOIN UPR00600 b
         ON a.EMPLOYID = b.EMPLOYID
            AND a.DEDUCTON = b.BENEFIT
WHERE  a.INACTIVE <> b.INACTIVE
-----------------------------

Scenario 3: ALL:  You can use the script below if you know a code (benefit and deduction) is inactive on the Payroll side for 'all' employees, and you just want to directly update the status for all the employees enrolled in it on the HR side too: (The first script will find all records for that code, whether a benefit or deduction, where it is not inactive on the HR side, and the second script will update the status for that code to be inactive for all employees enrolled in it on the HR side.)

Select * from BE010130 where BENEFITSTATUS_I <> '2' and BENEFIT = 'xxx'
---------------------------------------------------------------
update BE010130 set BENEFITSTATUS_I = '2' where BENEFIT = 'xxx'

--update the xxx placeholder for the benefit or deduction code that you would like to mark as inactive on the HR side. Note that this script just sets the INACTIVE status and does not update the Benefit/Deduction End Date on the HR side. Also refer to the note above in Scenario 2.


Benefit Status values mean:
1=active
2=inactive
3=waived

ADDITIONAL:

Note that if you are enabling HR for the Affordable Care Act, be sure to install the March Hot Fix that has just released.  (It includes the January Hot Fix in it.)  But you can read about the changes that were included in both for the Affordable Care Act using the blog links below:

MARCH HOT FIX:
https://community.dynamics.com/gp/b/dynamicsgp/archive/2015/05/08/the-march-hotfix-has-released-what-is-all-the-excitement-about.aspx

JANUARY HOT FIX:
https://community.dynamics.com/gp/b/dynamicsgp/archive/2015/01/23/draft-microsoft-dynamics-gp-2015-round-2-payroll-tax-update-what-you-need-to-know.aspx

I hope this information is helpful to you. 

Thanks,

Cheryl Waswick  | Sr. Technical Support Engineer | Microsoft Dynamics GP

--------------------------------------------------------------------------------------

Comments

*This post is locked for comments

  • Cheryl Waswick Profile Picture Cheryl Waswick
    Posted at

    Hi Ernie, In user setup, you should have "HR view for Payroll" marked.  So when you add the $.01 on the HR side, you should be prompted to create the linked ded and/or benefit on the payroll side.  You will want to do that.  But don't roll down to employees.    The payroll side doesn't matter.  It's the HR setup at the main setup level are you concerned with.

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Cheryl - an update and a question.  i updated one of the MISC codes for a penny and then i saw the incomplete link for the deduction (this one did not have a employer related cost).  looking through the deduction masters in the UPR00500, i did see that there were (of coarse) 0.00 deductions that were active.  would these also cause issue or should i be mostly concerned with the HR setup?

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Cheryl - OK, I am working out of the clients TEST environment and was starting to think that something must not be correct with the setup and it sounds like you just confirmed it.  Thanks for your replies, I will give it a try tomorrow.

  • Cheryl Waswick Profile Picture Cheryl Waswick
    Posted at

    Hi Ernie, All fields are 0?  This can happen if you still have any incompletes (even for life insurance).  You have to clean up ALL the incompletes at the setup level before you can run the reconcile for the enrollment level.   Even if you are doing a Misc Benefit, an unclassified Life Ins code may cause you issues.  They ALL need to be classified before you run the enrollment.  I would make a backup, delete the BE010130 table, then make sure ALL benefit setups are classified as either Health|Life|Misc or Retirement (From tools\setup for any type, click on the GO TO - INCOMPLETES and use the drop down list at the bottom to make sure ALL types are classified at the setup level first.  (And then the 'amount' fields should have $.01 on them at least at the setup level if the employee has a deduction, and the employer at least $.01 on the setup level for any benefits. The system looks to the amount fields at the top setup level to see how to link them to payroll. )  And then run the update benefit enrollment reconcile again to repopulate the BE010130 table.

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Cheryl - on my test example I used a Misc type.  The amount field, the period limit, the benefit type, type code, benefit method, deduction method were all '0'.  I am a bit hesitant just to update the status fields without updating the rest.

  • Cheryl Waswick Profile Picture Cheryl Waswick
    Posted at

    Hi Ernie,  I am just curious what other fields you need to update.   The reconcile process should update the BE010130 table, and the only fields we had questions about were the Active/Inactive, so that is the reason I wrote this blog.  So I do not have other scripts, as the reconcile process is the supported way to do it.  This reconcile process was intended to only be run ONCE when you add HR to Payroll and not to keep running it.  It could remove some information you have already edited.  We have received different reports on this, so nothing specific that I can say will happen.  It will depend on what edits you've made, what settings you have, etc.  So if you choose to run the reconcile options more than once, you could risk losing some data you have edited on it.  But again, it doesn't happen to everyone, so you will only know for sure if run it in a test environment first.  

  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at

    Cheryl - I know you made comments back to Jeanette in regards to only releasing scripts to update the "inactive" column, but I wanted to check and see if there was any additional updates to the scripts to update the other related fields?

    I have a client that there are approximately 4500 records affected in the BE010130 table.

    Additionally, you mention that there could be "issues" related to running the reconcile twice - I have heard this from other techs as well, but I am not finding any information as to what the "issues" exactly are.  What exactly are they as I am not finding any documentation on it?

    ernie

  • Jeannette Coty Profile Picture Jeannette Coty
    Posted at

    Thanks Cheryl... good tips!  

  • Cheryl Waswick Profile Picture Cheryl Waswick
    Posted at

    Hi Jeannette,

    If you wanted us to look at any setup, or why the incompletes happened, I'd have to ask you to open a support case for that assistance.  But really once it happens, it's quickest to go into GP and go to the INCOMPLETES window and click on each one.  It will prompt you what it needs and just press save.  This is the supported process and goes quickly even if you have a lot of them...   Just remember that we recommend to only run the reconcile options ONCE at initial startup when you add HR to an existing payroll setup, and not again.   You do risk some issues if you choose to run it more than once.   It should only be run one time.  

  • Jeannette Coty Profile Picture Jeannette Coty
    Posted at

    Thanks for the help, Cheryl! Yes, you gave me a couple of good tips.  I will verify the settings they had when they reconciled. The Health/Medical category reconciled fairly well, but, the Life and Retirement didn't.  There are hundreds of incomplete records that the has to update, and they don't have time to do that.  Let me know if you are interested in reviewing a document with the reconcile issues that this customer experienced.  Take care!