Search Results edw_bim_ih_media_m_u1

Overview

The BIM.EDW_BIM_IH_MEDIA_M table is a master data table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, specifically designed for the Interaction History (IH) and Business Intelligence (BI) modules. It functions as a reference or lookup table that stores standardized definitions for interaction media types. Its primary role is to provide a normalized, unique list of communication channels or media (e.g., Email, Phone, Web) used in customer or business interactions, enabling consistent categorization and reporting across the enterprise data warehouse (EDW) layer. The table's storage in the APPS_TS_ARCHIVE tablespace and the accompanying warning indicate it is part of Oracle's pre-built analytics infrastructure, intended for access through standard Oracle Applications programs rather than direct, unsupported modification.

Key Information Stored

The table's structure is centered around unique identifiers and descriptive attributes for interaction media. The most critical columns include the primary key MDIA_INTRCTN_MEDIA_PK_KEY, a system-generated numeric unique identifier. For integration and user-facing purposes, the table also provides MDIA_INTRCTN_MEDIA_CODE (a unique business code) and MDIA_INTRCTN_MEDIA_NAME (the descriptive name). The MDIA_MEDIA_DIRECTION column categorizes the media as INBOUND or OUTBOUND. Additional columns support system integration (ALL_ALL_PK_KEY, MDIA_INSTANCE), Discoverer reporting (MDIA_INTRCTN_MEDIA_DP), and extended descriptions (MDIA_DESCRIPTION). Five user-defined attribute columns (MDIA_USER_ATTRIBUTE1 through 5) offer customization, while MDIA_LAST_UPDATE_DATE is a standard auditing column.

Common Use Cases and Queries

This table is primarily used for dimension-based reporting and data validation in interaction analytics. Common scenarios include generating a list of all available media types for dashboard filters, enriching transactional interaction data with media descriptions for reports, and ensuring referential integrity for media codes. A typical query involves joining this master table to transactional fact tables to produce readable reports.

• Listing all active media types: SELECT MDIA_INTRCTN_MEDIA_CODE, MDIA_INTRCTN_MEDIA_NAME, MDIA_MEDIA_DIRECTION FROM BIM.EDW_BIM_IH_MEDIA_M ORDER BY 1;
• Enriching interaction facts with media details: SELECT t.INTERACTION_ID, m.MDIA_INTRCTN_MEDIA_NAME, m.MDIA_MEDIA_DIRECTION FROM BIM_EDW_INTRCTNS_F t JOIN BIM.EDW_BIM_IH_MEDIA_M m ON t.INTRCTN_MEDIA_FK_KEY = m.MDIA_INTRCTN_MEDIA_PK_KEY;
• Validating a specific media code exists: SELECT COUNT(*) FROM BIM.EDW_BIM_IH_MEDIA_M WHERE MDIA_INTRCTN_MEDIA_CODE = 'EMAIL';

Related Objects

The table's primary relationship is defined by its foreign key, which links it to the core interaction facts table. This establishes it as a critical dimension in the data model.

• Primary Key: EDW_BIM_IH_MEDIA_M_PK on column MDIA_INTRCTN_MEDIA_PK_KEY.
• Foreign Key (Referencing Table): The table BIM_EDW_INTRCTNS_F references this master table via its column INTRCTN_MEDIA_FK_KEY, which joins to MDIA_INTRCTN_MEDIA_PK_KEY. This relationship allows transactional interaction records to be categorized by a valid media type.

Two unique indexes support data integrity: EDW_BIM_IH_MEDIA_M_U1 on (MDIA_INTRCTN_MEDIA_PK, MDIA_INTRCTN_MEDIA_PK_KEY) and EDW_BIM_IH_MEDIA_M_U2 on (MDIA_INTRCTN_MEDIA_PK_KEY).