Search Results ams_hierarchies_all_b




Overview
Explore in AI Chat

Overview

The AMS_HIERARCHIES_ALL_B table is a core master data table within the Oracle E-Business Suite (EBS) Marketing (AMS) module. It serves as the central repository for defining and storing all hierarchical structures supported by Oracle Marketing. These hierarchies are fundamental for organizing and analyzing marketing data, enabling the classification of metrics, activities, and other marketing entities into meaningful, multi-level groupings. The table's role is to provide a standardized, reusable definition of hierarchies that can be applied across various marketing processes, ensuring consistency in reporting and analytical segmentation. Its multi-organization enabled structure, indicated by the "_ALL_" suffix, allows it to store data for multiple operating units within a single installation.

Key Information Stored

The table's primary purpose is to define the unique characteristics of each hierarchy. While the full column list is not detailed in the provided metadata, the documented primary and unique keys reveal its critical attributes. The HIERARCHY_ID column is the primary key, serving as the unique internal identifier for each hierarchy record. The HIERARCHY_CODE column is a unique, user-facing code or short name for the hierarchy. A foreign key relationship to FND_APPLICATION via the APPLICATION_ID column indicates the table stores the application context (e.g., Marketing, Service) with which the hierarchy is associated. Other typical columns in such a base table would include creation and last update dates, the user IDs responsible for those actions, and a descriptive name field, though the latter is often stored in the corresponding translation table, AMS_HIERARCHIES_ALL_TL.

Common Use Cases and Queries

This table is primarily referenced in setups and master data maintenance. Common use cases include configuring new metric hierarchies for campaign analysis or defining activity hierarchies for organizing marketing programs. A frequent reporting need is to list all available hierarchies within the system. A basic query for this, joining the base table with its translation table for descriptions, would be:

  • SELECT b.HIERARCHY_CODE, tl.NAME, b.APPLICATION_ID
  • FROM AMS_HIERARCHIES_ALL_B b, AMS_HIERARCHIES_ALL_TL tl
  • WHERE b.HIERARCHY_ID = tl.HIERARCHY_ID
  • AND tl.LANGUAGE = USERENV('LANG');

Another critical use case is validating foreign key relationships when querying metric or activity data, ensuring joins are performed on valid hierarchy identifiers.

Related Objects

The AMS_HIERARCHIES_ALL_B table is central to the Marketing data model, with several key objects depending on it. As per the metadata, it has a direct foreign key relationship with FND_APPLICATION. It is referenced as a parent table by AMS_ACT_METRICS_ALL and AMS_METRICS_ALL_B, meaning hierarchies are used to categorize activity metrics and general marketing metrics, respectively. The AMS_HIERARCHIES_ALL_TL table stores the translated descriptive names (e.g., NAME, DESCRIPTION) for the hierarchies defined in the base table, using HIERARCHY_ID as the foreign key. This relationship is standard for EBS objects requiring multilingual support.