add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, January 24

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;

No comments: