DeployingCustomizationsinOracleE

DeployingCustomizations in Oracle E-Business Suite Release 12.2 This documentdescribes how to deploy customizations in an Oracle E-Business Suite Release12.2 environment. Follow these instructions to ensure that your customizationsare hand

DeployingCustomizations in Oracle E-Business Suite Release 12.2

This documentdescribes how to deploy customizations in an Oracle E-Business Suite Release12.2 environment. Follow these instructions to ensure that your customizationsare handled appropriately in conjunction with the Online Patching featureintroduced in Release 12.2.

There is a change log at the end of this document.

In This Document

This document isdivided into the following divs:

div 1: Working with Editions

Note: This divreplaces the div "Working with Editions" in Chapter 6,"Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment," of the Oracle E-Business Suite Developer's Guide,Part No. E22961, in the Release 12.2.2 documentation library.

An OracleE-Business Suite Release 12.2 installation now includes two editions (versions)of the application code and seed data. The file system contains two completecopies of the Oracle E-Business Suite and technology files. In the database, weuse the Edition-based Redefinition feature to create a new database edition foreach online patching cycle.

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

The "PatchEdition" is an alternate copy of Oracle E-Business Suite code and seeddata that is updated by Online Patching. The Patch Edition includes a completecopy of the application-tier file system and editioned database code objects.The Patch Edition is only usable when an Online Patching session is inprogress. 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 systemwhen applying patches or debugging problems with Online Patch execution.

The OracleE-Business Suite application-tier files are installed in a root directory ofthe customer's choosing. Within that root directory you will now find threeimportant sub-directories:

The fs1 and fs2directories contain the Run Edition and Patch Edition files for OracleE-Business Suite. The "run" and "patch" file systemdesignation will switch back and forth between fs1 and fs2 for each patchingcycle. 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

fs1/EBSapps/appl/gbzd122x_slc04axp.env:FILE_EDITION="patch"
fs2/EBSapps/appl/gbzd122x_slc04axp.env:FILE_EDITION="run"

In the aboveexample, 'fs2' is the Run Edition file system, and 'fs1' is the Patch Edition.

div 1.1: Connecting to the Run Edition

The Run Editionfile system and database edition are used by the running application. Normaldevelopment activity (writing and testing new code) will also take place in theRun Edition of a development environment.

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

$ source /u01/R122_EBS/EBSapps.envrun
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u01/R122_EBS/fs2/EBSapps/appl
PATCH File System : /u01/R122_EBS/fs1/EBSapps/appl
Non-Editioned File System : /u01/R122_EBS/fs_ne
DB Host: slc04axp.us.oracle.com Service/SID: gbzd122x

Sourcing the RUN File System ...

$ echo $FILE_EDITION
run

$ sqlplus /

SQL> select ad_zd.get_edition_type fromdual;
GET_EDITION_TYPE
----------------
RUN

div 1.2: Connecting to the Patch Edition

The Patch Editioncontains a copy of the application code that can be modified by OnlinePatching. A developer may need to connect to the Patch Edition of an OracleE-Business Suite installation in order to apply patches by hand, or toinvestigate problems with Online Patch execution.

Warning: It is onlysafe to connect to the patch edition while an Online Patching session is inprogress. Specifically, the Patch Edition is created during the "adopphase=prepare" operation, and persists until the cutover or abortoperation is run.

Connect to thepatch edition using the EBSapps.env script as follows:

$ source/u01/R122_EBS/EBSapps.env patch
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u01/R122_EBS/fs2/EBSapps/appl
PATCH File System : /u01/R122_EBS/fs1/EBSapps/appl
Non-Editioned File System : /u01/R122_EBS/fs_ne
DB Host: slc04axp.us.oracle.com Service/SID: gbzd122x

Sourcing the PATCH File System ...
$ echo $FILE_EDITION
patch
$ sqlplus apps/apps
SQL> select ad_zd.get_edition_type from dual;

GET_EDITION_TYPE
----------------
PATCH

Theapplication-tier Patch Edition environment is configured to connect to thedatabase patch edition by default. If a database patch edition is not active,then attempting to connect to the database from the application-tier patchedition environment will fail.

div 1.3: Displaying Edition Status

