ESR Schedules

Description of new scheduling considerations

Previously scheduling was a key consideration as there were a number of timed jobs which exchanged files and kicked off processing to check for files. This was needed for a number of reasons as the queries for producing files were time dependent, the N3 bridge didnt accept incoming connections so it had to pass files out at specific times and check in the blob storage to see if a file had been recieved. The new code has three fundamental changes which make the timings less of a consideration:

The SFTP server is the S3 blob storage and the act of files coming into the bucket kicks off processing (Event driven)
The generation of information (App and Notification records) is done ahead of time, so there is less of a time pressure to run large jobs in one day and up to date data is available in the exporter service (Load spreading)
The process of information generation is continuous and exporting is time boxed, This results in any generation which is not complete before an export run being available at the next export opportunity. (Export time depenendencies largely decoupled)

This means we are no longer restricted by when ESR delivers files and our exporting process becomes less time critical. The current timings are 2pm for applicant export and 3 pm for notification export, but these can be tweaked as needed.

Below is some detailed information from the initial codes timings, which is a useful reference for when some of ESR's processes run.

Visual Timeline of the schedules (Historical)

Schedules - Files exchanged between ESR and TIS in the interface

(Historical)

This table outlines the schedules for the regular jobs that runs on the TIS-ESR interface.

All times are specified in the table below are in UK Current time, i.e. London/EU Timezone.

Please note all Jenkin jobs on TIS are currently set to use UTC time except the ESR-ETL jobs. The following tickets were raised to accommodate for the winter/summer time changes to ensure ESR-ETL always run on UK Current time.

TISDEV-4890 - Getting issue details... STATUS

TISNEW-2262 - Getting issue details... STATUS

ESR have confirmed their schedules are in UK Current time.

File Name	Abbrev.	Description	Creator	File name convention	Schedule (incl. weekends and bank hols. and are all in ~~BST, i.e.~~ UK Current Time) Blue - ESR-ETL schedules Green - FTP Synch schedules	Jenkins job (Automatic and their manual equivalent to run in case of failure)
Reconciliation and Medical Trainee Outbound interface (RMT)	RMF	Deanery Reconciliation and Medical Trainee Full File received on Day 1. A request has to be made by TIS if another one is needed.	ESR	DE_DBY_xxx_YYYYMMDD_99999999.DAT DE: Constant ‘DE’ to denote a Deanery Interface File DBY: The Deanery/HEE Local Office (former Deanery Body) Identifier xxx: Constant ‘RMF’ denoting a Deanery Reconciliation and Medical Trainee Full File. YYYYMMDD: Interface Run Date corresponding to the date the file was created 99999999: Interface Run Sequence Number, incremented for each run DAT: Constant ‘DAT’ extension denoting data file For example for WMD : DE_WMD_RMF_20101126_00001234.DAT	Pav: RMT file will be made available latest by 2:00 in the afternoon. Hence if you schedule your jo to pick this up at this time daily this should be ok. In the past it used to be the morning but that has changed. The process to produce the RMF/RMC files runs daily (inc. weekend and bank holidays) at 1pm and generally takes about 40 mins, so files usually will be available for download by 2pm. The data is ‘up to midnight on the previous day’ but if ESR itself is not ‘up to this date’ the extract will be held until the data is available. ESR is often subject to weekend/bank holiday downtime. 03/05 VH: Process runs about 2.30p.m on ESR. TIS to run a daily scheduled job at~~14:00~~14h45 and 15h15 to pick up RMT files sent by ESR. (Download) TIS to load the RMC/RMF files daily at~~15:00~~.15:30 - load-type=applicant-load	Automatic at 14:45 and 15:15: Run on N3 Box Manual equivalent of 14:45 and 15:15: Run on N3 Box Automatic at 15:30: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-applicant-load Manual equivalent of 15:30: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-applicant-load
Deanery Reconciliation and Medical Trainee Changes File (RMT)	RMC	Denoting Deanery Reconciliation and Medical Trainee Changes File. Only changes sent subsequent to RMF. (deltas to the RMF)	ESR	DE_DBY_xxx_YYYYMMDD_99999999.DAT DE: Constant ‘DE’ to denote a Deanery Interface File DBY: The Deanery/HEE Local Office (former Deanery Body) Identifier xxx: Constant ‘RMC’ Denoting Deanery Reconciliation and Medical Trainee Changes File YYYYMMDD: Interface Run Date corresponding to the date the file was created 99999999: Interface Run Sequence Number, incremented for each run DAT: Constant ‘DAT’ extension denoting data file For example for WMD : DE_WMD_RMC_20101126_00001234.DAT	TIS to run a daily scheduled job at~~14:00~~14h45 and 15h15to pick up RMT files sent by ESR. (Download) TIS to load the RMC/RMF files daily at~~15:00~~.15:30 - load-type=applicant-load	Automatic at 15:30: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-applicant-load Manual equivalent: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-applicant-load
Reconciliation and Trainee Information Confirmation File (RTC)	RTC	TIS confirms that the RMT file were received with details of success or error to ESR.	TIS	DE_DBY_RTC_YYYYMMDD_99999999.DAT DE: Constant ‘DE’ to denote a Deanery Interface File DBY: The Deanery/HEE Local Office (former Deanery Body) Identifier RTC: Constant ‘RTC’ denoting a Deanery Reconciliation and Medical Trainee Confirmation File. YYYYMMDD: Interface Run Date corresponding to the date the file was created (will match the corresponding UPD file) 99999999: Interface Run Sequence Number, incremented for each run (will match the corresponding UPD file) DAT: Constant ‘DAT’ extension denoting data file For example: DE_EOE_RTC_20180305_00000237.DAT	Triggered on completion of processing of the RMT file. Generally sent after midday and before 5p.m. Any time is acceptable by ESR. TIS to gnerate the RTC files daily at 15:30 - load-type=applicant-load TIS to send the RTC file/s daily at 18:00 to ESR. (Upload)	Automatic to generate: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-applicant-load Automatic to upload: Run on N3 Box. Manual equivalent to generate: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-applicant-load
Applicant (Export) File	APP	Matches trainees and placements in TIS and returns them to ESR.	TIS	DE_EOE_APP_20180305_00000240.DAT DE: Constant ‘DE’ to denote a Deanery Interface File DBY: The Deanery/HEE Local Office (former Deanery Body) Identifier APP: Constant ‘APP’ denoting an Applicant Export File YYYYMMDD: Interface Run Date corresponding to the date the file was created (will match the corresponding UPD file) 99999999: Interface Run Sequence Number, incremented for each run (will match the corresponding UPD file) DAT: Constant ‘DAT’ extension denoting data file For example: DE_EOE_APP_20180305_00000240.DAT	Expected to be received by 19:04 at NHS Hub. TIS to generate the APP file/s daily at 17:00 - load-type=applicant-export TIS to send the APP file/s daily at 18:00 to ESR. (Upload)	Automatic at 16:00: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-cron-applicant-export/ Manual equivalent of 16:00: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-cron-applicant-export/ Automatic at 18:00: Run on N3 Box Manual Equivalent of 18:00: Run on N3 Box
Applicant Confirmation File	APC	Sent to TIS and confirms that the APP were received successfully and list any warning against records created on ESR.	ESR	DE_DBY_APC_YYYYMMDD_99999999.DAT DE: Constant ‘DE’ to denote a Deanery Interface File DBY: The Deanery/HEE Local Office (former Deanery Body) Identifier RTC: Constant ‘RTC’ denoting a Deanery Reconciliation and Medical Trainee Confirmation File. YYYYMMDD: Interface Run Date corresponding to the date the file was created (will match the corresponding UPD file) 99999999: Interface Run Sequence Number, incremented for each run (will match the corresponding UPD file) DAT: Constant ‘DAT’ extension denoting data file For example: DE_EOE_RTC_20080426_00006871.DAT	Following completion of processing of the Applicant or APP file by ESR. This can take up to 2 days to be received. Pav: The final (NHS Hub) job to produce the APC runs hourly at 12 mins past the hour from 07:00 to 19:00 and any APC files returned from ESR will be processed on the next run and placed for collection shortly afterwards. ESR’s processing of APP files is generally completed overnight in time for the 07:12 run. TIS to run a daily scheduled job to pick up APC files at 19:00. (Download) TIS to process the APC file/s daily at 20:00. - load-type=apc-import	Automatic at 19:00 and manual to upload: Run on N3 Box Automatic at 20:00: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-apc-import Manual equivalent of 20:00: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-apc-import
Notification File in Full Mode	DNF	Full Notifications sent to ESR for the corresponding Positions prior to receiving an RMF on Day 1 of switching off the interface. Any future DNF file should follow a manual request process between ESR and TIS before sending.	TIS	DE_ddd_DNF_YYYYMMDD_99999999.DAT DE: The fixed length file type. Define as a constant ‘Deanery Notification’ for the ‘GI’ Interface File Ddd: The fixed length Deanery/HEE Local OfficeLETB (former Deanery Body) Organisation Code. DNF: The fixed length sub file type.‘DNF’ - to denote ‘Full’ notification file. YYYYMMDD: Date corresponding to the date the file was created 99999999: Interface Run Sequence Number, incremented for each file DAT: Constant ‘DAT’ extension denoting data file For example: DE_EOE_DNF_20080426_00006871.DAT	TIS to generate (manual) DNF file after the Applicant Export i.e. after 17:00 . Regular DNC should be disabled for that period and turned back on after the DNF files have been generated. No application schedule. TIS to send the DNF file/s on Day 1 at 18:00 to ESR. (Upload)	Manual only to generate: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-full-notification/ Steps to generate: ESR Full Notification File manual generation process Automatic and manual to upload: Run on N3 Box
Notification File in Change Mode	DNC	Notification File in change mode sent by the ETL on a daily basis automatically.	TIS	DE_ddd_DNC_YYYYMMDD_99999999.DAT DE: The fixed length file type. Define as a constant ‘Deanery Notification’ for the ‘GI’ Interface File Ddd: The fixed length Deanery/HEE Local OfficeLETB (former Deanery Body) Organisation Code. DNC: The fixed length sub file type.‘DNC’ - to denote a ‘Changes’ notification file. YYYYMMDD: Date corresponding to the date the file was created 99999999: Interface Run Sequence Number, incremented for each file DAT: Constant ‘DAT’ extension denoting data file For example: DE_EOE_DNC_20080426_00006871.DAT	TIS to generate auto notifications daily at 06:00. - load-type = notification-daily-load TIS to generate and load TCS Notifications to Azure daily at~~17:00~~17:30. - load-type=notification-daily-export TIS to send the DNC file/s daily at 18:00 to ESR. (Upload)	Automatic at 06:00: https://build.tis.nhs.uk/jenkins/job/tis-esr-cron-notification-daily-load Manual equivalent of 06:00: https://build.tis.nhs.uk/jenkins/job/tis-esr-cron-notification-daily-load Automatic at 17:30: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-cron-daily-export/ Manual equivalent of 17:30: https://build.tis.nhs.uk/jenkins/job/tis-esr-etl-cron-daily-export/ Automatic or Manual equivalent of 18:00: Run on N3 Box
Notification Confirmation File	DCC	It's a confirmation of the DNF or DNC file received and processed by ESR sent to TIS.	ESR	DE_ddd_DCC_YYYYMMDD_99999999.DAT DE: The fixed length file type. Define as a constant ‘Deanery Notification’ for the ‘GI’ Interface File Ddd: The fixed length Deanery/HEE Local OfficeLETB (former Deanery Body) Organisation Code. DCC: The fixed length sub file type.‘DCC’ - to denote a notification confirmation file. YYYYMMDD: Date corresponding to the date the file was created 99999999: Interface Run Sequence Number, incremented for each file DAT: Constant ‘DAT’ extension denoting data file For example: DE_EOE_DCC_20180601_00006871.DAT	TIS to run a daily scheduled job at~~14:00~~14h45 and 15h15 to pick up DCC along with RMT files sent by ESR. (Download)	Automatic downloaded at 14:45 and 15:15: Run on N3 Box Manual equivalent of 14:45 and 15:15: Run on N3 Box Future work to include in the ETL process: TISNEW-2698 - Getting issue details... STATUS

Implementation/Design

(Historical)

Friendly Name (shown above)	ETL JOB (shown below)	Broadest Description
Notifications load	notification-daily-load	Tell TCS to record 'notifications' for 1) Placements starting in 13 weeks and 2) Posts which just became vacant.
Process RMT & RTC files	applicant-load	1) Pull the `Position Info.` and `Position Reconciliation` records from ESR, 2) Get placements from TCS and Create Applicant Records in `esr`.
Process APP files	applicant-export	For each of the following, find the necessary placement details, create the applicant and export: 1) `Position Reconciliation`, 2) `Position Info.`, 3) `EsrNotifications` and 4) `Errored Applicants`.
Process DNC files	notification-daily-export	Get and Export all the Notifications for Applicants.
Process APC files	apc-import	Pull results of ESR attempting to import the Applicant files we have sent them.

ETL Data Sources & Sinks

(Historical)

Image source: