create  proc ujo_chk_cond
    @job_name  varchar(64) = 'ALL',
    @mode int
AS

BEGIN
    if @job_name = 'ALL' begin
        if @mode = 1 begin
            select j.job_name Job, cond_job_name Missing_Condition_Job
            from ujo_job_cond c join ujo_job j on j.joid = c.joid
            where cond_job_name != ''
                AND type != 'g'
                AND cond_job_AUTOSERV = ''
                AND NOT EXISTS( select 1 from ujo_job where job_name = c.cond_job_name)
                AND c.over_num = -1
            return -999 /*SUCCESS*/
        end    
        else begin
            select j.job_name Job, cond_job_name Missing_Condition_Job
            from ujo_job_cond c join ujo_job j on c.joid = j.joid join ujo_job_status s on c.joid = s.joid
            where NOT EXISTS ( select j.job_name from ujo_job j where j.job_name = c.cond_job_name)
                AND cond_job_name != ''
                AND type != 'g'
                AND cond_job_AUTOSERV = '' /* No X-instance checking */
                AND c.over_num = s.over_num
            return -999 /*SUCCESS*/    
        end    
    end

    if @job_name = 'ON_ICE' begin
        if @mode = 1 begin
            select j.job_name Job, cond_job_name Condition_Job_ON_ICE
            from ujo_job j join ujo_job_cond c on j.joid = c.joid
                join ujo_job_status s on j.joid = s.joid
                join ujo_job j2 on j2.job_name = cond_job_name
            where s.status = 7  /* 7=ON_ICE */
                AND cond_job_name != ''
                AND cond_job_AUTOSERV = ''
                AND c.over_num = -1
            return -999 /*SUCCESS*/
        end    
        else begin
            select  j.job_name Job, cond_job_name Condition_Job_ON_ICE
            from ujo_job j join ujo_job_cond c on j.joid = c.joid
                join ujo_job_status s on j.joid = s.joid
                join ujo_job j2 on j2.job_name = cond_job_name
            where s.status = 7  /* 7=ON_ICE */
                AND cond_job_name != ''
                AND cond_job_AUTOSERV = ''
                AND c.over_num = s.over_num
            return -999 /*SUCCESS*/    
        end    
    end

    if @mode = 1 begin
           select  j.job_name Job, cond_job_name Missing_Condition_Job
        from ujo_job_cond c join ujo_job j on j.joid = c.joid
        where job_name = @job_name
            AND NOT EXISTS ( select 1 from ujo_job where job_name = c.cond_job_name)
            AND cond_job_name != ''
            AND type != 'g'
            AND cond_job_AUTOSERV = ''
            AND c.over_num = -1
        return -999 /*SUCCESS*/
    end        
    else begin
        select  j.job_name Job, cond_job_name Missing_Condition_Job
        from ujo_job_cond c join ujo_job j on c.joid = j.joid join ujo_job_status s on c.joid = s.joid
        where job_name = @job_name
            AND NOT EXISTS ( select j.job_name from ujo_job j where j.job_name = c.cond_job_name)
            AND cond_job_name != ''
            AND type != 'g'
            AND cond_job_AUTOSERV = ''
            AND c.over_num = s.over_num
        return -999 /*SUCCESS*/
    end

END
GO
GRANT EXECUTE ON  [dbo].[ujo_chk_cond] TO [ujoadmin]
GO