Table Layouts

On this page:

The following details describe eSchoolPlus database tables used by the OCR Reports.

Table REGTB_GROUP_USED_FOR

Student Groups Used For Codes

Column	Data Type	Required
District	Int	Y
Code	Varchar(50)	Y
Description	Varchar(255)	Y
Active	Char(1)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table REG_GROUP_USED_FOR

Connection between your local Student Group Definition records (Group_Code) and the Group Used For codes provided by PowerSchool (Used_for_code).

Column	Data Type	Required
District	Int	Y
Group_Code	Varchar(50)	Y
Used_for_code	Varchar(50)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table REG_GROUP_HDR

Student Search Groups Header Record

Column	Data Type	Required
District	Int	Y
Group_Code	Varchar(50)	Y
Description	Varchar(255)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table SPI_SEARCH_FAV

Used for all eSchoolPlus searches. For OCR, the Login_ID and Search_type will always be _ESP_GROUP.

Column	Data Type	Required
District	Int	Y
Login_ID	Char(20)	Y
Search_type	Char(20)	Y
Search_Number	Smallint	Y
Search_Name	Varchar(50)	N
Description	Varchar(2048)	Y
Last_search	Char(1)	Y
Grouping_mask	Varchar (512)	N
Category	Char (5)	N
Publish	Bit	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table SPI_USER_SEARCH

Used for all eSchoolPlus searches. For OCR, the Login_ID and Search_type will always be _ESP_GROUP.

Column	Data Type	Required
District	Int	Y
Login_ID	Char(20)	Y
Search_type	Char(20)	Y
Search_Number	Smallint	Y
Sequence_Num	Smallint	Y
And_or_Flag	Smallint	Y
Table_name	Char(128)	Y
Screen_type	Char(2)	N
Screen_number	Smallint	N
Program_id	Char(5)	N
Column_name	Char(128)	Y
Field_number	Smallint	N
Operator	Smallint	Y
Search_value1	Varchar(255)	Y
Search_value2	Varchar(255)	N
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_DISTDEF_SCREENS

Stores the location of the Federal Course Information page.

Column	Data Type	Required
District	Int	Y
Screen_used_for	Varchar(50)	Y
Screen_type	Char(1)	Y
Screen_number	Smallint	Y
Change_date_time	Datetime	Y
Change_uid	Char(20)	Y

Table STATE_OCR_BLDG_CFG

OCR Building Setup

Column	Data Type	Required
District	Int	Y
School_year	Smallint	Y
Building	Int	Y
Federal_code_equiv	Varchar(20)	Y
HS_age_in_ungraded	Char(1)	Y
Ungraded_detail	Char(1)	Y
Disability_school	Char(1)	Y
Charter_school	Char(1)	Y
Alternative_school	Char(1)	Y
Magnet_school	Char(1)	Y
Magnet_entire_school	Char(1)	Y
Alt_academic_students	Char(1)	Y
Alt_discipline_students	Char(1)	Y
Alt_other_students	Char(1)	Y
Ability_grouped_school	Char(1)	Y
Dual_enroll	Char(1)	Y
Ap_self_select	Char(1)	Y
Classroom_teacher_fte	Decimal(9,2)	Y
Licensed_teacher_fte	Decimal(9,2)	Y
Fte_teachers_notmeetstatereq	Decimal(9,2)	Y
Counselor_fte	Decimal(9,2)	Y
fte_psychologists	Decimal(9,2)	Y
fte_social_workers	Decimal(9,2)	Y
fte_nurses	Decimal(9,2)	Y
fte_security_guards	Decimal(9,2)	Y
fte_law_enforcement	Decimal(9,2)	Y
Intersch_athletics	Char(1)	Y
Intersch_sports_male	Smallint	Y
Intersch_sports_female	Smallint	Y
Intersch_teams_male	Smallint	Y
Intersch_teams_female	Smallint	Y
Intersch_partic_male	Smallint	Y
Intersch_partic_female	Smallint	Y
firearm_use	Char(1)	Y
homicide_deaths	Char(1)	Y
justice_facility	Char(1)	Y
justfac_num_days	Int	Y
justfac_hours_perweek	Int	Y
justfac_eduprog_less15	Int	Y
justfac_eduprog_15to30	Int	Y
justfac_eduprog_31to90	Int	Y
justfac_eduprog_91to180	Int	Y
justfac_eduprog_more180	Int	Y
law_enforce_off	Char(1)	Y
retained_use_fed_or_loc_grade_code	Char(1)	Y
internet_fiber	Char(1)	Y
internet_wifi	Char(1)	Y
internet_school_issued_device	Char(1)	Y
internet_student_owned_device	Char(1)	Y
internet_wifi_enabled_devices	Int	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y
dind_instruction_type	Char(1)
dind_virtual_type	Char(1)

Table STATE_OCR_BLDG_MARK_TYPE

Mark type information used to determine passing courses.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Building	Int	Y
Mark_type	Char(5)	Y
Mark_order	Smallint	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_BLDG_RET_EXCLUDED_CALENDAR

Calendar codes the OCR Reports option ignores when determining if a student counts as Retained for the School Form report.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Building	Int	Y
Calendar	Char(1)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DETAIL

OCR Civil Rights Data detail records.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Ocr_part	Smallint	Y
Student_id	Char(10)	Y
Record_type	Varchar(50)	Y
Building	Int	Y
Fed_grade	Char(2)	Y
Fed_race	Char(1)	Y
Gender	Char(1)	Y
Detail_record_count	Smallint	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DIST_ATT

This table is no longer used for OCR reporting in the current submission year. It stored attendance codes selected on the OCR District Setup's Miscellaneous tab.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Att_code	Char(5)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DIST_CFG

OCR District Setup (non-comments)

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Federal_code_equiv	Varchar(20)	Y
Enroll_date	Datetime	Y
Idea_date	Datetime	Y
Semester2_date	Datetime	Y
Year_start_date	Datetime	Y
Year_end_date	Datetime	Y
Race_category	Char(1)	Y
Ged_prep	Char(1)
Tot_pub_schools	Int
Tot_pub_membership	Decimal(9,3)
Tot_pub_served	Int
Tot_pub_waiting	Int
Desegregation_plan	Char(1)
PreK_age_none	Char(1)
PreK_age_2_stu_count	Int
PreK_age_3_stu_count	Int
PreK_age_4_stu_count	Int
PreK_age_5_stu_count	Int
Civ_rights_coord_gndr_id	Char(10)
Civ_rights_coord_gndr_phone	Char(10)
Civ_rights_coord_gndr_ext	Char(5)
Civ_rights_coord_race_id	Char(10)
Civ_rights_coord_race_phone	Char(10)
Civ_rights_coord_race_ext	Char(5)
Civ_rights_coord_dis_id	Char(10)
Civ_rights_coord_dis_phone	Char(10)
Civ_rights_coord_dis_ext	Char(5)
Har_pol_none	Char(1)
Har_pol_sex	Char(1)
Har_pol_dis	Char(1)
Har_pol_race	Char(1)
Har_pol_any	Char(1)	Y
Har_pol_weblink	Char(1)
Certified	Char(1)
Cert_name	Varchar(60)
Cert_title	Varchar(60)
Cert_phone	Varchar(14)
Cert_date	Datetime
Cert_auth	Char(1)
Cert_email	Varchar(70)
Att_view_type	Char(5)
Enroll_dist_edu_crs	Char(1)
Retention_policy	Char(1)	Y
Num_stu_non_lea	Smallint
Stu_disc_transfer	Char(1)
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DIST_COM

District/LEA Comments

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Comment_type	Varchar(50)	Y
Comment	Varchar(2048)
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DIST_EXP

This table is no longer used for OCR reporting in the current submission year. It stored expenditure inclusion and exclusion information from the OCR District Setup's Expenditures pane.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Expenditure_ID	Char(5)	Y
Expenditure_incl	smallint	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DIST_DISC

OCR District Setup – Discipline pane.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Disc_code_ID	Char(5)	Y
Code_or_subcode	Char(1)	Y
Disc_code	Char(5)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DIST_STU_DISC_XFER

OCR District Setup – Codes that indicate student was transferred for disciplinary reasons.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Reg_or_alt	Char(1)	Y
Code_value	Char(5)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_NON_STU_DET

OCR Building Setup – Calculated Non-Student Data.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Building	Int	Y
Count_type	Varchar(50)	Y
OCR_part	Char(1)	Y
Count_value	Varchar(9)
Override	Char(1)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_DIST_LTDB_TEST

OCR District Setup Testing pane.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
Test_type	Char(3)	Y
AP_Subject_code	Char(5)	Y
Test_code	Char(20)	Y
Test_level	Char(10)	Y
Test_form	Char(10)	Y
Subtest	Char(10)	Y
Score_code	Char(10)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_QUESTION

Stores the questions for the LEA and School Counts displayed on the OCR Summary Page.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
OCR_Part	Smallint	Y
Form_type	Char(1)	Y
Question_ID	Char(50)	Y
Record_type	Varchar(50)	Y
Question_order	Int	Y
Description	Varchar(255)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATE_OCR_SUMMARY

Stores data entered and saved on the OCR Summary Page.

Column	Data Type	Required
District	Int	Y
School_year	Int	Y
OCR_Part	Smallint	Y
Record_type	Varchar(50)	Y
Building	Int	Y
Fed_race	Char(1)	Y
Gender	Char(1)	Y
Question_ID	Char(50)	Y
Count	Int	Y
Override	Char(1)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATETB_AP_SUBJECT

Validation table for Federal AP Subject codes used to tie AP courses to AP tests.

Column	Data Type	Required
District	Int	Y
Code	Char(5)	Y
Description	Varchar(255)	Y
Active	Char(1)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATETB_OCR_COM_TYPE

Internal Comment Type validation table used to associate comments in the STATE_OCR_DIST_COM table with a field on the OCR District Setup page.

Column	Data Type	Required
District	Int	Y
Comment_type	Varchar(50)	Y
Description	Varchar(255)	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATETB_OCR_DISC_TYPE

Internal validation table used for the OCR District Setup – Discipline pane.

Column	Data Type	Required
District	Int	Y
Disc_Code_ID	Char(5)	Y
Description	Varchar(255)	Y
Incident_or_action	Char(1)	Y
Disc_code_order	Smallint	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y

Table STATETB_OCR_EXP_TYPE

This table is no longer used for OCR reporting in the current submission year. It is an internal validation table that was used for the OCR District Setup's Expenditures pane.

Column	Data Type	Required
District	Int	Y
Expenditure_ID	Char(5)	Y
Expenditure_order	Smallint	Y
Description	Varchar(255)	Y
Expenditure_type	Char(1)	Y
Ed_preferred	Smallint	Y
Change_date_time	Datetime	Y
Change_uid	Varchar(20)	Y