5 Replies Latest reply on Jul 5, 2015 11:17 AM by yoonas

    Edition based redefinition in R12.2.4 on custom schemas

    user1441240

      Hi,

      We have some custom schemas created in ERP database which are used by Web applications. These web applications directly update these  tables in Java application using Hybernet. These applications also calls APPS owned packages, functions, views etc inside their database packages. There are also some triggers created on the tables. Now after upgrading to R12.2, the triggers are not getting fired. After the research, we found that all these custom schemas are editioned and ad_zd_upgrade modified the trigger by creating on editioned view instead of the table. So when the Web application updates this table, the trigger never gets fired as it is not on the table.

      When we don't edition the Custom schema, web applications are throwing an error: (edition objects can't be accessed from non-editioned schema) when try to access any of the APPS owned objects.

       

      Is anyone facing this issue in your environment?? If Yes, how did you resolve  it?

       

      The only solution we came up with it is: Edition the custom schema, but don't edition Tables in it. So we are doing below

      1) Redirect all synonyms to Tables instead of edition view

      2) Redirect all the grants onto tables

      3) Drop edition views (which are created on tables)

      4) Modify all the Triggers to fire on Tables instead of editioned views.

       

      Is this approach right?? Please advise.

       

      Thanks,
      DD

        • 1. Re: Edition based redefinition in R12.2.4 on custom schemas
          Bashar.

          Hi,

           

          What if you keep the edition feature for the tables and create the synonyms and triggers on the tables?

           

          Regards,

          Bashar

          • 2. Re: Edition based redefinition in R12.2.4 on custom schemas
            Pravin Takpire

            1) Redirect all synonyms to Tables instead of edition view

            2) Redirect all the grants onto tables

            3) Drop edition views (which are created on tables)

            4) Modify all the Triggers to fire on Tables instead of editioned views.

             

            I think you may not be able to do step 3, otherwise approach looks ok. But better you raise SR with Oracle.

             

            Few questions for you though ..

             

            There are also some triggers created on the tables -- are these triggers on Custom Tables or Custom triggers on BASE Tables ?

            If these triggers are on Base Tables then better write new triggers and use them.

            Because

            The following objects are editionable:

            FUNCTION

            LIBRARY

            PACKAGE and PACKAGE BODY

            PROCEDURE

            TRIGGER

            TYPE and TYPE BODY

            SYNONYM

            VIEW

            All other objects are non-editionable.  That means tables can not be editioned!

             

            regards

            Pravin

            • 3. Re: Edition based redefinition in R12.2.4 on custom schemas
              yoonas

              DD,

              Now after upgrading to R12.2, the triggers are not getting fired. After the research, we found that all these custom schemas are editioned and ad_zd_upgrade modified the trigger by creating on editioned view instead of the table. So when the Web application updates this table, the trigger never gets fired as it is not on the table.

              If editioned view won't work with a trigger i think ad_zd_upgrade would not have created a trigger on view. Here something else is going wrong thats why triggers are not getting fired i guess

               

              How are you updating using editioned views or using base table name itself?

               

              You can create DML triggers on editioning views. In this case, the database considers the editioning view to be the base object of the trigger. Such triggers fire when a DML operation target the editioning view itself. They do not fire if the DML operation targets the base table.

              http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8004.htm#SQLRF01504

              Regards

              Yoonas

              • 4. Re: Edition based redefinition in R12.2.4 on custom schemas
                user1441240

                Thanks Bashar, Pravin and Yoonas.

                Pravin: We have created triggers on custom tables. Why can't we drop editioned views? Is there an issue ?

                Do you see these objects gets created again if the DBAs do ADOP prepare phase or something during patching?? Iam just worried that eventhough we perform all those steps, when DBAs apply patches, Oracle might create all those again.

                 

                Yoonas: The triggers should be created on base tables because all the web applications directly updates the tables using Groovy and Hibernet and they don't use any synonyms (which point to editioned views) like in Oracle ERP.

                • 5. Re: Edition based redefinition in R12.2.4 on custom schemas
                  yoonas
                  Yoonas: The triggers should be created on base tables because all the web applications directly updates the tables using Groovy and Hibernet and they don't use any synonyms (which point to editioned views) like in Oracle ERP.

                  I have not worked with any of these. If oracle is allowing there should not be any restrictions i think.

                  Is there any restriction imposed by those ?

                   

                  We have created triggers on custom tables. Why can't we drop editioned views? Is there an issue ?

                  While upgrading customs schemas to R12.2 Online Patching Enablement: Readiness Report gives you two options, to make the custom schema ready for editioning and to remain non-edition.

                  I think you have already enabled online patching for your custom schema by making it enabled for editioning.

                   

                  If your schema is not enabled for editioning editioned objects are not created by oracle automatically.


                  Guidance for Integrating Custom and Third-Party Products With Oracle E-Business Suite Release 12.2 (Doc ID 1916149.1)

                  Create And Register CUSTOM Schema On EBS 12.2 (Doc ID 1929668.1)

                  How to De-register a Custom Schema in the e-business Suite [1632215.1] 

                  How to De-register a Custom Schema in the e-business Suite 12.2 [1644146.1]

                  Custom Schemas