Search Results passport_cntry_code




Overview
Explore in AI Chat

Overview

IGSFV_PASSPORTS is a read-only Oracle E-Business Suite view owned by the APPS schema and defined over the passport and party data model used by Oracle Student System (formerly Oracle iLearning/Student Information) and Oracle Human Resources. It presents a consolidated, denormalized listing of person passport records, joining each passport row in the base passport table to its corresponding party record so that the HZ_PARTIES party number is available alongside the passport attributes. The view is exposed as a reporting and integration object: because it is created with the WITH READ ONLY clause, it cannot be used for DML and is intended strictly for query access, extracts, and API/integration lookups. The presence of the leading underscore literal in the third column ('_LA:PEP.PASSPORT_CNTRY_CODE:HR_LOOKUPS:PER_US_COUNTRY_CODE:MEANING') indicates the view is also used by Oracle's descriptive flexfield / list-of-values (LOV) substitution mechanism, where the string instructs the framework to resolve the passport country code to its meaning from the HR_LOOKUPS view using the lookup type PER_US_COUNTRY_CODE. This makes the object relevant both to ad hoc SQL reporting and to EBS form-based LOV rendering in the student and person management modules.

Underlying Base Objects

The documented view definition joins two base tables:

  • IGS_PE_PASSPORT (aliased PEP) — the source table holding the passport records, including the passport number, issuing country code, expiry date, and the person identifier.
  • HZ_PARTIES (aliased HZP) — the Trading Community Architecture party table, supplying the human-readable PARTY_NUMBER.

The join predicate is PEP.PERSON_ID = HZP.PARTY_ID, which links a person's passport record to the party record representing that same person or organization. The ETRM metadata for the 12.2.2 release documents the view owner as APPS and lists no separately documented base objects beyond those appearing in the view text itself, so the join above is the authoritative definition. All columns that carry audit attributes (CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE) originate from IGS_PE_PASSPORT, while PARTY_NUMBER originates from HZ_PARTIES.

Key Columns

  • PARTY_NUMBER — the TCA party number from HZ_PARTIES; the business-facing identifier for the person.
  • PASSPORT_NUMBER — the passport document number as recorded on IGS_PE_PASSPORT.
  • PASSPORT_CNTRY_CODE — the country code of the issuing authority. This is the column referenced by the passport_cntry_code search. It is resolved for display through HR_LOOKUPS lookup type PER_US_COUNTRY_CODE.
  • PASSPORT_EXPIRY_DATE — the expiration date of the passport.
  • PERSON_ID — the person identifier, equal to the HZ_PARTIES party identifier in the join.
  • PASSPORT_ID — the surrogate primary key of the passport row in IGS_PE_PASSPORT.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE, supporting change tracking and data lineage.

Common Use Cases and Queries

Typical uses include student visa and immigration reporting, identification verification for admissions, and extracts feeding downstream systems that must reconcile a person's passport data to their party record. The following query lists passports for a given person:

  • SELECT party_number, passport_number, passport_cntry_code, passport_expiry_date FROM apps.igsfv_passports WHERE person_id = :person_id;

To obtain the descriptive meaning of the country code, join to HR_LOOKUPS:

  • SELECT p.party_number, p.passport_number, p.passport_cntry_code, l.meaning FROM apps.igsfv_passports p, hr_lookups l WHERE l.lookup_type = 'PER_US_COUNTRY_CODE' AND l.lookup_code = p.passport_cntry_code AND l.enabled_flag = 'Y';

Expiry monitoring for compliance reporting follows a similar pattern using PASSPORT_EXPIRY_DATE as the filter predicate. Because the view is read-only, all maintenance of passport data must be performed against IGS_PE_PASSPORT directly or through the supported student/person APIs.