Search Results channel_end_date_active

Overview

APPS.BIL_DIMV_SLS_CHANNELS is a read-only dimensional view in Oracle E-Business Suite that consolidates sales channel lookup values into a single queryable source. It is part of the BI (Business Intelligence) dimensional view family used by Oracle's reporting and analytics layers — including ETRM (Enterprise Territory and Resource Management) and related BI Publisher or Discoverer components — to resolve sales channel dimension members. The view presents a denormalized list of sales channel codes and their associated descriptive attributes, enabling downstream reports, dashboards, and integration extracts to join transactional sales data against a stable channel reference without directly coupling to the underlying lookup tables.

The view is defined with a WITH READ ONLY clause, confirming it is intended strictly for query and reporting purposes. No DML is permitted against it, and it carries no user-maintained triggers or constraints of its own; it inherits its shape entirely from the two contributing lookup sources described below.

Underlying Base Objects

Per the documented view text, BIL_DIMV_SLS_CHANNELS is defined over two base lookup tables joined by a UNION ALL:

• OE_LOOKUPS — the Order Entry lookup table, queried for rows where LOOKUP_TYPE = 'SALES_CHANNEL'. This supplies the primary population of sales channel dimension members.
• FND_LOOKUPS — the common application lookup table, queried for rows where LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999'. This branch contributes a single sentinel or default row, commonly representing an "unassigned," "unknown," or placeholder channel used to preserve referential integrity in fact-table joins.

The metadata notes no other documented base objects. The structure follows the standard EBS BI dimensional view pattern, where lookup-driven dimensions are flattened for analytical consumption rather than normalized.

Key Columns

The view exposes the following columns, with the second UNION branch supplying aliases for the same positional slots:

• LOOKUP_CODE (aliased CHANNEL_LOOKUP_CODE / ID in the second branch) — the sales channel identifier used as the dimension key.
• MEANING (aliased CHANNEL_MEANING / VALUE) — the display name of the channel.
• DESCRIPTION (aliased CHANNEL_DESCRIPTION) — optional extended description of the channel.
• ENABLED_FLAG (aliased CHANNEL_ENABLED_FLAG) — indicates whether the lookup is currently active.
• START_DATE_ACTIVE / END_DATE_ACTIVE (aliased with a CHANNEL_ prefix) — effective dating that governs the validity window of each channel member.

Because the second branch reuses LOOKUP_CODE and MEANING as the trailing pair (ID and VALUE), consumers can treat the view as a uniform name/value dimension despite the internal UNION.

Common Use Cases and Queries

Typical usage involves joining the view to sales fact tables to label channel codes, filtering by active status and effective dates, or extracting the dimension into an external warehouse.

List active sales channels currently in effect:

• SELECT LOOKUP_CODE, MEANING FROM APPS.BIL_DIMV_SLS_CHANNELS WHERE ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE);

Retrieve the default/sentinel channel populated from FND_LOOKUPS:

• SELECT ID, VALUE FROM APPS.BIL_DIMV_SLS_CHANNELS WHERE ID = '-999';

Join to sales order lines to resolve channel descriptions:

• SELECT h.order_number, c.MEANING channel FROM oe_order_headers_all h, APPS.BIL_DIMV_SLS_CHANNELS c WHERE h.sales_channel_code = c.LOOKUP_CODE;

Because the view is read-only and effective-dated, reports should always apply appropriate date predicates to avoid returning superseded channel members.