REM +======================================================================+
REM | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM     wfmonv.sql - WorkFlow Monitor Views
REM +======================================================================+

set verify off
define hdr = "$Header: wfmonv.sql 26.1 2000/10/31 23:24:20 troveda ship $"

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

CREATE OR REPLACE FORCE VIEW wf_item_activities_history_v
(
item_type,
item_key,
activity_def_begin_date,
activity_def_end_date,
begin_date,
end_date,
execution_time,
begin_date_time,
duration,
activity_item_type,
activity_type,
parent_activity_name,
activity_name,
activity_display_name,
parent_display_name,
activity_status,
notification_status,
notification_id,
recipient_role,
recipient_role_name,
activity_status_display,
result
)
AS select  /* $Header: wfmonv.sql 26.1 2000/10/31 23:24:20 troveda ship $ */
           ias.item_type,
           ias.item_key,
           a.begin_date activity_def_begin_date,
           a.end_date   activity_def_end_date,
           ias.begin_date begin_date,
           ias.end_date end_date,
           ias.execution_time,
           to_char(ias.begin_date) || ' ' 
                 || to_char(ias.begin_date, 'HH24:MI:SS')
                begin_date_time,
           DECODE(ias.activity_status,
                            'COMPLETE', (ias.end_date - ias.begin_date) * 86400,
                            'CANCEL',   (ias.end_date - ias.begin_date) * 86400,
                            'ERROR',    (ias.end_date - ias.begin_date) * 86400,
                                        (sysdate - ias.begin_date) * 86400)
               duration,
           pa.activity_item_type,
           a.type activity_type,
           par.name         parent_activity_name,
           a.name           activity_name,
           a.display_name   activity_display_name,
           par.display_name parent_display_name,
           DECODE(ias.activity_status, 
                                 'DEFERRED', 'ACTIVE',
                                 'WAITING',  'ACTIVE',
                                 'NOTIFIED', 'ACTIVE',
                                 ias.activity_status) 
               activity_status,
           n.status            notification_status,
           ias.notification_id,
           DECODE(a.type, 'NOTICE', 
                   n.recipient_role, 
                   'WF_ENGINE') 
               recipient_role,
           DECODE(a.type, 'NOTICE',
                  wf_directory.getroledisplayname(n.recipient_role),
                  wf_core.translate('WFMON_WF_ENGINE')) 
               recipient_role_name,
           wf_core.translate (DECODE(ias.activity_status, 
                                 'DEFERRED', 'ACTIVE',
                                 'WAITING',  'ACTIVE',
                                 'NOTIFIED', 'ACTIVE',
                                 ias.activity_status))
               activity_status_display,
           wf_core.activity_result(a.result_type, 
               DECODE(ias.activity_result_code, '#NULL', null, 
                      ias.activity_result_code)) 
               result
  from wf_item_activity_statuses ias,
         wf_notifications n,
         wf_activities_vl a,
         wf_activities_vl par,
         wf_process_activities pa
  where ias.process_activity = pa.instance_id
  and   pa.activity_item_type = a.item_type
  and   pa.activity_name = a.name
  and   pa.process_name         = par.name
  and   pa.process_item_type    = par.item_type
  and   pa.process_version      = par.version
  and   n.group_id(+)  = ias.notification_id
  union all
select     ias.item_type,
           ias.item_key,
           a.begin_date activity_def_begin_date,
           a.end_date   activity_def_end_date,
           ias.begin_date begin_date,
           ias.end_date end_date,
           ias.execution_time,
           to_char(ias.begin_date) || ' ' 
                 || to_char(ias.begin_date, 'HH24:MI:SS')
                begin_date_time,
           DECODE(ias.activity_status,
                            'COMPLETE', (ias.end_date - ias.begin_date) * 86400,
                            'CANCEL',   (ias.end_date - ias.begin_date) * 86400,
                            'ERROR',    (ias.end_date - ias.begin_date) * 86400,
                                        (sysdate - ias.begin_date) * 86400)
               duration,
           pa.activity_item_type,
           a.type activity_type,
           par.name         parent_activity_name,
           a.name           activity_name,
           a.display_name   activity_display_name,
           par.display_name parent_display_name,
           DECODE(ias.activity_status, 
                                 'DEFERRED', 'ACTIVE',
                                 'WAITING',  'ACTIVE',
                                 'NOTIFIED', 'ACTIVE',
                                 ias.activity_status) 
               activity_status,
           n.status            notification_status,
           ias.notification_id,
           DECODE(a.type, 'NOTICE', 
                   n.recipient_role, 
                   'WF_ENGINE') 
               recipient_role,
           DECODE(a.type, 'NOTICE',
                  wf_directory.getroledisplayname(n.recipient_role),
                  wf_core.translate('WFMON_WF_ENGINE')) 
               recipient_role_name,
           wf_core.translate (DECODE(ias.activity_status, 
                                 'DEFERRED', 'ACTIVE',
                                 'WAITING',  'ACTIVE',
                                 'NOTIFIED', 'ACTIVE',
                                 ias.activity_status))
               activity_status_display,
           wf_core.activity_result(a.result_type, 
               DECODE(ias.activity_result_code, '#NULL', null, 
                      ias.activity_result_code)) 
               result
  from wf_item_activity_statuses_h ias,
         wf_notifications n,
         wf_activities_vl a,
         wf_activities_vl par,
         wf_process_activities pa
  where ias.process_activity = pa.instance_id
  and   pa.activity_item_type = a.item_type
  and   pa.activity_name = a.name
  and   pa.process_name         = par.name
  and   pa.process_item_type    = par.item_type
  and   pa.process_version      = par.version
  and   n.group_id(+)  = ias.notification_id;

commit;
exit;