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 as below.




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.

Comments (26)

Subscribe to this comment's feed

...
written by kartikey , November 27, 2006

Hi Anil,
Thanks alot.Really I hv to appreciate u r dedication.I believe this is a best site for oracle apps beginner.
once again thanks alot.
bye

...
written by Gayathri , December 01, 2006

Hi Anil

This is an excellent article beautifully written and useful too.I can see ur patience in this article.
Thanks !

...
written by Vivek , December 13, 2006

Excellent article. Appreciate your efforts.

thanks!

...
written by Vivek , December 13, 2006

Excellent article. Appreciate your efforts.

thanks!

...
written by Bharat Daga , December 16, 2006

Hi Anil,

I have a user requirement to create audit reports on few of HRMS areas like Person, assignment, salary details. I did enabled audit trail on required HRMS tables which included Date tracked and Non Date tracked tables. I am able to create audit reports on non date tracked tables from shadow audit tables.
I need some information on how to extract data from shadow audit tables to HR_AUDIT, HR_AUDIT_COLUMNS table to create audit reports on date tracked tables. Is it possible to create audit reports on date tracked tables without extracting data to HR_AUDIT and HR_AUIT_COLUMNS?

Need some assistance!!!

Thanks,
Bharat

...
written by Bharat Daga , December 16, 2006

Hi Anil,

I have a user requirement to create audit reports on few of HRMS areas like Person, assignment, salary details. I did enabled audit trail on required HRMS tables which included Date tracked and Non Date tracked tables. I am able to create audit reports on non date tracked tables from shadow audit tables.
I need some information on how to extract data from shadow audit tables to HR_AUDIT, HR_AUDIT_COLUMNS table to create audit reports on date tracked tables. Is it possible to create audit reports on date tracked tables without extracting data to HR_AUDIT and HR_AUIT_COLUMNS?

Need some assistance!!!

Thanks,
Bharat

...
written by Bharat , December 17, 2006

Hi Anil,

Thanks a ton for useful information. This solves my query.

Cheers,
Bharat

...
written by Bharat , December 17, 2006

Hi Anil,

Thanks a ton for useful information. This solves my query.

Cheers,
Bharat

...
written by Milan , January 02, 2007

Hi Anil,

I would like to implement the excellent strategy that you provided to develop a best practice that identifies when changes are made to bank information in Oracle Accounts Payable.

Is this a good approach, or do you suggest using Oracle Alert? Any other insight/feedback would be greatly appreciated.

Thanks again,

Milan

...
written by Milan , January 02, 2007

Hi Anil,

I would like to implement the excellent strategy that you provided to develop a best practice that identifies when changes are made to bank information in Oracle Accounts Payable.

Is this a good approach, or do you suggest using Oracle Alert? Any other insight/feedback would be greatly appreciated.

Thanks again,

Milan

...
written by Anil Passi , January 03, 2007

.
.
Hi Milan,

Indeed you can use Alerts to notify people/group of people when Bank Account record gets changed.

However, Alerts are being phased out and being replaced by Workflow Business Events.

I think Events should be prefered approach.

Thanks
Anil

...
written by Anil Passi , January 03, 2007

.
.
Hi Milan,

Indeed you can use Alerts to notify people/group of people when Bank Account record gets changed.

However, Alerts are being phased out and being replaced by Workflow Business Events.

I think Events should be prefered approach.

Thanks
Anil

...
written by Anil Passi , January 23, 2007

Hi Vinod,

Its a pleasure to hear this, to see this effort help someone.

Thanks
Anil

...
written by vinod , January 23, 2007

I am very thank full to you for your wounder full articals.

...
written by Anil Passi , January 23, 2007

Hi Vinod,

Its a pleasure to hear this, to see this effort help someone.

Thanks
Anil

...
written by vinod , January 23, 2007

I am very thank full to you for your wounder full articals.

...
written by Anil Passi , March 28, 2007

for audit trail, it hits DB tier.

if you enable FND Logging, it will hit both mid tier and DB tier, assuming you are debugging framework

...
written by rajesh , March 28, 2007

Hi anil

Can you tell me how the Performance hits either in Application or at database level when you enable a Audit trail

...
written by rajesh , March 28, 2007

Hi anil

Can you tell me how the Performance hits either in Application or at database level when you enable a Audit trail

...
written by shulabh nigam , April 23, 2007

hi,
could any one explain me how to track changes on per_all_assinments_f with the help of per_all_assinments_f_a.Waht set of conditions should be written to achive this.

Shulabh

...
written by SwethaJR , August 14, 2007

Hi Anil,

I came across your website when searching for alerts. I have a requirement to send a notification to the requisition requester when the PO is rejected. Can this task be done with Oracle alerts? Please let me know how this can be accomplished. I am new to Oracle alerts and haven't configured any till now. Appreciate your help on this issue.

Thanks!
Swetha.

...
written by Anil Passi , August 19, 2007

Hi Swetha

I think Oracle PO Approval workflow already sends a notification when a Purchase Order gets rejected.

Please refer to PO Approval Workflow, you do not need to write a alert for this business requirement.

Thanks,
Anil Passi

...
written by Tushar Joshi , September 20, 2007

Hi
This is very nice informative data,Just want to know if it will help me to capture the data whenever there will be action like Insert / update / delete / correction on base tables.

...
written by Tushar Joshi , September 20, 2007

Hi Anil

I have created the audit trail for the date tracked table PER_ALL_PEOPLE_F table.
And updated columns last_name and bith date which I added manually.
Although, I am not able to see the changes in the audit table PER_ALL_PEOPLE_F_A.
Can you tell me what could be the reason.

Manually added columns are not populating data
written by Joe , October 15, 2007

Hi Anil,
i have created an audit trail on OE_ORDER_HEADERS_ALL table.
I have manually added columns like order_number, booked_date and etc.
Run the AuditTrail Update tables request!
Thouch i can the empty columns, when queried from the back end...
no data is poplulated into these four columns, even when im creating and updating records.
am i missing something here?

Thans a ton in advance Anil,
Joe

...
written by Akash Jain , February 19, 2008

Hi Anil,
i tried creating Audit trail for Fnd_looup_values table.

i was successful till step 6.

but i am not getting any data by the query mentioned in step 7.

Also i am not getting my Audit group name in the value set attached to the field "Functional Group" in the Audit report menu.

Can you please help .