AD_DD Package: Oracle Table Registration

Custom Application Tables are registered using a PL/SQL routing in the AD_DD Package

You only need to register the Table if you are to use them with Oracle Alerts or Flexfields. You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables.

If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the existing registration, then reregister the table or column.

You should delete the column registration first, then the table registration.

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.

Procedures in the AD_DD Package:

procedure register_table (p_appl_short_name in varchar2,
                                         p_tab_name             in varchar2,          
                                         p_tab_type               in varchar2,            
                                         p_next_extent          in number default 512,
                                         p_pct_free                in number default 10,
                                         p_pct_used               in number default 70);

procedure register_column (p_appl_short_name in varchar2,
                                             p_tab_name   in varchar2,
                                             p_col_name   in varchar2,
                                             p_col_seq    in number,
                                             p_col_type   in varchar2,
                                             p_col_width  in number,
                                             p_nullable   in varchar2,
                                             p_translate  in varchar2,
                                             p_precision  in number default null,
                                             p_scale      in number default null);

procedure delete_table  (p_appl_short_name in varchar2,
                                        p_tab_name             in varchar2);

procedure delete_column (p_appl_short_name in varchar2,
                                           p_tab_name             in varchar2,
                                           p_col_name             in varchar2);




Example of Using the AD_DD Package:

Here is an example of using the AD_DD package to register a flexfield table and its columns:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID', 1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE', 2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SUMMARY_FLAG', 8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG', 9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16, 'VARCHAR2', 60, 'Y', 'N');


Print This Post