you need to be VERY careful with using LAST_NUMBER from user_sequences. This is not always the last number used through the use of NEXTVAL.

This is dependent on the CACHE_SIZE of the sequence in question.

for instance:

Code:
SQL> create sequence seq_1 increment by 1;

Sequence created.

SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
         1

SQL> select last_number, cache_size from user_sequences where sequence_name = 'SEQ_1';

LAST_NUMBER CACHE_SIZE
----------- ----------
         21         20

SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
         2
So this could be the reason why you are having problems in your code as you are using the LAST_NUMBER in the calculations. Just go and check if your CACHE_SIZE is greater than 1.

if you want to reset a sequence number based on values in a table, there is no way around using NEXTVAL to find out what the true setting of a sequence is. Don't worry about this as your code should reset everything.

for instance :
if high ID in table is 10
select nextval from sequence will result in 11
set increment by -1
select nextval from dual (this will select 10 off sequence)
set increment by 1 (next sequence will be 11)

hope this helps.