Search Results sex_code

Overview

The IGSBV_UC_ENQUIRY_DETAILS view is a base database view owned by the APPS schema within the IGS (Student System) product family of Oracle E-Business Suite. It exposes basic person-level details for a UCAS applicant who is submitting an Extra or Clearing application. UCAS (the Universities and Colleges Admissions Service) is the centralised admissions body used by UK higher education institutions, and the Extra and Clearing processes represent late-cycle admission routes through which applicants may still secure a place. This view provides the foundational applicant demographic and address information required by those processes.

The object is a read-only view, as indicated by the WITH READ ONLY clause in its definition. This constraint guarantees that consumers of the view cannot perform DML operations against it, ensuring that the underlying staging and applicant data remains protected from accidental modification. The view is valid and available in both Oracle EBS 12.1.1 and 12.2.2 environments wherever the IGS product is installed.

Underlying Base Objects

The view is defined over two base tables within the IGS schema:

• IGS_UC_ENQ_DETAILS (alias EN) — the primary source of enquiry-level applicant person data, including name, sex, birth date, address, and contact details.
• IGS_UC_APPLICANTS (alias FT1) — the source of UCAS application-cycle attributes, including system code, Extra passport number, request indicators, Clearing entry form number, and the OSS person identifier.

The two tables are joined on APP_NO using an outer join (EN.APP_NO = FT1.APP_NO (+)), meaning that enquiry detail records are retained even where no corresponding applicant row exists in IGS_UC_APPLICANTS. This design reflects the fact that an enquiry may be captured before a full applicant record is established.

Key Columns

The view exposes a mixture of identity, address, and UCAS-specific process columns:

• UCAS_APPLICANT_NUMBER — the UCAS application number, derived from APP_NO, serving as the primary business key linking enquiries to applicants.
• SURNAME, GIVEN_NAMES, PREFIX — applicant name components.
• SEX_CODE and _LA:SEX_DESC — the stored sex code plus a lookup-derived description resolved against IGS_LOOKUP_VALUES using the SEX lookup type.
• BIRTH_DATE — the applicant's date of birth.
• ADDRESS_LINE1 through ADDRESS_LINE4, COUNTRY, POSTCODE — the correspondence address.
• EMAIL, TELEPHONE — contact channels.
• UCAS_SYSTEM_CODE, EXTRA_PASSPORT_NUMBER — identifiers specific to the UCAS Extra route.
• REQUEST_APPL_DETAILS_IND, REQUEST_COPY_APPL_FORM_IND — flags indicating applicant requests for details or application form copies.
• CLEARING_ENTRY_FORM_NUMBER — the Clearing entry form reference.
• OSS_PERSON_IDENTIFIER — the associated person identifier in the underlying student system.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard Oracle audit columns.

Common Use Cases and Queries

This view is typically consumed by enquiry and admissions reporting, integration extracts, and operational dashboards supporting the Extra and Clearing cycles. A representative query retrieves current enquiries with applicant contact detail:

• SELECT ucas_applicant_number, surname, given_names, _LA:SEX_DESC, birth_date, email, telephone, clearing_entry_form_number FROM igsbv_uc_enquiry_details WHERE clearing_entry_form_number IS NOT NULL; — supports Clearing workload review.
• SELECT ucas_applicant_number, surname, given_names, ucas_system_code, extra_passport_number FROM igsbv_uc_enquiry_details WHERE extra_passport_number IS NOT NULL; — isolates Extra-route applicants.
• SELECT country, COUNT(*) FROM igsbv_uc_enquiry_details GROUP BY country; — geographic analysis of enquiry volume.
• SELECT * FROM igsbv_uc_enquiry_details WHERE creation_date >= TRUNC(SYSDATE) - 30; — recent enquiry activity for operational monitoring.

Because the view is read-only and joins an outer-joined applicant table, consumers should expect nullable UCAS-specific attributes where no applicant row exists, and should apply appropriate NVL or filter logic accordingly.