Search Results amv_c_authors




Overview
Explore in AI Chat

Overview

AMV_C_AUTHORS is a transactional table owned by the AMV schema, which underpins the Marketing Encyclopedia System module in Oracle E-Business Suite 12.1.1 and 12.2.2. The table stores one-to-many author assignments for a specific channel, enabling the Marketing Encyclopedia to record multiple named contributors against a single channel record. In practice, it functions as the child detail table of the channel model, where each row associates one author with one channel.

From a Data Vault modeling perspective, the mined relationship structure classifies this table as satellite-leaning. It carries descriptive attributes attached to a parent channel key rather than representing an independent business hub or a pure many-to-many link. The presence of a surrogate primary key, a business unique key, standard audit columns, and a security group foreign key is consistent with the pattern used across AMV and other EBS transactional entities.

Key Information Stored

The table contains ten documented columns. The most significant are summarized below, distinguishing the surrogate key from the documented business-key candidates.

  • CHANNEL_AUTHOR_ID — Surrogate primary key, enforced by the AMV_C_AUTHORS_PK constraint. Also appears as unique index AMV_C_AUTHORS_U1. This is the internally generated identifier for each author-to-channel row.
  • CHANNEL_ID — Foreign key to the parent channel. Together with AUTHOR, it forms the documented business unique key in unique index AMV_C_AUTHORS_U2, preventing duplicate author entries on the same channel.
  • AUTHOR — The author name or identifier associated with the channel. This is the descriptive business attribute central to the table's purpose.
  • SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, controlling row-level access consistent with multi-organization and function security in EBS.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the EBS framework to detect concurrent updates.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns recording who created and last modified each row, and the login context of the change.

Common Use Cases and Queries

Because the table maps channels to their authors, it supports channel authoring metadata in the Marketing Encyclopedia and related publishing or catalog reporting. Typical scenarios include listing all authors for a channel, identifying channels with no assigned author, and auditing authorship changes over time.

To retrieve authors for a given channel:

  • SELECT channel_author_id, channel_id, author FROM amv.amv_c_authors WHERE channel_id = :channel_id ORDER BY author;

To find channels lacking author assignments using an outer join against the parent channel table (join on CHANNEL_ID):

  • SELECT c.channel_id FROM amv.amv_c_channels c LEFT JOIN amv.amv_c_authors a ON c.channel_id = a.channel_id WHERE a.channel_author_id IS NULL;

For audit reporting, joining on SECURITY_GROUP_ID to FND_SECURITY_GROUPS and filtering by LAST_UPDATE_DATE supports change tracking and data governance extracts. Because the business key is (CHANNEL_ID, AUTHOR), deduplication and reconciliation scripts should key on that pair rather than the surrogate identifier.

Related Objects

  • AMV_C_CHANNELS — The parent channel entity. Join on AMV_C_AUTHORS.CHANNEL_ID = AMV_C_CHANNELS.CHANNEL_ID; the documented foreign key relationship defines this dependency.
  • FND_SECURITY_GROUPS — Referenced by AMV_C_AUTHORS.SECURITY_GROUP_ID; governs access control and row visibility.
  • AMV_C_AUTHORS_PK — Primary key constraint on CHANNEL_AUTHOR_ID.
  • AMV_C_AUTHORS_UK1 / AMV_C_AUTHORS_U2 — Unique constraint and unique index on (CHANNEL_ID, AUTHOR), the documented business-key candidate.
  • AMV_C_AUTHORS_U1 — Unique index on CHANNEL_AUTHOR_ID.
  • AMV Marketing Encyclopedia UI and concurrent programs — The AMV application layer that inserts, updates, and deletes author rows through Oracle Forms and associated APIs.

Note that the documented foreign-key material maps AMV_C_AUTHORS.CHANNEL_ID to the channel parent without fully naming the target table, and CHANNEL_AUTHOR_ID to the security group. Implementation work should confirm the exact parent table definition against the specific EBS patch level before building custom joins.