Fixing an Audit Tables cock-up

In the course of my current project I've been asked to set up some auditing against Budgets. To do this I'm going to use standard Oracle Apps AuditTrail functionality against the GL_BALANCES tables. The auditted columns will be those that make up the primary key of the table and also the PERIOD_NET_CR and PERIOD_NET_DR columns.

Whoever runs the Budget Import program will be the user recorded as having made the changes.

During my testing on our development environment it became necessary for me to remove one of the columns being audited from the GL_BALANCES_A table (the table holding the audit data for GL_BALANCES).

Oracle say you can't do this. Actually, as I found out later, Metalink Note 383053.1 actually describes how to do this - but I tried the brute force approach of removing the audit table wholesale. This wasn't as straightforward as it might have been so I'm recording the steps here in case they may be useful for anyone else.

As we're hitting the tables directly here - all usual disclaimers apply - use any of the below entirely at your own risk!

If the table being auditted is MY_TABLE then to completely remove all trace of it from Auditting:

Remove the triggers Oracle has created against MY_TABLE (probably called MY_TABLE_AC/AD/AH/AI/AT/AU)
Remove the Procedures Oracle has created - probably called MY_TABLE_ADP/AIP/AUP.

Execute:

SELECT table_id from fnd_tables where table_name = 'MY_TABLE';

and make a note of the result - you will use this to clear down the FND_AUDIT_xxx tables.

Drop the audit table itself - MY_TABLE_A;

Execute the following:

DELETE FROM FND_AUDIT_TABLES WHERE table_id = ;
DELETE FROM FND_AUDIT_COLUMNS WHERE table_id = ;

If you want to clear out the Audit Group you need to delete the relevant record from FND_AUDIT_GROUPS.

If you want to disable the relevant application for auditing then you need to delete the relevant record from FND_AUDIT_SCHEMAS (this is the table that is updated when you tick the application in the Sys Admin / Security / AuditTrail / Install form.

The above steps remove all trace of auditting from Oracle Apps and allow the developer to start again from a clean slate.

Good luck.