Search Results get_fk_igs_ps_note_type

Overview

IGS_EN_UNIT_SET_NOTE_PKG is a public PL/SQL package owned by the APPS schema in Oracle E-Business Suite release 12.1.1 and 12.2.2. It belongs to the Student Systems product family, specifically the Student Records and Enrollment (IGS) module, and is classified as an OTHER API in the ETRM repository. The package encapsulates the business logic required to maintain the IGS_EN_UNIT_SET_NOTE entity, which associates free-text or reference notes with unit set definitions within a specific unit set version.

The package declares AUTHID CURRENT_USER, meaning it executes with the privileges of the calling session rather than the definer. This design is consistent with the Oracle Forms-based architecture used across the IGS schema, where a form triggers package logic that performs table maintenance on behalf of the connected user. The header comment indicates the file was last shipped during the 11.5.10 era and has been carried forward, unchanged in signature, into the 12.1.x and 12.2.x code lines.

Key Procedures and Functions

Eleven public program units are documented. They fall into the standard CUSTOM-style PL/SQL API pattern used throughout the IGS schema:

• INSERT_ROW — Creates a new note row in IGS_EN_UNIT_SET_NOTE. It accepts a ROWID output handle together with the unit set code, version number, reference number, note type, and a processing mode flag.
• ADD_ROW — A wrapper variant for row creation, typically called from a different Forms trigger path than INSERT_ROW.
• UPDATE_ROW — Modifies an existing note row identified by ROWID, applying the same attribute set as INSERT_ROW.
• LOCK_ROW — Implements pessimistic locking using SELECT ... FOR UPDATE, guarding against concurrent modifications between query and commit in the Forms transaction.
• DELETE_ROW — Removes a note row identified by ROWID.
• GET_PK_FOR_VALIDATION — Returns a BOOLEAN indicating whether a candidate primary key combination (unit set, version, reference number) already exists; used to validate uniqueness before insert.
• GET_FK_IGS_PS_NOTE_TYPE — The foreign-key validation routine for the CRS_NOTE_TYPE column, checking the value against the IGS_PS_NOTE_TYPE reference entity. This is the procedure surfaced by the search term.
• GET_FK_IGS_GE_NOTE — Validates the reference number against the IGS_GE_NOTE entity.
• GET_FK_IGS_EN_UNIT_SET — Validates unit set code and version against IGS_EN_UNIT_SET.
• CHECK_CONSTRAINTS — Performs column-level constraint validation; accepts an optional column name and value.
• BEFORE_DML — A centralized pre-DML hook that applies audit stamping (creation date, created by, update date, updated by) and dispatches the appropriate validation and DML action.

Tables Accessed

The package operates on a single base table, accessed through the APPS synonym IGS_EN_UNIT_SET_NOTE. This table stores the note linkage records that connect a unit set version to a note reference and note type. All INSERT_ROW, UPDATE_ROW, ADD_ROW, DELETE_ROW, and LOCK_ROW operations target this table. Validation routines additionally consult the parent entities referenced by the foreign-key procedures (IGS_PS_NOTE_TYPE, IGS_GE_NOTE, and IGS_EN_UNIT_SET) to confirm that incoming values satisfy referential integrity rules before a DML against IGS_EN_UNIT_SET_NOTE is permitted.

Usage Notes

The package is referenced by four other packages in the IGS schema and is normally invoked indirectly through the unit set definition Oracle Forms module rather than from SQL*Plus. Typical call sequences are: GET_FK_* procedures fire during item validation, GET_PK_FOR_VALIDATION fires on record navigation, BEFORE_DML and CHECK_CONSTRAINTS fire on save, and the INSERT/UPDATE/DELETE procedures execute the resulting DML. Customers extending unit set notes through custom PL/SQL should call the same procedures rather than issuing direct DML, because BEFORE_DML owns the WHO-column audit population and skipping it will leave audit columns null. Because the package is AUTHID CURRENT_USER and resides as a public package in APPS, grants must be in place for any custom schema invoking it directly.