Online Registration Integration Setup

Introduction

With the implementation of PASI End Point 2020 the below data elements have been revised and updated, source applications submitting student information might require an update to their mappings so the proper information is submitted to PASI.

Responsible School Information

New data element, this is optional information that can be submitted during enrolment of new students. The Alberta ministry of Education is asking schools to inform if they are taking responsibility for a student, if so effective date, school information and transition reason must be provided. Alternatively a school might inform no school is responsible for a specific student (typically used at student graduation time) or the responsibility status is unknown (student leaving a school but current school does not know of a new responsible school). Submissions of similar consecutive responsible school records for the same school are rejected by PASI. i.e. If a school is currently reported as responsible for a student a new record indicating the same school is responsible for the same student on a future date will be rejected.

New table: S_AB_STU_RESPONSIBLE_SCHOOL_C

Column Name	Description
ID	Unique row ID, value assigned by internal sequence generator
STUDENTSDCID	Student DCID, maximum 19 digits, mandatory
EFFECTIVEDATE	Responsible school record effective date. Each effective date must be unique, duplicates are not allowed by PASI. Also if a school is already responsible for a student no consecutive record of the same type is allowed, i.e. if school 1234 is responsible since 01/01/2020 then a new record with effective date 01/01/2021 making school 1234 responsible for the same student will be rejected by PASI.
HASRESPONSIBLESCHOOL	Yes, No or Unknown, mandatory. This field indicates if on the specified effective date there is a responsible school for the student (Yes) or no school is responsible (No, normally used for graduating students) or the status is Unknown (students leaving current district)
SCHOOLCODE	PowerSchool school code, 4 characters or empty, optional field. The school taking responsibility for the student
TRANSITIONREASON	Reason code, maximum 100 characters, mandatory. Values are provided by PASI and stored in tables AB_CODE_VALUE and AB_CODE_VALUE_RELATIONSHIP. The below query extracts permitted values by responsible school type (Yes, No, Unknown): `1select cvr.parent_code, code_text, description 2from ab_code_value cv inner join ab_code_value_relationship cvr on cv.code_text = cvr.child_code 3where cv.class_name = 'TransitionReason' 4and cvr.parent_class = 'HasResponsibleSchool' and cvr.child_class= 'TransitionReason' 5order by cvr.parent_code, cv.display_seq;` Currently the below codes, per responsible school status, are available: No Grade No Programming No Expulsion No Moved No CompletedK12 No Choice No HomeEd No NotProvided No Deceased No Employment No Other Unknown Expulsion Unknown Moved Unknown NotProvided Unknown Other Unknown ToBeDetermined Yes New Yes Grade Yes Programming Yes Expulsion Yes Moved Yes Other
SCHOOLNAME	If no school code is provided school name can be provided, up to 225 characters, however in this case SCHOOLCITY and SCHOOLCOUNTRY and/or SCHOOLSTATEPROVINCE must also be provided and must be out of Alberta
SCHOOLCITY	Mandatory if SCHOOLNAME is provided and SCHOOLCOUNTRY is Canada. Up to 60 characters are allowed
SCHOOLSTATEPROVINCE	Mandatory if SCHOOLNAME is provided and SCHOOLCOUNTRY is Canada. Value can’t be Alberta (because in this case school code is expected). Up to 20 characters are allowed. Permitted values retrieved from PowerSchool core service codesetService (method findByCodetypeAndParentcodesetid("State",38) if country is ‘Canada'; findByCodetypeAndParentcodesetid("State",37) if country is 'United States’). Alternatively the below query extracts provinces from the database: `1select displayvalue 2from codeset 3where codetype = 'State' 4and parentcodesetid = '38';`
SCHOOLCOUNTRY	Mandatory if SCHOOLNAME is provided. Up to 60 characters are allowed. Permitted values retrieved from PowerSchool core service codesetService (method findByCodetype("Country")). Alternatively the below query extracts countries from the database: `1select displayvalue 2from codeset 3where codetype = 'Country';`
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date

Citizenship Status

With end point 2020 multiple student citizenship status records are supported so instead of a student extension table a new child table has been introduced. Citizenship status columns are still available in the student extended table, S_AB_STU_X however they are deprecated, on the upgrade date if citizenship status information is available for a student a corresponding record is created in the new child table, S_AB_STU_CITIZENSHIP_C, at this point data present in the extended table is no longer used.

New table: S_AB_STU_CITIZENSHIP_C (table S_AB_STU_X is no longer used for citizenship status information)

