Database Changes for 4.36

This section contains details of database changes between the full 4.35 and 4.36 releases.

Table changes:

The following tables have been added.

Table Name	Description
ILP_INSTANCE_REVIEW_ANSWERS	An answer to a review question by either the learner or staff, for an ILP.
ILP_INSTANCE_REVIEW_PEOPLE	Staff members specifically assigned to a learners ILP review instance.
ILP_INSTANCE_REVIEW_QUESTIONS	List of questions for a learners review, for an ILP. Links question snapshots to the review instance.
ILP_QUESTION_SNAPSHOTS	A versioned copy of an ILP Question Definition.
LLWR_SUSPENSIONS	To record student periods of Suspensions of study relating to Learning Programmes.
PRIOR_ATTAINMENT_ENTITY	Hold Prior_Level and DateLevelApp data linked to individual Learners which is required for the ILR return against Learner records.
THIRDPARTY_IDENTIFIERS	Describe mappings between ebs entities and third party systems.
UIO_HESA_FINANCE	An extension of UIO table in order to accommodate HESA Finance specific information.
UI_HESA_DELIVORGANISATIONS	Records the delivery organisations and locations associated with a HESA SA returned Course.

New columns added to existing tables

The following columns have been added.

Table Name	Column Name	Type (Size)	Nullable	Description
ILP_REVIEW_DEFINITIONS	CONCLUSION_MESSAGE	VARCHAR2(500)	Y	The message given to the user when they complete a review, providing information on the next steps where applicable.
ILP_REVIEW_DEFINITIONS	INTRODUCTION_MESSAGE	VARCHAR2(500)	Y	The message given to the user when they open a review, providing explanation and guidance where applicable.
LEARNER_EMPLOYMENT	OTHER_EMPLOYMENT_TYPE1	VARCHAR2(3)	Y	To identify other types of employment status such as redundancy (OET1) [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=EMPLOYMENT_OTHER_TYPE)].
LEARNER_EMPLOYMENT	OTHER_EMPLOYMENT_TYPE2	VARCHAR2(3)	Y	To identify other types of employment status such as redundancy (OET2) [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=EMPLOYMENT_OTHER_TYPE)].
LEARNING_PROG	ACTUALASYNCHRONOUSHOURS	NUMBER(4)	Y	(LP86) The total actual hours rendered for the entire Learning Programme.(Non-tutor led)
LEARNING_PROG	ACTUALSYNCHRONOUSHOURS	NUMBER(4)	Y	(LP87) The total actual hours rendered for the entire Learning Programme.(Tutor led)
LEARNING_PROG	ESSENTIALSKILLSDISPENSATION	VARCHAR2(1)	Y	(LP89) Adjustment for the minimum requirement on English and Math or both upon assessment.
LEARNING_PROG	EXPECTEDASYNCHRONOUSHOURS	NUMBER(4)	Y	(LP84) The total estimated hours for the entire Learning Programme.(Non-tutor led)
LEARNING_PROG	EXPECTEDSYNCHRONOUSHOURS	NUMBER(4)	Y	(LP85) The total estimated hours for the entire Learning Programme.(Tutor led)
LEARNING_PROG	WELSHMEDIUMPERCENTAGE	NUMBER(4)	Y	(LP88) Welsh/bilingual medium delivery indicator at a programme level. This will be the percentage of the programme which will be delivered in Welsh.
ORGANISATION_UNITS	APPRENTICE_LEVY	VARCHAR2(2)	Y	The Apprentice Levy Indicator (LP82) [FK=ELWA_Verifiers.CODE (RV_DOMAIN=APPRENTICELEVY)].
ORGANISATION_UNITS	EMPLOYER_SIZEID	VARCHAR2(1)	Y	Details of the number of staff employed by the employer [FK=ELWA_Verifiers.CODE (RV_DOMAIN=EMPSIZE)].
ORGANISATION_UNITS	LLWR_EXPORT_ACCEPTANCE_DATE	DATE	Y	The date on which the record was accepted as error free by DCELLS.
ORGANISATION_UNITS	SMECODE	VARCHAR2(1)	Y	Indicator of status of employer as public, private or third sector Small and Medium Enterprise (SME) or non-SME, as confirmed in the relevant signed SME4 form [FK=ELWA_Verifiers.CODE (RV_DOMAIN=SMECODE)].
ORG_UNIT_PEOPLE	CONTACTID	NUMBER(10)	Y	Unique ID allocated by the learning provider to the Employer Contact.
ORG_UNIT_PEOPLE	CONTACTS_DELETE_FLAG	VARCHAR2(1)	N	The indicator when the Contacts has been deleted by LLWR Deletion process [Y/N] [DEFAULT=N].
ORG_UNIT_PEOPLE	CONTACTS_EXPORT_ACCEPT_DATE	DATE	Y	The date on which the Employment Contact record was accepted as error free by DCELLS.
ORG_UNIT_PEOPLE	EMPLOYER_DELETE_FLAG	VARCHAR2(1)	N	The indicator when the Employer has been deleted by LLWR Deletion processes [Y/N] [DEFAULT=N].
ORG_UNIT_PEOPLE	EMPLOYER_EXPORT_ACCEPT_DATE	DATE	Y	The date on which the Employer record was accepted as error free by DCELLS.
ORG_UNIT_PEOPLE	EMPPERIOD_DELETE_FLAG	VARCHAR2(1)	N	The indicator when the Employment Period has been deleted by the LLWR Deletion processes [Y/N] [DEFAULT=N].
ORG_UNIT_PEOPLE	EMPPERIOD_EXPORT_ACCEPT_DATE	DATE	Y	The date on which the Employment Period record was accepted as error free by DCELLS.
ORG_UNIT_PEOPLE	LEARNERID	VARCHAR2(20)	Y	Reference number for the learner, allocated by the learning provider.
ORG_UNIT_PEOPLE	PROGRAMMEID	VARCHAR2(10)	Y	Identifier for each learning programme with provider for this learner.
ORG_UNIT_PEOPLE	PROVIDERID	VARCHAR2(8)	Y	Provider code as supplied by the Welsh Government of the College the return is for.
ORG_UNIT_PEOPLE	TERMINATIONREASONID	VARCHAR2(40)	Y	The Reason of Employment Period ended [FK=ELWA_VERIFIERS.CODE(RV_DOMAIN=Termination_Reason)].
PEOPLE_HESA	APELCRD	VARCHAR2(2)	Y	APEL Credits : Accreditation of Prior Experiential Learning (APEL) is the recognition of Learning and Achievement outside formal education or training. [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=APELCRD)]
PEOPLE_HESA	CARELEAVER	VARCHAR2(2)	Y	Whether student is a Care Leaver. [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=CARELEAVER)]
PEOPLE_HESA	CRDPTSTU	VARCHAR2(3)	Y	Previous Study Points : The number of credit points being used from a previous award towards the students current study.
PEOPLE_HESA	CRDSCM	VARCHAR2(2)	Y	Credit Points Transfer Scheme : Identifies the credit scheme to give value to CRDPTSTU. [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=CRDSCM)]
PEOPLE_HESA	FES_USER_1	VARCHAR2(10)	Y	FES User Field 1.
PEOPLE_HESA	FES_USER_2	VARCHAR2(10)	Y	FES User Field 2.
PEOPLE_HESA	FES_USER_3	VARCHAR2(10)	Y	FES User Field 3.
PEOPLE_HESA	FES_USER_4	VARCHAR2(10)	Y	FES User Field 4.
PEOPLE_HESA	FES_USER_5	VARCHAR2(20)	Y	FES User Field 5.
PEOPLE_HESA	FES_USER_6	VARCHAR2(20)	Y	FES User Field 6.
PEOPLE_HESA	FES_USER_7	VARCHAR2(50)	Y	FES User Field 7.
PEOPLE_HESA	FES_USER_8	VARCHAR2(50)	Y	FES User Field 8.
PEOPLE_HESA	PARED	VARCHAR2(2)	Y	Parents Education : Records information about whether an entrants parents have higher education qualifications. [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=PARED)]
PEOPLE_HESA	SEC	VARCHAR2(2)	Y	HESA Specific Socio Economic Classification : Socio-Economic Classification of students participating in higher education if aged 21 or over. [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SEC)]
PEOPLE_UNITS	REBOOK_ATTEMPTS	NUMBER(2)	Y	Number of times an applicant has attempted to re-book an interview.
PEOPLE_UNITS_CDR	AWARD_DATE	DATE	Y	The date on which the exam board grade was awarded.
PEOPLE_UNITS_CDR	EXAM_BRD_GRADE	VARCHAR2(3)	Y	The exam board grade awarded to the learner.
UIO_LLWR	ACTUALASYNCHRONOUSHOURS	NUMBER(4)	Y	(LP86) The total actual hours rendered for the entire Learning Programme.(Non-tutor led)
UIO_LLWR	ACTUALSYNCHRONOUSHOURS	NUMBER(4)	Y	(LP87) The total actual hours rendered for the entire Learning Programme.(Tutor led)
UIO_LLWR	ESSENTIALSKILLSDISPENSATION	VARCHAR2(1)	Y	(LP89) Adjustment for the minimum requirement on English and Math or both upon assessment.
UIO_LLWR	EXPECTEDASYNCHRONOUSHOURS	NUMBER(4)	Y	(LP84) The total estimated hours for the entire Learning Programme.(Non-tutor led)
UIO_LLWR	EXPECTEDSYNCHRONOUSHOURS	NUMBER(4)	Y	(LP85) The total estimated hours for the entire Learning Programme.(Tutor led)
UIO_LLWR	WELSHMEDIUMPERCENTAGE	NUMBER(4)	Y	(LP88) Welsh/bilingual medium delivery indicator at a programme level. This will be the percentage of the programme which will be delivered in Welsh.
UNIT_INSTANCE_OCCURRENCES	SP_CAN_APPLY_THEN_ENROL	VARCHAR2(1)	N	Indicator if a learner can apply then enrol [Y/N] [DEFAULT=N].

Changes to existing columns

The following columns have been changed.

Table Name	Column Name	Type (Size)	Nullable	Previous Values Type (Size) [Nullable]
APP_FRAMEWORK_PATHWAYS	PATHWAY_CODE	NUMBER(4)	Y	NUMBER(3)[Y]
ILP_COMMENTS	COMMENT_TEXT	VARCHAR2(2000)	N	VARCHAR2(500)[N]
ILP_TARGET_STEPS	ILP_TARGET_DEF_STEP_ID	NUMBER(10)	Y	NUMBER(10)[N]
LEARNER_AIMS	APPRENTICESHIP_PATHWAY	VARCHAR2(4)	Y	VARCHAR2(3)[Y]
PEOPLE_UNITS_SPECIAL	APPRENTICESHIP_PATHWAY	VARCHAR2(4)	Y	VARCHAR2(3)[Y]
UIO_QUAL_AIMS	APPRENTICESHIP_PATHWAY	VARCHAR2(4)	Y	VARCHAR2(3)[Y]

View changes:

New columns in existing views

The following columns have been added.

View Name	Column Name
EBS_BASKET_ENROLMENTS	REBOOK_ATTEMPTS
EBS_BULK_ENROLMENT	PU_CDR_AWARD_DATE
EBS_BULK_ENROLMENT	PU_CDR_EXAM_BRD_GRADE
EBS_CORRESPONDENCE_STUDENTS	PERSONAL_EMAIL
EBS_COURSE_ASMT_RESULTS_ANSW	NUMBER_OF_RESITS
EBS_COURSE_ASSESSMENT_RESULTS	NUMBER_OF_RESITS
EBS_EMPLOYMENTS	ENROLMENTGROUP
EBS_EMPLOYMENTS	LEARNERID
EBS_EMPLOYMENTS	LEARNING_PROG_ID
EBS_EMPLOYMENTS	PERSONCODE2
EBS_EMPLOYMENTS	PROGRAMMEID
EBS_EMPLOYMENTS	PROVIDERID
EBS_EMPLOYMENTS	PROVIDER_ID
EBS_EMPLOYMENTS	EMPPERIOD_EXPORT_ACCEPT_DATE
EBS_EMPLOYMENTS	EMPPERIOD_DELETE_FLAG
EBS_EMPLOYMENTS	ACCEPTANCE_STATUS
EBS_EMPLOYMENTS	CONTACTID
EBS_EMPLOYMENTS	TERMINATIONREASONID
EBS_EMPLOYMENTS	DELETE_FLAG
EBS_EMPLOYMENTS	CONTACT_DETAILS
EBS_EMPLOYMENTS_AND_UNSPEC	PERSONCODE2
EBS_EMPLOYMENTS_AND_UNSPEC	LEARNING_PROG_ID
EBS_EMPLOYMENTS_AND_UNSPEC	LEARNERID
EBS_EMPLOYMENTS_AND_UNSPEC	ENROLMENTGROUP
EBS_EMPLOYMENTS_AND_UNSPEC	EMPPERIOD_EXPORT_ACCEPT_DATE
EBS_EMPLOYMENTS_AND_UNSPEC	EMPPERIOD_DELETE_FLAG
EBS_EMPLOYMENTS_AND_UNSPEC	DELETE_FLAG
EBS_EMPLOYMENTS_AND_UNSPEC	CONTACT_DETAILS
EBS_EMPLOYMENTS_AND_UNSPEC	CONTACTID
EBS_EMPLOYMENTS_AND_UNSPEC	ACCEPTANCE_STATUS
EBS_EMPLOYMENTS_AND_UNSPEC	PROGRAMMEID
EBS_EMPLOYMENTS_AND_UNSPEC	TERMINATIONREASONID
EBS_EMPLOYMENTS_AND_UNSPEC	PROVIDER_ID
EBS_EMPLOYMENTS_AND_UNSPEC	PROVIDERID
EBS_EXAM_CANDIDATES	APELCRD
EBS_EXAM_CANDIDATES	CARELEAVER
EBS_EXAM_CANDIDATES	CRDPTSTU
EBS_EXAM_CANDIDATES	CRDSCM
EBS_EXAM_CANDIDATES	PARED
EBS_EXAM_CANDIDATES	SEC
EBS_LEARNERS	APELCRD
EBS_LEARNERS	CARELEAVER
EBS_LEARNERS	CRDPTSTU
EBS_LEARNERS	CRDSCM
EBS_LEARNERS	PARED
EBS_LEARNERS	SEC
EBS_LEARNERS_DEDUP	APELCRD
EBS_LEARNERS_DEDUP	CARELEAVER
EBS_LEARNERS_DEDUP	SEC
EBS_LEARNERS_DEDUP	PARED
EBS_LEARNERS_DEDUP	CRDSCM
EBS_LEARNERS_DEDUP	CRDPTSTU
EBS_LEARNERS_DEDUP_ANSW	DECLARED_SEX
EBS_LEARNERS_DEDUP_ANSW	GENDER
EBS_LEARNERS_DEDUP_ANSW	HAS_DEPENDENT_ADULT
EBS_LEARNERS_DEDUP_ANSW	HAS_DEPENDENT_CHILD
EBS_LEARNERS_DEDUP_ANSW	VPRS
EBS_LEARNERS_DEDUP_ANSW	PRONOUNS
EBS_LEARNERS_DEDUP_ANSW	P_CDR_HAS_NO_DEPENDANTS
EBS_LEARNERS_DEDUP_ANSW	HAS_DISABLED_DEPENDANT
EBS_LEARNERS_DEDUP_ANSW	HOMELESS_EXCLUSION
EBS_LEARNERS_NSW	DECLARED_SEX
EBS_LEARNERS_NSW	GENDER
EBS_LEARNERS_NSW	HAS_DEPENDENT_ADULT
EBS_LEARNERS_NSW	HAS_DEPENDENT_CHILD
EBS_LEARNERS_NSW	HAS_DISABLED_DEPENDANT
EBS_LEARNERS_NSW	HOMELESS_EXCLUSION
EBS_LEARNERS_NSW	PRONOUNS
EBS_LEARNERS_NSW	P_CDR_HAS_NO_DEPENDANTS
EBS_LEARNERS_NSW	VPRS
EBS_LEARNER_EMPLOYMENT	OTHER_EMPLOYMENT_TYPE1
EBS_LEARNER_EMPLOYMENT	OTHER_EMPLOYMENT_TYPE2
EBS_LEARNER_ENROLMENTS	PU_CDR_AWARD_DATE
EBS_LEARNER_ENROLMENTS	PU_CDR_EXAM_BRD_GRADE
EBS_LLWR_LEARNING_PROG	WELSHMEDIUMPERCENTAGE
EBS_LLWR_LEARNING_PROG	ACTUALSYNCHRONOUSHOURS
EBS_LLWR_LEARNING_PROG	ESSENTIALSKILLSDISPENSATION
EBS_LLWR_LEARNING_PROG	EXPECTEDASYNCHRONOUSHOURS
EBS_LLWR_LEARNING_PROG	EXPECTEDSYNCHRONOUSHOURS
EBS_LLWR_LEARNING_PROG	ACTUALASYNCHRONOUSHOURS
EBS_ORGANISATION_REGIONS	APPRENTICE_LEVY
EBS_ORGANISATION_REGIONS	SMECODE
EBS_ORGANISATION_REGIONS	LLWR_EXPORT_ACCEPTANCE_DATE
EBS_ORGANISATION_REGIONS	EMPLOYER_SIZEID
EBS_ORGANISATION_UNITS	APPRENTICE_LEVY
EBS_ORGANISATION_UNITS	EMPLOYER_SIZEID
EBS_ORGANISATION_UNITS	SMECODE
EBS_ORGANISATION_UNIT_LINKS	LLWR_EXPORT_ACCEPTANCE_DATE
EBS_ORGANISATION_UNIT_LINKS	EMPLOYER_SIZEID
EBS_ORGANISATION_UNIT_LINKS	APPRENTICE_LEVY
EBS_ORGANISATION_UNIT_LINKS	SMECODE
EBS_ORG_UNIT_PEOPLE_REGIONS	EMPPERIOD_EXPORT_ACCEPT_DATE
EBS_ORG_UNIT_PEOPLE_REGIONS	EMPPERIOD_DELETE_FLAG
EBS_ORG_UNIT_PEOPLE_REGIONS	EMPLOYER_EXPORT_ACCEPT_DATE
EBS_ORG_UNIT_PEOPLE_REGIONS	EMPLOYER_DELETE_FLAG
EBS_ORG_UNIT_PEOPLE_REGIONS	CONTACTS_EXPORT_ACCEPT_DATE
EBS_ORG_UNIT_PEOPLE_REGIONS	CONTACTS_DELETE_FLAG
EBS_ORG_UNIT_PEOPLE_REGIONS	CONTACTID
EBS_ORG_UNIT_PEOPLE_REGIONS	LEARNERID
EBS_ORG_UNIT_PEOPLE_REGIONS	TERMINATIONREASONID
EBS_ORG_UNIT_PEOPLE_REGIONS	PROVIDERID
EBS_ORG_UNIT_PEOPLE_REGIONS	PROGRAMMEID
EBS_PEOPLE_GROUPS	ACTUALASYNCHRONOUSHOURS
EBS_PEOPLE_GROUPS	ACTUALSYNCHRONOUSHOURS
EBS_PEOPLE_GROUPS	ESSENTIALSKILLSDISPENSATION
EBS_PEOPLE_GROUPS	EXPECTEDASYNCHRONOUSHOURS
EBS_PEOPLE_GROUPS	EXPECTEDSYNCHRONOUSHOURS
EBS_PEOPLE_GROUPS	WELSHMEDIUMPERCENTAGE
EBS_PEOPLE_LP_GROUPS	ACTUALASYNCHRONOUSHOURS
EBS_PEOPLE_LP_GROUPS	ACTUALSYNCHRONOUSHOURS
EBS_PEOPLE_LP_GROUPS	ESSENTIALSKILLSDISPENSATION
EBS_PEOPLE_LP_GROUPS	EXPECTEDASYNCHRONOUSHOURS
EBS_PEOPLE_LP_GROUPS	EXPECTEDSYNCHRONOUSHOURS
EBS_PEOPLE_LP_GROUPS	WELSHMEDIUMPERCENTAGE
EBS_PEOPLE_UNITS	PU_CDR_AWARD_DATE
EBS_PEOPLE_UNITS	PU_CDR_EXAM_BRD_GRADE
EBS_PEOPLE_UNIT_LINKS	REBOOK_ATTEMPTS
EBS_UIO	SP_CAN_APPLY_THEN_ENROL
EBS_UIO_CHILD_LINKS	SP_CAN_APPLY_THEN_ENROL
EBS_UIO_LLWR	ACTUALSYNCHRONOUSHOURS
EBS_UIO_LLWR	ACTUALASYNCHRONOUSHOURS
EBS_UIO_LLWR	ESSENTIALSKILLSDISPENSATION
EBS_UIO_LLWR	SP_CAN_APPLY_THEN_ENROL
EBS_UIO_LLWR	EXPECTEDASYNCHRONOUSHOURS
EBS_UIO_LLWR	EXPECTEDSYNCHRONOUSHOURS
EBS_UIO_LLWR	WELSHMEDIUMPERCENTAGE
EBS_UNIT_SPECIAL	PU_CDR_AWARD_DATE
EBS_UNIT_SPECIAL	PU_CDR_EXAM_BRD_GRADE

Service Pack 1 Changes

The following changes have been made for 4.36 Service Pack 1:

New table added

The following table has been added.

Table Name	Description
PEOPLE_UNITS_SPECIAL_HESA_FINANCE	Financial support received by a student which is different from other students studying on the same course who are also included in the return.

New columns added to existing tables

The following column has been added.

Table Name	Column Name	Type (Size)	Nullable	Description
PEOPLE_UNITS_CDR	PROG_PATHWAY_DATE	Date	Y	Populated if the programme pathway is Introductory or Referral. If Introductory, the date the learner has completed the introductory phase and commences on the full Traineeship. If Referral, the date the learner commences the Level 1 course.

Service Pack 2 Changes

The following changes have been made for 4.36 Service Pack 2: