![\"reg1\"](\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/reg1.png\")
<\/p>\nRegister Custom Tables in Oracle Apps:<\/p>\n
Say you have a custom table called “ERPS_EMPLOYEE” with columns EMP_ID, EMP_NAME and EMP_TYPE in your database.\u00a0 You need to create a\u00a0 TABLE type Value set\u00a0 that pulls up information from\u00a0 this table as LOV. If you give in the custom table name in “TABLE NAME” field in the “Validation Table Information” Form, Oracle Apps will not recognize it and you will get the below error saying table does not exist.<\/p>\n
<\/p>\n
So to make your custom table\u00a0 visible in front end ( while creating Value Set or in Alerts or Audits etc), you have to register it in Oracle Apps.<\/p>\n
Let’s now see how to register a custom table.\u00a0 You will need API named AD_DD for this.<\/p>\n
1.\u00a0 First you register the table using the below API: 3. Thirdly you register Primary Key if the table has any using the below code snippet: Navigate to Application Developer responsibility > Application > Database > Table Query for the table name that we have registered – “ERPS_EMPLOYEE”. Please note that you cannot register your table using this form in the front end. You will have to use API. This form is only meant for viewing the information. Check for the primary key information by clicking on the Primary Key button Now in your Value set, you will be able to\u00a0 use the table ERPS_EMPLOYEE without any errors. Register Custom Tables in Oracle Apps: Say you have a custom table called “ERPS_EMPLOYEE” with columns EMP_ID, EMP_NAME and EMP_TYPE in your database.\u00a0 You need to create a\u00a0 TABLE type Value set\u00a0 that pulls up information from\u00a0 this table as LOV. If you give in the custom table name in “TABLE NAME” field in the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[215],"class_list":["post-4656","post","type-post","status-publish","format-standard","hentry","category-sysadmin-and-aol","tag-register-table"],"yoast_head":"\n
\n
\nbegin
\nad_dd.register_table
\n(p_appl_short_name =>\u00a0 'CUSTOM',\u00a0\u00a0\u00a0 --Application name in which you want to register
\np_tab_name\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0 'ERPS_EMPLOYEE',\u00a0--Table Name
\np_tab_type\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0 'T',\u00a0\u00a0 -- T for Transaction data , S for seeded data
\np_next_extent\u00a0\u00a0\u00a0\u00a0 =>\u00a0 512,\u00a0\u00a0 -- default 512
\np_pct_free\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0 10,\u00a0\u00a0 -- Default 10
\np_pct_used\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0 70 --Default 70
\n);
\nend;
\ncommit;
\n<\/code>
\n2. Secondly register each of the columns as below:
\nRegister Column EMP_ID
\n
\nbegin
\nad_dd.register_column
\n(p_appl_short_name =>\u00a0\u00a0 'CUSTOM', --Application Name
\np_tab_name\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'ERPS_EMPLOYEE', --Table Name
\np_col_name\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'EMP_ID', --Column Name
\np_col_seq\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 1, --Column Sequence
\np_col_type\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'NUMBER', --Column Data type
\np_col_width\u00a0\u00a0\u00a0 =>\u00a0\u00a0 10, --Column Width
\np_nullable\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'N', --Use'N' if mandatory column otherwise 'Y'
\np_translate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'N', --Use 'Y' if this has translatable values
\np_precision\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 null, --Decimal precision
\np_scale\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 NULL --Number of digits in number
\n);
\nend;
\nCommit;
\n<\/code>
\nRegister Column EMP_NAME
\n
\nbegin
\nad_dd.register_column
\n(p_appl_short_name =>\u00a0\u00a0 'CUSTOM',
\np_tab_name\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'ERPS_EMPLOYEE',
\np_col_name\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'EMP_NAME',
\np_col_seq\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 2,
\np_col_type\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'VARCHAR2',
\np_col_width\u00a0\u00a0\u00a0 =>\u00a0\u00a0 15,
\np_nullable\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'Y',
\np_translate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'N',
\np_precision\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 null,
\np_scale\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 null
\n);
\nend;
\n<\/code>
\nRegister Column EMP_TYPE
\n
\nbegin
\nad_dd.register_column
\n(p_appl_short_name =>\u00a0\u00a0 'CUSTOM',
\np_tab_name\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'ERPS_EMPLOYEE',
\np_col_name\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'EMP_TYPE',
\np_col_seq\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 3,
\np_col_type\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'VARCHAR2',
\np_col_width\u00a0\u00a0\u00a0 =>\u00a0\u00a0 15,
\np_nullable\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'Y',
\np_translate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'N',
\np_precision\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 null,
\np_scale\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 null);
\nend;
\ncommit;
\n<\/code><\/p>\n
\n
\nBegin
\nad_dd.register_primary_key
\n(p_appl_short_name =>\u00a0\u00a0 'CUSTOM', --Application Name
\np_key_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'EMP_ID_PK', --Unique name for primary key
\np_tab_name\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'ERPS_EMPLOYEE', --Table Name
\np_description\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'Emp ID Primary Key', --Description
\np_key_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'S', --S for Surrogate, D for Developer
\np_audit_flag\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'Y',
\np_enabled_flag\u00a0\u00a0\u00a0 =>\u00a0\u00a0 'Y');
\nend;
\ncommit;
\n<\/code>
\n4. Finally you register Primary Key column if your table has a primary key:
\n
\nBegin
\nad_dd.register_primary_key_column
\n(p_appl_short_name =>\u00a0 'CUSTOM', --Application Name
\np_key_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0 'EMP_ID_PK', --Primary Key name given above
\np_tab_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0 'ERPS_EMPLOYEE',--Table Name
\np_col_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =>\u00a0 'EMP_ID', --Primary Column name
\np_col_sequence\u00a0\u00a0\u00a0 =>\u00a0 1); --Column seq
\nend;
\ncommit; <\/code><\/p>\n
\n![\"Reg2\"](\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Reg2.png\")
<\/p>\n
\n![\"Reg3\"](\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Reg3.png\")
<\/p>\n
\n![\"Reg4\"](\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Reg4.png\")
<\/p>\n
\n![\"Reg5\"](\"http:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/Reg5.png\")
<\/p>\nTo delete the registered Tables and its columns, use the below API:<\/div>\nAD_DD.DELETE_COLUMN(appl_short_name,<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0table_name,<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0column_name);<\/span><\/div>\n\u00a0AD_DD.DELETE_TABLE(\u00a0appl_short_name, table_name);<\/div>\n<\/div>\n\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"