Rem
Rem Copyright (c) 2000, 2008, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem

----------------------------------------------------------------------------
-- AUDIT VIEWS
----------------------------------------------------------------------------

create or replace view wb_rtv_audit_deployments
as
  select
    audit_deployment_id,
    deployment_name,
    description,
    runtime_version,
    client_version,
    client_repository,
    client_repository_version,
    client_repository_user,
    generation_time,
    audit_status,
    number_of_script_errors,
    number_of_script_warnings,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by
  from
    wb_rt_audit_deployments
  with read only;

create or replace view wb_rtv_audit_units
as
  select
    audit_unit_id,
    audit_deployment_id,
    unit_name,
    unit_number,
    store_uoid,
    store_name,
    deployment_adapter_id,
    audit_status,
    abort_sent,
    job_mode,
    job_owner,
    ops_enabled,
    number_of_script_errors,
    number_of_script_warnings,
    current_op_aborted,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by
  from      
    wb_rt_audit_units
  with read only;

create or replace view wb_rtv_audit_objects
as
  select
    audit_object_id,
    audit_unit_id,
    parent_audit_object_id,
    object_number,
    object_uoid,
    object_name,
    tgt_store_uoid,
    tgt_store_name,
    object_type_id,
    impl_type_id,
    object_version_tag,
    client_info,
    metadata_info,
    number_of_script_errors,
    number_of_script_warnings,
    final_object_status,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by
  from
    wb_rt_audit_objects
  with read only;

create or replace view wb_rtv_audit_tasks
as
  select
    audit_task_id,
    audit_object_id,
    execution_store_uoid,
    execution_operator_id,
    execution_object_uoid,
    execution_object_type_id,
    execution_object_store_uoid,
    execution_object_store_type_id,
    task_name,
    task_input,
    task_input_binary,
    task_input_mime_type,
    task_input_encoding_type,
    creation_date,
    created_by
  from 
    wb_rt_audit_tasks
  with read only;

create or replace view wb_rtv_audit_custom_parameters
as
  select
    audit_custom_parameter_id,
    audit_task_id,
    parameter_uoid,
    parameter_name,
    parameter_type,
    parameter_mode,
    parameter_scope,
    is_fixed,
    is_required,
    type_length,
    type_scale,
    type_precision,
    creation_date,
    created_by
  from
    wb_rt_audit_custom_parameters
  with read only;

create or replace view wb_rtv_audit_parameter_deflts
as
  select
    audit_parameter_default_id,
    audit_task_id,
    audit_custom_parameter_id,
    system_parameter_id,
    bound_to_name,
    bound_to_kind,
    bound_to_scope,
    value_kind,
    clob_value,
    value,
    creation_date,
    created_by
  from
    wb_rt_audit_parameter_defaults
  with read only;

create or replace view wb_rtv_audit_scripts 
as
  select
    audit_script_id,
    audit_unit_id,
    audit_object_id,
    script_number,
    action,
    is_runnable,
    script,
    script_binary,
    wb_rt_constants.FORMAT_TYPE(mime_type, encoding_type) as format_type,
    mime_type,
    encoding_type,
    generation_time,
    audit_status,
    number_of_script_errors,
    number_of_script_warnings,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by
  from
    wb_rt_audit_scripts
  with read only;

create or replace view wb_rtv_audit_script_runs
as
  select
    audit_script_run_id,
    audit_script_id,
    operation,
    audit_status,
    elapse,
    external_audit_id,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by
  from
    wb_rt_audit_script_runs
  with read only;

create or replace view wb_rtv_audit_executions
as
  select
    audit_execution_id,
    top_level_audit_execution_id,
    parent_audit_execution_id,
    scope_audit_execution_id,
    task_object_uoid,
    task_object_name,
    task_object_type_id,
    task_object_store_uoid,
    task_object_store_name,
    task_object_store_type_id,
    task_name,
    task_input,
    task_input_binary,
    task_input_mime_type,
    task_input_encoding_type,
    execution_store_uoid,
    execution_store_name,
    execution_operator_id,
    execution_object_uoid,
    execution_object_name,
    execution_object_type_id,
    execution_object_store_uoid,
    execution_object_store_name,
    execution_object_store_type_id,
    execution_name,
    description,
    return_result,
    return_result_number,
    return_code,
    audit_status,
    abort_sent,
    client_info,
    metadata_info,
    elapse,
    number_of_task_errors,
    number_of_task_warnings,
    current_op_aborted,
    external_audit_id,
    request_audit_id,
    created_by,
    creation_date,
    last_update_date,
    last_updated_by
  from
    wb_rt_audit_executions
  with read only;

create or replace view wb_rtv_audit_parameters 
as
  select
    audit_parameter_id,
    audit_execution_id,
    parameter_name,
    parameter_type,
    parameter_kind,
    parameter_mode,
    parameter_scope,
    value_mode,
    bound_to_id,
    decode(value_mode, wb_rt_constants.PARAM_VALUE_OUTPUT, 1, 0) as is_output,
    is_required,
    parameter_uoid,
    value_kind,
    value,
    clob_value,
    type_length,
    type_scale,
    type_precision
  from
    wb_rt_audit_parameters
  with read only;

create or replace view wb_rtv_audit_files 
as
  select
    audit_file_id,
    audit_unit_id,
    audit_script_run_id,
    audit_execution_id,
    file_type_id,
    file_text,
    file_binary,
    wb_rt_constants.FORMAT_TYPE(mime_type, encoding_type) as format_type,
    mime_type,
    encoding_type,
    created_by,
    creation_date
  from
    wb_rt_audit_files
  with read only;

create or replace view wb_rtv_audit_messages 
as
  select
    audit_message_id,
    audit_unit_id,
    audit_script_run_id,
    audit_execution_id,
    severity,
    created_by,
    creation_date
  from
    wb_rt_audit_messages
  with read only;

create or replace view wb_rtv_audit_message_lines 
as
  select
    audit_message_line_id,
    audit_message_id,
    line_number,
    is_nls,
    nls_key,
    plain_text
  from
    wb_rt_audit_message_lines
  with read only;

create or replace view wb_rtv_audit_message_params 
as
  select
    audit_message_parameter_id,
    audit_message_line_id,
    parameter_number,
    value
  from
    wb_rt_audit_message_parameters
  with read only;

create or replace view wb_rtv_audit_notifications
as
  select
    'A' as notification_type,
    a.audit_id,
    a.stream_id,
    a.ack_type,
    a.ack_pending,
    null as component_id,
    null as event_type,
    null as message_timeout
  from
    wb_rt_notification_acks a
  union
  select
    'C' as notification_type,
    c.audit_id,
    c.stream_id,
    null as ack_type,
    null as ack_pending,
    c.component_id,
    c.event_type,
    null as message_timeout
  from
    wb_rt_notification_components c
  union
  select
    'S' as notification_type,
    null as audit_id,
    s.stream_id,
    null as ack_type,
    null as ack_pending,
    null as component_id,
    null as event_type,
    s.message_timeout
  from
    wb_rt_notification_streams s
  with read only;

create or replace view wb_rtv_queue_tabs_info
as
  select
    'A' as queue_type,
    a.corr_id,
    a.msg_state,
    a.enq_time
  from
    aq$wb_rt_ack_queue_tab a
  union
  select
    'N' as queue_type,
    n.corr_id,
    n.msg_state,
    n.enq_time
  from
    aq$wb_rt_notify_queue_tab n
  union
  select
    'S' as queue_type,
    s.corr_id,
    s.msg_state,
    s.enq_time
  from
    aq$wb_rt_service_queue_tab s
  with read only;

create or replace view wb_rtv_locked_deploy_objs
as
  select
    u.store_uoid,
    o.object_uoid    
  from
    wb_rt_audit_deployments d,
    wb_rt_audit_units u,
    wb_rt_audit_objects o
  where
    o.audit_unit_id = u.audit_unit_id and
    u.audit_deployment_id = d.audit_deployment_id and
    d.audit_status = wb_rt_constants.DEPLOYMENT_STATUS_READY
  union
  select
    e.execution_object_store_uoid as store_uoid,
    e.execution_object_uoid as object_uoid   
  from
    wb_rt_audit_executions e
  where
    e.audit_status in (
      wb_rt_constants.EXECUTION_STATUS_BUSY,
      wb_rt_constants.EXECUTION_STATUS_READY);

