Implementation Plan
Stage: Build
Implementation Plan
eRecruitment
HR General
HRS070
Document Version: 1.10
Date: 24/07/2012
Contents
2.1 Infrastructure components. 4
2.2.1 Create new Extra Information Types in HR.. 6
2.2.2 Employee Check Interface. 31
2.2.3 IDM email and uun feed to HR.. 31
2.2.4 Reference data interface. 33
2.2.5 One off load of reference data that is not in the interface. 36
2.2.6 Successful Applicant Interface. 36
2.2.7 Pro-rata Salary Interface. 37
2.4 Integration components. 38
2.7 Implementation validation. 38
2.7.1 Employee check interface. 38
2.7.2 Reference Data Interface. 38
1 Document Management
1.1 Contributors
Please provide details of all contributors to this document.
Role | Unit | Name |
Systems Analyst Designer (Owner) |
| Alyson Shaw |
Technical Architect (Owner) |
| Pride Shoniwa |
Developer |
| Greg Carter |
Development Technology Administrator |
|
|
Production Management Coordinator |
|
|
Project Manager |
| Rhian Davies |
Other document contributors |
|
|
1.2 Version Control
Please document all changes made to this document since initial distribution.
Date | Version | Author | Section | Amendment |
27/03/12 | 1.0 | GregC | All | Initial draft |
30/03/12 | 1.1 | GregC | 2.2.1 | Added steps to create the “at risk” field in HR |
18/04/12 | 1.2 | GregC | 2.2, 2.7 | Updated with progress on HR uun load and enhanced interfaces tests. |
23/04/12 | 1.3 | A Shaw | 2.2.1 | Add details of creation of new fields in HR |
09/05/12 | 1.4 | A Shaw | 2.2.1, 2.2.5 | Add one off load of reference data (2.2.5) and fix screenshot error in 2.2.1. |
09/05/12 | 1.5 | A Shaw | 2.2.1.5 | Add section on alteration of HR fastpath menu |
17/05/12 | 1.5.1 | D Adamson | 2.2.1.4 | Update Spelling Error in ERECUIT to ERECRUIT |
29/05/12 | 1.6 | A Shaw | 2.2.6 | Add successful applicant interface |
30/05/12 | 1.7 | PShoniwa | Various | Updates following implementation to TEST |
12/06/12 | 1.8 | A Shaw | 2.2.4.1 | Remove ORG_ROLES file from section on reference data interface as this file is no longer required. |
26/06/12 | 1.9 | A Shaw | 2.1 | Add extra step to section on creation of IDM services (to handle manually created service groups) |
24/07/12 | 1.10 | A Shaw | 2.2.7 | Add pro-rata salary interface |
2 Implementation tasks
2.1 Infrastructure components
The TAD is located at:
- O:\projects\areas\hr\general\HRS070\secure\Technical_Architecture.doc
The TAD is the golden copy for infrastructure specification and is assumed to be up to date – what follows here is just a brief summary to get an idea of the integration. All the technical infrastructure must be created before any software components can be deployed.
The software needs to access IDM. So the following IDM service users need to be created and assigned the SERVICE_USER role:
- COREHR_SERVICEUSER
- HR_SERVICEUSER
The first step is to create manual service groups within grouper. These are probably not strictly required for the interface we are building but the default position is that it should always be created when the service is set up just in case.
This task has to be done by someone who has admin access to grouper via the UI.
http://www-test.grouper.is.ed.ac.uk/grouper/
Note that the / at the end of the url is important else will get error.
- Log in as admin user
- Select the Services Link
- Select Create Group
- Name: COREHR
- ID:COREHR
- Description: Group for eRecruitment system employee check interface
- Make sure that the check box for unix custom group is ticked.
- Save
- Create another group
- Name: HR
- ID:HR
- Description: Group for HR uun load
- Make sure that the check box for unix custom group is ticked
- Save
Then the following scripts need to be executed as database user IDMENGINE@IDM[TEST|LIVE]:
- K:\mis\dsg\Projects\hrs070\implementation\idmengineuser_schema\create_idm_auto_services.sql
- K:\mis\dsg\Projects\hrs070\implementation\idmengineuser_schema\create_idm_manual_services.sql
Please note that these services are defined with service IDs 251 and 252, which were valid at the time of creation. Due to the nature of service ID generation, please check that these service IDs are available on the target environment.
You can check the SERVICEID availability by doing a SELECT on the IDM_SERVICE_DEFINITIONS table within the IDMENGINE schema.
The above files must be run in the order specified as the create_idm_services_auto.sql actually creates the services and assigns them to the automatic groups. The second script then assigns them to the manual groups that we have created via the UI in the previous step.
The following database links (defined in the TAD) need to be created to allow communication among IDM, HR and COREINT. Ensure that the link users have been assigned the appropriate database roles as stated in the TAD.
- COREINTUOEPPLINK
- COREINTIDMLINK
- COREINTAPPSLINK
- APPSIDMLINK
- APPSCOREINTLINK
The following additional privileges need to be granted to APPS by a dba account:
- Create materialised views (and use dbms_mview)
- Select on v$instance (this may already exist)
- Ability to use dbms_scheduler (Assign the CREATE JOB system privilege to the user)
The following additional privileges need to be granted to COREINT by a dba account:
- Create materialised views (and use dbms_mview)
- Select on v$instance
- Ability to use dbms_scheduler (Assign the CREATE JOB system privilege to the user)
The reference data interfaces need to use UTL_FILE. An oracle directory object called COREINT_DIR needs to be created pointing to the UNIX directory “/u01/software/coreint/core_extract “. The Oracle user needs to have read/write on this directory and COREHR needs to have access to this directory to pick up the files.
2.2 Application components
eRecruitment consists of a lot of individual components that can be deployed in isolation, although there are some common objects/scripts shared by components that will be deployed with the first component that gets deployed.
This implementation plan will break the deployment down into the various components. Each component will list the common scripts that need to be run first – it is the implementer’s job to track whether common scripts have been run.
Note that the root location for all deployment scripts is K:\mis\dsg\Projects\hrs070\implementation. From now on that location will be referred to as ${root}.
Unless otherwise stated, running order of scripts does matter so execute them in the order that they are presented in the bullet point lists.
2.2.1 Create new Extra Information Types in HR
2.2.1.1 Create New Lists of Values
Navigate System Administrator > Application > Validation >Set
2.2.1.1.1 VS_UOE_RELIGION
Value Set Name | VS_UOE_RELIGION |
Description | List of religions and beliefs |
List Type | List of Values |
Security Type | No Security |
Format Type | Char , numbers only 0-9 |
Maximum size | 3 |
Validation Type | Independent |
Then navigate Validation > Values and search for the value set you have just created:
Input the following:
Value | Description |
01 | No Religion |
04 | Christian – Church of Scotland |
05 | Christian – Catholic |
08 | Christian – Other Denomination |
09 | Hindu |
10 | Jewish |
11 | Muslim |
12 | Sikh |
13 | Spiritual |
14 | Any other religion or belief |
98 | Prefer not to say |
Set the enabled from date to be current date for all of them.
2.2.1.1.2 VS_UOE_ORIENTATION
Value Set Name | VS_UOE_ORIENTATION |
Description | List of sexual orientations |
List Type | List of Values |
Security Type | No Security |
Format Type | Char , numbers only 0-9 |
Maximum size | 2 |
Validation Type | Independent |
Input values as follows:
Value | Description |
01 | Bisexual |
02 | Gay man |
03 | Gay woman/lesbian |
04 | Heterosexual |
05 | Other |
98 | Prefer not to say |
Set the enabled date to be current date for all of them.
2.2.1.1.3 VS_UOE_YES_NO_NOT_SAY
Value Set Name | VS_UOE_YES_NO_NOT_SAY |
Description | Yes, No, Prefer not to say |
List Type | List of Values |
Security Type | No Security |
Format Type | Char , numbers only 0-9 |
Maximum size | 3 |
Validation Type | Independent |
Input Values as follows:
Value | Description |
01 | Yes |
02 | No |
98 | Prefer not to say |
Set the enabled date to be current date for all of them.
2.2.1.1.4 VS_UOE_CLINICAL_ACCESS
Value Set Name | VS_UOE_CLINICAL_ACCESS |
Description | List of clinical access levels |
List Type | List of Values |
Security Type | No Security |
Format Type | Char , numbers only 0-9 |
Maximum size | 3 |
Validation Type | Independent |
Insert values as follows:
Value | Description |
01 | Contact with patients, providing prevention, diagnosis or treatment (face to face or telephone) |
02 | Contact with patients but NOT providing prevention, diagnosis or treatment (face to face or telephone) |
03 | Access to patient data or tissue |
04 | Access to patient data |
Set the date enabled to current date for all of them.
2.2.1.1.5 VS_UOE_PVG_CHECK
Value Set Name | VS_UOE_PVG_CHECK |
Description | List of PVG Check Types |
List Type | List of Values |
Security Type | No Security |
Format Type | Char , numbers only 0-9 |
Maximum size | 3 |
Validation Type | Independent |
Input values as follows:
Value | Description |
01 | PVG Scheme – Children |
02 | PVG Scheme – Protected adults |
03 | PVG Scheme – Children and protected adults |
04 | Basic Disclosure |
05 | Standard Disclosure |
06 | Disclosure Not Required |
Set the date enabled to current date for all of them.
2.2.1.1.6 VS_UOE_RESP_TO
Value Set Name | VS_UOE_RESP_TO |
Description | List of job roles which have people reporting to them |
List Type | List of Values |
Security Type | No Security |
Format Type | Char , numbers only 0-9 |
Maximum size | 3 |
Validation Type | Independent |
Input values as follows:
Value | Description |
01 | Director |
02 | Head of Department |
03 | Head of School |
04 | Head of College |
Set the date enabled to current date for all of them.
2.2.1.1.7 VS_UOE_VACANCY_REASON
Value Set Name | VS_UOE_VACANCY_REASON |
Description | List of vacancy reasons |
List Type | List of Values |
Security Type | No Security |
Format Type | Char , numbers only 0-9 |
Maximum size | 3 |
Validation Type | Independent |
Input values as follows:
Value | Description |
01 | New |
02 | Replacement |
03 | Maternity/Paternity Leave Cover |
04 | Sickness or other Absence Cover |
05 | Secondment Cover |
06 | Hours to be notified |
Set the date enabled to current date for all of them.
2.2.1.2 Create New Extra Information Type on the Person Record for eRecruitment information
Log in to HR application as System Administrator
1. Navigate to Application> Flexfield > Descriptive > Segments
2. F11 to query and type ‘Extra Person Information’ into the Title box. Ctrl F11 to execute the query.
3. Unclick the Freeze Flexfield Definition box – just accept the message that comes up:
4. Click in the context field values section and then click the plus icon at the top left of the toolbar – this adds a new row:
5. Input the code for the new field: UOE_ERECRUIT_EIT . Set the name to Recruitment Information. Set description to be ‘Extra information type used by eRecruitment ’ then ctrl S:
6. Hit the Segments and create new entry. Name is AT_RISK. Window Prompt: At Risk? When you click on the column field a search box comes up for you to select which column to use. Just use PEI_INFORMATION1. Choose Value set VS_UOE_Yes_No from the list. Make sure that displayed and enabled are both ticked:
7. Click next to AT_RISK and click Open button at the bottom right. Fill in the description –‘Identifies employees whose employment is considered at risk ’. Make sure that the required field is unchecked as some people will not have this populated ctrlS to save :
8. Navigate back to the previous screen and input another entry for PERSONAL_EMAIL:
Set the other fields as shown below. Again, make sure that the ‘required field’ box is de-selected.
9. Navigate back to the initial screen, ctrl S and then click the Freeze Flexfield definition box. When you navigate away it will prompt you to save and then say that it is recompiling the flexfield.
Register the new EIT
1. Switch responsibility to UK HRMS Manager
2. Navigate Processes and Reports> submit processes and reports>single request
3. Search for ‘Register Extra Information Types (EITs)’ (or Register%)
4. In the parameters screen, set table to be Assignment Extra Information PER_PEOPLE_INFO_TYPES
5. Set the Information type to Recruitment Information
6. Set multiple rows to N
Grant access to the EIT to the relevant responsibilities
1. Switch responsibility to UK HRMS Manager
2. Navigate Security>Information Types Security
3. In the top section put into query mode (F11) and type in %UK%HRMS% to bring up UK HRMS Manager
4. Select it, and click in the information types zone to add in UOE_ERECRUIT_EIT. In the Find box that comes up, type UOE%ERECRUIT% and Find it should appear. Select it and click ok.
5. CtrlS to save
6. Repeat steps 3 , 4 and 5 to add it to the UOE Personnel support responsibility – query UOE%Pers%
2.2.1.3 Create new Extra Information Type on Person Record for Equal Opportunities and HESA related information.
Log in to HR application as System Administrator
1. Navigate to Application> Flexfield > Descriptive > Segments
2. F11 to query and type ‘Extra Person Information’ into the Title box. Ctrl F11 to execute the query.
7. Unclick the Freeze Flexfield Definition box – just accept the message that comes up.
8. Click in the context field values section and then click the plus icon at the top left of the toolbar – this adds a new row:
9. Input the code for the new field: UOE_EQUAL_OPPS_EIT . Set the name to Equal Opportunities. Set description to be ‘Equal Opportunities Information (also used for HESA) ’ then ctrl S:
Hit the Segments and create new entries as below.
Note that the value set name for GENDER_IDENTITY is VS_UOE_YES_NO_NOT_SAY and the name of the second one is SEXUAL_ORIENTATION. Make sure that displayed and enabled are both ticked:
10. Click next to RELIGION_BELIEF and click Open . Fill in details as shown below. Make sure that the required field is unchecked as some people will not have this populated ctrlS to save :
11. Repeat for SEXUAL_ORIENTATION:
12. Repeat for GENDER_IDENTITY:
13. Navigate back to the initial screen, ctrl S and then click the Freeze Flexfield definition box. When you navigate away it will prompt you to save and then say that it is recompiling the flexfield.
Register the new EIT
7. Switch responsibility to UK HRMS Manager
8. Navigate Processes and Reports> submit processes and reports>single request
9. Search for ‘Register Extra Information Types (EITs)’ (or Register%)
10. In the parameters screen, set table to be Assignment Extra Information PER_PEOPLE_INFO_TYPES
11. Set the Information type to Equal Opportunities
12. Set multiple rows to N
Grant access to the EIT to the relevant responsibilities
13. Switch responsibility to UK HRMS Manager
14. Navigate Security>Information Types Security
15. In the top section put into query mode (F11) and type in %UK%HRMS% to bring up UK HRMS Manager
16. Select it, click in the information types zone and add in UOE_EQUAL_OPPS_EIT (select from the list). ctrlS to save.
17. Repeat steps 15 and 16 for the UOE Personnel support responsibility.
2.2.1.4 Create Extra Information Type on the Assignment Record
Log in to HR application as System Administrator
1. Navigate to Application> Flexfield > Descriptive > Segments
2. F11 to query and type ‘Extra Assignment Information’ into the Title box. Ctrl F11 to execute the query.
4. Unclick the Freeze Flexfield Definition box – just accept the message that comes up:
5. Click in the context field values section and then click the plus icon at the top left of the toolbar – this adds a new row:
6. Input the code for the new field: UOE_ERECRUIT_EIT. This can be the same name as the other one created earlier as it is against different flexfield. Set the name to Recruitment Information. Set description to be ‘Extra information type used by eRecruitment ’ then ctrl S:
6. Hit the Segments and create new entries as below. Note that the value set name for CLINICAL_ACCESS is VS_UOE_CLINICAL_ACCESS and the one for VACANCY_REASON is VS_UOE_VACANCY_REASON. Make sure that displayed and enabled are both ticked:
7. Click next to HONORARY_CONTRACT and click Open . Fill in details as shown below. Make sure that the required field is unchecked as some people will not have this populated ctrlS to save :
8. Repeat for CLINICAL_ACCESS:
9. Repeat for PVG_CHECK:
10. Repeat for RESPONSIBLE_TO:
11. Repeat for VACANCY_REASON:
12. Navigate back to the initial screen, ctrl S and then click the Freeze Flexfield definition box. When you navigate away it will prompt you to save and then say that it is recompiling the flexfield.
Register the new EIT
13. Switch responsibility to UK HRMS Manager
14. Navigate Processes and Reports> submit processes and reports>single request
15. Search for ‘Register Extra Information Types (EITs)’ (or Register%)
16. In the parameters screen, set table to be Assignment Extra Information PER_ASSIGNMENT_INFO_TYPES
17. Set the Information type to Recruitment Information.
18. Set multiple rows to N
Grant access to the EIT to the relevant responsibilities
19. Switch responsibility to UK HRMS Manager
20. Navigate Security>Information Types Security
21. In the top section put into query mode (F11) and type in %UK%HRMS% to bring up UK HRMS Manager
22. Select it, click in the information types zone and add in UOE_ERECRUIT_EIT (select from the list – there will be two so pick the one that is attached to the assignment table PER_ASSIGNMENT_EXTRA_INFO
23.
24. PER_ASSIGNMENT_INFO_TYPES ?
25. ).
26. Repeat steps 21 and 22 for the UOE Personnel support responsibility.
2.2.1.5 Amend the Fastpath menu for UOE Personnel Support
Navigate System Administrator > Application > Menu.
F11 to enter query mode and query up UOE_MN_PST_FASTPATH (ctrlF11 to execute)
Click in the bottom section and alter sequence no 8 , Extra Information, to say Extra Assignment Information. Ctrl S to save. A message comes up saying that the menu is being recompiled.
Then click the plus icon to add a new one. Give it sequence number 11. Prompt is Extra Person Information. Set the Function to Define Person Extra Information (there is a search box that comes up so choose this from the list. Description is View/Enter Person Extra Information. CtrlS to save.
2.2.2 Employee Check Interface
2.2.2.1 Common scripts
Log in as database user COREINT@EJOB[TEST|LIVE] and execute:
- ${root}/coreint_schema/common_scripts/coreint_logs.sql
- ${root}/coreint_schema/common_scripts/coreint_refresh_environment.sql
- ${root}/coreint_schema/common_scripts/coreint_utility_pkg.sql
2.2.2.2 Main scripts
Log in as database user APPS@HRDB[TEST|LIVE] and execute:
- ${root}/apps_schema/employee_check_interface/apps_grants_for_employee_check_interface.sql
Log in as database user COREINT@EJOB[TEST|LIVE] and execute:
- ${root}/coreint_schema/employee_check_interface/coreint_internal_employee_tmp.sql
- ${root}/coreint_schema/employee_check_interface/coreint_internal_employee_mv.sql
- ${root}/coreint_schema/employee_check_interface/coreint_employee_type_tmp.sql
- ${root}/coreint_schema/employee_check_interface/coreint_employee_type_mv.sql
- ${root}/coreint_schema/employee_check_interface/create_materialised_views.sql
- ${root}/coreint_schema/employee_check_interface/coreint_employee_check_pkg.sql
- ${root}/coreint_schema/employee_check_interface/define_employee_check_schedule.sql
- ${root}/coreint_schema/employee_check_interface/coreint_interfaces_pkg.sql
2.2.3 IDM email and uun feed to HR
Miss out this section (2.2.3) as the build for this not complete
2.2.3.1 Common scripts
Log in as database user APPS@HRDB[TEST|LIVE] and execute:
- ${root}/apps_schema/common_scripts/uoe_apps_logs.sql
- ${root}/apps_schema/common_scripts/uoe_apps_environment.sql
- ${root}/apps_schema/common_scripts/uoe_apps_utility_pkg.sql
2.2.3.2 Main scripts
Log in as databse user UOEPP@HRDB[TEST|LIVE] and execute:
- ${root}/uoepp_schema/idm_email_and_uun_feed/uoepp_grants_for_idm_email_uun_feed.sql
- ${root}/uoepp_schema/idm_email_and_uun_feed/uoe_idm_temp_patch_uun.sql
- ${root}/uoepp_schema/idm_email_and_uun_feed/UOEPP.EPP4220_ddl.sql
Log in as database user APPS@HRDB[TEST|LIVE] and execute:
- ${root}/apps_schema/idm_email_and_uun_feed/uoe_idm_upload_tmp.sql
- ${root}/apps_schema/idm_email_and_uun_feed/uoe_idm_upload_mv.sql
- ${root}/apps_schema/idm_email_and_uun_feed/create_idm_upload_materialised_views.sql
- ${root}/apps_schema/idm_email_and_uun_feed/uoe_idm_upload_pkg.sql
- ${root}/apps_schema/idm_email_and_uun_feed/define_uoe_idm_upload_pkg_schedule.sql
Log in as database user IDSTORE@IDM[TEST|LIVE] and execute:
- ${root}/idstore_schema/idm_email_and_uun_feed/cre_idstore_identity_package.sql[GC1]
2.2.3.3 Cron job changes
Log in to the [TEST|LIVE] database server as unix user hr11i. The root folder on the database server is held in the environment variable $UOE_TOP, so you’ll need to run the relevant environment script (as that file is itself not held in a consistent location in each environment you’ll just have to figure out that part for yourself).
- Delete the files:
- $UOE_TOP/cron/cron_prefaddr
- $UOE_TOP/cron/cron_uun
- Copy the file ${root}/database_filesystem/cron/cron_card to $UOE_TOP/cron
- Ensure that $UOE_TOP/cron/cron_card uses unix line terminators (use the dos2unix utility if necessary) and has permissions 755.
- Copy the file ${root}/database_filesystem/bin/uun/load_sit_table.sh to $UOE_TOP/bin/uun
- Ensure that $UOE_TOP/bin/uun/load_sit_table.sh uses unix line terminators and has permissions 755.
- Copy the file ${root}/database_filesystem/sql/uun/uoe_hrms_update.sql to $UOE_TOP/sql/uun
- Ensure that $UOE_TOP/sql/uun/uoe_hrms_update.sql uses unix line terminators and has permissions 755.
Copy the file ${root}/database_filesystem/cron/crontab_hr to $UOE_TOP/cron
- Ensure that $UOE_TOP/cron/cron_card uses unix line terminators and then use the crontab command to refresh the scheduled croncrontsabs. NOTE: the crontab_hr file is the one taken from the Live server at the start of this project, as such all of the environment arguments are “live”. The specific changes made to the crontab_hr file for HRS070 are:
- # HRS070 eRecruitment removed this job 55 06 * * * /u12/software/hr11i/product/ora11iappl/uoe/1.0/cron/cron_prefaddr live 2>&1 | mailx -s"LIVE PrefAddr copy" mis-appsupp@lists.ed.ac.uk #NEW CRON #
- # HRS070 eRecruitment removed this job and replaced with new job below 30 07 * * 1-5 /u12/software/hr11i/product/ora11iappl/uoe/1.0/cron/cron_uun live |mailx -s"LIVE Email Upload" mis-appsupp@lists.ed.ac.uk SSHR.support@ed.ac.uk
- 30 07 * * 1-5 /u12/software/hr11i/product/ora11iappl/uoe/1.0/cron/cron_card live |mailx -s"LIVE Card Upload" mis-appsupp@lists.ed.ac.uk SSHR.support@ed.ac.uk
2.2.3.4 One-off load for ex-employees
Log in as database user APPS@HRDB[TEST|LIVE] and execute:
- ${root}/apps_schema/idm_email_and_uun_feed/one_off_load_for_ex_employees.sql
2.2.4 Reference data interface
2.2.4.1 Set up user-defined table within HR
Log in to the HR application and navigate to UK HRMS Manager > Other Definitions > Table Structure.
Input the following details (see screenshot below):
- Name: UOE_ERECRUITMENT_INTERFACE
- User Table Key: UOE_ERECRUITMENT_INTERFACE
- Match Type: Match
- Key Units of Measure: Text
- Row Title: Interface Scheduling
Hit ctrl-S to save.
Columns
Hit the columns button. There will be one column for each interface. Ensure that the column names are input exactly as specified as the extract job checks the names when deciding what to run.
User Column Key | Name |
ACCOUNT_CODES | ACCOUNT_CODES |
COST_CENTRES | COST_CENTRES |
DEPARTMENTS | DEPARTMENTS |
FUNDING_SOURCES | FUNDING_SOURCES |
HIERARCHY | HIERARCHY |
JOB_CODES | JOB_CODES |
LOCATIONS | LOCATIONS |
MANAGEMENT_UNITS | MANAGEMENT_UNITS |
USER1 | USER1 |
Please ignore the ‘ORG_ROLES’ entry in the above screenshot. The requirement for ORG_ROLES file was removed after initial set up but I have been unable to delete the column from the table we had originally created on DEV and so cannot obtain revised screenshot without ORG_ROLES in it.
Ctrl-S to save
Rows
There is only 1 row (see screenshot):
- Sequence: 1
- Exact: run_interface
- User Row Key: run _interface
- Effective Date From: just set this to current date
Values
Navigate UK HRMS Manager > Other Definitions > Table Values.
Hit F11 to enter query mode.
In the Table name box, input %UOE%ERECRUITMENT% and ctrl-F11 to execute the search. This should retrieve details for our UOE_ERECRUITMENT_INTERFACE table.
Insert value to say whether to run each interface: e.g. when form opens it has ACCOUNT_CODES column displayed. Input Y for the value of run_interface (see screenshot below). Once have input for the ACCOUNT_CODES, click on the column name field and then use down arrow key to move on to the next one (COST_CENTRES) and so on until all input. Set them all to Y for now as want to run them all the first time. Remember to save.
2.2.4.2 Common scripts
Log in as database user COREINT@EJOB[TEST|LIVE] and execute:
- ${root}/coreint_schema/common_scripts/coreint_logs.sql
- ${root}/coreint_schema/common_scripts/coreint_refresh_environment.sql
- ${root}/coreint_schema/common_scripts/coreint_utility_pkg.sql
2.2.4.3 Main scripts
Log in as database user APPS@HRDBTEST/ORA11i and execute:
- ${root}/apps_schema/reference_data_interface/uoepp_grant.sql
Log in as database user UOEPP@HRDBTEST/ORA11i and execute:
- ${root}/uoepp_schema/reference_data_interface/create_uoepp_views.sql
Log in as database user COREINT@EJOB[TEST|LIVE] and execute:
- ${root}/coreint_schema/reference_data_interface/create_coreint_tables.sql
- ${root}/coreint_schema/reference_data_interface/coreint_ref_data_pkg_ddl.sql
- ${root}/coreint_schema/reference_data_interface/initial_run.sql
- ${root}/coreint_schema/reference_data_interface/define_reference_data_schedule.sql
2.2.5 One off load of reference data that is not in the interface
Log in as database user APPS@/HRDBTEST/ORA11i and execute:
- ${root}/apps_schema/one_off_load_grant.sql
Log in as database user UOEPP@HRDBTEST/ORA11i and execute:
- ${root}/uoepp_schema/one_off_load/create_gradestep_mapping.sql
- ${root}/uoepp_schema/one_off_load/uoepp_one_off_load_pkg.sql
- ${root}/uoepp_schema/one_off_load/one_off_load_test_version.sql (or live_version depending on environment!). This calls the procedure to generate output files.
Log into unix (girnigoe/glamis) as hr11i and cd to:
/u12/software/hr11i/product/hrdbtestcomn/admin/out (TEST)
/u12/software/hr11i/product/ora11icomn/admin/out (LIVE)
The following files should have been created and should have file size > 0:
- reference.csv
- hr_reference1.csv
- posref.csv
- scale_point.csv
- pos_rate.csv
Copy these files to:
${root}/uoepp_schema/one_off_load/output
Make sure to specify ascii for transfer mechanism (do not let it do automatic).
They will be loaded to the application itself by the application suppliers.
Once files created, drop package uoepp_one_off_load_pkg. Do NOT drop the gradestep mapping table for now.
2.2.6 Successful Applicant Interface
2.2.6.1 Pre-requisites
2.2.6.1.1 COREHR api has to be in place
The following package/procedure has to exist on COREHR database (TEST/LIVE)
corehr.pers_recruit_interface.set_applicant_interfaced_date
This has to be implemented by CORE (the vendors) as they have built this specially for us.
2.2.6.1.2 Database link APPSCOREINTLINK and bi_corehr role must exist
Link is owned by APPS and connects to EJOBDEV. Link user name is COREINTAPPSLINK and the user has role called bi_corehr.
This role is also used by BOXI universe and was created on DEV under the auspices of the BOXI implementation. If it does not already exist on TEST then the bi_corehr role will have to be created before continuing. The grants it needs for this implementation are dealt with in 2.2.6.2 below (as we only need subset of the CORE views).
2.2.6.2 Database scripts
Log in as database user COREHR@EJOBTEST/LIVE and execute:
- ${root}/pro_rata_salary/core_grant.sql
- ${root}/corehr/grants/core_api_grant.sql
Log in as database user APPS@HRDBTEST/ORA11i and execute:
- ${root}/apps_schema_schema/successful_applicant_interface/create/create_erecruit_tables.sql
- ${root}/apps_schema_schema/successful_applicant_interface/packages/uoe_erecruit_appoint_util_pkg.sql
- ${root}/apps_schema_schema/successful_applicant_interface/packages/uoe_erecruit_appoint_pkg.sql
2.2.7 Pro-rata Salary Interface
This interface was added late in the day after the initial test implementation. It makes use of same coreint_interfaces_pkg and employee_check_pkg packages which we implemented earlier (with some amendments) and requires some new tables and new materialized view. Since it was implemented separately on dev, I have kept it separate for test and live as well in case the whole thing does not go live at the same time. This is an update to the initial implementation.
Log in as database user COREINT@EJOB[TEST|LIVE] and execute:
- ${root}/pro_rata_salary/ coreint_grade_hours_tmp.sql
- ${root}/pro_rata_salary/ coreint_grade_hours_mv.sql
- ${root}/ pro_rata_salary/ create_mat_view.sql
- ${root}/pro_rata_salary/coreint_interfaces_pkg.sql
- ${root}/pro_rata_salary/employee_check_pkg.sql
2.3 Application setup
n/a at this time.
2.4 Integration components
The required database links are listed in 2.1 and defined in the TAD. Oracle scheduled jobs and crontab alterations were created in section 2.2.
2.5 Data population
n/a at this time.
2.6 Data Migration
n/a at this time.
2.7 Implementation validation
2.7.1 Employee check interface
Log in as database user COREINT@EJOB[TEST|LIVE] and ensure that there is a job called “EmployeeCheckInterfaceRefresh” registered in table USER_SCHEDULER_JOBS.
Log in as database user COREINT@EJOB[TEST|LIVE] and run the following sql statements:
- SELECT 1 FROM dual@coreintappslink
- SELECT 1 FROM dual@coreintidmlink
Log in as database user COREINT@EJOB[TEST|LIVE] and execute the packaged procedure COREINT_EMPLOYEE_CHECK_PKG.DO_IMPORT. When that procedure finishes check the COREINT_LOGS table for the interface “Employee Check Interface Refresh”.
The interface API has a suite of unit tests that can be run by logging in as database user COREINT@EJOB[TEST|LIVE] and executing the PL/SQL anonymous block:
- ${root}/coreint_schema/employee_check_interface/coreint_interfaces_pkg_tests.sql
That same test script can be run as the database user COREHR@EJOB[TEST|LIVE] to prove that the API can be called by the CoreHR user.
2.7.2 Reference Data Interface
The initial_run.sql script that had to be executed as part of the implementation process (2.2.3.3) also validates this part of the implementation because it does an initial run of the extract.
Check that the run has completed successfully and generated files by logging in to UNIX as coreint and navigating to /u01/software/coreint/core_extract.
Also check the COREINT_LOGS table for details of what has run and any error messages.
3 Rollout steps
TODO.
[GC1]THIS BUILD WORK HAS YET TO BE DONE (SDS 3.4.2.3.3)
MICHAEL SUN IS CURRENTLY AMENDING THIS PACKAGE FOR DEI001 – THIS PACKAGE SHOULD BE READY FOR US TO WORK ON WEEK COMMENCING 23/04/2012.