Search Results get_fk_igs_az_students

Overview

IGS_AZ_ADVISING_RELS_PKG is a PL/SQL package owned by APPS within the Oracle E-Business Suite Student Systems (IGS) product family, specifically the Academic Advising (IGS_AZ) module. The package encapsulates the database logic governing the IGS_AZ_ADVISING_RELS entity, which tracks relationships between advisors and students organized into advising groups. It follows the standard Oracle EBS "table handler" pattern, providing a controlled, validated DML interface over the underlying base table and its shadow/entity tables rather than permitting direct writes from client code. The package is declared with AUTHID CURRENT_USER, meaning its SQL executes with the privileges of the invoking schema — in practice APPS — rather than the defining schema. The $Header comment indicates an initial version dated June 2003, consistent with the 11i-to-R12 lineage of the IGS product suite, and the package remains registered and documented under both EBS 12.1.1 and 12.2.2.

Key Procedures and Functions

The package exposes eleven documented procedures and functions. The core DML routines are INSERT_ROW, UPDATE_ROW, ADD_ROW, DELETE_ROW, and LOCK_ROW. INSERT_ROW and UPDATE_ROW accept the row identifier, the primary key, and the business attributes — group name, group advisor identifier, group student identifier, start date, and end date — together with an operating mode flag (defaulting to 'R') and three standard return parameters: X_RETURN_STATUS, X_MSG_DATA, and X_MSG_COUNT. These three OUT parameters are the conventional EBS error-handling contract, allowing callers to detect success or failure and retrieve the associated message text and count. ADD_ROW performs an unconditional insert and, unlike INSERT_ROW, does not return the standard status triple, indicating it is intended for internal or bulk use. DELETE_ROW removes a row given its rowid and returns the status triple. LOCK_ROW performs a pessimistic row lock, revalidating all the business attributes before locking, preventing concurrent modification.

The validation functions are GET_PK_FOR_VALIDATION, which verifies that a given group advising relationship identifier exists by primary key, GET_UK_FOR_VALIDATION, which validates the unique key composed of group name, advisor, and student, and three foreign-key validators: GET_FK_IGS_AZ_GROUPS, GET_FK_IGS_AZ_STUDENTS, and GET_FK_IGS_AZ_ADVISORS. The user query get_fk_igs_az_groups maps directly to the first of these, the routine that confirms a candidate group reference resolves to a valid row in the IGS_AZ_GROUPS entity before the advising relationship is committed. BEFORE_DML is the internal pre-processing hook, typically invoked to default audit columns, enforce WHO-column population, and apply the operating-mode logic prior to the actual insert, update, or delete.

Tables Accessed

Through APPS synonyms, the package reads and writes IGS_AZ_ADVISING_RELS, the base relationship table, and IGS_AZ_ADVISING_RELS_S, its companion shadow table used for primary-key and rowid bookkeeping in the table-handler pattern. Foreign key validation additionally depends on the referenced group, student, and advisor entities exposed by the IGS_AZ synonym layer.

Usage Notes

The package is registered as an API of classification OTHER and is referenced by four other packages, indicating it is consumed both by the advising forms (the IGS Academic Advising windows that maintain group advising relationships) and by programmatic callers within the IGS schema stack. Custom code should invoke the DML procedures rather than issuing direct DML against the tables, and must inspect X_RETURN_STATUS, X_MSG_DATA, and X_MSG_COUNT to honor the package's error contract. Because the package is AUTHID CURRENT_USER, callers must execute under a schema with appropriate synonym and privilege grants.