Determining Notification Response Values

If you are using email or the standard web pages to respond to notifications, then the system will automatically prompt you to let you know what the possible values for a response are. However, there are certain circumstances where this isn’t appropriate – a customer I worked with last year wanted all their notifications to be handled via an Oracle Form, which needed to dynamically create a drop-down list for each notification.

Since the Workflow Data Model isn’t really available anywhere yet (it’ll be in the book though!), it’s not immediately apparent to determine where you need to look to find out what the result is, so here’s how.

In order for a notification to use conditional routing, there needs to be an attribute called RESULT. This attribute has a type of LOOKUP and a subtype of RESPOND – i.e. this is something coming back into the Workflow process and the value corresponds to a lookup type. The FORMAT of this attribute contains the internal name of the lookup. All this information is stored in the WF_MESSAGE_ATTRIBUTES table.

In order to get to this table, we need to know what the message is that is linked to the notification. The WF_MESSAGE_ATTRIBUTES table is joined to the WF_MESSAGES table using the MESSAGE_NAME and MESSAGE_TYPE columns on the attributes table to join to the NAME and TYPE columns in the messages table.

Finally, the message needs to be linked to the notification, which is stored in the WF_NOTIFICATIONS table, which joins using the same columns as the join from the messages table to the attributes table. This way, for any notification that you are using, you can fetch a list of what possible responses to the notification are.

Here’s the code:
SELECT wfl.lookup_code
,      wfl.meaning
FROM wf_notifications        wfn
,    wf_messages             wfm
,    wf_message_attributes   wfma
,    wf_lookups              wfl
WHERE wfn.notification_id = 1035
AND   wfn.message_name = wfm.name
AND   wfn.message_type = wfm.type
AND   wfn.message_name = wfma.message_name
AND   wfn.message_type = wfma.message_type
AND   wfma.name = 'RESULT'
AND   wfl.lookup_type = wfma.format

Enjoy!

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