-- -- workflow_case__notify_assignee/4 -- create or replace function workflow_case__notify_assignee( integer, integer, character varying, character varying ) returns int4 as $$ declare notify_assignee__task_id alias for $1; notify_assignee__user_id alias for $2; notify_assignee__callback alias for $3; notify_assignee__custom_arg alias for $4; v_deadline_pretty varchar; v_object_name text; v_transition_key wf_transitions.transition_key%TYPE; v_transition_name wf_transitions.transition_name%TYPE; v_party_from parties.party_id%TYPE; v_party_to parties.party_id%TYPE; v_subject text; v_body text; v_request_id integer; v_workflow_url text; v_str text; begin select to_char(ta.deadline,'Mon fmDDfm, YYYY HH24:MI:SS'), acs_object__name(c.object_id), tr.transition_key, tr.transition_name into v_deadline_pretty, v_object_name, v_transition_key, v_transition_name from wf_tasks ta, wf_transitions tr, wf_cases c where ta.task_id = notify_assignee__task_id and c.case_id = ta.case_id and tr.workflow_key = c.workflow_key and tr.transition_key = ta.transition_key; select apm__get_value(p.package_id,'SystemURL') || site_node__url(s.node_id) into v_workflow_url from site_nodes s, apm_packages a, (select package_id from apm_packages where package_key = 'acs-kernel') p where s.object_id = a.package_id and a.package_key = 'acs-workflow'; /* Mail sent from */ select wfi.principal_party into v_party_from from wf_context_workflow_info wfi, wf_tasks ta, wf_cases c where ta.task_id = notify_assignee__task_id and c.case_id = ta.case_id and wfi.workflow_key = c.workflow_key and wfi.context_key = c.context_key; if NOT FOUND then v_party_from := -1; end if; /* Subject */ v_subject := 'Assignment: ' || v_transition_name || ' ' || v_object_name; /* Body */ v_body := 'You have been assigned to a task. ' || ' Case : ' || v_object_name || ' Task : ' || v_transition_name || ' '; if v_deadline_pretty != '' and v_deadline_pretty is not null then v_body := v_body || 'Deadline : ' || v_deadline_pretty || ' '; end if; v_body := v_body ||'Task website: '||v_workflow_url||'task?task_id='||notify_assignee__task_id||' '; /* * We would like to add a URL to go visit, but how do we get that URL? * * The notifications should really be sent from the application * server layer, not from the database */ -- FIXME: last three args are also out varibles. if notify_assignee__callback != '' and notify_assignee__callback is not null then v_str := 'select ' || notify_assignee__callback || ' (' || notify_assignee__task_id || ',' || coalesce(quote_literal(notify_assignee__custom_arg),'null') || ',' || notify_assignee__user_id || ',' || v_party_from || ',' || quote_literal(v_subject) || ',' || quote_literal(v_body) || ')'; execute v_str; else v_request_id := acs_mail_nt__post_request ( v_party_from, -- party_from notify_assignee__user_id, -- party_to 'f', -- expand_group v_subject, -- subject v_body, -- message 0 -- max_retries ); end if; return 0; end;$$ language plpgsql;