...
- 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 | case when mothra_person.studentid is null 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 is 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 null, --!Add to Mothra Person table/feed | 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.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) | banner_level.major_code_1 |
students_degree_code_1 null, --!SGBSTDN_DEGC_CODE_1 (add column) | banner_level.degree_code_1 |
students_coll_code_1 null, --!SGBSTDN_COLL_CODE_1 (add column) | banner_level.college_code_1 |
students_level_code_1 | banner_level.level_code, |
students_major_code_2 null, --!SGBSTDN_MAJR_CODE_2 (add column) | banner_level.major_code_2 |
students_degree_code_2 null, --!SGBSTDN_DEGC_CODE_2 (add column) | banner_level.degree_code_2 |
students_coll_code_2 null, --!SGBSTDN_COLL_CODE_2 (add column) | banner_level.college_code_2 |
students_level_code_2 null, --!SGBSTDN_LEVL_CODE_2 (add column) | banner_level.level_code_2 |
students_class_level | DEFERRED: null --!SGVCLSS_CLAS_CODE (new mviewanalysis 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 null, --!SCBCRSE_CREDIT_HR_LOW (add column) | banner_course.credit_hours_low |
baseinfo_unit_high null, --!SCBCRSE_CREDIT_HR_HIGH (add column) | banner_course.credit_hours_high |
baseinfo_coll_code null, --!SCBCRSE_CREDIT_HR_LOW (add column) | banner_course.college_code |
baseinfo_dept_code | banner_course.dept_code, |
baseinfo_xlist_flag null, --!SCBCRSE_SUBJ_CODE_XLIST (add column) | case when banner_course.crosslist_subj_code is null then 1 else 0 end |
baseinfo_xlist_group | null --!SSRXLIST_XLST_GROUP (new mview) |
...