12 Implementing Audit Trail Reporting

This chapter describes how to implement Audit Trail Reporting in Oracle Fusion Applications.

This chapter contains the following sections:

Section 12.1, "Introduction to Audit Trail Reporting"
Section 12.2, "Implementing Audit Trail Reporting"

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.

The user interface, shown in Figure 12-1:

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 holds seed data for the applications modules that can be audited for a given application. This table holds data of the attributes in a view object that have auditing enabled. 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)

WEBAPP	APPLICATION_MODULE
VARCHAR2(80)	VARCHAR2(240)

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:

Update the ApplicationAuditDB connection to point to your Audit Vault Server (login as auditor) in your application.
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.
```
<Properties>
    <CustomProperties>
      <Property
        Name="Auditable"
        Value="Y"/>
    </CustomProperties>
</Properties>
```

Determine which attributes of each business object to not audit. By default, all attributes are shown except:

Attributes with a non-translatable custom property with Name="Auditable" and Value="N". This follows an opt-out policy. That is, if no property is defined, the value is assumed to be "Y."
Attributes that have display hint set to hidden.
Attributes that are history columns; for example: Created By, Creation Date, Last Updated By

<ViewAttribute
    Name="Attribute1"
    IsUpdateable="false"
    PrecisionRule="true"
    EntityAttrName="Attribute1"
    EntityUsage="AttributeEO"
    AliasName="ROWID">
    <Properties>
      <CustomProperties>
        <Property
          Name="Auditable"
          Value="N"/>
      </CustomProperties>
      <SchemaBasedProperties>
        <DISPLAYHINT
          Value="Hide"/>
      </SchemaBasedProperties>
    </Properties>
  </ViewAttribute>

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.
```
<Properties>
    <CustomProperties>
      <Property
        Name="Auditable"
        Value="Y"/>
    </CustomProperties>
    <SchemaBasedProperties>
      <LABEL
        ResId="ViewObjectVO_LABEL"/>
      <TOOLTIP
        ResId="ViewObjectVO_TOOLTIP"/>
    </SchemaBasedProperties>
  </Properties>
  <ResourceBundle>
    <XliffBundle
      id="oracle.apps.fnd.applcore.audit.test.model.view.common.ViewObjectVOMsgBundle"/>
  </ResourceBundle>
```
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.

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.

<itemNode id="auditSetup" focusViewId="/AuditPage" label="Audit Setup"
     taskType="dynamicMain"
     taskFlowId="/WEB-INF/oracle/apps/fnd/applcore/audit/ui/flow/AuditSetupAdminTF.xml#AuditSetupAdminTF"/>

Audit Reporting Taskflow - For Administrator Use Only

To use the public taskflow for "Admin reporting" the package structure is /WEB-INF/oracle/apps/fnd/applcore/audit/ui/flow/AuditReportAdminTF.xml#AuditReportAdminTF.

<itemNode id="auditObjectReport" focusViewId="/AuditPage"
          taskType="dynamicMain"
          taskFlowId="/WEB-INF/oracle/apps/fnd/applcore/audit/ui/flow/AuditReportAdminTF.xml#AuditReportAdminTF" label="Admin UI"/>

Audit Reporting Taskflow Business Object Specific

To use the public taskflow for "Object specific reporting" the package structure is /WEB-INF/oracle/apps/fnd/applcore/audit/ui/flow/AuditReportObjectSpecificTF.xml#AuditReportObjectSpecificTF.

These parameters need to be passed:

Full name of the view object (Required)
Primary Key (Required)
FromDate (Optional)
ToDate (Optional)

<itemNode id="auditAdminReport" focusViewId="/AuditPage"
          taskType="dynamicMain"
          taskFlowId="/WEB-INF/oracle/apps/fnd/applcore/audit/ui/flow/AuditReportObjectSpecificTF.xml#AuditReportObjectSpecificTF"
          label="Object Specific UI"
   parametersList="viewObject=oracle.apps.fnd.applcore.patterns.test.model.view.ServiceRequestsVO;pkValue=157;fromDate=01/01/2010;toDate=12/31/2010"/>