select
ors.run_id,
ors.component run_component,
ors.report_id,
ors.report_name,
ors.report_type,
ors.run_by,
xxen_util.client_time(ors.created_date)created_date,
xxen_util.client_time(ors.completion_time)completion_time,
xxen_util.time(ors.time_in_seconds)execution_time,
ors.output_type,
ors.error_message,
ors.rows_retrieved,
round(ors.rows_retrieved/decode(ors.time_in_seconds,0,0.25,ors.time_in_seconds),2) rows_second,
ors.process_description ,
ors.process_outputfilepath,
ors.process_output_type,
ors.process_logfilepath,
ors.process_phase,
ors.process_status,
xxen_util.client_time(ors.process_start_time)process_start_time,
xxen_util.client_time(ors.process_end_time)process_end_time,
ors.schedule_name,
xxen_util.client_time(ors.next_schedule_time)next_schedule_time,
xxen_util.client_time(ors.previous_start_time)previous_start_time,
ors.schedule_timezone,
ors.schedule_trigger_type,
ors.last_modified_by,
xxen_util.client_time(ors.last_modified_date)last_modified_date,
ors.sql_query
from
(select
ors.id run_id,
ors.component,
orv.report_id,
orv.report_name,
orv.report_type,
xxen_orb_util.orb_user(ors.run_by) run_by,
cast(ors.created_date as date) created_date,
cast(ors.run_date as date) run_date,
cast(ors.completion_time as date) completion_time,
round((cast(ors.completion_time as date) - cast(ors.created_date as date)) * 86400,2) time_in_seconds,
ors.output_type,
ors.error_message,
ors.rows_retrieved,
ors.sql_query,
op.description process_description ,
op.outputfilepath process_outputfilepath,
op.outputtype process_output_type,
op.logfilepath process_logfilepath,
op.phase process_phase,
op.status process_status,
cast(op.start_time as date) process_start_time,
cast(op.end_time as date) process_end_time,
os.name schedule_name,
cast(os.next_fire_time as date)next_schedule_time,
cast(os.prev_fire_time as date)previous_start_time,
os.time_zone schedule_timezone,
os.trigger_type schedule_trigger_type,
xxen_orb_util.orb_user(ors.last_modified_by)last_modified_by,
cast(ors.last_modified_date as date) last_modified_date
from
xxorb.orb_report_stats ors,
xxorb.orb_report_v orv,
xxorb.orb_process op,
xxorb.orb_schedule os
where orv.report_id=ors.report_id and
ors.process_id=op.id(+) and
op.schedule_id=os.id(+)
)ors
where 1=1
order by ors.run_date desc |
