Earlier this year our HR department made the decision to switch all our employees from jobs to positions as well as create a new position numbering scheme. At that time we were in the middle of our Vista 7 migration, so we decided to make it part of the conversion. Clark Public Utilities is not a huge company, but we have enough employees that making the switch is very time-consuming. We also needed a way to apply the changes to different Vista 7 environments for testing since our number one rule is Do no harm to payroll. Our PDS Consultant Perry gave us a start, but since it was geared to the 4.1 environment, I needed something to use for Vista 7.
First step was to build a staging table with all the information to create and assign an employee to the position. Next I needed a way to load the table. I created a spreadsheet that holds all the information in the staging table. I save this spreadsheet as a tab-delimited file and created an External Interface to load the table.
So now I have all the information ready, next step is to create the positions. This took two separate steps:
Step 1 – Create the position, compensation, and position rate. The script creates a cursor from the staging table and calls the POS_EDIT procedure. This will create or update the position. Then I add the compensation data, then position rate.
Step 2 – Now that the positions are created, it’s time to assign employees to the positions. Once again, we create a cursor from the staging table. Because this script will remove the employee from their current job or position, we want to hold their current job/pos code. Next we call Emp_Position to add the employee. This procedure will kick off the Build Structure RPX job. Because of this I elected to assign the employees to the jobs in a pending assignment…trick here is setting the IsImmediate to “N”. After adding the employee to the position, we need to remove them from their old job or position. The handy ACT_RemoveEmpPos and ACT_RemoveEmpJob procedures came in handy here.
To get the employee positions out of pending, I ran the nightly procedure, UTL_Nightly. You could also run UTIL_PENDING_ASSIGN, but I chose the nightly procedure since I know that will sync everything up.
With all these steps complete, I decided to make things even easier for me. I converted the step 1 and step 2 scripts to stored procedures. In the External Interface I created to load the staging table, I added in the steps to call those procedures and run the nightly utility. So now switching everybody’s jobs/positions is as easy as running that interface. I even put this interface into our production 4.1 environment so that when the database was copied over to one of my Vista 7 test upgrade environments it’s there and part of my process for upgrading the system.
Attached is a step-by-step document on how to create the staging table and steps 1 and 2 and an example of the excel spreadsheet used, click here to view the details. You’ll need your PDS Support username and password to access the document.
Lead Applications/Analyst Developer
Clark Public Utilities