Search Results ethnic_origin_id




Overview
Explore in AI Chat

Overview

IGS_PE_DUP_MATCHES_P_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, sitting within the Student Systems / People Enterprise (IGS) product family. Its purpose is to project a consolidated, denormalised set of person attributes drawn from the Oracle Trading Community Architecture (TCA) registry tables. The naming suffix "DUP_MATCHES" indicates that the view is intended to support duplicate-person detection and matching routines, typically invoked during prospective student, applicant, or person registration workflows where an incoming record must be compared against existing parties.

The view is exposed through the "P" variant convention, meaning it presents a restricted projection of a broader underlying cursor used by the duplicate matching engine. Only a subset of the full matching attribute set is surfaced — the fields required for surname, given name, sex, birth date, and ethnicity comparison, plus the preferred alternate identifier. Because it reads live from HZ_PARTIES and its effective-dated profile, the view is suitable for both point-in-time reporting and on-line integration calls.

Underlying Base Objects

The view is defined over three documented objects:

  • HZ_PARTIES P — the TCA master party registry, aliased P and driving the outer query. It supplies the party identifier, person name parts, and party status.
  • HZ_PERSON_PROFILES PP — the person-specific profile extension, aliased PP. It supplies gender, date of birth, and declared ethnicity, and is joined to the party on PARTY_ID.
  • IGS_PE_PERSON_ID_TYPE_V PIT — an IGS view of person identifier types, aliased PIT, outer-joined on PE_PERSON_ID to supply the preferred alternate (API) identifier.

The join to HZ_PERSON_PROFILES is constrained by an effective-dating predicate: SYSDATE must fall between PP.EFFECTIVE_START_DATE and NVL(PP.EFFECTIVE_END_DATE, SYSDATE). This ensures only the currently active profile row for each party participates in the result, preventing duplicate rows from historical profile versions. The join to IGS_PE_PERSON_ID_TYPE_V is an outer join (+), so parties without a preferred alternate identifier are still returned, with PREF_ALTERNATE_ID null.

Key Columns

  • PERSON_ID — sourced from HZ_PARTIES.PARTY_ID; the unique party identifier used as the matching key.
  • SURNAME — PERSON_LAST_NAME from HZ_PARTIES.
  • GIVEN_NAMES — PERSON_FIRST_NAME from HZ_PARTIES.
  • SURNAME_5_CHAR / GIVEN_NAME_1_CHAR — hard-coded NULL placeholders retained for interface compatibility with the fuller matching cursor.
  • SEX — HZ_PERSON_PROFILES.GENDER.
  • BIRTH_DT — HZ_PERSON_PROFILES.DATE_OF_BIRTH, the primary discriminator in duplicate screening.
  • ETHNIC_ORIGIN_ID — mapped from HZ_PERSON_PROFILES.DECLARED_ETHNICITY. Although named with an "_ID" suffix, this column carries the declared ethnicity value as stored in the person profile; it is the field targeted by the search term "ethnic_origin_id" and represents the ethnicity attribute used in match scoring.
  • PREF_ALTERNATE_ID — API_PERSON_ID from the IGS person identifier view; the preferred system-generated alternate key.
  • STATUS — HZ_PARTIES.STATUS, indicating active or inactive party state.

Common Use Cases and Queries

The principal use case is duplicate candidate retrieval prior to creating a new party. A caller passes surname, given name, and date of birth fragments and inspects the returned SEX, BIRTH_DT, and ETHNIC_ORIGIN_ID to score potential matches.

SELECT person_id, surname, given_names, sex, birth_dt,
       ethnic_origin_id, pref_alternate_id
  FROM apps.igs_pe_dup_matches_p_v
 WHERE UPPER(surname) = UPPER(:p_surname)
   AND birth_dt = :p_birth_dt;

A second scenario filters on ethnicity for cohort analysis or equality monitoring:

SELECT person_id, surname, ethnic_origin_id
  FROM apps.igs_pe_dup_matches_p_v
 WHERE ethnic_origin_id IS NOT NULL
   AND status = 'A';

Because the view performs no filtering of its own beyond current effective-dating, consumers should always apply their own status, institution, or name predicates to avoid full scans of the party registry. Note that the view is read-only and carries no documented base object grants beyond those listed; direct DML against it is not supported.