Results for “ams_tcop_contacts”

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

Overview

AMS_TCOP_CONTACTS is an Oracle E-Business Suite table owned by the AMS schema (Marketing module). It resides in the Oracle Marketing foundation and is documented in ETRM release 12.1.1 / 12.2.2 with a status of VALID. The table records the individual contacts made to parties as a result of fatiguing schedules — that is, the automated outbound communications generated when a marketing campaign schedule governs how frequently, and through which media, a customer or prospect may be contacted. Each row represents a discrete contact event against a party within the context of a given campaign schedule and media channel.

In Data Vault modeling terms, the heuristic classification mined from the ETRM relationship metadata suggests this table functions as a link: it resolves many-to-many associations between parties, campaign schedules, and media, capturing the intersection of these dimensions at a point in time. It is not a pure dimension nor a descriptive satellite — its identity is defined by the combination of participating business keys and the contact event itself.

Key Information Stored

The documented physical schema contains 11 columns. The most functionally significant are:

• CONTACT_ID — the surrogate primary key, enforced by index AMS_TCOP_CONTACTS_PK1 and unique index AMS_TCOP_CONTACTS_U1. This is the system-generated identifier for each contact record.
• PARTY_ID — foreign key to HZ_PARTIES, identifying the customer, prospect, or organization that was contacted.
• SCHEDULE_ID — foreign key to AMS_CAMPAIGN_SCHEDULES_B, linking the contact to the specific fatiguing schedule that triggered it.
• MEDIA_ID — foreign key to AMS_MEDIA_B, indicating the delivery channel (e.g., email, direct mail, telemarketing) used for the contact.
• CONTACT_DATE — the effective date the contact occurred; central to frequency capping and fatigue analysis.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing Multi-Org / data security partitioning.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO-column audit trail attributes.

Two unique indexes are documented: AMS_TCOP_CONTACTS_U1 on CONTACT_ID and AMS_TCOP_CONTACTS_U2 on (SCHEDULE_ID, PARTY_ID). The latter is the strongest business-key candidate, as it prevents duplicate contact records for the same party within the same schedule.

Common Use Cases and Queries

Primary use cases include fatigue management reporting, campaign frequency analysis, and compliance audits confirming that contact thresholds were honored. A representative query counts contacts per party per schedule over a rolling window:

• SELECT party_id, schedule_id, COUNT(*) FROM ams.ams_tcop_contacts WHERE contact_date >= :from_date GROUP BY party_id, schedule_id HAVING COUNT(*) > :threshold;
• Joining to HZ_PARTIES to enrich contact records with party name and type for marketing dashboards.
• Joining to AMS_MEDIA_B to analyze channel effectiveness across fatiguing schedules.
• Reconciling contacts against AMS_CAMPAIGN_SCHEDULES_B to validate schedule execution.

Related Objects

• HZ_PARTIES — joined on AMS_TCOP_CONTACTS.PARTY_ID to resolve the contacted party.
• AMS_CAMPAIGN_SCHEDULES_B — joined on SCHEDULE_ID, the driving schedule definition.
• AMS_MEDIA_B — joined on MEDIA_ID, the delivery channel.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for data access control.
• AMS_CAMPAIGN_SCHEDULES_TL — the translatable companion to the schedule base table, useful for reporting schedule names.

Together these relationships place AMS_TCOP_CONTACTS at the operational center of Oracle Marketing's contact-frequency enforcement logic.