Search Results surname_5_char




Overview
Explore in AI Chat

Overview

IGS_PE_DUP_MATCHES_P_V is a database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the IGS — Student System product family. The view is documented as VALID in ETRM for releases 12.1.1 and 12.2.2, and its stated purpose is to expose the duplicate information held about persons. In practice, it presents a flattened, denormalized projection of person-level attributes drawn from the Oracle Human Resources/HZ party model, reshaped into the column naming conventions used by the Student System for duplicate person detection and reconciliation.

Because it is a view rather than a table, it stores no data of its own. Its role in EBS reporting and integration is to provide a stable, read-only interface through which duplicate-person matching processes, data-cleansing routines, and downstream reports can compare candidate person records without needing to understand the joins across HZ_PARTIES, HZ_PERSON_PROFILES, and the person identifier view. This makes it particularly useful when auditing the quality of person records or feeding third-party de-duplication tools.

Underlying Base Objects

The documented view text defines IGS_PE_DUP_MATCHES_P_V over three referenced objects:

The join conditions are P.PARTY_ID = PP.PARTY_ID, an effective-dating predicate of SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND NVL(PP.EFFECTIVE_END_DATE, SYSDATE), and P.PARTY_ID = PIT.PE_PERSON_ID(+). No additional base objects are documented in the ETRM metadata.

Key Columns

The view exposes ten columns, several of which are aliased to match the legacy Student System naming that duplicate-matching logic expects:

  • PERSON_ID — the HZ_PARTIES.PARTY_ID, the unique person key.
  • SURNAME — the person's last name (PERSON_LAST_NAME).
  • SURNAME_5_CHAR — a placeholder column, always NULL, reserved for a five-character surname comparison key.
  • GIVEN_NAMES — the person's first/given name (PERSON_FIRST_NAME). This is the column most relevant to searches for "given_names".
  • GIVEN_NAME_1_CHAR — a placeholder column, always NULL, reserved for a single-character given-name comparison key.
  • SEX — gender from the person profile.
  • BIRTH_DT — date of birth from the person profile.
  • ETHNIC_ORIGIN — declared ethnicity from the person profile.
  • PREF_ALTERNATE_ID — the preferred person identifier from IGS_PE_PERSON_ID_TYPE_V.
  • STATUS — the party status code.

Note that the ETRM column listing shows "ETHNIC_ORIGIN" while the view text aliases DECLARED_ETHNICITY to "ETHNIC_ORIGIN_ID"; consumers should verify the actual column name against their database instance.

Common Use Cases and Queries

Typical uses include identifying potential duplicate persons by matching surname, given names, birth date, or gender, and reconciling records against a preferred alternate identifier.

List candidate duplicates on surname and given name:

  • SELECT person_id, surname, given_names, birth_dt FROM igs_pe_dup_matches_p_v WHERE UPPER(given_names) = UPPER(:p_given_names) AND UPPER(surname) = UPPER(:p_surname);

Find persons sharing a birth date and sex:

  • SELECT surname, given_names, COUNT(*) FROM igs_pe_dup_matches_p_v GROUP BY surname, given_names HAVING COUNT(*) > 1;

Restrict to active parties with a preferred alternate id:

  • SELECT person_id, given_names, surname, pref_alternate_id FROM igs_pe_dup_matches_p_v WHERE status = 'A' AND pref_alternate_id IS NOT NULL;

Because the view performs the effective-dating and outer-join logic internally, these queries remain simple and are safe for reporting and integration extracts.