Search Results igs_or_unit_hist_all_u1

Overview

The IGS.IGS_OR_UNIT_HIST_ALL table is a transactional table in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the IGS schema (the Student Systems / Oracle Student System product family). It functions as an audit and history store for organizational units, capturing the state of an organizational unit each time a change is made to its defining attributes. An organizational unit in this context represents a business unit of an institution or organization, such as a faculty, department, school, or administrative division. Every time a change is committed to a live organizational unit record, the prior or newly effective values are frozen into a row of this history table, preserving description, status, type, member type, institution, and end date as they stood at the moment the history record was created.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is standalone. No foreign keys were detected, so the object sits outside the standard hub/link/satellite relationships as a self-contained historical ledger. It can nonetheless be modeled conceptually as a satellite-like history construct anchored on the organizational unit business key (ORG_UNIT_CD and OU_START_DT), with HIST_START_DT providing the change-tracking grain. Because there are no enforced foreign keys, referential integrity is maintained by the application layer rather than the database.

Key Information Stored

The table is documented with 18 columns. The primary key is IGS_OR_UNIT_HIST_PK, defined on the composite of ORG_UNIT_CD, OU_START_DT, and HIST_START_DT. The unique index IGS_OR_UNIT_HIST_ALL_U1 shares the identical column list (ORG_UNIT_CD, OU_START_DT, HIST_START_DT) and therefore does not introduce a separate business-key candidate; it reinforces the natural key of the organizational unit version combined with the history effective timestamp. The most significant columns are:

• ORG_UNIT_CD (VARCHAR2(30)) — code for the organizational unit; combined with start date this uniquely identifies the unit.
• OU_START_DT (DATE) — date from which the organizational unit version is effective.
• HIST_START_DT (DATE) — effective start date/time of the history record, set to the moment the previous history record was ended.
• HIST_END_DT (DATE) — effective end date/time of the history record.
• HIST_WHO (NUMBER(15)) — Oracle username of the person who created the history record.
• OU_END_DT (DATE) — organizational unit end date at the time the history record was created.
• DESCRIPTION (VARCHAR2(360)) — description of the unit captured at record creation.
• ORG_STATUS (VARCHAR2(10)) — status of the unit (for example active or inactive) as of the history snapshot.
• ORG_TYPE (VARCHAR2(30)) — organizational type value at snapshot time.
• MEMBER_TYPE (VARCHAR2(30)) — member type value at snapshot time.
• INSTITUTION_CD (VARCHAR2(30)) — institution associated with the unit at snapshot time.
• NAME (VARCHAR2(360)) — name of the organizational unit at snapshot time.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO columns for audit tracking.
• ORG_ID — multi-org operating unit identifier supporting row-level security.

The distinguishing design feature is that the descriptive columns are point-in-time copies, not references. Each history row preserves what the unit looked like, ensuring that current changes never overwrite prior values.

Common Use Cases and Queries

The primary use case is temporal reconstruction: determining what an organizational unit looked like on any given date, and identifying who changed it and when. A typical query filters on the business key and orders by history timestamp:

• Point-in-time lookup: SELECT * FROM igs.igs_or_unit_hist_all WHERE org_unit_cd = :code AND hist_start_dt <= :as_of_date ORDER BY hist_start_dt DESC;
• Change auditing: compare consecutive history rows for a unit to detect when ORG_STATUS, ORG_TYPE, or DESCRIPTION changed, using analytic functions such as LAG() OVER (PARTITION BY org_unit_cd ORDER BY hist_start_dt).
• Change attribution reporting: group by HIST_WHO to report which users have modified organizational units most frequently within a period.
• Institution-level reconciliation: aggregate by INSTITUTION_CD to see historical unit counts per institution.
• Open-history detection: query rows where HIST_END_DT IS NULL to find the currently effective history snapshot.

Because the table resides in the APPS_TS_TX_DATA tablespace with indexes in APPS_TS_TX_IDX, heavy temporal range scans should be driven through the IGS_OR_UNIT_HIST_ALL_U1 index on the leading ORG_UNIT_CD column. Reporting typically joins back to the live organizational unit table to compare historical versus current values.

Related Objects

The Dependency Information identifies FND Design Data IGS.IGS_OR_UNIT_HIST_ALL, and the application layer maintains logical relationships despite the absence of declared foreign keys. The most significant related objects are:

• IGS.IGS_OR_UNIT_ALL — the live organizational unit table keyed on ORG_UNIT_CD and OU_START_DT; the history table is joined to it on the same two columns.
• IGS.IGS_OR_UNIT_V — the current-view wrapper over organizational units, frequently used alongside the history table for delta reporting.
• IGS.IGS_OR_UNIT_HIST_PK — the primary key constraint on ORG_UNIT_CD, OU_START_DT, HIST_START_DT.
• IGS_OR_UNIT_HIST_ALL_U1 — the unique index enforcing the business key.
• IGS.IGS_OR_UNIT_TYPES and IGS.IGS_OR_UNIT_STATUS — reference/lookup tables resolving ORG_TYPE, MEMBER_TYPE, and ORG_STATUS values captured historically.
• IGS.IGS_INSTITUTION_ALL — joined on INSTITUTION_CD to resolve institution names.
• FND_ORG_SECURITY / ORG_ID multi-org views — applying operating unit security to ORG_ID.
• IGS_OR_UNIT_HIST_API (or equivalent PL/SQL API package) — the application interface that inserts history rows, including HIST_WHO and HIST_START_DT.

Because the object is classified as standalone at the database level, all joins are logical and must be supplied explicitly in queries. Retention and partitioning strategy should account for the fact that this table grows monotonically with every organizational unit change.