Hey,

Are you using a PLSQL Block event action type at all ? (In which case it is possible).

If you’re just using a mail type event, it’d be a lot more complex to do without being able to call some PLSQL logic.

If you’re using PLSQL Logic you basically first need to extract the list of supervisor person IDs, which you can do using

company_pers_assign_api.get_direct_sup_person_list(company_id_ => company_id_,                                                   emp_no_ => emp_no_,  valid_from_ => sysdate)

This gets you the Supervisor(s) Person IDs for the Emp_No and Company_ID you’re parsing as parameters.

With those tokenized person IDs, you can fairly easily then check either their SMTP_MAIL_ADDRESS or their Email Type Comm Methods.

I was hoping that it would be possible to do it with a mail type event. 

It might be possible with a custom event attribute, as you can run a plsql block when the event triggers to generate an attribute usable by the event action.

Where do you currently store the email addresses? Are those against comm methods on the person record, or is it in the email field of the user record?

Emails are in the email field of the user record.

Ok so looked into it, and this requires a custom PL/SQL function being created first, as the custom event attribute expects a PL/SQL context, and can’t context switch to straight SQL (so you can’t use a direct SQL statement in it).

The following PL/SQL function will accept company_id_ and emp_no_ as parameters, and return a list of  emails from the user records of the direct supervisor(s) of the company/emp_no tuple being parsed as parameters.

You’d need to deploy it as a custom util package function and you could then use it in your custom event to automatically retrieve an email list when the event triggers.

function get_supervisor_emails_list(company_id_ in varchar2, emp_no_ in varchar2) RETURN VARCHAR2
IS

  sup_email_list_ varchar2(4000);

BEGIN

select listagg(ifsapp.fnd_user_property_api.get_value(ifsapp.person_info_api.get_user_id(person_id),'SMTP_MAIL_ADDRESS'), ',' on overflow truncate) within group (order by person_id) as email_list
into sup_email_list_
from ifsapp.person_info_all p
where instr('^'||(select ifsapp.company_pers_assign_api.get_direct_sup_person_list(company_id_,emp_no_) from dual)||'^','^'||p.person_id||'^') > 0;

return dummy_

exception
  when others then
    return null;

END get_supervisor_emails_list;

Sorry, slight error in my code above due to me being a bit hasty with copy pasting

function get_supervisor_emails_list(company_id_ in varchar2, emp_no_ in varchar2) RETURN VARCHAR2
IS

  sup_person_list_ varchar2(4000);
  sup_email_list_ varchar2(4000);


BEGIN

sup_person_list_ := ifsapp.company_pers_assign_api.get_direct_sup_person_list(company_id_,emp_no_);

select listagg(ifsapp.fnd_user_property_api.get_value(ifsapp.person_info_api.get_user_id(person_id),'SMTP_MAIL_ADDRESS'), ',' on overflow truncate) within group (order by person_id) as email_list
into sup_email_list_
from ifsapp.person_info_all p
where instr('^'||sup_person_list_||'^','^'||p.person_id||'^') > 0;

return sup_email_list_;

exception
  when others then
    return null;

END get_supervisor_emails_list;