Oracle Customer merge api : Sample PLSQL code of Oracle Customer merge api

Oracle Customer merge api

Customer Merge is the option in oracle in which we can merge the one customer to another customer.Use Customer Merge to consolidate any duplicate customers or transfer site use activity from a customer or site that is no longer active or has been taken over by another customer or site. After the merge completes successfully, all activity that was previously associated with the old customer or site is now associated with the new customer or site.  When You merge one customer to Another , System Automatically Transferred all the Receipts ,AR Invoices and other Activities of the Old Customer to this New Customer so the Liability of the customer will be transferred to this New supplier too. In this post , We will be discuss about the API to merge customers in Oracle Apps.

 

 

Sample PLSQL code of Oracle Customer merge api

Declare

l_reqid NUMBER;
l_merge_id NUMBER;
l_merge_header_id NUMBER;
l_customer_id NUMBER;
l_customer_number VARCHAR2(50);
l_customer_ref VARCHAR2(50);
l_duplicate_id NUMBER;
l_duplicate_number VARCHAR2(50);
l_duplicate_ref VARCHAR2(50);

BEGIN

BEGIN
SELECT ra_customer_merges_s.nextval
INTO l_merge_id
FROM DUAL;

SELECT ra_customer_merge_headers_s.nextval
INTO l_merge_header_id
FROM DUAL;

SELECT hca.cust_account_id, hca.account_number, hca.orig_system_reference
INTO l_customer_id, l_customer_number, l_customer_ref
FROM hz_cust_accounts hca
WHERE hca.orig_system_reference = p_customer_number;

SELECT hca.cust_account_id, hca.account_number, hca.orig_system_reference
INTO l_duplicate_id, l_duplicate_number, l_duplicate_ref
FROM hz_cust_accounts hca
WHERE hca.orig_system_reference = p_duplicate_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('Erros in Extracting the Customer and Duplication Customer Informations');
RAISE;
END;

INSERT INTO ra_customer_merge_headers
( CUSTOMER_MERGE_HEADER_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,CUSTOMER_ID
,CUSTOMER_NAME
,CUSTOMER_NUMBER
,CUSTOMER_REF
,DUPLICATE_ID
,DUPLICATE_NAME
,DUPLICATE_NUMBER
,DUPLICATE_REF
,DELETE_DUPLICATE_FLAG
,PROCESS_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,CUSTOMER_FIRST_NAME
,CUSTOMER_LAST_NAME
,CUSTOMER_TYPE
,DUPLICATE_FIRST_NAME
,DUPLICATE_LAST_NAME
,DUPLICATE_TYPE
,MERGE_REASON_CODE
,MERGE_FAIL_MSG )
VALUES( l_merge_header_id
,SYSDATE
,'0' --created_by--
,SYSDATE
,'0'
,'0' --last_update_login--
,NULL
,NULL
,NULL
,NULL --request_id--
,l_customer_id --customer_id--
,p_customer_name
,l_customer_number
,l_customer_ref
,l_duplicate_id
,p_duplicate_name
,l_duplicate_number
,l_duplicate_ref
,p_delete_duplicate_flag
,p_process_flag
,NULL --DFF attribute_category--
,NULL
,p_customer_first_name
,p_customer_last_name
,p_customer_type
,p_duplicate_first_name
,p_duplicate_last_name
,p_duplicate_type
,p_merge_reason_code
,p_merge_fail_msg
);

l_reqid := FND_REQUEST.SUBMIT_REQUEST
( application => 'AR'
,program => 'RAXMRG'
,Description => 'Customer Merge'
,start_time => NULL
,sub_request => FALSE
);

END;