Search Results bil_sumv_sls_chnl_perf

Overview

BIL_SUMV_SLS_CHNL_PERF is a reporting view in the Oracle E-Business Suite Sales Intelligence (BIL) product family. It exposes quantitative measures related to reporting on sales performance for sales channels, aggregating transactional outcomes such as revenue, cost, margin, opportunity counts, units sold, and customer satisfaction percentages into a channel-oriented analytical structure. The view is designed to support business intelligence reporting and dashboarding, primarily through the Sales Intelligence/ETRM analytics stack, where channel effectiveness is evaluated across defined periods, territories, and interest classifications.

The view inherits its name from a summary ("SUMV") construct, indicating it is intended for aggregated rather than transactional-level reporting. It serves as a semantic layer that flattens the underlying summary fact table together with a channel dimension, removing the need for report authors to join these objects manually. As documented in the ETRM 12.2.2 metadata, the view is not implemented in the reference database, meaning the object is provided as a definition for deployment in environments where the Sales Intelligence schemas have been installed and populated.

The user search term of interest, no_of_leads, maps directly to the NO_OF_LEADS column exposed by this view, which records the count of leads attributed to a sales channel within the reporting period.

Underlying Base Objects

The view text defines a join between two objects:

• BIL_SLS_CHNL_PERF_SUMM — the base summary table holding the performance measures. It supplies the majority of columns, including SALES_CHANNEL_ID, PERIOD_NAME, START_DATE, END_DATE, TERRITORY_ID, INTEREST_TYPE_ID, and the quantitative measures such as INVOICED_AMOUNT, MARGIN, UNITS_SOLD, NO_OF_LEADS, and NO_OF_ORDERS.
• BIL_DIMV_SLS_CHANNELS — a channel dimension view that resolves the SALES_CHANNEL_ID lookup code into a descriptive CHANNEL_MEANING value via the CHANNEL_LOOKUP_CODE column.

The join condition is BSCP.SALES_CHANNEL_ID = BDS.CHANNEL_LOOKUP_CODE. Notably, the fetched-and-displayed column aliases CHANNEL_MEANING and SALES_CHANNEL both derive from the same dimension attribute; the view's column list labels it SALES_CHANNEL, while the SQL selects it as CHANNEL_MEANING. The ETRM metadata records no additional referenced base tables beyond these two objects.

Key Columns

• SALES_CHANNEL / CHANNEL_MEANING — the descriptive name of the sales channel, resolved from the channel dimension.
• SALES_CHANNEL_ID — the lookup code identifying the channel.
• PERIOD_NAME, START_DATE, END_DATE — the reporting period and its boundaries for the aggregated measures.
• TERRITORY_ID — the sales territory associated with the performance record.
• INTEREST_TYPE_ID, PRIMARY_INTEREST_CODE_ID, SECONDARY_INTEREST_CODE_ID — classification attributes used to segment performance by interest categorization.
• INVOICED_AMOUNT, COGS_AMOUNT, MARGIN — financial measures covering revenue, cost of goods sold, and resulting margin.
• NO_OF_WON_OPTY, NO_OF_CLOSED_OPTY — counts of won and closed opportunities.
• UNITS_SOLD — quantity of units sold in the period.
• CUST_SATISFACTION_PCT — customer satisfaction percentage.
• NO_OF_LEADS — the number of leads generated or attributed to the channel, a key top-of-funnel metric for channel performance analysis.
• NO_OF_ORDERS — count of orders attributable to the channel.
• Audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) — standard EBS who-columns indicating the concurrent program and user context that populated the summary.

Common Use Cases and Queries

Typical usage involves channel performance dashboards, lead-to-order conversion analysis, margin analysis by channel, and customer satisfaction trending. A representative query returning leads and orders per channel for a period:

SELECT sales_channel,
       period_name,
       no_of_leads,
       no_of_won_opty,
       no_of_orders,
       invoiced_amount,
       margin
  FROM bil_sumv_sls_chnl_perf
 WHERE period_name = :period
   AND territory_id = :territory_id
 ORDER BY no_of_leads DESC;

A conversion-focused query comparing leads to orders:

SELECT sales_channel,
       SUM(no_of_leads)  AS total_leads,
       SUM(no_of_orders) AS total_orders,
       ROUND(SUM(no_of_orders) / NULLIF(SUM(no_of_leads),0) * 100, 2) AS lead_to_order_pct
  FROM bil_sumv_sls_chnl_perf
 WHERE start_date >= :from_date
   AND end_date   <= :to_date
 GROUP BY sales_channel;

Because NO_OF_LEADS is a summary measure, aggregation should be performed with SUM at the reporting grain of channel, period, and territory. Reports querying this view should confirm that BIL_SLS_CHNL_PERF_SUMM has been populated for the target periods, since the view itself performs no aggregation.