Results for “ams_list_rule_usages”

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

Overview

AMS_LIST_RULE_USAGES is a Marketing (AMS) module table in the Oracle E-Business Suite that records the association between deduplication rules and the lists against which those rules are applied. When a list is generated or imported in Oracle Marketing, the deduplication rules configured for that list are stored here, allowing the system to enforce suppression and duplicate-elimination logic consistently at list build time.

In Data Vault modeling terms, the FK structure mined from the metadata suggests this object is best classified as a link table. It resolves a many-to-many relationship between list headers (AMS_LIST_HEADERS_ALL) and deduplication rules (AMS_LIST_RULES_ALL), with the natural business key established by the combination of LIST_HEADER_ID and LIST_RULE_ID.

Key Information Stored

The table contains 13 documented columns in the AMS schema. The most significant columns are:

• LIST_RULE_USAGE_ID — The surrogate primary key (AMS_LIST_RULE_USAGES_PK) and the value most commonly searched by users. It uniquely identifies each rule-usage assignment within a list.
• LIST_HEADER_ID — Foreign key to AMS_LIST_HEADERS_ALL. Identifies the list to which the deduplication rule applies.
• LIST_RULE_ID — Foreign key to AMS_LIST_RULES_ALL. Identifies the specific deduplication rule used against the list.
• ACTIVE_FROM_DATE / ACTIVE_TO_DATE — Date range during which the rule assignment is effective for the list.
• PRIORITY — Determines the order in which multiple deduplication rules are evaluated.
• SECURITY_GROUP_ID — Multi-tenant security group associated with the row, joining to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the framework for concurrent updates.
• Standard audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.

Two unique indexes are documented. AMS_LIST_RULE_USAGES_U1 covers LIST_RULE_USAGE_ID (reinforcing the surrogate PK), while AMS_LIST_RULE_USAGES_U2 covers the business-key candidate pair (LIST_HEADER_ID, LIST_RULE_ID), preventing duplicate rule assignments to the same list.

Common Use Cases and Queries

A frequent operational query is to retrieve every rule assigned to a given list, ordered by evaluation priority:

• SELECT RULE_USAGE.LIST_RULE_USAGE_ID, RULE_USAGE.LIST_RULE_ID, RULE_USAGE.PRIORITY FROM AMS.AMS_LIST_RULE_USAGES RULE_USAGE WHERE RULE_USAGE.LIST_HEADER_ID = :P_LIST_HEADER_ID ORDER BY RULE_USAGE.PRIORITY;

Administrators also audit whether a specific rule is reused across multiple lists:

• SELECT LIST_HEADER_ID, PRIORITY FROM AMS.AMS_LIST_RULE_USAGES WHERE LIST_RULE_ID = :P_LIST_RULE_ID;

Reporting scenarios commonly join this table to AMS_LIST_HEADERS_ALL and AMS_LIST_RULES_ALL to produce a catalog of deduplication behavior per list, and reconcile active windows using ACTIVE_FROM_DATE and ACTIVE_TO_DATE. Because the table is secured by SECURITY_GROUP_ID, any custom query should filter on the appropriate security group to respect Oracle Marketing's multi-tenant boundaries.

Related Objects

• AMS_LIST_HEADERS_ALL — joined via LIST_HEADER_ID; provides the parent list definition.
• AMS_LIST_RULES_ALL — joined via LIST_RULE_ID; provides the referenced deduplication rule.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; enforces access isolation.
• AMS_LIST_HEADERS_ALL / AMS_LIST_RULES_ALL — also expose related list members, rule attributes, and deduplication metadata consumed during list generation.

Together these objects form the rule-assignment layer used by Oracle Marketing's list management and deduplication engine.