Monday, 19 October 2015

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.  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.

reg1
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:
2. Secondly register each of the columns as below:
 
Register Column EMP_ID
Register Column EMP_NAME
Register Column EMP_TYPE
3. Thirdly you register Primary Key if the table has any using the below code snippet:
4. Finally you register Primary Key column if your table has a primary key:
Navigate to Application Developer responsibility > Application > Database > Table


Reg2

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.


Reg3

Check for the primary key information by clicking on the Primary Key button


Reg4

Now in your Value set, you will be able to  use the table ERPS_EMPLOYEE without any errors.


Reg5

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);

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete