PeopleSoft Views Data Model

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

Enhanced write back feature and stored procedure is currently only available for PeopleSoft Oracle (not SQL).

EMS Software has created a set of views for PeopleSoft to enable the integration between EMS and PeopleSoft. Outlined below are the baseline tables and columns accessed by the views.

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.

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

A stored procedure has been introduced into EMS_SPUPDATECOURSEDATES to provide additional updates to PeopleSoft. These updates and new stored procedure accomplish the following:

  1. Update the PS_CLASS_MTG_PAT record with the room ID.

  2. If an Event ID already exists, update the existing PS_CAMPUS_MTG records.

  3. If an Event ID does not exist:

    1. Update the PS_EVENT_LAST_TBL table – increment by one.

    2. Retrieve the EVENT_NBR_LAST value from the PS_EVENT_LAST_TBL table.

    3. Update the PS_CLASS_TBL record with the Event ID.

    4. If it’s a combined section then update the related combined sections in PS_CLASS_TBL with the Event ID so long as the SKIP_MTGPAT_EDIT flag is set to ‘No.’

  4. Create PS_CAMPUS_EVENT record if it doesn’t exist for the Event ID.

  5. Create PS_CAMPUS_MTG records if they don’t exist for the Event ID and Meeting Number.

The Oracle account used for the integration with EMS will need to have write access to the following tables, if it does not already:

  • PS_CLASS_MTG_PAT

  • PS_EVENT_LAST_TBL

  • PS_CLASS_TBL

  • PS_CAMPUS_EVENT

  • PS_CAMPUS_MTG

  • PS_FACILITY_TBL

View Modifications

Fields highlighted in BLUE are required to be unique.

Buildings

EMS View – EMS_vwBuildings

View Permissions – Read Only

PeopleSoft Tables – PS_BLDG_TBL

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

PeopleSoft Field

SISID

BLDG_CD

BuildingCode

BLDG_CD

BuildingDescription

DESCR

Room Types

EMS View – EMS_vwRoomTypes

View Permissions – Read Only

PeopleSoft Tables – PSXLATITEM (‘FACILITY_TYPE’)

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

PeopleSoft Field

SISID

FIELDVALUE

RoomTypeDescription

XLATLONGNAME

Rooms

EMS View – EMS_vwRooms

View Permissions – Read Only

PeopleSoft Tables – PS_FACILITY_TBL, PS_BLDG_TBL, PSXLATITEM

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

PeopleSoft Field

SISID

FACILITY_ID

SISBuildingID

BLDG_CD

SISRoomTypeID

FACILITY_TYPE

RoomCode

ROOM

RoomDescription

DESCR

Capacity

ROOM_CAPACITY

Instructors

EMS View –  EMS_vwInstructors

View Permissions – Read Only

PeopleSoft Tables – PS_INSTR_ADVISOR, PS_NAMES

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

EMS Field

PeopleSoft Field

SISID

EMPLID

InstructorDescription

PS_NAMES.NAME

Terms

EMS View – EMS_vwTerms

View Permissions – Read Only

PeopleSoft Tables – PS_TERM_TBL

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

PeopleSoft Field

SISID

STRM

TermCode

STRM

TermDescription

DESCR

StartDate

TERM_BEGIN_DT

EndDate

TERM_END_DT

Subjects

EMS View –  EMS_vwSubjects

View Permissions – Read Only

PeopleSoft Tables – PS_SUBJECT_TBL, PS_CLASS_TBL (Distinct values)

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

PeopleSoft Field

SISID

SUBJECT

SubjectCode

SUBJECT

SubjectDescription

DESCR

CampusCode

PS_CLASS_TBL.LOCATION*

DivisionCode

NULL

DepartmentCode

NULL

LevelCode

NULL

CustomCode1

NULL

CustomCode2

NULL

*NULL if not using Campus Codes

Course Types

EMS View – EMS_vwCourseTypes

View Permissions – Read Only

PeopleSoft Tables – PSXLATITEM (‘SSR_COMPONENT’)

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

PeopleSoft Field

SISID

FIELDVALUE

Description

XLATLONGNAME

Courses

EMS View – EMS_vwCourses

View Permissions – Read Only

PeopleSoft Tables – PS_CLASS_TBL, PS_CRSE_CATALOG, PS_CLASS_INSTR, PS_SCTN_CMBND

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

EMS Field

PeopleSoft Field

SISID

STRM + CLASS_NBR

SISTermID

STRM

SISSubjectID

SUBJECT

CampusCode

LOCATION*

DepartmentCode

NULL

LevelCode

NULL

CustomCode1

NULL

CustomCode2

NULL

CRN

CLASS_NBR

Course

CATALOG_NBR

Section

CLASS_SECTION

CourseTitle

DESCR

SISInstructorID

PS_CLASS_INSTR.EMPLID**

EstimatedEnrollment

ENRL_CAP

CrosslistParentID

PS_SCTN_CMBND.STRM + PS_SCTN_CMBND.CLASS_NBR***

ActualEnrollment

ENRL_TOT

CreditHours

PS_CRSE_CATALOG.UNITS_MINIMUM

*NULL if not using Campus Codes
**First record
***’0’ if not crosslisted, otherwise matching on SCTN_COMBINED_ID

Course Dates

EMS View – EMS_vwCourseDates

View Permissions – Update if allowing EMS to update room locations in PeopleSoft

PeopleSoft Tables – PS_CLASS_MTG_PAT, PS_CLASS_TBL, PS_FACILITY_TBL

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

EMS Field

PeopleSoft Field

SISID

SQL –  STRM + CLASS_NBR + Row Number
Oracle –  STRM + CRSE_OFFER_NBR + SESSION_CODE + CLASS_SECTION + CLASS_MTG_NBR

SISTermID

STRM

SISCourseID

STRM + CLASS_NBR

StartDate

START_DT

EndDate

END_DT

StartTime

MEETING_TIME_START

EndTime

MEETING_TIME_END

SISBuildingID

BLDG_ID

SISRoomID

FACILITY_ID*

Sunday

SUN

Monday

MON

Tuesday

TUES

Wednesday

WED

Thursday

THURS

Friday

FRI

Saturday

SAT

SISCourseTypeID

SSR_COMPONENT

*Field used for updating room assignments