Register a table and all its columns in Oracle Applications
If we want to build a Custom Web ADI using custom table or standard table.
Or we want to define a custom dff or custom kff on a custom form then just creating a table may not be enough.
We also need to register this table so that the entry for this table exists in fnd_tables. And the entries for its columns exist in fnd_columns table.
By using the below procedure we can define a concurrent program and run it by passing the input parameters of the concurrent program as table name.
When this concurrent program completes normal it would have registered the table and all its columns by using a standard API ad_dd.
This program can be rerun any number of times, especially if there are new columns are created or modified. This program will delete the entries and register it again.
retcode OUT VARCHAR2,
p_owner IN VARCHAR2,
p_table IN VARCHAR2)
IS
vc_tab_type CONSTANT VARCHAR2 (50) := 'T';
vc_next_extent CONSTANT NUMBER := 512;
vc_pct_free CONSTANT NUMBER := 10;
vc_pct_used CONSTANT NUMBER := 70;
BEGIN
fnd_file.put_line (fnd_file.output,
'Start delete the existing registrations..');
BEGIN
FOR table_columns IN (SELECT column_name,
column_id,
data_type,
data_length,
nullable
FROM all_tab_columns
WHERE table_name = p_table)
LOOP
fnd_file.put_line (
fnd_file.output,
'Deleting column ..' || table_columns.column_name);
ad_dd.delete_column (
p_appl_short_name => p_owner,
p_tab_name => p_table,
p_col_name => table_columns.column_name);
END LOOP;
FOR table_detail IN (SELECT table_name,
tablespace_name,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent
FROM dba_tables
WHERE table_name = p_table)
LOOP
fnd_file.put_line (fnd_file.output,
'Deleting table ..' || table_detail.table_name);
ad_dd.delete_table (p_appl_short_name => p_owner,
p_tab_name => table_detail.table_name);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.output,
'Error occured in deleting the registration ..' || SQLERRM);
END;
fnd_file.put_line (fnd_file.output, ' Start Register Custom Table..');
fnd_file.put_line (fnd_file.output, 'Get the table details in cursor..');
BEGIN
FOR table_detail IN (SELECT table_name,
tablespace_name,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent
FROM dba_tables
WHERE table_name = p_table)
LOOP
fnd_file.put_line (fnd_file.output,
' Call the API to register table');
ad_dd.register_table (
p_appl_short_name => p_owner,
p_tab_name => table_detail.table_name,
p_tab_type => vc_tab_type,
p_next_extent => NVL (table_detail.next_extent,
vc_next_extent),
p_pct_free => NVL (table_detail.pct_free, vc_pct_free),
p_pct_used => NVL (table_detail.pct_used, vc_pct_used));
END LOOP;
fnd_file.put_line (fnd_file.output, 'End Register Custom Table..');
fnd_file.put_line (fnd_file.output, '-- Start Register Columns');
fnd_file.put_line (fnd_file.output,
'-- Get the column details of the table in cursor');
FOR table_columns IN (SELECT column_name,
column_id,
data_type,
data_length,
nullable
FROM all_tab_columns
WHERE table_name = p_table)
LOOP
fnd_file.put_line (
fnd_file.output,
'-- -- Call the API to register column'
|| table_columns.column_name);
ad_dd.register_column (
p_appl_short_name => p_owner,
p_tab_name => p_table,
p_col_name => table_columns.column_name,
p_col_seq => table_columns.column_id,
p_col_type => table_columns.data_type,
p_col_width => table_columns.data_length,
p_nullable => table_columns.nullable,
p_translate => 'N',
p_precision => NULL,
p_scale => NULL);
END LOOP;
fnd_file.put_line (fnd_file.output, '-- End Register Columns');
fnd_file.put_line (fnd_file.output, '-- Start Register Primary Key');
fnd_file.put_line (
fnd_file.output,
'-- Get the primary key detail of the table in cursor');
FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
FROM all_constraints
WHERE constraint_type = 'P' AND table_name = p_table)
LOOP
fnd_file.put_line (
fnd_file.output,
'-- Call the API to register primary_key'
|| all_keys.constraint_name);
ad_dd.register_primary_key (
p_appl_short_name => p_owner,
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');
fnd_file.put_line (fnd_file.output,
'-- Start Register Primary Key Column');
fnd_file.put_line (fnd_file.output,
'-- Get the primary key column detial in cursor');
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
fnd_file.put_line (
fnd_file.output,
'-- -- Call the API to register primary_key_column'
|| all_columns.column_name);
ad_dd.register_primary_key_column (
p_appl_short_name => p_owner,
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;
fnd_file.put_line (fnd_file.output,
'-- -- End Register Primary Key Column');
END LOOP;
fnd_file.put_line (fnd_file.output,
'-- -- -- End Register Primary Key');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.output,
'Error occured in creating the registration ..' || SQLERRM);
END;
END;
/
Comments
Post a Comment