Rem
Rem Copyright (c) 2000, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem NAME
Rem   execute_pv.sql
Rem FUNCTION
Rem   Create Public Views specific to the Execution of Processes/Activities
Rem   in the OWB Runtime Platform.
Rem NOTES
Rem   - This file can be tested using SQL*Plus.
Rem   - OWB developers must make sure this file is also runnable
Rem     using Runtime assistant. 
Rem
Rem MODIFIED
Rem    lmogilev   03/18/08 - 
Rem    tzrose     01/16/08 - 
Rem    mglewis    10/30/07 - 
Rem    tzrose     05/12/07 - 
Rem    bzmaher    12/27/06 - XbranchMerge bzmaher_merge_oslo_to_main from
Rem                          main_oslo
Rem    tzrose     10/13/05 - 
Rem    tzrose     10/04/05 - 
Rem    tzrose     07/18/05 - 
Rem    tzrose     06/09/05 - 
Rem  G. Watters       05/11/2001    Started
Rem  T. Rose          16/11/2001    Updated
Rem  T. Rose          29/01/2002    Upgraded for new table definitions
Rem  T. Rose          18/09/2002    Renamed RAB_RT_... (for RAB-specific views)
Rem                                 and ALL_RT_... (for Public Views)
Rem  T. Rose          Dec 2003      Add/update views for wb_rt_audit_struct and logical_errors
Rem

create or replace view RAB_RT_PLATFORM_PROPERTIES as
select property_path as path,
       property_value as value
from wb_rt_platform_properties
;

create or replace view RAB_RT_SOURCE_TARGET as
select        'S' as code, 'SOURCE' as symbol from dual
union select  'T' as code, 'TARGET' as symbol from dual
;

create or replace view RAB_RT_EXEC_PARAM_NAMES as
select distinct parameter_name as name,
                upper(replace(replace(parameter_name,' ','_'),'/','_')) as symbol
from wb_rt_def_system_parameters
;

create or replace view RAB_RT_EXEC_PARAM_VALUES as
      select wb_rt_constants.PARAM_TYPE_BOOLEAN as type, '0' as code,
             'No' as name, 'NO' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_BOOLEAN as type, '1' as code,
             'Yes' as name, 'YES' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_AUDIT_LEVEL as type, '0' as code,
             'None' as name, 'NONE' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_AUDIT_LEVEL as type, '1' as code,
             'Statistics' as name, 'STATISTICS' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_AUDIT_LEVEL as type, '2' as code,
             'Error Details' as name, 'ERROR_DETAILS' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_AUDIT_LEVEL as type, '3' as code,
             'Complete' as name, 'COMPLETE' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_OPERATING_MODE as type, '0' as code,
             'Set based' as name, 'SET_BASED' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_OPERATING_MODE as type, '1' as code,
             'Row Based' as name, 'ROW_BASED' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_OPERATING_MODE as type, '2' as code,
             'Row Based (Target Only)' as name, 'ROW_BASED_TARGET_ONLY' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_OPERATING_MODE as type, '3' as code,
             'Set Based Fail Over to Row Based' as name,
             'SET_BASED_FAIL_OVER_TO_ROW_BASED' as symbol from dual
union select wb_rt_constants.PARAM_TYPE_OPERATING_MODE as type, '4' as code,
             'Set Based Fail Over to Row Based (Target Only)' as name,
             'SET_BASED_FAIL_OVER_TO_ROW_BASED_TARGET_ONLY' as symbol from dual
;

create or replace view RAB_RT_EXEC_PROC_STATUS as
select        wb_rt_constants.EXECUTION_STATUS_BUSY as status,
              wb_rt_constants.to_string(wb_rt_constants.EXECUTION_STATUS_BUSY) as symbol,
              'true' as purge_disabled, 'false' as abort_disabled, 'false' as complete_disabled,
              'OraDataText' as style from dual
union select  wb_rt_constants.EXECUTION_STATUS_COMPLETE as status,
              wb_rt_constants.to_string(wb_rt_constants.EXECUTION_STATUS_COMPLETE) as symbol,
              'false' as purge_disabled, 'true' as abort_disabled, 'true' as complete_disabled,
              null as style from dual
union select  wb_rt_constants.EXECUTION_STATUS_INACTIVE as status,
              wb_rt_constants.to_string(wb_rt_constants.EXECUTION_STATUS_INACTIVE) as symbol,
              'false' as purge_disabled, 'true' as abort_disabled, 'true' as complete_disabled,
              null as style from dual
union select  wb_rt_constants.EXECUTION_STATUS_READY as status,
              wb_rt_constants.to_string(wb_rt_constants.EXECUTION_STATUS_READY) as symbol,
              'true' as purge_disabled, 'false' as abort_disabled, 'true' as complete_disabled,
              'OraDataText' as style from dual
;

create or replace view RAB_RT_EXEC_PROC_RESULTS as
select wb_rt_constants.RESULT_NUMBER_SUCCESS as result_number,
       wb_rt_constants.RESULT_SUCCESS as result_symbol,
       to_char(wb_rt_constants.EXECUTION_STATUS_COMPLETE) || ':' ||
       to_char(wb_rt_constants.RESULT_NUMBER_SUCCESS) as complete_result,
       wb_rt_constants.to_string(wb_rt_constants.EXECUTION_STATUS_COMPLETE) || '_' ||
       wb_rt_constants.RESULT_SUCCESS as complete_symbol from dual
union
select wb_rt_constants.RESULT_NUMBER_WARNING as result_number,
       wb_rt_constants.RESULT_WARNING as result_symbol,
       to_char(wb_rt_constants.EXECUTION_STATUS_COMPLETE) || ':' ||
       to_char(wb_rt_constants.RESULT_NUMBER_WARNING) as complete_result,
       wb_rt_constants.to_string(wb_rt_constants.EXECUTION_STATUS_COMPLETE) || '_' ||
       wb_rt_constants.RESULT_WARNING as complete_symbol from dual
union
select wb_rt_constants.RESULT_NUMBER_FAILURE as result_number,
       wb_rt_constants.RESULT_FAILURE as result_symbol,
       to_char(wb_rt_constants.EXECUTION_STATUS_COMPLETE) || ':' ||
       to_char(wb_rt_constants.RESULT_NUMBER_FAILURE) as complete_result,
       wb_rt_constants.to_string(wb_rt_constants.EXECUTION_STATUS_COMPLETE) || '_' ||
       wb_rt_constants.RESULT_FAILURE as complete_symbol from dual
;

create or replace view RAB_RT_EXEC_MAP_STATUS as
select        1 as status, 'COMPLETE' as symbol, 'false' as purge_disabled, null as style from dual
union select  2 as status, 'FAILURE' as symbol, 'false' as purge_disabled, 'OraErrorText' as style from dual
union select  0 as status, 'RUNNING' as symbol, 'true' as purge_disabled, 'OraDataText' as style from dual
;

create or replace view RAB_RT_EXEC_ACT_RESULTS as
select r.return_result_number as result_number,
       r.return_result as result_value,
       r.result_category as result_category,
       decode(r.result_category, wb_rt_constants.RESULT_CATEGORY_SUCCESS, wb_rt_constants.RESULT_NUMBER_SUCCESS,
                                 wb_rt_constants.RESULT_CATEGORY_WARNING, wb_rt_constants.RESULT_NUMBER_WARNING,
                                 wb_rt_constants.RESULT_CATEGORY_FAILURE, wb_rt_constants.RESULT_NUMBER_FAILURE,
                                 wb_rt_constants.RESULT_NUMBER_SUCCESS) as result_code,
       e.audit_execution_id as audit_execution_id
from wb_rt_def_operator_results r, wb_rt_audit_executions e
where r.execution_operator_id = e.execution_operator_id
;

create or replace view RAB_RT_EXEC_SEVERITY as
select        'ERROR' as symbol, 'images/error.gif' as icon from dual
;

create or replace view RAB_RT_ERROR_TABLE_SEVERITY as
select '1' as severity_code, 'FATAL' as symbol, 'images/Failed.gif' as icon from dual
union
select '2' as severity_code, 'ERROR' as symbol, 'images/error.gif' as icon from dual
union
select '3' as severity_code, 'WARNING' as symbol, 'images/warning.gif' as icon from dual
union
select '4' as severity_code, 'INFO' as symbol, 'images/info.gif' as icon from dual
;

create or replace view RAB_RT_ERROR_TABLE_RULES as
select 1 as rule_id, 'orphan level record' as Type, 'RULE_ORPHAN_LEVEL_RECORD' as symbol from dual
union
select 2 as rule_id, 'attribute value' as Type, 'RULE_ATTRIBUTE_VALUE' as symbol from dual
union
select 3 as rule_id, 'functional dependency' as Type, 'RULE_FUNCTIONAL_DEPENDENCY' as symbol from dual
union
select 4 as rule_id, 'unique key' as Type, 'RULE_UNIQUE_KEY' as symbol from dual
union
select 5 as rule_id, 'referential' as Type, 'RULE_REFERENTIAL' as symbol from dual
union
select 6 as rule_id, 'domain list' as Type, 'RULE_DOMAIN_LIST' as symbol from dual
union
select 7 as rule_id, 'domain pattern list' as Type, 'RULE_DOMAIN_PATTERN_LIST' as symbol from dual
union
select 8 as rule_id, 'domain range' as Type, 'RULE_DOMAIN_RANGE' as symbol from dual
union
select 9 as rule_id, 'domain common format' as Type, 'RULE_DOMAIN_COMMON_FORMAT' as symbol from dual
union
select 10 as rule_id, 'not null' as Type, 'RULE_NOT_NULL' as symbol from dual
;

create or replace view RAB_RT_EXTERNAL_URLS as
select        'OEM' as ext_product, 'SCHEDULER_JOBS' as ext_target, 'OEM_SCHEDULER_JOBS' as url_symbol,
              'RTProcess' as report_base, '**' as param_delimiter,
              'Oracle Database' as type_name_check, '10' as type_version_check,
              '**OEMWebServerBase**/em/console/database/instance/schr/jobs' as preferred_path,
              'http://**Host**:5500/em/console/database/instance/schr/jobs' as alternative_path from dual
union
select        'OWF' as ext_product, 'MONITOR' as ext_target, 'OWF_MONITOR' as url_symbol,
              'RTProcess' as report_base, '**' as param_delimiter,
              'Oracle Workflow' as type_name_check, '0' as type_version_check,
              '**OWFWebServerBase**' as preferred_path,
              null as alternative_path from dual
;

create or replace view RAB_RT_EXCEPTION_CHECKS as
select        -1 as err_number, 'ORA-00001' as err_code, 'constraint_checking' as rab_context,
              'ORA-00001: unique constraint (string.string) violated' as err_desc from dual
union
select        -942 as err_number, 'ORA-00942' as err_code, 'login_validation' as rab_context,
              'ORA-00942: table or view does not exist' as err_desc from dual
union
select        -942 as err_number, 'ORA-00942' as err_code, 'shadow_table' as rab_context,
              'ORA-00942: table or view does not exist' as err_desc from dual
union
select        -1017 as err_number, 'ORA-01017' as err_code, 'login_validation' as rab_context,
              'ORA-01017: invalid username/password; logon denied' as err_desc from dual
union
select        -1017 as err_number, 'ORA-01017' as err_code, 'store_validation' as rab_context,
              'ORA-01017: invalid username/password; logon denied' as err_desc from dual
union
select        -1031 as err_number, 'ORA-01031' as err_code, 'shadow_table' as rab_context,
              'ORA-01031: insufficient privileges' as err_desc from dual
union
select        -2291 as err_number, 'ORA-02291' as err_code, 'constraint_checking' as rab_context,
              'ORA-02291: integrity constraint (string.string) violated - parent key not found' as err_desc from dual
union
select        -20001 as err_number, 'RTV-20001' as err_code, 'constraint_checking' as rab_context,
              'RTV-20001: integrity constraint {0} violated - parent key not found.' as err_desc from dual
union
select        -20002 as err_number, 'RTV-20002' as err_code, 'constraint_checking' as rab_context,
              'RTV-20002: Cannot validate {0} - parent keys not found.' as err_desc from dual
union
select        -22285 as err_number, 'ORA-22285' as err_code, 'store_validation' as rab_context,
              'ORA-22285: non-existent directory or file for string operation' as err_desc from dual
union
select        -28000 as err_number, 'ORA-28000' as err_code, 'login_validation' as rab_context,
              'ORA-28000: the account is locked' as err_desc from dual
union
select        -28000 as err_number, 'ORA-28000' as err_code, 'store_validation' as rab_context,
              'ORA-28000: the account is locked' as err_desc from dual
union
select        -28001 as err_number, 'ORA-28001' as err_code, 'login_validation' as rab_context,
              'ORA-28001: the password has expired' as err_desc from dual
union
select        -28001 as err_number, 'ORA-28001' as err_code, 'store_validation' as rab_context,
              'ORA-28001: the password has expired' as err_desc from dual
;

-- Public View Version set to 11.2 (11.1 for Tokyo, 10.1 for Paris)
create or replace view RAB_RT_INSTALLATIONS as
select
a.installation_id,
a.installation_name,
a.description,
a.installed_version,
a.release,
a.public_view_version,
decode(v.uv_count, 0, a.schema_type, 'HRAB') as schema_type,
a.updated_on,
a.created_on
from all_rt_installations a,
     (select count(*) as uv_count from user_views where view_name = 'HAB_RT_INSTALLATIONS') v
with read only;

create or replace view RAB_RT_EXEC_PROC_HIER as
select
e.audit_execution_id as audit_execution_id,
e.parent_audit_execution_id as parent_audit_execution_id,
e.top_level_audit_execution_id as top_level_audit_execution_id
from wb_rt_audit_executions e
with read only;

create or replace view RAB_RT_EXEC_PROCESS as
select
e.execution_object_uoid as process_uoid,
ot.object_type_name as process_type,
rt.symbol as process_type_symbol,
e.execution_object_name as process_name,
decode(e.execution_name, null, e.task_object_name, e.execution_name) as execution_name,
e.audit_execution_id as audit_execution_id,
e.scope_audit_execution_id as scope_audit_execution_id,
e.execution_object_store_name as tool_name,
st.store_type_version as tool_version,
e.execution_object_store_uoid as execution_store_uoid,
e.execution_object_store_name as execution_store_name,
e.return_code as result_code,
e.return_result_number as result_number,
e.audit_status as run_status,
e.audit_status || ':' || e.return_result_number as run_status_filter,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
e.external_audit_id as external_audit_id,
e.request_audit_id as request_audit_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.created_by as created_by,
e.last_update_date as updated_date,
e.last_updated_by as updated_by
from wb_rt_audit_executions e,
     wb_rt_def_object_types ot,
     wb_rt_def_object_types tt,
     wb_rt_def_store_types st,
     rab_rt_rep_types rt,
     (select qe.task_object_uoid, qt.object_type_name, qe.task_object_store_uoid,
             max(audit_execution_id) as max_exec_id
       from wb_rt_audit_executions qe, wb_rt_def_object_types qt
      where qe.task_object_type_id = qt.object_type_id
        and qe.parent_audit_execution_id is null
      group by qe.task_object_uoid, qt.object_type_name, qe.task_object_store_uoid) mx
where 
  e.parent_audit_execution_id is null and
  e.execution_object_type_id = ot.object_type_id and
  e.task_object_type_id = tt.object_type_id and
  rt.obj_type (+) = ot.object_type_name and
  mx.task_object_uoid = e.task_object_uoid and
  mx.object_type_name = tt.object_type_name and
  mx.task_object_store_uoid = e.task_object_store_uoid and
  e.audit_execution_id = mx.max_exec_id and
  e.execution_object_store_type_id = st.store_type_id (+)
with read only;  

create or replace view RAB_RT_EXEC_PROCESS_RUNS as
select
e.execution_object_uoid as process_uoid,
decode(ot.object_type_name,
       null, decode(op.operator_name, null, 'TaskGroup', op.operator_name),
       ot.object_type_name) as process_type,
decode(ot.object_type_name,
       null, decode(op.operator_name, null, 'TASKGROUP', rt2.symbol),
       rt.symbol) as process_type_symbol,
e.execution_object_name as process_name,
decode(e.execution_name, null, e.task_object_name, e.execution_name) as execution_name,
e.audit_execution_id as audit_execution_id,
e.parent_audit_execution_id as parent_audit_execution_id,
e.top_level_audit_execution_id as top_level_audit_execution_id,
e.scope_audit_execution_id as scope_audit_execution_id,
e.execution_object_store_name as tool_name,
st.store_type_version as tool_version,
decode(e.execution_object_store_uoid, null, t.execution_object_store_uoid, e.execution_object_store_uoid) as execution_store_uoid,
decode(e.execution_object_store_uoid, null, t.execution_object_store_name, e.execution_object_store_name) as execution_store_name,
e.return_code as result_code,
e.return_result_number as result_number,
e.audit_status as run_status,
e.audit_status || ':' || e.return_result_number as run_status_filter,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
e.external_audit_id as external_audit_id,
e.request_audit_id as request_audit_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.created_by as created_by,
e.last_update_date as updated_date,
e.last_updated_by as updated_by
from wb_rt_audit_executions e, wb_rt_audit_executions t,
     wb_rt_def_object_types ot, wb_rt_def_execution_operators op,
     rab_rt_rep_types rt, rab_rt_rep_types rt2,
     wb_rt_def_store_types st
where
  e.top_level_audit_execution_id = t.audit_execution_id and
  e.execution_object_type_id = ot.object_type_id (+) and
  rt.obj_type (+) = ot.object_type_name and
  e.execution_operator_id = op.execution_operator_id (+) and
  rt2.obj_type (+) = op.operator_name and
  e.execution_object_store_type_id = st.store_type_id(+)
with read only;

create or replace view RAB_RT_EXEC_PROC_RUN_COUNTS as
select
e.execution_object_uoid as process_uoid,
decode(ot.object_type_name,
       null, decode(op.operator_name, null, 'TaskGroup', op.operator_name),
       ot.object_type_name) as process_type,
decode(ot.object_type_name,
       null, decode(op.operator_name, null, 'TASKGROUP', rt2.symbol),
       rt.symbol) as process_type_symbol,
e.execution_object_name as process_name,
decode(e.execution_name, null, e.task_object_name, e.execution_name) as execution_name,
e.audit_execution_id as audit_execution_id,
e.parent_audit_execution_id as parent_audit_execution_id,
e.top_level_audit_execution_id as top_level_audit_execution_id,
e.scope_audit_execution_id as scope_audit_execution_id,
e.execution_object_store_name as tool_name,
st.store_type_version as tool_version,
decode(e.execution_object_store_uoid, null, t.execution_object_store_uoid, e.execution_object_store_uoid) as execution_store_uoid,
decode(e.execution_object_store_uoid, null, t.execution_object_store_name, e.execution_object_store_name) as execution_store_name,
e.return_code as result_code,
e.return_result_number as result_number,
e.audit_status as run_status,
e.audit_status || ':' || e.return_result_number as run_status_filter,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
e.external_audit_id as external_audit_id,
e.request_audit_id as request_audit_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.created_by as created_by,
e.last_update_date as updated_date,
e.last_updated_by as updated_by,
nvl(x.err,0) as number_errors,
nvl(x.ler,0) as number_logical_errors
from wb_rt_audit_executions e, wb_rt_audit_executions t,
     wb_rt_def_object_types ot, wb_rt_def_execution_operators op,
     rab_rt_rep_types rt, rab_rt_rep_types rt2,
     wb_rt_def_store_types st,     
     (select e.audit_execution_id as exec_id,
             decode(sum(a.rta_errors), null, 0, sum(a.rta_errors)) as err,
             decode(sum(a.rta_logical_errors), null, 0, sum(a.rta_logical_errors)) as ler
      from wb_rt_audit_executions e, wb_rt_audit a
      where e.audit_execution_id = a.rte_id
      group by e.audit_execution_id) x
where
  e.top_level_audit_execution_id = t.audit_execution_id and
  e.audit_execution_id = x.exec_id (+) and
  e.execution_object_type_id = ot.object_type_id (+) and
  rt.obj_type (+) = ot.object_type_name and
  e.execution_operator_id = op.execution_operator_id (+) and
  rt2.obj_type (+) = op.operator_name and
  e.execution_object_store_type_id = st.store_type_id(+)
with read only;

create or replace view RAB_RT_EXEC_PROC_RUN_METRICS as
select
e.execution_object_uoid as process_uoid,
decode(ot.object_type_name,
       null, decode(op.operator_name, null, 'TaskGroup', op.operator_name),
       ot.object_type_name) as process_type,
decode(ot.object_type_name,
       null, decode(op.operator_name, null, 'TASKGROUP', rt2.symbol),
       rt.symbol) as process_type_symbol,
e.execution_object_name as process_name,
decode(e.execution_name, null, e.task_object_name, e.execution_name) as execution_name,
e.audit_execution_id as audit_execution_id,
e.parent_audit_execution_id as parent_audit_execution_id,
e.top_level_audit_execution_id as top_level_audit_execution_id,
e.scope_audit_execution_id as scope_audit_execution_id,
e.execution_object_store_name as tool_name,
st.store_type_version as tool_version,
decode(e.execution_object_store_uoid, null, t.execution_object_store_uoid, e.execution_object_store_uoid) as execution_store_uoid,
decode(e.execution_object_store_uoid, null, t.execution_object_store_name, e.execution_object_store_name) as execution_store_name,
e.return_code as result_code,
e.return_result_number as result_number,
e.audit_status as run_status,
e.audit_status || ':' || e.return_result_number as run_status_filter,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
e.external_audit_id as external_audit_id,
e.request_audit_id as request_audit_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.created_by as created_by,
e.last_update_date as updated_date,
e.last_updated_by as updated_by,
decode(x.approx,null,0,x.approx) as number_approx,
nvl(x.err,0) as number_errors,
nvl(x.ler,0) as number_logical_errors,
x.sel as number_records_selected,
x.ins as number_records_inserted,
x.upd as number_records_updated,
x.del as number_records_deleted,
x.dis as number_records_discarded,
x.mer as number_records_merged,
x.cor as number_records_corrected
from wb_rt_audit_executions e, wb_rt_audit_executions t,
     wb_rt_def_object_types ot, wb_rt_def_execution_operators op,
     rab_rt_rep_types rt, rab_rt_rep_types rt2,
     wb_rt_def_store_types st,     
     (select e.audit_execution_id as exec_id,
             sum(instr(a.rta_info, '<TARGET TYPE="AW"/>')) as approx,
             decode(sum(a.rta_errors), null, 0, sum(a.rta_errors)) as err,
             decode(sum(a.rta_logical_errors), null, 0, sum(a.rta_logical_errors)) as ler,
             sum(a.rta_select) as sel,
             sum(a.rta_insert) as ins,
             sum(a.rta_update) as upd,
             sum(a.rta_delete) as del,
             sum(a.rta_discarded) as dis,
             sum(a.rta_merge) as mer,
             sum(a.rta_corrections) as cor
      from wb_rt_audit_executions e, wb_rt_audit a
      where e.audit_execution_id = a.rte_id
      group by e.audit_execution_id) x
where
  e.top_level_audit_execution_id = t.audit_execution_id and
  e.audit_execution_id = x.exec_id (+) and
  e.execution_object_type_id = ot.object_type_id (+) and
  rt.obj_type (+) = ot.object_type_name and
  e.execution_operator_id = op.execution_operator_id (+) and
  rt2.obj_type (+) = op.operator_name and
  e.execution_object_store_type_id = st.store_type_id(+)
with read only;

create or replace view RAB_RT_EXEC_PROC_RUN_PARAMS as
select
e.audit_execution_id as audit_execution_id,
e.execution_object_uoid as process_uoid,
p.parameter_name as parameter_name,
pn.symbol as parameter_name_symbol,
p.parameter_type as parameter_type,
wb_rt_constants.to_string(p.parameter_type) as parameter_type_symbol,
p.parameter_kind as parameter_kind,
wb_rt_constants.to_string(p.parameter_kind) as parameter_kind_symbol,
p.parameter_mode as parameter_mode,
wb_rt_constants.to_string(p.parameter_mode) as parameter_mode_symbol,
p.parameter_scope as parameter_scope,
wb_rt_constants.to_string(p.parameter_scope) as parameter_scope_symbol,
p.type_length as type_length,
p.type_scale as type_scale,
p.type_precision as type_precision,
p.value_kind as parameter_value_kind,
wb_rt_constants.to_string(p.value_kind) as parameter_value_kind_symbol,
p.value as parameter_value,
p.clob_value as parameter_clob_value,
decode(p.value_mode, wb_rt_constants.PARAM_VALUE_OUTPUT, 'true', 'false') as is_output_value
from wb_rt_audit_executions e, wb_rt_audit_parameters p,
rab_rt_exec_param_names pn
where
p.audit_execution_id = e.audit_execution_id and
pn.name (+) = p.parameter_name and
p.parameter_scope = wb_rt_constants.PARAM_SCOPE_PARAM
with read only;

create or replace view RAB_RT_EXEC_ACTIVITY as
select
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
a.rta_iid as map_run_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.created_by as created_by,
e.last_update_date as updated_date,
e.last_updated_by as updated_by,
a.rta_status as run_status,
st.symbol as run_status_symbol,
decode(a.rta_errors, null, 0, a.rta_errors) as number_errors, 
decode(a.rta_logical_errors, null, 0, a.rta_logical_errors) as number_logical_errors 
from wb_rt_audit a, rab_rt_exec_map_status st, wb_rt_audit_executions e
where a.rta_iid in (select max(rta_iid) from wb_rt_audit where (rta_lob_uoid = a.rta_lob_uoid)
                                                         or (rta_lob_uoid is null and a.rta_lob_uoid is null))
and st.status (+) = a.rta_status and e.audit_execution_id = a.rte_id
with read only;

create or replace view RAB_RT_EXEC_ACTIVITY_RUNS as
select
a.rte_id as audit_execution_id,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
a.rta_iid as map_run_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.created_by as created_by,
e.last_update_date as updated_date,
e.last_updated_by as updated_by,
a.rta_physical_name as physical_name,
a.rta_status as run_status,
st.symbol as run_status_symbol,
a.rta_info as info,
a.rta_load_date as load_date,
a.rta_load_time as load_time,
a.rta_fks_ok as fks_ok,
null as mapping_mode,
decode(a.rta_errors, null, 0, a.rta_errors) as number_errors,
decode(a.rta_logical_errors, null, 0, a.rta_logical_errors) as number_logical_errors,
a.rta_select as number_records_selected,
a.rta_insert as number_records_inserted,
a.rta_update as number_records_updated,
a.rta_delete as number_records_deleted,
a.rta_discarded as number_records_discarded,
a.rta_merge as number_records_merged,
a.rta_corrections as number_records_corrected
from wb_rt_audit a, rab_rt_exec_map_status st, wb_rt_audit_executions e
where st.status (+) = a.rta_status and e.audit_execution_id = a.rte_id
with read only;

create or replace view RAB_RT_EXEC_ACT_RUN_PARAMS as
select
a.rte_id as audit_execution_id,
a.rta_lob_uoid as map_uoid,
p.parameter_name as parameter_name,
pn.symbol as parameter_name_symbol,
p.parameter_type as parameter_type,
wb_rt_constants.to_string(p.parameter_type) as parameter_type_symbol,
p.parameter_kind as parameter_kind,
wb_rt_constants.to_string(p.parameter_kind) as parameter_kind_symbol,
p.parameter_mode as parameter_mode,
wb_rt_constants.to_string(p.parameter_mode) as parameter_mode_symbol,
p.parameter_scope as parameter_scope,
wb_rt_constants.to_string(p.parameter_scope) as parameter_scope_symbol,
p.type_length as type_length,
p.type_scale as type_scale,
p.type_precision as type_precision,
p.value_kind as parameter_value_kind,
wb_rt_constants.to_string(p.value_kind) as parameter_value_kind_symbol,
p.value as parameter_value,
p.clob_value as parameter_clob_value,
decode(p.value_mode, wb_rt_constants.PARAM_VALUE_OUTPUT, 'true', 'false') as is_output_value
from wb_rt_audit a, wb_rt_audit_parameters p,
rab_rt_exec_param_names pn
where
p.audit_execution_id=a.rte_id and
pn.name (+) = p.parameter_name and
p.parameter_scope = wb_rt_constants.PARAM_SCOPE_PARAM
with read only;

create or replace view RAB_RT_EXEC_ACT_RUN_SOURCES as
select
a.rte_id as audit_execution_id,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
a.rta_iid as map_run_id,
decode(s.yy,'*',null,s.yy) as source_uoid,
decode(instr(s.xx,'@'),0,s.xx,substr(s.xx,1,instr(s.xx,'@')-1)) as source_name,
null as source_type,
null as source_type_symbol,
decode(instr(s.xx,'@'),0,null,substr(s.xx,instr(s.xx,'@')+1)) as source_dblink
from wb_rt_audit a,
(select rta_iid as id,
        replace(substr(rta_primary_source,1,decode(instr(rta_primary_source,','),0,
                length(rta_primary_source),instr(rta_primary_source,',')-1)),'"') as xx,
        decode(rta_primary_source_uoid,null,null,
               replace(substr(rta_primary_source_uoid,1,
                       decode(instr(rta_primary_source_uoid,','),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',')-1)),' ')) as yy
 from wb_rt_audit
 where rta_primary_source is not null
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',')+1,
                decode(instr(rta_primary_source,',',1,2),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,2)-1) - instr(rta_primary_source,',')),'"') as xx,
        decode(instr(rta_primary_source_uoid,','),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',')+1,
                       decode(instr(rta_primary_source_uoid,',',1,2),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,2)-1) - instr(rta_primary_source_uoid,',')),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',') != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,2)+1,
                decode(instr(rta_primary_source,',',1,3),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,3)-1) - instr(rta_primary_source,',',1,2)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,2),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,2)+1,
                       decode(instr(rta_primary_source_uoid,',',1,3),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,3)-1) - instr(rta_primary_source_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,2) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,3)+1,
                decode(instr(rta_primary_source,',',1,4),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,4)-1) - instr(rta_primary_source,',',1,3)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,3),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,3)+1,
                       decode(instr(rta_primary_source_uoid,',',1,4),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,4)-1) - instr(rta_primary_source_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,3) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,4)+1,
                decode(instr(rta_primary_source,',',1,5),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,5)-1) - instr(rta_primary_source,',',1,4)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,4),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,4)+1,
                       decode(instr(rta_primary_source_uoid,',',1,5),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,5)-1) - instr(rta_primary_source_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,4) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,5)+1,
                decode(instr(rta_primary_source,',',1,6),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,6)-1) - instr(rta_primary_source,',',1,5)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,5),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,5)+1,
                       decode(instr(rta_primary_source_uoid,',',1,6),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,6)-1) - instr(rta_primary_source_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,5) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,6)+1,
                decode(instr(rta_primary_source,',',1,7),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,7)-1) - instr(rta_primary_source,',',1,6)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,6),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,6)+1,
                       decode(instr(rta_primary_source_uoid,',',1,7),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,7)-1) - instr(rta_primary_source_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,6) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,7)+1,
                decode(instr(rta_primary_source,',',1,8),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,8)-1) - instr(rta_primary_source,',',1,7)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,7),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,7)+1,
                       decode(instr(rta_primary_source_uoid,',',1,8),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,8)-1) - instr(rta_primary_source_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,7) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,8)+1,
                decode(instr(rta_primary_source,',',1,9),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,9)-1) - instr(rta_primary_source,',',1,8)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,8),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,8)+1,
                       decode(instr(rta_primary_source_uoid,',',1,9),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,9)-1) - instr(rta_primary_source_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,8) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,9)+1,
                decode(instr(rta_primary_source,',',1,10),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,10)-1) - instr(rta_primary_source,',',1,9)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,9),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,9)+1,
                       decode(instr(rta_primary_source_uoid,',',1,10),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,10)-1) - instr(rta_primary_source_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,9) != 0
) s
where s.id = a.rta_iid
with read only;

create or replace view RAB_RT_EXEC_ACT_RUN_TARGETS as
select
a.rte_id as audit_execution_id,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
a.rta_iid as map_run_id,
decode(t.yy,'*',null,t.yy) as target_uoid,
decode(instr(t.xx,'@'),0,t.xx,substr(t.xx,1,instr(t.xx,'@')-1)) as target_name,
null as target_type,
null as target_type_symbol,
decode(instr(t.xx,'@'),0,null,substr(t.xx,instr(t.xx,'@')+1)) as target_dblink
from wb_rt_audit a,
(select rta_iid as id,
        replace(substr(rta_primary_target,1,decode(instr(rta_primary_target,','),0,
                length(rta_primary_target),instr(rta_primary_target,',')-1)),'"') as xx,
        decode(rta_primary_target_uoid,null,null,
               replace(substr(rta_primary_target_uoid,1,
                       decode(instr(rta_primary_target_uoid,','),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',')-1)),' ')) as yy
 from wb_rt_audit
 where rta_primary_target is not null
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',')+1,
                decode(instr(rta_primary_target,',',1,2),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,2)-1) - instr(rta_primary_target,',')),'"') as xx,
        decode(instr(rta_primary_target_uoid,','),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',')+1,
                       decode(instr(rta_primary_target_uoid,',',1,2),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,2)-1) - instr(rta_primary_target_uoid,',')),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',') != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,2)+1,
                decode(instr(rta_primary_target,',',1,3),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,3)-1) - instr(rta_primary_target,',',1,2)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,2),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,2)+1,
                       decode(instr(rta_primary_target_uoid,',',1,3),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,3)-1) - instr(rta_primary_target_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,2) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,3)+1,
                decode(instr(rta_primary_target,',',1,4),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,4)-1) - instr(rta_primary_target,',',1,3)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,3),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,3)+1,
                       decode(instr(rta_primary_target_uoid,',',1,4),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,4)-1) - instr(rta_primary_target_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,3) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,4)+1,
                decode(instr(rta_primary_target,',',1,5),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,5)-1) - instr(rta_primary_target,',',1,4)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,4),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,4)+1,
                       decode(instr(rta_primary_target_uoid,',',1,5),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,5)-1) - instr(rta_primary_target_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,4) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,5)+1,
                decode(instr(rta_primary_target,',',1,6),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,6)-1) - instr(rta_primary_target,',',1,5)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,5),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,5)+1,
                       decode(instr(rta_primary_target_uoid,',',1,6),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,6)-1) - instr(rta_primary_target_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,5) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,6)+1,
                decode(instr(rta_primary_target,',',1,7),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,7)-1) - instr(rta_primary_target,',',1,6)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,6),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,6)+1,
                       decode(instr(rta_primary_target_uoid,',',1,7),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,7)-1) - instr(rta_primary_target_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,6) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,7)+1,
                decode(instr(rta_primary_target,',',1,8),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,8)-1) - instr(rta_primary_target,',',1,7)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,7),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,7)+1,
                       decode(instr(rta_primary_target_uoid,',',1,8),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,8)-1) - instr(rta_primary_target_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,7) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,8)+1,
                decode(instr(rta_primary_target,',',1,9),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,9)-1) - instr(rta_primary_target,',',1,8)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,8),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,8)+1,
                       decode(instr(rta_primary_target_uoid,',',1,9),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,9)-1) - instr(rta_primary_target_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,8) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,9)+1,
                decode(instr(rta_primary_target,',',1,10),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,10)-1) - instr(rta_primary_target,',',1,9)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,9),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,9)+1,
                       decode(instr(rta_primary_target_uoid,',',1,10),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,10)-1) - instr(rta_primary_target_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,9) != 0
) t
where t.id = a.rta_iid
with read only;

create or replace view RAB_RT_EXEC_ACT_STEPS as
select
a.rte_id as audit_execution_id,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
d.rta_iid as map_run_id,
d.rtd_step as map_step,
d.rtd_iid as step_id,
replace(decode(instr(d.rtd_name,'_',-1),0,d.rtd_name,
               decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
                      '_Bat',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),
                      '_p',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),
                      '_t',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),d.rtd_name)),'"') as step_name,
decode(instr(d.rtd_name,'_',-1),0,null,
       decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
              '_Bat','Set-based','_p','Row-based','_t','Row-based (target)',null)) as step_type,
decode(instr(d.rtd_name,'_',-1),0,null,
       decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
              '_Bat','SET_BASED','_p','ROW_BASED','_t','ROW_BASED_TARGET',null)) as step_type_symbol,
d.creation_date as start_time,
d.creation_date + (d.rtd_elapse/86400) as end_time,
d.rtd_elapse as elapse_time,
d.rtd_status as run_status,
st.symbol as run_status_symbol,
d.rtd_statement as statement,
d.rtd_info as info,
d.rtd_when_clause as when_clause,
decode(d.rtd_errors, null, 0 ,d.rtd_errors) as number_errors,
decode(d.rtd_logical_errors, null, 0 ,d.rtd_logical_errors) as number_logical_errors,
d.rtd_select as number_records_selected,
d.rtd_insert as number_records_inserted,
d.rtd_update as number_records_updated,
d.rtd_delete as number_records_deleted,
d.rtd_discarded as number_records_discarded,
d.rtd_merge as number_records_merged,
d.rtd_corrections as number_records_corrected
from wb_rt_audit_detail d, wb_rt_audit a, rab_rt_exec_map_status st
where d.rta_iid = a.rta_iid
and st.status (+) = d.rtd_status
with read only;

create or replace view RAB_RT_EXEC_ACT_STEP_SOURCES as
select
d.rta_iid as map_run_id,
d.rtd_step as map_step,
d.rtd_iid as step_id,
replace(decode(instr(d.rtd_name,'_',-1),0,d.rtd_name,
               decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
                      '_Bat',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),
                      '_p',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),
                      '_t',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),d.rtd_name)),'"') as step_name,
decode(instr(d.rtd_name,'_',-1),0,null,
       decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
              '_Bat','Set-based','_p','Row-based','_t','Row-based (target)',null)) as step_type,
decode(instr(d.rtd_name,'_',-1),0,null,
       decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
              '_Bat','SET_BASED','_p','ROW_BASED','_t','ROW_BASED_TARGET',null)) as step_type_symbol,
decode(s.yy,'*',null,s.yy) as source_uoid,
decode(instr(s.xx,'@'),0,s.xx,substr(s.xx,1,instr(s.xx,'@')-1)) as source_name,
null as source_type,
null as source_type_symbol,
decode(instr(s.xx,'@'),0,null,substr(s.xx,instr(s.xx,'@')+1)) as source_dblink
from wb_rt_audit_detail d,
(select rtd_iid as id,
        replace(substr(rtd_source,1,decode(instr(rtd_source,','),0,
                length(rtd_source),instr(rtd_source,',')-1)),'"') as xx,
        decode(rtd_source_uoid,null,null,
               replace(substr(rtd_source_uoid,1,
                       decode(instr(rtd_source_uoid,','),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',')-1)),' ')) as yy
 from wb_rt_audit_detail
 where rtd_source is not null
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',')+1,
                decode(instr(rtd_source,',',1,2),0,length(rtd_source),
                instr(rtd_source,',',1,2)-1) - instr(rtd_source,',')),'"') as xx,
        decode(instr(rtd_source_uoid,','),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',')+1,
                       decode(instr(rtd_source_uoid,',',1,2),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,2)-1) - instr(rtd_source_uoid,',')),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',') != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,2)+1,
                decode(instr(rtd_source,',',1,3),0,length(rtd_source),
                instr(rtd_source,',',1,3)-1) - instr(rtd_source,',',1,2)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,2),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,2)+1,
                       decode(instr(rtd_source_uoid,',',1,3),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,3)-1) - instr(rtd_source_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,2) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,3)+1,
                decode(instr(rtd_source,',',1,4),0,length(rtd_source),
                instr(rtd_source,',',1,4)-1) - instr(rtd_source,',',1,3)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,3),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,3)+1,
                       decode(instr(rtd_source_uoid,',',1,4),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,4)-1) - instr(rtd_source_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,3) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,4)+1,
                decode(instr(rtd_source,',',1,5),0,length(rtd_source),
                instr(rtd_source,',',1,5)-1) - instr(rtd_source,',',1,4)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,4),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,4)+1,
                       decode(instr(rtd_source_uoid,',',1,5),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,5)-1) - instr(rtd_source_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,4) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,5)+1,
                decode(instr(rtd_source,',',1,6),0,length(rtd_source),
                instr(rtd_source,',',1,6)-1) - instr(rtd_source,',',1,5)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,5),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,5)+1,
                       decode(instr(rtd_source_uoid,',',1,6),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,6)-1) - instr(rtd_source_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,5) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,6)+1,
                decode(instr(rtd_source,',',1,7),0,length(rtd_source),
                instr(rtd_source,',',1,7)-1) - instr(rtd_source,',',1,6)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,6),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,6)+1,
                       decode(instr(rtd_source_uoid,',',1,7),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,7)-1) - instr(rtd_source_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,6) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,7)+1,
                decode(instr(rtd_source,',',1,8),0,length(rtd_source),
                instr(rtd_source,',',1,8)-1) - instr(rtd_source,',',1,7)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,7),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,7)+1,
                       decode(instr(rtd_source_uoid,',',1,8),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,8)-1) - instr(rtd_source_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,7) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,8)+1,
                decode(instr(rtd_source,',',1,9),0,length(rtd_source),
                instr(rtd_source,',',1,9)-1) - instr(rtd_source,',',1,8)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,8),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,8)+1,
                       decode(instr(rtd_source_uoid,',',1,9),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,9)-1) - instr(rtd_source_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,8) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_source,instr(rtd_source,',',1,9)+1,
                decode(instr(rtd_source,',',1,10),0,length(rtd_source),
                instr(rtd_source,',',1,10)-1) - instr(rtd_source,',',1,9)),'"') as xx,
        decode(instr(rtd_source_uoid,',',1,9),0,null,
               replace(substr(rtd_source_uoid,instr(rtd_source_uoid,',',1,9)+1,
                       decode(instr(rtd_source_uoid,',',1,10),0,length(rtd_source_uoid),
                              instr(rtd_source_uoid,',',1,10)-1) - instr(rtd_source_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_source,',',1,9) != 0
) s
where s.id = d.rtd_iid
with read only;

create or replace view RAB_RT_EXEC_ACT_STEP_TARGETS as
select
d.rta_iid as map_run_id,
d.rtd_step as map_step,
d.rtd_iid as step_id,
replace(decode(instr(d.rtd_name,'_',-1),0,d.rtd_name,
               decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
                      '_Bat',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),
                      '_p',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),
                      '_t',substr(d.rtd_name,1,instr(d.rtd_name,'_',-1)-1),d.rtd_name)),'"') as step_name,
decode(instr(d.rtd_name,'_',-1),0,null,
       decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
              '_Bat','Set-based','_p','Row-based','_t','Row-based (target)',null)) as step_type,
decode(instr(d.rtd_name,'_',-1),0,null,
       decode(rtrim(substr(d.rtd_name,instr(d.rtd_name,'_',-1)),'" '),
              '_Bat','SET_BASED','_p','ROW_BASED','_t','ROW_BASED_TARGET',null)) as step_type_symbol,
decode(t.yy,'*',null,t.yy) as target_uoid,
decode(instr(t.xx,'@'),0,t.xx,substr(t.xx,1,instr(t.xx,'@')-1)) as target_name,
null as target_type,
null as target_type_symbol,
decode(instr(t.xx,'@'),0,null,substr(t.xx,instr(t.xx,'@')+1)) as target_dblink
from wb_rt_audit_detail d,
(select rtd_iid as id,
        replace(substr(rtd_target,1,decode(instr(rtd_target,','),0,
                length(rtd_target),instr(rtd_target,',')-1)),'"') as xx,
        decode(rtd_target_uoid,null,null,
               replace(substr(rtd_target_uoid,1,
                       decode(instr(rtd_target_uoid,','),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',')-1)),' ')) as yy
 from wb_rt_audit_detail
 where rtd_target is not null
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',')+1,
                decode(instr(rtd_target,',',1,2),0,length(rtd_target),
                instr(rtd_target,',',1,2)-1) - instr(rtd_target,',')),'"') as xx,
        decode(instr(rtd_target_uoid,','),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',')+1,
                       decode(instr(rtd_target_uoid,',',1,2),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,2)-1) - instr(rtd_target_uoid,',')),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',') != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,2)+1,
                decode(instr(rtd_target,',',1,3),0,length(rtd_target),
                instr(rtd_target,',',1,3)-1) - instr(rtd_target,',',1,2)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,2),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,2)+1,
                       decode(instr(rtd_target_uoid,',',1,3),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,3)-1) - instr(rtd_target_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,2) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,3)+1,
                decode(instr(rtd_target,',',1,4),0,length(rtd_target),
                instr(rtd_target,',',1,4)-1) - instr(rtd_target,',',1,3)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,3),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,3)+1,
                       decode(instr(rtd_target_uoid,',',1,4),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,4)-1) - instr(rtd_target_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,3) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,4)+1,
                decode(instr(rtd_target,',',1,5),0,length(rtd_target),
                instr(rtd_target,',',1,5)-1) - instr(rtd_target,',',1,4)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,4),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,4)+1,
                       decode(instr(rtd_target_uoid,',',1,5),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,5)-1) - instr(rtd_target_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,4) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,5)+1,
                decode(instr(rtd_target,',',1,6),0,length(rtd_target),
                instr(rtd_target,',',1,6)-1) - instr(rtd_target,',',1,5)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,5),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,5)+1,
                       decode(instr(rtd_target_uoid,',',1,6),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,6)-1) - instr(rtd_target_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,5) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,6)+1,
                decode(instr(rtd_target,',',1,7),0,length(rtd_target),
                instr(rtd_target,',',1,7)-1) - instr(rtd_target,',',1,6)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,6),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,6)+1,
                       decode(instr(rtd_target_uoid,',',1,7),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,7)-1) - instr(rtd_target_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,6) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,7)+1,
                decode(instr(rtd_target,',',1,8),0,length(rtd_target),
                instr(rtd_target,',',1,8)-1) - instr(rtd_target,',',1,7)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,7),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,7)+1,
                       decode(instr(rtd_target_uoid,',',1,8),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,8)-1) - instr(rtd_target_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,7) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,8)+1,
                decode(instr(rtd_target,',',1,9),0,length(rtd_target),
                instr(rtd_target,',',1,9)-1) - instr(rtd_target,',',1,8)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,8),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,8)+1,
                       decode(instr(rtd_target_uoid,',',1,9),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,9)-1) - instr(rtd_target_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,8) != 0
 UNION
 select rtd_iid as id,
        replace(substr(rtd_target,instr(rtd_target,',',1,9)+1,
                decode(instr(rtd_target,',',1,10),0,length(rtd_target),
                instr(rtd_target,',',1,10)-1) - instr(rtd_target,',',1,9)),'"') as xx,
        decode(instr(rtd_target_uoid,',',1,9),0,null,
               replace(substr(rtd_target_uoid,instr(rtd_target_uoid,',',1,9)+1,
                       decode(instr(rtd_target_uoid,',',1,10),0,length(rtd_target_uoid),
                              instr(rtd_target_uoid,',',1,10)-1) - instr(rtd_target_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit_detail
 where instr(rtd_target,',',1,9) != 0
) t
where t.id = d.rtd_iid
with read only;

create or replace view RAB_RT_EXEC_ACT_STEP_STRUCTS as
select
t.rtt_iid as struct_id,
t.rtd_iid as step_id,
d.rta_iid as map_run_id,
a.rte_id as audit_execution_id,
t.rtt_parent_operator_uoid as parent_operator_uoid,
t.rtt_parent_object_uoid as parent_object_uoid,
t.rtt_parent_object_type as parent_object_type,
z1.symbol as parent_object_type_symbol,
t.rtt_parent_object_loc_uoid as parent_object_location_uoid,
t.rtt_parent_object_name as parent_object_name,
t.rtt_object_uoid as object_uoid,
t.rtt_object_type as object_type,
z2.symbol as object_type_symbol,
t.rtt_object_loc_uoid as object_location_uoid,
t.rtt_object_name as object_name
from wb_rt_audit_struct t, wb_rt_audit_detail d, wb_rt_audit a,
rab_rt_rep_types z1, rab_rt_rep_types z2
where t.rtd_iid = d.rtd_iid
and a.rta_iid = d.rta_iid
and z1.obj_type (+) = t.rtt_parent_object_type
and z2.obj_type (+) = t.rtt_object_type
with read only;

create or replace view RAB_RT_EXEC_PROCESS_RUN_ERRORS as
select
a.rte_id as audit_execution_id,
e.rta_iid as map_run_id,
e.rte_iid as run_error_id,
e.rte_rowkey as cursor_rowkey,
e.rte_sqlerr as run_error_number,
e.rte_sqlerrm as run_error_message,
null as target_uoid,
replace(e.rte_dest_table,'"') as target_name,
null as target_type,
null as target_type_symbol,
decode(t1.xx,null,null,decode(instr(t1.xx,'@'),0,t1.xx,substr(t1.xx,1,instr(t1.xx,'@')-1))) as first_target,
decode(t1.xx,null,null,decode(instr(t1.xx,'@'),0,null,substr(t1.xx,instr(t1.xx,'@')+1))) as first_dblink,
replace(e.rte_dest_column,'"') as target_column,
e.created_by as created_by,
e.creation_date as error_time
from wb_rt_errors e, wb_rt_audit a,
(select rte_iid as id,
        replace(substr(rte_dest_table,1,decode(instr(rte_dest_table,','),0,
                length(rte_dest_table),instr(rte_dest_table,',')-1)),'"') as xx
 from wb_rt_errors
 where rte_dest_table is not null
) t1
where e.rta_iid = a.rta_iid and e.rtd_iid is null
  and t1.id (+) = e.rte_iid
with read only;

create or replace view RAB_RT_EXEC_ACT_RUN_ERRORS as
select
a.rte_id as audit_execution_id,
e.rta_iid as map_run_id,
e.rta_step as map_step,
e.rtd_iid as step_id,
e.rte_iid as run_error_id,
e.rte_rowkey as cursor_rowkey,
e.rte_rowid as row_ident,
e.rte_sqlerr as run_error_number,
e.rte_sqlerrm as run_error_message,
null as target_uoid,
replace(e.rte_dest_table,'"') as target_name,
null as target_type,
null as target_type_symbol,
decode(t1.xx,null,null,decode(instr(t1.xx,'@'),0,t1.xx,substr(t1.xx,1,instr(t1.xx,'@')-1))) as first_target,
decode(t1.xx,null,null,decode(instr(t1.xx,'@'),0,null,substr(t1.xx,instr(t1.xx,'@')+1))) as first_dblink,
replace(e.rte_dest_column,'"') as target_column,
e.creation_date as error_time,
e.rte_value as column_value,
e.rte_statement as statement,
e.rte_correction as correction,
e.created_by as created_by,
f.rtf_role as role,
f.rtf_action as action,
f.rtf_key01 || f.rtf_key02 || f.rtf_key03 || f.rtf_key04 || f.rtf_key05 || 
f.rtf_key06 || f.rtf_key07 || f.rtf_key08 || f.rtf_key09 as keys
from wb_rt_errors e, wb_rt_audit a, wb_rt_feedback f,
(select rte_iid as id,
        replace(substr(rte_dest_table,1,decode(instr(rte_dest_table,','),0,
                length(rte_dest_table),instr(rte_dest_table,',')-1)),'"') as xx
 from wb_rt_errors
 where rte_dest_table is not null
) t1
where e.rta_iid = a.rta_iid and e.rtd_iid is not null
  and e.rta_iid = f.rta_iid (+) and e.rte_rowkey = f.rte_rowkey (+)
  and f.rte_status (+) = 'ERROR'
  and t1.id (+) = e.rte_iid
with read only;  

create or replace view RAB_RT_EXEC_ERROR_COLUMNS as
select
e.rte_iid as run_error_id,
c.rte_rowkey as cursor_rowkey,
c.rts_iid as source_column_id,
c.rts_seq as source_column_seq,
replace(c.rts_source_column,'"') as source_column,
c.rts_instance as source_column_instance,
c.rts_value as source_column_value,
c.rts_role as role
from wb_rt_error_sources c, wb_rt_errors e
where c.rta_iid = e.rta_iid and c.rte_rowkey = e.rte_rowkey
with read only;

create or replace view RAB_RT_EXEC_ERROR_SOURCES as
select
e.rte_iid as run_error_id,
c.rte_rowkey as cursor_rowkey,
c.rts_iid as source_column_id,
replace(c.rts_source_column,'"') as source_column,
c.rts_instance as source_column_instance,
c.rts_value as source_column_value,
null as source_uoid,
decode(instr(s.xx,'@'),0,s.xx,substr(s.xx,1,instr(s.xx,'@')-1)) as source_name,
null as source_type,
null as source_type_symbol,
decode(instr(s.xx,'@'),0,null,substr(s.xx,instr(s.xx,'@')+1)) as source_dblink,
c.rts_role as role,
c.created_by as created_by
from wb_rt_error_sources c, wb_rt_errors e,
(select rts_iid as id,
        replace(substr(rts_source_table,1,decode(instr(rts_source_table,','),0,
                length(rts_source_table),instr(rts_source_table,',')-1)),'"') as xx
 from wb_rt_error_sources
 where rts_source_table is not null
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',')+1,
                decode(instr(rts_source_table,',',1,2),0,length(rts_source_table),
                instr(rts_source_table,',',1,2)-1) - instr(rts_source_table,',')),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',') != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,2)+1,
                decode(instr(rts_source_table,',',1,3),0,length(rts_source_table),
                instr(rts_source_table,',',1,3)-1) - instr(rts_source_table,',',1,2)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,2) != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,3)+1,
                decode(instr(rts_source_table,',',1,4),0,length(rts_source_table),
                instr(rts_source_table,',',1,4)-1) - instr(rts_source_table,',',1,3)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,3) != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,4)+1,
                decode(instr(rts_source_table,',',1,5),0,length(rts_source_table),
                instr(rts_source_table,',',1,5)-1) - instr(rts_source_table,',',1,4)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,4) != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,5)+1,
                decode(instr(rts_source_table,',',1,6),0,length(rts_source_table),
                instr(rts_source_table,',',1,6)-1) - instr(rts_source_table,',',1,5)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,5) != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,6)+1,
                decode(instr(rts_source_table,',',1,7),0,length(rts_source_table),
                instr(rts_source_table,',',1,7)-1) - instr(rts_source_table,',',1,6)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,6) != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,7)+1,
                decode(instr(rts_source_table,',',1,8),0,length(rts_source_table),
                instr(rts_source_table,',',1,8)-1) - instr(rts_source_table,',',1,7)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,7) != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,8)+1,
                decode(instr(rts_source_table,',',1,9),0,length(rts_source_table),
                instr(rts_source_table,',',1,9)-1) - instr(rts_source_table,',',1,8)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,8) != 0
 UNION
 select rts_iid as id,
        replace(substr(rts_source_table,instr(rts_source_table,',',1,9)+1,
                decode(instr(rts_source_table,',',1,10),0,length(rts_source_table),
                instr(rts_source_table,',',1,10)-1) - instr(rts_source_table,',',1,9)),'"') as xx
 from wb_rt_error_sources
 where instr(rts_source_table,',',1,9) != 0
) s
where c.rta_iid = e.rta_iid and c.rte_rowkey = e.rte_rowkey
  and s.id = c.rts_iid
with read only;  

create or replace view RAB_RT_EXEC_ACT_RUN_TRACES as
select
a.rte_id as audit_execution_id,
f.rta_iid as map_run_id,
f.rtf_step as map_step,
f.rtf_iid as trace_id,
f.rte_rowkey as rowkey,
f.rte_rowid as row_ident,
f.rte_status as status,
f.rtf_role as role,
f.rtf_action as action,
replace(rtf_table_name,'"') as table_name,
decode(t1.xx,null,null,decode(instr(t1.xx,'@'),0,t1.xx,substr(t1.xx,1,instr(t1.xx,'@')-1))) as first_table,
decode(t1.xx,null,null,decode(instr(t1.xx,'@'),0,null,substr(t1.xx,instr(t1.xx,'@')+1))) as first_dblink,
f.rtf_key01 || f.rtf_key02 || f.rtf_key03 || f.rtf_key04 || f.rtf_key05 || 
f.rtf_key06 || f.rtf_key07 || f.rtf_key08 || f.rtf_key09 as keys,
f.creation_date as trace_time
from wb_rt_feedback f, wb_rt_audit a,
(select rtf_iid as id,
        replace(substr(rtf_table_name,1,decode(instr(rtf_table_name,','),0,
                length(rtf_table_name),instr(rtf_table_name,',')-1)),'"') as xx
 from wb_rt_feedback
 where rtf_table_name is not null
) t1
where a.rta_iid = f.rta_iid
  and t1.id (+) = f.rtf_iid
with read only;

