Table registration API in Oracle Apps

Table registration API in Oracle Apps, www.askhareesh.com

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;

*/