Results for “igs_ps_unit_stat”

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

Overview

IGS_PS_UNIT_STAT is a reference (lookup) table within the Oracle E-Business Suite Student System (IGS) product family. It stores user-defined unit statuses — such as "Current" or "Suspended" — that describe the enrollment state of a student unit attempt. The core design intent is to decouple institutional terminology from Oracle's internal, hard-coded system unit statuses: each user-defined status row maps back onto one of the predefined system statuses via the S_UNIT_STATUS column, allowing institutions to define a large number of descriptive statuses while still driving system behavior through a small, fixed set of functional codes.

From a Data Vault modeling perspective, the ETRM heuristic classifies this object as hub-leaning. This is consistent with its role: UNIT_STATUS is a stable, unique business key that acts as an anchor for descriptive attributes and is referenced by other tables. In practice it is best treated as a hub (or a small reference/dimension table in a star schema) rather than a transactional or link entity, since it carries classification and control attributes rather than relationships between business entities.

Note that the ETRM metadata records this object as "Not implemented in this database" and lists the product as "IGS – Student System (Obsolete)". It is therefore a legacy object: it may exist in older 12.1.1/12.2.2 instances that retain Student System data, but it should be regarded as deprecated for new implementations.

Key Information Stored

The table is documented with nine columns. The most significant are:

• UNIT_STATUS — The primary key column and the unique business-key candidate (enforced by the IGS_PS_UNIT_STAT_PK primary key and the IGS_PS_UNIT_STAT_U1 unique index). It is the short code that identifies each user-defined status and is the value propagated to dependent tables.
• DESCRIPTION — The human-readable name or explanation of the status (for example, "Current" or "Suspended"), used for display and reporting.
• S_UNIT_STATUS — The mapping column that ties the user-defined status back to the system unit status, enabling Oracle's delivered functionality to interpret the institution-specific code.
• CLOSED_IND — A flag indicating whether the status represents a closed/terminal state, supporting status-transition and validation logic.
• CREATED_BY, CREATION_DATE — Standard audit columns recording the user and timestamp of row creation.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit columns recording the most recent modification and the login/session that performed it.

The surrogate key and the business key are effectively the same column here (UNIT_STATUS); the U1 unique index confirms it as the definitive business-key candidate, while the remaining attributes behave as satellite-style descriptive and control data.

Common Use Cases and Queries

Typical usage is validation and reporting on unit status codes. Two representative patterns:

• Resolving a code to its display text: SELECT unit_status, description, s_unit_status FROM igs_ps_unit_stat WHERE closed_ind = 'N'; — used to populate list-of-values queries and prompts.
• Mapping user codes to system codes for reporting: SELECT v.unit_status, s.description, s.s_unit_status FROM igs_ps_unit_ver_all v, igs_ps_unit_stat s WHERE v.unit_status = s.unit_status; — this join surfaces the institution-defined status alongside its system counterpart for downstream analytics.

Reporting scenarios include enrollment status breakdowns, auditing of status-transition rules, and reconciliation of custom statuses against the delivered system statuses to verify that all configured codes map correctly. Because the table is small and relatively static, it is commonly extracted as a dimension and refreshed on a slow cadence.

Related Objects

• IGS_PS_UNIT_VER_ALL — The primary consumer. Its UNIT_STATUS column is a foreign key to IGS_PS_UNIT_STAT.UNIT_STATUS, so every unit version record references a valid status row.
• IGS_PS_UNIT_STAT_PK — The primary key constraint on UNIT_STATUS.
• IGS_PS_UNIT_STAT_U1 — The unique index on UNIT_STATUS, serving as the business-key candidate and reinforcing the one-to-one identity between key and code.

Downstream, any query or concurrent program that resolves unit attempt states ultimately joins through IGS_PS_UNIT_VER_ALL back to this table. The dependency direction is one-way: dependent objects reference IGS_PS_UNIT_STAT, and it references nothing itself, which confirms its position as a hub-style anchor. Given its obsolete status, integration designs should be validated against the delivered system status values before relying on this table in new development.