To help keep trackof what environment and edition you are connected to, it can be helpful to setthe TWO_TASK or FILE_EDITION environment variable as your shell prompt.

$PS1='$TWO_TASK> '
zd122_patch>

You can find outwhether a system is in an Online Patching cycle using the "adop-status" command.

$ adop-status

Enter the APPS username: apps
Enter the APPS password:

Current Patching Session ID: 60

Node Name Node Type Phase Status Started Finished Elapsed
----------- ---------- ---------- -------- ------------------------- ------------------------- ------------
slc04axp master PREPARE COMPLETED 02-JUL-13 04:03:25 -07:00 02-JUL-1305:03:32 -07:00 1:00:07
APPLY COMPLETED 09-JUL-1312:20:45 -07:00 09-JUL-13 01:23:00 -07:00 1:02:15
CUTOVER COMPLETED 10-JUL-13 09:11:41-07:00 10-JUL-13 09:18:47 -07:00 0:07:06
CLEANUP COMPLETED 10-JUL-13 09:29:53-07:00 10-JUL-13 09:52:50 -07:00 0:22:57

If the CUTOVERphase status in not COMPLETED, then an online patching session is in progressand it is valid to connect to the patch edition of the environment.

You can also seethe names and status of past and present database editions using theADZDSHOWED.sql script.

$ sqlplusapps/apps @ADZDSHOWED
"---- Editions ----"
Edition Name Type Status Current?
--------------- -------- -------- --------
ORA$BASE RETIRED
V_20120510_1507 OLD RETIRED
V_20120510_1547 RUN ACTIVE CURRENT
V_20120511_1528 PATCH ACTIVE

The script liststhe existing database editions and identifies the OLD, RUN, and PATCH editions.The Status indicates whether you can connect to the edition (you may onlyconnect to an ACTIVE edition). The Current flag indicates which edition you arecurrently in.

From SQL*Plus itis possible to change your current edition.

SQL> execad_zd.set_edition('PATCH')

div 1.4: Tools and Scripts for Edition-basedDevelopment

The examples inthis guide use various SQL*Plus scripts and command line tools like adop,xdfgen.pl and xdfcmp.pl. The scripts and tools used in Online Patching areoften dependent on a specific code level in the rest of the system, so whenusing an Oracle E-Business Suite environment for development make sure to usethe scripts and tools that come with that environment. Connect to theapplication-tier host for your development environment and source the RunEdition environment file.

$ source/u01/R122_EBS/EBSapps.env run
...
$ which adop
/u01/R122_EBS/fs_ne/EBSapps/appl/ad/bin/adop
$ which xdfgen.pl
/u01/R122_EBS/fs2/EBSapps/appl/fnd/12.0.0/bin/xdfgen.pl
$ which xdfcmp.pl
/u01/R122_EBS/fs2/EBSapps/appl/fnd/12.0.0/bin/xdfcmp.pl

There are a numberof SQL*Plus scripts that can provide useful information about the state of youreditioned development environment. 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

The followingscripts are for experts:

div 2: Applying Online Patches

Note: This divshould follow the div "Working with Editions" in Chapter 6,"Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment," of the Oracle E-Business Suite Developer's Guide,Part No. E22961.

Before developingon an editioned application system, you should understand how online patchesare applied to that system. Application development is done on the Run Editionof a development system, while an online patch is always applied to the PatchEdition of a target system. The online patch may take the form of a ManualPatch or an ARU patch (Oracle patch).

div 2.1: TheOnline Patching Cycle

All patches to aneditioned system are applied within the context of an Online Patching Cycle.The patching cycle has several phases which proceed in order.

1. Prepare - creates the patch edition.
2. Apply - apply ARU or manual patches to the patch edition.
3. Finalize - perform any actions required to prepare for cutover.
4. Cutover - Promote Patch Edition to be the new Run Edition.
5. Cleanup - remove obsolete code and data from old editions.

Online PatchingCycle phases are executed using the new "adop" command line tool.Syntax for each of the phases is described below. At any time you can get adopcommand line help by running "adop -help". You can check the statusof the patching cycle by running "adop -status".

The followingdivs describe how to progress through each phase in detail.

div 2.2:Prepare

