OPP is not getting UP and All XML report is giving Error in apps R12

Issue: OPP is not getting UP. XML Reports are going in Error.

Solutions:

This is process how to purge the FND_CP_GSM_OPP_AQTBL manually to clean up the table:

1. Shut down the concurrent managers cleanly.

2. Check the number of records in the table :

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

Output: Millions of rows.

3. 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;
/

4. Re-check again the number of records in the table:

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

5. Run the script $FND_TOP/patch/115/sql/afopp001.sql as the SYSTEM user.

SQL> @$FND_TOP/patch/115/sql/afopp001.sql
Enter value for 1: SYSTEM
Enter value for 2: manager
Connected.

PL/SQL procedure successfully completed.

Grant succeeded.

Commit complete.

6. Run the script $FND_TOP/patch/115/sql/afopp002.sql as the APPLSYS user.

SQL> @$FND_TOP/patch/115/sql/afopp002.sql
Enter value for 1: applsys
Enter value for 2: appssup
Connected.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

7. Restart the concurrent manager and retest.

In case the purge did not complete successfully after the second time, or did not purge all the queues,
then you would have to recreate the queue. Recreate the queue using $FND_TOP/patch/115/sql/afopp002.sql file as 'APPLSYS' user.
On running the script you will be prompted for username and password. Please note that this may take a longer length of time to complete.

8. Run cmclean twice ensuring commit after each run - the script is available in note 134007.1

9. Start up the application services and retest.

*********************************************************************************