Results for “ams_act_messages”

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

Overview

AMS_ACT_MESSAGES is an association table in the AMS (Marketing) product schema of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. Its stated purpose is to associate messages with a campaign or event. In practical terms, the table records which message definitions are deployed against a specific marketing activity, and whether that activity is a campaign, an event header, or an event offer. It therefore acts as the linking structure between the message library and the execution objects of the marketing module.

The ETRM metadata classifies this object heuristically as a link table under a Data Vault modeling convention. That classification is a modeling suggestion rather than a functional designation: the table carries no descriptive attributes of its own beyond audit columns, and its purpose is to resolve a many-to-many relationship between messages and the marketing activities that use them. The single degenerate attribute of interest is the discriminator column MESSAGE_USED_BY, which identifies which of the three possible parent entities the MESSAGE_USED_BY_ID value refers to.

Key Information Stored

The table contains eleven documented columns. The most significant are listed below.

• ACT_MESSAGE_ID — Surrogate primary key, enforced by AMS_ACT_MESSAGES_PK. It is also the sole business-key candidate, backed by unique index AMS_ACT_MESSAGES_U1.
• MESSAGE_ID — Foreign key to AMS_MESSAGES_B, identifying the message definition being associated.
• MESSAGE_USED_BY_ID — Polymorphic foreign key that points to the consuming entity. Depending on the value of MESSAGE_USED_BY, it references AMS_CAMPAIGNS_ALL_B, AMS_EVENT_HEADERS_ALL_B, or AMS_EVENT_OFFERS_ALL_B.
• MESSAGE_USED_BY — Discriminator that determines which of the three parent tables MESSAGE_USED_BY_ID resolves against.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting multi-org / security group partitioning of the row.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Application Framework to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS WHO columns capturing insert and update audit context.

No denormalized message text, status, or scheduling attributes are stored here; those reside on AMS_MESSAGES_B. The link table's responsibility is relationship persistence and auditability only.

Common Use Cases and Queries

Typical reporting and integration scenarios involve resolving the message library against specific campaigns or events, or determining which activities a given message has been attached to. A representative query joining the link to its message parent is:

• SELECT a.act_message_id, a.message_id, a.message_used_by, a.message_used_by_id FROM ams.ams_act_messages a WHERE a.message_used_by = 'CAMPAIGN';
• Joining to AMS_MESSAGES_B on MESSAGE_ID to retrieve message names and types for a given campaign.
• Filtering by SECURITY_GROUP_ID when reporting across multiple security groups or operating units.
• Auditing message reuse, for example counting how many campaigns reference the same MESSAGE_ID, which informs message library governance and retirement decisions.
• Validating orphaned rows, where MESSAGE_USED_BY_ID no longer resolves to a live parent, as part of data cleanup.

Because MESSAGE_USED_BY_ID is polymorphic, joins to the three parent tables must be guarded by the MESSAGE_USED_BY discriminator to avoid incorrect matches.

Related Objects

• AMS_MESSAGES_B — Parent message definition table, joined on MESSAGE_ID.
• AMS_CAMPAIGNS_ALL_B — Campaign parent, joined on MESSAGE_USED_BY_ID when MESSAGE_USED_BY indicates a campaign.
• AMS_EVENT_HEADERS_ALL_B — Event header parent, joined on the same column.
• AMS_EVENT_OFFERS_ALL_B — Event offer parent, joined on the same column.
• FND_SECURITY_GROUPS — Security group reference, joined on SECURITY_GROUP_ID.
• AMS_ACT_MESSAGES_PK and AMS_ACT_MESSAGES_U1 — Primary key constraint and unique index on ACT_MESSAGE_ID.

Together these objects define the message-to-activity association used throughout Oracle Marketing campaign and event execution.