Search Results inst_ctl_type_desc

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

• INSTITUTION_CD — institution code; in the TCA branch this maps from OSS_ORG_UNIT_CD.
• HIST_START_DT / HIST_END_DT — validity window of the history record; HIST_END_DT is NULL for the TCA-sourced branch.
• NAME — institution name (from IGS_OR_INST_HIST_ALL.NAME or HZ_PARTIES.PARTY_NAME).
• INST_PHONE_COUNTRY_CODE / INST_PHONE_AREA_CODE / INST_PHONE_NUMBER — telephone attributes.
• INSTITUTION_PRIORITY / PRIORITY_DESCRIPTION — priority code and its decoded meaning.
• INSTITUTION_STATUS / INST_STATUS_DESC — status code and decoded description from IGS_OR_INST_STAT.
• INSTITUTION_TYPE / INST_TYPE_DESC — the institution type code and its description from IGS_OR_ORG_INST_TYPE. This is the column most relevant to searches on "institution_type".
• INST_CONTROL_TYPE / INST_CTL_TYPE_DESC — control type (for example, public or private) and decoded meaning.
• LOCAL_INSTITUTION_IND / OS_IND — local institution and overseas indicators. In the TCA branch these derive from OI_LOCAL_INSTITUTION_IND and OI_OS_IND.
• GOVT_INSTITUTION_CD — government institution classification code.
• EPS_CODE — associated EPS code (INST_EPS_CODE in the TCA branch).
• CREATED_BY / CREATION_DATE / LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN — standard audit 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.