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)
and sj.eff_date = (select max(c.eff_date) from SCHEDULER_JOB as c)
and sc.eff_date = (select max(d.eff_date) from SCHEDULER_COMMAND as d)

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(c.eff_date) from SCHEDULER_JOB as c)
and sc.eff_date = (select max(d.eff_date) from SCHEDULER_COMMAND as d);
