FYI or Response Required notifications – Follow up

This is a quick follow up to a comment posted on the original thread – I had a discussion with some people about the post, and was asked the following (hence the new post):  “Can we use wma.name = ‘RESULT’ instead of wma.subtype=’RESPOND’ as each message that requires a RESPONSE action will have RESULT attribute?”.  Here’s my response:


Not every message that requires a response will have a RESULT attribute, though. The RESULT attribute only applies to notifications which have conditional branching based on a result applied to them.

If you have no result type, but have respond attributes, then the notification still requires a response but does not have a result.

Have a look at this test flow for an example. Launching the flow and then selecting from the WF_NOTIFICATION_ATTRIBUTES table returns one row for the attribute IA1 – there is no RESULT attribute since the notification does not have a result.

Here’s the output:

**** Notifications

NID   Context         Status
----- --------------- --------
40899 NEW_TYP4:1:1692 OPEN

SELECT notification_id, NAME
FROM   wf_notification_attributes
WHERE  notification_id = 40899
/

NOTIFICATION_ID NAME
--------------- ----
          40899 IA1

So, although the notification requires a response, there is no RESULT attribute, and it’s certainly not uncommon to have notifications and messages defined in this way – if the requirement is to capture a response, but not to branch conditionally depending on the result, this is the way to build the Workflow.

Matt

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!

Bulk updating workflow users

Recently, there was a question asked on Metalink about how to update a large number of user preferences in one easy step. The original question was to see how to turn off the email preference for users, in an environment that had been cloned from production.

There are a couple of ways that you could do this, but my preferred way is through the WF_DIRECTORY APIs – I’m currently working with TCA a lot, and so the importance of using the supplied APIs is really being hammered home!

I’ve attached a script which loops through the records in the WF_USERS table, and updates the notification preference to ‘QUERY’ for all the records it finds – if you want to change different preference settings, then you just plug them into the script. Although the API is designed for use with ad-hoc roles, there is no reason why this does not work with standard roles. I tested it this morning on eBS release 11.5.10.2 on a 10g database running on Linux, and it worked fine for me.

Enjoy!

SQL script

DBMS_OUTPUT Limitations

I’ve just hit (for the nth time) the limit in DBMS_OUTPUT.PUT_LINE that you can only have 1000000 characters in the output.  I know that that sounds a lot, but if you’re debugging complex workflows (with lots written out to the screen), and processing multiple flows in the same transaction, then this can easily be blown.  I know that in 10g, you can have an unlimited DBMS_OUTPUT buffer, but not everyone is using that version, or even using SQL*Plus.

So I “googled” (yes, it’s a verb!) the error that I was getting thrown, and (as ever) found this thread on AskTom.

Tom has provided a new PL/SQL package which replicates pretty much what DBMS_OUTPUT does by storing it in a PL/SQL table, and then has a view on top of it to retrieve (and delete) the data from the table.  You can then just select from the view to get the output back.

I’ve copied the code into the attached SQL file, just for ease:

DBMS_OUTPUT alternative

Enjoy!

Matt

Who’s logged in?

I’ve just been asked to come up with a quick script for our DBAs to let them know who has been using the system recently. They want something that they can use to pick up the recent eBS users and their email addresses, so that they can let them know when the system is about to be bounced or shut down.

So, here’s the SQL:

SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')
/

Note, it will only pick up recent eBS users – it doesn’t look at database users who are logged in, so if there is other access to the system then you need to be aware that this isn’t going to be a definitive list. Good starter for ten though.

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