Loading SADS School Data into the Ministry Client

This is one of the steps the Ministry Client performs during the School Synchronization with SADS process.

As the School data structure between SADS and PASI are different, the data loaded from SADS is transformed to match the structure of the data within PASI.

SADS Data Structures for School Data PASI Data Structures for School Data
500500

Base School Information Transformation Rules

This information is used to create the base School entity.

School Code

This field is populated with the value of the org_code attribute from the Org table in SADS.

Phone Number

This field is populated with the value of the tel_item attribute from the OrgTel table in SADS contained in the record meeting the following criteria:

  • telty_cd is ‘TL’
  • eff_end_dt is the End of Time

Fax Number

This field is populated with the value of the tel_item attribute from the OrgTel table in SADS contained in the record meeting the following criteria:

  • telty_cd is ‘FC’
  • eff_end_dt is the End of Time

Email Address

This field is populated with the value of the tel_item attribute from the OrgTel table in SADS contained in the record meeting the following criteria:

  • telty_cd is ‘EM’
  • eff_end_dt is the End of Time

Source Version

This field is populated with the value of the lst_mod_ts attribute from the Org table in SADS where org_type is ’S’.

Organization Name

Each school is populated with a set of names based on the names available within SADS contained within the OrgNm table in SADS. An Organization Name is created for each associated OrgNm record in SADS that has an eff_end_dt greater than or equal to 2009-01-01.

Name

The Name field is populated with the value of the Corporate Name (contained in the orgnm_corp_nm attribute) if populated within SADS. If a Corporate Name is not defined within SADS (i.e. is null, an empty string, or a string with just spaces), then the school’s Legal Name (contained in the orgnm_legal_nm attribute) is used.

Effective Date

The Effective Date field is populated with the value of the eff_start_dt attribute.

School Authority Association

Each school is associated with a School Authority, and this association may change over time. To establish this association, records from the OrgRel table are used. The relationship between a school and an authority may be defined using one of two Relationship Types (contained within OrgRel.orgrty_cd). The relationship type is used to describe the relationship between two organizations, defined by the OrgRel.org_From_id and the OrgRel.org_To_id.

  • A Relationship Type of AB is used to define the relationship from a school to an authority, i.e. that the school (the From organization) is governed by the authority (the To organization).
  • A Relation Type of BA is used to define the inverse relationship between an authority and a school. I.e. that the authority (the From organization) is the governing organization for the school (the To organization).

Therefore, every OrgRel record with a type of AB or BA, and an eff_end_dt >= 2009-01-01 is considered when loading the school authority association from SADS. Note, that as the relationship can be defined in two different directions (and sometimes the relationship is defined in both directions), only 1 of the records will actually be used to ensure that duplicate School Authority Association records are not loaded.

School Authority Code

The School Authority code is populated based on the value of org_To_id when the orgrty_cd is AB, and is populated based on the value of org_From_id when the orgrty_cd is BA. The Organization ID is used to determine the School Authority Code by using a join to the Org table to determine the org_code.

Effective Date

The Effective Date is populated using the value contained in OrgRel.eff_start_dt.

Organization Status

Each school is populated with a set of Organization Status records based on the records within SADS contained within the OrgStat table in SADS. An Organization Status is created for each associated OrgStat record in SADS that has an eff_end_dt greater than or equal to 2009-01-01 as long as the most recent AudJnl record (based on aj_aud_upd_dt) meeting the following criteria, does not have an aj_op value of D:

  • an aj_table_nm of OrgStat
  • an aj_parm_3 equal to the value of orgst_id in the OrgStat record

Is Active

The Is Active field is populated based on the value of the orgstty_cd attribute.

  • If the value is A, then Is Active is populated as True.
  • If the value is anything other than A, Is Active is populated as False.

Effective Date

The Effective Date is populated with the value of the eff_start_dt attribute.

School Classification

Each school is populated with a set of School Classification records based on the records within SADS contained within the OrgSchCls table in SADS. A School Classification record is created for each associated OrgSchCls record in SADS that has an eff_end_dt greater than or equal to 2009-01-01.

Classification Type

This field is populated with the value of the schcl_cd attribute.

Effective Date

This field is populated with the value of the eff_start_dt attribute.

Mailing Address

A single Mailing Address record will be created as an Address in PASI based on the record in the OrgAddr table meeting the following criteria:

  • eff_end_dt equal to the End of Time, and
  • addrty_cd has a value of ML.

The details of the address come from the associated Addr table in SADS.

Address Type

The field is populated with the value Mailing.

Street

This field is populated based on the value of addr_ln_1 and addr_ln_2.

  • If addr_ln_1 is not populated (i.e. is null, an empty string, or a string with just spaces), then Street is populated with the value of addr_ln_2.
  • If addr_ln_2 is not populated, then Street is populated with the value of addr_ln_1.
  • If both addr_ln_1 and addr_ln_2 are populated within SADS, then Street is populated with the concatenation of addr_ln_1, a Carriage Return and Line Feed, and addr_ln_2.

City

This field is populated with the value of the muni_nm attribute.

State Province

This field is populated with the value of the prov_cd attribute.

Postal Code

This field is populated with the value of the addr_pcd attribute.

Country

This field is populated based on the value of the addr_cntry attribute. If the addr_cntry is a value representing Canada (i.e. CAND, CANA, or CAN) then Country is populated with the value Canada. If the addr_cntry field is any other value, Country is populated with the value of addr_cntry.

Effective Date

This field is populated with the value of eff_start_dt attribute.

Expiry Date

This field is populated with a Min Date value.

Delivery Address

A single Delivery Address record will be created as an Address is PASI based on the record in the OrgAddr table meeting the following criteria:

  • eff_end_dt equal to the End of Time, and
  • addrty_cd has a value of DL.

The address details are populated in the same fashion as the Mailing Address, except for Address Type which is populated with a value of Delivery.