Appendix L - Course Repeats Logic

Student course repeats should be calculated based on the Alternate Course Number (first five characters of a course code). Calculating repeats using the complete course number may result in incorrectly awarding extra credit toward graduation. Several reports are enhanced to use a common function for calculating Course repeats.

List of affected Reports

Ontario Student Status
Ontario Student Transcript Report
Ontario Secondary Report Card
OSSD Diploma Date Calculations
OCAS Transmission
OCAS - Electronic Transcript
OUAC Transmission
Potential Scholars
Specialist High Skills Major Record
OnSIS Secondary School Submission
OnSIS Night School Submission
OnSIS ECPP Submission

How Course Repeats is calculated on Provincial Reports

A common function PSSR_ON_COMMON.getStudentCrsRepeats is created to standardize the logic used to calculate repeats on the different reports.

A Course is considered a repeat of another course if both courses have the same Alt Course Number, with the exception that a course is not considered a repeat of the same course (same Alt course number) that is defined as Coop.

(Note: if the Alt Course Number is not defined for a course, the first 5 characters of the Course Number is used for the calculation.

Also, the following course pairs (GLS1O/GLE1O, BTT1O/BTT2O, HFN1O/HFN2O, HIF1O/HIF2O, BBI1O/BBI2O) are considered pairs of each other. If student has taken both 1O and 2O versions of a course from any of the pairs, only the course with the higher credit/mark is considered, and the other is considered a repeat. If both have the same credit/mark the 2O will be considered and the 1O will be the repeat.

The course repeat flag for the first (earliest) instance of a course is F (false). The flag is updated to T (true) for all subsequent instances. The same applies to course pairs and all submission periods. If the student is taking both courses in a pair, the second or subsequent course in the pair is treated as a repeat.

Courses with the following criteria are considered when calculating repeats:

• Only credit courses are considered ( where credit_hours[Courses] > 0)

• Only completed courses with final marks (where PotentialCrHrs > 0)

• Only passed courses (where EarnedcreditHrs >0). Failed courses are not considered.

• Courses that are Not Excluded from Transcripts

• Courses that are Not Exclude from GPA

• Course does not start with PLE99, QEV1W, QEE99, QMA99, QAP99, QSE99

The following rules are used to identify COOP course:

If any of the following fields are set as indicated, then the course is considered COOP:

Course with Delivery Type set to Co-Op (4) on the Course Historical Grades record or on the Course Section Level (S_ON_SGR_X]Delivery_Type = 4/[S_ON_SEC_X]Delivery_Type = 4)

OR

If the Transcript Co-op Note Flag is set ON for Historical Grades, CC enrolment record, Courses ([S_ON_SGR_X]Transcript_C = 1/[S_ON_CRS_X]Transcript_C = 1/[S_ON_CC_X]Transcript_C = 1)

The following rules are used to identify the highest mark:

• The mark from the course with the highest credit is used first.

• If more than one course has the same highest credit value, then the one with the highest mark is used.

Calculate repeat on course number flag

This new flag has been added on Courses, Sections, and Historical Grades Pages. This flag is used to enforce calculate repeats using the complete course number. When selected the course will be considered repeat only if the student is enrolled in the same exact course number more than once.

Setting this flag may be necessary for the following scenarios

• If a one-credit course is split over two semesters (for example, GLE1O1 and GLE1O5), and the student earns 0.5 credit per semester, then those two courses should not be considered a repeat of each other even though they have the same Alt course number(GLE1O). When this flag is set to ON for one of these courses (for this example GLE1O5), the logic will calculate repeat on the Course Number for GLE1O5, and they will not be considered as repeats. If the student has two instances of GLE1O5, then one of them will be a repeat.

• The same idea applies to Coop courses where a student can earn extra credits for the same coop course, you will need to set the 'Calculate Repeat on Course number' flag ON for one of the courses.

• For graduated students, to print their Transcript as it was printed in the past (even if extra credits was granted by mistake). A script is provided in the package to mass update this flag for graduated students.

• In the case of course pairs, if the 'Calculate repeat on course number' flag is set to 'on', the logic should calculate repeat based on the course number, ignoring the first 5 characters. Example  GLS1O1A will not be a repeat of GLE1O1B but GLS1O1A will be a repeat of GLE1O1A.

Note: The Calculate Repeat on Course number' flag is added to the courses page (applies to all students taking the course), the Section page (applies to only students enrolled in the section), and on the Historical Grades pages (applies to a particular student).

Examples:

Common Function PSSR_ON_COMMON.getStudentCrsRepeats

Function Name: PSSR_ON_COMMON.getStudentCrsRepeats

Function description: This function calculates course repeats for one or a list of students. Courses included in the calculation are completed courses as of a specific date. It determines the course with the highest marks and the instances that are considered repeats. The calculation is based on a flag that indicates whether the course number or the Alt-course number should be used for the calculation. Also, there is special logic for coop courses.

Function Parameters:

• List of StudentDCIDs.

• One studentDCID (default null when the list of StudentDCIDs is passed).

• Effective date ( default is system date). This date will be used to include only courses that are completed before or on that date in the calculation. The courses with final marks are also considered as completed courses regardless of whether the course ended before or on the effective date.

