Search Results amv_c_categories_vl




Overview
Explore in AI Chat

Overview

AMV_C_CATEGORIES_VL is a Marketing Encyclopedia System (AMV) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents marketing channel category definitions — the hierarchical grouping structure used to organize marketing content, collateral, and channel-related material within the Marketing Encyclopedia — in the language of the current session. The "_VL" suffix denotes a "view with language," an Oracle EBS naming convention indicating that the object joins a base table to its translation table and filters the translation rows by USERENV('LANG'). As a result, the view returns exactly one row per channel category, carrying the translated name and description while exposing all operational columns from the base table. It is the standard reporting and integration entry point for channel category data, because it hides the language-join complexity from callers and guarantees a single, session-appropriate row per category.

Underlying Base Objects

The view is defined over two documented base objects:

  • AMV_C_CATEGORIES_B — the base table storing language-independent category attributes such as identifiers, ordering, hierarchy, and audit columns.
  • AMV_C_CATEGORIES_TL — the translation table storing language-dependent attributes: CHANNEL_CATEGORY_NAME and DESCRIPTION, keyed by language.

The join condition is B.CHANNEL_CATEGORY_ID = T.CHANNEL_CATEGORY_ID AND T.LANGUAGE = USERENV('LANG'). Because the filter is applied against the session language, callers cannot retrieve alternate-language translations through this view; that requires querying AMV_C_CATEGORIES_TL directly. The view also selects B.ROWID, which Oracle EBS uses to support updatable-view behavior on the base entity.

Key Columns

  • CHANNEL_CATEGORY_ID — primary identifier for the channel category; the join key across the base and translation tables.
  • CHANNEL_CATEGORY_NAME — translated display name of the category, sourced from the TL table.
  • DESCRIPTION — translated description, sourced from the TL table.
  • PARENT_CHANNEL_CATEGORY_ID — self-referencing parent identifier that defines the category hierarchy; null indicates a root-level category.
  • CHANNEL_CATEGORY_ORDER — sequencing value controlling display order within a parent.
  • CHANNEL_COUNT — count of channels or associated items linked to the category.
  • APPLICATION_ID — owning application identifier, establishing the category's product context.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the framework during concurrent updates.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns; useful for incremental extraction and troubleshooting.
  • ROW_ID — row identifier carried from the base table for view updatability.

Common Use Cases and Queries

Typical uses include marketing taxonomy reporting, channel hierarchy validation, integration extracts into data warehouses, and lookups feeding LOV or concurrent programs. Root categories are located by filtering on the parent column, as shown below:

  • SELECT channel_category_id, channel_category_name, description FROM amv_c_categories_vl WHERE parent_channel_category_id IS NULL ORDER BY channel_category_order;
  • SELECT child.channel_category_id, child.channel_category_name, parent.channel_category_name AS parent_name FROM amv_c_categories_vl child, amv_c_categories_vl parent WHERE child.parent_channel_category_id = parent.channel_category_id ORDER BY parent.channel_category_name, child.channel_category_order;
  • SELECT channel_category_id, channel_category_name FROM amv_c_categories_vl WHERE last_update_date >= :p_since_date ORDER BY last_update_date; — for incremental extract.

The view always returns session-language text, so reports and extracts inherit the language of the executing user. Define a synonym or grant for any schema requiring access, and reference the view rather than the base tables to preserve translation correctness and single-row-per-category semantics.