Search Results ams_list_entry_usages_n1

Overview

The AMS_LIST_ENTRY_USAGES table is a core transactional entity within the Oracle E-Business Suite Marketing (AMS) module, specifically for versions 12.1.1 and 12.2.2. It functions as a critical junction table, establishing and managing the operational relationship between marketing campaigns (promotions) and the target list entries (prospects or customers). Its primary role is to track the assignment, status, and lifecycle of individual list entries as they are utilized within specific promotional campaigns. This table was introduced to facilitate the technical upgrade path from Oracle Sales & Marketing (OSM) to the integrated Oracle Marketing application, ensuring data continuity and relationship integrity.

Key Information Stored

The table stores metadata that governs the execution of campaign list usage. Key columns include the unique identifier (LIST_ENTRY_USAGE_ID), the foreign keys linking to the campaign (CAMPAIGN_ID), list header (LIST_HEADER_ID), and specific list entry (LIST_ENTRY_ID). The ASSIGNED_PERSON_ID column identifies the resource responsible for the entry within the campaign context. Operational status is managed via the STATUS column, while the DUE_DATE and DUE_TIME fields control the entry's expiration for the promotion. The table includes standard Oracle EBS "Who" columns (CREATED_BY, LAST_UPDATE_DATE, etc.) for auditing, an OBJECT_VERSION_NUMBER for optimistic locking, and user-defined fields (USER_DEFINED1-3) for extensibility.

Common Use Cases and Queries

This table is central to campaign execution reporting and operational audits. Common use cases include analyzing list penetration for a specific campaign, tracking the assignment of leads or contacts to sales resources, and monitoring the status of all list entries in an active promotion. A typical query to retrieve all active list entries for a given campaign, including the assigned person, would be:

• SELECT aleu.list_entry_id, aleu.assigned_person_id, aleu.status, aleu.due_date, aca.campaign_name FROM ams.ams_list_entry_usages aleu JOIN ams.ams_campaigns_all aca ON aleu.campaign_id = aca.campaign_id WHERE aleu.campaign_id = :p_campaign_id AND aleu.status = 'ACTIVE';

Another critical reporting pattern involves joining to AMS_LIST_ENTRIES to get the contact details for all entries used in a campaign, enabling performance analysis and follow-up task management.

Related Objects

AMS_LIST_ENTRY_USAGES has direct, mandatory relationships with several key AMS tables, forming the core data model for campaign list management. It is primarily linked to AMS_CAMPAIGNS_ALL via the CAMPAIGN_ID column and to AMS_LIST_ENTRIES via the LIST_ENTRY_ID column. The LIST_HEADER_ID column is a foreign key to AMS_LIST_HEADERS_ALL. The table is indexed for performance on the unique combination of campaign, list header, and list entry (AMS_LIST_ENTRY_USAGES_U1), and on the ASSIGNED_PERSON_ID (AMS_LIST_ENTRY_USAGES_N1) for quick lookups of a resource's assigned entries. Its existence is a direct result of the OSM to Oracle Marketing upgrade path.