This blog is an excerpt from a longer article by the same name published in the Winter 2020 issue of OATUG Insight magazine. Members can read the full-length article there.
Auditing data in Oracle EBS
Auditors are increasingly recommending high risk activities be tracked and reported. Oracle provides functionality to address this, but some EBS customers, concerned that audit tracking may create performance issues, don’t enable it. Too often, audit tracking in EBS has been an afterthought to the overall implementation, when it should be a core component.
Did you know that Oracle provides standard audit functionality using the system administrator responsibility included in your licence 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, product and accounts. These low-volume entities will have negligible performance impact.
On the other hand, 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. 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 functionality is activated to confirm performance metrics.
Apart from satisfying your auditors, audit tracking in EBS has many benefits, such as:
- Reduce the volume of stuck transactions, the “one touch flow”
- Eliminate mis-appropriated accounting before it occurs, remove double accounting
- 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 wastes time and irritates your customers. Wouldn’t it be far better to have orders, invoices, subledger 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 transactional 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 occurs when the account type of the segment value is different from the account type on a code combination that uses that value—usually because the account was 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.
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.
Fast track metric creation
Creating metrics allows you to measure 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 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 audit has been enabled.
You can find suitable metric reports that are free of charge to use in EBS.
For further research on this, use these suggested search engine criteria: creating EBS DIFOT metric reports
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 systems performance statistics, and benchmark timings before and after to ensure there is no performance impact.
Instead of using the standard reports, download a free-to-use EBS toolkit rather than re-inventing the wheel. There are AWR monitoring SQL reports freely available that will take 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.
For further research on this, use search engine criteria: AWR reporting toolkit Oracle EBS
OATUG members: For the full-length article, including additional sections on system administration and set-up, limitations on standard EBS reporting and alternative reports, read it here.
About the Author:
Glen Whelan is an ERP reporting specialist at Enginatics, provider of Blitz Report, the world’s fastest operational reporting for Oracle EBS. He has been working with Oracle E-Business Suite and its technologies since 1993 in numerous industries with special interests in problem solving, solution design and implementation.