Register Custom Tables in Oracle Apps

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),  7    ATTRIBUTE3              VARCHAR2(2000 BYTE),
  8    ATTRIBUTE4              VARCHAR2(2000 BYTE),
  9    ATTRIBUTE5              VARCHAR2(2000 BYTE),
 10    ATTRIBUTE6              VARCHAR2(2000 BYTE),
 11    ATTRIBUTE7              VARCHAR2(2000 BYTE),
 12    ATTRIBUTE8              VARCHAR2(2000 BYTE),
 13    ATTRIBUTE9              VARCHAR2(2000 BYTE),
 14    ATTRIBUTE10             VARCHAR2(2000 BYTE),
 15    STATUS                  VARCHAR2(50 BYTE)    NOT NULL,
 16    ERROR_MESSAGE           VARCHAR2(2000 BYTE),
 17    CREATED_BY              NUMBER               NOT NULL,
 18    CREATION_DATE           DATE                 NOT NULL,
 19    LAST_UPDATED_BY         NUMBER               NOT NULL,
 20    LAST_UPDATE_DATE        DATE                 NOT NULL,
 21    REQUEST_ID              NUMBER,
 22    BATCH_ID                NUMBER
 23  )
 24  TABLESPACE C_APPS
 25  PCTUSED    0
 26  PCTFREE    10
 27  INITRANS   1
 28  MAXTRANS   255
 29  STORAGE    (
 30              INITIAL          64K
 31              NEXT             1M
 32              MINEXTENTS       1
 33              MAXEXTENTS       UNLIMITED
 34              PCTINCREASE      0
 35              BUFFER_POOL      DEFAULT
 36             )
 37  LOGGING
 38  NOCACHE
 39  NOPARALLEL
 40  /
 
Table created
 
SQL> 

You would have noticed that table is created under custom schema. Make sure you create synonym under APPS schema.

There are 4 steps involved in registering table in Oracle Applications

#1 - Register Table

ad_dd.register_table 
(p_appl_short_name IN VARCHAR2, -- Application Short Name
 p_table_name      IN VARCHAR2, -- Table Name
 p_table_type      IN VARCHAR2, -- Table Type. Use 'T' if transaction table. 'S' for seed data table.
 p_next_extent     IN NUMBER DEFAULT 512,
 p_pct_free        IN NUMBER DEFAULT 10, -- % of space in each of the table block reserved for future updates(1-99)
 p_pct_used        IN NUMBER DEFAULT 70) -- MINimum percentage of used space IN each data block of the table (1-99)
                                       ; -- The sum of p_pct_free and p_pct_used must be less than 100

Sample Script

EXECUTE ad_dd.register_table 

(p_appl_short_name =>  'C_APPS',
 p_table_name      =>  'C_FND_INTERFACES',
 p_table_type      =>  'T',
 p_next_extent     =>  512,
 p_pct_free        =>  10,
 p_pct_used        =>  70);
                             

#2 - Register Column

ad_dd.register_column

(p_appl_short_name IN VARCHAR2, -- Application Short Name
 p_table_name      IN VARCHAR2, -- Table Name
 p_column_name     IN VARCHAR2, -- Column Name
 p_column_seq      IN NUMBER,   -- Sequence NUMBER of Column in table
 p_column_type     IN VARCHAR2, -- Column Type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
 p_column_width    IN NUMBER,   -- Colum Size,Can use 9 for DATE columns, 38 for NUMBER columns
 p_nullable        IN VARCHAR2, -- Use 'N' if mandatory or 'Y' if it allows null values
 p_translate       IN VARCHAR2, -- 'N' if the values are not translated
 p_precision       IN NUMBER DEFAULT NULL, -- NUMBER of digits in a NUMBER    
 p_scale           IN NUMBER DEFAULT NULL);-- NUMBER of digits to the right of the decimal point

Sample Script

EXECUTE ad_dd.register_column
(p_appl_short_name =>   'C_APPS'
 p_table_name      =>   'C_FND_INTERFACES'
 p_column_name     =>   'interface_id'
 p_column_seq      =>   'c_fnd_interfaces_seq'
 p_column_type     =>   NUMBER
 p_column_width    =>   10
 p_nullable        =>   'N'
 p_translate       =>   'N'
 p_precision       =>   NULL
 p_scale           =>   NULL);

#3 - Register Primary Key

ad_dd.register_primary_key
(p_appl_short_name IN VARCHAR2,  -- Application Short Name
 p_key_name        IN VARCHAR2,  -- Primary Key Name
 p_table_name      IN VARCHAR2,  -- Table Name
 p_description     IN VARCHAR2,  -- Primary key Description
 p_key_type        IN VARCHAR2,  -- (D/S)Developer/Surrogate,DEFAULTs to null
 p_audit_flag      IN VARCHAR2,  -- (Y/N)DEFAULTs to null
 p_enabled_flag    IN VARCHAR2); -- (Y/N)DEFAULTs to null

Sample Script

EXECUTE ad_dd.register_primary_key
(p_appl_short_name =>   'C_APPS',
 p_key_name        =>   'INTERFACE_ID_PK',
 p_table_name      =>   'C_FND_INTERFACES',
 p_description     =>   'Interface ID Primary Key',
 p_key_type        =>   'S',
 p_audit_flag      =>   'Y',
 p_enabled_flag    =>   'Y');

#4 - Register Primary Key Column

ad_dd.register_primary_key_column
(p_appl_short_name IN VARCHAR2,
 p_key_name        IN VARCHAR2,
 p_tab_name        IN VARCHAR2,
 p_col_name        IN VARCHAR2,
 p_col_sequence    IN NUMBER);

Sample Script

EXECUTE ad_dd.register_primary_key_column
(p_appl_short_name =>  'C_APPS',
 p_key_name        =>  'INTERFACE_ID_PK',
 p_tab_name        =>  'C_FND_INTERFACES',
 p_col_name        =>  'INTERFACE_ID',
 p_col_sequence    =>  '1');

Let’s go and validate in application.

Navigate to Application Developer –> Application –> Database –> Table
F11 –> Enter your table_name ‘C_FND_INTERFACES’ –> Ctrl + F11.