CREATE OR REPLACE TRIGGER trinsinf_soc_trabajador
AFTER INSERT OR UPDATE
ON inf_soc_trabajador
FOR EACH ROW
DECLARE
St              NUMBER;                                                                
St1             NUMBER;                                                                
St2             NUMBER;                                                                
i_status        INTEGER; 
i_fin           NUMBER(1);                                                              
i_flag          NUMBER(1);
i_flag_int      NUMBER(1); 
i_dato_int      NUMBER(19,6);
i_flag_char     NUMBER(2);
i_dato_char     VARCHAR2(255);
i_flag_date     NUMBER(1);
i_dato_date     DATE;
i_flag_number   NUMBER(1); 
i_dato_number   NUMBER(19,6);
i_status_char    NUMBER(02);
i_status_int    NUMBER(02);
i_status_number    NUMBER(02);
i_status_date    NUMBER(02);
i_indice         CHAR(10);
i_secuencia      NUMBER(08);
/**********************************************************/                    
/* Nombre fisico: t0057.tri                               */                    
/* Autor:         Fernando Diaz Medina                    */                    
/* Fecha:         26/Dic/96                               */                    
/* Proyecto:      ADAMv3                                  */                    
/**********************************************************/                    
BEGIN                                                                           
/* Seguridad por Clase de Nómina */                                             
   BEGIN                                                                        
	i_status := sp_existe_tabla('clases_nomina','NS');                                  
	IF i_status = 1 THEN                                                           
		SELECT COUNT(1) INTO St                                                       
		FROM trabajadores_grales t                                                    
		WHERE :new.trabajador = t.trabajador                                     
		AND t.clase_nomina IS NOT NULL;                                               
		IF St <> 0 then                                                               
			SELECT  COUNT(1) INTO St1                                                    
			FROM trabajadores_grales G, rel_usu_clase_nom R
			WHERE G.trabajador = :new.trabajador                                    
			AND   G.clase_nomina = R.clase_nomina
			AND   R.usuario = USER;                                          
			IF St1 !=  St THEN                                                          
				raise_application_error(-20001, '3057008-El Usuario no tiene Autoridad para la Clase de Nómina a la que pertenece el trabajador [inf_soc_trabajador]');     
			END IF;                                                                      
		END IF;                                                                       
	END IF;                                                                        
/* Seguridad por Apartado */                                                   
	SELECT  COUNT(1) INTO St                                                       
	FROM aut_apartados A,trabajadores_grales G                                     
	WHERE :new.trabajador = G.trabajador                                      
	AND G.compania = A.compania                                                    
	AND A.usuario = USER                                             
	AND A.num_apartado = 8                                                         
	AND A.nivel_seguridad > 2;                                                     
	SELECT  COUNT(1) into St1                                                      
	FROM trabajadores_grales G                                                     
	WHERE G.trabajador = :new.trabajador;                                     
	IF St != St1 THEN                                                              
		raise_application_error(-20001, '3057008-El Usuario no tiene Autoridad para Modificar Información Socioeconómica [inf_soc_trabajador]');                      
	END IF;                                                                        
/* Seguridad por Agrupaciones de trabajador */                                  
	SELECT COUNT(1) INTO St                                                        
	FROM rel_trab_agr T,rel_agr_companias C,rel_usu_agr_trab U                     
	WHERE T.compania = C.compania                                                  
	AND T.agrupacion = C.agrupacion                                                
	AND T.agrupacion = U.agrupacion                                                
	AND T.dato = U.dato                                                            
	AND T.trabajador = :new.trabajador                                        
	AND U.usuario = USER;                                            
	IF St != 0 THEN                                                                
		raise_application_error(-20001, '3057008-El Usuario no tiene Autoridad para el Dato de Agrupación al que pertenece el trabajador [inf_soc_trabajador]');      
	END IF;                                                                        
