How to resolve the issue for concurrent program stuck in opp

Follow the below steps to resolve program stuck in OPP issues,

1.You will need to check and fix the Invalid FND Objects :

select owner, object_name,object_type,status from dba_objects where status =’INVALID’ and object_name like ‘FND%’;

2.AQ$_FND_CP_GSM_OPP_AQTBL tables has a lot of rows and purge is needed

select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

select count(*) from APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_T;

select count(*) from APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_G;

select count(*) from APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_H;

select count(*) from APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_I;

select count(*) from APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_L;

select count(*) from APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_S;

HOW TO PURGE ABOVE MENTIONED TABLES:

1.Shut down the application

2.Purge the FND_CP_GSM_OPP_AQTBL manually to clean up the table:

2.1. Check the number of records in the table :

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

COUNT(*)
———-
31759

2.2. Run the following as SYS:

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => ‘APPLSYS.FND_CP_GSM_OPP_AQTBL’,
purge_condition => NULL,
purge_options => po);
END;
/

2.3. Re-check again the number of records in the table

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

COUNT(*)
———-
0

3.Repeat the above steps for other tables.

4.Start the application.