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 (11.5.10.2)

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:

DECLARE
  ad   DATE;
  ai   NUMBER;
  rt   VARCHAR2(30);
  vr   NUMBER;
  rtid PLS_INTEGER;
BEGIN
  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));
END;
/

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!

How do I link TRANSACTION_ID to NOTIFICATION_ID and ITEM_KEY?

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?

Thanks
KK

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   DUAL
/

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 )
WHERE  wiav.name           = 'TRANSACTION_ID'
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 🙂

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:

_TERRITORY.CHARSET

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

.UTF8

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 (oracle.apps.wf.event.any) 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!

Dealing with errors when creating an adhoc role – part three – WF_DIRECTORY error codes

In this last part in the series, I’m going to look at some other common errors which you might hit when creating an adhoc role using the WF_DIRECTORY APIs.  The first part of the series can be read here, and the second part is here.

The table below lists the error numbers and messages which can be returned by the WF_DIRECTORY package – the SQLCODE returned by the error will always be -20002, with the error number included in the text of the SQLERRM.

Error Name Number Error Message
WF_DUP_ROLE 395 Duplicate role &DISPNAME (‘&ROLENAME’)
WF_INVALID_ROLE 396 Invalid role name ‘&ROLENAME’
WF_DUP_USER 397 Duplicate user &DISPNAME (‘&USERNAME’)
WF_INVALID_USER 398 Invalid user name ‘&USERNAME’
WF_NO_LANG_TERR 399 Could not resolve NLS language and territory
WF_INVALID_NTF_PREF 4004 ‘&NTF_PREF’ is not an allowed notification preference.
WF_INVAL_CONCAT_NAME 4001 ‘&NAME’ is an invalid internal name because it contains a colon. For this particular role, if the name contains a colon, it has to be ‘&ORIG_SYSTEM:&ORIG_SYS_ID’ .
WF_ROLENAME_TOO_LONG 4002 ‘&NAME’ exceeds the maximimum length of &LENGTH characters.
WF_INVALID_ROLE_STATUS 4003 ‘&STATUS’ is not an allowed status.
WF_DUP_USER_ROLE 4016 User/Role relationship for user ‘&UNAME’ and role ‘&RNAME’ already exists.

This list of errors is not a definitive list of every error which may be thrown by the API, this is a list of the most likely ones which can occur when using adhoc roles.

If you need to look for any of these errors in your code, then include the error codes within the exception handler – one thing to be careful of (and it’s caught me in the past!) is that if you are working in multiple languages, ensure that you only look for the error number rather than the error message as well.  For example, check for “%4016%” within SQLERRM rather than looking for “%4016%already exists”, since the text will vary depending on the Workflow language!

Dealing with errors when creating an adhoc role – part two – duplicate roles

In this post, I wrote about how you can use custom code to create an adhoc role which suppresses errors when the role is invalid.

The next logical extension to the code is another common problem – what happens if the list of users contains duplicate records?  If you attempt to call the standard WF_DIRECTORY APIs, and the list of users contains duplicates, then the API will return an error:

  1  DECLARE
  2    v_rn           VARCHAR2(100);
  3    v_dn           VARCHAR2(100);
  4    v_role_users   WF_DIRECTORY.UserTable;
  5
  6  BEGIN
  7    v_rn := 'MATT'||TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF');
  8    v_dn := v_rn;
  9
 10    v_role_users(1) := 'SEARLEM';
 11    v_role_users(2) := 'SEARLEM';
 12    v_role_users(3) := 'SEARLEM';
 13
 14    WF_DIRECTORY.CREATEADHOCROLE2 ( ROLE_NAME         => v_rn
 15                                  , ROLE_DISPLAY_NAME => v_dn
 16                                  , ROLE_USERS        => v_role_users );
 17
 18* END;
APPS@R12_DEV > /
DECLARE
*
ERROR at line 1:
ORA-20002: 4016: User/Role relationship for user 'SEARLEM' and
          role 'MATT20120123115841745021000' already exists.
ORA-06512: at "APPS.WF_DIRECTORY", line 2005
ORA-06512: at line 14

In this example, the API raises the same ORA-20002 error with a Workflow specific error ID of 4016, which we can modify the code from the earlier post to catch and deal with this error as well:

CREATE OR REPLACE PROCEDURE xx_create_adhocrole
       ( role_name               IN VARCHAR2
       , role_display_name       IN VARCHAR2
       , language                IN VARCHAR2 DEFAULT NULL
       , territory               IN VARCHAR2 DEFAULT NULL
       , role_description        IN VARCHAR2 DEFAULT NULL
       , notification_preference IN VARCHAR2 DEFAULT 'MAILHTML'
       , role_users              IN WF_DIRECTORY.UserTable
       , email_address           IN VARCHAR2 DEFAULT NULL
       , fax                     IN VARCHAR2 DEFAULT NULL
       , status                  IN VARCHAR2 DEFAULT 'ACTIVE'
       , expiration_date         IN DATE     DEFAULT NULL
       , parent_orig_system      IN VARCHAR2 DEFAULT NULL
       , parent_orig_system_id   IN NUMBER   DEFAULT NULL
       , owner_tag               IN VARCHAR2 DEFAULT NULL ) IS

  e_workflow_error  EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_workflow_error , -20002);

BEGIN

  WF_DIRECTORY.CreateRole ( role_name               => role_name
                          , role_display_name       => role_display_name
                          , orig_system             => 'WF_LOCAL_ROLES'
                          , orig_system_id          => 0
                          , language                => language
                          , territory               => territory
                          , role_description        => role_description
                          , notification_preference => notification_preference
                          , email_address           => email_address
                          , fax                     => fax
                          , status                  => status
                          , expiration_date         => expiration_date
                          , parent_orig_system      => parent_orig_system
                          , parent_orig_system_id   => parent_orig_system_id
                          , owner_tag               => owner_tag );

  IF role_users.COUNT > 0 THEN
    FOR i IN 1 .. role_users.COUNT LOOP
      BEGIN

        WF_DIRECTORY.AddUsersToAdHocRole ( role_name  => role_name
                                         , role_users => role_users(i));

      EXCEPTION
        WHEN e_workflow_error THEN
          IF   (SQLERRM LIKE 'ORA-20002:%3205%')
            OR (SQLERRM LIKE 'ORA-20002:%4016%') THEN
            NULL;
          ELSE
            RAISE;
          END IF;

        WHEN OTHERS THEN
          RAISE;

      END;
    END LOOP;
  END IF;

END xx_create_adhocrole;
/

This version of the code can be downloaded here.

In the next post in this series, I’ll be looking at the different errors which the WF_DIRECTORY API can throw.

Dealing with errors when creating an adhoc role – part one – invalid roles

I was recently asked about a problem with trying to create and adhoc role, and some of the errors which might occur.

I am sending notification to multiple users.  While doing that if the roles are valid, then there is no problem.

The problem occurs when one of the roles isn’t valid, for example:
v_role_users => ‘ROLE1 ROLE2 ROLE3’;

In this example, if ROLE2 is not valid then the notification is not sent to anyone because the role cannot be built. Instead, we get this error: 3205: ‘ADHOC_ROLE’ is not a valid role or user name.

If one of the roles is invalid, I still want the notification to be sent to the valid roles.

How to achieve this?

The way round the problem is to modify the code which builds the role, so that rather than passing all the users into the API at the same time, they are passed in sequentially.  When each role is added, include additional error handling to catch any error and handle that.

Unfortunately, the Workflow development team use the same custom error code (-20002) for all errors which occur in the product, which means that as well as looking for the error to occur, we then need to parse the SQLERRM error message to check for the Workflow specific error number (3205 in this case).

The following code creates a new procedure which you can call to create the role, without any particularly invasive changes – instead of calling WF_DIRECTORY APIs to create a new role, call the procedure instead, which will suppress expected errors but still raise anything unexpected:

CREATE OR REPLACE PROCEDURE xx_create_adhocrole
       ( role_name               IN VARCHAR2
       , role_display_name       IN VARCHAR2
       , language                IN VARCHAR2 DEFAULT NULL
       , territory               IN VARCHAR2 DEFAULT NULL
       , role_description        IN VARCHAR2 DEFAULT NULL
       , notification_preference IN VARCHAR2 DEFAULT 'MAILHTML'
       , role_users              IN WF_DIRECTORY.UserTable
       , email_address           IN VARCHAR2 DEFAULT NULL
       , fax                     IN VARCHAR2 DEFAULT NULL
       , status                  IN VARCHAR2 DEFAULT 'ACTIVE'
       , expiration_date         IN DATE     DEFAULT NULL
       , parent_orig_system      IN VARCHAR2 DEFAULT NULL
       , parent_orig_system_id   IN NUMBER   DEFAULT NULL
       , owner_tag               IN VARCHAR2 DEFAULT NULL ) IS

  e_workflow_error  EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_workflow_error , -20002);

BEGIN

  WF_DIRECTORY.CreateRole ( role_name               => role_name
                          , role_display_name       => role_display_name
                          , orig_system             => 'WF_LOCAL_ROLES'
                          , orig_system_id          => 0
                          , language                => language
                          , territory               => territory
                          , role_description        => role_description
                          , notification_preference => notification_preference
                          , email_address           => email_address
                          , fax                     => fax
                          , status                  => status
                          , expiration_date         => expiration_date
                          , parent_orig_system      => parent_orig_system
                          , parent_orig_system_id   => parent_orig_system_id
                          , owner_tag               => owner_tag );

  IF role_users.COUNT > 0 THEN
    FOR i IN 1 .. role_users.COUNT LOOP
      BEGIN

        WF_DIRECTORY.AddUsersToAdHocRole ( role_name  => role_name
                                         , role_users => role_users(i));

      EXCEPTION
        WHEN e_workflow_error THEN
          IF SQLERRM LIKE 'ORA-20002:%3205%' THEN
            NULL;
          ELSE
            RAISE;
          END IF;

        WHEN OTHERS THEN
          RAISE;

      END;
    END LOOP;
  END IF;

END xx_create_adhocrole;
/

The SQL file can also be found here.

In later posts, I’ll be looking at extending this to catch different, common errors.

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