/* Si tipo_indice = 3 (Interpersonal)         */                               
   /* Verifica que exista el trabajador ó la Persona Relacionada  */           
	IF :new.trabajador_persona IS NULL                                        
	AND :new.persona_relacionada IS NOT NULL                                  
	OR  :new.trabajador_persona IS NOT NULL                                       
	AND :new.persona_relacionada IS NULL THEN                                 
		raise_application_error(-20001, '3057006-No se ha especificado trabajador_Persona o bien Persona_Relacionada. [inf_soc_trabajador]');                         
	END IF;                                                                        
	SELECT COUNT(1) INTO St                                                        
	FROM indices_inf_soc i                                                         
	WHERE :new.trabajador_persona = 2                                         
	AND :new.indice_inf_soc = i.indice_inf_soc                                
	AND i.tipo_indice = 3                                                          
	AND NOT EXISTS (SELECT NULL                                                    
		FROM personas_relacionada p                                                   
		WHERE :new.trabajador = p.trabajador                                     
		AND :new.persona_relacionada = p.persona_relacionada);                   
	IF St != 0 THEN                                                                
		raise_application_error(-20001, '3057006-No existe la Persona Relacionada con el trabajador [inf_soc_trabajador]');                                           
	END IF;                                                                        
	SELECT  COUNT(1) INTO St                                                       
	FROM indices_inf_soc i                                                         
	WHERE :new.trabajador_persona = 1                                         
	AND :new.indice_inf_soc = i.indice_inf_soc                                
	AND i.tipo_indice = 3                                                          
	AND NOT EXISTS (SELECT  NULL                                                   
			FROM trabajadores t,trabajadores r                                           
			WHERE :new.trabajador = t.trabajador                                    
			AND :new.persona_relacionada = r.trabajador);                           
	IF St != 0 THEN                                                                
		raise_application_error(-20001, '3057006-No existe el trabajador Relacionado con el trabajador [inf_soc_trabajador]');                                        
	END IF;                                                                        
   END;           
/*Actualización de Historial_laboral*/
i_flag_int    := 0;
i_dato_int    := NULL;
i_flag_char   := 0;
i_dato_char   := NULL;
i_flag_date   := 0;
i_dato_date   := NULL;
i_flag_number := 0;
i_dato_number := NULL;
i_fin := 0;
i_status_char := 0;
i_status_int := 0;
i_status_number := 0;
i_status_date := 0;
i_indice := ' ';
 
WHILE i_fin = 0 loop
i_indice := :new.indice_inf_soc;

if i_dato_char IS NULL and i_flag_char < 1 then
   if (:NEW.dato_01 is not null or :NEW.dato_01 <> ' ') and (:old.dato_01 is null or :old.dato_01 = ' ') then
      i_status_char := 1;
   elsif
      (:NEW.dato_01 is null or :NEW.dato_01 = ' ') and (:old.dato_01 is not null or :old.dato_01 <> ' ') then
      i_status_char := 2;
   elsif
      (:new.dato_02 is not null or :NEW.dato_01 <> ' ') and (:old.dato_01 is not null or :old.dato_01 <> ' ')
      and (:new.dato_01 <> :old.dato_01) then
      i_status_char := 3;
   end if;

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 1
               and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 1));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_01;
	 i_flag_char := 1;
     end if;   
end if;
if i_dato_char is null and i_flag_char < 2 then

   if (:NEW.dato_02 is not null or :NEW.dato_02 <> ' ') and (:old.dato_02 is null or :old.dato_02 = ' ') then
      i_status_char := 1;
   elsif
      (:NEW.dato_02 is null or :NEW.dato_02 = ' ') and (:old.dato_02 is not null or :old.dato_02 <> ' ') then
      i_status_char := 2;
   elsif
      (:new.dato_02 is not null or :NEW.dato_02 <> ' ') and (:old.dato_02 is not null or :old.dato_02 <> ' ')
      and (:new.dato_02 <> :old.dato_02) then
      i_status_char := 3;
   end if;

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 2
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 2));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_char := :new.dato_02;
	 i_flag_char := 2;
   end if;   
end if;
if i_dato_char is null and i_flag_char < 3 then
   if (:new.dato_03 is not null or :new.dato_03 <> ' ') and (:old.dato_03 is null or :old.dato_03 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_03 is null or :new.dato_03 = ' ') and (:old.dato_03 is not null or :old.dato_03 <> ' ')  then 
      i_status_char := 2;   
   elsif (:new.dato_03 is not null or :NEW.dato_03 <> ' ') and (:old.dato_03 is not null or :old.dato_03 <> ' ')
     and (:new.dato_03 <> :old.dato_03) then
      i_status_char := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 3
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 3));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_03;
	 i_flag_char := 3;
     end if;   
