Audit Trail for Custom Table in Oracle Apps - Step By Step

Hi All ...

Here the step by step for Audit Trail for Custom Table in Oracle Apps

1. Register Custom Schema 
Navigate to System Administrator Menu/Security/ORACLE/Register

2. Ensure that Audit on the Application is Enabled

Navigate to System Administrator Menu Security/AuditTrail/Install

The owner of table XX_TABLE  is XX_SCHEMA. Hence query on XX_SCHEMA to ensure that Audit is enabled for this Application.

3. Register table,columns and primary key

Here the procedure to register the custom able,columns and primary key in Oracle Applications. Install the procedure on your DB and run the following:

begin

register_table('XX_TABLE','XX_SCHEMA'); /* (table name, table owner) */

commit;

end;

Procedure "register_table":

CREATE OR REPLACE PROCEDURE register_table (

table_name VARCHAR2,

application_short_name VARCHAR2

)

AS

status VARCHAR2 (10);

CURSOR c_columns ( p_table_name all_tab_columns.table_name%TYPE )

IS

SELECT column_name,

data_type,

data_length,

nullable,

ROWNUM,

data_precision,

data_scale

FROM all_tab_columns

WHERE table_name = p_table_name;

CURSOR c_constraints (p_table_name all_tab_columns.table_name%TYPE,

p_application_short_name all_tab_columns.owner%TYPE )

IS

SELECT constraint_name,

table_name,

status

FROM all_constraints

WHERE table_name = p_table_name AND owner = p_application_short_name AND constraint_type = 'P';

CURSOR c_constraint_columns (

p_table_name all_tab_columns.table_name%TYPE,

p_application_short_name all_tab_columns.owner%TYPE

)

IS

SELECT acc.constraint_name,

acc.column_name,

acc.POSITION

FROM all_cons_columns acc, all_constraints ac

WHERE ac.constraint_name = acc.constraint_name

AND ac.table_name = p_table_name

AND ac.constraint_type = 'P'

AND ac.owner = p_application_short_name;

BEGIN

DBMS_OUTPUT.put_line ('Registering Table '|| table_name ||'in application ' || application_short_name);

ad_dd.register_table (p_appl_short_name => application_short_name,

p_tab_name => table_name,

p_tab_type => 'T'

);

FOR r_columns IN c_columns (table_name)

LOOP

DBMS_OUTPUT.put_line ('Registering Column '|| r_columns.column_name);

ad_dd.register_column (p_appl_short_name => application_short_name,

p_tab_name => table_name,

p_col_name => r_columns.column_name,

p_col_seq => r_columns.ROWNUM,

p_col_type => r_columns.data_type,

p_col_width => r_columns.data_length,

p_nullable => r_columns.nullable,

p_translate => 'N',

p_precision => r_columns.data_precision,

p_scale => r_columns.data_scale

);

END LOOP;

FOR r_constraints IN c_constraints (table_name, application_short_name)

LOOP

DBMS_OUTPUT.put_line ('Creating Primary Key Constraint ' || r_constraints.constraint_name);

SELECT DECODE (r_constraints.status,

'ENABLED', 'Y',

'N'

)

INTO status

FROM DUAL;

ad_dd.register_primary_key (p_appl_short_name => application_short_name,

p_key_name => r_constraints.constraint_name,

p_tab_name => table_name,

p_description => 'Primary Key for Table '|| table_name,

p_key_type => 'D',

p_audit_flag => 'Y',

p_enabled_flag => status

);

END LOOP;

FOR r_constraint_columns IN c_constraint_columns (table_name, application_short_name)

LOOP

DBMS_OUTPUT.put_line ( 'Registering Primary Key Column '||

r_constraint_columns.column_name||

' for Constraint '||

r_constraint_columns.constraint_name);

ad_dd.register_primary_key_column (p_appl_short_name => application_short_name,

p_key_name => r_constraint_columns.constraint_name,

p_tab_name => table_name,

p_col_name => r_constraint_columns.column_name,

p_col_sequence => r_constraint_columns.POSITION

);

END LOOP;

END register_table;

4. Create Audit Group

Once you table registered,navigate to System Administrator Menu Security/AuditTrail/Groups 

Application Name: XX Custom Schema
Audit Group: XX Audit
Group State: Enabled

Now, add audit tables to this group[you can add as many tables]
User Table Name: XX_TABLE

5. Run Concurrent program “AuditTrail Update Tables”

This process can be run from System Administrator responsibility. It has no parameter. Running this process will create the Audit tables and the triggers that manage Audit data.

6. Ensure that Audit Tables have been created as expected

SELECT object_name, object_type

FROM all_objects

WHERE object_name LIKE 'XX_TABLE_A%'

OBJECT_NAME                            OBJECT_TYPE

--------------------------                      --------------------------

XX_TABLE_A                               TABLE

XX_TABLE_A                               SYNONYM

XX_TABLE_AC                            TRIGGER

XX_TABLE_AC1                          VIEW

XX_TABLE_AD                            TRIGGER

XX_TABLE_ADP                          PROCEDURE

XX_TABLE_AH                            TRIGGER

XX_TABLE_AI                              TRIGGER

XX_TABLE_AIP                            PROCEDURE

XX_TABLE_AT                             TRIGGER

XX_TABLE_AU                            TRIGGER

XX_TABLE_AUP                          PROCEDURE

XX_TABLE_AV1                           VIEW

Fine, this proves that the concurrent program in Step 5 did its job.

Optionally, you may run concurrent process “AuditTrail Report for Audit Group Validation” to validate the success of Audit Table/Trigger creation.

7. Add further columns for Audit Trail

By default Oracle will Audit Trail on all columns that are a part of first available Unique Index on XX_TABLE.
However further columns can be added to the Audit Trail. Lets say you wish to Audit Trail on Column Meaning too.
Navigate to System Administrator Menu Security/AuditTrail/Tables

You can add additional columns to audit trail and re-execute Step 5.
Please note that adding columns for Audit could have been done immediately after Step 4.

You are DONE...

Usefull notes:

How To Enable Auditing On A Table (Doc ID 1359749.1)

Unable to Enable Audit Trail for Custom Objects (Doc ID 433527.1)

Good Luck ...