Search Results igs_or_status_u1

Overview

IGS.IGS_OR_STATUS is a reference (lookup) table in the Oracle E-Business Suite IGS schema that stores user-defined organization statuses, such as Current or Suspended. Each row defines a named status that can be applied to an organizational unit record, and every user-defined status must be mapped to one of the two system-recognized statuses, ACTIVE or INACTIVE, captured in the S_ORG_STATUS column. This mapping allows application code that must reason about whether an organizational unit is operational to do so against a stable system vocabulary while administrators retain the flexibility to name and describe statuses in business terminology.

The table is physically stored in the APPS_TS_TX_DATA tablespace with PCT Free 10 and is classified under FND Design Data as IGS.IGS_OR_STATUS. Under a heuristic Data Vault classification, this object behaves as a hub: it is a standalone reference entity with no foreign key dependencies on other database objects, and its business key is the organization status code itself. In Data Vault terms it is the anchor for organization status values, with no dependent link or satellite structures documented in the ETRM metadata.

Key Information Stored

The primary key is IGS_OR_STATUS_PK on ORG_STATUS. The unique index IGS_OR_STATUS_U1 — the object of the user's search — is a NORMAL, UNIQUE index in tablespace APPS_TS_TX_IDX on the ORG_STATUS column, confirming ORG_STATUS as the business-key candidate. The most significant columns are:

• ORG_STATUS (VARCHAR2(10), mandatory) — the primary key. The user-facing name of the organizational unit status and the value stored on organizational unit records.
• S_ORG_STATUS (VARCHAR2(10)) — the system-recognized status. Each row must map to ACTIVE or INACTIVE. This is the column application logic should join or filter on when it needs a binary operational state.
• DESCRIPTION (VARCHAR2(60)) — free-text description of the status, used for display in list-of-values and inquiry screens.
• CLOSED_IND (VARCHAR2) — open/closed indicator. When checked (closed), the status cannot be selected on new or modified organizational unit records, effectively retiring it without deleting history.
• Who columns — CREATED_BY (NUMBER(15)), CREATION_DATE (DATE), LAST_UPDATED_BY (NUMBER(15)), LAST_UPDATE_DATE (DATE), and LAST_UPDATE_LOGIN (NUMBER(15)) provide standard Oracle Applications audit and concurrency information.

Common Use Cases and Queries

Typical scenarios include populating a status list-of-values for organizational unit maintenance, validating that a status is not retired before assigning it, and reporting counts of organizational units grouped by system status. A frequently used pattern is joining the user-defined status to its system equivalent so that reporting can be performed on the two canonical values:

• Retrieve all selectable (open) statuses: SELECT ORG_STATUS, DESCRIPTION, S_ORG_STATUS FROM IGS.IGS_OR_STATUS WHERE NVL(CLOSED_IND,'N') = 'N';
• Resolve the system status for a given organizational unit status: SELECT S_ORG_STATUS FROM IGS.IGS_OR_STATUS WHERE ORG_STATUS = :p_org_status;
• Group organizational units by canonical state for metrics dashboards, joining the operational organization table to IGS_OR_STATUS on ORG_STATUS and aggregating on S_ORG_STATUS.
• Audit changes to the status set using LAST_UPDATED_BY and LAST_UPDATE_DATE.

Related Objects

The ETRM dependency metadata records that IGS.IGS_OR_STATUS does not reference any database object and is referenced only by the APPS synonym IGS_OR_STATUS. Consequently, related objects are understood through the semantic join on ORG_STATUS rather than enforced foreign keys. The most significant relationships are:

• IGS_OR_STATUS_U1 — the unique index enforcing uniqueness of ORG_STATUS.
• APPS.IGS_OR_STATUS — the APPS-level synonym through which the table is normally queried by reports, concurrent programs, and forms.
• Organizational unit master tables (for example IGS organizational unit/entity tables) that carry an ORG_STATUS column and join to this table on ORG_STATUS to resolve the current activity level.
• List-of-values and lookup definitions that source their permitted values from ORG_STATUS and DESCRIPTION.
• Reporting views and BI Publisher data templates that filter on S_ORG_STATUS for active/inactive analysis.

Because the table is standalone, maintaining referential consistency between organizational unit records and the status set is a functional responsibility rather than a database-enforced constraint; retired statuses should be flagged with CLOSED_IND rather than deleted, preserving the integrity of historical assignments.