Results for “fnd_tables”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

FND_TABLES is the Oracle E-Business Suite repository of database tables that have been registered with the Application Object Library (FND) dictionary. It resides in the APPLSYS schema and is documented as VALID in ETRM for both 12.1.1 and 12.2.2. Every table that participates in the EBS metadata model — including flexfields, concurrent programs, audit definitions, and business object registrations — must have a corresponding row in FND_TABLES carrying its APPLICATION_ID and TABLE_ID. In this sense FND_TABLES is the master inventory of the EBS physical data dictionary; downstream dictionary tables such as FND_COLUMNS, FND_INDEXES, FND_PRIMARY_KEYS, and FND_FOREIGN_KEYS all hang off it.

Following the heuristic Data Vault classification mined from the foreign key structure, FND_TABLES behaves as a hub: its primary key is a stable, non-descriptive business key surrogate (APPLICATION_ID, TABLE_ID) that anchors many dependent tables. This is a modeling suggestion rather than a physical constraint of the EBS schema.

Key Information Stored

• APPLICATION_ID — the FND application that owns the registered table; part of the primary key and a foreign key to FND_APPLICATION.
• TABLE_ID — the FND-assigned internal identifier for the table; the second component of the primary key.
• TABLE_NAME — the physical database table name; jointly unique with APPLICATION_ID per FND_TABLES_U2 (and ZD_EDITION_NAME in 12.2.2).
• USER_TABLE_NAME — the user-facing table name used in the Oracle Forms/dictionary UI; jointly unique with APPLICATION_ID per FND_TABLES_U3.
• TABLE_TYPE — classification of the table kind within the dictionary.
• DESCRIPTION — the descriptive text shown when browsing the registered table.
• HOSTED_SUPPORT_STYLE — hosting support classification for the registered object.
• ZD_EDITION_NAME — edition-based redefinition marker used in 12.2.x; part of all three unique indexes.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns for who registered or changed the dictionary record.
• Storage attributes (AUTO_SIZE, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, INI_TRANS, MAX_TRANS, PCT_FREE, PCT_USED) — legacy sizing information retained for the registered table.
• IREP_COMMENTS, IREP_ANNOTATIONS — Oracle Reports-related descriptive attributes.

The surrogate primary key is FND_TABLES_PK (APPLICATION_ID, TABLE_ID). The business-key candidates are the three unique indexes FND_TABLES_U1, U2, and U3, which respectively add TABLE_ID, TABLE_NAME, and USER_TABLE_NAME uniqueness ahead of ZD_EDITION_NAME. All 25 documented columns are physically present in the 12.2.2 schema.

Common Use Cases and Queries

FND_TABLES is the natural starting point for any dictionary-metadata query: to find the columns of a table, resolve an application ID, or enumerate the physical tables owned by a given product. A typical lookup by table name is:

• SELECT APPLICATION_ID, TABLE_ID, TABLE_NAME, USER_TABLE_NAME, DESCRIPTION FROM FND_TABLES WHERE TABLE_NAME = :name;
• SELECT c.COLUMN_NAME, c.DESCRIPTION FROM FND_COLUMNS c JOIN FND_TABLES t ON c.APPLICATION_ID = t.APPLICATION_ID AND c.TABLE_ID = t.TABLE_ID WHERE t.TABLE_NAME = :name;
• SELECT i.INDEX_NAME, i.UNIQUENESS FROM FND_INDEXES i JOIN FND_TABLES t ON i.APPLICATION_ID = t.APPLICATION_ID AND i.TABLE_ID = t.TABLE_ID;
• SELECT t.TABLE_NAME, a.APPLICATION_SHORT_NAME FROM FND_TABLES t JOIN FND_APPLICATION a ON t.APPLICATION_ID = a.APPLICATION_ID ORDER BY 2, 1;

Common operational and reporting scenarios include auditing the dictionary for tables registered under an obsolete application, verifying that a custom table has been properly registered before flexfield or audit setup, tracing which FND_AUDIT_TABLES definitions reference a given base table, and generating documentation of the EBS data dictionary for compliance or migration projects. Because FND_TABLES is also referenced by AD_MERGE_ACTION_TABLES, ALR_ALERTS, SO_OBJECTS, and FND_BUILDING_BLOCK_OBJECTS, it is frequently joined when diagnosing merge actions, alert definitions, or object-library building blocks.

Related Objects

• FND_APPLICATION — parent of FND_TABLES; joined via FND_TABLES.APPLICATION_ID = FND_APPLICATION.APPLICATION_ID.
• FND_COLUMNS — holds the column dictionary for each registered table; joined by APPLICATION_ID and TABLE_ID.
• FND_INDEXES — index metadata for registered tables; joined by APPLICATION_ID and TABLE_ID.
• FND_PRIMARY_KEYS — primary-key definitions for registered tables; joined by APPLICATION_ID and TABLE_ID.
• FND_FOREIGN_KEYS — foreign-key definitions for registered tables; joined by APPLICATION_ID and TABLE_ID.
• FND_AUDIT_TABLES — audit definitions referencing FND_TABLES via TABLE_APP_ID and TABLE_ID.
• ALR_ALERTS — alert definitions referencing FND_TABLES via TABLE_APPLICATION_ID and TABLE_ID.
• AD_MERGE_ACTION_TABLES — merge-action table list referencing FND_TABLES via TABLE_APPLICATION_ID and TABLE_ID.
• FND_DESCRIPTIVE_FLEXS and FND_ID_FLEXS — flexfield definitions referencing FND_TABLES via TABLE_APPLICATION_ID and APPLICATION_TABLE_NAME.
• FND_BUILDING_BLOCK_OBJECTS and SO_OBJECTS — object library and order-management object registrations referencing FND_TABLES.