--
-- 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.3 2001/04/11 04:47:19 danw Exp $
--


-- create or replace package workflow_case
-- is
-- 
--     function new (
--         case_id         in number 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 number,
--         transition_key  in varchar2,
--         party_id        in number
--     );
--   
--     procedure remove_manual_assignment (
--         case_id         in number,
--         transition_key  in varchar2,
--         party_id        in number
--     );
--   
--     procedure clear_manual_assignments (
--         case_id         in number,
--         transition_key  in varchar2
--     );
--   
--     procedure start_case (
--         case_id         in number,
--         creation_user   in integer default null,
--         creation_ip     in varchar2 default null,
--         msg             in varchar2 default null
--     );
-- 
--     procedure delete(
--         case_id         in number
--     );
-- 
--     procedure suspend(
--         case_id         in number,
--         user_id         in number default null,
--         ip_address      in varchar2 default null,
--         msg             in varchar2 default null
--     );
-- 
--     procedure resume(
--         case_id         in number,
--         user_id         in number default null,
--         ip_address      in varchar2 default null,
--         msg             in varchar2 default null
--     );
-- 
--     procedure cancel(
--         case_id         in number,
--         user_id         in number default null,
--         ip_address      in varchar2 default null,
--         msg             in varchar2 default null
--     );
-- 
--     procedure fire_message_transition (
--         task_id         in number
--     );
-- 
--     /* To perform an action on the workflow: 
--      * (numbers in parenthesis is the number 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 number,
--         action          in varchar2,
--         action_ip       in varchar2,
--         user_id         in number,
--         msg             in varchar2 default null
--     ) return number;
-- 
--     procedure set_attribute_value (
--         journal_id      in number,
--         attribute_name  in varchar2,
--         value           in varchar2
--     );
-- 
--     procedure end_task_action (
--         journal_id      in number,
--         action          in varchar2,
--         task_id         in number
--     );
-- 
--     /* Shortcut, that does both begin and end, when you have no attributes to set or assignments to make */
--     function task_action (
--         task_id         in number,
--         action          in varchar2,
--         action_ip       in varchar2,
--         user_id         in number,
--         msg             in varchar2 default null
--     ) return number;
-- 
--     function get_attribute_value (
--         case_id         in number,
--         attribute_name  in varchar2
--     ) return varchar2;
-- 
--     procedure add_task_assignment (
--         task_id         in number,
--         party_id        in number
--     );
-- 
--     procedure remove_task_assignment (
--         task_id         in number,
--         party_id        in number
--     );
-- 
--     procedure clear_task_assignments (
--         task_id         in number
--     );
--   
-- 
--     /* 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 (number,varchar,varchar,integer,timestamp,integer,varchar)
returns integer as '
declare
  new__case_id                alias for $1;  
  new__workflow_key           alias for $2;  
  new__context_key            alias for $3;  
  new__object_id              alias for $4;  
  new__creation_date          alias for $5;  
  new__creation_user          alias for $6;  
  new__creation_ip            alias for $7;  
  v_case_id                   number;
  v_workflow_case_table       varchar;
  v_context_key_for_query     varchar;
begin
        if 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 (number,varchar,number)
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 (number,varchar,number)
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 (number,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 (number,integer,varchar,varchar)
returns integer as '
declare
  start_case__case_id                alias for $1;  
  start_case__creation_user          alias for $2;  
  start_case__creation_ip            alias for $3;  
  start_case__msg                    alias for $4;  
  v_journal_id                       number;        
begin
        /* Add an entry to the journal */
        v_journal_id := journal_entry__new(
            null, 
            start_case__case_id,
            ''case start'',
            ''Case started'',
            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 (number)
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 (number,number,varchar,varchar)
returns integer as '
declare
  suspend__case_id                alias for $1;  
  suspend__user_id                alias for $2;  
  suspend__ip_address             alias for $3;  
  suspend__msg                    alias for $4;  
  v_state                         varchar;   
  v_journal_id                    number;        
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 (number,number,varchar,varchar)
returns integer as '
declare
  resume__case_id                alias for $1;  
  resume__user_id                alias for $2;  
  resume__ip_address             alias for $3;  
  resume__msg                    alias for $4;  
  v_state                        varchar;   
  v_journal_id                   number;        
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 (number,number,varchar,varchar)
returns integer as '
declare
  cancel__case_id                alias for $1;  
  cancel__user_id                alias for $2;  
  cancel__ip_address             alias for $3;  
  cancel__msg                    alias for $4;  
  v_state                        varchar;   
  v_journal_id                   number;        
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 (number)
returns integer as '
declare
  fire_message_transition__task_id               alias for $1;  
  v_case_id                                      number;        
  v_transition_name                              varchar;  
  v_trigger_type                                 varchar;   
  v_journal_id                                   number;        
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 (number,varchar,varchar,number,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;  
  v_state                                   varchar;
  v_journal_id                              number;
  v_case_id                                 number;
  v_transition_name                         varchar;
  v_num_rows                                number;
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_name;
            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_name;
            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 doens''t 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 (number,varchar,number)
returns integer as '
declare
  journal_id             alias for $1;  
  action                 alias for $2;  
  task_id                alias for $3;  
  v_user_id              number;
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 (number,varchar,varchar,number,varchar)
returns number 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;  
  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 (number,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                                   number;
  v_attribute_id                              number;
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 (number,varchar)
returns integer 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 (number,number)
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           c_callback%ROWTYPE;
  v_assigned_user        record;
begin
        -- check that we don 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 (number,number)
returns integer as '
declare
  remove_task_assignment__task_id                alias for $1;  
  remove_task_assignment__party_id               alias for $2;  
  v_num_assigned                                 number;        
  v_case_id                                      number;        
  v_workflow_key                                 varchar;  
  v_transition_key                               varchar;  
  v_context_key                                  varchar;  
  callback_rec                                   c_callback%ROWTYPE;
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,
                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 (number)
returns integer as '
declare
  clear_task_assignments__task_id                alias for $1;  
  v_case_id                                      number;        
  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,
            task_id,
            v_custom_arg
        );

        return 0; 
end;' language 'plpgsql';


-- function evaluate_guard
create function workflow_case__evaluate_guard (varchar,varchar,number,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;
begin
        if evaluate_guard__callback is null then
            -- null guard evaluates to true
            return ''t'';
        else
            if evaluate_guard__callback = ''#'' then
                return ''f'';
            else
                for v_rec in 
                    execute ''select '' || evaluate_guard__callback
                || ''('' || 
                evaluate_guard__case_id || '','' || 
                evaluate_guard__workflow_key || '','' || 
                evaluate_guard__transition_key || '','' || 
                evaluate_guard__place_key || '','' || 
                evaluate_guard__direction || '','' || 
                evaluate_guard__custom_arg || '')''
                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,number,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;  
begin
        if execute_transition_callback__callback is not null then 
            execute ''select '' || execute_transition_callback__callback
            || ''('' || execute_transition_callback__case_id || '','' || 
            execute_transition_callback__transition_key || '','' || 
            execute_transition_callback__custom_arg '')''
        end if;

        return 0; 
end;' language 'plpgsql';


-- function execute_time_callback
create function workflow_case__execute_time_callback (varchar,varchar,number,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;
begin
        if 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;
 
        for v_rec in execute ''select '' || execute_time_callback__callback || ''('' || 
                  execute_time_callback__case_id || '','' || 
                  execute_time_callback__transition_key || '','' || 
                  execute_time_callback__custom_arg || '') as trigger_time''
        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,number,varchar)
returns integer 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;
begin
        /*
         * 1. or if there''s 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 is not null then
                /* callback */
                for v_rec in 
                execute ''select '' || get_task_deadline__callback || ''('' || 
                get_task_deadline__case_id || '','' || 
                get_task_deadline__transition_key || '','' || 
                get_task_deadline__custom_arg ||  '') as deadline''
                LOOP
                    v_deadline := v_rec.deadline;
                    exit;
                end LOOP;
            else if 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,number,varchar)
returns integer 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;
begin
        if execute_hold_timeout_callback__callback is null then
            return null;
        end if;
 
        for v_rec in 
        execute ''select '' || execute_hold_timeout_callback__callback 
        || ''('' ||
        execute_hold_timeout_callback__case_id || '','' ||
        execute_hold_timeout_callback__transition_key || '','' ||
        execute_hold_timeout_callback__custom_arg || '') into hold_timeout''
        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,number,varchar)
returns integer as '
declare
  callback               alias for $1;  
  task_id                alias for $2;  
  custom_arg             alias for $3;  
begin
        if callback is not null then
            execute ''select '' || callback
            || ''('' || task_id || '','' || custom_arg || '')'';
        end if;

        return 0; 
end;' language 'plpgsql';


-- procedure set_task_assignments
create function workflow_case__set_task_assignments (number,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;
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 (
                task_id,
                case_assignment_rec.party_id
            );
        end loop;

        if v_done_p != ''t'' then

            if set_task_assignments__callback is not null then
                execute ''select ''|| set_task_assignments__callback || ''('' || 
                set_task_assignments__task_id || '','' || 
                set_task_assignments__custom_arg || '')'';
            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 (
                        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 (number,varchar,number)
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                        number;
  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 (number,varchar,number,number)
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  case_id = lock_token__case_id
        and    place_key = lock_token__place_key
        and    state = ''free'';

        return 0; 
end;' language 'plpgsql';


-- procedure release_token
create function workflow_case__release_token (number,number)
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
            PERFROM 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 (number,varchar,number,number)
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;  
begin
        if task_id is null then
            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 = ''free''
            and    rownum = 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 (number,number)
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 enabled_automatic_transitions 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 (number,number)
returns booleanr as '
declare
  finished_p__case_id                alias for $1;  
  finished_p__journal_id             alias for $2;  
  v_case_state                       varchar;
  v_token_id                         number;
  v_num_rows                         number;
  v_journal_id                       number;
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''s 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''s a token in the end place.
                 * Count the total number 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,
                    object_id => finished_p__case_id,
                    action => ''case finish'',
                    action_pretty => ''Case finished'',
                    now(),
                    null,
                    null,
                    null
                );

                return ''t'';
            end if;
        end if;
     
end;' language 'plpgsql';


-- procedure notify_assignee
create function workflow_case__notify_assignee (integer,integer,<=>,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;       
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 */
        v_party_from := -1;   /* This default value should probably be pulled from somewhere */
        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 != '''' then
            v_body := v_body ||''Deadline: ''||v_deadline_pretty||''
'';
        end if;

        /* We''d 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 is not null then
            execute ''select '' || notify_assignee__callback || ''('' || 
            notify_assignee__task_id || '','' ||
            notify_assignee__custom_arg || '','' ||
            notify_assignee__user_id || '','' ||
            v_party_from || '','' ||
            v_subject || '','' ||
            v_body || '')'';
        end if;
            
        -- FIXME: notifications package not ported yet.

        v_request_id := nt__post_request (       
            v_party_from,
            notify_assignee__user_id,
            ''f'' ,
            v_subject,
            v_body,
            3
        );

    return 0; 
end;' language 'plpgsql';


-- procedure enable_transitions
create function workflow_case__enable_transitions (number)
returns integer as '
declare
  enable_transitions__case_id                alias for $1;  
  v_task_id                                  number;        
  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                             number;        
  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''re 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 (number,number)
returns integer as '
declare
  fire_transition_internal__task_id                alias for $1;  
  fire_transition_internal__journal_id             alias for $2;  
  v_case_id                                        number;        
  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                                 number;   
  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 didn''t 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 (number,varchar)
returns integer as '
declare
  ensure_task_in_state__task_id         alias for $1;  
  ensure_task_in_state__state           alias for $2;  
  v_count                               number;        
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 (number,number,number)
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                          number;        
  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 (number,number)
returns integer as '
declare
  cancel_task__task_id                alias for $1;  
  cancel_task__journal_id             alias for $2;  
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 (number,number)
returns integer as '
declare
  finish_task__task_id                alias for $1;  
  finish_task__journal_id             alias for $2;  
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
--

create function inline_0 ()
returns integer as '
begin
    PERFORM  nt__schedule_process (1,'localhost',25);

    return 0;
end;' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();