Search Results ra_customer_merges

Overview

The RA_CUSTOMER_MERGES table is a core transactional table within Oracle E-Business Suite Receivables (AR) that serves as the central repository for tracking and executing customer data consolidation activities. Its primary role is to store detailed information about customers, addresses, and site uses that are being merged or moved as part of master data management processes. This table is critical for maintaining data integrity during these operations, ensuring that transactional history is correctly preserved and associated with the surviving customer record. It functions as the system of record for the merge process, enabling both the execution and auditability of customer data rationalization in Oracle EBS versions 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to capture the relationship between the original (duplicate) records and the surviving (customer) records across different entity levels. The primary key is CUSTOMER_MERGE_ID, which uniquely identifies each merge operation record. The most significant columns define these relationships: CUSTOMER_ID and DUPLICATE_ID for the customer account level, CUSTOMER_ADDRESS_ID and DUPLICATE_ADDRESS_ID for the address (site) level, and CUSTOMER_SITE_ID and DUPLICATE_SITE_ID for the specific site use level. Additional columns typically track the process status, request identifier, creation date, and the last update date, providing a complete audit trail for the merge job's lifecycle.

Common Use Cases and Queries

The primary use case is the execution and monitoring of customer merges initiated via the "Merge Customers" or "Merge Parties" standard forms in AR. Administrators query this table to verify the status and outcome of merge requests, troubleshoot failed processes, and generate audit reports. A common reporting query involves joining to the HZ_CUST_ACCOUNTS table to display the names of the duplicate and surviving customers. For example:

• SELECT rcm.customer_merge_id, hca1.account_number AS surviving_cust_num, hca2.account_number AS duplicate_cust_num, rcm.process_flag, rcm.request_id FROM ra_customer_merges rcm, hz_cust_accounts hca1, hz_cust_accounts hca2 WHERE rcm.customer_id = hca1.cust_account_id AND rcm.duplicate_id = hca2.cust_account_id AND rcm.process_flag = 'ERROR';

This query helps identify merge records that failed processing, which is crucial for data cleanup and re-submission of jobs.

Related Objects

RA_CUSTOMER_MERGES has defined foreign key relationships with several key Trading Community Architecture (TCA) tables, as documented in the ETRM metadata. These relationships enforce referential integrity and define the core entities involved in a merge operation:

• HZ_CUST_ACCOUNTS: Linked via RA_CUSTOMER_MERGES.CUSTOMER_ID. This identifies the surviving customer account.
• HZ_CUST_ACCT_SITES_ALL: Linked via two columns: RA_CUSTOMER_MERGES.CUSTOMER_ADDRESS_ID (surviving address) and RA_CUSTOMER_MERGES.DUPLICATE_ADDRESS_ID (duplicate address).
• HZ_CUST_SITE_USES_ALL: Linked via three columns: RA_CUSTOMER_MERGES.CUSTOMER_SITE_ID (surviving site use), RA_CUSTOMER_MERGES.DUPLICATE_SITE_ID (duplicate site use), and again via CUSTOMER_SITE_ID as noted in the documentation.

These relationships are fundamental, as the merge process updates foreign keys in transactional tables across Oracle EBS (e.g., in RA_CUSTOMER_TRX_ALL) to point from the duplicate TCA entities to the surviving ones, with RA_CUSTOMER_MERGES recording the mapping.