设为首页 - 加入收藏 焦点技术网
热搜:java
当前位置:首页 >

Deploying Customizations in Oracle E-Business Suite Release 12.2

2014-04-10 10:38:00.0 R12.2  
导读:DeployingCustomizations in Oracle E-Business Suite Release 12.2This documentdescribes how to deploy customizations in an Oracle E-Business Suite Release12.2 environment. Follow these instructions to e...。。。

DeployingCustomizations in Oracle E-Business Suite Release 12.2

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

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

In This Document

This document isdivided into the following sections:

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:

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 /

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

The followingscripts are for experts:

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

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.

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

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

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

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

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

adop exiting withstatus = 0 (Success)

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

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

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 Section 7: Troubleshooting and correctthe problem, then run the adop apply command again, adding the"restart=yes" option.

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

Apply a ManualPatch

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

Manual patchingactions normally involve the following steps:

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

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

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

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

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

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

$ apply_db.sh 

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

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

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

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

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

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 Section 7: Troubleshooting, correct theproblem and run the finalize command again.

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

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 Section 7: Troubleshooting, resolve theproblem, and try the cutover command again. One common failure on under-poweredenvironments is that the application startup does not happen quickly enough andthe startup script times out. In this case, just run the cutover command againand adop will retry the startup script.

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

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

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.1: Setting Up an Environment for Customizations

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

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

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

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

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

On yourdevelopment environment, you should invoke adsplice from the run file system.Connect to the run file system as described in Section 1.1: Connecting to the Run Edition. 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 Section 6: Component-Specific Steps for ApplicationTier Objects.

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

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

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

  1. Connect to the run edition file system on your     development environment.
  2. Copy the custom files to the appropriate directory     on the run edition file system.
  3. If you copied any custom files under the $JAVA_TOP     directory, run the adcgnjar utility to generate and sign a JAR file     containing these files. When prompted, enter the user name and     password of the APPS user. See Section 5.3: Running the adcgnjar Utility.
  4. If necessary, use the appropriate utility for your     product or component to upload the custom files to the database.
  5. Add entries for the custom files to the custom     synchronization driver file to ensure that the adop utility synchronizes     these files between the run file system and the patch file system the next     time you run the prepare phase. See Section 5.4: Adding Entries to the Custom     Synchronization Driver File.

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:

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

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

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

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

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

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

Step 2: Createthe patch

Patch files in theabove example would be:

The manual applyactions for the file system would be:

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

The manual applyactions for the database would be:

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

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) 

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

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

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

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

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

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

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

$ xdfgen.pl /@dbXYZ_USER_SERVICE

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

  1. Create the patch.

Patch Files:

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

Manual apply phaseactions for the file system:

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

Manual apply phaseactions for the database:

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

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

Add a new column to a table

This stepdemonstrates adding a new logical column to a table (as opposed to revising anexisting logical column, which we will cover in a later 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. 

  1. Create the new column in your development     database.

We can do this inSQL*Plus as follows:

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

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

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

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

4.  

5.     @ADZDSHOWEV XYZ_USER_SERVICE

6.     -- EV Column Mapping

7.  

8.     VIEW_COLUMN                    ->   TABLE_COLUMN

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

10.    USER_ID                        =    USER_ID

11.    SERVICE_TYPE                   =    SERVICE_TYPE

12.    COMMENTS                       =    COMMENTS

13.    SERVICE_STATUS                 =    SERVICE_STATUS

   

The new column isnow present in the Editioning View.

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

xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE

  1. Create the patch.

Patch Files:

Manual apply phaseactions for the file system:

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

Manual apply phaseactions for the database:

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

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

  1. Create the new index in your development database.

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

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

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

xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE

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

  1. Create the patch.

Patch Files:

Manual apply phaseactions for the file system:

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

Manual applyactions for the database:

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

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

  1. Create a revised column in your development     database.

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

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


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

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

@ADZDSHOWEVXYZ_USER_SERVICE

 

    -- EVColumn Mapping

 

    VIEW_COLUMN                    ->   TABLE_COLUMN

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

    USER_ID                        =    USER_ID

    SERVICE_TYPE                   ===> SERVICE_TYPE#1

    COMMENTS                       =    COMMENTS

    SERVICE_STATUS                 =    SERVICE_STATUS

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

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

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

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

The ForwardCross-edition Trigger Template is as follows:

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

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

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

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


commit; 
exit; 

For our example,the FCET looks like the following:

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

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

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

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


commit; 
exit; 

Create the triggerwith the following naming standards:

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/ @XYZ_USER_SERVICE_X1 APPLSYS 
sqlplus /@$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/ 
  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.

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

xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE

At last, you areready to create the patch.

  1. Create the patch.

Patch Files:

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/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf / 
sqlplus /@$FND_TOP/patch/115/sql/XYZ_USER_SERVICE_X1 APPLSYS 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_USER_SERVICE_F1

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

  1. F1
  2. F2 follows F1
  3. F3 follows F2
  4. ... 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. 

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

  1. 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 Copyright (c) 2013 Oracle Corporation, All Rights Reserved 
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/ @XYZ_USER_SERVICE_X2 APPLSYS 
   # note: the trigger will create with compilation error, that isOK 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_USER_SERVICE_F2 
sqlplus / 
  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.

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

xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE

  1. Create the patch.

Patch Files:

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/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf/ 
sqlplus /@$FND_TOP/patch/115/sql/XYZ_USER_SERVICE_X2 APPLSYS 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_USER_SERVICE_F2

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

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)

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

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

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

  1. Extract the table definition from your     development database:

xdfgen.pl/@$TWO_TASK XYZ_SERVICE_TYPES

  1. Extract starting seed data from your development     database:

FNDLOAD/ 0 Y DOWNLOAD xyzst.lct xyzst_data.ldtXYZ_SERVICE_TYPE

  1. Create the patch.

Patch Files:

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/@$TWO_TASK $FND_TOP/patch/115/xdf/xyz_service_types.xdf/ 
FNDLOAD / 0 Y UPLOAD$FND_TOP/patch/115/import/xyzst.lct $FND_TOP/patch/115/import/US/xyzst_data.ldt

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

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

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

  1. 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 Copyright (c) 2013 Oracle Corporation, All Rights Reserved 
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/ @XYZ_SERVICE_TYPES_X1 APPLSYS 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_F1 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_R1 

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

  1. Extract the table definition from your     development database:

xdfgen.pl/@$TWO_TASK XYZ_SERVICE_TYPES

  1. Create the patch.

Patch Files:

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/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_service_types.xdf/ 
sqlplus /@$FND_TOP/patch/115/sql/XYZ_SERVICE_TYPES_X1 APPLSYS 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_F1 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_R1

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

  1. F1
  2. F2 follows F1 ...
  3. F3 follows F2 ...
  4. ... and so on ...

And each new RCETPRECEDES the previous RCET.

  1. R1
  2. R2 precedes R1 ...
  3. R3 precedes R2 ...
  4. ... 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)

 

 

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

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

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

  1. 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 Copyright (c) 2013 Oracle Corporation, All Rights Reserved 
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;

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

sqlplus/ @XYZ_SERVICE_TYPES_X2 APPLSYS 
  # both triggers create with compilation errors, no worries. 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_F2 
sqlplus /@$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLY XYZ_SERVICE_TYPES_R2 

sqlplus / 
 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 

  1. Extract the table definition from your     development database:

xdfgen.pl/@$TWO_TASK XYZ_SERVICE_TYPES

  1. Create the patch.

Patch Files:

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/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_service_types.xdf/ 
sqlplus /@$FND_TOP/patch/115/sql/XYZ_SERVICE_TYPES_X2 APPLSYS 
sqlplus / @$AD_TOP/patch/115/sql/AD_ZD_TABLE_APPLYXYZ_SERVICE_TYPES_F2 
sqlplus /@$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.

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

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

xdfgen.pl/@$TWO_TASK XYZ_USER_GT

  1. Create the patch.

Patch Files:

Manual apply phaseactions for the file system:

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

Manual apply phaseactions for the database:

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

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.

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

  1. Extract the temporary table definition to an XDF     file.

xdfgen.pl/@$TWO_TASK XYZ_USER_GT2

  1. 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 Copyright (c) 2013 Oracle Corporation, All Rights Reserved 
REM $Header$ 
REM @xyz_user_gt.sql  
REM   Set XYZ_USER_GT synonym to .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;

  1. Create the patch.

Patch Files:

Manual apply phaseactions for the file system:

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

Manual apply phaseactions for the database:

xdfcmp.pl/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_gt2.xdf/ 
sqlplus / @$FND_TOP/patch/115/xdf/xyz_user_gtAPPLSYS

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

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

  1. 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/ @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

  1. 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/@$TWO_TASK XYZ_SCHEMAS_MV

  1. Create the patch.

Patch Files:

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/@$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.

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

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

  1. Extract the MV definition.

xdfgen.pl/@$TWO_TASK XYZ_SCHEMAS_MV

  1. Create the patch.

Patch files:

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/@$FND_TOP/patch/115/sql/xyz_schema_types 
xdfcmp.pl /@$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

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.

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 6: Component-Specific Steps for ApplicationTier Objects.

Section 5.1: Setting Up a Production Environment

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

  1. On the production environment, run the adop prepare     phase.
  2. 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)
  3. Connect to the patch edition file system on the production     environment. See Section 1.2: Connecting to the Patch Edition.
  4. Invoke adsplice from the patch file system to     register your custom application. For more information about using     adsplice, see Section 3.1: Setting Up an Environment for     Customizations.
  5. Run the adop cutover phase.

Section 5.2: Deploying Customizations on a ProductionEnvironment

To deploycustomizations, perform the following steps:

  1. On the production environment, run the adop prepare     phase.
  2. Connect to the patch edition file system on the     production environment.
  3. Copy the custom files to the appropriate directory     on the patch edition file system. See the component-specific steps     in Section 6: Component-Specific Steps for     Application Tier Objects.
  4. If you copied any custom files under the $JAVA_TOP     directory, run the adcgnjar utility to generate and sign a JAR file     containing these files. When prompted, enter the user name and     password of the APPS user. See Section 5.3: Running the adcgnjar Utility.
  5. 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 Section 6: Component-Specific Steps for     Application Tier Objects.
  6. 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 5.4: Adding Entries to the Custom     Synchronization Driver File.
  7. 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:

  1. Creates a temporary custom.zip file that contains     all the directories under $JAVA_TOP except the oracle, META-INF, and policies directories.
  2. Generates and signs the customall.jar file with the     contents of the custom.zip file.
  3. 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//* 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/ %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//* .

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,

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,

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:

  1. 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  where      the  is the list of all      the directory paths, relative to $JAVA_TOP, for custom application's java      files at the non-standard location.
  2. 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     -keyPass
  3. Delete the temporary customprod.zip. Command: rm $JAVA_TOP/customprod.zip
  4. Follow the steps below to make the custom jar file     available for WebLogic Server:
  1. 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/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 /oracle//* 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/      %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.

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:

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.

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

Section 6.1.3:Developing Concurrent Program Files

Follow the stepsin Section 3: Developing Customizations 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 Section 3.1: Setting Up an Environment for     Customizations. Then copy the Java class files to the $JAVA_TOP//* 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/@ 0 Y DOWNLOAD$FND_TOP/patch/115/import/afcpprog.lct .ldt PROGRAMAPPLICATION_SHORT_NAME=

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 Section 5: Deploying Custom Application Tier Objects to deployyour custom files.

  • Copy your custom files to the following locations:
    • Copy the *.ldt files to the $_TOP/patch/115/import//       folder.
    • Copy the *.pls files to the $_TOP/patch/115/sql      folder.
    • Copy the Java class files to the      $JAVA_TOP//* 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 $_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

All the form .fmbfiles are staged under $AU_TOP/forms/, for example,$AU_TOP/forms/US. The compiled forms (.fmx files) are staged under$PROD_TOP/forms/, 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 $_TOP/forms/US.

Section 6.2.3:Deploying Forms Files

In addition tothese instructions, follow the steps in Section 5.4: Adding Entries to the CustomSynchronization Driver File.

Forms (.fmb and .fmx files)

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

  1. Source the patch edition environment.
  2. 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/ (if for US this would be $AU_TOP/forms/US).     If these directories are not set under $FORMS_PATH, set FORMS_PATH     accordingly.
  3. Compile the .fmb file as follows using MYCUSTOM.fmb     as an example:

cd $AU_TOP/forms/US 
frmcmp_batch MYCUSTOM.fmb /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 /output_file=$FND_TOP/forms/US/MYCUSTOM.fmx compile_all=special

Forcustomer-created forms, the output_file should point to $_TOP/forms/.

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

You need to sourcethe patch edition environment and stage any customer-created or -modifiedOracle E-Business Suite form under $AU_TOP/forms/ 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.

  1. Source the patch edition environment.
  2. 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.
  3. Compile the .pll as follows using CUSTOM.pll as an     example:

cd$AU_TOP/resource 
frmcmp_batch CUSTOM.pll /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.ldt FND_FOLDERS

To upload folders:

