--
-- 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.1 2001/04/09 23:13:49 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
  case_id                alias for $1;  
  workflow_key           alias for $2;  
  context_key            alias for $3;  
  object_id              alias for $4;  
  creation_date          alias for $5;  
  creation_user          alias for $6;  
  creation_ip            alias for $7;  
begin
        if context_key is null then
            v_context_key_for_query := ''default'';
        else
            v_context_key_for_query := context_key;
        end if;

        /* insert a row into acs_objects */
        v_case_id := acs_object.new(
            object_id => new__case_id,
            object_type => new__workflow_key,
            creation_date => new__creation_date,
            creation_user => new__creation_user,
            creation_ip => new__creation_ip
        );

        /* 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 (:1)''
        using 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
  case_id                alias for $1;  
  transition_key         alias for $2;  
  party_id               alias for $3;  
  v_workflow_key         varchar(100);  
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
  case_id                alias for $1;  
  transition_key         alias for $2;  
  party_id               alias for $3;  
  v_workflow_key         varchar(100);  
begin
        select workflow_key into v_workflow_key from wf_cases where case_id = remove_manual_assignment__case_id;
        
        delete from wf_case_assignments
        where  workflow_key = v_workflow_key
        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
  case_id                alias for $1;  
  transition_key         alias for $2;  
  v_workflow_key         varchar(100);  
begin
        select workflow_key into v_workflow_key from wf_cases where case_id = clear_manual_assignments__case_id;
        
        delete 
        from   wf_case_assignments 
        where  workflow_key = v_workflow_key 
        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
  case_id                alias for $1;  
  creation_user          alias for $2;  
  creation_ip            alias for $3;  
  msg                    alias for $4;  
  v_journal_id           number;        
begin
        /* Add an entry to the journal */
        v_journal_id := journal_entry.new(
            object_id => start_case__case_id,
            action => ''case start'',
            action_pretty => ''Case started'',
            creation_user => start_case__creation_user,
            creation_ip => start_case__creation_ip,
            msg => start_case__msg
        );

        update wf_cases set state = ''active'' where case_id = start_case__case_id;

        add_token(
            case_id => start_case__case_id, 
            place_key => ''start'',
            journal_id => v_journal_id
        );

        /* Turn the wheels */
        sweep_automatic_transitions(
            case_id => start_case__case_id,
            journal_id => v_journal_id
        );
    return 0; 
end;' language 'plpgsql';


-- procedure delete
create function workflow_case__delete (number)
returns integer as '
declare
  case_id                alias for $1;  
  v_workflow_case_table   varchar(30);   
