Results for “ast_ls_org_adv_v”

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

Overview

AST_LS_ORG_ADV_V is a reporting and integration view within the Oracle EBS TeleSales (AST) module. It presents a consolidated, denormalized record set combining organization-level party data with associated telephone contact points, exposing both the party attributes (name, number, category, address, revenue potential) and the phone-related contact point attributes (country code, area code, phone number, extension) in a single row per party/phone combination. Its purpose is to support TeleSales and telesales-adjacent functions that require an agent-facing "organization with phone" listing, such as call-list generation, prospect or customer lookup by phone, and outbound dialing screens that resolve a party to its contactable numbers without joining the underlying HZ tables at runtime.

It is important to note that the ETRM metadata states "Not implemented in this database." This indicates the view is documented but is not deployed in the queried environment; it is expected to exist only in installations where the relevant TeleSales product components have been installed and the object was created. The view is owned by the TeleSales schema in a standard configuration, though owner metadata was not populated in the source.

Underlying Base Objects

The view is defined over two base tables in the Oracle Trading Community Architecture (TCA) model:

• HZ_PARTIES PARTY — the driving table in the FROM clause, supplying organization-level party attributes.
• HZ_CONTACT_POINTS CONT_POINT — joined with outer-join notation (+) so that organizations without a matching phone contact point are still returned.

The join conditions are:

• PARTY.PARTY_TYPE = 'ORGANIZATION'
• PARTY.STATUS = 'A' (active parties only)
• CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE'
• CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
• CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID

Because the contact-point predicates use the (+) operator, the view behaves as a left outer join from HZ_PARTIES to HZ_CONTACT_POINTS. Parties with no phone contact point appear with NULL contact-point columns. The ETRM metadata documents no referenced base objects explicitly, but the embedded view text establishes the two-table relationship above.

Key Columns

The projection includes identity, classification, address, and telephony attributes. Notable columns and their meaning:

• PARTY_ID, PARTY_NUMBER, PARTY_NAME — the TCA party identifier, the user-visible party number, and the organization name.
• PARTY_TYPE, CATEGORY_CODE, STATUS — classification flags; PARTY_TYPE is always 'ORGANIZATION' and STATUS is 'A' given the filter predicates.
• CUSTOMER_KEY, COMPETITOR_FLAG, SIC_CODE, TAX_REFERENCE — customer/competitor designation and standard reference codes.
• CURRENT_FY_REVENUE — aliased from PARTY.CURR_FY_POTENTIAL_REVENUE, the current fiscal-year potential revenue.
• FISCAL_YEAREND_MONTH — the party's fiscal year-end month.
• CONTACT_POINT_ID, PRIMARY_FLAG, TELEPHONE_TYPE, PHONE_LINE_TYPE — identify and characterize the contact point.
• PHONE_COUNTRY_CODE, PHONE_AREA_CODE, PHONE_NUMBER, PHONE_EXTENSION — the individual phone components.
• FULL_PHONE_NUMBER — a concatenated display value built as country code, dash, area code, dash, phone number, space, extension, using NVL to substitute a space for missing country/area codes.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — the party's address attributes.
• EMAIL_ADDRESS — from the party record.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and ORIG_SYSTEM_REFERENCE, primarily from the contact point.

Common Use Cases and Queries

Typical scenarios include building telesales call lists, validating that an organization has a phone number before dialing, and producing organization contact-list extracts. A basic query filtering for a named organization is shown below.

• Look up active organizations with phone numbers:
  SELECT party_name, party_number, full_phone_number, city, country FROM ast_ls_org_adv_v WHERE party_type = 'ORGANIZATION' AND status = 'A';
• Retrieve all phone contact points for a given party:
  SELECT party_name, contact_point_id, telephone_type, full_phone_number, primary_flag FROM ast_ls_org_adv_v WHERE party_id = :p_party_id;
• Identify organizations lacking a phone contact point (NULL contact-point columns indicate no phone):
  SELECT party_name, party_number FROM ast_ls_org_adv_v WHERE contact_point_id IS NULL;
• Search by partial phone number for inbound-call resolution:
  SELECT party_name, full_phone_number FROM ast_ls_org_adv_v WHERE phone_number LIKE '%'||:p_number||'%';

Because the join is outer, queries that assume a phone always exists should filter on CONTACT_POINT_ID IS NOT NULL. Where the view reports as not implemented, equivalent results can be obtained by querying HZ_PARTIES and HZ_CONTACT_POINTS directly with the same join conditions.