Results for “ams_p_list_rules_v”

24 results




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

Overview

AMS_P_LIST_RULES_V is a public Oracle EBS view owned by the APPS schema and delivered as part of the AMS (Marketing) product family. In Oracle EBS 12.1.1 and 12.2.2, its documented status is VALID. The view exposes user-defined rules that Oracle Marketing uses to identify duplicate accounts or contacts within a list, together with the priority assigned to those rules when duplicates are removed.

Unlike transactional marketing tables, this view is primarily a configuration and reference object. List rules govern how the deduplication and list-cleanup process determines which records represent the same account or contact, and which surviving record takes precedence when multiple candidates exist. Reporting and integration users query the view to audit rule configuration, validate which rules are active for a given list, or extract rule definitions into external systems. Because it is a view rather than a table, it carries no independent storage and reflects the current state of its underlying base objects at query time.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through APPS synonyms:

The documented view text is a join between these two objects, filtered so that only the highest rule identifier in use for a given list header is returned:

SELECT R.LIST_RULE_ID, R.LIST_RULE_NAME, R.LIST_RULE_TYPE, RU.LIST_HEADER_ID FROM AMS_LIST_RULES_ALL R, AMS_LIST_RULE_USAGES RU WHERE R.LIST_RULE_ID = RU.LIST_RULE_ID AND RU.LIST_RULE_ID = (SELECT MAX(LIST_RULE_ID) FROM AMS_LIST_RULE_USAGES WHERE LIST_HEADER_ID = RU.LIST_HEADER_ID)

Consequently, the view is not a simple projection of a single table; it applies an inline aggregate subquery against AMS_LIST_RULE_USAGES. The effect is that, for each list header, only the rule usage with the maximum LIST_RULE_ID is surfaced. Rule identities present in AMS_LIST_RULES_ALL but not referenced by a usage row are excluded entirely.

Key Columns

The view exposes four documented columns:

  • LIST_RULE_ID — the unique identifier of the list rule, sourced from AMS_LIST_RULES_ALL and matched against AMS_LIST_RULE_USAGES. It is the join key between the two base objects and the column used in the MAX() filter.
  • LIST_RULE_NAME — the user-defined name of the rule, used to identify the rule in setup and administrative screens.
  • LIST_RULE_TYPE — the classification of the rule, indicating the nature of the duplicate-matching or priority logic it represents.
  • LIST_HEADER_ID — the identifier of the list to which the rule usage applies, taken from AMS_LIST_RULE_USAGES. This column ties the rule back to a specific list header.

Note that no priority value or sequence column is directly exposed among the documented columns; priority is implied by the MAX(LIST_RULE_ID) selection rather than surfaced as a discrete field. Any downstream consumer relying on an explicit priority attribute must derive it or read the base tables directly.

Common Use Cases and Queries

Typical scenarios include auditing which rule is currently associated with each list, reconciling list configuration against rule definitions, and supporting migration or integration extracts of deduplication configuration.

Retrieve all rule associations:

SELECT LIST_HEADER_ID, LIST_RULE_ID, LIST_RULE_NAME, LIST_RULE_TYPE FROM APPS.AMS_P_LIST_RULES_V ORDER BY LIST_HEADER_ID;

Retrieve the rule associated with a specific list:

SELECT LIST_RULE_ID, LIST_RULE_NAME, LIST_RULE_TYPE FROM APPS.AMS_P_LIST_RULES_V WHERE LIST_HEADER_ID = :p_list_header_id;

Count rules by type:

SELECT LIST_RULE_TYPE, COUNT(*) FROM APPS.AMS_P_LIST_RULES_V GROUP BY LIST_RULE_TYPE;

Because the view enforces one row per list header via the MAX(LIST_RULE_ID) subquery, queries against it should not be expected to return multiple rules for the same LIST_HEADER_ID. Where historical or superseded rule usages are required, the base table AMS_LIST_RULE_USAGES must be queried directly. Standard Oracle EBS security applies through the APPS schema and the AMS responsibility grants that accompany Marketing list administration.