HOW TO register table in oracle apps

API to register table in Oracle Apps R12

API: ad_dd.register_table

• You register your custom application tables using a PL/SQL routine in the AD_DD package.
• Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.
• You can also use the AD_DD API to delete the registrations of tables and columns. You should delete the column registration first, then the table registration.
• To alter a registration you should first delete the registration, then re-register the table or column. 
• The AD_DD API does NOT check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API.
• You need NOT register views.
• You should include calls to the table registration routines in a PL/SQL script.
• You should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.

DECLARE   
   v_appl_short_name   VARCHAR2 (40) := '&appl_short_name';  
   v_tab_name          VARCHAR2 (32) := '&table_name';   
   v_tab_type          VARCHAR2 (50) := 'T';   
   v_next_extent       NUMBER := 512;  
   v_pct_free          NUMBER;   
   v_pct_used          NUMBER;
BEGIN   
   -- Unregistering the custom table if it exists   
   ad_dd.delete_table (p_appl_short_name   => v_appl_short_name, 
                       p_tab_name          => v_tab_name); 
   -- Registering the custom table   
FOR i_tab_details IN (SELECT table_name, 
                             tablespace_name,
                             pct_free,   
                             pct_used, 
                             ini_trans, 
                             max_trans, 
                             initial_extent,  
                             next_extent    
                       FROM dba_tables               
           WHERE table_name = v_tab_name)  
 LOOP     
    ad_dd.register_table (        
       p_appl_short_name   => v_appl_short_name,
       p_tab_name          => i_tab_details.table_name,
       p_tab_type          => v_tab_type, 
       p_next_extent       => NVL (i_tab_details.next_extent, 512), 
       p_pct_free          => NVL (i_tab_details.pct_free, 10),   
       p_pct_used          => NVL (i_tab_details.pct_used, 70));   
END LOOP;      -- Registering the columns of our custom table   
FOR i_all_tab_cols IN (SELECT column_name,    
                                column_id,                                 
                                data_type,                                
                              data_length,                                 
                                nullable                      
                     FROM all_tab_columns                           
            WHERE table_name = v_tab_name)   
LOOP      
     ad_dd.register_column (        
           p_appl_short_name   => v_appl_short_name,         
           p_tab_name          => v_tab_name,         
           p_col_name          => i_all_tab_cols.column_name,         
           p_col_seq           => i_all_tab_cols.column_id,         
           p_col_type          => i_all_tab_cols.data_type,         
           p_col_width         => i_all_tab_cols.data_length,         
           p_nullable          => i_all_tab_cols.nullable,         
           p_translate         => 'N',         
           p_precision         => NULL,         
           p_scale             => NULL);  
 END LOOP;     
     FOR all_keys IN (SELECT constraint_name,
                                  table_name,
                              constraint_type                     
                       FROM all_constraints                     
        WHERE constraint_type = 'P' AND table_name = v_tab_name)   
LOOP      
  ad_dd.register_primary_key (        
      p_appl_short_name   => v_appl_short_name,        
      p_key_name          => all_keys.constraint_name,        
      p_tab_name          => all_keys.table_name,        
      p_description       => 'Register primary key',        
      p_key_type          => 'S',        
      p_audit_flag        => 'N',        
      p_enabled_flag      => 'Y');  
     
  FOR all_columns       
      IN (SELECT column_name, position
            FROM dba_cons_columns              
      WHERE table_name = all_keys.table_name  
        AND constraint_name = all_keys.constraint_name)      
  LOOP         
     ad_dd.register_primary_key_column (           
         p_appl_short_name   => v_appl_short_name,  
         p_key_name          => all_keys.constraint_name,          
         p_tab_name          => all_keys.table_name,           
         p_col_name          => all_columns.column_name,          
         p_col_sequence      => all_columns.POSITION);      
  END LOOP;   
END LOOP;     
   COMMIT;
END;
/