ORA-00001: unique constraint (APPLSYS.AD_SNAPSHOT_FILES_U2) violated in R12 (12.1.1)

Happy and Prosperous New Year 2010!!!

Today I need to discuss an issue while planning to upgrade my 12.1.1 instance to 12.1.2 using the patch 7303033 released recently by Oracle.

While updating the snapshot information through adadmin, i got the error as follows:

AD Administration error:
adusnapUpdateGlobalView(): Error while executing statement

AD Administration error:
ORA-00001: unique constraint (APPLSYS.AD_SNAPSHOT_FILES_U2) violated
ORA-06512: at "APPS.AD_FILE_UTIL", line 2535
ORA-06512: at line 3


AD Administration error:
adusnapStoreInDB(): Error calling adusnapUpdateGlobalView()

Failed to store APPL_TOP Snapshot to tables
adusnapMaintainSnapshot() : Error calling adusnapUpdateCurrentView2()
Error calling adusnapPartialCurVw()
Error calling adusnapMaintainSnapshot

Fix:
use the sql as below as apps user:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select distinct(snapshot_id) from ad_snapshot_files;

SNAPSHOT_ID
-----------
129
128
132
-130
130
131

6 rows selected.

SQL>
Note, the negative number -130, we need to delete the entry and retry with adadmin

SQL> delete from ad_snapshot_files where snapshot_id=-130;

84196 rows deleted.

SQL>

Rerun adadmin, it should continue normally.

Cheers!!!