begin
        /* delete attribute_value_audit, tokens, tasks  */
        delete from wf_attribute_value_audit where case_id = workflow_case.delete__case_id;
        delete from wf_case_assignments where case_id = workflow_case.delete__case_id;
        delete from wf_case_deadlines where case_id = workflow_case.delete__case_id;
        delete from wf_tokens where case_id = workflow_case.delete__case_id;
        delete from wf_task_assignments where task_id in (select task_id from wf_tasks where case_id = workflow_case.delete__case_id);
        delete from wf_tasks where case_id = workflow_case.delete__case_id;

        /* delete the journal */
        journal_entry.delete_for_object(workflow_case.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 = workflow_case.delete__case_id
        and    object_type = c.workflow_key;
        
        execute ''delete from ''||v_workflow_case_table||'' where case_id = :case_id''
        using in workflow_case.delete__case_id;

        /* delete from the generic cases table */
        delete from wf_cases where case_id = workflow_case.delete__case_id;

        /* delete from acs-objects */
        acs_object.delete(workflow_case.delete__case_id);
    return 0; 
end;' language 'plpgsql';


-- procedure suspend
create function workflow_case__suspend (number,number,varchar,varchar)
returns integer as '
declare
  case_id                alias for $1;  
  user_id                alias for $2;  
  ip_address             alias for $3;  
  msg                    alias for $4;  
  v_state                varchar(40);   
  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_application_error(-20000, ''Only active cases can be suspended'');
        end if;
        
        /* Add an entry to the journal */
        v_journal_id := journal_entry.new(
            object_id => suspend__case_id,
            action => ''case suspend'',
            action_pretty => ''case suspended'',
            creation_user => suspend__user_id,
            creation_ip => suspend__ip_address,
            msg => 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
  case_id                alias for $1;  
  user_id                alias for $2;  
  ip_address             alias for $3;  
  msg                    alias for $4;  
  v_state                varchar(40);   
  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_application_error(-20000, ''Only suspended or canceled cases can be resumed'');
        end if;

        /* Add an entry to the journal */
        v_journal_id := journal_entry.new(
            object_id => resume__case_id,
            action => ''case resume'',
            action_pretty => ''case resumed'',
            creation_user => resume__user_id,
            creation_ip => resume__ip_address,
            msg => 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
  case_id                alias for $1;  
  user_id                alias for $2;  
  ip_address             alias for $3;  
  msg                    alias for $4;  
  v_state                varchar(40);   
  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_application_error(-20000, ''Only active or suspended cases can be canceled'');
        end if;

        /* Add an entry to the journal */
        v_journal_id := journal_entry.new(
            object_id => cancel__case_id,
            action => ''case cancel'',
            action_pretty => ''Case canceled'',
            creation_user => cancel__user_id,
            creation_ip => cancel__ip_address,
            msg => 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
  task_id                alias for $1;  
  v_case_id              number;        
  v_transition_name      varchar(100);  
  v_trigger_type         varchar(40);   
  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_application_error(-20000, ''Transition ''''''||v_transition_name||'''''' is not message triggered'');
        end if;

        /* Add an entry to the journal */
        v_journal_id := journal_entry.new(
            object_id => v_case_id,
            action => ''task ''||fire_message_transition__task_id||'' fire'',
            action_pretty => v_transition_name || '' fired''
        );
        
        fire_transition_internal(
            task_id => fire_message_transition__task_id,
            journal_id => v_journal_id
        );

        sweep_automatic_transitions(
            case_id => v_case_id,
            journal_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
  task_id                alias for $1;  
  action                 alias for $2;  
  action_ip              alias for $3;  
  user_id                alias for $4;  
  msg                    alias for $5;  
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_application_error(-20000, ''Task is in state ''''''||v_state||'''''', ''||
                    ''but it must be in state ''''enabled'''' to be started.'');
            end if;
        
            select decode(count(*),0,0,1) 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_application_error(-20000, ''You are not assigned to this task.'');
            end if;
        elsif begin_task_action__action = ''finish'' or begin_task_action__action = ''cancel'' then

            if v_state = ''started'' then
                /* Is this user the holding user? */
                select decode(count(*),0,0,1) 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_application_error(-20000, ''You are the user currently working on this task.'');
                end if;
            elsif v_state = ''enabled'' then
                if begin_task_action__action = ''cancel'' then
                    raise_application_error(-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 decode(count(*),0,0,1) 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_application_error(-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_application_error(-20000, ''Task is in state ''''''||v_state||'''''', ''||
                    ''but it must be in state ''''enabled'''' or ''''started'''' to be finished'');
            end if;

        elsif 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;

        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(
            object_id => v_case_id,
            action => ''task ''||begin_task_action__task_id||'' ''||begin_task_action__action,
            action_pretty => v_transition_name || '' '' || begin_task_action__action,
            creation_user => begin_task_action__user_id,
            creation_ip => begin_task_action__action_ip,
            msg => 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;  
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
            start_task(end_task_action__task_id, v_user_id, end_task_action__journal_id);
        elsif end_task_action__action = ''finish'' then
            finish_task(end_task_action__task_id, end_task_action__journal_id);
        elsif end_task_action__action = ''cancel'' then
            cancel_task(end_task_action__task_id, end_task_action__journal_id);
        elsif end_task_action__action != ''comment'' then
            raise_application_error(-20000, ''Unknown action '''''' || end_task_action__action || '''''''');
        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_id                alias for $1;  
  action                 alias for $2;  
  action_ip              alias for $3;  
  user_id                alias for $4;  
  msg                    alias for $5;  
  v_journal_id           integer;       
begin
        v_journal_id := begin_task_action(
            task_id => task_action__task_id,
            action => task_action__action,
            action_ip => task_action__action_ip,
            user_id => task_action__user_id,
            msg => task_action__msg
        );
        
        end_task_action(
            journal_id => v_journal_id,
            action => task_action__action,
            task_id => 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
  journal_id             alias for $1;  
  attribute_name         alias for $2;  
  value                  alias for $3;  
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;
        
        acs_object.set_attribute(
            object_id_in => v_case_id,  
            attribute_name_in => set_attribute_value__attribute_name,
            value_in => 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
  case_id                alias for $1;  
  attribute_name         alias for $2;  
begin
        return acs_object.get_attribute(
            object_id_in => get_attribute_value__case_id,
            attribute_name_in => 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
  task_id                alias for $1;  
  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;
                                        
  --                     might          
  cursor                 c_new_assigned_users
  select                 distinct       
  from                   users          
  where                  u.user_id      
  select                 distinct       
  from                   wf_task_assignments
  party_approved_member_map   m2,            
  users                  u2             
  where                  tasgn2.task_id 
  and                    m2.party_id    
  and                    u2.user_id     
  and                    exists         
  select                 1              
  from                   party_approved_member_map
  where                  m.member_id    
  and                    m.party_id     
  );                                    
  cursor                 c_callback     
  select                 notification_callback,
  notification_custom_arg                  
  from                   wf_context_transition_info
  where                  context_key    
  and                    workflow_key   
  and                    transition_key 
  callback_rec           c_callback%ROWTYPE;
                                        
begin
        -- check that we don''t 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;
        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;

        open c_callback;
        fetch c_callback into callback_rec;

        if c_callback%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 c_new_assigned_users loop
            notify_assignee(
                task_id => add_task_assignment__task_id,
                user_id => v_assigned_user.user_id,
                callback => v_notification_callback,
                custom_arg => 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
  task_id                alias for $1;  
  party_id               alias for $2;  
  v_num_assigned         number;        
  v_case_id              number;        
  v_workflow_key         varchar(100);  
  v_transition_key       varchar(100);  
  v_context_key          varchar(100);  
                                        
  cursor                 c_callback     
  select                 unassigned_callback,
  from                   wf_context_transition_info
  where                  workflow_key   
  and                    context_key    
  and                    transition_key 
  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;
        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;

        open c_callback;
        fetch c_callback into callback_rec;

        if c_callback%FOUND then
            execute_unassigned_callback (
                callback => callback_rec.unassigned_callback,
                task_id => task_id,
                custom_arg => callback_rec.unassigned_custom_arg
            );
        end if;
        close c_callback;

    return 0; 
end;' language 'plpgsql';


-- procedure clear_task_assignments
create function workflow_case__clear_task_assignments (number)
returns integer as '
declare
  task_id                alias for $1;  
  v_case_id              number;        
  v_transition_key       varchar(100);  
  v_workflow_key         varchar(100);  
  v_context_key          varchar(100);  
  v_callback             varchar(100);  
  v_custom_arg           varchar(4000); 
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;

        execute_unassigned_callback (
            callback => v_callback,
            task_id => task_id,
            custom_arg => 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 integer as '
declare
  callback               alias for $1;  
  custom_arg             alias for $2;  
  case_id                alias for $3;  
  workflow_key           alias for $4;  
  transition_key         alias for $5;  
  place_key              alias for $6;  
  direction              alias for $7;  
begin
        if callback is null then
            -- null guard evaluates to true
            return ''t'';
        else
            if callback = ''#'' then
                return ''f'';
            else
                execute ''begin :1 := '' || callback
                || ''(:2, :3, :4, :5, :6, :7); end;''
                using out v_guard_happy_p, 
                      in case_id, 
                      in workflow_key, 
                      in transition_key, 
                      in place_key, 
                      in direction,
                      in custom_arg;
                return v_guard_happy_p;
            end if;
        end if;
     
end;' language 'plpgsql';


-- procedure execute_transition_callback
create function workflow_case__execute_transition_callback (varchar,varchar,number,varchar)
returns integer as '
declare
  callback               alias for $1;  
  custom_arg             alias for $2;  
  case_id                alias for $3;  
  transition_key         alias for $4;  
begin
        if callback is not null then 
            execute ''begin ''||callback
            || ''(:1, :2, :3); end;''
            using in case_id, 
                  in transition_key,
                  in 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 integer as '
declare
  callback               alias for $1;  
  custom_arg             alias for $2;  
  case_id                alias for $3;  
  transition_key         alias for $4;  
begin
        if callback is null then
            raise_application_error(-20000, ''There''''s no time_callback function for the timed transition '''''' || transition_key || '''''''');
        end if;
 
        execute ''begin :1 := '' || callback 
        || ''(:2, :3, :4); end;''
        using out v_trigger_time, 
              in case_id, 
              in transition_key,
              in custom_arg;
        
        return v_trigger_time;
     
end;' language 'plpgsql';


-- function get_task_deadline
create function workflow_case__get_task_deadline (varchar,varchar,varchar,number,varchar)
returns integer as '
declare
  callback               alias for $1;  
  custom_arg             alias for $2;  
  attribute_name         alias for $3;  
  case_id                alias for $4;  
  transition_key         alias for $5;  
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 */
        open case_deadline_cur;
        fetch case_deadline_cur into v_deadline;
        if case_deadline_cur%NOTFOUND then
            if callback is not null then
                /* callback */
                execute ''begin :1 := '' || callback 
                || ''(:2, :3, :4); end;''
                using out v_deadline, 
                      in case_id, 
                      in transition_key,
                      in custom_arg;
            elsif attribute_name is not null then
                /* attribute */
                v_deadline := acs_object.get_attribute(
                    object_id_in => get_task_deadline__case_id,
                    attribute_name_in => get_task_deadline__attribute_name
                );
            else 
                v_deadline := null;
            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
  callback               alias for $1;  
  custom_arg             alias for $2;  
  case_id                alias for $3;  
  transition_key         alias for $4;  
begin
        if callback is null then
            return null;
        end if;
 
        execute ''begin :1 := '' || callback 
        || ''(:2, :3, :4); end;''
        using out v_hold_timeout, 
              in case_id, 
              in transition_key,
              in custom_arg;
        
        return v_hold_timeout;
     
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 ''begin '' || callback
            || ''(:1, :2); end;''
            using in task_id,
                  in 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
  task_id                alias for $1;  
  callback               alias for $2;  
  custom_arg             alias for $3;  
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 case_assignments loop
            v_done_p := ''t'';
            add_task_assignment (
                task_id => task_id,
                party_id => case_assignment_rec.party_id
            );
        end loop;

        if v_done_p != ''t'' then

            if callback is not null then
                execute ''begin ''|| set_task_assignments__callback 
                || ''(:1, :2); end;''
                using in set_task_assignments__task_id,
                      in set_task_assignments__custom_arg;
            else
                for context_assignment_rec in context_assignments loop
                    add_task_assignment (
                        task_id => task_id,
                        party_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
  case_id                alias for $1;  
  place_key              alias for $2;  
  journal_id             alias for $3;  
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
  case_id                alias for $1;  
  place_key              alias for $2;  
  journal_id             alias for $3;  
  task_id                alias for $4;  
begin
        update wf_tokens
        set    state = ''locked'',
               locked_task_id = lock_token__task_id,
               locked_date = sysdate,
               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;
    return 0; 
end;' language 'plpgsql';


-- procedure release_token
create function workflow_case__release_token (number,number)
returns integer as '
declare
  task_id                alias for $1;  
  journal_id             alias for $2;  
  cursor                 token_cur      
  select                 token_id,      
  case_id,                              
  place_key                             
  from                   wf_tokens      
  where                  state          
  and                    locked_task_id 
begin
        /* Add a new token for each released one */
        for token_rec in token_cur loop
            add_token(
                case_id => token_rec.case_id,
                place_key => token_rec.place_key,
                journal_id => release_token__journal_id
            );
        end loop;

        /* Mark the released ones canceled */
        update wf_tokens
        set    state = ''canceled'',
               canceled_date = sysdate,
               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
  case_id                alias for $1;  
  place_key              alias for $2;  
  journal_id             alias for $3;  
  task_id                alias for $4;  
begin
        if task_id is null then
            update wf_tokens
            set    state = ''consumed'',
                   consumed_date = sysdate,
                   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 = sysdate,
                   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
  case_id                alias for $1;  
  journal_id             alias for $2;  
  cursor                 enabled_automatic_transitions
  select                 task_id        
  from                   wf_tasks       
  where                  tr.workflow_key
  and                    tr.transition_key
  and                    tr.trigger_type
  and                    ta.state       
  and                    ta.case_id     
  v_done_p               char(1);       
  v_finished_p           char(1);       
begin

        enable_transitions(case_id => sweep_automatic_transitions__case_id);

        loop
            v_done_p := ''t'';
            v_finished_p := finished_p(
                case_id => sweep_automatic_transitions__case_id,
                journal_id => sweep_automatic_transitions__journal_id);

            if v_finished_p = ''f'' then
                for task_rec in enabled_automatic_transitions loop
                    fire_transition_internal(
                        task_id => task_rec.task_id,
                        journal_id => sweep_automatic_transitions__journal_id
                    );
                    v_done_p := ''f'';
                end loop;
                enable_transitions(case_id => 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 integer as '
declare
  case_id                alias for $1;  
  journal_id             alias for $2;  
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 decode(count(*),0,0,1) 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 decode(count(*),0,0,1,1,2) 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_application_error(-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'');

                consume_token(
                    case_id => finished_p__case_id,
                    place_key => ''end'',
                    journal_id => finished_p__journal_id
                );

                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(
                    object_id => finished_p__case_id,
                    action => ''case finish'',
                    action_pretty => ''Case finished''
                );

                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
  task_id                alias for $1;  
  user_id                alias for $2;  
  callback               alias for $3;  
  custom_arg             alias for $4;  
  v_deadline_pretty      varchar(400);  
  v_object_name          varchar(4000); 
  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              varchar(4000); 
  v_body                 varchar(4000); 
  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 */
    
        if notify_assignee__callback is not null then
            execute ''begin ''||notify_assignee__callback||''(:1, :2, :3, :4, :5, :6); end;''
                using in notify_assignee__task_id,
                      in notify_assignee__custom_arg,
                      in notify_assignee__user_id,
                      in out v_party_from,
                      in out v_subject,
                      in out v_body;
        end if;
            
        v_request_id := nt.post_request (       
            party_from => v_party_from,
            party_to => notify_assignee__user_id,
            expand_group => ''f'' ,
            subject => v_subject,
            message => v_body,
            max_retries => 3
        );

    return 0; 
end;' language 'plpgsql';


-- procedure enable_transitions
create function workflow_case__enable_transitions (number)
returns integer as '
declare
  case_id                alias for $1;  
  cursor                 tasks_to_create
  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
  where                  et.case_id     
  and                    not            
  where                  case_id        
  and                    transition_key 
  and                    state          
  v_task_id              number;        
  v_workflow_key         varchar(100);  
  v_trigger_time         timestamp;     
  v_deadline_date        timestamp;     
  v_party_from           integer;       
  v_subject              varchar(500);  
  v_body                 varchar(4000); 
  v_num_assigned         number;        
  request_id             nt_requests.request_id%TYPE;
  cursor                 assignees_cur  
  select                 distinct       
  from                   wf_task_assignments
  party_approved_member_map   m,             
  users                  u              
  where                  tasgn.task_id  
  and                    m.party_id     
  and                    u.user_id      
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 = sysdate
        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 tasks_to_create loop

            v_trigger_time := null;
            v_deadline_date := null;

            if trans_rec.trigger_type = ''user'' then
                v_deadline_date := get_task_deadline(
                    callback => trans_rec.deadline_callback, 
                    custom_arg => trans_rec.deadline_custom_arg,
                    attribute_name => trans_rec.deadline_attribute_name,
                    case_id => enable_transitions__case_id, 
                    transition_key => trans_rec.transition_key
                );
            elsif trans_rec.trigger_type = ''time'' then
                v_trigger_time := execute_time_callback(trans_rec.time_callback, 
                    trans_rec.time_custom_arg,
                    enable_transitions__case_id, trans_rec.transition_key);
            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
            );
            
            set_task_assignments(
                task_id => v_task_id,
                callback => trans_rec.assignment_callback,
                custom_arg => trans_rec.assignment_custom_arg
            );

            /* Execute the transition enabled callback */
            execute_transition_callback(
                callback => trans_rec.enable_callback, 
                custom_arg => trans_rec.enable_custom_arg,
                case_id => enable_transitions__case_id, 
                transition_key => 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
                execute_unassigned_callback (
                    callback => trans_rec.unassigned_callback,
                    task_id => v_task_id,
                    custom_arg => 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
  task_id                alias for $1;  
  journal_id             alias for $2;  
  v_case_id              number;        
  v_state                varchar(40);   
  v_transition_key       varchar(100);  
  v_workflow_key         varchar(100);  
  v_place_key            varchar(100);  
  v_direction            varchar(3);    
  v_guard_happy_p        char(1);       
  v_fire_callback        varchar(100);  
  v_fire_custom_arg      varchar(4000); 
  v_found_happy_guard    char(1);       
  v_locked_task_id       number;        
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;
        elsif v_state = ''started'' then
            v_locked_task_id := fire_transition_internal__task_id;
        else 
            raise_application_error(-20000, ''Can''''t fire the transition if it''''s not in state enabled or started'');
        end if;
        

        /* Mark the task finished */

        update wf_tasks
        set    state = ''finished'',
               finished_date = sysdate
        where  task_id = fire_transition_internal__task_id;


        /* Consume the tokens */

        for place_rec in input_places(v_workflow_key, v_transition_key) loop 
            consume_token(
                case_id => v_case_id,
                place_key => place_rec.place_key,
                journal_id => fire_transition_internal__journal_id,
                task_id => v_locked_task_id
             );
        end loop;

    
        /* Spit out new tokens in the output places */

        v_found_happy_guard := ''f'';
        for place_rec in output_places(v_workflow_key, v_transition_key) loop
            v_place_key := place_rec.place_key;
            v_direction := place_rec.direction;
            v_guard_happy_p := evaluate_guard(
                callback => place_rec.guard_callback, 
                custom_arg => place_rec.guard_custom_arg,
                case_id => v_case_id, 
                workflow_key => v_workflow_key, 
                transition_key => v_transition_key, 
                place_key => v_place_key, 
                direction => v_direction
            );
    
            if v_guard_happy_p = ''t'' then
                v_found_happy_guard := ''t'';
                add_token(
                    case_id => v_case_id, 
                    place_key => place_rec.place_key,
                    journal_id => 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
                add_token(
                    case_id => v_case_id, 
                    place_key => place_rec.place_key,
                    journal_id => fire_transition_internal__journal_id
                );
            end loop;
        end if;


        /* Execute the transition fire callback */

        execute_transition_callback(
            callback => v_fire_callback, 
            custom_arg => v_fire_custom_arg, 
            case_id => v_case_id, 
            transition_key => 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
  task_id                alias for $1;  
  state                  alias for $2;  
  v_count                number;        
begin
        select decode(count(*),0,0,1) 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_application_error(-20000, ''The task ''|| ensure_task_in_state__task_id || '' is not in state '''''' || 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
  task_id                alias for $1;  
  user_id                alias for $2;  
  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;     
begin
        ensure_task_in_state(task_id => start_task__task_id, state => ''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 := 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 = sysdate,
               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 input_places(v_workflow_key,v_transition_key) loop
            lock_token( 
                case_id => v_case_id,
                place_key => place_rec.place_key,
                journal_id => start_task__journal_id,
                task_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
  task_id                alias for $1;  
  journal_id             alias for $2;  
begin
        ensure_task_in_state(task_id => cancel_task__task_id, state => ''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 =  sysdate
        where  task_id = cancel_task__task_id;

    
        /* Release our reserved tokens */

        release_token(
            task_id => cancel_task__task_id,
            journal_id => cancel_task__journal_id
        );

        /* The workflow state has now changed, so we must run this */
        
        sweep_automatic_transitions(
            case_id => v_case_id,
            journal_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
  task_id                alias for $1;  
  journal_id             alias for $2;  
begin
        select case_id into v_case_id
        from   wf_tasks
        where  task_id = finish_task__task_id;

        fire_transition_internal(
            task_id => finish_task__task_id,
            journal_id => finish_task__journal_id
        );

        sweep_automatic_transitions(
            case_id => v_case_id,
            journal_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
    select nt__schedule_process (1,'localhost',25);
    NONE,
    NONE,
    25
    );
  return 0;
end;' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();