create or replace view wb_rtv_locked_exec_objs
as
  select
    u.store_uoid,
    o.object_uoid    
  from
    wb_rt_audit_deployments d,
    wb_rt_audit_units u,
    wb_rt_audit_objects o
  where
    o.audit_unit_id = u.audit_unit_id and
    u.audit_deployment_id = d.audit_deployment_id and
    d.audit_status = wb_rt_constants.DEPLOYMENT_STATUS_READY;


create or replace view wb_rtv_deployment_stores
as
  select distinct
    d.audit_deployment_id,
    u.store_uoid
  from
    wb_rt_audit_deployments d,
    wb_rt_audit_units u
  where
    d.audit_deployment_id = u.audit_deployment_id and
    u.store_uoid is not null and
    u.store_uoid <> '{0}';

create type wb_rt_execution_store_t as object
(
  audit_execution_id number(22),
  store_uoid varchar2(32)
);
/

create type wb_rt_execution_stores_t as table of wb_rt_execution_store_t;
/

create or replace package wb_rt_execution_stores_f
is
  function list(p_audit_execution_id in number)
    return wb_rt_execution_stores_t pipelined;
end;
/

create or replace package body wb_rt_execution_stores_f
is
  function list(p_audit_execution_id in number)
    return wb_rt_execution_stores_t pipelined
  is
    type lookup_t is table of wb_rt_execution_store_t index by varchar2(32);
    l_lookup lookup_t;
    l_key varchar2(32);
    l_rec wb_rt_execution_store_t := wb_rt_execution_store_t(null, null);
    l_object_uoid varchar2(32);
    l_object_type_name varchar2(64);
    l_store_uoid varchar2(32);

    procedure add_task_stores(p_task_id in number)
    is
      l_execution_store_uoid varchar2(32);
      l_object_store_uoid varchar2(32);
      l_sub_object_id number;
    begin
      select
        execution_store_uoid,
        execution_object_store_uoid
      into
        l_execution_store_uoid,
        l_object_store_uoid
      from
        wb_rt_tasks
      where
        task_id = p_task_id;

      if l_execution_store_uoid is not null and l_execution_store_uoid <> '{0}'
      then
        l_rec.store_uoid := l_execution_store_uoid;
        l_lookup(l_rec.store_uoid) := l_rec;
      end if;

      if l_object_store_uoid is not null and l_object_store_uoid <> '{0}'
      then
        l_rec.store_uoid := l_object_store_uoid;
        l_lookup(l_rec.store_uoid) := l_rec;
      end if;

      for p in (
        select
          default_value
        from
          wb_rtv_task_parameters
        where
          task_id = p_task_id and
          parameter_type = wb_rt_constants.PARAM_TYPE_LOCATION_UOID and
          default_value is not null and
          default_value <> '{0}'
      )
      loop
        l_rec.store_uoid := wb_rt_conversions.extract_location_UOID(p.default_value);
        l_lookup(l_rec.store_uoid) := l_rec;
      end loop;

      for t in (
        select
          k.task_id
        from
          wb_rt_tasks st,
          wb_rt_warehouse_objects o,
          wb_rt_tasks k,
          wb_rt_def_object_types ot,
          wb_rt_stores s
        where
          st.task_id = p_task_id and
          st.execution_object_store_uoid = s.store_uoid and
          o.store_id = s.store_id and
          st.execution_object_uoid = o.object_uoid and
          st.execution_object_type_id = ot.object_type_id and
          o.object_type_name = ot.object_type_name and
          k.warehouse_object_id = o.warehouse_object_id and
          st.task_id <> k.task_id
      )
      loop
        add_task_stores(t.task_id);
      end loop;

    end;

  begin
    l_rec.audit_execution_id := p_audit_execution_id;

    for r in (
      select
        audit_execution_id,
        task_object_store_uoid as store_uoid
      from
        wb_rt_audit_executions
      where
        audit_execution_id = p_audit_execution_id and
        task_object_store_uoid is not null and
        task_object_store_uoid <> '{0}'
    )
    loop
      l_rec.store_uoid := r.store_uoid;
      l_lookup(l_rec.store_uoid) := l_rec;
    end loop;

    for r in (
      select
        audit_execution_id,
        execution_store_uoid as store_uoid
      from
        wb_rt_audit_executions
      where
        audit_execution_id = p_audit_execution_id and
        execution_store_uoid is not null and
        execution_store_uoid <> '{0}'
    )
    loop
      l_rec.store_uoid := r.store_uoid;
      l_lookup(l_rec.store_uoid) := l_rec;
    end loop;

    for r in (
      select
        audit_execution_id,
        execution_object_store_uoid as store_uoid
      from
        wb_rt_audit_executions
      where
        audit_execution_id = p_audit_execution_id and
        execution_object_store_uoid is not null and
        execution_object_store_uoid <> '{0}'
    )
    loop
      l_rec.store_uoid := r.store_uoid;
      l_lookup(l_rec.store_uoid) := l_rec;
    end loop;

    for t in (
      select
        k.task_id
      from
        wb_rt_audit_executions a,
        wb_rt_def_object_types ot,
        wb_rt_tasks k,
        wb_rt_warehouse_objects o,
        wb_rt_stores s
      where
        a.audit_execution_id = p_audit_execution_id and
        a.task_object_type_id = ot.object_type_id and
        k.warehouse_object_id = o.warehouse_object_id and
        o.store_id = s.store_id and
        o.object_uoid = a.task_object_uoid and
        o.object_type_name = ot.object_type_name and
        s.store_uoid = a.task_object_store_uoid
    )
    loop
      add_task_stores(t.task_id);
    end loop;

    l_key := l_lookup.first;
    while l_key is not null
    loop
      l_rec := l_lookup(l_key);
      pipe row(l_rec);
      l_key := l_lookup.next(l_key);
    end loop;  

    return;
  end;
