How to Enable Oracle E-Business Suite Audit Trail on Tables



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.


Steps:

1) Grant required privileges.

Grant SELECT privileges on SYS.DBA_TABLES to the APPLSYS account. Normally, this would already be there

2) Register custom tables and primary keys.

Your tables and their primary key information must already be registered and defined for successful auditing. If the table you want to audit is a custom table (not shipped as part of Oracle E-Business Suite), you should also perform the following two steps:

Register your table and its primary key columns using Oracle Application Object Library's Tables window (Application Developer Responsibility).

Run the Register Tables concurrent program from the Submit Requests window.

3) Turn on Audit Trail.

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

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


4) 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.


5) Now we need to set the Audit on the Table and the column to which we want to keep track.
 
System Administrator ==> Security ==>AuditTrail ==>Install

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



 
6) Enable Audit Columns 
System Administrator ==> Security ==>AuditTrail ==>Tables



 
Select the Table 'HZ_PARTIES' and the columns as required
 
7) Create the Table Audit Group.
 
System Administrator ==> Security ==>AuditTrail ==>Groups
 
Create the Audit Group for table 'HZ_PARTIES'



 
8) Now we will run the concurrent Program in System Administrator responsibility "AuditTrail report for Audit Group Validation"
 
 
9) Create the Audit Trail Templates
 
 
System Administrator ==> Security ==>AuditTrail Reporting ==> Audit Industry Template
 
10) Run the concurrent Program in System Administrator responsibility "AuditTrail Update Tables'
  If you change any of your definitions later, you must rerun this program. Submit the Audit Trail Update Tables concurrent request from the standard submission (Submit Reports) form.
 
11) We need to run the Audit report  as below
 
System Administrator ==> Security ==>AuditTrail Reporting ==> Audit Report


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


Refer More on 








If you like please follow and comment