Auditing Record Changes in an E-Business Suite Environment

A question that comes up all the time on the OTN Forums is “How do I know who updated this record?”. Generally the response is to point the member in the direction of the last_updated_by column on the table. The next question often then comes up… So how do I see what the old value was?”. Hmmmm… a little more tricky. We can try to look at flashback query however if the update was more than a few hours ago on a busy OLTP system then the chances are the undo has been cleared out. Sometimes we get lucky and the table holds a date-tracked history (HRMS tables for example). But usually it’s just tough luck – once the data has been overwritten, it’s gone (without resorting to backups).
So if you have some important tables in your E-Business Suite system that you wish to track changes on such as this, then you might want to consider enabling AuditTrail on those tables. It doesn’t require any additional licences and is relatively easy to configure. Rather than just narrating what it does, I’ll explain with an example.

Assume we have a concern that somebody might update the definition of a form function in EBS and do things we should be tracking; things like altering the parameters to the form. First we need to know what table(s) the data is being maintained in. There are several ways to do this, however often the easiest it to simply do Help > Record History or Help > Diagnostics > Examine > [SYSTEM|LAST_QUERY] and track the view through to the base table(s). So in our case it is fnd_form_functions.

Form Last Query

In order to use AuditTrail the table needs to be registered with the application. For standard tables this is (generally) done – you can check for the existence of a record in FND_TABLES – however for custom tables you’ll need to register the table using ad_dd.register_table and ad_dd.register_column, then register the primary key of the table using ad_dd.register_primary_key and ad_dd.register_primary_key_column. Unfortunately there isn’t a screen with update capabilities to do this. What I do for custom tables is use a script which reads the data dictionary for a given table and does all this for me. Anyway, once we have the table registered, we need to enable the owner of that table for auditing. In the System Administrator responsibility, navigate to Security > AuditTrail (all our configuration will be done via this menu path) and select the Install option. For our table, fnd_form_functions, the owner is APPLSYS.

SQL> Select owner, table_name   2  From all_tables   3  Where table_name='FND_TABLES';  OWNER                          TABLE_NAME ------------------------------ ------------------------------ APPLSYS                        FND_TABLES 

In my vision instance this user is already enabled.

Enable User for Auditing

Next we need to create an Audit Group – this is a logical grouping of like audited tables. Navigate to the Groups menu option and we’ll create that. Note that we have to set the initial status to Enable Requested. This is very important.


 

Create Audit Group

Once that is done we have to say which columns we are interested in auditing. So we navigate to Tables and query back ours. The primary key is a must – otherwise how would pin down a specific record? Other than that, we can choose whichever we wish. It might be tempting to just pick everything here, but please try and resist! The more we audit, the greater overhead. In my case I’m going to select the PARAMETERS and TYPE columns.

Select Columns

Finally we just need to run the job AuditTrail Update Tables from the same responsibility.
What this job does is create a set of triggers on the table which fire for all DML statements.

SQL> Select trigger_name, trigger_type, triggering_event   2  From dba_triggers   3  Where table_name='FND_FORM_FUNCTIONS';   TRIGGER_NAME                   TRIGGER_TYPE     TRIGGERING_EVENT ------------------------------ ---------------- ------------------------------ FND_FORM_FUNCTIONS_AH          BEFORE STATEMENT INSERT FND_FORM_FUNCTIONS_AI          AFTER EACH ROW   INSERT FND_FORM_FUNCTIONS_AT          BEFORE STATEMENT UPDATE FND_FORM_FUNCTIONS_AU          AFTER EACH ROW   UPDATE FND_FORM_FUNCTIONS_AC          BEFORE STATEMENT DELETE FND_FORM_FUNCTIONS_AD          AFTER EACH ROW   DELETE  6 rows selected.  

It also creates a shadow table (generally the base table suffixed with _A and a bunch of helper views.

SQL> ;   1  Select owner, object_type, object_name   2  From dba_objects   3* Where object_name Like 'FND_FORM_FUNC%' and created>sysdate-1 SQL> /  OWNER      OBJECT_TYPE         OBJECT_NAME ---------- ------------------- ------------------------------ APPS       PROCEDURE           FND_FORM_FUNCTIONS_AUP APPS       PROCEDURE           FND_FORM_FUNCTIONS_ADP APPS       TRIGGER             FND_FORM_FUNCTIONS_AH APPS       TRIGGER             FND_FORM_FUNCTIONS_AI APPS       TRIGGER             FND_FORM_FUNCTIONS_AT APPS       TRIGGER             FND_FORM_FUNCTIONS_AU APPS       TRIGGER             FND_FORM_FUNCTIONS_AC APPS       TRIGGER             FND_FORM_FUNCTIONS_AD APPS       VIEW                FND_FORM_FUNCTIONS_AV1 APPS       VIEW                FND_FORM_FUNCTIONS_AV2 APPS       VIEW                FND_FORM_FUNCTIONS_AV3  OWNER      OBJECT_TYPE         OBJECT_NAME ---------- ------------------- ------------------------------ APPS       VIEW                FND_FORM_FUNCTIONS_AC1 APPLSYS    TABLE               FND_FORM_FUNCTIONS_A APPS       SYNONYM             FND_FORM_FUNCTIONS_A APPS       PROCEDURE           FND_FORM_FUNCTIONS_AIP  15 rows selected. 

So whenever we make any change to a record in that table, the corresponding trigger will fire and store a copy of the data how it looked before I made the change (using the packages generated above). So I’ll do that now on function AP_APXIISIM_VIEW. Now we see that we have a record in the table of how the record looked prior to my change.

SQL> ;   1  select audit_timestamp, audit_user_name, function_id, parameters   2* from fnd_form_functions_a SQL> /  AUDIT_TIMESTAMP    AUDIT_USER FUNCTION_ID PARAMETERS ------------------ ---------- ----------- ---------------------------------------- 15-MAY-17          OPERATIONS        4371 QUERY_ONLY="YES" 

We can however use the supporting views (details of which can be found here) to get a more comprehensive view.

SQL> ;   1  select audit_timestamp, audit_user_name, function_id, parameters   2  from fnd_form_functions_ac1 where function_id=4371   3* order by audit_timestamp desc SQL> /  AUDIT_TIMESTAMP               AUDIT_USER FUNCTION_ID PARAMETERS ----------------------------- ---------- ----------- ---------------------------------------- 15-MAY-2017 15:13:20                            4371 QUERY_ONLY="YES" JK_TEST_PARAM=YES 15-MAY-2017 15:07:39          OPERATIONS        4371 QUERY_ONLY="YES" 

There are standard reports available in the Audit Trail Reporting sub-menu, although functionality is a little “light”. Personally I would recommend using the provided views and writing your own queries. The key thing to remember is that the shadow table holds the information before the change was made, however the audit username is the one who caused the change to that record. Some people would prefer to see the user who made the changes to that record – for that you need to do a bit of messing around with analytic functions to pull the user up from the previous row.
Finally be aware that if you add new columns to be audited then you need to run the “AuditTrail Update Tables” job to add those into the database objects.

The Oracle E-Business Suite System Administrator’s Guide – Security contains a lot of useful information if you wish to know more. However I would encourage you to consider this method before opting for a custom approach.

Leave a Reply

Your email address will not be published. Required fields are marked *

fourteen − 3 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.