Search Results no_of_rows_duplicates




Overview
Explore in AI Chat

Overview

AMS_LIST_SELECT_ACTIONS is a Marketing (AMS) module table in the Oracle E-Business Suite database, owned by the AMS schema and present in both 12.1.1 and 12.2.2. It stores the set of Discoverer workbooks, target segments, and existing lists that are used to generate the entries of a marketing list. Each row represents one selection action — a building block that determines which customer or prospect records are drawn into a list header. Because list generation in the AMS module is composed from multiple selection steps (segment queries, spreadsheet extracts, previously built lists, cell-level filters, and data-mining scores), this table acts as the junction that records which object, of which type, was used in which order for each list.

The heuristic Data Vault classification for this object is link. This is a sensible modeling suggestion: the table contains few descriptive attributes of its own and is dominated by foreign key references to AMS_LIST_HEADERS_ALL and to the various inclusion objects (INCL_OBJECT_ID), making it primarily a relationship record rather than a hub or a satellite.

Key Information Stored

The surrogate primary key is LIST_SELECT_ACTION_ID, enforced by AMS_LIST_SELECT_ACTIONS_PK and reinforced by unique index AMS_LIST_SELECT_ACTIONS_U1 (LIST_SELECT_ACTION_ID, ZD_EDITION_NAME). Business-key candidates are defined by two additional unique constraints: AMS_LIST_SELECT_ACTIONS_UK1 (LIST_HEADER_ID, INCL_OBJECT_NAME, ARC_INCL_OBJECT_FROM) and AMS_LIST_SELECT_ACTIONS_UK2 (LIST_HEADER_ID, ORDER_NUMBER). The most operationally significant columns are:

  • LIST_SELECT_ACTION_ID — surrogate primary key for the action row.
  • LIST_HEADER_ID — the parent marketing list to which the action belongs.
  • ORDER_NUMBER — sequence in which actions are executed during list generation.
  • LIST_ACTION_TYPE — classifies the selection action (e.g., workbook, segment, prior list).
  • INCL_OBJECT_ID — identifies the specific object being included; this is the polymorphic FK column.
  • INCL_OBJECT_NAME — the descriptive name of the inclusion object, part of the business key.
  • ARC_INCL_OBJECT_FROM — the archived/original source type of the included object, also part of the business key.
  • INCL_OBJECT_WB_SHEET, INCL_OBJECT_WB_OWNER — sheet and owner context for Discoverer workbook inclusions.
  • INCL_OBJECT_CELL_CODE — cell-level qualifier when the inclusion references a cell.
  • ACTION_USED_BY_ID — identifies the model or score that drove the action.
  • RANK — ordering or priority rank of the selection result.
  • NO_OF_ROWS_AVAILABLE, NO_OF_ROWS_REQUESTED, NO_OF_ROWS_USED — volume metrics that trace yield from the source through to the final list.
  • NO_OF_ROWS_TARGETED, NO_OF_ROWS_DUPLICATES, RUNNING_TOTAL, DELTA — audit figures for targeting, deduplication, and cumulative counts.
  • DISTRIBUTION_PCT and RESULT_TEXT — allocation percentage and textual outcome of the action.
  • SECURITY_GROUP_ID — the operating unit context enforced against FND_SECURITY_GROUPS.

Standard Who columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) and the DESCRIPTION column complete the 32-column physical schema.

Common Use Cases and Queries

The most frequent reporting need is to reconstruct how a given marketing list was populated. The following pattern lists the ordered actions for a list:

  • SELECT order_number, list_action_type, incl_object_name, no_of_rows_requested, no_of_rows_used FROM ams_list_select_actions WHERE list_header_id = :p_list_id ORDER BY order_number;

Analysts also measure yield and drop-off per action by comparing NO_OF_ROWS_AVAILABLE, NO_OF_ROWS_REQUESTED, NO_OF_ROWS_USED, and NO_OF_ROWS_DUPLICATES. Counting the entry rows produced by each action requires joining to AMS_LIST_ENTRIES on LIST_SELECT_ACTION_ID:

  • SELECT a.list_select_action_id, a.incl_object_name, COUNT(e.list_entry_id) FROM ams_list_select_actions a LEFT JOIN ams_list_entries e ON e.list_select_action_id = a.list_select_action_id WHERE a.list_header_id = :p_list_id GROUP BY a.list_select_action_id, a.incl_object_name;

A third common scenario identifies every list that uses a particular segment, query, or workbook — useful for impact analysis before modifying a query:

  • SELECT list_header_id, order_number, list_action_type FROM ams_list_select_actions WHERE incl_object_id = :p_query_id;

Related Objects

Because INCL_OBJECT_ID is polymorphic, the table references several candidate parent objects. The documented foreign keys and their join columns are:

  • AMS_LIST_HEADERS_ALL — joined on ACTION_USED_BY_ID and on INCL_OBJECT_ID; the primary parent for the list itself.
  • AMS_CELLS_ALL_B — joined on INCL_OBJECT_ID; supports cell-level inclusions.
  • AMS_LIST_QUERIES_ALL — joined on INCL_OBJECT_ID; the target-segment query definition.
  • AMS_DM_MODELS_ALL_B and AMS_DM_SCORES_ALL_B — both joined on ACTION_USED_BY_ID; record the data-mining model or score that generated the action.
  • AMS_IMP_LIST_HEADERS_ALL — joined on INCL_OBJECT_ID; prior imported lists reused as sources.
  • AMS_DISCOVERER_SQL — joined on INCL_OBJECT_ID; the Discoverer workbook SQL backing an action.
  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; enforces org-level access.
  • AMS_LIST_ENTRIES and AMS_LIST_TMP_ENTRIES — both reference this table via LIST_SELECT_ACTION_ID; these are the child tables holding the actual generated entries and their temporary working set.

Together these relationships make AMS_LIST_SELECT_ACTIONS the central link between a list header and the diverse upstream selection sources, and the anchor point for entry-level and yield reporting.