Results for “active_from_date”

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

Overview

AMS_MEDIA_CHANNELS is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that defines which channel can handle which kind of media. In functional terms, it is a cross-reference or association table that binds a media type (for example, a print insert, banner, or email creative format) to a delivery channel (for example, email, direct mail, or web). This mapping drives campaign execution logic, allowing the Marketing application to validate that a given media asset is compatible with the channel selected on a campaign or activity. The table resides in the AMS schema and holds twelve documented columns in the 12.2.2 physical schema.

Under a heuristic Data Vault classification derived from the foreign key structure, the table is satellite-leaning. In Data Vault modeling terms, it behaves as a satellite or link-style association capturing the relationship between two hub entities, Media and Channel, together with descriptive attributes such as effective dates and audit columns. This classification is a modeling suggestion rather than a documented Oracle construct, but it accurately reflects the associative nature of the row.

Key Information Stored

• MEDIA_CHANNEL_ID — surrogate primary key, enforced by AMS_MEDIA_CHANNELS_PK and also identified by unique index AMS_MEDIA_CHANNELS_U1. This is the system-generated identifier for each media-to-channel association.
• MEDIA_ID — foreign key to AMS_MEDIA_B, identifying the media type. Combined with CHANNEL_ID, it forms the business-key candidate captured by unique index AMS_MEDIA_CHANNELS_U2.
• CHANNEL_ID — foreign key to AMS_CHANNELS_B, identifying the delivery channel. Together with MEDIA_ID it prevents duplicate media-channel pairings.
• ACTIVE_FROM_DATE and ACTIVE_TO_DATE — effective-dating columns that bound the validity window of the association, supporting historical and time-phased reporting.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org / data-security partitioning of the row.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications framework during concurrent updates.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN record who created and last modified each row and when.

Common Use Cases and Queries

The most frequent reporting need is listing valid media-channel combinations for campaign setup validation. A typical query joins to the base tables to resolve descriptive names:

• SELECT mc.media_channel_id, m.media_name, c.channel_name, mc.active_from_date, mc.active_to_date FROM ams.ams_media_channels mc, ams.ams_media_b m, ams.ams_channels_b c WHERE mc.media_id = m.media_id AND mc.channel_id = c.channel_id AND TRUNC(SYSDATE) BETWEEN mc.active_from_date AND NVL(mc.active_to_date, SYSDATE);
• Duplicate-prevention checks against the AMS_MEDIA_CHANNELS_U2 business key (MEDIA_ID, CHANNEL_ID) before inserting new associations.
• Security-filtered extracts that join SECURITY_GROUP_ID to FND_SECURITY_GROUPS for multi-org deployments.
• Data warehouse loads — the EDW_BIM_MDCH_MDCHN_LSTG and EDW_BIM_MDCH_MDCHN_LTC staging tables reference MEDIA_CHANNEL_ID, indicating this table feeds downstream marketing analytics.

Related Objects

• AMS_MEDIA_B — joined on MEDIA_ID; supplies media definitions for each association.
• AMS_CHANNELS_B — joined on CHANNEL_ID; supplies channel definitions.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for row-level security partitioning.
• AMS_CAMPAIGNS_ALL_B — referenced through CHANNEL_ID, linking campaign records to channel processing logic.
• EDW_BIM_MDCH_MDCHN_LSTG and EDW_BIM_MDCH_MDCHN_LTC — data warehouse staging and load tables that consume MEDIA_CHANNEL_ID for marketing analytics.
• AMS_MEDIA_CHANNELS_PK, AMS_MEDIA_CHANNELS_U1, AMS_MEDIA_CHANNELS_U2 — primary and unique indexes that enforce identity and business-key uniqueness on the table.

Together these relationships position AMS_MEDIA_CHANNELS as a compact but central association table in the Oracle Marketing data model, connecting media assets, delivery channels, campaigns, and downstream analytical stores.