select sc.control_id, 

df.djs_path, 

sc.job_id, 

sj.short_desc, 

sj.email, 

sj.server_run_app, 

df.eff_date as FS_EFF_DATE, 

sj.eff_date as SJ_EFF_DATE, 

1 as ON_FS,

NULL as ON_SCHED



from DJS_FILE as df

left outer join SCHEDULER_COMMAND as sc

on df.djs_path like sc.djs_path

left outer join SCHEDULER_JOB as sj

on sj.job_id = sc.job_id

where df.djs_path like '%MGM%'

and df.eff_date = (select max(a.eff_date)

                    from DJS_FILE as a 

                    where a.djs_path like df.djs_path)

union

select sc.control_id, 

sc.djs_path, sc.job_id, 

sj.short_desc, 

sj.email, 

sj.server_run_app, 

NULL as FS_EFF_DATE, 

sj.eff_date as SJ_EFF_DATE, 

NULL AS ON_FS,

1 AS ON_SCHED



from SCHEDULER_COMMAND as sc

join SCHEDULER_JOB as sj

on sc.job_id = sj.job_id

where sc.control_id = 'MGM'

and not exists (select 1 from djs_file as df

                where df.djs_path like sc.djs_path)

and sj.eff_date = (select max(e.eff_date) 

                    from SCHEDULER_JOB as e

        			where e.job_id = sj.job_id)

and sc.eff_date = (select max(f.eff_date) 

					from SCHEDULER_COMMAND as f

					where f.djs_path = sc.djs_path);
