Colleague Unidata Views Data Model
Now supported for legacy customers only.
EMS Software will code a set of views specific to your organization’s use of Unidata to allow for integration between EMS and Unidata. Outlined are the baseline tables/columns accessed in the views.
EMS Software has created a set of views for
Your Implementation Consultant can further assist you with the use of information in EMS supporting the Academic Scheduling process.
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.
Learn more about:
To build the EMS views, the following are required:
-
The VSG (Visual Schema Generator), an executable that is shipped with Unidata, must be installed on a computer that can access Unidata. If you cannot locate the .msi installer for this tool, EMS Software can provide a copy.
-
The purpose of the VSG is to create SQL compliant dictionary files based on the dictionary files that contain data that EMS requires. Once SQL compliant dictionaries are created by the VSG the final EMS views can be created and permissions granted via the VSG.
-
-
The 32-bit IBM Unidata ODBC driver must be installed in order for the VSG to connect to Unidata. If you cannot locate this installer, EMS Software can provide a copy.
Modification of SQL compliant dictionaries and views by the customer is unsupported and voids the Annual Service Agreement for the integration with Colleague Unidata.
Fields highlighted in BLUE are required to be unique.
New Unidata Dictionary Legend:
- BLACK – Fields generated from VSG – no AE Editor changes required.
- GREEN – New fields that must be added via the AE Editor.
- RED – Required changes via the AE Editor.
Buildings
EMS View – EMS_vwBuildings
View Permissions – Read Only
Unidata Dictionary – BUILDINGS
New Dictionary – BUILDINGS_EMS
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
ASSOC |
|---|---|---|---|---|---|---|---|
|
BLDG_DESC |
D |
1 |
|
Description |
30L |
S |
|
|
ID |
D |
0 |
|
BUILDINGS |
10L |
S |
|
|
ID0 |
D |
0 |
|
BUILDINGS_EMS |
10L |
S |
|
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 |
BUILDINGS_EMS Field |
|---|---|
|
SISID |
ID |
|
BuildingCode |
ID |
|
BuildingDescription |
BLDG_DESC |
Room Types
EMS View – EMS_vwRoomTypes
View Permissions – Read Only
Unidata Dictionary – ROOM.TYPES
New Dictionary – ROOM_TYPES_EMS
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
ASSOC |
|---|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
ROOM.TYPES |
10L |
S |
|
|
ID0 |
D |
0 |
|
ROOM_TYPES_EMS |
10L |
S |
|
|
RMTP_DESCRIPTION |
D |
1 |
|
Description |
30L |
S |
|
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 |
ROOM_TYPES_EMS Field |
|---|---|
|
SISID |
ID |
|
RoomTypeDescription |
RMTP_DESCRIPTION |
Rooms
EMS View – EMS_vwRooms
View Permissions – Read Only
Unidata Dictionary – ROOMS
New Dictionary – ROOMS_EMS (Requires modifications via AE Editor)
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
ROOMS |
10L |
S |
|
ID0 |
D |
0 |
|
ROOMS_EMS |
10L |
S |
|
ROOM_NAME |
D |
1 |
|
Description |
30L |
S |
|
ROOM_CAPACITY |
D |
2 |
MD0, |
Capacity |
7R |
S |
|
ROOM_TYPE |
D |
4 |
|
Room Type |
20L |
S |
|
ROOMS_BLDG_ID |
I |
FIELD(@ID,”*”,1) |
|
Building |
4L |
S |
|
ROOM_CAPACITY_1 |
I |
IF ROOM_CAPACITY=’’ THEN ‘30’ ELSE ROOM_CAPACITY |
|
|
7R |
S |
|
ROOM_CODE |
I |
FIELD(@ID,”*”,2) |
|
|
30L |
S |
|
ROOM_TYPE_1 |
I |
IF ROOM_TYPE = ‘’ THEN ‘0’ ELSE ROOM_TYPE |
|
|
20L |
S |
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 |
ROOMS_EMS Field |
|---|---|
|
SISID |
ID |
|
SISBuildingID |
ROOMS_BLDG_ID |
|
SISRoomTypeID |
ROOM_TYPE_1 |
|
RoomCode |
ROOM_CODE |
|
RoomDescription |
ROOM_NAME |
|
Capacity |
ROOM_CAPACITY_1 |
Instructors
EMS View – EMS_vwInstructors
View Permissions – Read Only
Unidata Dictionary – FACULTY
New Dictionary – FACULTY_EMS (Requires modifications via AE Editor)
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
FACULTY |
10L |
S |
|
ID0 |
D |
0 |
|
FACULTY_EMS |
10L |
S |
|
FAC_FIRST_NAME |
I |
TRANS(“PERSON”,@ID,”FIRST.NAME”,”X”) |
|
Faculty First Name |
35L |
S |
|
FAC_FULL_NAME |
I |
FAC_LAST_NAME:’, ‘:FAC_FIRST_NAME |
|
|
35L |
S |
|
FAC_LAST_NAME |
I |
TRANS(“PERSON”,@ID,”LAST.NAME”,”X”) |
|
Faculty Last Name |
35L |
S |
Purpose – Used to define list of instructors in EMS; related to Courses.
|
EMS Field |
FACULTY_EMS Field |
|---|---|
|
SISID |
ID |
|
InstructorDescription |
FAC_FULL_NAME |
Terms
EMS View – EMS_vwTerms
View Permissions – Read Only
Unidata Dictionary – TERMS
New Dictionary – TERMS_EMS (Requires modifications via AE Editor)
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
ASSOC |
|---|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
TERMS |
10L |
S |
|
|
ID0 |
D |
0 |
|
TERMS_EMS |
10L |
S |
|
|
TERM_DESC |
D |
2 |
|
Description |
30L |
S |
|
|
TERM_START_DATE |
D |
5 |
D2/MDY |
Start Date |
8R |
S |
|
|
TERM_END_DATE |
D |
6 |
D2/MDY |
End Date |
8R |
S |
|
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 |
TERMS_EMS Field |
|---|---|
|
SISID |
ID |
|
TermCode |
ID |
|
TermDescription |
TERM_DESC |
|
StartDate |
TERM_START_DATE |
|
EndDate |
TERM_END_DATE |
Subjects (no Campus Codes)
EMS View – EMS_vwSubjects
View Permissions – Read Only
Unidata Dictionary – SUBJECTS
New Dictionary– SUBJECTS_EMS (Requires modifications via AE Editor)
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
SUBJECTS |
10L |
S |
|
ID0 |
D |
0 |
|
SUBJECTS_EMS |
10L |
S |
|
SUBJ_DESC |
D |
1 |
|
Description |
30L |
S |
|
CAMPUS_CODE |
I |
‘’ |
|
|
30L |
S |
|
DIVISION_CODE |
I |
‘’ |
|
|
30L |
S |
|
DEPT_CODE |
I |
‘’ |
|
|
30L |
S |
|
LEVEL_CODE |
I |
‘’ |
|
|
30L |
S |
|
CUSTOMCODE1 |
I |
‘’ |
|
|
30L |
S |
|
CUSTOMCODE2 |
I |
‘’ |
|
|
30L |
S |
Purpose – Used to define list of subjects in EMS. Within EMS, subjects are divided into Domains 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 |
SUBJECTS_EMS Field |
|---|---|
|
SISID |
ID |
|
SubjectCode |
ID |
|
SubjectDescription |
SUBJ_DESC |
|
CampusCode |
NULL |
|
DivisionCode |
NULL |
|
DepartmentCode |
NULL |
|
LevelCode |
NULL |
|
CustomCode1 |
NULL |
|
CustomCode2 |
NULL |
Subjects (with Campus Codes)
EMS View – EMS_vwSubjects
Unidata Dictionary – COURSE.SECTIONS
New Dictionary – COURSE_SECTIONS_EMS (See Courses Below)
Course Types
EMS View– EMS_vwCourseTypes
View Permissions – Read Only
Unidata Dictionary – INSTR.METHODS
New Dictionary – INSTR_METHODS_EMS
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
ASSOC |
|---|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
INSTR.METHODS |
10L |
S |
|
|
ID0 |
D |
0 |
|
INSTR_METHODS_EMS |
10L |
S |
|
|
INM_DESC |
D |
1 |
|
Description |
30L |
S |
|
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 |
INSTR_METHODS_EMS Field |
|---|---|
|
SISID |
ID |
|
Description |
INM_DESC |
Courses
EMS View – EMS_vwCourses
View Permissions – Read Only
Unidata Dictionary – COURSE.SECTIONS
New Dictionary – COURSE_SECTIONS_EMS
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
COURSE.SECTIONS |
21L |
S |
|
ID0 |
D |
0 |
|
COURSE_SECTIONS_EMS |
21L |
S |
|
SEC_TERM |
D |
15 |
|
Term |
35L |
S |
|
SEC_SUBJECT |
D |
6 |
|
Subject |
40L |
S |
|
SEC_COURSE_NO |
D |
17 |
|
Course Number |
7L |
S |
|
SEC_NO |
D |
18 |
|
Section |
5L |
S |
|
SEC_SHORT_TITLE |
D |
1 |
|
Short Title |
30L |
S |
|
SEC_FACULTY |
D |
40 |
|
Faculty |
10L |
S |
|
SEC_CAPACITY |
D |
5 |
MD0, |
Capacity |
5R |
S |
|
SEC_MIN_CRED |
D |
8 |
MD25 |
Min Cred |
8R |
S |
|
SEC_XLIST |
D |
32 |
|
XList |
10L |
S |
|
CAMPUS_CODE |
I |
‘’ |
|
(This record required when NOT using Campus Codes. Otherwise do not include this record.) |
40L |
S |
|
DIVISION_CODE |
I |
‘’ |
|
|
40L |
S |
|
DEPT_CODE |
I |
‘’ |
|
|
40L |
S |
|
LEVEL_CODE |
I |
‘’ |
|
|
40L |
S |
|
CUSTOMCODE1 |
I |
‘’ |
|
|
40L |
S |
|
CUSTOMCODE2 |
I |
‘’ |
|
|
40L |
S |
|
SEC_ACTIVE_STUDENT_COUNT |
I |
IF SEC_ACTIVE_STUDENTS AND SEC_ACTIVE_STUDENTS NE "0" THEN (((COUNT(SEC_ACT IVE_STUDENTS,@VM)) + (1))) ELSE (0) |
MD0, |
Active Student Count |
6R |
S |
|
CSXL_PRIMARY_SECTION |
I |
TRANS(COURSE.SEC.XLISTS,SEC_XLIST,’CSXL.PRIMARY.SECTION’,’X’);IF @=ID THEN ‘0’ ELSE IF @=’’ THEN ‘0’ ELSE @ |
|
Primary Section |
21L |
S |
|
SEC_FIRST_FACULTY |
I |
TRANS(“COURSE.SEC.FACULTY”, SEC_FACULTY, “CSF.FACULTY”,”X1”);IF @=’’ THEN ‘0’ ELSE @ |
|
Faculty |
10L |
S |
|
SEC_CURRENT_STATUS |
I |
FIELD(SEC_STATUS,@VM,1) |
|
Current Status |
5L |
S |
|
SEC_STATUS |
D |
88 |
|
Status |
30L |
S |
|
SEC_SYNONYM |
D |
61 |
|
Synonym |
11L |
S |
|
SEC_ACTIVE_STUDENTS |
D |
66 |
|
|
6R |
S |
|
SEC_SUBJ_DESC |
I |
TRANS(“SUBJECTS”, SEC_SUBJECT, “SUBJ.DESC”, “X1”) |
|
Subject Description (This record required when using Campus Codes) |
30L |
S |
|
SEC_LOCATION |
D |
6 |
|
Location (This record required when using Campus Codes) |
40L |
S |
Purpose – Used to define the list of courses per term; related to subjects, instructors, terms.
|
EMS Field |
COURSE_SECTIONS_EMS Field |
|---|---|
|
SISID |
ID |
|
SISTermID |
SEC_TERM |
|
SISSubjectID |
SEC_SUBJECT |
|
CampusCode |
NULL (or SEC_LOCATION if using Campus Codes) |
|
DepartmentCode |
NULL |
|
LevelCode |
NULL |
|
CustomCode1 |
NULL |
|
CustomCode2 |
NULL |
|
CRN |
SEC_SYNONYM |
|
Course |
SEC_COURSE |
|
Section |
SEC_NO |
|
CourseTitle |
SEC_SHORT_TITLE |
|
SISInstructorID |
SEC_FIRST_FACULTY |
|
EstimatedEnrollment |
SEC_CAPACITY |
|
CrosslistParentID |
CSXL_PRIMARY_SECTION |
|
ActualEnrollment |
SEC_ACTIVE_STUDENT_COUNT |
|
CreditHours |
SEC_MIN_CRED |
Subjects with Campus Codes (distinct values)
|
EMS Field |
COURSE_SECTIONS_EMS Field |
|---|---|
|
SISID |
SEC_SUBJECT |
|
SubjectCode |
SEC_SUBJECT |
|
SubjectDescription |
SEC_LOCATION |
|
CampusCode |
NULL |
|
DivisionCode |
NULL |
|
DepartmentCode |
NULL |
|
LevelCode |
NULL |
|
CustomCode1 |
NULL |
|
CustomCode2 |
NULL |
Course Dates
EMS View– EMS_vwCourseDates
View Permissions – Read Only
Unidata Dictionary – COUSRE.SEC.MEETING
New Dictionary – COURSE_SEC_MEETING_EMS
New Dictionary Permissions – Update if allowing EMS to update room locations in Unidata
|
@ID |
TYPE |
LOC |
CONV |
NAME |
FORMAT |
SM |
ASSOC |
|---|---|---|---|---|---|---|---|
|
ID |
D |
0 |
|
COURSE.SEC.MEETING |
10L |
S |
|
|
ID0 |
D |
0 |
|
COURSE_SEC_MEETING_EMS |
10L |
S |
|
|
CSM_SEC_TERM |
I |
TRANS(“COURSE_SECTIONS_EMS”,CSM_COURSE_SECTION,”SEC_TERM”,”X”) |
|
Term |
7L |
S |
|
|
CSM_COURSE_SECTION |
D |
1 |
|
Section |
21L |
S |
|
|
CSM_START_DATE |
D |
2 |
D2/MDY |
Start Date |
8R |
S |
|
|
CSM_END_DATE |
D |
3 |
D2/MDY |
End Date |
8R |
S |
|
|
CSM_START_TIME |
D |
8 |
MT |
Start Time |
8R |
S |
|
|
CSM_END_TIME |
D |
9 |
MT |
End Time |
8R |
S |
|
|
CSM_BLDG* |
D |
4 |
|
Building |
35L |
S |
|
|
CSM_ROOM* |
D |
5 |
|
Room |
35L |
S |
|
|
CSM_SUNDAY |
D |
20 |
|
(this must be blank) |
1L |
S |
|
|
CSM_MONDAY |
D |
14 |
|
(this must be blank) |
1L |
S |
|
|
CSM_TUESDAY |
D |
15 |
|
(this must be blank) |
1L |
S |
|
|
CSM_WEDNESDAY |
D |
16 |
|
(this must be blank) |
1L |
S |
|
|
CSM_THURSDAY |
D |
17 |
|
(this must be blank) |
1L |
S |
|
|
CSM_FRIDAY |
D |
18 |
|
(this must be blank) |
1L |
S |
|
|
CSM_SATURDAY |
D |
19 |
|
(this must be blank) |
1L |
S |
|
|
CSM_INSTR_METHOD |
D |
6 |
|
Instr Method |
35L |
S |
|
|
ROOM_ID |
I |
IF CSM_ROOM=’’ THEN ‘’ ELSE CSM_BLDG:’*’:CSM_ROOM |
|
|
35L |
S |
|
|
SUNDAY |
I |
IF CSM_SUNDAY=’Y’ THEN ‘1’ ELSE ‘0’ |
|
Sunday (this is required) |
1L |
S |
|
|
MONDAY |
I |
IF CSM_MONDAY=’Y’ THEN ‘1’ ELSE ‘0’ |
|
Monday (this is required) |
1L |
S |
|
|
TUESDAY |
I |
IF CSM_TUESDAY=’Y’ THEN ‘1’ ELSE ‘0’ |
|
Tuesday (this is required) |
1L |
S |
|
|
WEDNESDAY |
I |
IF CSM_WEDNESDAY=’Y’ THEN ‘1’ ELSE ‘0’ |
|
Wednesday (this is required) |
1L |
S |
|
|
THURSDAY |
I |
IF CSM_THURSDAY=’Y’ THEN ‘1’ ELSE ‘0’ |
|
Thursday (this is required) |
1L |
S |
|
|
FRIDAY |
I |
IF CSM_FRIDAY=’Y’ THEN ‘1’ ELSE ‘0’ |
|
Friday (this is required) |
1L |
S |
|
|
SATURDAY |
I |
IF CSM_SATURDAY=’Y’ THEN ‘1’ ELSE ‘0’ |
|
Saturday (this is required) |
1L |
S |
|
*Field used for updating room assignments
Purpose – Used to define the list of meeting patterns per course; related to courses, course types, buildings, rooms.
|
EMS Field |
COURSE_SEC_MEETING_EMS Field |
|---|---|
|
SISID |
ID |
|
SISTermID |
CSM_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 |
ROOM_ID |
|
RoomID |
CSM_ROOM |
|
Sunday |
SUNDAY |
|
Monday |
MONDY |
|
Tuesday |
TUESDAY |
|
Wednesday |
WEDNESDAY |
|
Thursday |
THURSDAY |
|
Friday |
FRIDAY |
|
Saturday |
SATURDAY |
|
SISCourseTypeID |
CSM_INSTR_METHOD |