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.
