Database Changes for 4.38

This section contains details of database changes between the 4.37 and 4.38 releases.

Table changes:

New tables included in this release

Table Name	Description
AUTHORITY_TO_DELIVER	A table to specify which enrolment locations have authority to deliver which products.
INTEGRATION_SYNC_LOG	New table to store meta data about integration processing.
LLWR_WAGESUBSIDY	Learner wage subsidies for the JGW+ programme.
LOAN_PERCENTAGE	Defines loan fee percentage for FEE-HELP/VSL loans.
OUTCOME_CODE_MAPPINGS	Table to store mapping of combinations of ebs enrolment progress codes and/or progress reasons to TCSI Course Outcome Codes.
PROGRESS_CODE_TCSI_MAPPINGS	TCSI return student statuses mapped to ebs progress codes. Only one set of mappings can exist for a progress code.
REG_EVENT_SELF_ATTENDEES	The learner mark once his PIN has been submitted and details about the context at the moment of submission.
REGISTER_EVENT_PIN	A generated self-attendance pin for a single event in time, which the learners can use to self attend.
UI_REPLACEMENTS	Replacement information for a unit instance code.
UIO_DAYS_TIMES	Days of week and time of day linked to an offering.

New columns added to existing tables

Table Name	Column Name	Type (Size)	Nullable	Description
COHORTS	START_DATE	datetime	Y	The start date for course enrolments to which this cohort group applies.
COHORTS	END_DATE	datetime	Y	The end date for course enrolments to which this cohort group applies.
COHORTS	IS_ACTIVE	nvarchar(1)	N	Specifies whether the cohort group is active or not (Y/N) [DEFAULT=Y].
COMMUNICATIONS	RECIPIENT_TYPE	nvarchar(1)	Y	Recipient type of the correspondence. E for Employers and NULL for not specified.
COMMUNICATIONS	RECIPIENT_ID	numeric(10, 0)	Y	ID of the recipient for which the correspondence was sent [FK=ORGANISATION_UNITS.ID when RECIPIENT_TYPE=E].
COMMUNICATIONS	RECIPIENT_CONTACT_ID	numeric(10, 0)	Y	ID of the contact linked to the recipient that the correspondence was sent to [FK=PEOPLE.PERSON_CODE when RECIPIENT_TYPE=E].
CONFIGURABLE_STATUSES	IS_TGA	nvarchar(1)	N	Boolean (Y/N) [Default=N]. Whether the status refers to a product downloaded from TGA or not
EDGE_DATA_SYNC_HASH	EDGE_IDENTIFIER	nvarchar(250)	Y	Unique identifier generated by ebs and used to identify ebs data objects in Edge for a specified interface and instance. The same ebs object can be synced with Edge for a different interface and instance.
EDGE_INTEGRATION_HISTORY	EDGE_IDENTIFIER	nvarchar(250)	N	Unique identifier generated by ebs and used to identify ebs data objects in Edge for a specified interface and instance. The same ebs object can be synced with Edge for a different interface and instance.
ELWA_ERR_LOGS	WAGE_SUBSIDY_ID	numeric(10, 0)	Y	The Wage Subsidy with an error, associated to [FK=LLWR_WAGESUBSIDY.ID].
FEE_CATEGORIES	AVETMISS_CATEGORY	nvarchar(1)	Y	Allows a fee category to be flagged as a tuition or a resource fee for AVETMISS reporting purposes. R = Resource, T = Tuition
GRADUATE_OUTCOME_SURVEY	XCAH01_1	varchar(5)	Y	The Common Aggregation Hierarchy, first level grouping 1.
GRADUATE_OUTCOME_SURVEY	XCAH01_2	varchar(5)	Y	The Common Aggregation Hierarchy, first level grouping 2.
GRADUATE_OUTCOME_SURVEY	XCAH01_3	varchar(5)	Y	The Common Aggregation Hierarchy, first level grouping 3.
GRADUATE_OUTCOME_SURVEY	XCAH01_4	varchar(5)	Y	The Common Aggregation Hierarchy, first level grouping 4.
GRADUATE_OUTCOME_SURVEY	XCAH01_5	varchar(5)	Y	The Common Aggregation Hierarchy, first level grouping 5.
GRADUATE_OUTCOME_SURVEY	XCAH03_1	varchar(11)	Y	The Common Aggregation Hierarchy, third level grouping 1.
GRADUATE_OUTCOME_SURVEY	XCAH03_2	varchar(11)	Y	The Common Aggregation Hierarchy, third level grouping 2.
GRADUATE_OUTCOME_SURVEY	XCAH03_3	varchar(11)	Y	The Common Aggregation Hierarchy, third level grouping 3.
GRADUATE_OUTCOME_SURVEY	XCAH03_4	varchar(11)	Y	The Common Aggregation Hierarchy, third level grouping 4.
GRADUATE_OUTCOME_SURVEY	XCAH03_5	varchar(11)	Y	The Common Aggregation Hierarchy, third level grouping 5.
ILP_INSTANCE_REVIEWS	HEADER_SNAPSHOT	nvarchar(4000)	Y	A JSON snapshot of the header data for an ILP instance review once it has been completed.
LEARNING_ACT	JGWACTIVITYDESCRIPTORS	numeric(2, 0)	Y	The activity descriptions for learners on the JGW+ programme [LA49].
LEARNING_ACT	EMPLOYERID	numeric(10, 0)	Y	The unique ID allocated to the employer by the lead contractor [LA50].
PEOPLE_DEDUPLICATION_AUDIT	EXTERNAL_REF	nvarchar(256)	Y	A copy of the value from the user record for the source learner in the de-duplication process.
PEOPLE_UNITS	HIGHEST_ATTAINMENT_CODE	nvarchar(40)	Y	Learner's highest level of prior educational achievement. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=PRIOR_EDUCATION_IDENTIFIER)]
PEOPLE_UNITS_VSL	ORIGINAL_AMOUNT_CHARGED	numeric(7, 2)	Y	The amount charged to the student prior to withdrawal.
PEOPLE_UNITS_VSL	ORIGINAL_AMOUNT_PAID_UPFRONT	numeric(7, 2)	Y	The amount the student paid upfront prior to withdrawal.
PEOPLE_UNITS_VSL	ORIGINAL_LOAN_AMOUNT	numeric(7, 2)	Y	The loan amount for the student prior to withdrawal.
PEOPLE_UNITS_VSL	ORIGINAL_LOAN_FEE	numeric(7, 2)	Y	The loan fee for the student prior to withdrawal.
PEOPLE_UNITS_VSL	ATTENDANCE_MODE	nvarchar(40)	Y	VSL/HELP Attendance Mode (E329) [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=VFH_DELIVERY_MODE)]
PERSON_FUNCTIONS	IS_ILP	nvarchar(1)	N	Enables the identification of ILP staff when used in registers (Y/N) [DEFAULT=N].
PROGRESS_CODES	AVETMISS_PROGRAM_STATUS	nvarchar(40)	Y	The AVETMISS Program Status Identifier that this progress code maps to for the AVETMISS return. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=AVETMISS_PROGRAM_STATUS)]
REGISTER_EVENT_DETAILS	CANCEL_REASON	nvarchar(40)	Y	Interview cancel reason [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=INTERVIEW_CANCEL_REASON)].
ROLE_DEFS	IS_VISIBLE	nvarchar(1)	N	Whether the role is visible in the application or not [Y/N, Default value = Y]
THIRDPARTY_LINKS	IS_USE_EXTERNAL	varchar(1)	N	Specifies whether to open the link on an external webview (Y/N) [DEFAULT=N].
UNIT_INSTANCE_OCCURRENCES	ATTENDANCE_TYPE	nvarchar(40)	Y	Type of Attendance for the Course [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=DEEWR_TYPE_OF_ATTENDANCE)]
UNIT_INSTANCES	TRAINING_PACKAGE_CODE	nvarchar(20)	Y	The code for the training package that this unit instance belongs to. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=NATIONAL_CODE)]
UNIT_INSTANCES	TRAINING_PACKAGE_TITLE	nvarchar(100)	Y	The title for the training package that this unit instance belongs to.
UNIT_INSTANCES	TGA_RECOMMENDED_USAGE	nvarchar(100)	Y	The recommended usage for this unit instance as specified by TGA download.
UNIT_INSTANCES	OBSOLETE_FROM_DATE	datetime	Y	The date when this unit instance becomes obsolete.
WORKFLOW_LAST_EXECUTION	LAST_SYNC_TIMESTAMP	datetimeoffset(7)	Y	Last timestamp when the workflow identified by WORKFLOW_REFERENCE was successful.

Changes to existing columns

Table Name	Column Name	Type (Size)	Nullable	Previous Values Type (Size) [Nullable]
EMAIL_TEMPLATES	EMAIL_TYPE	nvarchar(40)	N	nvarchar(20) [N]
MESSAGE_LOGS	DATA_1	nvarchar(100)	Y	nvarchar(60) [Y]
MESSAGE_LOGS	DATA_2	nvarchar(100)	Y	nvarchar(60) [Y]
MESSAGE_LOGS	DATA_3	nvarchar(100)	Y	nvarchar(60) [Y]
MESSAGE_LOGS	DATA_4	nvarchar(100)	Y	nvarchar(60) [Y]
MESSAGE_LOGS	DATA_5	nvarchar(100)	Y	nvarchar(60) [Y]
MESSAGE_LOGS	DATA_6	nvarchar(100)	Y	nvarchar(60) [Y]
MESSAGE_LOGS	DATA_7	nvarchar(100)	Y	nvarchar(60) [Y]
MESSAGE_LOGS	DATA_8	nvarchar(100)	Y	nvarchar(60) [Y]
PEOPLE_UNITS	AMNT_PRIOR_LRN_CREDIT_USED	numeric(7, 3)	Y	numeric(4, 0) [Y]
PEOPLE_VSL	HAS_HS_OR_CERTIV	nvarchar(1)	N	nvarchar(1) [Y]
PEOPLE_VSL	HAS_HS_OR_CERTIV_BEEN_VERIFIED	nvarchar(1)	N	nvarchar(1) [Y]
PEOPLE_VSL	HAS_LLN_BEEN_VERIFIED	nvarchar(1)	N	nvarchar(1) [Y]
REPORT_EVENTS	EMAIL_TYPE	nvarchar(40)	N	nvarchar(20) [N]
UNIT_INSTANCE_OCCURRENCES	BOOKABLE_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	FES_ACTIVE_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	FES_FINISHED_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	FES_MIN_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	FES_NON_STARTER_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	FES_TRANSFERRED_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	FES_TRIGGER_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	FES_WITHDRAWN_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	PLACES_BOOKED	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	TARGET_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	TOTAL_PLACES	numeric(6, 0)	Y	numeric(4, 0) [Y]
UNIT_INSTANCE_OCCURRENCES	TOTAL_PLACES_APPS	numeric(6, 0)	Y	numeric(4, 0) [Y]

