Search Results act_contact_used_by_id

Overview

The AMS_ACT_CONTACT_POINTS table is a core data object within the Oracle E-Business Suite Marketing (AMS) module. It stores every contact point associated with Marketing objects, providing the repository through which campaigns, activities, and other marketing entities resolve the phone numbers, e-mail addresses, and postal addresses used to reach prospects and customers. The table resides in the AMS schema and is marked VALID in Oracle EBS 12.1.1 and 12.2.2.

Marketing applications rely on this object to maintain a one-to-many relationship between a marketing activity or contact and the individual communication channels attached to it. From a Data Vault modeling perspective, the mined relationship structure classifies AMS_ACT_CONTACT_POINTS as a standalone entity. This classification should be treated as a heuristic suggestion rather than a prescriptive design: the table combines descriptive contact detail (type, value, geography) with foreign-key references to security structures, giving it a hybrid role that can inform either a hub or a satellite construct in a downstream warehouse.

Key Information Stored

The table's documented physical schema contains 32 columns. The most significant are summarized below.

• CONTACT_POINT_ID — The surrogate primary key, defined by the AMS_ACT_CONTACT_POINTS_PK constraint and mirrored by the unique index AMS_ACT_CONTACT_POINTS_U1. This is the definitive business-key candidate for row identity.
• CONTACT_POINT_TYPE — Classifies the channel or medium of the contact point (for example phone, e-mail, or address).
• CONTACT_POINT_VALUE — The actual communication value stored for the contact point, such as a telephone number or e-mail address.
• CONTACT_POINT_VALUE_ID — A secondary identifier that links the stored value to its underlying source or definition.
• ARC_CONTACT_USED_BY — Indicates the category of entity that owns or consumes the contact point.
• ACT_CONTACT_USED_BY_ID — The identifier of the specific marketing object or activity that uses the contact point.
• CITY, COUNTRY, ZIPCODE — Geographic components supporting postal and regional reporting.
• SECURITY_GROUP_ID — The foreign key to FND_SECURITY_GROUPS that enforces multi-organization and data-security partitioning.
• OBJECT_VERSION_NUMBER — Supports optimistic locking during concurrent updates.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle EBS descriptive flexfield columns used for client-specific extensions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — The standard WHO audit columns.

Common Use Cases and Queries

Typical usage centers on retrieving and segmenting contact points for marketing execution and reporting. The following pattern joins the table to its security groups to return secure contact data:

• SELECT c.CONTACT_POINT_ID, c.CONTACT_POINT_TYPE, c.CONTACT_POINT_VALUE FROM AMS.AMS_ACT_CONTACT_POINTS c WHERE c.SECURITY_GROUP_ID = :p_security_group_id
• Filtering by CONTACT_POINT_TYPE isolates e-mail or telephone channels for campaign delivery validation.
• Grouping by COUNTRY or CITY supports regional penetration and geographic distribution reports.
• Joining on ACT_CONTACT_USED_BY_ID links contact points back to the marketing activity that consumes them.
• Audit and reconciliation queries evaluate LAST_UPDATE_DATE ranges to detect recently modified contact data.

Related Objects

• FND_SECURITY_GROUPS — Referenced through the documented foreign key on SECURITY_GROUP_ID; governs data visibility.
• AMS_ACT_CONTACT_POINTS_PK — The primary key constraint enforcing row uniqueness.
• AMS_ACT_CONTACT_POINTS_U1 — The unique index on CONTACT_POINT_ID.
• AMS_ACT_CONTACTS — The associated marketing contact entity through which contact-point ownership is resolved.
• AMS_ACT_CONTACT_POINTS consumer entities, including marketing activity and campaign tables that join through ACT_CONTACT_USED_BY_ID.