Results for “as_lead_lines_all”

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

Overview

AS_LEAD_LINES_ALL is the transactional detail table that stores the individual product or opportunity lines belonging to a sales lead in Oracle E-Business Suite Releases 12.1.1 and 12.2.2. Within the AS – Sales Foundation product family, the table operates under the OSM schema and holds sales opportunity lines that capture what a prospect is interested in purchasing, in what quantity, at what price, and at what stage of the sales cycle. Each row represents one line of a lead or opportunity, linked back to its parent lead record through LEAD_ID. The table constitutes the primary transactional surface for lead and opportunity line processing in Oracle Sales, Oracle TeleSales, and the integrated order capture and quoting flows.

Based on the foreign key topology documented in the ETRM metadata, AS_LEAD_LINES_ALL can be classified heuristically as satellite-leaning. The table anchors on a surrogate primary key (LEAD_LINE_ID) and carries a mix of descriptive attributes and foreign key references to dimensions such as MTL_SYSTEM_ITEMS_B, AS_INTEREST_TYPES_B, and AS_SALES_STAGES_ALL_B. This pattern suggests it functions primarily as an attribute-bearing satellite around the lead and item hubs, though it also exhibits link-like characteristics through its many outgoing references. Modeling exercises may treat it as a satellite over a lead-line hub, with bridge associations to interest, item, and offer dimensions resolved through separate link constructs.

Key Information Stored

The table contains 55 documented columns. The most significant include:

• LEAD_LINE_ID – Surrogate primary key and sole column of unique index AS_LEAD_LINES_U1, generated internally.
• LEAD_ID – Foreign key to AS_LEADS_ALL identifying the parent lead or opportunity; also appears in unique key AS_LEAD_LINES_UK.
• STATUS_CODE and INTEREST_STATUS_CODE – Track the lifecycle state of the line and the status of the prospect's expressed interest.
• INVENTORY_ITEM_ID and ORGANIZATION_ID – Composite reference to MTL_SYSTEM_ITEMS_B identifying the specific product.
• QUANTITY, UOM_CODE, PRICE, and TOTAL_AMOUNT – Quantity and monetary forecast for the line.
• INTEREST_TYPE_ID, PRIMARY_INTEREST_CODE_ID, SECONDARY_INTEREST_CODE_ID – Classify the type and codes of prospect interest.
• SALES_STAGE_ID and WIN_PROBABILITY – Reference the sales stage and the estimated probability of winning the line.
• DECISION_DATE, FORECAST_DATE, SHIP_DATE, and ROLLING_FORECAST_FLAG – Drive forecast and pipeline reporting.
• SOURCE_PROMOTION_ID and OFFER_ID – Link to marketing source codes and offers for attribution analysis.
• ORG_ID and SECURITY_GROUP_ID – Support multi-org and security group data access.

Operational columns such as LAST_UPDATE_DATE, CREATED_BY, OBJECT_VERSION_NUMBER, and the fifteen ATTRIBUTE columns provide auditing, extensibility, and optimistic locking support.

Common Use Cases and Queries

The dominant use case is pipeline and forecast reporting against open sales opportunities. A typical query joins the line to its parent lead to obtain opportunity context:

• Open pipeline by product: select l.lead_line_id, l.inventory_item_id, l.quantity, l.total_amount, l.win_probability from as_lead_lines_all l where l.status_code = 'OPEN' and l.org_id = :p_org.
• Stage-based forecasting: join l.sales_stage_id to as_sales_stages_all_b to roll up weighted revenue by stage, filtering on forecast_date within the current period.
• Marketing attribution: join source_promotion_id to ams_source_codes and offer_id to ams_act_offers to attribute pipeline value to campaigns.
• Interest analysis: join primary_interest_code_id to as_interest_codes_all to segment prospects by expressed product interest.
• Line detail by lead: select all columns from as_lead_lines_all where lead_id = :p_lead_id, ordered by lead_line_id.

Because the table is multi-org enabled, queries in reports generated via BI Publisher or Oracle Sales Analytics should enforce ORG_ID predicates to respect operating unit security.

Related Objects

AS_LEAD_LINES_ALL participates in a dense network of relationships. The most significant reference targets include:

• AS_LEADS_ALL – Parent lead table, joined via LEAD_ID.
• MTL_SYSTEM_ITEMS_B – Product master, joined via INVENTORY_ITEM_ID and ORGANIZATION_ID.
• AS_INTEREST_TYPES_B and AS_INTEREST_CODES_B/ALL – Interest classification via INTEREST_TYPE_ID and PRIMARY/SECONDARY_INTEREST_CODE_ID.
• AS_SALES_STAGES_ALL_B – Sales stage lookup via SALES_STAGE_ID.
• AMS_SOURCE_CODES and AMS_ACT_OFFERS – Marketing attribution via SOURCE_PROMOTION_ID and OFFER_ID.

Dependent child objects that reference this table include AS_LEAD_COMP_PRODUCTS (competing products), AS_LEAD_DECISION_FACTORS, AS_OPP_COMPETING_PRODS, AS_SALES_CREDITS and AS_SALES_CREDITS_DENORM (sales credit allocation), AS_QUOTE_LINES_ALL and ASO_QUOTE_RELATED_OBJECTS (quote generation), and AS_SALES_LEAD_OPP_LINES (lead-to-opportunity conversion). Audit and interface variants such as AS_LEAD_LINES_LOG, BIL_BI_OPDTL_STG, and the BI materialized views BIM_I_LP_ITEM_MV, PV_SL_BIN_MV, and PV_PRD_BIN_MV consume LEAD_LINE_ID for downstream analytics, reflecting the table's central role in the Sales Foundation transactional model.