12 Replies Latest reply on Aug 15, 2018 9:18 AM by Beauty_and_dBest

    How to SR to Dev Support

    Beauty_and_dBest

      EBS 12.2.7

      12c

      OL6

       

       

      Hi ALL,

       

      Our new prod EBS 12.2.7 fresh install instance has a wrongly created synonym by EBS installer.

      The synonym is APPS.FA_DEPRN_SUMMARY and it refers to or derived from the table FA.FA_DEPRN_SUMMARY.

      Which in turn makes the package FA_DEPRN_SUMMARY_PKG invalid, resulting to our functional consultant encounters issues in FA setups.

       

      The synonym has missing columns 

      UNREVALUED_COST                               

      HISTORICAL_NBV

       

      Causing the package

       

      SQL> desc apps.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

       

      SQL> desc fa.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

       

      SQL>

       

       

      Is it possible that I just drop synonym and recreate it?

      Or how do I raise SR directed to the DEV team support? So they can advise to recreate the synonym.

       

      I have raised an SR, but it seem a functional support is handling it, and it has been turning round and round, applying patches that is not related to the issue.

      Even I told them the issue is the synonym, they still look into it at a form access level, looking for related errors in forms.

       

       

      Please help how to resolve this erroneous synonym.

       

      Kind regards,

      jc

       

      I

        • 1. Re: How to SR to Dev Support
          John_K

          A synonym can't have missing columns as it's just a pointer to an object. I think I replied to your other post on this with some SQL to run. Did you manage to do that? I suspect it's the editioning view that is missing the columns.

          • 2. Re: How to SR to Dev Support
            Beauty_and_dBest

            Thanks John,

             

            I think I did reply with the output requested.

            The columns exist in the table, but not in the editioned synonym

             

            Can you tell me briefly what is the difference between editioned table and non-editioned one?

            I always see this term but no comprendo.

             

             

            Kind regards,

            • 3. Re: How to SR to Dev Support
              John_K

              Ooops, sorry I missed you reply in (EBS 12.2.7 Invalid Objects ). Ok, so in 12.2, EBS uses the concept of "Editioning" to support minimum downtime (online patching)- this is where you can effectively have multiple copies of daabase objects such as packages in different "editions". So you can have your production instance running in one edition, then you deploy all your new code into another edition, then once done, you can "flip" the new edition to the current one at a point in the future. To support the fact that table structures might change etc, you create "Editioning Views" which allow you to provide a consistent view of a table to the application. That's because tables themselves are not editionable, i.e. there is oly one copy of the data. I wrote about it here a while back: http://www.wegobeyond.co.uk/blog/table-configuration-in-e-business-suite-r12-2-to-support-online-patching .

               

              So where in EBS your apps synonym used to point to the base table like this:

               

              Old Table Structure

               

              it no longer does - it now points to a view on top of the base table like this:

               

              New Table Structure

               

              There is an additional layer of abstraction. The reason I asked you to run the code:

               

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

               

              is because I'd expect to see two rows - one on the base table, and also one on the editioning view, which would have a name of FA_DEPRN_SUMMARY# (the editioning views follow a standard of appending a hash character to the table name).

               

              In your case, you only have the one, so I suspect the editioning view is missing the column.

               

               

              You could try running:

               

              exec ad_zd_table.upgrade('FA','FA_DEPRN_SUMMARY');

              • 4. Re: How to SR to Dev Support
                Beauty_and_dBest

                Thanks John,

                 

                I will need to read over and over to understand the concepts.

                 

                Meanwhile the SR support ask me to create the editioned synonym.

                 

                I am confused because in the APPS schema there are three editioned synonyms. I do not know which one is being accessed by the package FA_DEPRN_SUMMARY_PKG.

                 

                I can see the following:

                 

                CREATE OR REPLACE EDITIONABLE SYNONYM "APPS"."FA_DEPRN_SUMMARY" FOR "FA"."FA_DEPRN_SUMMARY#";

                 

                So the  sysnonym is not taken from the table FA.FA_DEPRN_SUMMARY which has the correct columns, but in the view FA_DEPRN_SUMMARY#

                 

                But the view has missing columns as well. Do I need to recreate the synonym as:

                 

                CREATE OR REPLACE EDITIONABLE SYNONYM "APPS"."FA_DEPRN_SUMMARY" FOR "FA"."FA_DEPRN_SUMMARY" ?

                 

                Is this supported?

                 

                 

                Please help...

                 

                 

                Kind regards,

                • 5. Re: How to SR to Dev Support
                  Beauty_and_dBest

                  Hi John,

                   

                  I run:

                   

                  SQL> exec ad_zd_table.upgrade('FA','FA_DEPRN_SUMMARY');

                   

                  PL/SQL procedure successfully completed.

                   

                   

                  Wow it worked!!!

                   

                  SQL> exec ad_zd_table.upgrade('FA','FA_DEPRN_SUMMARY');

                   

                  PL/SQL procedure successfully completed.

                   

                  SQL> 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

                   

                  SQL>

                   

                  So that is the solution only? why was it behaving that way?

                   

                  SQL> exec ad_zd_table.upgrade('FA','FA_DEPRN_SUMMARY');    << did the magic

                   

                  Is this command supported by Oracle? Is it not destructive?

                   

                  Please help me explain to my boss what happened You are my Angel today!!!!

                  We have been working on this for weeks, and all the functional consultant are blaming me, saying that the PROD FRESH EBS installer I have done is messy or dirty or not a good install.

                   

                  Kind regards,

                  • 6. Re: How to SR to Dev Support
                    John_K

                    A synonym is just a pointer to an object. I'm not sure why Support suggested it was a synonym related issue. It might have been, but I don't know how.

                     

                    I think you had a synonym which correctly pointed to the editioning view - and the editioning view didn't have the additional columns. I suspect that happened because the editioning view was created before the new columns were added to the table, so naturally if you add new columns, then you have to add them into the view. That's what "ad_zd_table.upgrade" does - it synchronises the EV/Synonyms etc. For whatever reason, I suspect the upgrade hasn't executed that step after adding the new columns to the base table.

                     

                    If you can see multiple synonyms then it could be they are from old editions - query from dba_objects rather than dba_synonyms to see the edition column.

                     

                    Is it the only solution? Probably not, but it's the supported one. You could have recreated the view definition:

                     

                    CREATE OR REPLACE FORCE EDITIONING VIEW FA.FA_DEPRN_SUMMARY# as select [[all the columns from the base table here ]] from FA_DEPRN_SUMMARY;

                     

                    But that's what ad_zd_table.upgrade does  - as well as maintains the other objects if necessary.

                    • 7. Re: How to SR to Dev Support
                      Beauty_and_dBest

                      Thanks my Angel

                       

                      Can you give me the note to quote? I need to justify to the management that is it supported by Oracle.

                       

                      Which link do I "quote", to give it a "go" signal to implement?

                       

                      Hi John,

                       

                      I am suspecting there are other objects not editioned properly. Is there a one time command I should run for all the affected objects?  in ADADMIN maybe?

                       

                       

                       

                      Kind regards,

                      • 8. Re: How to SR to Dev Support
                        John_K

                        You're very welcome Jenna.

                         

                        This is probably the nearest:

                         

                        https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=282214097894423&parent=DOCUMENT&sourceId=1583902.1&id=1577661.1&_afrWindowMode=0&_adf.ctrl-state=vi3tcshx4_156

                         

                        Section 1.4.3.1: Tables

                         

                        Although in there, there is a suggestion you can run the patch command instead when adding a table.

                         

                        Add a new column to a table

                        Regenerate the editioning view using AD_ZD_TABLE.PATCH. Whenever you directly alter the structure of a table, you must call the AD_ZD_TABLE.PATCH procedure. The PATCH procedure looks at the physical table columns and then generates the editioning view which presents the logical columns for that table. The PATCH procedure is called automatically when applying table structure changes using XDF or ODF.

                         

                         

                        I'd be tempted to try that first in another environment if you can, although I doubt it will make too much difference.

                        • 9. Re: How to SR to Dev Support
                          John_K

                          ... and if you have an SR you could always tell them you have executed that, it works, but can they confirm that as a resolution.

                          • 10. Re: How to SR to Dev Support
                            Beauty_and_dBest

                            Thanks John,

                             

                            I already inform the SR support, but he/she just told me , I can do it (without confirmation)?

                             

                             

                            Hi <support name>,

                             

                            Issue resolved using exec ad_zd_table.upgrade('FA','FA_DEPRN_SUMMARY');

                             

                            But I can not implement it without your go signal.

                             

                            Kindly reply to run the command, so I can attach it as supported document to our management.

                             

                            Kind regards,

                             

                            Supports response

                             

                             

                            Hi JC,

                             

                            you can proceed with the same

                             

                            Regards,

                             

                            • 11. Re: How to SR to Dev Support
                              John_K

                              I'd say that is enough of a confirmation from them? I'd be comfortable doing it based on that response.