create or replace view RAB_RT_EXEC_SOURCE_AUDITS as
select distinct
r.rtt_parent_object_uoid as source_uoid,
r.rtt_parent_object_name as source_name,
r.rtt_parent_object_type as source_type,
rt.symbol as source_type_symbol,
r.rtt_parent_object_loc_uoid as source_location_uoid,
decode(instr(s.xx,'@'),0,null,substr(s.xx,instr(s.xx,'@')+1)) as source_dblink,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
decode(e.execution_object_store_uoid, null, t.execution_object_store_uoid, e.execution_object_store_uoid) as map_store_uoid,
a.rta_iid as map_run_id,
a.rte_id as audit_execution_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.return_result_number as result_number,
e.audit_status as run_status,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
decode(a.rta_errors, null, 0, a.rta_errors) as number_errors,
decode(a.rta_logical_errors, null, 0, a.rta_logical_errors) as number_logical_errors,
a.rta_select as number_records_selected,
a.rta_insert as number_records_inserted,
a.rta_update as number_records_updated,
a.rta_delete as number_records_deleted,
a.rta_discarded as number_records_discarded,
a.rta_merge as number_records_merged,
a.rta_corrections as number_records_corrected
from wb_rt_audit_struct r, wb_rt_audit_detail d, wb_rt_audit a, wb_rt_audit_executions e, wb_rt_audit_executions t,
rab_rt_rep_types rt,
(select rta_iid as id,
        replace(substr(rta_primary_source,1,decode(instr(rta_primary_source,','),0,
                length(rta_primary_source),instr(rta_primary_source,',')-1)),'"') as xx,
        decode(rta_primary_source_uoid,null,null,
               replace(substr(rta_primary_source_uoid,1,
                       decode(instr(rta_primary_source_uoid,','),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',')-1)),' ')) as yy
 from wb_rt_audit
 where rta_primary_source is not null
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',')+1,
                decode(instr(rta_primary_source,',',1,2),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,2)-1) - instr(rta_primary_source,',')),'"') as xx,
        decode(instr(rta_primary_source_uoid,','),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',')+1,
                       decode(instr(rta_primary_source_uoid,',',1,2),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,2)-1) - instr(rta_primary_source_uoid,',')),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',') != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,2)+1,
                decode(instr(rta_primary_source,',',1,3),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,3)-1) - instr(rta_primary_source,',',1,2)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,2),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,2)+1,
                       decode(instr(rta_primary_source_uoid,',',1,3),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,3)-1) - instr(rta_primary_source_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,2) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,3)+1,
                decode(instr(rta_primary_source,',',1,4),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,4)-1) - instr(rta_primary_source,',',1,3)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,3),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,3)+1,
                       decode(instr(rta_primary_source_uoid,',',1,4),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,4)-1) - instr(rta_primary_source_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,3) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,4)+1,
                decode(instr(rta_primary_source,',',1,5),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,5)-1) - instr(rta_primary_source,',',1,4)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,4),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,4)+1,
                       decode(instr(rta_primary_source_uoid,',',1,5),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,5)-1) - instr(rta_primary_source_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,4) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,5)+1,
                decode(instr(rta_primary_source,',',1,6),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,6)-1) - instr(rta_primary_source,',',1,5)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,5),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,5)+1,
                       decode(instr(rta_primary_source_uoid,',',1,6),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,6)-1) - instr(rta_primary_source_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,5) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,6)+1,
                decode(instr(rta_primary_source,',',1,7),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,7)-1) - instr(rta_primary_source,',',1,6)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,6),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,6)+1,
                       decode(instr(rta_primary_source_uoid,',',1,7),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,7)-1) - instr(rta_primary_source_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,6) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,7)+1,
                decode(instr(rta_primary_source,',',1,8),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,8)-1) - instr(rta_primary_source,',',1,7)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,7),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,7)+1,
                       decode(instr(rta_primary_source_uoid,',',1,8),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,8)-1) - instr(rta_primary_source_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,7) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,8)+1,
                decode(instr(rta_primary_source,',',1,9),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,9)-1) - instr(rta_primary_source,',',1,8)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,8),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,8)+1,
                       decode(instr(rta_primary_source_uoid,',',1,9),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,9)-1) - instr(rta_primary_source_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,8) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,9)+1,
                decode(instr(rta_primary_source,',',1,10),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,10)-1) - instr(rta_primary_source,',',1,9)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,9),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,9)+1,
                       decode(instr(rta_primary_source_uoid,',',1,10),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,10)-1) - instr(rta_primary_source_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,9) != 0
) s
where d.rtd_iid = r.rtd_iid
  and a.rta_iid = d.rta_iid
  and e.audit_execution_id = a.rte_id
  and t.audit_execution_id = e.top_level_audit_execution_id
  and s.id = a.rta_iid
  and s.yy = r.rtt_parent_operator_uoid
  and rt.obj_type (+) = r.rtt_parent_object_type
UNION
select distinct
r.rtt_object_uoid as source_uoid,
r.rtt_object_name as source_name,
r.rtt_object_type as source_type,
rt.symbol as source_type_symbol,
r.rtt_object_loc_uoid as source_location_uoid,
decode(instr(s.xx,'@'),0,null,substr(s.xx,instr(s.xx,'@')+1)) as source_dblink,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
decode(e.execution_object_store_uoid, null, t.execution_object_store_uoid, e.execution_object_store_uoid) as map_store_uoid,
a.rta_iid as map_run_id,
a.rte_id as audit_execution_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.return_result_number as result_number,
e.audit_status as run_status,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
decode(a.rta_errors, null, 0, a.rta_errors) as number_errors,
decode(a.rta_logical_errors, null, 0, a.rta_logical_errors) as number_logical_errors,
a.rta_select as number_records_selected,
a.rta_insert as number_records_inserted,
a.rta_update as number_records_updated,
a.rta_delete as number_records_deleted,
a.rta_discarded as number_records_discarded,
a.rta_merge as number_records_merged,
a.rta_corrections as number_records_corrected
from wb_rt_audit_struct r, wb_rt_audit_detail d, wb_rt_audit a, wb_rt_audit_executions e, wb_rt_audit_executions t,
rab_rt_rep_types rt,
(select rta_iid as id,
        replace(substr(rta_primary_source,1,decode(instr(rta_primary_source,','),0,
                length(rta_primary_source),instr(rta_primary_source,',')-1)),'"') as xx,
        decode(rta_primary_source_uoid,null,null,
               replace(substr(rta_primary_source_uoid,1,
                       decode(instr(rta_primary_source_uoid,','),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',')-1)),' ')) as yy
 from wb_rt_audit
 where rta_primary_source is not null
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',')+1,
                decode(instr(rta_primary_source,',',1,2),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,2)-1) - instr(rta_primary_source,',')),'"') as xx,
        decode(instr(rta_primary_source_uoid,','),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',')+1,
                       decode(instr(rta_primary_source_uoid,',',1,2),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,2)-1) - instr(rta_primary_source_uoid,',')),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',') != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,2)+1,
                decode(instr(rta_primary_source,',',1,3),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,3)-1) - instr(rta_primary_source,',',1,2)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,2),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,2)+1,
                       decode(instr(rta_primary_source_uoid,',',1,3),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,3)-1) - instr(rta_primary_source_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,2) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,3)+1,
                decode(instr(rta_primary_source,',',1,4),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,4)-1) - instr(rta_primary_source,',',1,3)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,3),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,3)+1,
                       decode(instr(rta_primary_source_uoid,',',1,4),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,4)-1) - instr(rta_primary_source_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,3) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,4)+1,
                decode(instr(rta_primary_source,',',1,5),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,5)-1) - instr(rta_primary_source,',',1,4)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,4),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,4)+1,
                       decode(instr(rta_primary_source_uoid,',',1,5),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,5)-1) - instr(rta_primary_source_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,4) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,5)+1,
                decode(instr(rta_primary_source,',',1,6),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,6)-1) - instr(rta_primary_source,',',1,5)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,5),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,5)+1,
                       decode(instr(rta_primary_source_uoid,',',1,6),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,6)-1) - instr(rta_primary_source_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,5) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,6)+1,
                decode(instr(rta_primary_source,',',1,7),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,7)-1) - instr(rta_primary_source,',',1,6)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,6),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,6)+1,
                       decode(instr(rta_primary_source_uoid,',',1,7),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,7)-1) - instr(rta_primary_source_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,6) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,7)+1,
                decode(instr(rta_primary_source,',',1,8),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,8)-1) - instr(rta_primary_source,',',1,7)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,7),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,7)+1,
                       decode(instr(rta_primary_source_uoid,',',1,8),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,8)-1) - instr(rta_primary_source_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,7) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,8)+1,
                decode(instr(rta_primary_source,',',1,9),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,9)-1) - instr(rta_primary_source,',',1,8)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,8),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,8)+1,
                       decode(instr(rta_primary_source_uoid,',',1,9),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,9)-1) - instr(rta_primary_source_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,8) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_source,instr(rta_primary_source,',',1,9)+1,
                decode(instr(rta_primary_source,',',1,10),0,length(rta_primary_source),
                instr(rta_primary_source,',',1,10)-1) - instr(rta_primary_source,',',1,9)),'"') as xx,
        decode(instr(rta_primary_source_uoid,',',1,9),0,null,
               replace(substr(rta_primary_source_uoid,instr(rta_primary_source_uoid,',',1,9)+1,
                       decode(instr(rta_primary_source_uoid,',',1,10),0,length(rta_primary_source_uoid),
                              instr(rta_primary_source_uoid,',',1,10)-1) - instr(rta_primary_source_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_source,',',1,9) != 0
) s
where d.rtd_iid = r.rtd_iid
  and a.rta_iid = d.rta_iid
  and e.audit_execution_id = a.rte_id
  and t.audit_execution_id = e.top_level_audit_execution_id
  and s.id = a.rta_iid
  and s.yy = r.rtt_parent_operator_uoid
  and rt.obj_type (+) = r.rtt_object_type
with read only;

create or replace view RAB_RT_EXEC_TARGET_AUDITS as
select distinct
r.rtt_parent_object_uoid as target_uoid,
r.rtt_parent_object_name as target_name,
r.rtt_parent_object_type as target_type,
rt.symbol as target_type_symbol,
r.rtt_parent_object_loc_uoid as target_location_uoid,
decode(instr(s.xx,'@'),0,null,substr(s.xx,instr(s.xx,'@')+1)) as target_dblink,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
decode(e.execution_object_store_uoid, null, t.execution_object_store_uoid, e.execution_object_store_uoid) as map_store_uoid,
a.rta_iid as map_run_id,
a.rte_id as audit_execution_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.return_result_number as result_number,
e.audit_status as run_status,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
decode(a.rta_errors, null, 0, a.rta_errors) as number_errors,
decode(a.rta_logical_errors, null, 0, a.rta_logical_errors) as number_logical_errors,
a.rta_select as number_records_selected,
a.rta_insert as number_records_inserted,
a.rta_update as number_records_updated,
a.rta_delete as number_records_deleted,
a.rta_discarded as number_records_discarded,
a.rta_merge as number_records_merged,
a.rta_corrections as number_records_corrected
from wb_rt_audit_struct r, wb_rt_audit_detail d, wb_rt_audit a, wb_rt_audit_executions e, wb_rt_audit_executions t,
rab_rt_rep_types rt,
(select rta_iid as id,
        replace(substr(rta_primary_target,1,decode(instr(rta_primary_target,','),0,
                length(rta_primary_target),instr(rta_primary_target,',')-1)),'"') as xx,
        decode(rta_primary_target_uoid,null,null,
               replace(substr(rta_primary_target_uoid,1,
                       decode(instr(rta_primary_target_uoid,','),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',')-1)),' ')) as yy
 from wb_rt_audit
 where rta_primary_target is not null
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',')+1,
                decode(instr(rta_primary_target,',',1,2),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,2)-1) - instr(rta_primary_target,',')),'"') as xx,
        decode(instr(rta_primary_target_uoid,','),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',')+1,
                       decode(instr(rta_primary_target_uoid,',',1,2),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,2)-1) - instr(rta_primary_target_uoid,',')),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',') != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,2)+1,
                decode(instr(rta_primary_target,',',1,3),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,3)-1) - instr(rta_primary_target,',',1,2)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,2),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,2)+1,
                       decode(instr(rta_primary_target_uoid,',',1,3),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,3)-1) - instr(rta_primary_target_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,2) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,3)+1,
                decode(instr(rta_primary_target,',',1,4),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,4)-1) - instr(rta_primary_target,',',1,3)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,3),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,3)+1,
                       decode(instr(rta_primary_target_uoid,',',1,4),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,4)-1) - instr(rta_primary_target_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,3) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,4)+1,
                decode(instr(rta_primary_target,',',1,5),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,5)-1) - instr(rta_primary_target,',',1,4)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,4),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,4)+1,
                       decode(instr(rta_primary_target_uoid,',',1,5),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,5)-1) - instr(rta_primary_target_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,4) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,5)+1,
                decode(instr(rta_primary_target,',',1,6),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,6)-1) - instr(rta_primary_target,',',1,5)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,5),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,5)+1,
                       decode(instr(rta_primary_target_uoid,',',1,6),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,6)-1) - instr(rta_primary_target_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,5) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,6)+1,
                decode(instr(rta_primary_target,',',1,7),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,7)-1) - instr(rta_primary_target,',',1,6)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,6),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,6)+1,
                       decode(instr(rta_primary_target_uoid,',',1,7),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,7)-1) - instr(rta_primary_target_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,6) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,7)+1,
                decode(instr(rta_primary_target,',',1,8),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,8)-1) - instr(rta_primary_target,',',1,7)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,7),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,7)+1,
                       decode(instr(rta_primary_target_uoid,',',1,8),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,8)-1) - instr(rta_primary_target_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,7) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,8)+1,
                decode(instr(rta_primary_target,',',1,9),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,9)-1) - instr(rta_primary_target,',',1,8)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,8),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,8)+1,
                       decode(instr(rta_primary_target_uoid,',',1,9),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,9)-1) - instr(rta_primary_target_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,8) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,9)+1,
                decode(instr(rta_primary_target,',',1,10),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,10)-1) - instr(rta_primary_target,',',1,9)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,9),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,9)+1,
                       decode(instr(rta_primary_target_uoid,',',1,10),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,10)-1) - instr(rta_primary_target_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,9) != 0
) s
where d.rtd_iid = r.rtd_iid
  and a.rta_iid = d.rta_iid
  and e.audit_execution_id = a.rte_id
  and t.audit_execution_id = e.top_level_audit_execution_id
  and s.id = a.rta_iid
  and s.yy = r.rtt_parent_operator_uoid
  and rt.obj_type (+) = r.rtt_parent_object_type
