Results for “table_name”

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

Overview

GEMMS_TABLES is a dictionary-style view owned by the APPS schema in Oracle E-Business Suite. Its documented purpose is to present all tables owned by Oracle Process Manufacturing (OPM). The view belongs to the GMA — Process Manufacturing Systems product family and is defined over the EBS Application Object Library (FND) dictionary tables FND_TABLES and FND_APPLICATION. In Oracle EBS 12.1.1 and 12.2.2 the view is registered as VALID, making it a supported, queryable object rather than a deprecated or custom artifact.

Searches for "fnd_tables" typically land on this object because GEMMS_TABLES is a filtered projection of FND_TABLES. Where FND_TABLES lists every registered table across the entire EBS instance, GEMMS_TABLES narrows the result set to the tables registered under the OPM application IDs. It therefore behaves as a lightweight, OPM-scoped index of the data dictionary, useful for diagnostics, dependency review, data-model discovery, and operational reporting on the Process Manufacturing schema. Because it exposes only the TABLE_NAME column, it is an inventory view rather than a metadata view: it answers "which tables exist in OPM," not "what columns or data types do they have."

Underlying Base Objects

The documented referenced base objects are FND_APPLICATION and FND_TABLES. Both are referenced as synonyms, which is the standard pattern for an APPS-owned view that must resolve the FND dictionary tables through the APPS synonym layer.

FND_APPLICATION is the application registry. It supplies the APPLICATION_ID, APPLICATION_SHORT_NAME, and APPLICATION_NAME of each registered EBS application. FND_TABLES is the core table registry maintained by AD Dictionary / AOL, recording each table's owning application via APPLICATION_ID, along with TABLE_NAME and related attributes. GEMMS_TABLES joins the two through a nested subquery: the outer SELECT reads TABLE_NAME from FND_TABLES, and the WHERE clause restricts APPLICATION_ID to those found in FND_APPLICATION whose APPLICATION_ID is one of (550, 551, 552, 553, 554, 555, 556). Those IDs correspond to the OPM application family rather than to a single product, which is why the view is broader than any one OPM module.

Key Columns

• TABLE_NAME — the only documented column. It carries the database object name of each OPM-owned table as registered in FND_TABLES, in the form expected by the data dictionary (for example, a GME, GMD, GMF, or related OPM-prefixed table).

Because the view projects a single column, consumers who need the owning APPLICATION_ID, the application short name, or column-level detail must join back to FND_TABLES, FND_APPLICATION, or FND_COLUMNS using TABLE_NAME as the linking key. The absence of APPLICATION_ID in the projection is worth noting in queries that span multiple OPM applications.

Common Use Cases and Queries

The primary use case is enumerating OPM tables for impact analysis, support investigations, and documentation. A second use case is validating that an OPM table is properly registered in the dictionary — an unregistered table will not appear here even if it exists physically in the database.

Simple inventory query:

• SELECT TABLE_NAME FROM APPS.GEMMS_TABLES ORDER BY TABLE_NAME;

Pattern-match for a table family, for example GME-prefixed objects:

• SELECT TABLE_NAME FROM APPS.GEMMS_TABLES WHERE TABLE_NAME LIKE 'GME%' ORDER BY 1;

Join back to the dictionary for the owning application, since GEMMS_TABLES does not expose APPLICATION_ID:

• SELECT gt.TABLE_NAME, fa.APPLICATION_SHORT_NAME FROM APPS.GEMMS_TABLES gt, APPS.FND_TABLES ft, APPS.FND_APPLICATION fa WHERE gt.TABLE_NAME = ft.TABLE_NAME AND ft.APPLICATION_ID = fa.APPLICATION_ID ORDER BY 1;

Confirm existence of a specific object during troubleshooting:

• SELECT TABLE_NAME FROM APPS.GEMMS_TABLES WHERE TABLE_NAME = 'GME_BATCH_HEADERS';

Queries should be run with the APPS schema or a synonym granted to it. Consistent with other FND dictionary views, access is governed by standard EBS dictionary privileges, and runtime performance is typically acceptable because the underlying dictionary tables are small relative to transactional data.