Oracle HRMS Audit Trail – Audit Changes in HRMS
In this article I will explain step by step example that enables Auditing in Oracle HRMS. This article is a proof of concept for auditing in Oracle HRMS, as it ends with showing the data from HR_AUDITS and HR_AUDIT_COLUMNS tables. In this example we will see how changes to First Name and Last name of a person are being audited.
Also keep in you concederation that AuditTrail only in a limited capacity.On the following tables: PER_ALL_PEOPLE_F PER_ALL_ASSIGNMENTS_F PAY_ELEMENT_ENTRY_VALUES_F PAY_ELEMENT_ENTRIES_F PAY_PERSONAL_PAYMENT_METHODS_F PAY_EXTERNAL_ACCOUNTS However we do not audit all columns. 1- Check if Profile AuditTrail:Activate at site level to be Yes select fnd_profile.value(‘AUDITTRAIL:ACTIVATE’) from dual ; 2- Navigate to system administrator >> Security >> AuditTrail >> Install
after that you have to query on Oracle Username HR and then enable the Audit Enabled check-box as below screen.
3-Defined Audit Group: Navigate to Audit Group screen in System Administrator, and create an “Audit Group” as show below.
4- From System Administrator responsibility, add the desired columns that you wish to audit upon on table PER_ALL_PEOPLE_F
5- From System Administrator, Run Concurrent program “AuditTrail Update Tables” .
8- Run concurrent program “Audit Report” From Oracle HRMS Superuser responsibility and put this parameters:
A- Table Name: Per_All_People_F
B- initialization: Yes
By running the process “Audit Report”, data from shadow Audit tables [ _A ] will be moved into HRMS Audit Tables.
9- query the data from HR_AUDITS and HR_AUDIT_COLUMNS.
Also you can check using this query:
SELECT OLD_VALUE, NEW_VALUE
FROM HR_AUDITS AD , HR_AUDIT_COLUMNS AC
WHERE AD.AUDIT_ID = AC.AUDIT_ID
AND AD.TABLE_NAME = ‘PER_ALL_PEOPLE_F’
AND AC.COLUMN_NAME = ‘FIRST_NAME’ ;
Also keep in you concederation that AuditTrail only in a limited capacity.On the following tables: PER_ALL_PEOPLE_F PER_ALL_ASSIGNMENTS_F PAY_ELEMENT_ENTRY_VALUES_F PAY_ELEMENT_ENTRIES_F PAY_PERSONAL_PAYMENT_METHODS_F PAY_EXTERNAL_ACCOUNTS However we do not audit all columns. 1- Check if Profile AuditTrail:Activate at site level to be Yes select fnd_profile.value(‘AUDITTRAIL:ACTIVATE’) from dual ; 2- Navigate to system administrator >> Security >> AuditTrail >> Install
- This request will do the following:
-
-
- creates database triggers on your audited tables
-
-
-
- creates one shadow table for each audited table, to contain the audit information
-
-
-
- builds special views on non-datetracked tables, which you can use to retrieve your audit data for reporting
-
- This request will do the following:
-
-
- creates an index on the shadow table
-
-
-
- creates the function _TT and the procedure _VP, which extracts information from the shadow table when you run the predefined audit report
-