Search Results get_fk_igs_ps_unit_ofr_pat

Overview

IGS_PS_UOFR_WLST_PRI_PKG is a PL/SQL package owned by APPS and classified as an OTHER API within the Oracle E-Business Suite. It belongs to the Student Systems product family (IGS prefix), specifically the component that manages unit offering waitlist priorities. The package encapsulates the data manipulation logic for the IGS_PS_UOFR_WLST_PRI entity, which stores the priority rules that determine the order in which students are considered for places on a waitlisted unit offering. In academic institutions, each unit offering version may have one or more waitlist priority definitions keyed by calendar type, calendar instance sequence number, unit code, and version number. The package declares AUTHID CURRENT_USER, so it executes with the privileges of the calling schema rather than the owner.

The keyword get_fk_igs_ps_unit_ofr_pat, which prompted the search, corresponds to the documented procedure GET_FK_IGS_PS_UNIT_OFR_PAT. This procedure implements the foreign-key validation path linking the waitlist priority record to its parent unit offering pattern record. In Oracle EBS generated APIs, the Get_FK procedures confirm that the parent key supplied by a caller exists before a child row is inserted or updated, thereby preserving referential integrity at the application tier in addition to database constraints.

Key Procedures and Functions

The documented procedures and functions number ten in total:

• INSERT_ROW — Inserts a new waitlist priority row, returns the generated row identifier and primary key, and accepts the priority attributes plus a mode parameter.
• LOCK_ROW — Issues a locking read against the identified row so that concurrent updates cannot silently overwrite one another.
• UPDATE_ROW — Modifies an existing waitlist priority row identified by its primary key and row identifier.
• ADD_ROW — Adds a row, typically used in the generated API pattern where ADD is the primary entry point and INSERT supports it.
• DELETE_ROW — Removes a waitlist priority row identified by the row identifier.
• GET_PK_FOR_VALIDATION — Returns a Boolean indicating whether the supplied primary key, the unit offering waitlist priority identifier, exists; used by foreign-key validation of dependent entities.
• GET_UK_FOR_VALIDATION — Validates the alternate unique key comprising calendar type, calendar instance sequence number, priority value, unit code, and version number.
• GET_FK_IGS_PS_UNIT_OFR_PAT — Confirms the existence of the parent unit offering pattern identified by unit code, version number, calendar type, and calendar instance sequence number; this is the foreign-key validation procedure referenced by the user's search term.
• CHECK_CONSTRAINTS — Performs non-database constraint checking within the API flow.
• BEFORE_DML — A pre-DML hook that prepares or validates data immediately before insert, update, or delete operations.

Tables Accessed

Referencing is performed through APPS synonyms. The base table IGS_PS_UOFR_WLST_PRI holds the live waitlist priority rows and is the principal target of all DML operations. The shadow table IGS_PS_UOFR_WLST_PRI_S stores the corresponding translated or descriptive columns, following the standard EBS pattern in which the parent table carries the business key attributes and the _S table carries the descriptive columns. DUAL is referenced for PL/SQL-level validation, typically in the Boolean-returning validation functions and foreign-key checks that use a SELECT from DUAL to establish row existence. The FK validation procedure also implicitly references the parent IGS_PS_UNIT_OFR_PAT entity defined in the unit offering pattern component.

Usage Notes

The package is generated in the Oracle Forms and PL/SQL API style, so it is normally invoked from the waitlist priority maintenance form, from other generated APIs in the Student Systems schema, and from custom code that must respect the same validation, locking, and referential-integrity rules as the standard application. ETRM records that three other packages reference it, indicating it participates in a call graph rather than being invoked only interactively. Custom integrations should call ADD_ROW or INSERT_ROW rather than performing direct DML, since the API enforces the alternate-key and foreign-key checks and populates the shadow table consistently. Callers that need only to confirm that a parent unit offering pattern exists should call GET_FK_IGS_PS_UNIT_OFR_PAT, which encapsulates the parent key lookup and avoids duplicating query logic. Because of the AUTHID CURRENT_USER clause, the executing schema must hold the necessary privileges on the underlying tables and synonyms. The package header's revision stamp of 2003 indicates the logic has been stable since the 11i era and remains functional in both 12.1.1 and 12.2.2, where the IGS schema objects are largely unchanged.