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!
Leave a Reply