Results for “sales_stage”

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

Overview

AMS_LEAD_ACCOUNTS_V is an Oracle E-Business Suite (EBS) database view owned by the APPS schema and delivered as part of the AMS – Marketing product family. In both release 12.1.1 and 12.2.2 the object is documented as VALID and is designed to present a denormalized, reporting-ready projection of marketing opportunities (internally stored as "leads" in the AS_LEADS_ALL table) together with the customer and site information to which each opportunity is linked.

According to the ETRM documentation, the view exists to serve the Discoverer Business Area "List Generation Folders," whose purpose is to retrieve customers and sites linked to an opportunity. Because it flattens opportunity attributes, sales stage, win probability, and interest classification into a single row-per-opportunity result set, it is well suited for ad-hoc querying, Discoverer workbooks, and lightweight integration extracts. It is important to note that this is not a base transactional API; it is a read-only reporting construct that should not be used for inserts or updates.

Underlying Base Objects

The documented view text shows that AMS_LEAD_ACCOUNTS_V is defined over the following base objects:

• AS_LEADS_ALL (synonym) — the primary driving table, aliased AL, holding the opportunity/lead header record.
• AS_LEAD_LINES_ALL (synonym) — aliased ALA, holding opportunity line detail including interest type and interest codes.
• AS_INTERESTS_ALL (synonym) — aliased AI, joined on LEAD_ID with an outer join.
• AS_INTEREST_TYPES_VL (view) — aliased AIT, supplying the interest type description/name.
• AS_INTEREST_CODES_VL (view) — aliased AICP and AICS, supplying primary and secondary interest code values.
• AS_SALES_STAGES_ALL_VL (view) — aliased SSA, supplying the sales stage name through an outer join on SALES_STAGE_ID.

All joins to AS_INTERESTS_ALL, AS_LEAD_LINES_ALL, and AS_SALES_STAGES_ALL_VL are implemented as outer joins (denoted by the (+) syntax), while the lead is filtered with AL.LEAD_ID > 0. This ensures that a row is returned even when an opportunity has no associated line, interest, or stage, with NULL values appearing in the corresponding columns.

Key Columns

The view exposes twenty-four columns. The most significant for reporting include:

• LEAD_ID — primary identifier for the opportunity.
• SALES_STAGE — the user-facing sales stage name derived from AS_SALES_STAGES_ALL_VL. This is the column most relevant to a "sales_stage" search.
• WIN_PROBABILITY — stored as the numeric probability concatenated with a percent sign.
• OPP_STATUS — the opportunity status, aliased from AL.STATUS.
• TOTAL_AMOUNT, CURRENCY_CODE, CHANNEL_CODE, CLOSE_REASON — commercial attributes of the opportunity.
• INITIATIVES, FORECAST_SPONSOR, SEGMENTED, SOLUTION_TYPE, PROJECT_NAME_NUMBER, ACTION_TO_CLOSE, LEAD_SOURCE, ORDER_NUMBER, INTERNAL_PROCESS_STATUS, NEW_ACCOUNT — descriptive flexfield (ATTRIBUTE1–ATTRIBUTE12) values renamed to meaningful business labels.
• OPP_INTEREST_TYPE, OPP_PRIMARY_INTEREST, OPP_SECONDARY_INTEREST — concatenated interest classifications, e.g. INTEREST_TYPE/CODE or INTEREST_TYPE/CODE/CODE.
• ADDRESS_ID, CUSTOMER_ID — the linked site and customer, enabling the "List Generation" linkage to customers and sites.

Common Use Cases and Queries

A frequent scenario is filtering opportunities by sales stage, which the documented SALES_STAGE column directly supports:

SELECT LEAD_ID, SALES_STAGE, OPP_STATUS, WIN_PROBABILITY, TOTAL_AMOUNT, CURRENCY_CODE, CUSTOMER_ID, ADDRESS_ID FROM APPS.AMS_LEAD_ACCOUNTS_V WHERE SALES_STAGE = 'Qualified';

Another common pattern is producing a pipeline listing with customer links:

SELECT CUSTOMER_ID, ADDRESS_ID, OPP_STATUS, SALES_STAGE, TOTAL_AMOUNT FROM APPS.AMS_LEAD_ACCOUNTS_V WHERE OPP_STATUS = 'OPEN' ORDER BY TOTAL_AMOUNT DESC;

Because the view returns one row per qualifying opportunity and applies outer joins, consumers should be aware that NULL sales stage or interest values are possible. Queries should therefore avoid implied inner-join assumptions on those columns, and appropriate AMS responsibilities or read privileges on the APPS synonyms are required.