Search Results extra_passport_number

Overview

IGSFV_UC_ENQUIRY_DETAILS is a read-only Oracle EBS view owned by the APPS schema within the IGS (Student System) product family. It exposes the basic person details of a UCAS applicant who is making an Extra or Clearing application. UCAS is the United Kingdom's central admissions service, and the Extra and Clearing processes handle applicants seeking places outside the main application cycle. The view therefore serves as a consolidated enquiry surface for admissions staff who need immediate visibility of applicant identity, contact, and application-tracking information.

The object is fully qualified as APPS.IGSFV_UC_ENQUIRY_DETAILS and is defined WITH READ ONLY, meaning it cannot be used for DML and is intended exclusively for query, reporting, and integration purposes. Its role is to join the UCAS enquiry data with the corresponding applicant record, the Oracle Trading Community Architecture party record, and UCAS system defaults, presenting a single denormalised row per enquiry. Historically the view was widely referenced by enquiry forms, OBIEE or BI Publisher reports, and interfaces that reconcile UCAS applicant identifiers with EBS party identifiers.

Underlying Base Objects

Although ETRM 12.2.2 documents no referenced base objects, the view text identifies four source objects:

• IGS_UC_ENQ_DETAILS (alias EN) — the driving table, holding UCAS enquiry applicant number, name, sex, birth date, address, email, telephone, and audit columns.
• IGS_UC_APPLICANTS (alias FT1) — joined on APP_NO and supplying system code, Extra passport number, application-detail and copy-form indicators, Clearing Entry Form number, and the OSS person identifier.
• HZ_PARTIES (alias FT2) — outer-joined on OSS_PERSON_ID = PARTY_ID to retrieve the party number and party name from Trading Community Architecture.
• IGS_UC_DEFAULTS (alias FT3) — outer-joined on SYSTEM_CODE to provide the UCAS system name and description.

All three secondary joins are outer joins (the (+) operator), so the enquiry row is retained even when no applicant, party, or default record exists — an important consideration when interpreting NULLs in reporting.

Key Columns

• UCAS_APPLICANT_NUMBER — the primary applicant identifier (EN.APP_NO).
• SURNAME, GIVEN_NAMES, PREFIX — applicant name components; SEX_CODE and the lookup-driven _LA:SEX_DESC provide the sex meaning.
• BIRTH_DATE, and address columns ADDRESS_LINE1–4, COUNTRY, POSTCODE, EMAIL, TELEPHONE.
• UCAS_SYSTEM_CODE / UCAS_SYSTEM_NAME / UCAS_SYSTEM_DESCRIPTION — identify the UCAS system from which the enquiry originated. This is directly relevant to the search term "extra_passport_number".
• EXTRA_PASSPORT_NUMBER — sourced as FT1.EXTRA_PASSPORT_NO, the Extra applicant passport number from IGS_UC_APPLICANTS. This is the column most commonly retrieved in Extra/clearing enquiries.
• REQUEST_APPL_DETAILS_IND and REQUEST_COPY_APPL_FORM_IND — flags indicating requested application details or copy application form.
• CLEARING_ENTRY_FORM_NUMBER — the CEF number for Clearing applicants.
• OSS_PERSON_IDENTIFIER, OSS_PERSON_NUMBER, OSS_PERSON_NAME — the EBS/HZ party linkage.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — audit columns from the enquiry table.

Common Use Cases and Queries

Typical scenarios include admissions enquiry screens, Clearing applicant lookups, and reconciliation of UCAS Extra applicants to EBS parties. The following queries illustrate practical use:

• Retrieve an applicant by number:
  SELECT ucas_applicant_number, surname, given_names, extra_passport_number, ucAS_system_code FROM igsfv_uc_enquiry_details WHERE ucas_applicant_number = :app_no;
• Search by Extra passport number:
  SELECT ucas_applicant_number, surname, extra_passport_number FROM igsfv_uc_enquiry_details WHERE extra_passport_number = :passport;
• List Clearing applicants with CEF numbers and party linkage:
  SELECT ucas_applicant_number, clearing_entry_form_number, oss_person_number, oss_person_name FROM igsfv_uc_enquiry_details WHERE clearing_entry_form_number IS NOT NULL;
• Report by UCAS system:
  SELECT ucas_system_code, ucas_system_name, COUNT(*) FROM igsfv_uc_enquiry_details GROUP BY ucas_system_code, ucas_system_name;

The view must not be updated; all maintenance is performed against the underlying IGS_UC_ENQ_DETAILS and IGS_UC_APPLICANTS tables via the standard Student System forms and concurrent processes.