REM 
REM This script is used to print the Audit Execution Hierarchy and details
REM about the individual executions associated with an Audit Id
REM 
REM The output is placed in a file called exec_details.txt
REM
REM Logon as WorkspaceOwner or WorkspaceUser with Execute/Deploy/Admin privilege
REM 
REM It takes two parameters representing Audit Id and Workspace
REM
REM    @<OWB-HOME>/owb/rtp/sql/print_exec_details.sql <auditid> <workspace>
REM
REM For a given audit-id which represents a execution job, this script will
REM print the job details. The audit-id may have been obtained by
REM using the list_requests.sql script
REM
REM The given workspace is the one in which the job is running
REM <workspace> should be declared as workspaceOwner.workspaceName
REM if only workspaceName is given, workspaceOwner will be defaulted to user
REM
REM    Date            Who           Comments
REM -----------------------------------------
REM  11th Nov 2003     GWatters      V1.0
REM  16th Nov 2003     RWhittington  V2.0
REM

define EXEC_ID=&1.
define WORKSPACE=&2.

set role OWB_USER;
call owbsys.wb_rt_script_util.set_workspace('&WORKSPACE.');

set linesize 175
set pagesize 60
set newpage 0
set echo off
set verify off
set feedback off

repheader off
repfooter off

ttitle 'Audit Execution Report'

column top_level_execution_audit_id heading Top_Level_Id
column top_level_execution_audit_id format 99999999999
column top_level_execution_audit_id truncated

column audit_id heading Audit_Id
column audit_id format 99999999999
column audit_id truncated

column stream_id heading Stream_Id
column stream_id format 99999999999
column stream_id truncated

column parent_execution_audit_id heading Parent_Id
column parent_execution_audit_id format 99999999999
column parent_execution_audit_id truncated

column execution_audit_id heading Exec_Id
column execution_audit_id format 99999999999
column execution_audit_id truncated

column execution_audit_status heading Exec_Status
column execution_audit_status format a10
column execution_audit_status truncated

column event_type heading Event_Type
column event_type format a20
column event_type truncated

column ack_type heading Ack_Type

column ack_type format a20
column ack_type truncated

column run_status heading Map_Status
column run_status format a10
column run_status truncated

column step_count heading #Steps
column step_count format 9999
column step_count truncated

column created_on heading Created_On
column created_on format a25
column created_on truncated

column execution_name heading Name
column execution_name format a25
column execution_name truncated

column task_type heading TaskType
column task_type format a10
column task_type truncated

column exec_location_name heading Exec_Loc
column exec_location_name format a8
column exec_location_name truncated

column return_result heading Result
column return_result format a4
column return_result truncated

column return_code heading Code
column return_code format 999
column return_code truncated

column number_task_errors heading #Errors
column number_task_errors format 9999
column number_task_errors truncated

column number_task_warnings heading #Warns
column number_task_warnings format 9999
column number_task_warnings truncated

column corr_id heading Stream_id
column corr_id format a12
column corr_id truncated

column msg_state heading Msg_State
column msg_state format a10
column msg_state truncated

column enq_time heading Eng_Time
column enq_time format a25
column enq_time truncated

break on top_level_execution_audit_id skip 2

spool exec_details.txt

select ex.top_level_execution_audit_id, 
       ex.parent_execution_audit_id, 
       ex.execution_audit_id,
       to_char(ex.created_on, 'YYYY/MM/DD-HH24:MI:SS') created_on, 
       ex.execution_name,
       ex.task_type,
       ex.exec_location_name,
       ex.execution_audit_status,
       mr.run_status,
       count(sr.step_id) step_count,
       ex.return_result, 
       ex.return_code,
       ex.number_task_errors, 
       ex.number_task_warnings
from 
      owbsys.all_rt_audit_executions ex,
      owbsys.all_rt_audit_map_runs mr,
      owbsys.all_rt_audit_step_runs sr
where 
      (ex.execution_audit_id=&EXEC_ID. or 
       ex.parent_execution_audit_id=&EXEC_ID. or
       ex.execution_audit_id in
          (select 
             top_level_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.) or
       ex.execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.) or
       ex.parent_execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.)
       ) and
       ex.execution_audit_id=mr.execution_audit_id(+) and
       mr.map_run_id=sr.map_run_id(+) 
group by
       ex.top_level_execution_audit_id, 
       ex.parent_execution_audit_id, 
       ex.execution_audit_id,
       to_char(ex.created_on, 'YYYY/MM/DD-HH24:MI:SS'), 
       ex.execution_name,
       ex.task_type,
       ex.exec_location_name,
       ex.execution_audit_status,
       mr.run_status,
       ex.return_result, 
       ex.return_code,
       ex.number_task_errors, 
       ex.number_task_warnings
order by
      ex.top_level_execution_audit_id,
      ex.execution_audit_id;
      
break on audit_id skip 2

ttitle 'Audit Execution Acks'

select 
    ac.audit_id,
    ac.stream_id,
    decode(ac.ack_type, 
           20001, 'NONE', 
           20002, 'RECOVERIES',  
           20003, 'ERRORS', 
           20004, 'WARNINGS', 
           ack_type) ack_type, 
    ac.ack_pending
from
    owbsys.wb_rtv_audit_notifications ac,
    owbsys.all_rt_audit_executions ex
where 
      (ex.execution_audit_id=&EXEC_ID. or 
       ex.parent_execution_audit_id=&EXEC_ID. or
       ex.execution_audit_id in
          (select 
             top_level_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.) or
       ex.execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.) or
       ex.parent_execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.)
       ) and
       ac.notification_type = 'A' and
       ex.execution_audit_id=ac.audit_id
order by
      ac.audit_id,
      ac.stream_id;


ttitle 'Audit Execution Interests'

select 
    nc.audit_id,
    nc.stream_id,
    decode(nc.event_type, 
           1001, 'UNIT_OP_START',
           1002, 'UNIT_OP_COMPLETE',
           1003, 'EXEC_OP_START',
           1004, 'EXEC_OP_COMPLETE',
           1005, 'SCRIPT_RUN_BEGIN',
           1006, 'SCRIPT_RUN_END',
           1007, 'TASK_BEGIN',
           1008, 'TASK_END',
           1009, 'FILE',
           1010, 'INFORMATIONAL',
           1011, 'WARNING',
           1012, 'ERROR',
           1013, 'RECOVERY',
           1014, 'PREPARED',
           1015, 'UNPREPARED',
           1016, 'TIMEOUT',
           event_type) event_type
from
    owbsys.wb_rtv_audit_notifications nc,
    owbsys.all_rt_audit_executions ex
where 
      (ex.execution_audit_id=&EXEC_ID. or 
       ex.parent_execution_audit_id=&EXEC_ID. or
       ex.execution_audit_id in
          (select 
             top_level_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.) or
       ex.execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.) or
       ex.parent_execution_audit_id in
          (select 
             parent_execution_audit_id
           from
             owbsys.all_rt_audit_executions
           where
             execution_audit_id=&EXEC_ID.)
       ) and
       nc.notification_type = 'C' and
       ex.execution_audit_id=nc.audit_id
order by
      nc.audit_id,
      nc.stream_id;

ttitle 'Nofity Queue Messages'

select 
    corr_id,
    msg_state, 
    to_char(enq_time, 'YYYY/MM/DD-HH24:MI:SS') enq_time
from
    owbsys.wb_rtv_queue_tabs_info
where
    queue_type = 'N' and
    corr_id in (select distinct
                      ns.stream_id
                from 
                      owbsys.wb_rtv_audit_notifications nc,
                      owbsys.wb_rtv_audit_notifications ac,
                      owbsys.wb_rtv_audit_notifications ns,
                      owbsys.all_rt_audit_executions ex
                where
                      (ex.parent_execution_audit_id=&EXEC_ID. or 
                       ex.execution_audit_id=&EXEC_ID. or
                       ex.execution_audit_id in
                          (select 
                             top_level_execution_audit_id
                           from
                             owbsys.all_rt_audit_executions
                           where
                             execution_audit_id=&EXEC_ID.) or
                       ex.execution_audit_id in
                          (select 
                             parent_execution_audit_id
                           from
                             owbsys.all_rt_audit_executions
                           where
                             execution_audit_id=&EXEC_ID.) or
                       ex.parent_execution_audit_id in
                          (select 
                             parent_execution_audit_id
                           from
                             owbsys.all_rt_audit_executions
                           where
                             execution_audit_id=&EXEC_ID.)
                      ) and
                      nc.notification_type = 'C' and
                      ac.notification_type = 'A' and
                      ns.notification_type = 'S' and
                      ((ns.stream_id=nc.stream_id and ex.execution_audit_id=nc.audit_id) or
                       (ns.stream_id=ac.stream_id and ex.execution_audit_id=ac.audit_id))
                )
order by
      corr_id,
      enq_time;

ttitle 'All Service Queue Messages'

select 
    corr_id,
    msg_state, 
    to_char(enq_time, 'YYYY/MM/DD-HH24:MI:SS') enq_time
from
    owbsys.wb_rtv_queue_tabs_info
where
    queue_type = 'S'
order by
      corr_id,
      enq_time;

spool off