end if;
if i_dato_char is null and i_flag_char < 4 then
   if (:new.dato_04 is not null or :new.dato_04 <> ' ') and (:old.dato_04 is null or :old.dato_04 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_04 is null or :new.dato_04 = ' ') and (:old.dato_04 is not null or :old.dato_04 <> ' ') then 
      i_status_char := 2;   
   elsif (:new.dato_04 is not null or :NEW.dato_04 <> ' ') and (:old.dato_04 is not null or :old.dato_04 <> ' ')
     and (:new.dato_04 <> :old.dato_04) then 
      i_status_char := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 4
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 4));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_04;
	 i_flag_char := 4;
     end if;   
end if;
if i_dato_char is null and i_flag_char < 5 then
   if (:new.dato_05 is not null or :new.dato_05 <> ' ') and (:old.dato_05 is null or :old.dato_05 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_05 is null or :new.dato_05 = ' ') and (:old.dato_05 is not null or :old.dato_05 <> ' ') then 
      i_status_char := 2;   
   elsif (:new.dato_05 is not null or :NEW.dato_05 <> ' ') and (:old.dato_05 is not null or :old.dato_05 <> ' ')
     and (:new.dato_05 <> :old.dato_05) then 
      i_status_char := 3;   
   end if;

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 5
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 5));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_05;
	 i_flag_char := 5;
     end if;   
end if;
if i_dato_char is null and i_flag_char < 6 then
   if (:new.dato_06 is not null or :new.dato_06 <> ' ') and (:old.dato_06 is null or :old.dato_06 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_06 is null or :new.dato_06 = ' ') and (:old.dato_06 is not null or :old.dato_06 <> ' ') then 
      i_status_char := 2;   
   elsif (:new.dato_06 is not null or :NEW.dato_06 <> ' ') and (:old.dato_06 is not null or :old.dato_06 <> ' ')
     and (:new.dato_06 <> :old.dato_06) then 
      i_status_char := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 6
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 6));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_06;
	 i_flag_char := 6;
     end if;   
end if;
if i_dato_char is null and i_flag_char < 7 then
   if (:new.dato_07 is not null or :new.dato_07 <> ' ') and (:old.dato_07 is null or :old.dato_07 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_07 is null or :new.dato_07 = ' ') and (:old.dato_07 is not null or :old.dato_07 <> ' ') then 
      i_status_char := 2;   
   elsif (:new.dato_07 is not null or :NEW.dato_07 <> ' ') and (:old.dato_07 is not null or :old.dato_07 <> ' ')
     and (:new.dato_07 <> :old.dato_07) then 
      i_status_char := 3;   
   end if;   
begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 7
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 7));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_07;
	 i_flag_char := 7;
     end if;   
end if;
if i_dato_char is null and i_flag_char < 8 then
   if (:new.dato_08 is not null or :new.dato_08 <> ' ') and (:old.dato_08 is null or :old.dato_08 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_08 is null or :new.dato_08 = ' ') and (:old.dato_08 is not null or :old.dato_08 <> ' ') then 
      i_status_char := 2;   
   elsif (:new.dato_08 is not null or :NEW.dato_08 <> ' ') and (:old.dato_08 is not null or :old.dato_08 <> ' ')
     and (:new.dato_08 <> :old.dato_08) then 
      i_status_char := 3;   
   end if;  
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 8
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 8));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_08;
	 i_flag_char := 8;
     end if;   
end if;
if i_dato_char is null and i_flag_char < 9 then
   if (:new.dato_09 is not null or :new.dato_09 <> ' ') and (:old.dato_09 is null or :old.dato_09 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_09 is null or :new.dato_09 = ' ') and (:old.dato_09 is not null or :old.dato_09 <> ' ') then 
      i_status_char := 2;   
   elsif (:new.dato_09 is not null or :NEW.dato_09 <> ' ') and (:old.dato_09 is not null or :old.dato_09 <> ' ')
     and (:new.dato_09 <> :old.dato_09) then 
      i_status_char := 3;   
   end if;  
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 9
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 9));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_09;
	 i_flag_char := 9;
     end if;   
end if; 
if i_dato_char is null and i_flag_char < 10 then
   if (:new.dato_10 is not null or :new.dato_10 <> ' ') and (:old.dato_10 is null or :old.dato_10 = ' ') then
      i_status_char := 1;   
   elsif (:new.dato_10 is null or :new.dato_10 = ' ') and (:old.dato_10 is not null or :old.dato_10 <> ' ') then 
      i_status_char := 2;   
   elsif (:new.dato_10 is not null or :NEW.dato_10 <> ' ') and (:old.dato_10 is not null or :old.dato_10 <> ' ')
     and (:new.dato_10 <> :old.dato_10) then 
      i_status_char := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 10
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 10));
   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_char := :new.dato_10;
	 i_flag_char := 10;
     end if;   
