APEX

Hello,

I would like to schedule a job to send email containing the list of all requests that needs to be approved to all the approvers in my workflow without using the activity send-email in workflow as this will be done for each approval request.

I will also need to send the email to potential owners if the task is unassign (in case there is more than one approver).

Below is the procedure that I wrote. However, it seems it is not able to read directly from the apex_tasks table.

Could you please advise how it can be done?

procedure send_mail_approver( p_appid in number, p_task_def_static_id in varchar2) is
/*
This procedure is executed via a job twice daily.
It searches all approvers who have PI requests assigned to them that require approval
*/
m_subject varchar2(4000);
m_body varchar2(32000);
m_mailto varchar2(4000);
m_ccmail varchar2(4000);

--fetch all actual_owner
Cursor c1 is
select actual_owner, xxxpk0_pireq.sfunc_getusremail(actual_owner) actual_owner_mail
from apex_tasks
where state_code = 'ASSIGNED'
and application_id = p_appid
and task_def_static_id = p_task_def_static_id;--'PURCHASE_INDENT_REQUEST';

-- fetch all request that must be sent for approval to the approver
Cursor c2 (p_actual_owner varchar2) is
select detail_pk reqno, subject
from apex_tasks
where state_code = 'ASSIGNED'
and application_id = p_appid
and actual_owner = p_actual_owner
and task_def_static_id = p_task_def_static_id;
begin
For r1 in c1 Loop
m_mailto := r1.actual_owner_mail;
-- m_ccmail := r1.initiator;--p_mailcc;
m_subject := 'You have receive new PI requests to approve';
m_body := 'Dear '|| r1.actual_owner || ',' ||
'You have the following PI request to approve:';

 For r2 in c2(r1.actual\_owner\_mail) Loop  
   m\_body := m\_body || 'Reqno:' || r2.subject;  
 End Loop;

 m\_body := m\_body || 'This is an automatic email. Do not attempt to reply to this email.';

 if m\_mailto is not null then  
   begin  
       xxxpko.send ( p\_sender\_email =>'noreply@xxx.mu',  
                       p\_from => 'pfs-program \<noreply@xxx.mu>',  
                       p\_to => rtrim(m\_mailto),  
                       p\_cc => rtrim(m\_ccmail),  
                       p\_bcc => null,  
                       p\_subject => m\_subject,  
                       p\_body => m\_body,  
                      -- p\_mimetype => 'HTML',  
                       p\_async=>'Y');  
   end;  
 end if;  

End Loop;
end;