Script to Register Custom Table using AD_DD Package

             If a custom table is not registered then that table will not be shown in fnd_tables. You have to register your custom table if you are using those tables in AOL like value set creation. Using AD_DD package you can register custom tables,columns,Primary keys. If a table has many columns then you have to register each and every column manually . It is a tedious task.

       You can use below PLSQL block to register the table and all columns and primary key by passing table name and application short name as parameter

To copy code select+ctrl+C
DECLARE l_table_name VARCHAR2 (32) := 'XX_XXXXX_XXXXXXX';--The name of the custom table to be registered l_app_short_name VARCHAR2(50):='XXXXX';--The name of the Application short name . It is the custom application name BEGIN -- Get the table details using cursor for loop FOR table_detail IN (SELECT table_name, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent FROM dba_tables WHERE table_name = l_table_name) LOOP -- Call the API to register table ad_dd.register_table (p_appl_short_name => l_app_short_name , --The application short name of the application that owns the table (usually your custom application). p_tab_name => table_detail.table_name, --The name of the table p_tab_type => 'T', --T for Transaction data , S for seeded data p_next_extent => NVL(table_detail.next_extent, 512),----Default values as 512 p_pct_free => NVL(table_detail.pct_free, 10),----Default values as 10 p_pct_used => NVL(table_detail.pct_used, 70)----Default values as 70 ); END LOOP; -- End Register Custom Table -- Start Register Columns -- Get the column details of the table in cursor FOR column_details IN (SELECT column_name, column_id, data_type, data_length, nullable FROM all_tab_columns WHERE table_name = l_table_name) LOOP -- Call the API to register column ad_dd.register_column (p_appl_short_name => l_app_short_name , p_tab_name => l_table_name, p_col_name => column_details.column_name, p_col_seq => column_details.column_id, p_col_type => column_details.data_type, p_col_width => column_details.data_length, p_nullable => column_details.nullable, p_translate => 'N', p_precision => NULL, p_scale => NULL ); END LOOP; -- End Register Columns -- Start Register Primary Key -- Get the primary key detail of the table in cursor if the primary key column is there in the table FOR prim_keys IN (SELECT constraint_name, table_name, constraint_type FROM all_constraints WHERE constraint_type = 'P' AND table_name = l_table_name) LOOP -- Call the API to register primary_key ad_dd.register_primary_key (p_appl_short_name => l_app_short_name, p_key_name =>prim_keys.constraint_name, p_tab_name => prim_keys.table_name, p_description => 'Primary Key Registration', p_key_type => 'S', p_audit_flag => 'N', p_enabled_flag => 'Y' ); -- Start Register Primary Key Column -- Get the primary key column detial in cursor FOR pkey_columns IN (SELECT column_name, POSITION FROM dba_cons_columns WHERE table_name = prim_keys.table_name AND constraint_name = prim_keys.constraint_name) LOOP -- Call the API to register primary_key_column ad_dd.register_primary_key_column (p_appl_short_name => l_app_short_name, p_key_name => prim_keys.constraint_name, p_tab_name => prim_keys.table_name, p_col_name => pkey_columns.column_name, p_col_sequence => pkey_columns.POSITION ); END LOOP; -- End Register Primary Key Column END LOOP; -- End Register Primary Key COMMIT; END;--End of the table registration