R12 EBR : Deploying custom tables

Background

An Oracle E-Business Suite Release 12.2 installation now includes two editions (versions)of the application code and seed data. The file system contains two complete copies of the Oracle E-Business Suite and technology files.

The "RunEdition" is the code and data used by the running application. The Run Edition includes a complete application-tier file system along with all objects and data visible in the default edition of the database. As a developer, you will connect to the Run Edition whenever you are engaged in normal development activity on the system.

The "PatchEdition" is an alternate copy of Oracle E-Business Suite code and seed data that is updated by Online Patching. The Patch Edition includes a complete copy of the application-tier file system and editioned database code objects.The Patch Edition is only usable when an Online Patching session is in progress. End users cannot access the Oracle E-Business Suite Patch Edition,but as a developer you may need to connect to the Patch Edition of a system when applying patches or debugging problems with Online Patch execution.

The Oracle E-Business Suite application-tier files are installed in a root directory of the customer's choosing. Within that root directory you will now find three important sub-directories:

• fs1 - file system 1 (either run or patch edition)
• fs2 - file system 2 (alternate of file system 1)
• fs_ne - non-editioned file system, for data files

To find out which file system is the Run Edition you must look at thevalue of FILE_EDITION environment variable in the environment script for eachfile system:

$ cd /u01/R122_EBS 
$ grep FILE_EDITION= */EBSapps/appl/*.env

Oracle E-Business Suite Release 12.2.2 and higher includes a script to set the run or patch edition environment by name. The script is called "EBSapps.env" and is found in the root directory of an Oracle E-Business Suite application-tier installation.

$ source /u01/R122_EBS/EBSapps.env run 

SQL> select ad_zd.get_edition_type from dual; 

All ADZD* scripts are found under $AD_TOP/sql.For convenience, you can add this directory to the SQLPATH environment variableso that you can refer to the scripts by simple name.

$SQLPATH=$AD_TOP/sql; export SQLPATH

1. If you copied any custom files under the $JAVA_TOP     directory, run the adcgnjar utility to generate and sign a JAR file     containing these files. When prompted, enter the user name and     password of the APPS user. See Section 5.3: Running the adcgnjar Utility.

If your application changes will cause significant object invalidation in the development database, you may wish to call the "ad_zd.compile"procedure to recompile invalid objects in the run edition.

TABLE

exec ad_zd_table.upgrade('CUSTOM_SCHEMA', 'XYZ_CUSTOM_TABLE') 

Run in apps

This will create a editioning view XYZ_CUSTOM_TABLE# in apps, as well as a synonym XYZ_CUSTOM_TABLE in apps based on the view.

IF you have to alter a table, adding a column

exec ad_zd_table.patch('CUSTOM_SCHEMA', 'XYZ_CUSTOM_TABLE') 

For Indexes, Views, Packages you need not do anything.

Materialized View

First create a logical view with the name ending with #

create or replace view XYZ_SCHEMAS_MV#

While it is acceptable for the logical view to depend on editioned synonyms and views, it must not depend on editioned PL/SQL functions, such as those in the Oracle E-Business Suite APPS schema (built-in PL/SQL functions such as "upper" are acceptable). 

materialized views are generated from their corresponding logical views using the AD_ZD_MVIEW.UPGRADE procedure.

execad_zd_mview.upgrade('APPS', 'XYZ_SCHEMAS_MV')

Source