Search Results get_ufk_igs_ps_ofr_opt

Overview

The APPS.IGS_PS_OFR_OPT_NOTE_PKG package is a component of the Oracle E-Business Suite Student System (IGS) product family, specifically belonging to the Prospects and Admissions / Student Records domain. It manages the association between a course offering option and one or more course note types, persisted in the IGS_PS_OFR_OPT_NOTE entity. In Oracle EBS 12.1.1 and 12.2.2 this package is registered as an API-classified object owned by the APPS schema and is defined with AUTHID CURRENT_USER, meaning it executes with the privileges of the invoking user and relies on APPS synonyms for access to the underlying base tables.

The business purpose of the package is to provide a controlled, set-based interface for the full lifecycle of offering-option note records: creating them, locking them against concurrent updates, modifying their attributes, and deleting them. Because course offering options in the Student System are frequently combined with contextual note references (course notes, geographic notes, and note-type validations), a dedicated API layer is required to enforce referential integrity across the IGS note tables and to expose primary-key and foreign-key validation logic to callers.

Key Procedures and Functions

The package exposes twelve documented program units. The DML interface consists of INSERT_ROW, UPDATE_ROW, ADD_ROW, LOCK_ROW, and DELETE_ROW. Each operates on the composite key of the offering-option note record, which includes course code, version number, calendar type, attendance mode, reference number, attendance type, location code, course-offering-option identifier, and course note type. INSERT_ROW and ADD_ROW both return the row identifier via an X_ROWID out parameter; ADD_ROW is typically used by the Forms integration layer where the row may already be referenced in a block, while INSERT_ROW is the standard programmatic insert. The X_MODE parameter, defaulting to 'R', controls runtime behaviour such as validation strictness. LOCK_ROW acquires a pessimistic lock on the identified row to preserve optimistic concurrency semantics in multi-user sessions.

Validation and integrity routines include GET_PK_FOR_VALIDATION, a Boolean function that verifies the composite primary key exists and is valid; GET_FK_IGS_PS_NOTE_TYPE and GET_FK_IGS_PS_OFR_OPT, which validate that referenced course note types and offering options exist; GET_FK_IGS_GE_NOTE, which validates the geographic note reference; and GET_UFK_IGS_PS_OFR_OPT, the unique foreign-key lookup that resolves the offering-option reference used to satisfy the note record's parent relationship. The CHECK_CONSTRAINTS and BEFORE_DML routines provide the internal constraint-checking and pre-DML trigger logic used to enforce business rules consistently across all mutating entry points.

Tables Accessed

The single documented table referenced by this package, via its APPS synonym, is IGS_PS_OFR_OPT_NOTE. This table stores the relationship rows that bind a course offering option to a course note type along with the contextual keys (calendar type, attendance mode, attendance type, location, and reference number). All DML procedures read and write this table; the foreign-key and unique-key validation functions query it (and related parent note-type and offering-option tables) to confirm existence before permitting underlying DML.

Usage Notes

This package is invoked from the Oracle Forms-based user interface for course offering-option maintenance, through the generic BEFORE_DML and constraint-checking patterns typical of the IGS API framework, and from custom PL/SQL integration code. The AUTHID CURRENT_USER declaration means callers must have appropriate grants on the APPS synonyms and base objects. Because it is referenced by four other packages, changes to its signature or behaviour carry downstream impact and should be managed through the documented API rather than by direct DML against IGS_PS_OFR_OPT_NOTE. The GET_UFK_IGS_PS_OFR_OPT function is the entry point most commonly used when resolving or validating the offering-option key during note record creation.