OnSIS June Suspension/Expulsion Submission Report
Introduction
The June Suspension/Expulsion submission is generated using either the OnSIS Elementary School Submission or OnSIS Secondary School Submission report depending on the school for which the report is run. When the June Suspensions/Expulsions submission period is selected the submission file is generated with School Incident (and child segments). These segments are not generated for other submission periods.
The Run Demographic Snapshot process must be run at least once prior to running any OnSIS Submission report.
Pending Area Extract (PAE) files must be imported at least once for each submission period prior to running any OnSIS Submission report.
Additional Resources
See the following resources for more information on PowerSchool and OnSIS Batch File Processing.
OnSIS Guides
Ontario Ministry of Education School Information System (OnSIS Core System), Batch File Specifications.
Ontario Ministry of Education School Information System (OnSIS Core System), School Data Submission Business Rules.
Selection Criteria
Selection criteria determine which database records are used in the report. Refer to selection criteria for analysis when the report does not return the correct records.
Data Selection
The report selects records from multiple tables based on the submission type and submission period for the selected school.
Ensure that the count dates have been set up correctly in the District Info Setup. This report will not produce any output if the count date is not filled in.
Report Input
For help with navigation and running the report, see How to Find and Generate a Report.
Field | Description |
Current Selection Students* | If "Selected n Students Only" is selected, the report includes, subject to other student selection, only students in the current PowerSchool student selection. Default is "All Students". |
Select School* | Choose which school to run the submission report for. If the report is run at the school level, only the current school will be available. |
Submission Type* | Select the submission type for the report. Currently, only Public is supported. |
Submission Period (YY/MM/DD)* | Select the June - Suspensions/Expulsions submission period. Note: When the June Suspensions/Expulsions submission period is selected, the submission file is generated with School Incident (and child segments). Suspension/Expulsions submission is from the start of the current term until the June reporting date. |
Report Output
Each of the fields displayed in the output of the report are described below. See Understanding the Report Output Table for a definition of each column in the table.
XML Version and Encoding
Data Element | Description | [Table]FieldName |
<?xml version="1.0" encoding=" ISO-8859-1"?> | XML version and encoding types – required to validate a well-formed XML file | n/a |
ONSIS_BATCH FILE Segment
Data Element | Description | [Table]FieldName |
ONSIS_BATCH_FILE | Root node of the xml file. Required by the Ministry. | n/a |
HEADER Segment
There is only one Header Segment per OnSIS Data Submission.
Data Element | Description | [Table]FieldName |
HEADER | Section node. Required by the Ministry. | n/a |
VERSION | Document version number. Must have value “1.0”. | n/a |
BATCH_TYPE | File type. Must have value “School Data Submission”. | n/a |
BATCH_FILE_ID | File id. Must have value “0001”. | n/a |
DATE | Date of report creation. Format ‘YYYY/MM/DD’ | n/a |
TIME | Time of report creation. Format ‘HH24MISS’ | n/a |
DATA Segment
The DATA Segment contains the data related portion of the OnSIS Data Submission.
Data Element | Description | [Table]FieldName |
DATA | This is the outer tag for all data | n/a |
SCHOOL SUBMISSION Segment
Each submission is for a single school, therefore there will only be on SCHOOL_SUBMISSION segment.
Data Element | Description | [Table]FieldName |
SCHOOL_SUBMISSION | This is the outer tag for school data. | n/a |
ACADEMIC_YEAR | The academic year of the submission. Format ‘YYYY-YYYY’. For example, 2011-2012. | [Terms]YearId (since YearId is 2 digits, representing the year after 1990, this is displayed as a span of [Terms]YearId+1990-[Terms]YearId+1991 |
SUBMISSION_PERIOD_TYPE | Submission period type. This code must be one of those specified in the minstry OnSIS Reference Table Spreadsheet and will be set by the UI settings of Submission Type and Submission Period. The combination of element ACADEMIC_YEAR and SUBMISSION_PERIOD_TYPE is used to uniquely identify submission period. | JUNELEMSUS1 |
SCHOOL Segment
Each submission is for a single school. Therefore there will only be one SCHOOL segment.
For all CLEAR_* elements, refer to Appendix A – Batch Files Purge and Pre-Population XML Tags of the ministry OnSIS Batch Process and File Specifications document.
Data Element | Description | [Table]FieldName |
SCHOOL | This is the outer tag for all schools. | n/a |
SCHOOL_NUMBER | The unique identifier for the school. | [Schools]Alternate_School_Number or [Schools]School_Number if [Schools]Alternate_School_Number is 0 |
CLEAR_PENDING_AREA | Flag to clear pending area. Must have value “Y”. | n/a |
SCHOOL INCIDENT Segment
Selection Criteria
Incidents for the entire current year are included.
Incidents that occurred in the previous year but are not 'complete' (i.e. incidents appearing in the EXSMS-0013 PAE file) are included, with an ACTION of 'UPDATE'.
Incident segments are only generated during the June submission period.
Only one school incident record is generated for a given incident.
The actionattribute code for the non-academic program must be set to “YES” in order for the SUSPENSION EXPULSION PROGRAM segment be generated.
Student_Outcome
If student has a primary or a single Behavior, then the Action associated with it is reported as STUDENT_OUTCOME. If there is no Action associated with the primary or single Behavior, the Action associated with the student is reported.
If the student has multiple Behaviors, with or without actions associated with them, and none are marked as primary, then the Action associated with the student is reported as STUDENT_OUTCOME.
If the student has multiple Behaviors, where one is primary with an Action attached and the rest are with or without Actions attached, the Action associated with the primary Behavior is reported.
If the student has multiple Behaviors, and one is primary with no Action attached and the rest are with or without Actions attached, the Action associated with the student is reported, if none exists then no action will be reported.
- The SUSPENSION_EXPULSION_PROGRAM reported is the one related to the Action reported (i.e, STUDENT_OUTCOME).
- If the student has only one Behavior, then it is considered the primary Behavior.
- If there are multiple Actions, the Action with latest Start Date followed by max(ID) (when Start Dates are the same) is reported.
Note for multiple schools with the same BSID
Incidents from all schools with the same selected BSID will be included in this segment.
Data Element | Description | [Table]FieldName |
SCHOOL_INCIDENT | This is the outer tag for school incident. | n/a |
ACTION | This is the action associated with this submission. The value is determined by comparing the PowerSchool incident id ([Incident]Incident_Id) against the PAE referenence number ([ON_School_Incidents]Reference_Num. Value: “ADD” “UPDATE” “DELETE” | n/a |
SCHOOL_INCIDENT_ID | Business ID for school incident. When the ACTION of SCHOOL_INCIDENT is “UPDATE” or “DELETE “this element is mandatory. Not to be specified if SCHOOL_INCIDENT is “ADD”SCHOOL_INCIDENT_CODE PowerSchool Business Rules: Incidents are pulled for the entire year. Incident segments are only generated during the June submission period. Only one school incident record is generated for a given incident. | If action is UPDATE or DELETE, set to [ON_School_Incidents]reference_num. Element is set empty if action is ADD. |
INCIDENT_SITE_TYPE | When the ACTION of SCHOOL_INCIDENT is “ADD”, this element is mandatory. | [incident_lu_sub_code]state_detail_report_code where code_type = ‘LOCATIONCODE’ |
DATE_OF_OCCURRENCE | Date of occurrence for this record. Format = yyyy/mm/dd When the ACTION of SCHOOL_INCIDENT is “ADD”, this element is mandatory. | [Incident]incident_ts yyyy/mm/dd |
TIME_OF_OCCURRENCE | TIME of occurrence for this record. Format = HH:MM When the ACTION of SCHOOL_INCIDENT is “ADD”, this element is mandatory. | [Incident]incident_ts HH:MM |
REPORTED_TO_POLICE_FLAG | Report to police flag (T/F). When the ACTION of SCHOOL_INCIDENT is “ADD”, this element is mandatory. | The REPORTED_TO_POLICE_FLAG tag is set to T, if at least one of the subcode field Provincially Reportable is set to true. [incident_lu_sub_code] IS_STATE_REPORTABLE_FLG where code_type = ‘ATTRIBUTECODE’ and state_aggreagate_rpt_code = ‘ON-POLICE-REPORT’. If undefined in the database a value of 'F' is reported. |
YOUR_REFERENCE_NUMBER | SMS Incident reference number - This field is stored on the SCHOOL_INCIDENT table. | [Incident]incident_id |
STUDENT INCIDENT CHILD OF SCHOOL INCIDENT | ||
STUDENT_INCIDENT | This is the outer tag for student incident. | n/a |
ACTION | This is the action associated with this submission. Value: “ADD” “UPDATE” “DELETE” PowerSchool Business Rules: There can be multiple STUDENT_INCIDENT segments for an incident, but only one STUDENT_INCIDENT segment for each student. Only students who are associated with the incident and are categorized as offenders will be pulled. | n/a |
OEN | Student OEN. The value of this element must be consistent with database. STUDENT.STUDENT_OEN. Used for OEN Authentication. | [Students]state_studentnumber |
STUDENT_LEGAL_GIVEN_NAME | Student Legal Given Name. Used for OEN Authentication. | [S_ON_STU_X]Legal_First_Name. If not present use [Students]First_Name. |
STUDENT_LEGAL_SECOND_NAME | Student Legal Second Name. Second name no longer is used as part of the validation. The XML Batch file will not change; the Second name is simply ignored. | [S_ON_STU_X]Legal_Middle_Name. If not present use [Students]Middle_Name. |
STUDENT_LEGAL_LAST_NAME | Student Legal Last Name. Used for OEN Authentication. | [S_ON_STU_X]Legal_Last_Name. If not present use [Students]Last_Name. |
STUDENT_DOB | Student Date of Birth. Format = yyyy/mm/dd. Mandatory Field. Used for OEN Authentication. | [Students]dob |
GENDER_TYPE | Student Gender Valid values:
| [Students]Gender getStudentGenderReportValues function is used to get the student's gender type from the code sets tables. Not reported for Non-Returning students. |
GENDER_DESC | Student's gender description. This field is only populated when Gender is set to S for the student. | getStudentGenderReportValues function is used to get the student's gender desc from the code sets tables. |
APPEAL_START_DATE | Start Date of Appeal. Format = yyyy/mm/dd. PowerSchool Business Rules: If more than one Appeal action is associated with a student the last Appeal entered will be pulled. | [Incident_action]action_plan_begin_dt where [incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-APPEAL’ and [incident_lu_sub_code]code_type = ‘ACTIONCODE’ |
APPEAL_DECISION_DATE | Appeal Decision Date. Format = yyyy/mm/dd. | [Incident_action]action_actual_resolved_dt where [incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-APPEAL’ and [incident_lu_sub_code]code_type = ‘ACTIONCODE’ |
APPEAL_OUTCOME_TYPE | incident_lu_cub_code.state_detail_report_code where. incident_lu_cub_code.state_aggregate_rpt_code = ‘ON-APPEAL’ and incident_lu_cub_code.code_type = ‘ACTIONCODE’ | |
REVISED_NUMBER_OF_SCHOOL_DAYS | Revised Number of School Days. The REVISED_NUMBER_OF_SCHOOL_DAYS tag will be processed starting from 2009-2010 submission onwards. | [Incident_action]duration_actual where [incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-APPEAL’ and [incident_lu_sub_code]code_type = ‘ACTIONCODE’ |
STUDENT_INFRACTION CHILD OF STUDENT INCIDENT | ||
STUDENT_INFRACTION | This is the outer tag for student incident infraction. Note: If the student has multiple infractions for an incident, the report pulls only the primary infraction. | n/a |
ACTION | This is the action associated with this submission. Value: “ADD” “UPDATE” “DELETE” PowerSchool Business Rules: There can be multiple STUDENT_INFRACTION segments for each student. The student must have a behaviorcode for the action associated with them and the behaviorcode type must be ON-Infraction-Expel or ON-Infraction-Susp. Duplicates of the same infraction behaviorcode types for a student/incident are sent. | n/a |
INFRACTION_TYPE | The combination of element school incident ID + OEN + infraction type is used to uniquely identify the student incident infraction. | [incident_lu_sub_code]state_detail_report_code where [incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-INFRACTION-EXPEL’ or and ‘ON-INFRACTION-SUSP’ [incident_lu_sub_code]code_type = ‘BEHAVIORCODE’ |
OTHER_DESCRIPTION | Other Infraction description. Only reported if INFRACTION_TYPE = 'OTH', otherwise element is empty. | [incident_lu_sub_code]short_desc where [incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-INFRACTION-EXPEL’ or and ‘ON-INFRACTION-SUSP’ [incident_lu_sub_code]code_type = ‘BEHAVIORCODE’ |
SUSPENSION_EXPULSION_FLAG | Suspension/expulsion flag (S/E). | [incident_lu_sub_code]state_detail_report_code is translated to S or E where [incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-INFRACTION-EXPEL’ or and ‘ON-INFRACTION-SUSP’ [incident_lu_sub_code]code_type = ‘BEHAVIORCODE’ |
STUDENT OUTCOME CHILD OF STUDENT INCIDENT | ||
STUDENT_OUTCOME | This is the outer tag for student incident outcome. Note: The report only pulls the outcome which relates to the primary student infraction. | n/a |
ACTION | This is the action associated with this submission. Value: “ADD” “UPDATE” “DELETE” | n/a |
INFRACTION_OUTCOM_TYPE | The combination of element school incident code + OEN + infraction outcome type is used to uniquely identify the student incident outcome. The reported value is derived from the state aggregate report code as follows:
| Incident Management: Edit code - Provincial Aggregate Report Code [Incident_lu_code]incident_category where [Incident_lu_code]code_type = ‘ACTION’ and [Incident_lu_code]state_aggregate_rpt_code is ON-EXPEL-BOARD or ON-EXPEL-SCHOOL or ON-SUSPEND |
INFRACTION_AUTH_TYPE | When the ACTION of STUDENT_OUTCOME is “ADD”, this element is mandatory. | Incident Management - Edit Subcode - Provincial Detail Report code [Incident_lu_sub_code]state_detail_report_code where [Incident_lu_code]code_type = ‘ACTION’ and [Incident_lu_code]state_aggregate_rpt_code is ON-EXPEL-BOARD or ON-EXPEL-SCHOOL or ON-SUSPEND |
FIRST_DAY_SUSPENSION_EXPULSION | First day of suspension/expulsion Format = yyyy/mm/dd When the ACTION of STUDENT_OUTCOME is “ADD”, this element is mandatory. | [Incident_action]action_plan_begin_dt where [Incident_lu_code]code_type = ‘ACTION’ and [Incident_lu_code]state_aggregate_rpt_code is ON-EXPEL-BOARD or ON-EXPEL-SCHOOL or ON-SUSPEND |
LAST_DAY_SUSPENSION_EXPULSION | Last day of suspension/expulsion Format = yyyy/mm/dd The “Last day of suspension/expulsion” XML tag (<LAST_DAY_SUSPENSION_EXPULSION>) is optional. It will be reported as empty if the suspension or expulsion carries over to the next year. | [Incident_action]action_plan_end_dt where [Incident_lu_code]code_type = ‘ACTION’ and [Incident_lu_code]state_aggregate_rpt_code is ON-EXPEL-BOARD or ON-EXPEL-SCHOOL or ON-SUSPEND |
NUMBER_OF_SCHOOL_DAYS | Number of School Days Format = 999.99 | Assigned Duration [Incident_action]duration_assigned where [Incident_lu_code]code_type = ‘ACTION’ and [Incident_lu_code]state_aggregate_rpt_code is ON-EXPEL-BOARD or ON-EXPEL-SCHOOL or ON-SUSPEND |
APPEAL_FLAG | Appeal Flag (T/F) When the ACTION of STUDENT_OUTCOME is “ADD”, this element is mandatory. | Select ‘T’ if a record exists where [Incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-APPEAL’ and [Incident_lu_sub_code]code_type = ‘ACTIONCODE’ and they have a record where [Incident_lu_code]code_type = ‘ACTION’ and [Incident_lu_code]state_aggregate_rpt_code is ON-EXPEL-BOARD or ON-EXPEL-SCHOOL or ON-SUSPEND |
SUSPENSION EXPULSION PROGRAM CHILD OF STUDENT INCIDENT | ||
SUSPENSION_EXPULSION_PROGRAM | This is the outer tag for student suspension/expulsion program. Note: Only one occurrence can exist on file. Repeating block to allow “DELETE” followed by an “ADD” action. The SUSPENSION_EXPULSION_PROGRAM tag is valid and will be processed starting from 2009-2010 submission onwards. Note: The report pulls the suspension/expulsion info associated to the reported STUDENT_OUTCOME | n/a |
ACTION | This is the action associated with this submission Value: “ADD” “UPDATE” “DELETE” Note: “UPDATE” is a valid only when changing Suspension/Expulsion Program Status type and/or adding, deleting Non-Academic Program types. A “DELETE” action will result in auto-deletion of Non-Academic Program child records of the Suspension/Expulsion Program. | n/a |
TYPE | Element Type is used to uniquely identify the Suspension.Expulsion Program. | [Incident_lu_sub_code]state_detail_report_code is translated to S or E where [Incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-PGM-TYPE’ and [Incident_lu_sub_code]code_type = ‘ACTIONATTRIBUTE’ |
STATUS_TYPE | When the ACTION of SUSPENSION_EXPULSION_PROGRAM is “ADD” or “UPDATE”, this element is mandatory. | [Incident_lu_sub_code]state_detail_report_code is translated to S or E where [Incident_lu_sub_code]state_aggregate_rpt_code = ‘ON-PGM-STATUS’ and [Incident_lu_sub_code]code_type = ‘ACTIONATTRIBUTE’ |
NON ACADEMIC PROGRAM CHILD OF SUSPENSION EXPULSION PROGRAM | ||
NON_ACADEMIC_PROGRAM | This is the outer tag for Non Academic Program type. | n/a |
ACTION | This is the action associated with this submission. Value: “ADD”, “DELETE” | n/a |
NON_ACADEMIC_PROGRAM/TYPE | Element Type is used to uniquely identify the Non-Academic Program type. | [Incident_lu_sub_code]state_detail_report_code is translated to S or E where [Incident_lu_sub_code]state_aggregate_rpt_code like ‘%ON-NONACAD-%’ and [Incident_lu_sub_code]code_type = ‘ACTIONATTRIBUTE’ |