Search Results igs_ps_ofr_note_n2

Overview

The IGS_PS_OFR_NOTE table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (OSM) or Campus Solutions modules under the IGS (iGrants) schema. This table serves as a junction entity that establishes the relationship between a specific program offering and its associated textual notes. Its primary role is to enable the flexible attachment of multiple, categorized notes to a defined academic offering, which is a combination of a program (course), its version, and a calendar type. This supports critical academic administration functions such as publishing handbook entries, internal administrative remarks, and public-facing information.

Key Information Stored

The table stores a composite primary key that uniquely identifies a note attached to a program offering, along with a classification for the note's purpose. The critical columns are:

• COURSE_CD, VERSION_NUMBER, CAL_TYPE: Together, these columns uniquely identify the specific program offering (e.g., "BSC_CS", version 1, in a "SEMESTER" calendar).
• REFERENCE_NUMBER: A foreign key that points to the actual note text stored in a central notes repository table (commonly IGS_GE_NOTE).
• CRS_NOTE_TYPE: A mandatory, institution-defined code (VARCHAR2(10)) that categorizes the note's purpose. As per the documentation, examples include "HANDBOOK" for official publication or other internal types. This is the column relevant to the user's search term.
• Standard WHO Columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN): For auditing and tracking data changes.

Common Use Cases and Queries

A primary use case is generating official program catalogs or handbooks by extracting all notes of type 'HANDBOOK' for active offerings. Administrators may also query notes for a specific offering for review or update. The non-unique index on CRS_NOTE_TYPE (IGS_PS_OFR_NOTE_N2) is optimized for such filtering. A typical query to retrieve all notes for a specific offering would be:

SELECT n.CRS_NOTE_TYPE, gn.note_text
FROM IGS.IGS_PS_OFR_NOTE n,
     IGS.IGS_GE_NOTE gn
WHERE n.course_cd = 'BSC_CS'
  AND n.version_number = 1
  AND n.cal_type = 'SEMESTER'
  AND n.reference_number = gn.reference_number
ORDER BY n.CRS_NOTE_TYPE;

Another common pattern is identifying all program offerings that have a specific type of note attached, which leverages the indexed CRS_NOTE_TYPE column for performance.

Related Objects

Based on the provided dependency information, the IGS_PS_OFR_NOTE table has a clear foreign key relationship to a central notes table via the REFERENCE_NUMBER column, which is indexed (IGS_PS_OFR_NOTE_N1). While the specific table name is not listed in the excerpt, in the standard IGS schema design, this is typically the IGS_GE_NOTE table. The table itself is referenced by an APPS synonym (IGS_PS_OFR_NOTE), which is used by the EBS application code and reports. The primary key columns (COURSE_CD, VERSION_NUMBER, CAL_TYPE) are likely foreign keys referencing a program offerings table, such as IGS_PS_OFR, establishing the core relationship between an offering and its notes.