Search Results ast_ls_cons_adv_v

Overview

AST_LS_CONS_ADV_V is a reporting view within the Oracle EBS TeleSales (AST) module. Its name reflects its purpose: it exposes "consumer" (person-type) party records together with their associated contact point data, forming an "advanced" consolidated listing suitable for TeleSales inquiry and search operations. The view is defined over the Trading Community Architecture (TCA) registry tables and presents a flattened, denormalized row per person party, joined to that party's active phone contact points.

In the context of Oracle EBS 12.1.1 and 12.2.2, this view serves reporting and integration requirements rather than transactional data entry. It allows TeleSales agents, concurrent programs, and external integrations to retrieve a consolidated directory of active person customers along with their telephone numbers, without navigating the multi-table TCA model directly. Note that the ETRM metadata records this object as "Not implemented in this database," meaning the view definition is documented but the object was not deployed in the reference environment from which the metadata was harvested.

Underlying Base Objects

The view text is constructed from two documented TCA base tables:

• HZ_PARTIES — aliased as CST. Supplies the party identity, classification, and party-level address attributes (party name, party number, party type, status, and address columns 1 through 4, city, state, province, county, postal code, and country).
• HZ_CONTACT_POINTS — aliased as CPT. Supplies the telephone contact point attributes, including contact point type, telephone type, phone line type, country code, area code, phone number, and extension.

The join is an outer join driven from HZ_PARTIES to HZ_CONTACT_POINTS, keyed on CPT.OWNER_TABLE_NAME = 'HZ_PARTIES' and CPT.OWNER_TABLE_ID = CST.PARTY_ID. Filter predicates restrict the result set to person parties (PARTY_TYPE = 'PERSON') with active status (STATUS = 'A'), and — where a contact point exists — to active PHONE-type contact points (CPT.STATUS = 'A').

Key Columns

• PARTY_ID / PARTY_NUMBER / PARTY_NAME — Primary identity and display attributes of the person party.
• PARTY_TYPE / STATUS / CATEGORY_CODE — Classification attributes; PARTY_TYPE is always 'PERSON', while STATUS reflects the active flag.
• COMPETITOR_FLAG / CUSTOMER_KEY — Flags indicating competitor status and the customer surrogate key.
• PERSON_FIRST_NAME / PERSON_LAST_NAME / FULL_NAME — Person name components; FULL_NAME is a concatenation of first and last name.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — Party-level address attributes.
• PARTY_SITE_ID / PARTY_SITE_NUMBER / ADDRESS_KEY / LOCATION_ID — Present in the projection but returned as NULL by the view definition.
• CONTACT_POINT_ID / CONTACT_POINT_TYPE / TELEPHONE_TYPE / PHONE_LINE_TYPE — Contact point identity and classification; CONTACT_POINT_TYPE is restricted to 'PHONE'.
• PHONE_COUNTRY_CODE / PHONE_AREA_CODE / PHONE_NUMBER / PHONE_EXTENSION — Raw telephone components.
• FULL_PHONE_NUMBER — A formatted concatenation of the country code, area code, phone number, and extension.

Common Use Cases and Queries

Typical uses include building TeleSales prospect and customer directories, populating agent search screens with phone-enabled person records, and feeding outbound campaign or dialing lists. The view is also convenient for ad-hoc reporting where a single query is preferred over multi-table TCA joins.

SELECT PARTY_NAME,
       FULL_NAME,
       FULL_PHONE_NUMBER,
       CITY,
       COUNTRY
  FROM AST_LS_CONS_ADV_V
 WHERE STATUS = 'A'
   AND COMPETITOR_FLAG = 'N'
 ORDER BY PARTY_NAME;

Because the join to HZ_CONTACT_POINTS is an outer join, parties without a telephone contact point are still returned, with the contact point columns NULL. A count of active person parties with at least one active phone number can be obtained by filtering on CONTACT_POINT_ID IS NOT NULL. Organizations should verify whether the view exists in their database before relying on it, given the "Not implemented" annotation in the documented metadata.