REM dbdrv: none
REM HEADER
REM   $Header: wfstat.sql 26.10 2003/11/12 13:50:44 vshanmug ship $
REM NAME
REM   wfstat.sql - WorkFlow item STATus report
REM USAGE
REM   @wfstat <item_type> <item_key>
REM DESCRIPTION
REM   Displays a status report on the indicated item.
REM   150 character output
REM

set verify off
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback

col ITEM_KEY for a15 Head "Item Key"
col ITEM_TYPE for a10 Head "Item Type"
col PARENT_ITEM_KEY for a15 Head "Parent Key"
col PARENT_ITEM_TYPE for a10 Head "Parent Type"
col PARENT_CONTEXT for a15 head "Context"
col ROOT_ACTIVITY_VERSION for 99999 Head "Vers"
col BEGIN_DATE for a18 head "Begin Date"
col END_DATE for a18 head "End Date"
col ROOT_ACTIVITY for a30 head "Root Activity"
col OWNER_ROLE for a12 head "Owner Role"
col RESULT for a15
col ASSIGNED_USER for a12 head "User"
col ATTR_NAME for a30 head "Attribute Name"
col VALUE for a80 head "Value"
col STATUS for a8 Head Status
col ACTIVITY for a45 Head Activity
col ERROR_NAME for a30 head "Error Name"
col ERROR_MESSAGE for a132 head "Error Message"
col ERROR_STACK for a132 head "Error Stack"
col NID for 9999999 head "NID"
col context for a15 head "Context"
col group_id for 9999999 head "Group Id"
col mail_status for a8 head "Mail" 
col message_type for a10 head "Msg Type"
col message_name for a20 head "Message Name"
col access_key for a10 head "Access Key"
col priority for 999 head "Pri"
col due_date for a18 head "Due Date"
col callback for a15 head "Callback"
col recipient_role for a30 head "Recipient"
col responder for a30 head "Responder" 
col original_recipient for a30 head "Original Recipient"
col from_user for a30 head "From User"
col to_user for a30 head "To User"
col subject for a50 head "Subject"
col activity_name for a20 head "Activity Name"
col value_type for a10 head "Type"
col act_id for 99999999 head "Act Id"
col action for a10 head "Action"
col performed_by for a30 head "Performer"

set pagesize 999
set linesize 150
set arraysize 1

define item_type='&1'
define item_key='&2'

prompt **** WorkFlow Item
select  ITEM_TYPE,
        ITEM_KEY,
        PARENT_ITEM_TYPE,
        PARENT_ITEM_KEY,
        PARENT_CONTEXT,
        to_char(BEGIN_DATE,'DD-MON-RR HH24:MI:SS') BEGIN_DATE,
        to_char(END_DATE,'DD-MON-RR HH24:MI:SS') END_DATE,
	ROOT_ACTIVITY,
        ROOT_ACTIVITY_VERSION,
        OWNER_ROLE
from    wf_items
where   item_type = '&item_type'  
and     item_key = '&item_key'
/

prompt **** Child processes
select  ITEM_TYPE,
        ITEM_KEY,
        PARENT_ITEM_TYPE,
        PARENT_ITEM_KEY,
        PARENT_CONTEXT,
        to_char(BEGIN_DATE,'DD-MON-RR HH24:MI:SS') BEGIN_DATE,
        to_char(END_DATE,'DD-MON-RR HH24:MI:SS') END_DATE,
        ROOT_ACTIVITY,
        ROOT_ACTIVITY_VERSION,
        OWNER_ROLE
from    wf_items
where   parent_item_type = '&item_type' 
and     parent_item_key = '&item_key'
/

prompt **** Activity Statuses
select  to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        to_char(ias.end_date,'DD-MON-RR HH24:MI:SS') end_date,
        ap.name||'/'||pa.instance_label Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user assigned_user,
	ias.notification_id NID,
	ntf.status "Status",
        ias.action,
        ias.performed_by
from    wf_item_activity_statuses ias,
        wf_process_activities pa,
        wf_activities ac,
        wf_activities ap,
        wf_items i,
	wf_notifications ntf
where   ias.item_type = '&item_type'
and     ias.item_key  = '&item_key'
and     ias.process_activity    = pa.instance_id
and     pa.activity_name        = ac.name
and     pa.activity_item_type   = ac.item_type
and     pa.process_name         = ap.name
and     pa.process_item_type    = ap.item_type
and     pa.process_version      = ap.version
and     i.item_type             = '&item_type'
and     i.item_key              = ias.item_key
and     i.begin_date            >= ac.begin_date 
and     i.begin_date            < nvl(ac.end_date, i.begin_date+1)
and     ntf.notification_id(+)  = ias.notification_id
order by ias.begin_date, ias.execution_time
/

prompt **** Activity Statuses History
select  to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        ap.name||'/'||pa.instance_label Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user assigned_user,
	ias.notification_id NID,
	ntf.status "Status",
        ias.action,
        ias.performed_by
from    wf_item_activity_statuses_h ias,
        wf_process_activities pa,
        wf_activities ac,
        wf_activities ap,
        wf_items i,
	wf_notifications ntf
where   ias.item_type = '&item_type'
and     ias.item_key  = '&item_key'
and     ias.process_activity    = pa.instance_id
and     pa.activity_name        = ac.name
and     pa.activity_item_type   = ac.item_type
and     pa.process_name         = ap.name
and     pa.process_item_type    = ap.item_type
and     pa.process_version      = ap.version
and     i.item_type             = '&item_type'
and     i.item_key              = ias.item_key
and     i.begin_date            >= ac.begin_date 
and     i.begin_date            < nvl(ac.end_date, i.begin_date+1)
and     ntf.notification_id(+)  = ias.notification_id
order by ias.begin_date, ias.execution_time
/

prompt **** Notifications
select  wn.notification_id nid, 
        wn.context, 
        wn.group_id, 
        wn.status, 
        wn.mail_status, 
        wn.message_type, 
        wn.message_name, 
        wn.access_key, 
        wn.priority, 
        wn.begin_date, 
        wn.end_date, 
        wn.due_date, 
        wn.callback, 
        wn.recipient_role, 
        wn.responder, 
        wn.original_recipient, 
        wn.from_user, 
        wn.to_user, 
        wn.subject 
from    wf_notifications wn, wf_item_activity_statuses wias 
where  wn.group_id = wias.notification_id 
and  wias.item_type = '&item_type'
and  wias.item_key = '&item_key'
/

prompt **** Errored Activities
select  ac.name Activity,
        ias.activity_result_code Result,
	ias.error_name ERROR_NAME, 
	ias.error_message ERROR_MESSAGE,
	ias.error_stack ERROR_STACK
from    wf_item_activity_statuses ias,
        wf_process_activities pa,
        wf_activities ac,
        wf_activities ap,
        wf_items i
where   ias.item_type = '&item_type'
and     ias.item_key  = '&item_key'
and     ias.activity_status     = 'ERROR'
and     ias.process_activity    = pa.instance_id
and     pa.activity_name        = ac.name
and     pa.activity_item_type   = ac.item_type
and     pa.process_name         = ap.name
and     pa.process_item_type    = ap.item_type
and     pa.process_version      = ap.version
and     i.item_type             = '&item_type'
and     i.item_key              = ias.item_key
and     i.begin_date            >= ac.begin_date 
and     i.begin_date            < nvl(ac.end_date, i.begin_date+1)
order by ias.begin_date, ias.execution_time
/

prompt *** Error Process Activity Statuses
select  to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        ap.name||'/'||pa.instance_label Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user assigned_user,
	ias.notification_id NID,
	ntf.status "Status"
from    wf_item_activity_statuses ias,
        wf_process_activities pa,
        wf_activities ac,
        wf_activities ap,
        wf_items i,
	wf_notifications ntf
where   ias.item_type = i.item_type
and     ias.item_key  = i.item_key
and     ias.process_activity    = pa.instance_id
and     pa.activity_name        = ac.name
and     pa.activity_item_type   = ac.item_type
and     pa.process_name         = ap.name
and     pa.process_item_type    = ap.item_type
and     pa.process_version      = ap.version
and     i.parent_item_type      = '&item_type'
and     i.parent_item_key       = '&item_key'
and     i.begin_date            >= ac.begin_date 
and     i.begin_date            < nvl(ac.end_date, i.begin_date+1)
and     ntf.notification_id(+)  = ias.notification_id
order by ias.begin_date, ias.execution_time
/

