Search Results as_opportunity_lines

Overview

AS_OPPORTUNITY_LINES is a multi-organization (multi-org) reporting view in the Oracle E-Business Suite Sales Foundation (AS) module. It exposes sales opportunity line data, allowing reporting tools, concurrent programs, and custom integrations to query opportunity line records in a secured, organization-filtered manner. The view is documented in Oracle's ETRM reference for both EBS 12.1.1 and 12.2.2, where it is classified under the AS - Sales Foundation product family and described simply as "Sales opportunity lines (multi-org)." This view is a filtered presentation layer rather than a stored object; it does not carry its own data and is not materialized. Its defining characteristic is the enforcement of the multi-org security model through the ORG_ID column, which restricts result sets to the organization context established by the current session. This makes the view particularly relevant for multi-org implementations where opportunity lines must be isolated by operating unit or inventory organization.

Underlying Base Objects

The view is defined over a single base table, AS_LEAD_LINES_ALL, from which it selects an explicit column list. The name of the base table indicates it is the "_ALL" variant, meaning it stores records across all organizations in the multi-org model. The view's WHERE clause applies the standard EBS multi-org security predicate. It derives the current organization from USERENV('CLIENT_INFO'), extracting the first ten bytes of that value and converting it to a number, with an NVL fallback that substitutes -99 when the client information is absent or blank. The same expression appears on both sides of the equality, comparing the base table's ORG_ID against the session's organization context. Because the "_ALL" table is multi-org enabled, this filter ensures that a query against AS_OPPORTUNITY_LINES returns only rows belonging to the organization the user is currently operating within. No additional base objects, joins, or subqueries are documented for this view.

Key Columns

The view exposes a comprehensive set of opportunity line attributes. The primary key is LEAD_LINE_ID, which uniquely identifies each opportunity line and links it to its parent opportunity through LEAD_ID. Descriptive columns include INTEREST_TYPE_ID, PRIMARY_INTEREST_CODE_ID, SECONDARY_INTEREST_CODE_ID, and INTEREST_STATUS_CODE, which characterize the nature and status of the customer interest. Product-related columns include INVENTORY_ITEM_ID, ORGANIZATION_ID, UOM_CODE, QUANTITY, PRICE, TOTAL_AMOUNT, and PRICE_VOLUME_MARGIN. Pipeline and forecasting attributes are represented by SALES_STAGE_ID, WIN_PROBABILITY, STATUS_CODE, DECISION_DATE, CHANNEL_CODE, and QUOTED_LINE_FLAG. The ORG_ID column drives multi-org filtering. Standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and the concurrent program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) are also present, along with a fifteen-column descriptive flexfield block ranging from ATTRIBUTE_CATEGORY to ATTRIBUTE15.

Common Use Cases and Queries

Typical uses include opportunity pipeline reporting, forecasting by sales stage or win probability, and integration extracts that feed external CRM or analytics platforms. A basic query retrieves all lines for a given opportunity:

• SELECT LEAD_LINE_ID, LEAD_ID, INVENTORY_ITEM_ID, QUANTITY, TOTAL_AMOUNT, SALES_STAGE_ID, WIN_PROBABILITY FROM AS_OPPORTUNITY_LINES WHERE LEAD_ID = :p_lead_id;
• SELECT SALES_STAGE_ID, SUM(TOTAL_AMOUNT), AVG(WIN_PROBABILITY) FROM AS_OPPORTUNITY_LINES GROUP BY SALES_STAGE_ID;
• SELECT LEAD_LINE_ID, ORGANIZATION_ID, QUOTED_LINE_FLAG, DECISION_DATE FROM AS_OPPORTUNITY_LINES WHERE STATUS_CODE = :l_status AND ORG_ID = :l_org_id;

Because the view enforces multi-org security automatically, callers need not add ORG_ID predicates, though including them is harmless. Note that the view is not implemented in every database; availability depends on whether the Sales Foundation opportunity application is installed.