1 2 Previous Next 17 Replies Latest reply on Jul 14, 2015 9:00 AM by Ramaraju

    APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4

    Ramaraju

      Hi,

      We are getting the following error when access Purchase Order Summary in Purchasing, US Federal responsibility. Please help on this issue.

      APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, successful completion

      Cause:  A SQL error has occurred in PO_MOAC_UTILS.Initialize. The SQL error is ORA-0000:norma, successful completion.

      Action: Take note of both this error number and the actions you were trying to perform. Contact your system administrator for futher assistance.

       

      EBSR1224.png

      Please help on this.

       

      Thanks,

      Ramaraju.

        • 1. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
          Bashar.

          Hi,

           

          You may have invalid objects. Have you checked this document:

           

          APP-PO-14142 PO_MOAC_UTILS.Init_PO_StartUp Error When Accessing Some Purchasing Forms (Doc ID 784816.1)

           

          Is the setup of operating units done and working for other forms/modules?

           

          Regards,

          Bashar

          • 2. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
            yoonas

            Hi,

             

            May be What we were discussing yesterday Invalid objects in EBS R12.2.4 could be causing it

             

             

            APP-PO-14142 po_moac_utils.init_po_startup Error When Accessing Some Purchasing Forms(Doc ID 784816.1)

            Guide to Solve APP-PO-14142 Issues in Purchasing (Doc ID 1520084.1)

             

            Regards

            Yoonas

            • 3. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
              Ramaraju

              Yes, I have seen that Document. In that there small different.

              APP-PO-14142 PO_MOAC_UTILS.Init_PO_StartUp Error When Accessing Some Purchasing Forms

              APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, successful completion


              Is this fine to perform that solution ?

              I think we need down time for that solution.

              Please clarify me.


              Thanks,

              Ramaraju.

              • 4. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                Pravin Takpire

                As per note 784816.1 you will require downtime. to avoid downtime you can run

                 

                sqlplus "/as sysdba" @?/rdbms/admin/utlrp.sql to compile invalid objects. It may resolve your invalids issue.

                regards

                Pravin

                • 5. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                  Ramaraju

                  I have already ran @?/rdbms/admin/utlrp.sql multiple times. But still invalids count is same.

                  • 6. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                    yoonas

                    Is it new upgraded instance ? what was recent change on it?

                    Its not normal to have that many invalid objects unless something really went wrong.

                    • 7. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                      Ramaraju

                      Yes it is upgraded instance(EBS R12.2.4).

                      We have upgraded EBS R12.2.0 to R12.2.4 in Jan 2015.

                       

                      Thanks,

                      Ramaraju

                      • 8. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                        yoonas

                        Since then you had this many numbers of invalid objects?

                        And it was running without issues since 12.2.4 upgrade?

                         

                        I think you can create a clone and try the steps in the note as it requires downtime

                        • 9. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                          Pravin Takpire

                          Did you apply any patches recently ? or Somebody did something in database ?

                          Give output of following

                           

                          select count(*), owner from dba_objects where status ='INVALID' group by owner;

                          select count(*), object_type from dba_objects where status ='INVALID' group by object_type;

                           

                          VIEWS,PACKAGES, PACKAGE BODY try to compile manually and see what error do you get.

                           

                          regards

                          Pravin


                          • 10. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                            Ramaraju

                            Two days back we got these invalids. After upgraded to R12.2.4 we didn't get any issues.

                            select count(*), owner from dba_objects where status ='INVALID' group by owner;

                            COUNT(*)OWNER
                            1PUBLIC
                            1912APPS
                            3SRI

                             

                            select count(*), object_type from dba_objects where status ='INVALID' group by object_type;

                            COUNT(*)OBJECT_TYPE

                             

                            14PROCEDURE
                            957PACKAGE BODY
                            108PACKAGE
                            10TRIGGER
                            824VIEW
                            3SYNONYM

                             

                            SQL> alter package apps.APPO_XXX compile body;

                            Warning: Package Body altered with compilation errors.

                            SQL> show errors

                            Errors for PACKAGE BODY APPS.APPO_XXX:

                            LINE/COL ERROR

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

                            10/3 PL/SQL: SQL Statement ignored

                            13/10 PL/SQL: ORA-00980: synonym translation is no longer valid

                            43/3 PL/SQL: SQL Statement ignored

                            46/10 PL/SQL: ORA-00980: synonym translation is no longer valid

                            SQL> alter package AP_ETAX_SERVICES_PKG compile body;

                            Warning: Package Body altered with compilation errors.

                            SQL> show errors

                            Errors for PACKAGE BODY AP_ETAX_SERVICES_PKG:

                            LINE/COL ERROR

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

                            6944/35  PL/SQL: Item ignored

                            6944/35  PLS-00201: identifier 'PO_HEADERS_ALL.RATE' must be declared

                            6946/35  PL/SQL: Item ignored

                            6946/35  PLS-00201: identifier 'PO_HEADERS_ALL.RATE' must be declared

                            7236/12  PL/SQL: Statement ignored

                            7245/48  PLS-00320: the declaration of the type of this expression is

                              incomplete or malformed

                            8239/36  PL/SQL: Item ignored

                            8239/36  PLS-00201: identifier 'PO_HEADERS_ALL.RATE' must be declared

                            8373/13  PL/SQL: Statement ignored

                            LINE/COL ERROR

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

                            8382/49  PLS-00320: the declaration of the type of this expression is

                              incomplete or malformed

                            9462/13  PL/SQL: Statement ignored

                            9471/49  PLS-00320: the declaration of the type of this expression is

                              incomplete or malformed

                            10057/33 PL/SQL: Item ignored

                            10057/33 PLS-00201: identifier 'PO_HEADERS_ALL.RATE' must be declared

                            10533/13 PL/SQL: Statement ignored

                            10542/49 PLS-00320: the declaration of the type of this expression is

                            LINE/COL ERROR

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

                              incomplete or malformed

                            11896/33 PL/SQL: Item ignored

                            11896/33 PLS-00201: identifier 'PO_HEADERS_ALL.RATE' must be declared

                            12034/12 PL/SQL: Statement ignored

                            12043/48 PLS-00320: the declaration of the type of this expression is

                              incomplete or malformed

                            SQL> alter package ICX_REQ_CATEGORIES compile body;

                            Warning: Package Body altered with compilation errors.

                            SQL> show errors

                            Errors for PACKAGE BODY ICX_REQ_CATEGORIES:

                            LINE/COL ERROR

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

                            635/9 PL/SQL: SQL Statement ignored

                            649/14 PL/SQL: ORA-04063: view "APPS.ICX_PO_SUPPL_CATALOG_ITEMS_V" has

                              errors

                            SQL> alter package GHG_UTILITIES_PKG compile body;

                            Warning: Package Body altered with compilation errors.

                            SQL> show errors

                            Errors for PACKAGE BODY GHG_UTILITIES_PKG:

                            LINE/COL ERROR

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

                            237/3 PL/SQL: SQL Statement ignored

                            250/10 PL/SQL: ORA-04063: view "APPS.AP_INVOICE_DISTRIBUTIONS_V" has

                              errors

                            305/15 PL/SQL: Statement ignored

                            305/18 PLS-00364: loop index variable 'DIST_REC' use is invalid

                            313/15 PL/SQL: Statement ignored

                            313/85 PLS-00364: loop index variable 'DIST_REC' use is invalid

                            316/13 PL/SQL: Statement ignored

                            316/16 PLS-00364: loop index variable 'DIST_REC' use is invalid

                            341/7 PL/SQL: Statement ignored

                            LINE/COL ERROR

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

                            341/10 PLS-00364: loop index variable 'DIST_REC' use is invalid

                            386/10 PL/SQL: SQL Statement ignored

                            393/17 PL/SQL: ORA-00980: synonym translation is no longer valid

                            410/5 PL/SQL: Statement ignored

                            413/65 PLS-00364: loop index variable 'PO_RECS' use is invalid

                            430/3 PL/SQL: SQL Statement ignored

                            432/10 PL/SQL: ORA-04063: view "APPS.AP_INVOICE_DISTRIBUTIONS_V" has

                              errors

                            443/3 PL/SQL: SQL Statement ignored

                            445/10 PL/SQL: ORA-04063: view "APPS.AP_INVOICE_DISTRIBUTIONS_V" has

                            LINE/COL ERROR

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

                              errors

                             

                            Thanks,

                            Ramaraju.

                            • 11. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                              Ramaraju

                              While compiling invalids manually, getting following errors.

                              ORA-04063: view "APPS.ADS_BI_BMV_AP_PO" has errors

                              ORA-00980: synonym translation is no longer valid

                              ORA-04063: view "APPS.POFG_BKT_PURCHASE_ORDERS" has errors

                              PL/SQL: ORA-00904: "L_OSP_DETAILS_REC"."OSP_ORDER_ID": invalid identifier

                              PL/SQL: ORA-00942: table or view does not exist

                              PL/SQL: ORA-00904: : invalid identifier

                              PL/SQL: ORA-00904: "WCR_LINE_INFO"."PO_DISTRIBUTION_ID": invalid identifier

                              PL/SQL: ORA-06575: Package or function PO_CORE_S is in an invalid state

                              • 12. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                                yoonas

                                Two days back we got these invalids. After upgraded to R12.2.4 we didn't get any issues.

                                Some change in the database object might have triggered it. As EBS has many dependent objects across different schema modifying any single object may lead to many invalid objects. You will have to find out what caused this to happen. Any base objects got deleted or modified ?

                                 

                                Compiling one by one when you have these many invalid objects would be a daunting task.

                                1325394.1 note outlines many techniques ,   you can select one from the note to compile many invalids.

                                • 13. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                                  Ramaraju

                                  I have ran utlirp.sql in upgrade mode then after bring down the instance and started instance in normal mode and ran utlrp.sql .

                                  But I have same count of invalid objects & getting same error.

                                  In utlrp.sql found ERRORS DURING RECOMPILATION

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

                                                                                     2

                                  SQL> @utlrp.sql;

                                  TIMESTAMP

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

                                  COMP_TIMESTAMP UTLRP_BGN  2015-07-08 19:57:20

                                  DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

                                  DOC>   objects in the database. Recompilation time is proportional to the

                                  DOC>   number of invalid objects in the database, so this command may take

                                  DOC>   a long time to execute on a database with a large number of invalid

                                  DOC>   objects.

                                  DOC>

                                  DOC>   Use the following queries to track recompilation progress:

                                  DOC>

                                  DOC>   1. Query returning the number of invalid objects remaining. This

                                  DOC>      number should decrease with time.

                                  DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

                                  DOC>

                                  DOC>   2. Query returning the number of objects compiled so far. This number

                                  DOC>      should increase with time.

                                  DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

                                  DOC>

                                  DOC>   This script automatically chooses serial or parallel recompilation

                                  DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

                                  DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

                                  DOC>   On RAC, this number is added across all RAC nodes.

                                  DOC>

                                  DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

                                  DOC>   recompilation. Jobs are created without instance affinity so that they

                                  DOC>   can migrate across RAC nodes. Use the following queries to verify

                                  DOC>   whether UTL_RECOMP jobs are being created and run correctly:

                                  DOC>

                                  DOC>   1. Query showing jobs created by UTL_RECOMP

                                  DOC>         SELECT job_name FROM dba_scheduler_jobs

                                  DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

                                  DOC>

                                  DOC>   2. Query showing UTL_RECOMP jobs that are running

                                  DOC>         SELECT job_name FROM dba_scheduler_running_jobs

                                  DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

                                  DOC>#

                                  PL/SQL procedure successfully completed.

                                  TIMESTAMP

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

                                  COMP_TIMESTAMP UTLRP_END  2015-07-09 06:24:46

                                  DOC> The following query reports the number of objects that have compiled

                                  DOC> with errors (objects that compile with errors have status set to 3 in

                                  DOC> obj$). If the number is higher than expected, please examine the error

                                  DOC> messages reported with each object (using SHOW ERRORS) to see if they

                                  DOC> point to system misconfiguration or resource constraints that must be

                                  DOC> fixed before attempting to recompile these objects.

                                  DOC>#

                                  OBJECTS WITH ERRORS

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

                                                    0

                                  DOC> The following query reports the number of errors caught during

                                  DOC> recompilation. If this number is non-zero, please query the error

                                  DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

                                  DOC> are due to misconfiguration or resource constraints that must be

                                  DOC> fixed before objects can compile successfully.

                                  DOC>#

                                  ERRORS DURING RECOMPILATION

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

                                                            2

                                  Function created.

                                  PL/SQL procedure successfully completed.

                                  Function dropped.

                                  PL/SQL procedure successfully completed.


                                  Thanks,

                                  Ramaraju.

                                  • 14. Re: APP-PO-14142:PO_MOAC_UTILS.Initialize-000: ORA-0000:normal, EBS R12.2.4
                                    yoonas

                                    Looks like objects are not getting compiled because of underlying issues with the object. You will have to investigate and find out why objects are not getting compiled.

                                    If you have a clone of this instance before this invalid issue started, please try to do a comparison database objects .

                                    Can you have look at database log file also

                                    1 2 Previous Next