prompt *** Error Process Activity Statuses History
select  to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        ap.name||'/'||pa.instance_label Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user assigned_user,
	ias.notification_id NID,
	ntf.status "Status"
from    wf_item_activity_statuses_h ias,
        wf_process_activities pa,
        wf_activities ac,
        wf_activities ap,
        wf_items i,
	wf_notifications ntf
where   ias.item_type = i.item_type
and     ias.item_key  = i.item_key
and     ias.process_activity    = pa.instance_id
and     pa.activity_name        = ac.name
and     pa.activity_item_type   = ac.item_type
and     pa.process_name         = ap.name
and     pa.process_item_type    = ap.item_type
and     pa.process_version      = ap.version
and     i.parent_item_type      = '&item_type'
and     i.parent_item_key       = '&item_key'
and     i.begin_date            >= ac.begin_date 
and     i.begin_date            < nvl(ac.end_date, i.begin_date+1)
and     ntf.notification_id(+)  = ias.notification_id
order by ias.begin_date, ias.execution_time
/

prompt **** Error Process Errored Activities
select  ac.name Activity,
        ias.activity_result_code Result,
	ias.error_name ERROR_NAME, 
	ias.error_message ERROR_MESSAGE,
	ias.error_stack ERROR_STACK
from    wf_item_activity_statuses ias,
        wf_process_activities pa,
        wf_activities ac,
        wf_activities ap,
        wf_items i
where   ias.item_type = i.item_type
and     ias.item_key  = i.item_key
and     ias.activity_status     = 'ERROR'
and     ias.process_activity    = pa.instance_id
and     pa.activity_name        = ac.name
and     pa.activity_item_type   = ac.item_type
and     pa.process_name         = ap.name
and     pa.process_item_type    = ap.item_type
and     pa.process_version      = ap.version
and     i.parent_item_type      = '&item_type'
and     i.parent_item_key       = '&item_key'
and     i.begin_date            >= ac.begin_date 
and     i.begin_date            < nvl(ac.end_date, i.begin_date+1)
order by ias.execution_time
/

prompt **** Item Attribute Values
select  wiav.name attr_name,
        wia.type value_type,
        nvl(wiav.text_value, nvl(to_char(wiav.number_value),to_char(wiav.date_value,'DD-MON-YYYY hh24:mi:ss'))) value
from    wf_item_attribute_values wiav,
        wf_item_attributes wia
where   wiav.item_type = '&item_type'
and     wiav.item_key  = '&item_key'
and     wia.item_type(+)  = wiav.item_type
and     wia.name(+)       = wiav.name
and     wia.type(+)       <> 'EVENT' 
/

prompt **** Event Datatype Item Attribute Values
prompt **** Note: Some versions of SQLPlus cannot display complex Datatypes
select  wiav.name attr_name,
        wiav.event_value value 
from    wf_item_attribute_values wiav,
        wf_item_attributes wia
where   wiav.item_type = '&item_type'
and     wiav.item_key  = '&item_key'
and     wia.item_type  = wiav.item_type
and     wia.name       = wiav.name
and     wia.type       = 'EVENT'
/

prompt **** Activity Attribute Values
SELECT wa.display_name activity_name,
       wpa.instance_id act_id,
       wav.name        attr_name,
       wav.value_type,
       nvl(wav.text_value, nvl(to_char(wav.number_value),to_char(wav.date_value,'DD-MON-YYYY hh24:mi:ss'))) value
FROM   wf_item_activity_statuses wias,
       wf_process_activities     wpa,
       wf_items                  wi,
       wf_activity_attr_values   wav,
       wf_activities_vl          wa
WHERE  wi.item_type = '&item_type'
AND    wi.item_key = '&item_key'
AND    wias.item_type = wi.item_type
AND    wias.item_key = wi.item_key
AND    wias.process_activity = wpa.instance_id
AND    wav.process_activity_id = wpa.instance_id
AND    wpa.activity_name = wa.name
AND    wpa.activity_item_type = wa.item_type
AND    wa.begin_date <= wi.begin_date
AND    nvl(wa.end_date, sysdate) > wi.begin_date
/
set verify on
exit;