Results for “ast_ls_cont_name_v”

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

Overview

AST_LS_CONT_NAME_V is a reporting view within the Oracle EBS TeleSales (AST) module. It presents contact and party relationship information by joining the Oracle Trading Community Architecture (TCA) registries — specifically HZ_PARTIES, HZ_PARTY_RELATIONSHIPS, HZ_ORG_CONTACTS, and HZ_CONTACT_POINTS. Its primary design intent is to surface a denormalized, human-readable view of organization contacts alongside their relationship to the owning person or party, including contact name, phone and email/address detail.

In practice the view is used by TeleSales list generation and contact-lookup screens, where an agent or a concurrent program needs to enumerate contacts belonging to a customer or prospect organization. The distinctive aspect of the view is that it pairs a person (SUBJECT_ID) with an organization or object party (OBJECT_ID) through HZ_PARTY_RELATIONSHIPS, and then attaches the organization contact record (HZ_ORG_CONTACTS) and a primary phone contact point. Because the joins are largely built on TCA, the view is reusable beyond TeleSales for any listing that requires "contact names tied to a party."

The documentation explicitly records that the view is "Not implemented in this database," meaning the object may exist only as a seeded definition and not be created/compiled in a given environment. Analysts should verify existence before relying on it in custom code.

Underlying Base Objects

The view is defined over five TCA tables: HZ_PARTY_RELATIONSHIPS (alias REL), HZ_PARTIES as the person (P), HZ_PARTIES as the organization/object (O), HZ_PARTIES as the relationship party (R), HZ_CONTACT_POINTS (CPT, outer-joined), and HZ_ORG_CONTACTS (CNT). The join logic is:

• P.PARTY_TYPE = 'PERSON' and P.PARTY_ID = REL.SUBJECT_ID — restricts to person subjects.
• REL.STATUS = 'A' — only active relationships.
• REL.OBJECT_ID = O.PARTY_ID — the target party (typically an organization).
• REL.PARTY_RELATIONSHIP_ID = CNT.PARTY_RELATIONSHIP_ID — attaches the org contact record.
• R.PARTY_ID = REL.PARTY_ID — the relationship's own party.
• CPT is outer-joined on OWNER_TABLE_NAME='HZ_PARTIES' and OWNER_TABLE_ID=REL.PARTY_ID, filtered to PRIMARY_FLAG='Y', CONTACT_POINT_TYPE='PHONE', STATUS='A' — so a primary phone is returned when available but does not eliminate rows when absent.

All name, address, and phone data therefore originates in TCA; the TeleSales module supplies the presentation/consumer layer rather than a dedicated base table of its own.

Key Columns

The projected columns map cleanly to party, contact, and contact-point attributes:

• PARTY_NAME, PARTY_ID, PARTY_NUMBER, PARTY_CONTACT_ID, PARTY_TYPE — identity of the organization/object party, with PARTY_TYPE hard-coded to the literal 'PARTY_RELATIONSHIP'.
• PERSON_ID, PERSON_NAME, PERSON_FIRST_NAME, PERSON_LAST_NAME, FULL_NAME — the person subject, with FULL_NAME concatenated from first and last name.
• EMAIL_ADDRESS, ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — the relationship party's address and email.
• CONTACT_NUMBER, CONTACT_KEY, ORG_CONTACT_ID, JOB_TITLE, DEPARTMENT_CODE, DEPARTMENT — organization contact attributes from HZ_ORG_CONTACTS.
• CONTACT_POINT_ID, CONTACT_POINT_TYPE, TELEPHONE_TYPE, PHONE_LINE_TYPE, PHONE_COUNTRY_CODE, PHONE_AREA_CODE, PHONE_NUMBER, PHONE_EXTENSION, FULL_PHONE_NUMBER — primary phone detail, with FULL_PHONE_NUMBER assembled via concatenation and NVL defaults.
• PARTY_RELATIONSHIP_TYPE, PARTY_RELATIONSHIP_ID — the classification of the relationship linking person and organization.
• COMPETITOR_FLAG, CUSTOMER_KEY, STATUS, SIC_CODE, FISCAL_YEAREND_MONTH, CURRENT_FY_REVENUE, TAX_REFERENCE — organization-level attributes (CURRENT_FY_REVENUE is renamed from CURR_FY_POTENTIAL_REVENUE).

Common Use Cases and Queries

The view supports contact name resolution for an organization, phone-based lookups, and list-building for TeleSales campaigns. A typical query retrieving contact names and phones for a given organization is:

SELECT PARTY_ID, PARTY_NAME, PERSON_ID, FULL_NAME,
       JOB_TITLE, EMAIL_ADDRESS, FULL_PHONE_NUMBER,
       PARTY_RELATIONSHIP_TYPE
FROM   AST_LS_CONT_NAME_V
WHERE  PARTY_ID = :p_party_id
ORDER BY FULL_NAME;

A name search for a user-supplied contact or brand/contact line may be expressed as:

SELECT PARTY_NAME, FULL_NAME, FULL_PHONE_NUMBER
FROM   AST_LS_CONT_NAME_V
WHERE  UPPER(FULL_NAME) LIKE UPPER('%' || :search_text || '%')
   OR  UPPER(PARTY_NAME) LIKE UPPER('%' || :search_text || '%');

Because the view does not expose a brand-name column directly, brand-level reporting should be resolved against the party/organization records joined by PARTY_ID. Since the object is documented as "not implemented," environments should confirm the view exists (for example, via ALL_VIEWS) before use, and any dependent custom report should be validated against the target 12.1.1 or 12.2.2 instance.