end if;
/*Verifica datos numéricos*/
if i_dato_int is null and i_flag_int < 1 then
if (:new.dato_11 is not null or :new.dato_11 <> 0) and (:old.dato_11 is null or :old.dato_11 = 0) then
      i_status_int := 1;   
   elsif (:new.dato_11 is null or :new.dato_11 = 0) and (:old.dato_11 is not null or :old.dato_11 <> 0) then 
      i_status_int := 2;
   elsif (:new.dato_11 is not null or :NEW.dato_11 <> 0) and (:old.dato_11 is not null or :old.dato_11 <> 0)
     and (:new.dato_11 <> :old.dato_11) then 
      i_status_int := 3;   
   end if;

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 11
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 11));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
     if i_flag = 1 then
	 i_dato_int := :new.dato_11;
	 i_flag_int := 1;
     end if;   
end if;
if i_dato_int is null and i_flag_int < 2 then


if (:new.dato_12 is not null or :new.dato_12 <> 0) and (:old.dato_12 is null or :old.dato_12 = 0) then
      i_status_int := 1;   
   elsif (:new.dato_12 is null or :new.dato_12 = 0) and (:old.dato_12 is not null or :old.dato_12 <> 0) then 
      i_status_int := 2;
   elsif (:new.dato_12 is not null or :NEW.dato_12 <> 0) and (:old.dato_12 is not null or :old.dato_12 <> 0)
     and (:new.dato_12 <> :old.dato_12) then 
      i_status_int := 3;   
   end if;   

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 12
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 12));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_int := :new.dato_12;
	 i_flag_int := 2;
   end if;   
end if;
if i_dato_int is null and i_flag_int < 3 then


if (:new.dato_13 is not null or :new.dato_13 <> 0) and (:old.dato_13 is null or :old.dato_13 = 0) then
      i_status_int := 1;   
   elsif (:new.dato_13 is null or :new.dato_13 = 0) and (:old.dato_13 is not null or :old.dato_13 <> 0) then 
      i_status_int := 2;
   elsif (:new.dato_13 is not null or :NEW.dato_13 <> 0) and (:old.dato_13 is not null or :old.dato_13 <> 0)
     and (:new.dato_13 <> :old.dato_13) then 
      i_status_int := 3;   
   end if; 

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 13
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 13));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_int := :new.dato_13;
	 i_flag_int := 3;
   end if;   
end if;
if i_dato_number is null and i_flag_number < 1 then


if (:new.dato_14 is not null or :new.dato_14 <> 0) and (:old.dato_14 is null or :old.dato_14 = 0) then
      i_status_number := 1;   
   elsif (:new.dato_14 is null or :new.dato_14 = 0) and (:old.dato_14 is not null or :old.dato_14 <> 0) then 
      i_status_number := 2;
   elsif (:new.dato_14 is not null or :NEW.dato_14 <> 0) and (:old.dato_14 is not null or :old.dato_14 <> 0)
     and (:new.dato_14 <> :old.dato_14) then 
      i_status_number := 3;   
   end if;

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 14
	       and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 14));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_number := :new.dato_14;
	 i_flag_number := 1;
   end if;   
end if;
if i_dato_number is null and i_flag_number < 2 then
if (:new.dato_15 is not null or :new.dato_15 <> 0) and (:old.dato_15 is null or :old.dato_15 = 0) then
      i_status_number := 1;   
   elsif (:new.dato_15 is null or :new.dato_15 = 0) and (:old.dato_15 is not null or :old.dato_15 <> 0) then 
      i_status_number := 2;
   elsif (:new.dato_15 is not null or :NEW.dato_15 <> 0) and (:old.dato_15 is not null or :old.dato_15 <> 0)
     and (:new.dato_15 <> :old.dato_15) then 
      i_status_number := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 15
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 15));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_number := :new.dato_15;
	 i_flag_number := 2;
   end if;   
end if;
if i_dato_number is null and i_flag_number < 3 then
if (:new.dato_16 is not null or :new.dato_16 <> 0) and (:old.dato_16 is null or :old.dato_16 = 0) then
      i_status_number := 1;   
   elsif (:new.dato_16 is null or :new.dato_16 = 0) and (:old.dato_16 is not null or :old.dato_16 <> 0) then 
      i_status_number := 2;
   elsif (:new.dato_16 is not null or :NEW.dato_16 <> 0) and (:old.dato_16 is not null or :old.dato_16 <> 0)
     and (:new.dato_16 <> :old.dato_16) then 
      i_status_number := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 16
	       and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 16));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_number := :new.dato_16;
	 i_flag_number := 3;
   end if;   
end if;
if i_dato_number is null and i_flag_number < 4 then
if (:new.dato_17 is not null or :new.dato_17 <> 0) and (:old.dato_17 is null or :old.dato_17 = 0) then
      i_status_number := 1;   
   elsif (:new.dato_17 is null or :new.dato_17 = 0) and (:old.dato_17 is not null or :old.dato_17 <> 0) then 
      i_status_number := 2;
   elsif (:new.dato_17 is not null or :NEW.dato_17 <> 0) and (:old.dato_17 is not null or :old.dato_17 <> 0)
     and (:new.dato_17 <> :old.dato_17) then 
      i_status_number := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 17
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 17));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_number := :new.dato_17;
	 i_flag_number := 4;
   end if;   
end if;
if i_dato_number is null and i_flag_number < 5 then
if (:new.dato_18 is not null or :new.dato_18 <> 0) and (:old.dato_18 is null or :old.dato_18 = 0) then
      i_status_number := 1;   
   elsif (:new.dato_18 is null or :new.dato_18 = 0) and (:old.dato_18 is not null or :old.dato_18 <> 0) then 
      i_status_number := 2;
   elsif (:new.dato_18 is not null or :NEW.dato_18 <> 0) and (:old.dato_18 is not null or :old.dato_18 <> 0)
     and (:new.dato_18 <> :old.dato_18) then 
      i_status_number := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 18
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 18));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_number := :new.dato_18;
	 i_flag_number := 5;
   end if;   
end if;
if i_dato_number is null and i_flag_number < 6 then
if (:new.dato_19 is not null or :new.dato_19 <> 0) and (:old.dato_19 is null or :old.dato_19 = 0) then
      i_status_number := 1;   
   elsif (:new.dato_19 is null or :new.dato_19 = 0) and (:old.dato_19 is not null or :old.dato_19 <> 0) then 
      i_status_number := 2;
   elsif (:new.dato_19 is not null or :NEW.dato_19 <> 0) and (:old.dato_19 is not null or :old.dato_19 <> 0)
     and (:new.dato_19 <> :old.dato_19) then 
      i_status_number := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 19
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 19));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_number := :new.dato_19;
	 i_flag_number := 6;
   end if;   
end if;
if i_dato_number is null and i_flag_number < 7 then
if (:new.dato_20 is not null or :new.dato_20 <> 0) and (:old.dato_20 is null or :old.dato_20 = 0) then
      i_status_number := 1;   
   elsif (:new.dato_20 is null or :new.dato_20 = 0) and (:old.dato_20 is not null or :old.dato_20 <> 0) then 
      i_status_number := 2;
   elsif (:new.dato_20 is not null or :NEW.dato_20 <> 0) and (:old.dato_20 is not null or :old.dato_20 <> 0)
     and (:new.dato_20 <> :old.dato_20) then 
      i_status_number := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 20
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 20));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_number := :new.dato_20;
	 i_flag_number := 7;
   end if;   
end if;
if i_dato_date is null and i_flag_date < 1 then
if :new.dato_21 is not null and :old.dato_21 is null then
      i_status_date := 1;   
   elsif
      :new.dato_21 is null and :old.dato_21 is not null then 
      i_status_date := 2;   
   elsif :new.dato_21 is not null and :old.dato_21 is not null and :new.dato_21 <> :old.dato_21 then 
      i_status_date := 3;   
   end if;

   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 21
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 21));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_date := :new.dato_21;
	 i_flag_date := 1;
   end if;  
end if;
if i_dato_date is null and i_flag_date < 2 then
if :new.dato_22 is not null and :old.dato_22 is null then
      i_status_date := 1;   
   elsif
      :new.dato_22 is null and :old.dato_22 is not null then 
      i_status_date := 2;   
   elsif :new.dato_22 is not null and :old.dato_22 is not null and :new.dato_22 <> :old.dato_22 then 
      i_status_date := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 22
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 22));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_date := :new.dato_22;
	 i_flag_date := 2;
   end if;   
end if;
if i_dato_date is null and i_flag_date < 3 then
if :new.dato_23 is not null and :old.dato_23 is null then
      i_status_date := 1;   
   elsif
      :new.dato_23 is null and :old.dato_23 is not null then 
      i_status_date := 2;   
      elsif :new.dato_23 is not null and :old.dato_23 is not null and :new.dato_23 <> :old.dato_23 then 
      i_status_date := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 23
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 23));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_date := :new.dato_23;
	 i_flag_date := 3;
   end if;   
end if;
if i_dato_date is null and i_flag_date < 4 then
if :new.dato_24 is not null and :old.dato_24 is null then
      i_status_date := 1;   
   elsif
      :new.dato_24 is null and :old.dato_24 is not null then 
      i_status_date := 2;   
      elsif :new.dato_24 is not null and :old.dato_24 is not null and :new.dato_24 <> :old.dato_24 then 
      i_status_date := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 24
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 24));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_date := :new.dato_24;
	 i_flag_date := 4;
   end if;   
end if;
if i_dato_date is null and i_flag_date < 5 then
if :new.dato_25 is not null and :old.dato_25 is null then
      i_status_date := 1;   
   elsif
      :new.dato_25 is null and :old.dato_25 is not null then 
      i_status_date := 2;   
   elsif :new.dato_25 is not null and :old.dato_25 is not null and :new.dato_25 <> :old.dato_25 then 
      i_status_date := 3;   
   end if;
   begin
     select 1 into i_flag from dual
     where exists(select 1 from parametros_rh
               where compania = '0'
               and parametro_rh = 'Inc_HL_ID'
               and alfanumerico = ltrim(rtrim(i_indice))
               and entero = 25
		   and secuencia_parametro =(SELECT MAX(secuencia_parametro) 
                                         FROM parametros_rh
                                         WHERE compania = '0'
                                         AND parametro_rh = 'Inc_HL_ID'
                                         AND alfanumerico = ltrim(rtrim(i_indice))
                                         AND entero = 25));

   exception
         when no_data_found then
         i_flag:= 0;
   end;
   if i_flag = 1 then
	 i_dato_date := :new.dato_25;
	 i_flag_date := 5;
   end if;   
end if;

        if i_status_char = 2 or i_status_int = 2 or i_status_number = 2 or i_status_date = 2 then
           sp_alta_historial_laboral('0', :new.trabajador, 'ID', :new.indice_inf_soc, sysdate, NULL,
                             'Baja dato inf. socioeconómica', NVL(i_dato_int,0), 
                              NVL(i_dato_number,0), NVL(i_dato_date, NULL), NVL(i_dato_char, ' '), NULL,NULL,NULL);
        end if;
if i_dato_char is not null or i_dato_number is not null or i_dato_date is not null or i_dato_int is not null then

	if i_status_char = 1 or i_status_int = 1 or i_status_number = 1 or i_status_date = 1 then
	   sp_alta_historial_laboral('0', :new.trabajador, 'ID', :new.indice_inf_soc, sysdate, NULL,
                	             'Alta dato inf. socioeconómica', NVL(i_dato_int,NULL), 
        	                     NVL(i_dato_number,NULL), NVL(i_dato_date, NULL), NVL(i_dato_char, ' '), NULL,NULL,NULL);
        elsif   i_status_char = 3 or i_status_int = 3 or i_status_number = 3 or i_status_date = 3 then
	   sp_alta_historial_laboral('0', :new.trabajador, 'ID', :new.indice_inf_soc, sysdate, NULL,
        	                     'Cambio dato inf. socioeconómica', NVL(i_dato_int,0), 
                	             NVL(i_dato_number,0), NVL(i_dato_date, NULL), NVL(i_dato_char, ' '), NULL,NULL,NULL);
	end if;
   	i_dato_char := NULL;
   	i_dato_number := NULL;
   	i_dato_int := NULL;
   	i_dato_date := NULL;
        i_status_char := 0;
	i_status_number := 0;
	i_status_int := 0;
	i_status_date := 0;
      
else
   i_fin := 1;
end if;
end loop;


 IF :NEW.trabajador     <> :OLD.trabajador     OR
    :NEW.indice_inf_soc <> :OLD.indice_inf_soc OR
    :NEW.secuencia      <> :OLD.secuencia      THEN
          raise_application_error(-20001,'3109004-El usuario ha intentado actualizar campo llave [trabajadores_grales]');
 END IF;

END;                                                                            
/ 