Search Results igs_or_status_pk

Overview

IGS_OR_STATUS is a reference (lookup) table within the Oracle EBS IGS – Student System product family. As documented in the ETRM metadata, this entity describes user-defined organization statuses, such as "Current" or "Suspended." Its purpose is to provide a controlled, extensible vocabulary for classifying the lifecycle or administrative state of an organization record held elsewhere in the Student System schema. Rather than hard-coding status values, the application allows implementers to define statuses that match institutional business rules, and other IGS entities reference those definitions.

The metadata explicitly notes that this table is "Not implemented in this database" in the source environment and that the IGS product is marked Obsolete. Consequently, in Oracle EBS 12.1.1 and 12.2.2 environments where the Student System was never licensed or has been retired, IGS_OR_STATUS will not exist physically. Where it does exist, it is owned by the IGS schema and contains nine documented columns. The Data Vault classification provided is standalone, which is best interpreted as a modeling suggestion: the object behaves as a small reference/dimension table rather than a transactional hub or link. In Data Vault terms it most closely resembles a low-volume reference satellite, since the documented relationship structure shows no foreign key dependencies to other tables.

Key Information Stored

The table is narrow and primarily descriptive. The most significant elements are:

• ORG_STATUS — the primary key column and the business identifier for each status. It is also enforced by the unique index IGS_OR_STATUS_U1, making it both the surrogate and natural key candidate.
• DESCRIPTION — the human-readable label presented to users, for example "Current" or "Suspended."
• S_ORG_STATUS — a secondary or system-defined status code, typically used to distinguish seeded/system values from user-defined ones.
• CLOSED_IND — a flag indicating whether the status represents a closed or terminal state, useful for filtering active versus inactive organizations.
• CREATED_BY, CREATION_DATE — standard EBS audit columns recording the creating user and timestamp.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns capturing the most recent modification user, timestamp, and login session.

The primary key IGS_OR_STATUS_PK is defined on ORG_STATUS, and the unique index IGS_OR_STATUS_U1 also covers ORG_STATUS, confirming a one-to-one relationship between the surrogate and business key. The remaining columns are the four WHO-style audit fields plus the descriptive attributes above.

Common Use Cases and Queries

Typical usage centers on validation lists and reporting on organization state. A simple lookup of all active statuses might be written as:

• SELECT org_status, description FROM igs.igs_or_status WHERE NVL(closed_ind,'N') = 'N' ORDER BY description;
• SELECT s.org_status, s.description, COUNT(*) FROM igs.igs_or_status s, igs.<org_table> o WHERE s.org_status = o.org_status GROUP BY s.org_status, s.description; — to count organizations per status.
• SELECT * FROM igs.igs_or_status WHERE org_status = :p_status; — to resolve a single status for display in a form or report.

Because the table is a lookup, it is frequently joined to organization master tables to translate stored status codes into meaningful labels. Reporting use cases include enrollment or registration dashboards that segment organizations by open/closed state, and audit reports that leverage the CREATION_DATE and LAST_UPDATE_DATE columns to track configuration changes. The CLOSED_IND column is especially valuable in filter predicates, since it allows exclusion of terminal statuses without hard-coding individual status values.

Related Objects

The metadata classifies IGS_OR_STATUS as standalone, meaning it defines no outgoing foreign keys and no incoming FK dependencies are documented. In practice, related objects are those that consume the status values through application logic rather than enforced constraints:

• IGS_OR_STATUS_PK / IGS_OR_STATUS_U1 — the primary key and unique index that guarantee status uniqueness.
• Organization master tables in the IGS schema — these hold the ORG_STATUS column that joins to IGS_OR_STATUS.ORG_STATUS, though the specific table name is not enumerated in the provided metadata.
• IGS lookup/validation views and forms — UI and validation layers that read DESCRIPTION for display and CLOSED_IND for filtering.
• EBS audit-related objects — standard WHO columns align this table with the broader EBS audit and concurrency model.

Given that the product is obsolete and the table is not implemented in many environments, integrators should verify physical existence in the target schema before relying on it in custom code or reports.