It’s been a while…

I’m still here, honest – it’s just been a manic time over the past year, and I’ve not had chance to post anything meaningful here.

Sorry – I’ll try better in the new year. In the meantime, here’s a picture of me collecting two awards at the recent UK Oracle User Group Apps14 conference for my presentation last year – Best UK Speaker and Best New UK Speaker 🙂


UK Oracle User Group Apps2013 Conference

I will be presenting on Monday October 14th on the subject “How NOT to implement Oracle Workflow”.

Hope to see you there!

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.

What is ACTID and how do I find it?

Recently, I was asked what ACTID was and how to find it.  A user was trying to call some custom code which was usually invoked by Workflow to debug it, but ACTID is a mandatory parameter to the call.

What is ACTID?

The ACTID parameter is used to pass the internal ID for each Activity to the PL/SQL code for that Activity.  When the Workflow Engine moves to a new Activity, it retrieves the unique identifier for that particular use of the Activity in the process, and passes it to the PL/SQL for that Activity.

Actually, ACTID is only required by the code if it references any Activity Attributes – the API to retrieve the Activity Attribute values needs to know exactly which use of that Activity we are currently running, so it needs to have the ID passed into the code.  If the PL/SQL that you are going to call for that Activity does not reference any Activity Attributes, then for debug purposes you can actually pass in any value at all, since it won’t be used.

How do I find ACTID?

Ignoring the scenario where you don’t really need to know what the Activity ID is, when you are debugging the module, you will probably need to find out what the current value is for that activity.  To find it out, you will need to know the Item Type and Item Key for the process you are looking to debug, and you will also need to check the label for that Activity – not the display name or internal name, but the LABEL for that Activity.  If you have used the Activity a number of times in the same process, then each label will be different because the Workflow Builder automatically adds a number to the end of the name when you include it in a process.

Once you have those pieces of information, the following SQL will return the Activity ID for you:

  ad   DATE;
  ai   NUMBER;
  rt   VARCHAR2(30);
  vr   NUMBER;
  WF_ITEM.root_process('&item_type', '&item_key', rt, vr);
  rtid := WF_PROCESS_ACTIVITY.RootInstanceId('&item_type', '&item_key', rt);
  ad := WF_ITEM.Active_Date('&item_type', '&item_key');
  ai := WF_PROCESS_ACTIVITY.FindActivity(rtid, '&activity_label', ad);
  DBMS_OUTPUT.put_line('Activity id = '||TO_CHAR(ai));

You can find that in one SQL statement rather than relying on DBMS_OUTPUT by running the following command, which may be easier for you to run:

SELECT Wf_Process_Activity.FindActivity
  ( Wf_Process_Activity.RootInstanceId ( wfi.item_type
        , wfi.item_key
        , wfi.root_activity )
  , 'activity label'
  , Wf_Item.Active_Date(wfi.item_type, wfi.item_key) ) actid
FROM   wf_items wfi
WHERE  item_type = 'item type'
AND    item_key  = 'item key'

Hope that’s clear, but if not feel free to post a comment!


A long while back, there was a question about linking the transaction ID to a notification ID and item key within Oracle HR – I wrote the blog post below shortly afterwards, but somehow it never got published.  So, somewhat later than I’d planned, here’s the answer!

The original question can be found here and reads

Hello all,

how can i link Transaction_id in HR_API_TRANSACTIONS table with NOTIFICATION_ID and ITEM_KEY in wf_notifications table?


There are a couple of ways that you can link the different pieces of information – it depends on what your starting point is, though.

Firstly, the HR_AMEUTIL_SS package contains functions to find the item type and the item key from the transaction ID:

SELECT hr_ameutil_ss.get_item_type(transaction ID)
,      hr_ameutil_ss.get_item_key(transaction ID)

From here, you can then find the notification ID by looking at the WF_NOTIFICATIONS table, eg.

SELECT notification_id
FROM   wf_notifications
WHERE  message_type = hr_ameutil_ss.get_item_type(transaction ID)
AND    item_key     = hr_ameutil_ss.get_item_key(transaction ID)

If, however, you have the notification ID and you want to get to the transaction ID, then you need to look at the Workflow tables to find that information:

SELECT wiav.item_type
,      wiav.item_key
,      wiav.number_value
FROM   wf_item_attribute_values wiav
JOIN   wf_notifications         wfn  ON (    wfn.message_type = wiav.item_type
                                         AND wfn.item_key     = wiav.item_key )
AND    wfn.notification_id = <i>notification ID</i>

The number value for the item attribute will contain the transaction ID and you can then work from there to do what you need.

Apologies it’s taken over a year for me to get round to publishing this one – there may well be some more posts being published which I never got round to making public soon!

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!

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.