Search Results igs_pe_visa_pk

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:

• VISA_ID — the surrogate primary key, enforced by IGS_PE_VISA_PK. It uniquely identifies a visa record.
• PERSON_ID — the party for whom the visa is recorded; joins to HZ_PARTIES.
• VISA_TYPE, VISA_NUMBER, and VISA_CATEGORY — classification and identification attributes of the visa.
• VISA_ISSUE_DATE and VISA_EXPIRY_DATE — the effective period of the visa, used for eligibility and compliance reporting.
• VISA_ISSUING_POST and VISA_ISSUING_COUNTRY — the authority and jurisdiction that issued the visa.
• PASSPORT_ID — the associated passport record; joins to IGS_PE_PASSPORT.
• AGENT_PERSON_ID, AGENT_CONTACT_NAME, and AGENT_ORG_UNIT_CD — the sponsoring or processing agent associated with the visa; AGENT_PERSON_ID joins to HZ_PARTIES.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — the standard Oracle EBS descriptive flexfield container.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — the standard WHO audit columns.

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.