Search Results per_us_visa_types

Overview

The view APPS.IGS_AD_VISA_TYPE_V is a composite data source in Oracle E-Business Suite that presents a unified list of visa types available to the Student System (IGS) admissions functionality. Its principal design characteristic is conditional sourcing: the view returns data from one of two distinct underlying objects depending on the value of the profile option IGS_EN_VISA_TYPE. This profile option acts as a configuration switch that governs whether the institution uses the Admissions-specific visa type table or the Oracle Human Resources (PER) common lookup set.

Because visa type is a required attribute in applicant and student records within Oracle Student System, this view serves as the authoritative reference for value lists, validation logic, and reporting. Application modules reference it indirectly whenever a visa type code must be resolved to a meaningful description. In reporting contexts it allows a single query to return correct, environment-specific visa types without hard-coding which source is active.

Underlying Base Objects

The ETRM metadata does not document any base tables explicitly, but the view text reveals two source objects combined through a UNION:

• IGS_AD_VISA_TYPE — the Student System admissions visa type table. It supplies DESCRIPTION and VISA_TYPE when the profile option IGS_EN_VISA_TYPE equals 'O' (the "own"/IGS-controlled mode).
• FND_COMMON_LOOKUPS — the standard Oracle Applications common lookup table. It supplies MEANING aliased as DESCRIPTION and LOOKUP_CODE aliased as VISA_TYPE when the profile option equals 'H' (the Human Resources/PER mode). The lookup records filtered are those whose LOOKUP_TYPE equals 'PER_US_VISA_TYPES' — the same string the user searched on.

Only one branch of the UNION can satisfy its predicate at any time, since the profile option cannot simultaneously be both 'O' and 'H'. This guarantees that the view never returns duplicate rows from conflicting sources. The referenced FND_PROFILE.VALUE call is evaluated at runtime in the session context, making the view behave as a dynamically sourced lookup.

Key Columns

• DESCRIPTION — The human-readable label for the visa type. In the IGS branch this comes directly from IGS_AD_VISA_TYPE.DESCRIPTION; in the HR branch it is the lookup MEANING from FND_COMMON_LOOKUPS. This column is what end users see in list-of-values windows and reports.
• VISA_TYPE — The stored code value. In the IGS branch it is IGS_AD_VISA_TYPE.VISA_TYPE; in the HR branch it maps to FND_COMMON_LOOKUPS.LOOKUP_CODE. This is the value written to applicant and student records and used in joins and validation.

The view exposes only these two columns because both source branches are projected to the same two-column shape, ensuring a structurally consistent result set regardless of configuration.

Common Use Cases and Queries

Typical scenarios include populating visa type list-of-values, validating admission applications, and reporting on applicant nationality and immigration attributes. A basic query retrieves all available visa types in the current session:

• Simple list: SELECT visa_type, description FROM apps.igs_ad_visa_type_v ORDER BY description;
• Resolving a stored code: SELECT description FROM apps.igs_ad_visa_type_v WHERE visa_type = :p_visa_type;
• Driving an admissions report: join the view to applicant tables on VISA_TYPE to display the descriptive label alongside applicant data.
• Configuration check: SELECT fnd_profile.value('IGS_EN_VISA_TYPE') FROM dual; to confirm which source the view is currently returning.

Because the source is profile-determined, the same query returns consistent results within a session but may return different values across environments configured differently. Integrations and extracts that consume this view should therefore treat VISA_TYPE as the stable key and DESCRIPTION as presentation text.