Results for “ams_list_rule_fields”

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

Overview

AMS_LIST_RULE_FIELDS is a Marketing (AMS) module table in Oracle E-Business Suite, present and valid in both release 12.1.1 and 12.2.2. It stores the individual field-level definitions used by a deduplication rule to determine whether two records represent the same entity — typically a contact, lead, prospect, or customer. Each row associates one field (a table name plus a column name) with one list rule, and may carry additional matching parameters such as weighting, substring length, sequence, and word-replacement behavior. In effect, the table is the rule's field manifest: the rule itself lives in AMS_LIST_RULES_ALL, and this table enumerates precisely which columns that rule inspects and how it treats them.

The supplied metadata carries a heuristic Data Vault classification of link, mined from the foreign-key structure. This is a reasonable modeling suggestion: the table predominantly resolves many-to-many relationships between rules, source fields, and security groups rather than acting as a standalone descriptive hub or a pure satellite of changing attributes. The presence of descriptive columns (SUBSTRING_LENGTH, WEIGHTAGE, and similar) means it behaves partly as a link-satellite hybrid, so any Data Vault design should treat it as a link carrying its own attributes.

Key Information Stored

The surrogate primary key, enforced by AMS_LIST_RULE_FIELDS_PK, is LIST_RULE_FIELD_ID. Two unique indexes identify business-key candidates: AMS_LIST_RULE_FIELDS_UK1 spans FIELD_COLUMN_NAME and FIELD_TABLE_NAME together with LIST_RULE_ID, and the 12.2.2 edition-aware index AMS_LIST_RULE_FIELDS_U2 spans LIST_RULE_ID, FIELD_TABLE_NAME, FIELD_COLUMN_NAME, and ZD_EDITION_NAME. These constraints confirm that a given rule may reference a given field only once per edition — a natural safeguard against duplicated matching criteria.

• LIST_RULE_ID — foreign key to AMS_LIST_RULES_ALL; identifies the deduplication rule that owns this field entry.
• FIELD_TABLE_NAME / FIELD_COLUMN_NAME — the physical table and column the rule evaluates; jointly referenced by AMS_LIST_FIELDS_B, which supplies the permitted field catalog.
• LIST_SOURCE_FIELD_ID — foreign key to AMS_LIST_SRC_FIELDS; links the entry to a higher-level source field definition.
• SUBSTRING_LENGTH — the number of leading characters to compare, supporting partial or truncated matching.
• WEIGHTAGE — a scoring weight used when the rule aggregates multiple field comparisons into a single similarity score.
• SEQUENCE_NUMBER — the evaluation order of fields within the rule.
• WORD_REPLACEMENT_CODE — indicates whether word substitution or normalization is applied before comparison.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enabling multi-tenant or organizational row-level security.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the AMS framework.
• ZD_EDITION_NAME — editioning discriminator added in the 12.2 Online Patching architecture.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN record who created and last modified the row.

Common Use Cases and Queries

The most common requirement is to reconstruct the full set of matching criteria for a given rule. Joining this table to the rule header and to the permitted-field catalog produces a readable rule definition:

• List all fields for one rule: SELECT FIELD_TABLE_NAME, FIELD_COLUMN_NAME, SUBSTRING_LENGTH, WEIGHTAGE, SEQUENCE_NUMBER FROM AMS_LIST_RULE_FIELDS WHERE LIST_RULE_ID = :p_rule_id ORDER BY SEQUENCE_NUMBER;
• Trace which rules use a specific column: filter on FIELD_TABLE_NAME and FIELD_COLUMN_NAME to find every rule that matches against HZ_PARTIES or HZ_CUST_ACCOUNTS columns.
• Diagnose deduplication behavior when merges are missed or over-triggered, by reviewing weighting and substring settings.
• Impact analysis before altering a source column: query AMS_LIST_FIELDS_B and this table to confirm whether any active rule depends on it.
• Security and edition auditing: group rows by SECURITY_GROUP_ID and ZD_EDITION_NAME to confirm isolation and edition coverage.

Related Objects

• AMS_LIST_RULES_ALL — parent rule table; joined on LIST_RULE_ID.
• AMS_LIST_FIELDS_B — permitted field catalog; joined through FIELD_TABLE_NAME and FIELD_COLUMN_NAME.
• AMS_LIST_SRC_FIELDS — source field definitions; joined on LIST_SOURCE_FIELD_ID.
• FND_SECURITY_GROUPS — security group validation; joined on SECURITY_GROUP_ID.
• AMS_LIST_RULE_FIELDS_PK / UK1 / U1 / U2 — indexes used by the AMS concurrency and editioning framework.
• HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CONTACT_POINTS — the practical source tables most often named in FIELD_TABLE_NAME for deduplication.