Results for “ams_cat_activities_u1”

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

Overview

AMS_CAT_ACTIVITIES is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the activities associated with a category when that category is created for budgets. It functions as the association table between marketing categories and their underlying activities, allowing budget-oriented categories to enumerate the media activities that contribute to their budgeted spend. The table resides in the AMS schema and is classified as VALID in the data dictionary.

Under the heuristic Data Vault classification mined from its foreign key structure, AMS_CAT_ACTIVITIES is best modeled as a link table. Its core role is to resolve the many-to-many relationship between categories (AMS_CATEGORIES_B) and marketing activities (AMS_MEDIA_B), while carrying surrogate key and audit metadata typical of a transactional association entity. This classification is a modeling suggestion only; the operational table is a standard EBS intersection table, not a formal Data Vault construct.

Key Information Stored

The table contains 26 documented columns. The most significant are:

• CAT_ACTIVITY_ID — Surrogate primary key of the row, enforced by the AMS_CAT_ACTIVITIES_PK constraint. This is the identifier most commonly referenced when the table is searched by the term "cat_activity_id," and it also appears in the unique index AMS_CAT_ACTIVITIES_U1.
• CATEGORY_ID — Foreign key to AMS_CATEGORIES_B, identifying the budget category to which the activity association belongs.
• ACTIVITY_ID — Foreign key to AMS_MEDIA_B, identifying the specific activity (media activity) associated with the category. This column is also a business-key candidate, enforced by the unique index AMS_CAT_ACTIVITIES_U2.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, used to scope row-level access according to the operating unit or security profile under which the record was created.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications Framework to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns that record who created and last modified the record, when, and under which login session.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The flexible descriptive flexfield (DFF) columns, available for customer-specific extension of the category-to-activity association.

Two unique indexes serve as documented business-key candidates: AMS_CAT_ACTIVITIES_U1 on CAT_ACTIVITY_ID and AMS_CAT_ACTIVITIES_U2 on ACTIVITY_ID. The primary key is defined on CAT_ACTIVITY_ID via AMS_CAT_ACTIVITIES_PK.

Common Use Cases and Queries

Typical use cases involve reporting on which activities are budgeted under a given category, validating the completeness of activity assignments, and extracting the surrogate CAT_ACTIVITY_ID for downstream integration or interface tables.

• Listing all activities associated with a specific category:

  SELECT cat_activity_id, category_id, activity_id
  FROM   ams.ams_cat_activities
  WHERE  category_id = :category_id;

• Resolving a CAT_ACTIVITY_ID back to its category and activity:

  SELECT a.cat_activity_id, a.category_id, a.activity_id
  FROM   ams.ams_cat_activities a
  WHERE  a.cat_activity_id = :cat_activity_id;

• Joining to AMS_MEDIA_B to retrieve activity names for budget reporting:

  SELECT c.category_id, m.media_id, m.name
  FROM   ams.ams_cat_activities c,
         ams.ams_media_b m
  WHERE  c.activity_id = m.media_id;

• Security-scoped extraction using SECURITY_GROUP_ID for multi-org or profile-restricted reporting.

Because the table carries OBJECT_VERSION_NUMBER and the standard WHO audit columns, any programmatic insert or update should populate them consistently to avoid concurrent-update conflicts within the Marketing application.

Related Objects

The following objects are the most significant tables that relate to AMS_CAT_ACTIVITIES through its documented foreign keys and primary key structure:

• AMS_CATEGORIES_B — Referenced via CATEGORY_ID; the base table holding marketing category definitions.
• AMS_MEDIA_B — Referenced via ACTIVITY_ID; the base table holding marketing media activity definitions.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID; controls row-level security scoping.
• The primary key constraint AMS_CAT_ACTIVITIES_PK and unique indexes AMS_CAT_ACTIVITIES_U1 and AMS_CAT_ACTIVITIES_U2, which enforce the surrogate and business-key uniqueness of the row.
• Standard EBS audit and DFF infrastructure represented by the ATTRIBUTE1–ATTRIBUTE15 and WHO columns, shared across AMS transactional tables.

These relationships position AMS_CAT_ACTIVITIES as a dependent link between the category and activity master tables, and any deletion or merge of a category or activity should be evaluated for its impact on the corresponding rows in this table.