Oracle Apps Table Registeration Script

DECLARE
l_schema VARCHAR2(30) := 'XX';
l_tab_name VARCHAR2(30) := 'XX_CUSTOM_TABLE';
CURSOR c_get_tab_info IS
SELECT next_extent,
pct_free,
pct_used
FROM all_tables
WHERE owner = l_schema
AND table_name = l_tab_name;
CURSOR c_get_col_info IS
SELECT column_name,
column_id,
data_type,
data_length,
nullable,
--, data_precision,
data_scale
FROM all_tab_columns
WHERE owner = l_schema
AND table_name = l_tab_name
ORDER BY column_id;

CURSOR c_get_pk_info IS
SELECT constraint_name,
DECODE(status, 'ENABLED', 'Y', 'N') enabled_flag
FROM all_constraints
WHERE owner = l_schema
AND table_name = l_tab_name
AND constraint_type = 'P';

CURSOR c_get_pk_col_info(cp_pk_name VARCHAR2) IS
SELECT column_name,
position
FROM all_cons_columns
WHERE owner = l_schema
AND table_name = l_tab_name
AND constraint_name = cp_pk_name
ORDER BY position;

BEGIN

FOR tab_rec IN c_get_tab_info LOOP

ad_dd.register_table(p_appl_short_name => l_schema
,p_tab_name => l_tab_name
,p_tab_type => 'T'
,p_next_extent => tab_rec.next_extent
,p_pct_free => tab_rec.pct_free
,p_pct_used => tab_rec.pct_used);

END LOOP;

FOR col_rec IN c_get_col_info
LOOP

ad_dd.register_column(p_appl_short_name => l_schema
,p_tab_name => l_tab_name
,p_col_name => col_rec.column_name
,p_col_seq => col_rec.column_id
,p_col_type => col_rec.data_type
,p_col_width => col_rec.data_length
,p_nullable => col_rec.nullable
,p_translate => 'N'
,p_precision => col_rec.data_precision
,p_scale => col_rec.data_scale);

END LOOP;

FOR pk_rec IN c_get_pk_info
LOOP
ad_dd.register_primary_key(p_appl_short_name => l_schema,
p_key_name => pk_rec.constraint_name,
p_tab_name => l_tab_name,
p_description => NULL,
p_key_type => 'S',
p_audit_flag => 'Y',
p_enabled_flag => pk_rec.enabled_flag);


FOR pk_col_rec IN c_get_pk_col_info(pk_rec.constraint_name)
LOOP
ad_dd.register_primary_key_column
(p_appl_short_name => l_schema,
p_key_name => pk_rec.constraint_name,
p_tab_name => l_tab_name,
p_col_name => pk_col_rec.column_name,
p_col_sequence => pk_col_rec.position);
END LOOP;

END LOOP;

END;
/
COMMIT;