Search Results bim_dimv_channels




Overview
Explore in AI Chat

Overview

BIM_DIMV_CHANNELS is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BIM – Marketing Intelligence product family. It consolidates every marketing channel recognized by the application into a single, uniform result set, encompassing three distinct channel classes: media channels, event headers, and event offerings. The view is intended to serve as a conformed dimension source for marketing analytics, campaign attribution, and channel performance reporting, where downstream ETL processes or BI tools require one consistent list of channel identifiers regardless of the originating entity.

Because the view normalizes heterogeneous source records into a shared column signature (ID, VALUE, CHANNEL_ID, CHANNEL_TYPE_CODE, CHANNEL_TYPE, ACTIVE_FROM_DATE, ACTIVE_TO_DATE, CHANNEL_NAME, DESCRIPTION), it acts as a bridge between the AMS marketing tables and the BIM star-schema or dimensional reporting layer. The presence of a synthetic '-999' row sourced from FND_LOOKUPS for lookup type BIM_VALUE_TYPE further indicates it supports the BIM convention of an "unknown" or "unassigned" dimension member, which is essential for outer-join-safe fact loading.

Underlying Base Objects

The view is defined as a four-way UNION ALL over the following documented objects:

  • AMS_CHANNELS_VL (VIEW) — supplies media channels; rows are prefixed 'CHLS' concatenated with CHANNEL_ID. This leg is the only one exposing INBOUND_FLAG and OUTBOUND_FLAG.
  • AMS_EVENT_HEADERS_VL (VIEW) — supplies main-level event headers, prefixed 'EVEH'; filtered by EVENT_LEVEL = 'MAIN'.
  • AMS_EVENT_OFFERS_VL (VIEW) — supplies main-level event offerings, prefixed 'EVEO'; also filtered by EVENT_LEVEL = 'MAIN'. Note its date range uses EVENT_START_DATE and EVENT_END_DATE rather than the header's ACTIVE dates.
  • AMS_LOOKUPS (VIEW) — joined in the event legs to resolve EVENT_TYPE_CODE to a descriptive CHANNEL_TYPE via lookup type AMS_EVENT_TYPE.
  • FND_LOOKUPS (VIEW) — contributes the synthetic '-999' "unknown" row.
  • FND_GLOBAL (PACKAGE) — referenced by the documented dependency list, consistent with standard EBS multi-org and security context resolution.

All three _VL views are MLS-enabled synonyms, meaning the view respects the session's language setting when returning NAME-style columns.

Key Columns

  • ID — Globally unique synthetic key formed from a four-character class prefix plus the source primary key (e.g., CHLS1234, EVEH5678, EVEO9012, or -999).
  • VALUE — Truncated to 80 characters (SUBSTR ... 1,80) display value, suitable for list-of-values and dropdown rendering.
  • CHANNEL_ID — Mirrors ID and carries the prefixed key, ensuring a single consistent join column across all channel classes.
  • CHANNEL_TYPE_CODE / CHANNEL_TYPE — The raw code and its lookup meaning; for channels this derives from CHANNEL_TYPE_CODE, for events/offerings from the AMS_EVENT_TYPE lookup.
  • INBOUND_FLAG / OUTBOUND_FLAG — Populated only for media channels; NULL for events and offerings.
  • ACTIVE_FROM_DATE / ACTIVE_TO_DATE — The validity window. ACTIVE_TO_DATE is the column most relevant to requests such as active_to_date, since it defines when a channel record ceases to be current. For offerings it maps to EVENT_END_DATE.
  • CHANNEL_NAME / DESCRIPTION — The full, untruncated descriptive text.

Common Use Cases and Queries

Typical usages include populating a channel dimension in a marketing data mart, driving channel-selection prompts in concurrent programs, and reconciling event and media spend. A frequent pattern filters on the active_to_date column to isolate currently-open channels:

  • Active channels only: SELECT channel_id, value, channel_type, active_to_date FROM apps.bim_dimv_channels WHERE NVL(active_to_date, SYSDATE+1) >= TRUNC(SYSDATE) AND id <> '-999';
  • Channel class breakdown: SELECT SUBSTR(id,1,4) class_prefix, channel_type, COUNT(*) FROM apps.bim_dimv_channels GROUP BY SUBSTR(id,1,4), channel_type;
  • Inbound media channels: SELECT channel_id, channel_name, active_to_date FROM apps.bim_dimv_channels WHERE inbound_flag = 'Y';
  • Expiry monitoring: SELECT channel_id, channel_name, active_to_date FROM apps.bim_dimv_channels WHERE active_to_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE)+30 ORDER BY active_to_date;

Because the view is WITH READ ONLY, it must never be targeted by DML; all maintenance is performed against the underlying AMS channel, event header, and event offering entities.