[Bill 124] Deduction Override Script Instructions

Modified on Thu, 4 Apr at 9:24 AM

EMPLOYEE DEDUCTION OVERRIDE SCRIPT INSTRUCTIONS

Overview

Using edsembli’s Employee Deduction Override fields can be a powerful way to make extra deductions in some business situations.

Boards use it regularly but one historic complaint is it takes a lot of time to make entries if there are a many employees to be updated. This prompted us to create a process for bulk updates. The process has a number of steps but in the hands of experienced HR staff and ICT Programmers, you will see its fairly straightforward.

The process involves three steps which will be described in detail. Briefly they are:

HR Manager enters the employee / group / deduction type / coverage code / employee share type / amount into an XLS. You have to make sure to put the data into the correct column but it’s all labelled. HR Manager then hands off the XLS to the ICT Programmer. Keep a copy for audit.
ICT Programmer has two steps to complete. First, eight (8) fields in the employee deduction table (EC_EMPLOYEE_BENEFITS) must be set to null (blank / nothing) for the open employee / group / deduction type / coverage codes in question. The script and instructions are included.
ICT Programmer then takes the XLS provided by HR and for each tab copies the data into Notepad and does a bit of formatting (really simple). This formatting turns the file into an executable script. The programmer then executes the script.

NOTE: Link to files referenced in article: https://edsembliinc-my.sharepoint.com/:f:/g/personal/frank_ferlaino_edsembli_com/EpHbLXQ7XY9HkpH1Xi4DmEUB0L0hISW61LAQ9kammqKDlg?e=lDEXwq

STEPS 1 IN DETAIL

HR opens the attached file titled “Benefits Overrides Script Template.xls”. Do not use the first tab, but copy it for as many group / benefit type / coverage code combinations you have.
Do NOT do your calculations or other work in the file provided – simply paste the data from another XLS into the one provided. Paste the values only – not the formulas.
Enter data into all nine (9) light blue fields. Do not touch the fields in between the light blue fields – these are needed by ICT.

Do NOT rearrange the columns – leave then in the order presented.

Make sure there are no extra spaces in the data. If the deduction code is LTD be sure the fields has just three characters.

For the purposes of Bill 124 this will be a one time deduction (at least here at BW) so make sure the start and end pay numbers match.

Copy and paste straight from K212 HRP to avoid spelling issues (coverage code for example).

Keep the amount a plain number – no dollar signs and no commas. 123.45 is an acceptable amount but $123.45 is not and 1,234.56 is not.

Note: Be sure to not accidentally add 1 to each pay number if you drag the column down (auto populating – we’ve all done it where Excel is trying to hep but it is not helpful in that case).

Repeat these steps for each group / deduction type / coverage code.
Keep a copy for audit purposes.
Send the XLS to your ICT Programmer along with this file and the file titled ‘STEP 2 CLEANING DATA RECORDS.sql’. If this is the first time you have had such work done, ask an ICT Manager for the correct contact – not all ICT Team members can do this work.
When all three steps are complete you want each record to look like this (inside the red box).

STEP 2 IN DETAIL

Open the file titled “STEP 2 CLEANING DATA RECORDS.sql”. Here you will update the EC_EMPLOYEE_BENEFITS table and set eight fields to null. Follow the instructions in the script – you will need to run this once for every tab in the XLS provided by HR (except the first template tab of course).
In the script you will change the emp_group_code, benefit_type, and coverage_code fields in the WHERE CLAUSE to match that in the tab you are working on. Then add in the list of employee IDs in that tab (also in the WHERE CLAUSE).

STEP 3 IN DETAIL

This step might seem cumbersome but is actually straightforward. For each tab in the XLS provided to you by HR (and completed by HR) simply do:

