14 Replies Latest reply on Aug 14, 2018 7:52 AM by John_K

    EBS 12.2.7 Invalid Objects

    Beauty_and_dBest

      EBS 12.2.7

      12c

      OL6

       

       

      Hi ALL,

       

      We have these objects that can not be compiled, even of the run compile schema many times

       

      APPSFA_QUERY_BALANCES_PKGPACKAGE BODY
      APPSFA_REVALUATION_PVTPACKAGE BODY

      APPSFA_DEPRN_SUMMARY_PKGPACKAGE BODY

       

      Are above objects under FA (Fixed Assests)  module?

      Do I need to recompile FA forms using ADADMIN?

      Or is this a symptoms of  another module not licensed or shared?

      Please help....

       

       

      Kind regards,

      jc

        • 1. Re: EBS 12.2.7 Invalid Objects
          Ramaraju

          Hi Jc,

          Can you please try the following commands.

          On database tier connect to SYS user.

          sqlplus '/as sysdba'

          SQL> alter package APPS.FA_QUERY_BALANCES_PKG compile body;

          SQL> alter package compile APPS.FA_REVALUATION_PVT body;

          SQL> alter package compile APPS.FA_DEPRN_SUMMARY_PKG body;

           

          Thanks,

          Ramaraju

          • 2. Re: EBS 12.2.7 Invalid Objects
            Beauty_and_dBest

            Thanks Ram,

             

            I tried that and it still have errors

            It needs expert developer to open the program and find the erring line?

             

             

             

            Kind regards,

            • 3. Re: EBS 12.2.7 Invalid Objects
              John_K

              What are the errors? (Type show errors after compiling).

              • 4. Re: EBS 12.2.7 Invalid Objects
                Kanda-Oracle

                can you check FND_OBJECTS_VL  is valid

                • 5. Re: EBS 12.2.7 Invalid Objects
                  Beauty_and_dBest

                  Thanks Kanda,

                   

                  We have similar issue and raised SR to Oracle Support, and he recommended the following:

                   

                  Does generation of forms able to recompile invalid objects?

                   

                   

                  Kind regards,

                  • 6. Re: EBS 12.2.7 Invalid Objects
                    Kanda-Oracle

                    Hi JC,

                     

                    You can compile invalid objects through adadmin ; to validate invalid objects.

                     

                    Thanks

                     

                    N Kandasamy

                    • 7. Re: EBS 12.2.7 Invalid Objects
                      John_K

                      I wouldn't have thought recompiling the form would have any effect on the database objects. What do you get from "show errors" when you compile the db object?

                      • 8. Re: EBS 12.2.7 Invalid Objects
                        Beauty_and_dBest

                        Hi all,

                         

                        I checked the package and it still invalid

                        The synonym seem have missing columns?

                         

                         

                        Kind regards,

                        • 9. Re: EBS 12.2.7 Invalid Objects
                          Beauty_and_dBest

                          Hi ALL,

                           

                          What we have now is invalid package FA_DEPRN_SUMMARY_PKG.

                           

                           

                          Enter user-name: apps/apps

                          Last Successful login time: Wed Aug 08 2018 13:17:04 +08:00

                           

                          Connected to:

                          Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                          With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

                           

                          SQL> alter package FA_DEPRN_SUMMARY_PKG compile body;

                           

                          Warning: Package Body altered with compilation errors.

                           

                          SQL> show errors

                          Errors for PACKAGE BODY FA_DEPRN_SUMMARY_PKG:

                           

                          LINE/COL ERROR

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

                          122/11   PL/SQL: SQL Statement ignored

                          149/15   PL/SQL: ORA-00904: "HISTORICAL_NBV": invalid identifier

                          223/12   PLS-00341: declaration of cursor 'C' is incomplete or malformed

                          224/9    PL/SQL: SQL Statement ignored

                          244/11   PL/SQL: ORA-00904: "HISTORICAL_NBV": invalid identifier

                          248/13   PL/SQL: Item ignored

                          253/5    PL/SQL: SQL Statement ignored

                          253/18   PLS-00320: the declaration of the type of this expression is

                                   incomplete or malformed

                           

                          260/5    PL/SQL: Statement ignored

                           

                          262/17   PLS-00320: the declaration of the type of this expression is

                                   incomplete or malformed

                           

                          464/11   PL/SQL: SQL Statement ignored

                          566/59   PL/SQL: ORA-00904: "HISTORICAL_NBV": invalid identifier

                          688/11   PL/SQL: SQL Statement ignored

                          790/59   PL/SQL: ORA-00904: "HISTORICAL_NBV": invalid identifier

                          996/11   PLS-00341: declaration of cursor 'C_DEPRNSUMMARY' is incomplete

                                   or malformed

                           

                          997/9    PL/SQL: SQL Statement ignored

                           

                          1027/17  PL/SQL: ORA-00904: "HISTORICAL_NBV": invalid identifier

                          1033/26  PL/SQL: Item ignored

                          1064/5   PL/SQL: SQL Statement ignored

                          1064/31  PLS-00320: the declaration of the type of this expression is

                                   incomplete or malformed

                           

                          SQL>

                           

                           

                           

                          Is the table altered?  the table ( Add field) in oracle r12 because the synonym is not updated and package is become invalid in APPS schema.

                           

                          Here is the Original table of fa_deprn_summary:

                           

                          There is no  unrevalued cost and historical_nbv field  in the original table:

                           

                          Here is the description of new  fa_deprn_summary:

                           

                           

                          desc fa_deprn_summary

                           

                          Name Null Type       

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

                          BOOK_TYPE_CODE NOT NULL VARCHAR2(15)

                          ASSET_ID NOT NULL NUMBER(15) 

                          DEPRN_RUN_DATE NOT NULL DATE       

                          DEPRN_AMOUNT NOT NULL NUMBER     

                          YTD_DEPRN NOT NULL NUMBER     

                          DEPRN_RESERVE NOT NULL NUMBER     

                          DEPRN_SOURCE_CODE NOT NULL VARCHAR2(15)

                          ADJUSTED_COST NOT NULL NUMBER     

                          BONUS_RATE NUMBER     

                          LTD_PRODUCTION NUMBER     

                          PERIOD_COUNTER NOT NULL NUMBER(15) 

                          PRODUCTION NUMBER     

                          REVAL_AMORTIZATION NUMBER     

                          REVAL_AMORTIZATION_BASIS NUMBER     

                          REVAL_DEPRN_EXPENSE NUMBER     

                          REVAL_RESERVE NUMBER     

                          YTD_PRODUCTION NUMBER     

                          YTD_REVAL_DEPRN_EXPENSE NUMBER     

                          PRIOR_FY_EXPENSE NUMBER     

                          BONUS_DEPRN_AMOUNT NUMBER     

                          BONUS_YTD_DEPRN NUMBER     

                          BONUS_DEPRN_RESERVE NUMBER     

                          PRIOR_FY_BONUS_EXPENSE NUMBER     

                          DEPRN_OVERRIDE_FLAG VARCHAR2(1)

                          SYSTEM_DEPRN_AMOUNT NUMBER     

                          SYSTEM_BONUS_DEPRN_AMOUNT NUMBER     

                          EVENT_ID NUMBER(38) 

                          DEPRN_RUN_ID NUMBER(15) 

                          DEPRN_ADJUSTMENT_AMOUNT NUMBER     

                          BONUS_DEPRN_ADJUSTMENT_AMOUNT NUMBER     

                          IMPAIRMENT_AMOUNT NUMBER     

                          YTD_IMPAIRMENT NUMBER     

                          IMPAIRMENT_RESERVE NUMBER     

                          CAPITAL_ADJUSTMENT NUMBER     

                          GENERAL_FUND NUMBER     

                          REVAL_LOSS_BALANCE NUMBER     

                          UNREVALUED_COST NUMBER     

                          HISTORICAL_NBV NUMBER

                           

                           

                           

                          Please help...

                           

                          Kind regards,

                          • 10. Re: EBS 12.2.7 Invalid Objects
                            Kanda-Oracle

                             

                            Hope this helps to resolve.

                             

                            Thanks!

                            • 11. Re: EBS 12.2.7 Invalid Objects
                              Beauty_and_dBest

                              Hi Kanda,

                               

                              The patch was already applied.

                               

                              [orapsar@test01 ~]$ sqlplus apps/apps

                               

                              SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 8 19:19:40 2018

                               

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

                               

                              Last Successful login time: Wed Aug 08 2018 13:25:05 +08:00

                               

                              Connected to:

                              Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                              With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

                               

                              SQL> select bug_number from ad_bugs where bug_number ='22218289';

                               

                              BUG_NUMBER

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

                              22218289

                              • 12. Re: EBS 12.2.7 Invalid Objects
                                John_K

                                So is the column "HISTORICAL_NBV" actually on the table in your instance? Have you checked that it is on both the base table and the editioning view? What is the result of this query?

                                 

                                select owner, table_name from dba_tab_cols where table_name like 'FA_DEPRN_SUMMARY%' and column_name='HISTORICAL_NBV'

                                • 13. Re: EBS 12.2.7 Invalid Objects
                                  Beauty_and_dBest

                                  Hi John and ALL,

                                   

                                  SQL> select owner, table_name from dba_tab_cols where table_name like 'FA_DEPRN_SUMMARY%' and column_name='HISTORICAL_NBV';

                                   

                                  OWNER

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

                                  TABLE_NAME

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

                                  FA

                                  FA_DEPRN_SUMMARY

                                   

                                   

                                  Kind regards,