Register table in Apps

Register a custom table in Oracle Apps

1. Create the table in the database.

create table XX_USER_TABLE

(

user_id  NUMBER PRIMARY KEY,

user_name   VARCHAR2(40),

description VARCHAR2(100)

);




2.Execute the table registration API.

DECLARE

   v_appl_short_name   VARCHAR2 (40) := 'XXCUST';

   v_tab_name          VARCHAR2 (32) := 'XX_USER_TABLE'; -- 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      => 'XXCUST', 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;










Once the table registration API completes successfully, log in to Oracle Apps.

Responsibility: Application Developer

Navigation: Application > Database > Table

Query for the custom table, XX_USER_TABLE