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.
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.
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.
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.
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.