Wednesday, August 15, 2007

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

  2. Create Group Virtuate Bank Accounts
    Application Name: Payables
    Audit Group: Virtuate Bank Accounts
    Group State: Enabled
    Add tables:
  3. System Administrator > Security > AuditTrail > Tables

  4. 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

    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

    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

  5. System Administrator > Security > AuditTrail > Install

  6. Query User AP
    Check Audit Enabled if not already checked

  7. System Administrator > Requests > Run

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

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

  10. Industry Template Create:
    Description: Virtuate Bank Accounts
    Functional Areas:
    Virtuate Bank Accounts
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

  2. sqlplus apps
    select * from ap_bank_accounts_all_a;

    should get no rows selected

  3. Payables > Setup > Payment > Banks

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

  5. Check rows in ap_bank_accounts_all_a

  6. sqlplus
    select * from ap_bank_accounts_all_a;

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

  7. Payables > Setup > Payment > Banks

  8. 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

  2. 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!


seanmark said...

Hi Gareth,

I need a supplier audit trail query for objects ap_suppliers_a, ap_supplier_sites_all_a and iby_ext_bank_accounts_a.
can you help me out on this.
Thank you so much.

Sean Mark

Gareth said...

Hi Sean Mark,

For details of how to query Audit Trail tables, please see the following MOS Notes.
Overview of Oracle Applications AuditTrails [ID 60828.1]

Understanding Data Auditing in Oracle Application Tables [ID 69660.1]


Arati said...

hi Gareth

Very useful post. Can you please post the sql for aupplier audit trail query for ap_suppliers_a, ap_supplier_sites_all_a and iby_ext_bank_accounts_a.

Thanks so much

Shiva Krishna said...

Is this document valid for R12.
I did the below set up in our instance and it is not working.

Raised an MOS request with Oracle Support and the Engineer came back with the below comment.

Bank Accounts
Functionality moved to Cash Management.


So the table AP_BANK_ACCOUNTS_ALL is obsolete in R12 and replaced by CE_BANK_ACCOUNTS

Gareth said...

Hi Shiva, This blog post is specific to R11i. R12 bank account tables have migrated to IBY tables (for supplier accounts) and ce_bank_accounts for internal bank accounts.


Sridevi Koduru said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -