Quercus Message Link Developer’s Guide<>

Reference – Messages

A1 – Course data (out)

Purpose

To provide course data when new records are created or existing records are updated.

Triggering events

Event

Table

Operation(s)

1

Change of Course data

COURSE

I/U

2

Change to Course Director data

COURSE_DIRECTOR

I/U/D

3

Change to Course Tutor data

COURSE_TUTOR

I/U/D

4

Change to Course Field of Study data

COURSE_FIELD_OF_STUDY

I/U/D

5

Change to Clearing House Course data

CLEARING_HOUSE_COURSE

I/U/D

6

Change to Course Award data

COURSE_AWARD

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

COURSE_OUT

Out

Course Code

Y

Varchar

16

Course Code (unique in Quercus)

Out

Title

N

Varchar

80

Course description

Out

Duration

N

Number

Duration of course in years

Out

Approved Date

N

Date

Date course was approved

Out

Closed Date

N

Date

Date course was closed

Out

Status Code

N

Varchar

16

Status of course

Out

Level Code

N

Varchar

16

Course Level Code

Out

Level Description

N

Varchar

80

Course Level Description

Out

Department Code

N

Varchar

16

Department code

Out

Department Description

N

Varchar

80

Department Description

Out

Mode of Study Code

N

Varchar

16

Mode of Study Code

Out

Mode of Study Description

N

Varchar

80

Mode of Study Description

Out

Fee Category Code

N

Varchar

16

Fee Category Code

Out

Fee Category Description

N

Varchar

80

Fee Category Description

Out

Fee Level Code

N

Varchar

16

Fee Level Code

Out

Fee Level Description

N

Varchar

80

Fee Level Description

Out

Student Category Code

N

Varchar

16

Student Category Code

Out

Student Category Description

N

Varchar

80

Student category Description

Course Director (QUERCUS.COURSE_DIRECTOR)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

First Name

N

Varchar

30

Person First Name

Out

Surname

Y

Varchar

30

Person Surname

Repeated List of Course Tutors (QUERCUS.COURSE_TUTOR)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

First Name

N

Varchar

30

Person First Name

Out

Surname

Y

Varchar

30

Person Surname

Repeated List of Field of Study (QUERCUS.COURSE_FIELD_OF_STUDY)

Out

Field of Study Code

N

Varchar

16

Field of Study

Out

Field of Study Description

N

Varchar

80

Field of Study Description

Repeated List of Clearing House Course (QUERCUS.CLEARING_HOUSE_COURSE)

Out

Clearing House Course

N

Varchar

16

Course Code used by clearing house

Out

Organisation Code

N

Varchar

16

Organisation Code of the Clearing House

Out

Course Stream Code

N

Varchar

16

Course Stream Code

Repeated List of Course Awards (QUERCUS.COURSE_AWARD)

Out

Award Code

N

Varchar

16

Award Code

Out

Description

N

Varchar

80

Award Description

Out

Start Date

N

Date

Start Date

Out

End Date

N

Date

End Date

Out

Course Award Type Code

N

Varchar

16

Course Award Type Code

Out

Course Award Type Description

N

Varchar

80

Course Award Type Description

Notes

There can only be a maximum of one course director.

Repeated lists for course tutors, field of study, clearing house course and course awards can be empty.

A1a – Course instance data (out)

Purpose

To provide course instance data when new records are created or existing records are updated.

Triggering events

Event

Table

Operation(s)

1

Change of Course Instance data

COURSE_INSTANCE_TABLE

I/U

2

Change to Course Instance Defaults

COURSE_INSTANCE_DEFAULTS

I/U/D

3

Change to Course Instance Timetable

COURSE_INSTANCE_TIMETABLE

I/U/D

4

Change to Course Instance Person

COURSE_INSTANCE_PERSON

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

COURSE_INSTANCE_OUT

Out

Course Code

Y

Varchar

16

Course Code (unique in Quercus)

Out

Instance Code

Y

Varchar

16

Course Instance Code

Out

Course Instance ID

Y

Varchar

30

Unique Course Instance Identifier

Out

Academic Session

Y

Number

COURSE_INSTANCE_TABLE.ACADEMIC_YEAR

Out

Course Year

N

Number

Course Year

Out

Location Code

N

Varchar

16

Location code

Out

Location Description

N

Varchar

80

Location Description

Out

Start Date

N

Date

Start Date

Out

End Date

N

Date

End Date

Out

Credits

N

Number

Number of credits

Out

Mode of Study Code

N

Varchar

16

Mode of Study Code

Out

Mode of Study Description

N

Varchar

80

Mode of Study Description

Out

Semesterised

N

Varchar

5

TRUE if semesterised

Out

Course Stream Code

N

Varchar

16

Course Stream Code

Out

Course Stream Description

N

Varchar

80

Course Stream Description

Out

Quota

N

Number

Quota allowed for course instance

Out

Status Code

Y

Varchar

16

Status of course instance

Out

Status Description

N

Varchar

80

Status Description

Out

Structure Levels

N

Number

Level of course curriculum

Out

Results Entry Level Code

N

Varchar

16

Results Entry Level Code

Out

Results Entry Level Description

N

Varchar

80

Results Entry Level Description

Out

Award Code

N

Varchar

16

Award Code for course instance

Out

Award Description

N

Varchar

80

Award Description

Out

Sessional Type

Y

Varchar

16

Single Session or Cross Sessional

Out

Packaged

Y

Number

1=YES, 0=NO

Out

VAT Rate Code

N

Varchar

16

VAT Rate Code

Out

VAT Rate Description

N

Varchar

80

VAT Rate Description

Out

Graduation Year

Y

Number

1=YES, 0=NO (defaults to 0)

Repeated List of Course Instance Timetable (QUERCUS.COURSE_INSTANCE_TIMETABLE)

Out

Day of the Week

N

Varchar

16

Day of the week

Out

Start Time

N

Varchar

5

Start Time (stored as 4 digit number on database)

Out

Duration

N

Number

Duration in hours

Course Instance Defaults (QUERCUS.COURSE_INSTANCE_DEFAULTS)

Out

Attendance Entry Level Code

N

Varchar

16

Level at which attendance is taken

Out

Number of Weeks

N

Number

Number of weeks the course instance is run

Out

Number of Hours

N

Number

Number of hours the course instance has

Out

Enrolment Target

N

Number

The targeted number of enrolments for the instance

Out

Retention Target

N

Number

Retention target of students on course

Out

Thesis Required

N

Number

1 = YES, 0=NO

Out

Credit Tolerance Plus

N

Number

Out

Credit Tolerance Minus

N

Number

Out

Approved Date

N

Date

Course Instance Approved Date

Out

Course End Date

N

Date

Course End Date

Out

Publish On Web

N

Number

1 = YES, 0=NO

Out

Franchised

N

Number

1 = YES, 0= NO – is the course instance franchised out to a partner organisation

Out

Organisation Code

N

Varchar

16

Franchise Organisation Code

Out

Fee Category Code

N

Varchar

16

Fee Category Code

Out

Fee Level Code

N

Varchar

16

Fee Level Code

Out

Student Category Code

N

Varchar

16

Student Category Code

Repeated List of Course Instance Persons (QUERCUS.COURSE_INSTANCE_PERSON)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

First Name

N

Varchar

30

Person First Name

Out

Surname

Y

Varchar

30

Person Surname

Out

Person Type Code

N

Varchar

16

Role of person (TUTOR, DEPARTMENT HEAD, etc.)

Out

Start Date

N

Date

Start date

Out

End Date

N

Date

End date

Notes

Repeated lists for course instance timetable and course instance person can be empty

A1b – Course curriculum data (out)

Purpose

To provide course curriculum data when new records are created or existing records are updated.

Triggering events

Event

Table

Operation(s)

1

Change to Course Program Detail

COURSE_PROGRAM_DETAIL

I/U/D

2

Change to Course Program Unit

COURSE_PROGRAM_UNIT

I/U/D

3

Change to Course Unit Detail

COURSE_UNIT_DETAIL

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

COURSE_CURRICULUM_OUT

Out

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Course Instance ID

Y

Varchar

30

Unique Course Instance Identifier

Repeated List of Course Curriculum (see structure below in note 1)

Repeated List of Level 1 Modules

Out

L1 Module Code

Y

Varchar

16

CPD.MODULE

Out

L1 Module Type Code

Y

Varchar

16

CPD.MODULE_TYPE

Out

L1 Semester

N

Number

CPD.COURSE_SEMESTER

Out

L1 Credits

N

Number

CPD.CREDITS

Out

L1 Overall Grade Weighting

N

Number

CPD.OVERALL_GRADE_WEIGHT

Out

L1 Award Year Subject

N

Varchar

5

CPD.AWARD_YEAR_SUBJECT

Values are TRUE or FALSE

Out

L1 Qualification Code

N

Varchar

8

CPD.QUALIFICATION

Out

Repeated List of Requisites for Level 1 Modules (REQUISITE.COURSE_PROGRAM_DETAIL)

Out

Nested Repeated List of Requisite Types (REQUISITE.REQUISITE_TYPE)

Out

Requisite Type Code

Y

Varchar

16

REQUISITE.REQUISITE_TYPE

Out

Nested Repeated List of Choices (REQUISITE_DETAIL.REQUISITE)

Out

Requisite Choice

Y

Number

REQUISITE_DETAIL.REQUISITE_CHOICE (unique per requisite)

Out

Number Required

N

Number

REQUISITE_DETAIL.NUMBER_REQUIRED

Out

Nested Repeated List of Modules (REQUISITE_MODULE.REQUISITE_DETAIL)

Out

Module Code

Y

Varchar

16

REQUISITE_MODULE.MODULE

Out

Repeated List of CPD Targets (CPD_TARGET)

Out

Target Type Code

Y

Varchar

16

TARGET_TYPE.TARGET_TYPE

Out

Year1

N

Number

CPD_TARGET.TARGETS.YEAR1

Out

Target_Year1

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR1

Out

Year2

N

Number

CPD_TARGET.TARGETS.YEAR2

Out

Target_Year2

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR2

Out

Year3

N

Number

CPD_TARGET.TARGETS.YEAR3

Out

Target_Year3

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR3

Out

Year4

N

Number

CPD_TARGET.TARGETS.YEAR3

Out

Target_Year4

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR3

Out

Year5

N

Number

CPD_TARGET.TARGETS.YEAR3

Out

Target_Year5

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR3

Out

Repeated List of CPD Tutors (CPD_TUTOR)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

N

Number

Quercus Person ID Number

Repeated List of Module Groups for Level 1 Modules (MODULE_GROUP_DETAIL)

Out

Module Group Code

Y

Varchar

16

MODULE_GROUP_DETAIL.MODULE_GROUP

Out

Group Type Code

N

Varchar

16

MODULE_GROUP_DETAIL.GROUP_TYPE

Out

Minimum Allowed

N

Number

MODULE_GROUP_DETAIL.MINIMUM

Out

Maximum Allowed

N

Number

MODULE_GROUP_DETAIL.MAXIMUM

Out

Minimum Credits Allowed

N

Number

MODULE_GROUP_DETAIL.MINIMUM_CREDITS

Out

Maximum Credits Allowed

N

Number

MODULE_GROUP_DETAIL.MAXIMUM_CREDITS

Out

Relation

N

Number

MODULE_GROUP_DETAIL.RELATION

(must be AND or OR)

Out

Nested Repeated List of Group Modules (GROUP_DETAIL_MODULES)

Out

Module Instance ID

Y

Varchar

30

GROUP_DETAIL_MODULES.COURSE_PROGRAM_DETAIL

Out

Module Type Code

N

Varchar

16

GROUP_DETAIL_MDOULES.MODULE_TYPE

Nested Repeated List of Level 2 Modules

Out

L2 Module Code

Y

Varchar

16

CPU.MODULE

Out

L2 Module Type Code

Y

Varchar

16

CPU.MODULE_TYPE

Out

L2 Semester

N

Number

CPU.COURSE_SEMESTER

Out

L2 Credits

N

Number

CPU.CREDITS

Out

L2 Overall Grade Weighting

N

Number

CPU.OVERALL_GRADE_WEIGHT

Out

L2 Award Year Subject

N

Varchar

5

CPU.AWARD_YEAR_SUBJECT

Values are TRUE or FALSE

Out

L2 Qualification Code

N

Varchar

8

CPU.QUALIFICATION

Out

Repeated List of Requisites for Level 2 Modules (REQUISITE.COURSE_PROGRAM_DETAIL)

Out

Nested Repeated List of Requisite Types (REQUISITE.REQUISITE_TYPE)

Out

Requisite Type Code

N

Varchar

16

REQUISITE.REQUISITE_TYPE

Out

Nested Repeated List of Choices (REQUISITE_DETAIL.REQUISITE)

Out

Requisite Choice

N

Number

REQUISITE_DETAIL.REQUISITE_CHOICE

Out

Number Required

N

Number

REQUISITE_DETAIL.NUMBER_REQUIRED

Out

Nested Repeated List of Modules (REQUISITE_MODULE.REQUISITE_DETAIL)

Out

Module Code

N

Varchar

16

REQUISITE_MODULE.MODULE

Repeated List of Module Groups for Level 2 Modules (MODULE_GROUP_UNIT)

Out

Module Group Code

Y

Varchar

16

MODULE_GROUP_UNIT.MODULE_GROUP

Out

Minimum Allowed

N

Number

MODULE_GROUP_UNIT.MINIMUM

Out

Maximum Allowed

N

Number

MODULE_GROUP_UNIT.MAXIMUM

Out

Minimum Credits Allowed

N

Number

MODULE_GROUP_UNIT.MINIMUM_CREDITS

Out

Maximum Credits Allowed

N

Number

MODULE_GROUP_UNIT.MAXIMUM_CREDITS

Out

Relation

N

Number

MODULE_GROUP_UNIT.RELATION

(must be AND or OR)

Out

Nested Repeated List of Group Modules (GROUP_DETAIL_UNITS)

Out

Module Instance ID

Y

Varchar

30

GROUP_DETAIL_UNIT.COURSE_PROGRAM_UNIT

Nested Repeated List of Level 3 Modules

Out

L3 Module Code

Y

Varchar

16

CUD.MODULE

Out

L3 Module Type Code

Y

Varchar

16

CUD.MODULE_TYPE

Out

L3 Semester

N

Number

CUD.COURSE_SEMESTER

Out

L3 Credits

N

Number

CUD.CREDITS

Out

L3 Overall Grade Weighting

N

Number

CUD.OVERALL_GRADE_WEIGHT

Out

L3 Award Year Subject

N

Varchar

5

CUD.AWARD_YEAR_SUBJECT

Values are TRUE or FALSE

Out

L3 Qualification Code

N

Varchar

8

CUD.QUALIFICATION

Notes

Course curriculum is nested. A level 1 module can have multiple level 2 modules and each of those level 2 modules can have multiple level 3 modules.

A2 – Course data (in)

Purpose

Update Quercus Course data with data from external sources.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

COURSE_IN

In

Course Code

Y

Varchar

16

Course Code (unique in Quercus)

In

Title

N

Varchar

80

Course description

In

Duration

N

Number

Duration of course in years

In

Approved Date

N

Date

Date course was approved

In

Closed Date

N

Date

Date course was closed

In

Status Code

N

Varchar

16

Status of course

In

Level Code

N

Varchar

16

Course Level Code

In

Department Code

N

Varchar

16

Department code

In

Mode of Study Code

N

Varchar

16

Mode of Study Code

In

Fee Category Code

N

Varchar

16

Fee Category Code

In

Fee Level Code

N

Varchar

16

Fee Level Code

In

Student Category Code

N

Varchar

16

Student Category Code

Course Director (QUERCUS.COURSE_DIRECTOR)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

First Name

N

Varchar

30

Person First Name

In

Surname

Y

Varchar

30

Person Surname

Repeated List of Course Tutors (QUERCUS.COURSE_TUTOR)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

First Name

N

Varchar

30

Person First Name

In

Surname

Y

Varchar

30

Person Surname

Repeated List of Field of Study (QUERCUS.FIELD_OF_STUDY)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Field of Study Code

N

Varchar

16

Field of Study

Repeated List of Clearing House Course (QUERCUS.CLEARING_HOUSE_COURSE)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Clearing House Course

N

Varchar

16

Course Code used by clearing house

In

Organisation Code

N

Varchar

16

Organisation Code of the Clearing House

In

Course Stream Code

N

Varchar

16

Course Stream Code

Repeated List of Course Awards (QUERCUS.COURSE_AWARD)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Award Code

N

Varchar

16

Award Code

In

Start Date

N

Date

Start Date

In

End Date

N

Date

End Date

In

Course Award Type Code

N

Varchar

16

Course Award Type

Notes

There can only be a maximum of one course director

Repeated lists for course tutors, field of study, clearing house course and course awards can be empty

Rules for course data inbound messages

As a general rule for COURSE_IN messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of the field Course Code which is unique within Quercus.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD

Validation

The messageType in the metadata section must be COURSE_IN. If this is not correctly specified then the message will not be processed.

The following mandatory fields must be present in the COURSE_IN message

Message field name

Comment

Message ID

Message Type

Course Code

GUID

Where Course Directors are in the message

GUID

Where Course Tutors are in the message

Field Of Study Code

Where Field of Studies are in the message

Clearing House Course

Where Clearing House Courses are in the message

Award Code

Where Course Awards are in the message

The following fields are lookup fields therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

Status Code

STATUS

Level Code

COURSE_LEVEL

Department Code

DEPARTMENT

Mode of Study Code

MODE_OF_STUDY

Fee Category Code

FEE_CATEGORY

Fee Level Code

FEE_LEVEL

Student Category Code

STUDENT_CATEGORY

Director GUID

PERSON

Tutor GUID

PERSON

Field of Study Code

FIELD_OF_STUDY

Organisation Code

ORGANISATION

Course Stream Code

COURSE_STREAM

Award Code

AWARD

Course Award Type Code

COURSE_AWARD_TYPE

The system will check for the existence of the course using the Course Code field.. If the course is found then it will be updated according to the rules specified below. If a course record is not found then a new record will be created.

Course

The following fields will be updated on the Course even if the value in the message is null:

Approved Date

Closed Date

Level

Department

Mode of study

The following fields will on be updated on the Course only if the value in the message is not null:

Title

Duration

Status

Course director

The GUID (Global Unique Identifier) is very important in this part of the message. The GUID is expected to be unique across the institution. Quercus checks for the existence of the person record by comparing the GUID to the value of the LDAP_ID field in the PERSON_LDAP table.

The course director list can be null and if so then no action in Quercus is taken.

If the course director record already exists (check course and person, course_director) then no action in Quercus will be taken.

If the course director record does not already exist (check course and person, course director) then a new record will be created.

Course tutor

The GUID (Global Unique Identifier) is very important in this part of the message. The GUID is expected to be unique across the institution. Quercus checks for the existence of the person record by comparing the GUID to the value of the LDAP_ID field in the PERSON_LDAP table.

The course tutor list can be null and if so then no action in Quercus is taken.

If the a course tutor list is present in the message and the UPDATE_MODE is ADD_UPDATE then:

Course tutor records will be added provided that they do not already exist (check course, person and then course tutor).

If the UPDATE_MODE is REPLACE then the list of course tutors provided in the message will replace all current course tutors will be replaced (note: there are no child records to COURSE_TUTOR).

Field of study

The course field of study list can be null, if this is the case then no action in Quercus will be taken.

If a list of Field of Study is present in the message and the UPDATE_MODE is ADD_UPDATE then:

New Field of Study records will be created provided they do not already exist.

If the UPDATE_MODE is REPLACE then the list of fields of study provided in the message will replace all current field fo study records.

Clearing house course

The clearing house course list can be null, if this is the case then no action in Quercus will be taken.

If a list of clearing house courses is presented in the message and the UPDATE_MODE is ADD_UPDATE then

New Clearing House Course records will be created provided they do not already exist.

If the clearing house course records already exists (check course and Clearing House Course) the following fields will be updated even if the value in the message is null:

Organisation Code

Course stream

If the UPDATE_MODE is REPLACE then the list of Clearing House Courses provided in the message will replace the current Clearing House Courses.

Course awards

The course award list can be null, if this is the case then no action in Quercus will be taken.

If a list of course awards is presented in the message and the UPDATE_MODE is ADD_UPDATE then:

New course award records will be created provided that they do not already exist (Check COURSE and AWARD)

If the course award record already exists the following fields will be updated even if the value in the message is null:

Start Date

End Date

Course Award Type Code

If the UPDATE_MODE is REPLACE then the list of Course Awards in the message will replace the current Course Awards records.

A2a – Course instance data (in)

Purpose

Update Quercus Course Instance data from external sources.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

COURSE_INSTANCE_IN

In

Course Code

Y

Varchar

16

Course Code (unique in Quercus)

In

Instance Code

Y

Varchar

16

Course Instance Code

In

Course Instance ID

Y

Varchar

30

Unique Course Instance Identifier

In

Academic Session

Y

Number

COURSE_INSTANCE_TABLE.ACADEMIC_YEAR

In

Course Year

N

Number

Course Year

In

Location Code

N

Varchar

16

Location code

In

Start Date

N

Date

Start Date

In

End Date

N

Date

End Date

In

Credits

N

Number

Number of credits

In

Mode of Study Code

N

Varchar

16

Mode of Study Code

In

Semesterised

N

Varchar

5

TRUE if semesterised

In

Course Stream Code

N

Varchar

16

Course Stream Code

In

Quota

N

Number

Quota allowed for course instance

In

Status Code

Y

Varchar

16

Status of course instance

In

Structure Levels

N

Number

Level of course curriculum

In

Results Entry Level Code

N

Varchar

16

Results Entry Level Code

In

Award Code

N

Varchar

16

Award Code for course instance

In

Sessional Type

N

Varchar

16

Single Session or Cross Sessional

In

Packaged

N

Number

1=YES, 0=NO

In

VAT Rate Code

N

Varchar

16

VAT Rate Code

In

Graduation Year

N

Number

1=YES, 0=NO

Defaults to 0

Repeated List of Course Instance Timetable (QUERCUS.COURSE_INSTANCE_TIMETABLE)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Day of the Week

N

Varchar

16

Day of the week

In

Start Time

N

Varchar

5

Start Time (stored as 4 digit number on database)

In

Duration

N

Number

Duration in hours

Course Instance Defaults (QUERCUS.COURSE_INSTANCE_DEFAULTS)

In

Attendance Entry Level Code

N

Varchar

16

Level at which attendance is taken

In

Number of Weeks

N

Number

Number of weeks the course instance is run

In

Number of Hours

N

Number

Number of hours the course instance has

In

Enrolment Target

N

Number

The targeted number of enrolments for the instance

In

Retention Target

N

Number

Retention target of students on course

In

Thesis Required

N

Number

1 = YES, 0=NO

In

Credit Tolerance Plus

N

Number

In

Credit Tolerance Minus

N

Number

In

Approved Date

N

Date

Course Instance Approved Date

In

Course End Date

N

Date

Course End Date

In

Publish On Web

N

Number

1 = YES, 0=NO

In

Franchised

N

Number

1 = YES, 0= NO – is the course instance franchised out to a partner organisation

In

Organisation Code

N

Varchar

16

Franchise Organisation Code

In

Fee Category Code

N

Varchar

16

Fee Category Code

In

Fee Level Code

N

Varchar

16

Fee Level Code

In

Student Category Code

N

Varchar

16

Student Category Code

Repeated List of Course Instance Persons (QUERCUS.COURSE_INSTANCE_PERSON)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

First Name

N

Varchar

30

Person First Name

In

Surname

Y

Varchar

30

Person Surname

In

Person Type Code

N

Varchar

16

Role of person (TUTOR, DEPARTMENT HEAD, etc.)

In

Start Date

N

Date

Start date

In

End Date

N

Date

End date

Notes

Repeated lists for course instance timetable and course instance person can be empty.

Rules for course instance data inbound messages

As a general rule for COURSE_INSTANCE_IN messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of the field Course Code which is unique within Quercus.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD.

Validation

The messageType in the metadata section must be COURSE_INSTANCE_IN. If this is not correctly specified then the message will not be processed and sent to the Hospital queue.

The following mandatory fields must be present in the COURSE_IN message:

Message Field Name

Comment

Message ID

Message Type

Course Code

Instance Code

Course Instance ID

Unique identifier for course instance

Academic Session

Sessional Type

Status Code

GUID

If Course Instance Persons are included in the message

The following fields are lookup fields therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

Location Code

LOCATION

Mode of Study Code

MODE_OF_STUDY

Course Stream Code

COURSE_STREAM

Status Code

STATUS

Results Entry Level Code

RESULTS_ENTRY_LEVEL

Award Code

AWARD

Sessional Type

SESSIONAL_TYPE

VAT Rate Code

VAT_RATE

Attendance Entry Level Code

ATTENDANCE_ENTRY_LEVEL

Organisation Code

Organisation

Fee Category Code

FEE_CATEGORY

Fee Level Code

FEE_LEVEL

Student Category Code

STUDENT_CATEGORY

GUID

PERSON LDAP

Person Type Code

PERSON TYPE

The system will check for the existence of the course instance and if a course instance is found then it will be updated. The basis for the check is COURSE_INSTANCE_ID which is the unique code against all course instances; if COURSE_INSTANCE_TABLE.COURSE_INSTANCE_ID = COURSE_INSTANCE_ID then the course instance is found otherwise it has not been found.

If a course instance record is not found then a new record will be created.

Course instance

The following fields will be updated on the Course Instance even if the value in the message is null:

Location

Start Date

End Date

Mode of Study

Semesterised

Course Stream

Quota

Structure Levels

Results Entry Level

Award

Status

The value for SESSIONAL TYPE must either be SINGLE-SESSION or CROSS-SESSIONAL. This is a mandatory field. If the Quercus CROSS_SESSION_CI_FLAG parameter is set to 0 (disabled) then the value for this field should be set to SINGLE-SESSION.

The value for PACKAGED must either be 1 or 0. This is a mandatory field. If the CROSS_SESSION_CI_FLAG parameter is set to 0 (disabled) then the value for this field should be set to 0.

Course instance timetable

Valid values for Day of the Week are:

MONDAY

TUESDAY

WEDNESDAY

THURSDAY

FRIDAY

SATURDAY

SUNDAY

Only the listed values will be used, anything else is ignored.

The Start Time value is a four digit number with a colon between digit 3 and digit 4 reflecting the 24-hour clock. Examples:

07:00 – 7 o’clock in the morning

19:00 – 7 o’clock in the evening

If the there are any values outside this format and not within the 24-hour clock range (hours from 00 to 23, minutes from 00 to 59) then they will be ignored.

The course instance timetable list can be null and if so then no action in Quercus is taken.

If a list of Course Instance Timetable is present in the message and the UPDATE_MODE is ADD_UPDATE then:

New records will be created provided they don’t already exist, (check course instance, day of week and start time).

If the record already exists the Duration field will be updated.

If the UPDATE_MODE is REPLACE then the list for Course Instance Timetable in the message will replace all the Course Instance Timetable records in the database for that course instance.

Course instance defaults

The course instance defaults list can be null and if so then no action in Quercus is taken.

This list is not a repeated list so there should only ever be one set of values for course instance defaults.

If a course instance defaults record already exists (check course instance) then that record will be updated with the values from the message even if they are null.

If a course instance defaults record does not already exist (check course instance) then a new record will be created using the values from the message.

The values for ATTENDANCE ENTRY LEVEL CODE and ORGANISATION CODE must exist in Quercus.

The value for PUBLISH ON WEB must either be 1 or 0. The value for FRANCHISED must either be 1 or 0.

Course instance person

The GUID (Global Unique Identifier) is very important in this part of the message. The GUID is expected to be unique across the institution. Quercus checks for the existence of the person record by comparing the GUID to the value of the LDAP_ID field in the PERSON_LDAP table.

The list of course instance persons can be null and if so then no action in Quercus is taken.

If a list of course instance persons is present in the message and the UPDATE_MODE is ADD_UPDATE then new course instance person record will be created provided they don’t already exist and the person can be found in the Quercus database. If the module instance person record is found then the following fields will be updated:

Start Date

End Date

If the UPDATE_MODE is REPLACE then the list of course instance persons provided in the message will replace all course instance person records against the course instance provided that the person record is found.

