Audit Trail Must Do: Bank Accounts

If you are paying suppliers and other parties out of the Oracle eBusiness Suite via Oracle Payables and haven't performed the steps documented in Part 1 of this post, do it now! Even if you don't fully understand ...

This post details the steps required to implement the auditing of Banks, Bank Accounts and Bank Account Uses (Supplier to Bank Account Assignment), using standard Oracle Applications AuditTrail functionality. Auditing in this context means create, update and delete actions performed by authenticated Oracle eBusiness Suite Users. Specific columns only are tracked, with a full history from the completion of the setup steps.

The impact of performing these steps is:

• You will be able to run AuditTrail Reports against the columns configured for auditing
• New tables will be created: ap_bank_acccounts_all_a, ap_bank_branches_a, ap_bank_account_uses_all_a
• New triggers will be created on the tables ap_bank_accounts_all, ap_bank_branches and ap_bank_account_uses_all that insert into the _all tables
• There will be a very small storage and performance impact, but in the realm of your entire Oracle eBusiness Suite this impact is negligible and from where I sit, auditing of the items mentioned is a fundamental requirement.

Okay, enough mumble, onto the steps.

Part 1. Setup the AuditTrail Data Capture and Group

Note: Replace references to "Virtuate" with your company name/abbreviation - if you like ;-)

1. System Administrator > Security > AuditTrail > Groups

Create Group Virtuate Bank Accounts
Application Name: Payables
Audit Group: Virtuate Bank Accounts
Group State: Enabled
Add tables:
AP_BANK_ACCOUNTS_ALL
AP_BANK_BRANCHES
AP_BANK_ACCOUNT_USES_ALL
Save

2. System Administrator > Security > AuditTrail > Tables

Query table name AP_BANK_ACCOUNTS_ALL
Add row for column BANK_ACCOUNT_NUM
Add row for column BANK_ACCOUNT_NAME
Add rows for any other columns that you store critical bank account information in
Save

Query table name AP_BANK_BRANCHES
Add row for column BANK_NUMBER
Add row for column BANK_NUM
Add rows for any other columns that you store critical bank account information in
Save

Query table name AP_BANK_ACCOUNT_USES%
Add row for column EXTERNAL_BANK_ACCOUNT_ID
Add row for column VENDOR_ID
Add row for column VENDOR_SITE_ID
Add row for column CUSTOMER_ID
Add row for column CUSTOMER_SITE_USE_ID
Add row for column START_DATE
Add row for column END_DATE
Add rows for any other columns that you store critical bank account information in
Save


3. System Administrator > Security > AuditTrail > Install

Query User AP
Check Audit Enabled if not already checked
Save


4. System Administrator > Requests > Run

AuditTrail Update Tables
(no parameters)
Submit
Review Log to ensure no errors were encountered


5. System Administrator > Security > AuditTrail > Audit Trail Reporting > Audit

Industry Template Create:
Template Name: VIRTUATE_BANK_ACCOUNTS
Description: Virtuate Bank Accounts
Functional Areas:
Add:
Virtuate Bank Accounts
Save

Please Note: If you subsequently make any changes to the above setup you need to rerun AuditTrail Update Tables

Part 2 (optional) Verify auditing of bank accounts is working

1. Check rows in ap_bank_accounts_all_a



sqlplus apps
select * from ap_bank_accounts_all_a;

should get no rows selected

2. Payables > Setup > Payment > Banks

Query an existing bank account, go to Bank Accounts
Change bank account "Number" field and save


3. Check rows in ap_bank_accounts_all_a



sqlplus
select * from ap_bank_accounts_all_a;

Should see old value of changed record, plus other who and when columns etc.

4. Payables > Setup > Payment > Banks

Query the bank account from Step 7, go to Bank Accounts
Revert the change made to bank account "Number" field in Step 2. and save

Part 3 Run Audit Report

1. Run Test Report

System Administrator > Security > AuditTrail > Audit Trail Reporting > Audit Report
Functional Group: Virtuate Bank Accounts
Audit Table Name: AP_BANK_ACCOUNTS_ALL
Transacted By: (your username)
Click Select Columns
Choose BANK_ACCOUNT_NUM in column 1
Click Run Report
View report to see the details of the changes made.

Other Notes

The way that Oracle stores audit information is to record only the changed columns, plus the who (username) when (timestamp) and type (create/update/delete). So getting the complete row information at a given point in time in history is a little tricky, as you need to tree walk back in time through the rows of the audit table.

The associated report is left to a future blog ... maybe a good starting point for a new BIP Report.

At least the Audit Hierarchy Navigator (System Administrator > AuditTrail > AuditTrail Reporting > Audit Hierarchy Navigator) is there to use for now albeit not that useful given the tracking based on primary key ID fields.

Now your auditors might be a bit happier, and if someone tries any trickery on the front end you'll have a history of it on the backend!