Friday, June 26, 2020
How to register a custom table in Oracle Apps R12 (AD_DD.REGISTER_TABLE and AD_DD.REGISTER_COLUMN)
In this post, we have given a script which can be used to register a custom table in Oracle applications.
Prerequisites: Table should be created in database and “APPS” schema should have access to it.
Tested on: R12
API Used:
AD_DD.REGISTER_TABLE
AD_DD.REGISTER_COLUMN
AD_DD.REGISTER_PRIMARY_KEY
AD_DD.REGISTER_PRIMARY_KEY_COLUMN
Script:
DECLARE lc_appl_short_name CONSTANT VARCHAR2(40) DEFAULT 'XXSH'; lc_tab_name CONSTANT VARCHAR2(32) DEFAULT 'XXSH_TEST_CUSTOM_STG'; lc_tab_type CONSTANT VARCHAR2(50) DEFAULT 'T'; lc_next_extent CONSTANT NUMBER DEFAULT 512; lc_pct_free CONSTANT NUMBER DEFAULT 10; lc_pct_used CONSTANT NUMBER DEFAULT 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 = lc_tab_name ) LOOP -- Call the API to register table ad_dd.register_table (p_appl_short_name => lc_appl_short_name, p_tab_name => table_detail.table_name, p_tab_type => lc_tab_type, p_next_extent => NVL(table_detail.next_extent,lc_next_extent), p_pct_free => NVL(table_detail.pct_free,lc_pct_free), p_pct_used => NVL(table_detail.pct_used,lc_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 = lc_tab_name ) LOOP -- Call the API to register column ad_dd.register_column (p_appl_short_name => lc_appl_short_name, p_tab_name => lc_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 = lc_tab_name ) LOOP -- Call the API to register primary_key ad_dd.register_primary_key (p_appl_short_name => lc_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 => lc_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; DBMS_OUTPUT.PUT_LINE ('SUCCESS'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); END;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
0 Responses to “How to register a custom table in Oracle Apps R12 (AD_DD.REGISTER_TABLE and AD_DD.REGISTER_COLUMN)”
Post a Comment