Tracking master data changes via standard EBS Audit functionality

This article explains the Oracle EBS system admin setup required to implement Audit track changes to supplier or bank tables. 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 a Blitz Report.

Audit Setup

We use the standard EBS Audit functionality to achieve this together with an SQL query and a new Blitz Report.

Profile Audit Enable
Start by setting the system profile Audit Trail: Activate to Yes at the site level. Navigation: System Administrator Responsibility–>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:

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 Responsibility>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 have 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 Responsibility>Security>AuditTrail>Tables

Repeat for Bank, and Bank Account Number as required.

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 Concurrent program ‘AuditTrail Report for Audit Group Validation’ with parameter as per your Audit Group.

Check that the log file is free of errors and then define an Industry Template contain Audit Group.

e.g.

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

Next, run the concurrent program “AuditTrail Update Tables” and check the log file to confirm the existence of Audit tables

Navigation: Security > AuditTrail > Audit Trail Reporting > Audit Report

Above request will start a concurrent request which will create the framework for Audit reporting.

The standard Audit report


The standard report is rather limited and challenging to use, and can be improved by using Blitz Report which we will create in the next section.

In the following example using the standard Oracle Audit report process, we need to select the bank table for updates.

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

For example:

The report provides output in a text format that cannot be exported easily to Excel, for example:

We will define a Blitz Report instead!

Creation of the SQL Query for Audit reporting in Blitz Report


Now we need to create the SQL to select the audit changes from either the supplier or the bank.

Sample queries can be found on Google or you can review help pages from the Oracle Support Forums.

Here is an example query that could be used as a starting point, changing the select columns & tables based on your report needs.

SELECT
a.sup_name Supplier_Name,
a.segment1 Supp_num,
Mod_vend,
A.VENDOR_NAME Initial_Value,
NVL ( LAG (A.VENDOR_NAME, 1) OVER
(PARTITION BY A.VENDOR_ID ORDER BY A.AUDIT_TIMESTAMP DESC),A.sup_name) New_value, a.vend_mod_by, a.AUDIT_TIMESTAMP Vend_Mod_Date

FROM (SELECT ‘Vendor Name’ Mod_Vend,
sup_aud.AUDIT_TIMESTAMP,
sup_aud.VENDOR_ID,
sup_aud.VENDOR_NAME,
asa.vendor_name sup_name,
asa.segment1,
NVL (
(SELECT papf.full_name
FROM fnd_user fu, per_all_people_f papf
WHERE user_name = sup_aud.AUDIT_USER_NAME
AND fu.employee_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN papf.
effective_start_date
AND papf.effective_end_date),
(SELECT DESCRIPTION
FROM fnd_user fu1
WHERE fu1.user_name = sup_aud.AUDIT_USER_NAME))
vend_mod_by,
sup_aud.AUDIT_USER_NAME

FROM
ap_suppliers_a sup_aud,
ap_suppliers asa
WHERE sup_aud.VENDOR_NAME IS NOT NULL
AND sup_aud.vendor_id = asa.vendor_id) A

Creation of the Blitz Report


Once you have tested and perfected your SQL query, you simply paste it into the Blitz Report whilst in setup / developer mode. If you need help on how to create a Blitz report follow the video examples on the Enginatics YouTube channel.

The Blitz Report allows you to schedule your new Blitz Report for Audit tracking of supplier and bank via the standard Oracle concurrent request mechanism and email the output, for example at the start / end of each day.

Alternatively, you could add the Blitz Report to the AP Payments form so that prior to payment you can make a sensitive check on the supplier details via your new Audit Blitz Report. See the video channel or resources blogs on how to add the Blitz Report function to an Oracle form.

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 supplier bank in a convenient Excel output.

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 Admin > Security > Audit Trail >Install
Enable Audit Installation for AP

c) Define Audit tables and desired columns.
 Navigation: System Admin > Security > Audit Trail > Tables
Query for 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 Admin > Security > Audit Trail >Groups
Create Audit group for table defined in ‘C’

e) Run Concurrent program ‘AuditTrail Report for Audit Group Validation’ with parameter as your
Audit Group.

f) Define an Industry Template contain Audit Group
Navigation:  System Admin >Security > Audit Trail Reporting >Audit Industry Template

g) Run concurrent program “AuditTrail Update Tables”.

h) Confirm existence of Audit tables (_A).

i) Run Audit report from SysAdmin menus.
Navigation: Security > AuditTrai > Audit Trail Reporting > Audit Report

j) Create SQL and paste into your new Blitz Report and assign to the relevant data management teams.

k) Schedule the Blitz Report via the concurrent request process.