Search Results lead_opportunity_id

Overview

The AS_SALES_LEAD_OPPORTUNITY table is a core data object within the Oracle E-Business Suite Sales Foundation module (AS). It functions as a critical junction table, establishing and maintaining the formal relationships between sales leads (AS_SALES_LEADS) and sales opportunities (AS_LEADS_ALL). Its primary role is to enable the tracking of a lead's progression through the sales pipeline by linking it to one or more opportunity records that may be generated from it. This linkage is essential for sales analytics, forecasting, and understanding the conversion path from initial inquiry to qualified deal. As noted in its documentation, records in this table are designed to be persistent and cannot be deleted, preserving the historical audit trail of these relationships.

Key Information Stored

The table's structure is focused on the relationship itself rather than storing extensive transactional data. Based on the provided metadata, the central column is the LEAD_OPPORTUNITY_ID, which serves as the unique primary key for each relationship record. The two fundamental foreign key columns define the link: SALES_LEAD_ID references the originating lead in the AS_SALES_LEADS table, and OPPORTUNITY_ID references the associated opportunity in the AS_LEADS_ALL table. This simple yet powerful structure allows for many-to-many relationships, meaning a single sales lead can spawn multiple opportunities, and conversely, an opportunity might be influenced by multiple leads, though the latter is less common.

Common Use Cases and Queries

This table is primarily accessed for reporting and data integrity checks. A common business use case is generating a report on lead conversion rates, showing which leads have successfully progressed to the opportunity stage. Development and integration use cases often involve querying this table to validate or retrieve linked records during data migrations or when building custom interfaces. A typical SQL query would join these three core tables to create a comprehensive view:

• Sample Query: SELECT sl.lead_number, sl.status, opp.name opportunity_name, opp.sales_stage FROM as_sales_leads sl, as_sales_lead_opportunity link, as_leads_all opp WHERE sl.sales_lead_id = link.sales_lead_id AND link.opportunity_id = opp.lead_id;
• Reporting Use Case: Analyzing the time lag between lead creation and opportunity creation by joining on date columns from the related lead and opportunity tables.
• Data Validation: Identifying orphaned records where a SALES_LEAD_ID or OPPORTUNITY_ID no longer exists in its parent table.

Related Objects

The AS_SALES_LEAD_OPPORTUNITY table sits at the intersection of two major sales entities. Its existence is defined by its foreign key relationships:

• AS_SALES_LEADS: The source table for the SALES_LEAD_ID foreign key. This table holds the initial prospect or inquiry information.
• AS_LEADS_ALL: The source table for the OPPORTUNITY_ID foreign key. This is the primary opportunities table, often accessed via the AS_OPPORTUNITIES view, containing qualified sales deals with revenue estimates and stages.

Given its function, this table is also likely referenced by various standard Oracle Sales APIs (e.g., AS_LEAD_PUB, AS_OPPORTUNITY_PUB) and underlying PL/SQL packages that manage lead and opportunity lifecycle processes, ensuring the relationship is maintained during creation and updates.