create or replace package library_workflow
as
    function new_workflow_case (
        p_object_id	        in sn_objects.object_id%TYPE,
        p_creation_date         in acs_objects.creation_date%TYPE       default sysdate,
        p_creation_user         in acs_objects.creation_user%TYPE,
        p_creation_ip	        in acs_objects.creation_ip%TYPE         default null,
	p_publisher_id		in sn_objects.publisher_id%TYPE         default null
    ) return integer;

    procedure delete_workflow_case (
        object_id    in sn_objects.object_id%TYPE
    );

    function workflow_url (
        absolute_p	      in char default 't'
    ) return varchar2;

    function notification_sender (
        package_id in apm_packages.package_id%TYPE
    ) return users.user_id%TYPE;

end library_workflow;
/
show errors


create or replace package body library_workflow
as
    function new_workflow_case (
        p_object_id	        in sn_objects.object_id%TYPE,
        p_creation_date         in acs_objects.creation_date%TYPE       default sysdate,
        p_creation_user         in acs_objects.creation_user%TYPE,
        p_creation_ip	        in acs_objects.creation_ip%TYPE         default null,
	p_publisher_id		in sn_objects.publisher_id%TYPE         default null
    ) return integer
    is
        v_case_id               wf_cases.case_id%TYPE;
    begin
        -- create a new case
        v_case_id := workflow_case.new (
            workflow_key => 'library_approval_wf',
            object_id => p_object_id,
            creation_user => p_creation_user,
            creation_ip => p_creation_ip
        );

        -- start the case
        workflow_case.start_case (
            case_id => v_case_id,
            creation_user => p_creation_user,
            creation_ip => p_creation_ip
        );                                                        

        -- now automatically assign the task to the coordinator. The workflow
        -- package requires static assignment otherwise.
	return v_case_id;
    end new_workflow_case ;

    procedure delete_workflow_case (
        object_id    in sn_objects.object_id%TYPE
    )
    is
        cursor case_cur is
            select case_id
              from wf_cases 
             where object_id = object_id;
        case_rec case_cur%ROWTYPE;
    begin
        open case_cur;
        fetch case_cur into case_rec;
        
        -- delete the workflow case that's associated with this object

        if case_cur%FOUND then       
            workflow_case.delete(case_rec.case_id);
        end if;
        
        close case_cur;
    end delete_workflow_case ;

    function workflow_url (
        absolute_p	      in char default 't'
    ) return varchar2
    is
        v_system_url    apm_parameter_values.attr_value%TYPE;
        cursor node_cur is
            select sn.node_id
              from site_nodes sn,
                   apm_packages ap
             where ap.package_key = 'acs-workflow'
               and sn.object_id = ap.package_id;
        node_rec        node_cur%ROWTYPE;
    begin
        -- try to get the id of the site node that contains acs workflow
        open node_cur;
        fetch node_cur into node_rec;
        if node_cur%NOTFOUND then
            close node_cur;
            raise_application_error(-20000, 'ACS Workflow must be mounted');
        end if;
        close node_cur;
	
	-- if only relative url is needed
	if absolute_p <> 't' then
	    return site_node.url(node_rec.node_id);
        end if;

        -- since there's only 1 instance of the kernel
        -- it's ok to use select.. into..
        select apv.attr_value into v_system_url
          from apm_parameter_values apv,
               apm_parameters ap
         where ap.package_key = 'acs-kernel'
           and ap.parameter_name = 'SystemURL'
           and ap.parameter_id = apv.parameter_id;
        
        return v_system_url||site_node.url(node_rec.node_id);

    end workflow_url;                

    function notification_sender (
        package_id in apm_packages.package_id%TYPE
    ) return users.user_id%TYPE
    is
	v_result   users.user_id%TYPE;
    begin
	select nvl(apm.get_value(notification_sender.package_id,'NotificationSender'),-1)
	into v_result
	from dual;

        return v_result;
    end notification_sender;

end library_workflow;
/
show errors

create or replace package library_callback 
as
    procedure assign_task_to_submitter (
        task_id 	in number, 
        custom_arg    in varchar2
    );

    procedure assign_task_to_assignee (
        task_id       in number,
        custom_arg    in varchar2
    );

    procedure publish_fire (
        case_id           in number,
        transition_key    in varchar2,
        custom_arg        in varchar2
    );

    procedure clarify_fire (
        case_id           in number,
        transition_key    in varchar2,
        custom_arg        in varchar2
    );

    procedure review_fire (
        case_id          in number,
        transition_key   in varchar2,
        custom_arg       in varchar2
    );

    procedure notification (
        task_id 	in number, 
        custom_arg 	in varchar2, 
        party_to 	in integer, 
        party_from 	in out integer, 
        subject 	in out varchar2, 
        body	 	in out varchar2,
	sent_p		in out char
    );
                    
    procedure notify_admin (
        task_id in number, 
        custom_arg in varchar2
    ) ;
                                                                              
end library_callback;
/
show errors

create or replace package body library_callback 
as
    procedure assign_task_to_assignee (
        task_id       in number,
        custom_arg    in varchar2
    )
    is 
        v_publisher_id          users.user_id%TYPE;
        v_case_id               wf_tasks.case_id%TYPE;
        v_transition_key        wf_tasks.transition_key%TYPE;
    begin
          begin
          -- let's look up the last publisher.
             select o.publisher_id, wt.case_id, wt.transition_key
              into v_publisher_id, v_case_id, v_transition_key
              from sn_objects o, wf_tasks wt, wf_cases wc 
            where wt.task_id = assign_task_to_assignee.task_id
               and wt.case_id = wc.case_id
               and wc.object_id = o.object_id
	       and o.publisher_id is not null
               and (acs_permission.permission_p(o.object_id, o.publisher_id , 'km_publish'))='t';

            workflow_case.add_task_assignment(assign_task_to_assignee.task_id, v_publisher_id);
            workflow_case.add_manual_assignment (
              case_id => v_case_id,
              transition_key => v_transition_key,
              party_id => v_publisher_id
            );

          exception
            when no_data_found then 
            -- find the default assignees for this community

            for v_approval_coordinators in (
                select ac.coordinator_id,
  	             wt.case_id,
  		     wt.transition_key
                  from acs_objects ao, wf_tasks wt,wf_cases wc,
                     approval_coordinators ac
                 where wt.task_id = assign_task_to_assignee.task_id
                   and wt.case_id = wc.case_id
                   and wc.object_id = ao.object_id
                   and ac.package_id = ao.context_id )
            loop
              workflow_case.add_task_assignment(assign_task_to_assignee.task_id, v_approval_coordinators.coordinator_id);
              workflow_case.add_manual_assignment (
                case_id => v_approval_coordinators.case_id,
                transition_key => v_approval_coordinators.transition_key,
                party_id => v_approval_coordinators.coordinator_id
              );
            end loop;
          end;
      end assign_task_to_assignee;

    procedure assign_task_to_submitter (
        task_id 	in number, 
        custom_arg    in varchar2
    )      
    is
    cursor object_cur is
	    select ao.creation_user,
	           wt.case_id,
		   wt.transition_key
              from acs_objects ao, wf_tasks wt,wf_cases wc
             where wt.task_id = assign_task_to_submitter.task_id
               and wt.case_id = wc.case_id
               and wc.object_id = ao.object_id;
        object_rec	 object_cur%ROWTYPE;
    begin
        -- find the creation user of the object (submitter)
        open object_cur;
        fetch object_cur into object_rec;	
        close object_cur;
	
	-- assign the task to the object submitter
        workflow_case.add_task_assignment(assign_task_to_submitter.task_id, object_rec.creation_user);
	workflow_case.add_manual_assignment (
            case_id => object_rec.case_id,
            transition_key => object_rec.transition_key,
            party_id => object_rec.creation_user
        );

    end assign_task_to_submitter;

    procedure publish_fire (
        case_id           in number,
        transition_key    in varchar2,
        custom_arg        in varchar2
    )
    is
        v_journal_id     journal_entries.journal_id%TYPE;
        v_task_id        wf_tasks.task_id%TYPE;
        v_party_from     users.user_id%TYPE;
        v_party_to       users.user_id%TYPE;
        v_author_id      users.user_id%TYPE;
        v_subject        varchar2(4000);
        v_body           varchar2(4000);
        v_request_id     integer;
	v_sent_p         char(1);
    begin
        select distinct(task_id) into v_task_id
	from wf_tasks t
	where case_id = publish_fire.case_id
	and rownum = 1
	order by task_id desc;

	select ao.creation_user, so.original_author_id
	into v_party_to, v_author_id
	from acs_objects ao, wf_cases c, sn_objects so
	where ao.object_id = c.case_id
	and c.case_id = publish_fire.case_id
	and so.object_id = c.object_id;

	v_party_from := -1;
	v_subject := 'default';
	v_body := 'default';
	v_sent_p := 'f';

        library_callback.notification (
            task_id    => v_task_id,
            custom_arg => 'library_published',
            party_to   => v_party_to,
            party_from => v_party_from,
            subject    => v_subject,
            body       => v_body,
            sent_p     => v_sent_p
        );

	if v_party_to != v_author_id then
            library_callback.notification (
                task_id    => v_task_id,
                custom_arg => 'library_published',
                party_to   => v_author_id,
                party_from => v_party_from,
                subject    => v_subject,
                body       => v_body,
                sent_p     => v_sent_p
            );
	end if;

        v_journal_id := journal_entry.new (
            object_id => case_id,
            action => 'modify',
            action_pretty => 'Attribute Change',
            msg => 'Automatic action by the workflow process'
        );
        workflow_case.set_attribute_value (
            journal_id => v_journal_id,
            attribute_name => 'library_object_published',
            value => 't'
        );

    end publish_fire;

    procedure clarify_fire (
        case_id           in number,
        transition_key    in varchar2,
        custom_arg        in varchar2
    )
    is
        v_journal_id     journal_entries.journal_id%TYPE;
    begin
        v_journal_id := journal_entry.new (
            object_id => case_id,
            action => 'modify',
            action_pretty => 'Attribute Change',
            msg => 'Automatic action by the workflow process'
        );
        workflow_case.set_attribute_value (
            journal_id => v_journal_id,
            attribute_name => 'library_object_needs_clarification',
            value => 'f'
        );

    end clarify_fire;

    procedure review_fire (
        case_id          in number,
        transition_key   in varchar2,
        custom_arg       in varchar2
    )
    is
        v_journal_id     journal_entries.journal_id%TYPE;
    begin
        v_journal_id := journal_entry.new (
            object_id => case_id,
            action => 'modify',
            action_pretty => 'Attribute Change',
            msg => 'Automatic action by the workflow process'
        );
        workflow_case.set_attribute_value (
            journal_id => v_journal_id,
            attribute_name => 'verified',
            value => 't'
        );
    end review_fire;

    procedure notification (
        task_id 	in number, 
        custom_arg 	in varchar2, 
        party_to 	in integer, 
        party_from 	in out integer, 
        subject 	in out varchar2, 
        body	 	in out varchar2,
	sent_p		in out char
    )
    is
    begin
        select o.creation_user, j.msg into notification.party_from, notification.body
	from acs_objects o, journal_entries j
	where o.object_id = j.journal_id
	and j.journal_id = (select max(j2.journal_id) from journal_entries j2, wf_tasks t, acs_objects o2
	                    where t.case_id = j2.object_id
			    and t.task_id = notification.task_id
			    and o2.object_id = j2.journal_id
			    and o2.creation_user is not null);

	insert into wf_library_notifications (task_id, template_key, recipient_id, sender_id, body)
	values (notification.task_id, notification.custom_arg, notification.party_to,
                notification.party_from, notification.body);

	sent_p := 't';
    end notification;                                                 
                    
    procedure notify_admin (
        task_id in number, 
        custom_arg in varchar2
    ) 
    is    
    -- uses 2 separate queries to find out who has 'admin' privilege on this package
    -- because we don't want to join any table with acs_object_party_privilege_map 
    -- unless we absolutely have to (in this case, users table)
    -- the first query is only used to gather object info and package id
        cursor object_cur is
            select o.object_id,
                   o.one_line_description,
		   o.context_id as package_id            
              from sn_objects o,
                   wf_cases wc,
                   wf_tasks wt
             where wt.task_id = notify_admin.task_id
               and wc.case_id = wt.case_id
               and wc.object_id = o.object_id;
	object_rec	object_cur%ROWTYPE;
	cursor admin_cur is
	    select u.user_id
	      from users u,
	           acs_object_party_privilege_map m
             where m.object_id = object_rec.package_id
	       and m.party_id = u.user_id
	       and m.privilege = 'admin';	       
        v_request_id        integer;
    begin
        open object_cur;
	fetch object_cur into object_rec;
	close object_cur;
	for admin_rec in admin_cur loop
	    v_request_id := nt.post_request (
                party_from => library_workflow.notification_sender(object_rec.package_id),
                party_to => admin_rec.user_id,
                expand_group => 'f',
                subject => 'Object #'||object_rec.object_id||' -- '||'Assign Object',
                message => 'Subject: '||object_rec.one_line_description||'To do: '||'Assign Object Manage via: '||library_workflow.workflow_url||'task?task_id='||task_id,
                max_retries => 3
            );
	end loop;
    end notify_admin;
                                                                              
end library_callback;
/
show errors