Results for “ast_ls_cont_adv_v”

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

Overview

AST_LS_CONT_ADV_V is a reporting view within the Oracle E-Business Suite TeleSales (AST) module. It is designed to present a consolidated, denormalized picture of business contacts associated with organizations — specifically, parties that participate in party relationships — together with their contact details, addresses, and telephone information. In the TeleSales context, this view supports activities such as telemarketing, lead qualification, and customer interaction management, where agents require a single flattened record combining an organization's identifier data with the people and contact points linked to it.

The view's name follows the AST naming convention for a "Lead/Sales Contact Advanced View," indicating its role as an advanced query source for contact-centric reporting and integration. It is important to note that the ETRM metadata states Not implemented in this database for the documented instance and lists no referenced base objects. This indicates that the view is defined in the application metadata but may not be deployed in every environment; where it exists, the definition below applies.

Underlying Base Objects

The view is defined over the Oracle Trading Community Architecture (TCA) registry tables. Although the ETRM metadata records no referenced base objects, the documented view text reveals the following underlying tables:

• HZ_PARTY_RELATIONSHIPS — the relationship records (alias REL) linking organizations to persons through SUBJECT_ID and OBJECT_ID.
• HZ_PARTIES — referenced three times as O (the organization), P (the person subject), and R (the relationship party holding the address).
• HZ_ORG_CONTACTS (alias CNT) — organization contact assignments, joined on PARTY_RELATIONSHIP_ID.
• HZ_CONTACT_POINTS (alias CPT) — phone contact points, joined with outer joins on the party and filtered to active phone records.

The join structure requires the organization type to be 'ORGANIZATION' and the relationship status to be 'A' (active). Address columns are sourced from the HZ_PARTIES record identified by REL.PARTY_ID, while phone data is optional due to the (+) outer-join notation.

Key Columns

The view exposes a broad set of contact and organization attributes:

• PARTY_NAME, PARTY_NUMBER, PARTY_ID — organization name and identifier.
• PARTY_CONTACT_ID, PARTY_CONTACT_NAME, PARTY_TYPE — the contact identifier, with PARTY_TYPE hard-coded as 'PARTY_RELATIONSHIP'.
• PERSON_ID, PERSON_NAME, PERSON_FIRST_NAME, PERSON_LAST_NAME, FULL_NAME — the individual associated with the organization.
• COMPETITOR_FLAG, CUSTOMER_KEY, STATUS, SIC_CODE, FISCAL_YEAREND_MONTH, CURRENT_FY_REVENUE, TAX_REFERENCE — organization classification and profiling data. CURRENT_FY_REVENUE is aliased from CURR_FY_POTENTIAL_REVENUE.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY, EMAIL_ADDRESS — location details of the relationship party.
• CONTACT_NUMBER, CONTACT_KEY, ORG_CONTACT_ID, JOB_TITLE, DEPARTMENT_CODE, DEPARTMENT — role 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 — phone information, with FULL_PHONE_NUMBER concatenated for display.
• PARTY_RELATIONSHIP_TYPE, PARTY_RELATIONSHIP_ID — the relationship classification and key.

Common Use Cases and Queries

Because the view flattens TCA relationships, addresses, and phones into one row, it is well suited to list-style reporting in TeleSales, such as building call lists of organization contacts with reachable phone numbers.

• Retrieving active contacts for a specific organization:

SELECT party_name, full_name, job_title, full_phone_number
FROM   ast_ls_cont_adv_v
WHERE  party_number = :org_number;

• Listing competitors and customers with contact numbers:

SELECT party_name, competitor_flag, phone_number
FROM   ast_ls_cont_adv_v
WHERE  competitor_flag = 'Y';

• Exporting contacts by city or country for territory planning:

SELECT party_name, full_name, city, country
FROM   ast_ls_cont_adv_v
WHERE  country = :country_code
ORDER BY party_name, full_name;

Queries should account for the outer joins on phone columns, meaning phone fields may be null where no active contact point exists.