How to find the Actual Responder Displayed in Notification

Back in February, there was a question asked in the WorkflowFAQ forum about how to find exactly who responded to a notification:

If a notification for one user was closed by another user through access to the first user’s worklist, the name of the second user, who actually took the action, is displayed as the responder.

I am only able to find the Original Recipeint username in the wf_notifications table.

But where will the details of the Actual Responder name displayed in the notification be stored.

The quick answer is that the PROXY_ROLE column in WF_COMMENTS is where the variable WF_ENGINE.context_proxy gets stored once the notification is closed.

Sending a Notification only to the Worklist Web page

There was a question asked on OTN in January about whether there was a way to stop a notification being sent to a user for one particular notification:

Dear All,

We are using Oracle Applications 11i (

Notification is sending to both e-mail and Worklist Web pages.

My Question is ,For a particular Notification i would like to send only to Worklist Web pages (Not to e-mail) . Can I?
Thanks Alot.

This got me thinking about whether I’d already addressed the question – there are a couple of posts which I’ve written over the years which come close, but nothing which quite hits the exact answer.

I think that between the two of those posts, they offer an answer to the problem.  The first post will still send an email, but with no content other than an instruction to log into the application and verify the notification.  The second post provides information on how to make a more intrusive change to the system – this requires more code but seems to fit the problem neater than the first.

My preference would still to be to use a trigger, as described in the second post, which gives a lot more flexibility for future requirements.

Generating a Warning Message for Notification Responses

I was asked recently whether there was any way that I could come up with where a user is presented with a warning message when they respond to a notification.  The way that the client wanted the process to work was:

  • When the user responds to the notification via the Worklist, then a warning message should appear.
  • Once the system has displayed the warning message, the user should confirm their decision by responding again.
  • If the response is not being provided via the Worklist, then no warning should be sent.

Workflow doesn’t offer this functionality as standard, so we need to develop something technical and custom to do this.

You could include a second “confirmation” notification in the process, so that this gets sent as a second notification as soon as the user responds, but that has two problems with it.  Firstly, there is a disconnect between when the user responds to the notification and when the next notification appears.  Secondly, you would need to include some extra logic which determines whether the first notification was responded to via the Worklist or not, and then conditionally branch to send the next notification or to bypass it.  These two limitations mean that this really doesn’t work as a solution.

So, the way that we developed the solution was to include a custom post notification function for that notification, which handles the situation.

Firstly, the PNF checks whether the recipient is working via the Worklist or not.  If the response is not via the Worklist, then the PNF ends indicating success.

We then added an extra attribute to the notification which indicates whether the warning message should be displayed or not.  This uses the standard Yes/No Lookup Type, and defaults to “Y” indicating that the message should be displayed.

The PNF checks the value of this attribute for the notification – if it is “N” then there is nothing further to do, so ends indicating success.  However, if the value is “Y” then we need to display the warning message to the user.  The PNF sets the attribute to “N” so that when the user views the notification a second time, the warning message is no longer displayed.  The code then retrieves the message to display from FND messages (we could hard code it in the PNF, but if the wording changes in the future, this would require a technical change, which I prefer to avoid if necessary), and sets it as an error message.  The last thing that the PNF does is to return an error, which is then displayed to the user as the warning message.

There are two downsides to this approach, however.  Firstly, the warning message will always be prefixed with an “Error” since that is what the code has actually returned.  There is no way that you can code round this, however.  Secondly, there is always a chance that the user will work in the following way

  • Respond to the notification
  • PNF displays a warning message to the user
  • User does not immediately respond
  • At a later point, the user responds again
  • Since the warning message has already been displayed, it does not get displayed again

We could code round this issue by setting the attribute to a date including the time when the PNF fires the first time.  When the PNF fires again, a check could be made against the current date and time versus the value in the attribute – if the second action is reasonably close to the first, then don’t display the warning, otherwise repeat the warning message.

The only reason we didn’t adopt this approach was that the business logic was quite clear that as long as the warning message was displayed once, that was sufficient from an audit perspective.  Personally, I would have preferred to store a date value, as that also provides extra auditing, as the notification attribute will also include the date and time that the warning message was displayed.  However, the client didn’t want to adopt this approach, and since it was their development team building the code, they wanted the simplest solution possible.

If anyone can think of an alternative approach to displaying a warning message, please let me know 🙂

Sending Notifications in Multiple Languages

I was asked recently about how to send Workflow Notifications in different languages, dependent on the language preference of the recipient, so here’s my response in more detail.

Firstly, we need to understand how Workflow determines which language to use.  The default language will be American (US), since this is the “primary” language that Oracle uses.  For notifications, the Workflow Engine will look at the user preferences, and if they aren’t set, the global Workflow preferences – if the notification has not been installed in that language, then the system falls back to the default and uses American again.

Once you have the text converted into a different language, then you need to include them in the Workflow definition – I’m going to assume that you already have the translated text, so will skip my attempt to translate from one language to another!

The supported method for doing this is as follows.  Firstly, change the NLS_LANG environment variable for your Workflow Builder machine:

  • Run regedit to edit the registry
  • Locate the NLS_LANG setting for the Oracle Workflow Home under HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
  • Change the value to the new language, in the format LANGUAGE_TERRITORY.CHARSET, e.g.

Editing NLS_LANG via regedit

Once you have changed the language, start Workflow Builder and create a new version of the Workflow definition which includes the translated text.  You can translate as much or as little as you wish  – message subjects and bodies as well as the display name for any object can be translated as necessary.

With the saved definition containing your translations, you can now save that version to the database using either the Workflow Builder or the command line Workflow Definitions Loader utility.  Again, if you are using the Workflow Definitions Loader, you MUST change the NLS_LANG setting to make sure that the right language is loaded.  This time, you should change the NLS_LANG setting to the following format:


Note that there is no need to include the language in the setting, because that is stored in the .wft file definition – however the leading underscore MUST be included.  If you have defined the translations using a UTF8 character set, then you can set the NLS_LANG to just


and then load the definition.

That is the only supported method of loading a translation for Workflow into the database, but there is a much simpler way, albeit one which has marginally more risk.

For Notifications, the message body and subject are all defined in the WF_MESSAGES_TL table within the Workflow schema.  When you deploy a translated definition to the database, any existing records are updated in the table, and any missing records are created as necessary.  So, as a quick and simple way of deploying a translation, you could just insert or update WF_MESSAGES_TL directly with the translated text.

There are three main downsides that I can think of with this approach, however.  Firstly, there are support issues if there was a problem – since you are not using the supported mechanism that Oracle provides, then I’d be astounded if Oracle Support will help if there are any later issues.

Secondly, there is no source controlled file which contains the translations – if you have defined a flat file, then you can version control the change, but this approach does not involve different Workflow files, so there is nothing from a Workflow perspective to version control.

Finally, if there are any changes to be made in the future, I would find it easier to change a Workflow definition than to have to change a SQL script which did the create / update of the translated record.  That might be a minor point, but is one which is worth considering – how will someone maintain these changes in the future?

The main upside to these issues, however, is that there is no need to make a change to the registry to force the different language.  This is a significant upside to the approach 🙂

How can I stop one notification going by email to one particular person?

I was asked recently whether there was any way to stop one particular notification going to one particular user via email – the user wanted to receive all their other notifications via email, but not this one.

Since the user wanted to receive all their other notifications via email, it wasn’t a case of being able to change their notification preference, since that would stop all the others going out as well.  We could consider flicking the profile before sending the notification and then changing it back immediately afterwards, but I’m not convinced that this would work.  Also, it’s a pretty intrusive change to make to process, since you would need new logic before and after the notification activity.

So, there are two other ways that the client could do this.

Firstly, they could write a trigger on WF_NOTIFICATIONS which fires when a new notification is created.  The trigger would check whether the recipient is the user we are interested in and the notification type being created, and if so immediately set the MAIL_STATUS to SENT.  This would fool the mailer into believing that the notification had already been emailed, so would never go out via email.

The second approach would be to change the Workflow definition and include a post notification function for the one notification which performs the same logic – if the user is the one person who doesn’t want to receive the notification via email, then update the MAIL_STATUS to SENT again to stop the email being sent.

Both of these approaches have pros and cons.  Creating a trigger on the table may introduce a performance overhead – every notification which is created would need to be checked to determine whether to suppress the email being sent.  Creating a post-notification function means that changes need to be made to the Workflow definition, and the support guidelines would need to be checked before making changes to a seeded notification.  Also, if the notification already has a post notification function, then you would need to either change that PNF, or create a wrapper which calls the original PNF and then does the new logic.

There were three questions that I put to the client before recommending an approach to adopt:

  1. Is it possible / likely that more than one user will want this functionality?
  2. Is is possible / likely that the users might change their minds about whether they want this enabled or disabled?
  3. Is it possible / likely that the user(s) might want to include different notifications?

If the answer to the last question is “No”, then I would change the existing Workflow definition and include a post notification function.  If the answers to the other questions are “Yes”, but it still only applies to one notification, then I would still create a PNF to perform the logic, but build extra flexibility into the logic.  For example, I would also have a profile option set at the right level which indicates whether emails should be suppressed or not, which provides flexibility and ease of use to enable / disable the suppression of emails via the front-end screens rather than needing a coding change.

If, however, the answer to the last question is “Yes”, then I would define a trigger on the table which can then fire under multiple scenarios.  I would define a value set which dictates which notifications should be suppressed (either for a whole item type or on an individual notification basis), and have the trigger check the value set first to see whether this notification is one where email might not be needed.  If the notification is one which shouldn’t be emailed, then again I would have a profile option for the user / responsibility / org / site which determines whether this particular notification should not be emailed.  This may be more processor intensive than a quick and easy check of one thing, but it provides the most flexibility to the solution – no coding changes are required to add / remove notifications or people to the list, which means that changes can be implemented quickly and easily without the need for further testing to be performed.

In the end, the client adopted the post notification function approach and decided not to make it widely known that this was a possibility.  It may be that in future years, more notifications / users are required, at which point they should revisit the recommendations and switch to the trigger approach.  There is nothing wrong with the way they have gone – it meets their current requirements, but if those requirements change significantly in the future then a review will be required.

Finding the Response to a Notification

A while back, I was asked for advice on finding the result of a notification within the process:

Can you please help me, how to capture the result of notification approval?  My notification has result type approval.

My first response was to question why the client needed to know the result within the process.  Merely by having a result for the notification, the knowledge is automatically communicated back to the process, which can then have conditional branching based on the response provided.  If the result is needed further down the line as an attribute, then a standard ASSIGN activity could be used to store the result in an attribute.

The problem with this approach is that if the process models multiple results on the same transition branch (i.e. by using a <Default> or <Any> transition), then there is no easy way to determine which of the multiple responses was actually provided.  For example, imagine that the notification has a lookup type as a response which has lookup codes defined which mean “Approved”, “Finally Approved”, “Rejected” and “Resubmit”.  In the process flow, the notification has a transition defined for “Rejected”, one for “Resubmit” and a “default” transition which manages the two approval routes.  If the business requirement is that the only thing that we need to capture is the “type” of response (i.e. approval or rejection) then we can still take the same approach.  If, however, the business requirement is that the exact response is required rather than the type of response, then the response needs to be captured in a different method.

There are two approaches that can be adopted to find the exact response to the notification, both requiring custom PL/SQL.  Firstly, you can define an activity in the process which accesses the Workflow tables directly to find the previous notification and then find the response to the notification.  In this case, you will need to hard-code in the PL/SQL the type of message that you are looking for, and if there is any change to the Workflow process at a later stage, the PL/SQL will need to be modified to handle the change of notification type.

The second approach, which is the one that I would recommend is to write a custom post-notification function (PNF).  Within the PNF, you can find the details of the response directly, and perform your processing logic.  Additionally, because the code can be written in a generic manner, the PNF can be re-used for any number of scenarios where the result is required.

The developer for the client followed this advice, but still had problems trying to find the result:

I attached the function to the notification. in the function I’m trying to capture the result using getItemattrtext

lc_result := wf_engine.getitemattrtext( itemtype => itemtype,
itemkey => itemkey,
aname => 'RESULT'

I always get null

The problem here, which is an easy fix is that he was using the wrong API to fetch the result.  When the notification is being responded to, the result won’t be stored in item attributes. That would only happen once the processing of the post notification function is complete.  The way to find the result is to firstly find the notification ID using WF_ENGINE.context_nid.

Once you know the notification ID, call WF_NOTIFICATION.getAttrText to find the attribute from the notification. You’ll need to do that for the RESULT attribute and any other attributes on the notification that the business requires.

Why would you need the result?

After some discussion, we finally got to the business requirements, which then meant that we could design an effective solution – one of my bugbears about providing support and advice is that I would much prefer for someone to ask “what is the best way to solve the problem?” rather than “how do I do XYZ?” which means that some times you go off down the wrong path (see this post featuring a video by Tom Kyte on the same issue!).

The business requirements were that the notification has a “comments” field, which if the notification is being rejected must be completed to conform to the business processing requirements.  Therefore, the check needs to be that when the notification is responded to, and it is being rejected, and the comments field has not been completed, then an error should be raised.  This can only be done effectively using a post notification function – if I had known the business requirements from the start, then I would not have suggested by first approach.

Anyway, that’s how to find the response in different ways – depending on the business requirements, either one may be the right approach to adopt.

Suppress Workflow Cancellation without using the global setting

Recently, I got an email asking me about whether there was a way to suppress the cancellation notification on an Item Type basis – the business had a requirement that for certain processes, the cancellation message needed to be sent, whereas for others, the message needed to be suppressed:

I am wondering if it is possible to stop the workflow cancellation from being sent to a user when he workflow is cancelled. I realize there is a global profile setting but I can’t do it globally. It is a custom workflow that I am developing. I was looking into possibly using the #wfm_cancel template and hard code an email address into the peformer but that didn’t seem to work. Has anyone done this type of thing before or have thoughts on how this might be achieved.

Some years back, the global preference didn’t even exist – the cancellation message was always sent, much to the consternation of some of my clients.  The way that we dealt with the problem to suppress all the cancellation messages was to create a database trigger which fired when the record in WF_NOTIFICATIONS is updated.

When the global preference was introduced, this trigger became irrelevant and was disabled.  However, if there is a requirement to provide a conditional suppression of the cancellation message, the trigger can be extended to provide this functionality.

Firstly, to remove any need for hard-coding a list of messages which need to have the cancellation message suppressed, define a value set which will contain the notifications which we want to suppress cancellation messages.  The values in the value set can be either the item type internal name (if all the messages in the item type should not have cancellation messages) or the internal names of the item type and the message with a colon delimiter if a specific message should have the cancellation suppressed.

Once the values have been defined in the value set, then create a trigger on WF_NOTIFICATIONS to check whether the message is in the value set.  If it is in the value set, and a response is not required, and the notification is being cancelled, then update the record so that the notification appears to have been sent already by the notification mailer:



  dummy PLS_INTEGER;
  v_suppress_ntf          VARCHAR2(10);

  CURSOR c_check_value_set ( cp_vs_name   IN VARCHAR2
                           , cp_type_name IN VARCHAR2
                           , cp_msg_name  IN VARCHAR2 ) IS
    FROM   fnd_flex_values_vl       ffvv
    JOIN   fnd_flex_value_sets      ffvs
           ON ( ffvv.flex_value_set_id = ffvs.flex_value_set_id )
    WHERE  ffvs.flex_value_set_name = cp_vs_name
    AND    (    ffvv.flex_value = cp_type_name
            OR  ffvv.flex_value = cp_type_name||':'||cp_msg_name
    AND    NVL(ffvv.enabled_flag,'N') = 'Y'
    AND    SYSDATE BETWEEN NVL(ffvv.start_date_active,SYSDATE - 1)
                       AND NVL(ffvv.end_date_active,SYSDATE + 1);


  IF     :new.status      = 'CANCELED'
     AND :old.status      = 'OPEN'
     AND :new.mail_status = 'MAIL'
     AND :old.mail_status = 'SENT' THEN

      -- Check whether the notification type is in the Value Set
      OPEN  c_check_value_set ( cp_vs_name   => 'XXX_SUPPRESS_LIST_VS'
                              , cp_type_name => :new.message_type
                              , cp_msg_name  => :new.message_name );
      FETCH c_check_value_set INTO v_suppress_ntf;
      CLOSE c_check_value_set;

      IF v_suppress_ntf = 'EXISTS' THEN
        -- Record is included in the value set, so need to include it
        -- Check that the notification expects a response otherwise
        -- do not cancel it

        SELECT 1
        INTO   dummy
        FROM   DUAL
                        FROM   wf_message_attributes wma
                        WHERE  wma.message_name = :new.message_name
                        AND    wma.message_type = :new.message_type
                        AND    wma.subtype = 'RESPOND' );

        :new.mail_status := 'SENT';
      END IF;

        -- Not a response, so no need to cancel it


As ever, if you are relying on the code sample here, it is provided without any warranty – test it fully before deploying to a production environment.

SQL script

Most useless enhancement in R12?

Just a quick post, since it’s been a while – I’ve been VERY busy recently, and am only now taking a decent break with the family.

Anyway – reading some of the R12 documentation yesterday, I saw that there is a new “enhancement” to Workflow Notifications in R12, which I suggest is the most useless enhancement for a while.

In R12, there is now a facility to use a document attribute as the subject of an email, as long as the subject that is generated is less than 240 characters long.  The “idea” seems to be so that you can “easily” include code top make the subject of a Notification dynamic.  Now, forgive me if I’m wrong, but couldn’t you do that in EVERY version of Workflow, by using a text attribute??

What benefit does a document for a subject line give you?  I can think of only one – you no longer need to include a PL/SQL activity within the process to set the attribute that is used for the Notification subject.  That’s the only one I can think of – and personally, I would rather see an explicit step in the process which says “Set Subject” (or the like) rather than hiding the place where the subject is determined.

I can’t see a situation where I will ever be looking to use a document API instead of setting a text attribute, but you never know – if someone can come up with a reason why you should use it, please let me know! 🙂

Displaying the history of a Notification

A thread on OTN recently (which I missed while I was ill) asked about displaying the history of a notification, and when I got round to it, I recalled the same thing being asked in September on Metalink here.

However, I knew that I’d addressed the problem somewhere, but didn’t know whether it was on OTN, WorkflowFAQ or on Metalink.  So, to make sure that I can find it again, here’s the contents of the post from Metalink:

From:  Dmitry Lidvansky  18-Sep-08 09:29
Subject: how to select notification history (?)

I need a select statement that returns notification history as it shown in notification: (see image)

please help

it seems that WF_ITEM_ACTIVITY_STATUSES/WF_ITEM_ACTIVITY_STATUSES_H does not contain all neccessary information for example, Delegate action

From: Matthew Searle 18-Sep-08 10:39
Subject: Re : how to select notification history (?)


This should do the trick:

2                                     , p_disp_type IN VARCHAR2 DEFAULT
                                           WF_NOTIFICATION.doc_text )
                    RETURN VARCHAR2 AS
3   v_hist VARCHAR2(32767);
5   Wf_Notification.GetComments2 ( p_nid => p_nid
6                                , p_display_type => NVL(p_disp_type
                                      , WF_NOTIFICATION.doc_text)
7                                , p_hide_reassign => 'Y'
8                                , p_hide_requestinfo => 'Y'
9                                , p_action_history => v_hist );
11   RETURN v_hist;
13 END get_hist;

APPS@SOLDEV2 on 18-SEP-2008 10:43:34> select get_hist(1574024) from dual;

Action History
Num : Action Date : Action : From : To : Details
1 : 17-JUL-2008 12:03:45 : Submit : SYSADMIN : Searle, Matthew :
2 : 18-SEP-2008 10:21:00 : Request Information : Searle, Matthew : SEARLEM4B : Hello

From: Dmitry Lidvansky 19-Sep-08 10:10
Subject: Re : Re : how to select notification history (?)

3 v_hist VARCHAR2(32767);

that means notification history cannot be longer than 32767 ?
what if it will be so ?

From: Matthew Searle 22-Sep-08 11:50
Subject: Re : Re : Re : how to select notification history (?)

If it’s over 32767, then it will error 🙂

The API returns a VARCHAR2, so that’s the limit – I’ve not tested it to see what happens if it’s too long.

If you want something that will cater for something bigger than 32K then you will need to look at the underlying code and pick out the cursor(s) it uses, then use that instead.

So, if you need to know how to display the full history of a notification from the notification ID, that’s how to do it.

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 = ‘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

--------------- ----
          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.


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.