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
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 |
case when mothra_person.studentid is null then 1 else 0 end, |
flags_academic |
null, --PPS |
flags_staff |
case when mothra_person.user_type is 'S' 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 |
null, --!Add to Mothra Person table/feed |
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 |
null, --!SGBSTDN_MAJR_CODE_1 (add column) |
students_degree_code_1 |
null, --!SGBSTDN_DEGC_CODE_1 (add column) |
students_coll_code_1 |
null, --!SGBSTDN_COLL_CODE_1 (add column) |
students_level_code_1 |
banner_level.level_code, |
students_major_code_2 |
null, --!SGBSTDN_MAJR_CODE_2 (add column) |
students_degree_code_2 |
null, --!SGBSTDN_DEGC_CODE_2 (add column) |
students_coll_code_2 |
null, --!SGBSTDN_COLL_CODE_2 (add column) |
students_level_code_2 |
null, --!SGBSTDN_LEVL_CODE_2 (add column) |
students_class_level |
null --!SGVCLSS_CLAS_CODE (new mview) |
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 |
null, --!SCBCRSE_CREDIT_HR_LOW (add column) |
baseinfo_unit_high |
null, --!SCBCRSE_CREDIT_HR_HIGH (add column) |
baseinfo_coll_code |
null, --!SCBCRSE_CREDIT_HR_LOW (add column) |
baseinfo_dept_code |
banner_course.dept_code, |
baseinfo_xlist_flag |
null, --!SCBCRSE_SUBJ_CODE_XLIST (add column) |
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 |