Rem    NAME  
Rem      ckmigfun.sql
Rem    DESCRIPTION
Rem      Creates Functions used for Migration of Designer/2000 data to Oracle Repository
Rem    RETURNS
Rem 	 None
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    CREATED
Rem	  30/03/1999

PROMPT CREATING FUNCTION get_unique_id 
create or replace function get_unique_id (designer_id in number, transaction_id in number) return number is rtnval number; 
begin
	rtnval := NULL; 


	if (designer_id is not null) then 
		select irid 
		into 	 rtnval 
		from 	 jr_id_maps
		where  old_id = designer_id 
		and  	 trans_ref = transaction_id; 				
	else
		rtnval := NULL;
	end if; 

	return rtnval;
	
	exception when others then
	 	dbms_output.put_line ('Warning raised during FUNCTION.GET_UNIQUE_ID');			
		dbms_output.put_line ('...Designer id ' || to_char(designer_id) || ' not found in table JR_ID_MAPS');	
		raise;
end get_unique_id; 
/

PROMPT CREATING FUNCTION get_irid
create or replace function get_irid (designer_id in number, transaction_id in number) return number is
rtnval number; 

begin
	rtnval := NULL; 

	if (designer_id is not null) then 
		select irid 
		into 	 rtnval 
		from 	 jr_id_maps
		where  old_id = designer_id 
		and  	 trans_ref = transaction_id; 
	end if; 

	return rtnval;
 
 	exception when others then
 	    dbms_output.put_line ('Error raised during FUNCTION.GET_IRID');						
		dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));  
		dbms_output.put_line ('...Designer id ' || to_char(designer_id) || ' not found in table JR_ID_MAPS');
		return rtnval;
		
end get_irid; 
/

PROMPT CREATING FUNCTION get_ivid
create or replace function get_ivid (designer_id in number, transaction_id in number) return number is 
rtnval number; 
begin
	rtnval := NULL; 

	if (designer_id is not null) then 
		select ivid 
		into 	 rtnval 
		from 	 jr_id_maps
		where  old_id = designer_id 
		and  	 trans_ref = transaction_id; 
	end if; 

	return rtnval;
 
 	exception when others then
  	    dbms_output.put_line ('Error raised during FUNCTION.GET_IVID');							    				
		dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));  
		dbms_output.put_line ('...Designer id ' || to_char(designer_id) || ' not found in table JR_ID_MAPS');
		return rtnval;
			
end get_ivid; 
/

PROMPT CREATING FUNCTION get_types
create or replace function get_types (designer_id in number) return number is
rtnval number; 

begin
	rtnval := NULL; 

	if (designer_id is not null) then 
		select   type_id
		into 	 rtnval 
		from 	 jr_id_maps
		where    old_id = designer_id;
	end if; 

	return rtnval;
 
	exception when others then
  	    dbms_output.put_line ('Error raised during FUNCTION.GET_TYPES');				
  		dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));  		    			
		dbms_output.put_line ('...Designer id ' || to_char(designer_id) || ' not found in table JR_ID_MAPS');  		
	    return rtnval;
					    
end get_types;
/


-- Bugs fixed 897760
PROMPT CREATING FUNCTION get_element_type 
create or replace function get_element_type (designer_id in number) return varchar2
is rtnval varchar2(40);
begin
	rtnval := ''; 

	if (designer_id is not null) then 
		select   element_type
		into 	 rtnval 
		from 	 jr_id_maps
		where    old_id = designer_id;
	else
		rtnval := 'NULL';
	end if; 

	return rtnval;
 
 	exception when others then
  	    dbms_output.put_line ('Error raised during FUNCTION.GET_ELEMENT_TYPE');				
  		dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));  				
		dbms_output.put_line ('...Designer id ' || to_char(designer_id) || ' not found in table JR_ID_MAPS');  	
		return rtnval;
					   
end get_element_type;
/

PROMPT CREATING FUNCTION get_occur_type 
create or replace function get_occur_type (designer_id in number) return varchar2

is rtnval varchar2(3);
begin
	rtnval := ''; 

	if (designer_id is not null) then 
		select   occur_type
		into 	 rtnval 
		from 	 jr_id_maps
		where    old_id = designer_id;
	else
		rtnval := 'NULL';
	end if; 

	return rtnval;
 
	exception when others then
  	    dbms_output.put_line ('Error Raised during FUNCTION.GET_OCCUR_TYPE');				
  		dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));	
		dbms_output.put_line ('...Designer id ' || to_char(designer_id) || ' not found in table JR_ID_MAPS');  		
		return rtnval;	   
			
end get_occur_type;
/

PROMPT CREATING FUNCTION get_def_val_type
create or replace function get_def_val_type(defVal in varchar2, dataType in varchar2 := 'VARCHAR2')
return number is
  strVal varchar2(2000);
  defVal_Literal number(5) := 0;
  defVal_FunctionCall number(5) := 1;
  retVal number(5) := defVal_Literal;
begin
  -- We First initialise the String we will work on
  -- In order to search easily for the special keywords
  -- we will work on an uppercased string and without any leading and trailing spaces
  strVal := upper(ltrim(rtrim(defVal, ' '), ' '));
  
  -- The Rules that apply to decide if the DataType Value Type is Literal or Function Call
  if  instrb(strVal, 'TO_CHAR', 1, 1) = 1 or
      (dataType = 'DATE' and instrb(strVal, '''', 1, 1) != 0) or
      strVal = 'USER' or
      strVal = 'NULL' or
      instrb(strVal, 'SYSDATE', 1, 1) != 0
  then
    retVal := defVal_FunctionCall;
  end if;
  
  return retVal;

end get_def_val_type;
/

PROMPT CREATING FUNCTION get_date_format_mask
create or replace function get_date_format_mask(formatMask in varchar2) return varchar2
is   
  strVal varchar2(60);
  retVal varchar2(60) := formatMask;
begin
  if (formatMask is not null) then
  
	-- strip leading and trailing spaces
    strVal := ltrim(rtrim(formatMask, ' '), ' ');
  
    -- convert format mask to 'rr' if year component is 'yy'
    if instrb(strVal, 'y',1, 2) != 0 and
	   instrb(strVal, 'y',1, 4) = 0 then			
		 
	   select replace(strVal, 'yy', 'rr')
	   into retVal
	   from sys.dual;		 
    end if;  
	
	-- convert format mask to 'RR' if year component is 'YY'
	if instrb(strVal, 'Y',1, 2) != 0 and
	   instrb(strVal, 'Y',1, 4) = 0 then			
			 
	   select replace(strVal, 'YY', 'RR')
	   into retVal
	   from sys.dual;		 
    end if;  
  end if;
  
  return retVal;
  
end get_date_format_mask;
/
