Search Results amv_u_my_channels




Overview
Explore in AI Chat

Overview

AMV_U_MY_CHANNELS is an Oracle E-Business Suite table owned by the AMV schema, which supports the Marketing Encyclopedia System (MES) product. The table records the subscription relationships between users or user groups and the channels or categories they have elected to follow. In practical terms, it is the personalization and delivery-preference store for MES channel content: each row represents a single subscription entry linking an actor (a user or a group) to a subscribable target (a channel or a category), together with associated delivery, notification, and status attributes.

The table is classified as VALID in both Oracle EBS 12.1.1 and 12.2.2, and its column set is stable across those releases. Applying a heuristic Data Vault model, AMV_U_MY_CHANNELS is best characterized as a link table: it resolves a many-to-many association between subscribing actors and subscribed-to targets, and carries descriptive satellite-style attributes (status, ordering, notification flags) alongside the relationship keys. This classification is a modeling suggestion rather than an enforced warehouse design.

Key Information Stored

The primary key is the surrogate column MY_CHANNEL_ID, enforced by the AMV_U_MY_CHANNELS_PK constraint and additionally covered by unique index AMV_U_MY_CHANNELS_U1. The business-key candidates are defined by unique index AMV_U_MY_CHANNELS_U2 over the combination (USER_OR_GROUP_ID, USER_OR_GROUP_TYPE, SUBSCRIBING_TO_ID, SUBSCRIBING_TO_TYPE), which guarantees that a given actor is not subscribed to the same target more than once.

Common Use Cases and Queries

Typical usage centers on determining which channels a user receives, personalizing MES content pages, and auditing subscription coverage across groups. A common query retrieves all active subscriptions for a user:

  • SELECT my_channel_id, subscribing_to_id, subscribing_to_type, status FROM amv_u_my_channels WHERE user_or_group_id = :p_user_id AND user_or_group_type = 'USER' AND status = 'ACTIVE';
  • Counting subscribers per channel: SELECT subscribing_to_id, COUNT(*) FROM amv_u_my_channels WHERE subscribing_to_type = 'CHANNEL' GROUP BY subscribing_to_id;
  • Locating notification-eligible rows using NOTIFY_FLAG and NOTIFICATION_INTERVAL_TYPE to drive outbound messaging.
  • Reconciling orphaned subscriptions by outer-joining to AMV_C_CHANNELS_B and AMV_C_CATEGORIES_B to detect targets that no longer exist.

Related Objects

The FK relationships documented for this table identify its most significant dependencies:

  • AMV_C_CHANNELS_B — joined on SUBSCRIBING_TO_ID for channel subscriptions.
  • AMV_C_CATEGORIES_B — joined on SUBSCRIBING_TO_ID for category subscriptions.
  • JTF_RS_RESOURCE_EXTNS — joined on USER_OR_GROUP_ID for individual resources.
  • JTF_RS_GROUPS_B — joined on USER_OR_GROUP_ID for group subscriptions.
  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for data segregation.
  • AMV_U_MY_CHANNELS_PK / _U1 / _U2 — the constraints and unique indexes governing row integrity.

Because AMV is a marketing content module, insert and update operations are generally performed through MES application logic rather than direct DML; direct SQL is best confined to read-only reporting and reconciliation.