Why not? From our experiences, Oracle applications has one of the best transaction auditing approach, by logging who created and updated transactions while data is processed through a form based application.
Adapting the same methodology, recently we have created almost similar functionality for our upcoming custom applications. If you intend to use this solution with your own forms applications, make sure that your tables have the following four columns as mandatory.
ALTER TABLE BAC_MENU
ADD(
CREATED_BY NUMBER ,
CREATION_DATE DATE ,
LAST_UPDATE_DATE DATE ,
LAST_UPDATED_BY NUMBER
);
set_who procedure
PROCEDURE set_who IS
blk_name VARCHAR2(40);
curr_item VARCHAR2(40);
curr_mode VARCHAR2(40);
trx_date date := sysdate;
curr_user NUMBER;
BEGIN
--Get the current block name
blk_name := name_in(':SYSTEM.CURRENT_BLOCK');
--Judge the current transaction mode
curr_mode := name_in(':SYSTEM.RECORD_STATUS');
--if you are passing login details
--using global variables, adjust the following
--lines accordingly
curr_user := name_in(':PARAMETER.P_USER_ID');
if curr_mode='CHANGED' then
COPY(curr_user,blk_name||'.LAST_UPDATED_BY');
COPY(to_char(trx_date,'dd/mm/yyyy HH24:MI:SS'),blk_name||'.LAST_UPDATE_DATE');
elsif curr_mode = 'INSERT' then
COPY(curr_user,blk_name||'.CREATED_BY');
COPY(to_char(trx_date,'dd/mm/yyyy HH24:MI:SS'),blk_name||'.CREATION_DATE');
end if;
--Mandatory columns with tables
--against which you will log
--the insert, update user details & time
/*
CREATED_BY
CREATION_DATE
LAST_UPDATE_DATE
LAST_UPDATED_BY
*/
END;
Now attach this procedure with individual forms or make it a part of a custom PL/SQL library for global calls. You can call the procedure from PRE-INSERT and PRE-UPDATE triggers at block level to achieve the scope.
regards,