select dep_jobs.name,
dependencies.conjunction,
CASE
WHEN dependencies.prereq_object_type = 'Job' THEN pre_jobs.name
WHEN dependencies.prereq_object_type = 'JobSuite' THEN pre_jobs.name
WHEN dependencies.prereq_object_type = 'JobSuiteMemberJob' THEN pre_jobs.name
WHEN dependencies.prereq_object_type = 'MemberJob' THEN pre_jobs.name
WHEN dependencies.prereq_object_type = 'RemoteEvent' THEN pre_remote_events.remote_object_name
WHEN dependencies.prereq_object_type = 'AgentEventMonitor' THEN pre_aems.name
WHEN dependencies.prereq_object_type = 'SapEventMonitor' THEN pre_sems.name
WHEN dependencies.prereq_object_type = 'SnmpTrapMonitor' THEN pre_snmp.name
ELSE ''
END prereq_name,
CASE
WHEN dependencies.prereq_type = 0 THEN 'Job Status'
WHEN dependencies.prereq_type = 1 THEN 'Agent Event Monitor Event'
WHEN dependencies.prereq_type = 2 THEN 'Job Monitor Event'
WHEN dependencies.prereq_type = 3 THEN 'SNMP Trap Event'
WHEN dependencies.prereq_type = 4 THEN 'Job Change'
WHEN dependencies.prereq_type = 5 THEN 'Suite Member Status'
WHEN dependencies.prereq_type = 6 THEN 'Suite Monitor Event'
WHEN dependencies.prereq_type = 7 THEN 'Suite Status'
WHEN dependencies.prereq_type = 8 THEN 'SAP Monitor Event'
WHEN dependencies.prereq_type = 9 THEN 'Suite Member Monitor'
WHEN dependencies.prereq_type = 10 THEN 'Remote Event'
ELSE ''
END prereq_subtype,
CASE
WHEN dependencies.react_to_status = 'S' THEN 'Submitted'
WHEN dependencies.react_to_status = 'H' THEN 'Condition Check'
WHEN dependencies.react_to_status = 'W' THEN 'Condition Retry Wait'
WHEN dependencies.react_to_status = 'R' THEN 'Running'
WHEN dependencies.react_to_status = 'F' THEN 'Failed'
WHEN dependencies.react_to_status = 'C' THEN 'Completed'
WHEN dependencies.react_to_status = 'A' THEN 'Canceled'
WHEN dependencies.react_to_status = 'K' THEN 'Skipped'
WHEN dependencies.react_to_status = 'D' THEN 'Ended (Complete/Cancel/Fail)'
WHEN dependencies.react_to_status = 'U' THEN 'Underrun'
WHEN dependencies.react_to_status = 'O' THEN 'Overrun'
WHEN dependencies.react_to_status = 'L' THEN 'Late Start'
WHEN dependencies.react_to_status = '*' THEN 'Occurred'
ELSE dependencies.react_to_status
END prereq_status,
dependencies.sequence_number,
dependencies.prereq_object_type prereq_type
from dependencies
join jobs dep_jobs on dependencies.dependent_job_id = dep_jobs.id
left outer join jobs pre_jobs on dependencies.prereq_object_id = pre_jobs.id
left outer join remote_events pre_remote_events on dependencies.prereq_object_id = pre_remote_events.id
left outer join agent_event_monitors pre_aems on dependencies.prereq_object_id = pre_aems.id
left outer join snmp_trap_monitors pre_snmp on dependencies.prereq_object_id = pre_snmp.id
left outer join sap_event_monitors pre_sems on dependencies.prereq_object_id = pre_sems.id
order by dep_jobs.name, dependencies.sequence_number
