Search Results ams_act_change_req_items




Overview
Explore in AI Chat

Overview

The AMS_ACT_CHANGE_REQ_ITEMS table resides in the AMS (Marketing) schema and is a core component of Oracle E-Business Suite's Marketing module (AMS). It functions as a change-tracking repository that stores individual field-level modifications requested against an already-active marketing activity, such as a Promotion, Event, or Campaign. In Oracle EBS, once a marketing activity is activated and flowing through its lifecycle, certain attributes cannot be edited directly. Instead, a formal change request must be raised, routed for approval, and processed through workflow. This table captures the granular details of each such request — specifically, which column on the activity is being changed, its old value, and its new value.

The table serves as a transactional detail store tied to workflow-driven approval requests. Its role is analogous to an audit/change-item line table within the AMS workflow architecture. Under a heuristic Data Vault classification mined from its foreign key structure, this table leans toward a satellite pattern. This is a modeling suggestion: the table records descriptive, changing attributes (old/new values, descriptions) that are dependent on and contextualized by a parent workflow request key, rather than acting as an independent business hub or a pure many-to-many link.

Key Information Stored

The table contains 13 documented columns. The most operationally significant ones are summarized below.

The surrogate key (ACTIVITY_CHANGE_REQ_ITEM_ID) is distinct from business-key semantics; because the same U1 index covers the same column, no separate natural business key is documented, so the surrogate identifier doubles as the uniqueness constraint.

Common Use Cases and Queries

Typical usage centers on auditing and reporting change requests against active activities. A frequent query retrieves all change lines for a given workflow request:

  • Change-request audit trail — Join this table to AMS_ACT_WF_REQUESTS on ACTIVITY_WF_REQUEST_ID to report which fields were requested for change and their before/after values.
  • Approval workflow reporting — Aggregate change items per request to measure volume and scope of change activity.
  • Pending vs. applied value comparison — Compare COLUMN_OLD_VALUE and COLUMN_NEW_VALUE to analyze proposed modifications.

Sample pattern (illustrative):

  • SELECT i.ACTIVITY_CHANGE_REQ_ITEM_ID, i.USER_COLUMN_NAME, i.COLUMN_OLD_VALUE, i.COLUMN_NEW_VALUE, i.DESCRIPTION FROM AMS.AMS_ACT_CHANGE_REQ_ITEMS i WHERE i.ACTIVITY_WF_REQUEST_ID = :request_id;
  • Joining to AMS_ACT_WF_REQUESTS r ON i.ACTIVITY_WF_REQUEST_ID = r.ACTIVITY_WF_REQUEST_ID enriches results with request-level metadata.
  • Filtering by SECURITY_GROUP_ID enforces security-group isolation in multi-org reporting.

Related Objects

The following objects are most significant to this table based on documented foreign key relationships and lifecycle dependency.

  • AMS_ACT_WF_REQUESTS — Parent workflow request table; joined via ACTIVITY_WF_REQUEST_ID. This is the primary upstream entity defining the change request context.
  • FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID to resolve security-group ownership and data partition.
  • AMS_ACT_CHANGE_REQ_ITEMS_PK / _U1 — Indexes enforcing uniqueness on ACTIVITY_CHANGE_REQ_ITEM_ID.
  • AMS Activity base tables (e.g., AMS_ACTIVITIES and associated detail tables) — The targets whose attributes are represented by USER_COLUMN_NAME; indirectly referenced through the workflow request.
  • AMS Marketing workflow/approval APIs — Process these change items during activity modification approvals.

Administrators and developers should treat this object as a satellite detail table always accessed in the context of its parent workflow request, with security-group filtering applied for correct multi-org visibility.