Step by Step : ‘AuditTrail’

Posted on May 16th, 2008 by Sanjit Anand | Print This Post | Email This Post

Previous Post:

• Are seeking auditing ability in EBS…use ‘AuditTrail’

Steps by step: Enabling audit trial

As per the below example the 'Define An application user' is a user table name for FND_USER,the same steps you can follow for your own tables.

1. Go to Audit Trail -> Tables -> Query for Define an Application User.let say you are suppose to do the audit on these tables.
   • PO_DISTRIBUTIONS_ALL
   • PO_LINES_ALL
   • PO_LINE_LOCATIONS_ALL
   • PO_HEADERS_ALL
2. Add the columns whatever you want to audit.
3. Go to Audit Trail -> Groups -> Query for Audit Setup Group
4. Enable the 'Audit Setup Group'.
5. Run audittrail update table.
6. add the Define an application user table under Audit Setup Group.
7. Run the audit train update table again.
8. Go to Audit Query Navigator ->Functional Groups-> Now you can see the Define an Application user table added under the audit setup
   group.
9. If the table is available as per the step 8,Now run the Audit trail report,you will be able to get the audit information.

Steps by step: How to Disable AuditTrails

There are three ways to disable auditing feature:

1. Disable Prepare for Archive
2. Disable Interrupt Audit
3. Disable Purge Table

Steps by step: How To Remove a Column From Audit Trial Tables?

1. You need to navigate with System Administrator responsiblity Security -> AuditTrail ->Groups
2. Query the group that contains the table that you want to modify columns assignment
3. Disable this group using the option : Disable - Purge Table
4. Run the concurrent request "AuditTrail Update Tables"
5. Truncate fnd_audit_columns drop all triggers,procedure, view , synoyms
6. Make a copy of table "FND_AUDIT_COLUMNS"
7. Go to the following navigation path : System Administrator -> Security -> AuditTrail -> Tables
8. Query your table that you need to remove a column from it.
9. Go to Help -> Diagnostics -> Examine <Enter apps password>
10. Choose the field = COLUMN_ID and take remark of that value
11. Choose the field = TABLE_ID and take remark of that value too.
12. Login to sqlplus using apps/<apps password>
13. Execute the following:
    delete from FND_AUDIT_COLUMNS where TABLE_ID = <value in step 10> and column_id = <value in
    step 9>;
14. Save you work
15. Go back to step 1 and query the group that contains the table that you made the columns change and Enable this group using the option : Enabled
16. Finally complete this by executing the concurrent request "AuditTrail Update Tables"

Steps by step: Enabling Audit Trail for Custom Objects

The process remain same, as mention above. You should make sure that your have used ad_dd package to create custom objects in Applications. and you have used ad_dd.register_table to register table and ad_dd.register_column to register columns

Your step for enabling custom Object should be as

• Grant select on table to APPS
• Create a synonym as APPS to the table
• Run the AD_DD package to register the table
• Run the AD_DD package to register the columns
• Run the AD_DD package to register the primary keys
  • execute ad_dd.register_primary_key('application short name', 'primary key column name', 'table name','description of key column', 'S', 'Y','Y');
  • execute ad_dd.register_primary_key_column('application short name', 'primary key column name', 'table name', 'column name', 1);

or you can also do it In the screen:

-> Security > Oracle > Register

Report helpful while enabling this feature

As discussed in last post these two report is important.

• AuditTrail Report for Audit Group Validation
• AuditTrail Update Tables

1. Are you seeking auditing ability in EBS…use ‘AuditTrail’
2. R12 : Uploading a Journal using Web ADI : Step by Step
3. How to use Descriptive Flex Fields (DFF) in forms
4. 11i Uploading a Journal using Web ADI : Step by Step
5. _ALL, _TL, _VL, _V,_F,_VL,_A,_AVN and what else..