September 4th, 2011 Matt
Recently, I got an email asking me about whether there was a way to suppress the cancellation notification on an Item Type basis – the business had a requirement that for certain processes, the cancellation message needed to be sent, whereas for others, the message needed to be suppressed:
I am wondering if it is possible to stop the workflow cancellation from being sent to a user when he workflow is cancelled. I realize there is a global profile setting but I can’t do it globally. It is a custom workflow that I am developing. I was looking into possibly using the #wfm_cancel template and hard code an email address into the peformer but that didn’t seem to work. Has anyone done this type of thing before or have thoughts on how this might be achieved.
Some years back, the global preference didn’t even exist – the cancellation message was always sent, much to the consternation of some of my clients. The way that we dealt with the problem to suppress all the cancellation messages was to create a database trigger which fired when the record in WF_NOTIFICATIONS is updated.
When the global preference was introduced, this trigger became irrelevant and was disabled. However, if there is a requirement to provide a conditional suppression of the cancellation message, the trigger can be extended to provide this functionality.
Firstly, to remove any need for hard-coding a list of messages which need to have the cancellation message suppressed, define a value set which will contain the notifications which we want to suppress cancellation messages. The values in the value set can be either the item type internal name (if all the messages in the item type should not have cancellation messages) or the internal names of the item type and the message with a colon delimiter if a specific message should have the cancellation suppressed.
Once the values have been defined in the value set, then create a trigger on WF_NOTIFICATIONS to check whether the message is in the value set. If it is in the value set, and a response is not required, and the notification is being cancelled, then update the record so that the notification appears to have been sent already by the notification mailer:
CREATE OR REPLACE TRIGGER XXX_STOP_CANCEL_MSG
BEFORE UPDATE ON WF_NOTIFICATIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
dummy PLS_INTEGER;
v_suppress_ntf VARCHAR2(10);
CURSOR c_check_value_set ( cp_vs_name IN VARCHAR2
, cp_type_name IN VARCHAR2
, cp_msg_name IN VARCHAR2 ) IS
SELECT 'EXISTS'
FROM fnd_flex_values_vl ffvv
JOIN fnd_flex_value_sets ffvs
ON ( ffvv.flex_value_set_id = ffvs.flex_value_set_id )
WHERE ffvs.flex_value_set_name = cp_vs_name
AND ( ffvv.flex_value = cp_type_name
OR ffvv.flex_value = cp_type_name||':'||cp_msg_name
)
AND NVL(ffvv.enabled_flag,'N') = 'Y'
AND SYSDATE BETWEEN NVL(ffvv.start_date_active,SYSDATE - 1)
AND NVL(ffvv.end_date_active,SYSDATE + 1);
BEGIN
IF :new.status = 'CANCELED'
AND :old.status = 'OPEN'
AND :new.mail_status = 'MAIL'
AND :old.mail_status = 'SENT' THEN
BEGIN
-- Check whether the notification type is in the Value Set
OPEN c_check_value_set ( cp_vs_name => 'XXX_SUPPRESS_LIST_VS'
, cp_type_name => :new.message_type
, cp_msg_name => :new.message_name );
FETCH c_check_value_set INTO v_suppress_ntf;
CLOSE c_check_value_set;
IF v_suppress_ntf = 'EXISTS' THEN
-- Record is included in the value set, so need to include it
-- Check that the notification expects a response otherwise
-- do not cancel it
SELECT 1
INTO dummy
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM wf_message_attributes wma
WHERE wma.message_name = :new.message_name
AND wma.message_type = :new.message_type
AND wma.subtype = 'RESPOND' );
:new.mail_status := 'SENT';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Not a response, so no need to cancel it
NULL;
END;
END IF;
END;
As ever, if you are relying on the code sample here, it is provided without any warranty – test it fully before deploying to a production environment.
SQL script
Posted in Functional, Oracle, Technical | 2 Comments »
August 30th, 2011 Matt
I was asked a few days back about whether there is any way for an existing Workflow process to be forced to use a new definition when the new version is saved to the database:
There is some transaction already in ACTIVE status ( i.e. ACTIVE workflows ) in database. I need to run this transaction according to new wft file what I have putted in database recently. Is is possible ?
The simple answer is: “it depends”! Workflow allows multiple versions of the same process to exist in the database at the same time, so in this post, I’ll be looking at what you can do to use the new definition.
Workflow Automatic Versioning
When a new definition of an Item Type is deployed to the database, the Workflow Engine will automatically create a new version of some objects if they have been changed. The objects which are versioned by the Workflow system are:
- Notifications
- Functions
- Events
- Processes and Sub-Processes
- Process Activities (anything on the Node, Node Attributes or Event Details tabs for an activity)
- Activity Attributes
- Activity Attribute Values
- Transitions
So, if you have made any changes to any of these things, when you save the definition to the database, a new version of the object will be created – any existing processes will continue to use the old definition rather than the new one.
If you have made any changes to Item Attributes, Lookup Types, Lookup Codes, Messages or the underlying PL/SQL code then those changes will immediately be picked up by processes which are already running and any new processes which are launched. So, here’s a warning – if you are making changes to messages or lookups within a process, any changes you make will take immediate effect when the definition is deployed to the database.
Changing your PL/SQL will also impact the processes which are already running – particularly if the code references any new attributes which do not exist in the processes which are already running. There are some things that can be done within the code to catch and handle any errors which may be thrown by attributes not existing – for example, you can include an exception handler which calls WF_ENGINE.AddItemAttr to add a new attribute if one does not exist in the definition.
Forcing Existing Processes to Use a New Definition
So, if you have made changes to the process which means that a new version has been deployed, what do you do with the existing processes?
The simple approach would be to abort the existing process and then re-run them from the beginning. Some processes may include a mechanism for doing this, for example in an approval process, if a user rejects the request then the process may send it back to the requestor to resubmit. However, if the process has been running for a long time, then it may not be practical to restart it. Some years back, I worked with a business where their processes could run for years before completion, where aborting the process just wouldn’t be practical.
When an activity is started by the Workflow Engine, the engine determines which version of the definition to use by checking the begin date of the record within WF_ITEMS. The date that the process started is then checked against the effective dates for the activities and the version which was current when the Workflow process started will be used to determine which activity to run and what the transitions are from the activity.
So, if you really, really need to force the running processes to use the latest definition, then you can update the BEGIN_DATE field for the record in WF_ITEMS to a later date. When the next activity is executed, the engine will check the date and will use the latest version of the process.
There are two main drawbacks of having to adopt this approach. Firstly, any reporting / auditing / metrics that you have for determining process execution time will no longer be accurate because the start date of the process has now been manipulated. Secondly, if there are any problems further down the line with the process, then Oracle won’t support you – that said, if this is a custom process, then you won’t be getting much support from Oracle anyway…
As ever, make your changes in a test environment before making changes to your production system to ensure that everything still works, though.
Posted in Oracle, Technical | No Comments »
August 23rd, 2011 Matt
I recently had a question where the developer had built a process which included a loop. Within that loop, there was a custom function which determined which level of approver was required before applying processing logic, something similar to this:
The problem was that when the “Do Something” step was being executed, it appeared that the “Find Approval Level” step was always jumping two levels – the first and third approvers were being found, but never the second or fourth. So, there must have been something wrong in the “Find Approval Level” step if it was always jumping two levels, right?
Wrong! The problem was that the loop activities in the process hadn’t been defined properly, and the code hadn’t been written correctly in a very basic manner.
The Workflow activities should have had the “On Revisit” flag set correctly for the activities that were in the loop. The standard is to have it set to “Reset” which will re-run the activities in CANCEL mode before re-running the loop in RUN mode. Instead, the activities should have had the flag set to “Loop”:
If the flag is set to “Ignore”, then the activity would only be executed once – which means that you would end up in a perpetual loop in this example, since the “More Required?” step would always return “Yes” unless there was only one approval level.
The basic change to the code is to check what the FUNCMODE is before doing any further processing. If the code was written so that the search for the approver only happened during RUN mode, then the process wouldn’t appear to skip a level. Unless there is specific code to undo an execution, then I always include a check at the start of my activities which says that if FUNCMODE does not equal WF_ENGINE.ENG_RUN (the externalized constant for RUN mode), then simply RETURN to the Workflow Engine. This saves processing time and avoids problems like the one we see here.
Posted in Oracle, Technical | No Comments »
July 15th, 2011 Matt
One of the common questions I receive relates to why a process does not wait for the right amount of time when using a standard Wait activity – some times it will wait for the right amount of time, some times a little bit longer, and some times a lot longer (maybe up to an hour!). Today, I’ll look at how the Wait activity works and why you get the different times for how long you need to wait before the process continues.
The way that the standard Wait activity works (WF_STANDARD.wait) is that it firstly determines whether this is the first or second time that the activity has been visited. If it’s the first time that the activity has been visited by the process, then the different activity attributes are checked and the “wake up” time is calculated (i.e. when should the process next do something). Once the time has been calculated, the procedure sets the result to deferred until that time and ends. If it’s the second time that the activity has been visited, then the process sets the result to complete and ends.
So, rather than a “Wait” activity, it is actually a “Defer Until” activity. That may not sound like too much of a difference, but there is a fundamental one. If I suggested that the process will “wait”, then the inference is that as soon as the process has waited for a given period of time, then it will continue – if I was telling a PL/SQL piece of code to wait for a minute then I would call DBMS_LOCK.sleep(60); and as soon as that minute was up, the process will automatically continue.
However, the standard Wait activity doesn’t actually wait and then continue, it defers processing and the Workflow Engine moves on and processes something else instead. That’s because rather than waiting, the thread has been deferred until a certain point in time which corresponds to the wake up time.
Unfortunately, this means that you need to run something to process those deferred activities – the Background Engine (WF_ENGINE.Background). One or more Background Engines will need to be scheduled to run at the right frequency to process your deferred activities – which is why sometimes you wait for the right period of time (your Workflow got to the Wait activity just before the Background Engine ran), a short amount of time (the process got there and waited a little while until the next scheduled run of the Background Engine started), or you had to wait a long time (the Background Engine isn’t scheduled at the right frequency to meet the process needs).
Determining the number of Background Engines required, and their frequency of scheduling, is a bit of a dark art – each enterprise will need to monitor that the Background Engines are run at a suitable period but one that isn’t going to thrash the database into submission. If, for example, you need the process to wait for 10 seconds before continuing, then you need to schedule a background engine to run every 9 seconds or less, so that the wait cannot get to more than 10 seconds.
Having something wait for a short period of time sounds wonderful in theory (“Oh, we’ll just have the process pause for a few seconds while XYZ happens”) but can have a significant impact on the overall performance of the system. As soon as I hear someone suggest such a thing (which is fairly often!) then the question I always ask is “Why?” What is so important to the process that you need to wait for 10 seconds, but can’t wait for 5 minutes? This is particularly appropriate if you are then sending notifications which require a response – you want to pause the process for a minute, to get all the data, so you can send it in an email??
If you really (and I do mean really) need a short wait, then you need to consider what the triggering mechanism is – exactly what are you waiting for? If that’s something that can be determined by a change of state somewhere then you are better off including a pause in the process via a Business Event and then have your triggering condition raise the event to show that it has completed the work and the process can restart. Some developers (more than I care to remember!) have a tendency to include a short wait, then a check to see whether the job has completed, and if not, then loop back and wait again. If you change the development approach to an event-driven one, then this becomes redundant – and it means that if something happens to prevent the action taking place, your Workflow isn’t going to loop and loop and loop, which adds lots more audit and history to the process than you really want.
As a final thought – I have NEVER used a standard Wait activity, since I started with Workflow development. There are better ways to do it, and always have been (even before Business Event System was invented).
Posted in Functional, Oracle, Technical | No Comments »
July 8th, 2011 Matt
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.
Posted in Oracle, Technical | No Comments »
July 1st, 2011 Matt
I was asked recently whether there was any way to convert a BLOB into Base64 in PL/SQL easily – it’s possible in Java, but the resulting string may well be more than 32k which seems to be a limit in what can be returned.
Digging out the trusty Oracle Built Ins guide, there is a UTL_ENCODE package which can do this, but only up to 32k it seems, because it only handles a RAW datatype. So, the way round the limitation would be to strip out the first <n> characters in the BLOB, convert them to a raw, then encode them, then put them into a new CLOB, which can handle the greater than 32k limit.
Putting together a few calls to UTL_ENCODE, DBMS_LOB and UTL_RAW (since we need to convert the RAW back into a VARCHAR2 to be able to append it to a CLOB), I came up with the following:
CREATE OR REPLACE PROCEDURE encode_to_base64 ( p_blob IN BLOB
, p_clob OUT CLOB ) AS
l_raw RAW(32767);
l_length PLS_INTEGER;
l_loops PLS_INTEGER;
l_pos PLS_INTEGER;
l_encoded_raw RAW(32767);
l_encoded_vc2 VARCHAR2(32767);
l_size PLS_INTEGER;
BEGIN
l_size := 12288;
l_length := DBMS_LOB.getLength(p_blob);
IF l_length > l_size THEN
l_loops := ROUND((l_length/l_size)+0.5);
ELSE
l_loops := 1;
END IF;
l_pos := 1;
FOR i IN 1..l_loops LOOP
DBMS_LOB.READ(p_blob, l_size, l_pos, l_raw);
l_encoded_raw := UTL_ENCODE.base64_encode(l_raw);
l_encoded_vc2 := UTL_RAW.cast_to_varchar2(l_encoded_raw);
DBMS_LOB.writeAppend ( p_clob, LENGTH(l_encoded_vc2), l_encoded_vc2);
l_pos := l_pos + l_size;
END LOOP;
DBMS_LOB.writeAppend(p_clob,1,CHR(10));
END encode_to_base64;
/
That should do it – hope it’s useful!
Posted in Oracle, Technical | No Comments »
June 27th, 2011 Matt
Another one from the inbox – is it possible to send a notification via email based on a profile option (or some other condition)? The requirement is that notifications should always appear in the Workflow work list, but for some notifications, there should be an additional copy sent via email. If the condition is not met, then the notification should only appear in the work list.
—
Firstly, let’s look at how notifications are displayed. Every notification is automatically available in a Workflow worklist – there is no way to stop that happening. Additionally, as an “optional extra”, you can send the same notification via email, based on the following conditions:
- The Workflow Mailer must be running
- The Workflow Mailer must be configured correctly
- The recipient role has an email address configured for it OR (if sending to a role which has multiple users assigned) the individual recipients have an email address configured
- Either the global preference is set to receive email (and there is no over-riding preference set by the user) OR the user preference has been set to receive email
If any of those are not happening in your system, then the notification isn’t going to be sent as an email, regardless of what you want to happen.
So, it looks as if the only thing you can do is to either enable or disable the sending of an email. BUT, there is an alternative which doesn’t quite meet the requirements, but might do the trick – it depends on what the business requirements are about whether the notification should be emailed or not.
If the business are concerned about sensitive information being emailed out, then there is an attribute which can be used for the message called #WF_SECURITY_POLICY which determines whether the notification should be sent by email or not. If this is set to NO_EMAIL then an email will be generated which tells the user that they need to log into the application to be able to read it. If that is what the business really want, then this is the way forward – if not, then adding the attribute may make the situation worse rather than better.
Posted in Functional, Oracle | No Comments »
June 24th, 2011 Matt
Back in April, there was a post on OTN relating to whether you could specify the date when aborting a Workflow process using the standard WF_ENGINE.AbortProcess API. The reason seemed to be that the user only wanted to purge data according to a specific date (e.g. up to the end of 2010).
As I said in my response, it doesn’t make sense to try to convince the system that you did something on a different date. If you set it to BEGIN_DATE+10, what happens to the child activities which occurred on BEGIN_DATE + 11? You would have an end date that was before the begin date, which doesn’t do much for any auditing and reporting that you want to do.
If the requirement is actually to abort and purge processes which started before a certain date, then the code below should help:
BEGIN
FOR i IN ( SELECT item_type
, item_key
FROM wf_items
WHERE 1 = 1 ) LOOP
WF_ENGINE.AbortProcess ( i.item_type, i.item_key );
END LOOP;
FOR j IN ( SELECT item_type
, item_key
FROM wf_items
WHERE TRUNC(begin_date) <= TRUNC(TO_DATE('31-DEC-2010'
,'DD-MON-YYYY')) ) LOOP
WF_PURGE.items ( j.item_type, j.item_key );
END LOOP
END;
Modify the implicit cursor in the i loop to find the records that you want to abort, modify the cursor in j loop to include any other criteria.
The reason that you have to do them in loops is to ensure that the processes have all been aborted before trying to purge them - the process (and any child processes it spawns) must be complete before it can be purged. Likewise, when purging child flows, the parent must have completed before it can be purged (something I suggested as a feature many years back 
)
Posted in Oracle, Technical | No Comments »
June 17th, 2011 Matt
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.
Posted in Oracle, Technical | No Comments »
June 10th, 2011 Matt
I recently had an email asking me what was wrong with their Workflow Builder installation – whatever they did no icons were appearing in the Builder.
Also, there was no Standard item type being opened – when the user tried to create a new Item Type using the quickstart wizard, they got an error message – and it wasn’t even a nice one, as it looked like the messages file hadn’t been installed correctly and all they got was an error name and code.
Some years back, I had exactly the same problem when I had installed Builder on a machine on client site. The installation process had “worked” correctly (i.e. Oracle hadn’t reported any errors), but obviously there was something wrong.
The problem was that the user I was logged in as did not have FULL admin rights on the machine – it had enough to make most of the install work and write some information into the Windows registry, but not enough to actually install EVERYTHING required.
So, the quick fix is to make sure that you are logging into the machine as someone with full rights (typically log into the machine itself rather than onto a network) and then do the install.
Posted in Oracle, Technical | No Comments »
