Results for “as_leads_all”

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

Overview

AS_LEADS_ALL is the core transactional table within the Oracle E-Business Suite Sales Foundation (AS) module, holding sales opportunities and lead records for the CRM and TeleSales/Field Sales flows. In Release 12.1.1 and 12.2.2 the table is owned by the OSM schema and is validated as a standard product table. The table stores one row per sales lead or opportunity, capturing the pipeline entity that later drives forecasting, quoting, territory assignment, and sales-credit computation.

From a dimensional modeling standpoint, the heuristic Data Vault classification of AS_LEADS_ALL is a hub-leaning structure. The table functions as a business-key-bearing entity with a single-column surrogate identifier (LEAD_ID) and a dense set of outbound foreign keys to reference or dimension tables such as HZ_PARTIES, AS_SALES_STAGES_ALL_B, and FND_CURRENCIES. This pattern suggests modeling AS_LEADS_ALL as the hub table for the "Opportunity/Lead" business concept, with its descriptive and status-oriented columns handled as satellites in a Data Vault design.

Key Information Stored

The surrogate primary key is LEAD_ID, backed by the AS_LEADS_ALL_PK constraint and the unique index AS_LEADS_U1. LEAD_ID is the column referenced by nearly every dependent child table through foreign keys.

• LEAD_NUMBER — user-visible opportunity/lead identifier used in forms and reports.
• STATUS — foreign key to AS_STATUSES_B, indicating the lifecycle state of the opportunity.
• CUSTOMER_ID — foreign key to HZ_PARTIES (with the legacy RA_CUSTOMERS overlap retained for older data).
• ADDRESS_ID — foreign key to HZ_PARTY_SITES (and RA_ADDRESSES_ALL for legacy rows).
• SALES_STAGE_ID — foreign key to AS_SALES_STAGES_ALL_B, tracking pipeline progression.
• WIN_PROBABILITY — foreign key to AS_FORECAST_PROB_ALL_B, used in forecast weighting.
• TOTAL_AMOUNT and CURRENCY_CODE — revenue value and its currency (FND_CURRENCIES).
• DECISION_DATE — expected close/decision date for the opportunity.
• OWNER_SALESFORCE_ID and OWNER_SALES_GROUP_ID — foreign keys to JTF_RS_RESOURCE_EXTNS and JTF_RS_GROUPS_B, defining the sales owner and group.
• SALES_METHODOLOGY_ID — foreign key to AS_SALES_METHODOLOGY_B, defining the sales methodology applied.
• SOURCE_PROMOTION_ID — foreign key to AMS_SOURCE_CODES, linking the lead to a marketing campaign source.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS controlling multi-org access.
• ORG_ID and DELETED_FLAG — multi-org partitioning and soft-delete indicator.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID — concurrent program audit context for which batch created or updated the row.

Common Use Cases and Queries

AS_LEADS_ALL is primarily queried for pipeline reporting, forecast roll-ups, and opportunity aging analysis. A typical extraction filters on ORG_ID and an open STATUS range, joining to HZ_PARTIES to resolve customer names:

• Open pipeline by owner: SELECT LEAD_ID, LEAD_NUMBER, TOTAL_AMOUNT, CURRENCY_CODE FROM AS_LEADS_ALL WHERE STATUS NOT IN ('WON','LOST') AND DELETED_FLAG = 'N'.
• Stage-weighted forecast: join WIN_PROBABILITY to AS_FORECAST_PROB_ALL_B to produce the weighted forecast amount per opportunity.
• Territory and quota analysis: aggregate TOTAL_AMOUNT by OWNER_SALES_GROUP_ID joined to JTF_RS_GROUPS_B.
• Campaign attribution: join SOURCE_PROMOTION_ID to AMS_SOURCE_CODES to trace leads generated by marketing activities.
• Performance and summary views: reporting layers frequently rely on supplied materialized views such as ASF_LEADS_CHN_SUM_MV, ASF_OPP_TOP_MV, and ASF_WIN_WON_TOP5_MV, all keyed on LEAD_ID.

Related Objects

AS_LEADS_ALL sits at the center of the AS opportunity model, and references both master reference tables and numerous dependent entities:

• AS_LEAD_LINES_ALL.LEAD_ID — the product line detail belonging to each opportunity.
• AS_LEAD_CONTACTS_ALL.LEAD_ID — contacts associated with the opportunity.
• AS_SALES_CREDITS.LEAD_ID and AS_SALES_CREDITS_DENORM.LEAD_ID — sales-credit allocation records.
• AS_LEAD_COMPETITORS.LEAD_ID and AS_LEAD_COMP_PRODUCTS.LEAD_ID — competitive intelligence on the deal.
• AS_SALES_LEAD_OPPORTUNITY.OPPORTUNITY_ID — association between leads and formal opportunity records.
• ASO_QUOTE_RELATED_OBJECTS.OBJECT_ID — quoting linkage via Object-ID relationship.
• AS_ACCESSES_ALL_ALL.LEAD_ID and AS_PARTNER_ACCESSES.LEAD_ID — access and sharing rules for the opportunity.
• AS_QUOTES_ALL.LEAD_ID — quotes originating from the opportunity.
• ASF_* materialized views and staging tables such as BIL_BI_PIPELINE_STG.LEAD_ID — used by Business Intelligence and data-warehouse extraction.
• AS_SALES_STAGES_ALL_B, AS_STATUSES_B, AS_FORECAST_PROB_ALL_B, and HZ_PARTIES — the primary lookup and dimension tables referenced by the table's foreign keys.