R12 Audit Trail AP_SUPPLIERS AND vendor_site_code of AP_SUPPLIER_SITES_ALL.

Seeded Functionality (R12) - Demo

Consider the following scenario: We have a requirement where we need to capture the changes in vendor_name of AP_SUPPLIERS AND vendor_site_code of AP_SUPPLIER_SITES_ALL. Now let us see the detailed Stepwise approach to achieve this in R12.

1. Enable Audit Trail Profile Set the system profile AuditTrail:Activate to Yes at the site level. Navigation: System Administrator Responsibility-->System--->Profile

2. Enable Audit for Schemas We need to enable the audit for the schemas which are the owners of the tables, on which we are doing audit. We need to include APPS schema in every case. In this case we need to enable audit for AP (as it is the owner of the tables AP_SUPPLIERS and AP_SUPPLERS_SITES_ALL). Navigation: System Administrator Responsibility--->Security--->AuditTrail--->Install

3. Audit Group Creation Navigation: System Administrator Responsibility--->Security--->AuditTrail--->Groups. Here we need to create the Audit Group under Payables Application. Create an audit group with a proper naming convention, and select the group state as Enabled. Now include the table names AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL.

4. Specify the Columns to be Audited By default, Oracle has specified few columns under few tables that are Audit enabled. Check whether columns which we want to audit exists under these particular tables. If not include the columns. In this case (R12) Oracle has included few columns under AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL. We don’t have the vendor_name under AP_SUPPLIERS. So include it. Navigation: System Administrator Responsibility--->Security--->AuditTrail--->Tables

5. Run the concurrent request Now in order to create the Audit tables and corresponding views for the base tables that we want to audit, we need to run a concurrent request from System Administrator responsibility.

After the successful completion of the request, the audit tables and views will be created in the database. This will create following Audit tables: AP_SUPPLIERS_A for AP_SUPPLIERS AP_SUPPLIER_SITES_ALL_A for AP_SUPPLIER_SITES_ALL Also it will create following main views based on base table and Audit table. AP_SUPPLIERS_AC1 for AP_SUPPLIERS AP_SUPPLIER_SITES_ALL_AC1 for AP_SUPPLIER_SITES_ALL This will create Audit views on individual columns too. These will be in the form of TABLE_NAME_AV% Query it with following command for the audit views of the table AP_SUPPLIERS SELECT * FROM all_objects WHERE object_name LIKE 'AP_SUPPLIERS_AV%'

We have 25 audit views created, because Oracle has included few columns in the setup. When we ran the audit trial update program it creates views for those columns too. Now we need to check the view name for vendor_name column. In this case we have the view AP_SUPPLIERS_AV25 created for vendor_name. Verify it using the query SELECT * FROM ap_suppliers_av25

Similarly, Query for the audit views of the table AP_SUPPLIER_SITES_ALL We are nothing to do with these audit tables and columns. Oracle has used these tables and views and developed a report to report the changed data in these columns.

6. Run the Audit Report Now run the report Audit report from System Administrator responsibility. To do so we need to follow few steps as mentioned below. Create template SUPPLIERS TEMPLATE to include the audit group (the one we created to include the columns while doing the audit setup) i.e. AUDIT SUPPLIERS under Functional group. Navigate--->Audit Trial--->Audit Trial--->Reporting--->Audit Industry Template

Then run the report by navigating Navigate--->Audit Trial--->Audit Trial--->Reporting--->Audit Report

And pass the parameters for the remaining as per the requirement. Let us run the report for AUDIT SUPPLIERS Group.

Here we are passing null for all fields So it tries to report the data which is Transacted by All users, of all transactions, the data which has been changed before or equal to system time.

Select the VENDOR_SITE_CODE under the table name AP_SUPPLIER_SITES_ALL and VENDOR_NAME under the table AP_SUPPLIERS. Run the report.

Output:

Metalink ID's

How do you audit an Oracle Applications' user? [ID 395849.1] Auditing: How Do I Audit Responsibilities and Data? [ID 436316.1] How To Audit Data Changes In Tables Using Triggers [ID 1025832.6] Reference Documentation to Set Up of Audit Trail in Oracle HRMS [ID 111786.1] Understanding Data Auditing in Oracle Applications Tables using Audit Trail (AuditTrail) [ID 69660.1] Which HRMS Tables Need To Be Audited for SOX compliance? [ID 737201.1] Is There a Performance Issue when Enabling the Audit Trail for HRMS Tables? [ID 334379.1] Is There Any Way to Enable Auditing for All Tables in 11i ? [ID 471474.1]