Search Results tca_party_number




Overview
Explore in AI Chat

Overview

IGS_OR_INSTITUTION_ADVSTEX_V is a reporting view owned by the APPS schema within the IGS (Student System) product family of Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to capture exempted institutions — that is, external academic or awarding organizations that are recognized as exempt from certain standard institutional validation or registration requirements in the student records model. The view functions as a read-only presentation layer that normalizes two otherwise dissimilar data sources into one rectangular result set, so downstream reporting, integration, and LOV (list of values) mechanisms can consume a single canonical row shape.

For users searching on tca_party_number, the view is relevant because its first column, TCA_PARTY_NUMBER, is sourced directly from HZ_PARTIES.PARTY_NUMBER. This column exposes the Trading Community Architecture (TCA) party number, allowing Student System records to be correlated with the TCA registry and with other EBS modules that key on party identity.

Underlying Base Objects

The view text is defined as a UNION ALL of two branches:

  • HZ_PARTIES HP joined to IGS_PE_HZ_PARTIES IHP on HP.PARTY_ID = IHP.PARTY_ID, filtered by IHP.INST_ORG_IND = 'I'. This branch supplies real, registered institution parties from the TCA registry that have been flagged as institutions in the Student System extension table.
  • IGS_LOOKUP_VALUES LK, filtered by LOOKUP_TYPE = 'OR_INSTITUTION_ADVSTEX' and ENABLED_FLAG = 'Y'. This branch supplies lookup-defined exemption codes that are not backed by a TCA party record. In this branch the lookup code is projected into both the party number and institution code columns, with literal or NULL placeholders for the remaining columns.

The ETRM 12.2.2 metadata documents no persisted referenced base objects, reflecting that the view is a pure join/union construct over TCA and Student System tables rather than a materialized or denormalized entity.

Key Columns

Common Use Cases and Queries

Typical uses include populating exemption-institution lists of values, validating inbound admissions or transfer-credit data, and reconciling Student System institution references against the TCA registry by party number.

SELECT tca_party_number,
       exemption_institution_cd,
       name,
       institution_status
FROM   apps.igs_or_institution_advstex_v
WHERE  tca_party_number IS NOT NULL
ORDER  BY name;
SELECT v.exemption_institution_cd, v.name
FROM   apps.igs_or_institution_advstex_v v
WHERE  v.creation_date IS NULL;   -- lookup-sourced exemption codes only

Because the second branch synthesizes rows with NULL audit columns, filters intended to isolate genuine TCA institutions should test for non-NULL TCA_PARTY_NUMBER or CREATION_DATE.