In my last post I talked about how to register table in Oracle Application. The steps involved are too much and will take lot of time if we have to create multiple tables or a single table is having too many columns.
To Save time you can use below script which will do the job for you. I am considering the your table is already created.
DECLARE vc_appl_short_name CONSTANT VARCHAR2 (40) := 'C_APPS'; vc_tab_name CONSTANT VARCHAR2 (32) := 'C_FND_INTERFACES'; 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 -- Start Register Custom Table -- Get the table details in cursor 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 = vc_tab_name) LOOP -- Call the API to register table ad_dd.register_table (p_appl_short_name => vc_appl_short_name, 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; -- End Register Custom Table -- Start Register Columns -- 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 = vc_tab_name) LOOP -- Call the API to register column ad_dd.register_column (p_appl_short_name => vc_appl_short_name, p_tab_name => vc_tab_name, 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; -- End Register Columns -- Start Register Primary Key -- 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 = vc_tab_name) LOOP -- Call the API to register primary_key ad_dd.register_primary_key (p_appl_short_name => vc_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' ); -- Start Register Primary Key Column -- 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 -- Call the API to register primary_key_column ad_dd.register_primary_key_column (p_appl_short_name => vc_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 Register Primary Key Column END LOOP; -- End Register Primary Key COMMIT; END;Let’s go and validate in application.
F11 –> Enter your table_name ‘C_FND_INTERFACES’ –> Ctrl + F11.


See Also : Registering Custom Table Using Script
p_audit_flag => 'N' should be 'Y' in case you would like to enable audit on this table!
ReplyDeletedeclare
Deletei integer;
begin
ad_dd.update_primary_key('SCHEMA_NAME','PRIMARY_KEY_NAME','TABLE_NAME','Updating Audit Flag','S','Y','Y');
commit;
end;
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDelete