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 OCTOBER 13, 2022

Applies to:

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.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.