Oracle Workflow Notification Query

Oracle Workflow Notification Query

Firstly, retrieve all the messages from the system which have a message attribute which is does not have a type of RESPOND, but which also do not have any RESPOND attributes.

SELECT *
FROM   ( SELECT DISTINCT ‘FYI’          action
,      wfi.display_name        item_type
,      wfma.message_type       item_type_internal
,      wfmt.display_name       message_name
,      wfma.message_name       message_internal
FROM   wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfma.SUBTYPE     != ‘RESPOND’
AND    wfma.message_type = wfi.name
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
AND    (wfma.message_type, wfma.message_name) NOT IN ( SELECT DISTINCT wfma2.message_type
,      wfma2.message_name
FROM   wf_message_attributes   wfma2
WHERE  wfma2.SUBTYPE      = ‘RESPOND’
)
UNION
SELECT DISTINCT ‘Response Required’
,      wfi.display_name        item_type
,      wfma.message_type       item_type_internal
,      wfmt.display_name       message_name
,      wfma.message_name       message_internal
FROM   wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfma.SUBTYPE     = ‘RESPOND’
AND    wfma.message_type = wfi.name
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
) messages
ORDER BY item_type_internal, message_internal

We now need to include the notifications that are open in the system, so include WF_NOTIFICATIONS in the query as well

SELECT *
FROM   ( SELECT DISTINCT ‘FYI’           action
,      wfi.display_name         item_type
,      wfn.message_type         item_type_internal
,      wfmt.display_name        message_name
,      wfn.message_name         message_internal
,      wfn.notification_id
FROM   wf_notifications        wfn
,      wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfn.message_type  = wfma.message_type
AND    wfn.message_name  = wfma.message_name
AND    wfma.message_type = wfi.name
AND    wfma.SUBTYPE     != ‘RESPOND’
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
AND    (wfn.message_type, wfn.message_name) NOT IN ( SELECT DISTINCT wfn2.message_type
,      wfn2.message_name
FROM   wf_notifications        wfn2
,      wf_message_attributes   wfma2
WHERE  wfn2.message_type  = wfma2.message_type
AND    wfn2.message_name  = wfma2.message_name
AND    wfma2.SUBTYPE      = ‘RESPOND’ )
UNION
SELECT DISTINCT ‘Response Required’
,      wfi.display_name         item_type
,      wfn.message_type         item_type_internal
,      wfmt.display_name        message_name
,      wfn.message_name         message_internal
,      wfn.notification_id
FROM   wf_notifications        wfn
,      wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfn.message_type  = wfma.message_type
AND    wfn.message_name  = wfma.message_name
AND    wfma.message_type = wfi.name
AND    wfma.SUBTYPE      = ‘RESPOND’
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
) notifications
ORDER BY item_type_internal