AwE-5001 - APPWORX.SO_USER_TABLE_IDX unique constraint violated error when creating New User

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

We are receiving the following error when trying to add new users in our Applications Manager environment:

ErrorMsg: AwE-5001 Database Query Error 

Details: null

java.sql.SQLException: ORA-00001: unique constraint (APPWORX.SO_USER_TABLE_IDX) violated

ORA-06512: at "APPWORX.AWAPI2", line 2325

ORA-06512: at "APPWORX.AWAPI", line 971

ORA-06512: at "APPWORX.AW_WEB_API", line 128

ORA-06512: at line 1

Environment

Release : 9.3.3

Component : APPLICATIONS MANAGER

Oracle 19C

Cause

This error is usually seen if there is a sequence number off in the table.

Resolution

Log into the Applications Manager database in sqlplus and run the following 2 select statements to check the sequence numbers:

SQL> select * from so_seqs;

SQL> select MAX(so_user_seq) "user seq" from so_user_table;

Take the number from the query above and use it for the following SQL:

SQL> update so_seqs set so_seq_value = ## where so_seq_name = 'so_user_seq';
commit;

 

 

Additional Information

It is always advisable to take the needed database backup as well as test the steps on a non production system prior to taking in production.