Before applying apatch, you must start an Online Patching Cycle. This is done using the adop"prepare" command. Connect to the primary application-tier node ofyour target system and source the run edition environment. Then execute theprepare command.

$ source/u01/R122_EBS/EBSapps.env run
...
$ adop phase=prepare

The adop utilitymay first execute the cleanup phase from the previous cycle if needed, and willthen proceed to prepare the patch edition for a new Online Patching Cycle. Toprepare the patch edition, adop will:

1. Create a new database patch edition
2. Synchronize the file system patch edition with the run edition
3. Configure the patch edition for use by the patching tools

File systemsynchronization may be done by applying the delta (changes) from the previouspatching cycle, or by re-creating the entire patch edition file system as a freshcopy of the run edition (called "fs_clone"). When complete, check theexiting status code (success is '0'):

adop exiting withstatus = 0 (Success)

If there were anyproblems with the prepare phase, check div 7: Troubleshooting and resolvethe problem. Then run the prepare command again.

After a successfulprepare phase, the database and file system patch edition will contain a copyof the run edition code and seed data. You can now apply ARU patches and manualpatches to the patch edition.

div 2.3:Apply

Once the PatchEdition is prepared, you can apply any number of ARU patches or manual patchesto the patch edition. Changes to the patch edition are isolated from the runedition, which is still available for use.

Apply an ARUPatch

Before applying anARU patch, you must first download the patch bundle from ARU through the webuser interface (support.oracle.com). The downloads will be in the form of ZIPfiles. Place the ZIP files in the "fs_ne/EBSapps/patch" directory onthe application-tier installation of your target application system, and thenunzip all ZIP files.

ARU patches areapplied to the patch edition using the "adop phase=apply" command.The command accepts a "patches=..." parameter where you can specify asingle patch or a comma-separated list of patches.

$ adop phase=applypatches=16605855
...
$ adop phase=apply patches=15111111,15222222
...

Note that the adopcommand will apply patches to the patch edition no matter what edition yourcurrent environment is set to.

If the adop applycommands fail, check div 7: Troubleshooting and correctthe problem, then run the adop apply command again, adding the"restart=yes" option.

$ adop phase=applypatches=16605855 restart=yes
...

Apply a ManualPatch

Manual patchesmust be applied to the patch edition of a target system "by hand". Dothis by changing to the patch edition environment and manually executing thepatching actions necessary to install the update. The manual patch actions areidentical to those you would take when applying manual patches to anon-editioned system; the only difference is that on an editioned system, theseactions take place in the patch edition.

Manual patchingactions normally involve the following steps:

1. Copy patch files to their destination directories in the patch edition.
2. Execute any commands necessary to deploy changes to the file system.
3. Execute any commands necessary to deploy changes to the database.
4. Update the custom synchronization driver to include any file system actions that must be executed again on the next prepare phase, in order to synchronize the alternate file system. See div 5.4: Adding Entries to the Custom Synchronization Driver File.

The exact commandsneeded to apply a manual patch vary by the type of files or database objectsbeing patched. These required deployment commands for each file and object typeare discussed later in this document.

The following is asimple example of installing a new server PL/SQL package.

$ source/u01/R122_EBS/EBSapps.env patch
...

$ cd $NE_BASE/EBSapps/patch/manual_000
$ apply_fs.sh

# apply patch to file system
cp fnd/patch/115/sql/* $FND_TOP/patch/115/sql

$ apply_db.sh

# apply patch to database
sqlplus apps/apps @$FND_TOP/patch/115/sql/XYZUTILS.pls
sqlplus apps/apps @$FND_TOP/patch/115/sql/XYZUTILB.pls

After applying anARU patch or a manual patch you can look at the patch edition file system ordatabase status to verify that the patching actions were successful and thatthe resulting patch edition code and seed data are as expected. When you havesourced the patch edition environment, the default database connection goes tothe patch edition. Although you cannot run the application user interface orprogram code in the patch edition, it is possible to connect to the database viaSQL*Plus or other tools and confirm that the desired changes have beensuccessfully implemented. To confirm the updates of the previous manualpatching example, you could do the following:

$ source/u01/R122_EBS/EBSapps.env patch
...

$ sqlplus apps/apps
SQL> show errors package XYZ_UTIL
SQL> show errors package body XYZ_UTIL
SQL> quit

Once all patchingactions are complete and validated, you may proceed to the finalize phase.

div 2.4:Finalize

The finalize phaseis used by the Online Patching system to perform any final actions needed tomake the system ready for the fastest possible cutover. The finalize command isrun as follows:

$ adopphase=finalize

If the finalizecommand returns an error, the system is not ready for cutover. In this case,check div 7: Troubleshooting, correct theproblem and run the finalize command again.

After successfulcompletion of the finalize phase, the system is ready for cutover, but you donot need to execute the cutover right away; you can delay executing cutoveruntil a convenient or predetermined time in the future. You may also applyadditional patches if needed, but you will need to run the finalize phase againafter doing so.

div 2.5:Cutover

The cutover phasewill configure the patch edition to become the new run edition, and restart theapplication on this new run edition.

$ adop phase=cutover
...

$ source /u01/R122_EBS/EBSapps.env run

If cutover fails,check div 7: Troubleshooting, resolve theproblem, and try the cutover command again. One common failure on under-poweredenvironments is that the application startup does not happen quickly enough andthe startup script times out. In this case, just run the cutover command againand adop will retry the startup script.

After successfulcompletion of the cutover phase, the application will be up and running on thenew edition, ready for use. Since the run/patch designation of the dual filesystems are swapped during cutover, you must re-source the run editionenvironment directly after cutover.

Important: Remember tore-source the run edition environment directly after cutover.

div 2.6:Cleanup

The cleanup phasewill remove unnecessary code and data from old editions that are no longerneeded by the running application. Cleanup should be run after cutover, at anytime before the next prepare phase. It is best to run cleanup immediately aftercutover so that there is no delay when preparing the next online patching cycle.There are two levels of cleanup available:

Quick cleanup isthe default, and is all that is necessary after normal patching.

$ adopphase=cleanup

Use full cleanupperiodically or after major updates to restore the system to optimal spaceusage. Warning: full cleanup can take many hours and shouldonly be done when there is no immediate need to start a new patching cycle.

$ adopphase=cleanup cleanup_mode=full

Note: Due to a knownissue in Release 12.2.2, full cleanup is is currently available only in Release12.2.3 and higher

div 2.7:Special Patching Actions

For completeness,the following actions are also listed here. Refer to the OracleE-Business Suite Maintenance Guide, Part No. E22954 for more information onthese commands.

FS Clone

$ adopphase=fs_clone

Abort

$ adopphase=abort
$ adop phase=cleanup
$ adop phase=fs_clone

Note: Due to a known issuein Release 12.2.2, abort is is currently available only in Release 12.2.3 andhigher

div 3: Developing Customizations

div 3.1: Setting Up an Environment for Customizations

If you aredeveloping customizations for the first time, begin by setting up your customapplication on your development environment. See: Overview of Setting Up YourApplication Framework, Oracle E-Business Suite Developer's Guide.

As part of settingup your application, use the AD Splicer utility (adsplice) to register yourcustom application as a product within Oracle E-Business Suite. Forinstructions on running adsplice, see: Applications DBA System ConfigurationTools, Oracle E-Business Suite Setup Guide, and Applications DBASystem Maintenance Tasks and Tools, Oracle E-Business Suite MaintenanceGuide.

Note: In Release 12.2,you should use adsplice to register your application in order to ensure thatthe application is set up for online patching. Do not use the Applicationswindow to register applications in this release.

Note: Wheninstalling or upgrading to Release 12.2, do not run adsplice until you haveapplied the 12.2.2 Release Update Pack. Running adsplice before your instanceis at the 12.2.2 code level may cause file synchronization issues.

You can use Patch 3636980, "SupportDiagnostics (IZU) patch for AD Splice", to help you create your customapplication. See: Creating a Custom Application in Oracle E-BusinessSuite Release 12.2, Document 1577707.1.

On yourdevelopment environment, you should invoke adsplice from the run file system.Connect to the run file system as described in div 1.1: Connecting to the Run Edition. Then runthe adsplice command.

In OracleE-Business Suite Release 12.2, adsplice performs the following steps:

When you start thenext online patching cycle, the prepare phase will run fs_clone to synchronizethe two file systems.

Note: If you upgradedyour environment from an earlier release to Release 12.2, then you should runadsplice for your custom application again after the upgrade, using the sameapplication ID and application name as when you originally added your customapplication. Running the Release 12.2 version of adsplice after the upgradehelps ensure that the custom top folder for your application will be includedwhen the two file systems are synchronized during online patching.

If yourcustomizations will include custom Java or BC4J code or extensions, apply thefollowing patches to your development environment in hotpatch mode using the ADOnline Patching utility (adop). For instructions on running adop, see: The adopUtility, Oracle E-Business Suite Maintenance Guide.

div 3.2: Building Customizations

After setting upyour development environment, build your customizations according to thedeveloper's guide for the product or component you are customizing, as well asany guidelines in div 6: Component-Specific Steps for ApplicationTier Objects.

For customizationsdeveloped directly in the Oracle E-Business Suite instance, you should downloadthe custom object files that you will deploy to your production environment.

1. Connect to the run edition file system on your development environment.
2. Use the appropriate utility for your product or component to download the custom object files.

For customizationsdeveloped in a tool outside the Oracle E-Business Suite instance, you shouldsave the custom object files from that tool. To deploy the custom object filesin your development environment for testing, perform the following steps:

1. Connect to the run edition file system on your development environment.
2. Copy the custom files to the appropriate directory on the run edition file system.
3. 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 div 5.3: Running the adcgnjar Utility.
4. If necessary, use the appropriate utility for your product or component to upload the custom files to the database.
5. Add entries for the custom files to the custom synchronization driver file to ensure that the adop utility synchronizes these files between the run file system and the patch file system the next time you run the prepare phase. See div 5.4: Adding Entries to the Custom Synchronization Driver File.

div 4: Developing and DeployingCustom Database Objects

For moreinformation on database object development standards, see: Database Object DevelopmentStandards for Online Patching, Oracle E-Business Suite Developer'sGuide.

div 4.1:Editioned Database Objects

Note: This divreplaces the div "Editioned Database Objects" in Chapter 6,"Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment", of the Oracle E-Business Suite Developer's Guide,Part No. E22961, in the Release 12.2.2 documentation library.

Editioned DatabaseObjects may have a different definition in each database edition. This meansthat such objects can be created or replaced in the Patch Edition withoutaffecting the running application. Editioned Database Object Types are:

For moreinformation on these objects, refer to the Oracle DatabaseAdministrator's Guide 11g Release 2 (11.2).

Step 1: Createor Replace Editioned Database Objects in your development database:

An applicationdeveloper can create or replace editioned database objects in the run editionof a development database using whatever scripts or tools they normally use.Typically this involves editing SQL scripts that contain DDL statements, andthen applying the scripts to the development database. For example:

sqlplus/ @XYZUTILS.pls"
sqlplus / @XYZUTILB.pls"
sqlplus /
exec ad_zd.compile
quit

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

Test your changesin the running application. When satisfied, make note of the changed DDLscripts and proceed to the next step.

Step 2: Createthe patch

Patch files in theabove example would be:

The manual applyactions for the file system would be:

cpfnd/patch/115/sql/* $FND_TOP/patch/115/sql

The manual applyactions for the database would be:

sqlplus/@$FND_TOP/patch/115/sql/XYZUTILS.pls
sqlplus /@$FND_TOP/patch/115/sql/XYZUTILB.pls

div 4.2:Effectively-Editioned Database Objects

Note: This div replacesthe divs "Tables" and "Materialized Views" in Chapter6, "Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment", of the Oracle E-Business Suite Developer's Guide,Part No. E22961, in the Release 12.2.2 documentation library.

div 4.2.1:Tables

Since theapplication is still running during an online patch (and the application datais continuously changing), it is not possible to upgrade application data usinga one-time update script. Instead we will need to use a new technique involvingEditioning Views and Crossedition Triggers, described below.

Note: This divdescribes how to develop and patch ordinary application data tables. But thereare some special types of tables that have additional or alternate standardsand procedures. If you are working with one of these special table types,please consult that div of the guide instead.

Create a New Table

This example willshow how to develop and patch a new table on an editioned developmentenvironment. Suppose we want to create a table that holds "serviceinformation" per user account for some application with the followinglogical table structure:

XYZ_USER_SERVICE

Name Null? Type

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

USER_ID NOT NULLNUMBER

-- PK, FK to FND_USER.USER_ID

SERVICE_TYPE NOT NULLVARCHAR2(8)

-- 'BASIC' - normal service

-- 'PREMIUM' - premium service

COMMENTS VARCHAR2(1000)

1. Create the initial table definition in your development database.

In this example weuse SQL*Plus to execute the required DDL. This step includes creation of any requiredindexes, storage properties, and so on. As with all development, you should beconnected to the Run Edition of your EBS development environment.

create tableAPPLSYS.XYZ_USER_SERVICE
(
USER_ID NUMBER(15) not null,
SERVICE_TYPE VARCHAR2(8) not null,
COMMENTS VARCHAR2(1000)
)
tablespace APPS_TS_TX_DATA
/
create unique index APPLSYS.XYZ_USER_SERVICE_U1
on APPLSYS.XYZ_USER_SERVICE ( USER_ID )
tablespace APPS_TS_TX_IDX
/

Please avoid usingofficial database constraints for Primary Key and Unique Key enforcement.Unique indexes achieve the goal and are easier to manage under Online Patching.

2. Upgrade the table for Online Patching using the AD_ZD_TABLE.UPGRADE procedure.

This will generatean Editioning View (EV) for the table and then create an APPS synonym thatpoints to the Editioning View.

execad_zd_table.upgrade('APPLSYS', 'XYZ_USER_SERVICE')

The table is nowready for use from the APPS schema. The generated EV is named XYZ_USER_SERVICE#and looks exactly like the table at this point. When the table structure ispatched in the future, the EV will serve to map logical column names (used bythe application code) to the table columns that store the data in each edition.You can see a display of the EV column mapping with the ADZDSHOWEV.sql script:

$AD_TOP/sql/ADZDSHOWEV.sqlXYZ_USER_SERVICE

-- EV ColumnMapping

VIEW_COLUMN -> TABLE_COLUMN

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

USER_ID = USER_ID

SERVICE_TYPE = SERVICE_TYPE

COMMENTS = COMMENTS

Now we can addsome data to the table for demonstration purposes:

insert intoxyz_user_service (user_id, service_type, comments)
values (0, 'PREMIUM', 'Big Spender');
insert into xyz_user_service (user_id, service_type, comments)
values (2, 'BASIC', 'Mr Prudent');
commit;

3. Extract the table definition from your development database using the xdfgen.pl utility.

Due to a databaserequirement you must first insert at least one row into the table beforeextraction will work.

$ xdfgen.pl /@dbXYZ_USER_SERVICE

This produces afile called 'xyz_user_service.xdf' that contains the definition of the tablealong with any related indexes, sequences, and policies.

4. Create the patch.

Patch Files:

· fnd/patch/115/xdf/xyz_user_service.xdf

Manual apply phaseactions for the file system:

cpfnd/patch/115/xdf/* $FND_TOP/patch/115/xdf

Manual apply phaseactions for the database:

xdfcmp.pl/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf/

When the patch isapplied, XDF will create the table and index, and will automatically call theAD_ZD_TABLE.UPGRADE procedure to generate the editioning view and APPS tablesynonym.

Add a new column to a table

This stepdemonstrates adding a new logical column to a table (as opposed to revising anexisting logical column, which we will cover in a later div). Todemonstrate this procedure, will add a new flag to our example service tablethat indicates whether the service is enabled. The desired logical tablestructure is as follows:

XYZ_USER_SERVICE

Name Null? Type

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

USER_ID NOT NULLNUMBER

-- PK, FK to FND_USER.USER_ID

SERVICE_TYPE NOT NULLVARCHAR2(8)

-- 'BASIC' - normal service

-- 'PREMIUM' - premium service

COMMENTS VARCHAR2(1000)

==> SERVICE_STATUS NOT NULLVARCHAR2(8)

==> -- 'ENABLED' - service is active

==> -- 'DISABLED' - service is notactive.

1. Create the new column in your development database.

We can do this inSQL*Plus as follows:

alter tableAPPLSYS.XYZ_USER_SERVICE
add (SERVICE_STATUS varchar2(8) default 'ENABLED' not null)
/

Note: When addinga NOT NULL column, it is recommended to choose a default value. Even if thecolumn value will be populated through application logic you should stillspecify a default value for a NOT NULL column. The default value will allowXDF/ODF to create the column with the NOT NULL constraint in a single pass.Populating a new or revised column during online patching is done using acrossedition trigger which will be explained later.

2. 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.

3. execad_zd_table.patch('APPLSYS', 'XYZ_USER_SERVICE')

4.

5. @ADZDSHOWEV XYZ_USER_SERVICE

6. -- EV Column Mapping

7.

8. VIEW_COLUMN -> TABLE_COLUMN

9. ---------------------------------- ------------------------------

10. USER_ID = USER_ID

11. SERVICE_TYPE = SERVICE_TYPE

12. COMMENTS = COMMENTS

13. SERVICE_STATUS = SERVICE_STATUS

The new column isnow present in the Editioning View.

14. Extract the updated table definition from your development database:

xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE

15. Create the patch.

Patch Files:

15. fnd/patch/115/xdf/xyz_user_service.xdf

Manual apply phaseactions for the file system:

cpfnd/patch/115/xdf/* $FND_TOP/patch/115/xdf

Manual apply phaseactions for the database:

xdfcmp.pl/$FND_TOP/patch/115/xdf/xyz_user_service.xdf

16. Test the patch.

When the patch isapplied, XDF will add the new column and automatically call theAD_ZD_TABLE.PATCH procedure on the target system.

Add a new index

This divdemonstrates how to add a new index to an existing table. In the following example,we add a non-unique index on the SERVICE_TYPE attribute of our example table.The logical table structure is unchanged.

1. Create the new index in your development database.

create indexAPPLSYS.XYZ_USER_SERVICE_N1 on APPLSYS.XYZ_USER_SERVICE
( SERVICE_TYPE )
tablespace APPS_TS_TX_IDX
/

When adding anindex it is not necessary to call the AD_ZD_TABLE.PATCH procedure, as the tablestructure has not changed.

2. Extract the updated table definition from your development database:

xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE

When extracting atable definition, XDF also extracts any related index definitions.

3. Create the patch.

Patch Files:

3. fnd/patch/115/xdf/xyz_user_service.xdf

Manual apply phaseactions for the file system:

cp fnd/patch/115/xdf/*$FND_TOP/patch/115/xdf

Manual applyactions for the database:

xdfcmp.pl/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf

4. Test the patch

When XDF appliesthe table definition, it will detect that the target database is missing thenew index, and it will create the new index. Note that when the XDF is appliedin the Patch Edition of a target system, the new index is initially createdwith an alternate name, which will then be updated to the correct index nameduring cutover.

Update an existing column

This div showshow to update an existing logical column. To update existing data withoutdisturbing the running application we must create a new physical column (calleda revised column) to hold the updated data. In this example, we upgradeSERVICE_TYPE codes from the original two-value scheme (‘BASIC’, ‘PREMIUM’) to athree-value scheme (‘BRONZE’, ’SILVER’, ’GOLD’). Since the new values are notcompatible with the existing application, we must use a revised physical columnto hold the new data. The logical name of the column (as exposed through theeditioning view) remains the same. The desired logical table structure is asfollows:

XYZ_USER_SERVICE

Name Null? Type

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

USER_ID NOT NULLNUMBER

-- PK, FK to FND_USER.USER_ID

SERVICE_TYPE NOT NULL VARCHAR2(8)

==> -- 'BRONZE' - cheap service (was 'BASIC')

==> -- 'SILVER' - new mid-level service

==> -- 'GOLD' - best service (was 'PREMIUM')

COMMENTS VARCHAR2(1000)

SERVICE_STATUS NOT NULLVARCHAR2(8)

-- 'ENABLED' - service is active

-- 'DISABLED' - service is notactive.

1. Create a revised column in your development database.

Revised columnsuse a naming standard of COLUMN_NAME#REVISION, where a later REVISION tag mustbe alphabetically greater than the earlier revision. Since this is the firstrevision of the column, start with revision ‘1’. The data upgrade logic will beplaced in a Forward Crossedition Trigger described later. Alter the table inyour development database to add the new revised column, and remember to callthe AD_ZD_TABLE.PATCH procedure whenever you change the table structuremanually:

alter tableAPPLSYS.XYZ_USER_SERVICE
add (SERVICE_TYPE#1 varchar2(8) default '*NULL*' not null)
/

exec ad_zd_table.patch('APPLSYS', 'XYZ_USER_SERVICE')

Since the revisedcolumn is not null, specify a default value so that the column can be createdwith the not null constraint in a single operation. The actual value of thecolumn will be populated by a crossedition trigger, so the default value doesnot matter, but it is useful to specify a default value which clearly indicatesthat the column is not yet populated.

@ADZDSHOWEVXYZ_USER_SERVICE

-- EVColumn Mapping

VIEW_COLUMN -> TABLE_COLUMN

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

USER_ID = USER_ID

SERVICE_TYPE ===> SERVICE_TYPE#1

COMMENTS = COMMENTS

SERVICE_STATUS = SERVICE_STATUS

Notice that afterexecuting the PATCH procedure the SERVICE_TYPE column in the EV (the logicalcolumn) is now mapped to the revised physical column. Also notice that this newcolumn is not yet populated with data. That comes next.

2. Create a Forward Crossedition Trigger to populate the revised column.

A ForwardCrossedition Trigger (FCET) is a table trigger with a special rule about how itfires: During online patching, the FCET is created in the Patch Edition, but(being a crossedition trigger) it will only fire on changes made in the parent(Run) edition. The upgrade logic is implemented as a trigger instead of asimple update statement so that the upgrade logic can be re-executed on rowsthat are inserted or changed by the running application.

Although the FCETis intended to be installed in the Patch Edition during an online patch, youcan create and test an FCET in the Run Edition of a development database. Tocreate an FCET, start with the Forward Crossedition Trigger Template and addthe data upgrade logic to the trigger body.

The ForwardCross-edition Trigger Template is as follows:

REM ---- CreateFCET ----
REM dbdrv: sql ~PROD ~PATH ~FILE \
REM dbdrv: none none none sqlplus &phase=ccet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE&un_
REM ---- Apply FCET ----
REM dbdrv: sql ad patch/115/sql AD_ZD_TABLE_APPLY.sql \
REM dbdrv: none none none sqlplus &phase=acet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE_F

REM Copyright (c) 2013 Oracle, All Rights Reserved
REM $Header$
REM _X.sql
REM

SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

create or replace trigger _F
before insert or update on &1..
for each row forward crossedition
/* follows */ disable
begin

end;
/

commit;
exit;

For our example,the FCET looks like the following:

REM ---- CreateFCET ----
REM dbdrv: sql ~PROD ~PATH ~FILE \
REM dbdrv: none none none sqlplus &phase=ccet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE &un_fnd
REM ---- Apply FCET ----
REM dbdrv: sql ad patch/115/sql AD_ZD_TABLE_APPLY.sql \
REM dbdrv: none none none sqlplus &phase=acet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE XYZ_USER_SERVICE_F1

REM Copyright (c) 2013 Oracle Corporation, All Rights Reserved
REM $Header$
REM XYZ_USER_SERVICE_X1.sql
REM Update XYZ_USER_SERVICE SERVICE_TYPE toBRONZE/SILVER/GOLD

SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

create or replace trigger XYZ_USER_SERVICE_F1
before insert or update on &1..XYZ_USER_SERVICE
for each row forward crossedition
disable
begin
if :new.service_type = 'BASIC' then
:new.service_type#1 := 'BRONZE';
elsif :new.service_type = 'PREMIUM' then
:new.service_type#1 := 'GOLD';
end if;
end;
/

commit;
exit;

Create the triggerwith the following naming standards:

2. Crossedition Trigger Script Name: _X.sql
3. is incremented for each successive patch to the table
4. Example: XYZ_USER_SERVICE_X1.sql, XYZ_USER_SERVICE_X2.sql, ...
5. Forward Crossedition Trigger Name: _F
6. Reverse Crossedition Trigger Name: _R