--
-- packages/ticket-tracker/sql/ticket-tracker-packages.sql
--
-- 
-- @author Phong Nguyen (phong@arsdigita.com)
-- @author Tony Tseng (tony@arsidigta.com)
-- 
-- @creation-date 2000-11-30
--
-- @cvs-id $Id: ticket-tracker-packages.sql,v 1.8 2002/02/09 02:33:36 donb Exp $
--

----------------------------------
-- PACKAGE HEADERS
----------------------------------

create or replace package ttracker_ticket 
as
    function new (
        ticket_id	 in ttracker_tickets.ticket_id%TYPE      default null,
        object_type	 in acs_objects.object_type%TYPE         default 'ttracker_ticket',
        context_id       in acs_objects.context_id%TYPE          default null,
        subject          in cr_revisions.title%TYPE,              
        description      in varchar2,
        mime_type        in cr_revisions.mime_type%TYPE          default 'text/plain',
        category_id      in ttracker_categories.category_id%TYPE,
        severity         in ttracker_tickets.severity%TYPE,
        priority         in ttracker_tickets.priority%TYPE,
        creation_date    in acs_objects.creation_date%TYPE       default sysdate,
        creation_user    in acs_objects.creation_user%TYPE,       
        creation_ip	 in acs_objects.creation_ip%TYPE         default null
    ) return ttracker_tickets.ticket_id%TYPE;

    procedure delete (
        ticket_id    in ttracker_tickets.ticket_id%TYPE
    );

    function name (
        ticket_id    in ttracker_tickets.ticket_id%TYPE
    ) return varchar2;
        
    -- return 't' if the ticket id is valid and the ticket belongs to the package instance
    function id_valid_p (
        ticket_id    in ttracker_tickets.ticket_id%TYPE,
        package_id   in apm_packages.package_id%TYPE
    ) return char;
    
    -- update mime_type, subject, and description 
    -- (the fields that are not directly stored in ttracker_tickets table)
    -- note this procedure does NOT update the subject field in ttracker_tickets
    procedure update_mime_subj_desc (
        ticket_id    in ttracker_tickets.ticket_id%TYPE,
        mime_type    in cr_revisions.mime_type%TYPE,
        subject      in cr_revisions.title%TYPE,
	description  in varchar2
    );

end ttracker_ticket;
/
show errors


create or replace package ttracker_category 
as

    function new (
        category_id         in ttracker_categories.category_id%TYPE      default null,
        package_id          in ttracker_categories.package_id%TYPE,
        object_type         in acs_objects.object_type%TYPE              default 'ttracker_category',
        creation_date       in acs_objects.creation_date%TYPE            default sysdate,
        creation_user       in acs_objects.creation_user%TYPE            default null,
        creation_ip         in acs_objects.creation_ip%TYPE              default null,
        context_id	    in acs_objects.context_id%TYPE               default null,
        name                in ttracker_categories.name%TYPE,
        default_assignee    in ttracker_categories.default_assignee%TYPE default null,
        description         in ttracker_categories.description%TYPE      default null
    ) return acs_objects.object_id%TYPE;

    procedure delete (
      category_id		in ttracker_categories.category_id%TYPE
    );

    function name (
      category_id		in ttracker_categories.category_id%TYPE
    ) return varchar2;

end ttracker_category;
/
show errors

create or replace package ttracker_option
as
    -- to add a priority or severity option 
    procedure add_option (
        option_id    in ttracker_options.option_id%TYPE         default null,
        package_id   in ttracker_options.package_id%TYPE,
        function     in ttracker_options.function%TYPE,
        name         in ttracker_options.name%TYPE,
        value        in ttracker_options.value%TYPE
    );

    -- to update a priority or severity option 
    procedure update_option (
        option_id    in ttracker_options.option_id%TYPE,
        name         in ttracker_options.name%TYPE,
        value        in ttracker_options.value%TYPE
    ); 

    -- given the package_id, function, and value
    -- return the human readable name of the option
    function option_name (
        package_id   in ttracker_options.package_id%TYPE,
	function     in ttracker_options.function%TYPE,
        value        in ttracker_options.value%TYPE
    ) return ttracker_options.name%TYPE;             

    -- given the package_id, function, and name
    -- return the integer value
    function option_value ( 
        package_id   in ttracker_options.package_id%TYPE,
	function     in ttracker_options.function%TYPE,
	name	     in ttracker_options.value%TYPE
    ) return ttracker_options.value%TYPE;

end ttracker_option;
/
show errors
  
create or replace package ttracker_util 
as
    -- return the value of the NotificationSender parameter
    function notification_sender (
        package_id	      in apm_packages.package_id%TYPE
    ) return parties.party_id%TYPE;

    -- depending on the argument, it can return either absolute or relative url
    -- pointing to acs-workflow
    function workflow_url (
        absolute_p	      in char default 't'
    ) return varchar2;
   
    -- installs the default category and priority/severity info
    procedure install (
        package_id   in apm_packages.package_id%TYPE
    );

end ttracker_util;
/
show errors

-- This package contains callbacks used by acs-workflow
create or replace package ttracker_callback 
as
    -- assign the task to the ticket submitter
    procedure assign_task_to_submitter (
        task_id          in number,
        custom_arg       in varchar2
    );

    -- assign the task to the ticket assignee
    procedure assign_task_to_assignee (
        task_id          in number,
        custom_arg       in varchar2
    );                
  
    -- set need_clarification to 'f'
    procedure clarify_fire (
        case_id          in number,
        transition_key   in varchar2,
        custom_arg       in varchar2
    );

    -- set verified to 't'
    procedure resolve_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
    );               

    -- unassigned callback
    -- email the admin when a task is unassigned
    procedure notify_admin (
        task_id in number, 
        custom_arg in varchar2
    );         
          
end ttracker_callback;
/
show errors

----------------------------------
-- PACKAGE BODIES
----------------------------------

create or replace package body ttracker_ticket
as
    function new (
        ticket_id	 in ttracker_tickets.ticket_id%TYPE      default null,
        object_type	 in acs_objects.object_type%TYPE         default 'ttracker_ticket',
        context_id       in acs_objects.context_id%TYPE          default null,
        subject          in cr_revisions.title%TYPE,              
        description      in varchar2,
        mime_type        in cr_revisions.mime_type%TYPE          default 'text/plain',
        category_id      in ttracker_categories.category_id%TYPE,
        severity         in ttracker_tickets.severity%TYPE,
        priority         in ttracker_tickets.priority%TYPE,
        creation_date    in acs_objects.creation_date%TYPE       default sysdate,
        creation_user    in acs_objects.creation_user%TYPE,
        creation_ip	 in acs_objects.creation_ip%TYPE         default null
    ) return ttracker_tickets.ticket_id%TYPE
    is
        v_ticket_id             ttracker_tickets.ticket_id%TYPE;
        v_case_id               wf_cases.case_id%TYPE;
    begin
        -- create a new acs-message to hold the contents of the ticket
        v_ticket_id := acs_message.new (
            message_id    => new.ticket_id,
            title         => new.subject,
            text          => new.description,
            mime_type     => new.mime_type,
            context_id    => new.context_id,
            object_type   => new.object_type,
            creation_date => new.creation_date,
            creation_user => new.creation_user,
            creation_ip   => new.creation_ip
        );
        -- insert additional ticket info
        insert into ttracker_tickets
            (ticket_id,
             creation_user,
             creation_date,
             category_id,
             subject,
             severity,
             priority)
        values
            (v_ticket_id,
             new.creation_user,
             new.creation_date,
             new.category_id,
             new.subject,
             new.severity,
             new.priority);

        -- create a new case
        v_case_id := workflow_case.new (
            workflow_key => 'ttracker_wf',
            object_id => v_ticket_id,
            creation_user => new.creation_user,
            creation_ip => new.creation_ip
        );

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

        return v_ticket_id;
    end new;

    procedure delete (
        ticket_id    in ttracker_tickets.ticket_id%TYPE
    )
    is
        cursor case_cur is
            select case_id
              from wf_cases 
             where object_id = ticket_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 ticket

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

    function name (
        ticket_id    in ttracker_tickets.ticket_id%TYPE
    ) return varchar2
    is
        v_result        ttracker_tickets.subject%TYPE;
    begin
        select subject into v_result
        from ttracker_tickets
        where ticket_id = name.ticket_id;

        return v_result;
    end name;
        
    function id_valid_p (
        ticket_id    in ttracker_tickets.ticket_id%TYPE,
        package_id   in apm_packages.package_id%TYPE
    ) return char
    is
        v_result        char(1);
    begin
        select decode(count(tt.ticket_id), 0, 'f', 't') into v_result
          from ttracker_tickets tt            
         where exists (select tc.category_id
                         from ttracker_categories tc
                        where tc.category_id = tt.category_id
                          and tc.package_id = id_valid_p.package_id)
           and tt.ticket_id = id_valid_p.ticket_id;

        return v_result;
    end id_valid_p;        

    procedure update_mime_subj_desc (
        ticket_id    in ttracker_tickets.ticket_id%TYPE,
        mime_type    in cr_revisions.mime_type%TYPE,
        subject      in cr_revisions.title%TYPE,
	description  in varchar2
    )
    is
        v_cr_revision_id    cr_revisions.revision_id%TYPE;	
    begin
        v_cr_revision_id := content_revision.new (
            title => subject,
            mime_type => update_mime_subj_desc.mime_type,
            text => description,
            item_id => ticket_id
        );
        content_item.set_live_revision(v_cr_revision_id);
    end update_mime_subj_desc;   
         
end ttracker_ticket;
/
show errors


create or replace package body ttracker_category
as

    function new (
      category_id         in ttracker_categories.category_id%TYPE      default null,
      package_id          in ttracker_categories.package_id%TYPE,
      object_type         in acs_objects.object_type%TYPE              default 'ttracker_category',
      creation_date       in acs_objects.creation_date%TYPE            default sysdate,
      creation_user       in acs_objects.creation_user%TYPE            default null,
      creation_ip         in acs_objects.creation_ip%TYPE              default null,
      context_id	  in acs_objects.context_id%TYPE               default null,
      name                in ttracker_categories.name%TYPE,
      default_assignee    in ttracker_categories.default_assignee%TYPE default null,
      description         in ttracker_categories.description%TYPE      default null
    ) return acs_objects.object_id%TYPE
    is
        v_category_id	acs_objects.object_id%TYPE;
    begin
        v_category_id := acs_object.new (
            object_id => new.category_id,
            object_type => new.object_type,
            creation_date => new.creation_date,
            creation_user => new.creation_user,
			creation_ip => new.creation_ip,
            context_id => new.context_id
        );
        insert into ttracker_categories (category_id, package_id, name, default_assignee, description)
            values (v_category_id, new.package_id, new.name, new.default_assignee, new.description);
        return v_category_id;
    end new;

    procedure delete (
        category_id    in ttracker_categories.category_id%TYPE
    )
    is
        v_ticket_number integer;
    begin
        -- check if any ticket exists for this category before deleting
        select decode(count(ticket_id), 0, 0, 1) into v_ticket_number
          from ttracker_tickets 
         where category_id = ttracker_category.delete.category_id;
         
        if v_ticket_number > 0 then
          raise_application_error(-20000, 'Invalid severity.');          
        end if;

        acs_object.delete(category_id);
    end delete;

    function name (
        category_id    in ttracker_categories.category_id%TYPE
    ) return varchar2
    is
        v_result    ttracker_categories.name%TYPE;
    begin
        select name into v_result 
        from ttracker_categories
        where category_id = name.category_id;

        return v_result;
    end name;
        
end ttracker_category;
/
show errors

create or replace package body ttracker_option
as                           
    procedure add_option (
        option_id    in ttracker_options.option_id%TYPE         default null,
        package_id   in ttracker_options.package_id%TYPE,
        function     in ttracker_options.function%TYPE,
        name         in ttracker_options.name%TYPE,
        value        in ttracker_options.value%TYPE
    )
    is
        v_option_id  ttracker_options.option_id%TYPE;
    begin
        if package_id is null or function is null or name is null or value is null then
            raise_application_error(-2000, 'Arguments, with the exception of option_id, cannot be null');
        end if;

        if option_id is null then
            select ttracker_option_id_sequence.nextval into v_option_id
              from dual;
        else
            v_option_id := add_option.option_id;
        end if;

        insert into ttracker_options (option_id, package_id, function, name, value) values
            (v_option_id, add_option.package_id, add_option.function, add_option.name, add_option.value);

    end add_option;             

    procedure update_option (
        option_id    in ttracker_options.option_id%TYPE,
        name         in ttracker_options.name%TYPE,
        value        in ttracker_options.value%TYPE
    ) 
    is
    begin
        if option_id is null or name is null or value is null then
            raise_application_error(-2000, 'Arguments cannot be null');
        end if;
    
        update ttracker_options 
           set name = update_option.name,
               value = update_option.value
         where option_id = update_option.option_id; 
    
    end update_option;

    function option_name (
        package_id   in ttracker_options.package_id%TYPE,
	function     in ttracker_options.function%TYPE,
        value        in ttracker_options.value%TYPE
    ) return ttracker_options.name%TYPE 
    is
        cursor option_cur is
            select name
              from ttracker_options
             where package_id = option_name.package_id
               and function = option_name.function
               and value = option_name.value;
        option_rec option_cur%ROWTYPE;               
    begin
        open option_cur;
        fetch option_cur into option_rec;
        if option_cur%NOTFOUND then
            close option_cur;
            raise_application_error(-20000, 'Invalid option.');          
        end if;
        close option_cur;        
        return option_rec.name;
    end option_name;
                 
    -- given the package_id, function, and name
    -- return the integer value
    function option_value ( 
        package_id   in ttracker_options.package_id%TYPE,
	function     in ttracker_options.function%TYPE,
	name	     in ttracker_options.value%TYPE
    ) return ttracker_options.value%TYPE
    is
        cursor option_cur is
            select value
              from ttracker_options
             where package_id = option_value.package_id
               and function = option_value.function
               and upper(name) = upper(option_value.name);
        option_rec option_cur%ROWTYPE;               
    begin
        open option_cur;
        fetch option_cur into option_rec;
        if option_cur%NOTFOUND then
            raise_application_error(-20000, 'Invalid option.');
        end if;
        close option_cur;
        return option_rec.value;
    end option_value;
    
end ttracker_option;
/
show errors

create or replace package body ttracker_util
as
    function notification_sender (
        package_id	      in apm_packages.package_id%TYPE
    ) return parties.party_id%TYPE
    is
        cursor value_cur is
            select apv.attr_value
            from apm_parameter_values apv,
                 apm_parameters ap
           where apv.package_id = notification_sender.package_id
             and ap.package_key = 'ticket-tracker'
             and ap.parameter_name = 'NotificationSender'
             and ap.parameter_id = apv.parameter_id;
       value_rec  value_cur%ROWTYPE;
        v_result   parties.party_id%TYPE;
    begin
        open value_cur;
        fetch value_cur into value_rec;
        if value_cur%NOTFOUND then
            v_result := -1;
        else
            v_result := to_number(value_rec.attr_value);
        end if;
        close value_cur;                       
 
        return v_result;

        -- in case somebody tempered with the parameter
        exception
            when others then
                return -1;
    end notification_sender;

    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;                

    procedure install (
        package_id    in apm_packages.package_id%TYPE
    )
    is
        v_category_id   ttracker_categories.category_id%TYPE;
        v_ttracker_url  varchar2(4000);
	v_cat_cnt	integer;
    begin
        -- check if there's any existing category
	-- if yes, quit
        select count(category_id) into v_cat_cnt
	  from ttracker_categories
	 where package_id = install.package_id;
	if v_cat_cnt > 0 then
	  return;
	end if;

        -- create the default category
        v_category_id := ttracker_category.new (
            package_id => install.package_id,
            name => 'General',
            description => 'Default category'
        );

        -- insert the default severity/priority info                        
        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'severity',
            name => 'critical',
            value => 0
        );

        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'severity',
            name => 'serious',
            value => 1
        );

        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'severity',
            name => 'medium',
            value => 2
        );

        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'severity',
            name => 'low',
            value => 3
        );

        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'severity',
            name => 'wishlist',
            value => 4
        );

        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'priority',
            name => 'high',
            value => 0
        );

        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'priority',
            name => 'medium',
            value => 1
        );

        ttracker_option.add_option (
            package_id => install.package_id,
            function => 'priority',
            name => 'low',
            value => 2
        );
        
    end install;

end ttracker_util;
/
show errors

create or replace package body ttracker_callback 
as
    procedure assign_task_to_submitter (
        task_id       in number,
        custom_arg    in varchar2
    )      
    is
        cursor ticket_cur is
	    select tt.creation_user,
	           wt.case_id,
		   wt.transition_key
              from ttracker_tickets tt, 
                   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 = tt.ticket_id;
        ticket_rec	 ticket_cur%ROWTYPE;
    begin
        -- find the creation user of the ticket (submitter)
        open ticket_cur;
        fetch ticket_cur into ticket_rec;	
        close ticket_cur;
	
	-- assign the task to the ticket submitter
        workflow_case.add_task_assignment(assign_task_to_submitter.task_id, ticket_rec.creation_user,'f');
	workflow_case.add_manual_assignment (
            case_id => ticket_rec.case_id,
            role_key => ticket_rec.transition_key,
            party_id => ticket_rec.creation_user
        );

    end assign_task_to_submitter;

    procedure assign_task_to_assignee (
        task_id       in number,
        custom_arg    in varchar2
    )
    is
        cursor assignee_cur is
            select tc.default_assignee,
		   wt.case_id,
		   wt.transition_key
              from wf_tasks wt,
                   wf_cases wc,
                   ttracker_tickets tt,
                   ttracker_categories tc
             where wt.task_id = assign_task_to_assignee.task_id
               and wt.case_id = wc.case_id
               and wc.object_id = tt.ticket_id
               and tt.category_id = tc.category_id;
        assignee_rec assignee_cur%ROWTYPE;
    begin

        open assignee_cur;
        fetch assignee_cur into assignee_rec;
        close assignee_cur;

	if assignee_rec.default_assignee is null then
	    return;
	end if;

        -- assign the task             
	workflow_case.add_task_assignment(assign_task_to_assignee.task_id, assignee_rec.default_assignee);
	workflow_case.add_manual_assignment (
            case_id => assignee_rec.case_id,
            role_key => assignee_rec.transition_key,
            party_id => assignee_rec.default_assignee
        );	        

    end assign_task_to_assignee;

    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 => 'need_clarification',
            value => 'f'
        );
    end clarify_fire;

    procedure resolve_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 resolve_fire;

    procedure notification (
        task_id 	in number, 
        custom_arg 	in varchar2, 
        party_to 	in integer, 
        party_from 	in integer, 
        subject 	in varchar2, 
        body	 	in varchar2
    )
    is
        v_request_id integer;
        v_url           varchar2(1000);
        v_request_id    integer;
        v_body          varchar2;
        v_subject       varchar2;
        v_party_from    parties.party_id%TYPE;
        cursor ticket_cur is
            select tt.ticket_id,
                   tt.subject,
		   tc.package_id
              from ttracker_tickets tt,
	           ttracker_categories tc,
                   wf_tasks wt,
                   wf_cases wc
             where wt.task_id = notification.task_id
               and wt.case_id = wc.case_id
               and wc.object_id = tt.ticket_id
	       and tt.category_id = tc.category_id;
        ticket_rec ticket_cur%ROWTYPE;
    begin
        open ticket_cur;
        fetch ticket_cur into ticket_rec;
        if ticket_cur%NOTFOUND then
            close ticket_cur;
            raise_application_error(-20000, 'Invalid task id');
        end if;
        close ticket_cur;
	
        v_party_from := ttracker_util.notification_sender(ticket_rec.package_id);
        v_subject := 'Ticket #'||ticket_rec.ticket_id||' -- '||custom_arg;
        v_body := 'Subject: '||ticket_rec.subject||'
'||'To do: '||custom_arg||'
'||'Manage via: '||ttracker_util.workflow_url||'task?task_id='||task_id;
 
		-- notification call moved from acs-workflow to here
		-- to retain consistency with postgres version

        v_request_id := acs_mail_nt.post_request (       
            party_from => v_party_from,
            party_to => notification.party_to,
            subject => v_subject,
            message => v_body,
            max_retries => 0
        );

    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 ticket info and package id
        cursor ticket_cur is
            select tt.ticket_id,
                   tt.subject,
		   tc.package_id            
              from ttracker_tickets tt,
                   ttracker_categories tc,
                   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 = tt.ticket_id
	       and tt.category_id = tc.category_id;
	ticket_rec	ticket_cur%ROWTYPE;
	cursor admin_cur is
	    select u.user_id
	      from users u,
	           acs_object_party_privilege_map m
             where m.object_id = ticket_rec.package_id
	       and m.party_id = u.user_id
	       and m.privilege = 'admin';	       
        v_request_id        integer;
    begin
        open ticket_cur;
	fetch ticket_cur into ticket_rec;
	close ticket_cur;
	for admin_rec in admin_cur loop
	    v_request_id := acs_mail_nt.post_request (
                party_from => ttracker_util.notification_sender(ticket_rec.package_id),
                party_to => admin_rec.user_id,
                subject => 'Ticket #'||ticket_rec.ticket_id||' -- '||'Assign Ticket',
                message => 'Subject: '||ticket_rec.subject||'
'||'To do: '||'Assign Ticket'||'
'||'Manage via: '||ttracker_util.workflow_url||'task?task_id='||task_id
            );
	end loop;
    end notify_admin;
                                                                              
end ttracker_callback;
/
show errors