• Parameter is for ignoring / including 'ExcludeFromGPA' records. Default is 1 i.e., the function will exclude such records. If 0 is passed then it will include marks with 'ExcludeFromGPA' selected.

Function Returns

The function returns the following fields:

• toredgrades.DCID

• studentid

• schoolid( where mark is entered)

• course_number

• alt_course_number

• sectionID (as entered on storedgrades record)

• termID on storedgrades record

• completionDate

  • The course completion date is determined as follows:

    If [StoredGrades.S_ON_SGR_X]Credit_Date is defined use that value.
    If [StoredGrades.S_ON_SGR_X]Credit is undefined and there is an associated CC record, use the calendar day immediately prior to [CC]DateLeft.
    If [StoredGrades.S_ON_SGR_X]Credit_Date is undefined and there is no associated CC record and [StoredGrades]TermId is defined (i.e. non-zero), use [Terms]LastDay for the associated term.
    If [StoredGrades.S_ON_SGR_X]Credit_Date is undefined and there is no associated CC record and [StoredGrades]TermId is undefined, use [StoredGrades]DateStored (TermID should never be undefined).

• credit_type,

• grade,

• earnedcrhrs

• repeatCategory

  • this can be altCrsNumber or altCrsNumberCOOP if the Calculate Repeats on Course Number is Off on storedgrade record and on course/section level AND depending on whether the course is COOP or not

  • crsNumber if Calculate Repeats on Course Number is ON at course/section or storedgrades level

• highmarkRec

  • will have 1 if it is the highest mark among repeated class marks

  • will have less than 1 value if it is a lower mark among repeated class marks

  • will have NULL if there is no repeated class mark

• repeatStatus

  • this will have + if it is highest mark R when it is a repeated mark

Function Logic

Only courses with the following criteria should be considered: (current and failed courses should not be part of the calculations)

• Credited courses(credit_hours[Courses] > 0)

• EarnedcreditHrs >0 indicate passing mark

• Final Marks (potentialCrHrs > 0)

• Not Excluded from Transcripts 

• not Exclude from GPA ( depends on the fourth parameter of the function)

• 'PLE99', 'QEV1W', 'QEE99', 'QMA99', 'QAP99', 'QSE99' should be excluded from the calculation

The function process the student courses as follows:

Records with the Calculate Repeats on Course Number flag is ON

• Group courses where the courses/sections/storegrade record has the Calculate Repeats on Course Number flag is ON

  • Use current logic to calculate repeats for this group - logic based on the course_number - ( all 6/more characters)

Records with the Calculate Repeats on Course Number flag is OFF and Course is coop

• For the rest of the records, Group courses where the courses/sections/storegrade record has the Calculate Repeats on Course Number flag OFF, and 
  If any of the following fields are set as indicated, then it is a COOP course:
  [S_ON_SGR_X]Delivery_Type = 4
  [S_ON_SEC_X]Delivery_Type = 4
  [S_ON_SGR_X]Transcript_C = 1
  [S_ON_CRS_X]Transcript_C = 1
  [S_ON_CC_X]Transcript_C = 1

• Calculate repeats on the alt_course_number (first 5 characters) for this group

Records with the Calculate Repeats on Course Number flag is OFF and Course is NOT coop

• For all courses that are not yet processed, Group courses where the courses/sections/storegrade record has the Calculate Repeats on Course Number flag OFF and [S_ON_SGR_X]/S_ON_SEC_X[Delivery_Type != 4 and [S_ON_SGR_X]/[S_ON_CC_X]/[S_ON_CRS_X]Transcript_C != 1 )

• Calculate repeats on the alt_course_number (first 5 characters) for this group

Available Scripts

The following are two optional scripts available.

	Description
Script – RepeatedCourses.sql Review additional information and a download link for the Course Repeat Differences script.	This script can be used to get a list of active students (as of the effective date entered) who have repeated courses using the logic matching on alternate course number and will not be considered repeated if matched on the complete course number. The script has 2 parameters.  Enter both parameters, and then click Apply. Parameter 1: school_id_Comma_Separated_List where a comma-separated list of schools can be entered Parameter 2: effective_date  - The student must be active on the date entered format YYYY-MM-DD Sample Script Output For this student, this course 'GWL3O' will show as a repeat on the reports ( both courses, the student earned a credit, both are Coop courses, and both match on the Alt course number) If this is not correct and both credits should be counted then you have the following options: If this applies to all students that are taking those 2 courses, then you need to set the ‘Calculate repeats on Course Number flag’ ON for those courses on the courses page. If this applies to this student only, then you should set the flag on the historical grade record of GLW3OD and GLWOR Also, this flag can be set on the section level, so only students enrolled in the sections will not have these courses be considered as repeats by matching on the Course number.
Script - ON_UPDATE_COURSEREPEATFLAG.sql Review additional information and a download link for the Calculate Course Repeats script.	Running this script is optional and can be done at the end of your testing. Run this script to set ‘Calculate Repeats on Course Number flag’ ON for all Historical Grades records for all graduated students in your board. If this flag is selected for all the student's courses, the reports will use the Course Number to calculate repeats (old Logic) and this will ensure to get the same Transcript is printed for graduated students using the old logic.