Function to Get Customer's Contact Primary and Secondary Email Addresses

CREATE OR REPLACE FUNCTION CUST_EMAIL_FUNC (p_bill_to_contact_id IN NUMBER, p_flag VARCHAR2) RETURN VARCHAR2
IS
V_email  VARCHAR2(1000): = NULL;
BEGIN
IF p_flag = 'PRI' THEN

   select cont_point.email_address
   into V_email
   from  hz_contact_points cont_point,
         hz_cust_account_roles acct_role
   where acct_role.cust_account_role_id = p_bill_to_contact_id
     and acct_role.party_id = cont_point.owner_table_id
     and cont_point.owner_table_name = 'HZ_PARTIES'
     AND cont_point.primary_flag(+) = 'Y'
AND cont_point.status = 'A'
AND acct_role.status = 'A'
     and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'EMAIL';
   
ELSIF p_flag = 'SEC' THEN
   select LISTAGG(cont_point.email_address, ', ') WITHIN GROUP (ORDER BY cont_point.email_address)
   into V_email
   from  hz_contact_points cont_point,
         hz_cust_account_roles acct_role
   where acct_role.cust_account_role_id = p_bill_to_contact_id
     and acct_role.party_id = cont_point.owner_table_id
     and cont_point.owner_table_name = 'HZ_PARTIES'
     AND NVL(cont_point.primary_flag(+),'~') <> 'Y'
AND cont_point.status = 'A'
AND acct_role.status = 'A'
     and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'EMAIL';
  END IF;
RETURN V_email;
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END;