Search Results lookup'




The FND_TABLES_ALL_VIEW in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 is a critical data dictionary view that provides comprehensive metadata about tables registered within the Applications Object Library (AOL). This view serves as a centralized repository for table definitions, offering developers, administrators, and integration specialists essential information about the underlying database structure. Below is a detailed analysis of its purpose, structure, and usage in Oracle EBS.

Purpose and Significance

FND_TABLES_ALL_VIEW consolidates metadata from multiple base tables, including FND_TABLES, FND_APPLICATION, and FND_PRODUCT_INSTALLATIONS, to present a unified view of all registered tables across Oracle EBS modules. Its primary functions include:
  • Metadata Repository: Stores table names, owners, application associations, and technical attributes (e.g., table type, storage parameters).
  • Integration Support: Facilitates external integrations by exposing table structures without direct database access.
  • Customization Tracking: Helps identify custom tables added to the EBS environment.

Key Columns and Structure

The view includes columns that categorize tables by application, module, and technical properties. Notable columns include:
  • APPLICATION_ID: Links the table to an EBS application (e.g., GL, AP).
  • TABLE_NAME: The physical name of the table in the database.
  • BASE_TABLE_FLAG: Indicates if the table is a base table or a view.
  • USER_TABLE_NAME: A user-friendly name for the table.
  • DESCRIPTION: Functional description of the table's purpose.
  • MAINTENANCE_MODE: Specifies if the table is customizable (e.g., 'C' for Custom, 'S' for Seed Data).

Usage Scenarios

  1. Development & Debugging: Developers query this view to validate table registrations or resolve issues like missing AOL metadata.
  2. Data Migration: Used to map source tables during legacy system migrations to EBS.
  3. Security Audits: Administrators review registered tables to ensure compliance with data access policies.
  4. Custom Report Design: Report writers identify tables and joins for custom reporting solutions.

Technical Considerations

  • Performance: Queries against this view may require optimization due to its reliance on joins with underlying AOL tables.
  • Version Differences: While the core structure remains consistent between 12.1.1 and 12.2.2, minor column additions may exist in later versions.
  • Dependencies: The view relies on AOL's registration framework; unregistered tables won't appear here.

Example Query

SELECT table_name, user_table_name, description 
FROM fnd_tables_all_view 
WHERE application_id = 101 
ORDER BY table_name;
This query retrieves all tables registered under the General Ledger application (ID 101).

Best Practices

  • Use FND_TABLES_ALL_VIEW instead of direct database catalog queries (e.g., USER_TABLES) to ensure EBS context is considered.
  • Combine with other AOL views like FND_COLUMNS for complete entity-relationship analysis.
  • Document custom tables by registering them via AOL's Table Registration form.
In summary, FND_TABLES_ALL_VIEW is an indispensable tool for EBS professionals, bridging the gap between the physical database and application-layer metadata. Its comprehensive coverage of table attributes supports development, maintenance, and governance activities across the EBS lifecycle.