Search Results ax_query_chains




Overview
Explore in AI Chat

Overview

AX_QUERY_CHAINS is a table in the AX schema, owned by the Global Accounting Engine product within Oracle E-Business Suite (documented for 12.1.1 and 12.2.2). It stores query chain information — the ordered definitions of tables and structures that the Global Accounting Engine (AX) traverses when generating accounting distributions and journal entries. Query chains act as a metadata-driven routing mechanism: rather than hard-coding which tables participate in a derivation, the AX engine reads chain definitions from this table and follows them at runtime.

The table carries 12 documented columns and is classified as VALID in the ETRM repository. Under a heuristic Data Vault classification, AX_QUERY_CHAINS is hub-leaning: its composite primary key (APPLICATION_ID, QUERY_CHAIN_NAME) identifies a durable, business-meaningful entity — a named query chain — around which dependent child tables cluster. This classification is a modeling suggestion only, not a physical constraint imposed by EBS.

Key Information Stored

The most significant columns in AX_QUERY_CHAINS are:

  • APPLICATION_ID — the owning application identifier; part of the composite primary key and the primary partitioning discriminator between chains belonging to different AX-enabled applications.
  • QUERY_CHAIN_NAME — the user-defined name of the query chain; the second component of the primary key and the primary human-readable identifier.
  • TABLE_APPLICATION_ID — the application that owns the starting table referenced by the chain, allowing cross-application table resolution.
  • STARTING_TABLE — the table from which the query chain begins its traversal; the entry point for the derivation logic.
  • FREQUENCY — a control attribute describing how often or under what cadence the chain is processed.
  • ENABLED_FLAG — indicates whether the chain is active and therefore eligible for use by the AX engine.
  • DESCRIPTION — free-text explanation of the chain's purpose, useful for functional documentation.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns recording who last changed the row and when.
  • CREATION_DATE, CREATED_BY — standard EBS audit columns recording row creation.

The surrogate-style primary key is AX_QUERY_CHAINS_PK on (APPLICATION_ID, QUERY_CHAIN_NAME). A unique index, AX_QUERY_CHAINS_U1, exists on the same columns, confirming that APPLICATION_ID combined with QUERY_CHAIN_NAME is the business-key candidate: no two chains may share a name within the same application.

Common Use Cases and Queries

Typical scenarios involve auditing which query chains are enabled, tracing how a distribution header was derived, and validating chain configuration after patching or migration. A fundamental lookup returns active chains for a given application:

  • SELECT query_chain_name, starting_table, table_application_id, frequency FROM ax.ax_query_chains WHERE application_id = :app_id AND enabled_flag = 'Y';
  • SELECT a.query_chain_name, t.table_name FROM ax.ax_query_chains a, ax.ax_query_tables t WHERE a.application_id = t.application_id AND a.query_chain_name = t.query_chain_name;
  • SELECT h.* FROM ax.ax_distrib_headers h, ax.ax_query_chains c WHERE h.application_id = c.application_id AND h.query_chain_name = c.query_chain_name;

Reporting use cases include impact analysis before disabling a chain, verifying that every chain defined for an application has at least one entry in AX_QUERY_TABLES, and comparing chain definitions across environments during a 12.1.1-to-12.2.2 upgrade.

Related Objects

The following objects reference AX_QUERY_CHAINS through the documented foreign keys, joining on (APPLICATION_ID, QUERY_CHAIN_NAME):

  • AX_DISTRIB_HEADERS — distribution header records whose derivation is governed by the chain; FK on APPLICATION_ID and QUERY_CHAIN_NAME.
  • AX_QUERY_TABLES — the ordered list of tables that constitute the chain; FK on APPLICATION_ID and QUERY_CHAIN_NAME.
  • AX_STRUCTURES — accounting structure definitions associated with the chain; FK on APPLICATION_ID and QUERY_CHAIN_NAME.

Together these three dependent objects form the complete query chain definition: AX_QUERY_CHAINS supplies the header, AX_QUERY_TABLES supplies the traversal path, and AX_STRUCTURES and AX_DISTRIB_HEADERS consume the chain during accounting generation. Any change to a chain row should be assessed against all three referencing tables to avoid orphaned or inconsistent configuration.