1

Resolve Duplicates - TISDEV-4841: Resolve the duplicate invalid GMC numbers, such as 'Retired,' and make them 'UNKNOWN'

Data leads

First discussed on DQ on 04/06: 

08/01: TISNEW-2364-Merging, then removal of Duplicate Person Records - Round 1DONE - Work completed for Round 1 on Stage and testing began. Since this was a flyway script, the importance was stressed on completion of the testing on stage as this would block anything else from being released to Production in out release pipeline

DS: Has done some testing. SB: leave manager tests carried out. JH/DS/AH: re-use some of the scripts that were used during consolidation for testing on metabase for next week.

DS: Prog membership, placements and assessments - Looks good so far. 

How long more this would need? - conclude by Wed 16/01.( and  are going to test this on Wednesday afternoon)

21/09: The scripts have been run on Prod over the weekend.

has taken extracts of the data on Friday afternoon ahead of the scripts being run. 

4/02: Round two will be needed. LaSE still to advise DS on what to do with the records that weren't an easy match. Chris picking this up - London may be ok but KSS need more attention

4/02: Leave manager - need to do checks for the circa 500 records to make sure that the round 2 de-duping would not leave orphaned records (as per the procedure for Round 1

01/04:

to provide the list of Id's/template for the second round of duplication (second strongest match rule). To send the list out to all regions be checked. Jen would have done for the South - to be checked?

15/04: Still awaiting for EMD, South and London.

24/06: Jen to speak to David tomorrow (25th) re queries she has found in the South. 

James to ask Gwil where has gotten to with this - returns from leave on the 1st July

08/07: David to resend file to Gwil and Jen is no longer confused   as she now understands that this part of the exercise will not remove ALL erroneous records. Work is continuing........

22/07: Ongoing, waiting for South and EMD data

02/09 EM have submitted, awaiting the South (ongoing)

14/10: Back with DS - to compare and rebuild the new list of what's been merged

• Might be useful for bringing in the Northern Ireland data into TIS (@Adewale Adekoya) 

11/11: Completed by South. With David at the moment.

09/12: There will be some duplicates because of Study Leave. The merging would have to be done at some point on TIS. Almost ready to go. 

06/01: DS post on slack for additional feedback.

03/02: DS posted on slack for everyone else to confirm this is OK. (De-dupe Round 2 spreadsheet)

20/04: Recommendation: to be done ahead of Northern Ireland and HCS onboarding onto TIS.

• Aim for May 2020

04/05:

• Mids & East

• North

• South (requested a report from Hicom re People Id’s that relates to TIS Person ID’s) - To then check against David’s SS

• London

20/05: Round 2 de-duplication file -

Open

We can go ahead and de-duplicate the records identified (~20) @David Short (Unlicensed) to share those records, @Ashley Ransoo @James Harris @Sebastian Kwok (Unlicensed) create ticket for Round 2.

Some coordination with Hicom after the bulk has been done.

03/06: Dig out the template/ticket and the script for refinement. (@David Short (Unlicensed) @Ashley Ransoo)

15/06: Template in TIS format expected by end today.

29/06: Sue: Hicom sent over a national list (personTISIds) of ~18 approvers to exclude in the merge. (Based on anyone that has ever approved a study leave application). Some coordination with Hicom for these records. The list on Slack to go first iteration excluding these 18 records. Second iteration look at the Hicom coord. ones. Sue to check against the master file.

29/06: Received and ticket updated.

15/07:

South - sent findings on merged records of duplicate Trainees where duplicate trainee record has leave application records

Open

27/07:

Iteration 1: Exclude Hicom Records

https://hee-tis.atlassian.net/browse/TISNEW-4705

Iteration 2: Hicom records only

https://hee-tis.atlassian.net/browse/TISNEW-4871

Include the 38 records to Round 2 - Iteration 2 (Hicom coordinated)

21/09: There are still some duplicates being encountered. Re-run DS script to identify?

Ticket to retrospectively set the training status - ?

06/10: Deduplication Round 3 -

Open

Rules for Round 3 (TBD): (Regional Leads to take it away and look at the above before coming up with a set of Rules as a group)

• Leave manager might take longer

• @David Short (Unlicensed) to amend scenario 4 to remove SFB default merge and rerun count

• 16/11:

Open

Do the Round 3 split into 1000, followed by the hicom records.

14/12: Sue has reviewed this but will be picked up in the new year.

11/01:

https://healtheducationengland-my.sharepoint.com/personal/sue_barrand_hee_nhs_uk/_layouts/15/AccessDenied.aspx?Source=https%3A%2F%2Fhealtheducationengland-my.sharepoint.com%2Fpersonal%2Fsue_barrand_hee_nhs_uk%2FDocuments%2FWork in Progress%2FWork in Progress - Sue%2FData Leads%2FDuplicate Person records on TIS%2F27.11.2020%2FDeDup3.xlsx%3Fweb%3D1%26CT%3D1610365865477%26OR%3DOutlook-Attachments%26CID%3D3DAF47FC-C53D-4693-BC23-1BA7DF1CB05B%26wdLOR%3Dc10F86DBC-38A1-497A-8AFA-F036C59C7557&correlation=5e37a09f-206b-2000-59b2-7e5623befefc&Type=item&name=97d9aad2-370a-4a31-b760-c936ebc525be&listItemId=221480

Open

Open

11/01:

• Suggestion is to wait until Accent Leave Manager migration, although that would mean re-running the script then.

• Archiving work to follow.

2

Identification and removal of Consultant data on TIS

All

Removal of consultant data has a dependency on De-duplication work.

22/10: Dependency on the De-dupe work. Follow up with Chris after the de-dupe work has taken place. This is to identify which Consultant data belongs to which Trust so that they can have this if the trust wants.

29/10: Linked to the removal of the consultant information from TIS we also need to address the issue of the non-trainee posts. So a further piece of work needs to take place to identify and hard delete these posts. Will need to take a copy of this so that (like with the non-trainee records) we can pass this to the trusts if needed. JW to create ticket related to the deletion of posts.

19/11 - Once de-dupe is done, check the full list of People from Chris. Please review against the surviving records (IDs) onTISNEW-2057.

14/01: Wait until the testing of the de-dupe has been done on stage then carry on.- 

04/02: Awaiting on DS on testing outcome for TISNEW-2057 to prioritise TISNEW-2083. Does the script attached to 2083 has everything? 

Check with Rob about urgency of this re IG - JW to do this

01/04: to check with Rob/Ben.

15/04: response from Rob 'From an IG perspective - of we don't need it then we should not keep it.'

Supervisors and Leave approvers taken into account on Chris's script.

24/06: The South has undertaken a post audit and set to Inactive Consultant/non current posts which has resulted in a reduction of approx. 2000 posts

Suggestion that this is something that the other three regions can do while the Person de-dup is in the process of being completed.

Suspect most of the Consultant posts in the North and Mids and East are now inactive meaning that the majority of outstanding posts belong to KSS (possibly)

Once this has been completed for Posts they can remain as Inactive rather than undertake another unnecessary step.

22/07: Still waiting on the de-duplication work. To discuss about Supervisors after this piece of work.

17/02: Intrepid/Leave Manager roles to be sorted out ahead of de-dupe of consultants on TIS

20/04:

• Aim for May 2020.

• Action: Investigating Chris’s script, check for any leave manager records.

04/05: Report has been amended - Query saved on Data warehouse. Exclude list of Approvers before removing.

21/05: Update from the south and EM.

03/06: Waiting on Hicom.

• https://hee-tis.atlassian.net/browse/TISNEW-2083

29/06: Sue: Hicom sent over a national list (personTISIds) of ~38 approvers to exclude in the merge. (Based on anyone that has ever approved a study leave application). Some coordination with Hicom for these records. The list on Slack to go first iteration excluding these 18 records. Second iteration look at the Hicom coord. ones. Sue to check against the master file.

19/10:

Script for consultant data updated:

https://healtheducationengland.sharepoint.com/TIS/Shared Documents/Forms/AllItems.aspx?id=%2FTIS%2FShared Documents%2FJ. Data and Information%2FData Quality%2FNon Training Placements and Programmes.sql&parent=%2FTIS%2FShared Documents%2FJ. Data and Information%2FData Quality&p=true&originalPath=aHR0cHM6Ly9oZWFsdGhlZHVjYXRpb25lbmdsYW5kLnNoYXJlcG9pbnQuY29tLzp1Oi9nL1RJUy9FZTNBa3BqQXFvdEhoRmpBdkZnX04wOEI2WE1HazFrdWhFNXBuMDFMU212VWpnP3J0aW1lPWUxZEdUQ04wMkVn

19/10: Spreadsheet of grades ← James:

https://healtheducationengland.sharepoint.com/:x:/r/TIS/_layouts/15/Doc.aspx?sourcedoc={E10875EF-5AE0-4B22-B77D-4CE1A2484575}&file=IsTrainingGrades for Review RUN 15-10-2020.xlsx&action=default&mobileredirect=true

14/12: Sue has reviewed this but will be picked up in the new year.

11/01:

• Suggestion is to wait until Accent Leave Manager migration, although that would mean re-running the script then.

• Archiving work to follow.

On hold until Point 1 has been completed to the group's satisfaction for People area

3

Sites created with Trust code. Trust codes are not unique. 

01/03/2019: Tom De Salis:

Cleansing the NTS data revealed the fact that 3 of our CCGs merged into a super-CCG last year, which means their sites have the wrong Trust/Trust code on TIS. This is an issue on both the Site reference table and on all the posts related to these sites. It affects around 100 sites.

1. Do we know when the post update tool might be available to update the posts? Is it worth waiting?

2. Would we be able to do an update on the back-end to the site reference table?

AR:  Site is linked to Trust via the TrustId. When creating a Site, a valid Trust Code is entered and the Trust ID is fetched from the Trust reference table to attach to the Site. 

However, since the Trust Code is not currently unique in the Trust reference table, the site might be linked to incorrect Trusts.

E.g. Trust Code RQ3:

Trust Code VTS:

Are these sites linked to the correct trusts?

01/04: Check if the FE is displaying INACTIVE values when creating site. Need to understand what the problem is.

to speak to Tom de Salis/invite him to the next data leads call . 

• Raise a ticket to get the Trust search behave as the Site Search.

• list posted on DQ channel to be discussed.

15/04:

• Identify duplicate Sites (Site codes not unique) in Site Reference Table- 

• Identify duplicate Trusts codes in Trust reference table

• Identify association between Current Sites to Inactive Trusts

• Each Region data Lead to review on DQ channel.

24/06: Regions to act on report created by Chris. Not for immediate attention due to the current work ongoing in Local Offices.

19/08: Ongoing

07/11/2019 Similar issue raised Lynn Bradley team https://hee-tis.atlassian.net/browse/TISNEW-3571 another ticket raised for all duplicate trust code https://hee-tis.atlassian.net/browse/TISNEW-3577

• Mids and East -

• South -

• North - 

• London - 

11/11: Post the spreadsheet on DQ channel to sort out the duplicate Trusts codes and ensure 1 only is CURRENT for each code. 

Trusts do not have Local Office boundaries, owner field can be ignored. 

03/02: Inactivate and ensure only unique trust codes are left

• London: In progress, @James Harris /Larissa

• North: In progress @Liam.Lofthouse (Unlicensed)

• South:

• Midlands and East:

20/04: Check if there are still records on metabase.@Ashley Ransoo / @James Harris

https://build.tis.nhs.uk/metabase/question/316

15/07: Sites and posts linked to the trusts that remain in the above query.

24/08/2020: Still same number of duplicate trust codes. Can someone take the lead to finalise the last few?

07/09/2020: DS: Having run some queries, quite a few don't seem to be associated with any current posts or, current/future post funding episodes. I'd recommend the ones highlighted in yellow be made inactive and this will eliminate the bulk of the duplicates. Happy to speak about it this afternoon on the call.

Inactivate the highlighted ones.

Open

21/09: Trust merger issue.

@David Short (Unlicensed) - send some examples of updating Sites.

select * from Site where trustId in (130,735,2003)

Validation of Site table - ticket - https://hee-tis.atlassian.net/browse/TISNEW-3577

05/10: AR: Unable to replicate with Site ID = 76 with Trust Id 735

• Ticket up the updating of Trust linked to a Site (Updating the Trust Code is not updating the Trust ID) - Ideally show the trust dropdown to select from

  • https://hee-tis.atlassian.net/browse/TISNEW-5573

  • https://hee-tis.atlassian.net/browse/TISNEW-3870

• Metabase query to sort out where Trust code does not match Trust ID on Sites @Sebastian Kwok (Unlicensed) / @James Harris
  https://build.tis.nhs.uk/metabase/question/347

• Another query to check trainees placements where those incorrect combination of site and trust code/trust id have been used.@Sebastian Kwok (Unlicensed)
  https://build.tis.nhs.uk/metabase/question/350

Not for immediate attention as priority in LOs is August intake and ARCPS

4

Programmes and rotations on TIS which are current but do not seem to being used and need to be inactivated.

All

https://build.tis.nhs.uk/metabase/question/320
https://build.tis.nhs.uk/metabase/question/321

20/05: GW suggestion to look at what GMC approved programmes are per site before inactivating

03/06:

15/07:

• M&E: Programmes with no approved sites to be identified,

• South: All blank rotations deleted/inactivated

• London/KSS: Larissa/Chris/Maxine

Open

• North: David to Check with YH

  Impact of inactivating Rotations associated with Programmes on Posts. Rotations are associated to Programmes and to Posts.

15/06:

We will need to supply a list of Programmes to Hicom to add to Oriel as part of Oriel 2 development to facilitate the ingestion of trainees between the Oriel and TIS.

02/11 - Inactivate Rotations. @Ashley Ransoo @James Harris

• @Maxine Owen (Unlicensed) to provide a list of Rotations to inactivate.

16/11:

Need to check dev time to inactivate those in bulk.

14/12:@Ashley Ransoo create a ticket and pick up in the new year to bulk inactivate the rotations.

11/01/2021: https://hee-tis.atlassian.net/browse/TIS21-1007

5

Issues around unique site codes

All

Branch sites.docx

09/12:

• Are Site Code and Site number the same for all site records - report?

• Site code is not unique on TIS - should this be?

• Can/Should one of  Site code or Site number be unique? Therefore serve the purpose of branch sites for recording the branch code

• An investigation into the ODS list, to get the ODS list into the NDW.

16/12:

Further discussions on #dataleads

• Suggestion is to record the branch code in the Site Number field and this to be unique. The Site Code will be duplicated where the parent site has multiple branch codes/Site numbers in the Site reference table.

• Ticket to add validation to TIS to make Site Number and SiteKnownAs unique once data leads have done a tidying-up of the site numbers and SiteKnownAs. https://hee-tis.atlassian.net/browse/TISNEW-3713

• A meaningful name is needed for Site Number or Site Code so that it is clear to users when recording Sites. 

  • 274 duplicate SiteNumbers https://build.tis.nhs.uk/metabase/question/298

  • 28 duplicate SiteKnownAs https://build.tis.nhs.uk/metabase/question/299

• There are only 49 sites where there is a mismatch between the two fields (Site number and Site Code) and of those a good chunk on is null. 

  • https://build.tis.nhs.uk/metabase/question/297

• SiteKnownAs will be different for each Branch Site where the parent code is the same and branch code is different, therefore no impact on TIS.

• Raise ticket to add SiteCode to TIS_Interim and send to NDW. https://hee-tis.atlassian.net/browse/TISNEW-3714 Add Site

• 20/04: Code to TIS_Interim and send to NDW.Add SiteCode to TIS_Interim and send to NDW.

20/04: Will be discussed next at PAG.

04/05: Site code added to NDW.

• Is the branch code in the dataset from the API? - @James Harris to do some mining. (Waiting on NDW)

27/07: Parked for now. Data warehouse/NDW going through major re-design. Find out ETA.

11/01: James discussing with JT this afternoon.

6

Adding/Amending Trusts - Mergers in the South

Sue/All

https://hee-tis.atlassian.net/browse/TISNEW-4721

• If the above ticket is not done in time, we’ll need a manual refresh in UM scheduled with devs.

Programme in UM - Refreshed as and when a change is made.

27/07: Not merged yet. @Caroline Grisdale / @Sue Barrand (Unlicensed) to give us the heads up.

21/09: Waiting on the new ESR code to be rolled out before doing the Trust mergers.

02/11: Post ARCP return

14/12: Will be picked up in the new year. @Mike.Richardson (Unlicensed) / @James Harris / @Gwilym Williams (Unlicensed) - Make this available in the NDW.

11/01: This has been postponed to March 2021 before August placements are put in. (South/East). UM List will need to be refreshed once added to Reference.

7

Current Trainees presently on an Inactive Curriculum

All

There are 1198 current trainees presently on an inactive curriculum.
https://build.tis.nhs.uk/metabase/question/343

Broken down across the various local offices, the majority belong to LDN and KSS (1062), followed by NW (112)
https://build.tis.nhs.uk/metabase/question/345

02/11: In Progress.

25/01: Closed

Closed

8

Placement.SubSpecialty

James/All

• Placement Sub Specialties not being sent to NDW, but only Placement Other Specialty - https://hee-tis.atlassian.net/browse/TISNEW-5857

• Posts Sub Specialties are recorded under Post Sub Specialties instead of Post Other Specialty

• Future work: Posts that have Sub specialties put into the Other Specialties field to be tidied up.

9

Post Funding with no end dates but are INACTIVE

James/All

• PAG reporting breaks ~ 6500 records

• Individual Posts
  https://build.tis.nhs.uk/metabase/question/352Count by local office

• Count by local office
  https://build.tis.nhs.uk/metabase/question/353

Use the Bulk Post Funding Update tool to update them. (Note: by TIS_PostFunding_ID* not PostID)

Impact on leave manager? - @Mike.Richardson (Unlicensed) to check.

Check impact with ESR on Inactive Posts - @Ashley Ransoo / @James Harris

11/01: Post Status of CURRENT only are reconciled and placements exported against.

• If Post Funding does not become active until at a point in Future, the status of the post has to be CURRENT 13 weeks ahead of the placement start date for them to be exported. May need to change the rule on the interface to still export the placement regardless of the post status in this case. @Ashley Ransoo / @James Harris to discuss with @Joseph (Pepe) Kelly .

• OR, when a CURRENT/FUTURE placement is added to an Inactive Post, perhaps the post needs to be made CURRENT before being able to do so. (Query to check)

10

Trust coding in TIS

James/All

from Kate Parkyn (NHS Digital):

We’ve had something TIS-related flagged up us by our sister team who work on the hospital/trust side of workforce data. It’s not explicitly related to your side of the TIS data, more the inputting I think.

But hopefully you can help with untangling it for the future, or at least might know the specifics on who might need informing about it.

It seems the ODS code = RYK has been reused for a new organisation, a circumstance which is living up to its potential for causing some issues.

The original use of the code ended when Dudley and Walsall Mental Health Partnership NHS Trust (RYK) and Black Country Partnership NHS Foundation Trust (TAJ) merged as at 1 April 2020 to become Black Country Healthcare NHS Foundation Trust (TAJ): https://www.bcpft.nhs.uk/

But a new organisation has also come into being: Dudley Integrated Health and Care NHS Trust is re-using the ODS code = RYK.

In terms of coding, this has been worked through on the Secondary Care side and the latest situation appears to be that:

Staff at Dudley and Walsall Mental Health Partnership NHS Trust (RYK) should now be coded at TAJ

Staff at Dudley Integrated Health and Care NHS Trust (RYK) should be coded at RYK

However it’s been flagged up (by St Helens and Knowsley Teaching Hospitals NHS Trust I believe) that reference is still being made to the old Dudley and Walsall Mental Health Partnership NHS Trust (RYK) in the TIS data.

It sounds like St Helens and Knowsley will be raising this through their usual channels where they catch up with HEE, but it seemed worth coming at it from our side too in case the information correction can be expediated that way.

14/12: Caroline/Gwil: There’s been a trust merge in EOE, will check.

11/01: Raise with Kate Parkyn (NHS Digital)

Closed

11

TIS ID’s v.s. UUIDs (Unique identifiers across systems NI and HEE)

Dev/all

• Impact on Leave Manager/Accent

• Dependencies on reporting

• Preference is to have UUID as an addition rather than replacement to TIS IDs

12

Redeploying OOPs

All/Sarah

• Awaiting BM’s decision on this

• 25/01:

London -
South -
East -
North West -

Note: There is a spreadsheet that is completed by Trusts and not sure where this goes afterwards, not on TIS?

13

New ESR Interface data to NDW for reporting

All

• Ticket to send MongoDB to NDW?

• https://hee-tis.atlassian.net/browse/TIS21-224

14

NTN Generation for NI to take into account LAT.

Mark/Gerard

Closed

15

Dental NTN and Dental NPN

All

Being finalised by BMs, Marc.

16

Programme codes to be updated on TIS

Liam/All

• Programme update - Programme TIS IDs and New Programme Number

• Programme create - All TIS mandatory fields for a programme (c.f. TIS UI)