Search Results msd_audit_sql_statements_uk

Overview

MSD_AUDIT_SQL_STATEMENTS is a metadata repository table owned by the MSD schema within Oracle E-Business Suite, associated with the MSD – Demand Planning product family. It forms the foundation of the Audit Statements architecture, in which each row represents a discrete, valid SQL query. A concurrent program reads selected rows from this table, filtered by the FUNCTION and APPLICATION_CODE columns, executes the associated query definition, and logs the results. In practice, the table functions as a configurable rule engine: rather than hard-coding validation or diagnostic logic, the application stores query templates centrally and drives them dynamically at runtime.

From a modeling perspective, the ETRM metadata classifies this object heuristically as standalone. It does not behave as a conventional hub, link, or satellite within a Data Vault model, since it is a self-contained definition table rather than a transactional or relationship entity. Downstream tables reference it by primary key, so in a Data Vault interpretation it would more closely resemble a reference or definition hub, but the documented classification remains standalone.

Key Information Stored

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

• STATEMENT_ID — the surrogate primary key, enforced by the unique index MSD_AUDIT_SQL_STATEMENTS_UK. It uniquely identifies each audit statement definition.
• STATEMENT_NAME / STATEMENT_DESCRIPTION — the human-readable identifier and narrative describing the statement's purpose.
• APPLICATION_CODE — the key selection filter. It identifies which Oracle EBS application owns or qualifies the statement, allowing the concurrent program to pick only statements relevant to a given application context. This is the column most frequently searched by implementers.
• FUNCTION — the second selection filter, used in combination with APPLICATION_CODE to scope which statements execute for a given functional flow.
• FROM_CLAUSE / WHERE_CLAUSE — the query body fragments that, combined with the column definitions, form the executable SQL. Storing these separately enables templating and reuse.
• TRANSLATE — governs translation handling for the statement text.
• COLUMN1 through COLUMN11 and DESCRIPTION1 through DESCRIPTION11 — paired slots defining the selected columns and their display labels.
• ERROR_MESSAGE — the message surfaced when the executed statement returns a violation result.
• SUMMARY_MESSAGE / SUMMARY_MESSAGE_ONLY — control output summarization behavior.
• SUMMARY_TOKEN1 through SUMMARY_TOKEN3 and SUMMARY_TOKEN1_VALUE through SUMMARY_TOKEN3_VALUE — token substitution pairs used to parameterize summary output.
• ENABLED — the activation flag determining whether the concurrent program considers the statement during selection.

Common Use Cases and Queries

The primary operational scenario is the Audit Statements concurrent program, which joins this table against its filters to build and execute a working set of queries. Administrators typically query by APPLICATION_CODE and FUNCTION to audit which statements are active for a module, or by ENABLED to review disabled diagnostics.

A representative query to list active statements for an application:

• SELECT STATEMENT_ID, STATEMENT_NAME, FUNCTION, ENABLED FROM MSD_AUDIT_SQL_STATEMENTS WHERE APPLICATION_CODE = :code AND ENABLED = 'Y';

To inspect the SQL body for troubleshooting:

• SELECT STATEMENT_NAME, FROM_CLAUSE, WHERE_CLAUSE FROM MSD_AUDIT_SQL_STATEMENTS WHERE STATEMENT_ID = :id;

Reporting use cases include inventorying statements per application, identifying statements that reference particular tables in their FROM_CLAUSE, auditing token configuration, and reviewing error message coverage.

Related Objects

Two documented dependents reference this table by primary key:

• FND_PLAN_TABLE — joins via STATEMENT_ID; links planning data to the originating audit statement definition.
• AMW_ACCT_ASSOCIATIONS — joins via STATEMENT_ID; associates accounting mappings with a specific statement.

The concurrent program driving execution, along with any reporting views surfaced through the MSD schema, also depends on this table. Implementers should treat MSD_AUDIT_SQL_STATEMENTS as a shared definition source, since changes to FROM_CLAUSE or WHERE_CLAUSE propagate immediately to every dependent execution path.