Tracking master data changes via standard EBS Audit functionality

Audience: Master data, Support Consultants, and Operational teams.

Related Blogs:

  1. How to use audit functionality to speed up operations for error-free transactions
  2. Can data governance be free?

This article explains the Oracle EBS system admin setup required to implement Audit track changes to supplier or bank tables by example. There is also a generic version of the audit report in our library that allows tracking of any table or column based on whether auditing has been enabled.

Additionally, we cover the limitations of the standard Audit reporting, e.g. how to readily evaluate the master data changes and or automatically alert master data audit (MDM) teams on changes. More importantly, we cover an enhanced audit procedure by use of our Blitz Report.

Audit Setup

We use the standard EBS Audit functionality to achieve this together with our Blitz Report.

Profile Audit Enable
Start by setting the system profile AuditTrail:Activate to Yes at the site level. Navigation: System Administrator > System > Profile

 

Enable Audit for the Oracle user

We need to enable the audit for the schema’s which are the owners of the supplier and bank tables, on which we are reporting the audit, this would include the following Oracle user names: APPS (mandatory), AP (Payables), and CE (Cash Management).

Navigation: System Administrator > Security > AuditTrail > Install

Here’s an example for Cash Management Oracle Username (schema):

 

Audit Group Setup

Next, we need to create an Audit Group, use a sensible naming convention for the Audit Group, and set the State to Enabled.

Navigation: System Administrator > Security > AuditTrail > Groups.

Include the table names AP_SUPPLIERS AP_SUPPLIER_SITES_ALL and IBY_EXT_BANK_ACCOUNTS

 

Audit specific columns

As per standard implementation, Oracle has already seeded a number of columns to be Audit enabled, but typically the required column may need to be added. Hence, if you need to add a missing tracking column then proceed as follows:

Navigation: System Administrator > Security > AuditTrail > Tables

 

Repeat for all table columns as required and re-run the table update program.

Note: Adding audit on low volume master data has negligible implications, we always recommend that you run our AWR performance report as a benchmark for any data that you wish to audit. For example, if adding audit to high volume data such as invoices or sales orders then you should test with volume transactions, recording before and after timings.

Audit Concurrent Requests and Industry Template

After you have created the Audit Group and assigned the tables and columns you are ready to activate the setup.

To do this, run the concurrent program AuditTrail Update Tables and check that the log file is free of errors. To validate the audit setup, you can use Blitz Report FND Audit Setup or run the Oracle standard report AuditTrail Report for Audit Group Validation.

In case you want to use Oracle standard audit reporting, define an Industry Template containing the Audit Group.

Navigation:  System Administrator > Security > Audit Trail > Audit Trail Reporting > Audit Industry Template

The standard audit report

The standard report is rather limited and challenging to use and can be significantly improved by using Blitz Report. In the following example, we use the Oracle Audit report process, Here we need to select the bank table for updates or All transaction types per the example below.

Parameters are provided to select the bank account and number from the column selector.

 

For example, the standard report is not usable as it doesn’t report related details such as supplier name. The data exists, but the standard report hasn’t been developed with usability in mind.

 

 

It is far easier to use our generic Blitz Reports FND Audit Table Changes by Column and FND Audit Table Changes by Record and clone them to suit your needs.

For example, the run-time parameters for AP suppliers could be as follows:

 

Note how we have the ability to select multiple columns, and informational or none audited columns. We also have the capability to include related tables, in this case we have selected AP_SUPPLIERS so that we can report the vendor name.

And here is another example, this time for auditing items master data:

 

Here is the Excel output, note the formatting and filters are applied automatically, thereby saving time. All of the Blitz Reports are available as output files that can be viewed through the standard EBS form View Requests.

 

You can add our Blitz Report to any EBS form, for example, it would be sensible to add check the audit trail on banks prior to running payments to suppliers.

Blog: Adding Blitz Report to a form example guide

Video: Adding Blitz Report to the order organizer tutorial

For example, the screen image below is an example Blitz Report assigned to the System Administrator menu. The report output records the changed master data elements around the supplier bank in a convenient Excel output.

Reporting performance improvement

As per recommendations from Oracle documentation it may be required to create indexes on audit tables to improve performance. Please review audit views referenced in the audit report. Note candidate columns used for joins and filtering and create indexes on them. For example FND Audit Table Changes by Column report with parameter “Audit Table” set to AP_SUPPLIERS references AP_SUPPLIERS_A audit table and AP_SUPPLIERS_AC1 view. Upon analyzing the view SQL text the following condition was spotted:

(e.VENDOR_ID=o.VENDOR_ID) AND e.ROW_KEY > o.ROW_KEY)

Hence the following index was created:

create index ap.xxen_ap_suppliers_a_1 on ap.ap_suppliers_a (vendor_id,row_key) tablespace apps_ts_tx_idx;

Usually primary key columns and row_key column are good candidates for indexing. Another column that will probably benefit from indexing is audit_timestamp as most probably only a subset of data will be selected from the audit table basing on the transaction time.

create index ap.xxen_ap_suppliers_a_2 on ap.ap_suppliers_a (audit_timestamp) tablespace apps_ts_tx_idx;

Test indexes creation in a test instance first before implementing in the production system. Note the performance and the execution plan of an audit report before and after the indexes creation. Also test if EBS transaction performance did not decrease significantly and is still acceptable as the indexes on the audit tables require resources for maintenance and may slow things down. Oracle documentation recommends dropping your indexes from the shadow table when you have finished reporting. Another alternative is to move audit data to an operational data store for reporting purposes, or to run Blitz Report on an Active Data Guard standby server.

Summary

Exporting or sifting exceptional master data changes with large volumes of master data can be time-consuming, but with Blitz Report it is straight-forward to achieve.

The summarized steps are as follows:

a) Responsibility: System Administrator
Navigation:   Profile > System
Query Profile: ‘AuditTrail:Activate’. Click FIND
Set it to ‘Yes’ at Site level.

b) Enable Audit Installations (AP)
 Navigation: System Administrator > Security > Audit Trail > Install
Enable Audit Installation for AP

c) Define Audit tables and desired columns
 Navigation: System Administrator > Security > Audit Trail > Tables
Start a new query for the user table name ‘AP_SUPPLIERS’ and add columns on which you want to enable trail and repeat for the Site and Bank tables.

d) Define an Audit Group and associated tables
Navigation:  System Administrator > Security > Audit Trail > Groups
Create an Audit group for a table defined in the previous step.

e) Run concurrent program AuditTrail Update Tables to create audit database triggers and tables (_A)

f) Run validation Blitz Report FND Audit Setup and verify that column ‘Audit Table Name’  is not null and ‘Audit Column Exists’ shows ‘Yes’, or run Oracle standard report AuditTrail Report for Audit Group Validation

g) If you want to use Oracle standard audit reporting, define an Industry Template containing your created Audit Group
Navigation:  System Administrator > Security > Audit Trail > Audit Trail Reporting > Audit Industry Template

h) Run Audit Blitz Reports FND Audit Table Changes by Column and FND Audit Table Changes by Record or Oracle’s standard report from the sysadmin menu
Navigation: System Administrator > Security > AuditTrail > Audit Trail Reporting > Audit Report

Video overview for the setup:

Author: