Search Results igs_ps_ofr_opt_note_u1

Overview

The IGS_PS_OFR_OPT_NOTE table is a core data object within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically in the Oracle Student Management (OSM) product family under the IGS (iGrants) schema. It functions as the central repository for storing descriptive notes and comments associated with specific program offering options. A program offering option defines the precise delivery mode, location, and attendance pattern for an academic program (course) within a given academic calendar. This table enables institutions to attach contextual information—such as administrative notes, handbook entries, or special instructions—to these granular offerings, ensuring relevant details are maintained and accessible throughout the student lifecycle and administrative processes.

Key Information Stored

The table's structure combines identifiers for the program offering option with note metadata. The primary key is a composite of columns identifying the specific offering: COURSE_CD, VERSION_NUMBER, CAL_TYPE, LOCATION_CD, ATTENDANCE_MODE, and ATTENDANCE_TYPE, along with a REFERENCE_NUMBER for the note itself. A critical foreign key column is COO_ID (Program Offering Option ID), which provides a direct numeric reference to the parent offering option record, facilitating efficient joins. Other significant columns include CRS_NOTE_TYPE, which classifies the note's purpose (e.g., 'HANDBOOK'), and the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) for auditing. The table is indexed to optimize queries on COO_ID, REFERENCE_NUMBER, and CRS_NOTE_TYPE.

Common Use Cases and Queries

This table is primarily accessed for reporting, data validation, and integration processes. Common scenarios include generating official program handbooks by extracting notes of a specific type, or validating note completeness during offering setup. A frequent query pattern involves joining to the main program offering option table via COO_ID or the composite key to retrieve notes for a specific offering.

• Retrieve all notes for a specific Program Offering Option ID:
  SELECT * FROM IGS.IGS_PS_OFR_OPT_NOTE WHERE COO_ID = :p_coo_id ORDER BY REFERENCE_NUMBER;
• Find handbook notes for a set of offerings:
  SELECT n.* FROM IGS.IGS_PS_OFR_OPT_NOTE n, IGS.IGS_PS_OFR_OPT o WHERE n.COO_ID = o.COO_ID AND n.CRS_NOTE_TYPE = 'HANDBOOK' AND o.COURSE_CD = :p_course_cd;

Related Objects

The IGS_PS_OFR_OPT_NOTE table maintains a direct relationship with the core program offering option table, which is inferred from the COO_ID column and the composite primary key structure. The unique index (IGS_PS_OFR_OPT_NOTE_U1) suggests the table is a child of the entity defining COURSE_CD, VERSION_NUMBER, CAL_TYPE, LOCATION_CD, ATTENDANCE_MODE, and ATTENDANCE_TYPE—typically a table like IGS_PS_OFR_OPT (Program Offering Option). The non-unique index on COO_ID (IGS_PS_OFR_OPT_NOTE_N3) explicitly supports this foreign key relationship. The table is also related to the institution's note type lookup values (for CRS_NOTE_TYPE) and is referenced by any reports, forms, or APIs that display or manage notes for academic offerings within the Student Management system.