Database Changes for 4.39
This section contains details of database changes between the 4.38 and 4.39 releases.
Table changes:

The following tables have been added.
Table Name | Description |
---|---|
EDGE_ROLES | Roles used to control user interaction with the third party links in Engage. |
SFG_CATEGORIES | Categories that will be set for safeguarding note, form and meeting templates. |
SFG_DISTRIBUTION_LIST_LINKS | Links staff and groups to safeguarding distribution lists. |
SFG_DISTRIBUTION_LISTS | Named distribution lists for safeguarding records, notes and meetings. |
SFG_RECORD_TEMPLATE_CTGY | Links safeguarding categories to safeguarding record templates. |
SFG_RECORD_TEMPLATE_DIST | Links safeguarding distribution lists and individual staff members to safeguarding record templates. |
SFG_RECORD_TEMPLATES | Named safeguarding record templates. |
SFG_TEMPLATE_DEFINITIONS | Named safeguarding template form definitions. |

The following columns have been added.
Table Name | Column Name | Type (Size) | Nullable | Description |
---|---|---|---|---|
ATTAINMENTS | QUALRESULT | nvarchar(30) | Y | The qualification result held by the Student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=QUALRESULT)]. |
LEARNING_PROG | IDPCODE | nvarchar(1) | Y | Records whether a learner has an Individual Development Plan(LP90) [FK=ELWA_Verifiers.CODE (RV_DOMAIN=IDPINDICATOR)]. |
LOCATIONS | INCLUDE_IN_SPACEFTE | nvarchar(1) | N | Indicates Locations where enrolments need to have the CDR Spacial FTE calculation performed [Y/N] [DEFAULT=N]. |
PEOPLE_CDR | GCSE_A_C | numeric(2, 0) | Y | Number of GCSEs grade A to C or equivalent. |
PEOPLE_CDR | GCSE_D_G | numeric(2, 0) | Y | Number of GCSEs grade D to G or equivalent. |
PEOPLE_CDR | CARE_LEAVER | nvarchar(2) | Y | Whether student is a Care Leaver [FK=CDR_Verifiers.CODE (RV_DOMAIN=CARE_LEAVER)]. |
PEOPLE_HESA | SID | numeric(17, 0) | Y | The Student Identifier. |
PEOPLE_HESA | SCN | nvarchar(9) | Y | The Scottish Candidate Number. |
PEOPLE_HESA | CARER | nvarchar(2) | Y | Indicates whether a student is a carer [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=CARER)]. |
PEOPLE_HESA | TRANS | nvarchar(2) | Y | The transgender status of the student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=TRANS)]. |
PEOPLE_HESA | LANGPREF | nvarchar(2) | Y | The language in which the student would prefer to be contacted [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=LANGPREF)]. |
PEOPLE_HESA | SERLEAVE | nvarchar(2) | Y | Indicates whether the student is a service leaver, according to the relevant body definition [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SERLEAVE)]. |
PEOPLE_HESA | SERSTU | nvarchar(2) | Y | Indicates whether the student is a service student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SERSTU)]. |
PEOPLE_HESA | TTACCOM | nvarchar(2) | Y | The type of accommodation the student is living in during term time [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=TTACCOM)]. |
PEOPLE_HESA | LANGUAGEID | nvarchar(2) | Y | The language ID in which the student proficiency is recorded [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=LANGUAGEID)]. |
PEOPLE_HESA | PROFICIENCYTYPE | nvarchar(2) | Y | The type of proficiency the student has in the language indicated [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=PROFICIENCYTYPE)]. |
PEOPLE_HESA | PROFICIENCYLEVEL | nvarchar(2) | Y | The students ability to speak in the language indicated [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=PROFICIENCYLEVEL)]. |
PEOPLE_HESA | ACCESSPRG | nvarchar(2) | Y | Indicates whether entrants have entered HE via SWAP courses [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=ACCESSPRG)]. |
PEOPLE_HESA | DEPENDANT | nvarchar(2) | Y | Indicates whether the student has any dependants [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=DEPENDANT)]. |
PEOPLE_HESA | ESTRANGED | nvarchar(2) | Y | Indicates whether the student is estranged, based on their own self-assessment [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=ESTRANGED)]. |
PEOPLE_HESA | MARSTAT | nvarchar(2) | Y | The marital status of the Student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=MARSTAT)]. |
PEOPLE_HESA | RELIGIOUSBGROUND | nvarchar(2) | Y | The religious background that the student was brought up in [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=RELIGIOUSBGROUND)]. |
PEOPLE_HESA | YRLLPROV | nvarchar(4) | Y | The year in which the student left the previous provider. |
PEOPLE_HESA | SOC2010 | nvarchar(4) | Y | The standard occupational classification code for the student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SOC2010)]. |
PEOPLE_UNITS_CDR | HLA_PATHWAY | nvarchar(5) | Y | Code to identify the correct HLA pathway for the learner at enrolment level as defined by DfE [FK=Verifiers.LOW_VALUE (RV_DOMAIN=U_HLA_PATHWAY)]. |
RRQ_ORGANISATIONS | OFQUAL_STATUS | nvarchar(21) | Y | The Ofqual status of the organisation. |
RRQ_ORGANISATIONS | OFQUAL_RECOG_FROM | datetime | Y | The date an organisation became recognised by Ofqual. |
RRQ_ORGANISATIONS | OFQUAL_RECOG_TO | datetime | Y | The date an organisation stopped being recognised by Ofqual. |
RRQ_ORGANISATIONS | CCEA_REG_STATUS | nvarchar(21) | Y | The CCEA regulation status of the organisation. |
RRQ_ORGANISATIONS | CCEA_REG_RECOG_FROM | datetime | Y | The date an organisation became recognised by CCEA regulation. |
RRQ_ORGANISATIONS | CCEA_REG_RECOG_TO | datetime | Y | The date an organisation stopped being recognised by CCEA regulation. |
THIRDPARTY_LINKS | EDGE_ROLE | nvarchar(250) | Y | A comma delimited list of edge roles to control the usage of a third party link, constructed by associating a third party link to one or more edge roles. |
UIO_CDR | HECOS_CODE | nvarchar(10) | Y | Current HECOS code for HE courses [FK=CDR_Verifiers.CODE (RV_DOMAIN=HECOS_CODE)]. |
UIO_CDR | HLA_PATHWAY | nvarchar(5) | Y | Code to identify the correct HLA pathway for the learner at course level as defined by DfE [FK=Verifiers.LOW_VALUE (RV_DOMAIN=U_HLA_PATHWAY)]. |

The following columns have been changed.
Table Name | Column Name | Type (Size) | Nullable | Previous Values Type (Size) [Nullable] |
---|---|---|---|---|
ATTAINMENTS | HESA_QUAL_TYPE | nvarchar(5) | Y | nvarchar(2) [Y] |
ATTAINMENTS | MAIN_SUBJECT_1 | nvarchar(6) | Y | nvarchar(4) [Y] |
LEARNING_PROG | EMP_NAME | nvarchar(200) | Y | nvarchar(60) [Y] |
ORGANISATION_UNITS | FES_FULL_NAME | nvarchar(200) | Y | nvarchar(100) [Y] |
PEOPLE | COLLEGE_EMAIL | nvarchar(320) | Y | nvarchar(128) [Y] |
PEOPLE | PERSONAL_EMAIL | nvarchar(320) | Y | nvarchar(100) [Y] |

The following columns have been added.
View Name | Column Name |
---|---|
EBS_LEARNER_ENROLMENTS | HLA_PATHWAY |
EBS_LEARNERS | GCSE_A_C |
EBS_LEARNERS | GCSE_D_G |
EBS_LEARNERS | P_CDR_CARE_LEAVER |
EBS_LEARNERS_DEDUP | GCSE_A_C |
EBS_LEARNERS_DEDUP | GCSE_D_G |
EBS_LEARNERS_DEDUP | P_CDR_CARE_LEAVER |
EBS_LLWR_LEARNING_PROG | IDPCODE |
EBS_PEOPLE_LP_GROUPS | DATE_OF_BIRTH |
EBS_PEOPLE_LP_GROUPS | IDPCODE |
EBS_UIO | HECOS_CODE |
EBS_UIO | HLA_PATHWAY |
EBS_UIO_LLWR | HECOS_CODE |
EBS_UIO_LLWR | HLA_PATHWAY |

The following views have been dropped.
-
DM_APP_APPLICATIONS_NI
-
DM_CPL_EFA
-
DM_CUR_COURSE_KIS
-
DM_CUR_COURSE_SUMMARY_NI
-
DM_ENQ_LEARNER_ENQUIRIES_NI
-
dm_sys_wamusers
-
ebs_de_failure_attend_school
-
ebs_de_suspension
-
EBS_EXAM_BOARDS_NO_PLR
-
ebs_learner_employment_TEMP
-
EBS_LEARNER_ENROLMENTS2
-
Perfomance
-
v_rep_studentPastoralStatus
-
v_report_ilpTargets
-
WEST_LEARNERS

The following columns have been dropped.
Table Name | Description |
---|---|
EBS_STAFFUSER | relationship |
EBS_STAFFUSER_ANSWS | relationship |
EBS_STAFFUSER_NSW | relationship |