Creating a Custom Application in Oracle E-Business Suite Release 12.2 (Doc ID 1577707.1)

In This Document

This note describes the basic steps needed to create a custom application in Oracle E-Business Suite Release 12.2. Custom applications are required if you are creating new forms, reports, and so on. They allow you to segregate your custom-written files from the standard seeded functionality that Oracle E-Business Suite provides. Customizations can be preserved when applying patches to your environment, or performing upgrades.

Custom data and index tablespaces are defaulted to APPS_TS_TX_DATA and APPS_TS_TX_IDX.

Note: The procedure described in this document should be performed on the run file system, when there is no active patching cycle.

This procedure can also be followed to correct the registration of a custom application that was previously created without using AD Splice. However, in such a case some additional preparatory steps are required, as specified in Section 6.

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

Section 1: Create A Custom Application Using AD Splice

Section 2: Verify Creation of Custom Product in the Database

Section 3: Perform Required Steps on Application Tier Nodes

Section 4: Create Custom Objects

Section 5: Propagate Changes to Other File System

Section 6: Known Issues

Section 7: References

Section 1: Create A Custom Application Using AD Splice

1. Download Patch 3636980 "Support Diagnostics (IZU) patch for AD Splice" from My Oracle Support.
2. Manually copy the three .txt files from the 3636980\izu\admin directory to your own temporary directory.
3. Rename izuprod.txt to <CUSTOM MODULE>prod.txt. In this example, we will use "xxmzprod.txt".
4. Rename izterr.txt to <CUSTOM MODULE>terr.txt. In this example, we will use "xxmzterr.txt".
5. Open newprods.txt in a text editor.
   
   
   1. Change all references of "izu" to <custom module> and all references of "IZU" to <CUSTOM MODULE> (i.e. keep the case sensitivity).
   Note: In this example, we will be changing references of "izu" to be "xxmz" and "IZU" to be "XXMZ".
   
   
   2. Modify any other values as appropriate.
      
      In this example case, the modified version of this file will look like the following:
      product=xxmz
base_product_top= *APPL_TOP*
oracle_schema=xxmz
sizing_factor=100
main_tspace= USER_DATA
index_tspace=USER_IDX
temp_tspace=TEMP
default_tspace= USER_DATA
6. (Conditional) In Release 12.2, any new custom application objects will be stored in APPS_TS_TX_DATA and APPS_TS_TX_IX, regardless of the settings for "main_tspace=" and "index_tspace=" in the newprods.txt file. AD Splice will ignore any information for these two settings, and will use only the default OATM tablespaces for your new custom objects.  For this reason, any new custom schema should use default_tspace as APPS_TS_TX_DATA.
   
   Only for old custom applications created in Release 11i should you update these two values to point to your old custom OFA tablespaces. This should only be done if you did not convert to OATM before upgrading to Release 12.2.
Note: For reference, see Oracle E-Business Suite Maintenance Guide, Release 12.2 Part No. E22954, Chapter 7, Applications DBA System Maintenance Tasks and Tools, section Managing Files, subsection Adding New Off-Cycle Products, Step 4. Create tablespaces (conditional).
This means that AD Splice will take into consideration your custom tablespaces only for old Release 11i custom applications that were never converted to use the OATM model. In such cases, you therefore need to perform the following additional steps:

1. Create the new user that will own the custom schema.
2. Define the default tablespace for the new user to be APPS_TS_TX_DATA.
3. Edit newprods.txt to include the following custom information:
product=xxmz
base_product_top= *APPL_TOP*
oracle_schema=xxmz
sizing_factor=100
main_tspace= USER_DATA // This will be ignored by adsplice.
index_tspace=USER_IDX // This will be ignored by adsplice.
temp_tspace=TEMP
default_tspace= USER_DATA // This should be set to APPS_TS_TX_DATA, the default tablespace for your new custom schema user.
Do not include the comments, which are preceded by "//".
7. Open xxmzprod.txt in a text editor.
   
   
   1. Before starting, be aware of the following important points:
      • If you are going to rename the application, you cannot use the old application ID with the new application name.
        • If you are going to retain the existing application name, you must use the same application ID when running AD Splice.
      • If you are upgrading, and the custom application already exists in the database, you should specify the same application ID.
        • If you are performing a new installation, choose an application ID number above 50,000 that is not already in use.
   2. Change all references of "izu" to <custom module> and all references of "IZU" to <CUSTOM MODULE> (keeping the case sensitivity).
      
      
      Note: In this example, references to "izu" will be changed to "xxmz", and references to "IZU" will be changed to "XXMZ".
   3. Change all references to prodid 278 to your own application ID.
      
      You can run the following SQL to find out if your selected custom application ID number is available:
      SQL>select decode(count ,0, 'Selected number is available', 'Selected number is already in use') Status, &&enter_custom_applID selected_number
from
(
select count(*) as count from
(
select 'x' from fnd_oracle_userid
where oracle_id= &&enter_custom_applID
union
select 'x' from fnd_application
where application_id= &&enter_custom_applID
)
);
      Note: This example will use a prodid of 50201.

7. Open xxmzterr.txt in a text editor.
   
   
   1. Change all references of "izu" to <custom module> and all references of "IZU" to <CUSTOM MODULE> (keeping the case sensitivity).
      
      
      Note: In this example, references to "izu" will to changed to "xxmz", and references to "IZU" will be changed to "XXMZ".
   2. Change the text "Oracle_Support_Diagnostic_Tools" to be "xxmz_custom_app".

8. Copy the following text files to the $APPL_TOP/admin directory:
• xxmzprod.txt
• xxmzterr.txt
• newprods.txt
9. Change directory to the admin directory under $APPL_TOP.
10. Run AD Splice.
    
    
    Note: In Oracle E-Business Suite Release 12.2, AD Splice first makes the new user edition-enabled, and then enables Edition-Based Redefinition (EBR) for the custom objects.
    
    AD Splice must be run from the admin directory under APPL_TOP, and is invoked by running the command:
    $ adsplice
    When prompted for the following, you can press Enter to accept the default location:
    Enter the directory where your AD Splicer control file is located.
The default directory is [/oracle/VIS/apps/apps_st/appl/admin] :
    When prompted for the following, you can press Enter to accept the default filename:
    Please enter the name of your AD Splicer control file [newprods.txt] :
    When prompted for the following, you can press Enter to accept the default value and regenerate the environment file:
    Do you wish to regenerate your environment file [Yes] ?
    AutoConfig will be run automatically as part of this procedure.

11. Review the AD Splice log file to ensure the procedure completed successfully.
12. Review the AutoConfig log file to ensure the procedure completed successfully.

Note: If you have multiple custom tops, there is no need to create a full set of individual files (such as xxxprod.txt, xxxterr.txt, and newprods.txt) for each custom top, and then run AD Splice for each custom top.Instead, you can create separate xxxprod.txt and xxxterr.txt files for each custom product, but have a single newprod.txt file with entries for multiple custom products. When you run AD Splice, it will go through the product entries in the newprod.txt file one by one, finding the corresponding xxxprod.txt and xxxterr.txt files and then registering the associated product.
For example, if you want to create four custom tops, you will need to create four xxxprod.txt and four xxxterr.txt files, but only need to create a single newprod.txt file with entries for all four custom products. You can then register all four products in a single run of AD Splice.

Section 2: Verify Creation of Custom Product in the Database

1. Log in to SQL*Plus as APPS, and run the following SQL to confirm that the fnd_application table has one row:
   SQL>select * from fnd_application where application_short_name = 'XXMZ';
   This statement should return one row only.
2. Run the following SQL to check the product installations table has one row for your custom product:
   SQL>select * from fnd_product_installations where APPLICATION_ID = 50201;
   This statement should return a single row.
3. Run the following SQL to check the database user:
   SQL>select * from dba_users where username = 'XXMZ';
   This statement should return one row only.
4. To ensure the new environment file is picked up, log out and back in again as applmgr, then run the following command to confirm the $XXMZ_TOP variable is correctly set:
   $ env | grep XXMZ
5. Run the following command to check the file system has been created correctly. You should see a directory listing returned as shown:
   $ ls $XXMZ_TOP
admin log mesg out sql
   Note: If you have registered any schemas using the fnd_oracle_user_pkg.load_row API, their passwords will not be encrypted. As a result, adpatch and adsplice will fail. To correct this, you need to run the FNDCPASS utility to set the password for the schemas.

Section 3: Perform Required Steps on Application Tier Nodes

Follow the relevant steps for the Oracle E-Business Suite application tier nodes in your environment.

1. For shared APPL_TOP installations, run AutoConfig for the remaining nodes (there is no need to rerun AD Splice). Or, if the APPL_TOP is not shared, repeat Steps 8 to 12 in Section 1 on all the other nodes.
2. Stop and restart the application tier services.
3. Test Oracle E-Business Suite operation thoroughly to confirm the effect of your changes.

Section 4: Create Custom Objects

You can now create any custom objects required. Follow the instructions and related guidance below.

1. Database data objects such as tables, indexes, and sequences should be created in the XXMZ schema. You must perform the following two steps for each custom data object:
   
   
   1. Grant all privileges from each custom data object to the APPS schema. For example, logged in as the XXMZ user:       
      grant all privileges on myTable to apps;
   2. Create a synonym in APPS for each custom data object. For example, logged in as the APPS user:
      SQL>create synonym myTable for XXMZ.myTable;
2. Database code objects such as packages, functions, procedures and triggers must be created in the APPS schema. You must differentiate custom code from the Oracle E-Business Suite seeded code by prefixing it with "XX". For a full description of customization standards you must adhere to, refer to Oracle E-Business Suite Developer's Guide (HTML or PDF) under the Technology tab of the Oracle E-Business Suite Documentation Web Library, Release 12.2.
3. All source files for database objects (code and data) as well as all other types of custom code should be saved under the $XXMZ_TOP directory. Create source code files appropriate to the type of object. For example, forms would be located in $XXMZ_TOP/forms/US, package source code in $XXMZ_TOP/admin/sql, and table source code in either /odf or /xdf.
4. You may also want to create custom request group, responsibilities, menus, etc. that your users need to access your custom objects.

Section 5: Propagate Changes to Other File System

When you start the next online patching cycle, the prepare phase will run AD Splice sync-up actions to synchronize the two file systems.

If the AD Splice sync-up fails when the prepare phase is run, check the $APPL_TOP/admin/$TWO_TASK/log/adsplicelog log file for the following:

UPDATE FND_ORACLE_USERID SET READ_ONLY_FLAG = 'A' WHERE ORACLE_ID

If you see this, apply Patch 18283295 (R12.AD.C.Delta.5) and restart the prepare phase.

Note: Instructions for applying this patch can be found in My Oracle Support Knowledge Document 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2.

You should add entries for all your custom files to the custom synchronization driver file located at $APPL_TOP_NE/ad/custom/adop_sync.drv. The adop utility uses this driver file to synchronize files between the run file system and the patch file system. For instructions, refer to Section 1.5.4: Adding Entries to the Custom Synchronization Driver File, Document 1577661.1, Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2.

Section 6: Known Issues

The following are currently known issues:

• If you are an upgrade customer with a custom product top in the context file, but have not run AD Splice, you must:
  
  
  1. Remove the relevant entry from the FND_OAM_CONTEXT_CUSTOM table.
  2. Run AutoConfig on all nodes.
  3. Apply the latest AD-TXK release update pack as needed.
  4. Run AD Splice to add/register the custom product.
• A custom schema abbreviation cannot exceed sixteen (16) characters. If you have any custom context variables with more than sixteen (16) characters, you must take the same actions for the above known issue (custom product top in context file).
• If you have custom product tops from an older release, the FND_OAM_CONTEXT_CUSTOM table will have entries for them. If you do not want those custom product tops, remove the entries from the FND_OAM_CONTEXT_CUSTOM table and then run AutoConfig.

Section 7: References

For more information about AD Splice and patching operations in Oracle E-Business Suite Release 12.2, see:

• Oracle E-Business Suite Setup Guide, Part No. E22953
• Oracle E-Business Suite Maintenance Guide, Part No. E22954

Change Log

Date	Description
2018-01-05	Expanded various sections of the document with additional information, and changed Section 3, Step 2 from "Restart the application tier services." to "Stop and restart the application tier services."
2017-10-03	Amended Section 4 as per Bug 26910344.
15-Sep-2016	Amended Section 6 with new limitation on custom schema abbreviations for R12.AD.C.Delta.8; changed it from eight to 16 characters.
18-May-2015	Amended Section 1, Section 6, Point b to distinguish between application ID requirements in an upgrade and a new install.
08-Apr-2015	Removed database login example from Section 2, Step c.
13-Mar-2015	In introduction, added note that custom data and index tablespaces default to APPS_TX_TX_DATA and APPS_TS_TX_IDX. In Section 6, added known issue about removing custom product tops from older releases.
14-Jan-2015	In Section 5, added instructions on how to obtain Patch 18815526:R12.AD.C.
14-Aug-2014	Made minor amendments.
19-Sep-2013	Initial publication.