Existing columns dropped

Table Name	Column Name	Type (Size)	Nullable	Description
SKILLS_LIST_ITEMS	IS_NAT_AVAIL_FND_SKILLS	nvarchar(1)	N	Whether the skill is nationally available for the Foundation Skills program stream [Y/N, Default value = N]
SKILLS_LIST_ITEMS	IS_NAT_AVAIL_FULL_QUAL	nvarchar(1)	N	Whether the skill is nationally available for the Full Qualifications program stream [Y/N, Default value = N]
SKILLS_LIST_ITEMS	IS_NAT_AVAIL_APPRENTICESHIP	nvarchar(1)	N	Whether the skill is nationally available for the Apprenticeship program stream [Y/N, Default value = N]
SKILLS_LIST_ITEMS	IS_NAT_AVAIL_TRAINEESHIP	nvarchar(1)	N	Whether the skill is nationally available for the Traineeship program stream [Y/N, Default value = N]
SKILLS_LIST_ITEMS	IS_NAT_AVAIL_TRGT_PRIORITY	nvarchar(1)	N	Whether the skill is nationally available for the Targeted Priority program stream [Y/N, Default value = N]
SKILLS_LIST_ITEMS	IS_LOC_AVAIL_FULL_QUAL	nvarchar(1)	N	Whether the skill is locally available for the Full Qualifications program stream [Y/N, Default value = N]

