rem HEADER
rem   $Header: wfstatus.sql 26.1 2001/11/05 20:21:38 ctilley ship $
rem NAME
rem   wfstatus.sql - WorkFlow item STATUS report
rem USAGE
rem   @wfstatus <item_type> <item_key>
rem DESCRIPTION
rem   Displays a status report on the indicated item.
rem   132 charachter output
rem 
rem   dbdrv: none

col ITEM_KEY for a15 Head "Item Key"
col ITEM_TYPE for a10 Head "Item Type"
col PARENT_ITEM_KEY for a10
col ROOT_ACTIVITY for a10
col ACTIVITY_RESULT_CODE for a15
col ASSIGNED_USER for a10
col ATTR_NAME for a30 head "Attribute Name"
col VALUE for a30 head "Value"
col STATUS for a8 Head Status
col RESULT for a15 head Result
col ASS_USER for a15 Head User
col ACTIVITY for a45 Head Activity
col BEGIN_DATE for a18 head "Begin Date"
col END_DATE for a18 head "End Date"
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 EXECUTION_TIME noprint
--
set verify off
set pagesize 999
set linesize 132
set arraysize 1
--
define item_type='&1'
define item_key='&2'
--

prompt **** WorkFlow Item
select  ITEM_TYPE,
        ITEM_KEY,
        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 Activity
from    wf_items
where   item_type = upper('&item_type') and item_key = '&item_key'
/

prompt **** Activity Statuses
select  execution_time, 
	to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        ap.display_name||'/'||ac.display_name Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user ass_User
from    wf_item_activity_statuses ias,
        wf_process_activities pa,
        wf_activities_vl ac,
        wf_activities_vl ap,
        wf_items i
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)
union all
select  execution_time, 
	to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        ap.display_name||'/'||ac.display_name Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user ass_User
from    wf_item_activity_statuses_h ias,
        wf_process_activities pa,
        wf_activities_vl ac,
        wf_activities_vl ap,
        wf_items i
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)
order by 2,1
/

prompt **** Errored Activities
select  ac.display_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_vl ac,
        wf_activities_vl 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.execution_time
/

prompt *** Error Process Activity Statuses
select  execution_time, 
	to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        ap.display_name||'/'||ac.display_name Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user ass_User
from    wf_item_activity_statuses ias,
        wf_process_activities pa,
        wf_activities_vl ac,
        wf_activities_vl ap,
        wf_items i
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)
union all
select  execution_time, 
	to_char(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
        ap.display_name||'/'||ac.display_name Activity,
        ias.activity_status Status,
        ias.activity_result_code Result,
        ias.assigned_user ass_User
from    wf_item_activity_statuses_h ias,
        wf_process_activities pa,
        wf_activities_vl ac,
        wf_activities_vl ap,
        wf_items i
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)
order by 2,1
/

prompt **** Error Process Errored Activities
select  ac.display_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_vl ac,
        wf_activities_vl 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 **** Attribute Values
select  name attr_name,
        nvl(text_value, nvl(to_char(number_value),to_char(date_value))) value
from    wf_item_attribute_values
where   item_type = upper('&item_type')
and     item_key  = nvl('&item_key',item_key)
/
set verify on

commit;
exit;