Search Results bim_dimv_media




Overview
Explore in AI Chat

Overview

BIM_DIMV_MEDIA is a read-only dimension view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BIM (Marketing Intelligence) product family. In Release 12.1.1 and 12.2.2 this module is flagged as obsolete, meaning it is retained for backward compatibility with existing Marketing Intelligence schemas, historical ETL mappings, and custom reports, but it is no longer the subject of new development. The view presents all marketing media defined in the AMS (Advanced Marketing) media repository, together with the descriptive meaning and description of each media type as resolved from the AMS_MEDIA_TYPE lookup.

The object is a reporting and integration artifact rather than an operational entity. It conforms to the dimensional naming conventions used by BIM views (the DIMV prefix denotes a dimension view) and is intended to be consumed by marketing analytics extracts and star-schema loads. Because the view is declared WITH READ ONLY, it cannot be used as a DML target, and it exposes no row-level security predicates of its own; visibility is governed entirely by the privileges granted on the underlying AMS objects.

Underlying Base Objects

ETRM documents the view as defined over three referenced objects:

The documented metadata also lists FND_GLOBAL (PACKAGE) as a referenced object. Although it does not appear literally in the captured view text, it is typically introduced by the VL synonym resolution to apply the current language setting when returning translated media names; because AMS_MEDIA_VL is language-aware, the view returns the media name appropriate to the session's NLS environment.

The critical structural feature is the UNION ALL with a hard-coded surrogate row (MEDIA_ID = -999, MEDIA_TYPE_CODE = '-999', MEDIA_NAME and MEDIA_TYPE_NAME taken from the lookup meaning). This manufactures a default "unknown / not applicable" member so that fact records with no matching media can still be assigned to a valid dimension key.

Key Columns

  • MEDIA_ID — the media identifier, exposed both as a character value (TO_CHAR of the numeric ID) and as a numeric column in the same select list, allowing joins to either character or numeric keys.
  • MEDIA_NAME — the media name, truncated to 80 characters via SUBSTR for the character-form column; the untruncated value is also exposed.
  • MEDIA_TYPE_CODE — the lookup code referencing AMS_MEDIA_TYPE.
  • MEDIA_TYPE_NAME — the translated meaning of the media type lookup.
  • MEDIA_TYPE_DESCRIPTION — the translated description of the media type lookup.
  • MEDIA_DESCRIPTION — the free-text description of the individual media record. This is NULL for the synthetic -999 row.
  • INBOUND_FLAG and ENABLED_FLAG — indicators of whether the media supports inbound activity and whether it is currently active. Both are NULL for the synthetic row.

The MEDIA_TYPE_CODE / MEDIA_TYPE_NAME pair is what an investigator searching for "ams_media_type" is looking for: the view is the reporting-side join that turns the raw lookup code into a business-readable media type classification.

Common Use Cases and Queries

The primary uses are media dimension population and validation reporting. A typical extraction enumerates enabled media with their type classification:

SELECT media_id, media_name, media_type_code, media_type_name, enabled_flag FROM apps.bim_dimv_media WHERE media_id > 0 AND enabled_flag = 'Y' ORDER BY media_name;

To validate that every media record resolves to a valid AMS_MEDIA_TYPE lookup entry:

SELECT m.media_id, m.media_name, m.media_type_code FROM apps.bim_dimv_media m WHERE m.media_type_name IS NULL AND m.media_id > 0;

To list the distinct media types available in the instance, the lookup view AMS_LOOKUPS is queried directly on LOOKUP_TYPE = 'AMS_MEDIA_TYPE'; the BIM_DIMV_MEDIA view is used when the type must be reported alongside each media record. When reconciling fact tables, the synthetic -999 member should be excluded or explicitly mapped, and because the view is READ ONLY, any maintenance must be performed against the AMS base tables rather than through the view.