Results for “bne_stored_sql”

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

Overview

BNE_STORED_SQL is a database table owned by the BNE schema within Oracle E-Business Suite. BNE is the schema associated with Web Applications Desktop Integrator (Web ADI), the framework that generates desktop-integrated spreadsheets, documents, and integrations for EBS forms and concurrent programs. The table's documented description identifies it as the "Definition of SQL statements for the Stored SQL Content," meaning it serves as a central repository for named SQL statements used by Web ADI content definitions and integrator layouts.

From a Data Vault modeling perspective, the metadata's heuristic classification places this object as a link. This is consistent with the observed foreign key structure: the table resolves relationships between an application and a content code on one side, and between a query application and a query code on another side. Rather than storing descriptive attributes tied to a single business key, BNE_STORED_SQL stitches together references, which is the characteristic role of a link table in a dimensional or Data Vault-style model.

Key Information Stored

The table contains twelve documented columns. The most significant are the identifier, relationship, and audit columns:

• CONTENT_CODE – Part of the composite primary key; identifies the stored SQL content definition.
• APPLICATION_ID – Part of the composite primary key; the application that owns the content code.
• QUERY_CODE – Identifies the SQL query being stored or referenced.
• QUERY_APP_ID – The application owning the query code, forming the second foreign key relationship.
• ZD_EDITION_NAME – Editioning column used by the EBS edition-based redefinition strategy; part of the unique index BNE_STORED_SQL_UK1.
• OBJECT_VERSION_NUMBER – Optimistic locking column used by the Web ADI framework to detect concurrent updates.
• QUERY – The actual SQL statement text associated with the stored content.
• CREATED_BY / CREATION_DATE – Standard audit columns recording who created the row and when.
• LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN – Audit columns capturing the most recent modification and the login session that performed it.

The surrogate primary key is BNE_STORED_SQL_PK over (CONTENT_CODE, APPLICATION_ID). A separate unique index, BNE_STORED_SQL_UK1 over (APPLICATION_ID, CONTENT_CODE, ZD_EDITION_NAME), acts as the business-key candidate and enforces uniqueness across edition names.

Common Use Cases and Queries

BNE_STORED_SQL is queried primarily for diagnostics and Web ADI configuration auditing. Administrators investigating a failing integrator or a spreadsheet layout generated through Web ADI will often inspect the stored SQL to confirm which statement is attached to a given content code. A typical lookup pattern is:

• Retrieve all SQL for a given content definition: SELECT query FROM bne.bne_stored_sql WHERE content_code = :code AND application_id = :app_id;
• Locate where a specific query code is reused: SELECT content_code, application_id FROM bne.bne_stored_sql WHERE query_code = :query AND query_app_id = :qapp;
• Audit recent changes: SELECT content_code, last_updated_by, last_update_date FROM bne.bne_stored_sql ORDER BY last_update_date DESC;

These queries support troubleshooting of Web ADI integrators, reviewing customizations after patching, and building configuration inventory reports.

Related Objects

The documented foreign key relationships identify the objects most closely tied to BNE_STORED_SQL. The composite foreign key (APPLICATION_ID, CONTENT_CODE) references BNE_STORED_SQL itself in the ETRM structure, pointing to the parent content definition. The composite foreign key (QUERY_APP_ID, QUERY_CODE) likewise references BNE_STORED_SQL, establishing the query-to-content linkage. In practice, these relationships connect to the broader BNE content and integrator definition tables, including the Web ADI content repository and application reference tables such as FND_APPLICATION, which supplies the APPLICATION_ID and QUERY_APP_ID values. Audit and concurrency behavior relies on standard EBS framework columns (CREATED_BY, LAST_UPDATED_BY, OBJECT_VERSION_NUMBER) shared with the FND audit model. Because the ETRM metadata references the table to itself, primary dependencies on BNE_STORED_SQL are primarily internal to the BNE content-definition subsystem rather than distributed across unrelated product schemas.