Oracle Applications : Table Registraion

Why we need table registration?

There are many places (specially flexfield validations) are performed using the fnd_tables. Also in CRM a lot of metadata objects expect you to register table for this. Simple example is, if you are creating a value set and you want the validation type as Table, your custom table will come in LOV only if you have registered the table.

Example
SQL> create table wip_new_items(item  varchar2(10),
  2               itemdesc varchar2(100),
  3           itemcost number(9),
  4           created_by number(8),
  5           creation_date date,
  6           last_updated_by number(8),
  7           last_update_date date,
  8           attribute_category varchar2(150),
  9           attribute1    varchar2(150),
 10           attribute2    varchar2(150),
 11           attribute3    varchar2(150),
 12           attribute4    varchar2(150),
 13           attribute5    varchar2(150));

Table created.

SQL> /
create table wip_new_items(item  varchar2(10),
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> GRANT ALL ON WIP_NEW_ITEMS TO APPS;

Grant succeeded.

SQL> CONN APPS/APPS@PROD
Connected.
SQL> CREATE PUBLIC SYNONYM WIP_NEW_ITEMS FOR WIP.WIP_NEW_ITEMS;

Synonym created.

SQL> EXEC AD_DD.REGISTER_TABLE('WIP','WIP_NEW_ITEMS','T',8,10,90);

PL/SQL procedure successfully completed.

SQL> EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_NEW_ITEMS','ITEM',1,'VARCHAR2',10,'N','Y');

PL/SQL procedure successfully completed.

SQL> EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_NEW_ITEMS','ITEMDESC',2,'VARCHAR2',100,'N','Y');

PL/SQL procedure successfully completed.

SQL> EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_NEW_ITEMS','ITEMCOST',3,'NUMBER',9,'N','Y');