Search Results phone_area_number




Overview
Explore in AI Chat

Overview

APPS.AST_LM_PER_FUZZY_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments that exposes person-type party records participating in the Oracle Data Librarian (formerly Trading Community Architecture / Customer Data Hub) fuzzy matching process. The name prefix AST_LM denotes an Oracle Data Librarian ("LM" = List Management / matching) object, and the _V suffix indicates a view rather than a base table. Its rowset is not a static list of parties but, critically, a dynamically populated set driven by the global temporary table HZ_MATCHED_PARTIES_GT. Only parties whose PARTY_ID is present in that global temporary table — i.e., the candidates produced by a fuzzy match run — appear in the result set. This makes the view a transient, session-scoped component of the de-duplication and party-consolidation workflow rather than a general-purpose master data source.

The view surfaces identity, address, status, email, and — of particular interest given the search term primary_phone_number — telephone contact attributes for person parties, along with a pre-formatted telephone string and a concatenated area-code-plus-number field. It therefore supports match-review user interfaces and downstream reporting that need formatted contact information for candidate duplicate persons.

Underlying Base Objects

Per documented ETRM 12.2.2 metadata, the view is owned by APPS and is defined over the following referenced base objects:

  • HZ_PARTIES (synonym) — the primary party source, aliased PARTY, supplying all person name, address, email, status, country, and phone columns.
  • HZ_MATCHED_PARTIES_GT (synonym) — a global temporary table aliased SMARTPER, supplying SEARCH_CONTEXT_ID and constraining the result set via SMARTPER.PARTY_ID = PARTY.PARTY_ID.
  • AR_LOOKUPS (view) — aliased ALK, joined on LOOKUP_TYPE = 'CODE_STATUS' and LOOKUP_CODE = PARTY.STATUS to translate the status code into a meaningful description.
  • FND_TERRITORIES_TL (synonym) — aliased FTT, an outer-joined ((+), Oracle pre-12c syntax) translation table keyed on TERRITORY_CODE and language, supplying TERRITORY_SHORT_NAME.

The join filters restrict output to PARTY_TYPE = 'PERSON' and STATUS IN ('A','I') (Active and Inactive), excluding other party types and statuses such as 'M' (merged).

Key Columns

  • PARTY_ID — primary key from HZ_PARTIES; the correlation key with the matched-parties temporary table.
  • CUST_ACCOUNT_ID — explicitly TO_NUMBER(NULL), so always null for person parties; present only to align the view's column list with other fuzzy-match views used in the same interface.
  • PERSON_FIRST_NAME, PERSON_MIDDLE_NAME, PERSON_LAST_NAME — person name components.
  • Concatenated address (ADDRESS1||ADDRESS2||ADDRESS3||ADDRESS4) plus CITY, STATE, POSTAL_CODE, PROVINCE, COUNTRY, and TERRITORY_SHORT_NAME.
  • EMAIL_ADDRESS, STATUS, and MEANING (the decoded status from AR_LOOKUPS).
  • SEARCH_CONTEXT_ID — links matched persons back to a specific match context / fuzzy match execution.
  • PRIMARY_PHONE_COUNTRY_CODE, PRIMARY_PHONE_AREA_CODE, PRIMARY_PHONE_NUMBER, PRIMARY_PHONE_EXTENSION — raw telecom components.
  • Formatted phone: NVL(country,' ')||'-'||NVL(area_code,' ')||'-'||primary_phone_number||''||primary_phone_extension, plus a concatenation of PRIMARY_PHONE_AREA_CODE||PRIMARY_PHONE_NUMBER.

Common Use Cases and Queries

Typical usage is within the fuzzy-match review flow, where a matching program first populates HZ_MATCHED_PARTIES_GT for the current session and then queries this view to display candidates. Because the GTT is session-scoped, the view returns rows only after a match process has run in the same session.

Retrieve formatted contact details, prioritizing the phone number:

  • SELECT party_id, person_first_name, person_last_name, primary_phone_number, primary_phone_area_code||primary_phone_number AS full_phone FROM apps.ast_lm_per_fuzzy_v WHERE primary_phone_number IS NOT NULL;
  • SELECT party_id, person_last_name, city, meaning, email_address FROM apps.ast_lm_per_fuzzy_v WHERE status = 'A';
  • SELECT search_context_id, COUNT(*) FROM apps.ast_lm_per_fuzzy_v GROUP BY search_context_id;

Given the semantics of the underlying temporary table, the view should not be used for broad, persistent party reporting; for those purposes query HZ_PARTIES and the HZ_CONTACT_POINTS hierarchy directly.