System Processes

Pre-requisite Steps

Run the Archive Process

Before starting any work on DEV/TEST/LIVE, run the archive process to populate the archive tables with details of last year's return. It is imperative that this is done before starting any work as otherwise we risk losing the data. This should be added to the implementation plan as first step.  It has already been run in the dev environment.

Log into the HR application and navigate:

UOE HESA Administration > Submit Extract and Reports.

select EPP0106 Archive Latest Hesa Extract tables (2004 on) . Make sure number of copies to print is set to 0 , although I do not believe that it would try to output anything anyway.

Load of SHEF codes from finance

SHEF codes (Scottish Higher Education Funding Council codes) are held in the Finance system in the Finance Hesa Hierarchy. Finance are currently in the process of updating their system to reflect a revised list of HESA cost centre codes for this year.

Once this is done (it may not be done in the early stages of the build but we can work with the old version initially), ask Ann Finnan or Paul Ranaldi  in applications management team to run the ACTR05 report which produces a report of the Finance Hesa Hierarchy. The report name has been defined as HR Hesa Cost Centres : Parameters are Structure = 2      Level = N ( all levels)  

This report creates a .prt file on the Finance server. The file is in the format of a report so needs to be formatted before uploading to the HR system.  

Ask Anne/Paul to copy the file to whatever directory you require. Open file in excel. Order the file on the Postable column. Remove all rows where Postable is not = 'Y' . Remove all heading , total and blank rows. Remove all columns except shef_code, cost_code and description. The shef_code is normally in the format 'NNSHEF'. The first 2 digits of this code are what Hesa expects to be returned in CCENTRE1 , CCENTRE2 and CCENTRE3 columns.

In Dirleton/Girnigoe/Glamis HR unix application:

cd $UOE_TOP/uoe_cron/ctl/hesa

Sqlldr uoepp/password shefcode.ctl

There are some employees who are costed to 101000 cost centre but this does not appear in the hesa hierarchy. Susan in the HR team will add the SHEF codes via her admin screens after the extract has run.  If we see missing ccentre information during build and peer testing, then check the cost centre and if it is 101000 this is not an error.

Addition of New Fields to HR Application

The following new fields have to be created inside the HR application:

Person

• ACTCHQUAL1 -ACTCHQUAL6
• ACTLEAVE
• CLINARD
• LOCLEAVE

Contract

• CLINSUB
• HESA_LEVELS
• RESCON
• ACTSOC

ACTCHQUAL - Academic Teaching Qualification

A person can have up to 6 academic teaching qualifications. This field is optional for Scottish Institutions but HR have collected the information via staff survey so we should provide it in the return.

List of Values for ACTCHQUAL

Create a new list of values within the HR Application called VS_UOE_HESA_ACTCHQUAL using the codes and descriptions listed in the HESA field list and detail document

 http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

Create Person level Extra Information Type to store HESA Person Information

It is preferable to keep all the HESA information in one place as this makes it easier to locate. Some of the more sensitive information is already held in an equal opportunities Extra information type but it is proposed to create a new one for this field and any other new HESA fields that we may need in future. Document 70122.1 on Oracle Metalink provides further details on Extra Information Types and how they are configured within the application.

Create a new EIT called UOE_HESA_Personal against the Person Extra Information flexfield: 

 Add the following segments: 

Remember that before using, it is necessary to register the EIT by running the concurrent process as described in document 70122.1.

 ACTLEAVE - Activity After Leaving

This is very similar to the old LEDEST (destination on leaving) field and it was originally suggested that we could just use LEDEST field for this and amend the list of values.  LEDEST is held as a segment on the Additional Period of Service Details page.  On further investigation, it would be better to set LEDEST to not displayed and not enabled and then create a new segment for ACTLEAVE. This avoids confusion.

List of Values for ACTLEAVE

Create a new list of values within the HR Application called VS_UOE_HESA_ACTLEAVE using the codes and descriptions listed in the HESA field list and detail document

 http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

Create field in the Additional Period of Service Details flexfield in HR Application

Add a new segment to Additional Period of Service Details flexfield as noted below: 

CLINARD - Clinical Excellence Award

This is returned as an attribute of the person. It only applies to staff who hold clinical contracts and so will only apply to the UOE Enter Clinical Details responsibility. Since the existing UOE_HESA_INFORMATION EIT used by that responsibility is linked to the assignment, it will be necessary to create another one linked to the person record that we can then associate with the same responsibility.

 List of Values for CLINARD

Create a new list of values within the HR Application called VS_UOE_HESA_CLINARDusing the codes and descriptions listed in the HESA field list and detail document http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

Create a new EIT called UOE_HESA_CLINICAL_EIT against the Person Extra Information flexfield: 

 Add the following segments: 

Remember that before using, it is necessary to register the EIT by running the concurrent process and add the Person Extra Information type to the menu for UOE Enter Clinical Details responsibility (see Document 70122.1 on Oracle Metalink).

LOCLEAVE - Location After Leaving

This is recorded against the Person in the HESA return. It will be filled in when the person's employment is terminated and so will have to appear on the terminate employment screen.

List of Values for LOCLEAVE

Create a new list of values within the HR Application called VS_UOE_HESA_LOCLEAVE using the codes and descriptions listed in the HESA field list and detail document

 http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

Create field in the Additional Period of Service Details flexfield in HR Application

Add a new segment to Additional Period of Service Details flexfield as noted below: 

CLINSUB - Clinical Sub-Speciality

List of Values for CLINSUB

Create a new list of values within the HR Application called VS_UOE_HESA_CLINSUB using the codes and descriptions listed in the HESA field list and detail document http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

Add to the UOE_HESA_INFORMATION Assignment Extra Information Type

Create a new segment on UOE_HESA_INFORMATION EIT: 

 RESCON - Reason for End of Contract

This forms part of the contract details in the HESA return and so logically the field ought to be created against the assignment record in HR. However due to business processing requirements, we will have to store it both against the assignment and the person (behind the End Employment screen).

The reason for this is that for employees who have multiple assignments, the business process for ending either of the assignments is not the same as ending a one and only assignment, as in the latter case we are terminating their employment with the university and in the former they remain an employee.  The business process for ending a multiple assignment involves input of information to the assignment screen, but ending one and only assignments, i.e Terminations, is dealt with by department administrators via Manager Self Service and so they do not have access to the assignment screen to input the RESCON.

The logic inside the HESA extract would be to use the RESCON held against the assignment, else if not populated use that held against the person's termination details. This aspect is expanded on later in the document.

List of Values for RESCON

Create a new list of values within the HR Application called VS_UOE_HESA_RESCON using the codes and descriptions listed in the HESA field list and detail document

 http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

Create field in the Additional Period of Service Details flexfield in HR Application

Add a new segment to Additional Period of Service Details flexfield as noted below:

Create Assignment level Extra Information Type to store Non Clinical HESA information

As explained previously, due to the way in which multiple assignments are processed, we need to store the RESCON against the assignment as well as the period of service.  Since there are other pieces of information that will also have to be stored against the assignment, it would be tidier to group these together in an Extra Information Type than to use up any more of the limited number of assignment user defined fields.  Document 70122.1 on Oracle Metalink provides further details on Extra Information Types and how they are configured within the application.

There is already an Extra Information Type (EIT) called UOE_HESA_INFORMATION and at first glance it would seem sensible to add new fields in here. However, this EIT is used specifically by Medicine and Veterinary Medicine to store clinical information (using the UOE Enter Clinical Details responsibility). All the fields that are in there at the moment relate to clinical information. Therefore it is proposed to keep this separate and create a second EIT to store the non-clinical HESA information. This EIT will have a wider audience than the clinical one.

Create a new EIT called UOE_HESA_NON_CLINICAL against the Assignment Extra Information flexfield:

 Add the following segments:

Remember that before using, it is necessary to register the EIT by running the concurrent process as described in document 70122.1.

 ACTSOC - Activity Standard Occupational Classification

This forms part of the contract information and will be created on the Additional Assignment Details flexfield.

List of Values for ACTSOC

Create a new list of values within the HR Application called VS_UOE_HESA_ACTSOC using the codes and descriptions listed in the HESA field list and detail document. This field replaces the SOC_CODE field.

 http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

Create field in the Additional Assignment Details flexfield in HR Application

Add a new segment to Additional Assignment Details flexfield as noted below: 

 Disable the soc_code field

Un-check the displayed field for soc code in the Additional Assignment Details flexfield (ass_attribute24).

 Amendments to HESA extract tables

 Creation of new holding tables for REF related information,  LEVELS and CURACCDIS

HR does not hold research information, the golden copy for which is the PURE research management system. However there is a requirement to return some REF related information as part of the staff HESA return. Since HR has no requirement to store this information other than for the HESA return, rather than create an interface between the two systems we will set up a manual process to extract the required fields from PURE in csv format and load into the hesa tables via sql loader. This only has to happen once a year at the time of making the HESA return so is not a huge overhead.

We need to be careful here as we only want one row in the file per person.

It has been agreed that we will receive 2 separate files from PURE - one with ECRSTAT information, the other with RESAST. The files  will contain the employee_number so we will be able to use that to join to the correct record. Name the files ECRSTAT.dat and RESAST.dat and create a ctl file for each. Use Append option within sql loader so that we can load both files to the same holding table.

Create a new table inside the UOEPP schema called UOE_HESA_RESEARCH_INFO_TMP.

The table should have the following columns:

We need to create a similar table for storing HESA_LEVELS data that will be supplied by HR in csv format. This information is not stored in the core HR application.  HESA_LEVELS is an assignment related field so this file should contain the assignment_number instead of employee number.

Create a new table inside the UOEPP schema called UOE_HESA_LEVELS_TMP.

The table should have the following columns:

 Create a new table inside the UOEPP schema called UOE_HESA_CURACCDIS_TMP

We will receive a file of department_codes and corresponding curaccdis. Rename this curaccdis.dat and create ctl file. Use sqlldr to load details to the table.

Addition of new columns to HESA extract tables

 Increase size of CCENTRE1, CCENTRE2, CCENTRE3 column

The size of the HESA cost centres is going to be increased from 2 to 3 chars so we have to increase the size of:

• CCENTRE1
• CCENTRE2
• CCENTRE3

for the following tables:

• UOE_HESA_CONTRACT_DETAILS
• UOE_HESA_CONTRACT_DETS_ARCHIVE

Removal of redundant columns from HESA extract tables

  We are no longer required to return the following fields:

Drop obsolete extract tables

The following tables are now obsolete as we are no longer returning these items to HESA so they can be dropped. Any data will already have been stored in the archive table since the archive will be run before development starts. These are just working tables used in the extract process.

1. UOE_HESA_ADDITIONAL_POSITION
2. UOE_HESA_GRADE_DETAILS

Amendments to the PLSQL extract procedures

The PLSQL extract has to be amended to include new fields, to remove redundant ones and to change some logic.

Amendments to UOEPP.UOE_HESA_UTILS 

NEW FUNCTION for CLINSUB

Function get_hesa_clinsub(p_assignment_id in number) return uoe_hesa_contract_details.clinsub%type;

               SELECT NVL(paei.aei_information6, 'EEEE')                  FROM per_assignment_extra_info paei                 WHERE paei.information_type ='UOE_HESA_INFORMATION' 

               AND paei.assignment_id = p_assignment_id ;

Per_assignment_Extra_info is the table which stores details of the extra information type that we created inside the HR application.

The HESA specification is that this must be populated for all clinical staff.'EEEE' is not a valid value and will fail HESA validation. However setting it to EEEE where not populated inside HR will make it easier to run reports to identify problem records.

UOE_HESA_UTILS.GET_HESA_ACEMPFUN

 ACTSOC

This function currently takes soc_code as a parameter but we are now using the new ACTSOC values. The logic inside the function will have to be re-written to reflect this. This requires input from HR.

 ADDITIONAL CONTRACTS

Note that the undernoted code snippets refer to p_soc_code but as described above, this will also be changing!

1. Remove p_add_contract from the list of parameters.
2. Remove   'AND p_add_contract = 'N' from the following line : 'ELSIF p_soc_code = '2A' AND p_add_contract = 'N' THEN'.
3. Remove the whole section beginning  'ELSIF p_soc_code = '2A' THEN' as this relates to additional contracts and will no longer apply
4. Remove p_add_contract from the error log line.

REVISED LOGIC FOR GRADES

As per JIRA HRS071-8:

The line of code which reads  'IF p_soc_code = '2A' AND (p_pay_scale LIKE 'UE%' OR p_pay_scale = 'OTHS') THEN' has to be amended to include pay scales  beginning 'M1','RCB','XM' . 

The value of p_pay_scale being passed in is the grade name. There is only one 'M1' grade but several 'RCB' and 'XM' so add :

or p_pay_scale='M1' or p_pay_scale like 'RCB%' or p_pay_scale like 'XM%' inside the brackets.

UOE_HESA_UTILS.GET_HESA_TERMS

ADDITONAL CONTRACTS

1. Remove p_add_contract from the list of parameters
2. Remove the case statement that checks p_add_contract and always use the code that currently applies when it = 'N' - i.e the code that checks the assignments table in HR for the information rather than the uoe_hesa_additional_position table.
3. Remove p_add_contract from the error log line.

UOE_HESA_UTILS.GET_HESA_MOEMP

ADDITONAL CONTRACTS

1. Remove p_add_contract from the list of parameters
2. Remove the case statement that checks p_add_contract and always use the code that currently applies when it = 'N' - i.e the code that checks the assignments table in HR for the information rather than the uoe_hesa_additional_position table.

UOE_HESA_UTILS.GET_HESA_NHSCON

This function currently takes soc_code as a parameter but we are now using the new ACTSOC values. The logic inside the function will have to be re-written to reflect this. This requires input from HR.

UOE_HESA_UTILS.GET_HESA_NHSCONGR

This function currently takes soc_code as a parameter but we are now using the new ACTSOC values. The logic inside the function will have to be re-written to reflect this. This requires input from HR.

 UOE_HESA_UTILS.GET_HESA_HSPEC

This function currently takes soc_code as parameter but we are now using the new ACTSOC values. The logic inside the function will have to be re-written to reflect this. This requires input from HR.

 UOE_HESA_UTILS.CALC_HOURS_WORKED

1. Remove the parameter for additional contract
2. Remove CASE statement for additional contracts and use the query that is currently used for additional_contract = 'N' for all of them.

 Remove Redundant Functions

The following functions are no longer used and can be removed. They either relate to fields that we no longer return or to additional contracts (which we no longer use).

1. UOE_HESA_UTILS.GET_HESA_SOBS_TYP_ADD
2. UOE_HESA_UTILS.GET_HESA_SSOBS_TYP_MAIN
3. UOE_HESA_UTILS.GET_HESA_CCENTRE_ATYP_ADD
4. UOE_HESA_UTILS.GET_HESA_CCENTRE_TYP_ADD
5. UOE_HESA_UTILS.GET_ADDPOS_SCREEN_VALUE
6. UOE_HESA_UTILS.GET_HESA_LEDEST
7. UOE_HESA_UTILS.GET_GRADID
8. UOE_HESA_UTILS.GET_DEFAULT_SOC_CODE

Amendment to UOEPP.UOE_HESA_EXTRACT_API

Procedure STEP_THROUGH_EXTRACT

Remove the first step in the list 'GET_REFERENCE_DATA' as this is now redundant. Re-number all the other steps in the list starting from 1.

Procedure EXECUTE_STEP_BY_NAME

Remove the following function calls as these relate to additional positions and grade information which is no longer used:

1. Remove  entire step 'GET_REFERENCE_DATA'  
2. uoe_hesa_extract_steps.raw_ass_Create_typ_add (in steps GET_CURRENT_RAW_ASSIGNMENTS and GET_PREVIOUS_RAW_ASSIGNMENTS)
3. uoe_hesa_extract_steps.raw_ass_create_atyp_add (in steps GET_CURRENT_RAW_ASSIGNMENTS and GET_PREVIOUS_RAW_ASSIGNMENTS)
4. uoe_hesa_extract_steps.sum_ass_create_typ_add (in steps SUMMARISE_CURRENT_ASSIGNMENTS and SUMMARISE_PREVIOUS_ASSIGNMENTS)
5. uoe_hesa_extract_steps.sum_ass_create_atyp_add (in steps SUMMARISE_CURRENT_ASSIGNMENTS and SUMMARISE_PREVIOUS_ASSIGNMENTS)

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS

Remove the following functions:

1. get_additional_positions
2. get_grades  
3. uoe_hesa_extract_steps.raw_ass_create_typ_add
4. uoe_hesa_extract_steps.raw_ass_create_atyp_add
5. uoe_hesa_extract_steps.sum_ass_create_typ_add

 Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.SUM_ASS_CREATE_ATYP_CONT

ADD_CONTRACT

Remove this column from the select/insert statement.

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.SUM_ASS_CREATE_ATYP_NO_CONT

ADD_CONTRACT

Remove this column from the select/insert statement.

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.RAW_ASS_CREATE_TYP_MAIN

ACTSOC

Since ACTSOC is simply held against the assignment record in HR, we can retrieve it along with the other basic assignment information.

Add substr(actsoc(paf.ass_attribute6),1,3)   to both the insert statements.

SOC_CODE

Remove soc_code from both insert statements. This field has now been superceded by ACTSOC1.

PERIOD_OF_SERVICE_ID

We need to retrieve and store the period_of_service_id so that we can use it to find RESCON and LOCLEAVE later in the process.

Add period_of_service_id(paf.period_of_service_id) to both the insert statements.

ADD_CONTRACT

Remove all references to ADD_CONTRACT from the select/insert statement.

 Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.SUM_ASS_UPDATE_ALL

Amend the update statement that populates the organization hierarchy data ( UPDATE uoe_hesa_assignments_summary uhas         SET         (             uhas.department_name             ,uhas.school_name             ,uhas.college_name         ) =

 to add in update to uhas.department_code. Set it to be value of department_attribs.attribute1.

 Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.SUM_ASS_UPDATE_TYP

ADD_CONTRACT

Remove the entire update statement  for additional contracts (the one where add_contract='Y').

Amend the update statement where add_contract='N' to remove that join condition (i.e remove  condition add_contract='N').

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.SUM_ASS_UPDATE_ATYP

ADD_CONTRACT

Remove the updates to the add_contract field (NB there are 2 of them).

 Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.RAW_ASS_UPDATE_ATYP

 ADD_CONTRACT

Amend the call to UOE_HESA_UTILS.CALC_HOURS_WORKED so that it no longer passes ADD_CONTRACT as parameter

SOC_CODE

We no longer have to generate 'fake' soc_codes (or new ACTSOC codes) as all atypical staff should now have one in the system.

Remove the call to uoe_hesa_utils.get_default_soc_code.

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.SUM_ASS_CREATE_TYP_MAIN

ACTSOC1

Add ACTSOC1 to the insert statement.

SOC_CODE

Remove SOC_CODE from the insert statement.

PERIOD_OF_SERVICE_ID

Add PERIOD_OF_SERVICE_ID to the insert statement.

ADD_CONTRACT

Remove from the select list in the insert statement and from the join condition

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.HESA_CONT_CREATE_TYP

REMOVE REDUNDANT FIELDS

The following fields are no longer being returned and should be removed from the select/insert

1. ACT1
2. TCHWLH
3. GRADE
4. SMPH
5. ADD_CONTRACT (also amend case statements for the start and end dates which refer to this field)
6. PAY_SCALE
7. GRADID
8. PROF

ADD_CONTRACT

As well as removing add_contract from the list of items being selected, remove it from the following function calls:

1. UOE_HESA_UTILS.GET_HESA_ACEMPFUN
2. UOE_HESA_UTILS.GET_HESA_TERMS
3. UOE_HESA_UTILS.GET_HESA_MOEMP

ACTSOC1

Add ACTSOC1 to the list of columns in the insert statement and set this to the value of ACTSOC from the assignments summary table.

SOC_CODE

Update the following function calls (within the select/insert statement) to replace parameter uhas.soc_code with uhas.actsoc1:

• uoe_hesa_utils.get_hesa_acempfun
• uoe_hesa_utils.get_hesa_nhscon
• uoe_hesa_utils.get_hesa_nhscongr
• uoe_hesa_utils.get_hesa_hspec

Update the case statements for clinical and prof fields to whatever we decide is the equivalent of 'has_2a_soc_code'.

PERIOD_OF_SERVICE_ID

Add PERIOD_OF_SERVICE_ID to the insert statement.

CLINSUB

Add CLINSUB to the list of items being inserted to the uoe_hesa_contract_details table. This information is only required where the value of 'CLINICAL' is 1 so will need either a decode or a case statement to decide whether or not to call the function.

Where CLINICAL is not 1 return null.

   e.g case when uhas.clinical=1 then   

             uoe_hesa_utils.get_hesa_clinsub(uhas.assignment_id);

        else

             null;

      end CLINSUB

Note that there are no column aliases in the code as it stands but would recommend using for the new fields as it makes it easier to spot them in the code.

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.HESA_CONT_CREATE_ATYP

REMOVE REDUNDANT FIELDS

The following fields are no longer being returned and should be removed from the select/insert

1. ACT1
2. ACT2
3. ACT3
4. TCHWLH
5. GRADE
6. SMPH
7. SSOBS
8. ADD_CONTRACT
9. GRADID
10. PROF

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.HESA_CONT_UPDATE_TYP_SALSRC

SSOBS

Remove the update of SSOBS field (uhcd.ssobs = uoe_hesa_utils.get_hesa_ssobs_typ_main(person_id,assignment_id,assignment_number,summarised_endcon).

ADDITIONAL CONTRACTS

Remove all updates that only apply to additional contracts (i.e those with condition add_contract = 'Y')

Remove 'where add_contract' = 'N' from remaining statements.

 Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.HESA_CONT_UPDATE_TYP_CCENTRE

Replace references to ACT1,ACT2,ACT3 with ACTSOC1,ACTSOC2, ACTSOC3

ADDITIONAL CONTRACTS

We no longer have any additional contracts and the column is being removed from the underlying tables.

1. Remove the entire update statement for additional contracts (where add_contract='Y').
2. Remove the join condition where add_contract = 'N' from the other insert statement.

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.CONT_UPDATE_ATYP_CENTRE

ADDITIONAL CONTRACTS

We no longer have any additional contracts and the column is being removed from the underlying tables.

1. Remove the entire update statement for additional contracts (where add_contract='Y').
2. Remove the join condition where add_contract = 'N' from the other insert statement.

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.FIND_MISSING_CONTRACTS

REMOVE REDUNDANT FIELDS

The following fields are no longer being returned and should be removed from the select/insert

1. ACT1
2. ACT2
3. ACT3
4. TCHWLH
5. GRADE
6. SMPH
7. SSOBS
8. ADD_CONTRACT
9. PAY_SCALE
10. GRADID
11. PROF

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.FIND_PREVIOUS_CONTRACTS

ADD_CONTRACT

Remove from the select/insert statement.

 Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.HESA_PEOPLE_CREATE

ADD_CONTRACT

Remove add_Contract from the join condition beginning 'and uhas.row_id in...).

ECRSTAT

Default the ECRSTAT to '2' across the board and we will update for exceptions later on.

Add ECRSTAT to the insert statement.

Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.CLEAROUT

ADD_CONTRACT

Remove references to add_contract which appear throughout the procedure, including references to l_binds('add_contract') and any joins that use it.

Remove the step 'ADDITIONAL_POSITIONS' from the case statement as we no longer need to clear down the additional positions table.

GRADE

Remove the step 'GRADE DETAILS' from the case statement as we are no longer  returning grade details and this table will not be in use.

 Amendments to UOEPP.UOE_HESA_EXTRACT_STEPS.HESA_PEOPLE_UPDATE_BASIC

MISCELLANEOUS UPDATES

1)has_rescon_8

update uoe_hesa_person_details pers

set has_rescon_8 =( select count(*)

                              from uoe_hesa_contract_Details cont

                              where rescon='8'                             

                             and pers.person_id = cont.person_id);

2)Period_of_service_id

update uoe_hesa_person_details pers

set pers.period_of_service_id=(select cont.period_of_service_id

                                                  from uoe_hesa_contract_details cont

                                                  where cont.person_id=pers.person_id

                                                 and cont.start_con=(select max cont2.start_con

                                                                                      from uoe_hesa_contract_details cont2

                                                                                      where cont2.person_id=cont.person_id);

3) has_actsoc_123

update uoe_hesa_person_details pers

set has_actsoc_123 = (select count(*)

                                    from uoe_hesa_contract_details cont

                                   where substr(actsoc,1,1) in ('1','2','3')

                                  and cont.person_id=pers.person_id);

4) has_clinical_1

update uoe_hesa_person_details pers

set has_clinical_contract =( select count(*)

                                            from uoe_hesa_contract_details cont

                                           where  cont.clinical ='1'

                                           and cont.person_id=pers.person_id );      

REGBODY

The update statement that sets up REGBODY currently does so on the basis of the values of ACT1,ACT2 and ACT3 

This will have to be amended as ACT1,ACT2 and ACT3 have now been superceded by ACTSOC1, ACTSOC2 and ACTSOC3

HR are providing a note of the combinations of ACTSOC values which would correspond to the old '2a' . The update statement will have to be amended accordingly.

REF RELATED INFORMATION 

Update the person record with details from the UOE_HESA_RESEARCH_INFO_TMP table.

We have to update RESASTand ECRSTAT, as per undernoted example.

ECRSTAT 

Applies to all staff where any (Contract.ACEMPFUN is coded 2 or 3 and Contract.TERMS is coded 1 or 2).

update uoe_hesa_person_details pers

set pers.ecrstat= (Select tmp.ecrstat

                                   from uoe_hesa_research_info_Tmp tmp

                                   where tmp.employee_number= pers.employee_number)   

where exists (select 1

                     from uoe_hesa_contract_details cont

                     where cont.person_id=pers.person_id

                     and cont.acempfun in ('2','3')

                     and cont.terms in ('1','2'));      

RESAST 

Applies to all staff where any (Contract.ACEMPFUN is coded 2 or 3 and Contract.TERMS is coded 1 or 2).

update uoe_hesa_person_details pers

set pers.resast= (Select tmp.resast

                                   from uoe_hesa_research_info_Tmp tmp

                                   where tmp.employee_number= pers.employee_number)   

where exists (select 1

                     from uoe_hesa_contract_details cont

                     where cont.person_id=pers.person_id

                     and cont.acempfun in ('2','3')

                     and cont.terms in ('1','2'));      

CURACCDIS1 and CURACCDIS2

Applies to all staff where any (Contract.ACEMPFUN is coded 1, 2 or 3 and Contract.TERMS is coded 1 or 2).

We are going to set up Curaccdis1 based on the department in which the employee is working. HR will provide mapping between orgs and curaccdis codes and we will load them into temp table UOE_HESA_CURACCDIS_TMP (see earlier section). We haev decided not to populate CURACCDIS2 just now but leave the field there.

We will use the organization from their primary assignment.

update uoe_hesa_person_details pers

set pers.curaccdis1=(Select tmp.curaccdis

                                   from uoe_hesa_curaccdis_Tmp tmp

                                         ,uoe_hesa_assignments_summary assign

                                   where assign.person_id=tmp.person_id

                                  and assign.primary_flag='Y'

                                  and assign.person_id=pers.person_id)   

where exists (select 1

                     from uoe_hesa_contract_details cont

                     where cont.person_id=pers.person_id

                     and cont.acempfun in ('1','2','3')

                     and cont.terms in ('1','2'));          

 ETHNIC

We have to map White British and White Irish to 'Other White Background'.

There is already a case statement in the select:

,CASE ppf.per_information1                         WHEN '10' THEN '98'                         ELSE NVL(ppf.per_information1, '98')                         END

Add another case when '11' or '12' then '19'                        

ACTCHQUAL 

This field is optional for institutions in Scotland where any (Contract.ACEMPFUN is coded 1 or 3 and Contract.TERMS is coded 1 or 2).

Add  new updates to hesa_people_update_basic to update ACTCHQUAL. If the UOEPP user does not already have select privilege on the per_people_extra_info table, this can be granted by the APPS user.

update uoe_hesa_person_details pers

set pers.actchqual1=(    SELECT nvl(pei_information||qual_number,'90')                                       FROM per_people_Extra_info

                                   WHERE.information_type ='UOE_HESA_PERSONAL' 

                                   AND person_id=pers.person_id)

where exists (select 1

                     from uoe_hesa_contract_details cont

                     where cont.person_id=pers.person_id

                     and cont.acempfun in ('1','3')

                     and cont.terms in ('1','2'));

Repeat the above for actchqual2 - actchqual6.

Per_people_Extra_info is the table which stores details of the extra information type that we created inside the HR application.

Actchqual is an optional field but it makes sense to default to '90' Not known if missing.

ACTLEAVE

Applies to all staff where any (Activity.ACTSOC is in SOC2010 Major Groups 1, 2 or 3 and Person.DATELEFT is not null and Contract.TERMS is coded 1 or 2 and no Contract.RESCON is coded 8).

update uoe_hesa_person_details pers

set pers.actleave= (Select nvl(attribute5,'90')

                                from per_periods_of_Service

                                 where period_of_service_id = pers.period_of_service_id)

where pers.dateleft is not null

and pers.has_rescon_8 = 0;

and exists (select 1

                     from uoe_hesa_contract_details cont

                     where cont.person_id=pers.person_id

                     and substr(cont.actsoc1,1,1) in ('1','2','3')

                     and cont.terms in ('1','2'));

In the above query, we are returning value of '90' (not known) instead of null as this is a required field for people who meet the criteria. Leaving null for these people would fail validation.

CLINARD

This only applies to people who have a contract with clinical = '1'

update uoe_hesa_person_details pers

set pers.clinard=(SELECT nvl(pei_information1,'EE')

                   FROM per_people_extra_info

                     WHERE information_type ='UOE_HESA_CLINICAL' 

                     AND person_id = pers.person_id )

where pers.has_clinical_1 > 0;

This is a required field where CLINICAL = '1'. 'EE' is not a valid value but its presence will aid in initial validation checks.

 LOCLEAVE

We only return this where

1. DATELEFT is not null
2. AND the person has a contract with activity.actsoc which begins 1,2 or 3  
3. AND none of the person's contracts has RESCON of 8

update uoe_hesa_person_details pers

set pers.locleave= (Select nvl(attribute5,8)

                                from per_periods_of_Service

                                 where period_of_service_id = pers.period_of_service_id)

where pers.dateleft is not null

and pers.has_actsoc_123 >0

and pers.has_rescon_8 = 0;

In the above query, we are returning value of '8' (not known) instead of null as this is a required field for people who meet the criteria. Leaving null for these people would fail validation.

 DISABLE

This definition of this field has changed. Also note that the name of the column on the uoe_hesa_person_details table is DISABLED and not DISABLE but this will be left as is to save work. We currently also use DISABLE1 and DISABLE2. These are now redundant in hesa terms. We do still hold 2 disability fields inside the HR application - attribute28 and attribute29 on per_all_people_f.

The new requirement is that where there is a value held in both disability1 and 2, set disable to '08' (more than one disability) else use the value in disability 1. Disability 2 will never be populated when disability 1 is null

In the insert statement, instead of setting DISABLED to nvl(ppf.attribute28,'97') as it does currently, amend to something like:

case when ppf.attribute29 is null then

   nvl(ppf.attribute28,'97')

else

   '08'

end disable

We will return '97' Information Refused when both fields null as this is a required field.

Remove the entries for disable1 and disable2 from the insert statement.

GENDERID

GENDERID is held in the Equal Opportunities Extra Information Type in the HR application.

It is in column pei_information3.

This field is optional so no need to default where null.

update uoe_hesa_person_details pers

set pers.genderid=(SELECT pei_information3

                              FROM per_people_extra_info

                               WHERE information_type ='UOE_EQUAL_OPPS_EIT'

                               AND person_id = pers.person_id );

RELBLF

RELBLF is held in the Equal Opportunities Extra Information Type in the HR application.

It is in column pei_information1.

This field is optional so no need to default where null.

update uoe_hesa_person_details pers

set pers.relblf=(SELECT pei_information1

                              FROM per_people_extra_info

                               WHERE information_type ='UOE_EQUAL_OPPS_EIT'

                               AND person_id = pers.person_id );

SEXORT

SEXORT is held in the Equal Opportunities Extra Information Type in the HR application.

It is in column pei_information2.

This field is optional so no need to default where null.

update uoe_hesa_person_details pers

set pers.sexort=(SELECT pei_information2

                              FROM per_people_extra_info

                               WHERE information_type ='UOE_EQUAL_OPPS_EIT'

                               AND person_id = pers.person_id );

Add new procedure UOE_HESA_EXTRACT_STEPS.HESA_CONT_UPDATE

This procedure will carry out  miscallaneous updates that depend on some of the values derived as part of the initial insert.

Update UOE_HESA_EXTRACT_API.EXECUTE_STEP_BY_NAME to that it calls HESA_CONT_UPDATE  directly after uoe_hesa_extract_steps.hesa_cont_create_typ

HESA_LEVELS

Applies to all contracts where (Contract.TERMS is coded 1 or 2) or (Contract.ACEMPFUN is coded 1, 2, 3 or 9 and Contract.TERMS is coded 3).

We do not yet have the full requirement for this one as HR having to give a bit more thought. However it will be stored against the assignment and we  have enough information to provide broad outline of code changes.

Create a new function inside UOE_HESA_UTILS called get_hesa_levels

This function should take the assignment_id as a parameter and return varchar2.

Hesa_levels will be set depending on combination of the grade and job_segment3 associated with that assignment. Note that the logic from HR refers to the secong job segment but to complicate matters this is actually stored in segment 3 on the database!

select pjd.segment3        ,pg.name        from per_grades pg        ,per_job_definitions pjd        ,per_jobs pj        ,per_all_assignments_f paf where paf.assignment_id=p_assignment_id

and pj.job_id=paf.job_id and pjd.job_definition_id=pj.job_definition_id and pg.grade_id=paf.grade_id and trunc (sysdate) between paf.effective_start_date and paf.effective_end_Date

Add HESA_LEVELS to HESA_CONT_UPDATE procedure

update uoe_hesa_contract_details cont

set cont.hesa_levels= get_hesa_levels(p_assignment_id)

where cont.terms in ('1','2')

OR (cont.terms='3' and cont.acempfun in '1','2','3','9');

This will set a default value for HESA_LEVELS  but we will want to overwrite this for anyone who has an entry in the UOE_HESA_LEVELS table. This is the table we have loaded with csv details provided by HR.

update uoe_hesa_contract_details cont

set cont.HESA_LEVELS= (select hesa_levels

                      from uoe_hesa_levels_tmp tmp

                      where cont.assignment_number=tmp.assignment_number));

