Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Miscellaneous
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

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.
Image



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
Image



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

Comments   

0 #1 kartikey 2006-11-27 00:00
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
Quote
0 #2 Gayathri 2006-12-01 00:00
Hi Anil

This is an excellent article beautifully written and useful too.I can see ur patience in this article.
Thank s !
Quote
0 #3 Vivek 2006-12-13 00:00
Excellent article. Appreciate your efforts.

tha nks!
Quote
0 #4 Vivek 2006-12-13 00:00
Excellent article. Appreciate your efforts.

tha nks!
Quote
0 #5 Bharat Daga 2006-12-16 00:00
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_COLUMN S 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,
Bhar at
Quote
0 #6 Bharat Daga 2006-12-16 00:00
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_COLUMN S 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,
Bhar at
Quote
0 #7 Bharat 2006-12-17 00:00
Hi Anil,

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

Cheer s,
Bharat
Quote
0 #8 Bharat 2006-12-17 00:00
Hi Anil,

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

Cheer s,
Bharat
Quote
0 #9 Milan 2007-01-02 00:00
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/feedbac k would be greatly appreciated.

Thanks again,

Milan
Quote
0 #10 Milan 2007-01-02 00:00
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/feedbac k would be greatly appreciated.

Thanks again,

Milan
Quote
0 #11 Anil Passi 2007-01-03 00:00
.
.
Hi Milan,

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

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

I think Events should be prefered approach.

Th anks
Anil
Quote
0 #12 Anil Passi 2007-01-03 00:00
.
.
Hi Milan,

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

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

I think Events should be prefered approach.

Th anks
Anil
Quote
0 #13 Anil Passi 2007-01-23 00:00
Hi Vinod,

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

Tha nks
Anil
Quote
0 #14 vinod 2007-01-23 00:00
I am very thank full to you for your wounder full articals.
Quote
0 #15 Anil Passi 2007-01-23 00:00
Hi Vinod,

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

Tha nks
Anil
Quote
0 #16 vinod 2007-01-23 00:00
I am very thank full to you for your wounder full articals.
Quote
0 #17 Anil Passi 2007-03-28 00:00
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
Quote
0 #18 rajesh 2007-03-28 00:00
Hi anil

Can you tell me how the Performance hits either in Application or at database level when you enable a Audit trail
Quote
0 #19 rajesh 2007-03-28 00:00
Hi anil

Can you tell me how the Performance hits either in Application or at database level when you enable a Audit trail
Quote
0 #20 shulabh nigam 2007-04-23 00:00
hi,
could any one explain me how to track changes on per_all_assinme nts_f with the help of per_all_assinme nts_f_a.Waht set of conditions should be written to achive this.

Shulab h
Quote
0 #21 SwethaJR 2007-08-14 21:06
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.
Quote
0 #22 Anil Passi 2007-08-19 07:04
Hi Swetha

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

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

T hanks,
Anil Passi
Quote
0 #23 Tushar Joshi 2007-09-20 02:06
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.
Quote
0 #24 Tushar Joshi 2007-09-20 19:22
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.
Altho ugh, 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.
Quote
0 #25 Joe 2007-10-15 14:10
Hi Anil,
i have created an audit trail on OE_ORDER_HEADER S_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
Quote
0 #26 Akash Jain 2008-02-19 12:44
Hi Anil,
i tried creating Audit trail for Fnd_looup_value s 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 .
Quote
0 #27 burak 2009-07-10 05:11
Hi Anil,

Thanks for article.

i fetch this query:
SELECT application_nam e
FROM fnd_application _vl fav, fnd_tables ft
WHERE ft.table_name = 'mtl_system_ite ms' or ft.table_name = 'mtl_system_ite ms_flv'
AND ft.application_ id = fav.application _id

but there is no data. why?

thenks
Quote
0 #28 Anil Passi- 2009-07-10 06:52
Use upper case and use like statement

For example

table_ name like 'MTL PERCENT SYS PERCENT ITEM PERCENT'

Of course Replace PERCENT with the symbol
Quote
0 #29 KRISHNENDU HAZRA 2009-11-18 05:57
HI,

I am trying to enable the Audit Trial feature in the Custom table. I have registered the table in apps as per suggestion from Oracle metalink. When I am submitting the Audittrail Update Tables concurrent program , it is showing some Fatal Error. Please find the attached error file in details.


Please see the summary of the fatal error.

Fatal error in fdasql, quitting...
Fat al error in fdacv, quitting...
com mit

Fatal error in fdaupc, quitting...

I need your help ..... tahnks

Krishne ndu Hazra
Quote
0 #30 pitou 2011-07-25 06:33
Dear all,
me also met the same problem with 'Krishnendu Hazra' the Error message appeared :

Fatal error in fdasql, quitting...
Fat al error in fdacv, quitting...
com mit

Fatal error in fdaupc, quitting...
+-- --------------- --------------- --------------- --------------- -------------+
Start of log messages from FND_FILE
+----- --------------- --------------- --------------- --------------- ----------+
+-- --------------- --------------- --------------- --------------- -------------+
End of log messages from FND_FILE
+----- --------------- --------------- --------------- --------------- ----------+


+ --------------- --------------- --------------- --------------- --------------- +
Executing request completion options...


Ou tput is not being printed because:
The print option has been disabled for this report.


Finis hed executing request completion options.

+---- --------------- --------------- --------------- --------------- -----------+
Co ncurrent request completed


--
Please help,

Thanks with regards,
Pitou
Quote
0 #31 urc 2011-07-29 20:23
HI Anil

You mentioned to use Workflow Bsiness events to track changes for BAnk account details onSupplier form..
Can you please provide some details as to how cani implement that or isthere any audittrail report where i can track this

Please advice
Quote
0 #32 nmy 2012-11-11 23:10
Hi Anil,
This a nice article.

I have requirement that my customer want to enable auditing for the document of records information in HRMS. I know that the attached files using DOR functionality gets stored in fnd_lobs table. would be possible to enable auditing for this? If someone deletes an attachment can i get it back from audit tables?

Please advice..

Regar ds,
Nagaraju
Quote
0 #33 Rajashekar Reddy 2015-11-19 10:11
Hi,

Superb work. Keep it up.

Thanks,

Rajashekar Reddy
Quote
0 #34 Rajashekar Reddy 2015-11-19 10:12
Hi,

Superb work. Keep it up.

Thanks,
Rajashekar Reddy.
http://kommaoracleapplications.blogspot.in/
Quote
0 #35 Tu 2021-06-25 21:48
Hi Anil,

How do I write a query to show Old Value and New Value for a column from the Audit Trail Tables.

Thank You,
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner