Search Results igs_en_unit_set_stat




Overview
Explore in AI Chat

Overview

The IGS_EN_UNIT_SET_STAT table belongs to the IGS (Student System) product family within Oracle E-Business Suite, a module documented as obsolete in the ETRM repository but historically significant for institutions running legacy student administration data. The entity describes user-defined unit set statuses — for example "Current" or "Suspended" — that map back onto a smaller set of system-defined unit set statuses. This mapping layer gives implementers the flexibility to define a large number of presentation-level or institution-specific statuses while still consolidating them onto a limited set of internal, functional system statuses used by application logic.

The table is documented with nine physical columns in ETRM 12.1.1 and is owned by the IGS schema. Under the heuristic Data Vault classification mined from its foreign key structure, the object is characterized as standalone — that is, no outgoing or incoming FK relationships were detected in the documented metadata. In Data Vault modeling terms, this suggests treating the table as a self-contained reference or lookup entity rather than as a link or satellite of a larger hub, though a formal modeler may still choose to represent it as a small reference hub with an attached descriptive satellite if integration with other IGS reference data is desired.

Key Information Stored

The table's primary key is defined by the constraint IGS_EN_UNIT_SET_STAT_PK, which covers the UNIT_SET_STATUS column. A separate unique index, IGS_EN_UNIT_SET_STAT_U1, also covers UNIT_SET_STATUS, making that column both the surrogate primary key and the sole documented business-key candidate. This is an unusual but not unheard-of pattern in legacy Oracle data models, where a short coded identifier serves simultaneously as the technical key and the natural identifier.

  • UNIT_SET_STATUS — The primary key and business key; the user-defined status code that uniquely identifies each row.
  • S_UNIT_SET_STATUS — The mapped system unit set status. The "S_" prefix conventionally indicates the system-level counterpart to the user-defined value, enabling the mapping behavior described in the entity documentation.
  • DESCRIPTION — The human-readable description of the status, used in list-of-values displays and reports.
  • CLOSED_IND — A flag indicating whether the status represents a closed state, useful for filtering active versus closed unit sets.
  • CREATED_BY, CREATION_DATE — Standard WHO audit columns recording row creation.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns recording the most recent modification and the login session responsible for it.

Common Use Cases and Queries

Because this is a reference table, the most common access pattern is a lookup join from a unit set or related entity onto UNIT_SET_STATUS to retrieve DESCRIPTION and CLOSED_IND. Reporting queries frequently need to determine which user-defined statuses roll up to a given system status, for example to count unit sets in a "Suspended" state across the institution.

A typical query pattern:

  • SELECT uss.unit_set_status, uss.description, uss.closed_ind FROM igs_en_unit_set_stat uss WHERE NVL(uss.closed_ind,'N') = 'N' ORDER BY uss.description;
  • SELECT s_unit_set_status, COUNT(*) FROM igs_en_unit_set_stat GROUP BY s_unit_set_status; — used to verify that every user-defined status maps onto a valid system status.
  • A validation query to detect orphaned mappings: SELECT * FROM igs_en_unit_set_stat uss WHERE NOT EXISTS (SELECT 1 FROM igs_en_unit_set syst WHERE syst.unit_set_status = uss.s_unit_set_status);

In migration or conversion projects, this table is commonly extracted in full and reconciled against legacy student systems to ensure status code mappings are preserved.

Related Objects

The documented FK mining classifies this table as standalone, meaning no formal foreign keys were detected in the ETRM metadata. In practice, the S_UNIT_SET_STATUS column is intended to reference the system-level unit set status definition, most plausibly held in the IGS_EN_UNIT_SET or IGS_EN_UNIT_SET_STATUS tables within the same IGS schema, joined on the unit set status code. Downstream, unit set entities that carry a status column depend on this table to resolve user-facing descriptions, and any IGS unit set inquiry or validation API that accepts a status parameter will resolve against UNIT_SET_STATUS. Because the module is obsolete, consumers integrating with EBS 12.1.1 or 12.2.2 should confirm the current state of these companion tables in their specific instance before relying on the mapping relationship.