Search Results igs_or_inst_stat




Overview
Explore in AI Chat

Overview

The IGS_OR_INST_STAT table is a reference (lookup) entity within the Oracle E-Business Suite Student System (IGS) product family. It stores user-defined institution statuses — the institution-specific codes that describe the operational or lifecycle state of an academic institution record. Its defining purpose is mapping: each user-defined status is tied back to a system institution status (S_INSTITUTION_STATUS), which allows institutions to define a large, granular set of their own statuses while preserving alignment with the seeded system functionality that drives processing logic. This decoupling is characteristic of the IGS data model, where extensible "OR" (Oracle-derived, user-maintainable) reference tables shadow core system reference tables.

From a Data Vault modeling perspective, the documented heuristic classification for this object is standalone. No foreign keys are mined from the schema, so the table is not modeled as a dependent link or satellite to another hub. In practice it is best treated as a descriptive reference hub (or a small reference dimension) keyed on the institution status code, since it is a master list of valid codes rather than a transactional or historical record. The absence of an effective-dating or history column set reinforces that this is a static lookup rather than a changing satellite.

Key Information Stored

The documented physical schema contains nine columns. The most significant are:

  • INSTITUTION_STATUS — the primary key column and the business key of the table. It carries the user-defined institution status code. It is also the sole documented unique index (IGS_OR_INST_STAT_U1), confirming it as the single business-key candidate and preventing duplicate status codes.
  • S_INSTITUTION_STATUS — the mapping column. It holds the system institution status to which the user-defined status resolves, providing functional "tie-back" to seeded processing behavior. This is the semantic core of the table.
  • DESCRIPTION — the descriptive label presented to end users in forms, lists of values, and reports.
  • CLOSED_IND — a flag indicating whether the status represents a closed or inactive state, supporting filtering of institution records deemed closed.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS "WHO" audit columns tracking insert and update attribution and timestamps.

The primary key constraint is IGS_OR_INST_STAT_PK, defined on INSTITUTION_STATUS; the unique index IGS_OR_INST_STAT_U1 is the corresponding business-key candidate.

Common Use Cases and Queries

Primary use cases centre on validating and decoding institution status values during lookups, reporting, and inbound interfaces. Extracting the user-defined-to-system mapping is the most frequent query pattern:

  • Lookup resolution: SELECT INSTITUTION_STATUS, DESCRIPTION FROM IGS.IGS_OR_INST_STAT WHERE CLOSED_IND = 'N' to drive a status list of values.
  • Mapping report: SELECT INSTITUTION_STATUS, S_INSTITUTION_STATUS, DESCRIPTION FROM IGS.IGS_OR_INST_STAT ORDER BY INSTITUTION_STATUS to audit custom statuses against system statuses.
  • Audit interrogation: filtering on LAST_UPDATE_DATE or LAST_UPDATED_BY to trace configuration changes.
  • Join pattern: institution records referencing their status via INSTITUTION_STATUS, with S_INSTITUTION_STATUS used downstream to drive system logic.

Related Objects

Because the metadata reports no foreign keys (standalone classification), relationships are semantic rather than enforced. Significant related objects include:

  • The IGS institution table(s) that reference IGS_OR_INST_STAT.INSTITUTION_STATUS to classify an institution's operational state.
  • The seeded system institution status table that S_INSTITUTION_STATUS maps back to.
  • IGS setup/validation APIs and concurrent programs that resolve status codes during data load.
  • IGS reporting views and the institution-status list of values that consume these codes.

Consult the IGS product schema documentation to confirm the exact referencing column names before writing joins, since FK enforcement is not present in the documented metadata.