Understanding Extract Synch for Data Futures and Register (ITT)
Once a Student (DF) extract is completed and reported to HESA via a successful XML file, the data set can be copied to the submitted data records. Don't run extract synch until you resolve the errors in the extract data and the extract is submitted to HESA.
For Register, once the Export Trainees process has completed, successfully exported records can be copied to the submitted data records.
The first step of the process is to copy extract records. This creates a full set of new effective-dated/sequenced extract records by copying from the most recent effective-dated rows for all the extract records for the Data Futures (DFSTNDT) and ITT return type. The synch process can run for multiple extracts on the same day so effective sequence is used to track the history of changes.
The Institution extract record is used to find the most recent effective-dated records that need to be copied. The same logic applies to both Data Futures and ITT/Register but different records are copied.
To copy extract records:
Select the most recent effective date and effective sequence from Institution extract for the Institution where Return Type is DFSTDNT or ITT.
Find the new effective date as the current date, and the new effective sequence as the highest existing effective sequence for the current date + 1 (or as 1, if the most recent record is for an earlier date).
Copy the existing Institution extract record using the new date/sequence from step 2.
For each extract entity and field record with return type = DFSTDNT or ITT:
Rows for the existing date/sequence selected in step 1 where the Entity or Field Status isn't Delete are copied to new rows using the new effective date/sequence in step 2.
The Entity Status and Field Status values for all the new records is initially set to Unchanged.
Note: Any rows for earlier effective dates that have been deleted before the most recent record and any records that were marked as Delete in the most recent record aren't copied to the new record.
If there's no existing Institution extract record for the DFSTDNT or ITT return type, the copy extract step is skipped. This will happen the first time the extract synch process is run.
If the extract Entity Status isn't one of the values in Entity Status Inclusion, the entity extract record, any child field records, and any child entities are skipped. These records aren't included in the XML and no updates are required to the corresponding extract records.
If the extract Entity Status is Identify, Delete, Unchanged, or Unused the Entity Status of the corresponding new extract record is updated to the new value. Any child field extract records are skipped. If there's no corresponding submitted data record, the record is skipped.
If the extract Entity Status is any of the values in Entity Status Inclusion (like Amended, Error, or New) and there's a corresponding submitted data record:
The Entity Status of the corresponding new extract record is updated to the extract entity value.
For each field record where the Field Status isn't one of the values in Field Status Inclusion, the field is skipped.
If the field record already exists in submitted data, the Field Status of the submitted data record is updated to the extract field value and the other values (Derived Value, Derived Step, Reported Value, Override Flag) are also updated.
If the field record doesn't already exist in submitted data, a new submitted data record is created using the new date/sequence and the Field Status is set to the extract field value.
If the extract Entity Status is any of the values in Entity Status Inclusion and there's no corresponding submitted data record, a new entity record is created in submitted data using the new date/sequence with Entity Status of the extract entity value. For each child field record where the Field Status is not one of the values in Field Status Inclusion, the field is skipped. A new submitted data field record is created with the same date/sequence as the parent entity and Field Status is set to the extract field value.
For Register, the Institution processing is similar to Data Futures. But the logic references the Export Status to identify which records to move for Student, Degree and Placement entities
Institution
The Institution records don't include Entity Status so the process handles creating or updating depending on the existing MST records. The Institution Extract record is processed ahead of any other entities.
If there's no existing Institution Submitted Data record for the combination Institution and Return Type = ITT, a new record is created and the following fields are populated from the extract record.
Effective Date = current date
Effective Sequence = 1
Return Name
Reporting Period
Record ID
The remaining fields are blank.
If a new Institution Submitted Data record was created when copying submitted data records, that record is updated with the following values from the extract record:
Return Name
Reporting Period
Record ID
The remaining values are unchanged.
Student
The synch processing references Export Status and doesn't have the configuration of which status values to sync. Only records with an Export Status of Success are synced. The Export Trainees process only considers extract records with an Entity Status or Field Status of New or Amended.
If the Student entity extract record has Export Status of Success and there's an existing record in the most recent submitted data record (matched on Institution and SID):
These values are updated: Entity Status, Trainee ID, Export Status, Register Updated, Register Status, Register Source.
For each Student field extract record with Field Status of New or Amended:
If there's a corresponding submitted data fields record, these values are updated: Derived Value, Derivation Step, Reported Value, Derived Value Overriden, Field Status.
Otherwise, a new submitted data fields record is created.
If the Student entity extract record has Export Status of Success and there isn't an existing record in the most recent submitted data record:
A new record is created from the extract entity record.
For each Student field extract record with Field Status of New or Amended, a new submitted data fields record is created.
Degree
If the Degree entity extract record has Export Status of Success and there's an existing record in the most recent submitted data record (matched on Institution and SID):
These values are updated: Entity Status, Degree ID, Export Status, Register Updated.
For each Placement field extract record with Field Status of New or Amended:
If there's a corresponding submitted data fields record, these values are updated: Derived Value, Derivation Step, Reported Value, Derived Value Overriden, Field Status.
Otherwise, a new submitted data fields record is created.
If the Degree entity extract record has Export Status of Success and there isn't an existing record in the most recent submitted data record:
A new record is created from the extract entity record.
For each Degree field extract record with Field Status of New or Amended, a new submitted data fields record is created.
Placement
If the Placement entity extract record has Export Status of Success and there's an existing record in the most recent submitted data record (matched on Institution, SID, School URN, Placement Name, and Start Date):
These values are updated: Entity Status, Degree ID, Export Status, Register Updated.
For each Placement field extract record with Field Status of New or Amended:
If there's a corresponding submitted data fields record, these values are updated: Derived Value, Derivation Step, Reported Value, Derived Value Overriden, Field Status.
Otherwise, a new submitted data fields record is created.
If the Placement entity extract record has Export Status of Success and there isn't an existing record in the most recent submitted data record:
A new record is created from the extract entity record.
For each Placement field extract record with Field Status of New or Amended, a new submitted data fields record is created.
Active entity extract records are deleted along with related child field extract records if the conditions outlined here are true.
The extract Entity Status is one of the values in Entity Status Inclusion and where the Synch checkbox is selected.
All the field extract records have a Field Status that's either blank or is one of the values in Field Status Inclusion and where the Synch checkbox is selected.
Any child entities have an Entity Status that's one of the values in Entity Status Inclusion and where the Synch checkbox is selected.
Any child entity field extract records have a Field Status that's either blank or is one of the values in Field Status Inclusion and where the Synch checkbox selected
Note: This doesn't check whether the entity exists in submitted data to allow Unused entities that haven't been copied to submitted data to be deleted.
The Institution Extract record is not deleted.
Active entity extract records for Student, Placements and Degrees are deleted along with related child field extract records if any one of the conditions here are true.
If Export Status is Success or Obsolete or Entity Status is Unchanged.