Skip to main content
EMS Software, LLC

Colleague (Oracle or SQL) Views Data Model

EMS Software will code a set of views specific to your organization’s use of Colleague to allow for integration between EMS and Colleague. Outlined are the baseline tables/columns accessed in the views.

You will be provided a Campus Technical Environment Questionnaire which allows for identification of specific fields used by EMS that tend to vary in individual Colleague implementations. Following return of the questionnaire, EMS Views will be coded for your organization and a final review done in EMS. Your Implementation Consultant can assist in more detailed explanations or demonstrations of how the information will be used in EMS to support the Academic Scheduling process, as needed for completion of the questionnaire.  Once reviewed and accepted, further view modifications will be subject to EMS Professional Services time and materials fees.

Note: Optionally and on a custom basis, up to 10 User Defined Fields can be added to the set of views for each organization to support desired workflow. Detailed business requirements are needed to provide a quote for customization of the views for this purpose.

Modification of views by the customer is unsupported and voids the Annual Service Agreement for the integration with Colleague.

Fields highlighted in BLUE are required to be unique.

Buildings

EMS View: EMS_vwBuildings

View Permissions: Read Only

Colleague Tables: BUILDINGS

Purpose: Used to define buildings list in EMS. Required for buildings that host academic courses; additional buildings can be added manually to EMS if used only for non-academic purposes.

EMS Field

Colleague Field

SISID

BUILDINGS_ID

BuildingCode

BUILDINGS_ID

BuildingDescription

BLDG_DESC

Room Types

EMS View: EMS_vwRoomTypes

View Permissions: Read Only

Colleague Tables: ROOM_TYPES

Purpose: Used to define room type list in EMS. Room Types classify each room and allows for searching, sorting, and reporting by classification. Additional room types can be added to EMS manually.

EMS Field

Colleague Field

SISID

ROOM_TYPES_ID

RoomTypeDescription

RMTP_DESCRIPTION

Rooms

EMS View: EMS_vwRooms

View Permissions: Read Only

Colleague Tables: ROOMS

Purpose: Used to define list of rooms and their capacities for academic purposes in EMS; related to Buildings and Room Types. Required for rooms that host academic courses; additional rooms can be added manually to EMS if used only for non-academic purposes.

EMS Field

Colleague Field

SISID

ROOMS_ID

SISBuildingID

ROOMS_ID (parsed)

SISRoomTypeID

ROOM_TYPE

RoomCode

ROOMS_ID (parsed)

RoomDescription

ROOM_NAME

Capacity

ROOM_CAPACITY

Instructors

EMS View: EMS_vwInstructors

View Permissions: Read Only

Colleague Tables: COURSE_SEC_FACULTY, PERSON

Purpose: Used to define list of instructors in EMS; related to Courses.

EMS Field

Colleague Field

SISID

PERSON.ID

InstructorDescription

PERSON.LAST_NAME + PERSON.FIRST_NAME

Terms

EMS View: EMS_vwTerms

View Permissions: Read Only

Colleague Tables: TERMS

Purpose: Used to define list of terms in EMS; related to Courses.  In conjunction with Domains, terms separate courses into distinct lists that will go through the academic planning process (synching, optimizing, publishing, reporting) as a unit.

EMS Field

Colleague Field

SISID

TERMS_ID

TermCode

TERMS_ID

TermDescription

TERM_DESC

StartDate

TERM_START_DATE

EndDate

TERM_END_DATE

Subjects

EMS View: EMS_vwSubjects

View Permissions: Read Only

Colleague Tables: SUBJECTS

Purpose: Used to define list of subjects in EMS. Within EMS, subjects are divided into Domains in order to create distinct lists of courses per term that will go through the academic planning process (synching, optimizing, publishing, reporting) as a unit, managed by the academic central scheduler.

EMS Field

Colleague Field

SISID

SUBJECTS_ID

SubjectCode

SUBJECTS_ID

SubjectDescription

SUBJECTS_ID

CampusCode

NULL

DivisionCode

NULL

DepartmentCode

NULL

LevelCode

NULL

CustomCode1

NULL

CustomCode2

NULL

Course Types

EMS View: EMS_vwCourseTypes

View Permissions: Read Only

Colleague Tables: INSTR_METHODS

Purpose: Used to define the course type list in EMS; related to coursedates. Course Types are used to classify coursedates for purposes of searching, sorting, optimizing, and reporting.

EMS Field

Colleague Field

SISID

INSTR_METHODS_ID

Description

INM_DESC

Courses

EMS View: EMS_vwCourses

View Permissions: Read Only

Colleague Tables: COURSE_SECTIONS, COURSE_SEC_FACULTY, COURSE_SEC_XLISTS, COURSE_SECTIONS_LS

Purpose: Used to define the list of courses per term; related to subjects, instructors, terms.

EMS Field

Colleague Field

SISID

COURSE_SECTIONS_ID

SISTermID

SEC_TERM

SISSubjectID

SEC_SUBJECT

CampusCode

NULL

DepartmentCode

NULL

LevelCode

NULL

CustomCode1

NULL

CustomCode2

NULL

CRN

SEC_SYNONYM

Course

SEC_COURSE_NO

Section

SEC_NO

CourseTitle

SEC_SHORT_TITLE

SISInstructorID

COURSE_SEC_FACULTY.CSF_FACULTY*

EstimatedEnrollment

SEC_CAPACITY

CrosslistParentID

COURSE_SEC_XLISTS.CSXL_PRIMARY_SECTION**

ActualEnrollment

COURSE_SECTIONS_LS.SEC_ACTIVE_STUDENTS***

CreditHours

SEC_MIN_CRED

*First record
**’0’ if not crosslisted
***COUNT of records

Course Dates

EMS View: EMS_vwCourseDates

View Permissions: Update if allowing EMS to update room locations in Colleague

Colleague Tables: COURSE_SEC_MEETING, COURSE_SECTIONS

Purpose: Used to define the list of meeting patterns per course; related to courses, coursetypes, buildings, rooms.

EMS Field

Colleague Field

SISID

COURSE_SEC_MEETING_ID

SISTermID

COURSE_SECTIONS.SEC_TERM

SISCourseID

CSM_COURSE_SECTION

StartDate

CSM_START_DATE

EndDate

CSM_END_DATE

StartTime

CSM_START_TIME

EndTime

CSM_END_TIME

SISBuildingID

CSM_BLDG*

SISRoomID

CSM_BLDG + CSM_ROOM

RoomID

CSM_ROOM*

Sunday

CSM_SUNDAY

Monday

CSM_MONDAY

Tuesday

CSM_TUESDAY

Wednesday

CSM_WEDNESDAY

Thursday

CSM_THURSDAY

Friday

CSM_FRIDAY

Saturday

CSM_SATURDAY

SISCourseTypeID

CSM_INSTR_METHOD

*fields used for updating room assignments