Page Comparison

...

• For each student with an active plan (map_plan.object_status = 1), SSP will compare the courses in external_student_transcript_course (formatted_course) for each term (distinct term_code from external_student_transcript_course) with the planned courses (map_plan_course.formatted_course) for equivalent terms (map_plan_course.term_code) starting with the oldest Plan term through the calculation cutoff term.  If not cutoff term is defined, the current term will be used.  A match will have matching criteria (configurable) for both the plan Plan and transcripted courses.  By default the formatted_course and term_code are used for matching.  Additionally, the course title and course hours can be used to provide a more precise match.  The result of the calculation is a database update in an SSP operational table to store the planning Plan status and reasons for being off plan (if they exist).  Each time the calculation is performed, the previous status calculation is removed so that only the most current calculation is stored in the database.  The external_person_planning_status table will remain for implementers choosing to provide the calculation through the data integration.

Process Diagram

Database Tables

Image Added

Database Tables

Image Added

Queries

• The initial version of the Plan status calculation does not produce any reports for the calculation process.  The student's current status and high level explanation of an off plan status will display in the Main Tool, MAP Tool and MAP Plan Status report.  For details of most recent calculation, example query are described below
• Identify Term Anomalies
  
  

select p.school_id, p.first_name, p.last_name, mr.plan_id, mr.plan_status, mr.plan_note, mct.term_code, mct.anomaly_code
from person p, map_status_report mr,  map_status_report_Term_details mct
where p.id = mr.person_id and
mr.id = mct.report_id
order by p.school_id

• Identify Course Anomalies

select p.school_id, p.first_name, p.last_name, mr.plan_id, mr.plan_status, mr.plan_note, mcd.formatted_course, mcd.anomaly_code
from person p, map_status_report mr, map_status_report_course_details mcd
where p.id = mr.person_id and
mr.id = mcd.report_id
order by p.school_id

Anomaly Codes

• MULTIPLE_ANOMALIES_IN_TERM: multiple course anomalies in the term

• NO_ANOMALY: not anomalies existed in the term

• CURR_OR_FUT_COURSE_NO_GRADE: a missing or failed grade in a current or future term

• COURSE_NOT_PASSED: a missing or failed grade in a past term

• COURSE_NOT_REGISTERED: a missing course in a current or future term

• COURSE_NOT_TAKEN: a missing course in a past term

Definitions

• Past Term: any term with a start date before the current term's start date
• Current Term: same definition that we are using for the MAP plan/template term grid.
• Future Term: any term with a start date on or after the current term’s start date
• Cutoff Term: the last term to consider for plan to transcript term matching.  The cutoff term is configurable while the current term is used if the configuration item does not exist or if the configured value is in the past.
• Passing Grade: any grade determined to be a passing grade and fulfill completion of a planned course
• Anomaly: for each instance where a planned course or term does not match the transcript data, an anomaly code describes the reason for
• On Plan: there is an exact match of courses for each transcripted term with the terms and courses on the active plan.  For planned terms after the current term including the calculation cutoff term without a matching transcript term, the Plan is considered on plan until the transcript data diverges from the plan.
• Off Plan: at least one term has a planned course that does not exist in the matching transcripted term or a transcripted course matching a planned courses was not passed.

...

Anomaly Codes