Column Name	Description
ID	Unique row ID, value assigned by internal sequence generator
STUDENTSDCID	Student DCID, mandatory field
CITIZENSHIPSTATUS	Citizenship status code, 1 character, mandatory. Values are provided by PASI and stored in table AB_CODE_VALUE. The below query extracts permitted values: `1select code_text, short_desc 2from ab_code_value 3where class_name = 'CitizenshipStatus' 4order by display_seq, code_text;` This is a mandatory field
COUNTRYOFCITIZENSHIP	Citizenship country code, maximum 100 characters. Not available if citizenship status is 1 (Canadian Citizen) otherwise optional. Country ISO code label as available in table COUNTRYISOCODELU. . The below query extracts permitted values: `1select REPLACE(TRIM(INITCAP( DISPLAYLABEL )),' ',' ') as country 2from COUNTRYISOCODELU;`
EFFECTIVEDATE	Citizenship status effective date. Duplicates are not allowed. This field is mandatory.
EXPIRYDATE	Citizenship status expiry date, available when citizenship status is 2 (Permanent Resident), 5 (Temporary Resident) or 9 (Other/Unknown). Mandatory when citizenship status is 5 (Temporary Resident)
GUARDIANCITIZENSHIPSTATUS	Guardian citizenship status code, allowed values are the same as for CITIZENSHIPSTATUS (above). This is an optional field
GUARDIANREFID	Guardian PASI Ref ID. 11 digits. Optional field used to indicate which PASI guardian is linked to the student citizenship status record. For brand new students this value should be null. Once the student has an Alberta Student Number (ASN) PASI makes guardian information available, it is stored in tables AB_STUDENT_PARENT_GUARDIANINFO and AB_PARENT_GUARDIAN_NAME. The below query extracts guardians available for a particular student: `1select guardian.refid, name.lastname, name.firstname, name.middlename 2from AB_STUDENT_PARENT_GUARDIANINFO guardian inner join AB_PARENT_GUARDIAN_NAME name on guardian.ab_student_guardian_infoid = name.student_guardian_infoid 3inner join AB_STUDENT_INFO info on info.AB_STUDENT_INFOid = guardian.student_infoid 4where info.state_province_id=:#theASN 5`
ISPREFERRED	This flag indicates if a citizenship status record is primary (1) or not (0). If a student has only one citizenship status record this flag must be set to 1, if more than one citizenship status record exist for the same student then the primary record must have a value of 1 and all other records must have a value of 0
ORGCODE	PowerSchool school number, normally 4 digits (but limit is 11), this field is mandatory.
REFID	PASI identification, 11 digits. Must be null for new records, once assigned this value must not be updated
CREATEDBYORGCODE	PASI organization code, 6 characters. Must be null for new records, once assigned this value must not be updated
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date

Student Addresses

With end point 2020 multiple student addresses are supported so in addition to the student extension table a new child table has been introduced. The primary address is always stored in the student extended table (some fields are also stored in the students table), all other addresses are stored in the new child table.

If a student has only one address then that address is the primary address and is stored in the student extension table, S_AB_STU_X; if more than one address is available then all other addresses are stored in the addresses child table, S_STU_ADDRESSES_C.

Updated table: S_AB_STU_X (relevant address related fields)

Column Name	Description
STUDENTSDCID	Student DCID, mandatory field
MAILINGCOUNTRY	Address country code, maximum 60 characters. Permitted values retrieved from PowerSchool core service codesetService (method findByCodetype("Country")). Most usual values are Canada and Unites States. This is a mandatory field
MAILINGPROVINCE	Address province, maximum 50 characters. This filed is only used for international addresses (free text). For Canadian and US address core PowerSchool stores province or state code in STUDENTS.MAILING_STATE column
ADDRESSEXPIRYDATEOVERRIDE	Address expiry date, if expiry date is provided by user it is stored in this column which is optional
ADDRESSTYPE (new column)	Address type code, maximum 50 characters, mandatory. Values are provided by PASI and stored in table AB_CODE_VALUE. The below query extracts permitted values: `1select code_text, short_desc 2from ab_code_value 3where class_name = 'AddressType' 4order by display_seq, code_text;` Current values include: Delivery - Delivery Address Mailing - Mailing Address (most frequent address type) Physical - Physical Address
ADDRESSFORMAT (new column)	Address format code, maximum 50 characters, mandatory. Values are provided by PASI and stored in table AB_CODE_VALUE. The below query extracts permitted values: `1select code_text, short_desc 2from ab_code_value 3where class_name = 'AddressFormat' 4order by display_seq, code_text;` Current values include: Mail-CA - Canadian Mailing Address (most frequent address format in Alberta) Mail-US - United States Mailing Address Mail-International - International Mailing Address RuralAlbertaAddress - Rural Alberta Address ATS - Alberta Township System
ADDRESSSTREET (new column)	The street part of the address, maximum 140 characters.
ADDRESSLATITUDE (new column)	Used if address format is ATS and geolocation is available, Null by default. Maximum 25 characters. Currently not in use
ADDRESSLONGITUDE (new column)	Used if address format is ATS and geolocation is available, Null by default. Maximum 25 characters. Currently not in use
ADDRESSORGCODE (new column)	PowerSchool school number, normally 4 digits (but limit is 11), this field is mandatory
ADDRESSREFID (new column)	PASI identification, 11 digits. Must be null for new records, once assigned this value must not be updated
ADDRESSWHOMODIFIED (new column)	User who modified the record due to address changes only (needed because the record holds other student data elements)
ADDRESSWHENMODIFIED (new column)	Address modification date
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date

New table: S_STU_ADDRESSES_C (Child table storing non primary student addresses):

Column Name	Description
ID	Unique row ID, value assigned by internal sequence generator
STUDENTSDCID	Student DCID, mandatory field
ADDRESSFORMAT	Address format code, maximum 50 characters, mandatory. Values are provided by PASI and stored in table AB_CODE_VALUE. The below query extracts permitted values: `1select code_text, short_desc 2from ab_code_value 3where class_name = 'AddressFormat' 4order by display_seq, code_text;` Currently the below values are available: Mail-CA - Canadian Mailing Address Mail-US - United States Mailing Address Mail-International - International Mailing Address RuralAlbertaAddress - Rural Alberta Address ATS - Alberta Township System
ADDRESSTYPE	Address type code, maximum 50 characters, mandatory. Values are provided by PASI and stored in table AB_CODE_VALUE. The below query extracts permitted values: `1select code_text, short_desc 2from ab_code_value 3where class_name = 'AddressType' 4order by display_seq, code_text;` Currently the below values are available: Mailing - Mailing Address Physical - Physical Address Physical addresses are only permitted if a mailing address is also provided.
CITY	City name, maximum 60 characters, mandatory field
COUNTRY	Address country code, maximum 60 characters, mandatory. Permitted values retrieved from PowerSchool core service codesetService (method findByCodetype("Country")). Most usual values are ‘Canada’ and ‘United States’.
EFFECTIVEDATE	Address effective date, mandatory
EXPIRYDATE	PASI Address expiry date, Must be null for new records, once assigned this value must not be updated
EXPIRYDATEOVERRIDE	Address expiry date, optional
LATITUDE	Used if address format is ATS and geolocation is available, Null by default. Maximum 25 characters
LONGITUDE	Used if address format is ATS and geolocation is available, Null by default. Maximum 25 characters
ORGCODE	PowerSchool school number, normally 4 digits (but limit is 11), this field is mandatory.
POSTALCODE	Address postal code, maximum 10 characters (column allows for 15 but for compatibility with PowerSchool core only 10 characters should be stored). Mandatory for Canadian and US addresses
REFID	PASI identification, 11 digits. Must be null for new records, once assigned this value must not be updated
STATEPROVINCE	Address province or state, maximum 20 characters. For Canadian and US address province or state code are stored, mandatory. Free text otherwise.
STREET	Street, maximum 100 characters, mandatory field
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date

Student Phones

With end point 2020 multiple student phones are supported so in addition to the student extension table a new child table has been introduced. The primary phone is always stored in the student extended table (some fields are also stored in the students table), all other phones are stored in the new child table.

If a student has only one phone then that phone is the primary phone and is stored in the student extension table, S_AB_STU_X; if more than one phone is available then all other phones are stored in the phones child table, S_STU_PHONE_C.

Updated table: S_AB_STU_X (relevant phone related fields)

Column Name	Description
STUDENTSDCID	Student DCID, mandatory field
PHONEEXT	Phone extension, maximum 7 characters, optional.
PHONETYPE	Phone type code, maximum 10 characters, mandatory field. Values are provided by PASI and stored in table AB_CODE_VALUE. The below query extracts permitted values: `1select code_text, short_desc 2from ab_code_value 3where class_name = 'PhoneType' 4order by display_seq, code_text;` Current values include: Home - Home Phone Mobile - Mobile Phone
ISLISTABLE	This mandatory flag indicates if the phone is listed in public phone directories. Permitted values are Y (Yes), N (No) and U (Unknown)
PHONEEFFECTIVEDT	Phone effective date, mandatory
PHONEEXPIRYDT	Phone expiry date, optional
PHONEORGCODE (new column)	PowerSchool school number, normally 4 digits (but limit is 11), this field is mandatory
PHONEREFID (new column)	PASI identification, 11 digits. Must be null for new records, once assigned this value must not be updated
PHONEWHOMODIFIED (new column)	User who modified the record due to phone changes only (needed because the record holds other student data elements)
PHONEWHENMODIFIED (new column)	Phone modification date
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date

New table: S_STU_PHONE_C (Child table storing non primary student phones):

Column Name	Description
ID	Unique row ID, value assigned by internal sequence generator
STUDENTSDCID	Student DCID, mandatory field
PHONENUMBER	Phone number, maximum 20 characters, mandatory
EXTENSION	Phone extension, maximum 7 characters, optional
PHONETYPE	Phone type code, maximum 10 characters, mandatory field. Values are provided by PASI and stored in table AB_CODE_VALUE. The below query extracts permitted values: `1select code_text, short_desc 2from ab_code_value 3where class_name = 'PhoneType' 4order by display_seq, code_text;` Current values include: Home - Home Phone Mobile - Mobile Phone
ISLISTABLE	This mandatory flag indicates if the phone is listed in public phone directories. Permitted values are Y (Yes), N (No) and U (Unknown)
EFFECTIVEDATE	Phone effective date, mandatory
EXPIRYDATE	Phone expiry date, optional
ORGCODE	PowerSchool school number, normally 4 digits (but limit is 11), this field is mandatory
REFID	PASI identification, 11 digits. Must be null for new records, once assigned this value must not be updated
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date

Update student address and phone

While inserting records in S_AB_STU_X table for new address and Phone number, the body of the request should include new values for phone and address, fields addresswhomodified and phonewhomodified must include a value ‘PS’.

8:32

While updating records in S_AB_STU_X table for address and Phone number, the body of the request should include the updated values for phone and address, the request must also include a value of '"PowerSchool Registration Signature"' for addresswhomodified and phonewhomodified as well as a random value ending with _2 for record status.

School Enrolment

Three new fields have been introduced for end point 2020, Programming Type, Programming Timeframe and Exit Date Type. For current enrolments, extended table S_AB_STU_X has been modified adding columns to store the new fields; similar changes were made for previous enrolments to table S_AB_REN_X.

Updated table: S_AB_STU_X

Column Name	Description
EXITDATETYPE	Mandatory, Usually, especially for new enrolments, the value should be ‘CompletionDate’ but it can also be ‘WithdrawalDate’
PROGRAMMINGTYPE	Mandatory, for most enrolments ‘Regular’ is the default value but ‘SummerSchool’ and ‘ContinuingEducation’ are also permitted
PROGRAMMINGTIMEFRAME	Mandatory, usually ‘Day’ but it can also be ‘Night’

Updated table: S_AB_REN_X

Column Name	Description
EXITDATETYPE	Mandatory, permitted values are ‘CompletionDate’ and ‘WithdrawalDate’
PROGRAMMINGTYPE	Mandatory, permitted values are ‘Regular’ , 'SummerSchool’ and ‘ContinuingEducation’
PROGRAMMINGTIMEFRAME	Mandatory, permitted values are ‘Day’ and 'Night’

Student Email Addresses

With end point 2020 multiple student email addresses are supported so in addition to the student extension table a new child table has been introduced. The primary email is always stored in the student extended table (some fields are also stored in the students table), all other email addresses are stored in the new child table.

If a student has only one email address then that email address is the primary email address and is stored in the student extension table, S_AB_STU_X; if more than one email address is available then all other email addresses are stored in the email address child table, S_STU_EMAILADRESS_C.

Updated table: S_AB_STU_X (relevant phone related fields)

Column Name	Description
STUDENTSDCID	Student DCID, mandatory field
EMAILADDRESSORGCODE (new column)	PowerSchool school number, normally 4 digits (but limit is 11), this field is mandatory
EMAIL ADDRESS (new column)	Email Address, maximum 255 characters, mandatory. Must be null for new records, once assigned this value must not be updated
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date

New table: S_STU_EMAILADDRESS_C (Child table storing non primary student email addresses):

Column Name	Description
ID	Unique row ID, value assigned by internal sequence generator
STUDENTSDCID	Student DCID, mandatory field
EMAIL ADDRESS	Email Address, maximum 255 characters, mandatory
IS ACTIVE	True if the email address is set active in PASI
TYPE	True of Email Address is primary
ORGCODE	PowerSchool school number, normally 4 digits (but limit is 11), this field is mandatory
REFID	PASI identification, 11 digits. Must be null for new records, once assigned this value must not be updated
WHOCREATED	User who created the record
WHENCREATED	Creation date
WHOMODIFIED	User who modified the record
WHENMODIFIED	Modification date