Oracle Applications style “set_who” for custom applications

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,

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.