Results for “ams_act_contact_points_n2”

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

Overview

AMS.AMS_ACT_CONTACT_POINTS is a transactional table in the Oracle E-Business Suite Marketing (AMS) schema that stores the contact details associated with marketing objects. Contact points captured here include telephone numbers, website URLs, physical addresses, and the scripts used by telemarketing or telesales agents when contacting a customer. The table therefore acts as the central repository linking a marketing entity — such as a campaign, an event, or a list — to one or more channels through which that entity may be reached or promoted.

The object carries Status VALID in both Oracle EBS 12.1.1 and 12.2.2, resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and is flagged as FND Design Data under the AMS application. From a Data Vault modeling perspective, the mined relationship structure classifies this object heuristically as standalone; in practice it behaves as a satellite-like detail table attached to a marketing object, since its foreign key relationship is limited to SECURITY_GROUP_ID referencing FND_SECURITY_GROUPS rather than to a parent marketing entity through a declared constraint. The association to the owning marketing record is instead carried by the polymorphic pair ARC_CONTACT_USED_BY and ACT_CONTACT_USED_BY_ID.

Key Information Stored

The table contains 32 columns in the documented 12.2.2 schema. The most operationally significant are:

• CONTACT_POINT_ID — surrogate primary key, enforced by unique index AMS_ACT_CONTACT_POINTS_U1 and constraint AMS_ACT_CONTACT_POINTS_PK.
• CONTACT_POINT_VALUE_ID — business identifier used by nonunique index AMS_ACT_CONTACT_POINTS_N2; supports lookup of a contact point by its underlying value identifier.
• ARC_CONTACT_USED_BY — the marketing object class that owns the contact point, validated against lookup type AMS_SYS_ARC_QUALIFIER (for example the value 'CAMP' for campaign).
• ACT_CONTACT_USED_BY_ID — the primary key of the owning marketing object; when ARC_CONTACT_USED_BY is 'CAMP' this holds the campaign identifier. Together with ARC_CONTACT_USED_BY and CONTACT_POINT_TYPE it forms nonunique index AMS_ACT_CONTACT_POINTS_N1.
• CONTACT_POINT_TYPE — the channel classification: phone, website, address, and similar values.
• CONTACT_POINT_VALUE — the actual value whose meaning depends on CONTACT_POINT_TYPE; for a phone contact point it stores the telephone number.
• CITY — city component, meaningful only when the contact point type is address.
• COUNTRY — country component, meaningful only for address contact points; resolves to a location hierarchy identifier in JTF_LOC_HIERARCHIES_B.
• ZIPCODE — postal code for address contact points.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org style data security.
• OBJECT_VERSION_NUMBER — optimistic locking column used to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Who columns for audit and row-level lineage.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the flexfield (DFF) extension columns available for customer-specific descriptive data.

Common Use Cases and Queries

The table is primarily consumed by Marketing contact management, campaign execution, and telemarketing integration. Typical reporting retrieves all contact points for a campaign or resolves a phone number back to its owning marketing object.

• Retrieve phone contacts for a specific campaign: SELECT CONTACT_POINT_ID, CONTACT_POINT_VALUE FROM AMS.AMS_ACT_CONTACT_POINTS WHERE ARC_CONTACT_USED_BY = 'CAMP' AND ACT_CONTACT_USED_BY_ID = :campaign_id AND CONTACT_POINT_TYPE = 'PHONE'; This predicate matches index AMS_ACT_CONTACT_POINTS_N1.
• Locate a contact point from its value identifier: SELECT * FROM AMS.AMS_ACT_CONTACT_POINTS WHERE CONTACT_POINT_VALUE_ID = :value_id; which is served by index AMS_ACT_CONTACT_POINTS_N2 — the index name that prompted the original search.
• Address cleansing or geocoding extracts filter on CONTACT_POINT_TYPE = 'ADDRESS' and project CITY, COUNTRY, and ZIPCODE.
• Security-scoped extracts join FND_SECURITY_GROUPS on SECURITY_GROUP_ID to restrict rows to the operating unit visible to the querying responsibility.
• Audit queries compare LAST_UPDATE_DATE and LAST_UPDATED_BY to identify recently modified contact data, while OBJECT_VERSION_NUMBER supports detection of conflicting updates in integration programs.

Related Objects

• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID; the only declared foreign key and the basis for row-level security filtering.
• AMS_SYS_ARC_QUALIFIER (lookup type) — defines the permitted values of ARC_CONTACT_USED_BY, which identifies the marketing object class owning each contact point.
• JTF_LOC_HIERARCHIES_B — the location hierarchy used to resolve the COUNTRY value for address contact points.
• AMS_ACT_CONTACT_POINTS_PK / U1 / N1 / N2 — the primary key constraint and the unique and nonunique indexes that govern access paths to CONTACT_POINT_ID and the ownership and value columns.
• Owning marketing entities (campaigns, events, lists) — joined polymorphically through ARC_CONTACT_USED_BY and ACT_CONTACT_USED_BY_ID rather than through a declared constraint; application logic resolves the specific parent table at runtime.
• FND_ATTRIBUTE_VALUES / attribute DFF tables — hold descriptive flexfield definitions and values stored in ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15.

Because the table is classified as standalone with a single declared foreign key, custom reporting should rely on the AMS_SYS_ARC_QUALIFIER lookup and the ACT_CONTACT_USED_BY_ID pairing to join back to the proper marketing object, and should always constrain queries by SECURITY_GROUP_ID where responsibility-level data separation is required.