Say you have a custom table called “ERPS_EMPLOYEE” with columns EMP_ID, EMP_NAME and EMP_TYPE in your database. You need to create a TABLE type Value set that pulls up information from 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.
So to make your custom table visible in front end ( while creating Value Set or in Alerts or Audits etc), you have to register it in Oracle Apps.
Let’s now see how to register a custom table. You will need API named AD_DD for this.
1. First you register the table using the below API:
/* Formatted on 10/19/2015 1:08:16 PM (QP5 v5.240.12305.39446) */
BEGIN
ad_dd.register_table (p_appl_short_name = > 'CUSTOM', --Application name in which you want to register
p_tab_name => 'ERPS_EMPLOYEE', --Table Name
p_tab_type => 'T', -- T for Transaction data , S for seeded data
p_next_extent => 512, -- default 512
p_pct_free => 10, -- Default 10
p_pct_used => 70 --Default 70
); |
|
|
2. Secondly register each of the columns as below:
Register Column EMP_ID
|
|
begin
ad_dd.register_column
(p_appl_short_name => 'CUSTOM', --Application Name
p_tab_name => 'ERPS_EMPLOYEE', --Table Name
p_col_name => 'EMP_ID', --Column Name
p_col_seq => 1, --Column Sequence
p_col_type => 'NUMBER', --Column Data type
p_col_width => 10, --Column Width
p_nullable => 'N', --Use'N' if mandatory column otherwise 'Y'
p_translate => 'N', --Use 'Y' if this has translatable values
p_precision => null, --Decimal precision
p_scale => NULL --Number of digits in number
);
end;
Commit;
|
Register Column EMP_NAME
|
|
begin
ad_dd.register_column
(p_appl_short_name => 'CUSTOM',
p_tab_name => 'ERPS_EMPLOYEE',
p_col_name => 'EMP_NAME',
p_col_seq => 2,
p_col_type => 'VARCHAR2',
p_col_width => 15,
p_nullable => 'Y',
p_translate => 'N',
p_precision => null,
p_scale => null
);
end;
|
Register Column EMP_TYPE
|
|
begin
ad_dd.register_column
(p_appl_short_name => 'CUSTOM',
p_tab_name => 'ERPS_EMPLOYEE',
p_col_name => 'EMP_TYPE',
p_col_seq => 3,
p_col_type => 'VARCHAR2',
p_col_width => 15,
p_nullable => 'Y',
p_translate => 'N',
p_precision => null,
p_scale => null);
end;
commit;
|
3. Thirdly you register Primary Key if the table has any using the below code snippet:
|
|
Begin
ad_dd.register_primary_key
(p_appl_short_name => 'CUSTOM', --Application Name
p_key_name => 'EMP_ID_PK', --Unique name for primary key
p_tab_name => 'ERPS_EMPLOYEE', --Table Name
p_description => 'Emp ID Primary Key', --Description
p_key_type => 'S', --S for Surrogate, D for Developer
p_audit_flag => 'Y',
p_enabled_flag => 'Y');
end;
commit;
|
4. Finally you register Primary Key column if your table has a primary key:
|
|
Begin
ad_dd.register_primary_key_column
(p_appl_short_name => 'CUSTOM', --Application Name
p_key_name => 'EMP_ID_PK', --Primary Key name given above
p_tab_name => 'ERPS_EMPLOYEE',--Table Name
p_col_name => 'EMP_ID', --Primary Column name
p_col_sequence => 1); --Column seq
end;
commit;
|
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 use the table ERPS_EMPLOYEE without any errors.
To delete the registered Tables and its columns, use the below API:
AD_DD.DELETE_COLUMN(appl_short_name,
table_name,
column_name);
AD_DD.DELETE_TABLE( appl_short_name, table_name);
No comments:
Post a Comment