Search Results igs_pe_visa




Overview
Explore in AI Chat

Overview

IGS_PE_VISA is a transactional table within the Oracle EBS Student System (IGS) product family, owned by the IGS schema. Its documented purpose is to contain all information related to a visa record associated with an individual tracked in the institution's person and party infrastructure. In Oracle EBS 12.1.1 and 12.2.2, the table resides in the IGS schema and is marked VALID in the ETRM repository, confirming it is an active, supported object at both release levels.

The table records visa details for persons managed by the Student System, linking each visa to a person, an optional passport, and an optional agent party. From a Data Vault modeling perspective, the metadata's heuristic classification is satellite-leaning. In practice this means IGS_PE_VISA functions primarily as a descriptive satellite attached to person and party hubs (HZ_PARTIES) and to the passport hub (IGS_PE_PASSPORT), carrying the attribute history of visa issuance. The primary key IGS_PE_VISA_PK on VISA_ID supplies the surrogate key, while foreign key columns such as PERSON_ID, AGENT_PERSON_ID, and PASSPORT_ID supply the hub or link references.

Key Information Stored

The documented schema for ETRM 12.1.1 contains 39 columns. The most significant are:

A second unique index, IGS_PE_VISA_U1, is defined on PERSON_ID, VISA_TYPE, and VISA_ISSUE_DATE, making this three-column combination the documented business-key candidate. VISA_ID remains the surrogate key, and the unique index prevents duplicate visa records for the same person, type, and issue date.

Common Use Cases and Queries

Typical reporting scenarios include verifying whether a student's visa is currently valid, tracking expiry dates for compliance notifications, and reconciling visa data against passport records. A validity check can be expressed as follows:

  • SELECT visa_id, person_id, visa_type, visa_number, visa_expiry_date FROM igs_pe_visa WHERE person_id = :person_id AND visa_expiry_date >= SYSDATE;
  • Expiry monitoring: SELECT person_id, visa_type, visa_expiry_date FROM igs_pe_visa WHERE visa_expiry_date BETWEEN SYSDATE AND SYSDATE + 90;
  • Joining to the party hub for names: SELECT v.visa_id, p.party_name, v.visa_type, v.visa_expiry_date FROM igs_pe_visa v, hz_parties p WHERE v.person_id = p.party_id;
  • Reconciling visa to passport: SELECT v.visa_id, v.visa_number, ps.passport_number FROM igs_pe_visa v, igs_pe_passport ps WHERE v.passport_id = ps.passport_id;
  • Visa history per person via the dependent history table: SELECT * FROM igs_pe_visit_histry WHERE visa_id = :visa_id;

Related Objects

IGS_PE_VISA participates in a small, well-defined relationship set documented in the ETRM metadata:

  • HZ_PARTIES — referenced twice: IGS_PE_VISA.PERSON_ID and IGS_PE_VISA.AGENT_PERSON_ID both join to HZ_PARTIES, making it the primary hub dependency for person and agent identity.
  • IGS_PE_PASSPORT — IGS_PE_VISA.PASSPORT_ID joins to this table, associating the visa with its passport record.
  • IGS_PE_VISIT_HISTRY — this child table references IGS_PE_VISA through IGS_PE_VISIT_HISTRY.VISA_ID, providing historical or visit-level detail for each visa.

Because the documented relationship data names only these three related objects, any additional integration with Student System enrollment, admission, or international-student processing tables should be validated against the actual 12.1.1 and 12.2.2 schemas before use.