Results for “igs_pe_visit_histry_pk”

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

Overview

IGS_PE_VISIT_HISTRY is a Student System (IGS) table in Oracle E-Business Suite that records the history of visa-related visits, including the port of entry through which the visit was made. It is a transactional/satellite-style object in the IGS People Enterprise (PE) "person/visa" data area, holding one row per recorded visa visit event. Its role is to persist visit-level detail (start and end dates, entry documentation references, and free-form remarks) that complements the core visa record maintained in IGS_PE_VISA.

From a Data Vault modeling perspective, the ETRM metadata classifies this object as satellite-leaning. This is a heuristic classification mined from the foreign-key structure: the table captures descriptive, time-varying attributes about a business entity (the visa) rather than acting as a hub of unique business keys or a pure link between hubs. Practically, this means the table should be treated as a dependent detail table whose grain is the visit, keyed back to its parent visa.

Key Information Stored

The documented schema contains 32 columns. The most significant are:

• PORT_OF_ENTRY — the port (location) through which the visa holder entered; part of the primary key.
• CNTRY_ENTRY_FORM_NUM — the country entry form number associated with the visit; the other half of the primary key.
• VISA_ID — foreign key to IGS_PE_VISA, tying each visit to its parent visa record.
• VISIT_START_DATE — the date the visit began.
• VISIT_END_DATE — the date the visit ended (or is expected to end).
• REMARKS — free-text notes about the visit.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 … ATTRIBUTE20 — the standard Oracle EBS extensibility (descriptive flexfield) columns for client-specific data.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns maintained by the EBS framework.

The surrogate/composite primary key is IGS_PE_VISIT_HISTRY_PK, defined over (PORT_OF_ENTRY, CNTRY_ENTRY_FORM_NUM). There is no separate single-column surrogate; the documented unique index matches this business key, making PORT_OF_ENTRY and CNTRY_ENTRY_FORM_NUM the natural business-key candidates.

Common Use Cases and Queries

Typical uses include:

• Listing all visits recorded against a specific visa for immigration or SEVIS-style reporting.
• Reporting visits within a date range by port of entry.
• Auditing travel history for a student or scholar.

Sample SQL:

SELECT h.PORT_OF_ENTRY,
       h.CNTRY_ENTRY_FORM_NUM,
       h.VISIT_START_DATE,
       h.VISIT_END_DATE
FROM   IGS.IGS_PE_VISIT_HISTRY h
WHERE  h.VISA_ID = :visa_id
ORDER BY h.VISIT_START_DATE DESC;

Joining to the parent visa:

SELECT v.VISA_ID, h.PORT_OF_ENTRY, h.VISIT_START_DATE
FROM   IGS.IGS_PE_VISA v,
       IGS.IGS_PE_VISIT_HISTRY h
WHERE  v.VISA_ID = h.VISA_ID;

Related Objects

The most significant related object is the parent visa table referenced by the documented foreign key:

• IGS_PE_VISA — joined via IGS_PE_VISIT_HISTRY.VISA_ID → IGS_PE_VISA.VISA_ID; the primary dependency for this satellite.

Associated objects in the IGS People Enterprise area (person and visa processing) — such as person/visa reporting views and the standard audit columns via generic EBS frameworks — depend on this table for visit-level detail. All queries should reference the IGS schema owner.