Script To register the Custom Table In Oracle Application using AD_DD_REGISTER_TABLE

SET SERVEROUTPUT ON;

DECLARE
   l_appl_short_name                            VARCHAR2(40) := 'XXSSN';
   l_tab_name                                   VARCHAR2(32) := 'XXJG_ADDL_INFO_DFF_TBL';  -- Change the table name if you require
   l_tab_type                                   VARCHAR2(50) := 'T';
   l_next_extent                                NUMBER := 512;
   l_pct_free                                   NUMBER;
   l_pct_used                                   NUMBER;
BEGIN
   -- Unregister the custom table if it exists
   ad_dd.delete_table( p_appl_short_name => 'XXSSN', 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 = l_tab_name)
   LOOP
      DBMS_OUTPUT.put_line(
            'Registering Table : '
         || l_tab_name);
      ad_dd.register_table(
         p_appl_short_name                         => l_appl_short_name
       , p_tab_name                                => tab_details.table_name
       , p_tab_type                                => l_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 dba_tab_columns
                         WHERE table_name = l_tab_name)
   LOOP
      DBMS_OUTPUT.put_line(
            'Registering Column : '
         || all_tab_cols.column_name);
      ad_dd.register_column(
         p_appl_short_name                         => l_appl_short_name
       , p_tab_name                                => l_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 = l_tab_name)
   LOOP
      ad_dd.register_primary_key(
         p_appl_short_name                         => l_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                         => l_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;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            'UNEXP_ERROR : '
         || SUBSTR( SQLERRM, 1, 250));
END;
/

Script to Explode the DBA_DEPENDENCIES Hierachy till Nth Level

    SELECT DISTINCT referenced_name
                  , referenced_type
                  , LEVEL
                  ,    LPAD( ' ', 2 * (LEVEL - 1))
                    || name
                  , owner
                  , name
                  , TYPE
      FROM (SELECT *
              FROM dba_dependencies
             WHERE 1 = 1
               AND (referenced_name LIKE 'XX%'
                 OR referenced_name LIKE 'XX_1%')
               AND (name LIKE 'XX_%'
                 OR name LIKE 'XX_1%')
               AND name <> referenced_name)
START WITH referenced_name =<<&p_object_name>>
CONNECT BY NOCYCLE referenced_name = PRIOR name
  ORDER BY LEVEL