/*
 * Business Process Definition: Library Approval (library_approval_wf)
 *
 * Auto-generated by ACS Workflow Export, version 4.0.2
 *
 * Context: default
 */

-- This table will be used to queue email notifications from Oracle,
-- which will then be composed and sent by a scheduled Tcl procedure.
create table wf_library_notifications (
    notification_id
      integer
	constraint wf_libr_notifications_pk
	  primary key,
    task_id
      integer
	constraint wf_libr_n_task_id_fk
	  references wf_tasks
	constraint wf_libr_n_task_id_nn
	  not null,
    template_key
      varchar2(64)
	constraint wf_libr_n_template_fk
	  references et_templates(key)
	constraint wf_libr_n_template_nn
	  not null,
    recipient_id
      integer
	constraint wf_libr_n_recipient_fk
	  references users
	constraint wf_libr_n_recipient_nn
	  not null,
    sender_id
      integer
	constraint wf_libr_n_sender_fk
	  references users
	constraint wf_libr_n_sender_nn
	  not null,
    body
      varchar2(4000)
        default null
);

create sequence library_notification_id_seq start with 1;

create or replace trigger wf_library_notifications_itrg
  before insert on wf_library_notifications for each row
begin
  select library_notification_id_seq.nextval into :new.notification_id from dual;
end;
/
show errors

-- This table will hold one row for each case using this workflow. 
create table wf_library_approval_cases (
    case_id    integer 
               constraint wf_library_appr_cases_pk
               primary key
	       constraint wf_library_appr_cases_case_fk
	       references wf_cases
);

declare
    v_workflow_key      wf_workflows.workflow_key%TYPE;
begin
    v_workflow_key := workflow.create_workflow(
	workflow_key  => 'library_approval_wf', 
	pretty_name   => 'Library Approval Process', 
	pretty_plural => 'Library Approval Process', 
	description   => 'Workflow for Approving a Knowledge Object Publication in the Library',
	table_name    => 'wf_library_approval_cases'
    );

    -- Creating places    
    workflow.add_place (
        workflow_key => 'library_approval_wf',
        place_key    => 'start',
        place_name   => 'Needs to be reviewed',
        sort_order   => 1
    );    
    workflow.add_place (
        workflow_key => 'library_approval_wf',
        place_key    => 'to_be_clarified',
        place_name   => 'Needs to be clarified',
        sort_order   => 2
    );
    workflow.add_place (
        workflow_key => 'library_approval_wf',
        place_key    => 'ready_for_publication',
        place_name   => 'Ready for Publication',
        sort_order   => 3
    );
    workflow.add_place (
        workflow_key => 'library_approval_wf',
        place_key    => 'end',
        place_name   => 'Closed',
        sort_order   => 4
    );

    -- Creating transitions
    workflow.add_transition (
        workflow_key    => 'library_approval_wf',
        transition_key  => 'library_review',
        transition_name => 'Review Knowledge Object',
        sort_order      => 1,
        trigger_type    => 'user'
    );
    workflow.add_transition (
        workflow_key    => 'library_approval_wf',
        transition_key  => 'library_clarify',
        transition_name => 'Clarify Knowledge Object',
        sort_order      => 2,
        trigger_type    => 'user'
    );
    workflow.add_transition (
        workflow_key    => 'library_approval_wf',
        transition_key  => 'library_publish',
        transition_name => 'Publish Knowledge Object',
        sort_order      => 3,
        trigger_type    => 'automatic'
    );
end;
/
show errors

begin
    -- creating arcs
    -- review in
    -- in
    workflow.add_arc (
        workflow_key   => 'library_approval_wf',
        transition_key => 'library_review', 
        place_key      => 'start',
        direction      => 'in'
    );
    -- out
    workflow.add_arc (
        workflow_key      => 'library_approval_wf',
        transition_key    => 'library_review', 
        place_key         => 'to_be_clarified',
        direction         => 'out',
        guard_callback    => 'wf_callback.guard_attribute_true',
        guard_custom_arg  => 'library_object_needs_clarification',
        guard_description => 'Knowledge Object needs clarification'
    );
    workflow.add_arc (
        workflow_key      => 'library_approval_wf',
        transition_key    => 'library_review', 
        place_key         => 'ready_for_publication',
        direction         => 'out',
        guard_callback    => '#',
        guard_description => 'Knowledge Object ready for publication'
    );
    
    -- clarify
    -- in
    workflow.add_arc (
        workflow_key   => 'library_approval_wf',
        transition_key => 'library_clarify', 
        place_key      => 'to_be_clarified',
        direction      => 'in'
    );
    -- out
    workflow.add_arc (
        workflow_key   => 'library_approval_wf',
        transition_key => 'library_clarify', 
        place_key      => 'start',
        direction      => 'out'
    );

    -- publish
    -- in
    workflow.add_arc (
        workflow_key   => 'library_approval_wf',
        transition_key => 'library_publish', 
        place_key      => 'ready_for_publication',
        direction      => 'in'
    );

    -- out
    workflow.add_arc (
        workflow_key      => 'library_approval_wf',
        transition_key    => 'library_publish', 
        place_key         => 'end',
        direction         => 'out',
        guard_callback    => 'wf_callback.guard_attribute_true',
        guard_custom_arg  => 'library_object_published',
        guard_description => 'Knowledge Object published'
    );

end;
/
show errors

declare
    v_attribute_id acs_attributes.attribute_id%TYPE;
begin
    v_attribute_id := workflow.create_attribute(
	workflow_key   => 'library_approval_wf',
	attribute_name => 'library_object_needs_clarification',
	datatype       => 'boolean',
	pretty_name    => 'Knowledge Object needs clarification',
	default_value  => 'f'
    );

    insert into wf_transition_attribute_map
        (workflow_key, transition_key, attribute_id, sort_order) 
    values
        ('library_approval_wf', 'library_review', v_attribute_id, 1);

    v_attribute_id := workflow.create_attribute(
	workflow_key   => 'library_approval_wf',
	attribute_name => 'verified',
	datatype       => 'boolean',
	pretty_name    => 'Result is okay',
	default_value  => 't'
    );

    v_attribute_id := workflow.create_attribute(
	workflow_key   => 'library_approval_wf',
	attribute_name => 'library_object_published',
	datatype       => 'boolean',
	pretty_name    => 'Knowledge Object published',
	default_value  => 't'
    );

end;
/
show errors

insert into wf_context_transition_info (
     context_key, 
     workflow_key, 
     transition_key, 
     assignment_callback, 
     fire_callback, 
     notification_callback,
     notification_custom_arg,
     unassigned_callback,
     access_privilege
) values (
     'default', 
     'library_approval_wf', 
     'library_review', 
     'library_callback.assign_task_to_assignee', 
     'library_callback.review_fire', 
     'library_callback.notification',
     'library_review_object',
     'library_callback.notify_admin',
     'write'
);

insert into wf_context_transition_info (
     context_key, 
     workflow_key, 
     transition_key, 
     assignment_callback, 
     fire_callback, 
     notification_callback,
     notification_custom_arg,
     access_privilege
) values (
     'default', 
     'library_approval_wf', 
     'library_clarify', 
     'library_callback.assign_task_to_submitter', 
     'library_callback.clarify_fire', 
     'library_callback.notification',
     'library_clarify_object',
     'admin'
);

insert into wf_context_transition_info (
     context_key, 
     workflow_key, 
     transition_key, 
     fire_callback, 
     notification_callback,
     notification_custom_arg,
     access_privilege
) values (
     'default', 
     'library_approval_wf', 
     'library_publish', 
     'library_callback.publish_fire', 
     'library_callback.notification',
     'library_published',
     'admin'
);
insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'library_approval_wf', 
    'library_review', 
    1, 
    'Knowledge Object Info', 
    '/packages/library/wf-templates/object-info'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'library_approval_wf', 
    'library_review', 
    2, 
    'What to do', 
    '/packages/library/wf-templates/review'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'library_approval_wf', 
    'library_clarify', 
    1, 
    'Knowledge Object Info', 
    '/packages/library/wf-templates/object-info'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'library_approval_wf', 
    'library_clarify', 
    2, 
    'What to do', 
    '/packages/library/wf-templates/clarify'
);

commit;