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.

How do I restrict the list of users to reassign to?

Standard Workflow functionality provides the users with the ability to reassign a notification to another user or role (unless you specifically tell the process not to).  However, if you aren’t careful, then everyone in the Workflow directory service is included in that list, which can produce some problems.

Firstly, the end user may not know which of the users they should be using – if there are multiple John Smith’s in the organization for example.  Or if there are two users called “Faisal Mohammad” and “Mohammad Faisal” which one is the right person to be receiving the notification?

Secondly, the business may want to restrict who the notification can be reassigned or transferred to.  For example, if the notification is one requiring an approval, then there may need to be some logic included to ensure that only people in a specific department can be used as the new recipient.

Thankfully, there is a mechanism included in the product which allows you to restrict the reassignment list of values using an attribute on the message.  Here’s how:

Firstly, you need to determine your list of new users and put them all in a Workflow Role.  This could be a new ad-hoc role that you have defined specifically for this one instance of the process, or you may already have a suitable role defined as standard (e.g. if you only want to be able to delegate to users who have been assigned the “UK Super HRMS Manager” responsibility.

Once you know the name of the role which have all the users assigned to it, then you need to store that name in the Workflow process in an item attribute.

The final step is to define an attribute on the message that you want to restrict the list, called #WF_REASSIGN_LOV and synchronise that with the item attribute holding the value of the role.

Once the new definition of the process has been deployed to the database, the messages will now check the value of the attribute and the list of users who can now be set as the new owner will be restricted accordingly.

How to populate the “From” field on a Workflow notification

Another quick one, but it pops up in fora and my email fairly regularly – when a notification is sent to a user / role, there is nothing in the “From” field to indicate who it actually came from. Sometimes, it’s there (in a seeded notification) and sometimes it’s missing, so how do we put something in there?

Firstly, the “from” needs to be defined in the Workflow Directory Service as a role – if you just want to put in “Bob Smith” for example, then you need to make sure that Bob exists in the directory. If not, then you need to create an ad-hoc role to use instead.

Once you have identified the role (and it may already be in your process) then you really should store that in an item attribute. Then you need to define an attribute on your message called #FROM_ROLE. You can set this to a constant, but I would recommend setting it to synchronize with an item attribute so you can easily change it later if necessary. Set the value of the attribute to the role that you want as the from, and save it.

Now, when the notification is sent out, the value will display the display name for the role. As a warning, though – messages aren’t versioned in the database, so you need to ensure that your changes (if you are using a new item attribute to synchronize with, for example) remain valid for any processes which are already running.

How do I tell if a role is valid?

Just a quick post in response to a question on the OTN forums today – if I have a role, how do I know whether it’s valid or not?

The quickest way to do it (I think!) would be just to check the end date – if the end date is set, and we’re past that point in time, then the role is invalid.  BUT – what if there is more to it than that?  What if Oracle modifies the product so that the end date field isn’t the only thing that needs to be tested?

The way that I recommend doing the check (and I’ve recently written some code around this very subject!) is to use the standard WF_DIRECTORY API to fetch me the information about the role.  If the role is invalid, then there won’t be any data returned; if the rolw is valid, then you get data back.

And here’s the code:

DECLARE
  l_display_name      WF_ROLES.display_name%TYPE;
  l_email_address     WF_ROLES.email_address%TYPE;
  l_notification_pref WF_ROLES.notification_preference%TYPE;
  l_language          WF_ROLES.language%TYPE;
  l_territory         WF_ROLES.territory%TYPE;
  l_module            VARCHAR2(100);
  l_role              WF_ROLES.name%TYPE := '&role';
BEGIN

  WF_DIRECTORY.getRoleInfo ( role                    => l_role
                           , display_name            => l_display_name
                           , email_address           => l_email_address
                           , notification_preference => l_notification_pref
                           , language                => l_language
                           , territory               => l_territory );

  IF l_display_name IS NULL THEN
    dbms_output.put_line('Role is invalid');
  ELSE
    dbms_output.put_line('Role is valid');
  END IF;
  
END;
/

It can be downloaded here.

Removing HZ_PARTY records from Workflow Roles

This workaround was originally suggested in the WorkflowFAQ forum by 67_eb_in_619, but I’ve moved it into the blog for more people to look at.

Within eBS, the default definition of the Workflow roles always includes the HZ_PARTY records, as well as those linked to people and users. The problem with this is that if you aren’t using email, then if the notification is sent to the role linked to the party record, then no-one is going to see it!

This is also really bad from a user-friendliness point of view. If you are reassigning a notification, then do you need to transfer it to the user called “SEARLE, Mr. Matthew” or “Mr. Matthew Searle” (in a recent project the first of these was the right one and the latter was for my party record). Potentially, the party record and the user record could have the same display name as well, making it pretty much pot-luck which one the user chooses.

So, how do you remove the records from the view?

There are two ways that you can do this – one supported, one not.

Firstly, you can make the party record inactive. The method that I have been recommended by Oracle to use is:

Navigate: Sales Online Responsibility > Customer Tab > Person Sub Tab
1. Query the person record.
2. Once the person details are displayed, you will see a Status field.
3. Select the status to be Inactive
4. Save

If you don’t use sales, then this is still probably the best place to do it, by all accounts!

Secondly, update the records through SQL:
UPDATE ar.hz_parties
SET    status='I'
WHERE  party_id IN ( SELECT party_id
FROM hr.per_all_people_f );

As ever, make sure you know what you are doing before you commit the changes – I’d be inclined to use the GUI to make the change, but if there are lots of records to fix, or you aren’t fussed about support anyway, then try the second one. I’m not taking any responsibility for it though!

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