Implementation Tasks

Infrastructure Components

There are no new infrastructure components

Application Components

The purpose of this implementation is to update the values for pension scheme code and pensions plus in the people group of employees who have been identified as requiring auto-enrolment. The auto-enrolment process itself does not update the people group and we need this to happen else they will still show as ineligible.

The api requires an initial run which will update several thousand assignment records and thereafter it will have to be run on a monthly basis to pick up new hours to be notified staff. Numbers in second and subsequent runs will be much lower.

Payroll will provide a csv file containing assignment numbers, scheme code and pensions plus value that wiil be loaded to a temp table via sqlldr. The api will then be run against this temp table.

The temp table will be updated with details of any errors encountered during the run. On development system all errors were as a result of enployees we were trying to update having future dated changes already in the HR system. There were only 17 of these (out of some 4000 records) and on the basis that the live run will return similar numbers, payroll have agreed that the easiest approach is for us to pass them the list and they will process these people manually.

Implementation Steps

1. Obtain file of assignments and pension values from Payroll. This file should  contain assignment number, scheme code and pensions plus, in that order
2. remove the header row
3. save the file as auto_enrol.dat
4. log into the database server as hrapp, run the hrenv file and cd to the uoe_cron/ctl directory.
5. mkdir uoe_auto_enrol
6. copy auto_enrol.dat into this directory making sure specify ascii transfer mode
7. copy K:\mis\dsg\projects\hrs072\auto_enrol_api\auto_enrol.ctl into the same directory
8. run K:\ms\dsg\projects\hrs072\auto_enrol_api\uoe_auto_enrol_tmp_ddl (as apps)
9. run sqlldr to load data from the dat file to the temp table we have created in step 8 - sqlldr apps/<apps pwd> auto_enrol.ctl  . This will create file auto_enrol.log and if there are any errors auto_enrol.bad.  If there is a .bad file then this will have to be investigated before going any further.
10. During development I had a suspicion that there were control chars and blank spaces in the data therefore to clear these run K:\mis\dsg\project\hrs072\auto_enrol\data_clean.sql (as apps).
11. Run K:\mis\dsg\projects\hrs072\auto_entol_api\auto_enrol_api.sql (as apps). This will take about an hour and a quarter to run.
12. select * from uoe_auto_enrol_tmp where status='Error' and export the details to xls or csv file for payroll.

Application Setup

N/A 

Integration Components

 N/A

Data Population

 N/A

Data Migration

 N/A

Implementation Validation

1.  Check the uoe_auto_enrol_tmp table for errors. As mentioned above, there will be a small number due to date track changes but we would not expect anything else.
2. Check the HR application - select a few assignments for each scheme code and check the values in the people group  for these employees as at 01-Mar-2013