Audience: Master data maintenance, support consultants and operational teams.

Related blogs:

1. Can data governance be free?
2. How to track changes using standard audit setup

Auditing data in Oracle EBS

Auditors are increasingly recommending that high-risk activities be tracked and reported. While Oracle provides functionality to address this, some EBS customers are concerned that enabling audit tracking will create system performance issues, so they do not enable the functionality. Too often, audit tracking in EBS has been an afterthought to the overall implementation, when in fact it should be a core component.

Did you know that Oracle provides standard audit functionality using the system administrator responsibility included in your license fees? This audit functionality allows tracking of any data element in the system and includes tracking of any, insert, update, or delete activity. As a “best practice”, most companies should at minimum choose to follow critical master data elements such as banks, products, and accounts. Since these are low volume entities, they will have a negligible performance impact.

The key to audit success is being mindful of what you are tracking. For example, enabling audit on high volume areas such as sales order entry could well have a negative impact on performance, especially if all data columns are being tracked. On the other hand, experience has shown that if the setup is done with careful consideration, i.e. only tracking essential columns, then audit can be safely enabled without negatively affecting performance. As with any change, it is recommended that benchmark timings be taken before and after the audit function is activated to confirm performance metrics.

Auditing has many benefits

Apart from satisfying your auditors, audit tracking in EBS has many benefits, such as:

1. Reduce the volume of stuck transactions, the “one-touch flow”
2. Eliminate misappropriated accounting before it occurs, remove double accounting
3. Fast track metric creation, where have we been how can we improve?

What is the one-touch flow?

EBS users working with sales order or invoice creation will be familiar with the situation where data has to be data fixed by IT due to incorrectly defined product attributes or accounting setup. While this isn’t necessarily going to cause alarms with the auditors, it will certainly waste a lot of time and irritate your customers. Wouldn’t it be far better to have orders, invoices, sub-ledger and journal transactions flow with just one touch origination rather than revisiting at every blocking stage?

Let’s take an example where an inventory product has been defined as an expense item, and instead of shipping to your customer, it immediately bills them for the item. This will certainly not endear you to the customer, however, it is a common type of issue.

By using audit on these critical or blocking transaction attributes, you can be immediately alerted whenever this type of situation arises, thereby allowing you to proactively correct the data before it becomes a blocking issue. By defining the criteria for data quality, master data and business teams would be notified as soon as a rule has been broken.

Example problem flow:

Accounting Mismatches

Account type mismatch is another red flag. This occurs when the account type of the segment value is different from the account type on a code combination that uses that value. This usually occurs when the account was initially created with the wrong account type.

Changing the account type in the account value form will not change the account type of the code combination. The code combination keeps its original type, e.g. Expense instead of Asset. At year-end, Profit and Loss balances will be rolled up into retained earnings, and balance sheet YTD totals will be rolled forward. Manual intervention is required to correct the year-end balances. This manual intervention generates significant IT support and user frustration. Refer to Oracle General Ledger Misclassified Accounts Setup Test (Doc ID 463517.1) for details.

A better option is to avoid the issue by implementing proper auditing to isolate the error before the code combinations are generated. In this example, the audit would be based on criteria for an accounting segment/account type for a given account number range. Exceptions would be generated, allowing time for data resolution before postings commence.

The Excel report below is from our FND flex values report which can be used to review your account types.

Fast track metric creation

Creating metrics allows you to measure the performance of your processes and systems and allows you to optimize important operational areas. For example you could measure the shift in dates between what was originally promised to a customer versus the actual delivery date. To do this we simply enable a couple of audit columns on the order line and then measure the shift between the original customer promise date against the actual ship date. This is an area where benchmark performance should definitely be tested since order entry tends to be a high-volume process area. In my experience, tracking a small number of columns has a minimal performance impact, but can readily prove this by using the AWR database performance reports. These allow you to record the benchmark before and after the audit function has been enabled.

Or alternatively, you can use our delivered in full and on time (DIFOT) report

DIFOT report example, columns J & K highlight full and or late delivery.

You can use the Blitz Report Toolkit – DBA for your performance measurement, see AWR reports as shown below.

System administration and setup

The audit setup execution takes a matter of minutes, however, before initiating the setup, there should be considerable time invested in planning. Time spent on planning is a small investment that will save a significant amount of frustration and IT support time. I recommend you enlist the help of your database administrator to monitor the before and after timings using a database monitoring tool such as AWR.

Using the item master as an example, here are the required high-level steps:

Use system administrator to add the audit setup:

• Set system profile: Audit Trail: Activate = ‘Yes’
• Enable Audit Installations for your entity that you need to track: Navigation: System Admin > Security > Audit Trail > Install and enable Audit Installation INV – Inventory
• Define Audit tables and add the desired columns. Navigation: System Admin > Security > Audit Trail > Tables. Query for user table name ‘MTL_SYSTEM_ITEMS_B’ and add columns as needed
• Define an Audit Group and associated tables. Navigation: System Admin > Security > Audit Trail > Groups. Create Audit group for a table defined in step C, use a suitable name, e.g., Product Master
• Run the Concurrent program ‘Audit Trail Report for Audit Group Validation’ with parameter per your Audit Group.
• Define an Industry Template for your Audit Group. Navigation: System Admin > Security > Audit Trail Reporting > Audit Industry Template
• Run the concurrent program “Audit Trail Update Tables”.

Limitations with standard EBS audit reporting

The out-of-the-box audit report is quite limited, as shown below. The report fails to show critical details such as Organization Code, Item Code and the use of ID’s prevents user readability.

Alternative reports

While there are limitations with the standard reporting, don’t these deter you from implementing audit functionality. You can use our audit reports to bridge these reporting gaps, transforming the output into a readable Excel report as shown below:

Lessons learned

It is important to invest the time and effort to decide what tables and columns to track. Defining what is essential, what is beneficial, and what is “nice to have” is key to auditing success. Become familiar with your system’s performance statistics which is a good idea anyway. Create benchmark timings before and after to ensure there is no performance impact.

I have worked on multiple implementations with audit enabled and to-date haven’t encountered performance issues. However, as stressed earlier, this should be part of your testing cycle, especially if you are assigning audit to high volume data areas.

Instead of using the standard reports, download Blitz Report and use our toolkits rather than re-inventing wheels. Our AWR monitoring toolkit reports remove the complexity out of database tuning and will certainly simplify your testing. And whilst you are there, why not give your systems a performance assessment to pinpoint pre-existing bottleneck process, user waits, etc?

Summary

The easiest way to report data in EBS is using Blitz Report, there are no volume limitations as with BI Publisher and no need for XML formatting to produce your Excel reports. It takes minutes to create new reports and is seeded with operational reports covering process areas such as R2R, O2C, SCM, and P2P.

Blitz Report is free to use for up to 30 reports. Request a demonstration or try the software yourself.