Search Results ams_categories_b_pk

Overview

The AMS_CATEGORIES_B table is a core master data table within the Oracle E-Business Suite Marketing (AMS) module. It serves as the central repository for defining and storing hierarchical category structures. As indicated in the ETRM documentation, these categories are utilized across multiple functional areas within Marketing, specifically for classifying and organizing Events, Metrics, and Deliverables. The table's design supports a parent-child hierarchy, enabling the creation of multi-level category trees (e.g., Type -> Sub-Type). Its role is fundamental to maintaining standardized classification, which drives reporting, filtering, and business logic in associated AMS components.

Key Information Stored

The primary column in AMS_CATEGORIES_B is CATEGORY_ID, which serves as the unique identifier (Primary Key: AMS_CATEGORIES_B_PK) for each category record. A critical structural column is PARENT_CATEGORY_ID, which creates the self-referential hierarchy by pointing to the CATEGORY_ID of a parent record; a NULL value in this column typically indicates a top-level category. While the ETRM excerpt does not list all columns, standard category tables in EBS commonly include columns for controlling the category's status (e.g., ENABLED_FLAG), start and end dates, and a user-defined code (CATEGORY_CODE). The descriptive, translatable name for the category is stored in the related TL (Translation) table, AMS_CATEGORIES_TL, which is linked via the CATEGORY_ID foreign key.

Common Use Cases and Queries

A primary use case is querying the category hierarchy for setup or reporting purposes. For instance, to list all active sub-categories under a specific parent, one might use a hierarchical SQL query. Another common scenario involves joining this table to transactional data to report on metrics or deliverables by their category.

• Sample Query: Hierarchical Category List
  SELECT CATEGORY_ID, PARENT_CATEGORY_ID, CATEGORY_CODE FROM AMS.AMS_CATEGORIES_B WHERE ENABLED_FLAG = 'Y' START WITH PARENT_CATEGORY_ID IS NULL CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID;
• Sample Query: Joining to Transaction Data (Metrics)
  SELECT M.METRIC_CODE, C.CATEGORY_CODE AS METRIC_CATEGORY FROM AMS.AMS_METRICS_ALL_B M, AMS.AMS_CATEGORIES_B C WHERE M.METRIC_CATEGORY = C.CATEGORY_ID;

Related Objects

As detailed in the foreign key metadata, AMS_CATEGORIES_B is extensively referenced by key transactional and setup tables across the Marketing module. Major related objects include:

• AMS_CATEGORIES_TL: Stores the translated names and descriptions for categories.
• AMS_METRICS_ALL_B: References the table twice, for METRIC_CATEGORY and METRIC_SUB_CATEGORY.
• AMS_DELIVERABLES_ALL_B: References the table for both CATEGORY_TYPE_ID and CATEGORY_SUB_TYPE_ID.
• AMS_ACT_CATEGORIES, AMS_ACT_PRODUCTS, AMS_CAT_ACTIVITIES: Associate categories with campaign activities and products.
• AMS_METRIC_FORMULAS: Uses categories as a SOURCE_ID for formula definitions.
• Self-Reference: The table has a foreign key to itself via PARENT_CATEGORY_ID to maintain the hierarchy.