Search Results hz_user_overwrite_rules

Overview

The HZ_USER_OVERWRITE_RULES table is an Oracle Receivables (AR) application table that stores user-defined overwrite rules for Third Party Data Integration. In Oracle EBS 12.1.1 and 12.2.2, this table governs whether data received from external (third-party) sources may be overwritten by user-entered values, or conversely, whether the incoming third-party values take precedence during data reconciliation. It functions as a configuration and control mechanism for the Trading Community Architecture (TCA) and Customer Data Hub, ensuring that critical customer, party, or account attribute values are not inadvertently replaced during bulk imports or integration flows.

Under a heuristic Data Vault classification, this object is satellite-leaning. Rather than acting as an independent hub of business entities, it records descriptive, rule-oriented attributes that qualify or modify behaviour of a parent entity attribute. The suggestion is that it be modelled as a satellite (or dependent child) keyed to its parent attribute, capturing rule flags and audit metadata rather than establishing a distinct business key of its own.

Key Information Stored

The table comprises nine documented columns. The most significant are described below.

• RULE_ID — Surrogate identifier for the overwrite rule. Forms part of the composite primary key HZ_USER_OVERWRITE_RULES_PK.
• ENTITY_ATTR_ID — Foreign key to HZ_ENTITY_ATTRIBUTES, identifying the specific TCA entity attribute to which the overwrite rule applies. This is the second component of the composite primary key.
• OVERWRITE_FLAG — The operative control column, indicating whether the user value may overwrite the third-party value (or vice versa) for the referenced attribute.
• ORIG_SYSTEM — Identifies the source system associated with the overwrite rule; part of the business-key candidate index.
• CREATED_BY, CREATION_DATE — Standard WHO audit columns recording the creating user and timestamp.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns capturing the most recent modification user, timestamp, and login session.

The surrogate primary key is the composite HZ_USER_OVERWRITE_RULES_PK (RULE_ID, ENTITY_ATTR_ID). The unique index HZ_USER_OVERWRITE_RULES_U1 (RULE_ID, ENTITY_ATTR_ID, ORIG_SYSTEM) represents the closer business-key candidate, since it extends the key with the originating source system and enforces uniqueness per attribute per source.

Common Use Cases and Queries

Typical scenarios include auditing which attributes are protected from third-party overwrites, troubleshooting why an incoming integration value failed to replace user-entered data, and reporting on source-system precedence configuration.

• Retrieve all overwrite rules for a given attribute using a join to HZ_ENTITY_ATTRIBUTES.
• Report rules by originating system to understand precedence across integrated sources.
• Audit recently created or modified rules using CREATION_DATE and LAST_UPDATE_DATE.

A representative query pattern:

SELECT r.RULE_ID, r.ENTITY_ATTR_ID, r.OVERWRITE_FLAG, r.ORIG_SYSTEM,
       r.LAST_UPDATE_DATE
FROM   HZ_USER_OVERWRITE_RULES r
WHERE  r.ORIG_SYSTEM = :p_orig_system
ORDER  BY r.LAST_UPDATE_DATE DESC;

To identify the attribute context, join ENTITY_ATTR_ID to HZ_ENTITY_ATTRIBUTES and traverse upward to the owning entity definition.

Related Objects

• HZ_ENTITY_ATTRIBUTES — Parent reference table; joined via HZ_USER_OVERWRITE_RULES.ENTITY_ATTR_ID = HZ_ENTITY_ATTRIBUTES.ENTITY_ATTR_ID. This is the sole documented foreign-key relationship.
• HZ_USER_OVERWRITE_RULES_PK — Primary key constraint on (RULE_ID, ENTITY_ATTR_ID).
• HZ_USER_OVERWRITE_RULES_U1 — Unique index on (RULE_ID, ENTITY_ATTR_ID, ORIG_SYSTEM), enforcing source-level uniqueness.
• TCA / Customer Data Hub integration APIs — The Third Party Data Integration processes that read these rules to determine overwrite behaviour during data loads.

Because the documented dependency set is narrow, the primary structural relationship remains the foreign key to HZ_ENTITY_ATTRIBUTES, with the surrounding TCA entity model providing the broader context in which these overwrite rules operate.