Database Changes for 4.42
This section contains details of database changes between the 4.41 and 4.42 releases.
Table changes:
New tables included in this release
The following tables have been added.
| Table Name | Description |
|---|---|
| CDR_VERIFIER_LINKS | Holds the link between two CDR verifiers to enable many to many relationship. |
| IDP_ASSIGNED_USERS | Holds the learner individual development plan assigned users records. |
| ILP_ACTION_LOGS | Logs key actions on an ILP record, for example when a target or review is reopened by an ILP manager. |
| PEOPLE_HESA_LANGPROFICIENCIES | The data for students proficiency in a given language. |
| PEOPLE_UNIT_SUPPORT_FUNDING | Holds the enrolment learner funding support records. |
| PUS_HESA_FUNDINGBODIES | Funding body for HESA-specific enrolments. |
New columns added to existing tables
The following columns have been added.
| Table Name | Column Name | Type (Size) | Nullable | Description |
|---|---|---|---|---|
| IDP_REVIEW | ONE_PAGE_PROFILE | nvarchar(MAX) | Y | The individual development plan (IDP) summary that will be shown as one page profile. |
| ILP_REVIEW_DEFINITIONS | IS_PRIVATE | nvarchar(1) | N | Indicates whether this review will be private to the recipients only (Y/N) [DEFAULT=N]. |
| ILP_REVIEW_DEFINITIONS | CAN_LEARNER_COMPLETE | nvarchar(1) | N | Indicates whether the learner can complete their review without staff approval (Y/N) [DEFAULT=N]. |
| LEARNER_AIMS | ACL_PROVISION_TYPE | nvarchar(2) | Y | The purpose of the Community Learning provision (FK to LSC_VERIFIERS where RV_Domain=ACL_PROVISION_TYPE). |
| LEARNER_AIMS | AFL_PROVISION_TYPE | nvarchar(2) | Y | Identifies Community Learning provision which is Family Learning (FK to LSC_VERIFIERS where RV_Domain=AFL_PROVISION_TYPE). |
| LOCATIONS | UKPRN | nvarchar(8) | Y | UKPRN (UK Provider Reference Number) associated with this location. |
| PEOPLE_CDR | CLIENT_ID | nvarchar(36) | Y | Identifier for apprenticeship learner. |
| PEOPLE_UNITS_CDR | PARTICIPANT_ID | nvarchar(36) | Y | Identifier for apprenticeship learner framework/programme enrolment. |
| PEOPLE_UNITS_CDR | LONG_TERM_DESTINATION | nvarchar(10) | Y | The current main employment status [FK=CDR_VERIFIERS.CODE(RV_DOMAIN=LTDESTINATION)]. |
| PEOPLE_UNITS_CDR | LONG_TERM_EMPLOYMENT | nvarchar(10) | Y | The employment situation on joining the programme [FK=CDR_VERIFIERS.CODE(RV_DOMAIN=LTEMPLOYMENT)]. |
| PEOPLE_UNITS_SPECIAL | ACL_PROVISION_TYPE | nvarchar(2) | Y | The purpose of the Community Learning provision (FK to LSC_VERIFIERS where RV_Domain=ACL_PROVISION_TYPE). |
| PEOPLE_UNITS_SPECIAL | AFL_PROVISION_TYPE | nvarchar(2) | Y | Identifies Community Learning provision which is Family Learning (FK to LSC_VERIFIERS where RV_Domain=AFL_PROVISION_TYPE). |
| PEOPLE_UNITS_SPECIAL_HESA | FUNDLENGTH | nvarchar(2) | Y | Indicates the length of the student course session for funding purposes [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=FUNDLENGTH)]. |
| PEOPLE_UNITS_SPECIAL_HESA | UCASSCHEMECODE | nvarchar(4) | Y | The 4-character scheme code issued by UCAS. |
| SFG_RECORD_LOGS | COMMENT | nvarchar(1000) | Y | An optional comment or reason associated with this change. |
| SFG_RECORD_LOGS | ACTION_TYPE | nvarchar(20) | Y | The type of log action (e.g. STATUS, RISK_LEVEL). |
| SFG_RECORD_TEMPLATES | FORCE_STATUS_CHANGE_REASON | nvarchar(1) | N | Force the entry of a reason when changing the status or risk level of a safeguarding record (Y/N) [DEFAULT=N]. |
| SFG_RECORD_TEMPLATES | SFG_MANAGER_VIEW_MODE | nvarchar(10) | N | Indicates whether all or only specified safeguarding managers can view records using this template (ALL/SPECIFIED) [DEFAULT=ALL]. |
| SFG_RECORD_TEMPLATES | IS_URGENT_RESTRICTED | nvarchar(1) | N | Indicates whether the is urgent template setting can be changed by tutors when creating a record (Y/N) [DEFAULT=N]. |
| SFG_RECORD_TEMPLATES | CAN_LEARNER_VIEW_RESTRICTED | nvarchar(1) | N | Indicates whether the can learner view template setting can be changed by tutors when creating a record (Y/N) [DEFAULT=N]. |
| SFG_RECORDS | RISK_LEVEL | nvarchar(40) | Y | The level of risk which has been flagged for this safeguarding record. e.g. Low, Medium, High [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=SFG_RISK_LEVEL)]. |
| UI_HESA | PREREQUISITE | nvarchar(2) | Y | Identifies the level of the course in relation to the qualifications that students are normally required to hold in order to gain entry to the course [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=FUNDLEV)]. |
| UIO_CDR | SOCIAL_INCLUSION | nvarchar(1) | N | Identifies a course as being part of the colleges CDP submission for social inclusion (Y/N) [DEFAULT=N]. |
| UIO_HESA | FUNDLENGTH | nvarchar(2) | Y | Indicates the length of the student course session for funding purposes [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=FUNDLENGTH)]. |
| UIO_QUAL_AIMS | ACL_PROVISION_TYPE | nvarchar(2) | Y | The purpose of the Community Learning provision (FK to LSC_VERIFIERS where RV_Domain=ACL_PROVISION_TYPE). |
| UIO_QUAL_AIMS | AFL_PROVISION_TYPE | nvarchar(2) | Y | Identifies Community Learning provision which is Family Learning (FK to LSC_VERIFIERS where RV_Domain=AFL_PROVISION_TYPE). |
Changes to existing columns
The following columns have been changed.
| Table Name | Column Name | Type (Size) | Nullable | Previous Values Type (Size) [Nullable] |
|---|---|---|---|---|
| PEOPLE_CDR | HAS_DISABILITY | nvarchar(2) | Y | nvarchar(1) [N] |
| PEOPLE_UNITS_SPECIAL_HESA | FUNDCODE | nvarchar(4) | Y | nvarchar(1) [Y] |
| UIO_HESA | FULLYFLEX | nvarchar(2) | Y | nvarchar(1) [N] |
View changes:
New views
The following views have been added:
-
EBS_CDR_VERIFIER_LEARNER_SUP_FUND
-
EBS_CDRGENERATERETURN_2023_24
-
EBS_HESA_CURRICULUM_ACCREDITATIONS
-
EBS_HESA_STUDY_LOCATIONS
-
EBS_HESA_SUPERVISOR_ALLOCATIONS
-
EBS_ILR_DPOUTCOME_XML_2324
-
EBS_ILR_LEARNERAIMS_XML_2324
-
EBS_ILR_LEARNERHE_XML_2324
-
EBS_ILR_LEARNERS_EMP_XML_2324
-
EBS_ILR_LEARNERS_LLDD_XML_2324
-
EBS_ILR_LEARNERS_WP_XML_2324
-
EBS_ILR_LEARNERS_XML_2324
-
EBS_ILR_LRNRAIM_BLZER_XML_2324
-
EBS_ILR_LRNRAIM_FAM_XML_2324
-
EBS_ILR_PRIOR_ATTAINMENT_ENTITY_XML_2324
-
EBS_PUS_HESA_ACCREDITATIONS
-
EBS_UI_HESA_COURSE_ROLES
-
EBS_UI_HESA_MODULE_COST_CENTRES
-
EBS_UI_HESA_MODULE_DELIVERY_ROLES
New columns in existing views
The following columns have been added.
| View Name | Column Name |
|---|---|
| EBS_ASSESSMENTS | UIO_STATUS |
| EBS_CDRGENERATERETURN | DEPENDENTS |
| EBS_CDRGENERATERETURN | PROG_PATHWAY |
| EBS_CDRGENERATERETURN_2022_23 | DEPENDENTS |
| EBS_CDRGENERATERETURN_2022_23 | PROG_PATHWAY |
| EBS_ILR_GENERATION_SOURCE | ACL_PROVISION_TYPE |
| EBS_ILR_GENERATION_SOURCE | AFL_PROVISION_TYPE |
| EBS_LEARNER_AIMS | ACL_PROVISION_TYPE |
| EBS_LEARNER_AIMS | AFL_PROVISION_TYPE |
| EBS_LEARNER_ENROLMENTS | PARTICIPANT_ID |
| EBS_LEARNER_ENROLMENTS | PU_CDR_LONG_TERM_DESTINATION |
| EBS_LEARNER_ENROLMENTS | PU_CDR_LONG_TERM_EMPLOYMENT |
| EBS_LEARNERS | CLIENT_ID |
| EBS_LEARNERS | SOC2020 |
| EBS_LEARNERS_DEDUP | CLIENT_ID |
| EBS_LEARNERS_DEDUP | SOC2020 |
| EBS_PARTIAL_PLANNED_ABSENCE | ABSENCE_REASON |
| EBS_PU_SPECIAL_HESA | APEL |
| EBS_PU_SPECIAL_HESA | COLPROVTYPEID |
| EBS_PU_SPECIAL_HESA | CONTINUING |
| EBS_PU_SPECIAL_HESA | EMPFEES |
| EBS_PU_SPECIAL_HESA | EMPLOYINGSCHOOL |
| EBS_PU_SPECIAL_HESA | ENTRYRTE |
| EBS_PU_SPECIAL_HESA | FEEELIG |
| EBS_PU_SPECIAL_HESA | FEEMETHOD |
| EBS_PU_SPECIAL_HESA | FEESTATUS |
| EBS_PU_SPECIAL_HESA | FUNDLENGTH |
| EBS_PU_SPECIAL_HESA | INACTIVEMOD |
| EBS_PU_SPECIAL_HESA | INTENDEDDESTINATION |
| EBS_PU_SPECIAL_HESA | INTENDEDTHESISTITLE |
| EBS_PU_SPECIAL_HESA | INTERCALATION |
| EBS_PU_SPECIAL_HESA | LEADSCHOOL |
| EBS_PU_SPECIAL_HESA | MODCOUNT |
| EBS_PU_SPECIAL_HESA | MODULEOUTCOME |
| EBS_PU_SPECIAL_HESA | MODULERESULT |
| EBS_PU_SPECIAL_HESA | NHSEMP |
| EBS_PU_SPECIAL_HESA | NONREGFEE |
| EBS_PU_SPECIAL_HESA | PARTNERNUMHUS |
| EBS_PU_SPECIAL_HESA | PARTNERSID |
| EBS_PU_SPECIAL_HESA | PARTNERUKPRN |
| EBS_PU_SPECIAL_HESA | PGRLANGID |
| EBS_PU_SPECIAL_HESA | PGRLANGPCNT |
| EBS_PU_SPECIAL_HESA | PHDSUB |
| EBS_PU_SPECIAL_HESA | PLACEMENT |
| EBS_PU_SPECIAL_HESA | PREPFLAG |
| EBS_PU_SPECIAL_HESA | QTS |
| EBS_PU_SPECIAL_HESA | RCSTDID |
| EBS_PU_SPECIAL_HESA | RCSTDNT |
| EBS_PU_SPECIAL_HESA | RSNSCSEND |
| EBS_PU_SPECIAL_HESA | THESISTITLE |
| EBS_PU_SPECIAL_HESA | TRN |
| EBS_PU_SPECIAL_HESA | UCASSCHEMECODE |
| EBS_SFG_GETINBOX | FORENAME |
| EBS_SFG_GETINBOX | PERSONTITLE |
| EBS_SFG_GETINBOX | RISK_LEVEL |
| EBS_SFG_GETINBOX | SURNAME |
| EBS_STAFF_UTILISATION_STATS | IS_ACTIVE |
| EBS_UIO | FUNDLENGTH |
| EBS_UIO | SESS_TITLE |
| EBS_UIO | SOCIAL_INCLUSION |
| EBS_UIO_LEARNING_AIMS | ACL_PROVISION_TYPE |
| EBS_UIO_LEARNING_AIMS | AFL_PROVISION_TYPE |
| EBS_UIO_LLWR | FUNDLENGTH |
| EBS_UIO_LLWR | SESS_TITLE |
| EBS_UIO_LLWR | SOCIAL_INCLUSION |
| EBS_UNIT_INSTANCES | PREREQUISITE |
| EBS_UNIT_INSTANCES_CANENROCCS | PREREQUISITE |
| EBS_UNIT_INSTANCES_LLWR | PREREQUISITE |
| EBS_UNIT_SPECIAL | ACL_PROVISION_TYPE |
| EBS_UNIT_SPECIAL | AFL_PROVISION_TYPE |
Columns dropped from existing views
The following columns have been dropped.
| Table Name | Description |
|---|---|
| ebs_CDRGenerateReturn | DEP_NO_CARE |
| ebs_CDRGenerateReturn | PROG_FAST_TRACK |
| ebs_CDRGenerateReturn_2022_23 | DEP_NO_CARE |
| ebs_CDRGenerateReturn_2022_23 | PROG_FAST_TRACK |
| ebs_sfg_getinbox | CanSendEmail |
Changes for Service Pack 1
New tables included in this release
The following tables have been added.
| Table Name | Description |
|---|---|
| OFF_VENUE_ACTIVITIES | Holds details of a student's placement activity or time spent abroad |