RESCON

Add RESCON to the list of items being updated.

We are only updating where ENDCON is not null.

We have to check for the existence of rescon against the assignment record and if not found, check for one against the period of service record.

-first check the assignment:

update uoe_hesa_contract_details cont

set cont.rescon=(SELECT paei.aei_information1                             FROM per_assignment_extra_info paei                            WHERE paei.information_type ='UOE_HESA_NON_CLINICAL'  AND paei.assignment_id = cont.assignment_id )

 where cont.endcon is not null;

--then check the period_of_service for any that are still null

update uoe_hesa_contract_details cont

set cont.rescon=( SELECT nvl(attribute4,'E')          

                            FROM per_periods_of_service   ppos

                            WHERE period_of_service_id =cont.period_of_service_id;

where cont.endcon is not null

and cont.rescon is null;

If nothing found we are setting it to 'E'. This is not a valid value but can be used during the checking and validation process to show that we have attempted to retrieve RESCON for this assignment but failed.

 Amendments to the Archive Process

The HESA archive process is run via the HR application, Concurrent Program EPP0106. This calls a sql script of the same name.

INSERT to UOE_HESA_PERSON_DETS_ARCHIVE

Add the following new fields to the insert statement:

1. ACTCHQUAL1
2. ACTCHQUAL2
3. ACTCHQUAL3
4. ACTCHQUAL4
5. ACTCHQUAL5
6. ACTCHQUAL6
7. CURACCDIS1
8. CURACCDIS2
9. ECRSTAT
10. GENDERID
11. LOCLEAVE
12. REFUOA2014
13. RELBLF
14. SEXORT

Remove the following fields:

1. ACCDIS1
2. ACCDIS2
3. ABLWELSH
4. LEDEST
5. UOA
6. DISABLE2
7. DISABLE1   
8. TOTSAL
9. GRADE_NAME
10. RESACT

Also amend the structure of the statement so that it inserts into named columns rather than just having the order implied by the order of the select statment. The archive table still has the superceded columns in it (since they have been populated for previous years) so it will make it easier to follow if the columns are explicitly listed in the insert.

INSERT  TO UOE_HESA_CONTRACT_DETS_ARCHIVE

Add the following fields to the insert statement:

1. CLINSUB
2. HESA_LEVELS
3. RESCON
4. ACTSOC1
5. ACTSOC2
6. ACTSOC3

Remove the following fields:

1. ACT1
2. ACT2
3. ACT3
4. TCHWLCH
5. GRADE
6. SMPH
7. SSOBS
8. ADD_CONTRACT
9. PAY_SCALE
10. GRADID
11. PROF

As above, amend the insert to use named columns rather than relying on the order of the select statement.

GRADE RELATED INFORMATION

The insert to the HESA grade_dets_archive can be remove, as can deletion from the uoe_hesa_grade_details table as this no longer exists.

 Amendments to HESA BUSINESS OBJECTS (BOXI) UNIVERSES

Before starting work, please ask applications management team to extract the universes from live.  Delete the ones that already exist on dev (or test) and import the live versions.

There are 2 BOXI universerses - one for viewing details of the current extract and one for the archive tables.  These will have to be updated to reflect the database changes to the core extract tables.

HR HESA UNIVERSE

The universe name is HESA HR  and it is stored in universe folder:

/Human Resources/HESA

Connect to the dev boxi server, import the universe and refresh the structure so that the universe picks up the changes to the UOE_HESA_PERSON_DETAILS and UOE_HESA_CONTRACT_DETAILS tables.

The UOE_HESA_GRADE_DETAILS table is no longer required and has been dropped.

The following objects have to be added to the universe:

The following columns no longer exist in the underlying tables so related objects should be removed:

The grade details table has been dropped so the undernoted objects should be removed:

HR HESA  ARCHIVE UNIVERSE

Before starting work, please ask applications management team to extract the universes from live.  Delete the ones that already exist on dev (or test) and import the live versions.

The name of this universe is HESAARCH.

It can be found in folder /Human Resources/HESA

Connect to the BOXI server and import the universe. Refresh the universe structure to ensure that it picks up the new columns on UOE_HESA_PERSON_DETS_ARCHIVE and UOE_HESA_CONTRACT_DETS_ARCHIVE.

Note that the columns which have been dropped from the current extract tables have not been dropped from the archive tables as they are populated for previous extracts and so have to be available for reporting. This also applies to the UOE_HESA_GRADE_DETS_ARCHIVE table. This table will remain in place but have no entries for exract periods from this year onwards.

PPIP NOTE: 

The HRMI / PPIP requirements (below) were developed during February, but were then removed from project scope by the Business Area Manger on 21-MAR-2013.  The database and code changes were therefore dropped.

AMENDMENTS to HRMI BOXI UNIVERSE (PPIPMI)

Before starting work, please ask applications management team to extract the universe from live.  Delete the one that already exists on dev (or test) and import the live version.

In addition to the 2 universes that exist specifically for HESA, HR has its own BOXI universe for general reporting purposes. The business name for this is HRMI but technical documentation and database objects still refer to it as PPIPMI (PPIP being the name of the project that created the universe in the first place). It has always been deemed to big a job to rename all the underlying objects and scripts.

We are now required to add  the following HR fields to the HRMI universe:

1. Gender Identity
2. Disability1
3. Disability2
4. Sexual Orientation
5. Religion or belief

There are two parts to the HRMI extract - a daily and monthly extract. The process is broadly the same for both and they use several scripts in common. The main difference is that the monthly extract is copied to different destination tables and the history is maintained, whereas the daily tables are just over-written.  The extracts are controlled by cron jobs which call a series of sql scripts. There is another cron that runs every quarter to update reference data tables.

The extract runs on the UOEPP schema in HR and populates holding tables on HR before transferring across to the datamart tables which are in the PPIPMI schema on NEWSDEV/TEST/LIVE. This insert is done via database link.

We will therefore be adding columns to tables in  UOEPP and PPIPMI schemas.

Reference Data Tables

All of the above fields use lists of values to match codes to descriptions and so we will have to create the relevant look up tables in the datamart.

To keep the format and naming conventions consistent with the current set up, create tables as follows:

Create 4 tables:

1. PPIPMI_GENDER_IDENTITY
2. PPIPMI_DISABILITY
3. PPIPMI_SEXUAL_ORIENTATION
4. PPIPMI_RELIGION_BELIEF

Note that there is already a table called PPIPMI_DISABILITY_TYPES - this relates to a another disability field that stores slightly different information and should be left as is. Our new fields Disability1 and Disability2 both have the same codes associated with them so we only need one look up table.

Create these tables as UOEPP on HRDEV and PPIPMI on NEWSDEV

All four should have the undernoted columns:

Create public synonyms for each table and grant select on the UOEPP ones to APPS.

Grants

The PPIPMILNK user on NEWSDEV has to be granted SELECT, UPDATE, INSERT, DELETE on the tables on PPIPMI schema. The database link between UOEPP and PPIPMI connects using this username. The current tables all use direct grants rather than via role so we will do the same here.

On HRDEV, grant select, update, insert, delete on these tables to hr10_cron and UOEPP_ADMIN. We may also have to grant to OPS$APPHR if this does not work (I have had difficulty getting to the bottom of the grants set up for this as it seems to vary from table to table). Grant select to UOEPP_BROWSER and DSG_UOEPP_BROWSER.

New SQL scripts

PPIPMI_GENDER_IDENTITY

Create a new sql script called ppipmi_gender_identity.sql.

This should be in similar format to existing scripts (see ppipmi_nhsjoint.sql for an example). It should:

1. Delete from ppipmi_gender_identity
2. insert new rows
3. output a count of number of rows inserted

Within HR, the gender identity field is linked to list of values VS_UOE_YES_NO_NOT_SAY (i.e the choice is Yes, No or Prefer not to Say).

The code to retrieve the values will be:

SELECT value.flex_value      , value_tl.description      , value.enabled_flag  FROM fnd_flex_value_sets value_set      , fnd_flex_values value      , fnd_flex_values_tl value_tl  WHERE value_set.flex_value_set_name ='VS_UOE_YES_NO_NOT_SAY'    AND value.flex_value_id = value_tl.flex_value_id    AND value.flex_value_set_id = value_set.flex_value_set_id;

PPIPMI_DISABILITY

As above:

Create a new script called ppipmi_disability.sql.

The code to retrieve the values will be:

SELECT value.flex_value      , value_tl.description      , value.enabled_flag  FROM fnd_flex_value_sets value_set      , fnd_flex_values value      , fnd_flex_values_tl value_tl  WHERE value_set.flex_value_set_name ='VS_UOE_HESA_Disability_Types'    AND value.flex_value_id = value_tl.flex_value_id    AND value.flex_value_set_id = value_set.flex_value_set_id;

PPIPMI_SEXUAL_ORIENTATION

As above:

Create a new script called ppipmi_sexual_orientation.sql

The code to retrieve the values will be:

SELECT value.flex_value      , value_tl.description      , value.enabled_flag FROM fnd_flex_value_sets value_set      , fnd_flex_values value      , fnd_flex_values_tl value_tl  WHERE value_set.flex_value_set_name ='VS_UOE_ORIENTATION'    AND value.flex_value_id = value_tl.flex_value_id    AND value.flex_value_set_id = value_set.flex_value_set_id;

PPIPMI_RELIGION_BELIEF

As above:

Create a new script called ppipmi_religion_belief.sql

The code to retrieve the values will be:

SELECT value.flex_value      , value_tl.description      , value.enabled_flag      FROM fnd_flex_value_sets value_set      , fnd_flex_values value      , fnd_flex_values_tl value_tl  WHERE value_set.flex_value_set_name ='VS_UOE_RELIGION'    AND value.flex_value_id = value_tl.flex_value_id    AND value.flex_value_set_id = value_set.flex_value_set_id;

Amendment to Existing SQL Scripts

Add the following to  build_ppipmi_lookups.sql:

@@ppipmi_gender_identity.sql

@@ppipmi_disability.sql

@@ppipmi_sexual_orientation.sql

@@ppipmi_religion_belief.sql

Add the following to ppipmi_transfer_datamart.sql:

Following the pattern already in use in the sql script, add delete and insert statements for:

1. ppipmi_gender_identity
2. ppipmi_disability
3. ppipmi_sexual_orientation
4. ppipmi_religion_belief

Add New Columns to PPIPMI Extract tables

 Note in the undernoted table, objects in the ppipmi schema are on NEWSDEV and uoepp are HRDEV.

 Amendments to the Extract Scripts

uoe_mi_cur_assign_temp1_01.sql

Add update statements as follows:

Gender Identity, Religion or Belief and Sexual Orientation are all held in the same Extra Information Type inside HR so can be done in single update:

UPDATE  uoe_mi_cur_assign_temp1 assign SET (genderid

        ,relblf

        ,sexort  ) = (select pei_information2                                     ,pei_information1

                                    ,pei_information3                            from per_people_extra_info pei                            where pei.person_id = assign.assign_id                             and pei.information_type = 'UOE_EQUAL_OPPS_EIT');

Now update the disability_codes:

update uoe_mi_cur_assign

   set disability1 = (select ppf.attribute28

                             from per_all_people_f ppf

                            where   ppf.person_id=assign.person_id

                             and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date);

update uoe_mi_cur_assign

   set disability1 = (select ppf.attribute29

                             from per_all_people_f ppf

                            where   ppf.person_id=assign.person_id

                             and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date);

uoe_mi_cur_assign.sql

This script has a rather complicated insert statement with nested queries. However, all that has to be done is to add our new column headings into the list at the top of the insert statement.

So - add genderid, relblf, sexort, disability1 and disability2 IN THE EXACT SAME ORDER IN WHICH THEY APPEAR IN THE UOE_MI_CUR_ASSIGN_TEMP1 table.  They have to be in the exact order since it does a select *.

Also, due to the way in which the insert is constructed, we have to insert them in a specific location, this being between the entries for  'line_manager' and 'step_id'.

ppipmi_cur_assign_funding_01.sql

Add genderid, relblf,sexort,disability1 and disability2 to the insert statement (pick up the values from the relevant fields on the uoe_mi_cur_assign table)    

ppipmi_transfer_newlive_daily_01.sql

First point to note is that 'newlive' above is not a typo. This is the name of the script although it transfers to NEWSLIVE (or dev or test).

Add genderid, relblf,sexort,disability1 and disability2 to the insert statement for the ppipmi_assign_funding_daily table.

ppipmi_transfer_newslive_01.sql

Add genderid, relblf,sexort,disability1 and disability2 to the insert statement for the ppipmi_assign_funding table.

 Amendments to HR BUSINESS OBJECTS (BOXI) UNIVERSE

The universe name is  HR  and it is stored in universe folder:

/Human Resources/ 

Connect to the dev boxi server, import the universe and refresh the structure so that the universe picks up the changes to the underlying tables.

Add the new look up tables (ppipmi_disability,ppipmi_sexual_orientation,ppipmi_religion_belief,ppipmi_gender_identity) to the universe structure.

Create relationships between the flex_value on the tables and the corresponding columns on the ppipmi_assign_funding and ppipmi_assign_funding_daily tables.

The following objects have to be added to the universe:

 Security Groups

These fields are considered to be very sensitive and as such should only be viewable inside BOXI by the HR Systems team. No-one outside that group should have access to these fields. The security can be modelled on that used for the gender and ethnic origin fields. 

 Addition of New Fields to Manager Self Service Termination Screens

The following fields in the addition period of service details flexfield have to be added to the termination screens:

1. RESCON
2. LOCLEAVE
3. ACTLEAVE

The Leaving Reason field should be removed.

First of all it is necessary to be set up in HR as someone's supervisor so that can access the screens we need to see. This is done by retrieving their HR record and then selecting the assignment tab and then supervisor. Choose your own name from the list and save.

Then you have to have to set yourself up with the UOE Manager Self Service (including Sickness Absence) responsibility. Do this as System Administrator > Security> User> Define. Query up your user name and click in the bottom half of the screen and then the plus button. Select the responsibility from the list and when done click the save icon.

Once this is done, have to set the System Profile value for 'Personalize self service definition' to Yes. (Navigate System Administrator> Profile > System.  This then brings up links on the self service pages allowing you to customise them.

The page we are amending is:

/oracle/apps/per/selfservice/termination/webui/TerminationPG

The easiest way to get to it is to navigate via the application screens.

select the UOE Manager Self Service (incl Sickness Absence ) responsibility.

Select one of the employees in your tree and click to edit.

Select termination from the available actions and hit Start Button.

Ignore the first screen - just click continue.

The next screen is the one we want.

Click on the Personalize Page link at the top right of the page.

Find the entry for

Choose the one that has Yes in the Shown field - for some reason it appears twice in the page.

Click on the pencil icon to edit:

 Find the entry for Segment list near the bottom. You will see that there is already an entry for this at responsibility level.

Delete what is there and set to:

Global Data Elements|Final Pay Adjustment|Hesa Activity After Leaving|Hesa Location After Leaving|Hesa Reason for End of Contract

Note that the original entry for Hesa Destination on Leaving has to be removed.

It is important that these entries match the window prompts set up within the Additional Period of Service Details Flexfield.

Click apply and then return to application. The new fields should appear.

 Creation of XML File

In previous years we have had to return 3 csv files to HESA - one for person details, one for contract and one for grades but these are being replaced with a single file in xml format. This part of the process is controlled by HR, who run a concurrent process via the HR application when they want to generate files. There are 3 processes, one for each file and each calls a plsql stored procedure to generate output which is written to the standard HR output directory and viewable via the application. HR can then view the output and save to directory on local machine or on the network before submitting to HESA. It is proposed to use the same method for generation of the XML file.

Create new concurrent process definition inside HR application

Navigate System Administrator > Concurrent Program > Execatable

Define a new executable as follows:

Executable: EPP4232 - Generate xml file for HESA

Short Name: EPP4232

Application: UOE Bespoke

Description: Produces an xml file containing person and contract details for the HESA Staff Return

Execution Method: PL/SQL Stored Procedure

Execution File Name: EPP4232

Note that EPP4232 is the next available sequence number in the list of executable names. This list is maintained by Applications Management, who have advised that this is the number to use.

Navigate System Administrator > Concurrent Program > Define

Define a new concurrent program as follows:

Name: EPP4232 - Generate xml file for HESA

Short Name: EPP4232

Application: UOE Bespoke

Description: Produces an xml file containing person and contract details for the HESA Staff Return

Executable Name: EPP4232

Method: PL/SQL Stored Procedure

Set output format to 'Text'.

Make sure the 'save' box is checked and the print box is not.

Accept other defaults and double check that the 'enabled' box is ticked.

Create new stored procedure EPP4232

Create a stored procedure in UOEPP schema in HRDEV.

The procedure must have 2 parameters - retcode out NUMBER and errbuf out VARCHAR2.

On succesful completion, set retcode to 0.

On failure, set retcode to 1 and set errbuf to the value of the error message.

These parameters must be exactly as specified  as this is required for them to work with the HR concurrent manager. The existing procedures EPP0115 /EPP0116/EPP0117 can be used as examples.

This procedure should loop through the uoe_hesa_person_details file and generate an entry in the xml file for each person. This entry will also include contract details.

Data should be written out using fnd_file (again this is a requirement for the concurrent manager and correct usage can be found in EPP0115/116/117).

Create a public synonym for the procedure and make sure that  the APPS user is granted EXECUTE privilege.

The definition of the xml file is on the HESA web site at:

http://www.hesa.ac.uk/index.php?option=com_studrec&Itemid=232&mnl=12025

but in summary, the general structure of the file is:

<?xml version="1.0" encoding="UTF-8"?> <institution>     <RECID/>     <UKPRN/>    <person>       <STAFFID/>       <OWNSTAFFID/>       <ACTCHQUAL/>       <ACTLEAVE/>       <BIRTHDTE/>       <CLINARD/>       <CURACCDIS/>       <DATEFHEI/>       <DATELEFT/>       <DISABLE/>       <ECRSTAT/>       <ETHNIC/>       <GENDERID/>       <HQHELD/>       <LOCLEAVE/>       <NATION/>       <NATIOND/>       <PREVEMP/>       <PREVHEI/>       <REGBODY/>       <RELBLF/>       <RESAST/>       <SEX/>       <SEXORT/>      <CONTRACT>           <CONTID/>          <OWNCONTID/>          <ACEMPFUN/>          <CAMPID/>          <CLINICAL/>          <CLINSUB/>          <CONFTE/>         <ENDCON/>         <HEIJOINT/>        <HSPEC/>        <LEVELS/>        <MOEMP/>        <NHSCON/>        <NHSCONGR/>        <PSCAG/>        <RESCON/>        <SALREF/>        <SOBS/>        <SPOINT/>        <STARTCON/>         <TERMS/>         <ACTIVITY>              <ACTSOC/>             <CCENTRE/>             <CCPROP/>         </ACTIVITY>      </CONTRACT>   </person> </institution>

There are some elements defined with min occurs = '0'. In these cases, if the underlying database column is null then that element can be missed from the file. Some can occur more than once in which case they will have max occurs > 1 or unbounded specified in the xsd.  These will tend be fields such as ACTCHQUAL for which we have 6 columns in the underlying tables. If only one of the fields is populated then only one entry should be made in the file for that element. Elements must appear in the order specified in the xsd.

 Amendments to Oracle Forms

The UOE Hesa Administration responsibility has menu options View/Update Current Hesa Extract and View Archive Hesa Extract. These options are linked to custom Oracle forms (fmb files) that were developed in-house. HR use the View/Update option to view the contents of the HESA extract tables UOE_HESA_PERSON_DETAILS and UOE_HESA_CONTRACT_DETAILS. They have the ability to update the values in the table via the form and also to amend the HESA_RETURNABLE flag so that they have full control of what gets included in the final version of the file. This allows them to cater for anomalies. The View Archive option, as the name suggests, allows them to view the contents of the archive tables.

Addition of new fields to the HESA extract necessitates forms work to add them to the fmbs. There is limited forms experience in the department and reluctance to go down the route of forms training as Oracle HR is moving away from forms in the longer term. Therefore it is proposed to replicate the current forms using Oracle APEX. We have implemented APEX for some existing HR reports and for the transport loans application and this is a supported method of extending the HR application. The slight downside at the moment is that although we have same sign on (so users can use HR credentials for APEX) we do not have single sign on yet. This means requirement to sign in twice - once on opening HR and then again when click on the menu option to call APEX. 

Person Details Form (Current Extract)

When there is data in the table the form will be populated on load with details of the first record, but it is also possible to query on Employee Number by using F11 key (which puts the form into query mode).

This form displays the person details from the UOE_HESA_PERSON_DETAILS table. HR can use it to view and update any of the fields on the underlying tables.

To display contract details the users then select the Contract Details button and this navigates to the following form:

 This will have one or more contracts per employee.

We need to replicate these forms using APEX. There is a utility available to convert fmb forms to APEX. It generates xml with the form definitions which can then be loaded into APEX. I have generated these XML files as part of the investigative process and they are available if required. However it is proposed that we start afresh for this development as this might be simpler and cleaner. Not all forms functions translate cleanly to apex and importing may well take as long as starting from scratch.

Initial investigation suggests that we might create a report and then an underlying master/detail form. The first page would list the people in the table and have an edit link for each. On clicking Edit the user would be taken to a second page which would be split in two. The top part would have the person details and the bottom section would display the contract details in tabular form.

Regardless of the final layout, the APEX form(s) should include the fields shown below. It may be useful to group them in the layout  - for example have different sections for the equal opps information such as gender,  gender id and sexual orientation, or perhaps for the research related fields that are coming from the PURE system such as ECRSTAT and RESAST, or fields storing 'leavers' information. However that is not a requirement that is set in stone, the main thing is that they have to appear somewhere on the screen:

Rather than use the long form of the field names, use the abbreviated ones as HR are familiar with them.

Person Details - Current Extract

As can be seen the main issue with the layout is going to be the number of fields that have to be displayed.   

 Contract and Activity Details - Current Extract

 Archive Screens (Person and Contract)

Display all the columns from uoe_hesa_person_dets_archive and uoe_hesa_contract_dets_archive, read only.  As above use the short names as field labels. The archive screens should follow similar format to the screens for the current extract in terms of user interface.

APEX set-up

We already have an HR workspace set up for APEX. It lives on separate database installation from HR (it is on APPSDEV/TEST/LIVE) and connects to HR via database link.

There are currently 2 users/schemas set up with the HR workspace - APEXTRANSLOANDEV and APEXABSDEV, one for the transport loans application and one for the sickness absence one. Each of these users connects to HR with its own database link. At the time of creating them we did question whether this was the correct strategy to adopt but were unsure as to the implications of doing it one way or the other.

Now that we have the benefit of a bit more experience, it is recommended that we create a more generic user name and use that from here on in.

There is little to be gained by creating separate database users and indeed this causes overhead as have to create new ones and new database links for each application that comes along. By creating a generic one we can make use of a single database link for all future developments by granting the link user access to the relevant HR tables.

The existing HR APEX applications run against views that we have created inside HR. However it is proposed that for the HESA forms we simply allow APEX to connect directly to the underlying tables.These tables do not form part of the core HR application but are self contained and only really used by a few people.

1. There will be no adverse affects on the performance of the HR application
2. The forms have to perform updates on the tables rather than just reporting on the contents.

APEX will connect to these tables via a database link.

The link user will be granted select ,insert and update privileges on UOE_HESA_PERSON_DETAILS and UOE_HESA_CONTRACT_DETAILS.  Insert is needed as there are rare occasions on which HR have to create a record manually in the HESA tables. These records will relate to real employees so will have employee and assignment numbers, but for some reason the extract does not pick them up. De;ete is not required as if the record is not to be included it will just be marked as N for HESA_RETURNABLE.

It will only have select on UOE_HESA_PERSON_DETS_ARCHIVE, UOE_HESA_CONTRACT_DETS_ARCHIVE and UOE_HESA_GRADE_DETS_ARCHIVE.

Authentication and Authorisation

We have already enabled same sign-on for APEX and HR and have defined an authentication scheme called 'HR app' inside APEX. All that has to be done is to add this authentication scheme to the new application (inside the Shared Components section) and APEX will then accept the users HR login details. Note that since this is same sign on rather than single they will be prompted for their details again by APEX when they navigate to it from the HR application.

Authorization:

This is concerned not with who you are but rather what you are allowed to access. APEX controls this using 'authorization schemes'.

The current APEX applications each have their own version of an authorization scheme  called EBS_RESPONSIBILITY. The only difference between them is that the databaselink name that it uses to connect to HR is different. Take a copy of this and amend the link name to the new generic one.  This scheme accesses a custom pl/sql package that we have created inside HR that confirms that the user has the relevant HR responsibility to permit them to access the screens. In this way, APEX is making use of the inbuilt HR application authorization rules.  To enable this to happen, when the HR application makes the call to APEX, it passes the 'RESPONSIBILITY KEY' (i.e the internal ID number for this menu option) as part of its URL parameters.  APEX then calls back to HR and tests that the user does have access to this responsibility. This means that it is not possible to call APEX directly via URL without knowing the responsibility key - and even if you do have the key, you will not gain entry unless you have the relevant access permissions within HR.  Either the transport loans or the sickness absence reporting applications can be used as examples for this - note that the parameter values are stored in application level items.

Structure of the APEX application

To save the users having to re-authenticate or navigate back via HR when they want to move between the archive screens and the current ones, we should incorporate both into the same application and have a tab for each of them.

When the user selects the option in HR it will open APEX at the correct tab because we pass the page number in as part of the URL. However if they then decide to look at the other form they can just tab to it inside APEX without going back to HR - the sickness absence reporting application is an example of how to achieve this.

HR changes to call APEX instead of the form

The HESA forms are called by function UOE_HESA4 (for the current one) and UOE_HESA5 (for the archive).

Navigate System Administrator > Application > Function and query on UOE_HESA%.

 Note that in the above list, UOE_HESA and UOE_HESA2 are old functions that have been superceded. HESA3 is the form used for HESA fund source maintenance and we are not touching that.

Click on the Properties tab and set the type  for UOE_HESA4 and UOE_HESA5 to SSWA jsp function:

 Click on the Form tab and remove all details relating to UOE_HESA4 and UOE_HESA5:

Click on the Web HTML tab and input URLs for UOE_HESA4 and UOE_HESA5.

The url should be in the undernoted format:

GWY.jsp?targetAppType=APEX&p=102:2:::::EBS_RESP_ID,EBS_APP_ID,EBS_SEC_GROUP:50101,800,0

In the above example, p=102:2 tells it to go to page2 of application 102. The application number will be allocated by apex when it is created so just use whatever it comes up with.

EBS_RESP_ID ( 50101),EBS_APP_ID (800) and EBS_SEC_GROUP(0) are 3 parameters that are used by the authorization scheme.

The EBS_RESP_ID will vary depending on the responsibility that is calling APEX and can be derived by running query against the HR database:

select responsibility_id from fnd_responsibility_vl

where responsibility_key='UOE_HESA_ADMINISTRATION;

This returns 50101.

The last 2 will always be 800 and 0 for our purposes.  

Since we are building both the archive and the current reports within the same APEX application, the only difference between the URLS for HESA4 and HESA5 will be the page numbers.

Changes to eRecruitment BOXI universe

https://www.projects.ed.ac.uk/project/hrs071/milestones/system-design-sign

At the design sign off meeting (see note at above URL) it was agreed that these changes would be taken forward as part of the eRecruitment project phase 1b. 

Changes to eRecruitment Successful Applicant Interface

The Successful applicant interface pulls details of successful applicants from eRecuitment and creates a staff record for them in the HR application.  It is controlled by a plsql package in the APPS schema - UOE_ERECRUIT_APPOINT_PKG.  The ddl for this package has been checked out of VSS and is available in the HRS071/VSS directory on the K drive.  First thing to do is run this script against the dev database to make sure it is in line with live.

Amendments to the temp working tables

Add new columns to the following tables

Changes to pick up and populate the new ACTHQUAL1-6 fields

This package will have to be amended to pick up details of the new ACTHQUAL1 - ACTHQUAL6 fields. HR deparment have added the new fields into eRecruitment and they will be made available in the eRecruitment views to which the interface has access. We have to amend the package so that it retrieves the values from the views and then loads the information to HR.

These fields are held in what eRecruitment defines as additional question categories and so the data is available in the rtbi_applicant_questions view.  This is a view on the COREHR schema on APPSDEV/TEST/LIVE. This is the schema owner of the eRecruitment application. These views are owned by eRecruitment and are usually used for its own internal reporting but we have been given access to them by way of a database link.

The questions for the ACTCHQUAL have been built as a list of qualifications where the user has to select Yes/No to confirm whether or not they hold that qualification. It had to be built the way it was due to technical/aconfiguration constraints within eRecruitment but it is less than ideal as the user can choose Yes to more than 6 qualifications and we only have space to store 6. The downside of this is that we just have to use the first 6 we come across and this might not be the 6 the applicant really wants us to pick depending on order of priority.

Also, this structure means that the data displays unusually in the rtbi view. The coding which follows is therefore by natrure a bit more clunky and complicated than I would like. What we need to do is pick up the first two characters from the 'question' column as this represents the code that we will be loading to HR. We want to pick it up where the value in the 'answer' column is 'Yes'.

Amend Procedure pop_appointee_table

Add a new cursor called c_hesa_actchqual (p_recruitment_id in varchar2,p_applicant_no in varchar2).

cursor c_hesa_actchqual (p_recruitment_id in varchar2,p_applicant_no in varchar2) is

select substr(question,1,2) actchqual_code

from corehr.rtbi_applicant_questions@APPSCOREINTLINK

where recruitment_id=p_recruitment_id

and applicant_no=p_applicant_no

and answer='Yes'

and substr(question,1,2) in ('01','02','03','04','05','06','07','08','09','10','99');

Create a new variable l_hesa_actchqual_count number(1):=0;

create a new variable l_sqlstring varchar2(200);

If we do not already have variables for l_recruitment_id and l_applicant_no then create two now. Both varchar2(12);

Then somehwere alongside the other HESA updates in the procedure add something similar to the following:

l_progress_marker:='hesa actchqual';

 /* if l_recruitment_id and l_applicant_no already populated then fine, else set them up in here with the values of a.recruitment_id and a.applicant_no*/

  l_hesa_actchqual_count:=0;

for b in c_hesa_actchqual (a.recruitment_id,a.applicant_no) loop

   l_hesa_actchqual_count:=l_hesa_actchqual_Count + 1;

  if l_hesa_actchqual_count <=6 then

      l_sqlstring:='update uoe_erecruit_appointees_tmp set actchqual'||l_hesa_actchqual_count||' = :1 where recruitment_id=:2 and applicant_no=:3';

      execute immediate l_sqlstring using l_actchqual_Count,l_recruitment_id,l_applicant_no;

     commit;

end if;

end loop;

Finally, update the call to append_to_appointees_log2 to pass in the values for each of the actchquals.  To achieve this will have to also amend the c_details_for_logging cursor so that it includes them.

Amend procedure append_to_appointees_log2

Add the 6 actchqual fields and insert to the log table.

Amend procedure person_extra_info

Add another 6 input parameters to the procedure -  p_actchqual1 in varchar2,p_actchqual2 in varchar2,p_actchqual3 in varchar2,p_actchqual4 in varchar2,p_actchqual5 in varchar2,p_actchqual6 in varchar2,

Create a new cursor that we will use for transfers and rehires to decide whether or not to create a new entry or update existing.

cursor c_person_extra_info_details3(p_person_id in number) is  select person_extra_info_id       ,object_version_number  from per_people_extra_info  where person_id = p_person_id  and information_type = 'UOE_HESA_PERSONAL_EIT;

Create new variables as in/out parameters for the api call:

l_person_extra_info_id_3  number

l_person_info_ovn_3 number

v_person_extra_info_id_3 number(15); v_eit_object_version_number_3 number(15);

At each point in the code where we are already creating UOE_EQUAL_OPPS_EIT and UOE_ERECRUIT_EIT  add a call to create an instance of the UOE_HESA_PERSONAL_EIT for this person                                                                 

hr_person_extra_info_api.create_person_extra_info(p_validate =>pkg_validate_flag                                                       ,p_person_id => p_person_id                                                        ,p_information_type => 'UOE_HESA_PERSONAL_EIT'

                                                       ,p_pei_information_category =>'UOE_HESA_PERSONAL_EIT'

                                                      ,p_pei_information1 =>p_actchqual1

                                                       ,p_pei_information2 => p_actchqual2

                                                      ,p_pei_information3 =>p_actchqual3

                                                      ,p_pei_information4 =>p_actchqual4

                                                       ,p_pei_information5 =>p_actchqual5

                                                       ,p_pei_information3 =>p_actchqual6

                                                      ,p_person_extra_info_id => v_person_extra_info_id_3

                                                      ,p_object_version_number => v_eit_object_version_number_3);

At each point in the code where we are updating other EITs, add a call to update the UOE_HESA_PERSONAL EIT as well:

 hr_person_extra_info_api.update_person_extra_info (p_person_extra_info_id =>l_person_extra_info_id_3                                                           ,p_pei_information1 =>p_actchqual1

                                                          ,p_pei_information2 => p_actchqual2

                                                         ,p_pei_information3 =>p_actchqual3

                                                         ,p_pei_information4 =>p_actchqual4

                                                         ,p_pei_information5 =>p_actchqual5

                                                         ,p_pei_information3 =>p_actchqual6

                                                         ,p_object_version_number => l_person_info_ovn_3);

Changes for SOC code

At present we are retrieving and loading soc_codes. We will now have to retrieve from a different location in eRecruitment and load to the new ACTSOC field in HR (ass_attribute6).

This is a relatively simple change to make as ACTSOC is being stored in the same location in eRecruimtent as soc_code used to be.

Although soc_code is 2 chars and ACTSOC 3, the underlying temp tables used by the interface have been set up as varchar2(150) to match the size of the HR column so we could just use the old field and rename it (syntax as follows)

ALTER TABLE table_name   RENAME COLUMN old_name to new_name; (this has been possible since 9i but I have never tried it before!)

This change would have to be made to UOE_ERECRUIT_APPOINTEES_TMP and UOE_ERECRUIT_APPOINTEES_LOG

Variable lengths inside the code have also been double checked and are ok.

Therefore it would just be a case of find and replace soc_code with actsoc throughout the code and amending the code in procedure ASSIGNMENT_1 to update ass_attribute6 with the value of ACTSOC where it currently sets ass_attribute24 to soc_code.  Note that there are some variables e.g l_soc_code so will also have to rename l_actsoc_code.