Deploying Customizations in Oracle E-Business Suite Release 12.2-白红宇

Deploying Customizations in Oracle E-Business Suite Release 12.2

阅读量：5332 次

发布时间：2019-06-15

本文共 126539 字，大约阅读时间需要 421 分钟。

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 at the end of this document.

In This Document

This document isdivided into the following sections:

Section 1: Working with Editions

Note: This sectionreplaces the section "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:

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

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.

Section 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 <apps_username>/<apps_password>

SQL> select ad_zd.get_edition_type fromdual;

GET_EDITION_TYPE

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

RUN

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

Section 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')

Section 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

ADZDDBCC - database compliance checker, shows violations of the database object development standards documented in the Oracle E-Business Suite Developer's Guide, Part No. E22961. Warning: this script takes a long time to run.

ADZDSHOWED - Show database editions and current edition.

ADZDSHOWLOG - Show full diagnostic log for online patching infrastructure

ADZDSHOWLOGEVT - Show only event and error messages from online patching diagnostic log (a useful summary, without the detailed statement text).

ADZDSHOWLOGERR - Show only error messages from online patching diagnostic log.

ADZDSHOWEV TABLE_SYNONYM_NAME - Show editioning view column mapping for table.

ADZDSHOWTAB TABLE_SYNONYM_NAME - Show table information and related objects.

ADZDSHOWMV MVIEW_NAME - Show materialized view information and related objects.

ADZDSHOWTS - Show important tablespace status. Ensure that you have enough SYSTEM tablespace.

ADZDCMPED - Compare Patch Edition with Run Edition. Warning: this script may take a long time to run.

ADZDSHOWDDLS - Show stored DDL summary by phase.

ADZDALLDDLS - Show stored DDL statement text and status.

ADZDDDLERROR - Show stored DDL execution errors and messages.

adutlrcmp - Recompile all objects, with before/after status report. Warning: this script may take a long time to run.

The followingscripts are for experts:

ADZDSHOWOBJS - Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.

ADZDSHOWAOBJS - Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.

ADZDSHOWIOBJS - Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the Patch Edition.

ADZDSHOWCOBJS - Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition.

ADZDSHOWCOBJX - Show Covered Object List. List of objects in old editions that have a replacement in the run edition.

ADZDSHOWSM - Show Seed Manager status.

ADZDSHOWTM - Show Table Manager status.

ADZDSHOWAD - AD (online patching) database object status

ADZDSHOWSES - Show sessions connected to the database (by edition).

ADZDSHOWDEP OBJECT_NAME - Show objects that OBJECT_NAME depends on.

ADZDSHOWDEPTREE OBJECT_NAME - Show full dependency tree of objects that OBJECT_NAME depends on.

Section 2: Applying Online Patches

Note: This sectionshould follow the section "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).

A manual patch consists of a set of files plus a set of installation actions that are executed to apply the changes to a target system. The procedure for applying a manual patch to an editioned system is similar to that of earlier non-editioned releases, with two important differences:
- Manual patching actions must be executed in the Patch Edition of the target system.
- Manual patching actions that affect the file system must be repeated or copied to the alternate file system on the next patching cycle.

An ARU Patch consists of a set of files that may be annotated with "dbdrv" comments, which are processed by ARU to produce a patch bundle. The patch bundle can then be applied automatically using the "adop phase=apply" command. This is the equivalent of running "adpatch" on non-editioned system. The "adop phase=apply" command executes all patch actions required to apply the update to the patch edition of the target system, and automatically handles file system synchronization on the next patching cycle.

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

Prepare - creates the patch edition.

Apply - apply ARU or manual patches to the patch edition.

Finalize - perform any actions required to prepare for cutover.

Cutover - Promote Patch Edition to be the new Run Edition.

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 followingsections describe how to progress through each phase in detail.

Section 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:

Create a new database patch edition

Synchronize the file system patch edition with the run edition

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

Section 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 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:

Copy patch files to their destination directories in the patch edition.

Execute any commands necessary to deploy changes to the file system.

Execute any commands necessary to deploy changes to the database.

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 .

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.

Section 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 , 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.

Section 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 , 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.

Section 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 - the minimal cleanup required before starting the next patching cycle.

full - removes all obsolete code and data to recover maximum free space.

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

Section 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

Section 3: Developing Customizations

Section 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 , "SupportDiagnostics (IZU) patch for AD Splice", to help you create your customapplication. See: Creating a Custom Application in Oracle E-BusinessSuite Release 12.2, .

On yourdevelopment environment, you should invoke adsplice from the run file system.Connect to the run file system as described in . Then runthe adsplice command.

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

Makes the new user edition-enabled.

Enables Edition-Based Redefinition (EBR) for the custom objects.

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.

17217965:R12.TXK.C (TEMPLATE CHANGE REQUIRED TO UPLOAD THE CLASS FILES RELATED TO CUSTOMIZATIONS)

17217772:R12.AD.C (NEED UTILITY TO GENERATE CUSTOMALL.JAR)

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

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

Connect to the run edition file system on your development environment.

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:

Connect to the run edition file system on your development environment.

Copy the custom files to the appropriate directory on the run edition file system.

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 .

If necessary, use the appropriate utility for your product or component to upload the custom files to the database.

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 .

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

Section 4.1:Editioned Database Objects

Note: This sectionreplaces the section "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:

View (Ordinary)

PL/SQL Package

PL/SQL Trigger

User Defined Type (Editioned)

Synonym

Virtual Private Database Policy

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<apps_username>/<apps_password> @XYZUTILS.pls"

sqlplus <apps_username>/<apps_password> @XYZUTILB.pls"

sqlplus <apps_username>/<apps_password>

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:

fnd/patch/115/sql/XYZUTILS.pls

fnd/patch/115/sql/XYZUTILB.pls

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<apps_username>/<apps_password>@$FND_TOP/patch/115/sql/XYZUTILS.pls

sqlplus <apps_username>/<apps_password>@$FND_TOP/patch/115/sql/XYZUTILB.pls

Section 4.2:Effectively-Editioned Database Objects

Note: This section replacesthe sections "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.

Section 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 sectiondescribes 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 section 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)

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.

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;

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 <apps_username>/<apps_password>@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.

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<applsys_user>/<applsys_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf<apps_user>/<apps_password>

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 section). 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.

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.

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')

5. @ADZDSHOWEV XYZ_USER_SERVICE

6. -- EV Column Mapping

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.

Extract the updated table definition from your development database:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_USER_SERVICE

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<apps_user>/<apps_password>$FND_TOP/patch/115/xdf/xyz_user_service.xdf

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

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.

Extract the updated table definition from your development database:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_USER_SERVICE

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

Create the patch.

Patch Files:

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<apps_user>/<apps_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf

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

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.

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_<table_owner_app_short_name>

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<table_name>_F<change_number>

REM $Header$

REM <table_name>_X<change_number>.sql

REM <description of change>

SET VERIFY OFF;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR EXIT FAILURE ROLLBACK;

create or replace trigger <table_name>_F<change_number>

before insert or update on &1..<table_name>

for each row forward crossedition

/* follows <previous_fcet> */ 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 $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:

Crossedition Trigger Script Name: <table_name>_X<change_number>.sql
- <change_number> is incremented for each successive patch to the table
- Example: XYZ_USER_SERVICE_X1.sql, XYZ_USER_SERVICE_X2.sql, ...

Forward Crossedition Trigger Name: <table_name>_F<change_number>

Reverse Crossedition Trigger Name: <table_name>_R<change_number>

For custom(manual) patches, you use the script template and remove or ignore the"dbdrv" comments. Your database apply script will include commands toinstall and apply the FCET.

To unit test yourcrossedition trigger logic you can install and apply the trigger manually inthe run edition of your development database. Execute the SQL script to createthe trigger and then call the AD_ZD_TABLE_APPLY script to apply the trigger.

sqlplus<apps_user>/<apps_password> @XYZ_USER_SERVICE_X1 APPLSYS

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY.sql XYZ_USER_SERVICE_F1

At this point thenew column is populated. The final step of updating an existing logical columnis to maintain any managed objects that may be referencing the original (nowout-of-date) column. There may be indexes or materialized views that referencethe physical table columns. If these objects reference obsolete table columns,they need to be updated to refer to the latest revised columns. This step canbe done automatically by Online Patching.

To fix managedobjects after revising an existing logical column, call the AD_ZD.FINALIZE,AD_ZD.CUTOVER and AD_ZD.CLEANUP procedures manually in your developmentdatabase. These operations are normally done as part of the Online PatchingCycle, but since your development environment is not actually in an OnlinePatching Cycle, you must call the procedures manually.

sqlplus<apps_user>/<apps_password>

exec ad_zd.finalize

exec ad_zd.cutover

exec ad_zd.cleanup

quit

The FINALIZEprocedure creates a revised version of the index on SERVICE_TYPE. Since we arenow storing the service type information in the SERVICE_TYPE#1 column, theexisting index must be updated to use the new column. FINALIZE creates therevised index under an alternate name, which will be changed to the originalname during the cutover phase.

The CUTOVERprocedure removes the "NOT NULL" constraint on the old SERVICE_TYPEcolumn, drops the old index, and renames the revised index to the originalname. In a real Online Patch, the CUTOVER procedure also promotes the PatchEdition to be the new Run Edition, but when called from the Run Edition thataction is skipped. The table is now ready for use.

The CLEANUPprocedure disables and removes the crossedition trigger.

Extract the updated table definition from your development database:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_USER_SERVICE

At last, you areready to create the patch.

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_user_service.xdf

fnd/patch/115/sql/XYZ_USER_SERVICE_X1.sql

Manual apply phaseactions for the file system:

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

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

Manual applyactions for the database:

xdfcmp.pl<applsys_user>/<applsys_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf <apps_user>/<apps_password>

sqlplus <apps_user>/<apps_password>@$FND_TOP/patch/115/sql/XYZ_USER_SERVICE_X1 APPLSYS

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_USER_SERVICE_F1

Test the patch

When XDF appliesthe table update, the revised column and index is added, and the EV will beregenerated to use the new revised column. After the FCET is created andapplied the revised column is populated with the new codes.

Multiple updates to the same table

When the sametable is patched multiple times, it is likely that the upgrade logic in asubsequent Forward Crossedition Trigger will require that the previous FCET hasbeen executed already. The previous FCET may have been applied in a previouspatching session, but it is also possible that the series of changes are mergedinto one rollup patch so that multiple FCETs for the same table are applied inthe same patching cycle. In order to guarantee correct ordering of FCETexecution, the database supports a "FOLLOWS" keyword in the trigger definition:

create or replacetrigger XYZ_USER_SERVICE_F2

before insert or update on &1..xyz_user_service

for each row forward crossedition

FOLLOWS XYZ_USER_SERVICE_F1

disable

...

When multiplechanges are made to the same table, each new FCET must be defined as followingthe previous FCET:

F2 follows F1

F3 follows F2

... and so on ...

To continue ourexample, lets imagine that we are adding a new service level to our app andexisting GOLD customers will automatically be promoted to the new level.

XYZ_USER_SERVICE

Name Null? Type

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

USER_ID NOT NULLNUMBER

-- PK, FK to FND_USER.USER_ID

SERVICE_TYPE NOT NULLVARCHAR2(8)

-- 'BRONZE' - cheap service

-- 'SILVER' - plus service

-- 'GOLD' - best service

==> -- 'PLATINUM' - VIP

COMMENTS VARCHAR2(1000)

SERVICE_STATUS NOT NULLVARCHAR2(8)

-- 'ENABLED' - service is active

-- 'DISABLED' - service is notactive.

Create the new column in your development database.

Again, we use a newrevised column to hold the new service type values. Add a new revised columnmanually in your development database.

alter tableAPPLSYS.XYZ_USER_SERVICE

add (SERVICE_TYPE#2 varchar2(8) default '*NULL*' not null)

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

Create a Forward Crossedition Trigger to populate the new column.

The FCET loads thenew column with upgrade service type codes. The logic will promote existingGOLD customers to VIP level. Note that the logic in this FCET depends on XYZ_USER_SERVICE_F1having run already. We indicate this dependency using the FOLLOWS clause.

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_F2

REM $Header$

REM XYZ_USER_SERVICE_X2.sql

REM Populate XYZ_USER_SERVICE.SERVICE_TYPE

SET VERIFY OFF;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR EXIT FAILURE ROLLBACK;

create or replace trigger XYZ_USER_SERVICE_F2

before insert or update on &1..XYZ_USER_SERVICE

for each row forward crossedition

follows XYZ_USER_SERVICE_F1 /* notice! */

disable

begin

if :new.service_type#1 = 'GOLD' then

:new.service_type#2 := 'VIP';

else

:new.service_type#2 := :new.service_type#1;

end if;

end;

commit;

exit;

You can apply andtest the FCET in the development database as follows:

sqlplus<apps_user>/<apps_password> @XYZ_USER_SERVICE_X2 APPLSYS

# note: the trigger will create with compilation error, that isOK

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_USER_SERVICE_F2

sqlplus <apps_user>/<apps_password>

exec ad_zd.finalize

exec ad_zd.cutover

exec ad_zd.cleanup

quit

Note the followingbehavior: When the XYZ_USER_SERVICE_F2 trigger is initially created, it willnot compile, because the trigger definition makes reference toXYZ_USER_SERVICE_F1 which no longer exists. This database behavior is correctedlater by the APPLY procedure. When you execute the AD_ZD_TABLE_APPLY script,the new "F2" trigger will be enabled and applied to all existingrows, but since the XYZ_USER_SERVICE_F2 is defined as followingXYZ_USER_SERVICE_F1, the APPLY procedure will automatically create an emptyXYZ_USER_SERVICE_F1 trigger to satisfy the reference and allow "F2"to compile.

Extract the updated table definition from your development database:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_USER_SERVICE

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_user_service.xdf

fnd/patch/115/sql/XYZ_USER_SERVICE_X2.sql

Manual apply phaseactions for the file system:

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

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

Manual apply phaseactions for the database:

xdfcmp.pl<applsys_user>/<applsys_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf<apps_user>/<apps_password>

sqlplus <apps_user>/<apps_password>@$FND_TOP/patch/115/sql/XYZ_USER_SERVICE_X2 APPLSYS

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_USER_SERVICE_F2

Test the patch.

When XDF appliesthe table update, the revised column and index will be added, and the EV willbe regenerated to use the new revised column. After the FCET is created andapplied the revised column will be populated with the new codes.

Section 4.2.2:Seed Data Tables

Seed Data Tablesmust implement a special feature to support Online Patching called EditionedData Storage. This feature allows a single seed data table to hold multiplecopies of the seed data: During online patching, the original seed data remainsin use by the Run Edition, and a separate copy of the data can be created forthe Patch Edition. Editioned Data Storage allows a loader to modify the PatchEdition copy of seed data without affecting the Run Edition. Seed data tablesare created and patched like ordinary tables with a few extra rules that willbe explained presently.

Is your tablereally a seed data table? The extra patching standards and runtime overheadassociated with seed data tables are best avoided, so please make sure that youdo not upgrade a table to be a seed data table unless it is truly necessary. Areal seed data table has ALL of the following properties:

The Table contains data that is "part of the application" and is delivered and maintained via application patching. More specifically:
- You (the application developer) create and deliver data in the table.
- You expect to maintain and patch the data along with the application code.
- Typically, you have a seed data loader for the purpose of delivering data updates.

The Table Data affects runtime application functionality or appearance
- Meaning the data is used by the code, rather than simply handled by the code. The data controls how the application operates.
- Business Reference data such as Parties or Products is not seed data.

The Table Data is predominately application seed data.
- There can be user-entered rows, but it is not appropriate to treat a high volume transaction table as a seed data table.
- Sample transaction or setup data is not seed data.

Create a new Seed Data Table

In this example,we add a new seed data table to the application to hold "servicetype" information. The standard service types are created and maintainedby application development, and so this table meets the definition of a seeddata table. The logical table structure is as follows:

XYZ_SERVICE_TYPES

Name Null? Type

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

SERVICE_TYPE NOT NULLVARCHAR2(8)

SERVICE_PRIORITY NOT NULL NUMBER

HOME_PAGE VARCHAR2(30)

Create the initial table definition in your development database.

This is done justlike an ordinary table. Remember that seed data tables should be stored in theAPPS_TS_SEED tablespace.

create tableAPPLSYS.XYZ_SERVICE_TYPES

(

SERVICE_TYPE VARCHAR2(8) not null,

SERVICE_PRIORITY NUMBER not null,

HOME_PAGE VARCHAR2(30)

)

tablespace APPS_TS_SEED

create unique index APPLSYS.XYZ_SERVICE_TYPES_U1

on APPLSYS.XYZ_SERVICE_TYPES ( SERVICE_TYPE )

tablespace APPS_TS_SEED

exec ad_zd_table.upgrade('APPLSYS', 'XYZ_SERVICE_TYPES')

Upgrade table to support Editioned Data Storage.

This is a newrequired step for seed data tables and is done by calling theAD_ZD_SEED.UPGRADE procedure.

execad_zd_seed.upgrade('XYZ_SERVICE_TYPES')

Now the table isofficially a Seed Data Table. The AD_ZD_SEED.UPGRADE procedure added a newcolumn to the table key that stripes the data by edition, along with varioussupporting objects. The loader for a seed data table must be coded to call theAD_ZD_SEED.PREPARE procedure before changing the content of the table in anOnline Patch, but no special action is required to update the table from theRun Edition. For this example, we can just put in some sample data directly.

insert intoXYZ_SERVICE_TYPES (service_type, service_priority, home_page)

values ('VIP', 0, 'VIP_HOME');

insert into XYZ_SERVICE_TYPES (service_type, service_priority, home_page)

values ('GOLD', 1, 'GOLD_HOME');

insert into XYZ_SERVICE_TYPES (service_type, service_priority, home_page)

values ('SILVER', 2, 'SILVER_HOME');

insert into XYZ_SERVICE_TYPES (service_type, service_priority, home_page)

values ('BRONZE', 3, 'BRONZE_HOME');

commit;

Create a loader for the seed data table:

The Seed DataLoader is typically implemented as a loader configuration file (LCT) for theFNDLOAD generic loader. This is created in the usual way, with one newaddition, which is to define the tables that must be prepared when seed data ispatched using the loader. Here is a simple LCT definition for the exampletable:

COMMENT ="dbdrv: exec fnd bin FNDLOAD bin &phase=daacheckfile:~PROD:~PATH:~FILE &ui_apps 0 Y UPLOAD @FND:patch/115/import/xyzst.lct@~PROD:~PATH/~FILE"

DEFINE XYZ_SERVICE_TYPE

KEY SERVICE_TYPE VARCHAR2(8)

BASE SERVICE_PRIORITY VARCHAR2(8)

BASE HOME_PAGE VARCHAR2(30)

END XYZ_SERVICE_TYPE

DOWNLOAD XYZ_SERVICE_TYPE

" select SERVICE_TYPE, to_char(SERVICE_PRIORITY), HOME_PAGE

from XYZ_SERVICE_TYPES

where (:SERVICE_TYPE is null or SERVICE_TYPE like :SERVICE_TYPE)"

UPLOAD XYZ_SERVICE_TYPE

" begin

update XYZ_SERVICE_TYPES

set SERVICE_PRIORITY = :SERVICE_PRIORITY,

HOME_PAGE = :HOME_PAGE

where SERVICE_TYPE = :SERVICE_TYPE;

if SQL%NOTFOUND then

insert into XYZ_SERVICE_TYPES

( SERVICE_TYPE, SERVICE_PRIORITY, HOME_PAGE)

values (:SERVICE_TYPE,to_number(:SERVICE_PRIORITY), :HOME_PAGE);

end if;

end; "

PREPARE XYZ_SERVICE_TYPE

TABLE XYZ_SERVICE_TYPES

Notice the PREPAREstatement at the end of the file. This statement tells the loader to preparethe 'XYZ_SERVICE_TYPES' table before attempting to load data for theXYZ_SERVICE_TYPE entity.

Extract the table definition from your development database:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_SERVICE_TYPES

Extract starting seed data from your development database:

FNDLOAD<apps_user>/<apps_password> 0 Y DOWNLOAD xyzst.lct xyzst_data.ldtXYZ_SERVICE_TYPE

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_service_types.xdf

fnd/patch/115/import/xyzst.lct

fnd/patch/115/import/US/xyzst_data.ldt

Manual apply phaseactions for the file system:

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

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

cp fnd/patch/115/import/US/* $FND_TOP/patch/115/import/US

Manual apply phaseactions for the database:

xdfcmp.pl<applsys_user>/<applsys_password>@$TWO_TASK $FND_TOP/patch/115/xdf/xyz_service_types.xdf<apps_user>/<apps_password>

FNDLOAD <apps_user>/<apps_password> 0 Y UPLOAD$FND_TOP/patch/115/import/xyzst.lct $FND_TOP/patch/115/import/US/xyzst_data.ldt

Test the patch.

When the patch isapplied, XDF first creates the initial table, and then automatically calls theAD_ZD_TABLE.UPGRADE. XDF also detects that the table definition is for a seeddata table, and calls the AD_ZD_SEED.UPGRADE procedure to install supportingobjects for Editioned Data Storage.

Once the seed datatable is in place, the FNDLOAD procedure will load data into the table. Theloader will automatically call the AD_ZD_SEED.PREPARE procedure for table to beloaded (although in this case the procedure will not do anything as there is norun edition copy of seed data).

When the patch iscomplete, verify that the seed data table definition and contents are asexpected.

Update a NOT NULL or Unique Indexed Column

If you patch anexisting column in a seed data table that either (a) has a NOT NULL constraintwith no default value, or (b) is part of a unique index, there is a newrequirement: In addition to writing the Forward Crossedition Trigger topopulate your revised column: You also need to code a Reverse CrosseditionTrigger to populate the original column when data is loaded in the PatchEdition. For example, suppose we need to increase the size of the SERVICE_TYPEcolumn from 8 to 30 bytes:

XYZ_SERVICE_TYPES

Name Null? Type

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

==> SERVICE_TYPE NOT NULLVARCHAR2(30)

SERVICE_PRIORITY NOT NULL NUMBER

HOME_PAGE VARCHAR2(30)

Create the revised column in your development database.

alter tableAPPLSYS.XYZ_SERVICE_TYPES

add (SERVICE_TYPE#1 varchar2(30) default '*NULL*' not null)

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

Create a Forward Crossedition Trigger to populate the revised column.

This is identicalto the technique used for ordinary tables. However, the SQL script that createsthe FCET will also create and apply a Reverse Crossedition Trigger.

Create a Reverse Crossedition Trigger to populate the original column.

The reversecrossedition trigger only fires when the table contents is changed from thepatch edition (such as during seed data loading). The purpose of the of theReverse Crossedition Trigger is to populate the old column in some way thatsatisfies the old NOT NULL or UNIQUE constraint. Following is an example of SQLscript that combines creation of both the FCET and RCET for a revised uniqueindexed column.

REM ---- CreateFCET+RCET ----

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_SERVICE_TYPES_F1

REM ---- Apply RCET ----

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_SERVICE_TYPES_R1

REM $Header$

REM XYZ_SERVICE_TYPES_X1.sql

REM Update XYZ_SERVICE_TYPES.SERVICE_TYPE to varchar2(30)

SET VERIFY OFF;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR EXIT FAILURE ROLLBACK;

-- FCET Definition

create or replace trigger XYZ_SERVICE_TYPES_F1

before insert or update on &1..XYZ_SERVICE_TYPES

for each row forward crossedition

disable

begin

:new.service_type#1 := :new.service_type;

end;

-- RCET Definition

create or replace trigger XYZ_SERVICE_TYPES_R1

before insert or update on &1..XYZ_SERVICE_TYPES

for each row reverse crossedition

disable

begin

:new.service_type := substrb(:new.service_type#1, 1, 8);

end;

commit;

exit;

You might havenoticed that the example reverse crossedition trigger logic may not satisfy theuniqueness constraint of the old column for new data that is longer than 8bytes. In cases where this is a concern, you can populate the old column withvalues from a sequence number, converted to an 8-byte string. The reversecrossedition trigger does not actually need to populate meaningful data in theold columns, it only needs ensure that database constraints on the old columnare satisfied when rows are loaded in the patch edition.

You can apply andtest the FCET/RCET triggers in a development database as follows:

sqlplus<apps_user>/<apps_password> @XYZ_SERVICE_TYPES_X1 APPLSYS

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_F1

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_R1

sqlplus <apps_user>/<apps_password>

exec ad_zd.finalize

-- test insert into table

insert into XYZ_SERVICE_TYPES

(service_type, service_priority, home_page)

values ('TEST_TYPE', 0, 'TEST');

select * from applsys.xyz_service_types

where service_type#1 = 'TEST_TYPE';

rollback;

exec ad_zd.cutover

exec ad_zd.cleanup

Since the reversecrossedition trigger is not applied to existing rows of the table, It isrecommended that you make a test insert into the table in order to verify thetrigger logic.

Extract the table definition from your development database:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_SERVICE_TYPES

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_service_types.xdf

fnd/patch/115/sql/XYZ_SERVICE_TYPES_X1.sql

fnd/patch/115/import/xyzst.lct

Manual apply phaseactions for the file system:

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

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

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

Manual apply phaseactions for the database:

xdfcmp.pl<applsys_user>/<applsys_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_service_types.xdf<apps_user>/<apps_password>

sqlplus <apps_user>/<apps_password>@$FND_TOP/patch/115/sql/XYZ_SERVICE_TYPES_X1 APPLSYS

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_F1

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_R1

Test the patch.

When applied to apatch edition, XDF creates the new revised column and index. Then thecrossedition triggers will be installed. The FCET will populate the new revisedcolumn. The RCET will populate the old column if any data is loaded into thetable in the patch edition. After cutover, the old index and crosseditiontriggers will be removed.

Section 4.2.3:Multiple updates to the same seed data table

As mentioned inthe preceding section for Tables, when you make second and subsequent updatesto the same table, each new Forward Crossedition Trigger must be created sothat it FOLLOWS the previous FCET. This rule applies to seed data tables aswell, but seed data tables have an additional requirement to specify orderingfor the second and subsequent Reverse Crossedition Triggers. Each new RCET mustbe created so that it PRECEDES the previous RCET, if any. The PRECEDES syntaxlooks like this:

create or replacetrigger XYZ_SERVICE_TYPES_R2

before insert or update on &1..xyz_user_service

for each row reverse crossedition

PRECEDES XYZ_SERVICE_TYPES_R1

disable

...

So each new FCETFOLLOWS the previous FCET.

F2 follows F1 ...

F3 follows F2 ...

... and so on ...

And each new RCETPRECEDES the previous RCET.

R2 precedes R1 ...

R3 precedes R2 ...

... and so on ...

For our codingexample, let us simply make a null change to the primary key column todemonstrate the technique:

XYZ_SERVICE_TYPES

Name Null? Type

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

SERVICE_TYPE NOT NULLVARCHAR2(30)

SERVICE_PRIORITY NOT NULL NUMBER

HOME_PAGE VARCHAR2(30)

Create the revised column in your development database.

alter tableAPPLSYS.XYZ_SERVICE_TYPES

add (SERVICE_TYPE#2 varchar2(30) default '*NULL*' not null)

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

Create a Forward Crossedition Trigger to populate the revised column.

The new FCET mustbe created so that it FOLLOWS the previous FCET.

Create a Reverse Crossedition Trigger to populate the original column.

The new RCET mustbe created so that it PRECEDES the previous RCET.

REM ---- CreateFCET+RCET ----

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_SERVICE_TYPES_F2

REM ---- Apply RCET ----

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_SERVICE_TYPES_R2

REM $Header$

REM XYZ_SERVICE_TYPES_X2.sql

REM Pretend update to XYZ_SERVICE_TYPES.SERVICE_TYPE attribute

SET VERIFY OFF;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR EXIT FAILURE ROLLBACK;

-- FCET Definition

create or replace trigger XYZ_SERVICE_TYPES_F2

before insert or update on &1..xyz_service_types

for each row forward crossedition

follows XYZ_SERVICE_TYPES_F1

disable

begin

:new.service_type#2 := :new.service_type#1;

end;

-- RCET Definition

create or replace trigger XYZ_SERVICE_TYPES_R2

before insert or update on &1..xyz_service_types

for each row reverse crossedition

precedes XYZ_SERVICE_TYPES_R1

disable

begin

:new.service_type#1 := :new.service_type#2;

end;

commit;

exit;

Apply and test the FCET/RCET triggers in a development database as follows:

sqlplus<apps_user>/<apps_password> @XYZ_SERVICE_TYPES_X2 APPLSYS

# both triggers create with compilation errors, no worries.

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_F2

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_R2

sqlplus <apps_user>/<apps_password>

exec ad_zd.finalize

-- test insert into table

insert into XYZ_SERVICE_TYPES

(service_type, service_priority, home_page)

values ('TEST_TYPE', 0, 'TEST');

select * from applsys.xyz_service_types

where service_type#1 = 'TEST_TYPE';

rollback;

exec ad_zd.cutover

exec ad_zd.cleanup

Extract the table definition from your development database:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_SERVICE_TYPES

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_service_types.xdf

fnd/patch/115/sql/XYZ_SERVICE_TYPES_X2.sql

Manual apply phaseactions for the file system:

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

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

Manual apply phaseactions for the database:

xdfcmp.pl<applsys_user>/<applsys_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_service_types.xdf<apps_user>/<apps_password>

sqlplus <apps_user>/<apps_password>@$FND_TOP/patch/115/sql/XYZ_SERVICE_TYPES_X2 APPLSYS

sqlplus <apps_user>/<apps_password> @$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLYXYZ_SERVICE_TYPES_F2

sqlplus <apps_user>/<apps_password>@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_R2

Section 4.2.4:Temporary Tables

A global temporarytable is a table that does not have permanent storage. Rows in a temporarytable are held in memory either for a single transaction or for a singlesession, and are not accessible outside of the current session. Temporarytables are normally used to hold interim or summarized results in order to improvethe performance of some other processing.

A global temporarytable is a non-editioned object with a special restriction compared to ordinarytables: A temporary table cannot be modified in any way during online patching.This is a database restriction: attempting to modify a temporary table while itis in use by any other session will result in an oracle error such as"ORA-14450: attempt to access a transactional temp table already inuse". Therefore, patching a temporary table definition requires a specialprocedure.

Create a Temporary Table

To create anddeliver the first version of a temporary table, you create the table in yourdevelopment environment, extract it using XDF, and include the XDF in yourpatch. This process should be the same as it was in classic downtime patching.

Create the temporary table in a development database. This is done using standard SQL*Plus. For example:

create globaltemporary table APPLSYS.XYZ_USER_GT

(

USER_ID NUMBER(15) not null,

USER_DATA VARCHAR2(10)

)

create index APPLSYS.XYZ_USER_GT_N1 on APPLSYS.XYZ_USER_GT(USER_ID)

create or replace synonym XYZ_USER_GT for APPLSYS.XYZ_USER_GT2;

Note that thetemporary table must be created in a product schema, not directly in the APPSschema. The APPS schema must contain a synonym that points to the temporarytable, which will serve as the permanent logical name of the temporary table.When the temporary table is be patched in the future, you will create a newtemporary table with a different name but the logical name (APPS synonym) willstay the same.

It is possible tocreate indexes on a temporary table. The index information will be includedwhen you extract the definition with XDF.

After manualcreation, you should validate that the temporary table definition is correctand works as expected.

Extract the temporary table definition to an XDF file. This is done as follows:

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_USER_GT

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_user_gt.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<applsys_user>/<applsys_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_gt.xdf <apps_user>/<apps_password>

Section 4.2.5:Revise an existing Temporary Table

When you make anystructural change to a temporary table or its indexes, it is not possible toalter the existing temporary table in place. Instead, you must create a newtemporary table with a different name, and then update the APPS synonym topoint at the new table.

Create the revised temporary table in a development database. This is done using standard SQL*Plus. For example:

create globaltemporary table APPLSYS.XYZ_USER_GT2

(

USER_ID NUMBER(15) not null,

JOB_TYPE VARCHAR2(8) not null,

USER_DATA VARCHAR2(10)

)

drop index APPLSYS.XYZ_USER_GT_N1;

create index APPLSYS.XYZ_USER_GT_N1

on APPLSYS.XYZ_USER_GT2 (USER_ID, JOB_TYPE);

create or replace synonym XYZ_USER_GT for APPLSYS.XYZ_USER_GT2;

You can keepindexes with their original names, even though the name of the underlingtemporary table has changed.

After this manualcreation, you should validate that the revised temporary table definition iscorrect and works as expected.

Extract the temporary table definition to an XDF file.

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_USER_GT2

Create the helper script.

At this time, XDFdoes not automatically manage the synonym (logical name) of a revised temporarytable. So we need to create a helper script that will be applied immediatelyfollowing the XDF. The helper script will fix up the original APPS synonym topoint to the revised temporary table.

REM dbdrv: sql~PROD ~PATH ~FILE \

REM dbdrv: none none none sqlplus &phase=tab+1 \

REM dbdrv: checkfile:~PROD:~PATH:~FILE &un_fnd

REM $Header$

REM @xyz_user_gt.sql <table_owner>

REM Set XYZ_USER_GT synonym to <table_owner>.XYZ_USER_GT2table

SET VERIFY OFF;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR EXIT FAILURE ROLLBACK;

-- drop XDF-generated synonym, which is not needed.

drop synonym XYZ_USER_GT2;

-- re-point original synonym at revised temporary table.

create or replace synonym XYZ_USER_GT for &1..XYZ_USER_GT2;

commit;

exit;

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_user_gt2.xdf

fnd/patch/115/xdf/xyz_user_gt.sql

Manual apply phaseactions for the file system:

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

Manual apply phaseactions for the database:

xdfcmp.pl<applsys_user>/<applsys_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_gt2.xdf<apps_user>/<apps_password>

sqlplus <apps_user>/<apps_password> @$FND_TOP/patch/115/xdf/xyz_user_gtAPPLSYS

Test the patch.

When the XDF isapplied to the Patch Edition, XDF creates the revised temporary table and index(using an alternate name for the index). The helper SQL script then resets thepatch edition APPS table synonym to point to the new revised temporary table.In the Run Edition, the APPS synonym continues to point at the originaltemporary table, which remains undisturbed. During cutover, the old index isdropped and the new index is renamed to the required name.

Section 4.2.6:Materialized Views

On an editioneddatabase, a materialized view (MV) definition currently may not referenceeditioned objects. However, application developers must define the materializedview query in terms of editioned APPS table synonyms and views. To work aroundthe restriction on materialized views, the Oracle E-Business Suite OnlinePatching solution implements a new Effectively-Editioned Materialized Viewcompound object. The developer-specified query is stored in an ordinary view, calledthe Logical View. The Materialized View is then generated from the LogicalView, using a new database feature that translates the logical query into anequivalent, edition-legal "implementation query". The followingsection describes how this procedure is done.

Create a new Materialized View

On an editioneddatabase, you can no longer create a materialized view directly.

Create the Logical View in your development database.

The Logical Viewis an ordinary database view that implements the desired query. The LogicalView name must be the desired materialized view name with a '#' characterappended to it. In this example, we intend to create a materialized view calledXYZ_SCHEMAS_MV that presents some information about the database schemasassociated with Oracle E-Business Suite. We start by creating the logical viewXYZ_SCHEMAS_MV#:

create or replaceview XYZ_SCHEMAS_MV# as

select upper(oracle_username) USERNAME,

decode(read_only_flag,

'C', 'pub', 'E','applsys', 'U', 'apps') USERTYPE

from fnd_oracle_userid

where read_only_flag in ('C', 'E', 'U');

While it isacceptable for the logical view to depend on editioned synonyms and views, itmust not depend on editioned PL/SQL functions, such as those in the Oracle E-BusinessSuite APPS schema (built-in PL/SQL functions such as "upper" areacceptable). Test the Logical View to ensure that its shape and results arecorrect.

select * from XYZ_SCHEMAS_MV#;

USERNAME USERTYP

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

APPLSYS applsys

APPS apps

APPLSYSPUB pub

Generate the Materialized View.

On an editioneddatabase, materialized views are generated from their corresponding logicalviews using the AD_ZD_MVIEW.UPGRADE procedure.

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

In this example,the UPGRADE procedure detects that materialized view is missing and generatesit from the Logical View. The Materialized View definition is generated bytransforming the Logical View query into an equivalent implementation querythat directly references the underlying tables and columns. You can see theresulting MV implementation objects using the "ADZDSHOWMV" utilityscript:

sqlplus<apps_user>/<apps_password> @ADZDSHOWMV XYZ_SCHEMAS_MV

-- MV Objects

OBJECT_NAME OBJECT_TYPE STATUS DESCRIPTION

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

XYZ_SCHEMAS_MV MATERIALIZED VIEW VALID Materialized View

XYZ_SCHEMAS_MV TABLE VALID Container Table

XYZ_SCHEMAS_MV# VIEW VALID Logical View

-- MV Properties

MV_NAME REFERS REFRESH_STALENESS

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

XYZ_SCHEMAS_MV DEMAND FORCE FRESH

The MVimplementation query should never be changed directly. It must always begenerated from the logical view using the UPGRADE procedure. The MVimplementation query can be difficult to read and normally the developer willnot need to look at it. But it is worth examining the implementation query ofour example to understand what the transformation is doing. The formatted MVimplementation query for our example logical view is as follows:

CREATEMATERIALIZED VIEW "APPS"."XYZ_SCHEMAS_MV"

("USERNAME", "USERTYPE") AS

SELECT

UPPER("A1"."ORACLE_USERNAME")"USERNAME",

DECODE("A1"."READ_ONLY_FLAG",

'C','pub','E','applsys','U','apps')"USERTYPE"

FROM "APPLSYS"."FND_ORACLE_USERID" "A1"

WHERE "A1"."READ_ONLY_FLAG"='C'

OR "A1"."READ_ONLY_FLAG"='E'

OR "A1"."READ_ONLY_FLAG"='U'

Notice that whilethe logical view references the APPS FND_ORACLE_USERID table synonym, thematerialized view references the base table directly. The generated MV isautomatically maintained by online patching whenever the logical view oranything it depends on is changed in a patch. Once generated, you can query orrefresh the MV as usual.

select * from XYZ_SCHEMAS_MV;

USERNAME USERTYP

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

APPLSYS applsys

APPS apps

APPLSYSPUB pub

Extract the MV definition using XDF.

Once the generatedMV has been tested, you can extract the definition using XDF. XDF has beenextended to automatically substitute the MV Logical View query for theimplementation query in extracting an MV definition.

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_SCHEMAS_MV

Create the patch.

Patch Files:

fnd/patch/115/xdf/xyz_schemas_mv.xdf

Manual apply phaseactions for the file system:

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

Manual apply phaseactions for the database:

xdfcmp.pl<apps_user>/<apps_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_schemas_mv.xdf

Note that unliketables, the MV definition is applied in the APPS schema, so the xdfcmp.plsyntax is slightly different.

Test the patch.

When the XDF fileis applied on a target database, XDF will automatically create the Logical Viewand then use the UPGRADE procedure to generated the Materialized View on thetarget database. The generated materialized view query will vary depending on thedefinitions of the objects that the Logical View depends on in the specifictarget database. In this case, the created MV is new (does not already exist inthe target database) so XDF creates the MV immediately. The case of changing anexisting MV is covered in the next section.

Section 4.2.7:Change a Materialized View

To change anexisting materialized view in a development database, the developer willreplace the Logical View with an updated definition and then regenerate theMaterialized View implementation. The patching procedure is the same.

Replace the Logical View in your development database.

To change thedefinition of a materialized view, you first replace the definition of thecorresponding logical view in your development database. This is done using SQLDDL as usual. In this example, we create a new view that our MV definition willreference, and then update the XYZ_SCHEMAS_MV# Logical View to use the newview. When you are satisfied with the results of the updated Logical View,remember to call the AD_ZD_MVIEW.UPGRADE procedure to regenerate materializedview implementation.

/* new view forschema type information - xyz_schema_types.sql */

create or replace view XYZ_SCHEMA_TYPES as

select lv.lookup_code CODE

, lv.meaning MEANING

from fnd_lookup_values lv

where lv.lookup_type = 'ORACLEID_PRIVILEGE_INVIS'

and lv.language = 'US';

/* change XYZ_SCHEMAS_MV logical view to use XYZ_SCHEMA_TYPES view */

create or replace view XYZ_SCHEMAS_MV# as

select fou.oracle_username USERNAME

, st.meaning USERTYPE

from fnd_oracle_userid fou, xyz_schema_types st

where fou.read_only_flag in ('C', 'E', 'U')

and st.code = fou.read_only_flag;

/* test logical view as needed */

select * from XYZ_SCHEMAS_MV#;

/* regenerate materialized view implementation */

exec ad_zd_mview.upgrade('APPS', 'XYZ_SCHEMAS_MV')

/* test materialized view as needed */

select * from XYZ_SCHEMAS_MV;

During an onlinepatching cycle, out-of-date MV regeneration happens automatically during thecutover phase. But when working in the run edition of a development database,you will need to execute MV regeneration yourself when you are ready using theAD_ZD_MVIEW.UPGRADE procedure.

Extract the MV definition.

xdfgen.pl<apps_user>/<apps_password>@$TWO_TASK XYZ_SCHEMAS_MV

Create the patch.

Patch files:

fnd/patch/115/sql/xyz_schema_types.sql

fnd/patch/115/xdf/xyz_schemas_mv.xdf

Manual apply phaseactions for the file system:

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

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

Manual apply phaseactions for the database:

sqlplus<apps_user>/<apps_password>@$FND_TOP/patch/115/sql/xyz_schema_types

xdfcmp.pl <apps_user>/<apps_password>@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_schemas_mv.xdf

Materialized View Online Patching - how it works

Aneffectively-editioned Materialized View includes both a Logical View (managedby the developer) and a Materialized View (generated by Online Patching). TheLogical View is an ordinary database view, and is therefore an editioned objectthat can be changed in the Patch Edition without affecting the Run Edition. Butthe generated Materialized View is a non-editioned object, meaning thedefinition and content of the materialized view is shared across all editions.In order to avoid breaking the running application during an online patch, thesystem must defer materialized view regeneration until the cutover phase, whenthe application is down.

During onlinepatching, materialized view regeneration happens automatically during the cutoverphase whenever the materialized view implementation is out-of-date with respectto the Logical View. A materialized view implementation becomes out-of-date if

the Logical View is patched

anything which the Logical View depends on is patched or recompiled

any seed data table the materialized view depends on is prepared for loading new content in the patch edition

Materialized viewcutover processing will take care to avoid regenerating materialized viewsunless there is an actual change required in the MV implementation. Whenregenerating the MV implementation, the system will try to preserve theexisting MV container table (with its data), but if the shape of the containertable must change, then the container table is dropped and recreatedautomatically.

Section 5: Deploying Custom ApplicationTier Objects

This sectiondescribes general procedures for deploying custom application tier objects. Usethese procedures together with any component-specific steps for the componentyou are customizing, as described in .

Section 5.1: Setting Up a Production Environment

First, you mustset up your custom application on your production environment.

On the production environment, run the adop prepare phase.

If your customizations include custom Java or BC4J code or extensions, apply the following patches to the production environment.
- 17217965:R12.TXK.C (TEMPLATE CHANGE REQUIRED TO UPLOAD THE CLASS FILES RELATED TO CUSTOMIZATIONS)
- 17217772:R12.AD.C (NEED UTILITY TO GENERATE CUSTOMALL.JAR)

Connect to the patch edition file system on the production environment. See .

Invoke adsplice from the patch file system to register your custom application. For more information about using adsplice, see .

Run the adop cutover phase.

Section 5.2: Deploying Customizations on a ProductionEnvironment

To deploycustomizations, perform the following steps:

On the production environment, run the adop prepare phase.

Connect to the patch edition file system on the production environment.

Copy the custom files to the appropriate directory on the patch edition file system. See the component-specific steps in .

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 .

If necessary, use the appropriate utility for your product or component to import or upload the custom files to the database. See the component-specific steps in .

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 .

Run the adop cutover phase.

Section 5.3: Running the adcgnjar Utility

Use the adcgnjarutility for any custom Java or BC4J code for Oracle Application Framework,Oracle CRM Technology Foundation (JTT), Oracle Web Applications DesktopIntegrator (BNE), custom servlets, or other custom Java code. This utilitygenerates and signs a file named customall.jar file containing the custom Javaand BC4J code and extensions. The customall.jar file is included in theebsProductManifest.xml so that the customall.jar will be in the CLASSPATHthrough the ebs-product shared library.

The adcgnjarutility does not require any parameters on the command line. Whenprompted, enter the user name and password of the APPS user.

The utilityperforms the following steps:

Creates a temporary custom.zip file that contains all the directories under $JAVA_TOP except the oracle, META-INF, and policies directories.

Generates and signs the customall.jar file with the contents of the custom.zip file.

Deletes the temporary custom.zip file.

Section 5.4: Adding Entries to the Custom SynchronizationDriver File

You should addentries for all your custom files to the custom synchronization driver filelocated at $APPL_TOP_NE/ad/custom/adop_sync.drv ( %s_ne_base%/EBSapps/appl/ad/custom/adop_sync.drv). The adoputility uses this driver file to synchronize files between the run file systemand the patch file system.

Add your entriesin the section marked by the '#Begin Customization' and '#End Customization'comments.

When adding yourentries, follow the syntax of the examples provided in the %s_adtop%/admin/template/adop_sync_drv.tmp templatefile. For example, if you have custom java class files under the $JAVA_TOP/<Companyidentifier>/* directory, and if all the files under this directory needto be synchronized between the patch file system and the run file system, thenadd the following entry in the custom synchronization driver file:

rsync -zr%s_current_base%/EBSapps/comn/java/classes/<Company identifier> %s_other_base%/EBSapps/comn/java/classes

You can usecontext variables in the entries you add. The syntax for a context variableis: %s_sample_var%

Any paths youinclude in your entries should be specified relative to s_current_base and s_other_base.

Section 5.5:Deploying Java Files at Non-Standard Location(s) for Custom Products

Oracle E-BusinessSuite standards emphasize having java files that pertain to custom applicationsat $JAVA_TOP/<Companyidentifier>/* .

When users choosea different location (one other than the standard location given above), fortheir business requirements, they may need to create a custom jar file manuallythat contains the custom application's java files at the non-standardlocation(s) and make this custom jar file available for the WebLogic Server topick up.

When custom javafiles are placed in one of the Oracle shipped application's directories, forexample,

$JAVA_TOP/oracle/apps/fnd

$JAVA_TOP/oracle/apps/ad, and so o

then, the customjar file creation is not necessary. Instead, users need to run "Generateproduct JAR files" from adadmin which will regenerate product jar filesfor Oracle applications and that will ensure the custom java files, placed inOracle shipped application's directories, are on board.

When custom javafiles are placed in any non-standard locations, for example,

$JAVA_TOP/oracle/<cust_prod>

$JAVA_TOP/oracle/java/<cust_prod> etc.

then the customjar file must be created manually and it must be made available for WebLogic topick up. Detailed steps for this procedure are given below.

Creating acustom jar file and making it available:

Create a temporary custom.zip file which contains all the custom application's directories/files at the non-standard location. The commands are:
1. cd $JAVA_TOP
2. zip -r customprod.zip <directory list> where the <directory list> is the list of all the directory paths, relative to $JAVA_TOP, for custom application's java files at the non-standard location.

Generate and sign the customprod.jar file. Command: adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass <KeyStore Password> -keyPass <Key Password>

Delete the temporary customprod.zip. Command: rm $JAVA_TOP/customprod.zip

Follow the steps below to make the custom jar file available for WebLogic Server:

Back up the existing <FND_TOP>/admin/template/ebsProductManifest_xml.tmp

Modify <FND_TOP>/admin/template/ebsProductManifest_xml.tmp to add the entry below for customprod.jar (after customall.jar):
<library>customprod.jar</library>

Run AutoConfig.

Bounce the middle-tier services.

NOTE: These changes will be lost if ebsProductManifest_xml.tmp is patched in future; changes will need to be done again.

In order to to synchronize the changes (during the next prepare phase) between both the file systems fs1 and fs2, follow the steps below: The custom synchronization driver file, located at<APPL_TOP_NE>/ad/custom/adop_sync.drv, should be used in these steps. This file has the required documentation on how to put an entry in for a file that needs to be synchronized between the two file systems.

If there are custom java class files under <JAVA_TOP>/oracle/<cust_prod>/* directory and if the files under this directory needs to be synchronized between fs1 and fs2 , then put the following entry in the custom synchronization driver file as below:
cp -r %s_current_base%/EBSapps/comn/java/classes/oracle/<cust_prod> %s_other_base%/EBSapps/comn/java/classes/oracle

To copy the custom jar file, add the following entry:
cp %s_current_base%/EBSapps/comn/java/classes/customprod.jar %s_other_base%/EBSapps/comn/java/classes

To synchronize the custom changes done to the template, add the entry below:
cp %s_current_base%/EBSapps/appl/fnd/12.0.0/admin/template/ebsProductManifest_xml.tmp %s_other_base%/EBSapps/appl/fnd/12.0.0/admin/template

After changes are synchonized, ensure Autoconfig is run for the latest template changes to take effect.

Section 6: Component-Specific Steps forApplication Tier Objects

This section listsspecific steps required for customizations to particular components orproducts.

Section 6.1: Concurrent Programs

Section 6.1.1:Overview

There are avariety of possible customizations for concurrent programs. Examples include:

Custom concurrent programs to meet specific business requirements.

Customizations to the Oracle Forms-based Submit Requests window. For example, you can give the Submit Requests window a different title, and define the form so that it allows users to select only those reports or concurrent programs belonging to a request group to which you have assigned a code.

Customizing the Submit Requests page in Oracle Application Framework. For example, you can customize this page to submit a single request or group of requests, such as a Generate Payroll report.

Section 6.1.2:Types of files and their locations

Customizations tothe user interface in Oracle Forms or Oracle Application Framework are subjectto the customization rules for those interfaces. For program executables, thefile locations are as listed below.

SQL*Plus and PL/SQL : $<PROD>_TOP/$APPLSQL or $<PROD>_TOP/sql

PL/SQL stored procedures : Stored in the database

Oracle Reports : $<PROD>_TOP/reports/<LANG>

SQL*Loader : $<PROD>_TOP/$APPLBIN

C : $<PROD>_TOP/bin

Perl : $<PROD>_TOP/bin

Java File : $CLASSPATH

In addition, thefile 'afcpprog.lct' is used to upload metadata into the database.

Section 6.1.3:Developing Concurrent Program Files

Follow the stepsin to developyour custom files.

To develop PL/SQL concurrent programs, use a SQL editor connected to the run edition environment.

To develop Java concurrent programs, use a Java editor. Ensure that your environment has been set up according to the instructions in . Then copy the Java class files to the $JAVA_TOP/<Company identifier>/* directory.

Log in to the Oracle E-Business Suite user interface, navigate to System Administrator > Concurrent > Program, and define the metadata for the concurrent programs.

Source the run edition environment and download the concurrent program metadata to an LDT file by invoking the Generic Loader (FNDLOAD) utility from the run edition environment with the afcpprog.lct configuration file. See: Using Loaders, Oracle E-Business Suite Setup Guide. For example:

FNDLOAD<apps_username>/<apps_password>@<service name> 0 Y DOWNLOAD$FND_TOP/patch/115/import/afcpprog.lct <ldt filename>.ldt PROGRAMAPPLICATION_SHORT_NAME=<CUSTOM_TOP>

Save a local copyof the LDT file.

Save local copies of your .pls files and Java class files.

Section 6.1.4:Deploying Concurrent Program Files

Follow the stepsin to deployyour custom files.

Copy your custom files to the following locations:
- Copy the *.ldt files to the $<CUSTOM>_TOP/patch/115/import/<LANG>/ folder.
- Copy the *.pls files to the $<CUSTOM>_TOP/patch/115/sql folder.
- Copy the Java class files to the $JAVA_TOP/<Company identifier>/* folder.

Invoke the the Generic Loader (FNDLOAD) utility with the afcpprog.lct configuration file from the patch edition environment to upload the concurrent program metadata. See: Using Loaders, Oracle E-Business Suite Setup Guide.

Compile any custom PL/SQL objects in the database from the files saved in the $<CUSTOM>_TOP/patch/115/sql folder on the patch file system.

6.2: Forms

Section 6.2.1:Overview

Custom forms aredefined as those forms created by the customer or shipped Oracle E-BusinessSuite forms modified by the customer. Forms personalizations are defined ascustomer-created metadata that is used at runtime to control the look and/orbehavior of a form at runtime. This metadata is created using thePersonalizations form (FNDCUSTM.fmb). The personalizations metadata can bedownloaded into an LDT file using FNDLOAD and affrmcus.lct. The metadata LDTfile can be uploaded using the corresponding LCT file.

Section 6.2.2:Types of Files

Forms (.fmb and .fmx files)

Forms Libraries (.pll and .plx files)

Forms personalizations (controlled by metadata LCT/LDT files)

All the form .fmbfiles are staged under $AU_TOP/forms/<LANG>, for example,$AU_TOP/forms/US. The compiled forms (.fmx files) are staged under$PROD_TOP/forms/<LANG>, for example, $FND_TOP/forms/US.

In the case ofcustom forms created by a customer, the .fmb files are staged under$AU_TOP/forms/US. The compiled forms (fmx files) are staged under $<CUSTOM>_TOP/forms/US.

Section 6.2.3:Deploying Forms Files

In addition tothese instructions, follow the steps in .

Forms (.fmb and .fmx files)

The generalinstructions for compiling forms in Release 12.2.2 and higher are as follows:

Source the patch edition environment.

Stage the fmb under $AU_TOP/forms/US. Verify the $FORMS_PATH environment variable. FORMS_PATH must contain $AU_TOP/resource and $AU_TOP/forms/<LANG> (if for US this would be $AU_TOP/forms/US). If these directories are not set under $FORMS_PATH, set FORMS_PATH accordingly.

Compile the .fmb file as follows using MYCUSTOM.fmb as an example:

cd $AU_TOP/forms/US

frmcmp_batch MYCUSTOM.fmb <apps_username>/<apps_password>output_file=$CUSTOM_TOP/forms/US/MYCUSTOM.fmx compile_all=special

Here is an exampleusing a version FNDSCAUS.fmb that a customer has modified (customized).

cd $AU_TOP/forms/US

frmcmp_batch FNDSCAUS.fmb <apps_username>/<apps_password>output_file=$FND_TOP/forms/US/MYCUSTOM.fmx compile_all=special

Forcustomer-created forms, the output_file should point to $<CUSTOM>_TOP/forms/<LANG>.

Forcustomer-modified product Oracle E-Business Suite forms, the output_file shouldpoint to $PROD_TOP/forms/<LANG>.

You need to sourcethe patch edition environment and stage any customer-created or -modifiedOracle E-Business Suite form under $AU_TOP/forms/<LANG> on the runedition file system. Next, you need to compile the form as directed above. Thenbring the patch edition file system online by running the adop cutover phase,and repeat the stage and compile steps. This procedure should keep the twoRelease 12.2 file systems in synchronization.

Form Libraries (.pll and .plx files)

All pll and plxfiles are staged under $AU_TOP/resource. Customer-created .pll and .plx filesalso reside under $AU_TOP/resource. For any changes made to CUSTOM.pll, as wellas customer-created .pll files, the following steps need to be done to deploythem in Release 12.2.

Source the patch edition environment.

Stage the file under $AU_TOP/resource. Verify the $FORMS_PATH environment variable. FORMS_PATH must contain $AU_TOP/resource directory. If the $FORMS_PATH is not correct, set FORMS_PATH accordingly.

Compile the .pll as follows using CUSTOM.pll as an example:

cd$AU_TOP/resource

frmcmp_batch CUSTOM.pll <apps_username>/<apps_password>module_type=library compile_all=special

Note that the .plxfile created from the above command will be located under $AU_TOP/resource.

Stage and compilethe .pll files as directed above. After this step is completed, bring the patchedition file system online by running the adop cutover phase, and repeat thestage and compile steps. This procedure should keep the two Release 12.2 filesystems synchronized with regards to the .pll and .plx files.

Form personalization (controlled by metadata LCT/LDT files)

Forany form personalizations, you must download the metadata you have created intoan .ldt file using the affrmcus.lct and FNDLOAD.

Becauseaffrmcus.lct has PREPARE and TABLE statements, FNDLOAD should handlepropagation of the metadata to the patch edition file system.

Folders Configuration Customizations (controlled by metadata LCT/LDTfiles)

For folders, usethe Folder file for FNDLOAD. These files are documented in the OracleE-Business Suite Setup Guide, Part No. E22953, in the "UsingLoaders" chapter. Note that fndfold.lct does NOT have PREPARE statementsbecause fndfold.lct is for customer use only and there are no shipped .ldtfiles that call fndfold.lct.

To download allfolders:

FNDLOADusername/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/fndfold.lct<name of file>.ldt FND_FOLDERS

To upload folders:

FNDLOADusername/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/fndfold.lct<name of file>.ldt

Section 6.3: Oracle Application Framework

Other sections inthis document may use the terms personalization, customization, and extensioninterchangeably; however, for Oracle Application Framework please note thatthese terms mean three different things. See: Deploying CustomerExtensions, Oracle Application Framework Developer's Guide (availablefrom ) and Personalizing Your Pages andPortlets, Oracle Application Framework Personalization Guide.

Section 6.3.1: OA Framework Personalizations: Types of Files

Once you create apersonalization, OA Framework inserts the metadata into the relevant Oracle MDSrepository tables. You can export the MDS metadata for the personalizationin the form of an XML or XLIFF file using the XMLExporter or XLIFF Extractorutilities, respectively.

You can run theXMLImporter or XLIFF Importer utility from the command line or from theFunctional Administrator responsibility to import the personalization in theXML/XLIFF file into another MDS repository and deploy the personalizationimmediately. See: Deploying Personalizations,Oracle Application FrameworkPersonalization Guide and Translating Personalizations, OracleApplication Framework Personalization Guide. If you have Oracle JDeveloperOA Extension, you may alternatively use the import.bat file or the import shellscript that is packaged with the JDeveloper IDE, located in thejdevbin\oaext\bin directory of the JDeveloper install area. The batch file andshell script each set up the classpath, path and environment for you. Just typingimport without any parameters will give help about its usage.

To deploy apersonalization via a patch, follow the instructions in below. Note that in this case, the personalizationdoes not become available until after the cutover.

Section 6.3.2: Deploying OA FrameworkPersonalizations Via a Patch

To deploy yourpersonalization via a patch:

On the source Oracle E-Business Suite instance where you create your personalization:
1. Source the run edition environment.
2. Export the personalized MDS metadata using the XMLExporter or XLIFF Extractor utility. See: Deploying Personalizations, Oracle Application Framework Personalization Guide and Translating Personalizations, Oracle Application Framework Personalization Guide. If you have Oracle JDeveloper OA Extension, you may alternatively use the export.bat / xliffextract.bat files or the export / xliffextract shell scripts that are packaged with the JDeveloper IDE, located in the jdevbin\oaext\bin directory of the JDeveloper install area. The batch files and shell scripts each set up the classpath, path and environment for you. Just typing export or xliffextract without any parameters will give help about its usage.

On the target Oracle E-Business Suite instance:
1. Perform the setup steps described in .
2. Run the adop prepare phase.
3. Source the patch edition environment.
4. Copy the exported XML/XLIFF file from to the $<CUSTOM>_TOP/mds directory, where $<CUSTOM>_TOP refers to your custom product top.
5. Invoke the XMLImporter or XLIFFImporter utility on the Patch file system to load the contents of the XML/XLIFF file to the MDS repository.

Note: Running theXLIFFImporter manually will make the personalization available immediately.

1. Follow the instructions in to add an entry of the XML file into the custom synchronization driver file. This ensures that the custom files are synchronized between the Run and Patch file systems the next time you run the adop prepare phase.
2. Run the adop cutover phase.

Section 6.3.3: OA Framework Business Logic Extensions: Types of Files

OA Extension Controller Java files

BC4J XML files

BC4J Substitutions

JRAD XML files

Section 6.3.4: Developing OA Framework Business Logic Extensions

To developbusiness logic extensions:

Use Oracle JDeveloper with OA Extensions to create your business logic extensions in the Source system. See: Extending OA Framework Applications, Oracle Application Framework Developer's Guide, available from .

Follow the initial setup instructions described in .

Perform the following steps in the Run file system:
1. Source the run edition environment.
2. For OA Extension Controller Java class file changes and BC4J XML file changes, copy the .class and .xml files to $JAVA_TOP/<Company identifier>/*.
3. Run the adcgnjar utility, as described in .
4. For BC4J substitutions and JRAD XML file changes, copy the JPX and XML files to the $<CUSTOM>_TOP/mds directory, where $<CUSTOM>_TOP refers to your custom product top.
5. Invoke the JPXImporter and XMLImporter utilities in the Run file system to load the contents of the JPX and XML files to the MDS repository. For information on these utilities, refer to the Oracle Application Framework Developer's Guide (available from ). If you have Oracle JDeveloper OA Extension, you may alternatively use the jpximport.bat file, import.bat or the import shell script that is packaged with the JDeveloper IDE, located in the jdevbin\oaext\bin directory of the JDeveloper install area. The batch files and shell script each set up the classpath, path and environment for you. Just typing jpximport or import without any parameters will give help about its usage.
6. Follow the instructions in to add an entry of the XML file into the custom synchronization driver file. This ensures that the custom files are synchronized between the Run and Patch file systems the next time you run the adop prepare phase.

Section 6.3.5: Deploying OA Framework Business Logic Extensions

To deploy businesslogic extensions:

Follow the initial setup instructions in ..

Run the adop prepare phase.

Source the patch edition environment.

For OA Extension Controller Java class file changes and BC4J XML file changes, copy the .class and .xml files to $JAVA_TOP/<Company identifier>/*.

Run the adcgnjar utility, as described in .

For BC4J substitutions and JRAD XML file changes, copy the JPX and XML files to the $<CUSTOM>_TOP/mds directory, where $<CUSTOM>_TOP refers to your custom product top.

Invoke the JPXImporter and XMLImporter utilities in the Patch file system to load the contents of the JPX and XML files to the MDS repository. For information on these utilities, refer to the Oracle Application Framework Developer's Guide (available from ). If you have Oracle JDeveloper OA Extension, you may alternatively use the jpximport.bat file, import.bat or the import shell script that is packaged with the JDeveloper IDE, located in the jdevbin\oaext\bin directory of the JDeveloper install area. The batch files and shell script each set up the classpath, path and environment for you. Just typing jpximport or import without any parameters will give help about its usage.

Follow the instructions in to add an entry of the XML file into the custom synchronization driver file. This ensures that the custom files are synchronized between the Run and Patch file systems the next time you run the adop prepare phase.

Run the adop cutover phase.

Section 6.4: Oracle CRM Technology Foundation

Section 6.4.1:File Types

Custom files forOracle CRM Technology Foundation (JTT) can be of the following types:

html, and so on

Section 6.4.2:Developing JTT Customizations

Follow the stepsin .

To testcustomizations in a development environment, perform the following steps:

Copy the newly created js, jsp, css, xss, xsl, htm, html and other files to the $OA_HTML/* directory.

To render custom jsp files in the browser, ensure that the profile option "Allow Unrestricted JSP Access" is set to "Yes".

Develop Java class files using any Java editor tool and copy the Java class files to the $JAVA_TOP/<Company identifier>/* directory.

You should alsosave local copies of all your custom files. From the development environment,source the run edition file system and then save the files.

Section 6.4.2:Deploying JTT Customizations

First, ensure you have performed the setup steps in .

Log into the Oracle E-Business Suite user interface running from the run edition environment and ensure that the profile option "Allow Unrestricted JSP Access" is set to "Yes".

Then follow the steps in .
- Copy any custom Java class files to the $JAVA_TOP/<Company identifier>/* directory.
- Copy your other custom files, including js, jsp, css, xss, xsl, htm, html, and other files to the $<CUSTOM>_TOP/html/* directory and mirror copy the same files to the $OA_HTML/* directory.

Section 6.5: Oracle Web Applications Desktop Integrator

Section 6.5.1:Developing Oracle Web Applications Desktop Integrator Customizations

You can createcustom integrators using Oracle E-Business Suite Desktop Integration Framework.You can create custom integrators for seeded Oracle E-Business Suiteapplications or for your own custom application. All objects created in theintegrator metadata are marked with the application ID and are uniquelyidentified by a combination of the application ID and the object's internalcode name. See: Oracle E-Business Suite Desktop Integration FrameworkDeveloper's Guide.

Follow the stepsin to developyour custom files.

Use Oracle E-Business Suite Desktop Integration Framework user interface to define your custom integrator metadata.

If your integrator uses any custom PL/SQL packages and functions, you can develop these using a SQL editor while connected to the run edition environment.

If your integrator uses any custom Java classes, then develop these in a Java editor and copy the Java class files to the $OA_HTML/WEB-INF/lib/* directory and to the $JAVA_TOP/<Company identifier>/* directory.

Source the run edition environment and download the integrator metadata to an LDT file by invoking the Generic Loader (FNDLOAD) utility from the run edition environment with the bneintegrator.lct configuration file. Save a local copy of the LDT file. See: Loading Integrator Definitions, Oracle E-Business Suite Desktop Integration Framework Developer's Guide.

Save local copies of your .pls files and Java class files.

Section 6.5.2:Deploying Oracle Web Applications Desktop Integrator Customizations

Follow the stepsin to deployyour custom files.

Copy your custom files to the following locations:
- Copy the *.ldt files to the $<CUSTOM>_TOP/patch/115/import/<LANG>/ folder.
- Copy the *.pls files to the $<CUSTOM>_TOP/patch/115/sql folder.
- Copy the Java class files to the $JAVA_TOP/<Company identifier>/* folder.

Invoke the the Generic Loader (FNDLOAD) utility with the bneintegrator.lct configuration file from the patch edition environment to upload the integrator metadata. See: Loading Integrator Definitions, Oracle E-Business Suite Desktop Integration Framework Developer's Guide.

Compile any custom PL/SQL objects in the database from the files saved in the $<CUSTOM>_TOP/patch/115/sql folder on the patch file system.

Note: Oracle does notsupport custom integrators created through PL/SQL APIs rather than throughOracle E-Business Suite Desktop Integration Framework. However, if you have acustom integrator created through APIs, you can follow the same general stepslisted in this document to deploy it in Release 12.2.

Section 6.6: Oracle Workflow

In OracleWorkflow, you can create the following types of customizations:

Workflow processes
- Customizations to seeded workflow processes
- New custom workflow processes

Business events and event subscriptions
- Customizations to seeded business events and event subscriptions
- New custom business events and event subscriptions

The followingsections describe how to develop and deploy each of these types of customizations.For more information, see the Oracle Workflow Developer's Guide. Inparticular, see the Customization Guidelines section in Appendix C.

Section 6.6.1:Customizing Workflow Processes

Developing Workflow Process Customizations

For workflows seeded by Oracle E-Business Suite products, check your product-specific documentation to determine whether any customizations are supported or required. If so, you can use the Oracle Workflow Builder client tool to edit the workflow definition. You can access the seeded workflow definition by either of the following methods:
- Use Oracle Workflow Builder to open a copy of the *.wft file from the run file system of your development environment. The .wft files for a product are usually located in the $PROD_TOP/patch/115/import/<LANG>/ directory.
- Use Oracle Workflow Builder to connect to the run edition of your development database and open the workflow definition stored in the database.

You can alsocreate your own new custom workflow processes using Oracle Workflow Builder.

For both customizations of seeded workflows and new custom workflows, save your workflow definition to a *.wft file in the $<CUSTOM>_TOP/patch/115/import/<LANG>/ folder, where $<CUSTOM>_TOP refers to your custom product top.
- You can use Oracle Workflow Builder to save the workflow definition as a *.wft file.
- If you have saved the workflow definition to your development database, you can also use the Workflow Definitions Loader concurrent program (WFLOAD) to download the workflow definition from the database to a *.wft file. Ensure that you connect to the run edition of your development database when running the Workflow Definitions Loader. See: Using the Workflow Definitions Loader, Oracle Workflow Administrator's Guide.

You can use either of the following methods to save the workflow definition to your development database for testing.
- Use Oracle Workflow Builder to connect to the run edition of your development database and save the workflow definition to the database.
- If you have saved the workflow definition to a *.wft file, you can also use the Workflow Definitions Loader concurrent program to upload the workflow definition to the database. Ensure that you connect to the run edition of your development database when running the Workflow Definitions Loader. See: Using the Workflow Definitions Loader, Oracle Workflow Administrator's Guide.

If you reference a custom PL/SQL function in a function activity in your workflow, then save that PL/SQL package and function definition locally in a *.pls file.

If you reference a custom event in an event activity, then download the metadata for that event from your development database to a *.wfx file and copy that file to the $<CUSTOM>_TOP/patch/115/xml/<LANG> folder, where $<CUSTOM>_TOP refers to your custom product top. First source the run file system of your development environment and then use the Workflow XML Loader utility (WFXLoad) to download the *.wfx file. See: Using the Workflow XML Loader, Oracle Workflow Administrator's Guide.

Deploying Workflow Process Customizations

Follow the generalsteps in .

Copy your custom files to the following locations on the patch file system of your production database:
- *.wft files - $<CUSTOM>_TOP/patch/115/import/<LANG>/ folder
- *.wfx files - $<CUSTOM>_TOP/patch/115/xml/<LANG>/ folder
- *.pls files - $<CUSTOM>_TOP/patch/115/sql folder

Upload your custom files to the production database as follows:
- Use the Workflow Definitions Loader concurrent program to upload the workflow definitions from the .*wft files on the patch file system to the database.
- Use the Workflow XML Loader utility to upload any event and subscription metadata from the *.wfx files on the patch file system to the database.
- Compile any custom PL/SQL objects in the database from the files saved in the $<CUSTOM>_TOP/patch/115/sql folder on the patch file system.

Section 6.6.2:Customizing Business Events and Subscriptions

Developing Event and Subscription Customizations

For business events or subscriptions seeded by Oracle E-Business Suite products, check your product-specific documentation to determine whether any customizations are supported. If so, you can use the Oracle Workflow Event Manager user interface in Oracle E-Business Suite to edit the event or subscription definition.

You can alsocreate your own new custom events and subscriptions using the Event Manager.

Note: Before you createa custom event or subscription owned by a custom product, ensure that yourcustom product is registered and licensed according to the instructionsin .

If any of your events have a custom PL/SQL generate function, or if any of your subscriptions have a custom PL/SQL rule function, then save that PL/SQL package and function definition locally in a *.pls file.

If any of your events have a custom Java generate function, or if any of your subscriptions have a custom Java rule function, then copy the Java class file to $OA_HTML/WEB-INF/lib/* and to $JAVA_TOP/<Company identifier>/*, and also save the Java class file locally.

Download the event and subscription metadata from your development database to a *.wfx file. First source the run file system of your development environment and then use the Workflow XML Loader utility (WFXLoad) to download the *.wfx file. See: Using the Workflow XML Loader, Oracle Workflow Administrator's Guide.

Deploying Event and Subscription Customizations

Follow the generalsteps in .

Copy your custom files to the following locations on the patch file system of your production database:
- *.wfx files - $<CUSTOM>_TOP/patch/115/xml/<LANG>/ folder
- *.pls files - $<CUSTOM>_TOP/patch/115/sql folder
- Java files - $JAVA_TOP/<Company identifier>/*

Upload your custom files to the production database as follows:
- Use the Workflow XML Loader utility to upload the event and subscription metadata from the *.wfx files on the patch file system to the database.
- Compile any custom PL/SQL objects in the database from the files saved in the $<CUSTOM>_TOP/patch/115/sql folder on the patch file system.
- If you have any custom Java files under the $JAVA_TOP folder, ensure that you run the adcgnjar utility as described in .

Section 6.7: Oracle XML Gateway

Section 6.7.1:Developing Oracle XML Gateway Customizations

Oracle XML Gatewayallows you to develop XML Gateway Maps and Document Type Definitions (DTDs) tomeet your business needs. For more information about using and customizingOracle XML Gateway, see: Oracle XML Gateway User's Guide.

In addition to thesteps in , use thefollowing guidelines to develop customizations for Oracle XML Gateway:

Use the XML Gateway Message Designer and configure it to connect to the run edition, which is the default.

Use XML Gateway Message Designer to create an XML Gateway Map. Since XML Gateway Message Designer cannot directly upload XML Gatway Maps, save the custom Map definition (*.xgm) in a local copy.

If the XML Gateway Map is created with any custom DTD, then use any XML editor to develop the custom DTD and save a local copy (*.dtd).

If the XML Gateway Map has any Action defined for custom XSL Transformation, then use any XML editor to develop the XSLT and save a local copy (*.xsl).

If the XML Gateway Map has any Action defined to execute any custom procedure, then use any SQL development tool to create packages and procedures and save a local copy (.pls).

Section 6.7.2:Deploying Oracle XML Gateway Customizations

Use the followingguidelines to deploy your customizations for Oracle XML Gateway on a targetproduction environment:

Follow the general steps in .

Copy your saved custom files to the following locations on the patch file system of your production database:
- Copy the .xgm file to $<CUSTOM>_TOP/patch/115/xml/<LANG>/
- Copy the .dtd and .xsl files to $<CUSTOM>_TOP/patch/115/xml/
- Copy the .pls file to $<CUSTOM>_TOP/patch/115/sql/

On the target instance, load the custom files using the following loader utilities:
- Invoke the LoadDTDToClob utility on the patch file system to load the contents of the document type definition *.dtd file.
- Invoke the LoadXSLTToClob utility on the patch file system to load the contents of the XSL Transformation *.xsl file.
- Invoke the LoadMap utility on the patch file system to load the contents of the XML Gateway Map *.xgm file.

Compile any custom PL/SQL objects in the database from the *.pls files saved in the $<CUSTOM>_TOP/patch/115/sql folder on the patch file system.

Section 6.8: Oracle XML Publisher

Section 6.8.1:Developing Oracle XML Publisher Customizations

For Oracle XMLPublisher, you can create the following types of custom files.

Data templates - These files are of type *.xml and can include data templates, sample XML, and bursting control files.

Layout templates - You can create the following types of layout templates:
- *.pdf - PDF template
- *.rtf - RTF template or E-Text template
- *.xsl - XSL template
- *.xls - Microsoft Excel template

Java class files - You can create custom Java class files if you use Java concurrent programs as the interface to generate Oracle XML Publisher reports.

Follow the stepsin to developyour custom files.

Create your custom data templates, sample XML, bursting control files, and layout templates according to the following guides, which are available through the Oracle E-Business Suite online help:
- Oracle XML Publisher Administration and Developer's Guide
- Oracle XML Publisher Report Designer's Guide

Log in to the Oracle E-Business Suite user interface, navigate to XML Publisher Adminstration, and upload your data templates and layout templates using the Oracle XML Publisher user interface..

Write the sample code to generate the Oracle XML Publisher reports using your custom files. For more information, see the Oracle XML Publisher Report Designer's Guide.

If you choose to use Java concurrent programs as the interface to generate Oracle XML Publisher reports, use a Java editor to develop the Java concurrent programs. Ensure that you follow the concurrent program SDK and the Oracle XML Publisher SDK. Then copy the Java class files to the $JAVA_TOP/<Company identifier>/* directory.

Source the run edition environment and download the Oracle XML Publisher metadata to an LDT file by invoking the Generic Loader (FNDLOAD) utility from the run edition environment with the $XDO_TOP/patch/115/import/xdotmpl.lct configuration file. Save a local copy of the LDT file. See: Using Loaders, Oracle E-Business Suite Setup Guide and the Oracle XML Publisher Administration and Developer's Guide.

Save local copies of your data templates, sample XML, bursting control files, layout templates, and any Java class files.

Section 6.8.1:Deploying Oracle XML Publisher Customizations

Follow the stepsin to deployyour custom files.

Copy your custom files to the following locations:
- Copy the *.ldt files to the $<CUSTOM>_TOP/patch/115/import/<LANG>/ folder.
- Copy *.xml files, including data templates, sample XML, and bursting control files, to the $<CUSTOM>_TOP/patch/115/publisher/defs folder.
- Copy all types of layout templates to the $<CUSTOM>_TOP/patch/115/publisher/templates/<LANG> folder.
- Copy the Java class files to the $JAVA_TOP/<Company identifier>/* folder.

Upload your custom files using the following utilities:
- Invoke the the Generic Loader (FNDLOAD) utility with the xdotmpl.lct configuration file from the patch edition environment to upload the Oracle XML Publisher metadata from your *.ldt files. See: Using Loaders, Oracle E-Business Suite Setup Guide.
- Use the XDOLoader utility from the patch edition environment to load the *.xml files, including data templates, sample XML, and bursting control files, as well as all types of layout templates. See: Oracle XML Publisher Administration and Developer's Guide in the Oracle E-Business Suite online help.

Section 7: Troubleshooting

This section liststroubleshooting tips.

If after a cutover there is a discrepancy in file versions, the discrepancy may cause the original file not to be replaced.

For moretroubleshooting tips about the online patching cycle, see the "Diagnosticsand Troubleshooting" section in Chapter 3, Patching Procedures, ofthe Oracle E-Business Suite Maintenance Guide.

Change Log

Date	Description
19 Sep 2013	Initial publication.
20 Sep 2013	Corrected name (capitalization) of 'ebsProductManifest.xml' in "Section 5.3: Running the adcgnjar Utility."
13 Dec 2013	Added section 5.5, "Deploying Java Files at Non-Standard Location(s) for Custom Products." Updated note for Release 12.2.3, including notes regarding abort and full cleanup in adop. Updated formatting.
06 Mar 2014	Added references in section 6.3 to Document 1315485.1 for the Oracle Application Framework Developer's Guide.
20 Mar 2014	Updated references for 's_run_base' and 's_patch_base' to 's_current_base' and 's_other_base', respectively. Added additional information for XMLImporter to Section 6.3.

Documentation Notices

This software andrelated documentation are provided under a license agreement containingrestrictions on use and disclosure and are protected by intellectual propertylaws. Except as expressly permitted in your license agreement or allowed bylaw, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in anyform, or by any means. Reverse engineering, disassembly, or decompilation ofthis software, unless required by law for interoperability, is prohibited.

The informationcontained herein is subject to change without notice and is not warranted to beerror-free. If you find any errors, please report them to us in writing.

If this issoftware or related documentation that is delivered to the U.S. Government oranyone licensing it on behalf of the U.S. Government, the following notice isapplicable:

U.S. GOVERNMENTEND USERS: Oracle programs, including any operating system, integratedsoftware, any programs installed on the hardware, and/or documentation,delivered to U.S. Government end users are "commercial computersoftware" pursuant to the applicable Federal Acquisition Regulation andagency-specific supplemental regulations. As such, use, duplication,disclosure, modification, and adaptation of the programs, including anyoperating system, integrated software, any programs installed on the hardware,and/or documentation, shall be subject to license terms and licenserestrictions applicable to the programs. No other rights are granted to theU.S. Government.

This software orhardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerousapplications, including applications that may create a risk of personal injury.If you use this software or hardware in dangerous applications, then you shallbe responsible to take all appropriate fail-safe, backup, redundancy, and othermeasures to ensure its safe use. Oracle Corporation and its affiliates disclaimany liability for any damages caused by use of this software or hardware indangerous applications.

Oracle and Javaare registered trademarks of Oracle and/or its affiliates. Other names may betrademarks of their respective owners.

Intel and IntelXeon are trademarks or registered trademarks of Intel Corporation. All SPARCtrademarks are used under license and are trademarks or registered trademarksof SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteronlogo are trademarks or registered trademarks of Advanced Micro Devices. UNIX isa registered trademark of The Open Group.

This software orhardware and documentation may provide access to or information on content,products, and services from third parties. Oracle Corporation and itsaffiliates are not responsible for and expressly disclaim all warranties of anykind with respect to third-party content, products, and services. OracleCorporation and its affiliates will not be responsible for any loss, costs, ordamages incurred due to your access to or use of third-party content, products,or services.

This document inany form, software or printed matter, contains proprietary information that isthe exclusive property of Oracle. Your access to and use of this confidentialmaterial is subject to the terms and conditions of your Oracle Software Licenseand Service Agreement, which has been executed and with which you agree tocomply. This document and information contained herein may not be disclosed,copied, reproduced, or distributed to anyone outside Oracle without priorwritten consent of Oracle. This document is not part of your license agreementnor can it be incorporated into any contractual agreement with Oracle or itssubsidiaries or affiliates.

For informationabout Oracle's commitment to accessibility, visit the Oracle AccessibilityProgram website at .

Access to OracleSupport

Oracle customershave access to electronic support through My Oracle Support. For information,visit orvisit if you arehearing impaired.

转载于:https://www.cnblogs.com/wanghang/p/6299318.html

你可能感兴趣的文章

web@h,c小总结

查看>>

java编程思想笔记（一）——面向对象导论

查看>>

Data Structure 基本概念

查看>>

Ubuntu改坏sudoers后无法使用sudo的解决办法