Monday, May 6, 2013

Registering Custom Table in Oracle Applications



DECLARE
   v_appl_short_name   VARCHAR2 (40) := 'PER';
   v_tab_name          VARCHAR2 (32) := 'XX_ORACLEAPPS88_BLOGSPOT_COM'; -- Change the table name if you require
   v_tab_type          VARCHAR2 (50) := 'T';
   v_next_extent       NUMBER        := 512;
   v_pct_free          NUMBER;
   v_pct_used          NUMBER;
BEGIN
   -- Unregister the custom table if it exists
  ----- ad_dd.delete_table (p_appl_short_name             => 'PER', p_tab_name => v_tab_name);

   -- Register the custom table
   FOR 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                    => tab_details.table_name,
                            p_tab_type                    => v_tab_type,
                            p_next_extent                 => NVL (tab_details.next_extent, 512),
                            p_pct_free                    => NVL (tab_details.pct_free, 10),
                            p_pct_used                    => NVL (tab_details.pct_used, 70)
                           );
   END LOOP;

   -- Register the columns of custom table
   FOR 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                    => all_tab_cols.column_name,
                             p_col_seq                     => all_tab_cols.column_id,
                             p_col_type                    => all_tab_cols.data_type,
                             p_col_width                   => all_tab_cols.data_length,
                             p_nullable                    => 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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect