Search Results statement_description

Overview

MSD_AUDIT_SQL_STATEMENTS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the MSD product family, Demand Planning, and serves as the data source for the MSDAUDIT form, where it supplies the validation rules used to audit and verify demand planning data. The view exposes the configurable definition of each audit SQL statement — its name, description, associated report function, application context, dynamic FROM and WHERE clauses, error messaging, and summary token substitution values — together with the descriptive meaning of the report function and application resolved from Oracle's lookup and application views.

Within EBS reporting and integration, the view functions as a metadata repository rather than a transactional data source. It does not store audit results itself; instead, it presents the catalog of validation statements that the MSDAUDIT form executes and interprets. As of ETRM 12.2.2 the object is documented with a status of VALID, confirming it is a supported, active dictionary object.

Underlying Base Objects

The view is defined over three documented base objects. The driving table is MSD_AUDIT_SQL_STATEMENTS, referenced through a synonym, which holds the audit statement definitions and columns such as FROM_CLAUSE, WHERE_CLAUSE, and the summary token fields. FND_APPLICATION_VL is joined on APPLICATION_CODE = APPLICATION_SHORT_NAME to resolve the human-readable application name. FND_LOOKUP_VALUES_VL is joined on FUNCTION = LOOKUP_CODE, restricted by LOOKUP_TYPE = 'MSD_AUDIT_REPORT', to retrieve the lookup MEANING that appears in the output as REPORT_NAME.

Because two of the three sources are translated views (the _VL suffix denotes language-enabled views) and the join to FND_APPLICATION_VL and FND_LOOKUP_VALUES_VL is made without an explicit language predicate, the view returns rows in every installed language for which lookup values are defined. Queries that must return a single language should therefore restrict results by language where appropriate.

Key Columns

• STATEMENT_ID — Unique identifier of the audit SQL statement definition; the primary key for joining back to the base table.
• STATEMENT_NAME / STATEMENT_DESCRIPTION — Functional name and description of the validation rule as presented in the MSDAUDIT form.
• FUNCTION / REPORT_NAME — The function code and its decoded lookup meaning from lookup type MSD_AUDIT_REPORT, identifying the report or report family the statement supports.
• APPLICATION_CODE / APPLICATION_NAME — The owning application short name and its translated display name.
• TRANSLATE — Flag indicating whether the statement text is subject to translation.
• COLUMN1 … COLUMN10 and DESCRIPTION1 … DESCRIPTION10 — Generic slots that parameterize a statement's select list and provide captions for the corresponding columns.
• FROM_CLAUSE / WHERE_CLAUSE — The dynamically executed SQL fragments that define the validation query itself.
• ERROR_MESSAGE — Text displayed when a validation rule fails.
• SUMMARY_MESSAGE_ONLY / SUMMARY_MESSAGE — Controls whether only a summary message is shown and stores that message text.
• SUMMARY_TOKEN1, SUMMARY_TOKEN2, SUMMARY_TOKEN3 — Named placeholders embedded in the summary message.
• SUMMARY_TOKEN1_VALUE, SUMMARY_TOKEN2_VALUE, SUMMARY_TOKEN3_VALUE — The runtime values substituted for each token; these are the columns most frequently referenced when extracting audit configuration.
• ENABLED — Indicates whether the audit statement is currently active.

Common Use Cases and Queries

Typical uses include reviewing which audit validations are enabled for a given application, documenting the SQL executed by the MSDAUDIT form, and diagnosing failed validations by inspecting the error and summary messages with their token mappings.

To list all enabled statements with their decoded report and application names:

SELECT statement_id, statement_name, report_name, application_name, enabled FROM apps.msd_audit_sql_statements_v WHERE enabled = 'Y' ORDER BY application_name, statement_name;

To retrieve the summary token bindings for a specific statement:

SELECT statement_name, summary_message, summary_token1, summary_token1_value, summary_token2, summary_token2_value, summary_token3, summary_token3_value FROM apps.msd_audit_sql_statements_v WHERE statement_id = :p_statement_id;

To inspect the dynamic SQL associated with a rule:

SELECT statement_name, from_clause, where_clause, error_message FROM apps.msd_audit_sql_statements_v WHERE application_code = 'MSD';