/****** Object:  Stored Procedure dbo.ujo_set2act    Script Date: 12/1/2005 11:49:51 PM ******/

create proc  ujo_set2act
    @status  int,
    @run_num int,  /* if = 0 its a restart of a box */
    @joid  int,
    @gmt   int
AS

declare @auto_hold  tinyint,
    @auto_hold_over tinyint,
    @curr_status  int,
    @over_num int,
    @job_name varchar(64),
    @has_ext  int,
    @isbox_job int,
    @box_status int

SELECT @isbox_job = 0

/* Get the Current Status */

SELECT     @curr_status = status, @over_num=over_num
FROM     ujo_job_status
WHERE     joid = @joid


/* See if its an Autohold Job - job_name is used later */

select     @job_name = job_name, @auto_hold = auto_hold
from     ujo_job
where     joid = @joid

/* Check whether job is inside the box */
select @isbox_job = box_joid
from ujo_job
where joid = @joid and box_joid != NULL

/* if box job get the status */
if (@isbox_job > 0)
begin
    select @box_status = status
    from ujo_job_status
    where joid = @isbox_job
end

/* If theres an override in effect, see if its the auto_hold one */

if ( @over_num > 0 )
BEGIN
    select @auto_hold_over = auto_hold from ujo_overjob
    WHERE joid=@joid AND over_num = @over_num

    /* if @auto_hold_over = 10 its sez NO OVERRIDE */
    IF @@rowcount=1 AND @auto_hold_over != 10
    BEGIN
        SELECT @auto_hold = @auto_hold_over
    END
END


/* If auto_hold and its changing to ACTIVATED, make it ON_HOLD */

if ( @auto_hold = 1 AND @status = 9 )  SELECT @status = 11


/* If for some reason its in QUE_WAIT, blow it off */

if ( @curr_status = 12 )
BEGIN
    DELETE    ujo_wait_que
    WHERE    joid = @joid
END


/* If ACTIVATE and ON_ICE or  ON_HOLD, just quit */
if ( @status=9 AND (@curr_status = 7 OR @curr_status = 11) )
    return 1

/* If INACTIVATE and ON_ICE just quit */
if ( @status=8 AND (@curr_status = 7 OR @curr_status = 11) )
    return 1

/* If box job and box status is ON_ICE or ON_HOLD just quit. */
if (@isbox_job > 0 AND (@box_status =7 OR @box_status = 11))
    return 1

/* IF its a normal start, change the statuses */

if ( @run_num > 0 )
BEGIN
    if ( @status = 8 )
    /* Dont reset times OR ntry if going to INACTIVE */
    BEGIN
        UPDATE    ujo_job_status
        SET     status = @status,
            status_time = @gmt, run_num = @run_num ,
            exit_code=-656
        WHERE    joid = @joid
    END
    
    else
    BEGIN
        UPDATE    ujo_job_status
        SET     status = @status,
            status_time = @gmt, run_num = @run_num ,
            ntry = 0, exit_code=-656,
            last_start=0,last_end=0
        WHERE    joid = @joid
    END

END
ELSE
BEGIN

    /* IF its a restart, change the statuses only if they arent running.
       otherwise we could start up mutiples */

    if ( @run_num = 0 AND @curr_status != 1 AND @curr_status != 3)
                /* RUNNING, STARTING */
    BEGIN
        UPDATE    ujo_job_status
        SET     status = @status,
            status_time = @gmt, last_start=0,last_end=0,
            exit_code=-656
        WHERE    joid = @joid

    END

END

/* NOW see if it has a External status to send */

if EXISTS ( select job_name FROM ujo_req_job WHERE job_name=@job_name )
    select @has_ext = 1
else
    select @has_ext = 0


/* See if it was in the QUE_WAIT state */
if @curr_status = 12 and @status != 9
BEGIN
    if  @has_ext=1
        return 4
    else
        return 3
END

if @has_ext=1
    return 2

return 1
GO
GRANT EXECUTE ON  [dbo].[ujo_set2act] TO [ujoadmin]
GO