Re: How to join WF_NOTIFICATIONS and WF_NOTIFICATION_OUT tables

Hello everyone,

With MarisE help we have got an excellent script for WF Notifications
Mailer troubleshooting.
Thank you Maris ! Well done!

Enjoy !

PS To run the SQL it is recommended to use GUI SQL interface (I use
SQL*Navigator). It is because of a user_data column based  on complex
type with a lot of useful information inside.

SELECT   n1.notification_id
       ,n1.access_key
       ,n1.MESSAGE_TYPE
       ,n1.message_name
       ,n1.recipient_role
       ,n1.SUBJECT
       ,n1.status
       ,n1.mail_status
       ,t1.*
       ,n1.begin_date
       ,n1.end_date
       ,n1.due_date
       ,n1.responder
       ,n1.user_comment
       ,n1.callback
       ,n1.CONTEXT
       ,n1.original_recipient
       ,n1.from_user
       ,n1.to_user
       ,n1.LANGUAGE
       ,n1.more_info_role
       ,n1.from_role
       ,n1.security_group_id
FROM     (SELECT t.deq_time q_deq_time
               ,t.enq_time q_enq_time
               ,t.q_name
               ,TO_NUMBER ((SELECT str_value
                            FROM   TABLE (t.user_data.header.properties)
                            WHERE  NAME = 'NOTIFICATION_ID'))
                                                           q_notification_id
               , (SELECT str_value
                  FROM   TABLE (t.user_data.header.properties)
                  WHERE  NAME = 'BES_FROM_AGENT') q_bes_from_agent
               , (SELECT str_value
                  FROM   TABLE (t.user_data.header.properties)
                  WHERE  NAME = 'ROLE') q_role
               , (SELECT str_value
                  FROM   TABLE (t.user_data.header.properties)
                  WHERE  NAME = 'BES_RECEIVE_DATE') q_bes_receive_date
               , (SELECT str_value
                  FROM   TABLE (t.user_data.header.properties)
                  WHERE  NAME = 'BES_SEND_DATE') q_bes_send_date
               , (SELECT str_value
                  FROM   TABLE (t.user_data.header.properties)
                  WHERE  NAME = 'BES_EVENT_KEY') q_bes_event_key
               , (SELECT str_value
                  FROM   TABLE (t.user_data.header.properties)
                  WHERE  NAME = 'BES_EVENT_NAME') q_bes_event_name
               ,t.user_data
         FROM   applsys.wf_notification_out t) t1
       ,applsys.wf_notifications n1
WHERE    1 = 1
AND      n1.notification_id = t1.q_notification_id(+)
--  and n1.notification_id in ( 229583, 229501                                 )
--  and n1.message_name='SMBC_NOTIFY_NEW_ASSIGNEE'
 and n1.recipient_role like upper('VJV_TEST')
--  and n1.status='OPEN'
--  and n1.mail_status in ('MAIL','SENT')
--  and n1.mail_status in ('MAIL')
--  and trunc(n1.begin_date) = trunc(sysdate)
-- and (n1.notification_id=231529 or n1.notification_id=229501)
-- and n1.notification_id=231529
ORDER BY n1.begin_date DESC;

Other related posts: