Search Results child_start_dt




Overview
Explore in AI Chat

Overview

The IGS.IGS_OR_UNIT_REL table is a foundational Oracle E-Business Suite object that stores the hierarchical relationships between organizational units within the Oracle Student System (IGS) footprint. Each row captures a parent-to-child association between two organizational units, where a single organizational unit may participate as a parent to many children and as a child to many parents. This many-to-many topology makes the table the authoritative source for organizational hierarchy traversal, roll-up reporting, and inheritance of attributes across the institutional structure.

The object resides in the APPS_TS_TX_DATA tablespace under the IGS schema and is registered as FND Design Data IGS.IGS_OR_UNIT_REL. The documented physical schema for 12.1.1 exposes eleven columns. Because the table records the state of a relationship at a point in time — including the effective dates and a logical deletion timestamp — it behaves as a time-versioned relationship store. Under a heuristic Data Vault classification, the object is best modeled as hub-leaning: the composite of organizational unit codes and their start dates functions as a set of business keys that anchor the relationship, rather than a pure transactional link or a descriptive satellite.

Key Information Stored

The columns that carry the analytical and functional weight of the table are:

  • PARENT_ORG_UNIT_CD (VARCHAR2(30)) — the organizational unit code of the parent in the relationship; an organizational unit may have multiple parents.
  • PARENT_START_DT (DATE) — effective start date of the parent side of the relationship.
  • CHILD_ORG_UNIT_CD (VARCHAR2(30)) — the organizational unit code of the child; an organizational unit may have multiple children.
  • CHILD_START_DT (DATE) — effective start date of the child side of the relationship. This is the column users typically search for when resolving the temporal validity of a child association.
  • CREATE_DT (DATE) — the date from which the organizational unit relationship is effective.
  • LOGICAL_DELETE_DT (DATE) — the date a relationship was logically deleted. Relationships are never physically removed, so this column enables enquiry into historically valid structures.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Who columns providing audit lineage.

The documented primary key is IGS_OR_UNIT_REL_PK over PARENT_ORG_UNIT_CD, PARENT_START_DT, CHILD_ORG_UNIT_CD, CHILD_START_DT, and CREATE_DT. A unique index, IGS_OR_UNIT_REL_U1, mirrors this composite and serves as the business-key candidate, while the non-unique index IGS_OR_UNIT_REL_N1 on CHILD_ORG_UNIT_CD and CHILD_START_DT optimizes reverse (child-to-parent) traversal.

Common Use Cases and Queries

Typical usage includes resolving the full ancestor chain of a child unit, deriving effective hierarchy snapshots as of a given date, and excluding logically deleted relationships from operational reports. A representative query for the immediate parent of a child as of a point in time is:

  • SELECT parent_org_unit_cd, parent_start_dt FROM igs.igs_or_unit_rel WHERE child_org_unit_cd = :child AND child_start_dt = :dt AND (logical_delete_dt IS NULL OR logical_delete_dt > :as_of);
  • A recursive CONNECT BY query over PARENT_ORG_UNIT_CD and CHILD_ORG_UNIT_CD supports reporting roll-ups and organizational tree visualizations.
  • Audit queries filtering on LOGICAL_DELETE_DT reconstruct the hierarchy as it existed on any prior date.

Related Objects

The documented foreign key relationship shows IGS_OR_REL_PS_TYPE referencing PARENT_ORG_UNIT_CD, indicating that relationship-type categorization in the student system depends on this table's business keys. Parent and child codes also resolve to the organizational unit master, and the standard Who columns align with FND audit conventions.