Data Futures Return: Student Entity

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Include for all entities

Derivation Steps:

1. Derive Date of Birth in the format YYYY-MM-DD.

2. Use default.

3. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if all conditions are met:

• Country = Scotland

• EntryProfile.PERMADDCOUNTRY = XH; if a value isn't found in the extract, the value is selected from the most recent submitted data record. If there's no value in the submitted data, a value is derived without reference to the field derivation rule.

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

Derivation Steps:

1. Derive value from the most recent effective dated Person HESA Data where the effective date is on or before the reporting period end date.

2. Use default.

3. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if any one of these sets of conditions is met:

• First set:

  • EntryProfile.PERMADDCOUNTRY = XF, XG. XH, XI, XK, XL, GG, JE, or IM; if a value isn't found in the extract, the value is selected from the most recent submitted data record. If there's no value in the submitted data, a value is derived without reference to the field derivation rule.

  • There is an Engagement entity in the extract for the student where INCOMINGEXCHANGE is null.

  • Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

• Second set:

  • Country = England

  • An Engagement entity exists for the student in the extract where the related Course.TTCIDC = 01.

  • There is an Engagement entity in the extract for the student where INCOMINGEXCHANGE is null.

  • Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

If all the Engagements for the student are subsequently flagged as dormant, the derived value is updated to null and the derivation step set to zero.

Derivation Steps:

1. Derive value from Person HESA Data.

2. Select Person Ethnicity records if:

   • A single record exists that record is used.

   • Multiple records exist and one if flagged as Primary that record is used.

   • Multiple records exists and none are flagged as primary, the record with the highest Percentage defined under Ethnicity Detail is used.

   • There is no way to distinguish between multiple records, the most recent record based on last updated date (LASTUPDDTTM from ETHNICITY_DTL) is used.

   For the selected record, map the Ethnic Group and Regulatory Region to a HESA code using Ethnic Mapping. If no mapping exists, log an error message and skip to next step.

3. Use default.

4. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if there is an Engagement entity in the extract for the student where INCOMINGEXCHANGE is null.

Derivation Steps:

1. Derive the First Name and Middle Name values of the most recent effective-dated Primary name record that falls within the reporting period.

   The maximum length of the derived and reported values in HESA extract data is 60 characters, so if all 30 characters have been used for both First and Middle Name, then the final character of Middle Name will be dropped to allow a space between First and Middle Name elements.

2. Use default.

3. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if these conditions are met:

• Country = England or Northern Ireland

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

Derivation Step:

1. Derive from the most recent effective-dated Person HESA Data, where the effective date is on or before the reporting period end date.

2. Use default.

3. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

If all the Engagements for the student are subsequently flagged as dormant, update the derived value to null then set the derivation step to zero.

Derivation Steps:

1. Derive from the most recent effective-dated Person HESA Data, where the effective date is on or before the reporting period end date.

2. Select Person Citizenship records and pick up the related two-character codes (COUNTRY_2CHAR) from the Country table (COUNTRY_TBL). If a mapping exists in the HESA Nationality Mapping for the CS Country, use the mapped HESA value. Otherwise, use the COUNTRY_2CHAR value.

   If a single record is found,derive the CS country or HESA country value.

3. Select Person Citizenship records (as in step 2). If multiple records are found and one has a value of ‘GB’, derive that value.

4. Select Person Citizenship records (as in step 2). If multiple records are found, none are ‘GB’ but at least one is flagged as European Union country, then derive the EU country.

   If there are multiple EU countries, report the one with the lowest alpha HESA code.

   Note: The COUNTRY_TBL contains a flag (EU_MEMBER_STATE) to indicate if the country is a member or the EU.

5. Select Person Citizenship records (as in step 2). If multiple records are found and none are UK or EU, select the lowest alpha code.

6. Use default.

7. If the Country is England or Scotland, derive as NULL ERROR.

8. Derive as null.

Return: Data Futures

Entity: Student

Pages Used: None

Field Derivation Rule: Include for all entities.

Derivation Step: Derive as EMPLID.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if these conditions are met:

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

• All related CourseInitiative entities for Engagement entities for the student in the extract have COURSEINITID other than 027.

• There are no StudentInitiative entities for the student in the extract with STUINITID of 027 or 030.

Derivation Steps:

1. Derive from the most recent effective-dated Person HESA Data, where the effective date is on or before the reporting period end date.

2. Derive Religious Preference for the person mapped to a HESA code using HESA Religion Mapping. If the value is not mapped, log an error then skip to the next step.

3. Use default.

4. If Country = England, derive as NULL ERROR.

5. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if one of the following set of conditions is met:

• Country = Scotland.

• Entry Profile.PERMADDCOUNTRY = XH (Scotland); if a value isn't found in the extract, the value is selected from the most recent submitted data record. If there's no value in the submitted data, a value is derived without reference to the field derivation rule.

• There is an Engagement entity in the extract related via COURSEID to a Qualification with QUALCAT beginning with H, I, J or C

• Country = England or Northern Ireland.

