Search Results igs_ad_appl_stat_pk

Overview

The IGS_AD_APPL_STAT table is a foundational data object within the now-obsolete Student System (IGS) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It functions as a reference or lookup table, centralizing the definition of all possible statuses for an admission application. Its primary role is to enable user-defined status codes, such as "RECEIVED" or "WITHDRAWN," which are then mapped internally to system-defined statuses that control application workflow logic. This abstraction allows institutions to customize the terminology of their admissions process while maintaining consistent underlying system behavior. The table's design is critical for maintaining data integrity across the admissions module, as it is referenced by core application transaction tables.

Key Information Stored

The table's structure is designed to manage the status lifecycle of an application. The most critical column is ADM_APPL_STATUS, which serves as the table's primary key. This column stores the unique, user-defined code for each application status (e.g., 'RCVD', 'WDN', 'UNDER_REV'). While the provided metadata does not list all columns, a table of this nature in the EBS context typically includes descriptive columns such as DESCRIPTION for the full status name, and columns to define the system mapping, effective dates (START_DATE, END_DATE), and who created or last updated the record. The core data it holds represents the controlled set of valid states any admission application can occupy.

Common Use Cases and Queries

The primary use case for this table is to support queries that report on or filter applications based on their status. It is essential for generating admissions funnel reports, tracking application completion rates, and listing applications in a specific state, such as all "Under Consideration" applications. A common SQL pattern involves joining this table to the main application table to translate status codes into meaningful descriptions for reporting.

Sample Query:
SELECT appl.person_id, appl.application_number, stat.description AS status
FROM igs_ad_appl_all appl,
igs_ad_appl_stat stat
WHERE appl.adm_appl_status = stat.adm_appl_status
AND stat.adm_appl_status = 'UNDER_REV';
This query retrieves all applications currently under review, joining the transaction data to the lookup table to get the human-readable status description.

Related Objects

The IGS_AD_APPL_STAT table has defined foreign key relationships with two major transaction tables in the admissions module, as documented in the ETRM metadata. These relationships enforce referential integrity, ensuring that only valid status codes from this lookup table can be used in application records.

• IGS_AD_APPL_ALL: This is the primary table for admission application headers. It references IGS_AD_APPL_STAT via the column IGS_AD_APPL_ALL.ADM_APPL_STATUS, which holds the current overall status of an application.
• IGS_AD_PS_APPL_INST_ALL: This table likely stores institution-specific details for program applications. It references IGS_AD_APPL_STAT via the column IGS_AD_PS_APPL_INST_ALL.APPL_INST_STATUS, which may track a status specific to an application instance at a given institution.

The primary key constraint IGS_AD_APPL_STAT_PK on the ADM_APPL_STATUS column is the anchor for these relationships.