12.1 Introduction to Audit Trail Reporting

Audit Trail is a history of the changes that have been made to data in Oracle Fusion Applications. Audit Trail includes information such as who has accessed an item, what operation was performed on it, when it was performed, and how the value was changed. The audit information is logged without any interaction from the end user.

12.2 Implementing Audit Trail Reporting

Database auditing involves observing a database to be aware of the actions of database users. This is often for security purposes, such as to ensure that information is not accessed by those without the permission to access it. Audit setup provides a user interface mechanism for administrators to choose and select the objects and the specific attributes that need to be audited. When an object has been set to be audited using the audit setup user interface, any DML actions on this object, that is, its underlying database schema objects, are captured and stored for reporting purposes.

The Audit Setup UI page is a public taskflow that is made available through the Oracle Middleware Extensions for Applications library. This taskflow can be included in an application that is accessible by the administrator. The UI consists of an Applications Tree Table that holds the structure of the auditable application modules, the children application modules, if present, and all the auditable view objects and their children. The administrator selects the view object on which to start auditing and in the Applications Table components below the tree table, the administrator chooses to add the attributes that need to be audited.

The Audit application holds all code for the Audit Setup and Audit Reporting taskflows.

Allows an administrator to select the objects and its specific attributes to audit.

Provides the ability to stop auditing.

Displays which objects currently are being audited.

Figure 12-1 Audit Setup Page

Metadata

Two database schema tables, FND_AUDIT_WEBAPP_AM and FND_AUDIT_ATTRIBUTES, hold metadata information that is required for Auditing.

FND_AUDIT_WEBAPP_AM captures the list of application modules for an application.

WEBAPP

APPLICATION_MODULE

VARCHAR2(80)

VARCHAR2(240)

FND_AUDIT_ATTRIBUTES table data is populated during runtime when view objects are configured using the Audit Setup UI. This is an internal table and should not be changed.

WEBAPP

VIEW_OBJECT

VIEW_ATTRIBUTE

ENABLED_FLAG

TABLE_NAME

COLUMN_NAME

VARCHAR2(80)

VARCHAR2(240)

VARCHAR2(80)

VARCHAR2(1)

VARCHAR2(30)

VARCHAR2(30)

Important Code Components

The important code components involved in creating the user interface are:

AuditSetup.jsff: The page fragment that holds the components for the Audit Setup UI. It consists of the Applications Tree Table components containing the structure of the applications modules from the fnd_audit_webapp_am for the given application and its children.

AuditTreeBean.java: Session scoped managed bean class for the AuditSetup page fragment. It contains the action/action listener, binding, and display methods. It also creates the Tree Model structure for the Applications Tree Table and filters the data shown in the Applications Table for the selected view object.

AuditModelUtility.java: Utility file that holds the logic for reading the applications modules and traversing through the structure to show those objects that are auditable.

12.2.1 How to Set Metadata

This section covers the steps needed to populate and define metadata needed to run the Audit taskflows.

To populate and define metadata:

Make sure every application has the webApp defined and set. This is done by populating the ASK tables.

Design from where the audit setup flow and reporting flow will be launched. This will be a link. It can be in the tasklist, or within the main area of a page. For example the administration flows can be as tasks in the region area, whereas the business object specific reporting can be placed beside the components that display this business object, such as a table.

This link will be protected to be visible to only those with the correct privileges.

Determine, for a given module, what are the Applications Modules that are to be registered in the metadata table (fnd_audit_webapp_am) for audit purposes. You can use the AuditServiceAM to register these Applications Modules. This service allows data to be extracted and uploaded.

Determine which business objects of each application module may be audited. Once the business objects are identified, set a non-translatable custom property on it with Name="Auditable" and Value="Y". Only when this property is set will the business object be shown in the Audit Setup UI for auditing. This follows an opt-in policy.

To have a user-friendly display name for all application modules, business objects and attributes names the display name property for each needs to be set. For applications modules and business objects it is also a good idea to put in a description, since the description will be shown in the Audit Setup UI and also will give the administrator further information on the business objects and/or application module. The display name and description are available as a property.

For custom business objects, identify the application module the custom business objects belong to, and add this to the metadata table as defined in step 4. Define the "Auditable" property as defined in step 5 on the custom business objects.

Enable the User Key.

To base a search ability on a key other than the primary key of the View Object, you need to set the AUDIT_USER_KEY non-translatable property on this attribute.

This attribute must be a unique value, and it should be set only on one attribute that is not the primary key.

Use to show Context Attributes that define the context of changed record.

For instance, in an audit table, objects may be identified by a primary key, such as A034, that has a description of "washing machine." However, you might need to look up in another table to determine the specific type, such as commercial or residential, or the customer.

This feature provides up to three values to give additional context. Note that the Property Names are fixed. That is, the first Context Attribute must have the AUDIT_CONTEXT1 prefix, the second Context Attribute must have the AUDIT_CONTEXT2 prefix, and the third Context Attribute must have the AUDIT_CONTEXT3 prefix.

12.2.2 How to Use Audit Taskflows

This section covers how to use the Audit taskflows and their taskFlowIDs.

Audit Setup Taskflow - For Administrator Use Only

Product teams should create a control flow for the Done outcome to go back to the original page from which the setup page was accessed. To use the public taskflow for Setup, the package structure is /WEB-INF/oracle/apps/fnd/applcore/audit/ui/flow/AuditSetupAdminTF.xml#AuditSetupAdminTF. This flow provides the Done button.