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.

Creating CSV files the (well - an) easy way

Recently I've been asked to generate some GL extracts. Specifically:

• A GL balance extract
• An extract of the account segment values and descriptions
• An extract of the hierarchies for the various account segments.

Now, whilst something like a hierarchy might seem an ideal candidate for expressing in XML format, my employer wants this generated the old fashioned .CSV way. I knocked up the below function which let's you generate your CSV data record as simply as:

v_file_line := csvize(t_stringlist('Contents of Field 1','Contents of "Field 2"',etc...));

You can specify as many fields as you like and the function deals appropriately with strings containing the quote character (by double quoting them - which seems to be pretty standard).

TYPE t_stringlist IS TABLE OF VARCHAR2(256);

-- Format a list of strings as CSV
----------------------------------
function csvize(p_stringlist t_stringlist) return varchar2 is
deliminator constant char(1) := '"';
separator constant char(1) := ',';
line VARCHAR2(1000);
begin
FOR i IN p_stringlist.FIRST .. p_stringlist.LAST LOOP
line := line || deliminator || REPLACE(p_stringlist(i),deliminator, deliminator || deliminator) || deliminator;
IF i != p_stringlist.LAST THEN
line := line || separator;
END IF;
END LOOP;
RETURN line;
end;

If you find it useful, let me know!