How to join WF_NOTIFICATIONS and WF_NOTIFICATION_OUT tables

Hello List,

I have found for myself that querying a WF_NOTIFICATIONS table is
right thing in order to troubleshoot WF notifications related
problems.
I am using the SQL below. You can use different filtering options etc.
SELECT a.notification_id, a.group_id, a.message_type, a.message_name,
      a.recipient_role, a.status, a.access_key, a.mail_status,
      a.priority, a.begin_date, a.end_date, a.due_date, a.responder,
      a.user_comment, a.callback, a.context, a.original_recipient,
      a.from_user, a.to_user, a.subject, a.language, a.more_info_role,
      a.from_role, a.security_group_id
 FROM APPLSYS.WF_NOTIFICATIONS a
 where 1=1
--  and a.notification_id in ( 157997                                 )
--  and a.message_name='SMBC_NOTIFY_NEW_ASSIGNEE'
 and a.recipient_role like upper('VJV')
--  and a.status='OPEN'
--  and a.mail_status in ('MAIL','SENT')
--  and trunc(a.begin_date) = trunc(sysdate)
 order by
 BEGIN_DATE desc,
 status desc
;

There is another table (actually it is Advance Queuing table) called
WF_NOTIFICATION_OUT. There Oracle Stored most important information
like Mail text (In XML format), mail addresses, etc.
Oracle Notification Mailer in order to find if there is some
notifications to send dequeuing WF_NOTIFICATION_OUT (it is not
querying WF_NOTIFICATIONS table). However after NM send a notification
I updates WF_NOTIFICATIONS table with statues.

Both tables are essential in the process. And it is sounds like a good
idea to join both tables. Only problem is that WF_NOTIFICATION_OUT is
type based table and it isn't straightforward to query of join type
based columns (at least for me).

May be some of you already have that type of SQL?
I would be more them appreciated if you share it with the list.

The tables' columns to join are:

WF_NOTIFICATIONS.notification_id and
WF_NOTIFICATION_OUT.USER_DATA.PROPERTIES[7].STR_VALUE(+)
and WF_NOTIFICATION_OUT.USER_DATA.PROPERTIES[7].NAME='NOTIFICATION_ID'(+)

Thank you in advance,
--
Yury
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html

Other related posts: