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