13 Replies Latest reply on Sep 15, 2018 6:55 AM by Beauty_and_dBest

    EBS 12.1.3 - Purchase Order Report - Completed Warning

    Beauty_and_dBest

      EBS 12.1.3

      11g

      OL6

       

      Hi ALL,

       

      We are running report but it completed warning

      MSG-00003: Calling the archiving routine with parameters PO, STANDARD, 1101153

       

      Report Builder: Release 10.1.2.3.0 - Production on Tue Sep 11 08:19:56 2018

       

      Copyright (c) 1982, 2005, Oracle. All rights reserved.

       

      +---------------------------------------------------------------------------+

      Start of log messages from FND_FILE

      +---------------------------------------------------------------------------+

      +---------------------------------------------------------------------------+

      End of log messages from FND_FILE

      +---------------------------------------------------------------------------+

      +---------------------------------------------------------------------------+

      Executing request completion options...

       

      Output file size:

      14128

      +------------- 1) PUBLISH -------------+

      Unable to find an Output Post Processor service to post-process request 14197046.

      Check that the Output Post Processor service is running.

      +--------------------------------------+

      +------------- 2) PRINT -------------+

      Not printing the output of this request because post-processing failed.

      +--------------------------------------+

      Finished executing request completion options.

      +---------------------------------------------------------------------------+

      Concurrent request completed

      Current system time is 11-SEP-2018 08:20:28

      +---------------------------------------------------------------------------+

       

       

      I check Output post processor and they are ok

       

       

       

       

      Please help....

       

       

      Kind regards,

        • 1. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
          Shaik

          Hi JC

           

          check the profile Concurrent: GSM Enabled is set to Y

           

          Concurrent Processing - Unable to Find the Concurrent Request Output Post Processor (OPP) Service (Doc ID 465036.1)

           

           

          Regards

          Shaik

          • 2. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
            Kanda-Oracle
            • Please examine the log file of the OPP  undder $APPLCSF/$APPLLOG.  The name will be FNDOPP<processor_id>.txt
            • The error could be because either OPP is down or not working (due to errors).
              • "Unable to find an Output Post Processor service"
            • 3. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
              Beauty_and_dBest

              Thanks Shaik, and Kanda,

               

              I will check that....brb

              • 4. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                Kanda-Oracle

                Hi,

                 

                you may check out this patch !

                 

                Patch 16000609 - OPP IS UNSTABLE / UNRELIABLE

                Tuning Output Post Processor (OPP) to Improve Performance (Doc ID 1399454.1)

                 

                Thanks!

                • 5. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                  Beauty_and_dBest

                  Hi Shaik and Kanda,

                   

                   

                  I got error of FNDOPPnnn.txt below :

                   

                  [9/11/18 12:36:40 PM] [main] Starting GSF service with concurrent process id = 812191.

                  [9/11/18 12:36:40 PM] [main] Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5

                  [9/11/18 12:36:40 PM] [Thread-18] Service thread starting up.

                  [9/11/18 12:36:40 PM] [Thread-19] Service thread starting up.

                  [9/11/18 12:36:40 PM] [EXCEPTION] [OPPServiceThread0] java.sql.SQLException: ORA-24067: exceeded maximum number of subscribers for queue APPLSYS.FND_CP_GSM_OPP_AQ

                  ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85

                  ORA-06512: at line 1

                   

                          at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)

                          at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)

                          at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)

                          at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)

                          at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)

                          at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)

                          at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)

                          at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)

                          at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)

                          at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)

                          at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)

                          at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)

                          at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)

                          at oracle.apps.fnd.cp.opp.OPPAQMonitor.initAQ(OPPAQMonitor.java:558)

                          at oracle.apps.fnd.cp.opp.OPPAQMonitor.init(OPPAQMonitor.java:534)

                          at oracle.apps.fnd.cp.opp.OPPAQMonitor.initialize(OPPAQMonitor.java:89)

                          at oracle.apps.fnd.cp.opp.OPPServiceThread.init(OPPServiceThread.java:94)

                          at oracle.apps.fnd.cp.gsf.BaseServiceThread.run(BaseServiceThread.java:135)

                   

                  4992K), 0.0001550 secs]

                  [GC 3086K->1678K(4992K), 0.0001560 secs]

                   

                   

                  Please help....

                   

                   

                  Kind regards,

                  • 6. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                    Beauty_and_dBest

                    Hi Shaik, Kanda,

                     

                    Please advise if I can implement the following solution:

                     

                    SOLUTION:


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


                      COUNT(*)

                    ----------

                       1039973


                    Take a back up of the table :


                    SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_bkp2 as select * from applsys.FND_CP_GSM_OPP_AQTBL;


                    Table created.


                    SQL> show user

                    USER is "APPS"

                    SQL> conn applsys/devapps

                    Connected.

                    SQL> show user

                    USER is "APPLSYS"



                    SQL> exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);


                    PL/SQL procedure successfully completed.


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


                      COUNT(*)

                    ----------

                             0


                    Connect as APPS


                    bash-3.00$ sqlplus apps/devapps


                    SQL*Plus: Release 10.1.0.5.0 - Production on Tue Sep 11 11:12:45 2012

                    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


                    Connected to:

                    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

                    With the Partitioning, OLAP, Data Mining and Real Application Testing options


                    SQL>@$FND_TOP/patch/115/sql/afopp002.sql

                    Enter value for 1: APPLSYS

                    Enter value for 2: devapps

                    Connected.


                    PL/SQL procedure successfully completed.


                    SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();


                    Now run cmclean and start the concurrent manager.

                     

                     

                    ==================================

                     

                     

                    SOLUTION 2

                     

                    • Stop OPP Manager

                     

                    • Connect to Apps User

                     

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


                      COUNT(*)

                    ----------

                       10

                     

                     

                    • Take a back up of the table :

                     


                    SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_14jun18 as select * from applsys.FND_CP_GSM_OPP_AQTBL;


                    Table created.

                     

                     

                    • Connect to Applsys User and run below

                     


                    SQL> exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);



                    PL/SQL procedure successfully completed.



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


                      COUNT(*)

                    ----------

                             0

                     

                    • Connect as APPS user and run below


                    SQL>@$FND_TOP/patch/115/sql/afopp002.sql

                    Enter value for 1: APPLSYS

                    Enter value for 2: apps

                    Connected.


                    PL/SQL procedure successfully completed.


                    SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();



                    Start OPP Manager and Validate.

                     

                     

                     

                    Kind regards,

                    • 7. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                      Beauty_and_dBest

                      Hi ALL,

                       

                      Followup please....

                      Are the above solutions destructive?

                       

                       

                      Kind regards,

                      • 8. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                        Shaik

                        Hi JC

                         

                        check this doc

                         

                        R12 E-Business Suite Output Post Processor (OPP) Fails To Pick Up Concurrent Requests With Error 'Unable to find an Output Post Processor service to post-process request nnnnn' (Doc ID 2215371.1)

                        • 9. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                          Beauty_and_dBest

                          Hi Shaik, Kanda.

                           

                          The docs did not mention what userid to login to?

                          Is it APPS or APPLSYS?

                           

                           

                          Do I need to unsubscribe all 598?

                          Except the active ones.

                           

                          OPP741618

                          OPP741955

                          OPP741958

                          OPP742292

                          OPP742294

                          OPP743302

                          OPP745095

                          OPP745429

                          OPP745428

                          OPP752163

                          OPP754856

                          OPP755870

                          OPP756207

                          OPP759697

                          OPP764774

                          OPP769205

                          OPP770063

                          OPP772062

                          OPP773288

                          OPP774299

                          OPP775881

                          OPP776198

                          OPP776535

                          OPP776532

                          OPP777882

                          OPP778315

                          OPP778314

                          OPP778316

                          OPP1580047

                          OPP1680049

                          OPP1680048

                           

                          598 rows selected.

                           

                          SQL> l

                            1* select name from APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S

                           

                          SQL> SELECT fcp.concurrent_process_id

                            2  FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp

                            3  WHERE concurrent_queue_name = 'FNDCPOPP'

                            4  AND fcq.concurrent_queue_id = fcp.concurrent_queue_id

                            5  AND fcq.application_id = fcp.queue_application_id

                            6  AND fcp.process_status_code = 'A';

                           

                          CONCURRENT_PROCESS_ID

                          ---------------------

                                        1680045

                                        1680046

                                        1680047

                                        1680048

                                        1680049

                           

                           

                           

                           

                          Kind regards,

                          • 10. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                            Beauty_and_dBest

                            Hi Shaik, Kanda

                             

                            I tried testing one row and it took very long time until now not yet completed.

                            SQL> exec fnd_cp_opp_ipc.unsubscribe('1580047');

                             

                            How much more the total 593 rows?

                             

                             

                            Is the above solution not applicable to purge?

                            exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);

                             

                             

                            Kind regards

                            • 11. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                              Kanda-Oracle

                              Hi JC

                               

                              Looks like the tables have grown huge.

                               

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

                              select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

                               

                              Thanks!


                              N Kandasamy

                              • 12. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                                Kanda-Oracle

                                If yes, then refer (Doc ID 1399454.1)

                                 

                                  6. Monitor AQ$ tables

                                • 13. Re: EBS 12.1.3 - Purchase Order Report - Completed Warning
                                  Beauty_and_dBest

                                  Thanks ALL,

                                   

                                  This resolved the issue:

                                   

                                  • Connect to Applsys User and run below

                                  SQL> exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);  PL/SQL procedure successfully completed.  SQL>

                                   

                                  • Connect as APPS user and run below


                                  SQL>@$FND_TOP/patch/115/sql/afopp002.sql

                                  Enter value for 1: APPLSYS

                                  Enter value for 2: apps

                                  Connected.


                                  PL/SQL procedure successfully completed.


                                  SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();