Search Results ams_list_entry_usages_pk

Overview

The AMS_LIST_ENTRY_USAGES table is a core data object within the Oracle E-Business Suite Marketing (AMS) module. It functions as a junction table that records the critical assignment relationships between marketing list entries, campaigns, and sales personnel. Its primary role is to track which specific contacts or accounts (list entries) from a marketing list are actively assigned to a particular promotion (campaign) and are the responsibility of a designated salesperson. This enables precise targeting, campaign execution tracking, and performance measurement for marketing initiatives in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's structure centers on foreign key relationships that define these assignments. The primary identifier is the system-generated LIST_ENTRY_USAGE_ID. The three most significant columns are LIST_HEADER_ID, which identifies the parent marketing list; LIST_ENTRY_ID, which identifies the specific contact or account within that list; and CAMPAIGN_ID, which identifies the promotion to which the entry is assigned. While the provided metadata does not explicitly name a salesperson column, the table's documented description confirms it stores assignment to a sales person, implying the existence of a resource or party identifier column. The unique key constraint (AMS_LIST_ENTRY_USAGES_UK1) on these three columns ensures a list entry cannot be assigned to the same campaign from the same list more than once.

Common Use Cases and Queries

A primary use case is generating a target list for a campaign team, showing which sales reps are responsible for which prospects. This is crucial for outbound telemarketing or direct sales campaigns. Another common scenario is auditing list usage to prevent contact fatigue, ensuring the same list entry is not over-solicited across multiple concurrent campaigns. A typical reporting query would join this table to campaign and list entry details:

• SELECT c.name campaign_name, lh.name list_name, le.party_id, le.list_entry_id FROM ams_list_entry_usages u, ams_campaigns_all_b c, ams_list_entries le WHERE u.campaign_id = c.campaign_id AND u.list_entry_id = le.list_entry_id AND u.list_header_id = le.list_header_id AND c.campaign_id = :p_campaign_id;

Data in this table is typically created and managed through the Marketing application's user interface when assigning list entries to campaigns, not via direct SQL manipulation.

Related Objects

The table maintains defined foreign key relationships with two primary tables, as documented in the ETRM metadata:

• AMS_LIST_ENTRIES: This relationship is dual-keyed. The AMS_LIST_ENTRY_USAGES table joins to AMS_LIST_ENTRIES on both LIST_HEADER_ID and LIST_ENTRY_ID to retrieve the detailed contact or account information (PARTY_ID) for the assigned entry.
• AMS_CAMPAIGNS_ALL_B: The table joins to AMS_CAMPAIGNS_ALL_B on CAMPAIGN_ID to obtain promotion details such as name, status, and dates.

As a junction table, it is central to queries involving campaign targeting and list management. The primary key constraint AMS_LIST_ENTRY_USAGES_PK and unique key constraint AMS_LIST_ENTRY_USAGES_UK1 enforce critical data integrity for these relationships.