Results for “ams_object_associations”

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

Overview

AMS_OBJECT_ASSOCIATIONS is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the cross-reference relationships between Campaigns, Events, Offers, Deliverables, and Creatives that are running concurrently. The table functions as the connective fabric of the AMS product family, allowing a single Campaign, Event, or Offer to be associated with a Master object and one or more "Using" objects, each carrying its own role through the PRIMARY_FLAG and USAGE_TYPE columns.

The object is owned by the AMS schema and is listed as VALID in the ETRM 12.2.2 repository. It is physically implemented with 40 columns and a single-column surrogate primary key. Based on the foreign key structure mined from the metadata, a heuristic Data Vault classification of satellite-leaning is suggested: the table records descriptive attributes (quantities, cost controls, flags, and descriptive flexfields) about the relationship between two business objects, rather than acting as a pure hub or link. Practitioners modeling this table for analytics should treat it as a link-satellite hybrid, where OBJECT_ASSOCIATION_ID is the durable key and the descriptive columns constitute the satellite payload.

Key Information Stored

The most significant columns documented in the ETRM metadata include:

• OBJECT_ASSOCIATION_ID — surrogate primary key, enforced by AMS_OBJECT_ASSOCIATIONS_PK and reinforced by the unique index AMS_OBJECT_ASSOCIATIONS_U1. It is the only documented business-key candidate.
• MASTER_OBJECT_TYPE / MASTER_OBJECT_ID — identifies the parent object (Campaign, Event, or Offer) at the head of the association.
• USING_OBJECT_TYPE / USING_OBJECT_ID — identifies the consuming object (Campaign, Event, Offer, or Deliverable) that draws on the master.
• PRIMARY_FLAG — distinguishes the primary association from secondary or ancillary relationships.
• USAGE_TYPE — categorizes the nature of the association (for example, budget, resource, or creative usage).
• QUANTITY_NEEDED / QUANTITY_NEEDED_BY_DATE — the requested quantity and its required date.
• COST_FROZEN_FLAG, PCT_OF_COST_TO_CHARGE_USED_BY, MAX_COST_TO_CHARGE_USED_BY, MAX_COST_CURRENCY_CODE — cost-control attributes governing what portion of the master object's cost may be charged through this association; MAX_COST_CURRENCY_CODE joins to FND_CURRENCIES.
• FULFILL_ON_TYPE_CODE and METRIC_CLASS — fulfillment and metric classification codes.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — the standard EBS descriptive flexfield segment set.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enabling multi-org-style security filtering.

Common Use Cases and Queries

Typical reporting scenarios include identifying all offers attached to a campaign, calculating the percentage of campaign cost already consumed, and tracing which deliverables fulfilled a given event. A representative query joins the association table to the master campaign:

• SELECT ao.object_association_id, ao.master_object_id, ao.using_object_id, ao.pct_of_cost_to_charge_used_by FROM ams.ams_object_associations ao WHERE ao.master_object_type = 'CAMPAIGN' AND ao.using_object_type = 'OFFER';
• SELECT ao.*, c.campaign_name FROM ams.ams_object_associations ao JOIN ams.ams_campaigns_all_b c ON c.campaign_id = ao.master_object_id AND ao.master_object_type = 'CAMPAIGN';

Because AMS_ACT_METRICS_ALL and AMS_ACT_METRIC_HST both reference ACTIVITY_METRIC_ORIGIN_ID back to this table, association rows are also the origin point for activity metrics — useful for attribution and ROI reporting where cost and response metrics must be linked to a specific campaign-event-offer combination.

Related Objects

• AMS_CAMPAIGNS_ALL_B — joined via MASTER_OBJECT_ID and USING_OBJECT_ID.
• AMS_EVENT_HEADERS_ALL_B — joined via MASTER_OBJECT_ID and USING_OBJECT_ID.
• AMS_EVENT_OFFERS_ALL_B — joined via MASTER_OBJECT_ID and USING_OBJECT_ID.
• AMS_DELIVERABLES_ALL_B — joined via USING_OBJECT_ID.
• FND_CURRENCIES — joined via MAX_COST_CURRENCY_CODE.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID.
• AMS_ACT_METRICS_ALL — references ACTIVITY_METRIC_ORIGIN_ID to this table.
• AMS_ACT_METRIC_HST — references ACTIVITY_METRIC_ORIGIN_ID to this table.