A2b – Course curriculum data (in)

Purpose

Update Quercus Course Curriculum data from external sources.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

COURSE_CURRICULUM_IN

In

Course Instance ID

Y

Varchar

30

Unique Identifier for Course Instance

Repeated List of Course Curriculum (see structure below in note 1)

Repeated List of Level 1 Modules

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

L1 Module Instance ID

Y

Varchar

30

MODULE_INSTANCE_TABLE.MODULE_INSTANCE

In

L1 Module Type Code

Y

Varchar

16

CPD.MODULE_TYPE

In

L1 Semester

N

Number

CPD.COURSE_SEMESTER

In

L1 Credits

N

Number

CPD.CREDITS

In

L1 Overall Grade Weighting

N

Number

CPD.OVERALL_GRADE_WEIGHT

In

L1 Award Year Subject

N

Varchar

5

CPD.AWARD_YEAR_SUBJECT

Values are TRUE or FALSE

In

L1 Qualification Code

N

Varchar

8

CPD.QUALIFICATION

In

Repeated List of Requisites for Level 1 Modules (REQUISITE.COURSE_PROGRAM_DETAIL)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Nested Repeated List of Requisite Types (REQUISITE.REQUISITE_TYPE)

In

Requisite Type Code

N

Varchar

16

REQUISITE.REQUISITE_TYPE

In

Nested Repeated List of Choices (REQUISITE_DETAIL.REQUISITE)

In

Requisite Choice

N

Number

REQUISITE_DETAIL.REQUISITE_CHOICE

In

Number Required

N

Number

REQUISITE_DETAIL.NUMBER_REQUIRED

In

Nested Repeated List of Modules (REQUISITE_MODULE.REQUISITE_DETAIL)

In

Module Code

N

Varchar

16

REQUISITE_MODULE.MODULE

In

Repeated List of CPD Targets (CPD_TARGET)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Target Type Code

Y

Varchar

16

TARGET_TYPE.TARGET_TYPE

In

Year1

N

Number

CPD_TARGET.TARGETS.YEAR1

In

Target_Year1

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR1

In

Year2

N

Number

CPD_TARGET.TARGETS.YEAR2

In

Target_Year2

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR2

In

Year3

N

Number

CPD_TARGET.TARGETS.YEAR3

In

Target_Year3

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR3

In

Year4

N

Number

CPD_TARGET.TARGETS.YEAR3

In

Target_Year4

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR3

In

Year5

N

Number

CPD_TARGET.TARGETS.YEAR3

In

Target_Year5

N

Number

CPD_TARGET.TARGETS.TARGET_YEAR3

In

Repeated List of CPD Tutors (CPD_TUTOR)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

N

Number

Quercus Person ID Number

Repeated List of Module Groups for Level 1 Modules (MODULE_GROUP_DETAIL)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Module Group Code

Y

Varchar

16

MODULE_GROUP_DETAIL.MODULE_GROUP

In

Group Type Code

N

Varchar

16

MODULE_GROUP_DETAIL.GROUP_TYPE

In

Minimum Allowed

N

Number

MODULE_GROUP_DETAIL.MINIMUM

In

Maximum Allowed

N

Number

MODULE_GROUP_DETAIL.MAXIMUM

In

Minimum Credits Allowed

N

Number

MODULE_GROUP_DETAIL.MINIMUM_CREDITS

In

Maximum Credits Allowed

N

Number

MODULE_GROUP_DETAIL.MAXIMUM_CREDITS

In

Relation

N

Number

MODULE_GROUP_DETAIL.RELATION

(must be AND or OR)

In

Nested Repeated List of Group Modules (GROUP_DETAIL_MODULES)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Module Instance ID

Y

Varchar

30

GROUP_DETAIL_MODULES.COURSE_PROGRAM_DETAIL

In

Module Type Code

N

Varchar

16

GROUP_DETAIL_MDOULES.MODULE_TYPE

Nested Repeated List of Level 2 Modules

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

L2 Module Instance ID

Y

Varchar

30

MODULE_INSTANCE_TABLE.MODULE_INSTANCE

In

L2 Module Type Code

Y

Varchar

16

CPU.MODULE_TYPE

In

L2 Semester

N

Number

CPU.COURSE_SEMESTER

In

L2 Credits

N

Number

CPU.CREDITS

In

L2 Overall Grade Weighting

N

Number

CPU.OVERALL_GRADE_WEIGHT

In

L2 Award Year Subject

N

Varchar

5

CPU.AWARD_YEAR_SUBJECT

Values are TRUE or FALSE

In

L2 Qualification Code

N

Varchar

8

CPU.QUALIFICATION

In

Repeated List of Requisites for Level 2 Modules (REQUISITE.COURSE_PROGRAM_DETAIL)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Nested Repeated List of Requisite Types (REQUISITE.REQUISITE_TYPE)

In

Requisite Type Code

N

Varchar

16

REQUISITE.REQUISITE_TYPE

In

Nested Repeated List of Choices (REQUISITE_DETAIL.REQUISITE)

In

Requisite Choice

N

Number

REQUISITE_DETAIL.REQUISITE_CHOICE

In

Number Required

N

Number

REQUISITE_DETAIL.NUMBER_REQUIRED

In

Nested Repeated List of Modules (REQUISITE_MODULE.REQUISITE_DETAIL)

In

Module Code

N

Varchar

16

REQUISITE_MODULE.MODULE

Repeated List of Module Groups for Level 2 Modules (MODULE_GROUP_UNIT)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Module Group Code

Y

Varchar

16

MODULE_GROUP_UNIT.MODULE_GROUP

In

Minimum Allowed

N

Number

MODULE_GROUP_UNIT.MINIMUM

In

Maximum Allowed

N

Number

MODULE_GROUP_UNIT.MAXIMUM

In

Minimum Credits Allowed

N

Number

MODULE_GROUP_UNIT.MINIMUM_CREDITS

In

Maximum Credits Allowed

N

Number

MODULE_GROUP_UNIT.MAXIMUM_CREDITS

In

Relation

N

Number

MODULE_GROUP_UNIT.RELATION

(must be AND or OR)

In

Nested Repeated List of Group Modules (GROUP_DETAIL_UNITS)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Module Instance ID

Y

Varchar

30

GROUP_DETAIL_UNIT.COURSE_PROGRAM_UNIT

Nested Repeated List of Level 3 Modules

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

L3 Module Instance ID

Y

Varchar

30

MODULE_INSTANCE_TABLE.MODULE_INSTANCE

In

L3 Module Type Code

Y

Varchar

16

CUD.MODULE_TYPE

In

L3 Semester

N

Number

CUD.COURSE_SEMESTER

In

L3 Credits

N

Number

CUD.CREDITS

In

L3 Overall Grade Weighting

N

Number

CUD.OVERALL_GRADE_WEIGHT

In

L3 Award Year Subject

N

Varchar

5

CUD.AWARD_YEAR_SUBJECT

Values are TRUE or FALSE

In

L3 Qualification Code

N

Varchar

8

CUD.QUALIFICATION

Notes

Course curriculum is nested. A level 1 module can have multiple level 2 modules and each of those level 2 modules can have multiple level 3 modules.

Rules for course curriculum data inbound messages

As a general rule for COURSE_CURRICULUM_IN messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of the field Course Instance Code.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD.

Validation

The messageType in the metadata section must be: COURSE_CURRICULUM_IN. If this is not correctly specified then the message will not be processed and sent to the hospital queue.

The following mandatory fields must be present in the COURSE_CURRICULUM_IN message:

Message field name

Comment

Message ID

Message Type

Course Instance ID

Unique identifier for course instance

Module Instance ID

At level 1, 2 & 3 and Module Groups Level 1 & 2

Module Type Code

At level 1, 2 & 3

Module Group Type

At Level 1 & 2 provided that Groups are presented

GUID

For CPD Tutors provided that they are presented

The following fields are lookup fields therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

Course Instance ID

COURSE_INSTANCE_TABLE

Module Instance ID

MODULE_INSTANCE

Module Type Code

MODULE_TYPE

Qualification Code

QUALIFICATION

Requisite Type Code

REQUISITE_TYPE

Requisite Module.Module Code

MODULE

Target Type Code

TARGET_TYPE

Module Group Code

MODULE_GROUP

Group Type Code

GROUP TYPE

GUID

PERSON_LDAP

Should the course instance not be found then the message will not be processed and sent to the hospital queue.

If the course instance is found then the course curriculum is either created or updated according to the contents of the message. The curriculum can be created for level 1, level 2 or level 3.

Should any of the module instance IDs (L1, L2 or L3) not be found then the message will not be processed and sent to the hospital queue.

Rules for level 1 modules

If the list of level 1 modules is presented in the message and the UPDATE_MODE=ADD_UPDATE then:

The system will first check for the existence of the level 1 module using a combination of COURSE_INSTANCE_ID and MODULE_CODE (check course instance and L1module code).

If not found then a new level 1 module will be created and any requisites will be added for that module provided:

The requisite types is valid (lookup requisite type)

The module codes is valid (lookup module code)

If the module being added has a module type of ‘CORE’ then it will be propagated to all registered students curriculum for that academic session.

If found then the following fields will be updated even if the field in the message contains a null value:

L1 Semester

L1 Credits

L1 Overall Grade Weighting

L1 Award Year Subject

L1 Qualification Code

If the list of level 1 modules is presented in the message and the UPDATE_MODE=REPLACE then the level 1 modules in the message will replace the current level 1 modules provided that:

Any module being replaced is not associated with a student curriculum record.

Rules for requisites at level 1

If a list of requisites is presented in the message and the UPDATE_TYPE=ADD_UPDATE then:

The system will check for the existence of requisites (COURSE_PROGRAM_DETAIL and REQUISITE_TYPE)

If not found then the requisites will be added for that module provided:

The requisite type is valid (lookup requisite type)

The module codes is valid (lookup module code)

If a list of requisites is presented in the message and the UPDATE_TYPE=REPLACE then the list of requisites in the message will replace the current requisites against the module.

Note: there are no constraints in the database which prevent users doing this, so the message system will replicate this functionality.

Rules for CPD targets

If a list of CPD targets is present in the message and the UPDATE_TYPE=ADD_UPDATE then:

The system will first check for the existence of a CPD Target record using the target type code and course program detail record

If not found then a new CPD target will be created using the values presented

If found then the existing record will be updated even if the field in the message contains a null value

If the list of CPD targets is presented in the message and the UPDATE_MODE=REPLACE then the CPD Targets in the message will replace the current CPD targets.

The standard database api QP_STRUCTURE will be used to manipulate the CPD Targets.

Rules for CPD tutors

If a list of CPD Tutors is present in the message and the UPDATE_TYPE=ADD_UPDATE then:

The system will first check for the existence of a CPD_TUTOR record using the course program detail record and person record (found using GUID)

If not found then a new CPD Tutor will be created

If found then the record will be left intact

If the list of CPD Tutors is presented in the message and the UPDATE_MODE=REPLACE then the CPD Tutors in the message will replace the current CPD Tutor records.

Rules for module groups at level 1

Modules presented in the Module Groups at Level 1 must be Level 1 Modules on the course structure.

If a list of module groups is presented in the message and the UPDATE_TYPE=ADD_UPDATE then:

The system will check for the existence of the module groups ( COURSE_INSTANCE and MODULE_GROUP)

If not found then the module groups and the modules in that group will be added provided that:

The module group code is valid (lookup module group)

The group type code is valid (lookup group type)

The modules being added to the group is a module on the COURSE_PROGRAM_DETAIL – if this is not the case the message will be sent to the HOSPITAL QUEUE

If found then the following fields on the MODULE_GROUP_DETAIL will be updated even if the value in the field is null:

Minimum Allowed

Maximum Allowed

Minimum Credits

Maximum Credits

Relation

If found then the modules in the group will be updated as follows:

If the module already exists in the group then the module type can be updated

If the module does not already exist in then it will be added

If of list of module groups is presented in the message and the UPDATE_TYPE=REPLACE then:

All existing module groups and their respective group modules will be replaced provided that:

The modules that are replacing the existing modules in the group are valid modules on the course structure

The standard database api QP_STRUCTURE will be used to manipulate the MODULE_GROUP_DETAIL records.

Rules for level 2 modules

The list for level 2 modules can be null and if so there is no further processing done.

If a list of level 2 modules is presented in the message and the UPDATE_MODE=ADD_UPDATE then

The system will first check for the existence of the level 2 module (check course program detail and L2 module code)

If not found then a new level 2 module will be created and any requisites will be added for that module provided:

The requisite types is valid (lookup requisite type)

The module codes is valid (lookup module code)

If the module being added has a module type of ‘CORE’ then it will be propagated to all registered students curriculum for that academic session.

If found then the following fields will be updated even if the field in the message contains a null value:

L2 Semester

L2 Credits

L2 Overall Grade Weighting

L2 Award Year Subject

L2 Qualification Code

If the list of level 2 modules is presented in the message and the UPDATE_MODE=REPLACE then the level 2 modules in the message will replace the current level 2 modules provided that:

Any module being replaced is not associated with a student curriculum unit record

Rules for requisites at level 2

If a list of requisites is presented in the message and the UPDATE_TYPE=ADD_UPDATE then:

The system will check for the existence of requisites (COURSE_PROGRAM_DETAIL and REQUISITE_TYPE)

If not found then the requisites will be added for that module provided:

The requisite type is valid (lookup requisite type)

The module codes is valid (lookup module code)

If a list of requisites is presented in the message and the UPDATE_TYPE=REPLACE then the list of requisites in the message will replace the current requisites against the module.

Note: there are no constraints in the database which prevent users doing this, so the message system will replicate this functionality.

Rules for module groups at level 2

Modules presented in the Module Groups at Level 2 must be Level 2 Modules on the course structure.

If a list of module groups is presented in the message and the UPDATE_TYPE=ADD_UPDATE then:

The system will check for the existence of the module groups ( COURSE_PROGRAM_DETAIL and MODULE_GROUP)

If not found then the module groups and the modules in that group will be added provided that:

The module group code is valid (lookup module group)

The module being added to the group is a module on the COURSE_PROGRAM_UNIT – if this is not the case the message will be sent to the HOSPITAL QUEUE

If found then the following fields on the MODULE_GROUP_UNIT will be updated even if the value in the field is null

Minimum Allowed

Maximum Allowed

Minimum Credits

Maximum Credits

Relation

If found then the modules in the group will be updated as follows:

If the module already exists in the group then it will remain as it is

If the module does not already exist in the group then it will be added

If of list of module groups is presented in the message and the UPDATE_TYPE=REPLACE then:

All existing module groups and their respective group modules will be replaced provided that:

The modules that are replacing the existing modules in the group are valid modules on the course structure at level 2

The standard database api QP_STRUCTURE will be used to manipulate the MODULE_GROUP_DETAIL records.

Rules for level 3 modules

The list for level 3 modules can be null and if so there is no further processing done.

If a list of level 3 modules is presented in the message and the UPDATE_MODE=ADD_UPDATE then:

The system will first check for the existence of the level 3 module (check course program unit and L3 module code)

If not found then a new level 3 module will be created

If the module being added has a module type of ‘CORE’ then it will be propagated to all registered students curriculum for that academic session

If found then the following fields will be updated even if the field in the message contains a null value:

L3 Semester

L3 Credits

L3 Overall Grade Weighting

L3 Award Year Subject

L3 Qualification Code

If the list of level 3 modules is presented in the message and the UPDATE_MODE=REPLACE then the level 3 modules in the message will replace the current level 3 modules provided that:

Any module being replaced is not associated with a student unit detail record

B1 – Biographic data (out)

Purpose

To provide biographic data when new records are created or existing records are updated.

Triggering events

Event

Table

Operation(s)

1

Change of person bio data

PERSON

I/U

2

Change of person address

ADDRESS

I/U

3

Change of reference number

PERSON_REFERENCE_TYPE

I/U/D

4

Change of person type

PERSON_TYPE

I/U/D

5

Change of person indicators

PERSON_INDICATORS

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

PERSON_BIO_OUT

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

First Name

N

Varchar

30

Person First Name

Out

Surname

Y

Varchar

30

Person Surname

Out

Middle Name

N

Varchar

30

Person Middle Name

Out

Preferred Name

N

Varchar

30

Person Preferred Name

Out

Title

N

Varchar

16

Person Honorific

Out

Gender

N

Varchar

6

Person Gender (M or F)

Out

Social Insurance Number

N

Varchar

16

Person Social Insurance Number

Out

Date of Birth

N

Date

Person Date of Birth

Out

Date Deceased

N

Date

Person Date Deceased

Out

Home Address

N

Address Type

Person Home Address – Type: Address

Out

Term Address

N

Address Type

Person Term Address – Type: Address

Out

Work Address

N

Address Type

Person Work Address – Type: Address

Out

Mailing Label

N

Address type

Person Mailing Label – Type: Address

Repeated List of Person Roles (e.g. Student, Tutor, Director etc.)

Out

Person Type

N

Varchar

16

Person Type Code

Out

Description

N

Varchar

80

Person Type Description

Repeated List of Person Reference Numbers (e.g. ULN, UCAS)

Out

Reference Number Type

N

Varchar

16

Reference Number Type Code

Out

Description

N

Varchar

80

Reference Number Type Description

Out

Reference Number

N

Varchar

Reference Number value

Out

Start Date

N

Date

Reference Number Start date

Out

End Date

N

Date

Reference Number End date

Person Indicators (e.g. Domicile, Nationality, Ethnic Grouping etc)

Country

Out

Country Code

N

Varchar

16

Country Code

Out

Country Description

N

Varchar

80

Country Description

Nationality

Out

Nationality Code

N

Varchar

16

Nationality Code

Out

Nationality Description

N

Varchar

80

Nationality Description

Ethnic Grouping

Out

Ethnic Grouping Code

N

Varchar

16

Ethnic Grouping Code

Out

Ethnic Group Description

N

Varchar

80

Ethnic Grouping Description

Socio Economic Group

Out

Socio Economic Group Code

N

Varchar

16

Socio Economic Group Code

Out

Socio Economic Group Description

N

Varchar

80

Socio Economic Group Description

Notes

All Reference numbers will be listed regardless of start and end dates.

B2 – Biographic data (in)

Purpose

Updates Quercus biographic data from external systems.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

PERSON_BIO_IN

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

First Name

N

Varchar

30

Person First Name

In

Surname

Y

Varchar

30

Person Surname

In

Middle Name

N

Varchar

30

Person Middle Name

In

Preferred Name

N

Varchar

30

Person Preferred Name

In

Title

N

Varchar

16

Person Honorific

In

Gender

N

Varchar

6

Person Gender (M or F)

In

Social Insurance Number

N

Varchar

16

Person Social Insurance Number

In

Date of Birth

N

Date

Person Date of Birth

In

Date Deceased

N

Date

Person Date Deceased

In

Home Address

N

Address Type

Person Home Address – Type: Address

In

Term Address

N

Address Type

Person Term Address – Type: Address

In

Work Address

N

Address Type

Person Work Address – Type: Address

In

Mailing Label

N

Address type

Person Mailing Label – Type: Address

Repeated List of Person Roles (e.g. Student, Tutor, Director etc.)

In

Person Type

N

Varchar

16

Person Type Code

In

Description

N

Varchar

80

Person Type Description

Repeated List of Person Reference Numbers (e.g. ULN, UCAS)

In

Reference Number Type

N

Varchar

16

Reference Number Type Code

In

Description

N

Varchar

80

Reference Number Type Description

In

Reference Number

N

Varchar

Reference Number value

In

Start Date

N

Date

Reference Number Start date

In

End Date

N

Date

Reference Number End date

Person Indicators (e.g. Domicile, Nationality, Ethnic Grouping etc)

Country

In

Country Code

N

Varchar

16

Country Code

In

Country Description

N

Varchar

80

Country Description

Nationality

In

Nationality Code

N

Varchar

16

Nationality Code

In

Nationality Description

N

Varchar

80

Nationality Description

Ethnic Grouping

In

Ethnic Grouping Code

N

Varchar

16

Ethnic Grouping Code

In

Ethnic Group Description

N

Varchar

80

Ethnic Grouping Description

Socio Economic Group

In

Socio Economic Group Code

N

Varchar

16

Socio Economic Group Code

In

Socio Economic Group Description

N

Varchar

80

Socio Economic Group Description

Rules for biographic data inbound messages

As a general rule for Bio_Data_In messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of the fields IDnumber and GUID.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

The messageType in the metadata section must be PERSON_BIO_IN. If this is not correctly specified then the message will not be processed.

The system will check for the existence of the person using the GUID (Global Unique Identifier) checking the table PERSON_LDAP.LDAP_ID. The GUID is expected to be unique across the institution.

If the person is found then that record will be updated according to the rules in the sections below.

If the person is not found then a new record is created. The new person will be assigned a new ID_NUMBER in Quercus and the PERSON_LDAP.LDAP_ID record for the person will be set to the GUID from the Bio_Data_In message.

Address Field Rules

The address fields are:

Addressline1

Addressline2

Addressline3

Addressline4

Addressline5

Postcode

Email

Phone

MobilePhone

Fax

Contact

For the purposes of update the address is split between the first 6 lines of address (Addressline1 to Postcode) and the rest of the address. Updates to address are processed as follows:

If the first 6 lines of address in the message are empty (null) then the address lines 1 to postcode are not updated in Quercus.

If the first 6 lines of address in the message are not empty then the address lines 1 to postcode are updated in Quercus.

The remaining address fields in the message are used to update the database address fields whether or not they are null.

Person Role Rules

If a person role is included in the Bio_Data_In message then the system checks if the role already exists for the person and if it doesn’t then the role is added to the persons record.

Person Roles are never removed from Quercus.

Person Reference Number Rules

If a reference number is included in the Bio_Data_In message then the following rules apply:

The system checks for an existing record using the reference number type.

If there is an existing reference number record for the person then it is updated (start date, end dates and reference number)

If a record does not exist then a new record is created.

Similar to Person Roles, Person Reference Numbers are never removed from Quercus.

Person Indicator Rules

If person indicators are included in the Bio_Data_In message the following rules apply.

The system checks for an existing Person Indicators record.

If a person indicators record exists for the person then it will be updated with the values given in the message, including null values.

If a person indicators record does not exist then a new record is created.

C1 – Module data (out)

Purpose

To provide module data to external systems when a new module is created or an existing module is updated.

Triggering events

Event

Table

Operation(s)

1

Change to module data

Module

I/U

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

MODULE_OUT

Out

Module Code

Y

Varchar

16

Module Code – unique within Quercus

Out

Title

N

Varchar

80

Module Description

Out

Short Name

N

Varchar

8

Module Short Name

Out

Date Approved

N

Date

Date Module Approved

Out

Date Closed

N

Date

Date Module Closed

Out

Module Level Code

N

Varchar

16

Module Level Code

Out

Module Level Description

N

Varchar

80

Module Level Description

Notes

Module data only, no module instance data in this message.

C1a – Module instance data (out)

Purpose

To provide module data to external systems when a new module instance is created or an existing module is updated.

Triggering events

Event

Table

Operation(s)

1

Change to module instance data

MODULE_INSTANCE_TABLE

I/U

2

Change to course program assessment data

COURSE_PROGRAM_ASSESSMENT

I/U/D

3

Change to module instance department data

MODULE_INSTANCE_DEPARTMENT

I/U/D

4

Change to module instance person data

MODULE_INSTANCE_PERSON

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

MODULE_INSTANCE_OUT

Out

Module Code

Y

Varchar

16

Module Code – unique within Quercus

Out

Module Instance ID

Y

Varchar

30

Unique Identifier for module instance

Out

Academic Session

Y

Number

Academic Year the module is associated with

Out

Credits

N

Number

Number of credits associated with the module instance

Out

Qualification

N

Varchar

16

Qualification Code

Out

Status Code

Y

Varchar

16

Status of the module instance

Out

Status Description

N

Varchar

80

Description

Out

Start Date

N

Date

Start Date

Out

End Date

N

Date

End Date

Out

JACS Subject

N

Varchar

16

JACS Subject Code

Out

JACS Subject Description

N

Varchar

80

JACS Subject Description

Out

ASC Code

N

Varchar

16

ASC Code

Out

ASC Description

N

Varchar

80

ASC Description

Out

Exam Entry Type

N

Number

1=REGISTER, 0=ENTRY (Default =1)

Out

Parent Course Code

N

Varchar

16

Course Code for parent course

Out

Sessional Type

Y

Varchar

16

Allowed values

SINGLE-SESSION

CROSS-SESSIONAL

Out

Packaged

Y

Number

1=YES, 0=NO

Out

VAT Rate Code

N

Varchar

16

VAT Rate Code

Out

VAT Rate Description

N

Varchar

80

VAT Rate Description

Repeated List of Module Instance Assessments (QUERCUS.COURSE_PROGRAM_ASSESSMENT)

Out

Assesment Code

N

Varchar

16

Assessment Code

Out

Assessment Description

N

Varchar

80

Description

Out

Weighting Factor

N

Number

Weighting factor of assessment

Out

Pass Mark

N

Number

Pass Mark for assessment

Out

Marks Out Of

N

Number

Total Number of marks available for assessment

Out

Repeated List of Assessment Schedule (QUERCUS.ASSESSMENT_SCHEDULE)

Out

Assessment Sitting Code

N

Varchar

16

Assessment Sitting Code

Out

Assessment Sitting Description

N

Varchar

80

Assessment Sitting Description

Out

Assessment Period Code

N

Varchar

16

Assessment Period Code

Out

Assessment Period Description

N

Varchar

80

Assessment Period Description

Out

Start Date

N

Date

Start Date

Out

End Date

N

Date

End Date

Out

Duration

N

Number

Duration

Out

Status Code

N

Varchar

16

Status Code

Out

Status Description

N

Varchar

80

Status Description

Out

Note

N

Varchar

255

Note against the assessment schedule

Repeated List of Assessment Schedule Bonds

Out

Module Code

Y

Varchar

16

Out

Academic Session

Y

Number

Out

Module Instance ID

Y

Varchar

30

Unique Identifier for Module Instance

Out

Sitting Code

Y

Varchar

16

Out

Bonded Assessment Code

Y

Varchar

16

Assessment Code

Out

Assessment Period Code

N

Varchar

16

Out

Bond Type Code

N

Varchar

16

Repeated List of Module Instance Departments (QUERCUS.MODULE_INSTANCE_DEPARTMENT)

Out

Department Code

N

Varchar

16

Department Code

Out

Department Description

N

Varchar

80

Description

Out

Lecture Hours

N

Number

Number of lecture hours

Out

Department Input

N

Number

Number of lecture hours input by department

Repeated List of Module Instance Persons (QUERCUS.MODULE_INSTANCE_PERSON)

Out

GUID

Y

Varchar

30

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

First Name

N

Varchar

30

Person First Name

Out

Surname

Y

Varchar

30

Person Surname

Out

Person Type Code

N

Varchar

16

Role of person (TUTOR, DEPARTMENT HEAD, etc.)

Out

Start Date

N

Date

Start date

Out

End Date

N

Date

End date

Notes

Module Instance Assessments, Module Instance Departments and Module Instance Persons can be null. Inserts, updates and deletes to these tables trigger this message for the single module instance only.

For Module Instance Person records the GUID, ID Number and Surname are mandatory and must exist on the record.

C2 – Module data (in)

Purpose

Update Quercus Module data with data from external sources.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

MODULE_IN

In

Module Code

Y

Varchar

16

Module Code – unique within Quercus

In

Title

N

Varchar

80

Module Description

In

Short Name

N

Varchar

8

Module Short Name

In

Date Approved

N

Date

Date Module Approved

In

Date Closed

N

Date

Date Module Closed

In

Module Level Code

N

Varchar

16

Module Level Code

Notes

Module data only, no module instance data in this message

Rules for module data inbound messages

As a general rule for MODULE_IN messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of the field Module Code which is unique within Quercus.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

Validation

The messageType in the metadata section must be MODULE_IN. If this is not correctly specified then the message will not be processed and sent to the Hospital queue.

The following mandatory fields must be present in the MODULE_IN message:

Message field name

Comment

Message ID

Message Type

Module Code

The following fields are lookup fields therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

Module Level Code

MODULE_LEVEL

The system will check for the existence of the module using the Module Code field.

If the module is found then that record will be updated using the fields:

Title

Short Name

Date Approved

Date Closed

Module Level Code

If all non-mandatory fields are null and the module is found then all relevant fields on the record will be updated to null.

If a module record is not found then a new record will be created.

C2a – Module instance data (in)

Purpose

Update Quercus Module data with data from external sources

Triggering Events

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

MODULE_INSTANCE_IN

In

Module Code

Y

Varchar

16

Module Code – unique within Quercus

In

Module Instance ID

Y

Varchar

30

Unique Identifier for Module Instance

In

Academic Session

Y

Number

Academic Year the module is associated with

In

Credits

N

Number

Number of credits associated with the module instance

In

Qualification

N

Varchar

16

Qualification Code

In

Status Code

Y

Varchar

16

Status of the module instance

In

Start Date

N

Date

Start Date

In

End Date

N

Date

End Date

In

JACS Subject

N

Varchar

16

JACS Subject Code

In

ASC Code

N

Varchar

16

ASC Code

In

Exam Entry Type

N

Number

1=REGISTER, 0=ENTRY (Default=1)

In

Parent Course Code

N

Varchar

16

Course Code for parent course

In

Sessional Type

Y

Varchar

16

Allowed values

SINGLE-SESSION

CROSS-SESSIONAL

In

Packaged

Y

Number

1=YES, 0=NO

In

VAT Rate

N

Varchar

16

VAT Rate Code

Repeated List of Module Instance Assessments (QUERCUS.COURSE_PROGRAM_ASSESSMENT)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Assessment Code

Y

Varchar

16

Assessment Code

In

Weighting Factor

N

Number

Weighting factor of assessment

In

Pass Mark

N

Number

Pass Mark for assessment

In

Marks Out Of

N

Number

Total Number of marks available for assessment

In

Repeated List of Assessment Schedule (QUERCUS.ASSESSMENT_SCHEDULE)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Assessment Sitting Code

N

Varchar

16

Assessment Sitting Code

In

Assessment Period Code

N

Varchar

16

Assessment Period

In

Start Date

N

Date

Start Date

In

End Date

N

Date

End Date

In

Duration

N

Number

Duration

In

Status Code

N

Varchar

16

Status Code

In

Note

N

Varchar

255

Note against the assessment schedule

In

Nested Repeated List of Assessment Schedule Bonds (QUERCUS.ASSESSMENT_SCHEDULE_BOND)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Module Code

Y

Varchar

16

In

Academic Session

Y

Number

In

Module Instance ID

Y

Varchar

30

Unique Identifier for Module Instance

In

Sitting Code

Y

Varchar

16

In

Bonded Assessment Code

Y

Varchar

16

Assessment Code

In

Assessment Period

N

Varchar

16

In

Bond Type Code

N

Varchar

16

Repeated List of Module Instance Departments (QUERCUS.MODULE_INSTANCE_DEPARTMENT)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

Department Code

N

Varchar

16

Department Code

In

Lecture Hours

N

Number

Number of lecture hours

In

Department Input

N

Number

Number of lecture hours input by department

Repeated List of Module Instance Persons (QUERCUS.MODULE_INSTANCE_PERSON)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

GUID

Y

Varchar

30

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

First Name

N

Varchar

30

Person First Name

In

Surname

Y

Varchar

30

Person Surname

In

Person Type Code

Y

Varchar

16

Role of person (TUTOR, DEPARTMENT HEAD, etc.)

In

Start Date

N

Date

Start date

In

End Date

N

Date

End date

Notes

Module Instance Assessments, Module Instance Departments and Module Instance Persons can be null. Inserts, updates and deletes to these tables trigger this message for the single module instance only.

For Module Instance Person records the GUID, ID Number and Surname are mandatory and must exist on the record.

Rules for module instance data inbound messages

As a general rule for MODULE_INSTANCE_IN messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of the fields Module Code and Module Instance which are unique within Quercus.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD.

Validation

The messageType in the metadata section must be MODULE_INSTANCE_IN. If this is not correctly specified then the message will not be processed and sent to the Hospital queue.

The following mandatory fields must be present in the MODULE_INSTANCE_IN message.

Message field name

Comment

Message ID

Message Type

Module Code

Module Instance ID

Unique identifier for Module Instance

Academic Session

Status Code

Sessional Type

Packaged

Assessment Code

If Module Instance Assessments are included in the message

Bonded Assessment Code

Assessment code

GUID

If Module Instance Persons are included in the message

Department Code

If Module Instance Departments are included in the message

The following fields are lookup fields therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

Module Code

MODULE

Qualification

QUALIFICATION

Status Code

STATUS

Sessional Type

SESSIONAL_TYPE

JACS Subject

JACS_SUBJECT

ASC Code

ASC_CODE

VAT Rate

VAT_RATE

Parent Course Code

COURSE

Assessment Code

ASSESSMENT

Assessment Schedule Sitting Code

ASSESSMENT_SITTING

Assessment Schedule Period Code

ASSESSMENT_PERIOD

Assessment Schedule Status Code

STATUS

Bonded Assessment Code

ASSESSMENT

Bond Type

BOND_TYPE

Department Code

DEPARTMENT

GUID

PERSON_LDAP.LDAP_ID

Person Type Code

PERSON_TYPE

The system will check for the existence of the module instance and if a module instance is found then it will be updated. The basis for the check is MODULE_INSTANCE_ID which is the unique code against all module instances; if MODULE_INSTANCE_TABLE.MODULE_INSTANCE = MODULE_INSTANCE_ID then the module instance is found otherwise it has not been found.

If a module instance record is not found then a new record will be created.

Module instance

The following fields will be updated on the module instance even if the value in the message is null:

Credits

Start Date

End Date

JACS Subject

ASC Code

VAT Rate

Parent Course

The value for SESSIONAL TYPE must either be SINGLE-SESSION or CROSS-SESSIONAL. This is a mandatory field.

If the CROSS_SESSION_MI_FLAG parameter is set to 0 (disabled) then the value for this field should be set to SINGLE-SESSION.

The value for PACKAGED must either be 1 or 0. This is a mandatory field. If the CROSS_SESSION_MI_FLAG parameter is set to 0 (disabled) then the value for this field should be set to 0.

Module instance assessments

The list of module instance assessments can be null and if so then no action in Quercus is taken.

If a list of module instance assessments is presented in the message and the UPDATE_MODE is ADD_UPDATE then

New assessments will be created provided they don’t already exist.

If the assessment already exists the following fields will be updated:

Weighting Factor

Pass Mark

Marks Out of

If the UPDATE_MODE is REPLACE then the list of assessments provided in the message will replace all the current assessments against the module instance provided that the current assessment:

Is not already attached to a student assessment record (STUDENT_ASSESSMENT_DETAIL)

Does not have any assessment schedule records that are bonded to other assessment schedule records

Note: if assessment schedule records are not bonded then the assessment schedule will be replaced.

Assessment schedule

The list of assessment schedule can be null and if so then no action in Quercus is taken.

If a list of assessment schedules is present in the message and the UPDATE_MODE is ADD_UPDATE then new assessment schedule records will be created provided they don’t already exist.

For those that do exist, the assessment schedule record will be updated with the values in the message fields even if the value is null. The following fields are subject to update:

Assessment Sitting

Assessment Period

Start Date

End Date

Duration

Status Code

Note

If the UPDATE_MODE is REPLACE then the list of assessments schedules provided in the messages will replace all the current assessment schedule records against the module instance assessment provided that the current assessment schedule is not already:

Bonded to other assessment schedule records. NOTE, if assessment schedule records are not bonded then the assessment schedule will be replaced.

Assessment schedule bonds

The list of Assessment Schedule Bonds can be null and if so then no action in Quercus is taken.

If a list of Assessment Schedule Bonds is present in the message and the UPDATE_MODE is ADD_UPDATE then new Assessment Schedule Bond records will be created provided they don’t already exist. If the assessment schedule bond does exist then the following fields will be updated:

Bond Type

If the UPDATE_MODE is REPLACE and the ASSESSMENT_SCHEDULE_BOND record is found then any existing bonds will be replaced with those specified in the message fields.

Module instance departments

The list of module instance departments can be null and if so then no action in Quercus is taken.

If a list of module instance departments is present in the message and the UPDATE_MODE is ADD_UPDATE then new module instance departments will be created provided they don’t already exist. If the module instance department already exists then the following fields will be updated even if the message field is null:

Lecture Hours

Department Input

If the UPDATE_MODE is REPLACE then the list of module instance departments provided in the messages will replace all the module instance department records against the module instance.

Module instance person

The GUID (Global Unique Identifier) is very important in this part of the message. The GUID is expected to be unique across the institution. Quercus checks for the existence of the person record by comparing the GUID to the value of the LDAP_ID field in the PERSON_LDAP table.

The list of module instance persons can be null and if so then no action in Quercus is taken.

If a list of module instance persons is present in the message and the UPDATE_MODE is ADD_UPDATE then new module instance person record will be created provided they don’t already exist and the person can be found in the Quercus database. If the module instance person record is found then the following fields will be updated:

Start Date

End Date

If the UPDATE_MODE is REPLACE then the list of module instance persons provided in the message will replace all module instance person records against the module instance provided that the person record is found based on the GUID.

E1 – Student enrolment (out)

Purpose

Provides details of student enrolments to external systems.

Triggering events

Event

Table

Operation(s)

1

New or change to existing Student Enrolment

Student Course Detail

I/U

2

Change of Student Course Detail Extended record

Student Course Detail Extended

I/U/D

3

Change to Student Residential Status

