wf_notification/wf_notification_out/wf_deferred Link

--
SELECT n.begin_date,
       n.status,
       n.mail_status,
       n.recipient_role,
       de.def_enq_time,
       de.def_deq_time,
       de.def_state,
       ou.out_enq_time,
       ou.out_deq_time,
       ou.out_state
  FROM applsys.wf_notifications n,
       (SELECT d.enq_time def_enq_time,
               d.deq_time def_deq_time,
               TO_NUMBER((SELECT VALUE
                           FROM TABLE(d.user_data.parameter_list)
                          WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
               msg_state def_state
          FROM applsys.aq$wf_deferred d
         WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
       (SELECT o.deq_time out_deq_time,
               o.enq_time out_enq_time,
               TO_NUMBER((SELECT str_value
                           FROM TABLE(o.user_data.header.properties)
                          WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
               msg_state out_state
          FROM applsys.aq$wf_notification_out o) ou
 WHERE n.notification_id = &NOTIFICATION_ID
   AND n.notification_id = de.d_notification_id(+)
   AND n.notification_id = ou.o_notification_id(+)
--
 Ex:
SELECT A.*
   FROM APPLSYS.AQ$WF_NOTIFICATION_OUT A
WHERE A.user_data.get_string_property('NOTIFICATION_ID') = 22048249;

--
wf_notification  package is also very handy

Ex: to get notification body/subject etc.

SELECT wf_notification.getbody(<Notification Id>)
  FROM dual;
--
SELECT wf_notification.getsubject(<Notification Id>)
  FROM dual;

--
Ref: https://me-dba.com/2009/09/10/notification-mailer-troubleshooting-part-ii/