Search Results institution_status




Overview
Explore in AI Chat

Overview

IGS_OR_INST_HIST_V is an APPS-owned view within the Oracle E-Business Suite Student System (IGS) product family. It presents the historical and current academic institution records maintained by an institution, joining the transactional institution history table to descriptive lookup and reference data so that report writers and integrators can retrieve institution details with fully resolved descriptions rather than coded values. The view is defined as a UNION ALL of two distinct source populations: institutions recorded in the IGS institution history table, and external organizations sourced from Oracle Trading Community Architecture (TCA) party records. This dual sourcing allows the view to serve as a consolidated institution directory across both internally defined institutions and external organizations recognized by the student system.

Because the object is a view rather than a base table, it carries no independent storage and cannot be updated directly. Its principal role is reporting and integration: it supplies institution identity, contact, classification, and status attributes in a denormalized form suitable for concurrent programs, BI Publisher data templates, OBIEE/EBS repository extracts, and inbound or outbound interface staging.

Underlying Base Objects

The view is defined over several base objects, which the ETRM metadata does not enumerate in its referenced-base-object list but which are visible in the documented view text. The first branch draws from IGS_OR_INST_HIST_ALL (aliased IH1), the institution history table. It is joined to three reference sources: IGS_LOOKUP_VALUES for the institution priority code (LOOKUP_TYPE 'OR_INST_PRIORITY_CD', outer-joined), IGS_OR_ORG_INST_TYPE for the institution type description, IGS_LOOKUP_VALUES again for institution control type (LOOKUP_TYPE 'OR_INST_CTL_TYPE'), and IGS_OR_INST_STAT for the institution status description.

The second branch draws from IGS_PE_HZ_PARTIES (IHP) joined to HZ_PARTIES (HP), with parallel lookup joins to IGS_LOOKUP_VALUES, IGS_OR_ORG_INST_TYPE, and IGS_OR_INST_STAT. Note that the UNION ALL requires column alignment rather than semantic identity: for the TCA branch, LAST_UPDATE_DATE is mapped into the HIST_START_DT position and HIST_END_DT is a NULL placeholder.

Key Columns

Common Use Cases and Queries

A typical requirement is listing all institutions of a given type with their current status. The following query filters on the type code and returns the decoded description:

  • SELECT institution_cd, name, institution_type, inst_type_desc, institution_status, inst_status_desc FROM igs_or_inst_hist_v WHERE institution_type = :p_type AND (hist_end_dt IS NULL OR hist_end_dt > SYSDATE) ORDER BY name;

Another frequent scenario is a directory extract joining the view to admissions or recruitment data, or building a lookup validation query for an institution type LOV using INST_TYPE_DESC. Reports requiring full history should key on INSTITUTION_CD and HIST_START_DT, noting that TCA-sourced rows have a single effective record with a NULL end date. Because of the outer join on priority and the UNION ALL structure, queries should avoid assuming uniqueness on INSTITUTION_CD across the combined result set.