Search Results igs_or_unit_rel




Overview
Explore in AI Chat

Overview

IGS_OR_UNIT_REL is a table in the IGS (Student System) product schema of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It stores the hierarchical parent-child relationships that exist between organizational units within the institution. As the ETRM documentation states, this entity "describes parent and child relationships between organizational units," and explicitly allows an organizational unit to have many parents as well as many children. This many-to-many, graph-style structure is fundamental to how Oracle Student System models institutional structures such as faculties, schools, departments, divisions, and reporting lines.

From a data modeling perspective, the supplied metadata classifies this object heuristically as hub-leaning. This suggests that IGS_OR_UNIT_REL behaves like a hub entity: its identity is defined by a composite business key rather than a single surrogate identifier, and it serves as a stable point of reference for relationships between organizational units. Its effective-dated, start-date-qualified keys reinforce this interpretation.

Key Information Stored

The table's identifying and descriptive content centers on the following documented columns:

No single scalar surrogate key is documented. Instead, the primary key constraint IGS_OR_UNIT_REL_PK is composed of (PARENT_ORG_UNIT_CD, PARENT_START_DT, CHILD_ORG_UNIT_CD, CHILD_START_DT, CREATE_DT), and the unique index IGS_OR_UNIT_REL_U1 mirrors the same column set as the business-key candidate. The start-date columns are essential because organizational units are effective-dated; the same unit code can exist across multiple date ranges, so the date is part of the identity. LOGICAL_DELETE_DT provides soft-delete semantics, allowing historical relationships to be retained for audit and time-based reporting.

Common Use Cases and Queries

Typical scenarios include building organizational hierarchy trees, resolving the parent of a given unit on a specific date, identifying all descendants or ancestors of a unit, and validating that no invalid cycles exist. A common query pattern retrieves active children of a parent as of a given date:

SELECT child_org_unit_cd, child_start_dt FROM igs_or_unit_rel WHERE parent_org_unit_cd = :p AND parent_start_dt = :d AND logical_delete_dt IS NULL;

Hierarchical reporting can be accomplished with a CONNECT BY or recursive WITH clause, using PARENT_ORG_UNIT_CD and CHILD_ORG_UNIT_CD as the join edges and respecting PARENT_START_DT and CHILD_START_DT for effective-dated correctness. Reporting use cases include organizational structure extracts, enrollment reporting by academic unit, and historical reconstruction of reporting lines at a point in time, using LOGICAL_DELETE_DT and the audit columns to filter or trace changes.

Related Objects

  • IGS_OR_REL_PS_TYPE (IGS) – references this table through PARENT_ORG_UNIT_CD, PARENT_START_DT, CHILD_ORG_UNIT_CD, CHILD_START_DT, and OUR_CREATE_DT, linking relationship records to their relationship type classification.
  • IGS_OR_UNIT_REL_PK – the primary key constraint enforcing uniqueness of the effective-dated parent-child relationship.
  • IGS_OR_UNIT_REL_U1 – the unique index supporting the documented business key.
  • Organizational unit base tables in the IGS schema (such as the organizational unit definition entities referenced by PARENT_ORG_UNIT_CD and CHILD_ORG_UNIT_CD) – joined to resolve unit names and attributes.
  • Other IGS_OR_* relationship and type entities – commonly queried alongside UNIT_REL to complete the organizational model.

Together these objects let developers and report writers traverse the effective-dated organizational hierarchy and join it to the classifications that describe each parent-child relationship.