end wb_rt_execution_stores_f;
/

create or replace view wb_rtv_execution_stores
as
  select
    audit_execution_id,
    task_object_store_uoid as store_uoid
  from
    wb_rt_audit_executions
  where
    task_object_store_uoid is not null and
    task_object_store_uoid <> '{0}'
  union select
    audit_execution_id,
    execution_store_uoid as store_uoid
  from
    wb_rt_audit_executions
  where
    execution_store_uoid is not null and
    execution_store_uoid <> '{0}'
  union select
    audit_execution_id,
    execution_object_store_uoid as store_uoid
  from
    wb_rt_audit_executions
  where
    execution_object_store_uoid is not null and
    execution_object_store_uoid <> '{0}';

----------------------------------------------------------------------------
-- AUDIT FUNCTIONS
----------------------------------------------------------------------------

create or replace function successful_object_deployment(p_audit_object_id number) return number is

  l_result number := 0;
  l_audit_unit_id number;
  l_number_of_messages number;
  l_count number;
begin

  select 
    count(*)
  into
    l_count
  from
    wb_rt_audit_script_runs sr,
    wb_rt_audit_scripts s
  where
    sr.audit_script_id = s.audit_script_id and
    sr.audit_status = wb_rt_constants.SCRIPT_RUN_STATUS_FAILED and
    s.audit_object_id = p_audit_object_id;
  
  if l_count = 0
  then
    select 
      audit_unit_id
    into
      l_audit_unit_id
    from 
      wb_rt_audit_objects
    where
      audit_object_id = p_audit_object_id;

    select 
      count(*)
    into
      l_count
    from 
      wb_rt_audit_messages m
    where
      m.severity in (wb_rt_constants.SEVERITY_ERROR, wb_rt_constants.SEVERITY_WARNING) and
      m.audit_unit_id = l_audit_unit_id;
  end if;

  if l_count = 0
  then
    select 
      audit_unit_id, 
      number_of_script_errors + number_of_script_warnings
    into
      l_audit_unit_id,
      l_number_of_messages
    from 
      wb_rt_audit_objects
    where
      audit_object_id = p_audit_object_id;

    if l_number_of_messages = 0
    then

      select 
        sum(number_of_script_errors + number_of_script_warnings)
      into
        l_number_of_messages
      from 
        wb_rt_audit_scripts s
      where
        s.audit_unit_id = l_audit_unit_id;

      if l_number_of_messages is null or l_number_of_messages = 0
      then
        l_result := 1;
      end if;

    end if;

  end if;
  
  return l_result;

