Rem Copyright (c) 2007, Oracle.  All rights reserved.  
Rem
Rem    NAME
Rem      create_wb_rv_public_audit_views.sql - create public audit view definitions
Rem
Rem    DESCRIPTION
Rem      This script is used to create public audit views
Rem      for a heterogeneous environment in an Oracle database
Rem
Rem    NOTES
Rem      Logon to the Oracle database to be used for the Audit Data Source
Rem      @<OWB-HOME>/owb/rtasst/jrtaudit/oracle/create_wb_rv_public_audit_views.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    tzrose      01/15/08 - Created to be compatible for Oracle
Rem

/* Need some info about Location such as DataSource name(s) */

create view ALL_RV_JOB_EXECUTIONS as
select
  jb.job_audit_id as job_audit_id,
  jb.job_uoid as job_uoid,
  jb.job_name as job_name,
  jb.job_type as job_type,
  jb.job_status as job_audit_status,
  (case jb.job_status when 0 then 16002
                      when 1 then 16004
		      when 2 then 16004
		      when 3 then 16004
		      else 16002 end) as job_execution_status,
  (case jb.job_status when 0 then 'BUSY'
                      when 1 then 'COMPLETE'
		      when 2 then 'COMPLETE'
		      when 3 then 'COMPLETE'
		      else 'BUSY' end) as job_execution_result,
  (case jb.job_status when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 else 0 end) as job_return_status,
  (case jb.job_status when 0 then ''
                      when 1 then 'OK'
                      when 2 then 'OK_WITH_WARNINGS'
                      when 3 then 'FAILURE'
                      else '' end) as job_return_result,
  jb.jrt_exec_key as jrt_exec_key,
  jb.start_time as start_time,
  jb.elapse_time as elapse_time,
  jb.time_zone as time_zone,
  jb.jndi_name as jndi_name,
  jb.object_id as object_id,
  jb.operation as operation,
  jb.created_by as created_by,
  jb.creation_date as created_on,
  jb.last_update_date as updated_on,
  jb.last_updated_by as updated_by
from
  wb_rt_ht_audit_job jb;
  
create view ALL_RV_PHASE_EXECUTIONS as
select
  ph.phase_audit_id as phase_audit_id,
  ph.parent_phase_audit_id as parent_phase_audit_id,
  ph.top_level_phase_audit_id as top_level_phase_audit_id,
  ph.job_audit_id as job_audit_id,
  ph.phase_uoid as phase_uoid,
  ph.phase_name as phase_name,
  ph.phase_type as phase_type,
  ph.phase_status as phase_audit_status,
  (case ph.phase_status when 0 then 16002
                        when 1 then 16004
		        when 2 then 16004
		        when 3 then 16004
		        else 16002 end) as phase_execution_status,
  (case ph.phase_status when 0 then 'BUSY'
                        when 1 then 'COMPLETE'
		        when 2 then 'COMPLETE'
		        when 3 then 'COMPLETE'
		        else 'BUSY' end) as phase_execution_result,
  (case ph.phase_status when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 else 0 end) as phase_return_status,
  (case ph.phase_status when 0 then ''
                      when 1 then 'OK'
                      when 2 then 'OK_WITH_WARNINGS'
                      when 3 then 'FAILURE'
                      else '' end) as phase_return_result,
  ph.jrt_exec_key as jrt_exec_key,
  ph.phase_errors as number_errors,
  ph.start_time as start_time,
  ph.elapse_time as elapse_time,
  ph.time_zone as time_zone,
  ph.operation as operation,
  ph.control_centre_task_name as control_centre_task_name,
  ph.control_centre_task_type as control_centre_task_type,
  ph.src_method as src_method,
  ph.created_by as created_by,
  ph.creation_date as created_on,
  ph.last_update_date as updated_on,
  ph.last_updated_by as updated_by
from
  wb_rt_ht_audit_phase ph;
  
create view ALL_RV_TASK_EXECUTIONS as
select
  tk.task_audit_id as task_audit_id,
  tk.parent_task_audit_id as parent_task_audit_id,
  tk.top_level_task_audit_id as top_level_task_audit_id,
  tk.phase_audit_id as phase_audit_id,
  tk.task_uoid as task_uoid,
  tk.task_name as task_name,
  tk.task_type as task_type,
  tk.task_desc as task_desc,
  tk.task_status as task_audit_status,
  (case tk.task_status when 0 then 16002
                       when 1 then 16004
		       when 2 then 16004
		       when 3 then 16004
		       else 16002 end) as task_execution_status,
  (case tk.task_status when 0 then 'BUSY'
                       when 1 then 'COMPLETE'
		       when 2 then 'COMPLETE'
		       when 3 then 'COMPLETE'
		       else 'BUSY' end) as task_execution_result,
  (case tk.task_status when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 else 0 end) as task_return_status,
  (case tk.task_status when 0 then ''
                      when 1 then 'OK'
                      when 2 then 'OK_WITH_WARNINGS'
                      when 3 then 'FAILURE'
                      else '' end) as task_return_result,
  tk.jrt_exec_key as jrt_exec_key,
  tk.task_errors as number_errors,
  tk.start_time as start_time,
  tk.elapse_time as elapse_time,
  tk.created_by as created_by,
  tk.creation_date as created_on,
  tk.last_update_date as updated_on,
  tk.last_updated_by as updated_by
from
  wb_rt_ht_audit_task tk;
  
create view ALL_RV_STEP_EXECUTIONS as
select
  st.step_audit_id as step_audit_id,
  st.task_audit_id as task_audit_id,
  st.step_uoid as step_uoid,
  st.step_name as step_name,
  st.step_type as step_type,
  st.step_info as step_info,
  st.step_status as step_audit_status,
  (case st.step_status when 0 then 16002
                       when 1 then 16004
		       when 2 then 16004
		       when 3 then 16004
		       else 16002 end) as step_execution_status,
  (case st.step_status when 0 then 'BUSY'
                       when 1 then 'COMPLETE'
		       when 2 then 'COMPLETE'
		       when 3 then 'COMPLETE'
		       else 'BUSY' end) as step_execution_result,
  (case st.step_status when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 else 0 end) as step_return_status,
  (case st.step_status when 0 then ''
                      when 1 then 'OK'
                      when 2 then 'OK_WITH_WARNINGS'
                      when 3 then 'FAILURE'
                      else '' end) as step_return_result,
  st.jrt_exec_key as jrt_exec_key,
  st.data_errors as number_errors,
  st.recs_select as number_records_selected,
  st.recs_insert as number_records_inserted,
  st.recs_update as number_records_updated,
  st.recs_delete as number_records_deleted,
  st.recs_merge as number_records_merged,
  st.start_time as start_time,
  st.elapse_time as elapse_time,
  st.created_by as created_by,
  st.creation_date as created_on,
  st.last_update_date as updated_on,
  st.last_updated_by as updated_by
 from
  wb_rt_ht_audit_step st;
  
create view ALL_RV_STEP_STRUCTS as
select
  str.struct_audit_id as struct_audit_id,
  str.step_audit_id as step_audit_id,
  str.struct_info as struct_info,
  str.parent_operator_uoid as parent_operator_uoid,
  str.parent_object_uoid as parent_object_uoid,
  str.parent_object_type as parent_object_type,
  str.parent_object_loc_uoid as parent_object_loc_uoid,
  str.parent_object_name as parent_object_name,
  str.object_uoid as object_uoid,
  str.object_type as object_type,
  str.object_loc_uoid as object_loc_uoid,
  str.object_name as object_name,
  str.created_by as created_by,
  str.creation_date as created_on,
  str.last_update_date as updated_on,
  str.last_updated_by as updated_by
 from
  wb_rt_ht_audit_struct str;
  
create view ALL_RV_AUDIT_RUN_ERRORS as
select
  err.error_audit_id as run_error_id,
  err.task_audit_id as task_audit_id,
  err.step_audit_id as step_audit_id,
  err.rowkey as cursor_rowkey,
  err.dest_table_name as target_name,
  err.dest_column_name as target_column,
  err.statement as statement,
  err.error_severity as error_severity,
  err.error_number as error_number,
  err.error_message as error_message,
  err.error_stack_trace as error_stack_trace,
  err.created_by as created_by,
  err.creation_date as created_on,
  err.last_update_date as updated_on,
  err.last_updated_by as updated_by  
from
  wb_rt_ht_audit_error err;

create view ALL_RV_AUDIT_RUN_ERROR_SOURCES as
select
  src.error_source_audit_id as run_error_source_id,
  src.error_audit_id as run_error_id,
  src.error_source_info as error_source_info,
  src.table_name as table_name,
  src.column_name as column_name,
  src.column_seq as column_seq,
  src.column_value as column_value,
  src.created_by as created_by,
  src.creation_date as created_on,
  src.last_update_date as updated_on,
  src.last_updated_by as updated_by 
from
  wb_rt_ht_audit_error_source src;
  
create view ALL_RV_AUDIT_EXEC_FILES as
select
  f.file_audit_id as file_audit_id,
  f.task_audit_id as task_audit_id,
  f.step_audit_id as step_audit_id,
  f.file_type as file_type,
  (case f.format_type when 0 then 'TEXT' when 1 then 'HTML' else 'OTHER' end) as format,
  f.file_text as file_text,
  f.created_by as created_by,
  f.creation_date as created_on
from
  wb_rt_ht_audit_file f;
  
create view ALL_RV_AUDIT_STEP_RUN_TRACE as
select
  tr.trace_audit_id as trace_audit_id,
  tr.step_audit_id as step_audit_id,
  tr.error_audit_id as run_error_id,
  tr.trace_info as trace_info,
  tr.table_name as table_name,
  tr.statement as statement,
  tr.value as trace_value,
  tr.created_by as created_by,
  tr.creation_date as created_on,
  tr.last_update_date as updated_on,
  tr.last_updated_by as updated_by
from
  wb_rt_ht_audit_trace tr;

create view ALL_RV_AUDIT_PARAMETERS as
select
  ap.param_audit_id as param_audit_id,
  ap.phase_audit_id as phase_audit_id,
  ap.param_name as param_name,
  ap.param_uoid as param_uoid,
  ap.param_type as param_type,
  ap.param_kind as param_kind,
  ap.param_mode as param_mode,
  ap.param_scope as param_scope,
  ap.is_required as is_required,
  ap.is_fixed as is_fixed,
  ap.value_kind as value_kind,
  ap.value_mode as value_mode,
  ap.value as value,
  ap.type_length as type_length,
  ap.type_scale as type_scale,
  ap.type_precision as type_precision,
  ap.created_by as created_by,
  ap.creation_date as created_on
from
  wb_rt_ht_audit_param ap;

  create view ALL_RV_AUDIT_MSG as
select
  mg.msg_audit_id as msg_audit_id,
  mg.phase_audit_id as phase_audit_id,
  mg.task_audit_id as task_audit_id,
  mg.step_audit_id as step_audit_id,
  mg.log_type as log_type,
  mg.log_status as log_status,
  mg.log_level as log_level,
  mg.log_msg as msg,
  mg.msg_id as msg_id,
  mg.created_by as created_by,
  mg.creation_date as created_on
from
  wb_rt_ht_audit_msg mg;