Welcome to Part 4 of the series Oracle Workflow for eBS DBA’s.
The ‘Defer Thread’ has no node attributes.
When we run this process, and check the activity_statuses we get the following result:
select wpa.instance_label
, case when wpa.start_end is not null then wpa.start_end else wa.function end function
, wias.begin_date
, wias.end_date
, wias.activity_status status
, wias.activity_result_code result
, outbound_queue_id
from wf_item_activity_statuses wias
join wf_process_activities wpa
on (wias.process_activity=wpa.instance_id)
join wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wa.end_date is null)
join wf_activities_tl wat
on (wa.item_type=wat.item_type
and wa.name=wat.name
and wa.version=wat.version
and wat.language='US')
where wias.item_type='DBA_TYPE'
and wias.item_key='14'
order by wias.begin_date,wias.execution_time;
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT OUTBOUND_QUEUE_ID
------------------- ----------------- ------------------ ------------------ -------- ------ -----------------
DBA_CONTROL_PROCESS 24-7-2009 14:02:46 ACTIVE #NULL
START START 24-7-2009 14:02:46 24-7-2009 14:02:46 COMPLETE #NULL
DEFER WF_STANDARD.DEFER 24-7-2009 14:02:46 DEFERRED #NULL 6F728D74CA8142E6E0440003BAB3AD6B
Even though control was given back to the client, the item did not complete yet. The root is still active and the second function got status ‘Deferred’. This function is waiting to be processed by a background engine.
Before we start a background engine, we first take a look at the deferring mechanism.
The WF_STANDARD.DEFER function only sets the result_code of the process_activity to ‘DEFERRED’. To be precise it is set to the constant ‘wf_engine.eng_deferred’. When the engine encounters this result_code, it will queue a message to the ‘WF_DEFERRED_QUEUE_M’queue with a payload of item_type, item_key and process_activity.
The table for the ‘WF_DEFERRED_QUEUE_M’ is ‘WF_DEFERRED_TABLE_M’. The queue table can be queried directly or through the queuing view. (Which shows some translated columns). The key is the outbound_queue_id from the wf_item_activity_statuses:
select queue
, corr_id
, msg_priority
, msg_state
, enq_time
, def.user_data.itemtype
, def.user_data.itemkey
, def.user_data.actid
, consumer_name
from applsys.AQ$WF_DEFERRED_TABLE_M def
where msg_id=(select outbound_queue_id
from wf_item_activity_statuses
where item_type='DBA_TYPE'
and item_key='14'
and activity_status='DEFERRED');
QUEUE CORR_ID PRIO MSG_STATE ENQ_TIME TYPE KEY ACTID CONSUMER_NAME
------------------- ------------ ---- --------- ------------------ -------- --- ------ -------------
WF_DEFERRED_QUEUE_M APPSDBA_TYPE 1 READY 24-7-2009 14:02:44 DBA_TYPE 14 245513 APPS
No surprises there. The message is queued, and ready to be picked up.
The queue is read by the ‘Workflow Background Process’. In eBS this is a concurrent request under the ‘System Administrator’ responsibility. We will discuss the parameters later in this article.
An alternative is to run a background engine directly from SQL. That is what we will do now.
Begin
Wf_engine.background (itemtype=>’DBA_TYPE’
,process_deferred=>TRUE
,process_timeout=>FALSE
,process_stuck=>FALSE);
End;
We will discuss the timeout and stuck parameters later in this article. And idem for 2 parameters for ‘Treshold’.
The background engine will dequeue the message from the queue, and process the workflow item as of that process_activity.
When we look at the ‘wf_item_activity_statuses’ we notice something strange.
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 24-7-2009 14:02:46 24-7-2009 14:20:43 COMPLETE #NULL
START START 24-7-2009 14:02:46 24-7-2009 14:02:46 COMPLETE #NULL
DEFER WF_STANDARD.DEFER 24-7-2009 14:20:43 24-7-2009 14:20:43 COMPLETE #NULL
END END 24-7-2009 14:20:43 24-7-2009 14:20:43 COMPLETE #NULL
The begin_date for the ‘Defer’ is the time that it was run by the background engine. This is because after dequeueing the workflow engine will restart the process starting with this process_activity. So it will actually be re-executed at that time.
We can see that more clearly with an adjusted ‘Track_flow_progress’ function (see part 2 of this series for the original). This version will return a completion code of ‘wf_engine.eng_deferred’ to defer the item. We then replace the original ‘Defer’ function with our ‘track_flow_progress’ function.
CREATE OR REPLACE Procedure track_flow_progress (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS
v_activity_name varchar2(30);
v_activity_version number;
v_process_name varchar2(30);
v_instance_label varchar2(30);
v_count number;
begin
select activity_name,wa.version,process_name,instance_label
into v_activity_name, v_activity_version, v_process_name, v_instance_label
from wf_process_activities wpa
join wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wa.end_date is null)
where wpa.instance_id=p_actid;
insert into xxx_track_flow (id
,item_type
,item_key
,activity_name
,activity_version
,process_name
,instance_id
,instance_label
,funcmode)
values (xxx_track_flow_s.nextval
,p_item_type
,p_item_key
,v_activity_name
,v_activity_version
,v_process_name
,p_actid
,v_instance_label
,p_funcmode
);
select count(*)
into v_count
from xxx_track_flow x
where item_type=p_item_type
and item_key=p_item_key
and instance_id=p_actid;
if v_count=1 then
p_result:=wf_engine.eng_deferred;
end if;
end;
/
Let’s see what happens when we put this instead of the standard ‘Defer’
begin
wf_engine.launchprocess(itemtype=>'DBA_TYPE',itemkey=>'15',process=>'DBA_CONTROL_PROCESS');
end;
We can confirm that the process is deferred:
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 24-7-2009 17:44:32 ACTIVE #NULL
START START 24-7-2009 17:44:32 24-7-2009 17:44:32 COMPLETE #NULL
TRACK_FLOW TRACK_FLOW_PROGRESS 24-7-2009 17:44:32 DEFERRED #NULL
The track_flow_progress function has executed once:
select count(*)
from xxx_track_flow
where item_type=’DBA_TYPE’
and item_key=’15’;
COUNT(*)
--------
1
Now when we run the background engine. And check again:
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 24-7-2009 17:44:32 24-7-2009 17:57:56 COMPLETE #NULL
START START 24-7-2009 17:44:32 24-7-2009 17:44:32 COMPLETE #NULL
TRACK_FLOW TRACK_FLOW_PROGRESS 24-7-2009 17:57:56 24-7-2009 17:57:56 COMPLETE #NULL
END END 24-7-2009 17:57:56 24-7-2009 17:57:56 COMPLETE #NULL
And ‘track_flow_progress’:
ID ITEM_TYPE KEY ACTIVITY_NAME VERSION PROCESS_NAME INSTANCE INSTANCE_LABEL FUNCMODE
174 DBA_TYPE 15 TRACK_FLOW 35 DBA_CONTROL_PROCESS 245549 TRACK_FLOW RUN
175 DBA_TYPE 15 TRACK_FLOW 35 DBA_CONTROL_PROCESS 245549 TRACK_FLOW RUN
The flow has completed now, and you can see the 2 calls to Track_flow_progress.
This is something that you need to be aware of, whenever an issue arises with a function that defers itself.
Now let’s look at the ‘costing’-model for the workflow engine. The goal of the background engines is to process activities with a long runtime, or that need heavy resources.
As we saw, the call to the background engine picks up all deferred items for a certain item_type. Especially with multiple or complex processes within an item_type, it might be convenient to make a further split.
This is done by assigning costs to different activities. Originally the cost is meant to be the runtime of an activity in seconds. (In workflow builder. It is stored in microseconds in the database).
When calling the background process, you can enter 2 parameters:
Mintreshold and maxtreshold.
The background engine will only process activities until the next defer. It does not matter if it is within the cost range of the background engine. The item will be deferred, and wait for the next run of an eligible background engine.
This is a good time to show the versioning of the workflow engine in action. We investigated the versioning system of the workflow in part 1 (and 2). Where we saw that the workflow will always follow the same process definition.
We can now see that this also goes for the activity_level. When we update the cost of an activity, while an item is active, the background engine will still use the original cost. Consider this:
I put a cost of 10 (seconds) to our ‘Track_flow_progress’ activity:
When we run an item now, it is deferred on the ‘Track_flow_progress’ activity. Deferring an item automatically means that the workflow engine will commit. (After all, the queue message needs to be visible to the background engine).
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT
------------------- ---- ------------------- ------------------ ------------------ -------- ------
DBA_CONTROL_PROCESS 0 24-7-2009 20:12:51 ACTIVE #NULL
START 0 START 24-7-2009 20:12:51 24-7-2009 20:12:51 COMPLETE #NULL
TRACK_FLOW-1 1000 TRACK_FLOW_PROGRESS 24-7-2009 20:12:51 DEFERRED
While the first item is deferred, I increased the cost of the activity to 20:
And started another item. This item is also deferred:
INSTANCE_LABEL COST ITEM_KEY FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 0 17 24-7-2009 20:13:01 ACTIVE #NULL
START 0 17 START 24-7-2009 20:13:01 24-7-2009 20:13:01 COMPLETE #NULL
TRACK_FLOW 2000 17 TRACK_FLOW_PROGRESS 24-7-2009 20:13:01 DEFERRED
Now when I run a background engine with minthreshold 0 and maxthreshold 1100 it nicely picks up the item with key ’16’. Only when the background engine is run with a maxthreshold of 2000 or more, it runs the item with key ’17’.
One final thing to mention about cost, is that the ‘online’ workflow engine will not defer activities with a cost under 50 microseconds. (0.50 in workflow builder).
You can change this threshold by adjusting wf_engine.threshold. In a pl/sql procedure call: wf_engine.threshold:= n;
All items launched with this setting, will use the adjusted threshold. Anything above this threshold needs to be picked up by the background engine.
Let’s take a look at the background engine then. We already saw that it uses the WF_DEFERRED_QUEUE_M to select activities to process.
But a common problem in eBS environments is the long run time of the background processes.
The first point of action would be to purge your obsolete workflow data. Try to use the concurrent program from eBS to do that. In a later part of this series, we will look at the purging mechanism and suggest some ways to optimize it.
But if after the purging the background engine still runs for a long time, some other factors might be involved.
A common issue is with processes that are dependent on some checks. For example, you might check if a condition is true. And if not, defer the process without a timeout. Every time the workflow background engine runs this function, it will be deferred again. Causing it to be re-queued for the background engine. Ultimately causing the background engine to get in a loop on this activity. Rather than deferring these activities, the developer should use a ‘Block’, or preferably the ‘Business Event System’. Using a ‘Wait’ with sufficient time-out time might also be an option.
A similar thing can happen with time-out values with very short wait times.
When you notice a high CPU usage for QMON (QMNx), it might be time to coalesce the AQ IOT’s (Index Organized Tables). At the time of writing Oracle published note: 271855.1 on Metalink to do this.
So far for the deferred function. Let’s take a look at the next control function: Block.
The block function is also available from the ‘STANDARD’ itemtype. It holds your item until the wf_engine.completeactivity API is called.
Originally this was developed to let the item wait for a call from an external system. But with the arrival of BES (Business Event System), which we’ll discuss later in this series, it’s usefulness has decreased.
However, the function is still found in lots of places. So we’ll look at the internals.
The block function is a call to ‘WF_STANDARD.BLOCK’. This procedure returns a resultcode of wf_engine.eng_notified. In wf_item_activity_statuses the activity_status of the process_activity will be ‘NOTIFIED’.
This status is also used for Notifications (see next part in the series), when a reply from the user is expected. Since there is no notification, the process_activity cannot continue.
I changed the ‘Control Process’ to include a ‘Block’ activity as follows:
Now when we launch an item, we see that it will hold on the block.
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 0 24-7-2009 20:55:00 ACTIVE #NULL
START 0 START 24-7-2009 20:55:00 24-7-2009 20:55:00 COMPLETE #NULL
BLOCK 1 WF_STANDARD.BLOCK 24-7-2009 20:55:00 NOTIFIED
To progress this item we now have to call the API: ‘wf_engine.completeactivity’. This API will initiate a workflow engine to continue the item.
The parameters for the API besides itemtype and itemkey are activity and result.
Activity is a concatenation of the process_name, ‘:’ and the instance_label from wf_process_activities.
In our case the call would be:
begin
wf_engine.completeactivity(itemtype=>'DBA_TYPE'
,itemkey=>'20'
,activity=>'DBA_CONTROL_PROCESS:BLOCK'
,result=>wf_engine.eng_completed);
end;
When the activity has a result type, the result has to match one of the lookup codes in the result type. Otherwise any value is allowed, even tough it is a good habit to use the result codes in use by the wf_engine.
So what happens when an item does not get the ‘CompleteActivity’ call? Since the ‘Block’ is designed for External Completion, it is not unlikely that an error in the external system will cause the ‘CompleteActivity’ not to be called. In that case the item will be in the ‘Notified’ state forever.
For these situations, you can put a ‘TimeOut’ on the process activity. A Time-out is either an absolute timestamp obtained from an item_attribute or a relative time set on the process_activity.
For this example, I set a relative time of 5 minutes on the ‘Block’ activity:
And of course an extra transition for the ‘Time out’, to a second ‘End’ node.
Now when we run the process, we see that the ‘Due_date’ column in WF_ITEM_ACTIVITY_STATUSES has been set:
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE
DBA_CONTROL_PROCESS 0 25-7-2009 17:26:05 ACTIVE #NULL
START 0 START 25-7-2009 17:26:05 25-7-2009 17:26:05 COMPLETE #NULL
BLOCK 1 WF_STANDARD.BLOCK 25-7-2009 17:26:05 NOTIFIED 25-7-2009 17:31:05
It is set for exactly 5 minutes after the begin_date of the process_activity.
After 5 minutes we can start a background process. With parameter ‘process_timeout=>TRUE’, or no parameter, since TRUE is the default.
Then we see that it has ran through the timeout transition:
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE
DBA_CONTROL_PROCESS 0 25-7-2009 17:26:05 25-7-2009 17:31:55 COMPLETE #NULL
START 0 START 25-7-2009 17:26:05 25-7-2009 17:26:05 COMPLETE #NULL
BLOCK 1 WF_STANDARD.BLOCK 25-7-2009 17:26:05 25-7-2009 17:31:55 COMPLETE #TIMEOUT 25-7-2009 17:31:05
END-1 0 END 25-7-2009 17:31:55 25-7-2009 17:31:55 COMPLETE #NULL
Note that this time, the begin_date of the ‘block’ activity did not change. This is because the activity was not rerun. It was just cut-off, and the engine picked up at the ‘timeout’ transition.
Even when the timeout period has expired, it is still possible to call the ‘CompleteActivity’ API. In that case, the workflow engine will still take the default transition, instead of the timeout one.
But once the item has gone through the timeout transition, it is not possible to call the ‘CompleteActivity’ API anymore. Trying to do so will result in a
‘ORA-20002: 3133: Activity instance ‘DBA_CONTROL_PROCESS:BLOCK’ is not a notified activity for item ‘DBA_TYPE/21’.
This error is usually innocent. It just means that the item has progressed beyond the process activity that is called to be completed. And a time-out is the most common cause. Of course all programs calling the ‘CompleteActivity’ API need error handling for this.
At this point, I’ll give a short example of the error handling. In a later part in this series, we’ll go deeper into workflow troubleshooting and error handling.
Now when we call the ‘CompleteActivity’ API for the item above, we run the following:
declare
v_errorname varchar2(30);
v_errormsg varchar2(2000);
v_errorstack varchar2(32000);
invalid_action EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_action, -20002);
begin
wf_engine.completeactivity(itemtype=>'DBA_TYPE'
,itemkey=>'21'
,activity=>'DBA_CONTROL_PROCESS:BLOCK'
,result=>wf_engine.eng_completed);
exception
when invalid_action THEN
wf_core.get_error(v_errorname,v_errormsg,v_errorstack);
dbms_output.put_line(v_errorname);
dbms_output.put_line(v_errormsg);
dbms_output.put_line(v_errorstack);
end;
/
WFENG_NOT_NOTIFIED
3133: Activity instance 'DBA_CONTROL_PROCESS:BLOCK' is not a notified activity
for item 'DBA_TYPE/21'.
Wf_Engine.CompleteActivity(DBA_TYPE, 21, DBA_CONTROL_PROCESS:BLOCK, COMPLETE)
Let’s go through the procedure.
First we see a declaration of a custom exception for ORA-20002. This is the custom error that workflow will issue. It is optional in this case, since any error can only occur from the API-call. But that might not always be the case.
In the exception block we first call ‘wf_core.get_error’ to retrieve the information about this error. If needed we can parse this error further. But the error_name already tells us that we tried to complete a process_activity that is not in a notified state. Based on this the caller should be able to decide what needs to be done.
With that we can move to the next function in this article.
That will be the ‘WAIT’ function. The ‘WAIT’ is used to postpone further processing of the item to a time set by an item_attribute or relative to the start of the process_activity.
It works differently from the way the ‘timeout’ worked on the ‘Block’ activity. That was only picked up by a background engine with parameter ‘process_timeout=>TRUE’.
Let’s replace the block in our process with a ‘Wait’ function. The ‘Wait’ can be copied from the ‘STANDARD’ itemtype. It also needs a lookup type ‘Wait Mode’. This lookup type defines how to determine the timestamp to complete the process activity.
The possible values are ‘Day of the week’, ‘Day of the month’, ‘Absolute date’ and ‘Relative time’.
When you add the ‘Wait’ function to your item, this will be the first ‘node attribute’ to set. As usual this can be set with a constant, or an item attribute value. When you choose to set it to a fixed date, you can also set the ‘Time of day’ attribute to set the timestamp. This is of course not needed for the ‘Relative time’ mode.
All node attributes can be set to a constant or an item_attribute_value. In our sample we will first set a relative time, based on an item attribute value. (for a change).
We use one function to set a new item_attribute:
And the second node is a ‘Wait’ till the requested time:
The relative time is in {days} . {fraction of day}. So 5/(24*60) is 5 minutes. 0.03 is just about 4.5 minutes therefore.
When we launch a new item, and check the status:
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE
DBA_CONTROL_PROCESS 0 25-7-2009 20:23:22 ACTIVE #NULL
START 0 START 25-7-2009 20:23:22 25-7-2009 20:23:22 COMPLETE #NULL
ASSIGN 1 WF_STANDARD.ASSIGN 25-7-2009 20:23:22 25-7-2009 20:23:22 COMPLETE #NULL
WAIT 1 WF_STANDARD.WAIT 25-7-2009 20:27:41 DEFERRED #NULL
So the ‘Wait’ activity is deferred. But it does not get a due_date. Instead the begin_date is set to the time till which the activity has to wait.
Since the status=’DEFERRED’, we also have a record in the wf_deferred_queue_m:
select queue
, corr_id
, msg_state
, enq_time
, delay
, deq_time
, def.user_data.itemtype itemtype
, def.user_data.itemkey itemkey
, def.user_data.actid actid
from applsys.AQ$WF_DEFERRED_TABLE_M def
where msg_id=(select outbound_queue_id
from wf_item_activity_statuses
where item_type='DBA_TYPE'
and item_key='22'
and activity_status='DEFERRED');
QUEUE CORR_ID MSG_STATE ENQ_TIME DELAY ITEMTYPE ITEMKEY ACTID
WF_DEFERRED_QUEUE_M APPSDBA_TYPE WAIT 25-7-2009 20:23:19 25-7-2009 20:27:39 DBA_TYPE 22 245973
So the message has been queued with a delay till the start_time of the process_activity.
Note that the msg_state=’WAIT’. This means that the message cannot be dequeued yet. One or more QMON processes need to be running. (Set with ‘alter system set aq_tm_processes=x scope=both;). These processes are responsible for moving messages from the ‘WAIT’ state to the ‘READY’ state at the designated moment.
Depending on the load on your system and the number of messages to be handled, you can put more QMON processes. But for most systems one QMON process will be sufficient for the workflow activity. If you need more than one QMON process, you’ll also have to be running multiple background engines.
Only messages with msg_state ‘READY’ can be dequeued and processed by the background engine.
When the message is eligible to be dequeued, you can run a background engine to process the process_activity as any ‘Deferred’ activity. The begin_date of the process_activity will then be updated to the time it was processed by the background engine.
Now let’s change the process to use a ‘Wait’ till a day of the week. In this case we’ll wait till Monday morning. First we set the attribute to the ‘Date’ type.
Then we change the ‘Assign activity’. Here we set the value to any date with the timestamp that we want. The date-part of the assignment will be ignored by the ‘Wait’ function.
Finally we update the Node attributes on the ‘Wait’ activity to be ‘Day of the Week’, a ‘Monday’, and the ‘Delay time’ attribute for ‘Time of the Day’.
Now when we launch a process, we can see that the activity is indeed postponed till the next Monday. (In this case 27th july 2009). At 8.00AM.
INSTANCE_LABEL COST FUNCTION BEGIN_DATE END_DATE STATUS RESULT DUE_DATE
DBA_CONTROL_PROCESS 0 25-7-2009 20:50:14 ACTIVE #NULL
START 0 START 25-7-2009 20:50:14 25-7-2009 20:50:14 COMPLETE #NULL
ASSIGN 1 WF_STANDARD.ASSIGN 25-7-2009 20:50:14 25-7-2009 20:50:14 COMPLETE #NULL
WAIT 1 WF_STANDARD.WAIT 27-7-2009 8:00:00 DEFERRED #NULL
At this point it’s time to look at a special ‘feature’ of the ‘DEFERRED’ status. Before I said that you can put an activity in ‘DEFER’ mode just by returning a result_code of wf_engine.eng_defer.
There is a bit more to that. The WF_STANDARD.DEFER procedure checks whether it is being executed for the first or second time. The first time it is executed, it is to set the status to ‘DEFERRED’. That time, the result_code from wf_item_activity_statuses has not been set yet. So the procedure returns the result_code ‘wf_engine.eng_defer’. When it is being called, while the result_code in wf_item_activity_statuses has been set, it will assume it is being called from a background process and return ‘wf_engine.eng_completed’. Which will then signal the workflow engine to continue with the item.
We can use this mechanism to our own advantage. When an item is deferred, and we want it to be run immediately, without the background process, then we can retry the process activity. The workflow engine has an API for this:
Begin
Wf_engine.handleerror(itemtype=>’DBA_TYPE’
,itemkey=>’21’
,activity=>’DBA_CONTROL_PROCESS:DEFER’
,command=>’RETRY’
);
End;
We will see more about the handleerror API in a later part. With the command ‘RETRY’, it will re-execute the indicated process_activity. In the case of a ‘DEFER’, it will therefore complete the activity. And continue the process.
The same mechanism also applies to the ‘WAIT’ function, since it basically only defers an activity.
Be aware of this, when activities are re-executed. Since it may not always be the desired result to complete the activity.
Now let’s move on to the last controls that we are discussing in this part.
We start with the ‘Launch Process’ function. You can copy it from the ‘STANDARD’ item_type. Including the lookup_type ‘YES/NO’, if you don’t already have that. The lookup_type is required for one of it’s activity_attributes.
It will be clear what this function is doing from the name. It launches another item from the current item. There is no restriction on the item_type. The function will perform the same ‘wf_engine.launch’, that we execute from sql*plus during this series.
The function has six activity_attributes, one of which is mandatory (2 in our case, since we don’t have an automatic selection for the process yet.).
The first attribute is item_type. This shouldn’t require a further explanation.
Item_key is optional. If no item_key is provided, the function will build an item of the ‘<current item_type>: <current item_key>-n’ where n is a sequence number for the number of items launched by the current item. The sequence is stored in a new item_attribute: ‘LAUNCH_COUNT’.
Then comes the process_name. It is possible to use a selector function to automatically start the right process within an item_type. But when this is not used, you need to provide the process_name.
User_key and owner are optional, and refer to a user-defined reference key and a workflow role that will assume ownership of this item.
The last attribute is ‘Defer Immediate’, with a lookup_type of ‘YES/NO’. When set to no, the new item will be launched immediately, and be executed by the current workflow engine.
When ‘Defer Immediate’ is set to ‘Yes’, the activity will be deferred. And both the ‘Launch Process’ activity as the new item will be executed by a background engine.
After the ‘Launch Process’ activity, the item will continue it’s own processing. (Without the ‘Defer Immediate’ attribute set to ‘Yes’, the engine will first process the new item as far as possible, before returning to the original item). There is no further relationship between the two items.
The last two controls do create a relationship between two or more items. It is possible to create parent/child relationships on the item-level.
We are going to create a sample of this by using ‘DBA_CONTROL_PROCESS’ to launch ‘DBA_MAIN_PROCESS’ and wait for it’s completion.
On the ‘Launch Process’ function it is not possible to specify the parent/child relationship. So we have to use the ‘wf_engine.CreateProcess’ and ‘wf_engine.StartProcess’ functions. The first one only creates an item, without executing it.
After the ‘CreateProcess’, we can call the ‘SetItemParent’ API to set the parent/child relationship.
So we have to create a new procedure:
CREATE OR REPLACE PROCEDURE XXX_LAUNCH_CHILD (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS BEGIN
if p_funcmode='RUN' then
wf_engine.CreateProcess(itemtype=>p_item_type
,itemkey=>p_item_key||'-1'
,process=>'DBA_MAIN_PROCESS');
wf_engine.SetItemParent(itemtype=>p_item_type
,itemkey=>p_item_key||'-1'
,parent_itemtype=>p_item_type
,parent_itemkey=>p_item_key
,parent_context=>NULL
,masterdetail=>TRUE);
wf_engine.StartProcess(itemtype=>p_item_type
,itemkey=>p_item_key||'-1');
end if;
p_result:=wf_engine.eng_completed;
END XXX_LAUNCH_CHILD;
/
The ‘CreateProcess’ and ‘StartProcess’ should be obvious in their usage. In the ‘SetItemParent, you can indicate a ‘parent_context’. This is a free text value, that can be referenced again in the ‘WaitforFlow’ and ‘ContinueFlow’ functions. In case you launch multiple child items, you use the context value to know which items should continue. We will come back to this at the end of the example.
First we are going to build our process with the new function:
The process will look like this:
And of course we need to put the ‘ContinueFlow’ in the ‘DBA_MAIN_PROCESS’. Actually twice, since I want to put it in front of the ‘End’ and we have 2 exits.
Now when we start the ‘DBA_CONTROL_PROCESS’ we can see what happens:
select wpa.instance_label
, wias.item_key
, case when wpa.start_end is not null then wpa.start_end else wa.function end function
, wias.begin_date
, wias.end_date
, wias.activity_status status
, wias.activity_result_code result
from wf_item_activity_statuses wias
join wf_process_activities wpa
on (wias.process_activity=wpa.instance_id)
join wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wa.end_date is null)
join wf_activities_tl wat
on (wa.item_type=wat.item_type
and wa.name=wat.name
and wa.version=wat.version
and wat.language='US')
where wias.item_type='DBA_TYPE'
and wias.item_key like '20%'
order by wias.begin_date,wias.execution_time;
INSTANCE_LABEL KEY FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 20 26-7-2009 14:26:37 ACTIVE #NULL
START 20 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL
XXX_LAUNCH_CHILD 20 xxx_launch_child 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE COMPLETE
DBA_MAIN_PROCESS 20-1 26-7-2009 14:26:37 ACTIVE #NULL
START 20-1 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL
INITIALIZE_FLOW 20-1 XXX_WF_DBA.init 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE COMPLETE
COMPARETEXT 20-1 WF_STANDARD.COMPARE 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE EQ
CHECK_INVALIDS 20-1 XXX_CHECK_INVALIDS 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE Y
DEFER 20-1 WF_STANDARD.DEFER 26-7-2009 14:26:37 DEFERRED #NULL
GET_INVALIDS 20-1 XXX_WF_UTILS.get_invalids 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE
LOOPCOUNTER 20-1 WF_STANDARD.LOOPCOUNTER 26-7-2009 14:26:38 26-7-2009 14:26:38 COMPLETE EXIT
AND 20-1 WF_STANDARD.ANDJOIN 26-7-2009 14:26:38 WAITING
WAITFORFLOW 20 WF_STANDARD.WAITFORFLOW 26-7-2009 14:26:38 NOTIFIED
I purposely left the ‘Defer’ in the ‘DBA_MAIN_PROCESS’ so we can see the status while the ‘DBA_MAIN_PROCESS’ is running. When the workflow engine could not process ‘DBA_MAIN_PROCESS’ further, it returned to the original item, and executed ‘WAITFORFLOW’, which of course got the ‘NOTIFIED’ status.
We can also see the parent_child relationship by querying ‘WF_ITEMS’:
select item_type
, item_key key
, begin_date
, parent_item_type parent_type
, parent_item_key parent_key
from wf_items
where item_type='DBA_TYPE'
and item_key like '20%'
ITEM_TYPE KEY BEGIN_DATE PARENT_TYPE PARENT_KEY
DBA_TYPE 20-1 26-7-2009 14:26:37 DBA_TYPE 20
DBA_TYPE 20 26-7-2009 14:26:37
Now when we run a background engine, and check again:
INSTANCE_LABEL KEY FUNCTION BEGIN_DATE END_DATE STATUS RESULT
DBA_CONTROL_PROCESS 20 26-7-2009 14:26:37 26-7-2009 14:38:40 COMPLETE #NULL
START 20 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL
XXX_LAUNCH_CHILD 20 xxx_launch_child 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE COMPLETE
DBA_MAIN_PROCESS 20-1 26-7-2009 14:26:37 26-7-2009 14:38:40 COMPLETE #NULL
START 20-1 START 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE #NULL
INITIALIZE_FLOW 20-1 XXX_WF_DBA.init 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE COMPLETE
COMPARETEXT 20-1 WF_STANDARD.COMPARE 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE EQ
CHECK_INVALIDS 20-1 XXX_CHECK_INVALIDS 26-7-2009 14:26:37 26-7-2009 14:26:37 COMPLETE Y
GET_INVALIDS 20-1 XXX_WF_UTILS.get_invalids 26-7-2009 14:26:37 26-7-2009 14:26:38 COMPLETE
LOOPCOUNTER 20-1 WF_STANDARD.LOOPCOUNTER 26-7-2009 14:26:38 26-7-2009 14:26:38 COMPLETE EXIT
WAITFORFLOW 20 WF_STANDARD.WAITFORFLOW 26-7-2009 14:26:38 26-7-2009 14:38:40 COMPLETE #NULL
DEFER 20-1 WF_STANDARD.DEFER 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL
AND 20-1 WF_STANDARD.ANDJOIN 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL
CONTINUEFLOW 20-1 WF_STANDARD.CONTINUEFLOW 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL
END 20 END 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL
END-1 20-1 END 26-7-2009 14:38:40 26-7-2009 14:38:40 COMPLETE #NULL
The ContinueFlow nicely completed the ‘WaitforFlow’ and both items completed successfully.
So how does the ‘ContinueFlow’ activity know which process_activity to continue? The first check being done is whether the activity_attribute ‘Waiting Flow’ is set to Master or Detail. When it is set to ‘Detail, the ContinueFlow is a parent process. So it will look for it’s children in wf_items, and complete all activities that have the instance_label from the ‘Waiting Activity Label’ activity_attribute.
When it is set to ‘Master’, this is just one of the (possibly) multiple children that have completed. So it will count the number of children for the parent item. And check all of them to see if they have reached the ‘ContinueFlow’ process_activity. When all children have reached the ‘ContinueFlow’, the last item will complete the activity on the parent item.
Of course it is possible for a parent item to have multiple ‘WaitforFlow’ activities, for different child items. In this case, you need to distinguish the different parent/child relationships. That is where the ‘parent_context’ parameter from the ‘SetItemParent’ function comes in.
The parent_context can be used to group together all child items that were started from the same process_activity.
It was superb!
Hey, nice post, really well written. You should write more about this.
Thanx Arian, the defered execution of activities, I was looking for that subject. It didn't really understand how it worked until now.
It's an excellent article, congratulations!!!. Until now this is the best blog that i have visited. The level of knowledge is very good!!!.
Wow. great job. Keep up the good work.
Thanks a lot 🙂 Very informative. I searched a lot for workflow but now after reading ur articales I gor clear idea about workflow.