Improving Master Data Audit Trail Reporting

The standard Oracle Audit report is indeed limited and challenging to use, often failing to report necessary related details like the supplier name, even though that data exists in the system. Blitz Report offers generic, highly usable reports like ‘FND Audit Table Changes by Column’ and ‘FND Audit Table Changes by Record’ that can be cloned and configured for master data auditing. These reports allow you to include related table information, such as querying `AP_SUPPLIERS` to report the vendor name alongside the bank change details, providing a clearly formatted Excel output with filters applied automatically.

You must first ensure that the profile option ‘AuditTrail:Activate’ is set to ‘Yes’ at the Site level under System Administrator. Next, you must enable the Audit Installation for the relevant application (e.g., AP for Payables) under Security > Audit Trail > Install. Then, define the specific tables (like `AP_SUPPLIERS`) and the desired columns to be tracked under Security > Audit Trail > Tables. Finally, define an Audit Group and run the concurrent program ‘AuditTrail Update Tables’ to create the necessary audit database triggers and shadow tables (which end in `_A`).

Performance optimization is critical for large audit tables. It is strongly recommended to create indexes on the primary key columns and the `row_key` column of the shadow tables, such as indexing `(vendor_id, row_key)` on `AP_SUPPLIERS_A`. Additionally, indexing the `audit_timestamp` column is beneficial because most audit reports restrict data selection based on the transaction time. However, always test index creation in a test environment first, as indexes require resources for maintenance and could potentially slow down EBS transaction performance.

Running audit reports on an Active Data Guard standby server is a recommended alternative to minimize the performance impact on the production EBS instance. Since querying large audit tables is resource-intensive, shifting this reporting load to a standby operational data store ensures that the production system’s performance remains acceptable while still allowing real-time audit data access.