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!

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close