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

As the School Calendar Year (Operating Schedule) 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 Calendar Year Data	PASI Data Structures for School Calendar Year Data

At most, one School Calendar Year Info Set record will be created for each school, for each school year. A School Calendar Year Info Set is created for each school that has at least one qualifying OpYrSched record as defined below.

The school code is populated with the value of org_code from the Org table.

The school year is populated with the value of opyrch_sch_yr from the OpYrSched table.

The maximum lst_mod_ts of the associated OpYrSched, AudJnl, OpYrGrOff information.

Associated AudJnl data includes data where aj_tbl_nm is OpYrSched or OpYrGrOff, aj_parm_1 is the associated org_id, aj_parm_2 is the associated opyrsh_sch_yr, and aj_op is ‘D’.

The reference ID is a guid calculated by the Ministry Client using schoolCode, schoolYear, calendarType.

A School Calendar Year Info record is created for each OpYrSched record with an opyrsh_chr_yr of 2009 or greater 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 OpYrSched
• an aj_parm_1 equal to the value of org_id in the OpYrSched record
• an aj_parm_2 equal to the value of opyrsh_sch_yr in the OpYrSched record
• an aj_parm_3 equal to the value of shdty_cd in the OpYrSched record

The reference ID is a guid calculated by the Ministry Client using schoolCode, schoolYear, calendarType.

Calendar Type is populated with the value of shdty_cd from the OpYrSched table.

Opening Date is populated with the value of opyr_open_dt from the OpYrSched table.

Closing Date is populated with the value of opyr_close_dt from the OpYrSched table.

In SADS, School Grade Offerings (located in the OpYrGrOff table) are associated directly to the organization. The only relationship between the Grade Offering and the Operating Year Schedule (located in the OpYrSched) table, is the School and the School Year.

In PASI, the School Grade Offerings are attached directly to a School Calendar Year Info record, and the School Calendar Year Info record is attached to the School Calendar Year Info Set record which identifies the School Year.

Therefore, for each School Grade Offering record in SADS (that does not have a Deletion Audit Journal record in the AudJnl table), a record will be created and associated to each School Calendar Year Info record in PASI, for the corresponding School Year.

Mapping for School Grade Offering

In other words, a School Grade Offering record is created:

• On each School Calendar Year Info record
  • With a parent School Calendar Year Info Set with a School Year equal to the oygo_sch_yr on the OpYrGrOff record
• For each OpYrGrOff record
• 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 OpYrGrOff
  • an aj_parm_1 equal to the value of org_id in the OpYrGrOff record
  • an aj_parm_2 equal to the value of oygo_sch_yr in the OpYrGrOff record
  • an aj_parm_3 equal to the value of offty_cd in the OpYrGrOff record
  • an aj_parm_2 equal to the value of grty_cd in the OpYrGrOff record

Grade Level is populated with the value contained in grty_cd from the OpYrGrOff table.

Programming Type is populated with the value contained in offty_cd from the OpYrGrOff table.

In SADS, Program Offerings (located in the OrgSchPgm table) are associated directly to the organization. The relationship between the Grade Offering and the Operating Year Schedule (located in the OpYrSched) table, is determined by mapping the dates on the Program Offering record to the dates on the Operating Year Schedule. The Program Offering record is mapped if it is effective for any period of time between the opening date and the closing date on the operating year schedule.

In PASI, the Program Offerings are attached directly to a School Calendar Year Info record, and the School Calendar Year Info record is attached to the School Calendar Year Info Set record which identifies the School Year.

Therefore, for each Program Offering record in SADS (that does not have a Deletion Audit Journal record in the AudJnl table), a record will be created and associated to each School Calendar Year Info record in PASI, where the effective periods of the records overlap.

Mapping for School Program Offerings

In other words, a Program Offering record is created:

• On each School Calendar Year Info record that
  • has an Opening Date that is less than or equal to the eff_end_dt of OrgSchPgm record, and
  • has a Closing Date that is greater than or equal to the eff_start_dt of the OrgSchPgm record
• For each OrgSchPgm record
  • with an eff_end_dt that is 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 OrgSchPgm
  • an aj_parm_1 equal to the value of orgsp_id

Program Type is populated with the value of schpgmty_cd from the OrgSchPgm table.