Results for “igs_ad_cndnl_ofrstat”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS.IGS_AD_CNDNL_OFRSTAT is a reference (lookup) table within the Oracle E-Business Suite Student System (IGS) product family. It stores the set of admission conditional offer statuses — the states that describe where a conditional offer of admission stands in the institution's recruitment and admissions workflow (for example, an offer that has been extended, accepted, met conditions, or withdrawn).

The table's distinguishing role is that it acts as the mapping layer between institution-defined statuses and Oracle-delivered, system-defined statuses. The columns ADM_CNDTNL_OFFER_STATUS and S_ADM_CNDTNL_OFFER_STATUS together capture this translation, permitting each institution to label conditional offer statuses according to its own business vocabulary while the application continues to evaluate internal processing logic against the seeded system values. This design is characteristic of IGS reference data, where S_-prefixed columns hold the system value and the unprefixed column carries the local, user-configurable value.

From a dimensional modeling perspective, the ETRM relationship metadata classifies this object as hub-leaning. In Data Vault terms, it behaves as a small reference hub: its primary key is a stable business identifier for each status, and it is referenced by transactional and defaulting tables rather than itself depending on many parents. This is a suggestion for modelers, not a constraint imposed by EBS.

Key Information Stored

The table is documented with ten columns. The primary key constraint is IGS_AD_CNDNL_OFRSTAT_PK, and a unique index IGS_AD_CNDNL_OFRSTAT_U1 exists on ADM_CNDTNL_OFFER_STATUS, confirming that column as the business-key candidate and the principal identifier used by downstream foreign keys.

• ADM_CNDTNL_OFFER_STATUS — the user-defined (institution-specific) conditional offer status code; primary key column and the value propagated to referencing tables.
• S_ADM_CNDTNL_OFFER_STATUS — the system-defined status to which the user-defined status maps; the anchor for Oracle's internal processing.
• DESCRIPTION — the display text shown to admissions users for the user-defined status.
• SYSTEM_DEFAULT_IND — flags statuses that are seeded / system-default and therefore not freely maintainable.
• CLOSED_IND — indicates whether the status represents a terminal, closed condition on the offer, useful for filtering out completed offers in activity reporting.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS WHO audit columns recording row provenance and change history.

The interpretation of the table therefore depends on reading ADM_CNDTNL_OFFER_STATUS and S_ADM_CNDTNL_OFFER_STATUS as a pair; obtaining only the user-defined code without its system mapping yields a label but not a processing meaning.

Common Use Cases and Queries

Typical usage includes validating the status distribution of conditional offers in the admissions pipeline, producing reconciliation reports between user-defined and system-defined statuses, and identifying which statuses are effectively terminal.

A simple reconciliation and listing query:

• SELECT ADM_CNDTNL_OFFER_STATUS, S_ADM_CNDTNL_OFFER_STATUS, DESCRIPTION, CLOSED_IND FROM IGS.IGS_AD_CNDNL_OFRSTAT ORDER BY DESCRIPTION;

Counting application instances by status, joining to the application instance table:

• SELECT a.ADM_CNDTNL_OFFER_STATUS, s.DESCRIPTION, COUNT(*) FROM IGS.IGS_AD_PS_APPL_INST_ALL a JOIN IGS.IGS_AD_CNDNL_OFRSTAT s ON a.ADM_CNDTNL_OFFER_STATUS = s.ADM_CNDTNL_OFFER_STATUS GROUP BY a.ADM_CNDTNL_OFFER_STATUS, s.DESCRIPTION;

Isolating persisted defaulting values that point to conditional offer statuses:

• SELECT d.ADM_CNDTNL_OFFER_STATUS, s.DESCRIPTION FROM IGS.IGS_AD_DFLT_OUDTLS d JOIN IGS.IGS_AD_CNDNL_OFRSTAT s ON d.ADM_CNDTNL_OFFER_STATUS = s.ADM_CNDTNL_OFFER_STATUS;

Because the table is small and changes infrequently, it is a natural candidate to expose through a value set for concurrent program parameters or a lookup for reporting tools.

Related Objects

The following objects reference this table through the ADM_CNDTNL_OFFER_STATUS column, or are directly bound to its key definition:

• IGS_AD_PS_APPL_INST_ALL — the application instance table; each admissions application instance records its current conditional offer status through ADM_CNDTNL_OFFER_STATUS. This is the highest-volume consumer.
• IGS_AD_DFLT_OUDTLS — default organizational unit details; holds defaulted conditional offer status values per organizational unit.
• IGS_AD_CNDNL_OFRSTAT_PK — the primary key constraint defining uniqueness of the status code.
• IGS_AD_CNDNL_OFRSTAT_U1 — the unique index on ADM_CNDTNL_OFFER_STATUS that guarantees the business key and supports foreign key lookups.

These relationships indicate that IGS_AD_CNDNL_OFRSTAT should be treated as master reference data: changes to a status code ripple into application instances and organizational unit defaulting records, so maintenance is normally performed through the Student System administrative setup screens rather than by direct DML.