FND_TABLES

FND_TABLES stores information about the registered tables in your applications. Each row includes the actual table name (TABLE_NAME) a more descriptive title (USER_TABLE_NAME), and a description. Each row also contains information about the storage parameters for the table, a flag (AUTO_SIZE) which indicates whether the install should automatically apply a sizing factor to the storage parameters, and the type of the table. You need one row for each table in each application. Oracle Applications uses this information for AuditTrail, when installing and upgrading your database, and for other purposes.

Name Datatype Length Mandatory Comments
APPLICATION_ID NUMBER (15) Yes Application identifier
TABLE_ID NUMBER (15) Yes Table identifier
TABLE_NAME VARCHAR2 (30) Yes Table name
USER_TABLE_NAME VARCHAR2 (80) Yes Table title
LAST_UPDATE_DATE DATE
Yes Standard Who column
LAST_UPDATED_BY NUMBER (15) Yes Standard Who column
CREATION_DATE DATE
Yes Standard Who column
CREATED_BY NUMBER (15) Yes Standard Who column
LAST_UPDATE_LOGIN NUMBER (15) Yes Standard Who column
AUTO_SIZE VARCHAR2 (1) Yes Flag to indicate if this table is automatically sized during an install
TABLE_TYPE VARCHAR2 (1) Yes Type of the table (seed data, transaction data, interim)
INITIAL_EXTENT NUMBER (15) Yes Initial extent storage parameter
NEXT_EXTENT NUMBER (15) Yes Next extent storage parameter
MIN_EXTENTS NUMBER (15) Yes Minimum extents storage parameter
MAX_EXTENTS NUMBER (15) Yes Maximum extents storage parameter
PCT_INCREASE NUMBER (15) Yes Percent increase storage parameter
INI_TRANS NUMBER (15) Yes Initial transactions storage parameter
MAX_TRANS NUMBER (15) Yes Maximum transactions storage parameter
PCT_FREE NUMBER (15) Yes Percent free storage parameter
PCT_USED NUMBER (15) Yes Percent used storage parameter
DESCRIPTION VARCHAR2 (240)
Description
HOSTED_SUPPORT_STYLE VARCHAR2 (30) Yes

Usage:


  cursor cur_tables(c_name   in dba_objects.object_name%type := '%'
                 , n_appid  in number) is
  select t.table_id   id
  ,      t.table_name name
  from  fnd_tables t
  where t.application_id = n_appid
  and t.table_name like c_name ESCAPE '\'
  order by table_name;

  cursor cur_tables_and_views(c_name   in dba_objects.object_name%type := '%'
                            , n_appid  in number) is
  select t.table_id   id
  ,      t.table_name name
  ,      'TABLE'      o_type
  from applsys.fnd_tables t
  where t.application_id = n_appid
  and t.table_name like c_name ESCAPE '\'
  UNION
  select v.view_id   id
  ,      v.view_name name
  ,      'VIEW'      o_type
  from applsys.fnd_views v
  where v.application_id = n_appid
  and   v.view_name like c_name ESCAPE '\'
  order by 2;

    cursor cur_name(n_appid in number
                  , n_tabid in number)
    is
    select table_name
    from applsys.fnd_tables
    where application_id = n_appid
    and   table_id = n_tabid;