Results for “ast_ls_opportunities_v”

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

Overview

AST_LS_OPPORTUNITIES_V is a reporting view in the Oracle E-Business Suite TeleSales (AST) module that consolidates lead and opportunity information used by the TeleSales application. In Oracle EBS 12.1.1 and 12.2.2, the view is defined over the AS_LEADS_ALL table and its supporting reference and party tables, providing a flattened, denormalized representation of lead/opportunity records along with associated customer, contact, sales representative, campaign, sales stage, and status attributes.

The view exists primarily to support TeleSales forms, dashboards, and custom reporting where a single-row-per-opportunity projection is required without repeatedly joining the lead base table to HZ_PARTIES, PER_ALL_PEOPLE_F, and the various lookup views. It is not implemented as a database object in every instance (the ETRM metadata notes "Not implemented in this database" for one documented environment), but its text is a standard delivered definition and is available wherever the TeleSales product is installed and the object has been created.

Because the view joins person and party tables with outer joins on contacts and accesses, it is well suited to agent-facing lead worklists and pipeline reporting where not every lead has an assigned contact or sales representative.

Underlying Base Objects

The view is defined over the following documented base tables and views:

• AS_LEADS_ALL — the primary lead/opportunity table (aliased OPP), supplying lead ID, lead number, customer ID, org ID, address ID, sales stage ID, status, channel, revenue, win probability, currency, decision date, source, and descriptive flexfield attributes.
• AS_LEAD_CONTACTS_ALL — link table between leads and contact parties (aliased LEAD_CONTACT), joined via LEAD_ID with an outer join.
• AS_ACCESSES_ALL — access records linking leads to persons (aliased ACC), outer-joined via LEAD_ID and PERSON_ID.
• PER_ALL_PEOPLE_F — the HR person/assignment table (aliased PER), outer-joined on PERSON_ID and filtered to the current effective date range, providing sales representative first and last names.
• HZ_PARTIES — referenced twice: once as the customer (PARTY, joined on CUSTOMER_ID) and once as the contact party (PARTY_CONTACT, outer-joined via CONTACT_PARTY_ID), supplying customer name, address fields, and contact first/last names.
• AMS_SOURCE_CODES — campaign/source promotion reference (aliased CAMP), outer-joined on SOURCE_PROMOTION_ID.
• AS_SALES_STAGES_ALL_VL — sales stage lookup view (aliased ASS), joined on SALES_STAGE_ID.
• AS_STATUSES_VL — status lookup view (aliased AST), joined on STATUS_CODE.

Note that the ETRM metadata records no documented base objects for the 12.2.2 environment; the object list above is derived from the view text itself.

Key Columns

The view exposes a broad set of columns, many of which are renamed aliases for clarity:

• LEAD_ID, LEAD_NUMBER — primary identifier and human-readable lead number.
• CUSTOMER_ID, CUSTOMER_NAME — the party ID and party name of the lead's customer (CUSTOMER_NAME is an alias for PARTY.PARTY_NAME).
• CONTACT_FIRST_NAME, CONTACT_LAST_NAME — the primary contact person's names, sourced from the outer-joined PARTY_CONTACT (aliases for PERSON_FIRST_NAME and PERSON_LAST_NAME).
• SALES_REP_FIRST_NAME, SALES_REP_LAST_NAME — the assigned sales representative from PER_ALL_PEOPLE_F.
• SALES_STAGE, STATUS, STATUS_CODE — sales stage name and status meaning from the lookup views.
• REVENUE_AMOUNT, TOTAL_AMOUNT, WIN_PROBABILITY, CURRENCY_CODE, DECISION_DATE — pipeline and forecast measures.
• CAMPAIGN_ID, CAMPAIGN_CODE — the source promotion/campaign reference.
• ADDRESS1 through POSTAL_CODE, CITY, STATE, COUNTRY — customer address components from HZ_PARTIES.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — descriptive flexfield columns.
• CREATION_DATE, LAST_UPDATE_DATE — standard audit columns.

Common Use Cases and Queries

The view is typically used for lead pipeline reporting, agent worklists, and integration extracts. A sample query listing open opportunities with contact and representative details follows:

SELECT lead_number, customer_name, contact_first_name, contact_last_name,
       sales_rep_first_name, sales_rep_last_name, sales_stage,
       status, revenue_amount, win_probability
FROM   ast_ls_opportunities_v
WHERE  status_code = 'OPEN'
ORDER BY decision_date;

Other scenarios include summarizing pipeline by sales stage, identifying leads without an assigned sales representative (where SALES_REP_FIRST_NAME IS NULL), and extracting lead data with address and campaign information for downstream CRM integration. Because the view applies an effective-date filter on PER_ALL_PEOPLE_F and uses outer joins for contacts and accesses, consumers should be aware that leads lacking a current person assignment or contact will still appear, with NULL values in the corresponding columns.