Results for “ams_channels_b”

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

Overview

AMS_CHANNELS_B is the base (language-independent) table in the Oracle Marketing (AMS) module of Oracle E-Business Suite, holding the master definition of every media channel available for use in marketing campaigns and activities. It stores the identity, type, directionality, validity window, and ownership attributes of each channel, acting as the authoritative registry that other AMS entities reference when a campaign schedule, activity category, or event registration must specify how it is executed or delivered.

Because the table is anchored on a single surrogate key (CHANNEL_ID) with no composite junction structure of its own, the ETRM relationship metadata classifies it as hub-leaning in a Data Vault sense. In that modeling convention, AMS_CHANNELS_B would best be represented as a hub, with descriptive attributes such as ratings, flags, and vendor associations distributed into one or more satellites. This classification is a heuristic derived from the foreign-key topology rather than a formal declaration, and should be treated as a design suggestion when planning downstream data-warehouse integration.

Key Information Stored

The documented physical schema in 12.2.2 contains 39 columns. The most operationally significant are:

• CHANNEL_ID — the surrogate primary key, enforced by AMS_CHANNELS_B_PK and mirrored by the unique index AMS_CHANNELS_B_U1. It is the single business-key candidate and the column through which all child tables join.
• CHANNEL_TYPE_CODE — classifies the channel (for example, direct mail, email, telemarketing, event), driving downstream presentation and processing logic.
• OUTBOUND_FLAG and INBOUND_FLAG — indicate whether the channel can be used for outbound campaign execution, inbound response capture, or both.
• ACTIVE_FROM_DATE and ACTIVE_TO_DATE — the effective date range during which the channel may be selected for use.
• MANAGED_BY_PERSON_ID — the internal owner or responsible party, referenced to FND_USER.
• PARTY_ID — links the channel to an external organization or vendor identity in HZ_PARTIES.
• PREFERRED_VENDOR_ID — records the preferred vendor associated with the channel.
• RATING and ORDER_SEQUENCE — support ranking and default display ordering of channels in the user interface.
• INTERNAL_RESOURCE — distinguishes channels fulfilled by internal resources from externally sourced ones.
• COUNTRY_ID — restricts or localizes a channel by country.
• SECURITY_GROUP_ID — enforces multi-tenant or organizational data segregation through FND_SECURITY_GROUPS.
• Whodunit and concurrency columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER.
• ATTRIBUTE1–ATTRIBUTE15 plus ATTRIBUTE_CATEGORY — the standard descriptive flexfield segment set.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrency program tracking used by the Marketing concurrent managers.

Translated names and descriptions are not held here; they reside in AMS_CHANNELS_TL, which maps back to this table through CHANNEL_ID.

Common Use Cases and Queries

The primary use case is populating channel-selection lists of values during campaign and activity setup, restricted to currently active channels. A typical reporting query joins the base table to its translation table and filters on the effective dates:

SELECT b.channel_id, t.channel_name, b.channel_type_code, b.outbound_flag, b.inbound_flag
FROM ams_channels_b b, ams_channels_tl t
WHERE b.channel_id = t.channel_id
AND t.language = USERENV('LANG')
AND SYSDATE BETWEEN b.active_from_date AND b.active_to_date;

Other recurring scenarios include: reconciling campaign schedules to the channels they consume via AMS_CAMPAIGN_SCHEDULES_B.MARKETING_MEDIUM_ID; measuring inbound response volume by channel through AMS_EVENT_REGISTRATIONS.INBOUND_CHANNEL_ID; auditing channel ownership by joining MANAGED_BY_PERSON_ID to FND_USER; and building channel-performance extracts that combine the RATING and PREFERRED_VENDOR_ID attributes with external response data. Data-governance queries also use SECURITY_GROUP_ID to verify that channels are correctly partitioned across operating units before promotion between environments.

Related Objects

• AMS_CHANNELS_TL — the translation table storing language-specific channel names and descriptions; joins on CHANNEL_ID. Any user-facing query should include it.
• AMS_ACT_CATEGORIES — references this table through ACT_CATEGORY_USED_BY_ID, associating activity categories with the channels used to fulfill them.
• AMS_CAMPAIGN_SCHEDULES_B — references this table through MARKETING_MEDIUM_ID, tying campaign schedule lines to the media channel employed.
• AMS_EVENT_REGISTRATIONS — references this table through INBOUND_CHANNEL_ID, capturing the channel by which a registration was received.
• AMS_MEDIA_CHANNELS — references CHANNEL_ID, extending the channel definition with media-specific configuration.
• HZ_PARTIES — the referenced party registry for PARTY_ID, resolving the vendor or organization linked to a channel.
• FND_USER — resolves MANAGED_BY_PERSON_ID to the internal owner of the channel.
• FND_SECURITY_GROUPS — resolves SECURITY_GROUP_ID and governs row-level access to channel records.

Collectively these relationships establish AMS_CHANNELS_B as a central reference hub for channel configuration across Oracle Marketing and its adjacent campaign, activity, and event processes.