Search Results ou_end_dt




Overview
Explore in AI Chat

Overview

APPS.IGS_OR_UNIT_HIST_V is a reporting and integration view within the Oracle E-Business Suite (EBS) Student System / Oracle Student System (OSS) module, specifically belonging to the Institutions (IGS) product family. The view exposes the historical record of organizational units — institutions, faculties, schools, departments, and similar academic structures — together with the effective-dated attributes that define each unit over time. Its role is to provide a consolidated, denormalized read model over organizational unit history so that reporting tools, concurrent programs, and integration interfaces can retrieve point-in-time organizational structure without navigating the normalized transactional tables directly.

The object is particularly relevant to users who filter or join on institution_cd, since a dedicated column of that name is exposed by the view and is resolved through a layered NVL fallback rather than a single source table column.

Underlying Base Objects

The view is defined over two principal object groups joined through a UNION ALL, producing the historical (H) and the current/live party-based rows. The documented underlying objects are:

  • IGS_OR_UNIT_HIST_ALL — aliased ouh1 in the first branch; holds the effective-dated organizational unit history record including ORG_UNIT_CD, OU_START_DT, HIST_START_DT, HIST_END_DT, and the audit columns.
  • IGS_PE_HZ_PARTIES — aliased ihp and ihp_inst; the OSS extension of the TCA party model, providing the organizational unit to party relationship and the default attribute values (ou_end_dt, ou_org_status, ou_org_type, ou_member_type, institution_cd).
  • HZ_PARTIES — aliased hp, hp_inst, and hp2; supplies the party name (used for the unit NAME and for the institution name), PARTY_ID, PARTY_NUMBER, and audit tracking columns.

The join condition is ihp.oss_org_unit_cd = ouh1.org_unit_cd with hp.party_id = ihp.party_id; the institution attribute is additionally resolved by joining ihp.institution_cd = ihp_inst.oss_org_unit_cd and ihp_inst.party_id = hp_inst.party_id. The UNION ALL second branch reads directly from the party records without a history table alias, using hp2 and ihp2.

Key Columns

  • ORG_UNIT_CD — organizational unit code; in the second UNION branch this is populated from hp2.party_number.
  • OU_START_DT / OU_END_DT — effective start and end dates of the organizational unit. The exposed END_DT column applies NVL across the history value, a derived value from the audit package, and ihp.ou_end_dt.
  • HIST_START_DT / HIST_END_DT — the date range for which the historical version row is valid.
  • INSTITUTION_CD — the institution code. Resolved as NVL(ouh1.institution_cd, NVL(AUDP_GET_OUH_COL('INSTITUTION_CD', ...), ihp.institution_cd)), so the value falls back from the history record to the audit-derived value to the party relationship default.
  • NAME / DESCRIPTION — the unit name or description, falling back to HZ_PARTIES.PARTY_NAME.
  • ORG_STATUS, ORG_TYPE, MEMBER_TYPE — unit status, organizational classification, and membership classification, each subject to the same layered NVL resolution.
  • HIST_WHO — the user who performed the historical update; in the second branch this maps to hp2.last_updated_by.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

Note that the second UNION branch supplies TO_DATE(NULL) for the history end date, which is characteristic of current/live rows merged into the historical result set.

Common Use Cases and Queries

Typical usage includes effective-dated organizational hierarchy reporting, extraction of institution-level unit listings, and integration feeds that require party-related attributes. A representative query filtering on the institution is:

  • SELECT org_unit_cd, ou_start_dt, end_dt, name, org_status, org_type, member_type, institution_cd FROM apps.igs_or_unit_hist_v WHERE institution_cd = :p_institution_cd ORDER BY org_unit_cd, ou_start_dt;
  • Point-in-time reconstruction: constrain HIST_START_DT and HIST_END_DT around a reporting date to obtain the structure valid at that moment.
  • Joining to IGS_PE_HZ_PARTIES or other OSS party-based views via ORG_UNIT_CD when the caller needs the party identifier rather than the code.

Because the view resolves attributes through the audit package function IGS_AU_GEN_003.AUDP_GET_OUH_COL, consumers should be aware that performance depends on the indexed access to IGS_OR_UNIT_HIST_ALL and the related party tables, and that results reflect the audit fallback precedence defined in the view text.