Search Results igs_or_unit_hist_v

Overview

IGS_OR_UNIT_HIST_V is a reporting view in the Oracle E-Business Suite Student System (IGS) product family. It exposes the historical record of organizational units — departments, faculties, schools, and other reporting hierarchies — together with the descriptive attributes that applied during each valid period. In ETRM metadata for release 12.2.2 the object is flagged as part of the obsolete IGS module and is documented with the notation "Not implemented in this database", meaning the underlying tables were not deployed in the reference environment used to generate the metadata. Despite this, the view text is preserved and remains useful for understanding historical organizational-unit reporting semantics in 12.1.1 and 12.2.2 installations where the Student System is still licensed.

The view presents one row per organizational-unit history segment, combining the native history rows from IGS_OR_UNIT_HIST_ALL with fallback values resolved from party and audit records. It is used primarily for reporting and integration, giving downstream consumers a denormalized, period-aware view of organization data without requiring them to join the history, party, and audit tables themselves.

Underlying Base Objects

The documented view text references the following objects:

• IGS_OR_UNIT_HIST_ALL — the primary history table, aliased OUH1, holding ORG_UNIT_CD, OU_START_DT, HIST_START_DT, HIST_END_DT, HIST_WHO, and the descriptive columns.
• IGS_PE_HZ_PARTIES — the IGS extension of the TCA party registry, aliased IHP and IHP_INST, supplying OU_ORG_STATUS, OU_ORG_TYPE, OU_MEMBER_TYPE, INSTITUTION_CD, and OSS_ORG_UNIT_CD.
• HZ_PARTIES — the Trading Community Architecture party master, aliased HP and HP_INST, supplying PARTY_NAME and PARTY_NUMBER.
• IGS_AU_GEN_003 — a PL/SQL audit package whose AUDP_GET_OUH_COL function resolves column values for a given unit, start date, and history end date.
• IGS_GE_DATE — a date utility package providing IGSDATE.

The metadata records no documented base objects for this view in 12.2.2, consistent with the "Not implemented in this database" annotation. Where the ETRM lists referenced objects, they mirror the FROM clause above. The view is defined as a UNION ALL: the first branch joins the history table to the current party records, while the second branch (partially shown) selects directly from IGS_PE_HZ_PARTIES and HZ_PARTIES using OU_START_DT and LAST_UPDATE_DATE to represent more recent or non-history rows.

Key Columns

• ORG_UNIT_CD — the organizational unit identifier; the primary join key across all referenced tables.
• OU_START_DT / END_DT — the effective start and end dates of the unit; END_DT is resolved through a nest of NVL calls, falling back to the audit package and then to the party record.
• HIST_START_DT / HIST_END_DT / HIST_WHO — the history segment boundaries and the user or process that created the segment.
• ORG_TYPE — the classification of the organizational unit; this is the column most directly associated with the search term org_type and is resolved as NVL(OUH1.ORG_TYPE, NVL(audit value, IHP.OU_ORG_TYPE)).
• ORG_STATUS, MEMBER_TYPE, INSTITUTION_CD — additional classification and ownership attributes, each resolved with the same three-tier fallback.
• DESCRIPTION and NAME — free-text description and the party name of the unit or its institution.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO columns for auditing and incremental extraction.

Common Use Cases and Queries

Typical uses include point-in-time organization reporting, building slowly changing dimension extracts for a data warehouse, and validating that history segments do not overlap. A query filtering on the organization type might read:

SELECT org_unit_cd, ou_start_dt, end_dt, org_type, org_status, description
FROM   igs_or_unit_hist_v
WHERE  org_type = :p_org_type
AND    :p_as_of_date BETWEEN ou_start_dt AND NVL(end_dt, :p_as_of_date);

To list the full history of a single unit:

SELECT org_unit_cd, ou_start_dt, end_dt, hist_start_dt, hist_end_dt,
       org_type, member_type, institution_cd
FROM   igs_or_unit_hist_v
WHERE  org_unit_cd = :p_org_unit_cd
ORDER  BY ou_start_dt, hist_start_dt;

Because the view performs the audit-package lookups internally, callers should expect row-by-row PL/SQL evaluation and should restrict their predicates tightly, especially when filtering on org_type. Note the "Not implemented in this database" status in the 12.2.2 metadata: verify object existence in the target instance before relying on this view in any migration or upgrade script.