Register Custom Table Using Script In Oracle Applications

In my last post I talked about how to register table in Oracle Application. The steps involved are too much and will take lot of time if we have to create multiple tables or a single table is having too many columns.

To Save time you can use below script which will do the job for you. I am considering the your table is already created.

DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'C_APPS';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'C_FND_INTERFACES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN

Register Custom Table In Oracle Applications

All you technical people out there very well know the purpose of creating custom table. Its basically when we need to store data before pulling into standard table (In case of inbound interface) or putting extract of data to share with external entity (In case of outbound interface). All these activities can be accomplished without registering custom table in oracle apps.

If you have a need to use these custom tables in standard functionality in frond end like using in Alerts / Audits, you must register them. Otherwise your custom table will not be visible in front end.

Lets start this by creating a sample custom table.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as c_apps
 
SQL> 
SQL> CREATE TABLE C_FND_INTERFACES
  2  ( INTERFACE_ID            NUMBER               NOT NULL PRIMARY KEY,
  3    SERVICE_CHANNEL         VARCHAR2(64 BYTE)    NOT NULL,
  4    SOURCE                  VARCHAR2(64 BYTE) NOT NULL,
  5    ATTRIBUTE1              VARCHAR2(2000 BYTE),
  6    ATTRIBUTE2              VARCHAR2(2000 BYTE),

Register Custom Application In Oracle Applications

As a standard practice when ever you need to write custom code/forms/reports in Oracle Applications you must put then under custom application TOP. This is to avoid impact of patching on custom objects. If we put custom object under stand TOP it might create issue and after patching it may start malfunctioning. If we have all custom code at one place it easy to maintain as we know exactly how many custom objects we have and where they are.

Before staring you need to first decide what should be your application name and short name. Here we go ....

SCHEMA NAME    : C_APPS
TOP NAME       : C_APPS_TOP
Application    : Custom Application
Data Group     : Standard
Request Group  : Custom Request Group

#1 – Create directory under application top

• Login to Application Server and Source the environment variable.
  

  login as: oracle
  oracle@192.168.56.200's password:
  Last login: Sun Feb  2 12:53:38 2014 from 192.168.56.1
  [oracle@ebsapp ~]$ 
  [oracle@ebsapp ~]$ pwd
  /home/oracle
  [oracle@ebsapp ~]$ . /u01/E-BIZ/apps/apps_st/appl/APPSVIS_ebsapp.env
  [oracle@ebsapp ~]$ cd $APPL_TOP
  [oracle@ebsapp appl]$ pwd
  /u01/E-BIZ/apps/apps_st/appl
  [oracle@ebsapp appl]$