Link WF_NOTIFICATIONS and WF_NOTIFICATION_OUT (queue table)
tables to find where the notification is pending
SELECT status, mail_status, notification_id FROM APPLSYS.WF_NOTIFICATIONS wfn
WHERE 1 = 1 AND wfn.mail_status = 'MAIL' and status in ('OPEN', 'CANCELED')
and exists
(select * from APPLSYS.AQ$WF_NOTIFICATION_OUT wfo
where wfn.notification_id = TO_NUMBER
((SELECT str_value FROM TABLE
(wfo.user_data.header.properties) WHERE
NAME ='NOTIFICATION_ID')
)
);
Also, you can always run $FND_TOP/sql/wfmlrdbg.sql by passing notification_id as parameter to this sql to get all the notification details
