-- -- acs-workflow/sql/workflow-case-package.sql -- -- Creates the PL/SQL package that provides the API for interacting -- with a workflow case. -- -- @author Lars Pind (lars@pinds.com) -- -- @creation-date 2000-05-18 -- -- @cvs-id $Id: workflow-case-package.sql,v 1.13 2001/08/24 14:46:34 vinodk Exp $ -- -- create or replace package workflow_case -- is -- -- function new ( -- case_id in integer default null, -- workflow_key in varchar2, -- context_key in varchar2 default null, -- object_id in integer, -- creation_date in date default sysdate, -- creation_user in integer default null, -- creation_ip in varchar2 default null -- ) return integer; -- -- procedure add_manual_assignment ( -- case_id in integer, -- transition_key in varchar2, -- party_id in integer -- ); -- -- procedure remove_manual_assignment ( -- case_id in integer, -- transition_key in varchar2, -- party_id in integer -- ); -- -- procedure clear_manual_assignments ( -- case_id in integer, -- transition_key in varchar2 -- ); -- -- procedure start_case ( -- case_id in integer, -- creation_user in integer default null, -- creation_ip in varchar2 default null, -- msg in varchar2 default null -- ); -- -- procedure delete( -- case_id in integer -- ); -- -- procedure suspend( -- case_id in integer, -- user_id in integer default null, -- ip_address in varchar2 default null, -- msg in varchar2 default null -- ); -- -- procedure resume( -- case_id in integer, -- user_id in integer default null, -- ip_address in varchar2 default null, -- msg in varchar2 default null -- ); -- -- procedure cancel( -- case_id in integer, -- user_id in integer default null, -- ip_address in varchar2 default null, -- msg in varchar2 default null -- ); -- -- procedure fire_message_transition ( -- task_id in integer -- ); -- -- /* To perform an action on the workflow: -- * (integers in parenthesis is the integer of times each function should get called) -- * -- * 1. begin_task_action (1) -- * 2. set_attribute_value (0..*) -- * 3. clear_manual_assignments (0..1) -- * 4. add_manual_assignment (0..*) -- * 5. end_task_action (1) -- */ -- function begin_task_action ( -- task_id in integer, -- action in varchar2, -- action_ip in varchar2, -- user_id in integer, -- msg in varchar2 default null -- ) return integer; -- -- procedure set_attribute_value ( -- journal_id in integer, -- attribute_name in varchar2, -- value in varchar2 -- ); -- -- procedure end_task_action ( -- journal_id in integer, -- action in varchar2, -- task_id in integer -- ); -- -- /* Shortcut, that does both begin and end, when you have no attributes to set or assignments to make */ -- function task_action ( -- task_id in integer, -- action in varchar2, -- action_ip in varchar2, -- user_id in integer, -- msg in varchar2 default null -- ) return integer; -- -- function get_attribute_value ( -- case_id in integer, -- attribute_name in varchar2 -- ) return varchar2; -- -- procedure add_task_assignment ( -- task_id in integer, -- party_id in integer -- ); -- -- procedure remove_task_assignment ( -- task_id in integer, -- party_id in integer -- ); -- -- procedure clear_task_assignments ( -- task_id in integer -- ); -- -- -- /* DBMS_JOBS */ -- -- procedure sweep_timed_transitions; -- -- procedure sweep_hold_timeout; -- -- end workflow_case; -- show errors -- create or replace package body workflow_case -- function new create function workflow_case__new (integer,varchar,varchar,integer,timestamp,integer,varchar) returns integer as ' declare new__case_id alias for $1; -- default null new__workflow_key alias for $2; new__context_key alias for $3; -- default null new__object_id alias for $4; new__creation_date alias for $5; -- default now() new__creation_user alias for $6; -- default null new__creation_ip alias for $7; -- default null v_case_id integer; v_workflow_case_table varchar; v_context_key_for_query varchar; begin if new__context_key = '''' or new__context_key is null then v_context_key_for_query := ''default''; else v_context_key_for_query := new__context_key; end if; /* insert a row into acs_objects */ v_case_id := acs_object__new( new__case_id, new__workflow_key, new__creation_date, new__creation_user, new__creation_ip, null ); /* insert the case in to the general wf_cases table */ insert into wf_cases (case_id, workflow_key, context_key, object_id, state) values (v_case_id, new__workflow_key, v_context_key_for_query, new__object_id, ''created''); /* insert the case into the workflow-specific cases table */ select table_name into v_workflow_case_table from acs_object_types where object_type = new__workflow_key; execute ''insert into '' || v_workflow_case_table || '' (case_id) values ('' || v_case_id || '')''; return v_case_id; end;' language 'plpgsql'; -- procedure add_manual_assignment create function workflow_case__add_manual_assignment (integer,varchar,integer) returns integer as ' declare add_manual_assignment__case_id alias for $1; add_manual_assignment__transition_key alias for $2; add_manual_assignment__party_id alias for $3; v_workflow_key varchar; begin select workflow_key into v_workflow_key from wf_cases where case_id = add_manual_assignment__case_id; insert into wf_case_assignments (case_id, workflow_key, transition_key, party_id) values (add_manual_assignment__case_id, v_workflow_key, add_manual_assignment__transition_key, add_manual_assignment__party_id); return 0; end;' language 'plpgsql'; -- procedure remove_manual_assignment create function workflow_case__remove_manual_assignment (integer,varchar,integer) returns integer as ' declare remove_manual_assignment__case_id alias for $1; remove_manual_assignment__transition_key alias for $2; remove_manual_assignment__party_id alias for $3; begin delete from wf_case_assignments where workflow_key = (select workflow_key from wf_cases where case_id = remove_manual_assignment__case_id) and case_id = remove_manual_assignment__case_id and transition_key = remove_manual_assignment__transition_key and party_id = remove_manual_assignment__party_id; return 0; end;' language 'plpgsql'; -- procedure clear_manual_assignments create function workflow_case__clear_manual_assignments (integer,varchar) returns integer as ' declare clear_manual_assignments__case_id alias for $1; clear_manual_assignments__transition_key alias for $2; begin delete from wf_case_assignments where workflow_key = (select workflow_key from wf_cases where case_id = clear_manual_assignments__case_id) and case_id = clear_manual_assignments__case_id and transition_key = clear_manual_assignments__transition_key; return 0; end;' language 'plpgsql'; -- procedure start_case create function workflow_case__start_case (integer,integer,varchar,varchar) returns integer as ' declare start_case__case_id alias for $1; start_case__creation_user alias for $2; -- default null start_case__creation_ip alias for $3; -- default null start_case__msg alias for $4; -- default null v_journal_id integer; begin /* Add an entry to the journal */ v_journal_id := journal_entry__new( null, start_case__case_id, ''case start'', ''Case started'', now(), start_case__creation_user, start_case__creation_ip, start_case__msg ); update wf_cases set state = ''active'' where case_id = start_case__case_id; PERFORM workflow_case__add_token( start_case__case_id, ''start'', v_journal_id ); /* Turn the wheels */ PERFORM workflow_case__sweep_automatic_transitions( start_case__case_id, v_journal_id ); return 0; end;' language 'plpgsql'; -- procedure delete create function workflow_case__delete (integer) returns integer as ' declare delete__case_id alias for $1; v_workflow_case_table varchar; begin /* delete attribute_value_audit, tokens, tasks */ delete from wf_attribute_value_audit where case_id = delete__case_id; delete from wf_case_assignments where case_id = delete__case_id; delete from wf_case_deadlines where case_id = delete__case_id; delete from wf_tokens where case_id = delete__case_id; delete from wf_task_assignments where task_id in (select task_id from wf_tasks where case_id = delete__case_id); delete from wf_tasks where case_id = delete__case_id; /* delete the journal */ PERFORM journal_entry__delete_for_object(delete__case_id); /* delete from the workflow-specific cases table */ select table_name into v_workflow_case_table from acs_object_types ot, wf_cases c where c.case_id = delete__case_id and object_type = c.workflow_key; execute ''delete from '' || v_workflow_case_table || '' where case_id = '' || delete__case_id; /* delete from the generic cases table */ delete from wf_cases where case_id = delete__case_id; /* delete from acs-objects */ PERFORM acs_object__delete(delete__case_id); return 0; end;' language 'plpgsql'; -- procedure suspend create function workflow_case__suspend (integer,integer,varchar,varchar) returns integer as ' declare suspend__case_id alias for $1; suspend__user_id alias for $2; -- default null suspend__ip_address alias for $3; -- default null suspend__msg alias for $4; -- default null v_state varchar; v_journal_id integer; begin select state into v_state from wf_cases where case_id = suspend__case_id; if v_state != ''active'' then raise EXCEPTION ''-20000: Only active cases can be suspended''; end if; /* Add an entry to the journal */ v_journal_id := journal_entry__new( null, suspend__case_id, ''case suspend'', ''case suspended'', now(), suspend__user_id, suspend__ip_address, suspend__msg ); update wf_cases set state = ''suspended'' where case_id = suspend__case_id; return 0; end;' language 'plpgsql'; -- procedure resume create function workflow_case__resume (integer,integer,varchar,varchar) returns integer as ' declare resume__case_id alias for $1; resume__user_id alias for $2; -- default null resume__ip_address alias for $3; -- default null resume__msg alias for $4; -- default null v_state varchar; v_journal_id integer; begin select state into v_state from wf_cases where case_id = resume__case_id; if v_state != ''suspended'' and v_state != ''canceled'' then raise EXCEPTION ''-20000: Only suspended or canceled cases can be resumed''; end if; /* Add an entry to the journal */ v_journal_id := journal_entry__new( null, resume__case_id, ''case resume'', ''case resumed'', now(), resume__user_id, resume__ip_address, resume__msg ); update wf_cases set state = ''active'' where case_id = resume__case_id; return 0; end;' language 'plpgsql'; -- procedure cancel create function workflow_case__cancel (integer,integer,varchar,varchar) returns integer as ' declare cancel__case_id alias for $1; cancel__user_id alias for $2; -- default null cancel__ip_address alias for $3; -- default null cancel__msg alias for $4; -- default null v_state varchar; v_journal_id integer; begin select state into v_state from wf_cases where case_id = cancel__case_id; if v_state != ''active'' and v_state != ''suspended'' then raise EXCEPTION ''-20000: Only active or suspended cases can be canceled''; end if; /* Add an entry to the journal */ v_journal_id := journal_entry__new( null, cancel__case_id, ''case cancel'', ''Case canceled'', now(), cancel__user_id, cancel__ip_address, cancel__msg ); update wf_cases set state = ''canceled'' where case_id = cancel__case_id; return 0; end;' language 'plpgsql'; -- procedure fire_message_transition create function workflow_case__fire_message_transition (integer) returns integer as ' declare fire_message_transition__task_id alias for $1; v_case_id integer; v_transition_name varchar; v_trigger_type varchar; v_journal_id integer; begin select t.case_id, tr.transition_name, tr.trigger_type into v_case_id, v_transition_name, v_trigger_type from wf_tasks t, wf_transitions tr where t.task_id = fire_message_transition__task_id and tr.workflow_key = t.workflow_key and tr.transition_key = t.transition_key; if v_trigger_type != ''message'' then raise EXCEPTION ''-20000: Transition "'' || v_transition_name || ''" is not message triggered''; end if; /* Add an entry to the journal */ v_journal_id := journal_entry__new( null, v_case_id, ''task '' || fire_message_transition__task_id || '' fire'', v_transition_name || '' fired'', now(), null, null, null ); PERFORM workflow_case__fire_transition_internal( fire_message_transition__task_id, v_journal_id ); PERFORM workflow_case__sweep_automatic_transitions( v_case_id, v_journal_id ); return 0; end;' language 'plpgsql'; -- function begin_task_action create function workflow_case__begin_task_action (integer,varchar,varchar,integer,varchar) returns integer as ' declare begin_task_action__task_id alias for $1; begin_task_action__action alias for $2; begin_task_action__action_ip alias for $3; begin_task_action__user_id alias for $4; begin_task_action__msg alias for $5; -- default null v_state varchar; v_journal_id integer; v_case_id integer; v_transition_name varchar; v_num_rows integer; begin select state into v_state from wf_tasks where task_id = begin_task_action__task_id; if begin_task_action__action = ''start'' then if v_state != ''enabled'' then raise EXCEPTION ''-20000: Task is in state "%", but it must be in state "enabled" to be started.'', v_state; end if; select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_user_tasks where task_id = begin_task_action__task_id and user_id = begin_task_action__user_id; if v_num_rows = 0 then raise EXCEPTION ''-20000: You are not assigned to this task.''; end if; else if begin_task_action__action = ''finish'' or begin_task_action__action = ''cancel'' then if v_state = ''started'' then /* Is this user the holding user? */ select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_tasks where task_id = begin_task_action__task_id and holding_user = begin_task_action__user_id; if v_num_rows = 0 then raise EXCEPTION ''-20000: You are the user currently working on this task.''; end if; else if v_state = ''enabled'' then if begin_task_action__action = ''cancel'' then raise EXCEPTION ''-20000: You can only cancel a task in state "started", but this task is in state "%"'', v_state; end if; /* Is this user assigned to this task? */ select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_user_tasks where task_id = begin_task_action__task_id and user_id = begin_task_action__user_id; if v_num_rows = 0 then raise EXCEPTION ''-20000: You are not assigned to this task.''; end if; /* This task is finished without an explicit start. * Store the user as the holding_user */ update wf_tasks set holding_user = begin_task_action__user_id where task_id = begin_task_action__task_id; else raise EXCEPTION ''-20000: Task is in state "%", but it must be in state "enabled" or "started" to be finished'', v_state; end if; end if; else if begin_task_action__action = ''comment'' then -- We currently allow anyone to comment on a task -- (need this line because PL/SQL does not like empty if blocks) v_num_rows := 0; end if; end if; end if; select t.case_id, tr.transition_name into v_case_id, v_transition_name from wf_tasks t, wf_transitions tr where t.task_id = begin_task_action__task_id and tr.workflow_key = t.workflow_key and tr.transition_key = t.transition_key; /* Insert a journal entry */ v_journal_id := journal_entry__new( null, v_case_id, ''task '' || begin_task_action__task_id || '' '' || begin_task_action__action, v_transition_name || '' '' || begin_task_action__action, now(), begin_task_action__user_id, begin_task_action__action_ip, begin_task_action__msg ); return v_journal_id; end;' language 'plpgsql'; -- procedure end_task_action create function workflow_case__end_task_action (integer,varchar,integer) returns integer as ' declare end_task_action__journal_id alias for $1; end_task_action__action alias for $2; end_task_action__task_id alias for $3; v_user_id integer; begin select creation_user into v_user_id from acs_objects where object_id = end_task_action__journal_id; /* Update the workflow state */ if end_task_action__action = ''start'' then PERFORM workflow_case__start_task(end_task_action__task_id, v_user_id, end_task_action__journal_id ); else if end_task_action__action = ''finish'' then PERFORM workflow_case__finish_task(end_task_action__task_id, end_task_action__journal_id ); else if end_task_action__action = ''cancel'' then PERFORM workflow_case__cancel_task(end_task_action__task_id, end_task_action__journal_id ); else if end_task_action__action != ''comment'' then raise EXCEPTION ''-20000: Unknown action "%"'', end_task_action__action; end if; end if; end if; end if; return 0; end;' language 'plpgsql'; -- function task_action create function workflow_case__task_action (integer,varchar,varchar,integer,varchar) returns integer as ' declare task_action__task_id alias for $1; task_action__action alias for $2; task_action__action_ip alias for $3; task_action__user_id alias for $4; task_action__msg alias for $5; -- default null v_journal_id integer; begin v_journal_id := workflow_case__begin_task_action( task_action__task_id, task_action__action, task_action__action_ip, task_action__user_id, task_action__msg ); PERFORM workflow_case__end_task_action( v_journal_id, task_action__action, task_action__task_id ); return v_journal_id; end;' language 'plpgsql'; -- procedure set_attribute_value create function workflow_case__set_attribute_value (integer,varchar,varchar) returns integer as ' declare set_attribute_value__journal_id alias for $1; set_attribute_value__attribute_name alias for $2; set_attribute_value__value alias for $3; v_workflow_key varchar; v_case_id integer; v_attribute_id integer; begin select o.object_type, o.object_id into v_workflow_key, v_case_id from journal_entries je, acs_objects o where je.journal_id = set_attribute_value__journal_id and o.object_id = je.object_id; select attribute_id into v_attribute_id from acs_attributes where object_type = v_workflow_key and attribute_name = set_attribute_value__attribute_name; PERFORM acs_object__set_attribute( v_case_id, set_attribute_value__attribute_name, set_attribute_value__value ); insert into wf_attribute_value_audit (case_id, attribute_id, journal_id, attr_value) values (v_case_id, v_attribute_id, set_attribute_value__journal_id, set_attribute_value__value); return 0; end;' language 'plpgsql'; -- function get_attribute_value create function workflow_case__get_attribute_value (integer,varchar) returns varchar as ' declare get_attribute_value__case_id alias for $1; get_attribute_value__attribute_name alias for $2; begin return acs_object__get_attribute( get_attribute_value__case_id, get_attribute_value__attribute_name ); end;' language 'plpgsql'; -- procedure add_task_assignment create function workflow_case__add_task_assignment (integer,integer) returns integer as ' declare add_task_assignment__task_id alias for $1; add_task_assignment__party_id alias for $2; v_count integer; v_workflow_key wf_workflows.workflow_key%TYPE; v_context_key wf_contexts.context_key%TYPE; v_case_id wf_cases.case_id%TYPE; v_transition_key wf_transitions.transition_key%TYPE; v_notification_callback wf_context_transition_info.notification_callback%TYPE; v_notification_custom_arg wf_context_transition_info.notification_custom_arg%TYPE; callback_rec record; v_assigned_user record; begin -- check that we do not hit the unique constraint select count(*) into v_count from wf_task_assignments where task_id = add_task_assignment__task_id and party_id = add_task_assignment__party_id; if v_count > 0 then return null; end if; -- get the notification callback info select case_id, workflow_key, transition_key into v_case_id, v_workflow_key, v_transition_key from wf_tasks where task_id = add_task_assignment__task_id; select context_key into v_context_key from wf_cases where case_id = v_case_id; select notification_callback, notification_custom_arg into callback_rec from wf_context_transition_info where context_key = v_context_key and workflow_key = v_workflow_key and transition_key = v_transition_key; if FOUND then v_notification_callback := callback_rec.notification_callback; v_notification_custom_arg := callback_rec.notification_custom_arg; else v_notification_callback := null; v_notification_custom_arg := null; end if; -- notify any new assignees for v_assigned_user in select distinct u.user_id from users u where u.user_id not in ( select distinct u2.user_id from wf_task_assignments tasgn2, party_approved_member_map m2, users u2 where tasgn2.task_id = add_task_assignment__task_id and m2.party_id = tasgn2.party_id and u2.user_id = m2.member_id) and exists ( select 1 from party_approved_member_map m where m.member_id = u.user_id and m.party_id = add_task_assignment__party_id ) LOOP PERFORM workflow_case__notify_assignee( add_task_assignment__task_id, v_assigned_user.user_id, v_notification_callback, v_notification_custom_arg ); end loop; -- do the insert insert into wf_task_assignments (task_id, party_id) values (add_task_assignment__task_id, add_task_assignment__party_id); return 0; end;' language 'plpgsql'; -- procedure remove_task_assignment create function workflow_case__remove_task_assignment (integer,integer) returns integer as ' declare remove_task_assignment__task_id alias for $1; remove_task_assignment__party_id alias for $2; v_num_assigned integer; v_case_id integer; v_workflow_key varchar; v_transition_key varchar; v_context_key varchar; callback_rec record; begin delete from wf_task_assignments where task_id = remove_task_assignment__task_id and party_id = remove_task_assignment__party_id; select count(*) into v_num_assigned from wf_task_assignments where task_id = remove_task_assignment__task_id; if v_num_assigned > 0 then return null; end if; select case_id, transition_key into v_case_id, v_transition_key from wf_tasks where task_id = remove_task_assignment__task_id; select workflow_key, context_key into v_workflow_key, v_context_key from wf_cases where case_id = v_case_id; select unassigned_callback, unassigned_custom_arg into callback_rec from wf_context_transition_info where workflow_key = v_workflow_key and context_key = v_context_key and transition_key = v_transition_key; if FOUND then PERFORM workflow_case__execute_unassigned_callback ( callback_rec.unassigned_callback, remove_task_assignment__task_id, callback_rec.unassigned_custom_arg ); end if; return 0; end;' language 'plpgsql'; -- procedure clear_task_assignments create function workflow_case__clear_task_assignments (integer) returns integer as ' declare clear_task_assignments__task_id alias for $1; v_case_id integer; v_transition_key varchar; v_workflow_key varchar; v_context_key varchar; v_callback varchar; v_custom_arg varchar; begin delete from wf_task_assignments where task_id = clear_task_assignments__task_id; select case_id, transition_key into v_case_id, v_transition_key from wf_tasks where task_id = clear_task_assignments__task_id; select workflow_key, context_key into v_workflow_key, v_context_key from wf_cases where case_id = v_case_id; select unassigned_callback, unassigned_custom_arg into v_callback, v_custom_arg from wf_context_transition_info where workflow_key = v_workflow_key and context_key = v_context_key and transition_key = v_transition_key; PERFORM workflow_case__execute_unassigned_callback ( v_callback, clear_task_assignments__task_id, v_custom_arg ); return 0; end;' language 'plpgsql'; -- function evaluate_guard create function workflow_case__evaluate_guard (varchar,varchar,integer,varchar,varchar,varchar,varchar) returns boolean as ' declare evaluate_guard__callback alias for $1; evaluate_guard__custom_arg alias for $2; evaluate_guard__case_id alias for $3; evaluate_guard__workflow_key alias for $4; evaluate_guard__transition_key alias for $5; evaluate_guard__place_key alias for $6; evaluate_guard__direction alias for $7; v_guard_happy_p boolean; v_rec record; v_str text default ''''; begin if evaluate_guard__callback = '''' or evaluate_guard__callback is null then -- null guard evaluates to true return ''t''; else if evaluate_guard__callback = ''#'' then return ''f''; else v_str := ''select '' || evaluate_guard__callback || ''('' || evaluate_guard__case_id || '','' || quote_literal(evaluate_guard__workflow_key) || '','' || quote_literal(evaluate_guard__transition_key) || '','' || quote_literal(evaluate_guard__place_key) || '','' || quote_literal(evaluate_guard__direction) || '','' || coalesce(quote_literal(evaluate_guard__custom_arg),''null'') || '') as guard_happy_p''; raise notice ''str = %'', v_str; for v_rec in execute v_str LOOP return v_rec.guard_happy_p; end LOOP; end if; end if; return null; end;' language 'plpgsql'; -- procedure execute_transition_callback create function workflow_case__execute_transition_callback (varchar,varchar,integer,varchar) returns integer as ' declare execute_transition_callback__callback alias for $1; execute_transition_callback__custom_arg alias for $2; execute_transition_callback__case_id alias for $3; execute_transition_callback__transition_key alias for $4; v_str text; begin if execute_transition_callback__callback != '''' and execute_transition_callback__callback is not null then v_str := ''select '' || execute_transition_callback__callback || ''('' || execute_transition_callback__case_id || '','' || quote_literal(execute_transition_callback__transition_key) || '','' || coalesce(quote_literal(execute_transition_callback__custom_arg),''null'') || '')''; execute v_str; end if; return 0; end;' language 'plpgsql'; -- function execute_time_callback create function workflow_case__execute_time_callback (varchar,varchar,integer,varchar) returns timestamp as ' declare execute_time_callback__callback alias for $1; execute_time_callback__custom_arg alias for $2; execute_time_callback__case_id alias for $3; execute_time_callback__transition_key alias for $4; v_rec record; v_str text; begin if execute_time_callback__callback = '''' or execute_time_callback__callback is null then raise EXCEPTION ''-20000: There''''s no time_callback function for the timed transition "%"'', execute_time_callback__transition_key; end if; v_str := ''select '' || execute_time_callback__callback || ''('' || execute_time_callback__case_id || '','' || quote_literal(execute_time_callback__transition_key) || '','' || coalesce(quote_literal(execute_time_callback__custom_arg),''null'') || '') as trigger_time'' for v_rec in execute v_str LOOP return v_rec.trigger_time; end LOOP; return null; end;' language 'plpgsql'; -- function get_task_deadline create function workflow_case__get_task_deadline (varchar,varchar,varchar,integer,varchar) returns timestamp as ' declare get_task_deadline__callback alias for $1; get_task_deadline__custom_arg alias for $2; get_task_deadline__attribute_name alias for $3; get_task_deadline__case_id alias for $4; get_task_deadline__transition_key alias for $5; v_deadline timestamp; v_rec record; v_str varchar; begin /* * 1. or if there is a row in wf_case_deadlines, we use that * 2. if there is a callback, we execute that * 3. otherwise, if there is an attribute, we use that */ /* wf_case_deadlines */ select deadline into v_deadline from wf_case_deadlines where case_id = get_task_deadline__case_id and transition_key = get_task_deadline__transition_key; if NOT FOUND then if get_task_deadline__callback != '''' and get_task_deadline__callback is not null then /* callback */ v_str := ''select '' || get_task_deadline__callback || ''('' || get_task_deadline__case_id || '','' || quote_literal(get_task_deadline__transition_key) || '','' || coalesce(quote_literal(get_task_deadline__custom_arg),''null'') || '') as deadline''; for v_rec in execute v_str LOOP v_deadline := v_rec.deadline; exit; end LOOP; else if get_task_deadline__attribute_name != '''' and get_task_deadline__attribute_name is not null then /* attribute */ v_deadline := acs_object__get_attribute( get_task_deadline__case_id, get_task_deadline__attribute_name ); else v_deadline := null; end if; end if; end if; return v_deadline; end;' language 'plpgsql'; -- function execute_hold_timeout_callback create function workflow_case__execute_hold_timeout_callback (varchar,varchar,integer,varchar) returns timestamp as ' declare execute_hold_timeout_callback__callback alias for $1; execute_hold_timeout_callback__custom_arg alias for $2; execute_hold_timeout_callback__case_id alias for $3; execute_hold_timeout_callback__transition_key alias for $4; v_hold_timeout timestamp; v_rec record; v_str text; begin if execute_hold_timeout_callback__callback = '''' or execute_hold_timeout_callback__callback is null then return null; end if; v_str := ''select '' || execute_hold_timeout_callback__callback || ''('' || execute_hold_timeout_callback__case_id || '','' || quote_literal(execute_hold_timeout_callback__transition_key) || '','' || coalesce(quote_literal(execute_hold_timeout_callback__custom_arg),''null'') || '') as hold_timeout''; for v_rec in execute v_str LOOP return v_rec.hold_timeout; end LOOP; return null; end;' language 'plpgsql'; -- procedure execute_unassigned_callback create function workflow_case__execute_unassigned_callback (varchar,integer,varchar) returns integer as ' declare callback alias for $1; task_id alias for $2; custom_arg alias for $3; v_str text; begin if callback != '''' and callback is not null then v_str := ''select '' || callback || ''('' || task_id || '','' || coalesce(quote_literal(custom_arg),''null'') || '')''; execute v_str; end if; return 0; end;' language 'plpgsql'; -- procedure set_task_assignments create function workflow_case__set_task_assignments (integer,varchar,varchar) returns integer as ' declare set_task_assignments__task_id alias for $1; set_task_assignments__callback alias for $2; set_task_assignments__custom_arg alias for $3; v_done_p boolean; case_assignment_rec record; context_assignment_rec record; v_str text; begin /* Find out who to assign the given task to. * * 1. See if there are rows in wf_case_assignments. * 2. If not, and a callback is defined, execute that. * 3. Otherwise, grab the assignment from the workflow context. * * (We used to use the callback first, but that makes * reassignment of tasks difficult.) */ v_done_p := ''f''; for case_assignment_rec in select party_id from wf_case_assignments ca, wf_tasks t where t.task_id = set_task_assignments__task_id and ca.case_id = t.case_id and ca.transition_key = t.transition_key LOOP v_done_p := ''t''; PERFORM workflow_case__add_task_assignment ( set_task_assignments__task_id, case_assignment_rec.party_id ); end loop; if v_done_p != ''t'' then if set_task_assignments__callback != '''' and set_task_assignments__callback is not null then v_str := ''select ''|| set_task_assignments__callback || ''('' || set_task_assignments__task_id || '','' || coalesce(quote_literal(set_task_assignments__custom_arg),''null'') || '')''; execute v_str; else for context_assignment_rec in select party_id from wf_context_assignments ca, wf_cases c, wf_tasks t where t.task_id = set_task_assignments__task_id and c.case_id = t.case_id and ca.context_key = c.context_key and ca.workflow_key = t.workflow_key and ca.transition_key = t.transition_key LOOP PERFORM workflow_case__add_task_assignment ( set_task_assignments__task_id, context_assignment_rec.party_id ); end LOOP; end if; end if; return 0; end;' language 'plpgsql'; -- procedure add_token create function workflow_case__add_token (integer,varchar,integer) returns integer as ' declare add_token__case_id alias for $1; add_token__place_key alias for $2; add_token__journal_id alias for $3; v_token_id integer; v_workflow_key varchar; begin select wf_token_id_seq.nextval into v_token_id from dual; select workflow_key into v_workflow_key from wf_cases c where c.case_id = add_token__case_id; insert into wf_tokens (token_id, case_id, workflow_key, place_key, state, produced_journal_id) values (v_token_id, add_token__case_id, v_workflow_key, add_token__place_key, ''free'', add_token__journal_id); return 0; end;' language 'plpgsql'; -- procedure lock_token create function workflow_case__lock_token (integer,varchar,integer,integer) returns integer as ' declare lock_token__case_id alias for $1; lock_token__place_key alias for $2; lock_token__journal_id alias for $3; lock_token__task_id alias for $4; begin -- FIXME: rownum -- update wf_tokens -- set state = ''locked'', -- locked_task_id = lock_token__task_id, -- locked_date = now(), -- locked_journal_id = lock_token__journal_id -- where case_id = lock_token__case_id -- and place_key = lock_token__place_key -- and state = ''free'' -- and rownum = 1; update wf_tokens set state = ''locked'', locked_task_id = lock_token__task_id, locked_date = now(), locked_journal_id = lock_token__journal_id where token_id = (select token_id from wf_tokens where case_id = lock_token__case_id and place_key = lock_token__place_key and state = ''free'' limit 1); return 0; end;' language 'plpgsql'; -- procedure release_token create function workflow_case__release_token (integer,integer) returns integer as ' declare release_token__task_id alias for $1; release_token__journal_id alias for $2; token_rec record; begin /* Add a new token for each released one */ for token_rec in select token_id, case_id, place_key from wf_tokens where state = ''locked'' and locked_task_id = release_token__task_id LOOP PERFORM workflow_case__add_token( token_rec.case_id, token_rec.place_key, release_token__journal_id ); end loop; /* Mark the released ones canceled */ update wf_tokens set state = ''canceled'', canceled_date = now(), canceled_journal_id = release_token__journal_id where state = ''locked'' and locked_task_id = release_token__task_id; return 0; end;' language 'plpgsql'; -- procedure consume_token create function workflow_case__consume_token (integer,varchar,integer,integer) returns integer as ' declare consume_token__case_id alias for $1; consume_token__place_key alias for $2; consume_token__journal_id alias for $3; consume_token__task_id alias for $4; -- default null begin if consume_token__task_id is null then update wf_tokens set state = ''consumed'', consumed_date = now(), consumed_journal_id = consume_token__journal_id where token_id = (select token_id from wf_tokens where case_id = consume_token__case_id and place_key = consume_token__place_key and state = ''free'' limit 1); else update wf_tokens set state = ''consumed'', consumed_date = now(), consumed_journal_id = consume_token__journal_id where case_id = consume_token__case_id and place_key = consume_token__place_key and state = ''locked'' and locked_task_id = consume_token__task_id; end if; return 0; end;' language 'plpgsql'; -- procedure sweep_automatic_transitions create function workflow_case__sweep_automatic_transitions (integer,integer) returns integer as ' declare sweep_automatic_transitions__case_id alias for $1; sweep_automatic_transitions__journal_id alias for $2; v_done_p boolean; v_finished_p boolean; task_rec record; begin PERFORM workflow_case__enable_transitions(sweep_automatic_transitions__case_id); LOOP v_done_p := ''t''; v_finished_p := workflow_case__finished_p( sweep_automatic_transitions__case_id, sweep_automatic_transitions__journal_id); if v_finished_p = ''f'' then for task_rec in select task_id from wf_tasks ta, wf_transitions tr where tr.workflow_key = ta.workflow_key and tr.transition_key = ta.transition_key and tr.trigger_type = ''automatic'' and ta.state = ''enabled'' and ta.case_id = sweep_automatic_transitions__case_id LOOP PERFORM workflow_case__fire_transition_internal( task_rec.task_id, sweep_automatic_transitions__journal_id ); v_done_p := ''f''; end loop; PERFORM workflow_case__enable_transitions(sweep_automatic_transitions__case_id); end if; exit when v_done_p = ''t''; end loop; return 0; end;' language 'plpgsql'; -- function finished_p create function workflow_case__finished_p (integer,integer) returns boolean as ' declare finished_p__case_id alias for $1; finished_p__journal_id alias for $2; v_case_state varchar; v_token_id integer; v_num_rows integer; v_journal_id integer; begin select state into v_case_state from wf_cases where case_id = finished_p__case_id; if v_case_state = ''finished'' then return ''t''; else /* Let us see if the case is actually finished, but just not marked so */ select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_tokens where case_id = finished_p__case_id and place_key = ''end''; if v_num_rows = 0 then return ''f''; else /* There is a token in the end place. * Count the total integer of tokens to make sure the wf is well-constructed. */ select case when count(*) = 0 then 0 when count(*) = 1 then 1 else 2 end into v_num_rows from wf_tokens where case_id = finished_p__case_id and state in (''free'', ''locked''); if v_num_rows > 1 then raise EXCEPTION ''-20000: The workflow net is misconstructed: Some parallel executions have not finished.''; end if; /* Consume that token */ select token_id into v_token_id from wf_tokens where case_id = finished_p__case_id and state in (''free'', ''locked''); PERFORM workflow_case__consume_token( finished_p__case_id, ''end'', finished_p__journal_id, null ); update wf_cases set state = ''finished'' where case_id = finished_p__case_id; /* Add an extra entry to the journal */ v_journal_id := journal_entry__new( null, finished_p__case_id, ''case finish'', ''Case finished'', now(), null, null, null ); return ''t''; end if; end if; end;' language 'plpgsql'; /* This procedure should be scheduled to run as a dbms_job. */ create function sweep_timed_transitions () returns integer as ' declare v_journal_id integer; trans_rec record; begin for trans_rec in select t.task_id, t.case_id, tr.transition_name from wf_tasks t, wf_transitions tr where trigger_time <= now() and state = ''enabled'' and tr.workflow_key = t.workflow_key and tr.transition_key = t.transition_key LOOP /* Insert an entry to the journal so people will know it fired */ v_journal_id := journal_entry__new( null, trans_rec.case_id, ''task '' || trans_rec.task_id || '' fire time'', trans_rec.transition_name || '' automatically finished'', now(), null, null, ''Timed transition fired.'' ); /* Fire the transition */ PERFORM workflow_case__fire_transition_internal( trans_rec.task_id, v_journal_id ); /* Update the workflow internal state */ PERFORM workflow_case___sweep_automatic_transitions( trans_rec.case_id, v_journal_id ); end loop; return 0; end;' language 'plpgsql'; /* This procedure should be scheduled to run as a dbms_job. */ create function sweep_hold_timeout () returns integer as ' declare v_journal_id integer; task_rec record; begin for task_rec in select t.task_id, t.case_id, tr.transition_name from wf_tasks t, wf_transitions tr where hold_timeout <= now() and state = ''started'' and tr.workflow_key = t.workflow_key and tr.transition_key = t.transition_key LOOP /* Insert an entry to the journal so people will know it was canceled */ v_journal_id := journal_entry__new( null, task_rec.case_id, ''task '' || task_rec.task_id || '' cancel timeout'', task_rec.transition_name || '' timed out'', now(), null, null, ''The user''''s hold on the task timed out and the task was automatically canceled'' ); /* Cancel the task */ PERFORM workflow_case__cancel_task( task_rec.task_id, v_journal_id ); end loop; returns 0; end;' language 'plpgsql'; -- procedure notify_assignee create function workflow_case__notify_assignee (integer,integer,varchar,varchar) returns integer as ' declare notify_assignee__task_id alias for $1; notify_assignee__user_id alias for $2; notify_assignee__callback alias for $3; notify_assignee__custom_arg alias for $4; v_deadline_pretty varchar; v_object_name text; v_transition_key wf_transitions.transition_key%TYPE; v_transition_name wf_transitions.transition_name%TYPE; v_party_from parties.party_id%TYPE; v_party_to parties.party_id%TYPE; v_subject text; v_body text; v_request_id integer; v_str text; begin select to_char(ta.deadline,''Mon fmDDfm, YYYY HH24:MI:SS''), acs_object__name(c.object_id), tr.transition_key, tr.transition_name into v_deadline_pretty, v_object_name, v_transition_key, v_transition_name from wf_tasks ta, wf_transitions tr, wf_cases c where ta.task_id = notify_assignee__task_id and c.case_id = ta.case_id and tr.workflow_key = c.workflow_key and tr.transition_key = ta.transition_key; /* Default values */ /* This default value should probably be pulled from somewhere */ v_party_from := -1; v_subject := ''Assignment: '' || v_transition_name || '' '' || v_object_name; v_body := ''You have been assigned to a task. '' || '' Task : '' || v_transition_name || '' Object : '' || v_object_name || '' ''; if v_deadline_pretty != '''' and v_deadline_pretty is not null then v_body := v_body || ''Deadline: '' || v_deadline_pretty || '' ''; end if; /* * We would like to add a URL to go visit, but how do we get that URL? * * The notifications should really be sent from the application * server layer, not from the database */ -- FIXME: last three args are also out varibles. if notify_assignee__callback != '''' and notify_assignee__callback is not null then v_str := ''select '' || notify_assignee__callback || ''('' || notify_assignee__task_id || '','' || coalesce(quote_literal(notify_assignee__custom_arg),''null'') || '','' || notify_assignee__user_id || '','' || v_party_from || '','' || quote_literal(v_subject) || '','' || quote_literal(v_body) || '')''; execute v_str; end if; v_request_id := acs_mail_nt__post_request ( v_party_from, -- party_from notify_assignee__user_id, -- party_to ''f'', -- expand_group v_subject, -- subject v_body, -- message 0 -- max_retries ); return 0; end;' language 'plpgsql'; -- procedure enable_transitions create function workflow_case__enable_transitions (integer) returns integer as ' declare enable_transitions__case_id alias for $1; v_task_id integer; v_workflow_key varchar; v_trigger_time timestamp; v_deadline_date timestamp; v_party_from integer; v_subject varchar; v_body text; v_num_assigned integer; -- request_id nt_requests.request_id%TYPE; trans_rec record; begin select workflow_key into v_workflow_key from wf_cases where case_id = enable_transitions__case_id; /* we mark tasks overridden if they were once enabled, but are no longer so */ update wf_tasks set state = ''overridden'', overridden_date = now() where case_id = enable_transitions__case_id and state = ''enabled'' and transition_key not in (select transition_key from wf_enabled_transitions where case_id = enable_transitions__case_id); /* insert a task for the transitions that are enabled but have no task row */ for trans_rec in select transition_key, transition_name, trigger_type, enable_callback, enable_custom_arg, assignment_callback, assignment_custom_arg, time_callback, time_custom_arg, deadline_callback, deadline_custom_arg, deadline_attribute_name, notification_callback, notification_custom_arg, unassigned_callback, unassigned_custom_arg, estimated_minutes from wf_enabled_transitions et where et.case_id = enable_transitions__case_id and not exists (select 1 from wf_tasks where case_id = enable_transitions__case_id and transition_key = et.transition_key and state in (''enabled'', ''started'')) LOOP v_trigger_time := null; v_deadline_date := null; if trans_rec.trigger_type = ''user'' then v_deadline_date := workflow_case__get_task_deadline( trans_rec.deadline_callback, trans_rec.deadline_custom_arg, trans_rec.deadline_attribute_name, enable_transitions__case_id, trans_rec.transition_key ); else if trans_rec.trigger_type = ''time'' then v_trigger_time := workflow_case__execute_time_callback( trans_rec.time_callback, trans_rec.time_custom_arg, enable_transitions__case_id, trans_rec.transition_key); end if; end if; /* we are ready to insert the row */ select wf_task_id_seq.nextval into v_task_id from dual; insert into wf_tasks ( task_id, case_id, workflow_key, transition_key, deadline, trigger_time, estimated_minutes ) values ( v_task_id, enable_transitions__case_id, v_workflow_key, trans_rec.transition_key, v_deadline_date, v_trigger_time, trans_rec.estimated_minutes ); PERFORM workflow_case__set_task_assignments( v_task_id, trans_rec.assignment_callback, trans_rec.assignment_custom_arg ); /* Execute the transition enabled callback */ PERFORM workflow_case__execute_transition_callback( trans_rec.enable_callback, trans_rec.enable_custom_arg, enable_transitions__case_id, trans_rec.transition_key ); select count(*) into v_num_assigned from wf_task_assignments where task_id = v_task_id; if v_num_assigned = 0 then PERFORM workflow_case__execute_unassigned_callback ( trans_rec.unassigned_callback, v_task_id, trans_rec.unassigned_custom_arg ); end if; end loop; return 0; end;' language 'plpgsql'; -- procedure fire_transition_internal create function workflow_case__fire_transition_internal (integer,integer) returns integer as ' declare fire_transition_internal__task_id alias for $1; fire_transition_internal__journal_id alias for $2; v_case_id integer; v_state varchar; v_transition_key varchar; v_workflow_key varchar; v_place_key varchar; v_direction varchar; v_guard_happy_p boolean; v_fire_callback varchar; v_fire_custom_arg text; v_found_happy_guard boolean; v_locked_task_id integer; place_rec record; begin select t.case_id, t.state, t.workflow_key, t.transition_key, ti.fire_callback, ti.fire_custom_arg into v_case_id, v_state, v_workflow_key, v_transition_key, v_fire_callback, v_fire_custom_arg from wf_tasks t, wf_cases c, wf_transition_info ti where t.task_id = fire_transition_internal__task_id and c.case_id = t.case_id and ti.context_key = c.context_key and ti.workflow_key = c.workflow_key and ti.transition_key = t.transition_key; /* Check that the state is either started or enabled */ if v_state = ''enabled'' then v_locked_task_id := null; else if v_state = ''started'' then v_locked_task_id := fire_transition_internal__task_id; else raise EXCEPTION ''-20000: Can''''t fire the transition if it''''s not in state enabled or started''; end if; end if; /* Mark the task finished */ update wf_tasks set state = ''finished'', finished_date = now() where task_id = fire_transition_internal__task_id; /* Consume the tokens */ for place_rec in select * from wf_transition_places tp where tp.workflow_key = v_workflow_key and tp.transition_key = v_transition_key LOOP PERFORM workflow_case__consume_token( v_case_id, place_rec.place_key, fire_transition_internal__journal_id, v_locked_task_id ); end loop; /* Spit out new tokens in the output places */ v_found_happy_guard := ''f''; for place_rec in select * from wf_transition_places tp where tp.workflow_key = v_workflow_key and tp.transition_key = v_transition_key and direction = ''out'' LOOP v_place_key := place_rec.place_key; v_direction := place_rec.direction; v_guard_happy_p := workflow_case__evaluate_guard( place_rec.guard_callback, place_rec.guard_custom_arg, v_case_id, v_workflow_key, v_transition_key, v_place_key, v_direction ); if v_guard_happy_p = ''t'' then v_found_happy_guard := ''t''; PERFORM workflow_case__add_token( v_case_id, place_rec.place_key, fire_transition_internal__journal_id ); end if; end loop; /* If we did not find any happy guards, look for arcs with the special hash (#) guard */ if v_found_happy_guard = ''f'' then for place_rec in select place_key from wf_transition_places tp where tp.workflow_key = v_workflow_key and tp.transition_key = v_transition_key and tp.direction = ''out'' and tp.guard_callback = ''#'' loop PERFORM workflow_case__add_token( v_case_id, place_rec.place_key, fire_transition_internal__journal_id ); end loop; end if; /* Execute the transition fire callback */ PERFORM workflow_case__execute_transition_callback( v_fire_callback, v_fire_custom_arg, v_case_id, v_transition_key ); return 0; end;' language 'plpgsql'; -- procedure ensure_task_in_state create function workflow_case__ensure_task_in_state (integer,varchar) returns integer as ' declare ensure_task_in_state__task_id alias for $1; ensure_task_in_state__state alias for $2; v_count integer; begin select case when count(*) = 0 then 0 else 1 end into v_count from wf_tasks where task_id = ensure_task_in_state__task_id and state = ensure_task_in_state__state; if v_count != 1 then raise EXCEPTION ''-20000: The task % is not in state "%"'', ensure_task_in_state__task_id, ensure_task_in_state__state; end if; return 0; end;' language 'plpgsql'; -- procedure start_task create function workflow_case__start_task (integer,integer,integer) returns integer as ' declare start_task__task_id alias for $1; start_task__user_id alias for $2; start_task__journal_id alias for $3; v_case_id integer; v_workflow_key wf_workflows.workflow_key%TYPE; v_transition_key varchar(100); v_hold_timeout_callback varchar(100); v_hold_timeout_custom_arg varchar(4000); v_hold_timeout timestamp; place_rec record; begin PERFORM workflow_case__ensure_task_in_state(start_task__task_id, ''enabled''); select t.case_id, t.workflow_key, t.transition_key, ti.hold_timeout_callback, ti.hold_timeout_custom_arg into v_case_id, v_workflow_key, v_transition_key, v_hold_timeout_callback, v_hold_timeout_custom_arg from wf_tasks t, wf_cases c, wf_transition_info ti where t.task_id = start_task__task_id and c.case_id = t.case_id and ti.context_key = c.context_key and ti.workflow_key = t.workflow_key and ti.transition_key = t.transition_key; v_hold_timeout := workflow_case__execute_hold_timeout_callback( v_hold_timeout_callback, v_hold_timeout_custom_arg, v_case_id, v_transition_key); /* Mark it started */ update wf_tasks set state = ''started'', started_date = now(), holding_user = start_task__user_id, hold_timeout = v_hold_timeout where task_id = start_task__task_id; /* Reserve one token from each input place */ for place_rec in select * from wf_transition_places tp where tp.workflow_key = v_workflow_key and tp.transition_key = v_transition_key and direction = ''in'' LOOP PERFORM workflow_case__lock_token( v_case_id, place_rec.place_key, start_task__journal_id, start_task__task_id ); end loop; return 0; end;' language 'plpgsql'; -- procedure cancel_task create function workflow_case__cancel_task (integer,integer) returns integer as ' declare cancel_task__task_id alias for $1; cancel_task__journal_id alias for $2; v_case_id integer; begin PERFORM workflow_case__ensure_task_in_state(cancel_task__task_id, ''started''); select case_id into v_case_id from wf_tasks where task_id = cancel_task__task_id; /* Mark the task canceled */ update wf_tasks set state = ''canceled'', canceled_date = now() where task_id = cancel_task__task_id; /* Release our reserved tokens */ PERFORM workflow_case__release_token( cancel_task__task_id, cancel_task__journal_id ); /* The workflow state has now changed, so we must run this */ PERFORM workflow_case__sweep_automatic_transitions( v_case_id, cancel_task__journal_id ); return 0; end;' language 'plpgsql'; -- procedure finish_task create function workflow_case__finish_task (integer,integer) returns integer as ' declare finish_task__task_id alias for $1; finish_task__journal_id alias for $2; v_case_id integer; begin select case_id into v_case_id from wf_tasks where task_id = finish_task__task_id; PERFORM workflow_case__fire_transition_internal( finish_task__task_id, finish_task__journal_id ); PERFORM workflow_case__sweep_automatic_transitions( v_case_id, finish_task__journal_id ); return 0; end;' language 'plpgsql'; -- show errors -- -- schedule processing of the notification queue -- -- FIXME: notifications is not ported yet. create function inline_0 () returns integer as ' begin PERFORM nt__schedule_process (1,''localhost'',25); return 0; end;' language 'plpgsql'; -- FIXME: notifications not yet ported. -- select inline_0 (); drop function inline_0 ();