end;
/


create or replace function object_deployment_status(p_audit_object_id number) return number is

  l_result number := 0;
  l_audit_unit_id number;
  l_number_of_warnings number;
  l_number_of_errors number;
  l_count number;
begin

  select 
    count(*)
  into
    l_count
  from
    wb_rt_audit_script_runs sr,
    wb_rt_audit_scripts s
  where
    sr.audit_script_id = s.audit_script_id and
    sr.audit_status = wb_rt_constants.SCRIPT_RUN_STATUS_FAILED and
    s.audit_object_id = p_audit_object_id;
  
  if l_count <> 0
  then
    l_result := wb_rt_constants.SEVERITY_ERROR;

  else
  
    select 
      audit_unit_id, 
      number_of_script_errors,
      number_of_script_warnings
    into
      l_audit_unit_id,
      l_number_of_errors,
      l_number_of_warnings
    from 
      wb_rt_audit_objects
    where
      audit_object_id = p_audit_object_id;

    if l_number_of_errors <> 0
    then
      l_result := wb_rt_constants.SEVERITY_ERROR;

    elsif l_number_of_warnings <> 0
    then
      l_result := wb_rt_constants.SEVERITY_WARNING;

    else

      select 
        count(*)
      into
        l_count
      from 
        wb_rt_audit_messages m
      where
        m.severity = wb_rt_constants.SEVERITY_ERROR and
        m.audit_unit_id = l_audit_unit_id;

      if l_count <> 0
      then
        l_result := wb_rt_constants.SEVERITY_ERROR;

      else

        select 
          count(*)
        into
          l_count
        from 
          wb_rt_audit_messages m
        where
          m.severity = wb_rt_constants.SEVERITY_WARNING and
          m.audit_unit_id = l_audit_unit_id;
      
        if l_count <> 0
        then          
          l_result := wb_rt_constants.SEVERITY_WARNING;
          
        else
      
          select 
            sum(number_of_script_errors),
            sum(number_of_script_warnings)
          into
            l_number_of_errors,
            l_number_of_warnings
          from 
            wb_rt_audit_scripts s
          where
            s.audit_unit_id = l_audit_unit_id;

          if l_number_of_errors <> 0
          then
            l_result := wb_rt_constants.SEVERITY_ERROR;

          elsif l_number_of_warnings <> 0
          then
            l_result := wb_rt_constants.SEVERITY_WARNING;
          end if;

        end if;

      end if;
      
    end if;
  
  end if;
  
  return l_result;

end;
/

create or replace function object_deployment_action(p_audit_object_id number) return number is

  l_result number;
  l_count number;
begin

  select 
    count(*)
  into
    l_count
  from
    wb_rt_audit_objects o,
    wb_rt_audit_scripts s
  where
    o.audit_unit_id = s.audit_unit_id and
    o.audit_object_id = p_audit_object_id;
    
  if l_count <> 0
  then
    l_result := wb_rt_constants.ACTION_UPGRADE;
    
  else
  
    select 
      count(*)
    into
      l_count
    from
      wb_rt_audit_scripts s
    where
      s.action = wb_rt_constants.ACTION_CREATE and
      s.audit_object_id = p_audit_object_id;

    if l_count <> 0
    then
      l_result := wb_rt_constants.ACTION_CREATE;
    else
      l_result := wb_rt_constants.ACTION_DROP;
    end if;
    
  end if;
  
  return l_result;

end;
/

create or replace function wb_rt_message_text(p_audit_message_id number) return varchar2 is

  l_result varchar2(4000);
begin

  for l in (
    select 
      plain_text 
    from 
      wb_rt_audit_message_lines
    where
      audit_message_id = p_audit_message_id
    order by
      line_number
  )
  loop
    if l_result is null
    then
      l_result := l.plain_text;
    else
      l_result := l_result || chr(10) || '- ' || l.plain_text;
    end if;
  end loop;
  
  return l_result;
  
exception
  when others
  then
    return l_result;

end;
/