Results for “ast_ls_cons_addr_v”

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

Overview

The AST_LS_CONS_ADDR_V view is a TeleSales (AST) reporting object within Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a consolidated, denormalized read-only projection of party, address, and telephone contact point data for individuals classified as persons. The view exists primarily to support TeleSales agent-facing inquiries and downstream reporting where a single flattened record per party—enriched with a formatted telephone string—is preferable to joining the normalized TCA (Trading Community Architecture) tables directly.

The view is documented in ETRM metadata as "Not implemented in this database," indicating it is a seeded dictionary object whose physical presence depends on whether the TeleSales module is installed and its related schema objects have been deployed. Its central practical value is the derived FULL_PHONE_NUMBER column, which concatenates country code, area code, phone number, and extension into a single display string.

Underlying Base Objects

The view is defined over two TCA base tables:

• HZ_PARTIES (aliased CST) — the driving table supplying party identity, name, party number, party type, category, status, e-mail address, and the standalone address attributes (ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY).
• HZ_CONTACT_POINTS (aliased CPT) — joined on CPT.OWNER_TABLE_ID(+) = CST.PARTY_ID with the owner table constrained to 'HZ_PARTIES', and further filtered to phone contact points that are primary and active.

The outer join (+) syntax on the contact point side ensures parties without a qualifying phone record are still returned. The ETRM metadata documents no additional referenced base objects; the view joins TCA directly rather than through intermediate AST tables.

Key Columns

• PARTY_ID / PARTY_NAME / PARTY_NUMBER — unique party identifier, display name, and system-assigned party number.
• FULL_NAME — derived as PERSON_FIRST_NAME || ' ' || PERSON_LAST_NAME.
• CONTACT_POINT_ID — identifier of the associated HZ_CONTACT_POINTS phone record.
• PHONE_COUNTRY_CODE, PHONE_AREA_CODE, PHONE_NUMBER, PHONE_EXTENSION — component phone attributes from HZ_CONTACT_POINTS.
• FULL_PHONE_NUMBER — the key derived column: NVL(country,' ')||'-'||NVL(area,' ')||'-'||PHONE_NUMBER||' '||PHONE_EXTENSION. This is the column surfaced when users search for "full_phone_number," and it explains why the search resolves to this view.
• CONTACT_POINT_TYPE / TELEPHONE_TYPE / PHONE_LINE_TYPE — contact point classification attributes; CONTACT_POINT_TYPE is constrained to 'PHONE'.
• STATUS, CATEGORY_CODE, COMPETITOR_FLAG, CUSTOMER_KEY — party-level classification and status flags.
• PARTY_SITE_NUMBER, ADDRESS_KEY, LOCATION_ID — returned as literal NULLs, as the view does not resolve party-site or location identifiers.

Common Use Cases and Queries

Typical usage includes TeleSales call-list preparation, telephone-based customer lookup, and outbound integration extracts requiring a formatted dial string. A representative query:

• SELECT party_id, party_name, full_name, full_phone_number, city, country FROM ast_ls_cons_addr_v WHERE full_phone_number LIKE '%555%'; — locate parties by phone fragment.
• SELECT party_id, full_name, full_phone_number FROM ast_ls_cons_addr_v WHERE party_type = 'PERSON' AND status = 'A'; — enumerate active person parties with phone data.
• SELECT customer_key, full_name, email_address, full_phone_number FROM ast_ls_cons_addr_v ORDER BY party_name; — build a contact directory extract.

Note that only primary, active phone contact points are exposed, and the outer join means FULL_PHONE_NUMBER may be partially populated or null for parties lacking such a record.