Search Results igs_or_unit_v




Overview
Explore in AI Chat

Overview

IGS_OR_UNIT_V is a seeded database view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It is delivered as part of the IGS (Student System) product family, specifically the Oracle Student System organizational unit model. The view presents a consolidated, denormalized picture of organizational units (ORG_UNIT) used within an institution's organizational hierarchy — typically academic departments, faculties, schools, or administrative divisions — joined to their underlying TCA (Trading Community Architecture) party records.

In EBS reporting and integration contexts, IGS_OR_UNIT_V serves as the standard descriptive view over organizational unit data. Rather than requiring report authors or integration developers to manually join HZ_PARTIES, IGS_PE_HZ_PARTIES, status and lookup tables, the view exposes the party identity, unit code, effective dates, status, type, member type, institution code, and preferred alternate identifier in a single row. This makes it well-suited for concurrent program extracts, OBIEE/BIP data models, and inbound/outbound interface logic in Student System implementations.

Underlying Base Objects

Although the ETRM 12.2.2 metadata records no referenced base objects explicitly, the view text documents the following dependencies:

  • HZ_PARTIES (alias HP) — supplies the TCA party identity, party name (exposed as DESCRIPTION), party number, audit columns, and DFF attributes.
  • IGS_PE_HZ_PARTIES (alias IHP) — the IGS extension of the party record, providing OSS_ORG_UNIT_CD, start/end dates, org status, org type, member type, and institution code.
  • IGS_OR_STATUS (alias OS) — provides the status description for OU_ORG_STATUS.
  • IGS_LOOKUP_VALUES (aliases OT and MT) — supplies the meanings for OR_ORG_TYPE and OR_MEMBER_TYPE lookups respectively.
  • An inline subquery (alias PID) over IGS_OR_ORG_ALT_IDS and IGS_OR_ORG_ALT_IDTYP — retrieves the preferred alternate unit identifier where PREF_UNIT_IND = 'Y' and the current date falls between the start and end dates.

The join enforces INST_ORG_IND = 'O', limiting results to organizational unit records rather than individuals, and applies outer joins for the status, type, member, and alternate ID lookups so units without these attributes are still returned.

Key Columns

Common Use Cases and Queries

Typical usages include LOV queries, validation in forms, BIP data models for organizational unit listings, and integration extracts to external student systems. Sample queries:

  • List all active organizational units for an institution:
    SELECT org_unit_cd, description, status_desc, type_desc FROM igs_or_unit_v WHERE institution_cd = :p_inst AND NVL(end_dt, SYSDATE) >= SYSDATE ORDER BY org_unit_cd;
  • Retrieve a unit's preferred alternate identifier:
    SELECT org_unit_cd, pref_unit_id_type, pref_unit_alt_id FROM igs_or_unit_v WHERE org_unit_cd = :p_unit_cd;
  • Drive an extract of units by type and member classification:
    SELECT party_number, org_unit_cd, member_desc, institution_cd FROM igs_or_unit_v WHERE org_type = 'DEPARTMENT' AND member_type = 'MEMBER';

Because the view joins TCA and IGS tables, queries should filter by institution and effective dates to avoid performance degradation on large implementations.