Tag Archives: background process

Oracle Workflow for eBS DBA’s (Part 4)

 Welcome to Part 4 of the series Oracle Workflow for eBS DBA’s.

The previous articles can be found here: 1, 2, 3
 
In this part we will look at the controls for the workflow items. This includes ‘Defer thread’, ‘Block’, ‘Wait for flow’ and ‘Wait’. Also we will see the suspend and abort functions from the workflow engine.
These functions allow the execution of a workflow item to be halted, delayed or taken over from online processing to background processing. The ‘Wait for Flow’ allows some interaction between different items.
 
The first function we will look into is the ‘Defer Thread’ (or short ‘Defer’).
 
By default the session that starts an item executes all functions in that item. That means that long running activities are also executed online. The ‘Defer’ function makes it possible to queue those long running activities to be executed by a background process.
 
Let’s start with a small process to show how this works. We create an item with only a function ‘Defer’ (copied from the STANDARD itemtype). I created the following process:

 process_defer

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’
 

 process_defer2

 

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.