Database Changes for 4.44

The following changes have been made for 4.44:

NEW TABLES

Table Name

Description

COHORT_MODULES Specifies which modules a cohort group can be used in (e.g. TribalILP, SAFEGUARDING).
PEOPLE_DSR PEOPLE extension table for data system refresh (DSR) fields.
PEOPLE_UNITS_DSR PEOPLE_UNITS extension table for data system refresh (DSR) fields.
UCAS_APPLICANT_BACKGROUND UCAS Applicant background - Unprocessed data retrieved from UCAS.
UI_DSR UNIT_INSTANCE extension table for data system refresh (DSR) fields.

DROPPED TABLES

Table Name

Description

NO TABLES DROPPED

NEW COLUMNS ADDED TO EXISTING TABLES  

Table Name

Column Name

Type (Size) Nullable

Description

ATTAINMENTS DSR_COMPLETION_RETURN_STATUS nvarchar(1) Y Indicates the completion status of the DSR return (P=Pending, E=Extracted, S=Submitted).
ATTAINMENTS VERIFIED_SCORE numeric(10, 0) Y The verified score for this unit.
ATTAINMENTS VERIFIED_GRADE nvarchar(255) Y The verified grade for this unit.
ATTAINMENTS VERIFIED_BY nvarchar(30) Y The username of the staff member who verified this unit.
ATTAINMENTS VERIFIED_DATE datetime Y The date and time this unit was verified.
FUNDING_RETURNS RETURN_TYPE nvarchar(5) Y Indicates the return type of the funding return [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=RETURN_TYPE)].
IDP_REVIEW EXPORT_SEQ_NO nvarchar(3) Y The sequence number that shows how many times this idp record has been exported.
ILP_TARGET_DEFINITIONS DESCRIPTION nvarchar(4000) Y Provides a description of the target to the user.
ILP_TARGETS DESCRIPTION nvarchar(4000) Y Provides a description of the target to the user.
PEOPLE_UNITS DSR_COMPLETION_RETURN_STATUS nvarchar(1) Y Indicates the completion status of the DSR return (P=Pending, E=Extracted, S=Submitted).
REGISTER_EVENTS OWNING_ORGANISATION nvarchar(30) Y The owning organisation [FK=ORGANISATION_UNITS.ORGANISATION_CODE].
REGISTER_EVENTS OFFERING_ORGANISATION nvarchar(30) Y The offering organisation [FK=ORGANISATION_UNITS.ORGANISATION_CODE].
SDR_COURSE_ENROLMENTS_SNAPSHOT CONSORTIUM nvarchar(6) Y Identifies the cooperative arrangement among groups or institutions [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=CONSORTIUM)].
SDR_COURSE_ENROLMENTS_SNAPSHOT ITE_SECTOR nvarchar(1) Y Identifies the sector for a learner enrolled in Initial Teacher Education [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=ITE_SECTOR)].
SDR_COURSE_ENROLMENTS_SNAPSHOT ITE_SUBJECT1 nvarchar(4) Y The specific curriculum subject 1 areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=ITE_SUBJECT)].
SDR_COURSE_ENROLMENTS_SNAPSHOT ITE_SUBJECT2 nvarchar(4) Y The specific curriculum subject 2 areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=ITE_SUBJECT)].
SDR_COURSE_ENROLMENTS_SNAPSHOT ITE_SUBJECT3 nvarchar(4) Y The specific curriculum subject 3 areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=ITE_SUBJECT)].
SDR_COURSE_ENROLMENTS_SNAPSHOT ITE_SUBJECT4 nvarchar(4) Y The specific curriculum subject 4 areas that a learner is studying where they are enrolled in an Initial Teacher Education qualification where the ITE sector is secondary [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=ITE_SUBJECT)].
SDR_COURSE_ENROLMENTS_SNAPSHOT ITE_SUBJECT nvarchar(19) Y A concatenated list of all specific curriculum subject areas that a learner is studying (from ITE_SUBJECT1->4), delimited by semi-colon.
SDR_COURSE_ENROLMENTS_SNAPSHOT EFTS_BY_MONTH nvarchar(1019) Y A concatenated list of all EFTS by month (from EFTS_MTH_01->12), delimited by semi-colon.
SDR_COURSE_REGISTER_SNAPSHOT DISC_COURSE_TUITION_FEE numeric(8, 2) Y The discounted course tuition fee charged to domestic learners who enrol in the course.
SDR_COURSE_REGISTER_SNAPSHOT DISC_COMPULSORY_CRS_COST_FEE numeric(8, 2) Y The discounted course fee charged to domestic learners who enrol in the course.
SDR_SNAPSHOT DSR_STATUS nvarchar(1) Y Status of the DSR return (P=Pending, E=Extracted, S=Submitted).
SDR_STUDENTS_SNAPSHOT DISAB_1 nvarchar(40) Y Disability Support Need 1.
SDR_STUDENTS_SNAPSHOT DISAB_2 nvarchar(40) Y Disability Support Need 2.
SDR_STUDENTS_SNAPSHOT DISAB_3 nvarchar(40) Y Disability Support Need 3.
SDR_STUDENTS_SNAPSHOT DISAB_4 nvarchar(40) Y Disability Support Need 4.
SDR_STUDENTS_SNAPSHOT DISAB_5 nvarchar(40) Y Disability Support Need 5.
SDR_STUDENTS_SNAPSHOT DISAB_6 nvarchar(40) Y Disability Support Need 6.
SDR_STUDENTS_SNAPSHOT DISAB_7 nvarchar(40) Y Disability Support Need 7.
SDR_STUDENTS_SNAPSHOT IWI_4 nvarchar(12) Y Indicates the IWI affiliation 4 of a learner.
SDR_STUDENTS_SNAPSHOT IWI_5 nvarchar(12) Y Indicates the IWI affiliation 5 of a learner.
SDR_STUDENTS_SNAPSHOT IWI_6 nvarchar(12) Y Indicates the IWI affiliation 6 of a learner.
SDR_STUDENTS_SNAPSHOT ETHNIC_4 nvarchar(9) Y Indicates the ethnicity 4 of a learner.
SDR_STUDENTS_SNAPSHOT ETHNIC_5 nvarchar(9) Y Indicates the ethnicity 5 of a learner.
SDR_STUDENTS_SNAPSHOT ETHNIC_6 nvarchar(9) Y Indicates the ethnicity 6 of a learner.
SDR_STUDENTS_SNAPSHOT DISABILITY_STATUS nvarchar(1) Y Indicates the disability status of a learner [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=DISABILITY_STATUS)].
SDR_STUDENTS_SNAPSHOT IWI_AFFILIATION nvarchar(77) Y A concatenated list of all Iwi affiliation of the learner (from IWI_1->6), delimited by semi-colon.
SDR_STUDENTS_SNAPSHOT ETHNICITY nvarchar(59) Y A concatenated list of all ethnicity of the learner (from ETHNIC_1->6), delimited by semi-colon.
SDR_STUDENTS_SNAPSHOT DISABILITY_SUPPORT_NEEDS nvarchar(286) Y A concatenated list of all disability support needs of the learner, delimited by semi-colon.
SFG_RECORDS CAN_LEARNER_VIEW nvarchar(1) Y When populated, overrides record definition learner can view setting (Y/N).
SFG_RECORDS CAN_LEARNER_REPLY nvarchar(1) Y When populated, overrides record definition learner can reply setting (Y/N).

CHANGES TO EXISTING COLUMNS

Table Name

Column Name

Type (Size) Nullable

Previous Values Type (Size) [Nullable]

HEPCAT_LOG_VETSTUDENT ATTENDANCE_CODE nvarchar(2) Y nvarchar(1) [Y]
PEOPLE_UNITS ATTENDANCE_CODE nvarchar(2) Y nvarchar(1) [Y]
PEOPLE_UNITS_SPECIAL FUNDING_CATEGORY nvarchar(2) Y nvarchar(1) [Y]
SDR_COURSE_COMPLETION_SNAPSHOT CRS_END nvarchar(10) Y nvarchar(8) [Y]
SDR_COURSE_COMPLETION_SNAPSHOT CRS_SRT nvarchar(10) Y nvarchar(8) [Y]
SDR_COURSE_ENROLMENTS_SNAPSHOT ATTEND nvarchar(2) Y nvarchar(1) [Y]
SDR_COURSE_ENROLMENTS_SNAPSHOT CATEGORY nvarchar(3) Y nvarchar(2) [Y]
SDR_COURSE_ENROLMENTS_SNAPSHOT CRS_END nvarchar(10) Y nvarchar(8) [Y]
SDR_COURSE_ENROLMENTS_SNAPSHOT CRS_SRT nvarchar(10) Y nvarchar(8) [Y]
SDR_COURSE_ENROLMENTS_SNAPSHOT CRS_WTD nvarchar(10) Y nvarchar(8) [Y]
SDR_COURSE_REGISTER_SNAPSHOT CATEGORY nvarchar(3) Y nvarchar(2) [Y]
SDR_COURSE_REGISTER_SNAPSHOT CCCOSTS_FEE nvarchar(10) Y nvarchar(5) [Y]
SDR_COURSE_REGISTER_SNAPSHOT EMB_LIT_NUM nvarchar(1) Y nvarchar(1) [N]
SDR_COURSE_REGISTER_SNAPSHOT FEE nvarchar(10) Y nvarchar(5) [Y]
SDR_COURSE_REGISTER_SNAPSHOT FOREIGN_FEE nvarchar(10) Y nvarchar(5) [Y]
SDR_SNAPSHOT SDR_STATUS nvarchar(1) Y char(1) [N]
SDR_STUDENTS_SNAPSHOT DIS_ACCESS int Y int [N]
SDR_STUDENTS_SNAPSHOT DOB nvarchar(10) Y nvarchar(8) [Y]
SDR_STUDENTS_SNAPSHOT FOREIGN_FEE nvarchar(10) Y nvarchar(5) [Y]
SDR_STUDENTS_SNAPSHOT MAX_EXEMPT_FEE nvarchar(10) Y nvarchar(5) [Y]
SDR_STUDENTS_SNAPSHOT TOTAL_FEE nvarchar(10) Y nvarchar(6) [Y]
UNIT_INSTANCES FUNDING_CATEGORY nvarchar(2) Y nvarchar(1) [Y]

EXISTING COLUMNS THAT HAVE BEEN DROPPED  

Table Name

Column Name

Type (Size) Nullable

Description

COHORTS MODULE_NAME nvarchar(20) N The area of functionality which this COHORTS applies to (TribalILP).

NEW VIEWS IN THIS RELEASE

View Name

EBS_CORRESPONDENCE_ADDRESSES
EBS_CORRESPONDENCE_APPLICATIONS
EBS_CORRESPONDENCE_ENQUIRY
EBS_CORRESPONDENCE_ENROLMENTS_GEN
EBS_CORRESPONDENCE_GENERAL
EBS_CORRESPONDENCE_IDP
EBS_CORRESPONDENCE_INTERVIEWS
EBS_CORRESPONDENCE_LEARNER
EBS_CORRESPONDENCE_OFFERS
EBS_DE_LEARNER_IDP
EBS_DSR_COURSE_COMPLETION_2024
EBS_DSR_COURSE_ENROLMENTS_2024
EBS_DSR_COURSE_REG_BASE_2024
EBS_DSR_COURSE_REGISTER_2024
EBS_DSR_EXTRACTED
EBS_DSR_EXTRACTED_ATTAINMENTS
EBS_DSR_QUAL_COMPLETIONS_2024
EBS_DSR_SETTINGS_2024
EBS_DSR_STUDENTS_2024
EBS_DSR_STUDENTS_BASE_2024
EBS_LM_FINANCIAL_SUPPORT
EBS_LM_STUDY_LOCATIONS
EBS_LM_SUPERVISOR_ALLOCATIONS
EBS_SDR_COURSE_COMPLETION_2024
EBS_SDR_COURSE_ENROLMENTS_2024
EBS_SDR_COURSE_REG_BASE_2024
EBS_SDR_COURSE_REGISTER_2024
EBS_SDR_QUAL_COMPLETIONS_2024
EBS_SDR_SETTINGS_2024
EBS_SDR_STUDENTS_2024
EBS_SDR_STUDENTS_BASE_2024
EBS_SELF_WITHDRAWN_PROGRESS_CODE
EBS_UCAS_APPLICANT_BCKGND
EBS_UCAS_YEARS

NEW COLUMNS IN EXISTING VIEWS

View Name

Column Name

EBS_BOOKABLE_INTERVIEWS CANCELLED_LEARNERS
EBS_BOOKABLE_INTERVIEWS OFFERING_ORGANISATION
EBS_BOOKABLE_INTERVIEWS OFFERING_ORGANISATION_NAME
EBS_BOOKABLE_INTERVIEWS OWNING_ORGANISATION
EBS_BOOKABLE_INTERVIEWS OWNING_ORGANISATION_NAME
EBS_CDRGENERATERETURN_2023_24 PROGRESS_STATUS
EBS_CORRESPONDENCE_ENROLMENTS DSR_COMPLETION_RETURN_STATUS
EBS_COURSE_OCC_STAFF_LIST CALOCC_OCCURRENCE_CODE
EBS_COURSE_OCC_STAFF_LIST FES_LONG_DESCRIPTION
EBS_COURSE_OCC_STAFF_LIST UNIT_INSTANCE_CODE
EBS_CP_UIO_QUAL_AIMS ACL_PROVISION_TYPE
EBS_CP_UIO_QUAL_AIMS AFL_PROVISION_TYPE
EBS_CP_UIO_QUAL_AIMS APPRENTICESHIP_PATHWAY
EBS_CP_UIO_QUAL_AIMS APPRENTICESHIP_TRAILBLAZER_STD
EBS_CP_UIO_QUAL_AIMS ASL_PROVISION_TYPE
EBS_CP_UIO_QUAL_AIMS FE_HE_FUNDED
EBS_CP_UIO_QUAL_AIMS FES_ACTIVE_PLACES
EBS_CP_UIO_QUAL_AIMS MAJOR_FUND_SOURCE
EBS_CP_UIO_QUAL_AIMS PLANNED_HOURS
EBS_CP_UIO_QUAL_AIMS PROG_TYPE
EBS_CP_UIO_QUAL_AIMS SECTOR_FRAMEWORK
EBS_CP_UIO_QUAL_AIMS SLOC_LOCATION_CODE
EBS_CP_UIOVALUES_21_1 ACL_PROVISION_TYPE
EBS_CP_UIOVALUES_21_1 AFL_PROVISION_TYPE
EBS_CP_UIOVALUES_21_1 APPRENTICESHIP_PATHWAY
EBS_CP_UIOVALUES_21_1 APPRENTICESHIP_TRAILBLAZER_STD
EBS_CP_UIOVALUES_21_1 ASL_PROVISION_TYPE
EBS_CP_UIOVALUES_21_1 FE_HE_FUNDED
EBS_CP_UIOVALUES_21_1 FES_ACTIVE_PLACES
EBS_CP_UIOVALUES_21_1 MAJOR_FUND_SOURCE
EBS_CP_UIOVALUES_21_1 PLANNED_HOURS
EBS_CP_UIOVALUES_21_1 PROG_TYPE
EBS_CP_UIOVALUES_21_1 SECTOR_FRAMEWORK
EBS_CP_UIOVALUES_21_1 SLOC_LOCATION_CODE
EBS_CP_UIOVALUES_24_2 ACL_PROVISION_TYPE
EBS_CP_UIOVALUES_24_2 AFL_PROVISION_TYPE
EBS_CP_UIOVALUES_24_2 APPRENTICESHIP_PATHWAY
EBS_CP_UIOVALUES_24_2 APPRENTICESHIP_TRAILBLAZER_STD
EBS_CP_UIOVALUES_24_2 ASL_PROVISION_TYPE
EBS_CP_UIOVALUES_24_2 FE_HE_FUNDED
EBS_CP_UIOVALUES_24_2 FES_ACTIVE_PLACES
EBS_CP_UIOVALUES_24_2 GROUPS_COUNT
EBS_CP_UIOVALUES_24_2 MAJOR_FUND_SOURCE
EBS_CP_UIOVALUES_24_2 PLANNED_HOURS
EBS_CP_UIOVALUES_24_2 PROG_TYPE
EBS_CP_UIOVALUES_24_2 SECTOR_FRAMEWORK
EBS_CP_UIOVALUES_24_2 SLOC_LOCATION_CODE
EBS_IDP_CONTACTS CONTACT_ADD_POSTCODE
EBS_IDP_CONTACTS CONTACT_ADDRESS_LINE_1
EBS_IDP_CONTACTS CONTACT_ADDRESS_LINE_2
EBS_IDP_CONTACTS CONTACT_ADDRESS_LINE_3
EBS_IDP_CONTACTS CONTACT_DATE_OF_DEATH
EBS_IDP_CONTACTS CONTACT_MOBILE
EBS_IDP_CONTACTS CONTACT_REGION
EBS_IDP_CONTACTS CONTACT_RELATIONSHIP
EBS_IDP_CONTACTS CONTACT_TOWN
EBS_IDP_CONTACTS CONTACT_USE_EMAIL
EBS_IDP_CONTACTS CONTACT_USE_SMS
EBS_IDP_LEARNERS COLLEGE_EMAIL
EBS_IDP_LEARNERS LEARNER_DATE_OF_DEATH
EBS_IDP_LEARNERS LEARNER_USE_EMAIL
EBS_IDP_LEARNERS LEARNER_USE_SMS
EBS_IDP_LEARNERS PERSONAL_EMAIL
EBS_LEARNER_ENROLMENTS CONSORTIUM
EBS_LEARNER_ENROLMENTS ITE_SECTOR
EBS_LEARNER_ENROLMENTS ITE_SUBJECT1
EBS_LEARNER_ENROLMENTS ITE_SUBJECT2
EBS_LEARNER_ENROLMENTS ITE_SUBJECT3
EBS_LEARNER_ENROLMENTS ITE_SUBJECT4
EBS_LEARNER_ENROLMENTS PU_DSR_ID
EBS_LEARNERS DISABILITY_STATUS
EBS_LEARNERS IWI_4
EBS_LEARNERS IWI_5
EBS_LEARNERS IWI_6
EBS_LEARNERS NZ_ETHNICITY_4
EBS_LEARNERS NZ_ETHNICITY_5
EBS_LEARNERS NZ_ETHNICITY_6
EBS_LEARNERS P_DSR_ID
EBS_LEARNERS_DEDUP DISABILITY_STATUS
EBS_LEARNERS_DEDUP IWI_4
EBS_LEARNERS_DEDUP IWI_5
EBS_LEARNERS_DEDUP IWI_6
EBS_LEARNERS_DEDUP NZ_ETHNICITY_4
EBS_LEARNERS_DEDUP NZ_ETHNICITY_5
EBS_LEARNERS_DEDUP NZ_ETHNICITY_6
EBS_LEARNERS_DEDUP P_DSR_ID
EBS_PEOPLE_UNIT_LINKS DSR_COMPLETION_RETURN_STATUS
EBS_REG_EVENTS_NOT_DEACTIVATED OFFERING_ORGANISATION
EBS_REG_EVENTS_NOT_DEACTIVATED OWNING_ORGANISATION
EBS_SPONSOR_FEE_SUMMARY UIO_ID
EBS_UNIT_INSTANCES DISC_COMPULSORY_CRS_COST_FEE
EBS_UNIT_INSTANCES DISC_COURSE_TUITION_FEE
EBS_UNIT_INSTANCES DSR_ID
EBS_UNIT_INSTANCES_CANENROCCS DISC_COMPULSORY_CRS_COST_FEE
EBS_UNIT_INSTANCES_CANENROCCS DISC_COURSE_TUITION_FEE
EBS_UNIT_INSTANCES_CANENROCCS DSR_ID
EBS4_INTERVIEWS CANCEL_REASON
UCAS_HE_DATALOAD DATASET

VIEWS DROPPED IN THIS RELEASE

View Name

NO VIEWS DROPPED

COLUMNS DROPPED FROM EXISTING VIEWS

View Name

Column Name

NO VIEW COLUMNS DROPPED

Changes for SP1

NEW COLUMNS ADDED TO EXISTING TABLES  

Table Name

Column Name

Type (Size) Nullable

Description

PEOPLE_UNITS_SPECIAL_HESA ENGPRIONUK nvarchar(1) Y Records where students will spend a majority of their engagement outside the UK are currentlyoutside the UK (Y/N) [DEFAULT=N]