Search Results igs_ps_ofr_pat_note

Overview

The IGS_PS_OFR_PAT_NOTE table is a core data object within the Oracle E-Business Suite Student System (IGS) module. It functions as a junction table that establishes and manages the relationship between a Program Offering Pattern and its associated descriptive notes. A Program Offering Pattern defines the specific temporal and logistical structure of a course offering, including its calendar type, location, and attendance mode. This table enables administrators to attach multiple contextual notes to these complex patterns, providing critical supplemental information for academic planning, student advisement, and administrative reporting. Its existence is central to maintaining detailed, auditable documentation directly linked to the operational definition of a course offering.

Key Information Stored

The table's structure is defined by a composite primary key and several foreign key relationships. The primary key uniquely identifies a specific note attached to a specific offering pattern and consists of: COURSE_CD, VERSION_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE, and REFERENCE_NUMBER. The REFERENCE_NUMBER column is the direct link to the note's textual content stored in the IGS_GE_NOTE table. Other critical columns include CRS_NOTE_TYPE, which classifies the note (e.g., administrative, prerequisite, fee information) via a foreign key to IGS_PS_NOTE_TYPE_ALL, and COP_ID, which provides an alternative foreign key reference to the parent IGS_PS_OFR_PAT table.

Common Use Cases and Queries

This table is primarily accessed for retrieving all notes associated with a particular course offering pattern for display in student-facing catalogs or internal administrative screens. A common reporting use case involves auditing note coverage across all active offerings. Sample SQL to fetch notes for a specific pattern would join this table to IGS_GE_NOTE for the note text and IGS_PS_NOTE_TYPE_ALL for the type description.

SELECT ntype.note_type, ge_note.note_text
FROM igs.igs_ps_ofr_pat_note pat_note,
     igs.igs_ge_note ge_note,
     igs.igs_ps_note_type_all ntype
WHERE pat_note.course_cd = 'MATH101'
  AND pat_note.cal_type = 'SEMESTER'
  AND pat_note.reference_number = ge_note.reference_number
  AND pat_note.crs_note_type = ntype.note_type;

Related Objects

The table maintains documented foreign key relationships with several core Student System tables, ensuring referential integrity:

• IGS_PS_OFR_PAT: The parent table defining the Program Offering Pattern. The relationship is enforced via columns (COURSE_CD, VERSION_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE) and alternatively via the COP_ID column.
• IGS_GE_NOTE: Stores the actual note text. Joined via the REFERENCE_NUMBER column.
• IGS_PS_NOTE_TYPE_ALL: Provides the valid list and description of note types (e.g., 'PREREQ', 'FEE'). Joined via the CRS_NOTE_TYPE column.