Results for “igsfv_passports”

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

Overview

IGSFV_PASSPORTS is a read-only Oracle EBS view owned by the APPS schema and classified under the IGS (Student System) product family. It exposes person-level passport information maintained within the Oracle Student System, joining passport records to the Oracle Trading Community Architecture (TCA) party model so that passport data can be reported alongside a stable, human-readable party number. The view is defined WITH READ ONLY, meaning it is intended strictly for query and reporting access; no DML is permitted against it. In Oracle EBS 12.1.1 and 12.2.2 it serves as the reporting and integration surface for passport details, shielding downstream consumers from the underlying normalized tables and the TCA party join. Because the view joins passport data to HZ_PARTIES, it returns only passports whose PERSON_ID resolves to an existing party record, making it a reliable source for person-identified passport extracts, visa and travel reporting, and third-party integrations requiring party-level identity attributes.

Underlying Base Objects

The view is defined over two base objects: IGS_PE_PASSPORT (aliased PEP) and HZ_PARTIES (aliased HZP). The join condition is PEP.PERSON_ID = HZP.PARTY_ID, which links each passport row to its owning party in TCA. This relationship is significant: IGS_PE_PASSPORT holds the transactional passport detail, while HZ_PARTIES supplies the PARTY_NUMBER and the master party identity. The ETRM 12.2.2 metadata documents no additional referenced base objects beyond this pair, and the view text confirms no other tables participate. Consequently, the row grain is one record per passport entry per person; a single party may therefore appear multiple times if multiple passports are recorded. The country code is resolved at runtime through a descriptive flexfield-style lookup reference against PER_US_COUNTRY_CODE rather than through a database join, which means the view stores the raw country code while attribute "_LA:PASSPORT_COUNTRY" surfaces the lookup meaning.

Key Columns

• PERSON_NUMBER — Sourced from HZP.PARTY_NUMBER; the human-readable party identifier used across TCA.
• PASSPORT_NUMBER — The passport document number as recorded on IGS_PE_PASSPORT.
• PASSPORT_COUNTRY_CODE — The issuing country code, validated against the PER_US_COUNTRY_CODE lookup.
• "_LA:PASSPORT_COUNTRY" — A lookup-attribute column that resolves the country code to its descriptive MEANING.
• PASSPORT_EXPIRY_DATE — The expiry date of the passport; the column most frequently used for validity and renewal reporting, and the term the user searched for.
• PERSON_ID — The party identifier linking the passport to HZ_PARTIES.PARTY_ID.
• PASSPORT_ID — The unique primary key of the underlying passport record.
• CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard audit columns for record provenance and change tracking.

Common Use Cases and Queries

Typical scenarios include identifying passports nearing expiry, listing passports by issuing country, and validating that students or applicants hold a current passport before travel, visa processing, or program placement. The view is well suited to Blö Reports, BI Publisher data templates, and inbound/outbound integrations that require party-number-based extracts.

• Expiring passports within a window:

  SELECT person_number, passport_number,
         passport_country_code, passport_expiry_date
    FROM apps.igsfv_passports
   WHERE passport_expiry_date BETWEEN SYSDATE AND SYSDATE + 180
   ORDER BY passport_expiry_date;

• Passports by issuing country:

  SELECT passport_country_code, COUNT(*)
    FROM apps.igsfv_passports
   GROUP BY passport_country_code;

• Passport lookup for a specific party:

  SELECT person_number, passport_number, passport_expiry_date
    FROM apps.igsfv_passports
   WHERE person_number = :party_number;

Because the view is read-only and tightly scoped to two base objects, it is safe for high-volume querying, though reporting consumers should account for the possibility of multiple passport rows per person when aggregating.