Student Residential Status

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(STUDENT_ENROLMENT_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Course Instance ID

Y

Varchar

30

Unique Identifier for Course Instance

Out

Student Status Code

Y

Varchar

16

Status of enrolment (e.g. P, R, WD)

Out

Student Status Description

N

Varchar

80

Status Description (e.g. pre-registered, registered)

Out

Student Category Code

N

Varchar

16

Category Code

Out

Student Category Description

N

Varchar

80

Category Description

Out

Fee Category Code

N

Varchar

16

Fee Category Code

Out

Fee Category Description

N

Varchar

80

Fee Category Description

Out

Fee Level Code

N

Varchar

16

Fee Level Code (M or CI)

Out

Fee Level Description

N

Varchar

80

Fee Level Description (Module or Course Instance)

Out

Enrolment Date

N

Date

Date student enrols on course instance

Out

Withdrawal Date

N

Date

Date student withdraws from course instance

Out

Student Start Date

N

Date

Date student starts course instance

Out

Student End Date

N

Date

Date student ends course instance

Out

Residential Status Code

N

Varchar

16

Residential Status Code

Out

Residential Status Description

N

Varchar

80

Residential Status Description

E2 – Student enrolment (in)

Purpose

Enables Quercus to enroll students onto a course instance

Only a restricted number of enrolment statuses are allowed

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(STUDENT_ENROLMENT_IN)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

QuercusPlus Person ID Number

In

Course Instance ID

Y

Varchar

30

Unique identifier for Course Instance

In

Student Status Code

Y

Varchar

16

Status of enrolment (e.g. P – Pre-Registered)

In

Student Category Code

N

Varchar

16

Category Code

In

Fee Category Code

N

Varchar

16

Fee Category Code

In

Fee Level Code

N

Varchar

16

Fee Level Code (M or CI)

In

Enrolment Date

N

Date

Date student enrols on course instance

In

Student Start Date

N

Date

Date student starts course instance

In

Student End Date

N

Date

Date student ends course instance

In

Residential Status Code

N

Varchar

16

Residential Status Code

Rules for student enrolment inbound messages

As a general rule for STUDENT_ENROLMENT_IN messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of the fields GUID, ID Number, Course Instance ID.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD.

The STUDENT_ENROLMENT_IN message will (in line with baseline processes):

Register a student on a course, assign fees (where they exist) and add any core modules.

De-register a student on a course. Where the student was previously registered will de-assign fees and remove curriculum.

The action taken is determined by the student status code:

If the status is REGISTERED_STATUS (default), Quercus will register the student, assign fees (if they exist) and add the curriculum.

If the status is PREREGISTER_STATUS (default), Quercus will de-register the student, and apply the normal de-

If the status STUDENT_WITHDRAWN_STATUS (default) or STD_CANCELLED (student cancelled), the message is rejected, and sent to the Hospital queue (with a description stating that the system cannot process these messages).

Note: To process these messages in this manner, you must set the values of the EARLY_FEES_IN_USE and EARLY_CURRICULUM_IN_USE parameters to FALSE (because if these values are TRUE, fees and the curriculum are assigned to students prior to registration).

Validation

The messageType in the metadata section must be STUDENT_ENROLMENT_IN. If this is not correctly specified then the message will not be processed and sent to the Hospital queue.

The following mandatory fields must be present in the MODULE_INSTANCE_IN message.

Message field name

Comment

Message ID

Message Type

GUID

ID Number

Course Instance ID

Unique identifier for Course Instance

Student Status Code

Only valid statuses of status type = STUDENT can be used.

The following fields are lookup fields therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

GUID

PERSON_LDAP

Course Instance ID

COURSE_INSTANCE_TABLE

Student Status Code

STATUS

Student Category Code

STUDENT_CATEGORY

Fee Category Code

FEE_CATEGORY

Fee Level Code

FEE_LEVEL

Residential Status Code

RESIDENTIAL_STATUS

The system will first check for the existence of the person record using the GUID which is unique within Quercus.

If the person is not found then the message will be sent to the Hospital Queue.

The system will then check for the existence of the course instance using the field Course Instance ID.

Should the course instance not be found then the message will not be processed and sent to the hospital queue.

The system will then check for the existence of the STUDENT_COURSE_DETAIL_TABLE (student enrolment) record. If a record already exists then it will be updated otherwise a new record will be created. The standard QP_STUDENT package will be used to create the student enrolment record – this will ensure that the processing in QML is the same as Quercus.

Student enrolment

The following fields will be updated on the student enrolment record even if the value in the message is null:

Student Category

Fee Category

Fee Level

Enrolment Date

Withdrawal Date

Student Start Date

Student End Date

Residential Status

F1 – Fee transaction (out)

Purpose

Provides details of financial transactions to external systems.

Triggering events

Event

Tables

Operations

1

Addition of financial transaction

Financial Transaction

I

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(FEE_TRANSACTION_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Course Instance ID

Y

Varchar

30

Unique Identifier for Course Instance

Out

Update Type

Y

Varchar

16

DEBIT or CREDIT

Out

Financial Transaction Type Code

Y

Varchar

16

Out

Cashbook Type

Y

Varchar

16

CASHBOOK or NON-CASHBOOK

Out

General Ledger Code

N

Varchar

40

General Ledger Code

Out

Financial Transaction Date

N

Date

Financial Transaction Date

Out

Payment Type Code

N

Varchar

16

Payment Types only required for receipts.

Out

Receipt Number

N

Number

Only required for receipts

Out

Amount

Y

Number

Financial Transaction Amount

Out

Journal Reference

N

Number

Quercus Journal Reference

Out

Transaction Note

N

Varchar

255

Optional note associated with Financial Transaction

Notes

All financial transactions are associated with a student on a course instance:

General Ledger Code can be set-up either in FINANCIALS_LINK table or in the General Ledger table.

Only non-cashbook items can be set-up in the FINANCIALS_LINK table, all cashbook items general ledger codes are found in the FINANCIAL_TRANSACTION_TYPE table.

The system looks at the lowest level first (FINANCIALS_LINK) and if no data is found then the General Ledger Code from the General Ledger is used.

The FINANCIALS_LINK table can store General Ledger Codes at either COURSE or COURSE_INSTANCE level.

The field FINANCIALS_LINK.OBJECT_NUMBER links to either COURSE_INSTANCE.OBJECT_ID or to COURSE.OBJECT_ID. The field FINANCIALS_LINK.FINANCIAL_TRANSACTION_TYPE links to FINANCIAL_TRANSACTION_TYPE.OBJECT_ID.

Cashbook type can only be CASHBOOK or NON-CASHBOOK. Lookup the table Financial_Transaction_Type to establish the cashbook type.

The financial transaction date is in standard XML format: YYYY-MM-DD.

G1 – Student curriculum (out)

Purpose

To provide student curriculum data when new records are created or existing records are updated.

Triggering events

Event

Tables

Operations

1

Changes to Student Curriculum Level 1

Student Curriculum

I/U/D

2

Changes to Student Curriculum Level 2

Student Curriculum Unit

I/U/D

3

Changes to Student Curriculum Level 3

Student Unit Detail

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(STUDENT_CURRICULUM_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Course Instance ID

Y

Varchar

30

Unique Identifier for Course Instance

Repeated List of Student Curriculum (for all levels)

Repeated List of Level 1 Modules

Out

L1 Module Code

Y

Varchar

16

CPD.MODULE

Out

L1 Module Type Code

Y

Varchar

16

CPD.MODULE_TYPE

Out

L1 Subject Registration Status Code

Y

Varchar

16

SC.SUBJECT_REGISTRATION_STATUS

Nested Repeated List of Level 2 Modules (if any exist)

Out

L2 Module Code

Y

Varchar

16

CPU.MODULE

Out

L2 Module Type Code

Y

Varchar

16

CPU.MODULE_TYPE

Out

L2 Subject Registration Status Code

N

Varchar

16

SCU.SUBJECT_REGISTRATION_STATUS

Nested Repeated List of Level 3 Modules (if any exist)

Out

L3 Module Code

Y

Varchar

16

CUD.MODULE

Out

L3 Module Type Code

Y

Varchar

16

CUD.MODULE_TYPE

Out

L3 Subject Registration Status Code

N

Varchar

16

SUD.SUBJECT_REGISTRATION_STATUS

Notes

Student Curriculum is nested. A level 1 module can have multiple level 2 modules and each of those level 2 modules can have multiple level 3 modules.

OLE-object

G2 – Student curriculum (in)

Purpose

To update Quercus student curriculum data with data from external sources.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(STUDENT_CURRICULUM_IN)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

Course Instance ID

Y

Varchar

30

Unique Identifier for Course Instance

Repeated List of Student Curriculum (for all levels)

Repeated List of Level 1 Modules

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

L1 Module Instance ID

Y

Varchar

30

CPD.MODULE_INSTANCE

In

L1 Subject Registration Status Code

Y

Varchar

16

SC.SUBJECT_REGISTRATION_STATUS

Nested Repeated List of Level 2 Modules (if any exist)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

L2 Module Instance ID

Y

Varchar

30

CPU.MODULE

In

L2 Subject Registration Status Code

N

Varchar

16

SCU.SUBJECT_REGISTRATION_STATUS

Nested Repeated List of Level 3 Modules (if any exist)

Attribute UPDATE_MODE=ADD_UPDATE or UPDATE_MODE=REPLACE (default is UPDATE_MODE=ADD_UPDATE)

In

L3 Module Instance ID

Y

Varchar

30

CUD.MODULE

In

L3 Subject Registration Status Code

N

Varchar

16

SUD.SUBJECT_REGISTRATION_STATUS

Notes

Student Curriculum is nested. A level 1 module can have multiple level 2 modules and each of those level 2 modules can have multiple level 3 modules.

OLE-object

Validation

The messageType in the metadata section must be STUDENT_CURRICULUM_IN. If this is not correctly specified then the message will not be processed and sent to the Hospital queue.

The following mandatory fields must be present in the STUDENT_CURRICULUM_IN message:

Message field name

Comment

Message ID

Message Type

GUID

Course Instance ID

Unique identifier for Course Instance (COURSE_INSTANCE_TABLE.COURSE_INSTANCE)

Module Instance ID

Unique identifier for Module Instance (MODULE_INSTANCE_TABLE.MODULE_INSTANCE).

[At levels 1, 2 & 3]

Subject Registration Status Code

L1 Module only

The following fields are lookup fields therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

GUID

PERSON_LDAP.LDAP_ID

Course Instance ID

COURSE_INSTANCE_TABLE.COURSE_INSTANCE

Module Instance ID

MODULE_INSTANCE_TABLE.MODULE_INSTANCE

Subject Registration Status Code

SUBJECT_REGISTRATION_STATUS

The system will first check for the existence of the Person using the value in the GUID field. If the person is not found then the message will be sent to the hospital queue.

The system will then check for the existence of the course instance using the value in the Course Instance ID. If a course instance record is not found the message will be sent to the hospital queue.

The system will then check for the existence of the Student Enrolment (STUDENT_COURSE_DETAIL_TABLE) record using the person and course instance values already retrieved. If a Student Enrolment record is not found the message will be sent to the hospital queue.

Once the Student Enrolment record is found the system will then process the student curriculum part of the message.

Student curriculum level 1

For each record in the level 1 curriculum the following processing will take place:

The module instance will be found based on the value in MODULE INSTANCE ID field. If the module instance is not found then the message will go to the hospital queue.

The course program detail will be found based on the Course Instance record and the Module Instance from above. If an existing record is not found then the message will be sent to the hospital queue.

Provided that the Course Program Detail record is found then the Student Curriculum will either be updated or created depending on the UPDATE_MODE.

If the UPDATE_MODE is ADD_UPDATE then:

New Student Curriculum records will be created provided they don’t already exist.

If the Student Curriculum record already exists the following fields will be updated:

Subject Registration Status

If the UPDATE_MODE is REPLACE then the list of student curriculum records presented in the message will replace all current student curriculum records, along with all the child records found in the following tables:

Student Assessment, Financial Transactions, Student Absence, FE Learning Aims & Student Attendance Groups

Student curriculum level 2

For each record in the level 2 curriculum the following processing will take place:

The module instance will be found based on the value in MODULE INSTANCE ID field. If the module instance is not found then the message will go to the hospital queue.

The course program unit will be found based on the Course Program Detail record (from the level 1 curriculum) and the Module Instance from above. If an existing record is not found then the message will be sent to the hospital queue.

Provided that the Course Program Unit record is found then the Student Curriculum Unit will either be updated or created depending on the UPDATE_MODE.

If the UPDATE_MODE is ADD_UPDATE then:

New Student Curriculum Unit records will be created provided they don’t already exist

If the Student Curriculum Unit record already exists the following fields will be updated:

Subject Registration Status

If the UPDATE_MODE is REPLACE then the list of student curriculum unit records presented in the message will replace all current student curriculum unit records provided that the student curriculum unit:

Is not already attached to child records such as Student Assessment, Financial Transactions, Student Absence & Student Attendance Groups

Student curriculum level 3

For each record in the level 3 curriculum the following processing will take place:

The module instance will be found based on the value in MODULE INSTANCE ID field. If the module instance is not found then the message will go to the hospital queue.

The course unit detail will be found based on the Course Program Unit record (from the level 2 curriculum) and the Module Instance from above. If an existing record is not found then the message will be sent to the hospital queue.

Provided that the Course Unit Detail record is found then the Student Unit Detail will either be updated or created depending on the UPDATE_MODE

If the UPDATE_MODE is ADD_UPDATE then:

New Student Unit Detail records will be created provided they don’t already exist

If the Student Unit Detail record already exists the following fields will be updated:

Subject Registration Status

If the UPDATE_MODE is REPLACE then the list of student unit detail records presented in the message will replace all current student unit detail records provided that the student unit detail:

Is not already attached to child records such as Student Assessment

H1 – M&S – student overall grade (out)

Purpose

To provide data about a student’s overall grade to external systems.

Triggering events

Event

Tables

Operations

1

Changes to Student Overall Course Grade

Student Overall Course Grade

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(STUDENT_OVERALL_GRADE_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Course Instance ID

Y

Varchar

30

Unique identifier for Course Instance

Out

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

Out

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

Out

Grade Code

N

Varchar

16

Out

Grade Description

N

Varchar

80

Out

Overall Average

N

Number

Out

Assessment Sitting Code

N

Varchar

16

Out

Assessment Sitting Description

N

Varchar

80

Notes

All values are derived from the table STUDENT_OVERALL_COURSE_GRADE

The PASSFLAG comes from the table GRADE_SCHEME_RANGE and is used as a flag in Quercus to determine whether or not the grade is a pass or fail. A null value is allowed

H1a – M&S – student assessment (out)

Purpose

To provide data about a student’s assessment results to external systems.

Triggering events

Event

Tables

Operations

1

Changes to Student Assessment (Level 1)

Student Assessment

I/U/D

2

Changes to Student Unit Assessment (Level 2)

Student Unit Assessment

I/U/D

3

Changes to Student Unit Assessment Detail (Level 3)

Student Unit Assess Detail

I/U/D

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(STUDENT_ASSESSMENT_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Course Instance ID

Y

Varchar

30

Unique Identifier for Course Instance

Repeated List of Student Assessment Results (for all levels)

Repeated List of Assessment Results at Level 1

Out

L1 Module Code

Y

Varchar

16

Out

Assessment Period Code

N

Varchar

16

Out

Assessment Sitting Code

N

Varchar

16

Out

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

Out

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

Out

Grade Code

N

Varchar

16

Out

Grade Description

N

Varchar

80

Out

Gross Result

N

Number

Out

Penalty Percentage

N

Number

Out

Assessment Result

N

Number

Out

Status Code

N

Varchar

16

Out

Result Locked

N

Number

1 = Locked

0 = Not Locked

Repeated List of Assessment Detail Results for Module At Level 1

In

Assessment Code

Y

Varchar

16

The code of the assessment

In

Assessment Result

N

Number

In

Weight

N

Number

The weight of the assessment detail. (the sum of the assessment detail records should add up to 1)

In

Penalty Percentage

N

Number

In

Gross Result

N

Number

In

Status Code

N

Varchar

16

Repeated List of Assessment Results at Level 2 (if any exist)

Out

L2 Module Code

Y

Varchar

16

Out

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

Out

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

Out

Grade Code

N

Varchar

16

Out

Grade Description

N

Varchar

80

Out

Gross Result

N

Number

Out

Penalty Percentage

N

Number

Out

Assessment Result

N

Number

Out

Status Code

N

Varchar

16

Repeated List of Assessment Detail Results for Module At Level 1

In

Assessment Code

Y

Varchar

16

The code of the assessment

In

Assessment Result

N

Number

In

Weight

N

Number

The weight of the assessment detail. (the sum of the assessment detail records should add up to 1)

In

Penalty Percentage

N

Number

In

Gross Result

N

Number

In

Status Code

N

Varchar

16

Repeated List of Assessment Results at Level 3 (if any exist)

Out

L3 Module Code

Y

Varchar

16

Out

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

Out

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

Out

Grade Code

N

Varchar

16

Out

Grade Description

N

Varchar

80

Out

Gross Result

N

Number

Out

Penalty Percentage

N

Number

Out

Assessment Result

N

Number

Out

Status Code

N

Varchar

16

Repeated List of Assessment Detail Results for Module At Level 1

In

Assessment Code

Y

Varchar

16

The code of the assessment

In

Assessment Result

N

Number

In

Weight

N

Number

The weight of the assessment detail. (the sum of the assessment detail records should add up to 1)

In

Penalty Percentage

N

Number

In

Gross Result

N

Number

In

Status Code

N

Varchar

16

Notes

All values for level 1 results are derived from the table STUDENT_ASSESSMENT.

All values for level 2 results are derived from the table STUDENT_UNIT_ASSESSMENT.

All values for level 3 results are derived from the table STUDENT_UNIT_ASSESS_DETAIL.

Level 4, STUDENT_ASSESSMENT_DETAIL, results can only be entered at the lowest level of course curriculum, so if it is a level 1 course then level 4 results must be entered at level 1, if it is a level 2 course then level 4 results must be entered at level 2 and if it is a level 3 course then level 4 results must be entered at level 3.

The PASSFLAG comes from the table GRADE_SCHEME_RANGE and is used as a flag in Quercus to determine whether or not the grade is a pass or fail. A null value is allowed.

OLE-object

H2 – M&S – student overall grade (in)

Purpose

Update Quercus Student Overall Grade from external sources.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(STUDENT_OVERALL_GRADE_IN)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

In

Course Instance ID

Y

Varchar

30

Unique identifier for course instance

In

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

In

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

In

Grade Code

N

Varchar

16

In

Grade Description

N

Varchar

80

In

Overall Average

N

Number

In

Assessment Sitting Code

N

Varchar

16

In

Assessment Sitting Description

N

Varchar

80

Notes

All values will be entered into the table STUDENT_OVERALL_COURSE_GRADE

The PASSFLAG comes from the table GRADE_SCHEME_RANGE and is used as a flag in Quercus to determine whether or not the grade is a pass or fail. A null value is allowed.

Rules for student overall grade inbound messages

As a general rule for STUDENT_OVERALL_GRADE_IN messages, if a field value has changed then it will be updated in Quercus with the changed value with the exception of the fields:

ID Number

Course Instance Code

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD.

The messageType in the metadata section must be STUDENT_OVERALL_GRADE_IN. If this is not correctly specified then the message will not be processed.

The system will check for the existence of a STUENT_OVERALL_COURSE_GRADE record using the ID Number and Course Instance Code to find a STUDENT_COURSE_DETAIL record from which a STUDENT_OVERALL_COURSE_GRADE record can be identified.

If no STUDENT_COURSE_DETAIL record is found then no further processing will take place and the message will be placed in the Hospital queue.

If the Student Overall Course Grade record is found then it will be updated with the values in the message otherwise a new record will be created.

The following rules apply for each field:

PassFlag

This field will only be used if a new GRADE_SCHEME_RANGE record is being created. See next point for details.

Grade scheme code and grade code

These fields will be used in conjunction with the Course Year from the Course Instance record to find the GRADE_SCHEME_RANGE value.

If no value is found for GRADE_SCHEME_RANGE then a new one will be created provided that the GRADE CODE and GRADE SCHEME CODE are not null (if no grade description is provided then the grade code will be used as the description). The grade scheme year will be same as the COURSE_INSTANCE.COURSE_YEAR.

Note: this will create a new grade scheme and add records to that new grade scheme.

Overall average

This field will always be updated even if it is null.

Assessment sitting

If this field is null or the value cannot be found in Quercus then it will always default to the value specified in the MARKS_AND_STANDARDS_DEFAULTS first sitting field.

H2a – M&S – student assessment (in)

Purpose

Update Quercus Student Assessment data from external systems.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(STUDENT_ASSESSMENT_IN)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

In

Course Instance ID

Y

Varchar

30

Unique identifier for course instance

Repeated List of Student Assessment Results (for all levels)

Repeated List of Assessment Results at Level 1

In

L1 Module Code

Y

Varchar

16

In

Assessment Period Code

N

Varchar

16

In

Assessment Sitting Code

N

Varchar

16

In

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

In

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

In

Grade Code

N

Varchar

16

In

Grade Description

N

Varchar

80

In

Gross Result

N

Number

In

Penalty Percentage

N

Number

In

Assessment Result

N

Number

In

Status Code

N

Varchar

16

In

Result Locked

N

Number

1 = Locked

0 = Not Locked

Repeated List of Assessment Detail Results for Module At Level 1

In

Assessment Code

Y

Varchar

16

The code of the assessment

In

Assessment Result

N

Number

In

Weight

N

Number

The weight of the assessment detail. (the sum of the assessment detail records should add up to 1)

In

Penalty Percentage

N

Number

In

Gross Result

N

Number

In

Status Code

N

Varchar

16

Repeated List of Assessment Results at Level 2 (if any exist)

In

L2 Module Code

Y

Varchar

16

In

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

In

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

In

Grade Code

N

Varchar

16

In

Grade Description

N

Varchar

80

In

Gross Result

N

Number

In

Penalty Percentage

N

Number

In

Assessment Result

N

Number

In

Status Code

N

Varchar

16

Repeated List of Assessment Detail Results for Module At Level 2

In

Assessment Code

Y

Varchar

16

The code of the assessment

In

Assessment Result

N

Number

In

Weight

N

Number

The weight of the assessment detail. (the sum of the assessment detail records should add up to 1)

In

Penalty Percentage

N

Number

In

Gross Result

N

Number

In

Status Code

N

Varchar

16

Repeated List of Assessment Results at Level 3 (if any exist)

In

L3 Module Code

Y

Varchar

16

In

PassFlag

N

Varchar

4

Valid values are:

‘PASS’

‘FAIL’

‘NA’

‘’

In

Grade Scheme Code

N

Varchar

16

The code of the grade scheme on which the result was based on.

In

Grade Code

N

Varchar

16

In

Grade Description

N

Varchar

80

In

Gross Result

N

Number

In

Penalty Percentage

N

Number

In

Assessment Result

N

Number

In

Status Code

N

Varchar

16

Repeated List of Assessment Detail Results for Module At Level 3

In

Assessment Code

Y

Varchar

16

The code of the assessment

In

Assessment Result

N

Number

In

Weight

N

Number

The weight of the assessment detail. (the sum of the assessment detail records should add up to 1)

In

Penalty Percentage

N

Number

In

Gross Result

N

Number

In

Status Code

N

Varchar

16

Notes

All values for level 1 results are entered into the STUDENT_ASSESSMENT table.

All values for level 2 results are entered into the STUDENT_UNIT_ASSESSMENT table.

All values for level 3 results are entered into the STUDENT_UNIT_ASSESS_DETAIL table.

Level 4, STUDENT_ASSESSMENT_DETAIL, results can only be entered at the lowest level of course curriculum, so if it is a level 1 course then level 4 results must be entered at level 1, if it is a level 2 course then level 4 results must be entered at level 2 and if it is a level 3 course then level 4 results must be entered at level 3.

The PASSFLAG comes from the table GRADE_SCHEME_RANGE and is used as a flag in Quercus to determine whether or not the grade is a pass or fail. A null value is allowed.

Rules for student assessment inbound messages

As a general rule for STUDENT_ASSESSMENT_IN messages, if a field value has changed then it will be updated in Quercus with the changed value with the exception of the fields

ID Number

Course Instance Code

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD

The messageType in the metadata section must be STUDENT_ASSESSMENT_IN. If this is not correctly specified then the message will not be processed.

The system will first check for the existence of a STUDENT_COURSE_DETAIL record the fields:

ID Number

Course Instance Code

If a STUDENT_COURSE_DETAIL record does not exist then no further processing will take place and the message will be placed in the Hospital Queue.

Once a STUDENT_COURSE_DETAIL record has been found the system will check for the existence of a STUDENT_CURRICULUM record based on the level 1 module code, a STUDENT_CURRICULUM_UNIT record based on the level 2 module code and a STUDENT_UNIT_DETAIL record based on the level 3 module code.

Note: it is possible to send one, two or three levels of assessments in the STUDENT_ASSESSMENT_IN message.

If a STUDENT_CURRICULUM, a STUDENT_CURRICULUM_UNIT or a STUDENT_UNIT_DETAIL does not exist for the student for the given module level no further processing will take place for that module and the system will move onto the next module.

Once the curriculum record has been found the system will either create or update the assessment record for that level of curriculum.

If the assessment record does not exist then a new assessment record will be created using the values from the message.

If the assessment record exists then that record will be updated using the values from the message.

The following rules apply for each field:

Module code – all levels

The module code at level 1 is used to determine the student curriculum record.

The module code at level 2 is used to determine the student curriculum unit record.

The module code at level 3 is used to determine the student unit detail record.

Assessment period code – level 1 only

If the assessment period not found and is not null then a new assessment period will be created. If a null value is passed then the STUDENT_ASSESSMENT record will be created/updated to null.

Assessment sitting code – level 1 only

If this field is null or the value cannot be found in Quercus then it will always default to the value specified in the MARKS_AND_STANDARDS_DEFAULTS first sitting field.

PassFlag

This field will only be used if a new GRADE_SCHEME_RANGE record is being created. See next point for details.

Grade scheme code & grade code

These fields will be used in conjunction with the Course Year from the Course Instance record to find the GRADE_SCHEME_RANGE value.

If no value is found for GRADE_SCHEME_RANGE then a new one will be created provided that the GRADE CODE and GRADE SCHEME CODE are not null (if no grade description is provided then the grade code will be used as the description). The grade scheme year will be same as the COURSE_INSTANCE.COURSE_YEAR.

NOTE – this will create a new grade scheme and add records to that new grade scheme.

Gross result

This field will always be updated even if it is null.

Penalty percentage

This field will always be updated even if it is null.

Assessment result

This field will always be updated even if it is null.

Status code

The value for STATUS CODE must be a valid result status in Quercus. If a valid result status code is not passed then a default status of NORMAL (Normal Result) will be used. If a null status code is passed then the record will be updated/created to a null status.

Result locked – level 1 only

Valid entries for this field are:

1 – record locked

0 – record not locked

If the field = 1 then a STUDENT_RESULT_COMPLETE record will be created.

If the field = 0 and a STUDENT_RESULT_COMPLETE record exists then it will be removed.

Assessment detail results – all levels

The ASSESSMENT CODE must exist in Quercus – no new assessment codes will be created.

The ASSESSMENT CODE must also already be associated with the module, that is, a course program assessment record should already exist in Quercus. If the course program assessment record does not exist then no further processing will be done.

ASSESSMENT RESULT, WEIGHT, PENALTY PERCENTAGE and GROSS RESULT can all be null. This will create a blank assessment detail record. If these are null values on the message and an assessment detail result exists then it will be updated with null values.

STATUS CODE can be a null value and the assessment detail result will be created/updated with a null value. If a STATUS CODE is present it must be a valid result status in Quercus. If a valid result status code is not passed then the default status of NORMAL (Normal Result) will be used.

J1 – Student sponsorship (out)

Purpose

To provide student sponsorship data when new records are created or existing records are updated.

Triggering events

Event

Tables

Operations

1

Changes to Student Funding

Student Funding Detail

I/U

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(STUDENT_SPONSORSHIP_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Course Instance Code

Y

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Course Instance ID

Y

Varchar

30

Unique identifier for course instance

Out

Funding Type Code

N

Varchar

16

Funding Type Code

Out

Funding Type Description

N

Varchar

80

Funding Type Description

Out

Organisation Code

N

Varchar

16

Organisation Code

Out

Amount

N

Number

Amount Funded

K1 – applicant (out)

Purpose

To provide a complete record of an applicant and their application choices

Triggering events

Event

Tables

Operations

1

Change to applicant choice workflow status

WF_WORKFLOW_INSTANCE

I/U

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(APPLICANT_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Fee Category Code

N

Varchar

16

Out

Residential Status Code

N

Varchar

16

Out

Student Category Code

N

Varchar

16

Repeated List of Applications

Out

Application Date

N

Date

Date the applicant first applied

Out

APLUS Application Type Code

Y

Varchar

16

DIRECT, UCAS, UKPASS, etc.

Out

APLUS Application Type Description

Y

Varchar

80

Description of aplus application type

Out

Year of Application

N

Number

Nested Repeated List of Choices

Out

Choice Number

Y

Number

Out

Record Date

N

Date

Date of Application

Out

Course Code

Y

Varchar

16

Mandatory Course Code

Out

Course Instance ID

N

Varchar

30

New identifier for Course Instances – will be used to uniquely identify course instances (version 8.2.2)

Out

Course Instance Code

N

Varchar

40

Unique code for a course instance. It is a concatenation of course code, instance code and academic session separated with a hyphen (-)

Out

Application Type Code

N

Varchar

16

Out

Application Type Description

N

Varchar

80

Out

Campus Code

N

Varchar

16

Out

Faculty Code

N

Varchar

16

Out

Year of Entry

N

Number

Out

Month of Entry

N

Number

Out

Point of Entry

N

Varchar

1

Out

Institution Code

N

Varchar

4

Out

Choice Cancelled

Y

Varchar

1

Out

Decision

N

Varchar

3

Out

Reply

N

Varchar

3

Out

Is Student

Y

Varchar

1

Indicator to identify if the applicant is a student already.

Allowed values:

Y – applicant is student

N – applicant is not student

Out

Workflow

Out

Workflow Stage

Y

Varchar

30

The workflow status of the applicant choice record

Out

Workflow Action

Y

Varchar

160

The description field from V_OC_AUDIT_TRAIL (see details below)

Out

Workflow Action Date

Y

Date

The change date of the workflow stage

Out

Interview Schedule

Out

Interview Date

N

Date

Out

Interview Time

N

Varchar

5

Stored a 4 digit number on the database, this will be present in the format HH24:MI

Notes

Workflow Action – this is the description field from V_OC_AUDIT_TRAIL where the OBJECT_NUMBER is equal to the OBJECT_ID of WF_WORKFLOW_INSTANCE and the TIMESTAMP is equal to the LAST_STAGE_CHANGE of WF_WORKFLOW_INSTANCE

Workflow Action Date – this is WF_WORKFLOW_INSTANCE.LAST_STAGE_CHANGE

Interview Schedule – this data comes from the table UCAS_INTERVIEW_SCHEDULE.

IsStudent – check if the applicant is already a student on the course instance.

Remove trigger from APLUS_CHOICE table – need drop script for existing customers.

XML sample

<?xml version="1.0" encoding="UTF-8"?>
<ApplicantData>
<QuercusMessageMeta>
  <messageId>QUERCUS_449855</messageId>
  <messageType>APPLICANT_OUT</messageType>
  <authenticationChecksum>b4546b46daf65d9c95f9f5501d49a570</authenticationChecksum>
</QuercusMessageMeta>
<ApplicantRecord applicantObjectId="360673861" timestamp="2014-01-20T15:48:14">
  <GUID>QUERCUS:80289</GUID>
  <idNumber>80289</idNumber>
  <Applications>
    <Application>
      <applicationDate>2013-11-14</applicationDate>
      <applicationTypeCode>DA</applicationTypeCode>
      <applicationTypeDescription>Direct</applicationTypeDescription>
      <yearOfApplication>2010</yearOfApplication>
      <Choices>
        <Choice>
          <choiceNumber>1</choiceNumber>
          <recordDate>2013-11-14</recordDate>
          <courseCode>PREHISTORY</courseCode>
          <courseInstanceId>PREHISTORY_64523882</courseInstanceId>
          <courseInstanceCode>PREHISTORY-PREHIST-3-2010</courseInstanceCode>
          <applicationTypeCode>ONLINE</applicationTypeCode>
          <applicationTypeDescription>Online</applicationTypeDescription>
          <yearOfEntry>2010</yearOfEntry>
          <monthOfEntry>9</monthOfEntry>
          <pointOfEntry>1</pointOfEntry>
          <institutionCode>G14</institutionCode>
          <choiceCancelled>N</choiceCancelled>
          <decision>C</decision>
          <reply>X</reply>
          <isStudent>N</isStudent>
          <workflow>
            <stage>INTERVIEW_SCHEDULED</stage>
            <action>Schedule Interview</action>
            <actionDate>2014-01-20T15:40:13</actionDate>
          </workflow>
          <interviewSchedule>
            <date>2014-02-14</ date >
            <time>16:30</time>
            <actionDate>2014-01-20T15:40:13</actionDate>
          </ interviewSchedule >
        </Choice>
      </Choices>
    </Application>
  </Applications>
</ApplicantRecord>
</ApplicantData>

L1 – Person note (out)

Purpose

Provides details of a given person note, only one note per message

Triggering events

Event

Tables

Operations

1

Change to Person Notes

Note

I/U

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(PERSON_NOTE_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

Note Type Code

Y

Varchar

16

Note Type Code

Out

Note Type Description

N

Varchar

80

Note Type Description

Out

Note

N

Varchar

2000

Out

Date Closed

N

Date

Date Closed

Out

Action

Y

Varchar

3

‘SET’ or ‘CLOSE’

Notes

Only one note per message will be queued. The addition of multiple notes against a person will trigger multiple messages.

The Action field in the note message is to allow consumers identify whether or not the note is a new or existing note being updated or if an existing note is being closed.

If the value is SET then the note is either a new note or an updated note

If the value is CLOSE then the note is an existing note being closed.

The Date Closed is in standard XML format: YYYY-MM-DD

L1 – Person note (in)

Purpose

Update Quercus Person Notes from external sources

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(PERSON_NOTE_IN)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

Note Type Code

Y

Varchar

16

Note Type Code

In

Note Type Description

N

Varchar

80

Note Type Description

In

Note

N

Varchar

2000

In

Date Closed

N

Date

Date Closed

In

Action

Y

Varchar

3

‘SET’ or ‘CLOSE’

Rules for person note inbound messages

As a general rule for PERSON_NOTE_IN messages the action field, which cannot be null, will govern how notes are created or updated.

The value of the action field can either be ‘SET’ or ‘CLOSE’.

The system will check for the existence of notes base on the PERSON, derived from ID Number, and NOTE TYPE CODE. The NOTE TYPE CODE must be a valid note type in Quercus

If the Action = SET then the following rules apply:

If the note does not already exist then a new note is created.

If a note already exists and there is only one note of that type then that note will be updated.

If multiple notes already exist then all existing notes of that type will be set to closed and the latest note, based on timestamp, will be updated. This ensures that only one note of any given type against person can be open at any one time.

If the Action = CLOSE the following rules apply:

If the note does not exist then nothing is done.

If a note is found and is the only note of that type then the note will be closed by marking the date closed with the date in the Date Closed field.

If more than one note of that type is found then all notes will be closed by marking the date closed with the date in the Date Closed field.

One message, one note – each message will contain one note only

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD

The messageType in the metadata section must be: PERSON_NOTE_IN. If this is not correctly specified then the message will not be processed.

M1 – HESA person (out)

Purpose

Provides details of a person’s HESA attributes.

Triggering events

Event

Tables

Operations

1

Change to HESA Person

HESA Person

I/U

2

Change to HESA Person Instance

HESA Person Instance

I/U

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(HESA_PERSON_OUT)

Out

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

Out

ID Number

Y

Number

Quercus Person ID Number

Out

HESA Dependants Code

N

Varchar

2

HESA Dependants

Out

HESA Dependants Description

N

Varchar

80

Out

HESA Disability Code

N

Varchar

2

HESA Disability

Out

HESA Disability Description

N

Varchar

80

Out

Ethnic Grouping Code

N

Varchar

2

Ethnic Grouping (from HESA_PERSON_INSTANCE.ETHNIC)

Out

Ethnic Grouping Description

N

Varchar

80

Out

Surname At 16

N

Varchar

30

Family Name at 16th Birthday

Out

HUSID

N

Varchar

HESA Unique Student Identifier

Out

National Identity1 Code

N

Varchar

1

HESA National Identity 1

Out

National Identity1 Description

N

Varchar

80

Out

National Identity2 Code

N

Varchar

1

HESA National Identity 2

Out

National Identity2 Description

N

Varchar

80

Out

Nationality Code

N

Varchar

2

Nationality

Out

Nationality Description

N

Varchar

80

Out

Scottish Candidate Number

N

Varchar

9

Out

Term Accommodation Code

N

Varchar

1

Term Time Accommodation Code (from HESA_PERSON_INSTANCE.HESA_TERM_ACCOMMODATION)

Out

Term Accommodation Description

N

Varchar

80

Out

Term Postcode

N

Varchar

8

Term Time Postcode (from HESA_PERSON_INSTANCE.TERM_TIME_POSTCODE)

Out

UCAS Personal ID

N

Varchar

10

UCAS Personal Identifier

Out

Unique Learner No

N

Varchar

10

Unique Learner Number

Out

Welsh Speaker Code

N

Varchar

1

Welsh Speaker Indicator

Out

Welsh Speaker Description

N

Varchar

80

Out

Sex Identifier Code

N

Varchar

1

Sex Identifier

Out

Sex Identifier Description

N

Varchar

80

Out

Religion Belief Code

N

Varchar

2

Religion or Belief

Out

Religion Belief Description

N

Varchar

80

Out

Sexual Orientation Code

N

Varchar

2

Sexual Orientation

Out

Sexual Orientation Description

N

Varchar

80

Out

Gender Identity Code

N

Varchar

2

Gender Identity

Out

Gender Identity Description

N

Varchar

80

Notes

A person can only have one HESA_PERSON record but multiple HESA_PERSON_INSTANCE records depending on the number of academic sessions they have studied at the institution. The system takes the values for HESA_PERSON_INSTANCE from the most recent academic session available for the person.

M2 – HESA person (in)

Purpose

Provides details a person’s HESA attributes.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(HESA_PERSON_IN)

In

GUID

Y

Varchar

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

Academic Session

Y

Number

Used to identify the HESA_PERSON_INSTANCE record

In

HESA Dependants Code

N

Varchar

2

HESA Dependants

In

HESA Disability Code

N

Varchar

2

HESA Disability

In

Ethnic Grouping Code

N

Varchar

2

Ethnic Grouping (from HESA_PERSON_INSTANCE.ETHNIC)

In

Surname At 16

N

Varchar

30

Family Name at 16th Birthday

In

HUSID

N

Varchar

16

HESA Unique Student Identifier

In

National Identity1 Code

N

Varchar

1

HESA National Identity 1

In

National Identity2 Code

N

Varchar

1

HESA National Identity 2

In

Nationality Code

N

Varchar

2

Nationality

In

Scottish Candidate Number

N

Varchar

9

In

Term Accommodation Code

N

Varchar

1

Term Time Accommodation Code (from HESA_PERSON_INSTANCE.HESA_TERM_ACCOMMODATION)

In

Term Postcode

N

Varchar

8

Term Time Postcode (from HESA_PERSON_INSTANCE.TERM_TIME_POSTCODE)

In

UCAS Personal ID

N

Varchar

10

UCAS Personal Identifier

In

Unique Learner No

N

Varchar

10

Unique Learner Number

In

Welsh Speaker Code

N

Varchar

1

Welsh Speaker Indicator

In

Sex Identifier Code

N

Varchar

1

Sex Identifier

In

Religion Belief Code

N

Varchar

2

Religion or Belief

In

Sexual Orientation Code

N

Varchar

2

Sexual Orientation

In

Gender Identity Code

N

Varchar

2

Gender Identity

Notes

A person can only have one HESA_PERSON record but multiple HESA_PERSON_INSTANCE records depending on the number of academic sessions they have studied at the institution. The system takes the values for HESA_PERSON_INSTANCE based on the Academic Session XML node.

Rules for HESA person inbound messages

As a general rule for HESA_PERSON_IN messages if a field value has changed then it will be updated in Quercus with the changed value with the exception of GUID and ID Number.

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD.

Validation

The messageType in the metadata section must be HESA_PERSON_IN. If this is not correctly specified then the message will not be processed and will be sent to the Hospital queue.

The following mandatory fields must be present in the HESA_PERSON_IN message

Message field name

Comment

Message ID

Message Type

GUID

ID Number

Academic Session

The following fields are lookup fields, therefore the data in these fields must already exist in Quercus otherwise the message will not be processed:

Lookup field

Quercus table name

GUID

PERSON_LDAP

HESA Dependants Code

HESA_DEPENDANTS

HESA Disability Code

HESA_DISABILITY

Ethnic Grouping Code

ETHNIC_GROUPING

National Identity1 Code

HESA_NATIONAL_IDENTITY

National Identity2 Code

HESA_NATIONAL_IDENTITY

Nationality Code

NATIONALITY

Term Accommodation Code

HESA_TERM_ACCOMMODATION

Welsh Speaker Code

HESA_WELSH_SPEAKER

Sex Identifier Code

HESA_SEXID

Religion Belief Code

HESA_RELIGION

Sexual Orientation Code

HESA_SEXORT

Gender Identity Code

HESA_GENDERID

HESA person

The system will check for the existence of the person using the GUID (Global Unique Identifier) checking the table PERSON_LDAP.LDAP_ID. The GUID is expected to be unique across the institution.

If the person is found then the system will check for the existence of a HESA_PERSON record. If the HESA_PERSON record does not exist then a new record is created and the values from the message are used even if they are null. A HESA_PERSON_INSTANCE record is also created for the academic session provided in the XML message.

If the person is found and a HESA_PERSON record already exists then the values from the message are used to update the HESA_PERSON record even if they are null. The HESA_PERSON_INSTANCE record for the academic session provided in the XML message will also be updated.

N2 – Course stream (in)

Purpose

Update Quercus Course Stream from external sources.

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(COURSE_STREAM_IN)

In

Course Stream Code

Y

Varchar

16

Course Stream Code

In

Course Stream Description

Y

Varchar

80

Course Stream Description

In

Valid From

N

Date

Date from which the course stream is valid

In

Valid To

N

Date

Date to which the course stream is valid

In

Sort Order

N

Number

Order in the table in which the data is sorted.

Rules for course stream inbound messages

All inbound messages should be enqueued to the MESSAGE_IN_QT queue table.

All date fields must be in the format YYYY-MM-DD.

The messageType in the metadata section must be COURSE_STREAM_IN. If this is not correctly specified then the message will not be processed.

The system will check for the existence of the COURSE_STREAM based on the course stream code.

If the course stream is found then the record will be updated with the values in the message.

If the course stream record is not found then a new course stream record will be created with the values in the message.

O1 – Person photo (out)

Purpose

To provide third party systems with person images.

Triggering events

Event

Tables

Operations

Change to Student Images

Student Images

I/U

Fields

In/Out

Field

Mandatory

Type

Length

Description

Out

Message ID

Y

Varchar

Message Identifier (transaction id)

Out

Message Type

Y

Varchar

(PERSON_PHOTO_OUT)

Out

GUID

Y

Varchar

30

Global Unique Identifier – LDAP_ID

Out

ID Number

Y

Number

Quercus ID Number

Out

Image

Y

Binary

Image is JPG formatted with base64 encoding

Notes:

Only one image per person. The message will be triggered if a new image is loaded for a person or an existing image is updated.

The image in the Quercus database is stored as a Binary Long Object (BLOB) so to transfer it to other media the image is encoded using Base64.

Example

<PersonPhotograph>
  <QuercusMessageMeta>
    <messageId>QUERCUS_449915</messageId> 
    <messageType>PERSON_PHOTO_OUT</messageType> 
  <authenticationChecksum>cd07cedefe7d4e0ee83f9bc636d02449</authenticationChecksum> 
  </QuercusMessageMeta>   
  <personPhotographRecord timestamp="2014-03-11T15:00:00">
    <GUID>64732</GUID>
    <idNumber>64732</idNumber>
    <image format="JPG" encoding="base64">   
/9j/4AAQSkZJRgABAQEA8ADwAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEB
AQEBAQECAQEBAQEBAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/2wBDAQEB
AQEBAQICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC
AgICAgICAgICAgICAgL/wAARCACCAGQDAREAAhEBAxEB/8QAHgAAAQQDAQEBAAAA
...
    </image>
  </personPhotographRecord>
</PersonPhotograph>

O2 – Person photo (in)

Purpose

Update Quercus with Person images from external sources.

Triggering events

Fields

In/Out

Field

Mandatory

Type

Length

Description

In

Message ID

Y

Varchar

Message Identifier (transaction id)

In

Message Type

Y

Varchar

(PERSON_PHOTO_IN)

In

GUID

Y

Varchar

30

Global Unique Identifier – LDAP ID

In

ID Number

Y

Number

Quercus Person ID Number

In

Image

Y

Binary

Image must be JPG formatted with base64 encoding

Example

<PersonPhotograph>
  <QuercusMessageMeta>
    <messageId>LIBRARY_5566001</messageId> 
    <messageType>PERSON_PHOTO_IN</messageType> 
  </QuercusMessageMeta>   
  <personPhotographRecord>
    <GUID>64732</GUID>
    <idNumber>64732</idNumber>
    <image format="JPG" encoding="base64">   
/9j/4AAQSkZJRgABAQEA8ADwAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEB
AQEBAQECAQEBAQEBAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/2wBDAQEB
AQEBAQICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC
AgICAgICAgICAgICAgL/wAARCACCAGQDAREAAhEBAxEB/8QAHgAAAQQDAQEBAAAA
...
    </image>
  </personPhotographRecord>
</PersonPhotograph>

Rules for person photo inbound messages

The messageType in the metadata section must be PERSON_PHOTO_IN. If this is not correctly specified then the message will not be processed.

All mandatory fields must be included in the message otherwise the message will be pushed to the hospital queue.

Images must be encoded using Base64, see http://en.wikipedia.org/wiki/Base64 for details.

Images will be processed using the standard Quercus package QP_PERSON.

The existence of the person record will be checked using the value in the GUID. If the GUID is not validated then the person is deemed not to exist in Quercus and the message will be sent to the hospital queue with an appropriate validation reason.

Once the person record is validated then the message will be processed as follows:

If there is no image on file for the person then a new record will be created.

If there already is an image on file then the existing image will be updated with the image in the message.

Only one image person message is allowed.