25.1.9.1.11.3.5 Studying Generic Assignee Code

Study package functions that return task owners and their email addresses.

The your_app_pkg contains two generic functions for returning the list of potential owners for a task, as well as a list of their emails. The package body code looks like the following. Notice the OWNER_EMAILS_FOR_TASK queries the APEX_TASK_PARTICIPANTS view using the supplied task id to find the potential owner participants, and joins that view with the APEX_WORKSPACE_APEX_USERS view to get the email addresses of the potential owners. It uses the LISTAGG function with a comma as the delimiter to return a single string of distinct email addresses.

package body your_app_pkg as
    -------------------------------------------------
    function owners_for_task(
        p_task_id   in number,
        p_detail_pk in varchar2)
        return         varchar2
    is
       l_ret varchar2(4000);
    begin
        -- Your custom logic here to return comma-separated
        -- list of case-sensitive user names.
        return l_ret;
    end owners_for_task;
    -------------------------------------------------    
    function owner_emails_for_task(
        p_task_id                in number,
        p_detail_pk              in varchar2)
        return         varchar2
    is
        l_ret varchar2(4000);
    begin
         select listagg(distinct u.email,',')
           into l_ret
           from apex_task_participants p
           join apex_workspace_apex_users u
             on u.user_name = p.participant
          where p.task_id = p_task_id
            and p.participant_type = 'POTENTIAL_OWNER';
         return l_ret;
    end owner_emails_for_task;
end your_app_pkg;