Search Results amv_c_chl_item_match




Overview
Explore in AI Chat

Overview

AMV_C_CHL_ITEM_MATCH is a table in the AMV schema belonging to the Marketing Encyclopedia System (MES) module of Oracle E-Business Suite. It records the publication relationships between marketing items and the channels through which those items are exposed to users. In practical terms, the table answers the question: "which items are published to which channels, and under what approval, availability, and security conditions?" It is the associative junction that ties the item master content stored in JTF_AMV_ITEMS_B to the delivery channels defined in AMV_C_CHANNELS_B.

From a Data Vault modeling perspective, the ETRM FK structure suggests that AMV_C_CHL_ITEM_MATCH functions as a link entity. Its foreign keys resolve to independent hubs (items, channels, categories, applications), and the presence of descriptive attributes such as approval status, approval date, and availability dates indicates that the table also carries satellite-like descriptive context alongside the link. It is therefore best treated as a link table with embedded descriptive columns rather than a pure transactional or reference table.

The object is VALID in both 12.1.1 and 12.2.2 and is owned exclusively by the AMV schema. The documented physical schema comprises 18 columns.

Key Information Stored

The primary key is AMV_C_CHL_ITEM_MATCH_PK, defined on the surrogate column CHANNEL_ITEM_ID. A unique index, AMV_C_CHL_ITEM_MATCH_U1 (also documented as AMV_C_CHL_ITEM_MATCH_UK1), is also defined on CHANNEL_ITEM_ID; because it duplicates the primary key column rather than defining a separate composite key, the true business key is effectively the combination CHANNEL_ID + ITEM_ID + TABLE_NAME_CODE.

Common Use Cases and Queries

Reporting on published content is the dominant use case. Administrators query this table to determine which items are live on a given channel, whether approvals are pending, and which publications are territory- or security-group restricted.

  • Channel content listing: join to JTF_AMV_ITEMS_B and AMV_C_CHANNELS_B to render a catalog of published items per channel.
  • Approval audits: filter on APPROVAL_STATUS_TYPE to identify unapproved or stale publications.
  • Availability scheduling: select rows where AVAILABLE_FOR_CHANNEL_DATE is in the future to preview upcoming publications.
  • Security checks: restrict results by SECURITY_GROUP_ID to enforce org-level visibility.

A representative query pattern:

SELECT i.item_name, c.channel_name, m.approval_status_type, m.available_for_channel_date FROM amv.amv_c_chl_item_match m, jtf_amv_items_b i, amv.amv_c_channels_b c WHERE m.item_id = i.item_id AND m.channel_id = c.channel_id AND m.security_group_id = :sg_id;

Related Objects

  • JTF_AMV_ITEMS_B — Joined on ITEM_ID; the item master holding published content.
  • AMV_C_CHANNELS_B — Joined on CHANNEL_ID; defines delivery channels.
  • AMV_C_CATEGORIES_B — Joined on CHANNEL_CATEGORY_ID; provides category grouping.
  • FND_APPLICATION — Joined on APPLICATION_ID; identifies the owning application.
  • FND_SECURITY_GROUPS — Joined on SECURITY_GROUP_ID; enforces security partitioning.