Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Auditing Record Changes in an E-Business Suite Environment

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print

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.

Last modified on
Tagged in: E-Business Suite
in Technical Hits: 226 0 Comments
0
My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.


Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest
Guest Friday, 23 June 2017