Results for “bne_queries_b”

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

Overview

BNE_QUERIES_B is the base definition table for metadata-driven queries within the BNE (Web Applications Desktop Integrator) product in Oracle E-Business Suite. It stores the header-level definition of queries that are used by the Desktop Integrator and Web ADI framework to retrieve and present data to end users. Each row represents a single query definition, identified by the combination of an application and a query code, and acts as the parent record for more specialized query implementations such as raw SQL queries and simple (wizard-defined) queries.

From a Data Vault modeling perspective, the FK structure indicates this table is hub-leaning: it holds the durable business key (APPLICATION_ID, QUERY_CODE) around which dependent query variants and relationships are organized. It functions as a reference and definition entity rather than a transactional fact table, and its lifecycle is governed by the standard EBS audit columns.

Key Information Stored

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

• APPLICATION_ID — Identifies the owning application; part of the composite primary key and business key.
• QUERY_CODE — The user-meaningful identifier of the query; part of the primary and business key.
• QUERY_CLASS — Classifies the query type or category, driving how the query is processed.
• DIRECTIVE_APP_ID / DIRECTIVE_CODE — Foreign reference linking the query to a directive definition, establishing the parent-child relationship to BNE_QUERIES_B itself.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the framework to detect concurrent updates.
• ZD_EDITION_NAME — Editioning column supporting EBR (Edition-Based Redefinition), part of the unique index.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and last modified the record.

The surrogate primary key is defined by BNE_QUERIES_B_PK over (APPLICATION_ID, QUERY_CODE). A unique index, BNE_QUERIES_B_UK1 over (APPLICATION_ID, QUERY_CODE, ZD_EDITION_NAME), serves as the business-key candidate, extending the PK with the editioning column to support edition-aware uniqueness.

Common Use Cases and Queries

BNE_QUERIES_B is typically queried to enumerate available queries within the Desktop Integrator, to trace which directives a query depends on, and to audit query definitions across applications. A representative pattern joins the table to its dependent query variants:

• Listing queries for an application: SELECT query_code, query_class FROM bne_queries_b WHERE application_id = :app_id ORDER BY query_code;
• Finding directive-linked queries: SELECT query_code, directive_app_id, directive_code FROM bne_queries_b WHERE directive_code IS NOT NULL;
• Resolving the concrete implementation: join to BNE_SIMPLE_QUERY or BNE_RAW_QUERY on (APPLICATION_ID, QUERY_CODE) to determine whether a given query is wizard-defined or raw SQL.
• Auditing recent changes: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY for compliance and troubleshooting.

These queries support migration validation, custom integrator development, and impact analysis before modifying or retiring query definitions.

Related Objects

The table participates in a clear parent-child hierarchy:

• BNE_RAW_QUERY — References BNE_QUERIES_B via (APPLICATION_ID, QUERY_CODE); holds raw SQL-based query implementations.
• BNE_SIMPLE_QUERY — References BNE_QUERIES_B via (APPLICATION_ID, QUERY_CODE); holds simple, wizard-defined queries.
• BNE_QUERIES_B (self-reference) — DIRECTIVE_APP_ID / DIRECTIVE_CODE point back to a directive row in the same table.

Together these objects form the metadata query framework used by Web ADI, with BNE_QUERIES_B acting as the definition hub that downstream query types depend upon.