Search Results hz_customer_merge_log




Overview
Explore in AI Chat

Overview

HZ_CUSTOMER_MERGE_LOG is an Oracle Receivables (AR) transaction table that records the account merge audit history for Oracle E-Business Suite. When two customer accounts (or related entities such as contacts, addresses, and sites) are consolidated through the Customer Merge or Account Merge concurrent programs, this table captures a row-level snapshot of the source records before and after the operation. It is the primary forensic and audit artifact for understanding what changed during a merge, which tables were touched, and how the merged data was transformed.

The table is owned by the AR schema, is marked VALID, and contains 356 columns in the documented ETRM 12.2.2 physical schema. Its structure is deliberately generic: a small set of descriptor columns (merge header, request, source table, primary keys) is followed by a large block of type-prefixed pair columns (NUM_*, VCHAR_*, DATE_*, DEL_*) that hold original and new values for the affected record. Based on the documented relationship data, the object is classified heuristically as standalone in Data Vault terms — it has a single-column surrogate primary key and no documented foreign key dependencies, suggesting it is best modeled as a standalone audit/history satellite rather than a hub or link, with MERGE_LOG_ID acting as the natural hub key candidate for downstream modeling.

Key Information Stored

The primary key is HZ_CUSTOMER_MERGE_LOG_PK, defined on MERGE_LOG_ID, which is the surrogate identifier for each logged merge row. The most operationally significant columns are:

  • MERGE_LOG_ID — surrogate primary key uniquely identifying each audit row.
  • MERGE_HEADER_ID — business-key candidate linking the row back to the parent merge header, grouping all changes for a single merge run.
  • REQUEST_ID — the concurrent request that produced the merge, enabling traceability to the submission log.
  • TABLE_NAME — identifies the source entity table whose row was merged (for example, customer, contact, or site tables).
  • PRIMARY_KEY_ID / PRIMARY_KEY_ID1–3, PRIMARY_KEY5–6 — composite identifiers for the affected record, allowing reconstruction of the original record key.
  • ACTION_FLAG — indicates the action applied (for example, update, delete, or merge).
  • NUM_COL*_ORIG / NUM_COL*_NEW — numeric before/after value pairs for numeric attributes changed by the merge.
  • VCHAR_COL*_ORIG / VCHAR_COL*_NEW — character before/after pairs for descriptive attributes.
  • DATE_COL*_ORIG / DATE_COL*_NEW — date before/after pairs for date-sensitive attributes.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit WHO columns.

The DEL_COL1 through DEL_COL200 columns are reserved deletion/flag slots populated by the merge framework to mark which attributes were deleted or nulled during consolidation. The paired ORIG/NEW convention lets a DBA reconstruct the pre-merge state of any affected record without restoring a backup.

Common Use Cases and Queries

Typical uses include auditing a completed customer merge, troubleshooting why an account lost data, and producing evidence for compliance or reconciliation reviews. A common pattern is to retrieve all rows for a given merge header:

  • SELECT * FROM hz_customer_merge_log WHERE merge_header_id = :header_id;
  • SELECT table_name, action_flag, COUNT(*) FROM hz_customer_merge_log WHERE request_id = :request_id GROUP BY table_name, action_flag;
  • Identifying which attributes changed: compare VCHAR_COL*_ORIG with VCHAR_COL*_NEW for non-null differences.
  • Tracking merge activity over time: SELECT TRUNC(creation_date), COUNT(*) FROM hz_customer_merge_log GROUP BY TRUNC(creation_date) ORDER BY 1;

Because the value columns are generically named, the TABLE_NAME and PRIMARY_KEY_ID columns must be interpreted alongside the merge framework documentation to map each column pair to its real attribute.

Related Objects

As a standalone audit table, HZ_CUSTOMER_MERGE_LOG has no documented foreign keys, but it logically depends on and joins to the following objects:

Oracle does not expose a public API against this table; it is populated internally by the Customer Merge and Account Merge concurrent programs, so reports should treat it as read-only audit data.