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 🙂

Using FNDLOAD with examples

This is a post which has been sitting in my “to-do” list for a LONG time 🙂  Every time I need to download or upload configuration files into eBusiness Suite, I either have to trawl through the internet, or the documentation, or hope that someone near me has got an example handy, because I can never remember immediately which script I need to run to do what I want it to!

So, in one easy to read place, here’s my quick guide to using FNDLOAD – note that each command should be entered all on one line.

Before looking at the examples, there are a few things to be aware of.

  1. Pay careful attention when downloading Menus or Responsibilities.  If there are several developers working on Responsibilities and Menus, then you should be even more careful.  If Menus and / or Responsibilities are migrated, then you may end up with forms and functions in the new environment which are not ready for use in that environment.
  2. Be very careful when downloading flexfields which reference value sets with independent values.  For example, if you are migrating GL Segment Codes, then you will extract all the test data in GL codes which might not be applicable for the new environment.
  3. FNDLOAD provides a number of variations for each extract – the examples below are not meant to be definitive, so please feel free to amend the examples as you find necessary.  For example, you can restrict the download and uploads to specific segments within Descriptive Flex Fields.
  4. Please test your FNDLOAD properly, to ensure that you do not get any unexpected data extracted.  Before uploading the extract into a new environment, make sure that the file only contains data that you want to migrate to the new environment.

So, here’s my examples on how to use FNDLOAD…

Read more

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 do you know Business Event is fired?

Recently, there was a question on OTN about how you can find out what Business Event has fired in a certain circumstance:

When a transaction is done in Self Service, how to know which business event is fired? We use status monitor to see the transactions of the workflow but how to know the details of the business event if there is no workflow process involved in the transaction?

The response which was given was to check WF_DEFERRED where any deferred Subscriptions are stored, but doesn’t address what Events fire where there are Subscriptions which are not deferred, or Events where there are no Subscriptions defined.  So, what’s the best way to find out what Events are firing where the Subscriptions are not deferred?

Using a development environment (or any environment other than Production), you can install some logging code and a custom Event Subscription which can provide the information for you.

