Results for “igs_or_inst_exempt_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_OR_INST_EXEMPT_V is a seeded Oracle E-Business Suite database view owned by the APPS schema and delivered as part of the IGS (Student System) product family. In EBS 12.1.1 and 12.2.2 this object is validated and available in the standard installation. Its purpose is to present the set of valid values for the "advanced standing exemption institution" — the institution from which a student has previously obtained learning that is recognised as advanced standing. The view supplies the reference data required to populate the DETYA element 368 (the Australian government reporting element for exemption institution), so that student advanced-standing records can be captured and subsequently reported against a controlled list of codes.

Because it is a view rather than a table, IGS_OR_INST_EXEMPT_V holds no data of its own; it presents a consolidated, read-only projection of institution records and lookup codes already maintained elsewhere in the system. This makes it suitable for use in concurrent reporting programs, discoverer worksheets, OA Framework list-of-values definitions, and inbound validation routines that must confirm an entered exemption institution against the permitted codes.

Underlying Base Objects

The view is defined by a UNION ALL of two sources, giving it both an externally maintained institution list and an internally maintained lookup list.

• HZ_PARTIES (HP) — the Trading Community Architecture party master record, joined on PARTY_ID. It supplies the party number, party name, and standard audit columns (created by, creation date, last updated by, last update date, last update login).
• IGS_PE_HZ_PARTIES (IHP) — the Student System extension table for parties, filtered by INST_ORG_IND = 'I' to restrict results to institution organisations, and by OI_GOVT_INSTITUTION_CD IS NOT NULL to ensure only government-registered institutions are returned.
• IGS_LOOKUP_VALUES (LK) — the seeded lookup table, filtered by LOOKUP_TYPE = 'OR_INST_EXEMPTIONS' and ENABLED_FLAG = 'Y', providing the maintained code list as an alternative source of valid values.

Only the second branch of the query contributes a meaningful SOURCE discriminator; the lookup branch returns NULL for the audit columns because those values are not applicable to reference codes.

Key Columns

• TCA_PARTY_NUMBER / EXEMPTION_INSTITUTION_CD — the party number is returned as TCA_PARTY_NUMBER while the institution code (OSS_ORG_UNIT_CD) is mapped to EXEMPTION_INSTITUTION_CD. In the lookup branch, the lookup code is projected into both columns.
• NAME — the party name for institution parties, or the lookup meaning for code entries.
• INSTITUTION_STATUS — the institution status held on IGS_PE_HZ_PARTIES (OI_INSTITUTION_STATUS); null for lookup rows.
• SOURCE — a literal discriminator. Institution-party rows carry the literal 'INSTITUTION CODE'; lookup rows carry an empty string, allowing consumers to distinguish the two origins.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard "WHO" audit columns, populated only for the institution-party branch.

Common Use Cases and Queries

The view is typically used to validate or display exemption institutions during advanced standing entry and to drive reporting extracts for DETYA element 368. A basic retrieval of all valid values is straightforward:

• SELECT exemption_institution_cd, name, source FROM igs_or_inst_exempt_v; — returns the full valid-value set.
• SELECT ... WHERE source = 'INSTITUTION CODE'; — restricts results to institutions derived from TCA parties.
• SELECT ... WHERE source = ''; — restricts results to maintained lookup codes.
• An EXISTS predicate against this view is the common mechanism for confirming that an entered exemption institution is valid before inserting the corresponding advanced standing record.

Because the object is a view, no direct DML is permitted; corrections must be applied to the underlying HZ_PARTIES, IGS_PE_HZ_PARTIES, or IGS_LOOKUP_VALUES records respectively.