FND Audit Setup – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Application Object Library · all Application Object Library reports
Overview
FND audit setup validation report including audit groups, audit tables and audited columns, and a check if the corresponding audit tables are created in columns ‘Audit Table Name’ and ‘Audit Column Exists’.
Oracle’s standard audit trail works with a concurrent program ‘AuditTrail Update Tables’, which creates a set of database triggers for updates, inserts and deletes. The triggers write table changes to an audit table with the name: audited_table_A.
The whole audit trail setup process is describe in this blog: https://www.enginatics.com/blog/how-to-track-master-data-changes-using-oracle-ebs-audit-function-and-blitz-report/?utm_source=oraclereporting.com&utm_medium=referral&utm_campaign=oracle-ebs-sql&utm_content=fnd-audit-setup
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Audit Table, Audit Group
Oracle EBS Tables Used
fnd_audit_tmplt_dtl, fnd_product_installations, fnd_oracle_userid, dba_tables, fnd_primary_key_columns, dba_tab_columns, fnd_application_vl, fnd_audit_groups, fnd_audit_tables, fnd_tables, fnd_audit_columns, fnd_columns
Report Categories
Related Reports
FND Applications, FND Lookup Search, FND Audit Table Changes by Column, AR Transactions and Lines, Blitz Report VPD Policy Setup, FND Audit Table Changes by Record, DBA Users, AP Expenses
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | FND Audit Setup 16-Aug-2020 161825.xlsx |
| Blitz Report™ XML Import | FND_Audit_Setup.xml |
| SQL Source | FND Audit Setup.sql |
| Full Report on Enginatics | www.enginatics.com/reports/fnd-audit-setup/ |
Executive Summary
The FND Audit Setup report validates the configuration of the Oracle Audit Trail feature. It ensures that the audit groups, tables, and columns defined in the application match the actual database triggers and shadow tables created by the system.
Business Challenge
- Compliance Gaps: Believing that auditing is active when the underlying database objects (shadow tables) haven’t been created.
- Configuration Drift: Identifying discrepancies between the intended audit policy and the technical implementation.
- Troubleshooting: Understanding why changes to a specific table are not being captured.
The Solution
This Blitz Report performs a health check on the audit setup:
- Configuration vs. Reality: Compares the FND setup tables with the DBA dictionary tables (
DBA_TABLES,DBA_TAB_COLUMNS). - Completeness Check: Verifies that every column marked for audit actually exists in the shadow table (suffix
_A). - Group Visibility: Shows which audit groups contain which tables.
Technical Architecture
The report joins FND_AUDIT_GROUPS, FND_AUDIT_TABLES, and FND_AUDIT_COLUMNS to get the definition. It then left joins to DBA_TABLES and DBA_TAB_COLUMNS to verify the existence of the physical audit objects (e.g., _A tables).
Parameters & Filtering
- Audit Table/Group: Filter to check specific objects.
Performance & Optimization
- Dictionary Access: Queries
DBA_views, which can be slow on very large databases, but usually performs well for metadata checks.
FAQ
- Q: What does “Audit Table Exists = No” mean?
- A: It means you have defined the audit policy in the application but haven’t run the “AuditTrail Update Tables” concurrent program to build the database objects.
- Q: Can I audit any table?
- A: Generally yes, but the table must have a primary key defined in EBS.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH