Table Registration Procedure in Oracle Applications

CREATE OR REPLACE

PROCEDURE XX_TABLE_REGISTRATION_PROC(

    Errbuf OUT VARCHAR2,

    Retcode OUT VARCHAR2,

    l_appl_short_name IN VARCHAR2,

    l_tab_name        IN VARCHAR2)

IS

  v_appl_short_name VARCHAR2 (50) := l_appl_short_name;

  v_tab_name        VARCHAR2 (30) := l_tab_name;

  v_tab_type        VARCHAR2 (30) := 'T'; --object type T for table

  v_next_extent     NUMBER        := 514;

  v_pct_free        NUMBER;

  v_pct_used        NUMBER;

BEGIN

  -- Unregister the custom table if it exists

  ad_dd.delete_table (p_appl_short_name => l_appl_short_name, p_tab_name => l_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, 514),

                     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;

  ---Registration of primary key

  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', --PK Type Surrogate

                               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;

  Fnd_File.Put_line(Fnd_File.Output ,'Table'||' '||v_tab_name||' '|| 'Registered successfully in' ||' '||v_appl_short_name||' '||'Schema');

EXCEPTION

  WHEN OTHERS THEN

    FND_FILE.PUT_LINE(FND_FILE.LOG ,'Error Occured during table registration');

END;