
create or replace PROCEDURE       RESET_SEQUENCE (
-- Nombre de la secuencia
seq_name IN VARCHAR2,
-- Valor inicial que debe tener la secuencia
exTableFinalVal IN PLS_INTEGER
) AS

	-- Valor actual de la secuencia
	tableFinalVal INTEGER;
	-- Valor actual de la secuencia
	seqLastVal INTEGER;
	-- Incremento a aplicar
	seqIncrement INTEGER;	
	-- Incremento a aplicar
	seqValFinal INTEGER;	  
	-- Literal de la secuencia
	inc_by VARCHAR2(25);
	-- Literal de la secuencia
	sqlSEQ VARCHAR2(300);  
  
BEGIN
  tableFinalVal := exTableFinalVal;
  if (tableFinalVal<1) then
     tableFinalVal:=1;
  end if;
  
	EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO seqLastVal;

	--cval2 := seqLastVal - tableFinalVal + 1;
	-- Si el ultimo valor de la secuencia es menor que el 
	-- ultimo valor de la tabla, el incremento debe ser positivo
	if (seqLastVal <> tableFinalVal) then
		seqIncrement := tableFinalVal - seqLastVal;	   
	else 
	   seqIncrement :=-1;
	end if;	  
     
    sqlSEQ := 'ALTER SEQUENCE AB39A.'||seq_name||' INCREMENT BY ' || seqIncrement;
     
    EXECUTE IMMEDIATE sqlSEQ;	
    EXECUTE IMMEDIATE 'SELECT AB39A.'||seq_name||'.NEXTVAL FROM dual'  INTO seqValFinal;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE AB39A.'||seq_name||' INCREMENT BY 1';

END reset_sequence;
/


DECLARE
	VALOR NUMBER:=0;
BEGIN 

	SELECT nvl(max(CODTECNICO), 1) AS VAL INTO VALOR FROM AB3901T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3901Q00', VALOR);

	SELECT nvl(max(CODENTIDAD), 1) AS VAL INTO VALOR FROM AB3902T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3902Q00', VALOR);

	SELECT nvl(max(CODPLANE), 1) AS VAL INTO VALOR FROM AB3907T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3907Q00', VALOR);

	SELECT nvl(max(CODELEMENTOPE), 1) AS VAL INTO VALOR FROM AB3908T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3908Q00', VALOR);

	
	SELECT nvl(max(CODPLANG), 1) AS VAL INTO VALOR FROM AB3909T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3909Q00', VALOR);

	SELECT nvl(max(CODELEMPG), 1) AS VAL INTO VALOR FROM AB3910T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3910Q00', VALOR);


	SELECT nvl(max(CODACCIONPG), 1) AS VAL INTO VALOR FROM AB3911T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3911Q00', VALOR);

	SELECT nvl(max(CODPLANIFICACION), 1) AS VAL INTO VALOR FROM AB3913T00;
	VALOR:=VALOR+1;
	reset_sequence('AB3913Q00', VALOR);
	
	SELECT nvl(max(ID_ELEMENTO), 1) AS VAL INTO VALOR FROM AB3905T00;
	VALOR:=VALOR+1000;
	reset_sequence('AB3902_07_10_Q00', VALOR);
	
	
	
END;
/

DROP PROCEDURE RESET_SEQUENCE;

commit;

exit;