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.zipUse 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_EDITIONNavigate 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_TOPfnd_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 100Execute 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.0List 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.
