Search Results bim_value_type




Overview
Explore in AI Chat

Overview

APPS.BIM_DIMV_MEDIA_CHANNELS is a read-only dimensional view used by Oracle E-Business Suite's Business Intelligence and Marketing (BIM) components to present a unified, denormalized perspective of media, channel, event header, and event offer data. It is designed as a dimension source for marketing analytics, providing a consolidated list of "channels" that spans traditional media channels, event headers, and event offers, together with a special sentinel row for the BIM_VALUE_TYPE lookup value of -999. Because the view is declared WITH READ ONLY, it is intended exclusively for reporting and integration consumption rather than transactional data manipulation.

In Oracle EBS 12.1.1 and 12.2.2 the view resides in the APPS schema and exposes a stable column set that includes MEDIA_ID, MEDIA_NAME, MEDIA_TYPE_CODE, MEDIA_TYPE_NAME, CHANNEL_ID, CHANNEL_TYPE_CODE, CHANNEL_TYPE, and CHANNEL_NAME. This makes it suitable for dimension tables in OBIEE, BI Publisher, and custom ETL extracts where a single flattened channel/media dimension is required.

Underlying Base Objects

The view is defined over a UNION ALL of five SELECT branches. The documented base objects are: AMS_CHANNELS_VL (VIEW), AMS_EVENT_HEADERS_VL (VIEW), AMS_EVENT_OFFERS_VL (VIEW), AMS_LOOKUPS (VIEW), AMS_MEDIA_CHANNELS (SYNONYM), AMS_MEDIA_VL (VIEW), FND_GLOBAL (PACKAGE), and FND_LOOKUPS (VIEW).

  • AMS_MEDIA_CHANNELS and AMS_MEDIA_VL provide the media-to-channel association and media descriptions for the primary branch.
  • AMS_CHANNELS_VL supplies CHANNEL_TYPE_CODE, CHANNEL_TYPE, and CHANNEL_NAME.
  • AMS_EVENT_HEADERS_VL contributes event headers as channel rows, joining to AMS_LOOKUPS on EVENT_TYPE_CODE with LOOKUP_TYPE = 'AMS_EVENT_TYPE'.
  • AMS_EVENT_OFFERS_VL contributes event offers in the same manner.
  • FND_LOOKUPS supplies the BIM_VALUE_TYPE lookup meaning for the -999 sentinel rows.
  • FND_GLOBAL is referenced for session context in the view text, though the exposed columns do not depend on it directly.

Key Columns

  • MEDIA_ID — Numeric media identifier; set to -999 on event and sentinel rows.
  • MEDIA_NAME — Media description from AMS_MEDIA_VL, or the lookup meaning on sentinel rows.
  • MEDIA_TYPE_CODE / MEDIA_TYPE_NAME — Media classification code and its display name; both '-999' on event and sentinel rows.
  • CHANNEL_ID — Synthetic key: 'CHLS' prefixed channel ID, 'EVEH' prefixed event header ID, 'EVEO' prefixed event offer ID, or '-999'.
  • CHANNEL_TYPE_CODE — For media channels the AMS channel type code; for events the event type code; '-999' on sentinel rows. This is the column most relevant to channel_type_code lookups.
  • CHANNEL_TYPE — Descriptive meaning joined from AMS_LOOKUPS.
  • CHANNEL_NAME — Display name of the channel, event header, or event offer.

Common Use Cases and Queries

Typical consumers use this view to enumerate every marketing channel available for attribution or targeting, to resolve CHANNEL_TYPE_CODE to a meaningful label, or to drive prompts in BI Publisher reports.

  • Listing all channels with their type: SELECT CHANNEL_ID, CHANNEL_NAME, CHANNEL_TYPE_CODE, CHANNEL_TYPE FROM APPS.BIM_DIMV_MEDIA_CHANNELS ORDER BY CHANNEL_TYPE_CODE;
  • Filtering by channel type: SELECT * FROM APPS.BIM_DIMV_MEDIA_CHANNELS WHERE CHANNEL_TYPE_CODE = :p_type;
  • Extracting the media-to-channel hierarchy for ETL.
  • Excluding sentinel rows: WHERE MEDIA_ID <> -999.