Results for “bim_dimv_media_channels”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BIM_DIMV_MEDIA_CHANNELS is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite, catalogued under the BIM – Marketing Intelligence product family (now marked obsolete in 12.1.1 and 12.2.2). The view materializes a conformed dimension that resolves the many-to-many relationship between marketing media and the channels through which those media are executed or attributed. Its principal role is to feed the Marketing Intelligence (BIM) analytical star schema, where downstream fact tables require a unified, surrogate-keyed channel/media dimension for slicing campaign performance, response attribution, and spend analysis.

Because BIM_DIMV_MEDIA_CHANNELS is a view rather than a table, it carries no materialized storage of its own; it is evaluated at query time and reflects the current state of the AMS foundation tables. Applications and custom reports often reference this view by name when the underlying transactional tables (AMS_MEDIA_CHANNELS, AMS_MEDIA_VL, AMS_CHANNELS_VL) would otherwise require multi-table joins. The name surfaced in user searches for "ams_media_vl" is consistent with the view's dependency on that base view for the media name and media type attributes.

Underlying Base Objects

The documented dependencies for this view are:

• AMS_CHANNELS_VL (VIEW) – supplies channel name, channel type, and channel type code.
• AMS_EVENT_HEADERS_VL (VIEW) – supplies main-level event headers as channels of type 'EVEH'.
• AMS_EVENT_OFFERS_VL (VIEW) – supplies main-level event offers as channels of type 'EVEO'.
• AMS_LOOKUPS (VIEW) – resolves AMS_EVENT_TYPE lookup meanings for event channel types.
• AMS_MEDIA_CHANNELS (SYNONYM) – the intersection entity binding media to channels.
• AMS_MEDIA_VL (VIEW) – supplies media name, media type code, and media type name.
• FND_LOOKUPS (VIEW) – drives the '-999' unknown placeholders via the BIM_VALUE_TYPE lookup type.

The view is constructed from four UNION ALL branches. The first branch joins AMS_MEDIA_CHANNELS to AMS_MEDIA_VL and AMS_CHANNELS_VL to produce the true media-to-channel relationships, prefixing channel identifiers with 'CHLS'. The second branch emits media rows with a synthetic '-999' channel, sourced from AMS_MEDIA_VL and FND_LOOKUPS. The third and fourth branches emit synthetic media rows with '-999' media and prefixed channel identifiers ('EVEH' for event headers, 'EVEO' for event offers), joining the event VL views to AMS_LOOKUPS and FND_LOOKUPS. FND_GLOBAL is referenced for session context in the underlying views.

Key Columns

• MEDIA_ID – Surrogate identifier for the media; synthetic value -999 denotes an unspecified media row used for event-only channels.
• MEDIA_NAME / MEDIA_TYPE_CODE / MEDIA_TYPE_NAME – Descriptive media attributes resolved from AMS_MEDIA_VL.
• CHANNEL_ID – Text-prefixed channel key. 'CHLS' precedes numeric channel IDs, 'EVEH' precedes event header IDs, 'EVEO' precedes event offer IDs, and '-999' indicates an unspecified channel.
• CHANNEL_TYPE_CODE / CHANNEL_TYPE – Channel classification, derived from AMS_CHANNELS_VL or resolved through AMS_LOOKUPS for event-based rows.
• CHANNEL_NAME – Display name of the channel, event header, or event offer.

Common Use Cases and Queries

Typical reporting scenarios include media/channel mix analysis, event vs. traditional channel comparison, and dimension loading into BIM analytic workspaces. A basic listing follows:

SELECT media_id, media_name, media_type_name,
       channel_id, channel_type, channel_name
  FROM apps.bim_dimv_media_channels
 WHERE media_id <> -999
   AND channel_id <> '-999'
 ORDER BY media_name, channel_name;

To isolate event-based channels, filter on the prefix:

SELECT media_name, channel_id, channel_type, channel_name
  FROM apps.bim_dimv_media_channels
 WHERE channel_id LIKE 'EVE%';

Because the view is obsolete in current EBS releases, integrators are advised to validate the dependency chain against the corresponding AMS base views before extending any BIM report. Where the view is still referenced, wrapping it in a materialized view with scheduled refresh is the customary performance remedy for its UNION ALL structure.