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 Deduction;  Cards|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

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