Miscellaneous Technology

Oracle Apps provides an Out of the Box Audit Trail Functionality that works like a charm.

I never had thought about writing an Article on Audit Trail feature, until Kartikey pinged me to write an article on this subject. You can achieve Auditing[as explained in Step by Step example below] without writing a single piece of Code in Oracle Apps. It can be argued that CDC [change data capture] might be used, but I think the design of CDC is more friendly for Warehousing purposes, rather than Audit Reporting.


Lets say that we wish to create a audit trail on a table named FND_LOOKUP_VALUES.

Step 1: Find the name of Application for table to be Audited
Lets first find the application name of this Oracle Apps table.
SELECT application_name
FROM fnd_application_vl fav, fnd_tables ft
WHERE ft.table_name = 'FND_LOOKUP_VALUES'
AND ft.application_id = fav.application_id

The above SQL returns Application Object Library.


Step 2: Ensure that Audit on the Application is Enabled
Navigate to System Administrator Menu Security/AuditTrail/Install

The owner of table FND_LOOKUP_VALUES is APPLSYS. Hence query on APPLSYS to ensure that Audit is enabled for this Application.




Step 3: Create Audit Group
Navigate to System Administrator Menu Security/AuditTrail/Groups

Application Name: Application Object Library
Audit Group: XX Audit Lookup Values
Group State: Enabled

Now, add audit tables to this group[you can add as many tables]
User Table Name: FND_LOOKUP_VALUES


Step 4: Run Concurrent program “AuditTrail Update Tables”
This process can be run from System Administrator responsibility. It has no parameter. Running this process will create the Audit tables and the triggers that manage Audit data.

Step 5: Ensure that Audit Tables have been created as expected
SELECT object_name, object_type
FROM all_objects
WHERE object_name LIKE 'FND_LOOKUP_VALUES_A%'

OBJECT_NAME                      OBJECT_TYPE
--------------------------                      --------------------------
FND_LOOKUP_VALUES_A       TABLE
FND_LOOKUP_VALUES_A       SYNONYM
FND_LOOKUP_VALUES_AC    TRIGGER
FND_LOOKUP_VALUES_AC1   VIEW
FND_LOOKUP_VALUES_AD    TRIGGER
FND_LOOKUP_VALUES_ADP  PROCEDURE
FND_LOOKUP_VALUES_AH    TRIGGER
FND_LOOKUP_VALUES_AI      TRIGGER
FND_LOOKUP_VALUES_AIP    PROCEDURE
FND_LOOKUP_VALUES_AT    TRIGGER
FND_LOOKUP_VALUES_AU    TRIGGER
FND_LOOKUP_VALUES_AUP  PROCEDURE
FND_LOOKUP_VALUES_AV1   VIEW

Fine, this proves that the concurrent program in Step 4 did its job.
Optionally, you may run concurrent process “AuditTrail Report for Audit Group Validation” to validate the success of Audit Table/Trigger creation.


Step 6: Now create a new lookup type to test Audit Trail
Navigate to “Application Developer” responsibility and create a lookup type and Lookup codes.


Step 7: Now see the results of the Audit Trail
Now run the query below, you can see the Audit information has been generated.
SELECT audit_transaction_type
      ,audit_user_name
      ,audit_sequence_id
      ,lookup_code
      ,lookup_type
FROM fnd_lookup_values_a




Step 8: Add further columns for Audit Trail
By default Oracle will Audit Trail on all columns that are a part of first available Unique Index on FND_LOOKUP_VALUES.
However further columns can be added to the Audit Trail. Lets say you wish to Audit Trail on Column Meaning too.
Navigate to System Administrator Menu Security/AuditTrail/Tables

You can add additional columns to audit trail and re-execute Step 4.
Please note that adding columns for Audit could have been done immediately after Step 3.

This explains how you can Audit trail changes to Data in Oracle Applications, without writing a single line of code.

Anil Passi

Add comment