Search Results resource_busy

Overview

The APPS.AHL_PARTY_MERGE_PKG package body provides the Advanced Service (AHL) implementation of Oracle E-Business Suite's Trading Community Architecture (TCA) party merge framework. Its business purpose is to merge duplicate parties — customers, suppliers, contacts, and other trading community entities — within the Advanced Service online tables. When the Oracle Customer Data Management (CDM) or TCA party merge process consolidates two or more party records into a single surviving party, each registered product schema must update its own foreign key references. This package serves that role for Advanced Service, repointing the *_PARTY_ID columns in the AHL tables from the merged ("from") party to the surviving ("to") party.

The package header comment block identifies the primary Advanced Service tables targeted for party merge, and the package carries maintenance history dating to 2001, including subsequent additions for Resource Manager (RM) entities and an OSP (Customer) routine. It is classified under Owner APPS with an API classification of OTHER, and is referenced by zero other packages, indicating it is invoked directly by the merge infrastructure rather than by peer PL/SQL code.

Key Procedures and Functions

The package exposes twelve documented procedures. All are party merge handler routines following the standard TCA merge callback signature, returning success or failure status to the calling merge framework.

• AHL_DI_SOURCE_PARTY — Handles merge of the source party on document header records (AHL_DOCUMENTS_B.SOURCE_PARTY_ID). This is the routine whose illustrative source excerpt declares RESOURCE_BUSY as a user-defined exception bound to Oracle error -0054 via PRAGMA EXCEPTION_INIT, and which uses an explicit cursor with FOR UPDATE NOWAIT to lock affected rows before merging.
• AHL_DI_SUPPLIER — Merges the supplier party reference on supplier document records.
• AHL_DI_RECIPIENT_PARTY — Merges the recipient party reference on recipient document records.
• AHL_DI_REQUESTED_BY_PARTY — Repoints the "requested by" party on document revision records.
• AHL_DI_SUBSCRIBED_FRM_PARTY — Consolidates the "subscribed from" party on subscription records.
• AHL_DI_APPROVED_BY_PARTY — Repoints the "approved by" party on the relevant document entity.
• AHL_DI_RECEIVED_BY_PARTY — Repoints the "received by" party on the relevant document entity.
• AHL_RM_OPER_OPERATOR_PARTY — Merges the operator party on Resource Manager operation records.
• AHL_RM_OPER_H_OPERATOR_PARTY — Merges the operator party on the Resource Manager operation history table.
• AHL_RM_ROUTE_OPERATOR_PARTY — Merges the operator party on Resource Manager route records.
• AHL_RM_ROUTE_H_OPERATOR_PARTY — Merges the operator party on the Resource Manager route history table.
• AHL_OSP_CUSTOMER — Merges the customer party on the OSP order entity; added in April 2003.

Tables Accessed

The package reads and updates party references across the following documented Advanced Service tables, all accessed through APPS synonyms:

• AHL_DOCUMENTS_B, AHL_DOC_REVISIONS_B, AHL_DOC_REVISION_COPIES — document header, revision, and revision copy records.
• AHL_SUBSCRIBER_DOCUMENTS (supplier), AHL_RECIPIENT_DOCUMENTS, AHL_SUBSCRIPTIONS_B — document distribution and subscription records.
• AHL_ROUTES_B — route definitions.
• AHL_OSP_ORDERS_B — OSP order header records.
• HZ_MERGE_BATCH — the TCA merge batch registry, consulted to obtain the merge reason code.

The header comment additionally lists AHL_OPERATIONS_B, AHL_OPERATIONS_H_B, AHL_ROUTES_H_B, and the recipient supplier document tables as merge-relevant entities.

Usage Notes

This package is not intended for direct invocation by end users or custom applications. It is registered with the TCA party merge framework and is called automatically during execution of party merge batches, typically run through the Customer Data Management concurrent programs (for example, Party Merge Batch execution). The package is also indirectly reachable from Oracle Service forms when a user initiates a party merge from the customer or contact interface.

Because the routines acquire row locks with FOR UPDATE NOWAIT and raise RESOURCE_BUSY (-0054) when a row is already locked, concurrent maintenance activity on Advanced Service documents during a merge will cause the merge to fail with a resource-busy condition; such failures must be resolved by retrying during a quiet processing window. All routines observe the standard FND_API return status convention, and errors are traced through ARP_MESSAGE.SET_LINE. Callers should ensure the APPS schema grants and FND_GLOBAL initialization (user ID, login ID) are in place, since the package references FND_GLOBAL.USER_ID and FND_GLOBAL.LOGIN_ID at package initialization.