How to Create Custom Tables in Oracle Apps R12

 

Here we are creating Custom Table and it's Fields in Oracle Application. Let see how to create / register it through API named AD_DD. First you need to login in Oracle Application and go to Application Developer Responsibility -- Application -- Database -- Table.

 

After click on Table you will see custom table Popup, but here you can not create Tables and Field. You need to create though above Oracle Standard API AD_DD .

1) Create Table

Begin

ad_dd.register_table 

(p_appl_short_name => 'FND', --Application name in which you want to register 

p_tab_name                 =>  'PWC_CORR_PRODUCTION_NOTE', --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

);

End;

Commit;

 

2) Create Column

Begin

ad_dd.register_column

(p_appl_short_name =>   'FND', --Application Name

p_tab_name                 =>   'PWC_CORR_PRODUCTION_NOTE', --Table Name

p_col_name                  =>   'PRODUCTION_NOTE_NUMBER', --Column Name

p_col_seq                      =>   1, --Column Sequence

p_col_type                    =>   'NUMBER', --Column Data type

p_col_width                 =>   20, --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;

3) Create Primary key

Begin

ad_dd.register_primary_key

(p_appl_short_name =>   'FND', --Application Name

p_key_name                =>   'PRODUCTION_NOTE_NO_PK', --Unique name for primary key

p_tab_name                 =>   'PWC_CORR_PRODUCTION_NOTE', --Table Name

p_description               =>   'Production Note Number Primary Key', --Description

p_key_type                   =>   'S', --S for Surrogate, D for Developer

p_audit_flag                =>   'Y',

p_enabled_flag           =>   'Y');

End;

Commit;

4) Create Primary key Column

Begin

ad_dd.register_primary_key_column

(p_appl_short_name =>  'FND', --Application Name

p_key_name                =>  'PRODUCTION_NOTE_NUMBER_PK', --Primary Key name given above

p_tab_name                 =>  'PWC_CORR_PRODUCTION_NOTE',--Table Name

p_col_name                 =>  'PRODUCTION_NOTE_NUMBER', --Primary Column name

p_col_sequence           =>  1); --Column seq

End;

Commit;

If you create wrong column, you can delete through below query:

Begin

AD_DD.delete_column('FND',

                                             'PWC_CORR_PRODUCTION_NOTE'

                                            ,'WIP_ENTITY_ID');

End;

 

Check Table and Column after Creation:

SELECT * FROM  FND_TABLES

SELECT * FROM  FND_COLUMNS

 

 

If you have any query you can ask me or comment below. Thanks