Oriel

10.196.16.50

C:\WorkforceExtract

Activity

E&W Midlands

Y&H

NW

NE

SW

London

TV/Wessex

HR/Lead Employer Team runs/extract Oriel Custom Report of trainees after Offer deadline

The recruitment Team depending on specialty IDs supplied by the programme Teams

The recruitment Team

One person in the Team has got access

One person in the Team has got access

One person in the Team has got access

Download Records with relevant documents;

How often is the download made

The download is weekly to check any new record

The download is weekly to check any new record

The download is weekly to check any new record and inform the Data and Programme Team

Download from Oriel and upload into TIS are not weekly, it happens when it needs to happen. They constantly check Oriel for new starter and upload them into TIS until post is full.

There is no Weekly extract from Oriel to Upload into TIS in this region but a big upload. There might be situations where some programmes’ recruitment might still be on even after deadline, then a report is pulled after the deadline of such extra recruitment.

The Download is automated in London

Download Records with relevant documents;

Any Document downloaded with Report?

No document downloaded with report

No document downloaded with report but the TPD do download the following which differs from school to school and documents are saved to the trainee file

• an application pack

• references submittedevidence of qualifications overseas

• ALS certificate (speaking English to a sufficient standard).

• Any additional documentation that the national recruitment team add to a particular application

No document downloaded with report

No document

No document downloaded with report

No document downloaded with report

The downloads; Core, Higher, academic, Foundation, Dentist depending on the national timeline. Academic Round- Academics trainees are first pulled then Core trainees in round 1(Academic could be a bit of Round 1 because of clinical interviews), round 2 will be higher trainees and so on depending on the timeline.

Core, Higher and academic. No Foundation and GP

Reports generated by the recruitment team includes Foundation Doctors Records which follows the same process

Run/Extract reports ;

What format?

CSV

CSV

CSV

CSV

CSV

XIsx

Run/Extract reports ;

Where is the report saved?

SharePoint

SharePoint- The Data Analyst save in SharePoint the extracts

SharePoint

SharePoint

SharePoint

SharePoint

HR/Lead Employer Team

Programme Team

Data Focus Admins for each School/Specialty in HET.

Mapping of reports

What activities is done in the mapping?

1. Manually remove person’s record without GMC number>fills in the recruitment Import template with Oriel received trainee details including ApplicantIDs, Surname and GMC AND with fields that do not come from Oriel e.g Programme and curriculum details> Using the People Bulk Upload
Template in TIS to upload into TIS and at the same time split the full data into different programme appointment for the programme Team but hide sensitive data - equality and diversity etc > saved into another folder in SharePoint for the different programme Teams to access> inform each programme Teams of the created record in TIS and the location of the folders.

Check if necessary data are in the report> If contain necessary data then run a VBA process to arrange columns and merge files.which will be a Mirror of the 4 report> If there are missing GMC number, create a separate file for person’s record without reference number (GMC/GDC) in Oriel and follow the process when GMC number is received>Add record to the Export Data file which can be accessed by all the member of the Data Team who will follow the next part of the process>Add the Programme and curricula details. For more complicated area e.g ACCS or Academics who are already on TIS, the Specialist Data Analysts add the programme and curricula>Check out missing data e.g. address line 2, gender fields etc>Create a file to be uploaded to TIS from the Import sheet which is unto populated>Uploads the files with the required details to TIS using the People/People Updates bulk upload function in TIS> other specialised Data Analyst then go into TIS to add placement. If foundation records, TPM get updated emails of Foundation trainees in late August time from Trainee and then update TIS using People Bulk update template

The Recruitment Team notify the Data Officer and the Programme Team (but sensitive data are made hidden for the programme Team)> Data Officer set up the recruitment template> Align template with Oriel report by using series of formulae and look up tables> Data Officer import raw Oriel data into recruitment template which converts these raw into a TIS bulk upload template > If no missing data, import data to stage to test and fix errors> but if missing data, missing data is highlighted in the bulk template> Hide sensitive data and lock password to protect> save in SharePoint> Each school/programme review and confirm data > Data Officer upload to TIS> Notify Programme/School record is created > Programme/School bulk add placement (this could be manual).

• This uses a series of formulas and look up tables, which should hopefully make things a little easier to understand

Manually Map/Match the custom Reports with the new starter template which is a mirror of the TIS People Bulk Upload Template; 1) Manually Add programme and curriculum details (which includes start and end dates) to the template 2) Carry out pre-employment Check on Trainees before uploaded on Trac (using a different template) which is the pre-employment system 3) Complete Occupational Health template and upload to Occupational Health system> After 1 - Manually Add programme and curriculum details (which includes start and end dates) to the template, Check for missing GMC/GDC> if missing GMC, Ask Trainee or go to GMC connect to get number and if not supplied, GMC/GDC number is not available, it goes into TIS as UNKNOWN. Most of the time to meet CoP deadline. Some international Trainees might not have GMC/GDC number> Split the Template into different specialties and upload each into TIS> after record is created in TIS, Lead Employer Team/HR get the Trainee Rotations from TPDs/specialties by send template without sensitive data to TPDs (Lead Employer Team/HR hide sensitivity data from TPDs. TPDs gets names, interview ranks and email)> Lead Employer Team/HR Update the placement template with the rotations received from TPDs> Lead Employer Team/HR Bulk upload Placement into TIS> Lead Employer Team/HR ask the Programme team to check and confirm the grades

After the recruitment Admin saved the report in the common SharePoint, the programme Teamm filter on GMC number to check missing GMC number> if missing, email trainee to send number within a deadline> if not submitted withing deadline, programme check GMC connect> Update Oriel report with number> Programme Team amend on the Oriel report the field’s language that are not matching the TIS fields language e.g. Ethnic origin, Gender, Sexuality, Religion, Nationality etc AND add Programme and curriculum details for each trainee on the Oriel report>Programme team transfer the Oriel report into the TIS People bulk upload template> upload to TIS> After record is created, Programme Admin/ Specialties Bulk upload Placement into TIS after the people bulk upload is done

After report is saved is auto saved into specialty SharePoint> Preprogramme and Team Data Focus Admin in HET Check data correctness. If second run duplicates is removed> Update with programme and curriculum and other missing data like GMC/GDC Number> Request Missing GMC/GDC number from Trainees> if not received, Update Spreadsheet with ‘UNKNOWN’ or ‘0’> Data Focus Admins for each School/Specialty uploads the spreadsheet to TIS using the People/People Updates bulk upload function into TIS>Programme Admin Bulk upload Placement into TIS

Mapping of reports

What template is use?

Template used; Oriel Custom Report - Sent by the recruitment Team to the Admin, Recruitment Import Template/TIS Bulk upload template - use by Admin to upload into TIS and which is also splitted into different programme team.

This uses a series of formulas and look up tables, which should hopefully make things a little easier to understand

Mapping of reports

What software/app is used?

No identified software

Note:

• LO split those without GMC/GDC number from Oriel and check on TIS to ensure any duplicate record is not created. Foundation and Dental, International Medical Graduates (IMGs) foundation are most likely not to have these numbers.
  TISid are added to keep track when manually adding.

• For the second round of upload, already upload/created records are removed before new upload is done

Software - VBA/sql script /snag to identify anomalies

his uses a series of formulas and look up tables, which should hopefully make things a little easier to understand

Note

• Please note that some schools in the region do the people bulk upload into TIS themselves but the Data/Information Team will be informed.The Data/Information Team will still have to upload the sensitive data using the People Bulk Update Template in TIS

No app/software

Lead Employer Team/HR

Data Focus Admins for each School/Specialty in HET

Bulk Upload data Person Record to TIS

What Template?

Use the People Bulk Upload Template/recruitment Import template

Export Sheet/Import Sheets

Recruitment Template/TIS Bulk Template

TIS Bulk Template

People Uploads Bulk Upload Template

Lead Employer Team/HR

Programme Admin/ Specialties manager

• Programme Admin

Bulk Upload/Upload Placement

What Template?

Bulk Placement template

Manually Add placement when trainee records are created

Manual/ Bulk placement template

TIS bulk placement template

Bulk placement template

TIS bulk placement template

Note: GP manually upload placement

Yes, follow the same process

No

Contacts:

Foundation; MADDOCK, Suzanne (NHS ENGLAND - T1510) suzanne.maddock2@nhs.net

GP; SLOMAN, Richard (NHS ENGLAND - T1510) richard.sloman@nhs.net

Yes

Reports generated by the recruitment team includes Foundation Doctors Records which follows the same process

Pain Point

• Not all of the data are pulled from Oriel because they are missing in Oriel

• Fields not matching with TIS fields

• Programme and Curriculum details depends on Team experience and TIS as it’s not clearly stated in Oriel.

Pain Point

• Oriel- TIS does not talk the same language around some data fields e.g Ethnic origin, Gender, Sexuality, Religion, Nationality etc. Louise to send list. Programme team have to amend these fields before uploading to TIS . This impact the deadline dates in the code of practice

• One person per region has access to pull data from Oriel

• 90% work on the full report file but some specialties create their own files to work on separately

Pain Points

1. Meeting CoP deadlines

2. Manually adding Programme, rotation and curriculum

3. Changing Oriel language to suit TIS languages

• This Information comes from the team experience and looking into TIS for the information. What comes from Oriel is the Offer/specialty which sometimes don’t match the programme

• Report pulling depend on national timelines. Report generations depends on offers made at different time- The reason for rounds of report generation

• Download from Oriel and upload into TIS are not weekly, it happens when it needs to happen. They constantly check Oriel for new starter and upload them into TIS until post is full.

• It is the same timeframe for CoP for Lead Employer as it is for other Local Offices. The CoP for Lead Employer is to tell the Trust and Trainees where trainees are going which is the same timeframe of 12 weeks

• Incomplete data e.g (GMC/GDC); Email doctors to request for the number before uploading spreadsheet to TIS

• There is no Weekly extract from Oriel and Upload into TIS in this region but a big upload. There might be situations where some programmes’ recruitment might still be on even after deadline, then a report is pulled after the deadline of such extra recruitment.

• There are different reports that can be generated from Oriel. National Recruitment webpage has got deadlines/timeline for different specialties. Reports are generated in rounds based on the timeline in the national recruitment timeline. Each of these specialties have got a vacancy ID number that will be used by the recruitment team to download reports from Oriel.The key specialties are:

  • Core specialties

  • Higher Specialties

  • Foundation

• Programme give vacancy ID number to Recruitment Admin of what specialty data report to pull from Oriel

• Please note that about 90% of Specialties work on the same full report but few do create their reports from the full report to work on.

• Programme and placement details comes from Oriel also. Curriculum don’t come from Oriel but from TIS

• Each specialty manager upload the placements for their respective trainees. The placement comes from Oriel most of the time
  There is a recruitment planner in the region that is used in placement and ranking. The TPD get involve in this decision of placement sometimes. There also what is called Long Term Planning and Distribution Posts

• Reports generated by the recruitment team includes Foundation Doctors Records which follows the same process

• For Missing (GMC/GDC), Request is made to Doctors to supply. If GMC/GDC number is not available, it goes into TIS as UNKNOWN or 0. Some foundation/ international Trainees might not have GMC/GDC number.

• For Programme, Rotations, and curriculum details, the Information comes from the team experience and looking into TIS for the information. What comes from Oriel is the Offer/specialty which sometimes don’t match the programme. This is a manual process of updating these details on the spreadsheets before upload to TIS. This is a challenge for the programme.

• After Trainee record is created in TIS, the programme team then add the placements/Rotations to the records using the Placement Bulk Upload template

Do Nothing

1

Mapping and Analysing LOs process

This ticket was raised by the TIS data team. Mapping sessions was carried out by the BA and the TIS data Team with each LO

Data Lead

TIS21-5323 - Getting issue details... STATUS

1

In the workbook on sub heading 3, there are fields that are reference data from Oriel and require further data mapping to be done. 

Agreed that wherever possible we will adopt Oriel ref tables to avoid having differences and having to map the data. Ashley to check impact on ESR interface to determine which tables we can do this for.

Agreed that nationality will have to align with ESR as data passes through interface so this must be mapped.

Data Leads

TISDEV-3356 - Getting issue details... STATUS

2

In the workbook on sub heading 3, there are fields that are not necessarily mandatory on Oriel and would therefore only pull through if the applicant/trainee has provided the information on their application form, e.g. National Insurance number, Religious belief, Sexual Orientation etc.

Recommendation is that if completed in Oriel that it is pulled through into TIS.

Data Leads

TISDEV-3357 - Getting issue details... STATUS

3

In the workbook on sub heading 3, there are fields that are on the Recruitment Import template but not Oriel. Which of these fields are required in the template and where would these be sourced from?

Which of these should be mandatory?

Feedback received from Claire and Alice as to which one to keep on the template and which one to remove.

Draft version consolidated and added to dev story.

Concern over mandatory fields in the template. E.g. DOB may not be available for non-trainees. 

Data Leads

TISDEV-3358 - Getting issue details... STATUS

4

Workbook on sub heading 4 was put together with input provided by Claire Lehoux. Although separate from Reference data mapping, there still need to have legacy data tidied up in the DR. Not related to the Oriel integration but for data quality in general.

Data Leads

TISDEV-3359 - Getting issue details... STATUS

5

Workbook on sub heading 5 has a list of Immigration statuses discrepancies that requires mapping. Oriel reference data for 2017 and 2018 have different list of values that will be passed on to TIS.

Agreed not to map and just take Oriel values as Intrepid data is not accurate and up to date.

Regarding the 2 new questions added for immigration on 2018 application form in Oriel, the recommendation is to record these as 2 new fields on TIS.

• 1) Blue card registration question,

• 2) Is this your own visa or are you a dependent?

Oriel to fix their extract to send those values.

Data Leads

TISDEV-3360 - Getting issue details... STATUS

6

Reference data changes on Oriel will need to be coordinated with TIS going forward. 

Data Leads

TISDEV-3313 - Getting issue details... STATUS

7

Custom report can be built on Oriel to extract a list of trainees for specific programme/s where they have accepted an offer or withdrawn after accepting. This task is to:

• Define the fields for the Custom Report

• Create the report template with support from Ashley on Oriel to be run by LO

• Test the Report

Ashley and Claire met to discuss and Recruitment lead is currently working on this. 

Concern raised over Dental trainees. They are expected to use the agreed approach in order to import trainees from Oriel into TIS.

Ashley - To speak to Joanne Watson (Unlicensed) Alistair Pringle (Unlicensed), to add channel on Microsoft Teams to get feedback over: - Done

• Agreed process

• Recruitment Import Template fields

• Oriel Custom Report/extraction to support bulk upload

Data Leads

TISDEV-3361 - Getting issue details... STATUS

8

Documents will be be extracted from Oriel and stored on TIS in a data structure on Azure to be consumed by the Bulk Upload function during trainee records creation.

For info only.

Data Leads

9

The Recruitment Import template may need to be changed to have new columns or some columns removed if not needed. For e.g. ApplicantID may need to be added to accomodate for the above Process.

Some of the columns in the current Recruitment import  template in its current format, will overlap with the fields that are received from Oriel. Do we want to keep them in the template?

Which of the  overlapped fields would be best to be manually filled? 

ApplicantID will be added. - Done

Next steps are:
a) Map the reference values for ESR to Oriel/TIS
b) Confirm the mandatory fields in the ESR inbound file.

SImilar action to #3.

ApplicantID = ORIEL PIN

Data Leads

TISDEV-3362 - Getting issue details... STATUS

10

The Recruitment Import template will be used both for creating and updating records on TIS.

In a scenarion where records already eixist on TIS, what should be the best course of action by the Upload function? For e.g. should all or a selection of  fields on TIS be updated/overwritten?

The template will be used with the relevant fields populated so that existing person record on TIS can be matched with to update the record. The rules for finding a matching person record are
1. Applicant ID match if it exists
2. else If ApplicantID does not exist on the template, then use Surname AND GMC/GDC number match
3. else if 1 & 2 are false, then create new Person record

If an existing person exists on TIS, then update with populated values from the spreadsheet and pull Oriel stored data from Azure storage to update the person record with programme and curriculum details.

Data Leads

TISDEV-3377 - Getting issue details... STATUS

11

Oriel XML file format

HEEOE_20171116_010003_Application.xml.xml

1

Understanding process followed in local offices for onboarding new PGDiTs from Oriel to TIS

TIS21-5323 - Getting issue details... STATUS