FNDLOADusername/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/fndfold.lct.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 Document 1315485.1) 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 Section 6.3.2: Deploying OA Framework PersonalizationsVia a Patch 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:

  1. 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.
  2. On the target Oracle E-Business Suite instance:
    1. Perform the setup steps described in Section 5.1: Setting Up a Production Environment.
    2. Run the adop prepare phase.
    3. Source the patch edition environment.
    4. Copy the exported XML/XLIFF file from step 1B to the $<CUSTOM>_TOP/mds      directory, where $_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 Section 5.4: Adding Entries to the Custom      Synchronization Driver File 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:

  1. 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 Document 1315485.1.
  2. Follow the initial setup instructions described     in Section 3: Developing Customizations.
  3. 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 Section 5.3: Running the adcgnjar Utility.
    4. For BC4J substitutions and JRAD XML file      changes, copy the JPX and XML files to the $<CUSTOM>_TOP/mds      directory, where $_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 Document 1315485.1). 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 Section 5.4: Adding Entries to the Custom      Synchronization Driver File 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:

  1. Follow the initial setup instructions in Section 5.1: Setting Up a Production Environment..
  2. Run the adop prepare phase.
  3. Source the patch edition environment.
  4. For OA Extension Controller Java class file changes     and BC4J XML file changes, copy the .class and .xml files to $JAVA_TOP/<Company     identifier>/*.
  5. Run the adcgnjar utility, as described in Section 5.3: Running the adcgnjar Utility.
  6. For BC4J substitutions and JRAD XML file changes,     copy the JPX and XML files to the $<CUSTOM>_TOP/mds     directory, where $_TOP refers to your custom     product top.
  7. 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 Document 1315485.1). 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.
  8. Follow the instructions in Section 5.4: Adding Entries to the Custom     Synchronization Driver File 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.
  9. 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:

Section 6.4.2:Developing JTT Customizations

Follow the stepsin Section 3: Developing Customizations.

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

  1. Copy the newly created js, jsp, css, xss, xsl, htm,     html and other files to the $OA_HTML/* directory.
  2. To render custom jsp files in the browser, ensure     that the profile option "Allow Unrestricted JSP Access" is set     to "Yes".
  3. Develop Java class files using any Java editor tool     and copy the Java class files to the $JAVA_TOP//* 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

  1. First, ensure you have performed the setup steps     in Section 5.1: Setting Up a Production Environment.
  2. 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".
  3. Then follow the steps in Section 5.2: Deploying Customizations on a     Production Environment.
    • Copy any custom Java class files to the      $JAVA_TOP//* directory.
    • Copy your other custom files, including js, jsp,      css, xss, xsl, htm, html, and other files to the $_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 Section 3: Developing Customizations 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//* 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 Section 5: Deploying Custom Application Tier Objects to deployyour custom files.

  • Copy your custom files to the following locations:
    • Copy the *.ldt files to the $_TOP/patch/115/import//       folder.
    • Copy the *.pls files to the $_TOP/patch/115/sql      folder.
    • Copy the Java class files to the      $JAVA_TOP//* 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 $_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:

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

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

  1. For both customizations of seeded workflows and new     custom workflows, save your workflow definition to a *.wft file in the $_TOP/patch/115/import//     folder, where $_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.
  2. 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.
  3. 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.
  4. 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 $_TOP/patch/115/xml/     folder, where $_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 Section 5: Deploying Custom Application Tier Objects.

  • Copy your custom files to the following locations on     the patch file system of your production database:
    • *.wft files - $_TOP/patch/115/import//      folder
    • *.wfx files - $_TOP/patch/115/xml//      folder
    • *.pls files - $_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 $_TOP/patch/115/sql      folder on the patch file system.

Section 6.6.2:Customizing Business Events and Subscriptions

Developing Event and Subscription Customizations

  1. 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 Section 3: Developing Customizations.

  1. 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.
  2. 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//*, and also save the Java class file     locally.
  3. 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 Section 5: Deploying Custom Application Tier Objects.

  • Copy your custom files to the following locations on     the patch file system of your production database:
    • *.wfx files - $_TOP/patch/115/xml//      folder
    • *.pls files - $_TOP/patch/115/sql      folder
    • Java files - $JAVA_TOP//*
  • 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 $_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 5.3: Running the adcgnjar Utility.

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 Section 3: Developing Customizations, 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:

  • Copy your saved custom files to the following     locations on the patch file system of your production database:
    • Copy the .xgm file to $_TOP/patch/115/xml//
    • Copy the .dtd and .xsl files to $_TOP/patch/115/xml/
    • Copy the .pls file to $_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     $_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.

Follow the stepsin Section 3: Developing Customizations 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//* 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 Section 5: Deploying Custom Application Tier Objects to deployyour custom files.

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

This section liststroubleshooting tips.

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.

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

Copyright © 2013,Oracle and/or its affiliates. All rights reserved.

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 http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to OracleSupport

Oracle customershave access to electronic support through My Oracle Support. For information,visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info orvisit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you arehearing impaired.

 

(编辑: caixingyun)

网友评论