• EntryProfile.PERMADDCOUNTRY = XH (Scotland) and EntryProfile.UCASSCHEMECODE isn't null; if a value isn't found in the extract, the value is selected from the most recent submitted data record. If no value is found in submitted data, a value is derived without reference to the field derivation rule.

• There's an Engagement entity in the extract related via COURSEID to a Qualification with QUALCAT beginning with H, I, J or C.

Derivation Steps:

1. Select External System ID records for the person with the External ID Type defined for SCN in the HESA Return Setup record. Value should not be 999999999.

2. Use default.

3. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if all conditions are met:

• Country = Scotland

• Entry Profile.PERMADDCOUNTRY = XH (Scotland); if a value isn't found in the extract, the value is selected from the most recent submitted data record. If there's no value in the submitted data, a value is derived without reference to the field derivation rule.

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

• All related CourseInitiative entities for Engagement entities for the student in the extract have COURSEINITID other than 027.

• There are no StudentInitiative entities for the student in the extract with STUINITID of 027 or 030.

Derivation Steps:

1. Derive from the most recent effective-dated Person HESA Data, where the effective date is on or before the reporting period end date.

2. Use default.

3. Derive as NULL ERROR if there is an Engagement entity in the extract related via COURSEID to a Qualification with QUALCAT beginning with H, I, J, or C, or M0002, M0016 or M0018.

4. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if all conditions are met:

• Country = Scotland

• Entry Profile PERMADDCOUNTRY = XH (Scotland); if a value isn't found in the extract, the value is selected from the most recent submitted data record. If there's no value in the submitted data, a value is derived without reference to the field derivation rule.

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

• All related CourseInitiative entities for Engagement entities for the student in the extract have COURSEINITID other than 027.

• There are no StudentInitiative entities for the student in the extract with STUINITID of 027 or 030.

Derivation Steps:

1. Derive from the most recent effective-dated Person HESA Data, where the effective date is on or before the reporting period end date.

2. Use default.

3. If there is an Engagement entity in the extract related via COURSEID to a Qualification with QUALCAT beginning with H, I, J, or C, or M0002, M0016, or M0018, derive as NULL ERROR.

4. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Include for all entities

Derivation Steps:

1. Derive from Person HESA Data.

2. Map the Gender value from the most recent effective dated active Biographical History record that falls within the reporting period to a HESA code via the Gender mapping.

3. If a mapping doesn’t exist, then the Gender value defaults as follows:

   • F (Female) to 10

   • M (Male) to 11

4. Use default.

5. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

Derivation Steps:

1. Derive from the most recent effective-dated Person HESA Data, where the effective date is on or before the reporting period end date.

2. Derive the coded value from the most recent effective-dated active Person Biographic record (SCC_PERS_BIOG) that falls within the reporting period and mapped to a HESA code in the Orientation mapping.

3. Use default.

4. If Country = England or Northern Ireland, derive as NULL ERROR.

5. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if there is an Engagement entity in the extract for the student where INCOMINGEXCHANGE is null.

Derivation Steps:

1. Derive the Long Last Name of 60 characters or fewer, if it exists, of the most recent effective-dated name record that falls on or before the reporting period end date where the Name Type corresponds to a type defined for SNAME16 in Returns Setup > HESA Types.

2. Derive the Last Name of the most recent effective-dated name record that falls on or before the reporting period end date where the Name Type corresponds to a type defined for SNAME16 in Returns Setup > HESA Types.

3. Use default.

4. Derive as null.

Return: Data Futures

Entity: Student

Pages Used:

Field Derivation Rule: Include for all entities

Derivation Steps:

1. Derive from HESA Instance Data for related Engagement entities included in the extract. If different multiple SSN values are found, select the value from the HESA Instance with the most recent Start Date.

2. Derive the SSN from the most recent Attendance Confirmation Report (ACR) record (that is, highest sequence number) for the EMPLID and institution where:

   • The Aid Year start date falls on or before the reporting period end date.

   • The Aid Year end date is not before the reporting period start date for the reporting period in which Engagement.ENGSTARTDATE falls.

   • At least one row exists for the ACR with Attendance Indicator set to any value other than S or X.

3. Derive the SSN from the most recent SSAR record (that is, highest sequence number) for the EMPLID and institution where these conditions are true:

   • The Aid Year start date falls on or before the reporting period end date.

   • The Aid Year end date is not before the reporting period start date for the reporting period in which Engagement.ENGSTARTDATE falls.

   • A related first attendance confirmation record (SFA_SLC_ATD_CFR) exists.

   • If the UCAS Course Code is defined in the SSAR, then the UCAS Course Code should be linked via a UCAS Course Mapping to the same career and program as an Instance being reported for the student. If the UCAS Course code is not defined in the SSAR, or if there are no UCAS Course Mapping records for the UCAS Course Code, then the SSAR record is assumed to be relevant to the Instance.

4. Use default.

5. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if all conditions are met:

• Country = Scotland

• There is an Engagement entity in the extract for the student where INCOMINGEXCHANGE is null

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

• All related CourseInitiative entities for Engagement entities for the student in the extract have COURSEINITID other than 027.

• There are no StudentInitiative entities for the student in the extract with STUINITID of 027 or 030.

If all the Engagements for the student are subsequently flagged as dormant, update the derived value to null then set the derivation step to zero.

Derivation Steps:

1. Derive value from the most recent effective dated Person HESA Data where the effective date is on or before the reporting period end date.

2. Use default.

3. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if there is an Engagement entity in the extract for the student where INCOMINGEXCHANGE is null

Derivation Steps:

1. Derive the Long Last Name of 60 characters or fewer, if it exists, of the most recent effective-dated Primary name record that falls on or before the reporting period end date.

2. Derive the Last Name value of the most recent effective dated Primary name record that falls on or before the reporting period end date.

3. Use default.

4. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if Country = Scotland.

Derivation Steps:

1. Derive from the most recent effective-dated Person HESA Data where the effective date is on or before the reporting period end date.

2. Use default.

3. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if these conditions are met:

• There is an Engagement entity for the student in the extract where INCOMINGEXCHANGE is null.

• There is a related Student Course Session entity in the extract with SCSMODE = 01, 02, or 03.

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

• All related CourseInitiative entities for Engagement entities for the student in the extract have COURSEINITID other than 027.

• There are no StudentInitiative entities for the student in the extract with STUINITID of 027 or 030.

• At least one Student Course Session entity exists with PLACEMENT not equal to 01 and STUDYABROAD not equal to 01.

• Either no StudyLocation entities exist or at least one StudyLocation entity exists with a null DISTANCESL.

If all the Engagements for the student are subsequently flagged as dormant, update the derived value to null then set the derivation step to zero.

Derivation Steps:

1. Derive from the Person HESA Data page.

2. Use default

3. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if these conditions are met:

• There is an Engagement entity for the student in the extract where INCOMINGEXCHANGE is null

• Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

• All related CourseInitiative entities for Engagement entities for the student in the extract have COURSEINITID other than 027.

• There are no StudentInitiative entities for the student in the extract with STUINITID of 027 or 030.

• At least one Student Course Session entity exists, with PLACEMENT not equal to 01 and STUDYABROAD not equal to 01.

• Either no StudyLocation exists, or at least one StudyLocation exists with a null value for DISTANCESL.

If all the Engagements for the student are subsequently flagged as dormant, update the derived value to null then set the derivation step to zero.

Derivation Steps:

1. Derive from the Person HESA Data page.

2. Select the most recent active effective-dated address record that falls on or before the end of the reporting period, and has an Address Type that corresponds with those defined as the Address Types for TTPCODE in the HESA Returns Setup record. If multiple valid addresses are found with the same effective date, then one is randomly chosen.

   Select the most recent address record (exclude records with an effective date greater than the end date of the reporting period) that has one of the UK-related country codes. The record must have an Address Type that corresponds with the Address Type set for TTPCODE in the HESA Types page.

   The following country codes are considered as UK-related for the purposes of this derivation: GBR (United Kingdom), ENG (England), XF (England), NIR (Northern Ireland), XG (Northern Ireland), SCT (Scotland), XH (Scotland), WAL (Wales), XI (Wales), XK (UK not otherwise specified), XL (Channel Islands not otherwise specified), GG (Guernsey), GGY (Guernsey), JE (Jersey), JEY (Jersey), IM (Isle of Man), or IMN (Isle of Man).

3. Use default.

4. Derive as null.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Derive a value if any one of these conditions is met:

• Country is England or Northern Ireland.

  Entry Profile UCASSCHEMECODE is not null. If a value isn't found in the extract, the value is selected from the most recent submitted data record. If a value isn't found in submitted data, a value is derived without reference to the field derivation rule.

• Country = Scotland

  • Entry Profile UCASSCHEMECODE is not null. If a value isn't found in the extract, the value is selected from the most recent submitted data record. If a value isn't found in submitted data, a value is derived without reference to the field derivation rule.

  • Leaver.ENGENDDATE is null, or is more than 14 days after Engagement.ENGSTARTDATE, or is less than 15 days after Engagement.ENGSTARTDATE and Leaver.RSNENGEND is not 03, 05, 11, or 12.

Derivation Steps:

1. Select External System ID records for the person with the External System ID Type defined for UCASPERID in the HESA Returns Setup record.

   The most recent effective-dated record whose Effective Date is on or before the reporting period end date is used.

2. Use default.

3. Derive as NULL ERROR.

Return: Data Futures

Entity: Student

Page Used:

Field Derivation Rule: Include for all Student entities.

Derivation Steps:

1. Select External System ID records for the person with the External ID Type defined for ULN in the HESA Returns Setup page.

   The most recent effective-dated record whose Effective Date is on or before the reporting period end date is used.

   Validate the derived value using the checksum method. If the value is invalid, an error message is logged and the derivation skips to the next step. For information on the checksum method, refer to the Notes section for the ULN field available on the HESA website.

2. Derive as NULL ERROR if Country = England and either condition is true:

   • A StudentInitiatives entity in the extract for the student with STUINITID = 004

   • An Engagement entity in the extract for the student linked via COURSEID to a Course entity with a related CourseInitiative entity with COURSEINITID = 004

3. Use default.

4. Derive as null.