Search Results ams_categories_vl




Overview
Explore in AI Chat

Overview

AMS_CATEGORIES_VL is a seeded, read-only view owned by the APPS schema in Oracle E-Business Suite. It is delivered as part of the AMS (Oracle Marketing) product family and is valid in both EBS 12.1.1 and 12.2.2. The view presents the marketing categories that are used throughout Oracle Marketing, specifically those associated with marketing deliverables and marketing events. Because categories in AMS are modeled as a translatable, multi-language entity, AMS_CATEGORIES_VL serves as the translated (VL, or "view language") interface: it joins the base table to its translation table and filters the translation rows to the language of the current session. This makes it the standard access point for any report, form, concurrent program, or integration that needs to display a marketing category by name rather than by internal identifier.

In the typical EBS layered architecture, a _VL view is the component intended for consumption by application code and end-user reporting, while the underlying _B and _TL tables hold the physical data. AMS_CATEGORIES_VL follows this convention exactly and should be treated as the supported public interface to marketing category data.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through APPS synonyms:

  • AMS_CATEGORIES_B — the base ("B") table holding language-independent columns such as CATEGORY_ID, parent relationships, enabled flag, accounting attributes, descriptive flexfield columns, and audit columns.
  • AMS_CATEGORIES_TL — the translation ("TL") table holding the language-dependent columns CATEGORY_NAME and DESCRIPTION, keyed by CATEGORY_ID and LANGUAGE.

The join condition is B.CATEGORY_ID = T.CATEGORY_ID AND T.LANGUAGE = USERENV('LANG'), which restricts the result set to the translation matching the session language. All non-translated columns are projected from AMS_CATEGORIES_B, while CATEGORY_NAME and DESCRIPTION are taken from AMS_CATEGORIES_TL. Because the view exposes the ROWID of the base table, it can be used as a key-preserved view for certain DML-through-view operations, though standard practice is to treat it as read-only for query purposes.

Key Columns

Common Use Cases and Queries

Because the view resolves the translation automatically, it is the preferred source whenever category names must be shown to users. Typical scenarios include marketing deliverable setup reports, event category listings, integration extracts feeding downstream marketing analytics, and validation queries confirming which categories are active and how they roll up hierarchically.

A simple active-category listing:

SELECT category_id, category_name, parent_category_id
FROM   ams_categories_vl
WHERE  enabled_flag = 'Y'
ORDER BY category_name;

Joining to translations for a specific ID and inspecting the DFF:

SELECT category_id, category_name, description,
       attribute_category, attribute1, ledger_id
FROM   ams_categories_vl
WHERE  category_id = :p_category_id;

Resolving the hierarchy through the self-referencing parent pointer:

SELECT c.category_name, p.category_name AS parent_name
FROM   ams_categories_vl c,
       ams_categories_vl p
WHERE  c.parent_category_id = p.category_id (+)
AND    c.enabled_flag = 'Y';

When the session language must not drive the result — for example, in a bilingual integration — query AMS_CATEGORIES_TL directly and filter on LANGUAGE explicitly, since AMS_CATEGORIES_VL always returns the USERENV('LANG') translation.