Search Results hz_merge_dictionary_u1

Overview

The AR.HZ_MERGE_DICTIONARY table is a seeded reference table within the Oracle E-Business Suite Trading Community Architecture (TCA) and is owned by the AR schema. Its purpose is to store metadata about every table that may be affected when two party records are merged. The party merge concurrent program reads this dictionary to determine which entities participate in a merge, how child entities relate to parent entities via foreign keys, and which PL/SQL or BC4J procedures must be invoked to consolidate the data. In both EBS 12.1.1 and 12.2.2 the table resides in the APPS_TS_SEED tablespace, confirming its role as seeded configuration rather than transactional data. Because rule set values are seeded and cannot be modified or extended by users, the dictionary functions as the authoritative control list for merge processing logic.

From a Data Vault modeling perspective, the mined relationship data classifies this object as hub-leaning. This reflects that MERGE_DICT_ID acts as a durable, non-transactional business key that anchors dependent history and log records, making the table analogous to a hub of dictionary definitions.

Key Information Stored

The surrogate primary key is MERGE_DICT_ID, a sequence-generated identifier. It is also the leading column of the unique index HZ_MERGE_DICTIONARY_U1, which, in the documented 12.2.2 schema, includes ZD_EDITION_NAME as a second column for editioning support. The most significant attributes are:

• RULE_SET_NAME — the seeded rule set name that groups dictionary entries; not user-editable.
• ENTITY_NAME — the entity (table) affected by a party merge; indexed by HZ_MERGE_DICTIONARY_N1.
• PARENT_ENTITY_NAME — the parent entity in the merge hierarchy; indexed by HZ_MERGE_DICTIONARY_N2.
• PK_COLUMN_NAME / FK_COLUMN_NAME — define the primary key of the entity and the foreign key linking it to its parent.
• PROCEDURE_TYPE and PROCEDURE_NAME — identify whether merge logic runs as BC4J or PL/SQL, and name the procedure invoked.
• JOIN_CLAUSE — the WHERE clause used to relate parent and child entities during processing.
• DESC_COLUMN_NAME and DESCRIPTION — support report and log generation by producing readable descriptions of merged records.
• DICT_APPLICATION_ID — associates the entry with its owning application; indexed by HZ_MERGE_DICTIONARY_N3.
• SEQUENCE_NO, BULK_FLAG, BATCH_MERGE_FLAG, VALIDATE_PURGE_FLAG — control processing order and merge behavior.
• FK_DATA_TYPE — describes the datatype of the foreign key column.

Common Use Cases and Queries

The primary use case is execution support for the party merge concurrent program, which queries the dictionary to build its processing sequence. DBAs and developers also query the table directly to understand merge scope before production merges or during troubleshooting.

To list all entities affected by a given rule set:

SELECT entity_name, parent_entity_name, procedure_name
FROM   ar.hz_merge_dictionary
WHERE  rule_set_name = :p_rule_set
ORDER  BY sequence_no;

To trace the parent-child hierarchy:

SELECT child.entity_name, child.fk_column_name,
       child.parent_entity_name, parent.pk_column_name
FROM   ar.hz_merge_dictionary child, ar.hz_merge_dictionary parent
WHERE  child.parent_entity_name = parent.entity_name
AND    child.dict_application_id = parent.dict_application_id;

Reporting queries typically join on DICT_APPLICATION_ID to filter by application and use DESCRIPTION for user-facing labels in merge audit reports.

Related Objects

The most significant dependent objects are the history and log tables that record merge execution against dictionary entries:

• HZ_MERGE_PARTY_HISTORY — references HZ_MERGE_DICTIONARY.MERGE_DICT_ID, capturing historical merge activity per entity.
• HZ_MERGE_PARTY_LOG — references HZ_MERGE_DICTIONARY.MERGE_DICT_ID, logging results of each merge step.
• HZ_PARTIES — the principal entity described by dictionary rows (for example, entity HZ_PARTIES with description "Party").
• The party merge concurrent program, which consumes the dictionary at runtime to drive procedure invocation.
• Additional TCA child entities (party sites, contacts, and relationships) that appear as ENTITY_NAME entries and are processed according to their dictionary procedure assignments.