Search Results or_institution_advstex

Overview

IGS_OR_INSTITUTION_ADVSTEX_V is a database view owned by the APPS schema in Oracle E-Business Suite. It is a component of the Oracle Student System (formerly Oracle Student System / OSS), specifically within the Student Records (IGS) product family, and is associated with the "OR" (Oracle Receivables-related institutional) module. The view presents a unified list of academic institutions that are recognized as "advisement exemption" institutions, combining two distinct data sources: institutional party records held in the TCA (Trading Community Architecture) model and value-set style lookup codes stored in the IGS lookup table.

Its role is to provide a single, consumable reporting and integration surface for downstream processes, such as admissions, registration, or external data exchange, that need to determine whether an applicant's or student's prior institution qualifies for an advisement exemption. Because it is a view rather than a table, it exposes no persistent storage of its own; it materializes rows at query time from the underlying sources.

Underlying Base Objects

The view is defined using a UNION ALL of two branches.

• Institutional branch: Joins HZ_PARTIES (aliased hp) with IGS_PE_HZ_PARTIES (aliased ihp) on party_id, filtered by ihp.inst_org_ind = 'I'. HZ_PARTIES supplies the TCA party attributes (party number, party name, audit columns), while IGS_PE_HZ_PARTIES supplies the Oracle Student System extension attributes for the party, including the OSS organisation unit code and institution status.
• Lookup branch: Selects from IGS_LOOKUP_VALUES (aliased lk) where lookup_type = 'OR_INSTITUTION_ADVSTEX' and enabled_flag = 'Y'. This branch surfaces lookup codes that represent exemption institutions not modelled as full TCA parties.

Because the UNION ALL requires column alignment, the second branch substitutes NULLs (cast to NUMBER or DATE) for the audit and status columns that only exist in the first branch.

Key Columns

• TCA_PARTY_NUMBER — The party_number from HZ_PARTIES in the first branch, and the lookup_code in the second branch. Serves as the primary identifier for the institution.
• EXEMPTION_INSTITUTION_CD — The OSS_ORG_UNIT_CD from IGS_PE_HZ_PARTIES in the first branch, and lookup_code in the second. This is the code used to match against exemption rules.
• PARTY_NAME / MEANING — For institutional parties, the party_name; for lookup rows, the meaning of the lookup code.
• OI_INSTITUTION_STATUS — Institution status from IGS_PE_HZ_PARTIES; blank ('') for lookup rows.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN are populated only for TCA-based rows and are NULL for lookup-derived rows.

Common Use Cases and Queries

Typical use is validation during admissions or registration, where a user enters a prior institution and the system must confirm it is a recognized exemption institution. Because of the UNION, callers should account for both identifier styles.

Example query listing all exemption institutions:

SELECT tca_party_number,
       exemption_institution_cd,
       party_name
  FROM apps.igs_or_institution_advstex_v
 ORDER BY party_name;

Example query retrieving the descriptive name for a known exemption code:

SELECT exemption_institution_cd, party_name
  FROM apps.igs_or_institution_advstex_v
 WHERE exemption_institution_cd = :p_institution_code;

Because the lookup branch filters on enabled_flag = 'Y', only currently enabled exemption institutions are returned, making the view suitable for live validation logic rather than historical reporting.