View changes:

New views

New columns in existing views

View Name	Column Name
DASHBOARDAPPLICATIONS	CALENDAR_TYPE
DASHBOARDATTENDANCE	CALENDAR_TYPE
DASHBOARDCOURSETARGETS	CALENDAR_TYPE
DASHBOARDENROLMENTS	CALENDAR_TYPE
DASHBOARDFEES	CALENDAR_TYPE
EBS_AGENT_PROGRESSIONS	OFFERING_TYPE
EBS_AGENT_PROGRESSIONS	REGION_ORGANISATION
EBS_AGENT_PROGRESSIONS	SLOC_LOCATION_CODE
EBS_AGENT_PROGRESSIONS	UI_LEVEL
EBS_ASSESSMENTS_ANSW	REGION_ORGANISATION_DESC
EBS_ATMNTS_AWARD_OUTCOMES_ANSW	OFFERING_TYPE
EBS_ATMNTS_AWARD_OUTCOMES_ANSW	UI_LEVEL
EBS_BULK_ENROLMENT	HE_CREDIT_PROVIDER_DESC
EBS_BULK_ENROLMENT	REGION_ORGANISATION
EBS_CLIENT_INTERVIEWS	CANCEL_REASON
EBS_COURSE_ASMT_RESULTS_ANSW	ASSESSMENT_TYPE_OPS_MASTER
EBS_ENROLMENT_LOCATIONS_NSW	COUNTRY_CODE
EBS_EXTERNAL_REFUND_SUMMARY	REGION_CODE
EBS_EXTERNAL_REFUND_SUMMARY	REGION_NAME
EBS_LEARNER_APP_SPECIAL	REGION_ORGANISATION
EBS_LEARNER_ATTAINMENTS_ANSW	INCLUDE_IN_HESA
EBS_LEARNER_ATTAINMENTS_ANSW	OFFERING_TYPE
EBS_LEARNER_ATTAINMENTS_ANSW	REGION_ORGANISATION
EBS_LEARNER_ATTAINMENTS_ANSW	UI_LEVEL
EBS_LEARNER_ENROLMENTS	ATTENDANCE_MODE
EBS_LEARNER_ENROLMENTS	HIGHEST_ATTAINMENT_CODE
EBS_LEARNER_ENROLMENTS	ORIGINAL_AMOUNT_CHARGED
EBS_LEARNER_ENROLMENTS	ORIGINAL_AMOUNT_PAID_UPFRONT
EBS_LEARNER_ENROLMENTS	ORIGINAL_LOAN_AMOUNT
EBS_LEARNER_ENROLMENTS	ORIGINAL_LOAN_FEE
EBS_LEARNER_ENROLMENTS	PRIOR_EDUCATION_ACHIEVEMENT
EBS_LEARNERS	PRIMARYROLE
EBS_LEARNERS	PRIMARYROLE_DESC
EBS_LEARNERS	SECONDARYROLES
EBS_LEARNERS	SECONDARYROLES_DESC
EBS_LEARNERS_DEDUP	PRIMARYROLE
EBS_LEARNERS_DEDUP	PRIMARYROLE_DESC
EBS_LEARNERS_DEDUP	SECONDARYROLES
EBS_LEARNERS_DEDUP	SECONDARYROLES_DESC
EBS_LEARNERS_DEDUP_ANSW	PREV_BPAY_NOS
EBS_LEARNERS_NSW	PREV_BPAY_NOS
EBS_LLWR_LEARNING_ACT	EMPLOYERID
EBS_LLWR_LEARNING_ACT	JGWACTIVITYDESCRIPTORS
EBS_LLWR_LEARNING_ACT	TYPE_LPG
EBS_LOAN_SUMMARY	REGION_ORGANISATION
EBS_LOCATIONS	COUNTRY_CODE
EBS_NON_PRINTED_LOAN_SUMMARY	REGION_ORGANISATION
EBS_ORG_UNIT_PEOPLE_REGIONS	REGION_FES_ACTIVE
EBS_ORG_UNITS_FACULTY_REGIONS	DEPARTMENT_HEAD_FORMATTED
EBS_ORG_UNITS_FACULTY_REGIONS	EMPLOYER_REFERENCE_NUMBER
EBS_ORG_UNITS_FACULTY_REGIONS	INSTITUTION_CONTEXT_ID
EBS_ORG_UNITS_TS_LOCATIONS	DEPARTMENT_HEAD_FORMATTED
EBS_ORG_UNITS_TS_LOCATIONS	EMPLOYER_REFERENCE_NUMBER
EBS_ORG_UNITS_TS_LOCATIONS	INSTITUTION_CONTEXT_ID
EBS_PEOPLE_REGIONS	FES_ACTIVE
EBS_PEOPLE_UNIT_LINKS	HIGHEST_ATTAINMENT_CODE
EBS_REG_EVENTS	REGION_ORGANISATION_UNIT_ID
EBS_REG_EVENTS_SEARCH_VIEW	ORGANISATION_CODE
EBS_REG_EVENTS_SEARCH_VIEW	REGION_ORGANISATION_UNIT_ID
EBS_REG_TASK_LIST	REGION_CODE
EBS_REG_TASK_LIST	REGION_DESCRIPTION
EBS_REG_TASK_LIST	REGION_ID
EBS_REGION_ORGANISATIONS	REGION_FES_ACTIVE
EBS_REGIONS	FES_ACTIVE
EBS_ROOMS	ROOM_TYPE_LONG_DESC
EBS_ROOMS	ROOM_TYPE_SHORT_DESC
EBS_SPONSOR_INVOICE_SUMM_ANSW	FES_VAT_AMOUNT
EBS_STAFF_ALL_NSW	CURRENT_DATE
EBS_STAFF_ALL_NSW	END_DATE
EBS_STAFF_ALL_NSW	START_DATE
EBS_STAFF_NSW	REGION_ID
EBS_UI_LINKS	IS_MANAGED_BY_TGA
EBS_UIO	ATTENDANCE_TYPE
EBS_UIO	CALOCC_DESC
EBS_UIO	LOCATION_DESCRIPTION
EBS_UIO	NATIONAL_COURSE_CODE
EBS_UIO	REGION_ORGANISATION_DESC
EBS_UIO	UI_IS_FEE_HELP
EBS_UIO	UI_IS_VET_FEE_HELP
EBS_UIO_CHILD_LINKS	ATTENDANCE_TYPE
EBS_UIO_FEES	REGION_ORGANISATION_DESC
EBS_UIO_INPUTSEARCH_ANSW	AWARD_CATEGORY_CODE
EBS_UIO_INPUTSEARCH_ANSW	NATIONAL_COURSE_CODE
EBS_UIO_INPUTSEARCH_ANSW	REGION_ID
EBS_UIO_INPUTSEARCH_ANSW	REGION_ORGANISATION
EBS_UIO_INPUTSEARCH_ANSW	UI_DESCRIPTION
EBS_UIO_LLWR	ATTENDANCE_TYPE
EBS_UIO_LLWR	CALOCC_DESC
EBS_UIO_LLWR	LOCATION_DESCRIPTION
EBS_UIO_LLWR	NATIONAL_COURSE_CODE
EBS_UIO_LLWR	REGION_ORGANISATION_DESC
EBS_UIO_LLWR	UI_IS_FEE_HELP
EBS_UIO_LLWR	UI_IS_VET_FEE_HELP
EBS_UIO_PARENT_CHILD_DETAILS	CHILD_REGION_ORGANISATION
EBS_UIO_PARENT_CHILD_DETAILS	PARENT_REGION_ORGANISATION
EBS_UIO_PARENT_CHILD_DETAILS	YEAR_LONG_INDICATOR
EBS_UNIT_INSTANCES	IS_MANAGED_BY_TGA
EBS_UNIT_INSTANCES	IS_STATUS_TGA
EBS_UNIT_INSTANCES	LAST_TGA_SYNC_DATE
EBS_UNIT_INSTANCES	OBSOLETE_FROM_DATE
EBS_UNIT_INSTANCES	SPECIAL_COURSE_INDICATOR
EBS_UNIT_INSTANCES	TGA_RECOMMENDED_USAGE
EBS_UNIT_INSTANCES	TRAINING_PACKAGE_CODE
EBS_UNIT_INSTANCES	TRAINING_PACKAGE_TITLE
EBS_UNIT_INSTANCES_ANSW	IS_MANAGED_BY_TGA
EBS_UNIT_INSTANCES_ANSW	IS_STATUS_TGA
EBS_UNIT_INSTANCES_ANSW	OBSOLETE_FROM_DATE
EBS_UNIT_INSTANCES_ANSW	TGA_RECOMMENDED_USAGE
EBS_UNIT_INSTANCES_ANSW	TRAINING_PACKAGE_CODE
EBS_UNIT_INSTANCES_ANSW	TRAINING_PACKAGE_TITLE
EBS_UNIT_INSTANCES_CANENROCCS	IS_MANAGED_BY_TGA
EBS_UNIT_INSTANCES_CANENROCCS	IS_STATUS_TGA
EBS_UNIT_INSTANCES_CANENROCCS	LAST_TGA_SYNC_DATE
EBS_UNIT_INSTANCES_CANENROCCS	OBSOLETE_FROM_DATE
EBS_UNIT_INSTANCES_CANENROCCS	SPECIAL_COURSE_INDICATOR
EBS_UNIT_INSTANCES_CANENROCCS	TGA_RECOMMENDED_USAGE
EBS_UNIT_INSTANCES_CANENROCCS	TRAINING_PACKAGE_CODE
EBS_UNIT_INSTANCES_CANENROCCS	TRAINING_PACKAGE_TITLE
EBS_VERIFIER_FEE_CATEGORIES	AVETMISS_CATEGORY
EBS_VFH_INVOICE_NOTICE_SUMMARY	REGION_ORGANISATION

Columns dropped from existing views

Table Name	Description
EBS_OWNERSHIPS_ACTIONS	context
EBS_OWNERSHIPS_ACTIONS	institution_context_id

Central and Shape

Learn about the features of Central and Shape, such as applications, learner, curriculum, and so on.

Ontrack Staff Hub

Learn about the the features of Ontrack Staff Hub, such as concierge, safeguarding, and so on.

Ontrack Learner Hub

Learn about the features of Ontrack Learner Hub, such as applications, enrolments, and so on.

Ontrack Contact Hub

Learn about the features of Ontrack Contact Hub, such as attendance, progress, and so on.

Tribal Communities

Learn about the Tribal products, knowledge bases, raising a support ticket, and much more.

Go to Tribal Communities