04/02/2019

Discussed on DQ on 04/06:

• Agreement re changing '0', 'Retired', 'TBC' or 'u' to UNKNOWN in the GMC/GDC/PH number
• Changing blank to UNKNOWN not recommended as it may legitimately be blank because they have a GDC or PH number
• What we should do to those with blank/null in all 3?
• JH: Among those none have programmes and very few have placements. There are Staff, Both, Contact, Admin.
• JW: 
  • Admin and Contact = N/A ?
  • Staff and Both = UNKNOWN ?
    is that the agreement?

04/06: The following has been agreed:

• GMC for Contacts, Staff and Both = UNKNOWN
• GMC for Admin = N/A

DS: List of persons with same GMC's, check the DW to see if some of those have been resolved?

TISDEV-4841 to be paused from releasing to Prod until David Short (Unlicensed) has done his investigation of the ones that could potentially be merged. Merge Rules to be shared. To create tickets following that.

11/06: Ongoing and will probably be ready mid-week.

18/06: On hold until the document uploads are available on People records? The idea being if de-duplicated before the documents have been added, we won't be able to attach the documents to the deduped person records. 

Get an update from Ben/Anita regarding document management in a couple of weeks. 

02/07: Benjamin Wittonare you able to give an update on this?

09/07: Ben: We are expecting the document delta from Hicom this week. The next step will be to get them onto Azure and then apply the agreed meta tags.  Do let me know if you need any other details/updates. 

16/07: Delta received. Document work to continue in this sprint. Following which David can work his magic on the duplicates. David Short (Unlicensed)  does your  initial work need to be revisited? and will there be any dev requirement related to your dup work? We will need to include this in Planning so will need to know  Complexity and volume - David may need to work with Dev in next sprint in order to prepare this. Prioritisation required. 

23/07: Work in progress, script modified. (David Short (Unlicensed),  bullet points of what's required to bring into the sprint as User Stories)

13/08: Document Management migration complete - when can work start on the duplicate records David Short (Unlicensed) ?

20/08: DS: Scripts being worked on. Next step to create the stories in Jira. 

03/09: David: Looking at having the scenarios by end of the week. 

10/09: DS confirmed this is a trainee on his list too -  TISNEW-1894 - Getting issue details... STATUS

10/09: DS demo'ed some 'strong match' scenarios that can be safely de-duped. Sue/ Joanne Watson (Unlicensed): impact on leave manager when de-duped.

Output of Round 1 of de-duplication:

https://healtheducationengland.sharepoint.com/:x:/r/sites/WPI-North/Shared Documents/TIS/North (all)/TIS Person De-Dup Project/Duplicates Output v1.xlsx?d=w70c77e6caa5b4cc1a2408663a1e77fd6&e=4%3a3feb8d869af74e61aeae63ea2a9baff4&at=9

17/09: Tickets for dev to start looking at. (AR and DS) 

Considering other priorities in the next sprint, we are looking at mid to late October.

24/09:  TISNEW-2057 - Getting issue details... STATUS  - DS to send the rules to add to the ticket. (This has been added to Sprint 59 which is due to start on Thursday - do we have all the info we need?)

01/10: Methodology attached to the ticket. David Short (Unlicensed) to separate out only the records that need to be merged for Round 1 to attach to the ticket.  

15/10: TISNEW-2057 has been pulled out of sprint due to impact on leave manager records. Merging on the TIS side would leave orphan records on the Intrepid/TWS side.

22/10: Awaiting confirmation on the above regarding the correct records out of the duplicates to get rid of.

29/10: List received from David - will add to the ticket and work with devs to get it into a forthcoming sprint.

05/11: Awaiting confirmation on the merge rules from Data leads, as the work involves merging data to the survivor person record then removing the duplicate.

DS: There are 2 main merge rules that need to be reviewed by other data leads and may require some tweaking.

Person Merge Rules (DRAFT)  (David Short (Unlicensed), James Harris, Alice Thompson, Sue Barrand (Unlicensed))

1.All associated records (placements, programme/curriculum memberships, ARCPs, etc.) belonging to a duplicate person record need to be transferred to the surviving person record.

2.Where the surviving person record has a field containing a NULL or Zero-Length-Strings value but the same field in the duplicate person record contains a non-NULL/ZLS value, this value should be copied into the surviving person record. This should exclude address fields due to possible town/county/postcode mismatches.

• Use the address that are against the survivor records. The script to be tested on UAT first with the data leads. 
• All associated records (placements, programme/curriculum memberships, ARCPs, etc.) regardless of whether they are different on the duplicate Person record, bring across to the Survivor records. 
• Data leads to specify the fields for which we do (2) and those to exclude e.g. Address. DS will provide this.
• Documents: Pull all the docs from the Duplicate across to the Survivor records regardless of whether these are duplicates.  
• GMC/GDC/PH that should be kept on the survivor records to be confirmed. DS: Basically, after a person record merge, if a GMC/GDC/PH number is specified, that is the value that should be assigned to the GMC/GDC/PH field of the surviving record. In the case of blanks, the current value (whatever that they be) of the surviving record is retained.

12/11: The merge rules have been updated on the ticket and David has provided attachments for:
- what GMC/GDC/PH to use on the surviving record
- list of front end fields where values can be taken from duplicates to surviving where there is a blank in the surviving record
Could you all confirm please?
TISNEW-2057 - Getting issue details... STATUS

• Added rule for Sensitive data - All rules confirmed by data leads. 

26/11:

The devs have done a spike on this and came up with the following approach,

Proposed Solution: With regard to the attached template "TIS People Upload Template - Duplicated records.xlsx", we propose to do a series of 'updates to' and 'insert into' the surviving record. Once this gets completed, we will proceed to delete the duplicate records.

Updates:
1) We will be implementing merge rule 3 on the following fields for the surviving record:
Title
Initials
Known As
Forename
Surname
Maiden Name
Legal Forenames
Legal Surname
Telephone Number
Mobile Number
Email Address

i.e. if a value is present on the duplicate record for the above but null on the surviving, we will be copying them onto the surviving record.

2) We will be directly updating the surviving records with the presented GmcDetail or GdcDetail. https://hee-tis.atlassian.net/secure/attachment/21793/DeDup_NoLeave_GMC_GDC_PH.xlsx

3) Insert:
We will be inserting all the following records of the duplicate into the surviving record
   - Programme Membership
   - Qualifications
   - Documents
   - Placements
   - Assessment

13/08: 

• Need to ID those Consultants who aren't on TIS as trainers and remove them
• Those who are on a trainers we need to understand what information we should surface on their records e.g. Assessment information perhaps isn't required.
• All open for discussion and agreement - Ben to set the scene
• Other regions to review Alice's scripts. 
• How were consultants recorded on intrepid?  - Simply, not in a consistent way.  Some Trusts might have used the consultant role to define these records, others may have applied a training/non-training grade to a record.  
• Benjamin Witton to update with some bullet points of what's been discussed. (not about the mulberry bushes) 

1. We have been asked to remove consultant/non-trainee data from the system.  The focus is those records where there is noinvolvement in the management of a trainee.
2. There are four parts involved in this work:
   1. Define the rules/cases to identify the records which should be removed from the system.  We want to achieve this incrementally, so we should start with easy win's and then work our way to the more difficult cases.
   2. Create the SQL scripts, run the SQL scripts and verify the results and that those records should be removed from the system.
   3. Introduce a process where the TIS Development Team are able to take the results and simply and efficiently remove the records from the system (without too much back and forth).
   4. Confirm with the owner the data to be removed if they want a copy of the data.  
3. Senior colleagues want to see progress by 22nd August.  We can satisfy this if we can show our approach and even any preliminary results.  

Objective:

Gain understanding of what the local office requirements for trust access should be based on - NPN seems to be the incorrect basis as in TWS it is based on Training and/or Employing body

Email conversation excerpts: 

07/09:
James:
- Having the logic based on NPN would require us to update thousands of posts numbers and the ESR positions in the trust.
- I was discussing this with Gwilym earlier and we think the logic should be an OR statement with EmployingBody and TrainingBody.
07/09:
- Joanne/Paul: Currently on TIS: Placement > Site > Trust determines the access

- JH: This is what was on Intrepid and works for me
10/09:
- Andy: concerns over placement > site > trust logic
Q - Site reference table - Is site attributed to only one parent trust

Q - So if we are using the Trust of the placement site – then we can still only grant access to a post & its occupant to that one trust

Q - e.g. a Severn GP trainee goes to Royal United Hospital (RD130) for a placement. The parent trust in the reference table for that site would be Royal United Hospitals Bath NHS Foundation Trust (RD1). We would also want however for Gloucestershire Hospitals NHS Foundation Trust (RTE) to have access as the lead employer. The only way I’d picture that happening would be through the ‘OR’ statement on Employing Body and Training Body that James mentioned below.

Q - small pockets of users that are trust based but have their data profiles restricted by Programme. We have done this for the new GP lead employer in Thames Valley as Pennine Acute Trust won the tender as well as some local trust based administrators that only look after GP. This allowed us to prevent them from seeing trainees that were not relevant to them at the trust they are based but also an easy way of seeing the trainees out in GP practices as they don’t really have a parent trust, we have the CCG in that field for GP practices. I suspect we may need to find a workaround to this in the short term perhaps using reporting.

Joanne Watson (Unlicensed) / Ashley Ransoo - Requirement for TIS: (Ticket to be created and get an estimate from Dev)

TISNEW-1987 - Getting issue details... STATUS

People - If either the trainnee's EmployingBody OR TrainingBody for their Current Placement Post match the Trust, then make available to the Trust.

Posts -  If either the User is at a trust which is the EmployingBody OR TrainingBody of the Post, then make available to the Trust.

17/09:

JW: 

...very rough guideline of the complexity of amending the current logic on the trust role was given an 8 (out of a possible 10). This is rather large and I have concerns that we might not make the deadline of the end of next week for the role to be amended, tested and users issued with their login details.

So, with the above in mind I considered what else we could do if we approach this from another angle.  Please could I ask you to consider the following - instead of changing the trust role we leave it as it currently is - so placement>post>site>trust but for those users who are lead employers or who just need to see the trainees and posts from a number of trusts we give them the access to that range of trusts so they can see the entire trainee landscape they should. We would obviously need this info from yourselves but it would save us a lot of dev time.

This isn't elegant but should get us across the line for the 1st October. I then suggest that I ask Ashley to speak to you all asap and get full understanding of the different roles in trust-land so that we can correctly create roles that are needed e.g. the one to allow users to only see trainees based on programme etc. We can then get these roles created and rolled out.

Many thanks for previously organising the email addresses to come to us out of TIS from the regions. The launch went well. We need to be able to identify the training programmes the trainees are on that were sent to us to help us calculate response rates. We have this for the London  region but is it possible to request this for the South, North and Midlands and East regions? As I say, it is only the training programme – e.g. Foundation, GP, General Surgery etc. - Richard Higgins

26/11: Alistair Pringle (Unlicensed) get a list of names for each region and post on DQ channel. 

03/12: Awaiting for the list.

14/01: James Harris will try and do a national pull. Andy has done for the South.

Missing applicants from the South.

Familiarisation of the ESR error reports. 

• DPNs successfully processed at ESR would exist with an esrLoadStatus of ‘SUCCESS’ in table EsrOutboundApplicantConfirmationRecord. e.g. https://www.useloom.com/share/f5044aa222084b4d98515c5c3d45a033

• DPNs successfully received from ESR Outbound Interface as Position Records would exist in tables EsrOutboundPositionInformationRecord or EsrOutboundPositionReconciliationRecord

• DPNs ‘EXPORTED’ from Hicom would exist with a TISstatus of ‘IMPORTED_AND_EXCLUDED’ in tables EsrOutboundPositionInformationRecord or EsrOutboundPositionReconciliationRecord

• DPNs successfully exported from TIS to ESR with Placement information would exist with a status of ‘EXPORTED’ in EsrInboundApplicantDetailsRecord

• And any other reports deemed necessary to investigate support issues.

03/12: Andy Horton (Unlicensed) - Examples of notifications where the next post holders are empty to be provided for further investigation.

08/01: More examples sent through Naz.  Comms to be sent trust regarding support.  

14/01: Investigation carried out and sent to Mike. Discussed on the ESR call last week. The outcome falls into 3 categories which Mike can explain. 

• Posts not set up on ESR
• Trainees have flowed from TIS to ESR and confirmed from ESR received.
• Not a full trail in the logs. 

Decision support tree being put in place (Craig Elton from ESR and Mike supporting this)

A demo of how to investigate ESR issues raised by trusts has been scheduled for this Friday. - Intended for HEE staff with access to Metabase.(record the session)

21/01: Session was done on Friday 18/01 on how to investigate ESR issues. Note: This is not to be shared with Trusts or anyone outside HEE due to IG. Link to recording:

https://healtheducationengland.sharepoint.com/:v:/r/TIS/ESR%20Project%20Documents/TIS-ESR%20interface%20issue%20investigation.%20.%20.%20-%2018%20January%202019%2014.04.33.mp4?csf=1

• Programmes with no Posts or People 
• Posts with no Programmes