1 2 Previous Next 24 Replies Latest reply on Jul 15, 2015 12:40 PM by Andre Dutra Ontalba

    Invalid objects in EBS R12.2.4

    Ramaraju

      Hi,

      We have 1940 invalid objects in instance. Compiled invalid objects with 'utlrp.sql', 'adadmin' but still we have same invalids.

      While compiling manually getting following errors. Please help on this.

      DB: 11.2.0.3.0

      Apps: R12.2.4

      OS: CentOS 5.8

      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.

        • 1. Re: Invalid objects in EBS R12.2.4
          Pk

          Did you check for any of the above package for reason. To understand the error, as what is missing.

           

          Also do you have CUSTOM objects as well?

          • 2. Re: Invalid objects in EBS R12.2.4
            Ramaraju

            We don't have custom objects. I don't know what can I do for these invalids. Please help on this.

            • 3. Re: Invalid objects in EBS R12.2.4
              Pk

              Looking at one of the package:

               

              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

               

              It seems column PO_HEADERS_ALL.RATE is missing in the database.

               

              Did you perform any upgrade or altering the tables or triggers?

               

              Wondering why will some one have so many invalid objects.  Can you have a overall look on number of objects and types are invalid.

              • 4. Re: Invalid objects in EBS R12.2.4
                yoonas

                Hi,

                 

                Is it an upgraded instance?

                Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 (Doc ID 1325394.1)


                What was recent change on the instance?


                 

                Regards

                Yoonas

                • 5. Re: Invalid objects in EBS R12.2.4
                  Ramaraju

                  We have upgraded the EBS from R12.2.0 to R12.2.4 in Jan 2015. But we don't have these invalids that time. We found these invalids yesterday.

                  What can we do for that missing PO_HEADERS_ALL.RATE

                   

                  Thanks,

                  Ramaraju

                  • 6. Re: Invalid objects in EBS R12.2.4
                    Ramaraju

                    Yes, it is upgraded instance. We have upgraded in Jan 2015.

                    • 7. Re: Invalid objects in EBS R12.2.4
                      Pk

                      Its better to check if any patches were installed recently and compare the table definition in any other instance where you do no have this issue.

                       

                      Also you many other tables/views or triggers as well.

                       

                      May be you need to troubleshoot.

                      • 8. Re: Invalid objects in EBS R12.2.4
                        Pravin Takpire


                        For error like below

                         

                        6944/35  PL/SQL: Item ignored

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

                         

                        select text from dba_source where name='AP_ETAX_SERVICES_PKG' and line between 6943 and 6945;

                         

                        This will give you exact Table name and to which column it is referencing. Check whether the table structure is change.

                        If this is happening after upgrade or migration, check if you have followed the correct procedures.

                        For all seeded objects you will get help from Oracle. You can even refer etrm.oracle.com for package definitions.

                        For all custom objects you will dig in and find the solution.

                         

                        regards

                        Pravin

                        • 9. Re: Invalid objects in EBS R12.2.4
                          Ramaraju

                          OP:

                               TEXT

                              l_ref_doc_line_quantity       zx_transaction_lines_gt.ref_doc_line_quantity%TYPE;

                              l_po_header_curr_conv_rat     po_headers_all.rate%TYPE;

                              l_ref_doc_trx_level_type      zx_transaction_lines_gt.ref_doc_trx_level_type%TYPE;

                           

                          Checked with other EBS R12.2.4 instance,

                          OP:

                               TEXT

                              l_ref_doc_line_quantity       zx_transaction_lines_gt.ref_doc_line_quantity%TYPE;

                              l_po_header_curr_conv_rat     po_headers_all.rate%TYPE;

                              l_ref_doc_trx_level_type      zx_transaction_lines_gt.ref_doc_trx_level_type%TYPE;

                          • 10. Re: Invalid objects in EBS R12.2.4
                            Ramaraju

                            Can anyone help on these invalids ?

                               

                            AP_MASS_ADDITIONS_CREATE_PKGPACKAGE BODY
                            AR_CASH_RECEIPT_PRINT_PKGPACKAGE BODY
                            BOM_DIAGUNITTEST_ITMHLCHKPACKAGE BODY
                            BOM_DIAGUNITTEST_TSDATAPACKAGE BODY
                            CEMLI_CSS_IMPACT_CALC_PKGPACKAGE BODY
                            CEMLI_CSS_IMPACT_DERIVATIONPROCEDURE
                            CEMLI_CSS_IMPACT_DERIVATION_V2PROCEDURE
                            CEMLI_CSS_IMPACT_DERIVATION_V3PROCEDURE
                            CEMLI_CSS_IMPACT_DERIVATION_V4PROCEDURE
                            CEMLI_CSS_IMPACT_DERIVATION_V5PROCEDURE
                            CEMLI_CSS_NAMES_UPDATE_FROM_ACPROCEDURE
                            CEMLI_CSS_XML_NAMES_UPDATEPROCEDURE
                            CEMLI_DOUBLE_CHECK_EXCLUSIONSPROCEDURE
                            CEMLI_IMPACT_ROLLUPSPROCEDURE
                            CEMLI_IMPACT_ROLLUPS_V2PROCEDURE
                            CEMLI_IMPACT_ROLLUPS_V3PROCEDURE
                            CONSOLIDATE_RICE_IDSPROCEDURE
                            CSP_EXCESS_PARTS_PVTPACKAGE BODY
                            CS_UWQ_DISTRIBUTE_ITEMSPACKAGE BODY
                            DROP_APPS_TABLESPROCEDURE
                            DROP_TABLESPROCEDURE
                            ECE_AP_PAYMENTPACKAGE BODY
                            ECE_PYO_INVOICE_VVIEW
                            ECE_PYO_PAYMENT_VVIEW
                            IBY_AR_UTILSPACKAGE BODY
                            IBY_FNDCPT_EXTRACT_GEN_PVTPACKAGE BODY
                            IBY_PYO_TRANSACTIONPACKAGE BODY
                            IEC_VALIDATE_PVTPACKAGE BODY
                            IEU_WR_PUBPACKAGE BODY
                            IEX_UWQ_POP_SUM_TBL_PVTPACKAGE BODY
                            IGI_CIS2007_IGIPMTHR_PKGPACKAGE BODY
                            JL_BR_AR_BANK_ACCT_PKGPACKAGE BODY
                            JTF_FM_IH_LOGGER_PVTPACKAGE BODY
                            JTF_FM_INT_REQUEST_PKGPACKAGE
                            JTF_FM_INT_REQUEST_PKGPACKAGE BODY
                            JTF_FM_TRACK_PVTPACKAGE BODY
                            JTF_TERR_ASSIGN_PUBPACKAGE BODY
                            JTF_TERR_LOOKUP_PUBPACKAGE BODY
                            JTF_TERR_SERVICE_PUBPACKAGE BODY
                            JTF_TERR_TASK_PUBPACKAGE BODY
                            JTF_TTY_ALIGN_WEBADI_INT_PKGPACKAGE BODY
                            JTY_ASSIGN_REALTIME_PUBPACKAGE BODY
                            JTY_TERR_DENORM_RULES_PVTPACKAGE BODY
                            JTY_TERR_ENGINE_GEN_PVTPACKAGE BODY
                            JTY_TERR_SPARES_PVTPACKAGE BODY
                            JTY_WEBADI_OTH_TERR_DWNL_PKGPACKAGE BODY
                            • 11. Re: Invalid objects in EBS R12.2.4
                              Ramaraju

                              While compiling invalids manually getting below errors.

                              SQL> alter package apps.AP_MASS_ADDITIONS_CREATE_PKG compile body;

                              Warning: Package Body altered with compilation errors.

                              SQL> show errors

                              Errors for PACKAGE BODY APPS.AP_MASS_ADDITIONS_CREATE_PKG:

                              LINE/COL ERROR

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

                              361/5 PL/SQL: SQL Statement ignored

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

                              560/7 PL/SQL: SQL Statement ignored

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

                              860/5 PL/SQL: SQL Statement ignored

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

                              1061/7 PL/SQL: SQL Statement ignored

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

                              1373/6 PL/SQL: SQL Statement ignored

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

                              1388/1 PL/SQL: SQL Statement ignored

                              LINE/COL ERROR

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

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

                              1412/5 PL/SQL: SQL Statement ignored

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

                               

                              Thanks,

                              Ramaraju

                              • 12. Re: Invalid objects in EBS R12.2.4
                                Pk

                                The error message clears shows that the table is missing in your instance which is accessed by your package.

                                 

                                Did you check the code and table name?

                                • 13. Re: Invalid objects in EBS R12.2.4
                                  Pk

                                  If you see too many of these, then you may need to consider raising an SR with Oracle to know the root cause on why the objects are missing

                                  • 14. Re: Invalid objects in EBS R12.2.4
                                    Ramaraju

                                    I am unable to find table name with that package body. How can I know that which table is using by package body ?


                                    Thanks,

                                    Ramaraju.

                                    1 2 Previous Next