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.
Comments
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
This is an excellent article beautifully written and useful too.I can see ur patience in this article.
Thank s !
tha nks!
tha nks!
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
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
Thanks a ton for useful information. This solves my query.
Cheer s,
Bharat
Thanks a ton for useful information. This solves my query.
Cheer s,
Bharat
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
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
.
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
.
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
Its a pleasure to hear this, to see this effort help someone.
Tha nks
Anil
Its a pleasure to hear this, to see this effort help someone.
Tha nks
Anil
if you enable FND Logging, it will hit both mid tier and DB tier, assuming you are debugging framework
Can you tell me how the Performance hits either in Application or at database level when you enable a Audit trail
Can you tell me how the Performance hits either in Application or at database level when you enable a Audit trail
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
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.
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
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.
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.
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
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 .
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
For example
table_ name like 'MTL PERCENT SYS PERCENT ITEM PERCENT'
Of course Replace PERCENT with the symbol
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
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
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
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
Superb work. Keep it up.
Thanks,
Rajashekar Reddy
Superb work. Keep it up.
Thanks,
Rajashekar Reddy.
http://kommaoracleapplications.blogspot.in/
How do I write a query to show Old Value and New Value for a column from the Audit Trail Tables.
Thank You,
excellent information you have right here on this post.
I am returning to your web site for more soon.
RSS feed for comments to this post