Copy (Ctrl-C) from cell A2 to TXX (XX being the last row).
Paste into Notepad.
In Notepad go to ‘Edit – Replace’ and replace invisible tabs with nothing (you will have to copy a ‘tab’ from the file and paste it into the Replace screen. Just to be clear, you are not replacing the three letters that spell the word TAB. You are replacing the blank space in Notepad that is created if you click on the tab button on your keyboard (apologies if that is remedial).
Replace all.
Remove the last comma at the end of the last line.
Copy all of what remains into MS SQL Server Management Studio (SSMS) Query Analyzer and execute.
Repeat for each tab.

Bonus Material for ICT Programmer:

When you execute the scripts the result set will say…

1 record affected

And on and on

… if you see anytime the result set says ‘0 records affected’ this means there was no record to update. Your HR manager would love to learn this info (they will know they made a mistake in the file OR that a record is missing that needs to be in place). It wouldn’t take much effort on your part to help the HR Manager out in this case (reminder – this script can be run over and over so you can identify which line produced the ‘0 records affected’ result.

Or… this script will identify the missing record(s)…

SELECT

E.EMPLOYEE_ID ,

E.FULLNAME ,

(SELECT COUNT(*)

FROM EC_EMPLOYEE_BENEFITS B

WHERE B.EMPLOYEE_ID = E.EMPLOYEE_ID

AND B.EMP_GROUP_CODE = 'SECTEACH' -- GET THIS VALUE FROM THE xls PROVIDED BY HR. THIS WILL NEED TO BE DONE ONCE FOR EACH TAB / GROUP IN THE XLS.

AND B.BENEFIT_TYPE = 'LTD' -- GET THIS VALUE FROM THE xls PROVIDED BY HR. THIS WILL NEED TO BE DONE ONCE FOR EACH TAB / GROUP IN THE XLS.

AND B.COVERAGE_CODE = 'LTD-SEC' -- GET THIS VALUE FROM THE xls PROVIDED BY HR. THIS WILL NEED TO BE DONE ONCE FOR EACH TAB / GROUP IN THE XLS.

AND GETDATE() BETWEEN BENEFIT_START_DATE AND ISNULL(BENEFIT_END_DATE, '2099-01-01')

) AS 'ZERO MEANS MISSING RECORD FOR THIS EMP / GRP / BEN TYPE / COV CODE – LET HR KNOW'

FROM EC_EMPLOYEE E

WHERE E.EMPLOYEE_ID IN

('',

'') -- USE THE EMPLOYEE LIST FROM THE TAB IN QUESTION

ORDER BY 3

Copy of 'STEP 2 CLEANING DATA RECORDS (1)' Script File:

-- STEP 1.

-- TO BE DONE BY HR

-- Use the template provided titled "Benefits Overrides Script Template.xls".

-- COMPLETE THE TABS COLOURed IN LIGHT BLUE.

-- CREATE ONE TAB FOR EACH EMPLOYEE GROUP / BENEFIT TYPE / COVERAGE CODE COMBO. IF THERE ARE MULTIPLE BENEFIT TYPES PER GROUP, CREATE A SEPERATE TAB FOR EACH GROUP / BENEFIT TYPE / COVERAGE CODE COMBO.

-- DONT WORRY ABOUT ALL THE COMMAS AND SINGLE QUOTES - YOUR ICT CONTACT WILL NEED THIS.

-- STEP 2 - CLEANING THE RECORDS FOR YOUR FUTURE UPDATES

-- HERE YOU WILL CLEAR OUT THE VALUES FOR 8 FIELDS. EVEN THOUGH ONLY LESS THAN ALL 8 ARE USED BY THIS PROCESS, HAVING OLD DATA IN THE FIELDS WILL MESS UP THE PROCESS.

-- YOU WILL HAVE TO DO THIS STEP ONCE FOR EVERY TAB IN THE XLS YOU WERE PROVIDED. IF THERE ARE DIFFERENT EMP GROUPS AND BENEFIT TYPES / CONVERAGE CODES

-- IN THE XLS YOU SHOULD SEND IT BACK TO HR TO HAVE THEM DELINIATE IT OUT CORRECTLY.

-- CHANGE THE 4 ITEMS BELOW AND EXECUTE IN YOUR TEST SYSTEM FIRST. NO NEED TO HAVE HR VERIFY YET.

UPDATE EC_EMPLOYEE_BENEFITS

SET OVERRIDE_START_PAY_NO = NULL,

OVERRIDE_END_PAY_NO = NULL,

OVERRIDE_EMP_TYPE = NULL,

OVERRIDE_EMP_AMOUNT = NULL,

OVERRIDE_BRD_TYPE = NULL,

OVERRIDE_BRD_AMOUNT = NULL,

OVERRIDE_TAX_TYPE = NULL,

OVERRIDE_TAX_AMOUNT = NULL

WHERE BENEFIT_TYPE = 'LTD' -- GET THIS VALUE FROM THE xls PROVIDED BY HR. THIS WILL NEED TO BE DONE ONCE FOR EACH TAB / GROUP IN THE XLS.

AND COVERAGE_CODE = 'LTD-SEC' -- GET THIS VALUE FROM THE xls PROVIDED BY HR. THIS WILL NEED TO BE DONE ONCE FOR EACH TAB / GROUP IN THE XLS.

AND EMP_GROUP_CODE = 'SECTEACH'-- GET THIS VALUE FROM THE xls PROVIDED BY HR. THIS WILL NEED TO BE DONE ONCE FOR EACH TAB / GROUP IN THE XLS.

AND GETDATE() BETWEEN BENEFIT_START_DATE AND ISNULL(BENEFIT_END_DATE, '2099-01-01')

AND EMPLOYEE_ID IN

('','') -- Get the list of employees FOR THIS GROUP FROM THE CORRECT TAB. ADD SINGLE QUOTES AND A COMMA ACCORDINGLY.