Search Results ast_ls_cons_phone_v

Overview

The AST_LS_CONS_PHONE_V view is a TeleSales (AST) module object in Oracle E-Business Suite 12.1.1 and 12.2.2. It is a reporting and inquiry view that consolidates consumer (person) party records with their primary telephone contact point into a single denormalized row. Its purpose within the TeleSales "consumer phone" function is to present each active person party alongside a fully formatted telephone number, sparing downstream consumers (reports, list-generation screens, telesales scripts, and integration extracts) from having to join and format the telephone segments themselves.

The view exposes one row per party per matching primary phone contact point. Because the join to HZ_CONTACT_POINTS is an outer join (indicated by the "(+)" in the contact point type predicate) and other contact point predicates are applied in the WHERE clause, the effective result set is an inner join on PRIMARY_FLAG = 'Y' and STATUS = 'A'. The view does not expose address or site identifiers as real values: columns such as PARTY_SITE_NUMBER, ADDRESS_KEY, and LOCATION_ID are hard-coded to NULL. Address attributes that are present (ADDRESS1-4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY) are sourced from HZ_PARTIES itself, not from a location table.

Underlying Base Objects

The view text references two Oracle HZ (Trading Community Model) base tables:

• HZ_PARTIES (aliased CST) — supplies party name, party ID, party number, party type, competitor flag, customer key, category code, status, email address, person first/last name, and the address lines.
• HZ_CONTACT_POINTS (aliased CPT) — supplies the contact point ID, contact point type, telephone type, phone line type, and the country code, area code, number, and extension that make up the phone.

The documented metadata records no referenced base objects and notes the view as "Not implemented in this database," meaning it exists in the ETRM reference but may not be deployed in every EBS instance. In practice it is a lightweight, read-only wrapper over the two HZ tables, joined on CPT.OWNER_TABLE_ID = CST.PARTY_ID with CPT.OWNER_TABLE_NAME = 'HZ_PARTIES'.

Key Columns

• PARTY_ID / PARTY_NUMBER / PARTY_NAME — the party's internal identifier, business key, and display name.
• PARTY_TYPE — filtered to 'PERSON', so the view covers consumers only, not organizations.
• PERSON_FIRST_NAME / PERSON_LAST_NAME / FULL_NAME — FULL_NAME is a concatenation of first and last name.
• CONTACT_POINT_ID — identifier of the primary phone contact point.
• PHONE_COUNTRY_CODE / PHONE_AREA_CODE / PHONE_NUMBER / PHONE_EXTENSION — the individual phone segments.
• FULL_PHONE_NUMBER — the concatenation NVL(country,' ')-NVL(area,' ')-number ' 'extension. This is the column most relevant to the "full_phone_number" search: it delivers a single formatted, display-ready telephone string.
• TELEPHONE_TYPE / PHONE_LINE_TYPE / CONTACT_POINT_TYPE — classification of the phone contact point.
• STATUS / EMAIL_ADDRESS / CATEGORY_CODE / COMPETITOR_FLAG / CUSTOMER_KEY — party-level attributes carried through from HZ_PARTIES.

Common Use Cases and Queries

Typical uses include consumer call lists, TeleSales scripts that need a formatted phone number, campaign extracts, and data-quality checks for parties that should have a primary phone. A representative query:

SELECT party_id,
       full_name,
       full_phone_number,
       telephone_type
  FROM ast_ls_cons_phone_v
 WHERE full_phone_number IS NOT NULL
 ORDER BY full_name;

To isolate records whose phone lacks a country or area code, filter on the segment columns directly. To reconcile consumers against contact point data, join back on CONTACT_POINT_ID. Because the view already restricts to active person parties with an active primary phone, no additional status predicates are normally required, though callers may still add party-level filters such as CATEGORY_CODE.