Results for “search_context_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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

• PARTY_NAME, PARTY_ID, PARTY_NUMBER — identify the organization, person, and relationship parties.
• PERSON_FIRST_NAME, PERSON_MIDDLE_NAME, PERSON_LAST_NAME — person name components, plus a concatenated full-name expression.
• JOB_TITLE and JOB_TITLE_CODE — job title text and the lookup meaning for its code.
• SCORE and SEARCH_CONTEXT_ID — Smart Search match score and context identifier from HZ_MATCHED_CONTACTS_GT.
• EMAIL_ADDRESS, URL, FULL_PHONE_NUMBER, PHONE_AREA_CODE, PHONE_COUNTRY_CODE, PHONE_NUMBER, PHONE_EXTENSION — contact point details.
• FORMAT_ADDRESS_LABEL result, ADDRESS, CITY, COUNTY, STATE, POSTAL_CODE, PROVINCE, COUNTRY, TERRITORY_SHORT_NAME — location data.
• STATUS and STATUS_MEAN — relationship status and its lookup meaning.
• CERTIFICATION_LEVEL and CERT_LEVEL_MEAN — organization certification level.
• ACCOUNT_NUMBER and CUST_ACCOUNT_ID — exposed as NULL placeholders in this view.

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.