PeopleSoft Audit Records

Modified GET_PS_OPRID

/* Title: GET_PS_OPRID                                     */
/* Purpose: Retrieves the operator id (OPRID)              */
/*          from a VARCHAR2 comma separated field          */
/*          of the format 'OPRID,OS_USER,MACHINE'          */
/*          If no OPRID is found, it returns '!NoOPRID'    */
/* Limitations: (any grants, privileges, etc)              */
/* Who: PeopleSoft Inc.                                    */
/* Date: 2000-04-07                                        */
Create or replace function SYSADM.GET_PS_OPRID (v_client_info VARCHAR2 )
	return VARCHAR2 is
	i integer;
	offset integer;
	sql_user VARCHAR2(30);
	max_len constant integer := 30;
	first_char constant integer := 1;
begin 
	if ( length(v_client_info) IS NULL ) then 
		/* Oracle delivered: Doesnt account for SQL updates */
		/* modified to select SQL user into oprid field */
		SELECT user INTO sql_user FROM dual;
		
		if ( length(sql_user) IS NULL ) then
			return('!NoOPRID');
		else
			return (substr (sql_user, first_char, max_len));
		end if;
	end if;
	
	i := 1;
	offset := 0;
	/* Test if non-user client. If so, add offset to get program name */
	if ( substr(v_client_info,first_char,1) = ',' ) then
		offset := 4;
		-- return('!NoOPRID');
	end if;

	/* loop until end of name, or end of field length */
	while ( (substr(v_client_info,i + offset,1)) <> ',' and i < max_len) loop
		i := i + 1;
	end loop;
	
	return (substr (v_client_info, first_char + offset, i));
end GET_PS_OPRID;
/