1

Upload RMC, APC and DCC files (as CSV files) to AWS S3 Bucket

ESR Deanery

Deanery have access to our S3 bucket to upload these files into ESR folder in the bucket.

RMC, PAC and DCC fields in our Confluence page. ESR - Datasets (Historical)

We get daily notification of the uploaded files in Slack (#esr-emails):

APC file at around 07:12

RMC file at around 14:09

DCC file at around 15:12

Is upload done daily by the deanery? @Joseph (Pepe) Kelly @Jayanta Saha

The flow of APP,APC, DNC, DCC and RMC files

1. ESR receives APP files (Applicant files) from TIS and ESR send TIS a confirmation of success/failure of the file - APC file after they have reconciled the file with theirs

2. ESR receives Notification of changes in TIS in form of DNC files and ESR send TIS a confirmation of success/failure of the files - DCC after they have reconciled the file with theirs

3. ESR send TIS RMC file of new/updated positions and new/updated applicant details via RMC. TIS  then reconcile the change and send as APP file to ESR

2

Receive Files in the ESR folder in the S3 bucket

TIS /ESR Interface

3

Do Nothing (This is a future work)

TIS /ESR Interface

APC and DCC we receive from the Deanery into the S3 bucket are currently not in use. We do nothing with the files. Future work is to decide what to do with these files. We currently use the RMC in the reconciliation to generate the APP file that goes to ESR.

4

Retrieve RMC File in the ESR folder in the S3  bucket

TIS /ESR Interface

5

Split RMC file into  position Records

TIS /ESR Interface

?Does it split into Position Records? @Joseph (Pepe) Kelly @Jayanta Saha

6

Publish position records  in the Rabbit Message queue

TIS /ESR Interface

7

Reconcile the ESR position with TIS post and save in the Mongo DB

TIS /ESR Interface

Reconciliation is about matching ESR position with TIS post.

ESR Reconciliation Positions Fields in Metabase
Question · Metabase (tis.nhs.uk)

Our AWS service will match the position number from ESR with Post number from TIS.  For matched position/post, our AWS service will then check applicant details/placement against TIS (Post) and ESR (position). Any gap will then be updated in the applicant records.

8

Publish the reconciliations  in the Rabbit Message queue

TIS /ESR Interface

9

Generate Application Record using the App Generator

TIS /ESR Interface

? Question · Metabase (tis.nhs.uk) is this the app record? @Joseph (Pepe) Kelly @Jayanta Saha

10

Publish Application and Notification Records in the Rabbit Message queue

TIS /ESR Interface

11

Data exporter Hold on to the following records;

a. App Records until 11am UTC/GMT

b. Notification until 12 noon UTC/GMT

TIS /ESR Interface

ESRExporter - Generated App Records Fileds in the metabase Question · Metabase (tis.nhs.uk)

ESRExporter - Generated App Notification Record Fileds in the metabase Question · Metabase (tis.nhs.uk)

Records is changed into CSV files in the data exporter

• Applicant file is created at 11am UTC/GMT

• Notification file is created at 12 noon UTC/GMT

12

Create the Applicant file 

TIS /ESR Interface

Applicant file is created at 11am UTC/GMT - csv file

13

Create the Notification file 

TIS /ESR Interface

Notification file is created at 12 noon UTC/GMT- csv file

14

Save files in the TIS folder in the S3 bucket 

TIS /ESR Interface

? Where is the TIS folder? @Joseph (Pepe) Kelly @Jayanta Saha

15

Retrieve files in the TIS folder in the S3 bucket 

ESR/NHS Hub

1a

Make change in TIS service

TIS Service

Make either of the following changes in TIS service:

1. Create new placement for a future post

2. Update existing placement

3. Add new trainee/starter

4. Update existing trainee details

2a

Reflect Update in Mysql and CdcRabbitRouter

TIS /ESR Interface

The changes/update made in 1a are reflected in Mysql and CdcRabbitRouter

3a

Publish Update in the Rabbit Message queue from CdcRabbitRouter

TIS /ESR Interface

4a

 Reconcile  the update and save in  Mongo DB

TIS /ESR Interface

The reconciliation of the change/update is Reconcile with what? @Joseph (Pepe) Kelly @Jayanta Saha

Identify when a Deanery Post Number has been allocated or changed against an ESR Position – ‘C’ – or removed/deleted entirely from a position record – ‘D’

NOTE: If a position is end dated in ESR or altered to any status other than ‘active’ the ‘D’ or delete indicator will be displayed on this record.

5a

Generate Notification using the Notification Generator

TIS /ESR Interface

ESRExporter - Generated App Notification Record Fileds in the metabase Question · Metabase (tis.nhs.uk)