Search Results igs_or_inst_stat_u1

Overview

IGS.IGS_OR_INST_STAT is a foundational setup table within the Oracle EBS Student Systems (IGS) schema, present in both release 12.1.1 and 12.2.2. It stores the user-defined institution statuses that control how an institution record is classified according to its level of activity. The design follows a deliberate two-tier pattern: every user-defined status must map back to a system-recognized status (active or inactive) so that administrators can define an arbitrarily large number of business-friendly statuses while the application continues to operate against a small, fixed set of system values. In the ETRM 12.1.1 physical schema the table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10 and carries nine columns.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. In modelling terms this suggests the table behaves as a reference or lookup hub rather than as a transactional link or satellite. The recorded primary key, IGS_OR_INST_STAT_PK, is defined on the INSTITUTION_STATUS column, and the unique index IGS_OR_INST_STAT_U1 is aligned to the same column, confirming the business key.

Key Information Stored

The most significant columns are:

• INSTITUTION_STATUS (VARCHAR2(10), mandatory) — the primary key and business-key candidate, uniquely identifying each user-defined institution status via IGS_OR_INST_STAT_PK and the IGS_OR_INST_STAT_U1 unique index.
• S_INSTITUTION_STATUS (VARCHAR2(10)) — the system-recognized status into which the user-defined status is mapped. This is the column that gives the table its functional meaning, because each value must resolve to a system status of active or inactive.
• DESCRIPTION (VARCHAR2(60)) — the human-readable description presented to users when selecting a status on an institution record.
• CLOSED_IND (VARCHAR2) — indicates whether the status is open or closed. Selecting the closed checkbox prevents the status from being applied to new or modified institution records, providing a soft-retirement mechanism without deleting history.
• Standard Who columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide the audit trail required for all EBS transactional and setup data.

Because the table is intentionally narrow, nearly every column participates in either setup validation or reporting, so extraction scripts should generally project the full column list.

Common Use Cases and Queries

Typical usage falls into three categories: setup validation, mapping verification, and reporting on institution activity.

• Reviewing all configured statuses is the most common ad hoc query:
  SELECT INSTITUTION_STATUS, DESCRIPTION, S_INSTITUTION_STATUS, CLOSED_IND FROM IGS.IGS_OR_INST_STAT ORDER BY DESCRIPTION;
• Mapping audit — verifying that each user-defined status maps correctly to a system status. Because S_INSTITUTION_STATUS is not enforced as a database check constraint in the documented schema, report writers should confirm the mapping explicitly:
  SELECT INSTITUTION_STATUS, S_INSTITUTION_STATUS FROM IGS.IGS_OR_INST_STAT WHERE S_INSTITUTION_STATUS NOT IN ('ACTIVE','INACTIVE');
• Retired status lookup — identifying statuses no longer available for selection:
  SELECT INSTITUTION_STATUS, DESCRIPTION FROM IGS.IGS_OR_INST_STAT WHERE CLOSED_IND = 'Y';
• Joining to institution records — reports on institutions typically join the institution's status code to IGS_OR_INST_STAT.INSTITUTION_STATUS to obtain the description and to confirm the current system-level activity state.

Because the table is standalone and small, it can be safely queried in full during setup reviews and converted to a lookup view for multi-org reporting without performance concerns.

Related Objects

The documented dependency metadata states that IGS_OR_INST_STAT does not reference any database object, but it is referenced by other IGS objects (the excerpt truncates at "is referenced by fo…"). Based on the institution-status role, the following objects are most significant:

• Institution master table (IGS_OR_INSTITUTIONS-style) — holds institution records whose status column references INSTITUTION_STATUS.
• IGS_OR_INST_STAT_PK — the primary key constraint on INSTITUTION_STATUS, referenced by foreign keys in dependent tables.
• IGS_OR_INST_STAT_U1 — the unique index underpinning business-key lookups.
• Student/recruiting applications tables — which filter institutions by CLOSED_IND when presenting valid choices.
• IGS setup validation views and concurrent programs — which consult this table when validating user-entered institution status codes.

The absence of outgoing foreign keys confirms the hub-like, standalone profile identified by the Data Vault heuristic, making this a stable reference dimension for downstream reporting and integration.