Pending Area Extract (PAE) Tables

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[53] in DT record	Community Involvement Hours Waived Flag
column[54] in DT record	OYAP-FAST Flag
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[5] in DT record	pre-population_flag
column[6] in DT record	level_support_type
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

Import Extract #039: Table ON_Early_Reading_Screening

Source	Destination
Column[1] in H2 record	BSID
Column[1] in D1 record	enrollment_key
Column[2] in D1 record	grade_type
Column[3] in D1 record	student_status
Column[4] in D1 record	ers_completed
Column[5] in D1 record	ers_completed_date
Column[6] in D1 record	benchmark_met
Column[1] in D1 + Column[2] in D1 + Column[5] in D1	key

Import Extract #040: Table ON_OYAP_Fast_Program

Source	Destination
Column[1] in H2	bsid
Column[1] in D1	enrollment_key
Column[2] in D1	grade_type
Column[3] in D1	number_of_credits
Column[4] in D1	trade_program_type_code
Column[5] in D1	trade_program_type_desc
Column[6] in D1	all_requirements_met_date Format: YYYY/MM
Column[1] in H2	Column[1] in H2 + Column[1] in D1 + Column[4] in D1 (BSID + enrollment_key + trade_program_type_code)