Search Results bil_dimv_sls_channels




Overview
Explore in AI Chat

Overview

BIL_DIMV_SLS_CHANNELS is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BIL (Sales Intelligence) product family. It exposes the set of sales channel values used by Oracle Sales Intelligence analytics and reporting, allowing dimension members to be joined to fact data for channel-level segmentation of sales performance. The object is registered in the E-Business Suite Technical Reference Manual (ETRM) with a status of VALID and is present in both the 12.1.1 and 12.2.2 releases.

Because it is a view rather than a table, it carries no storage of its own and reflects the current state of the underlying lookup definitions at query time. It is intended purely as a reporting and integration surface: BI Publisher reports, discoverer workbooks, OBIEE repository sources, and custom PL/SQL can select from it without directly touching the two lookup tables that supply its rows.

Underlying Base Objects

The view is defined over two dictionary tables, combined with a UNION ALL:

  • OE_LOOKUPS — the Order Management lookup table, filtered on LOOKUP_TYPE = 'SALES_CHANNEL'. This supplies the operational sales channel codes maintained through the Order Management setup windows.
  • FND_LOOKUPS — the Oracle Application Object Library lookup table, filtered on LOOKUP_TYPE = 'BIL_VALUE_TYPE' AND LOOKUP_CODE = '-999'. This contributes a single sentinel row representing an "unknown" or "not specified" channel, which is aliased so that its columns align with the OE_LOOKUPS branch.

No other base objects are documented in the ETRM metadata. The two lookups are the authoritative maintenance points; changes made to lookup codes, meanings, enabled flags, or active date ranges propagate to the view immediately, with no concurrent program or materialization step required.

Key Columns

The view presents the following columns, with the second branch of the UNION supplying aliases that map cleanly onto the first:

  • CHANNEL_LOOKUP_CODE — the sales channel identifier, sourced from OE_LOOKUPS.LOOKUP_CODE and from FND_LOOKUPS.LOOKUP_CODE in the sentinel branch.
  • CHANNEL_MEANING — the user-facing display name of the channel, from the MEANING column of either lookup table.
  • CHANNEL_DESCRIPTION — the longer descriptive text associated with the channel.
  • CHANNEL_ENABLED_FLAG — indicates whether the channel is currently active (typically 'Y' or 'N'); disabled channels remain in the view but should normally be excluded from current-period reporting.
  • CHANNEL_START_DATE_ACTIVE and CHANNEL_END_DATE_ACTIVE — the effective date window for the channel. A null end date denotes an open-ended channel.
  • ID and VALUE — positional columns produced by the UNION ALL, populated only from the sentinel FND_LOOKUPS row, which carries LOOKUP_CODE = '-999' in both. They exist to satisfy column-count alignment rather than to carry independent business meaning.

Common Use Cases and Queries

Typical usage is as a dimension source in Sales Intelligence extracts and in ad hoc reporting where channel descriptions are needed alongside aggregate sales measures. Filtering on CHANNEL_ENABLED_FLAG and the active date range keeps reports limited to currently valid members.

List all active sales channels:

  • SELECT channel_lookup_code, channel_meaning FROM bil_dimv_sls_channels WHERE channel_enabled_flag = 'Y' ORDER BY channel_meaning;

Restrict to channels valid on a given date:

  • SELECT channel_lookup_code, channel_meaning FROM bil_dimv_sls_channels WHERE channel_enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN NVL(channel_start_date_active, TRUNC(SYSDATE)) AND NVL(channel_end_date_active, TRUNC(SYSDATE));

Isolate the sentinel "unknown channel" member used to capture fact rows with no assigned channel:

  • SELECT id, value FROM bil_dimv_sls_channels WHERE id = '-999';

Join to fact data to produce channel-level totals:

  • SELECT c.channel_meaning, SUM(f.amount) FROM bil_dimv_sls_channels c, <sales_fact> f WHERE f.channel_code = c.channel_lookup_code GROUP BY c.channel_meaning;