Search Results ast_ls_opp_bsc_v

Overview

AST_LS_OPP_BSC_V is a reporting view in the Oracle EBS TeleSales (AST) module. Its name follows the EBS convention for "Lead Source Opportunity Basic" views: an LS (Lead Source) oriented projection over opportunity data. The view consolidates lead and opportunity records from AS_LEADS_ALL with customer party details and lookup descriptions, producing a single denormalized result set suitable for operational reporting, list-of-values queries, and downstream integrations.

The view is catalogued in ETRM 12.2.2 metadata. The metadata notes that the object is not implemented in the database from which the ETRM documentation was extracted, meaning the DDL is documented but the object may not be deployed in every environment. Confirm existence against ALL_VIEWS before use. The view text documented above is the authoritative definition for environments where it is present.

Underlying Base Objects

The view joins three source objects:

• AS_LEADS_ALL (aliased OPP) — the primary driving table supplying lead, amount, currency, stage, status, and descriptive flexfield columns.
• HZ_PARTIES (aliased PARTY) — the Trading Community Architecture party table, joined on OPP.CUSTOMER_ID = PARTY.PARTY_ID, supplying CUSTOMER_NAME and address attributes.
• AS_SALES_STAGES_ALL_VL (aliased ASS) — the sales stage lookup view, joined on OPP.SALES_STAGE_ID = ASS.SALES_STAGE_ID, supplying the translated SALES_STAGE name.
• AS_STATUSES_VL (aliased AST) — the status lookup view, joined on OPP.STATUS = AST.STATUS_CODE, supplying the translated STATUS meaning.

Note that the ETRM metadata lists no referenced base objects explicitly, but the view text above documents these four. The joins are effectively inner joins, so leads lacking a matching party, stage, or status will be excluded from the result set.

Key Columns

Columns exposed by the view fall into several functional groups. The lead identity columns are LEAD_ID, LEAD_NUMBER, CUSTOMER_ID, CUSTOMER_NAME, and ORG_ID (the operating unit for multi-org security). Stage and status information is available both by ID (SALES_STAGE_ID, STATUS_CODE) and by translated meaning (SALES_STAGE, STATUS).

The column most relevant to lead-source reporting is LEAD_SOURCE_CODE, inherited directly from AS_LEADS_ALL. This is the stored code identifying the origin of the lead (for example, campaign, referral, or web). It is a code, not a description, so a join to the corresponding lookup is typically required to render it readably. Related columns include CHANNEL_CODE and CAMPAIGN_ID (aliased from SOURCE_PROMOTION_ID). Note that CAMPAIGN_CODE, SALES_REP_FIRST_NAME, SALES_REP_LAST_NAME, CONTACT_FIRST_NAME, and CONTACT_LAST_NAME are defined as NULL placeholders in the view text; they exist for structural compatibility but carry no data.

Financial and pipeline indicators include REVENUE_AMOUNT (aliased from TOTAL_AMOUNT), TOTAL_AMOUNT, WIN_PROBABILITY, CURRENCY_CODE, and DECISION_DATE. Address columns (ADDRESS1–ADDRESS4, CITY, STATE, COUNTRY, POSTAL_CODE) come from the party record. Fifteen ATTRIBUTE columns plus ATTRIBUTE_CATEGORY expose the descriptive flexfield, and DESCRIPTION, CREATION_DATE, and LAST_UPDATE_DATE provide audit context.

Common Use Cases and Queries

Because the view pre-joins party, stage, and status data, it is convenient for lead-source analysis. A typical query aggregates pipeline by source:

• SELECT LEAD_SOURCE_CODE, COUNT(*) LEAD_CNT, SUM(REVENUE_AMOUNT) PIPELINE FROM AST_LS_OPP_BSC_V WHERE ORG_ID = :org_id GROUP BY LEAD_SOURCE_CODE;
• SELECT LEAD_NUMBER, CUSTOMER_NAME, SALES_STAGE, STATUS, LEAD_SOURCE_CODE, WIN_PROBABILITY FROM AST_LS_OPP_BSC_V WHERE LEAD_SOURCE_CODE = :source ORDER BY DECISION_DATE;
• SELECT SALES_STAGE, AVG(WIN_PROBABILITY) FROM AST_LS_OPP_BSC_V GROUP BY SALES_STAGE;

For integrations, the view can feed external CRM or marketing analytics extracts, since it delivers a flat, human-readable record per opportunity. Always filter on ORG_ID to respect multi-org security, and join LEAD_SOURCE_CODE to FND_LOOKUPS (or the appropriate AST lookup) to obtain a descriptive value. Confirm the view's presence in the target instance before referencing it in production code.