SOM External Database
- Brian Donnelly
School of Medicine External Database
Implementation Plan
- SOM DB Schema
A new schema will be created on the development sakai database called 'SOM'. - SVM View Creation
View ddl for each table in the aaud, courses, and dictionary databases will be created and executed on the SOM schema. This will create a set of views that mirror the columns in the SVM tables. These views can be queried in the same fashion as a table. The data for view columnds will be pulled from the existing materialized views in the external database. Unmappable values and columns requiring revision of the materialized views will be left blank. - Degree Navigator Feed
An account on the Degree Navigator system must be created for the external database. This account will be used to create a database link to degree navigator. Once created, an existing pl/sql feed procedure will be copied from the Course Management system to the external database. This feed procedure will copy data from Degree Navigator nightly into a local table on the external database. - Materialized View Revision
The existing External Database materialized view and view log ddl will be revised to include the new columns that are needed. Any new materialized views will be defined as well as their accompanying view logs. - Creation of Banner View Logs
The materialized view log ddl will be submitted to the Banner code migration team for analysis and deployment to the banner development and test environments. - Materialized View Creation
Once the view logs are created on the banner environments, the materialized view scripts will be run on our development environment. At this point the materialized views in the external database will contain the new data. - SVM View Revision
The view ddl for the SOM views will be revised to include the newly acquired data. The resulting views will have data for all columns that can be filled with data from the external database. - Project Completion
SVM View Mappings
The following tables will describe each view that will be created in the School of Medicine schema. Each column in the original SVM table will be listed along with the value to be mapped or the mapping status. A key is presented at the end of the section listing the various color codings.
AAUD.EMPLOYEES / AAUD_EMPLOYEES |
|
|---|---|
emp_pkey |
mothra_person.mothraid, |
emp_term_code |
null, --only latest data is recorded in external database |
emp_clientid |
nvl(mothra_person.studentid,mothra_person.employeeid), |
emp_home_dept |
mothra_person.dept_code, |
emp_alt_dept_code |
null, --PPS |
emp_school_division |
null, --PPS |
emp_cbuc |
null, --PPS |
emp_status |
null, --PPS |
emp_primary_title |
null, --PPS |
emp_teaching_title_code |
null, --PPS |
emp_teaching_home_dept |
null, --SVM specific field from dictionary table |
emp_teaching_percent_fulltime |
null, --Derived from SVM specific data |
emp_effort_title_code |
null, --Derived from SVM specific data |
emp_effort_home_dept |
null --Derived from SVM specific data |
AAUD.FLAGS / AAUD_FLAGS |
|
|---|---|
flags_pkey |
mothra_person.mothraid, |
flags_term_code |
null, --only latest data is recorded in external database |
flags_clientid |
nvl(mothra_person.studentid,mothra_person.employeeid), |
flags_student |
when mothra_person.user_type IN ('U','R','P','C') then 1 else 0 end, |
flags_academic |
null, --PPS (maybe when mothra_person.user_type IN ('F','M','P','C') then 1 else 0 end) |
flags_staff |
case when mothra_person.user_type IN ('S','R','M','C') then 1 else 0 end |
flags_teaching_faculty |
case when mothra_person.teaching_flag is 'Y' then 1 else 0 end, |
flags_wosemp |
null, --PPS |
flags_confidential |
case when mothra_person.last is 'CONFIDENTIAL' then 1 else 0 end, |
flags_svm_people |
null, --SVM specific field |
flags_svm_student |
null --SVM specific field |
AAUD.IDS / AAUD_IDS |
|
|---|---|
ids_pkey |
mothra_person.mothraid, |
ids_term_code |
null, --only latest data is recorded in external database |
ids_clientid |
nvl(mothra_person.studentid,mothra_person.employeeid), |
ids_mothraid |
mothra_person.mothraid, |
ids_logind |
mothra_person.kerberos_name, |
ids_mailid |
mothra_person.email, |
ids_spriden_id |
DEFERRED: banner_id.spriden_id |
ids_pidm |
mothra_person.banner_pidm, |
ids_employee_id |
mothra_person.employeeid, |
ids_vmacs_id |
null --SVM specific field |
AAUD.PERSON / AAUD_PERSON |
|
|---|---|
person_pkey |
mothra_person.mothraid, |
person_term_code |
null, --only latest data is recorded in external database |
person_clientid |
nvl(mothra_person.studentid,mothra_person.employeeid), |
person_last_name |
mothra_person.last, |
person_first_name |
mothra_person.first, |
person_middle_name |
mothra_person.middle, |
person_display_last_name |
mothra_person.last, |
person_display_first_name |
mothra_person.first, |
person_display_middle_name |
mothra_person.middle, |
person_display_full_name |
mothra_person.last||', '||mothra_person.first||' '||mothra_person.middle |
AAUD.STUDENTS / AAUD_STUDENTS |
|
|---|---|
students_pkey |
mothra_person.mothraid||banner_level.term_code, |
students_term_code |
banner_level.term_code, |
students_clientid |
nvl(mothra_person.studentid,mothra_person.employeeid), |
students_major_code_1 |
banner_level.major_code_1 |
students_degree_code_1 |
banner_level.degree_code_1 |
students_coll_code_1 |
banner_level.college_code_1 |
students_level_code_1 |
banner_level.level_code, |
students_major_code_2 |
banner_level.major_code_2 |
students_degree_code_2 |
banner_level.degree_code_2 |
students_coll_code_2 |
banner_level.college_code_2 |
students_level_code_2 |
banner_level.level_code_2 |
students_class_level |
DEFERRED: null --!SGVCLSS_CLAS_CODE (analysis required) |
COURSES.BASEINFO / COURSES_BASEINFO |
|
|---|---|
baseinfo_pkey |
banner_section.term_code||banner_section.crn, |
baseinfo_term_code |
banner_section.term_code, |
baseinfo_crn |
banner_section.crn, |
baseinfo_subj_code |
banner_section.subj_code, |
baseinfo_crse_numb |
banner_section.crse_numb, |
baseinfo_seq_numb |
banner_section.sec_numb, |
baseinfo_title |
banner_course.crse_title, |
baseinfo_enrollment |
banner_section.enrollment, |
baseinfo_unit_type |
case when banner_course.credit_hours_variable is 'Y' then 'V' else 'F' end, |
baseinfo_unit_low |
banner_course.credit_hours_low |
baseinfo_unit_high |
banner_course.credit_hours_high |
baseinfo_coll_code |
banner_course.college_code |
baseinfo_dept_code |
banner_course.dept_code, |
baseinfo_xlist_flag |
case when banner_course.crosslist_subj_code is null then 1 else 0 end |
baseinfo_xlist_group |
null --!SSRXLIST_XLST_GROUP (new mview) |
COURSES.CATALOG / COURSES_CATALOG |
|
|---|---|
catalog_pkey |
banner_section.term_code||banner_section.crn, |
catalog_term_code |
banner_section.term_code, |
catalog_subj_code |
banner_section.subj_code, |
catalog_crse_numb |
banner_section.crse_numb, |
catalog_origin_dept |
null, --CAF |
catalog_desc_title |
null, --CAF |
catalog_course_desc |
null, --CAF |
catalog_quarter_offered |
null, --CAF |
catalog_units |
null, --CAF |
catalog_prerequisite |
null, --CAF |
catalog_repeat_credit |
null, --CAF |
catalog_repeat_times |
null, --CAF |
catalog_remarks |
null, --CAF |
catalog_grading |
null, --CAF |
catalog_cafmain_id |
null, --CAF |
catalog_cafmain_cafecp |
null --CAF |
COURSES.FACULTY / COURSES_FACULTY |
|
|---|---|
faculty_course_id |
banner_teaching.term_code||banner_teaching.crn, |
faculty_pidm |
banner_teaching.pidm, |
faculty_clientid |
nvl(mothra_person.studentid,mothra_person.employeeid), |
faculty_term_code |
banner_teaching.term_code, |
faculty_role |
null --SVM specific field from iLios |
COURSES.POA / COURSES_POA |
|
|---|---|
poa_pkey |
banner_teaching.term_code||banner_teaching.crn||banner_teaching.pidm, |
poa_term_code |
banner_teaching.term_code, |
poa_crn |
banner_teaching.crn, |
poa_pidm |
banner_teaching.pidm, |
poa_role |
case when banner_teaching.ior_status is not null then 1 else 0 end |
COURSES.ROSTER / COURSES_ROSTER |
|
|---|---|
roster_pkey |
banner_rosterterm_code||banner_roster.crn||banner_roster.pidm, |
roster_term_code |
banner_roster.term_code, |
roster_crn |
banner_roster.crn, |
roster_pidm |
banner_roster.pidm, |
roster_level_code |
banner_roster.level_code, |
roster_enroll_status |
banner_roster.reg_status, |
roster_grade_mode |
banner_roster.grade_mode, |
roster_unit |
banner_roster.credit_hr |
COURSES.TERMINFO / COURSES_TERMINFO |
|
|---|---|
term_academic_year |
case when banner_term.term_id >= 9 then banner_term.term_yr + 1 else banner_term.term_yr end, |
term_code |
banner_term.term_code, |
term_desc |
banner_term.term_desc, |
term_coll_code |
null, --Not in current use |
term_start_date |
banner_term.start_date, |
term_end_date |
banner_term.end_date, |
term_current_term |
case when sysdate between banner_term.start_date and banner_term.end_date then 1 else 0 end, |
term_academic_year_description |
case when banner_term.term_id >= 9 then banner_term.term_yr||banner_term.term_yr+1 else banner_term.term_yr-1||banner_term.term_yr end |
Key |
|
|---|---|
Style |
Description |
Plain Text |
Currently mapped data |
Green Text |
Data that must be added to an existing MView before being mapped |
Blue Text |
Data requiring a new MView before being mapped |
Bold Text |
Data that does not exist in the external database and will not be mapped |