Results for “ams_campaign_relations”

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

Overview

The AMS_CAMPAIGN_RELATIONS table resides in the AMS schema and is a core component of the Oracle Marketing (AMS) module within Oracle E-Business Suite 12.1.1 and 12.2.2. As its name and documented description indicate, the table was originally created to support an OSM (Oracle Sales and Marketing) upgrade path, and it persists as the authoritative store of parent-child relationships between marketing campaigns. Each row captures a directed link between two campaign records held in AMS_CAMPAIGNS_ALL_B, allowing campaigns to be organized into hierarchies, programs, or family structures for reporting and execution purposes.

From a data modeling perspective, the mined metadata classifies AMS_CAMPAIGN_RELATIONS as a link table. This is a modeling suggestion rather than an enforced attribute: the table carries no descriptive business attributes of its own, but instead resolves a many-to-many relationship between campaigns, including self-referencing links (a campaign may act as both parent and child in different rows). The presence of a surrogate primary key, two foreign keys pointing to the same base table, and a security group column are all characteristic of a Data Vault link structure.

Key Information Stored

The table is documented with eleven columns. The most significant are described below.

• CAMPAIGN_RELATION_ID — The surrogate primary key, enforced by the constraint AMS_CAMPAIGN_RELATIONS_PK and backed by the unique index AMS_CAMPAIGN_RELATIONS_U1. This is the durable identifier for a relationship record; it is a surrogate key rather than a natural business key.
• PARENT_CAMPAIGN_ID — Foreign key to AMS_CAMPAIGNS_ALL_B identifying the upper-level campaign in the hierarchy.
• CHILD_CAMPAIGN_ID — Foreign key to AMS_CAMPAIGNS_ALL_B identifying the subordinate campaign.
• RELATION_TYPE_CODE — A code distinguishing the meaning of the parent-child association, allowing the same pair of campaigns to be linked under different semantics.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org / security-group based access control on relationship rows.
• OBJECT_VERSION_NUMBER — Optimistic locking token used by the Oracle Application Object Library for concurrency control during updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO columns providing the audit trail for row creation and modification.

The documented unique index AMS_CAMPAIGN_RELATIONS_U1 covers CAMPAIGN_RELATION_ID, confirming it as the sole business-key candidate; the parent/child pair is not documented as uniquely constrained, which is consistent with a link that may carry multiple relation types between the same two campaigns.

Common Use Cases and Queries

The primary use case is hierarchy traversal — resolving the full campaign tree for a program, rolling up child campaign metrics to a parent, or detecting orphaned and circular relationships.

• Retrieve direct children of a campaign:
  SELECT child_campaign_id FROM ams.ams_campaign_relations WHERE parent_campaign_id = :p_campaign_id;
• Retrieve the parent of a campaign:
  SELECT parent_campaign_id FROM ams.ams_campaign_relations WHERE child_campaign_id = :p_campaign_id;
• Isolate a specific relation type by joining on RELATION_TYPE_CODE and filtering with the security group.
• Feeding CONNECT BY / recursive CTE queries that walk the campaign hierarchy for reporting or analytics.

Typical consumers include Marketing reporting extracts, campaign roll-up dashboards, and integration flows that reconcile upgraded OSM data.

Related Objects

• AMS_CAMPAIGNS_ALL_B — The base campaign table; joined via CHILD_CAMPAIGN_ID and PARENT_CAMPAIGN_ID to resolve campaign names and attributes.
• AMS_CAMPAIGNS_ALL_TL — Provides translated campaign names for the joined campaign IDs.
• AMS_CAMPAIGNS_VL / _ALL_V — Descriptive views over the campaign base and translation tables, commonly used as the reporting entry point.
• FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID to enforce access control.
• AMS_CAMPAIGN_RELATIONS_PK / U1 — The primary key constraint and unique index governing uniqueness on CAMPAIGN_RELATION_ID.