Search Results amv_c_categories_b_pk

Overview

AMV_C_CATEGORIES_B is the base table within the Marketing Encyclopedia System (AMV) product family of Oracle E-Business Suite, holding the master definition of channel categories and their hierarchical sub-categories. It belongs to the AMV schema and is classified as VALID in both 12.1.1 and 12.2.2. The table sits at the center of the AMV content organization model, providing the structural groupings under which channels, items, and published marketing content are catalogued and surfaced to end users.

From a Data Vault modeling perspective, the heuristic classification supplied in the ETRM metadata is hub-leaning. This reflects the fact that CHANNEL_CATEGORY_ID functions as a durable, non-volatile business key that is referenced by numerous dependent tables. The table’s self-referencing foreign key (PARENT_CHANNEL_CATEGORY_ID to the same table) additionally supports a simple link role for the parent-child hierarchy, though the dominant characteristic remains that of a hub.

Key Information Stored

The table is defined with thirteen physical columns. The most significant are summarized below.

• CHANNEL_CATEGORY_ID — Surrogate primary key, constrained by AMV_C_CATEGORIES_B_PK, and the value propagated to every child table.
• PARENT_CHANNEL_CATEGORY_ID — Self-referencing foreign key pointing back to AMV_C_CATEGORIES_B; enables unlimited nesting of categories and sub-categories.
• CHANNEL_CATEGORY_ORDER — Sort sequence used when rendering the category tree in the UI and in reports.
• CHANNEL_COUNT — Denormalized count of channels associated with the category, used for display efficiency.
• APPLICATION_ID — Foreign key to FND_APPLICATION, scoping the category to the owning application.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting multi-tenant style data isolation.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS WHO columns and optimistic locking.
• ZD_EDITION_NAME — Editioning column introduced for Online Patching in 12.2.x.

Two unique constraints are documented: AMV_C_CATEGORIES_B_PK on CHANNEL_CATEGORY_ID, and AMV_C_CATEGORIES_B_U1 on the combination of CHANNEL_CATEGORY_ID and ZD_EDITION_NAME, which serves as the edition-aware business-key candidate.

Common Use Cases and Queries

Typical uses include building category navigation trees, filtering content by top-level groupings, and reconciling channel assignments. The following pattern retrieves a category with its parent:

• SELECT c.channel_category_id, c.parent_channel_category_id, c.channel_category_order, t.channel_category_name FROM amv_c_categories_b c, amv_c_categories_tl t WHERE c.channel_category_id = t.channel_category_id AND t.language = USERENV('LANG');

Recursive hierarchy extraction, used for rendering indented category reports, can be produced with a CONNECT BY query rooted at rows where PARENT_CHANNEL_CATEGORY_ID IS NULL. Aggregated channel counts per category are commonly joined against AMV_C_CHL_ITEM_MATCH to validate the stored CHANNEL_COUNT.

Related Objects

The table participates in an extensive foreign-key network:

• AMV_C_CATEGORIES_TL — Translation table joined on CHANNEL_CATEGORY_ID for language-specific names and descriptions.
• AMV_C_CHANNELS_B — Channels linked to categories via CHANNEL_CATEGORY_ID.
• AMV_C_CHL_ITEM_MATCH — Item-to-channel matching, joined on CHANNEL_CATEGORY_ID.
• AMV_U_ACCESS — User access records joined on ACCESS_TO_TABLE_RECORD_ID.
• AMV_U_MY_CHANNELS and AMV_U_EXT_SUBSCRIPTIONS — User subscription tables joined on SUBSCRIBING_TO_ID.
• FND_APPLICATION and FND_SECURITY_GROUPS — Referenced masters for APPLICATION_ID and SECURITY_GROUP_ID.

Together these relationships establish AMV_C_CATEGORIES_B as the structural anchor of the Marketing Encyclopedia System’s content taxonomy.