UNION
select distinct
r.rtt_object_uoid as target_uoid,
r.rtt_object_name as target_name,
r.rtt_object_type as target_type,
rt.symbol as target_type_symbol,
r.rtt_object_loc_uoid as target_location_uoid,
decode(instr(s.xx,'@'),0,null,substr(s.xx,instr(s.xx,'@')+1)) as target_dblink,
a.rta_lob_uoid as map_uoid,
a.rta_lob_name as map_name,
a.rta_type as map_type,
upper(replace(replace(a.rta_type,' ','_'),'/','_')) as map_type_symbol,
decode(e.execution_object_store_uoid, null, t.execution_object_store_uoid, e.execution_object_store_uoid) as map_store_uoid,
a.rta_iid as map_run_id,
a.rte_id as audit_execution_id,
e.creation_date as start_time,
e.creation_date + (e.elapse/86400) as end_time,
e.elapse as elapse_time,
e.return_result_number as result_number,
e.audit_status as run_status,
decode(e.return_result, null, wb_rt_constants.to_string(e.audit_status),
       wb_rt_constants.to_string(e.audit_status) || '_' || upper(e.return_result)) as run_status_symbol,
decode(a.rta_errors, null, 0, a.rta_errors) as number_errors,
decode(a.rta_logical_errors, null, 0, a.rta_logical_errors) as number_logical_errors,
a.rta_select as number_records_selected,
a.rta_insert as number_records_inserted,
a.rta_update as number_records_updated,
a.rta_delete as number_records_deleted,
a.rta_discarded as number_records_discarded,
a.rta_merge as number_records_merged,
a.rta_corrections as number_records_corrected
from wb_rt_audit_struct r, wb_rt_audit_detail d, wb_rt_audit a, wb_rt_audit_executions e, wb_rt_audit_executions t,
rab_rt_rep_types rt,
(select rta_iid as id,
        replace(substr(rta_primary_target,1,decode(instr(rta_primary_target,','),0,
                length(rta_primary_target),instr(rta_primary_target,',')-1)),'"') as xx,
        decode(rta_primary_target_uoid,null,null,
               replace(substr(rta_primary_target_uoid,1,
                       decode(instr(rta_primary_target_uoid,','),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',')-1)),' ')) as yy
 from wb_rt_audit
 where rta_primary_target is not null
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',')+1,
                decode(instr(rta_primary_target,',',1,2),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,2)-1) - instr(rta_primary_target,',')),'"') as xx,
        decode(instr(rta_primary_target_uoid,','),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',')+1,
                       decode(instr(rta_primary_target_uoid,',',1,2),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,2)-1) - instr(rta_primary_target_uoid,',')),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',') != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,2)+1,
                decode(instr(rta_primary_target,',',1,3),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,3)-1) - instr(rta_primary_target,',',1,2)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,2),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,2)+1,
                       decode(instr(rta_primary_target_uoid,',',1,3),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,3)-1) - instr(rta_primary_target_uoid,',',1,2)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,2) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,3)+1,
                decode(instr(rta_primary_target,',',1,4),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,4)-1) - instr(rta_primary_target,',',1,3)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,3),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,3)+1,
                       decode(instr(rta_primary_target_uoid,',',1,4),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,4)-1) - instr(rta_primary_target_uoid,',',1,3)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,3) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,4)+1,
                decode(instr(rta_primary_target,',',1,5),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,5)-1) - instr(rta_primary_target,',',1,4)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,4),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,4)+1,
                       decode(instr(rta_primary_target_uoid,',',1,5),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,5)-1) - instr(rta_primary_target_uoid,',',1,4)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,4) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,5)+1,
                decode(instr(rta_primary_target,',',1,6),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,6)-1) - instr(rta_primary_target,',',1,5)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,5),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,5)+1,
                       decode(instr(rta_primary_target_uoid,',',1,6),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,6)-1) - instr(rta_primary_target_uoid,',',1,5)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,5) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,6)+1,
                decode(instr(rta_primary_target,',',1,7),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,7)-1) - instr(rta_primary_target,',',1,6)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,6),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,6)+1,
                       decode(instr(rta_primary_target_uoid,',',1,7),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,7)-1) - instr(rta_primary_target_uoid,',',1,6)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,6) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,7)+1,
                decode(instr(rta_primary_target,',',1,8),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,8)-1) - instr(rta_primary_target,',',1,7)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,7),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,7)+1,
                       decode(instr(rta_primary_target_uoid,',',1,8),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,8)-1) - instr(rta_primary_target_uoid,',',1,7)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,7) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,8)+1,
                decode(instr(rta_primary_target,',',1,9),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,9)-1) - instr(rta_primary_target,',',1,8)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,8),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,8)+1,
                       decode(instr(rta_primary_target_uoid,',',1,9),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,9)-1) - instr(rta_primary_target_uoid,',',1,8)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,8) != 0
 UNION
 select rta_iid as id,
        replace(substr(rta_primary_target,instr(rta_primary_target,',',1,9)+1,
                decode(instr(rta_primary_target,',',1,10),0,length(rta_primary_target),
                instr(rta_primary_target,',',1,10)-1) - instr(rta_primary_target,',',1,9)),'"') as xx,
        decode(instr(rta_primary_target_uoid,',',1,9),0,null,
               replace(substr(rta_primary_target_uoid,instr(rta_primary_target_uoid,',',1,9)+1,
                       decode(instr(rta_primary_target_uoid,',',1,10),0,length(rta_primary_target_uoid),
                              instr(rta_primary_target_uoid,',',1,10)-1) - instr(rta_primary_target_uoid,',',1,9)),' ')) as yy
 from wb_rt_audit
 where instr(rta_primary_target,',',1,9) != 0
) s
where d.rtd_iid = r.rtd_iid
  and a.rta_iid = d.rta_iid
  and e.audit_execution_id = a.rte_id
  and t.audit_execution_id = e.top_level_audit_execution_id
  and s.id = a.rta_iid
  and s.yy = r.rtt_parent_operator_uoid
  and rt.obj_type (+) = r.rtt_object_type
with read only;