Pending Area Extract (PAE) Tables
Contents
Introduction
The following tables store information imported using the OnSIS Pending Area Extract Data File Import report. These tables are not PowerSchool Virtual tables; they are real tables in the PowerSchool database and are created upon system installation.
Note: When PAE files are imported using the PAE Import with "Enable PAE Import School Parameter" selected, the School ID is imported into column[1] in the H2 record.
Tables
Table Name | PAE Import File |
|---|---|
ON_Classes | Class List |
ON_Segments | Course Segment |
ON_Educator_Assignments | School Educator Assignment |
ON_Instructional_TMs | School Educator Assignment |
ON_Class_Assignments | School Educator Assigned Classes |
ON_Assigned_Subjects | SMS School Educator Assigned Subjects |
ON_Assigned_Grades | SMS School Educator Assigned Subjects |
ON_TLAs | SMS School Educator TLA Information |
ON_School_Enrollments | Student School Enrollment |
ON_Class_Enrollments | Student Class Enrollment |
ON_Special_Educations | Special Education |
ON_Second_Languages | Second Language Program |
ON_Diplomas | Student Diploma Certificate |
ON_Option_Sheets | Option Sheet |
ON_Option_Sheet_Courses | Option Sheet |
ON_SALEPs | SALEP |
ON_SHSM_Programs | SHSM Program |
ON_SHSM_Certifications | SHSM Program |
ON_SHSM_Courses | SHSM Courses |
ON_SHSM_Crs_Enrols | SHSM Courses |
ON_SHSM_Crs_Oth_Creds | SHSM Courses |
ON_School_Incidents | Suspension Expulsion Program |
ON_Stud_Inc_Infracs | Suspension Expulsion Program |
ON_Suspend_Expulsions | |
ON_Infractions | Suspension Expulsion Program |
ON_Infraction_Outcomes | |
ON_Course_Calendars | Course Calendar |
ON_Report_Cards | Report Card |
ON_Report_Card_Marks | Report Card Marks |
ON_RC_Learn_Skills | Report Card Learning Skills |
ON_Lit_Nums | Literacy Numeracy |
ON_Ave_Marks | Average Mark |
Field Listing for Each Table
Import Extract #001: Table ON_Classes
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | class_code |
column[2] in DT record | class_start_date |
column[3] in DT record | class_end_date |
column[5] in DT record | class_type |
column[4] in DT record | language_type |
column[6] in DT record | classroom_type |
column[1] in DT record | key |
Import Extract #002: Table ON_Segments
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | class_key |
column[2] in DT record | ministry_dfnd_crs |
column[3] in DT record | institution_type |
column[4] in DT record | local_dev_crs |
column[6] in DT record | language_type |
column[5] in DT record | credit_value |
| column[6] in DT record | TOTAL_CLASS_COURSE |
column[2] + column[3] + column[4] + column[6] + column[5] in DT | key |
Import Extract #003: Table ON_School_Enrollments
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | oen |
column[2] in DT record | province_state_type |
column[3] in DT record | province_state_type_exit |
column[4] in DT record | country_type |
column[5] in DT record | country_type_exit |
column[6] in DT record | language_type |
column[7] in DT record | stu_brd_res_stat_type |
column[8] in DT record | grade_type |
column[9] in DT record | grade_designation_type |
column[10] in DT record | attendance_type |
column[11] in DT record | literacy_status_type |
column[12] in DT record | special_education_flag |
column[13] in DT record | language_type_previous_sch |
column[14] in DT record | residence_status_type |
column[15] in DT record | student_mobility_type |
column[16] in DT record | student_mobility_type_exit |
column[17] in DT record | jk_timetable_type_code |
column[18] in DT record | other_halftime_desc |
column[19] in DT record | care_trtmnt_res_type |
column[20] in DT record | class_code |
column[21] in DT record | postal_area_type |
column[22] in DT record | country_type_birth |
column[23] in DT record | province_state_type_birth |
column[24] in DT record | country_type_perm |
column[25] in DT record | province_state_type_perm |
column[26] in DT record | year_of_entry_to_canada |
column[27] in DT record | main_school_flag |
column[28] in DT record | school_student_number |
column[29] in DT record | french_admission_approval_date |
column[30] in DT record | ontario_scholarship_flag |
column[31] in DT record | ontario_scholarship_date |
column[32] in DT record | community_involvement_accu_hrs |
column[33] in DT record | local_school_program_flag |
column[34] in DT record | fte |
column[35] in DT record | promotion_flag |
column[36] in DT record | enrolment_start_date |
column[37] in DT record | enrolment_end_date |
column[39] in DT record | habitually_absent_flag |
column[40] in DT record | aboriginal_self_identification |
column[41] in DT record | reach_ahead_flag |
column[42] in DT record | oyap_flag |
column[43] in DT record | extended_day_prog_type_code |
column[44] in DT record | high_credit_fte |
column[45] in DT record | ade |
column[46] in DT record | high_credit_ade |
| column[47] in DT record | curr_country |
| column[48] in DT record | curr_province |
| column[49] in DT record | designated_school |
| column[50 in DT record | Gender |
| column[51] in DT record | Specify |
| column[52] in DT record | Online_Graduation_Requirement |
column[1] in DT record | key |
Import Extract #004: Table ON_Class_Enrollments
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | class_code |
column[3] in DT record | ministry_dfnd_crs |
column[4] in DT record | institution_type |
column[5] in DT record | local_dev_crs |
column[6] in DT record | credit_value |
column[7] in DT record | language_type |
column[8] in DT record | course_delivery_type |
column[9] in DT record | course_sem_type |
column[10] in DT record | withdrawal_type |
column[11] in DT record | withdrawal_date |
column[12] in DT record | course_start_date |
column[13] in DT record | course_end_date |
column[14] in DT record | earned_credit_value |
column[15] in DT record | attempted_credit_value |
column[16] in DT record | final_mark |
column[17] in DT record | course_sbst_flag |
column[18] in DT record | course_complete_flag |
column[19] in DT record | course_repeat_flag |
column[20] in DT record | compulsory_course_flag |
column[21] in DT record | segment_language_type |
column[22] in DT record | course_incomplete_flag |
Column[23] in DT record | course_continued_flag |
| Column[24] in DT record | othrcrs_creditrecovery |
| Column[25] in DT record | othrcrs_ministrycontent |
| Column[26] in DT record | othrcrs_online |
column[1] + column[2] + column[3] + column[4] + column[5] + column[7] + column[6] in DT record | key |
Import Extract #005: Table ON_Special_Educations
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | exceptionality_type |
column[3] in DT record | special_edu_plmnt_type |
column[4] in DT record | iprc_review_date |
column[5] in DT record | iprc_student_flag |
column[6] in DT record | non_identified_student_flag |
column[7] in DT record | main_exceptionality_flag |
column[8] in DT record | individual_education_plan_flag |
column[1] + column[2] in DT record | key |
Import Extract #007: Table ON_Second_Languages
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | type |
column[3] in DT record | minutes_per_day_of_instruction |
column[4] in DT record | hours_per_year_of_instruction |
column[1] + column[2] in DT record | key |
Import Extract #009: Table ON_Educator_Assignments
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | men |
column[2] in DT record | position_type |
column[3] in DT record | educator_leave_type |
column[4] in DT record | assignment_withd_type |
column[5] in DT record | department_type |
column[6] in DT record | fte |
column[7] in DT record | board_employee_number |
column[8] in DT record | assignment_start_date |
column[9] in DT record | assignment_end_date |
column[11] in DT record | teaching_type |
column[12] in DT record | core_flag |
column[13] in DT record | letter_permission |
column[15] in DT record | ntip_status_type |
| column[16] in DT record | Gender |
| column[17] in DT record | Specify |
column[1] + column[2] + column[3] + column[4] in DT | key |
Import Extract #009: Table ON_Instructional_TMs
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] + column[2] + column[3] + column[4] in DT | educator_key |
parsing column[10] in DT record | instructional_tm_type |
parsing column[10] in DT record | key |
Import Extract #010: Table ON_Class_Assignments
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] + column[2] + column[3] + column[4] in DT | educator_key |
column[5] in DT record | class_code |
column[5] in DT record | key |
Import Extract #011: Table ON_Assigned_Subjects
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] + column[2] + column[3] + column[4] in DT | educator_key |
column[5] in DT record | secondary_subject_type |
column[6] in DT record | elementary_subject_type |
column[7] in DT record | language_type |
column[8] in DT record | grade_flag |
column[10] in DT record | number_of_classes |
column[11] in DT record | reference_number |
column[5] + column[6] + column[7] + column[11] in DT | key |
Import Extract #011: Table ON_Assigned_Grades
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] + column[2] + column[3] + column[4] in DT | educator_key |
column[5] + column[6] + column[7] + column[11] in DT | subject_key |
parsing column[9] in DT record | grade_type |
parsing column[9] in DT record | key |
Import Extract #012: Table ON_TLAs
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] + column[2] + column[3] + column[4] in DT | educator_key |
column[5] in DT record | secondary_subject_type |
column[6] in DT record | elementary_subject_type |
column[7] in DT record | non_teaching_flag |
column[7] + column[5] + column[6] in DT | key |
Import Extract #013: Table ON_School_Incidents
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | incident_code |
column[2] in DT record | incident_site_type |
column[3] in DT record | occur_date |
column[4] in DT record | occur_time |
column[5] in DT record | report_police_flag |
column[7] in DT record | reference_num |
column[1] in DT record | key |
Import Extract #014: Table ON_Stud_Inc_Infracs
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | incident_key |
column[2] in DT record | enrollment_key |
column[5] in DT record | appeal_date |
column[6] in DT record | decision_date |
column[7] in DT record | appeal_outcome_type |
column[9] in DT record | reference_num |
column[10] in DT record | revised_school_days |
column[1] + column[2] in DT | key |
Import Extract #014: Table ON_Infractions
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | incident_key |
column[2] in DT record | enrollment_key |
substring(0,10) of column[3] in DT record | infraction_type |
substring(10,11) of column[3] in DT record | suspend_flag |
substring(11,12) of column[3] in DT record | discretionary_flag |
substring(12,13) of column[3] in DT record | board_set_flag |
column[4] in DT record | other_description |
column[1] + column[2] + substring(0,10) of column[3] in DT | key |
Import Extract #015: Table ON_Infraction_Outcomes
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | incident_key |
column[2] in DT record | enrollment_key |
column[3] in DT record | infraction_outcome_type |
column[4] in DT record | suspend_start_date |
column[5] in DT record | suspend_end_date |
column[6] in DT record | infraction_authority_type |
column[7] in DT record | appeal_flag |
column[9] in DT record | number_school_days |
column[10] in DT record | reference_num |
column[1] + column[2] + column[3] in DT | key |
Import Extract #019: Table ON_Course_Calendars
Source | Destination |
|---|---|
column[3] in H1 record | year_id |
column[1] in H2 record | alternate_school_number |
column[1] in DT record | ministry_dfnd_crs |
column[2] in DT record | institution_type |
column[3] in DT record | local_dev_crs |
column[4] in DT record | placeholder_crs |
column[5] in DT record | course_identifier |
column[6] in DT record | school_course_name |
column[7] in DT record | compulsory_flag |
column[1] + column[2] + column[3] + column[4] + column[5] in DT | key |
Import Extract #020: Table ON_Ave_Marks
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | grade_code |
column[2] in DT record | na_flag |
column[3] in DT record | subj_strand_code |
column[4] in DT record | ave_mark1 |
column[5] in DT record | ave_mark2 |
column[6] in DT record | ave_mark3 |
column[1] + column[3] in DT | key |
Import Extract #021: Table ON_Lit_Nums
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | block_code |
column[2] in DT record | grade_code |
column[3] in DT record | na_flag |
column[4] in DT record | num_of_blocks |
column[1] + column[2] in DT | key |
Import Extract #022: Table ON_Report_Cards
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | term_code |
column[3] in DT record | grade_in_Sept |
column[4] in DT record | alt_reportcard_flag |
column[5] in DT record | days_absent |
column[6] in DT record | times_late |
column[1] + (column[2] (formatted to 000000000)) in DT | key |
Import Extract #023: Table ON_Option_Sheets
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | option_sheet_academic_year |
column[3] in DT record | is_Enrolled |
column[4] in DT record | option_sheet_grade |
column[1] + column[2] + column[4] + column[3] in DT | key |
Import Extract #023: Table ON_Option_Sheet_Courses
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[7] in DT record | ministry_dfnd_crs |
column[8] in DT record | institution_type |
column[9] in DT record | local_dev_crs |
column[10] in DT record | place_hldr_crs |
column[11] in DT record | course_type |
column[12] in DT record | crs_identifier |
column[2] + column[4] + column[3] in DT | option_sheet_key |
column[1] + column[7] + column[8] + column[9] + column[10] + column[12] in DT | key |
Import Extract #024: Table ON_Report_Card_Marks
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | grade_type |
column[3] in DT record | term_code |
column[4] in DT record | subj_strand_code |
column[5] in DT record | report_card_mark |
column[6] in DT record | IEP_flag |
column[7] in DT record | subj_prog_code |
column[8] in DT record | second_lang_prog_code |
column[9] in DT record | opt_subj_name |
column[10] in DT record | NA_Flag |
column[11] in DT record | french_flag |
column[1] + column[2] + (column[3] (formatted to 000000000)) + column[4] in DT | key |
Import Extract #025: Table ON_RC_Learn_Skills
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | term_code |
column[3] in DT record | skill_description |
column[4] in DT record | level_code |
column[1] + (column[2] (formatted to 000000000)) + column[3] in DT | key |
Import Extract #026: Table ON_Diplomas
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | type |
column[3] in DT record | certificate_issued |
column[1] + column[2] + (column[3] (formatted to YYYY-MM-DD)) in DT | key |
Import Extract #027: Table ON_SAL_Records
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | program_type |
column[5] in DT record | exit_type |
column[6] in DT record | start_date |
column[7] in DT record | end_date |
column[1] + column[2] + (column[6] (formatted to YYYY-MM-DD)) in DT | key |
Import Extract #027: Table ON_SAL_Components
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
parsing column[4] in DT record | type |
column[1] + column[2] + (column[6] (formatted to YYYY-MM-DD)) in DT | program_key |
column[1] + column[2] + (column[6] (formatted to YYYY-MM-DD)) + parsing column[4] in DT | key |
Import Extract #028: Table ON_SHSM_Programs
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | type |
column[3] in DT record | all_requirements_met_date |
column[1] + column[2] in DT record | key |
Import Extract #028: Table ON_SHSM_Certifications
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | enrollment_key |
column[2] in DT record | program_key |
column[4] in DT record | type |
column[5] in DT record | date_completed |
column[6] in DT record | certification_hours |
column[1] + column[4] in DT record | key |
Import Extract #029: Table ON_SHSM_Courses
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in D1 record | enrollment_key |
column[2] in D1 record | ministry_dfnd_crs |
column[3] in D1 record | institution_type |
column[4] in D1 record | local_dev_crs |
column[5] in D1 record | date_completed |
column[6] in D1 record | earned_credit_value |
column[7] in D1 record | final_mark |
column[8] in D1 record | course_delivery_type |
column[9] in D1 record | credit_type |
column[1] + column[2] + column[3] + column[4] + column[8] + (column[5] (formatted to YYYY-MM-DD)) + column[6] + column[7] in D1 | key |
Import Extract #029: Table ON_SHSM_Crs_Enrols
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in D2 record | enrollment_key |
column[2] in D2 record | ministry_dfnd_crs |
column[3] in D2 record | institution_type |
column[4] in D2 record | local_dev_crs |
column[5] in D2 record | course_end |
column[6] in D2 record | earned_credit_value |
column[7] in D2 record | final_mark |
column[8] in D2 record | course_delivery_type |
column[1] + column[2] + column[3] + column[4] + column[8] + (column[5] (formatted to YYYY-MM-DD)) + column[6] + column[7] in D2 | key |
Import Extract #029: Table ON_SHSM_Crs_Oth_Creds
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in D3 record | enrollment_key |
column[2] in D3 record | ministry_dfnd_crs |
column[3] in D3 record | institution_type |
column[4] in D3 record | local_dev_crs |
column[5] in D3 record | crs_completed |
column[6] in D3 record | earned_credit |
column[7] in D3 record | final_mark |
column[8] in D3 record | credit_type |
column[1] + column[2] + column[3] + column[4] + (column[5] (formatted to YYYY-MM-DD)) + column[6] + column[7] in D3 | key |
Import Extract #032: Table ON_Suspend_Expulsions
Source | Destination |
|---|---|
column[1] in H2 record | alternate_school_number |
column[1] in DT record | incident_key |
column[2] in DT record | reference_num |
column[3] in DT record | enrollment_key |
column[4] in DT record | program_code |
column[5] in DT record | program_status |
column[7] in DT record | non_academic_code |
column[1] + column[3] in DT | key |
Import Extract #038: Table ON_Transition_Dates
| Source | Destination |
|---|---|
| column[1] in H2 record | BSID |
| column[1] in DT record | enrollment_key |
| column[2] in DT record | admission_date |
| column[3] in DT record | discharge_date |
| column[1] + column[2] in DT | key |