Search Results igsfv_visas

Overview

IGSFV_VISAS is a read-only Oracle EBS view owned by the APPS schema and registered against the IGS (Student System) product. It presents consolidated visa information for a person, joining the visa record held in IGS_PE_VISA to party, passport, and agent information elsewhere in the E-Business Suite data model. In Oracle EBS 12.1.1 and 12.2.2 the view is exposed as a standard reporting and integration surface for international student and applicant data, and it is typically consumed by OBIEE/XML Publisher extracts, inbound/outbound interfaces, and custom concurrent programs.

The view is defined WITH READ ONLY, so it is intended exclusively for query access. Any modification to visa data must be performed against the underlying IGS_PE_VISA entity through the supported Student System forms or APIs. The view text embeds descriptive-flexfield and lookup translation directives (the _LA: and _DF: tokens), which means some columns are presented as user-facing descriptions rather than raw codes when the view is consumed by Oracle's standard reporting layer.

Underlying Base Objects

The view is defined over four base objects documented in the view text:

• IGS_PE_VISA (PEV) — the primary driving table holding one row per visa record. All outer-joined tables are anchored to it.
• HZ_PARTIES (HZP1) — the person holding the visa, joined on PEV.PERSON_ID = HZP1.PARTY_ID. Supplies PERSON_NUMBER.
• HZ_PARTIES (HZP2) — the issuing or sponsoring organization unit, joined with an outer join on PEV.AGENT_ORG_UNIT_CD = HZP2.PARTY_NUMBER(+). Supplies the agent organization unit name.
• HZ_PARTIES (HZP3) — the agent person, outer-joined on PEV.AGENT_PERSON_ID = HZP3.PARTY_ID(+). Supplies the agent person number.
• IGS_PE_PASSPORT (PEP) — the associated passport, outer-joined on PEV.PASSPORT_ID = PEP.PASSPORT_ID(+).

Because the agent organization and agent person relationships are outer joins, visa rows that have no populated agent information still return, with null agent columns. The ETRM metadata records no additional referenced base objects beyond those visible in the view text.

Key Columns

• PERSON_NUMBER — the party number of the visa holder, sourced from HZ_PARTIES.
• VISA_TYPE, VISA_NUMBER, VISA_CATEGORY — core visa attributes from IGS_PE_VISA, with VISA_TYPE translated to a meaning via PER_US_VISA_TYPES.
• VISA_ISSUE_DATE, VISA_EXPIRY_DATE — validity period of the visa.
• AGENT_ORG_UNIT_CD — the organization unit code of the sponsoring or issuing agent. This is the column most frequently searched, since it links a visa to its sponsoring organization and is the join key to HZ_PARTIES.PARTY_NUMBER.
• AGENT_ORG_UNIT_NAME — the resolved party name for the agent organization unit, derived through the HZP2 outer join.
• AGENT_PERSON_NUMBER, AGENT_CONTACT_NAME — the identified agent and the recorded contact, from HZP3 and IGS_PE_VISA.
• PASSPORT_NUMBER and the passport country description — the traveller's passport context.
• VISA_ISSUING_POST, VISA_ISSUING_COUNTRY — lookup-derived descriptions of where the visa was issued.
• PERSON_ID, VISA_ID, AGENT_PERSON_ID, PASSPORT_ID — surrogate keys supporting joins back to the base entities.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns.

Common Use Cases and Queries

The view is most often used to list visas for a population of students or applicants, to identify all visas sponsored by a particular organization unit, and to feed downstream reporting with resolved descriptions rather than codes. A typical query locating visas by agent organization unit is:

SELECT person_number, visa_type, visa_number, visa_expiry_date,
       agent_org_unit_cd, agent_org_unit_name
FROM   apps.igsfv_visas
WHERE  agent_org_unit_cd = :p_org_unit_cd;

To list a single person's visas, filter on PERSON_ID or PERSON_NUMBER. To find currently valid visas, compare VISA_EXPIRY_DATE to SYSDATE. Because the view is read-only and already resolves many lookup descriptions, it is preferable to query it directly rather than reimplementing the joins against IGS_PE_VISA, HZ_PARTIES, and IGS_PE_PASSPORT.