Enabling audit trail for Oracle E-Business Suite Application in R12.1.x/R12.2.x

Audit is a very important feature of the oracle apps. We can track the last changes in the Oracle apps records but what about the second last change so well there is no track of this. So to see all the changes that happened for the table we can use the Audit feature of the oracle apps. We can audit some of the sensitive tables in oracle apps R12 using audit trail functionality. We don't need to audit the complete table. We can audit the Complete or we can audit some of the columns in the table and the Audit report will give us all the details regarding any changes in this table for that audit columns.

You can choose to store and retrieve a history of all changes users make on a given table. Auditing is accomplished using audit groups, which functionally registered Oracle IDs or group tables to be audited. For a table to be audited, it must be included in an enabled audit group.

Audit Trail Groups are groups of tables and columns. You do not necessarily need to include all the columns in a given table. You enable auditing for audit groups rather than for individual tables. You would typically group together those tables that belong to the same business process (for example, purchase order tables).

A given table can belong to more than one audit group. If so, the table is audited according to the highest level of enabling for any of its groups, where Enabled is the highest, followed by Disable Dump Data, Disable No Growth, and Disable Purge Table, in that order.

You can enable auditing for a maximum of 240 columns for a given table, and you can enable auditing for all types of table columns except LONG, RAW, or LONG RAW. Your audit group must include all columns that make up the primary key for a table; these columns are added to your audit group automatically. Once you have added a column to an audit group, you cannot remove it.

Turn on Audit Trail.

Turn on Oracle E-Business Suite Applications Audit Trail by setting the system profile Audit Trail: Activate to Yes

System Administrator ==> Profile ==>System

Find the Profile 'AuditTrail:Activate' and set this to Yes at Site Level.

Identify which table we want to audit and which column of this table. Then need to identify the Module of the Audit table to which that Table belongs.

 

Install the Audit Trail

System Administrator ==> Security ==>AuditTrail ==>Install

 

Select the Module to which we want to Install the Audit Trail.

Create the Table Audit Group.

 

System Administrator ==> Security ==>AuditTrail ==>Groups

Create the Audit Group for table 'AP_INVOICES_ALL'

 

Note: When you first create the audit group the group status will be 'Enable Requested' this will automatically be changed to 'Enabled' once the AuditTrail Update Tables concurrent request is run

Enable Audit Columns 

System Administrator ==> Security ==>AuditTrail ==>Tables

Now we need to set the Audit on the Table and the column to which we want to keep track.

Now we will run the concurrent Program in System Administrator responsibility "AuditTrail report for Audit Group Validation".

After the given above concurrent program run successfully then we will run the concurrent Program in System Administrator responsibility "AuditTrail Update Tables'.

Confirm the Audit tables would be created with (_A) name. These are also called shadow Tables

Error:

While running AuditTrail Update Tables concurrent request on e-business suite R12 as part of audittrail enable process the request completed with Fatal error in fdasql, quitting.... Fatal error in fdacv, quitting.... Fatal error in fdaupc, quitting error.

Metalink note

AuditTrail Update Tables fails on View AP_SYSTEM_PARAMETERS_ALL_AC1 (Doc ID 727208.1)

Solution

1. Navigate to the audit group tables, and query back the table as before

2. Set the Group state to "Disable - Purge Table" for AP_SYSTEM_PARAMETERS_ALL. This option

Disable - Purge Table Drops the auditing triggers and views and deletes all data from the shadow

table.

3. Run the "AuditTrail Update Tables" concurrent program