Table registration API in Oracle Apps

Table registration API in Oracle Apps

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