CREATE OR REPLACE PACKAGE OMA_MAINT AS
  TYPE value_record IS RECORD( value1_type VARCHAR2(200)
  ,value2_type VARCHAR2(200)
  );
    TYPE value_list IS TABLE OF value_record INDEX BY binary_integer;
    
    FUNCTION OMA_MAINTENANCE (p_reqid IN NUMBER
	,p_parameter_name IN VARCHAR2
	)RETURN value_list;
END OMA_MAINT;

/


CREATE OR REPLACE PACKAGE BODY OMA_MAINT AS
 
  FUNCTION OMA_MAINTENANCE (
  p_reqid IN NUMBER,
  p_parameter_name IN VARCHAR2
  ) RETURN value_list AS
   
    l_list value_list;
    l_parameter_number VARCHAR2(20);
    l_valeur varchar2(100);
    l_request_number number;
    l_application VARCHAR2(100);
  
   BEGIN
 if length(p_parameter_name)>10 then
  l_parameter_number:= substr(p_parameter_name,length(p_parameter_name)-1,2);
  else
    l_parameter_number:= substr(p_parameter_name,length(p_parameter_name),1);
 end if;
 
 
 case  to_number(l_parameter_number)
        when 20 then 
            l_valeur:='BR%';
            l_request_number:=1;        
        when 21 then 
            l_valeur:='GR%';
            l_request_number:=1;      
        when 22 then 
            l_valeur:='kl.user_data3';
            l_request_number:=2;
        when 23 then
            l_valeur:='kl.user_data4';
            l_request_number:=2;
        
        when 24 then 
            l_valeur:='EX%';
            l_request_number:=1;
        
        when 25 then
            l_valeur:='RT%';
            l_request_number:=1;
        
        when 26 then
            l_valeur:='RT%';
            l_request_number:=1;
        
        when 27 then 
            l_valeur:='[REQ.P.OMA_REAL_TEAM_FO]';
            l_request_number:=3;
            
        when 28 then 
            l_valeur:='[REQ.P.OMA_REAL_TEAM_BO]';
            l_request_number:=3;
          
        when 33 then 
            l_valeur:='[REQ.P.OMA_BUSINESS_REP]';
            l_request_number:=3;
        
        when 34 then
            l_valeur:='[REQ.P.OMA_GEO_REP]';
            l_request_number:=3;
        
        when 35 then
            l_valeur:='[REQ.P.OMA_IT_REP]';
            l_request_number:=3;
        
        when 36 then 
            l_valeur:='[REQ.P.OMA_IT_OWNER]';
            l_request_number:=3;
            
        when 37 then
            l_valeur:='[REQ.P.OMA_EXPERTS]';
            l_request_number:=3;
                       
        when 38 then 
            l_valeur:='KU%';
            l_request_number:=1;      
        
        else        
            l_valeur:='0';
            l_request_number:=0;      
         
  end case;

     case l_request_number  
        when 1 then 
           select security_group_id 
           ,security_group_name
              bulk collect into l_list
              from KNTA_SECURITY_GROUPS
              where security_group_name like l_valeur
              and enabled_flag = 'Y'
              order by 2
           ;
                
        when 2 then 
        select application into l_application from kcrt_requests where request_id=p_reqid;
        
         SELECT ksg.security_group_id
         ,ksg.security_group_name
                bulk collect into l_list       
                FROM KNTA_SECURITY_GROUPS ksg, knta_lookups kl
                WHERE kl.lookup_type = 'APPLICATION'
                AND kl.lookup_code = l_application
                AND ksg.security_group_name = l_valeur
                AND ksg.enabled_flag = 'Y'
                ORDER BY 2
                ;
       
           
         when 3 then 
       
            SELECT kuv.user_id
            ,kuv.full_name
              bulk collect into l_list         
              FROM knta_users_v kuv,knta_user_security kus
              WHERE kuv.user_id = kus.user_id
              AND '#@#' || REPLACE(l_valeur,', ','#@#') || '#@#' like '%#@#' || kus.security_group_id || '#@#%'
              AND NVL (kuv.end_date, CURRENT_DATE) >= CURRENT_DATE
              ORDER BY kuv.full_name
              ;
         
        else        
            l_list(1).value1_type := '0';l_list(1).value2_type := '0';
           
  end case;
          
  RETURN l_list;
      
END OMA_MAINTENANCE;
   
 
END;

/