Update Customer Account Number via TCA API errors with 'This Record In Table Hz_cust_accounts Cannot Be Locked' (Doc ID 1518466.1)

Last updated on JULY 12, 2017

Applies to:

Oracle Trading Community - Version 12.1.2 and later
Information in this document applies to any platform.

Symptoms

In R12.1.2, the following error is received when trying to update the Customer Account Number using the API hz_cust_account_v2pub.update_cust_account as per Note 1134034.1:

Error
This record in table hz_cust_accounts cannot be locked as it has been updated by another user.

The error is not received when updating the customer via Customers screen.

 

Steps to reproduce:

1) Run the following TCA API script:

================

SET SERVEROUTPUT ON;

DECLARE
  p_cust_account_rec        HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
  p_object_version_number   NUMBER := 6;
  x_return_status           VARCHAR2 (2000);
  x_msg_count               NUMBER;
  x_msg_data                VARCHAR2 (2000);
  x_new_acc_num             VARCHAR2 (2000);

  CURSOR c1
  IS
     SELECT *
       FROM apps.hz_cust_accounts
      WHERE account_number in ('1000','1001');

  b1                        apps.hz_cust_accounts%ROWTYPE;
BEGIN
  mo_global.set_policy_context ('S', 84);

  OPEN c1;

  LOOP
     FETCH c1 INTO b1;

     p_cust_account_rec.cust_account_id := b1.cust_account_id;
     p_cust_account_rec.account_name := FND_API.G_MISS_CHAR;
     p_cust_account_rec.account_number := b1.orig_system_reference;

     DBMS_OUTPUT.put_line (
        'Cust Account ID: ' || SUBSTR (b1.cust_account_id, 1, 50));
     DBMS_OUTPUT.put_line (
        'Old Account Number: ' || SUBSTR (b1.account_number, 1, 50));
     DBMS_OUTPUT.put_line (
           'orig_system_reference: '
        || SUBSTR (b1.orig_system_reference, 1, 50));

     hz_cust_account_v2pub.update_cust_account ('T',
                                                p_cust_account_rec,
                                                p_object_version_number,
                                                x_return_status,
                                                x_msg_count,
                                                x_msg_data);
     
     SELECT account_number
       INTO x_new_acc_num
       FROM apps.hz_cust_accounts
      WHERE cust_account_id = b1.cust_account_id;

     IF x_return_status = fnd_api.g_ret_sts_success
     THEN
     
     
        DBMS_OUTPUT.put_line (
           'Cust Account ID: ' || SUBSTR (b1.cust_account_id, 1, 15));
        DBMS_OUTPUT.put_line (
           'New Account Number: ' || SUBSTR (x_new_acc_num, 1, 50));
        DBMS_OUTPUT.put_line (
           'x_return_status:  ' || SUBSTR (x_return_status, 1, 255));
        DBMS_OUTPUT.put_line ('x_msg_count:  ' || TO_CHAR (x_msg_count));
        DBMS_OUTPUT.put_line (
           'Object Version Number: ' || TO_CHAR (p_object_version_number));
        DBMS_OUTPUT.put_line ('x_msg_data: ' || SUBSTR (x_msg_data, 1, 255));
     ELSE
        DBMS_OUTPUT.put_line (
           'Cust Account ID: ' || SUBSTR (b1.cust_account_id, 1, 15));
        DBMS_OUTPUT.put_line (
           'Old Account Number: ' || SUBSTR (b1.account_number, 1, 50));
        DBMS_OUTPUT.put_line (
           'x_return_status: ' || SUBSTR (x_return_status, 1, 255));
        DBMS_OUTPUT.put_line ('x_msg_count: ' || TO_CHAR (x_msg_count));
        DBMS_OUTPUT.put_line (
           'Object Version Number: ' || TO_CHAR (p_object_version_number));
        DBMS_OUTPUT.put_line ('x_msg_data: ' || SUBSTR (x_msg_data, 1, 255));
     END IF;

     EXIT WHEN c1%NOTFOUND;
  END LOOP;

  CLOSE c1;
END;
/

================

2) The output shows:

x_return_status: E
x_msg_count: 1
Object Version Number: 6
x_msg_data: This record in table hz_cust_accounts cannot be locked as it has been updated by another user.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms