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.
 
Enjoy!
 
Regards,
 
Admin
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: