To inactivate the benefit and deduction codes in mass, run the below SQL scripts below against the company database in SQL Server Management Studio for the scenario that fits your needs:
*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:
---------------------------------------
Select a.EMPLOYID, a.deducton as 'Deduction', b.inactive as 'HR_Active',
a.inactive as 'Payroll_Active', 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 <> b. inactive
---------------------------------------
update a set a.inactive = b.inactive, a.BNFENDDT = b.DEDENDDT
from BE010130 a
join UPR00500 b
on b.EMPLOYID = a.EMPID_I
and b.DEDUCTON = a.BENEFIT
where b.INACTIVE <> a.INACTIVE
-------------------------------
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.
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:
---------------------------------------
Select a.EMPLOYID, a.BENEFIT as 'Paycode', b.inactive as 'HR_inactive',
a.inactive as 'Payroll_inactive', 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 <> b. inactive
---------------------------------------
update a set a.inactive = b.inactive, a.BNFENDDT = b.BNFENDDT
from BE010130 a
join UPR00600 b
on b.EMPLOYID = a.EMPID_I
and b.BENEFIT = a.BENEFIT
where b.INACTIVE <> a.INACTIVE
---------------------------------------
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 above 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 benefit is inactive, but the deduction 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.
Scenario 3: ALL: Use this script if you know a code (benefit and deduction) is inactive on the Payroll side for 'all' employees, and you just want to 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