Search Results audit_timestamp

Overview

FND_AUDIT_TABLES_V is a lightweight dictionary view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is part of the FND (Application Object Library) product and is delivered with a VALID status. Rather than storing application data, the view returns a filtered list of table names drawn from the data dictionary that participate in EBS auditing. It identifies every table that carries an AUDIT_TIMESTAMP column, which is the audit framework's mandatory marker column indicating that the table is audit-enabled.

The view is not a transactional or reporting object. Its role is diagnostic and introspective: it exposes the set of audit-tracked tables at runtime so that developers, DBAs, and integrators can enumerate the audit footprint of an EBS instance without hard-coding table lists. Because it resolves against the data dictionary rather than a fixed registry, the result set automatically reflects the current state of the database, including custom tables that follow EBS audit naming conventions. This makes it useful in the context of auditing, security reviews, and impact analysis for the AuditTrail feature of Application Object Library.

Underlying Base Objects

The only documented referenced base object is ALL_TAB_COLUMNS, accessed through a SYNONYM. ALL_TAB_COLUMNS is the data dictionary view that describes every column of every table and view visible to the current user, including TABLE_NAME, COLUMN_NAME, and datatype attributes.

The view text is a single SELECT with a compound WHERE clause:

• TABLE_NAME LIKE '%@_A' ESCAPE '@' — matches the EBS audit-table naming convention, where an audited base table (for example, a table named PER_ALL_PEOPLE_F) has a shadow audit table ending in an underscore followed by the letter A. The '@' character is designated as the escape character so the underscore is treated literally rather than as a single-character wildcard.
• TABLE_NAME = 'FND_SELAUDIT_LOG' — explicitly includes the FND self-audit log table, which does not follow the standard _A suffix convention.
• Both branches are additionally constrained by COLUMN_NAME = 'AUDIT_TIMESTAMP', ensuring only genuine audit tables that expose the audit timestamp column are returned.

Because it queries ALL_TAB_COLUMNS, the view does not reference APPS synonyms or application tables directly, and it inherits the caller's privileges within the APPS schema context.

Key Columns

The view exposes a single documented column:

• TABLE_NAME (VARCHAR2, inherited from ALL_TAB_COLUMNS) — the name of an audit-enabled table. Values are either shadow audit tables conforming to the %_A pattern or the FND_SELAUDIT_LOG table. Each returned name is guaranteed to contain an AUDIT_TIMESTAMP column.

No audit-trail history, timestamp values, or user identifiers are surfaced. The view reports structural metadata only; the AUDIT_TIMESTAMP values themselves reside in the referenced audit tables, not in this view.

Common Use Cases and Queries

Typical scenarios include inventorying audit-enabled tables before a patch or upgrade, verifying that customizations created shadow audit tables correctly, and driving dynamic SQL that reconciles or purges audit data. A basic listing is:

• SELECT table_name FROM apps.fnd_audit_tables_v ORDER BY table_name; — enumerates all audit tables in the instance.
• SELECT table_name FROM apps.fnd_audit_tables_v WHERE table_name LIKE 'PER%'; — restricts the output to a single product's audit tables.
• SELECT COUNT(*) FROM apps.fnd_audit_tables_v; — returns the total number of audit-enabled tables for capacity or compliance reporting.
• SELECT table_name FROM apps.fnd_audit_tables_v WHERE table_name = 'FND_SELAUDIT_LOG'; — confirms the FND self-audit log is present, since it is included by explicit name rather than by pattern.

Because the view reads live dictionary data, results are always current and require no maintenance. It should not be used for runtime performance-sensitive logic on very large dictionaries without appropriate filtering by table name prefix.