Search Results ax_query_tables




Overview
Explore in AI Chat

Overview

The AX_QUERY_TABLES table is a core configuration object within the Global Accounting Engine (AX) module of Oracle E-Business Suite, documented as VALID in releases 12.1.1 and 12.2.2. Its description—"Tables used by a query chain"—identifies it as the registry that defines which database tables participate in a given AX query chain. The Global Accounting Engine relies on query chains to construct, sequence, and execute accounting extraction logic against source subledger tables, and AX_QUERY_TABLES is the metadata layer that maps each chain to the physical tables it reads. Without this registry, a query chain has no defined table scope, and the clause definitions built on top of it cannot resolve properly.

From a modeling perspective, the mined foreign-key structure and the presence of descriptive attributes such as TABLE_NAME, TABLE_APPLICATION_ID, and JOIN_TYPE suggest a satellite-leaning classification. The table behaves primarily as a dependent attribute store attached to a parent chain entity, extending AX_QUERY_CHAINS with the table-level detail required for query execution rather than acting as an independent hub or a many-to-many link on its own.

Key Information Stored

The table contains eleven documented columns. The most significant are:

  • APPLICATION_ID — The application owning the query chain; part of the primary key and a foreign key to AX_QUERY_CHAINS.
  • QUERY_CHAIN_NAME — The name of the query chain to which the table belongs; also part of the primary key and foreign key.
  • TABLE_NUMBER — A sequence number ordering the table within the chain; completes the primary key.
  • TABLE_NAME — The physical database table referenced by the chain entry; captured in the unique index AX_QUERY_TABLES_U2.
  • TABLE_APPLICATION_ID — The application that owns the referenced table, allowing cross-application (multi-org) table references.
  • JOIN_TYPE — The join semantics (for example, inner or outer) applied when the table is combined with others in the chain.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns.

The surrogate primary key is AX_QUERY_TABLES_PK (APPLICATION_ID, QUERY_CHAIN_NAME, TABLE_NUMBER). Two unique indexes act as business-key candidates: AX_QUERY_TABLES_U1 (APPLICATION_ID, QUERY_CHAIN_NAME, TABLE_NUMBER), mirroring the PK, and AX_QUERY_TABLES_U2 (APPLICATION_ID, QUERY_CHAIN_NAME, TABLE_NAME), which prevents the same table from being registered twice within one chain under a duplicate name.

Common Use Cases and Queries

Typical usage centers on diagnosing and extending query chain definitions. Reporting queries frequently join this table to AX_QUERY_CHAINS to list the tables used by a specific chain:

  • Select all tables for a chain: SELECT TABLE_NUMBER, TABLE_APPLICATION_ID, TABLE_NAME, JOIN_TYPE FROM AX.AX_QUERY_TABLES WHERE APPLICATION_ID = :app AND QUERY_CHAIN_NAME = :chain ORDER BY TABLE_NUMBER;
  • Find chains referencing a given source table: use AX_QUERY_TABLES_U2 by filtering on TABLE_NAME and TABLE_APPLICATION_ID.
  • Validate join configuration before promoting a chain between environments.
  • Reconcile clause definitions by joining AX_CLAUSES back to this table on APPLICATION_ID, QUERY_CHAIN_NAME, and TABLE_NUMBER.

Related Objects

  • AX_QUERY_CHAINS — Parent entity; AX_QUERY_TABLES.APPLICATION_ID and QUERY_CHAIN_NAME reference this table.
  • AX_CLAUSES — Child entity; references AX_QUERY_TABLES via APPLICATION_ID, QUERY_CHAIN_NAME, and TABLE_NUMBER.
  • These two tables form the essential dependency set. Beyond them, the Object Library references the AX_QUERY_TABLES_PK and AX_QUERY_TABLES_UK indexes, which DBA and support staff can query through ALL_INDEXES, ALL_CONSTRAINTS, and ALL_CONS_COLUMNS when troubleshooting chain integrity.