Oracle EBS Output Post Processor (OPP) – Queue Rebuild

Problem

A few weeks ago, we ran into an issue with our Oracle E-Business Suite Output Post Processor (OPP). It was determined that the OPP queues had to be rebuilt. This is a straight forward procedures that requires a maintenance window including a clean shutdown of the concurrent managers.

We proceeded to perform this maintenance to rebuild the queues. There is a My Oracle Support document that outlines this, How To Purge FND_AQ Tables (Doc ID 1156523.1). We had the procedures in a separate internal document as we had performed it in the past. This is where the mistake began.

Maintenance Issues

We performed the procedures that we’ve executed in the past. OPP started up okay but we later discovered that programs were failing when sent to OPP. The OPP logs were capturing the following error messages:

<code>[2/27/23 8:37:59 AM] [EXCEPTION] [OPPAQMON:316751] ORA-25215: user_data type and queue type do not match</code>

After much research, we went back to the original MOS document on How to Purge FND_AQ tables and discovered a minor but critical update to the document! This update is related to those customers that have applied AD and TXK Delta 13 patch or higher. What has changed is for these customers, you have to pass a different set of parameters when executing the scripts. Executing the scripts the original way, would break the way the AQ objects are configured in the database. As a result, the AQ process will not behave as designed.

Solution

About 6 months ago we had installed the AD and TXK Delta 13 patches. We were now to required execute the scripts a little different than before. While the documentation is not concise on execution, I’ll share the steps that we performed that worked for us.

Warning: The following steps only work for those on AD/TXK Delta 13 or higher.

1. Concurrent managers must be shutdown before proceeding!!!
   • $ADMIN_SCRIPTS_HOME/adcmctl.sh stop
2. Initiate a SQL*Plus session on the application server.
3. Login as SYSTEM and execute
   • @$FND_TOP/patch/115/sql/afopp001.sql APPS_NE <apps_ne_pw> apps <apps_pw>
4. Login as APPLSYS
   • @$FND_TOP/patch/115/sql/aftcm049.sql APPLSYS <apps_pw> apps_ne <apps_pw>
5. Login as APPLSYS
   • @$FND_TOP/patch/115/sql/afopp002.sql APPLSYS <apps_pw> apps_ne <apps_pw>
6. Startup the concurrent managers
   • $ADMIN_SCRIPTS_HOME/adcmctl.sh start

References

The following do mention AD/TXK Delta or higher in the instructions.

• How To Purge FND_AQ Tables (Doc ID 1156523.1)
• Concurrent Manager Fails With ORA-25215 APPS.FND_CP_GSM_IPC (Doc ID 808676.1)

As of this writing, the following My Oracle Support documents have not been updated to reflect the changes in how the scripts should be executed if on AD/TXK Delta 13 or higher.

• R11i/R12: AP: 1099 Forms PDF Format Errors: Unable To Find Output Post Processor (Doc ID 1059712.1)
• Concurrent Manager Startup Fails To Spawn The Service Manager FNDSM and Generates Error Unable to contact Applications TNS Listener for FNDSM_XXX_XXX on $NODE (Doc ID 2567727.1)
• OPP Manager Error ‘ORA-600 [kqludp2]’ Unable To Publish XML Reports (Doc ID 1083510.1)