FYI or Response Required notifications
A thread on Metalink some time back asked how to tell whether a notification requires a response or not, and I’ve just found some notes that I took on the subject at the time. [For those without Metalink access, the exact question is "How do I know from the backend what notifications require action and what notifications and just FYI"].
The first thing I thought about the question was “what does it actually ask?” There are two possible things that the person posing the question might be after. Either they want to know what MESSAGES are defined in the system, and do they require a response or not; or they are looking at NOTIFICATIONS that have been sent to users to see whether they require a response. Accordingly, there are two different answers, since the source of the data is completely different.
Let’s take the first possible questions – what messages are there in the system, and do they require a response of not? The key thing in either of the situations is whether the notification has any attributes which require a response or not – in which case the table that needs to be accessed is WF_MESSAGE_ATTRIBUTES which stores all of this information. In order to make the information more understandable, I would also extend the query to look at the WF_ITEM_TYPES_TL and WF_MESSAGES_TL tables, which provide the display name for each of the internal names (message and item type), which makes it much easier to understand which message and item type you are dealing with.
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. This takes care of any messages which have multiple attributes, and at least one of them is a response attribute. Secondly, we need to include any of the messages which have RESPOND attributes, and union the result sets together. I have written the two SELECT statements as a sub-query in my example, so that it is then much easier to sort by whatever order you require. Here’s the code:
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
The second question requires us to expand the query a bit further – we now need to include the notifications that are open in the system, so include WF_NOTIFICATIONS in the query as well. Apart from the join between this table and the others, the only other change that I have made to the query is to include the notification_id in the query as well. Here’s the code for the second query:
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
Hopefully that all answers the question – how do I know what notifications require a response and which do not? It also answers the unwitting question – how do I know what messages require a response and which do not?
As ever, comments more than welcome!
Leave a Reply