create proc ujo_chase_state

    @starting_delay_time int

AS

declare @nstart  int

select @nstart = max( nstart ) from ujo_chase
if (@nstart IS NULL)
begin
    set @nstart = 0
end

set @nstart = @nstart+1

/* Rolling slots */
if @nstart > 50
begin
    set @nstart = 1
    delete ujo_chase     /* Issue 12216151 - sinpr01 */
end

/* Clean it out */

delete ujo_chase where nstart = @nstart

/* Put in the ones that were in the event and are waiting */

/* RUNNING */
/* Issue 12216151 - sinpr01 add eoid */
/* 14913319 - Chase is checking Cross Platform Jobs */
    insert into ujo_chase
        (nstart, joid, eoid, job_name, job_type, status, run_machine, pid, jc_pid )
    
    select    @nstart, j.joid, e.eoid, j.job_name, j.job_type, status, e.machine, pid, jc_pid
    from    ujo_event e, ujo_job j, ujo_machine m
    where    e.joid = j.joid
    and j.machine = m.name
    and job_type in ('c', 'f')
    and m.type in ('L', 'l', 'r', 'n')
    and    que_status = 0
    and    status = 1

/* Put in the current status */

    insert into ujo_chase
        (nstart, joid, job_name, job_type, status, run_machine, pid, jc_pid )

    select    @nstart, j.joid, j.job_name, j.job_type, status, run_machine, pid, jc_pid
    from    ujo_job j, ujo_job_status s, ujo_machine m
    where    j.joid = s.joid
    and j.machine = m.name
    and    status = 1
    and    job_type in ('c','f')
  and m.type in ('L', 'l', 'r', 'n')
  and    j.joid not in
        ( select joid from ujo_chase where nstart = @nstart )

/* STARTING */
    insert ujo_chase
        (nstart, joid, job_name, job_type, status, run_machine, pid, jc_pid )
    select    @nstart, j.joid, j.job_name, j.job_type, status, run_machine, pid, jc_pid
    from    ujo_job j, ujo_job_status s, ujo_machine m
    where    j.joid = s.joid
    and j.machine = m.name
    and    status = 3
    and    status_time < @starting_delay_time
    and    job_type in ('c','f')
  and m.type in ('L', 'l', 'r', 'n')
    and    j.joid not in
           ( select joid from ujo_event where status = 3 and event_time_gmt < @starting_delay_time )
    and    j.joid not in
        ( select joid from ujo_chase where nstart = @nstart )


/* Now blow off the 1's that are waiting in the event and finished */
    
delete from ujo_chase    
where joid in
     (SELECT r.joid
      FROM ujo_chase r, ujo_event e
      WHERE r.joid = e.joid
      and    que_status = 0 /* Means its waiting to be processed */
      and    e.status in ( 4,5,6,10 ) /* Success, Failure, Terminated, Restart */
      and    nstart = @nstart)

/* Remove the rows in the chase table that are for Z/Team machines. */
delete from ujo_chase where run_machine in
(select distinct j.run_machine from ujo_job_status j, ujo_machine m where
j.run_machine = m.name and m.type in ('z', 't'))

/* Issue 12184156 - sinpr01 */
/* Remove the rows in the chase table that have blank machine names */
delete from ujo_chase where run_machine='' and nstart=@nstart

/* pass back which chase number it is... IF = 0 there's nothing ! */
if ( select top 1 1 from ujo_chase where nstart = @nstart ) > 0
begin
    select @nstart
end
else
begin
    select @nstart = 0
end

select @nstart as nstart
return -999 /*SUCCESS*/
GO
GRANT EXECUTE ON  [dbo].[ujo_chase_state] TO [ujoadmin]
GO