Search Results child_org_unit_code

Note: this article is an independent technical reference and is not an official Oracle publication. All findings are based solely on the documented view text and metadata.

Overview

IGSFV_ORG_UNIT_HIERARCHIES is a read-only view owned by the APPS schema in the IGS (Student System) product family. It presents the self-referencing, many-to-many parent/child relationships that exist between organizational units, together with the party names that identify those units. Because an organizational unit may have many parents and many children, the view is effectively a flattened hierarchy edge list rather than a strict tree.

The view is defined with a WITH READ ONLY clause, as shown in the documented view text. This means it is intended purely for query, reporting, and integration purposes; no DML is permitted against it. It does not expose any of the underlying surrogate keys of IGS_OR_UNIT_REL, surfacing only the natural business keys (the org unit codes) and the standard audit columns.

For users searching on "logical_delete_date", the relevant point is that the view surfaces this column as LOGICAL_DELETE_DATE. Rows that have been logically deleted are not filtered out of the view; the date is exposed so that consumers can decide whether to include or exclude retired relationships. A NULL LOGICAL_DELETE_DATE generally indicates a relationship that is still active.

Underlying Base Objects

The documented view text references four base objects:

• IGS_OR_UNIT_REL (aliased OUR) — the core relationship table holding PARENT_ORG_UNIT_CD, CHILD_ORG_UNIT_CD and their respective start dates, plus the audit and deletion columns.
• IGS_PE_HZ_PARTIES (aliased IHP1 and IHP2) — the mapping between the OSS organization unit code and the HZ party identifier, resolved once for the parent side and once for the child side.
• HZ_PARTIES (aliased HP1 and HP2) — the party master, joined purely to obtain the PARTY_NAME for each side of the relationship.

The joins are equality joins on OUR.PARENT_ORG_UNIT_CD = IHP1.OSS_ORG_UNIT_CD, IHP1.PARTY_ID = HP1.PARTY_ID, and the equivalent pair for the child. The result is that both the parent and child org unit codes must resolve to a party record for the row to appear. Although the ETRM metadata records "Referenced base objects: none documented" for this view, the view text itself makes the dependencies above explicit.

Key Columns

• PARENT_ORG_UNIT_CODE / CHILD_ORG_UNIT_CODE — the organization unit codes on each side of the relationship; the natural keys used in queries.
• PARENT_START_DATE / CHILD_START_DATE — the start dates associated with each side of the relationship.
• CREATE_DATE — the creation date of the relationship record, sourced from OUR.CREATE_DT, distinct from the generic audit CREATION_DATE.
• LOGICAL_DELETE_DATE — the date on which the relationship was logically deleted; NULL means still active.
• PARENT_ORG_UNIT_NAME / CHILD_ORG_UNIT_NAME — the HZ_PARTIES party names resolved for each side.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard EBS audit columns carried through from IGS_OR_UNIT_REL.

Common Use Cases and Queries

Typical uses include retrieving the children of a given organizational unit, retrieving the parents of a unit, and producing an active-only hierarchy extract for downstream reporting or integration. Filtering on LOGICAL_DELETE_DATE IS NULL is the standard pattern for excluding retired relationships.

Children of a unit, active only:

SELECT parent_org_unit_code, child_org_unit_code, child_org_unit_name, child_start_date
FROM igsfv_org_unit_hierarchies
WHERE parent_org_unit_code = :p_unit_code
AND logical_delete_date IS NULL;

All relationships, including logically deleted, with the deletion date:

SELECT parent_org_unit_code, child_org_unit_code, logical_delete_date
FROM igsfv_org_unit_hierarchies
WHERE parent_org_unit_code = :p_unit_code
ORDER BY logical_delete_date NULLS FIRST;

Because the view is read-only, it is safe for ad hoc queries, extracts, and integration staging. Applications requiring updates must target IGS_OR_UNIT_REL directly, and any hierarchy traversal must tolerate multiple parents, since the source data explicitly permits them.