Oracle® Fusion Applications Developer's Guide 11g Release 1 (11.1.3) Part Number E15524-03 |
|
|
View PDF |
This chapter describes how to implement Audit Trail Reporting in Oracle Fusion Applications.
This chapter contains the following sections:
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.
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.
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.
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.
<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.
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.
<ViewAttribute Name="TableName" IsUnique="true" IsNotNull="true" PrecisionRule="true" EntityAttrName="TableName" EntityUsage="FndTablesEO" AliasName="TABLE_NAME"> <Properties> <CustomProperties> <Property Name="AUDIT_USER_KEY" Value="Y"/> </CustomProperties> </Properties> </ViewAttribute>
Configure attribute lookup based on the FND_LOOKUPS table.
Use this feature to link a code table to a description in the FND_LOOKUPS table, so that "washing machine" is displayed, instead of "A034."The property to use is FND_AUDIT_LOOKUP_TYPE, as shown here:
<ViewAttribute Name="ProblemDescription" IsNotNull="true" PrecisionRule="true" EntityAttrName="ProblemDescription" EntityUsage="ServiceRequestsEO" AliasName="PROBLEM_DESCRIPTION"> <Properties> <CustomProperties> <Property Name="FND_AUDIT_LOOKUP_TYPE" Value="AHL_PLANNING_VISIT_TYPE"/> </CustomProperties> </Properties> </ViewAttribute>
Configure context descriptive attributes.
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.
<ViewObject ... <Properties> <CustomProperties> <Property Name="AUDIT_CONTEXT1_VIEWOBJECT" Value=""/> <-fully qualified vo name, optional for same view-> <Property Name="AUDIT_CONTEXT1_DISPLAYATTR" Value=""/> <-attribute of lookup view to display-> <Property Name="AUDIT_CONTEXT1_WHEREATTR" Value=""/> <-attribute of lookup view to apply where condition-> <Property Name="AUDIT_CONTEXT1_KEYATTR" Value=""/> <-attribute of base view for where condition key-> <-composite key specification - start-> <Property Name="AUDIT_CONTEXT1_WHEREATTR_ADDL" Value=""/> <-attribute of lookup view to apply addl. where condition-> either <Property Name="AUDIT_CONTEXT1_VALUE_ADDL" Value=""/> <-for hardcoded value-> or <Property Name="AUDIT_CONTEXT1_KEYATTR_ADDL" Value=""/> <-attribute of base view for addl. key-> <-composite key specification - end-> </CustomProperties> </Properties> </ViewObject>
Configure attribute lookup based on Foreign Key tables.
This works the same as Configure Attribute Lookup Based on the FND_LOOKUPS Table, but is not limited to the FND_LOOKUPS table.
<ViewObject ... <ViewAttribute Name="OWNER" IsNotNull="true" PrecisionRule="true" EntityAttrName="Owner" EntityUsage="ServiceRequestsEO" AliasName="Owner"> <Properties> <CustomProperties> <Property Name="AUDIT_FK_VIEWOBJECT " Value=" "/> <—fully qualified vo name, optional for same view-> <Property Name="AUDIT_FK_DISPLAYATTR" Value=" "/> <-attribute of lookup view to display-> <Property Name="AUDIT_FK_WHEREATTR" Value=" "/> <-attr of lookup view for where cond-> <-composite key specification - start-> <Property Name="AUDIT_FK_WHEREATTR_ADDL" Value=""/> <-attr of lookup view to apply addl. where condition-> either <Property Name="AUDIT_VALUE_ADDL" Value=" "/> <-for hardcoded value-> or <Property Name="AUDIT_KEYATTR_ADDL" Value=""/> <-for additional key attribute-> <-composite key specification - end-> </CustomProperties> </Properties> </ViewAttribute> </ViewObject>
Define View Criteria on Business Objects.
Use this feature to filter the display of view objects. For instance, a table might be shared by three applications, and you need to filter based on only one of the applications.
Simple query-based View Criteria:
<ViewObject ... <Properties> <CustomProperties> <!-table name --> <Property Name="AUDIT_VIEWCRITERIA_DBOBJECT" Value=""/> <!-simple query condition --> <Property Name="AUDIT_VIEWCRITERIA_CONDITION" Value=""/> </CustomProperties> </Properties> </ViewObject>
Complex query-based View Criteria
<ViewObject ... <Properties> <CustomProperties> <-DB View Name : This DBOBJECT should represent Database View that has a query to list of all the primary keys specific to that View Object-> <Property Name="AUDIT_VIEWCRITERIA_DBOBJECT" Value=""/> <-Key column of the DB View-> <Property Name="AUDIT_VIEWCRITERIA_KEYCOL" Value=""/> <-Key attribute of the base View Object-> <Property Name="AUDIT_VIEWCRITERIA_KEYATTR" Value=""/> </CustomProperties> </Properties> </ViewObject>
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"/>