Overview
Phase 2 will introduced some enhance calculation features. The scope of which is defined as subtasks of
MAP Plan Status Calculation Implementation Details
Map Plan Status Schema
Map Status Data Dictionary
MAP_STATUS_REPORT (Process will produce one row per active plan assigned to an active student.)
| Column | Type | Notes |
|---|---|---|
| id | uuid | primary (technical) key, unique identifier |
| plan_status | varchar(enum) | originally proposed as a boolean, I think an enum here will give us more flexability |
| note | varchar | high level detail about the status calculation |
| plan_ratio | decimal(2,2) | the percentage of plan courses satisfied as 'on plan' |
| plan_id | uuid | foreign key to MAP_PLAN(id) |
| person_id | uuid | foreign key to PERSON(id) - student |
| modified_by | uuid | id of person modifying reference |
| created_by | uuid | id of person creating reference |
| modified_date | date | date modified |
| created_date | date | date created |
| object_status | enum | standard object status |
MAP_STATUS_REPORT_COURSE_DETAILS (Process will produce one row per anomaly per active plan. An errorless plan will produce zero rows.)
| Column | Type | Notes |
|---|---|---|
| id | uuid | primary (technical) key, unique identifier |
| report_id | uuid | foreign key to MAP_STATUS_REPORT(id) |
| term_code | varchar | soft reference to EXTERNAL_TERM(code) |
| formatted_course | varchar | soft reference to EXTERNAL_COURSE(formatted_course) |
| course_code | uuid | soft reference to EXTERNAL_COURSE(code) |
| anomaly_code | varchar | code that identifies the specific course level violation |
| anomaly_note | varchar | detailed information about specific course level violation |
| modified_by | uuid | id of person modifying reference |
| created_by | uuid | id of person creating reference |
| modified_date | date | date modified |
| created_date | date | date created |
| object_status | enum | standard object status |
MAP_STATUS_REPORT_TERM_DETAILS (Process will produce one row per plan_course_term in scope per active plan.)
| Column | Type | Notes |
|---|---|---|
| id | uuid | primary (technical) key, unique identifier |
| report_id | uuid | foreign key to MAP_STATUS_REPORT(id) |
| term_code | varchar | soft reference to EXTERNAL_TERM(code) |
| anomaly_code | varchar | code that identifies the specific course level violation |
| anomaly_note | varchar | detailed information about specific course level violation |
| term_status | varchar(enum) | the on/off plan status of the term |
| term_ratio | decimal(2,2) | percent of term courses satisfied as being 'on plan' |
| modified_by | uuid | id of person modifying reference |
| created_by | uuid | id of person creating reference |
| modified_date | date | date modified |
| created_date | date | date created |
| object_status | enum | standard object status |
MAP_STATUS_REPORT_SUBSTITUTION_DETAILS (Process will produce one row per substitution per active plan. An substitution-less plan will produce zero rows.)
| Column | Type | Notes |
|---|---|---|
| id | uuid | primary (technical) key, unique identifier |
| report_id | uuid | foreign key to MAP_STATUS_REPORT(id) |
| term_code | varchar | soft reference to EXTERNAL_TERM(code) Plan course term code being substituted |
| formatted_course | varchar | soft reference to EXTERNAL_COURSE(formatted_course) Plan course formatted_course being substituted |
| course_code | uuid | soft reference to EXTERNAL_COURSE(code) Plan course course_code being substituted |
| substitution_code | varchar | code that identifies the specific course level violation |
| substitution_note | varchar | detailed information about specific course level violation |
| substituted_term_code | varchar | soft reference to EXTERNAL_TERM(code) the substituted term code |
| substituted_formatted_course | varchar | soft reference to EXTERNAL_COURSE(formatted_course) the substituted formatted_course |
| substituted_course_code | uuid | soft reference to EXTERNAL_COURSE(code) the substituted course_code |
| modified_by | uuid | id of person modifying reference |
| created_by | uuid | id of person creating reference |
| modified_date | date | date modified |
| created_date | date | date created |
| object_status | enum | standard object status |
Integration
EXTERNAL_SUBSTITUTABLE_COURSE (Used to define course substitutions for plan calculation)
| Column | Type | Notes |
|---|---|---|
| term_code | varchar | term code for the substitution. if null applies to all term. |
| program_code | uuid | program code for the substitution. if null applies to all programs |
| source_formatted_course | varchar | formatted course, matched to the plan course |
| source_course_code | varchar | course course, matched to the plan course |
| source_course_title | varchar | course title, matched to the plan course |
| target_formatted_course | varchar | formatted course, matched to the transcript course |
| target_course_code | varchar | course code, matched to the transcript course |
| target_course_title | varchar | course title, matched to the transcript course |
New Configurations
| name | possible values | purpose |
|---|---|---|
map_plan_status_email | email address | Email address that status email should be sent to. map_plan_status_send_report_email should be set to true for email to be sent |
map_plan_status_send_report_email | true, false | If set the true, an email will be sent to the address listed in config map_plan_status_email |
| map_plan_status_send_off_plan_coach_email | true, false | Send off plan emails to coaches after status calc run |
map_plan_status_term_bound_strict | true, false | If set to true, course matches must be term bound to be considered on plan |
map_plan_status_use_substitutable_courses | true, false | Drives the use of substitutable courses when calculating plan status |
Matching criteria
Different schools may have their course/transcript data organized differently. This means they may want different criteria to drive the matching logic between plan and transcript. We will assume that three criteria are non-negotiable and static; term_code and formatted_course. In addition to the static matching criteria, implementors have the option to add additional criteria. COURSE_TITLE will match PLAN_COURSE.COURSE_TITLE to EXTERNAL_STUDENT_TRANSCRIPT_COURSE.TITLE and CREDIT_HOURS will match PLAN_COURSE.CREDIT_HOURS with EXTERNAL_STUDENT_TRANSCRIPT_COURSE.CREDIT_EARNED.
In addition for phase to, course code is being added to external_student_transcript_course so COURSE_CODE will be added as an additional criteria. Which will match EXTERNAL_STUDENT_TRANSCRIPT_COURSE.COURSE_CODE with PLAN_COURSE.COURSE_CODE.