Results for “ams_act_categories”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_ACT_CATEGORIES is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the generic category assignments an activity can belong to. Its stated purpose is to support reporting and search capability by classifying activities against entries in the AMS_CATEGORIES_B lookup, while also recording the specific activity object the classification applies to. The table is owned by the AMS schema and is documented as VALID with 27 physical columns in the 12.2.2 ETRM schema.

The table is not the underlying activity entity itself; it is a classification or association record. A heuristic Data Vault classification mined from the foreign key structure places this object as a link. In practice this is a reasonable modeling suggestion, because AMS_ACT_CATEGORIES connects a category definition to a specific activity-side object through the ACT_CATEGORY_USED_BY_ID column, rather than holding intrinsic descriptive attributes of a single business entity.

Key Information Stored

The most significant columns are as follows:

• ACTIVITY_CATEGORY_ID — surrogate primary key (AMS_ACT_CATEGORIES_PK). This is the unique identifier for each category-assignment row.
• CATEGORY_ID — foreign key to AMS_CATEGORIES_B; identifies the category being assigned.
• ACT_CATEGORY_USED_BY_ID — the polymorphic activity-side reference. It is part of the unique business key (AMS_ACT_CATEGORIES_UK1 / U2) and is documented as referencing multiple potential parent objects, including AMS_EVENT_HEADERS_ALL_B, AMS_CHANNELS_B, AMS_EVENT_OFFERS_ALL_B, and AMS_DELIVERABLES_ALL_B.
• ARC_ACT_CATEGORY_USED_BY — discriminator column that indicates which parent object the ACT_CATEGORY_USED_BY_ID value points to.
• SECURITY_GROUP_ID — supports Multi-Org / security group access control; foreign key to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — optimistic locking column for concurrent updates.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield (DFF) columns, allowing customer-specific extension.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

The documented unique indexes are AMS_ACT_CATEGORIES_U1 on ACTIVITY_CATEGORY_ID and AMS_ACT_CATEGORIES_U2 on the combination of ACT_CATEGORY_USED_BY_ID, ARC_ACT_CATEGORY_USED_BY, and CATEGORY_ID, making the latter the business-key candidate that enforces one category per activity object.

Common Use Cases and Queries

Typical usage involves retrieving all categories attached to a given event, channel, offer, or deliverable for reporting and search. Because the parent reference is polymorphic, queries must filter on both the ID and the ARC_ACT_CATEGORY_USED_BY discriminator to avoid mixing object types.

A representative pattern retrieving categories for a specific parent object is:

• SELECT ac.ACTIVITY_CATEGORY_ID, ac.CATEGORY_ID, cb.CATEGORY_NAME FROM AMS_ACT_CATEGORIES ac, AMS_CATEGORIES_B cb
  WHERE ac.CATEGORY_ID = cb.CATEGORY_ID
  AND ac.ACT_CATEGORY_USED_BY_ID = :parent_id
  AND ac.ARC_ACT_CATEGORY_USED_BY = :parent_type;

Reporting scenarios include activity classification summaries, category-to-activity counts, and join-based reporting against AMS_CATEGORIES_B to resolve category names. Search functionality in the Marketing UI depends on this table to filter activities by category.

Related Objects

The following objects are the most significant references and dependencies:

• AMS_CATEGORIES_B — joined on CATEGORY_ID; the category definition table.
• AMS_EVENT_HEADERS_ALL_B — potential parent activity, joined on ACT_CATEGORY_USED_BY_ID.
• AMS_CHANNELS_B — potential parent activity, joined on ACT_CATEGORY_USED_BY_ID.
• AMS_EVENT_OFFERS_ALL_B — potential parent activity, joined on ACT_CATEGORY_USED_BY_ID.
• AMS_DELIVERABLES_ALL_B — potential parent activity, joined on ACT_CATEGORY_USED_BY_ID.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for security group access.

These relationships confirm the table's role as an associative link between a category definition and the specific activity object it classifies, consistent with its heuristic Data Vault classification as a link.