Results for “igs_ad_visa_type_all_u1”

5 results




Overview
Explore in AI Chat

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

Overview

IGS.IGS_AD_VISA_TYPE_ALL is a foundational reference table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 Student System (IGS) module. It defines the catalogue of visa types that are required for international applicants and students, providing the controlled vocabulary used throughout the recruitment and admissions lifecycle to categorize the immigration documentation each person holds or requires. Because visa classification drives eligibility, I-20 processing, and compliance reporting for international populations, this table functions as a core lookup rather than a transactional store.

The table is registered in FND Design Data as IGS.IGS_AD_VISA_TYPE_ALL and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its purpose is straightforward: it exists so that a visa associated with an international applicant or student can be assigned to a recognized category. Records that are retired from active use are not deleted; instead they are flagged closed, preventing further linkage to person international visa records while preserving historical integrity.

Applying a heuristic Data Vault classification, this object models most naturally as a reference hub (or a small reference/lookup dimension), since its grain is one row per visa type code. It carries a single business key and descriptive attributes, and the metadata indicates it is a standalone object that references no other database object. The classification is offered as a modeling suggestion derived from the FK structure, not as a documented Oracle construct.

Key Information Stored

The table contains nine documented columns. The most significant are:

  • VISA_TYPE (VARCHAR2, 10) — The business key and primary identifier. It holds the code for the visa type, with one value existing for each visa associated with international applicants or students, and is used to categorize visas.
  • DESCRIPTION (VARCHAR2, 60) — A human-readable description of the visa type, used in forms, list of values, and reports.
  • CLOSED_IND (VARCHAR2) — Specifies whether the visa type is open or closed. A closed type cannot be linked to any further person international visa records.
  • ORG_ID (NUMBER, 15) — The operating unit identifier, supporting multi-org partitioning of the reference data.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard Who columns capturing audit and concurrency information.

The primary key is documented as IGS_AD_VISA_TYPE_PK on VISA_TYPE. A separate unique index, IGS_AD_VISA_TYPE_ALL_U1 — the object the user searched for — enforces uniqueness on the same VISA_TYPE column. This index is a normal, unique index residing in the APPS_TS_TX_IDX tablespace. Note that the primary key and the unique business-key index are documented on the same column, so in practice the U1 index identifies VISA_TYPE as the sole business-key candidate rather than exposing a separate surrogate key.

Common Use Cases and Queries

Typical scenarios include populating visa type lists of values in applicant and student maintenance forms, validating visa codes during data entry, filtering active versus retired categories, and producing compliance or regulatory reports on international student populations. Because the table is small and stable, it is frequently joined to person-level international visa records to attach the description and closed status.

A basic lookup of all active visa types:

  • SELECT VISA_TYPE, DESCRIPTION, ORG_ID FROM IGS.IGS_AD_VISA_TYPE_ALL WHERE NVL(CLOSED_IND,'N') = 'N';

Retrieving a single type by its business key, which the U1 index supports efficiently:

  • SELECT VISA_TYPE, DESCRIPTION, CLOSED_IND FROM IGS.IGS_AD_VISA_TYPE_ALL WHERE VISA_TYPE = :p_visa_type;

Joining to person visa data for reporting, filtering on open types only:

  • SELECT v.VISA_TYPE, v.DESCRIPTION FROM IGS.IGS_AD_VISA_TYPE_ALL v WHERE v.CLOSED_IND = 'N' AND v.ORG_ID = :p_org_id;

Auditors may also query the Who columns to determine when a category was created or last maintained, and administrators should review CLOSED_IND before attempting to attach a new person international visa record.

Related Objects

The documented metadata states that IGS_AD_VISA_TYPE_ALL does not reference any database object, and identifies it as being referenced by the APPS synonym IGS_AD_VISA_TYPE_ALL. Beyond that, join paths follow the VISA_TYPE business key. The most significant related objects include:

  • APPS.IGS_AD_VISA_TYPE_ALL — The application synonym through which forms, concurrent programs, and reports access the table.
  • IGS_AD_VISA_TYPE_PK — The primary key constraint on VISA_TYPE, enforcing entity integrity.
  • IGS_AD_VISA_TYPE_ALL_U1 — The unique index on VISA_TYPE in APPS_TS_TX_IDX, supporting the business key and fast lookups.
  • Person international visa tables in the IGS schema — Child records that carry the VISA_TYPE foreign reference for applicants and students, joined on VISA_TYPE.
  • Admissions and student forms/LOVs — UI components that render the visa type list of values from this table.
  • International compliance reports — Concurrent programs and BI Publisher reports that aggregate persons by VISA_TYPE for regulatory reporting.