Search Results person_middle_name

Overview

The ASF_HZ_PERSON_LOV_V view is a registered Oracle E-Business Suite database object owned by the APPS schema and assigned to the ASF – Sales Online product family. It presents a flattened, list-of-values (LOV) oriented projection of person-type party records maintained in the Trading Community Architecture (TCA) registry. Rather than requiring consumers to join or decode the normalized party structures directly, the view exposes a single denormalized row per person party, combining party identification, party name, postal address attributes, and the individual person name components.

Its principal role is to support LOV pickers and lookups within Sales Online and related ASF flows where an end user must select a person — for example, a contact or an individual customer — and where the display must include both an identifying label and sufficient address context to disambiguate similarly named parties. Because it selects from the central HZ_PARTIES registry, it reflects the same master data used across Order Management, Receivables, and other TCA-dependent modules, which makes it equally serviceable as a lightweight reporting source for person records. The STATUS predicate restricts output to active (A) and inactive (I) parties, deliberately excluding other lifecycle states.

Underlying Base Objects

The view text is defined over a single referenced base object, HZ_PARTIES, accessed through a synonym. No joins to HZ_PERSON_PROFILES, HZ_LOCATIONS, or HZ_PARTY_SITES are present in the documented definition; the address and name columns are read directly from columns materialized on the HZ_PARTIES record itself. This keeps the view inexpensive to query but also means the address values are those stored in the party's denormalized address columns rather than a resolved primary site address.

The definition carries the filter WHERE PARTY_TYPE = 'PERSON' AND STATUS IN ('A','I'), so the view is functionally a constrained subset of HZ_PARTIES. Organizations and party relationships beyond person type are not returned. Because the projection is fixed, any column added to the view's consumer expectations must be resolved through the base table or through alternative TCA views.

Key Columns

• PARTY_ID — The unique TCA party identifier; the primary join key back to HZ_PARTIES and to dependent tables such as HZ_CUST_ACCOUNTS.
• PARTY_NAME — The formatted display name of the party, typically the concatenation of the person name components.
• PERSON_LAST_NAME, PERSON_FIRST_NAME, PERSON_MIDDLE_NAME — The individual name elements. PERSON_MIDDLE_NAME is the column most commonly targeted by users searching this view, and it is exposed directly without transformation.
• PERSON_PRE_NAME_ADJUNCT — The name prefix or adjunct (for example, a title or honorific preceding the name).
• STATUS — Party lifecycle status; values returned are A (active) or I (inactive).
• ADDRESS1 through ADDRESS4, CITY, PROVINCE, STATE, POSTAL_CODE, COUNTY, COUNTRY — Postal address attributes used for display and disambiguation.
• PERSON_TITLE, PARTY_RELATIONSHIP_TYPE, PARTY_RELATIONSHIP_ID, RELATIONSHIP_PARTY_ID — Positional placeholders in the documented column list. In the view text these map to three trailing NULL expressions, so they do not carry relationship data despite their names. Consumers must not rely on them for relationship resolution.

Common Use Cases and Queries

Typical usage includes LOV validation, person lookups by partial name, and ad hoc reporting on active person parties. Because PERSON_MIDDLE_NAME is exposed directly, searches that filter or display the middle name are straightforward, though it should be noted that the middle name is frequently null in TCA records.

SELECT party_id,
       party_name,
       person_last_name,
       person_first_name,
       person_middle_name,
       city,
       state,
       country
  FROM apps.asf_hz_person_lov_v
 WHERE status = 'A'
   AND UPPER(person_last_name) = UPPER(:p_last_name)
 ORDER BY person_last_name, person_first_name;

A second common pattern retrieves full address context for a selected party, or performs a name-fragment search across the name components:

SELECT party_id,
       party_name,
       person_first_name || ' ' || NVL(person_middle_name,' ') ||
         ' ' || person_last_name AS formatted_name
  FROM apps.asf_hz_person_lov_v
 WHERE status IN ('A','I')
   AND UPPER(person_middle_name) LIKE UPPER(:p_fragment) || '%';

For reporting that requires resolved primary addresses, relationships, or organization parties, the view is not sufficient on its own; it should be joined to HZ_PARTIES and the relevant TCA site tables, or replaced by a broader registry view. Where only person identification, name components, and denormalized address context are required, ASF_HZ_PERSON_LOV_V provides a simple, stable, and low-cost access path.