Search Results igs_ad_ofrdfrmt_stat_u1

Overview

The IGS.IGS_AD_OFRDFRMT_STAT table is a foundational reference table within the Oracle EBS Student Systems (IGS) product family. It stores institution-defined admission offer deferment statuses that describe the state of an applicant's request to defer their admission. Each user-defined status must be mapped to a corresponding system-defined status (such as Pending, Approved, or Rejected), ensuring that institutions can tailor their admissions workflow terminology without breaking the underlying application logic that depends on the seeded system statuses.

Based on the foreign-key topology, this object exhibits a hub-leaning characteristic in Data Vault modeling terms: it holds a stable, uniquely identified business entity (the admission offer deferment status) referenced by dependent transactional and operational tables. It is populated as setup data and consumed throughout the admissions lifecycle.

Key Information Stored

The table is defined with a primary key IGS_AD_OFRDFRMT_STAT_PK on ADM_OFFER_DFRMNT_STATUS, and a unique index IGS_AD_OFRDFRMT_STAT_U1 on the same column. That index serves as the documented business-key candidate, confirming that ADM_OFFER_DFRMNT_STATUS is the natural unique identifier rather than a system-generated surrogate. Its most significant columns include:

• ADM_OFFER_DFRMNT_STATUS — Institution-defined status code (VARCHAR2(10)); the unique business key. Examples include Pending, Approved, and Rejected at the institution level.
• DESCRIPTION — Explanatory text (VARCHAR2(60)) describing the admission offer deferment status.
• S_ADM_OFFER_DFRMNT_STATUS — The system-defined status (VARCHAR2(30)) to which the institution-defined value is mapped, the anchor for application processing logic.
• SYSTEM_DEFAULT_IND — Indicates whether this is the default institution-defined status the system should use when referenced from a given system-defined status.
• CLOSED_IND — Indicates whether the status is open or closed, preventing its use on new or modified admission application records when set.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN follow the standard Oracle EBS WHO-column convention for row-level auditing.

Common Use Cases and Queries

Typical usage involves listing active deferment statuses for use in lookup validation, verifying that every institution-defined status maps to a valid system status, and reporting admissions activity by deferment outcome. A representative query retrieves open statuses with their system mappings:

• SELECT adm_offer_dfrmnt_status, description, s_adm_offer_dfrmnt_status FROM igs.igs_ad_ofrdfrmt_stat WHERE NVL(closed_ind,'N') = 'N';
• Identify the default: SELECT * FROM igs.igs_ad_ofrdfrmt_stat WHERE system_default_ind = 'Y';
• Join to applications: SELECT a.person_id, s.description FROM igs_ad_ps_appl_inst_all a, igs_ad_ofrdfrmt_stat s WHERE a.adm_offer_dfrmnt_status = s.adm_offer_dfrmnt_status;

These patterns support setup validation, status-trend reporting, and downstream admissions letters or notifications.

Related Objects

The most significant dependent object is the admissions application instance table, which carries the deferment status as a foreign key:

• IGS_AD_PS_APPL_INST_ALL — references ADM_OFFER_DFRMNT_STATUS from this table; the principal consumer at the application level.

Related reference and setup objects in the same functional area include the seeded system status values and their associated lookup definitions, and the admissions application and offer-processing tables whose logic reads the SYSTEM_DEFAULT_IND and CLOSED_IND flags. Correct maintenance of this table is essential because any break in the mapping between institution-defined and system-defined statuses can disrupt admissions processing and reporting.