Tag: Oracle Clinical

SQL for listing Scheduled BVS jobs in Oracle Clinical.

BVS = Batch Validation Session.

select 
 batch_job_id,
 module_name,
 m.description, 
 user_name,
 study, 
 execution_status,
 to_char(submission_ts,'DAY') submission_day,
 to_char(submission_ts,'YYYY-MM-DD HH24:MI:SS') submission_ts,
 to_char(entered_ts,'YYYY-MM-DD HH24:MI:SS') entered_ts,
 to_char(started_ts,'YYYY-MM-DD HH24:MI:SS') started_cet, 
 to_char(completion_ts,'YYYY-MM-DD HH24:MI:SS') completed_cet,
 round((completion_ts-started_ts)*24,2) dura_hours,
 round((completion_ts-started_ts)*1440,0) dura_min,
 round((completion_ts-started_ts)*86400,0) dura_sec, 
 output_file_name,
 log_file_name,
 failure_text,
 bj.schedule_server,
 bj.schedule_string
from batch_jobs bj, modules m
where bj.module_name = m.name
 and module_name = 'RXCBVBVS' 
 and bj.execution_status = 'SCHEDULED' 
order by 8 desc;

Also, see table BATCH_DM_RUNS for info on BVS runs including success flag, modified patient count and data currency flag (CURRENT =Y for production data).

select * from batch_dm_runs
order by creation_ts desc;