Firstly, define a custom Subscription to the “any” event ( to call a new function which I’ll describe in the next step.  The Subscription should fire when an Event is raised internally, since this is the most common method for raising an Event.  If you are expecting messages to arrive from an external source, then define another Subscription for an external source, which calls the same function.

Secondly, write a custom function which will be invoked from your Subscription(s) – make sure that the function conforms to the standard API signature (see the Workflow Documentation for more details).  The function should retrieve the EVENT_NAME from the inbound event parameter ADT and then store this information in a table or other logging mechanism, and then end.


For EVERY event which now fires, the any event will fire and call your function.  The function will then store the information, or log it somewhere, depending on your exact code.  You should then perform the transaction that you are interested in, and check what information the function recorded.

Once you have completed the test and determined which event(s) were fired, you should revisit the any event and either disable or delete the Subscription – otherwise your logging mechanism will get overloaded with all the Events which will fire!

Can I change the display name for an attachment link?

Some time last year, I was asked about adding different attachments to notifications – something that has been discussed in the forum a number of times. This time, I was asked about whether it was possible to modify the display name, so that each attachment link contained the name of the file that had been attached:


I have a requirement to send a notification with attachment but attachment name should be attachment name. Is there any way to change the display name of the Attachment?

This is something that was also asked in the forum in June last year – where there are multiple attachments (or even if there is a single attachment), users like to see the details of the attachment rather than a generic “attached file” link, for example.

When the notification is built, any attached documents are included as attributes to the message.  The link to the attachment is created using the display name for the attribute in the text.  It is possible to modify the display name of an attribute programmatically (although this is NOT supported by Oracle since it would be a direct table update), BUT attributes aren’t versioned.  There is only one definition of an attribute within the process definition, so if we changed the display name, this would impact EVERY notification that uses the same message.

So, the quick and simple answer is “No”, and hopefully this quick post explains why you can’t.

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.

Reusing a process in Workflow

I was recently a question which took me back to my very first Workflow project where we needed to re-use a sub-process repeatedly:

When I try to save the workflow, I am getting the error below:

What’s the problem?

The error is caused because Workflow will not allow the developer to re-use a sub-process within the same hierarchy, i.e. Start -> SUB1 -> SUB2 -> SUB1 -> End.  When I did my first Workflow project, we had a very complex process flow which managed sending messages via AQ from one system to a transformation hub, which then passed them onto a back-office system.  What was a simple message from the front-end became a series of simple messages in the back-end, and Workflow was used to manage the co-ordination between the systems.

What we found, however, was that in the front-end, we could build a message which allowed the user to enter a mailing address for the customer, for the product that the customer was buying (so different products could be posted to different addresses), for a proxy for a product and so forth – essentially we had four or five different levels where we wanted to do the same thing.  Unfortunately, because the “create an address” process could be called a number of times within the same hierarchy, we hit the limitation in Workflow that you cannot reuse the process.  As a workaround, we ended up creating copies of the sub-process and naming them differently (we added a different letter to the name), which became pretty annoying when it came to maintenance later on – if we had to make a change to one sub-process, we had to make sure that we were making the same change to each copy as well….

Why can’t you do it?

Here’s the background about WHY you can’t do it – feel free to skip ahead to my thoughts on bypassing the problem now.

Because of the way that the Workflow definition is stored in the database, if the same process is re-used, there is no way that the Workflow Engine can determine which time through the activities are being run.  For a single activity, when it is revisited, the previous visit is archived in the Workflow history tables.  For a series of activities (i.e. a sub-process), the same holds true when it is revisited – the whole process is recorded in the history tables.  However, there is a big difference between revisiting a process and reusing it.  If you are reusing the process, then there is nothing to archive – the previous use should remain in the current run.  When the activity then completes, the WF_ITEM_ACTIVITY_STATUSES table would have two records for the same activity – the first run and then the re-used process.  Sadly, the code which manages the update cannot take the two into account, so wouldn’t know which record to update.

If Workflow was being written today, then if the activity statuses used TIMESTAMP rather than a DATE to store the execution time (or even included a sequence number), then the Workflow Engine could more easily determine which record to update, to help bypass this limitation.  However, if you had a parallel branch, which routed to two identical re-used processes (I can’t see WHY you would do that, but it could be possible), then you hit the same limitation again – how do you identify within the Workflow tables which activity in the process is running???  Overall, it makes sense to include the limitation, IMO.

Ways Round The Problem

If there are only going to be a limited number of times that the process is going to be re-used, then I would be inclined to make a copy of the process and rename it slightly and use that.  That process is significantly easier if you know that the process is finalized, so that you won’t be needing to make the same change to multiple copies.  If you are going this route, then you need to ensure that the different process copies are sufficiently clearly labelled / documented / commented to allow you (or someone else) to maintain the definition going forwards.

If there are a large number of processes that need to be copied (I remember having copies A through to K of one sub-process!), then I would define the process as a separate, runnable process which is invoked as a child process with it’s own item type and item key.  There are no limitations on the number of times that the process can be re-used, and if the process is still in the same item type, then when it is created, the item attribute definitions will be the same – you just need to include something in your code which copies them from one instance to another.

One downside of implementing a parent-child relationship, however, is that when you look at the entire history of the process, it isn’t going to be all in one place – there will be some parts recorded in one process and others in other bits.  Also, I would be wary of using the standard WAITFORFLOW / CONTINUEFLOW activities to manage the flow, if there is a way that there could be more than one waiting process.  Instead, I would define some custom business events – have the main flow wait to receive the event, and the child flows raise the event.  This way, when you raise the event, you can pass attribute values easily back into the parent without having to directly call the WF_ENGINE APIs.


If I were building that first project from scratch, there are many things that I would change.  This would definitely be one of those.

Why isn’t the approval history available to all members of a role?

I recently had an email asking for help with trying to work out why a notification wasn’t showing the approval history:

I have a custom Workflow which has notification where in I am using Adhoc role to send notification to multiple users. The Expand roles checkbox is checked.
Now the issue is the Action History table which comes in the approval notifications does not appear in the notification sent to each user.  The history is visible to the user who was added first to the adhoc role (where the Notification Group ID = Notification ID), but for all the other users the history table itself is not shown in the notification.

I have tried using WF_NOTIFICATION(HISTORY) in the workflow message but this is unsuccessful.

This intrigued me for a while about why the history would appear for one user but not for any others, when the content should obviously be the same.  Then I considered the issue a bit more, and this turns out to be standard functionality.

Here’s a more detailed scenario.  A notification is sent to a role, which has four users assigned to the role.  The notification requires a response, and the “Expand Roles” checkbox is ticked so that each user receives their own copy of the notification.  The notification is accepted as approved when either 75% of the recipients have approved it or 50% have rejected it.  Within the notification, there is a history table.  Over the following few days, the following activities take place:

  • Day 1 – Within five minutes of receiving the notification, the first approver responds with an approval.
  • Day 1 – Within five minutes of receiving the notification, the fourth approver rejects the notification.
  • Day 2 – The second approver asks for more information from the person who submitted the request.
  • Day 2 – The third approver asks for more information from the first approver.
  • Day 3 – The first approver answers the question from the third approver.
  • Day 5 – The person who submitted the request responds to the question from the second approver.
  • Day 5 – The second approver approves the request.
  • Approval / rejection from the third approver is still outstanding.

At this stage, what should the approval history be showing?  As far as the Workflow process is concerned, the notification (although they all have different notification IDs) can only have one history.  So, what is the history?  Is the history of the notification which was sent to the first approver the same as that sent to the second?

At some stage, the Workflow product development team decided that it made sense that a notification could only have one history, therefore even when the role was expanded, only the first person in the role would have the history.  So, what was happening was expected functionality – only that one person will generate history in the notification.   (It turns out that there is a brief mention of this being standard functionality in Metalink note 431219.1)

So, what can be done about it?

There isn’t much that can really be done to circumvent the standard functionality.  I discussed the issue with another Certified Workflow Expert that I know and bounced a few ideas back and forth.

The first suggestion he made was to use the special attribute #RELATED_HISTORY on the notification, which allows you to include the history from one notification in another notification.  His thoughts were that if a post notification function were included, it might be possible to set the attribute on the second and third copies so that they include the history of the first approval notification.  However, when we looked at it a bit more, it became obvious that this idea won’t work.  The #RELATED_HISTORY attribute can only be used to associate different notifications together, since it needs to be set to the label for the notification to relate, and the attribute only works on an FYI notification.

The next suggestion was that a single notification could be sent (i.e. without expanding the roles) to an email distribution list.  This would allow the user to receive what appears to be their own copy of the notification and act on it, but means that there is no way that the voting requirements could be met, and would only allow a response via email if the flag was set to allow forwarded responses, since any response would come from a different email address to the one that was notified.  Depending on the exact business requirements, this may or may not meet the bill.

So, it comes to the final suggestion.  Rather than including the standard history within the notification, we could suppress it from the first notification and then replace it with a custom table.  Including an attribute as a PL/SQL document in the code to generate a table which provides the history of all the notifications would enable all copies of the notification to include an up-to-date history for all copies of the notification.  Setting the document ID to equal the notification ID would mean that you can programmatically determine the group ID for the notification and build a complete history of the process.

This turned out to be the solution that the client wanted, needed and implemented in the end, but for some clients it would be overkill.  I’ll ask for some screenshots to include, but am not hopeful given their penchant for security – failing that, I’ll try to come up with some examples of how we did it.

Why isn’t the Workflow Owner an LOV in the admin pages?

I was recently asked about why the LOV for Workflow owner wasn’t “working” for a user:

…Everything is working fine except the fact that when I go to the notifications Page from the WF Admin responsibility, I can only search for my own Workflows.  I can enter the Item Type and the internal name, but the field Workflow Owned By is not a LOV.  The field is appearing as a hard-coded value of my user name, so I cannot see any processes launched by anyone else.

The answer is that the ability to view processes which are owned by another user is restricted to either the user who has been set as the Workflow administrator, or if the administrator is set to a responsibility the user has been granted that responsibility.

In this case, the user was telling me that they had been granted the “WF Admin responsibility”, but that isn’t necessarily the same as being a Workflow administrator.  For example, the Workflow administrator might have been set to be an individual user (in standalone Workflow this was always set as SYSADMIN as standard).

If you have been granted the standard Workflow Administrator responsibility, you will be able to navigate the administration pages, although you won’t be able to modify any of those settings.  So, navigate to the Admin tab, which will show you who is the current administrator or which responsibility has been set:

In the above image, the user has used the Workflow Administrator responsibility to navigate to the administration tab, but cannot make any changes because they do not have the responsibility to perform any admin tasks.  If I grant the “Workflow Administrator Web (New)” responsibility to the user and refresh the page, I can now make changes because I now have the right access:

Once the user has been given the right responsibility, when they search for any Workflow processes, the previously hard-coded owner field is now an LOV.

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.