Search Results igs_ps_unt_ofr_opt_n




Overview
Explore in AI Chat

Overview

The table IGS_PS_UNT_OFR_OPT_N is a core data object within the Oracle E-Business Suite (EBS) Student System (IGS), specifically in versions 12.1.1 and 12.2.2. As indicated by its description, it serves as a junction table designed to associate descriptive notes with specific unit offering options. A unit offering option defines the delivery details (such as location, calendar, and class) for an academic unit. This table enables the attachment of contextual notes—which could include administrative instructions, delivery specifics, or prerequisite clarifications—to these offerings. It is critical to note that the ETRM metadata explicitly classifies the IGS - Student System as "Obsolete," indicating this table is part of a legacy module. Furthermore, the documentation states "Not implemented in this database," which may refer to a specific reference instance, but the structural definition confirms its existence in the data model for relevant installations.

Key Information Stored

The table's primary key is a composite key, reflecting its role in uniquely identifying a note link for a specific unit offering option. The key columns are UNIT_CD, VERSION_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, LOCATION_CD, UNIT_CLASS, and REFERENCE_NUMBER. These first six columns collectively identify the precise unit offering option, aligning with the primary key of the IGS_PS_UNIT_OFR_OPT_ALL table. The REFERENCE_NUMBER column is a foreign key to the IGS_GE_NOTE table, which holds the actual note text. An additional critical column is CRS_NOTE_TYPE, which is a foreign key to IGS_PS_NOTE_TYPE_ALL, allowing notes to be categorized (e.g., administrative, academic, warning). The UOO_ID column, also a foreign key to IGS_PS_UNIT_OFR_OPT_ALL, provides an alternative, single-column identifier for the parent unit offering option.

Common Use Cases and Queries

The primary use case is retrieving all notes attached to a particular unit offering for display in academic catalogs, student enrollment systems, or administrative interfaces. For instance, an enrollment form might query for any mandatory notes related to a unit's lab schedule. A common reporting need is to list all unit offerings that have a specific type of note attached, such as all offerings with a "Health and Safety" note. Sample SQL to fetch notes for a given unit offering would join this table to the note type and the actual note text:

  • SELECT n.note_text, nt.note_type_name FROM igs_ps_unt_ofr_opt_n ln, igs_ge_note n, igs_ps_note_type_all nt WHERE ln.reference_number = n.reference_number AND ln.crs_note_type = nt.note_type AND ln.unit_cd = 'MATH101' AND ln.cal_type = 'SEM1';

Data maintenance involves inserting or deleting records in this table to attach or detach notes from unit offering options, typically via the application's user interface.

Related Objects

The table maintains defined foreign key relationships with several core tables in the Student System, as documented in the ETRM metadata:

  • IGS_PS_UNIT_OFR_OPT_ALL: The parent table for the unit offering option. The relationship is defined via two foreign key paths: one using the composite key (UNIT_CD, VERSION_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, LOCATION_CD, UNIT_CLASS) and another using the surrogate key (UOO_ID).
  • IGS_GE_NOTE: The master table storing the note text. Linked via the REFERENCE_NUMBER column.
  • IGS_PS_NOTE_TYPE_ALL: The table defining valid note types. Linked via the CRS_NOTE_TYPE column.

These relationships ensure referential integrity, guaranteeing that a note link always points to a valid unit offering option, a valid note type, and a valid note record.