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:

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.

 Table Column Datatype uoepp.uoe_mi_cur_assign_temp1 genderid varchar2(2) uoepp.uoe_mi_cur_assign_temp1 relblf varchar2(2) uoepp.uoe_mi_cur_assign_temp1 sexort varchar2(2) uoepp.uoe_mi_cur_assign_temp1 disabiility1 varchar2(2) uoepp.uoe_mi_cur_assign_temp1 disability2 varchar2(2) uoepp.uoe_mi_cur_assign genderid varchar2(2) uoepp.uoe_mi_cur_assign relblf varchar2(2) uoepp.uoe_mi_cur_assign sexort varchar2(2) uoepp.uoe_mi_cur_assign disability1 varchar2(2) uoepp.uoe_mi_cur_assign disability2 varchar2(2) uoepp.ppipmi_cur_assign_funding genderid varchar2(2) uoepp.ppipmi_cur_assign_funding relblf varchar2(2) uoepp.ppipmi_cur_assign_funding sexort varchar2(2) uoepp.ppipmi_cur_assign_funding disability1 varchar2(2) uoepp.ppipmi_cur_assign_funding disability2 varchar2(2) ppipmi.ppipmi_assign_funding_daily genderid varchar2(2) ppipmi.ppipmi_assign_funding_daily relblf varchar2(2) ppipmi.ppipmi_assign_funding_daily sexort varchar2(2) ppipmi.ppipmi_assign_funding_daily disability1 varchar2(2) ppipmi.ppipmi_assign_funding_daily disability2 varchar2(2) ppipmi.ppipmi_assign_funding genderid varchar2(2) ppipmi.ppipmi_assign_funding relblf varchar2(2) ppipmi.ppipmi_assign_funding sexort varchar2(2) ppipmi.ppipmi_assign_funding disability1 varchar2(2) ppipmi.ppipmi_assign_funding disability2 varchar2(2)

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:

 Object Source Daily/Gender Identity ppipmi_assign_funding.genderid Monthly/Gender Identity ppipmi_assign_funding_daily.genderid Daily/Sexual Orientation ppipmi_assign_funding.sexort Monthly/Sexual Orientation ppipmi_assign_funding_daily.sexort Daily/Religion or Belief ppipmi_assign_funding.relblf Monthly/Religion or Belief ppipmi_assign_funding_daily.relblf Daily/Disability1 ppipmi_assign_funding_daily.disability1 Monthly/Disability1 ppipmi_assign_funding.disability1 Daily/Disability2 ppipmi_assign_funding_daily.disability2 Monthly/Disability2 ppipmi_assign_funding.disability2

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

Flex: Period Of Service...

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

 Label Source Updateable instid uoe_hesa_person_details.instid N unless this record is being inserted manually staffid uoe_hesa_person_details.staffid N unless this record is being inserted manually Employee Number uoe_hesa_person_details.employee_number N unless this record is being inserted manually Name uoe_hesa_person_details.name Y HESA returnable? uoe_hesa_person_details.hesa_returnable_flag Y Atypical uoe_hesa_person_details.atypical Y Casual uoe_hesa_person_details.casual Y Gender uoe_hesa_person_details.name.gender Y Birthdte uoe_hesa_person_details.birthdte Y ethnic uoe_hesa_person_details.ethnic Y nation uoe_hesa_person_details.nation Y disable uoe_hesa_person_details.disabled Y Datefhei uoe_hesa_person_details.datefhei Y Prevemp uoe_hesa_person_details.prevemp Y Prevhei uoe_hesa_person_details.prevhei Y hqheld uoe_hesa_person_details.name.hqheld Y Curaccdis1 uoe_hesa_person_details.curaccdis1 Y Curaccdis2 uoe_hesa_person_details.curaccdis2 Y Regbody uoe_hesa_person_details.regbody Y Dateleft uoe_hesa_person_details.dateleft Y Totsal uoe_hesa_person_details.totsal Y Actleave uoe_hesa_person_details.actleave Y Locleave uoe_hesa_person_details.locleave Y Clinard uoe_hesa_person_details.clinard Y Ecrstat uoe_hesa_person_details.ecrstat Y Genderid uoe_hesa_person_details.genderid Y Relblf uoe_hesa_person_details.relblf Y Resast uoe_hesa_person_details.resast Y Sexort uoe_hesa_person_details.sexort Y

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

 Label Source Updateable Employee Number uoe_hesa_person_details.employee_number N unless this row is being inserted manually Name uoe_hesa_person_details.name N unless this row is being inserted manually HESA returnable? uoe_hesa_contract_details.hesa_returnable_flag Y Contract Identifier uoe_hesa_contract_details.contid N unless this contract is being inserted manually Terms uoe_hesa_contract_details.terms Y Moemp uoe_hesa_contract_details.moemp Y Acempfun uoe_hesa_contract_details.acempfun Y Confte uoe_hesa_contract_details.confte Y Sobs uoe_hesa_contract_details.sobs Y Pscag uoe_hesa_contract_details.pscag Y Spoint uoe_hesa_contract_details.spoint Y Nhscon uoe_hesa_contract_details.nhscon Y Nhscongr uoe_hesa_contract_details.nhscongr Y Hspec uoe_hesa_contract_details.hspec Y Heijoint uoe_hesa_contract_details.hei_joint Y StartCon uoe_hesa_contract_details.startcon Y Endcon uoe_hesa_contract_details.endcon Y Actsoc1 uoe_hesa_contract_details.actsoc1 Y Ccentre1 uoe_hesa_contract_details.ccentre1 Y Ccprop1 uoe_hesa_contract_details.ccprop1 Y Actsoc2 uoe_hesa_contract_details.actsoc2 Y Ccentre2 uoe_hesa_contract_details.ccentre2 Y Ccprop2 uoe_hesa_contract_details.ccprop2 Y Actsoc3 uoe_hesa_contract_details.actsoc3 Y Ccentre3 uoe_hesa_contract_details.ccentre3 Y Ccprop3 uoe_hesa_contract_details.ccprop3 Y Atypical uoe_hesa_contract_details.atypical Y Casual uoe_hesa_contract_details.casual Y Clinical uoe_hesa_contract_details.clinical Y Clinsub uoe_hesa_contract_details.clinsub Y Levels uoe_hesa_contract_details.hesa_levels Y Rescon uoe_hesa_contract_details.rescon Y

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

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

 Table Column Datatype uoe_erecruit_appointees_tmp actchqual1 varchar2(2) uoe_erecruit_appointees_tmp actchqual2 varchar2(2) uoe_erecruit_appointees_tmp actchqual3 varchar2(2) uoe_erecruit_appointees_tmp actchqual4 varchar2(2) uoe_erecruit_appointees_tmp actchqual5 varchar2(2) uoe_erecruit_appointees_tmp actchqual6 varchar2(2) uoe_erecruit_appointees_log actchqual1 varchar2(2) uoe_erecruit_appointees_log actchqual2 varchar2(2) uoe_erecruit_appointees_log actchqual3 varchar2(2) uoe_erecruit_appointees_log actchqual4 varchar2(2) uoe_erecruit_appointees_log actchqual5 varchar2(2) uoe_erecruit_appointees_log actchqual6 varchar2(2)

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

where recruitment_id=p_recruitment_id

and applicant_no=p_applicant_no

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.

Project Info

Project
HR HESA Staff Return 12/13
Code
HRS071
Programme
Human Resources (HRS)
Project Manager
Nikki Stuart
Sheila Gupta
Current Stage
Close
Status
Closed
Start Date
24-Oct-2012
Planning Date
n/a
Delivery Date
n/a
Close Date
06-Dec-2013
Programme Priority
1
Overall Priority
Higher
Category
Compliance

Execute