September 30th, 2011 Matt
I was recently a question which took me back to my very first Workflow project where we needed to re-use a sub-process repeatedly:
When I try to save the workflow, I am getting the error below:
WFPACTV_FAIL OPERATION=SAVE NAME=XXOM_P_VLVS_LN_FLW_GEN_SCH_DEF TYPE=OEOL
What’s the problem?
The error is caused because Workflow will not allow the developer to re-use a sub-process within the same hierarchy, i.e. Start -> SUB1 -> SUB2 -> SUB1 -> End. When I did my first Workflow project, we had a very complex process flow which managed sending messages via AQ from one system to a transformation hub, which then passed them onto a back-office system. What was a simple message from the front-end became a series of simple messages in the back-end, and Workflow was used to manage the co-ordination between the systems.
What we found, however, was that in the front-end, we could build a message which allowed the user to enter a mailing address for the customer, for the product that the customer was buying (so different products could be posted to different addresses), for a proxy for a product and so forth – essentially we had four or five different levels where we wanted to do the same thing. Unfortunately, because the “create an address” process could be called a number of times within the same hierarchy, we hit the limitation in Workflow that you cannot reuse the process. As a workaround, we ended up creating copies of the sub-process and naming them differently (we added a different letter to the name), which became pretty annoying when it came to maintenance later on – if we had to make a change to one sub-process, we had to make sure that we were making the same change to each copy as well….
Why can’t you do it?
Here’s the background about WHY you can’t do it – feel free to skip ahead to my thoughts on bypassing the problem now.
Because of the way that the Workflow definition is stored in the database, if the same process is re-used, there is no way that the Workflow Engine can determine which time through the activities are being run. For a single activity, when it is revisited, the previous visit is archived in the Workflow history tables. For a series of activities (i.e. a sub-process), the same holds true when it is revisited – the whole process is recorded in the history tables. However, there is a big difference between revisiting a process and reusing it. If you are reusing the process, then there is nothing to archive – the previous use should remain in the current run. When the activity then completes, the WF_ITEM_ACTIVITY_STATUSES table would have two records for the same activity – the first run and then the re-used process. Sadly, the code which manages the update cannot take the two into account, so wouldn’t know which record to update.
If Workflow was being written today, then if the activity statuses used TIMESTAMP rather than a DATE to store the execution time (or even included a sequence number), then the Workflow Engine could more easily determine which record to update, to help bypass this limitation. However, if you had a parallel branch, which routed to two identical re-used processes (I can’t see WHY you would do that, but it could be possible), then you hit the same limitation again – how do you identify within the Workflow tables which activity in the process is running??? Overall, it makes sense to include the limitation, IMO.
Ways Round The Problem
If there are only going to be a limited number of times that the process is going to be re-used, then I would be inclined to make a copy of the process and rename it slightly and use that. That process is significantly easier if you know that the process is finalized, so that you won’t be needing to make the same change to multiple copies. If you are going this route, then you need to ensure that the different process copies are sufficiently clearly labelled / documented / commented to allow you (or someone else) to maintain the definition going forwards.
If there are a large number of processes that need to be copied (I remember having copies A through to K of one sub-process!), then I would define the process as a separate, runnable process which is invoked as a child process with it’s own item type and item key. There are no limitations on the number of times that the process can be re-used, and if the process is still in the same item type, then when it is created, the item attribute definitions will be the same – you just need to include something in your code which copies them from one instance to another.
One downside of implementing a parent-child relationship, however, is that when you look at the entire history of the process, it isn’t going to be all in one place – there will be some parts recorded in one process and others in other bits. Also, I would be wary of using the standard WAITFORFLOW / CONTINUEFLOW activities to manage the flow, if there is a way that there could be more than one waiting process. Instead, I would define some custom business events – have the main flow wait to receive the event, and the child flows raise the event. This way, when you raise the event, you can pass attribute values easily back into the parent without having to directly call the WF_ENGINE APIs.
If I were building that first project from scratch, there are many things that I would change. This would definitely be one of those.
September 25th, 2011 Matt
I recently had an email asking for help with trying to work out why a notification wasn’t showing the approval history:
I have a custom Workflow which has notification where in I am using Adhoc role to send notification to multiple users. The Expand roles checkbox is checked.
Now the issue is the Action History table which comes in the approval notifications does not appear in the notification sent to each user. The history is visible to the user who was added first to the adhoc role (where the Notification Group ID = Notification ID), but for all the other users the history table itself is not shown in the notification.
I have tried using WF_NOTIFICATION(HISTORY) in the workflow message but this is unsuccessful.
This intrigued me for a while about why the history would appear for one user but not for any others, when the content should obviously be the same. Then I considered the issue a bit more, and this turns out to be standard functionality.
Here’s a more detailed scenario. A notification is sent to a role, which has four users assigned to the role. The notification requires a response, and the “Expand Roles” checkbox is ticked so that each user receives their own copy of the notification. The notification is accepted as approved when either 75% of the recipients have approved it or 50% have rejected it. Within the notification, there is a history table. Over the following few days, the following activities take place:
- Day 1 – Within five minutes of receiving the notification, the first approver responds with an approval.
- Day 1 – Within five minutes of receiving the notification, the fourth approver rejects the notification.
- Day 2 - The second approver asks for more information from the person who submitted the request.
- Day 2 – The third approver asks for more information from the first approver.
- Day 3 – The first approver answers the question from the third approver.
- Day 5 – The person who submitted the request responds to the question from the second approver.
- Day 5 – The second approver approves the request.
- Approval / rejection from the third approver is still outstanding.
At this stage, what should the approval history be showing? As far as the Workflow process is concerned, the notification (although they all have different notification IDs) can only have one history. So, what is the history? Is the history of the notification which was sent to the first approver the same as that sent to the second?
At some stage, the Workflow product development team decided that it made sense that a notification could only have one history, therefore even when the role was expanded, only the first person in the role would have the history. So, what was happening was expected functionality – only that one person will generate history in the notification. (It turns out that there is a brief mention of this being standard functionality in Metalink note 431219.1)
So, what can be done about it?
There isn’t much that can really be done to circumvent the standard functionality. I discussed the issue with another Certified Workflow Expert that I know and bounced a few ideas back and forth.
The first suggestion he made was to use the special attribute #RELATED_HISTORY on the notification, which allows you to include the history from one notification in another notification. His thoughts were that if a post notification function were included, it might be possible to set the attribute on the second and third copies so that they include the history of the first approval notification. However, when we looked at it a bit more, it became obvious that this idea won’t work. The #RELATED_HISTORY attribute can only be used to associate different notifications together, since it needs to be set to the label for the notification to relate, and the attribute only works on an FYI notification.
The next suggestion was that a single notification could be sent (i.e. without expanding the roles) to an email distribution list. This would allow the user to receive what appears to be their own copy of the notification and act on it, but means that there is no way that the voting requirements could be met, and would only allow a response via email if the flag was set to allow forwarded responses, since any response would come from a different email address to the one that was notified. Depending on the exact business requirements, this may or may not meet the bill.
So, it comes to the final suggestion. Rather than including the standard history within the notification, we could suppress it from the first notification and then replace it with a custom table. Including an attribute as a PL/SQL document in the code to generate a table which provides the history of all the notifications would enable all copies of the notification to include an up-to-date history for all copies of the notification. Setting the document ID to equal the notification ID would mean that you can programmatically determine the group ID for the notification and build a complete history of the process.
This turned out to be the solution that the client wanted, needed and implemented in the end, but for some clients it would be overkill. I’ll ask for some screenshots to include, but am not hopeful given their penchant for security – failing that, I’ll try to come up with some examples of how we did it.
September 17th, 2011 Matt
A while back, I was asked for advice on finding the result of a notification within the process:
Can you please help me, how to capture the result of notification approval? My notification has result type approval.
My first response was to question why the client needed to know the result within the process. Merely by having a result for the notification, the knowledge is automatically communicated back to the process, which can then have conditional branching based on the response provided. If the result is needed further down the line as an attribute, then a standard ASSIGN activity could be used to store the result in an attribute.
The problem with this approach is that if the process models multiple results on the same transition branch (i.e. by using a <Default> or <Any> transition), then there is no easy way to determine which of the multiple responses was actually provided. For example, imagine that the notification has a lookup type as a response which has lookup codes defined which mean “Approved”, “Finally Approved”, “Rejected” and “Resubmit”. In the process flow, the notification has a transition defined for “Rejected”, one for “Resubmit” and a “default” transition which manages the two approval routes. If the business requirement is that the only thing that we need to capture is the “type” of response (i.e. approval or rejection) then we can still take the same approach. If, however, the business requirement is that the exact response is required rather than the type of response, then the response needs to be captured in a different method.
There are two approaches that can be adopted to find the exact response to the notification, both requiring custom PL/SQL. Firstly, you can define an activity in the process which accesses the Workflow tables directly to find the previous notification and then find the response to the notification. In this case, you will need to hard-code in the PL/SQL the type of message that you are looking for, and if there is any change to the Workflow process at a later stage, the PL/SQL will need to be modified to handle the change of notification type.
The second approach, which is the one that I would recommend is to write a custom post-notification function (PNF). Within the PNF, you can find the details of the response directly, and perform your processing logic. Additionally, because the code can be written in a generic manner, the PNF can be re-used for any number of scenarios where the result is required.
The developer for the client followed this advice, but still had problems trying to find the result:
I attached the function to the notification. in the function I’m trying to capture the result using getItemattrtext
lc_result := wf_engine.getitemattrtext( itemtype => itemtype,
itemkey => itemkey,
aname => 'RESULT'
I always get null
The problem here, which is an easy fix is that he was using the wrong API to fetch the result. When the notification is being responded to, the result won’t be stored in item attributes. That would only happen once the processing of the post notification function is complete. The way to find the result is to firstly find the notification ID using WF_ENGINE.context_nid.
Once you know the notification ID, call WF_NOTIFICATION.getAttrText to find the attribute from the notification. You’ll need to do that for the RESULT attribute and any other attributes on the notification that the business requires.
Why would you need the result?
After some discussion, we finally got to the business requirements, which then meant that we could design an effective solution – one of my bugbears about providing support and advice is that I would much prefer for someone to ask “what is the best way to solve the problem?” rather than “how do I do XYZ?” which means that some times you go off down the wrong path (see this post featuring a video by Tom Kyte on the same issue!).
The business requirements were that the notification has a “comments” field, which if the notification is being rejected must be completed to conform to the business processing requirements. Therefore, the check needs to be that when the notification is responded to, and it is being rejected, and the comments field has not been completed, then an error should be raised. This can only be done effectively using a post notification function – if I had known the business requirements from the start, then I would not have suggested by first approach.
Anyway, that’s how to find the response in different ways – depending on the business requirements, either one may be the right approach to adopt.
September 9th, 2011 Matt
I was recently asked about why the LOV for Workflow owner wasn’t “working” for a user:
…Everything is working fine except the fact that when I go to the notifications Page from the WF Admin responsibility, I can only search for my own Workflows. I can enter the Item Type and the internal name, but the field Workflow Owned By is not a LOV. The field is appearing as a hard-coded value of my user name, so I cannot see any processes launched by anyone else.
The answer is that the ability to view processes which are owned by another user is restricted to either the user who has been set as the Workflow administrator, or if the administrator is set to a responsibility the user has been granted that responsibility.
In this case, the user was telling me that they had been granted the “WF Admin responsibility”, but that isn’t necessarily the same as being a Workflow administrator. For example, the Workflow administrator might have been set to be an individual user (in standalone Workflow this was always set as SYSADMIN as standard).
If you have been granted the standard Workflow Administrator responsibility, you will be able to navigate the administration pages, although you won’t be able to modify any of those settings. So, navigate to the Admin tab, which will show you who is the current administrator or which responsibility has been set:
In the above image, the user has used the Workflow Administrator responsibility to navigate to the administration tab, but cannot make any changes because they do not have the responsibility to perform any admin tasks. If I grant the “Workflow Administrator Web (New)” responsibility to the user and refresh the page, I can now make changes because I now have the right access:
Once the user has been given the right responsibility, when they search for any Workflow processes, the previously hard-coded owner field is now an LOV.
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
CURSOR c_check_value_set ( cp_vs_name IN VARCHAR2
, cp_type_name IN VARCHAR2
, cp_msg_name IN VARCHAR2 ) IS
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);
IF :new.status = 'CANCELED'
AND :old.status = 'OPEN'
AND :new.mail_status = 'MAIL'
AND :old.mail_status = 'SENT' THEN
-- 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;
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
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';
WHEN NO_DATA_FOUND THEN
-- Not a response, so no need to cancel it
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.