Search Results hz_dup_sets




Overview
Explore in AI Chat

Overview

HZ_DUP_SETS is a Receivables (AR) schema table that stores Duplicate Party Sets — grouped collections of potential duplicate party records identified by the Oracle Trading Community Architecture (TCA) duplicate identification and merge process. In Oracle EBS 12.1.1 and 12.2.2, this table functions as a working container for a "duplicate set," holding the winning (surviving) party for a batch, the batch it belongs to, the processing status, and the assignment of the set to a specific user for review.

Based on the foreign key structure, the Data Vault classification for this table is heuristic satellite-leaning: it captures descriptive, mutable context (status, assignment, merge type) around a duplicate resolution batch rather than acting as a pure hub or link. It references FND_USER via ASSIGNED_TO_USER_ID, tying duplicate review work to an application user. The table is owned by AR and is marked VALID in the documented 12.2.2 schema.

Key Information Stored

The table contains 13 documented columns. The most significant are:

  • DUP_SET_ID — the unique identifier for a duplicate set. This is the single-column unique index HZ_DUP_SETS_U1, making it the primary business-key candidate for the set.
  • DUP_BATCH_ID — the batch under which the duplicate set was generated, grouping sets created in a single duplicate identification run.
  • WINNER_PARTY_ID — the party record designated to survive a merge for this set.
  • STATUS — the processing state of the set (for example, pending review, merged, or rejected).
  • ASSIGNED_TO_USER_ID — the FND_USER to whom the set is assigned for review, forming the documented foreign key HZ_DUP_SETS.ASSIGNED_TO_USER_ID → FND_USER.
  • MERGE_TYPE — the type of merge action associated with the set.
  • REQUEST_ID — the concurrent request that created the set, enabling traceability to the duplicate identification program.
  • OBJECT_VERSION_NUMBER — the optimistic locking version, supporting concurrent updates in OAF-based TCA screens.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the WHO audit columns tracking creation and modification.

Common Use Cases and Queries

Typical uses include auditing duplicate batches, tracking reviewer workload, and reconciling merges against their originating concurrent request. A common pattern lists pending sets with their assigned user and winner party:

  • SELECT s.DUP_SET_ID, s.DUP_BATCH_ID, s.WINNER_PARTY_ID, s.STATUS, u.USER_NAME FROM HZ_DUP_SETS s, FND_USER u WHERE s.ASSIGNED_TO_USER_ID = u.USER_ID AND s.STATUS = 'PENDING';
  • SELECT DUP_BATCH_ID, COUNT(*) FROM HZ_DUP_SETS GROUP BY DUP_BATCH_ID ORDER BY 1;
  • SELECT DUP_SET_ID, STATUS, REQUEST_ID, LAST_UPDATE_DATE FROM HZ_DUP_SETS WHERE REQUEST_ID = :request_id;

These queries support duplicate-resolution reporting, workload balancing across reviewers, and post-merge audits.

Related Objects

  • FND_USER — joined on HZ_DUP_SETS.ASSIGNED_TO_USER_ID = FND_USER.USER_ID; the only documented foreign key.
  • HZ_PARTIES — the party identified by WINNER_PARTY_ID.
  • HZ_DUP_BATCHES — the batch referenced by DUP_BATCH_ID.
  • HZ_MERGE_PARTIES — merge detail records for the duplicated parties in a set.
  • HZ_PARTY_MERGE_HISTORY — historical audit of completed merges.
  • HZ_PARTY_SITES / HZ_LOCATIONS — downstream party data consolidated during merge.