Search Results igs_ps_unit_ofr_pat_all




Overview
Explore in AI Chat

Overview

The table IGS_PS_UNIT_OFR_PAT_ALL is a core entity within the Oracle E-Business Suite Student System (IGS) for releases 12.1.1 and 12.2.2. It is a multi-organization (ALL) table that defines the specific offering pattern for a unit offering option. In the academic model, a unit offering option details how a particular unit (subject) is delivered within a given academic calendar. This table further refines that by storing the precise pattern or schedule instance (e.g., a specific semester or teaching period) within that calendar when the unit is actually offered. It serves as a critical junction point, linking unit definitions to calendar instances and enabling downstream processes like enrollment, assessment, and admissions.

Key Information Stored

The table's structure is defined by its composite primary key and its relationships to other core entities. The primary key uniquely identifies a single offering pattern instance and consists of: UNIT_CD (the unit code), VERSION_NUMBER (the unit version), CAL_TYPE (the calendar type, e.g., 'SEMESTER'), and CI_SEQUENCE_NUMBER (the sequence number identifying the specific calendar instance). Beyond the key columns, the table includes dates such as CI_START_DT and CI_END_DT, which are inherited from the linked calendar instance (IGS_CA_INST_ALL) and define the official start and end dates for this offering pattern. This structure ensures a unit version can be offered in multiple, distinct patterns across different calendar periods.

Common Use Cases and Queries

This table is central to queries determining when and how a unit is available. Common operational and reporting scenarios include generating unit offering timetables, validating student enrollment against available offerings, and supporting academic planning. A typical query might join this table to the unit offering (IGS_PS_UNIT_OFR) and calendar instance tables to list all unit offerings for an upcoming academic period.

SELECT uop.unit_cd,
       uop.version_number,
       uop.cal_type,
       uop.ci_sequence_number,
       ci.start_dt,
       ci.end_dt
FROM   igs_ps_unit_ofr_pat_all uop,
       igs_ca_inst_all ci
WHERE  uop.cal_type = ci.cal_type
AND    uop.ci_sequence_number = ci.sequence_number
AND    ci.cal_type = 'SEMESTER'
AND    ci.start_dt >= SYSDATE
ORDER BY ci.start_dt, uop.unit_cd;

Another critical use case is in assessment and grading systems, where mark sheets (IGS_AS_MARK_SHEET_ALL) and unit assessment items (IGS_AS_UNITASS_ITEM_ALL) are directly linked to a specific unit offering pattern to ensure results are recorded against the correct delivery instance.

Related Objects

The ETRM metadata shows IGS_PS_UNIT_OFR_PAT_ALL has extensive relationships, underscoring its integrative role. Key dependencies include:

  • Parent Tables: IGS_PS_UNIT_OFR (unit offering option) and IGS_CA_INST_ALL (calendar instance).
  • Child Tables: Several critical transactional tables reference it via foreign keys, including:

These relationships mandate that any data changes to a unit offering pattern must be carefully managed due to the potential cascading impact on admissions, assessment, and enrollment data.