Search Results pref_unit_alt_id




Overview
Explore in AI Chat

Overview

IGS_OR_UNIT is a reporting view owned by the APPS schema within the Oracle E-Business Suite Student System (IGS) product family. It presents a consolidated, denormalized picture of organizational units that are registered in the institution's organization hierarchy. The view joins TCA party records in HZ_PARTIES with the Student System's party extension table IGS_PE_HZ_PARTIES, restricting output to those parties flagged as institutional organizations (INST_ORG_IND = 'O'). This makes the view the primary reference point for querying the identity, status, classification, and institutional affiliation of organizational units without needing to navigate the underlying normalized model directly.

Because organizational unit data in IGS is spread across TCA party records, IGS lookup values, and alternate identifier tables, the view serves as the canonical read interface for both reporting and integration. It is commonly used to expose unit master data to downstream systems, to drive institutional hierarchy reports, and to provide organization context for student, program, and offering records that reference organizational units.

Underlying Base Objects

The view is defined over a set of documented base objects:

  • HZ_PARTIES (HP) — the TCA master party table, supplying party ID, party number, party name, audit columns, and the DFF attribute columns.
  • IGS_PE_HZ_PARTIES (IHP) — the IGS party extension table, supplying organizational unit code, organizational status, organizational type, member type, start and end dates, and the institution code.
  • IGS_OR_STATUS (OS) — the organizational status lookup, providing the status description.
  • IGS_LOOKUP_VALUES (OT, MT) — joined twice against lookup types OR_ORG_TYPE and OR_MEMBER_TYPE to resolve the meaning of the organizational type and member type codes.
  • IGS_OR_ORG_ALT_IDS (OLI) and IGS_OR_ORG_ALT_IDTYP (OLT) — combined in an inline view (PID) to return the preferred alternate identifier for each organization structure, where PREF_UNIT_IND is 'Y' and the current date falls within the altern

JOIN relationships thread these tables together on party ID, organizational status, and organizational unit code. Lookup joins are outer joins, so units with no valid type or member code still appear. The alternate identifier join is also outer, meaning units without a current preferred alternate ID are retained.

Key Columns

The most frequently referenced columns include:

Common Use Cases and Queries

Typical scenarios include listing active organizational units, resolving unit descriptions for reporting on student or offering data, and extracting master data for integration. A basic active-unit query:

  • SELECT org_unit_cd, description, status_desc, type_desc, institution_cd FROM igs_or_unit WHERE org_status = 'A';
  • SELECT party_number, description, member_desc FROM igs_or_unit WHERE org_type = 'DEPT' AND SYSDATE BETWEEN start_dt AND NVL(end_dt, SYSDATE);
  • SELECT org_unit_cd, pref_unit_id_type, pref_unit_alt_id FROM igs_or_unit WHERE pref_unit_alt_id IS NOT NULL ORDER BY org_unit_cd;

Because the view is read-only and denormalized, it is well suited to ad hoc reporting, extract pipelines, and BI datasets that require organizational unit context without direct access to the underlying TCA and IGS tables.