Search Results ax_query_chains_u1

Overview

The table AX.AX_QUERY_CHAINS is a repository object within the Oracle E-Business Suite (EBS) database, specifically owned by the AX schema, which is associated with Oracle Application Object Library and the Application eXtension (AX) infrastructure. As documented in the ETRM metadata for EBS 12.1.1 and 12.2.2, this table stores query chains. A query chain represents a defined sequence of database queries and associated logic used to drive data extraction, validation, or transformation processes within the EBS environment. The table contains one row for every query chain defined, capturing the application context, the chain's name, and the starting table from which the chain originates. The object is marked with a status of VALID and is physically stored in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

From a data modeling perspective, the heuristic Data Vault classification for AX_QUERY_CHAINS is hub-leaning. This suggests that the table functions as a central point of reference for query chain definitions, with its primary key serving as a unique business key. It is referenced by several other tables, indicating its role as a parent or master entity in the query chain ecosystem.

Key Information Stored

The table consists of 12 documented columns. The most critical columns include:

• APPLICATION_ID (NUMBER): Application identifier, forming part of the composite primary key.
• QUERY_CHAIN_NAME (VARCHAR2, 30): The name of the query chain, also part of the primary key.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN (DATE/NUMBER): Standard Who columns for auditing.
• ENABLED_FLAG (VARCHAR2): Indicates whether the query chain is active.
• DESCRIPTION (VARCHAR2, 255): A textual description of the query chain.
• TABLE_APPLICATION_ID (NUMBER): The application owning the starting table.
• STARTING_TABLE (VARCHAR2, 30): The name of the table from which the chain begins.
• FREQUENCY (NUMBER): Frequency value associated with the chain.

The primary key is defined by the constraint AX_QUERY_CHAINS_PK on the columns (APPLICATION_ID, QUERY_CHAIN_NAME). A unique index, AX_QUERY_CHAINS_U1, also exists on the same columns, reinforcing the business-key candidate. No surrogate key is present; the composite natural key serves as the unique identifier.

Common Use Cases and Queries

Query chains are typically used by EBS technical consultants to define and manage data extraction logic for interfaces, reports, or custom processes. A common query retrieves all enabled query chains for a given application:

SELECT QUERY_CHAIN_NAME, DESCRIPTION, STARTING_TABLE, FREQUENCY
FROM AX.AX_QUERY_CHAINS
WHERE APPLICATION_ID = :app_id
AND ENABLED_FLAG = 'Y';

Reporting use cases include auditing query chain definitions, identifying chains that start from a specific table, or joining with related tables to understand dependencies. For example, to find all chains starting from a particular table:

SELECT APPLICATION_ID, QUERY_CHAIN_NAME
FROM AX.AX_QUERY_CHAINS
WHERE STARTING_TABLE = 'FND_USER';

Related Objects

The table is referenced by several other objects, establishing foreign key relationships that enforce referential integrity. The documented foreign keys are:

• AX_QUERY_TABLES: References AX_QUERY_CHAINS via APPLICATION_ID and QUERY_CHAIN_NAME. This table likely stores the individual queries that make up a chain.
• AX_DISTRIB_HEADERS: References AX_QUERY_CHAINS via APPLICATION_ID (and presumably QUERY_CHAIN_NAME), linking distribution definitions to query chains.
• AX_STRUCTURES: References AX_QUERY_CHAINS via APPLICATION_ID, indicating a relationship with structural definitions.

These dependencies highlight the role of AX_QUERY_CHAINS as a foundational table for AX-related configuration and processing. No other database objects are referenced by this table, as per the dependency information.