Search Results igs_or_unit_hist_pk

Overview

IGS_OR_UNIT_HIST_ALL is a history (audit) table within the Oracle E-Business Suite IGS — Student System product family. In Oracle EBS 12.1.1 and 12.2.2 it is documented as an obsolete object and is not implemented in the standard database; the ETRM entry is retained for reference and upgrade/legacy analysis only. The entity records the version history of changes made to an organizational unit (OU) — capturing the state of an OU across successive versions, together with who made the change and when the prior version ceased to be effective.

From a Data Vault modeling perspective, the mined relationship data classifies this object as standalone, with no referential dependencies. Consistent with that, the heuristic modeling suggestion is to treat it as a satellite — a descriptive, time-variant store keyed by a parent business key plus a change-effective date, rather than a hub or link.

Key Information Stored

The table carries 18 documented columns in the 12.1.1 physical schema, owned by the IGS schema. The most significant are:

• ORG_UNIT_CD — the organizational unit business identifier; the leading component of the primary key.
• OU_START_DT — the effective start date of the OU version; second component of the primary key.
• HIST_START_DT — the start of the history/version interval; third component of the primary key.
• HIST_END_DT — the end of the version interval; supports point-in-time and "as-of" reconstruction.
• HIST_WHO — the actor or process responsible for the change recorded in the row.
• OU_END_DT — the end date of the OU's effectiveness.
• ORG_STATUS — status of the organizational unit at this version.
• ORG_TYPE and MEMBER_TYPE — classification of the OU and its membership semantics.
• NAME and DESCRIPTION — the descriptive attributes of the OU as captured in that version.
• INSTITUTION_CD — the institution context for the OU.
• ORG_ID — the surrogate organization identifier linked to the OU record.
• CREATED_BY / CREATION_DATE / LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN — standard EBS audit (WHO) columns describing row insertion and last modification.

Note that the documented primary key IGS_OR_UNIT_HIST_PK is composed of business columns (ORG_UNIT_CD, OU_START_DT, HIST_START_DT), while the unique index IGS_OR_UNIT_HIST_ALL_U1 shares the same column set; there is no separately documented surrogate key column. ORG_UNIT_CD, OU_START_DT and HIST_START_DT therefore serve simultaneously as the PK and the business-key candidates.

Common Use Cases and Queries

Despite being obsolete in current releases, the historical pattern remains instructive for as-of and change-tracking reporting. Typical uses include reconstructing an OU hierarchy as it existed on a given date, auditing who changed an OU's status or type, and comparing successive versions of an OU's name or description.

An as-of query selects the row whose history interval brackets the target date:

SELECT org_unit_cd, ou_start_dt, hist_start_dt, name, org_status
FROM   igs_or_unit_hist_all
WHERE  org_unit_cd  = :org_unit_cd
AND    hist_start_dt <= :as_of_date
AND   (hist_end_dt IS NULL OR hist_end_dt > :as_of_date);

A change-history report orders versions within an OU to reveal edits over time:

SELECT org_unit_cd, hist_start_dt, hist_end_dt, hist_who,
       name, org_status, org_type
FROM   igs_or_unit_hist_all
WHERE  org_unit_cd = :org_unit_cd
ORDER  BY hist_start_dt;

Because no foreign keys are documented, joins to organizational-unit master data are performed on business columns (ORG_UNIT_CD, OU_START_DT, INSTITUTION_CD) rather than on surrogate keys.

Related Objects

ETRM relationship mining classifies IGS_OR_UNIT_HIST_ALL as standalone, so no verified FK dependencies are documented. The most significant logically related objects — joined on the shared business key ORG_UNIT_CD and the OU date range (OU_START_DT/OU_END_DT) — are:

• IGS_OR_UNIT_ALL — the current organizational-unit master; the history rows mirror its versions.
• IGS_OR_UNIT_HIST — the base (non-"_ALL") history table where a multi-org variant is deployed.
• Organization/HR organizational unit tables — reachable through ORG_ID for master-detail linkage.
• IGS_OR_UNIT_HIST_PK / IGS_OR_UNIT_HIST_ALL_U1 — the primary-key constraint and unique index that enforce the ORG_UNIT_CD + OU_START_DT + HIST_START_DT business key.
• Institution reference data (keyed on INSTITUTION_CD) — supplies the institution context for each OU version.

Teams performing upgrades from 12.1.1 to 12.2.2 should confirm whether customizations introduced the object, since the standard ETRM entry reports it as not implemented and obsolete.