Results for “oe_cust_merges_gtt”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OE_CUST_MERGES_GTT is a Global Temporary Table owned by the ONT (Order Management) schema in Oracle E-Business Suite, present in both release 12.1.1 and 12.2.2. As a GTT, its rows are private to the session or transaction that populates them and are purged automatically according to the table's ON COMMIT clause, so it holds no permanent data. The table exists to support the Customer Merge process, in which duplicated customer records defined in Oracle Receivables are consolidated and the referencing order-management entities are repointed to the surviving (master) customer. During such a merge, Order Management must reconcile open and historical sales orders, order lines, and related fulfillment data against the duplicate being eliminated. OE_CUST_MERGES_GTT acts as a transient staging area that captures the set of customer duplicates relevant to a given merge request, allowing concurrent merge batches to be tracked independently.

Under the heuristic Data Vault classification derived from its foreign-key structure, this object is modeled as a standalone table rather than a hub, link, or satellite. This reflects the fact that it is a temporary work table, not a persistent business entity. In Data Vault terms it functions more like a transient staging construct feeding a link between the customer merge entity and the order objects being reassigned.

Key Information Stored

The table carries five documented columns, all of which serve the merge reconciliation logic:

• CUSTOMER_MERGE_ID — The operational key tying each staged row back to a specific customer merge event. This is documented as a foreign key to RA_CUSTOMER_MERGES, so it is the principle business-key candidate linking the temporary set to the permanent merge record.
• DUPLICATE_ID — Identifies the duplicate customer party/account being merged away, i.e., the record that will be superseded by the master customer.
• REQUEST_ID — The concurrent request identifier for the Customer Merge run, enabling the program to isolate and process only the rows belonging to its own execution.
• SET_NUMBER — A grouping/sequence value that allows large merges to be split into manageable sets processed incrementally.
• PROCESS_FLAG — A status indicator recording whether a staged row has been processed, is pending, or errored, giving the merge program restart-safe control over its work.

Because this is a GTT with no documented primary-key constraint or unique index, there is no enforced surrogate key; the combination of CUSTOMER_MERGE_ID, DUPLICATE_ID, REQUEST_ID, and SET_NUMBER effectively acts as the logical identifier for each staged item.

Common Use Cases and Queries

The primary use case is diagnostic: identifying which duplicate customers a merge request is processing and where it may have stalled. A typical query joins the GTT to its parent merge record:

• SELECT g.customer_merge_id, g.duplicate_id, g.process_flag, m.* FROM oe_cust_merges_gtt g JOIN ra_customer_merges m ON m.customer_merge_id = g.customer_merge_id WHERE g.request_id = :p_request_id;
• Counting unprocessed rows for a request: SELECT process_flag, COUNT(*) FROM oe_cust_merges_gtt WHERE request_id = :p_request_id GROUP BY process_flag;

Support teams use these patterns to confirm that all staged duplicates reached a complete status before the merge is finalized, and to re-drive failed sets. Because the data is session-private and transient, queries must run within the same session or while the originating request holds the rows.

Related Objects

• RA_CUSTOMER_MERGES — the parent merge header; joined on CUSTOMER_MERGE_ID.
• HZ_CUST_ACCOUNTS / HZ_PARTIES — the duplicate and master customer entities referenced by DUPLICATE_ID.
• OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL — sales orders whose customer references are reassigned during the merge.
• FND_CONCURRENT_REQUESTS — the request tracked by REQUEST_ID.
• ONT Customer Merge concurrent program — the process that populates and consumes this table.