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 Calculation Flow Chart
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 (Process will produce one row per substitution per active plan. An substitution-less plan will produce zero rows.)
| 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 | Drives the frequency of the cron job that will calculate status |
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.
Implementation Concerns
- System load and memory footprint will be big concerns for the calculation process. We don't want to load all active plans into memory at once. Implementors may not want to have cron job run during business hours as it could put load on the web server and database.
- Configuration map_plan_status_cutoff_term_code is a maintenance concern. Will most likely have to be updated every term. This must be clear to implementors.
- map_plan_status_passing_grades may not be very flexible enough for a lot of schools. We may want to explore a hierarchical configuration approach.
Open Questions
- Do we want to store one status per plan? If we want to store multiple statuses per plan, that may have performance and storage concerns.
- JME: During the original discussion we agreed for phase 1 that we would record only the current plan status and off plan reason. The one per is actually buried in the /wiki/spaces/SSP/pages/103986907 in the Operational Table description.