Results for “ams_list_entries”

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

Overview

AMS_LIST_ENTRIES is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the target prospects generated for a marketing list. Each row represents a single list entry — a party, contact point, or prospect selected by Oracle Marketing's list generation engine for use in mailshots, telemarketing campaigns, and related outbound activities. The table is populated programmatically by the list generation engine rather than through direct user maintenance, and it functions as the operational backbone of list-based marketing execution.

From a data modeling perspective, the heuristic Data Vault classification for this table is satellite-leaning. The table carries the descriptive and volatile attributes of a list membership — deduplication flags, random assignment markers, contact method pointers, telemarketing outcome fields, and usage restriction data — rather than acting as a pure hub or link. Its composite primary key (AMS_LIST_ENTRIES_PK on LIST_HEADER_ID and LIST_ENTRY_ID) anchors membership within a list, while the numerous foreign keys to HZ_PARTIES, HZ_CONTACT_POINTS, and HZ_LOCATIONS retain strong dimensional relationships to the Trading Community Architecture (TCA) model.

Key Information Stored

The table is exceptionally wide, with 423 documented columns spanning identifier, contact, telemarketing, deduplication, and custom attribute categories. The most significant columns include:

• LIST_HEADER_ID — part of the composite primary key; identifies the parent marketing list in AMS_LIST_HEADERS_ALL.
• LIST_ENTRY_ID — part of the composite primary key; the surrogate identifier for the individual entry. It is also documented as the single-column unique index AMS_LIST_ENTRIES_U1.
• PARTY_ID — foreign key to HZ_PARTIES; the prospect or customer designated as the target of the entry.
• CONTACT_POINT_ID — foreign key to HZ_CONTACT_POINTS; the specific phone, email, or other contact mechanism selected for outreach.
• LOCATION_ID — foreign key to HZ_LOCATIONS; the address associated with the entry.
• CUSTOMER_ID / PARENT_PARTY_ID — additional party context supporting hierarchical customer views.
• SOURCE_CODE / LIST_SOURCE / LIST_SELECT_ACTION_ID — trace the origin of the entry and the selection action that produced it.
• DEDUPE_KEY / MARKED_AS_DUPLICATE_FLAG / DUPLICATE_MASTER_ENTRY_ID — support the list engine's deduplication logic.
• RANDOMLY_GENERATED_NUMBER / MARKED_AS_RANDOM_FLAG / GROUP_CODE / PART_OF_CONTROL_GROUP_FLAG — support random sampling and control-group construction for test campaigns.
• ENABLED_FLAG / EXCLUDE_IN_TRIGGERED_LIST_FLAG / DO_NOT_USE_FLAG / MARKED_AS_SUPPRESSED_FLAG / MARKED_AS_FATIGUED_FLAG — control whether an entry is eligible for outreach.
• LEAD_ID — foreign key to AS_LEADS_ALL, linking an entry to a sales lead where applicable.
• EVENT_OFFER_ID — foreign key to AMS_EVENT_OFFERS_ALL_B, tying the entry to a specific offer.
• OUTCOME_ID / RESULT_ID / REASON_ID / NEXT_CALL_TIME / LAST_CONTACTED_DATE — capture telemarketing and campaign outcome history.
• CUSTOM_COLUMN1 … CUSTOM_COLUMN25 — extensibility columns for customer-defined segmentation attributes.

The presence of both surrogate keys (LIST_ENTRY_ID) and business keys (LIST_HEADER_ID plus party/contact context) allows flexible querying, though LIST_ENTRY_ID is the documented unique index candidate.

Common Use Cases and Queries

Typical reporting and integration scenarios query AMS_LIST_ENTRIES to produce campaign call lists, mail merge extracts, deduplication audits, and telemarketing dashboards. Common SQL patterns include:

• Retrieving all entries for a list: SELECT * FROM AMS.AMS_LIST_ENTRIES WHERE LIST_HEADER_ID = :p_list_header_id.
• Joining to party and contact data: SELECT le.LIST_ENTRY_ID, hp.PARTY_NAME, hcp.EMAIL_ADDRESS FROM AMS.AMS_LIST_ENTRIES le, HZ_PARTIES hp, HZ_CONTACT_POINTS hcp WHERE le.PARTY_ID = hp.PARTY_ID(+) AND le.CONTACT_POINT_ID = hcp.CONTACT_POINT_ID(+).
• Filtering out suppressed or duplicate entries before a mailshot: WHERE ENABLED_FLAG = 'Y' AND NVL(MARKED_AS_SUPPRESSED_FLAG,'N') = 'N' AND NVL(MARKED_AS_DUPLICATE_FLAG,'N') = 'N'.
• Auditing list generation outcomes: aggregating counts by SOURCE_CODE or LIST_SELECT_ACTION_ID to validate the selection engine's behavior.

Related Objects

The table participates in a dense set of foreign key relationships with TCA and Marketing objects:

• AMS_LIST_HEADERS_ALL — parent list definition; joined via LIST_HEADER_ID.
• HZ_PARTIES — the prospect or customer; joined via PARTY_ID.
• HZ_CONTACT_POINTS — the contact method; joined via CONTACT_POINT_ID.
• HZ_LOCATIONS — the address; joined via LOCATION_ID.
• AMS_LIST_SELECT_ACTIONS — the selection action that generated the entry; joined via LIST_SELECT_ACTION_ID.
• AMS_EVENT_OFFERS_ALL_B — the offer associated with the entry; joined via EVENT_OFFER_ID.
• AS_LEADS_ALL — the originating sales lead; joined via LEAD_ID.
• AMS_LIST_ENTRY_USAGES — child table tracking usage of each entry; joined via LIST_HEADER_ID and LIST_ENTRY_ID.
• FND_SECURITY_GROUPS — security grouping for multi-org access; joined via SECURITY_GROUP_ID.

Together these relationships establish AMS_LIST_ENTRIES as the central convergence point between Marketing campaign definitions, TCA party data, and downstream telemarketing and mail execution processes in Oracle EBS 12.1.1 and 12.2.2.