Search Results ast_lm_rel_smartsearch_v




Overview
Explore in AI Chat

Overview

The AST_LM_REL_SMARTSEARCH_V view is a TeleSales (AST) module database object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is a reporting and integration view that consolidates party, relationship, contact, and address information used by the TeleSales "Smart Search" functionality, which matches and scores contacts against a search context. The view exposes denormalized data drawn from the TCA (Trading Community Architecture) party model, including organization, person, and party-relationship records, together with related phone, email, job title, and certification details.

Its purpose is to provide a flattened, query-ready result set that application logic can use without re-joining the underlying normalized HZ tables. The view joins multiple HZ tables (parties, relationships, contact points, org contacts) with reference data (AR_LOOKUPS, FND_TERRITORIES_TL) and a temporary match table (HZ_MATCHED_CONTACTS_GT), and it invokes the ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL function and FND_PROFILE.VALUE to produce a formatted address label and default country context.

Underlying Base Objects

The view is documented as being defined over the following objects:

  • HZ_PARTIES (SYNONYM) — supplies organization (O), person (P), and relationship (R) party records.
  • HZ_RELATIONSHIPS (SYNONYM) and HZ_RELATIONSHIP_TYPES (SYNONYM) — define the links between organizations and persons.
  • HZ_CONTACT_POINTS (SYNONYM) — provides primary phone details.
  • HZ_ORG_CONTACTS (SYNONYM) — supplies job title and contact relationships.
  • HZ_MATCHED_CONTACTS_GT (SYNONYM) — the temporary match table that carries SCORE and SEARCH_CONTEXT_ID for Smart Search matching.
  • FND_TERRITORIES_TL (SYNONYM) — provides territory short names.
  • AR_LOOKUPS (VIEW) — resolves meanings for job title codes, status, and certification levels.
  • ARP_ADDR_LABEL_PKG (PACKAGE) — formats the display address label.
  • FND_PROFILE (PACKAGE) — retrieves the AST_DEFAULT_COUNTRY profile value.

The joins are driven by O.PARTY_TYPE = 'ORGANIZATION' and P.PARTY_TYPE = 'PERSON', with relationship records (R) typed as PARTY_RELATIONSHIP, restricted to active or inactive ('A','I') statuses.

Key Columns

Common Use Cases and Queries

This view is used to display Smart Search results and related contact details in TeleSales-based screens and custom reports. Typical usage retrieves high-scoring matches within a search context.

Example query:

SELECT party_name, person_first_name, person_last_name, job_title, score, search_context_id, email_address, full_phone_number, city, state, country FROM apps.ast_lm_rel_smartsearch_v WHERE score > 50 AND search_context_id = :context_id ORDER BY score DESC;

Another common case is retrieving all contacts related to a given organization with their formatted addresses:

SELECT organization_name, party_name, format_address_label FROM apps.ast_lm_rel_smartsearch_v WHERE party_type = 'PARTY_RELATIONSHIP';

Because the view depends on the HZ_MATCHED_CONTACTS_GT temporary table, its result set is populated during an active Smart Search session; queries executed outside that context may return no match rows. Understanding this dependency is essential for correct integration and reporting.