/* Correct problem with duplicate evt_num values */
/* 15024555 - Ensure unique evt_num values */
CREATE proc ujo_event_state
    @eoid        varchar(12),
    @que_status  int,
    @evt_num     int
AS
/* If it's a 2 (EVENT_DONE), 3 (HDLR_ERROR), or 4 (UN_SENT), copy it to the proc_event table and delete it */
if (@que_status > 1 AND @que_status < 5)
begin
    begin tran
    update ujo_next_oid set oid = oid + 1 where field = 'evt_proc_num'
    if @@rowcount = 0
    begin
        insert into ujo_next_oid (oid, field) values (1, 'evt_proc_num')
        select @evt_num = 1
    end
    else
    begin
        select @evt_num = oid from ujo_next_oid where field = 'evt_proc_num'
    end
    commit
    
    begin tran
    insert into ujo_proc_event (eoid, joid, job_name, box_name, AUTOSERV, priority,
                            event, status, alarm, event_time_gmt, exit_code, machine,
                            pid, jc_pid, run_num, ntry, text, que_priority, stamp,
                            evt_num, que_status, que_status_stamp)
      select e.eoid, e.joid, e.job_name, e.box_name, e.AUTOSERV, e.priority,
            e.event, e.status, e.alarm, e.event_time_gmt, e.exit_code, e.machine,
          e.pid, e.jc_pid, e.run_num, e.ntry, e.text, e.que_priority, e.stamp,
          @evt_num, @que_status, getdate()
      from ujo_event e
      where eoid = @eoid

    if @@rowcount = 1
    begin
        delete from ujo_event where eoid = @eoid
        commit
    end
    else
    begin
        rollback
    end
end
else
begin
    begin tran
    update    ujo_event
    set    que_status = @que_status, que_status_stamp = getdate()
        where eoid = @eoid
    if @@rowcount !=1
    begin
        print 'event_state stored proc has a problem'
        rollback
        return 0
    end
    commit
end

return 1
GO