--
-- acs-workflow/sql/sample-expenses-create.sql
--
-- Creates an expenses workflow to play with.
--
-- @author Lars Pind (lars@pinds.com)
--
-- @creation-date 2000-05-18
--
-- @cvs-id $Id: sample-expenses-create.sql,v 1.2 2001/04/11 12:41:21 danw Exp $
--
/* This table will hold one row for each case using this workflow. */
create table wf_expenses_cases (
  case_id 		integer primary key
			constraint wf_expenses_cases_case_fk
			references wf_cases on delete cascade
);

create function inline_0 () returns int
declare
    v_workflow_key varchar2(40);
begin
    v_workflow_key := workflow.create_workflow(
	workflow_key => 'expenses_wf', 
	pretty_name => 'Expense Authorization', 
	pretty_plural => 'Expense authorizations', 
	description => 'Workflow for authorizing employee''s expenses on the company''s behalf', 
	table_name => 'wf_expenses_cases'
    );
end;
/
show errors

insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('start', 'expenses_wf', 'Initial state', 1);
insert into wf_places(place_key, workflow_key, place_name, sort_order)
    values ('assignments_done', 'expenses_wf', 'Tasks have been assigned', 2);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('supervisor_to_approve', 'expenses_wf', 'Supervisor is to approve', 3);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('other_to_approve', 'expenses_wf', 'Other is to approve', 4);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('supervisor_approved', 'expenses_wf', 'Supervisor has approved', 5);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('other_approved', 'expenses_wf', 'Other has approved', 6);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('ready_to_buy', 'expenses_wf', 'Both have approved', 8);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('end', 'expenses_wf', 'End state', 9);


insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('assign', 'Assign users to approval', 'expenses_wf', 1, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('and_split', 'Both supervisor and Other approval', 'expenses_wf', 2, 'automatic');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('supervisor_approval', 'Supervisor approval', 'expenses_wf', 3, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type)
    values ('other_approval', 'Other approval', 'expenses_wf', 4, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('and_join', 'Supervisor and other approval both done', 'expenses_wf', 5, 'automatic');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('buy', 'Buy stuff', 'expenses_wf', 6, 'user');

-- assign --
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'assign', 'start', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'assign', 'assignments_done', 'out');


-- and-split --
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_split', 'assignments_done', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_split', 'supervisor_to_approve', 'out');
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_split', 'other_to_approve', 'out');


-- supervisor_approval
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'supervisor_approval', 'supervisor_to_approve', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'supervisor_approval', 'supervisor_approved', 'out');


-- other-approval
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'other_approval', 'other_to_approve', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'other_approval', 'other_approved', 'out');


-- and-join and or-split
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_join', 'supervisor_approved', 'in');
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_join', 'other_approved', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction, guard_callback, guard_description) 
    values ('expenses_wf', 'and_join', 'ready_to_buy', 'out', 'wf_expenses.guard_both_approved_p', 'Both supervisor and CEO approved');
insert into wf_arcs(workflow_key, transition_key, place_key, direction, guard_callback, guard_description)
    values ('expenses_wf', 'and_join', 'end', 'out', '#', 'Either supervisor or CEO did not approve');

-- buy
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction)
    values ('expenses_wf', 'buy', 'ready_to_buy', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction)
    values ('expenses_wf', 'buy', 'end', 'out');


declare
    v_attribute_id acs_attributes.attribute_id%TYPE;
begin
    v_attribute_id := workflow.create_attribute(
	workflow_key => 'expenses_wf',
	attribute_name => 'supervisor_ok',
	datatype => 'boolean',
	pretty_name => 'Supervisor Approval',
	default_value => 'f'
    );

    insert into wf_transition_attribute_map
        (workflow_key, transition_key, attribute_id, sort_order) 
    values
        ('expenses_wf', 'supervisor_approval', v_attribute_id, 1);

    v_attribute_id := workflow.create_attribute(
	workflow_key => 'expenses_wf',
	attribute_name => 'other_ok',
	datatype => 'boolean',
	pretty_name => 'Other Approval',
	default_value => 'f'
    );

    insert into wf_transition_attribute_map
        (workflow_key, transition_key, attribute_id, sort_order) 
    values 
        ('expenses_wf', 'other_approval', v_attribute_id, 1);

end;
/
show errors;

insert into wf_transition_assignment_map 
    (workflow_key, transition_key, assign_transition_key)
values
    ('expenses_wf', 'assign', 'supervisor_approval');

insert into wf_transition_assignment_map 
    (workflow_key, transition_key, assign_transition_key)
values
    ('expenses_wf', 'assign', 'other_approval');



/* Context stuff */

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, estimated_minutes
) values (
    'default', 'expenses_wf', 'assign', 5
);

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, hold_timeout_callback, hold_timeout_custom_arg, estimated_minutes
) values (
    'default', 'expenses_wf', 'supervisor_approval', 'wf_callback.time_sysdate_plus_x', 1/24, 15
);

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, estimated_minutes
) values (
    'default', 'expenses_wf', 'other_approval', 15
);

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, estimated_minutes
) values (
    'default', 'expenses_wf', 'buy', 30
);


insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'supervisor_approval', 1, 'Claim Info', 'sample/expenses-claim-info'
);

insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'supervisor_approval', 2, 'Logic and Aids', 'sample/expenses-approval-aids'
);

insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'other_approval', 1, 'Claim Info', 'sample/expenses-claim-info'
);

insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'other_approval', 2, 'Logic and Aids', 'sample/expenses-approval-aids'
);

commit;



/* Callbacks for the workflow */

create or replace package wf_expenses
is

    function guard_both_approved_p (
	case_id in number,
	workflow_key in varchar2,
	transition_key in varchar2,
	place_key in varchar2,
	direction_in varchar2,
	custom_arg in varchar2
    )
    return char;

end wf_expenses;
/
show errors;


create or replace package body wf_expenses
is
    function guard_both_approved_p (
	case_id in number,
	workflow_key in varchar2,
	transition_key in varchar2,
	place_key in varchar2,
	direction_in varchar2,
	custom_arg in varchar2
    )
    return char
    is
	v_other_ok_p char(1);
	v_supervisor_ok_p char(1);
    begin
	v_other_ok_p := workflow_case.get_attribute_value(
	    case_id => guard_both_approved_p.case_id,
	    attribute_name => 'other_ok'
	);
	if v_other_ok_p = 'f' then
	    return 'f';
	end if;
	v_supervisor_ok_p := workflow_case.get_attribute_value(
	    case_id => guard_both_approved_p.case_id,
	    attribute_name => 'supervisor_ok'
	);
	return v_supervisor_ok_p;
    end guard_both_approved_p;

end wf_expenses;
/
show errors;