Results for “port_of_entry_m”

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

Overview

IGS_PE_VISIT_HISTORY_V is a reporting view in the Oracle E-Business Suite IGS (Student System) product, which is documented as obsolete in ETRM 12.2.2. The view consolidates visa and visit-history information for a person, exposing fields that describe entry into the United States together with the visitor's visa and passport details. Its purpose is to provide a single, denormalized read interface that joins the person's visa record to each recorded visit, so that downstream reports, inquiries, or integrations can retrieve visit history without reconstructing the multi-table join themselves.

The user's search term, pe_us_port_of_entry, corresponds directly to the lookup type referenced in the view's definition. That lookup type supplies the descriptive meaning for the numeric or coded PORT_OF_ENTRY value stored on the visit-history record. The view therefore serves a dual role: it is both a person-centric visit-history query source and a convenience layer over the PE_US_PORT_OF_ENTRY lookup, translating a stored code into a user-readable port-of-entry label.

Because the underlying IGS Student System functionality was retired, the view is retained primarily for historical reference. The ETRM metadata records that the view is not implemented in the database documented by the ETRM, meaning no live definition or data is available for that instance.

Underlying Base Objects

The view text, as documented in the ETRM metadata, is defined over three objects:

• IGS_PE_VISIT_HISTORY — aliased as VV. This is the primary table and supplies the visit-specific columns, including PORT_OF_ENTRY, CNTRY_ENTRY_FORM_NUM, VISA_ID, VISIT_START_DATE, VISIT_END_DATE, REMARKS, and the twenty descriptive flexfield attribute columns.
• IGS_PE_VISA — aliased as PV. This table is joined on VISA_ID and contributes PERSON_ID and the visa-related attributes: VISA_TYPE, VISA_NUMBER, VISA_ISSUE_DATE, VISA_EXPIRY_DATE, VISA_CATEGORY, VISA_ISSUING_POST, and PASSPORT_ID.
• IGS_LOOKUP_VALUES — aliased as L1. This table is outer-joined to the visit-history record using LOOKUP_TYPE = 'PE_US_PORT_OF_ENTRY' and LOOKUP_CODE = VV.PORT_OF_ENTRY, and it supplies the MEANING value returned as PORT_OF_ENTRY_M.

The ETRM metadata lists "none documented" for referenced base objects, but the embedded view text provides the authoritative join relationships described above. The join between IGS_PE_VISIT_HISTORY and IGS_PE_VISA is an inner join on VISA_ID, while the lookup join is an outer join (indicated by the (+) operator on the L1 conditions), ensuring that visit records remain visible even when no matching lookup value exists.

Key Columns

• ROW_ID — the ROWID of the IGS_PE_VISIT_HISTORY row, exposed for unique row identification.
• PORT_OF_ENTRY / PORT_OF_ENTRY_M — the stored port-of-entry code and its decoded meaning from the PE_US_PORT_OF_ENTRY lookup.
• VISA_ID — the join key linking the visit record to the person's visa record.
• PERSON_ID — the person identifier, sourced from the visa table.
• VISA_TYPE, VISA_NUMBER, VISA_CATEGORY, VISA_ISSUING_POST — visa classification and issuance attributes.
• VISA_ISSUE_DATE, VISA_EXPIRY_DATE — validity dates of the visa.
• PASSPORT_ID — the associated passport reference.
• VISIT_START_DATE, VISIT_END_DATE — the period of the visit.
• CNTRY_ENTRY_FORM_NUM, REMARKS — the country entry form number and free-text notes.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–20 — descriptive flexfield context and segment values.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

A typical use is retrieving all visits for a person, including decoded port-of-entry and visa details:

SELECT person_id, visa_number, visa_type, port_of_entry_m,
       visit_start_date, visit_end_date
FROM   igs_pe_visit_history_v
WHERE  person_id = :person_id
ORDER  BY visit_start_date;

Reports validating lookup integrity can select records where the port-of-entry meaning is missing, indicating an unmatched lookup code:

SELECT row_id, port_of_entry, visa_id
FROM   igs_pe_visit_history_v
WHERE  port_of_entry IS NOT NULL
AND    port_of_entry_m IS NULL;

Because IGS Student System is obsolete and the view is not implemented in the ETRM database, these queries apply to legacy or archived instances. In current 12.1.1 / 12.2.2 environments the view should be treated as documented reference metadata rather than an available runtime object.