Create Custom Application in Oracle EBS R12.2


In most Oracle E-Business Suite implementations, Standard Oracle E-Business Suite needs to be customized to meet business requirements. In order to keep the custom files segregated from the standard Oracle files or the seeded functionality, Oracle highly recommends to create a custom application and sourcing the custom application tier files like forms, reports, etc in the CUSTOM_TOP.

In this post, I’ll show how to create a custom application in Oracle E-Business Suite R12.2.

The process consists of the following steps:

Download patch

Loging into My Oracle Support and download patch 3636980: Support Diagnostics (IZU) patch for AD Splice.

Once the patch is downloaded, copy the patch over to a directory in the application tier. I have copied it over to a directory ‘patch’ in the non-edition file system.

[oracle@apps scripts]$ cd /u01/install/APPS/fs_ne/EBSapps/patch
[oracle@apps patch]$ ls -lrt p3636980_R12_GENERIC.zip
-rw-r--r-- 1 oracle oinstall 13049 Sep 13  2021 p3636980_R12_GENERIC.zip

Login using ‘oracle‘ user and unzip the patch.

[oracle@apps patch]$ unzip -q p3636980_R12_GENERIC.zip

Use AD Splice to create the Custom Application

Navigate to the directory 3636980\izu\admin. If you list the files you will find three .txt files – izuprod.txt, izuterr.txt and newprods.txt, as shown below.

[oracle@apps patch]$ cd 3636980/izu/admin
[oracle@apps admin]$ ls
driver  izuprod.txt  izuterr.txt  newprods.txt

Copy these three .txt files to the temporary directory.

[oracle@apps tmp]$ cp /u01/install/APPS/fs_ne/EBSapps/patch/3636980/izu/admin/izuprod.txt /tmp/
[oracle@apps tmp]$ cp /u01/install/APPS/fs_ne/EBSapps/patch/3636980/izu/admin/izuterr.txt /tmp/
[oracle@apps tmp]$ cp /u01/install/APPS/fs_ne/EBSapps/patch/3636980/izu/admin/newprods.txt /tmp/

Rename izuprod.txt to <custom application>prod.txt. Here, I have renamed the file as xxrmprod.txt, as I want my custom application to be named ‘xxrm‘.

[oracle@apps tmp]$ mv izuprod.txt xxrmprod.txt

Similarly, rename izterr.txt to <custom application>terr.txt. For me I have renamed it as xxrmterr.txt.

[oracle@apps tmp]$ mv izuterr.txt xxrmterr.txt

Once you rename these two files, open the third file, newprods.txt, using any text editor and change the references of izu to <custom application> and IZU to <CUSTOM APPLICATION>. Note that this is the control file that AD Splicer would use while creating the new application and this file is case-sensitive. In my example, I have replaced izu with xxrm and IZU with XXRM.

It is worth noting that in R12.2, AD Splice ignores the values for the parameters ‘main_tspace‘ and ‘index_tspace‘, and uses the default OATM tablespaces. Hence, objects for the new custom application will be stored in APPS_TS_TX_DATA and APPS_TS_TX_IX.

The modified file looks like below. The changed values have been highlighted in yellow.

product=xxrm
base_product_top=*APPL_TOP*
oracle_schema=xxrm
sizing_factor=100
main_tspace=USER_DATA
index_tspace=USER_IDX
temp_tspace=TEMP
default_tspace=USER_DATA

Before progressing with the next step, you need to determine an application ID for the custom application. Since we are doing a new installation, any application ID above 50000, that is not already in use, should work. Use the SQL given below to check the availability of the application ID. Substitute ‘&appid’ with a suitable application id of your choice.

SQL> SELECT &appid "Application ID", DECODE(count ,0, 'Application ID is available', 'Application ID is in use') Status
  2  FROM
  3  (
  4  SELECT count(*) as count FROM
  5     (
  6        SELECT 'x' FROM apps.fnd_oracle_userid
  7        where oracle_id = &appid
  8        union
  9        SELECT 'x' FROM apps.fnd_application
 10        where application_id = &appid
 11     )
 12  );

In my case, I have used the Application ID 55555 for the custom application, since that was available.

Application ID STATUS
-------------- ---------------------------
         55555 Application ID is available

Now, open xxrmprod.txt in a text editor and as we have done before, change all the references of izu to <custom application> and IZU to <CUSTOM APPLICATION>. This is case-sensitive as well. Here, I have replaced izu with xxrm and IZU with XXRM. Additionally, substitute the application ID. Changes in the file has been highlighted in yellow.

.....
# Product specific Product ID's
xxrm     55555

END_OF_PRODUCT_ABBREVIATIONS   -999
#
# ##################################################
# Oracle Support Diagnostic Tools
# ##################################################
#
# application id, abbreviation, shortname, prefix
55555 xxrm XXRM APP
# multiple product installations for msob, "controlled release", shared only
#   optional fourth field is "stub product".  default is No
No No No No
# multilingual, has NLADD.sql
Yes Yes
# install oracle id, default ORACLE username, default ORACLE password
55555 XXRM XXRM
.....

Next open xxrmterr.txt in a text editor and all the references of izu to <custom application> and IZU to <CUSTOM APPLICATION>. Here, I have replaced izu with xxrm and IZU with XXRM. Changes are highlighted below.

# product names in this language
c       xxrm     xxrm_custom_app
END_OF_PRODUCT_NAMES
#

Copy the files xxrmprod.txt, xxrmterr.txt, newprods.txt over to the $APPL_TOP/admin directory.

[oracle@apps tmp]$ cp newprods.txt $APPL_TOP/admin/
[oracle@apps tmp]$ cp xxrmprod.txt $APPL_TOP/admin/
[oracle@apps tmp]$ cp xxrmterr.txt $APPL_TOP/admin/

NOTE: Before moving onto the next step, make sure you have sourced the RUN File System for EBS R12.2 instance. You can confirm which file system you are sourced to using the following command.

[oracle@apps tmp]$ echo $FILE_EDITION

Navigate to the $APPL_TOP/admin directory and run AD Splice.

[oracle@apps tmp]$ cd $APPL_TOP/admin/
[oracle@apps appl]$ adsplice

                     Copyright (c) 2002, 2012 Oracle Corporation
                        Redwood Shores, California, USA

                                   AD Splicer

                                 Version 12.2.0

NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.

APPL_TOP directory is displayed. Press enter and continue. This will start the installation.

Your default directory is '/u01/install/APPS/fs1/EBSapps/appl'.
Is this the correct APPL_TOP [Yes] ?

You are about to install or upgrade Oracle E-Business Suite product tables
in your ORACLE database 'EBSDB'
using ORACLE executables in '/u01/install/APPS/fs1/EBSapps/10.1.2'.

Enter the AD Splicer control file [newprods.txt] directory, when prompted. Since we have copied over the control file in the default location, press enter.

Please enter the directory where your AD Splicer control file is located.

The default directory is [/u01/install/APPS/fs1/EBSapps/appl/admin] :

When prompted to enter the AD Splicer control file name, press enter and continue.

Please enter the name of your AD Splicer control file [newprods.txt] :

Once the installation is complete, review the AD Splice log file to confirm that the process was successful.

That is us. The new custom application is now created. Move on to the next steps to verify the custom application.

Verification On the Database Tier

In order to verify the custom application, connect to SQL*PLUS using the APPS user and execute the following query.

SQL> select application_id, application_short_name,basepath from fnd_application where application_short_name = 'XXRM';

   APPLICATION_ID    APPLICATION_SHORT_NAME    BASEPATH
_________________ _________________________ ___________
            55555 XXRM                      XXRM_TOP

fnd_product_installations table should also contain one row for the newly installed product.

SQL> SELECT application_id, oracle_id, product_version, tablespace,index_tablespace, sizing_factor
  2  from fnd_product_installations
  3* where application_id = 55555;
   APPLICATION_ID    ORACLE_ID    PRODUCT_VERSION         TABLESPACE    INDEX_TABLESPACE    SIZING_FACTOR
_________________ ____________ __________________ __________________ ___________________ ________________
            55555        55555 12.0.0             APPS_TS_TX_DATA    APPS_TS_TX_IDX                   100

Execute the following query to check the database user details. Since we are using R12.2, the new user will be edition-enabled and Edition-Based Redefinition(EBS) will be enabled on the custom objects created under the new custom application.

SQL> SELECT username,user_id from dba_users where username = 'XXRM'
;
   USERNAME    USER_ID
___________ __________
XXRM              1981

Verification On the Application Tier

Login using applmgr user and execute the following command to confirm that the <CUSTOM_TOP> environment variable is set.

[applmgr@apps ~]$ env | grep XXRM
XXRM_TOP=/u01/install/APPS/fs1/EBSapps/appl/xxrm/12.0.0

List out the directories under CUSTOM_TOP. The following directories should be created correctly.

[applmgr@apps ~]$ ls $XXRM_TOP
admin  log  mesg  out  sql

If everything looks grand, then that is it. We have configured an new custom application on our Oracle E-Business Suite R12.2 instance.

References

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s