Monthly Archives: July 2009

Oracle workflow for eBS DBA’s (Part 4b ;-)

 A reader pointed out that I didn’t fulfill my promise to write about suspend, resume and abort in part 4 of ‘Oracle workflow for eBS DBA’s.

 
So to make up for that omission, I will first write a separate note about it here. Then I’ll incorporate it in the article at a later time. 
 
Let’s start with the abort. 
In part 3, we saw that we can get workflows in a state where they will never be able to continue again. We did this by setting the ‘On Revisit’ property to ignore. 
 
The correct way to handle these workflows is to run a workflow background engine with parameter ‘process_stuck’ set to ‘TRUE’. That will set the item status to ‘Error’ and run the appropriate error process. 
 
But there may be reasons where you want to just abort the item, without error processing. 
 
For those situations Oracle provides the ‘wf_engine.abortprocess’ API. 
The API will set the status of the process to complete. If a result is needed, you can set this on the call to the API. It defaults to ‘#FORCE’ (wf_engine.eng_force constant).
 
Let’s see how this works. First I used the ‘MAIN_DBA_PROCESS’ from part 3 of the series, and I set the ‘On Revisit’ for the ‘LOOP_COUNTER’ to ‘Ignore’.
Now when I run the process, we get this result: 
 
INSTANCE_LABEL   FUNCTION                      BEGIN_DATE         END_DATE           STATUS   RESULT   OUTBOUND_QUEUE_ID
DBA_MAIN_PROCESS                               31-7-2009 12:36:58                    ACTIVE   #NULL 
START            START                         31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE #NULL 
INITIALIZE_FLOW  XXX_WF_DBA.init               31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE COMPLETE 
COMPARETEXT      WF_STANDARD.COMPARE           31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE EQ 
CHECK_INVALIDS   XXX_CHECK_INVALIDS            31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE Y 
GET_INVALIDS     XXX_WF_UTILS.get_invalids     31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE  
LOOPCOUNTER      WF_STANDARD.LOOPCOUNTER       31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE LOOP 
PROCESS_INVALIDS XXX_WF_UTILS.process_invalids 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE  
DEFER            WF_STANDARD.DEFER             31-7-2009 12:37:33 31-7-2009 12:37:33 COMPLETE #NULL 
AND              WF_STANDARD.ANDJOIN           31-7-2009 12:37:33                    WAITING  
TRACK_FLOW-1     TRACK_FLOW_PROGRESS           31-7-2009 12:37:42 31-7-2009 12:37:42 COMPLETE  
 
 
Now we can abort the item with the API:
 
 
begin
         wf_engine.abortprocess(itemtype=>'DBA_TYPE'
                               ,itemkey=>'30'
                               ,process=>'ROOT:DBA_MAIN_PROCESS');
end;
 
Note how we have to indicate that we want to abort the root process of the DBA_MAIN_PROCESS. The workflow engine needs to know unambiguously which process to abort. The way to do that is to set ‘process:<activity>’ to indicate the process. In our case this would be ‘ROOT:DBA_MAIN_PROCESS’.
 
And this is the result afterwards. 
 
INSTANCE_LABEL FUNCTION BEGIN_DATE END_DATE STATUS RESULT OUTBOUND_QUEUE_ID
DBA_MAIN_PROCESS                               31-7-2009 12:36:58 31-7-2009 12:39:10 COMPLETE #FORCE 
START            START                         31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE #NULL 
INITIALIZE_FLOW  XXX_WF_DBA.init               31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE COMPLETE 
COMPARETEXT      WF_STANDARD.COMPARE           31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE EQ 
CHECK_INVALIDS   XXX_CHECK_INVALIDS            31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE Y 
GET_INVALIDS     XXX_WF_UTILS.get_invalids     31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE  
LOOPCOUNTER      WF_STANDARD.LOOPCOUNTER       31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE LOOP 
PROCESS_INVALIDS XXX_WF_UTILS.process_invalids 31-7-2009 12:36:58 31-7-2009 12:36:58 COMPLETE  
DEFER            WF_STANDARD.DEFER             31-7-2009 12:37:33 31-7-2009 12:37:33 COMPLETE #NULL 
AND              WF_STANDARD.ANDJOIN           31-7-2009 12:37:33 31-7-2009 12:39:10 COMPLETE #FORCE 
TRACK_FLOW-1     TRACK_FLOW_PROGRESS           31-7-2009 12:37:42 31-7-2009 12:37:42 COMPLETE  
 
 
 
Compare this with the result from running a background engine with parameter ‘process_stuck=>TRUE’:
 
ORA-20002: 3150: Process 'DBA_TYPE/33' is being worked upon. Please retry the current request on the process later.
ORA-06512: at "APPS.WF_CORE", line 300
ORA-06512: at "APPS.WF_ENGINE", line 4528
ORA-06512: at line 2
 
Of course this error can be captured and handled as we saw in ‘Oracle workflow for eBS DBA’s (Part 4)’
 
Then lets take a look at the ‘wf_engine.Suspend’ function. This is basically a ‘pause’-API for a workflow item. It sets the active process to ‘SUSPEND’. 
 
The workflow engine will not pick it up any more until the wf_engine.resume API is called. 
 
Let’s see the resume and suspend with a small example. I used the same dba_control_process. After launching it, it will be deferred. Instead of running a background engine, we suspend it. 
 
begin
     wf_engine.suspend(itemtype=>'DBA_TYPE',itemkey=>'34');
end;
 
And the status becomes:
 
INSTANCE_LABEL      FUNCTION          BEGIN_DATE         END_DATE           STATUS   RESULT OUTBOUND_QUEUE_ID
DBA_CONTROL_PROCESS                   31-7-2009 18:30:45                    SUSPEND  #NULL 
START               START             31-7-2009 18:30:45 31-7-2009 18:30:45 COMPLETE #NULL 
DEFER               WF_STANDARD.DEFER 31-7-2009 18:31:02                    DEFERRED #NULL  6FFEFFF31C2604F5E0440003BAB3AD6B
 
The interesting thing is that the deferred status is still there. Including its queue_id. So when we run a background engine. We’ll see that it indeed picks up the item. It dequeues the message but leaves the status on deferred. 
 
Now when we resume the process:
 
begin
    wf_engine.resume(itemtype=>'DBA_TYPE',itemkey=>'34');
end;
 
The function is performed, and the item continues as usual.
 

More on 1 != 1

I was reading the WorkflowFaq Blog today, where they discuss when 1 != 1 in Oracle.

Of I did some testing with this. First of course I reproduced the issue easy enough:

 

SQL> select 1/3*3 from dual;

 

     1/3*3

----------

         1

 

SQL> select * from dual where 1/3*3=1;

No rows selected.

It is obviously a rounding issue. So what happens if we force Oracle to round the result?

 

SQL> select * from dual where round(1/3*3)=1;

 

D

-

X

 

If the rounding went wrong, it will most likely be on the low side (0.3333……*3=0.9999……)

 

SQL> select trunc(1/3*3) from dual where ceil(1/3*3)=1;

 

D

-

0

 

We can see the real contents by dumping the result:

 

SQL> select dump(1/3*3) d from dual
  2  union
  3  select dump(1) d from dual;

 

D

--------------------------------------------------------------------------------

Typ=2 Len=21: 192,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,10

0,100,100,100,100

 

Typ=2 Len=2: 193,2 

I will go into the internal number format in a while. For now, it is sufficient to say that the 1/3*3 is indeed 0.9999999…….

Now how many decimals can we get?

 

SQL> select to_char(1/3*3,'9.99') from dual;

 

TO_CH

-----

 1.00

 

SQL> select to_char(1/3*3,'9.99999999999999999999999999999999999999999') from dual;

 

TO_CHAR(1/3*3,'9.999999999999999999999999999

--------------------------------------------

  .99999999999999999999999999999999999999990

 

There is the real value of 1/3*3.

The Oracle documentation says that the precision of the number format is 126 binary digits, approximately 38 decimals digits.

So when we force a mask with more decimals, Oracle will show the real value.

Àn interesting 'feature'. And definitely something to keep in mind.

One last note on the dumping of numbers.
dump(<number>,10) will dump the decimal value.
dump(<number>,16) will dump the hexadecimal value.

How do we translate it back to the real value then?
Let's do the excercise for a few numbers: 1234.5678 and -1234.5678.

There is some difference between positive and negative values. So let's do the positive one first.

 

SQL> select dump(1234.5678,10),dump(1234.5678,16) from dual;

 

DUMP(1234.5678,10)           DUMP(1234.5678,16)

---------------------------- --------------------------

Typ=2 Len=5: 194,13,35,57,79 Typ=2 Len=5: c2,d,23,39,4f

 

We start with the second to the last numbers. And we subtract 1:
194,12,34,56,78

Ignore the 194 for now, and put the decimal . after the second number: 12.345678

Now subtract 193 from the first value: 194-193=1.

Multiply the decimal value this number of times by 100: 12.345678 * 100 = 1234.5678.
(In our case we multiply by 100 once if the first value would be higher, mulitply more often).

The negative values are a little different:

 

SQL> select dump(-1234.5678,10),dump(-1234.5678,16) from dual;

 

DUMP(-1234.5678,10)             DUMP(-1234.5678,16)

------------------------------- ------------------------------

Typ=2 Len=6: 61,89,67,45,23,102 Typ=2 Len=6: 3d,59,43,2d,17,66

 

The 102 at the end means it is a negative value. Discard the 102 for the rest of the calculation.

61,89,67,45,23.

Subtract the second to the last number from 101.
101-89=12, 101-67=34, 101-45=56,101-23=78  gives
61,12 34 56 78

Put the decimal . after the second number. So we get 12.345678

Now subtract 62 from the first value and multiply by 100: 61-62 = -1.

Finally we divide the value this number of times by 100. In our case we have a negative, so we
multiply instead:

12.345678 * 100 = 1234.5678 -> And we had a negative. So -1234.5678

Redoing the first parts of the workflow series

Just to inform you that I’m in the process of reformatting the first parts of the ‘Oracle Workflow for eBS DBA’s’ series.

So there might be some links going wrong. But I’ll make sure the links page always has the correct links. Please check back there, if you think something is wrong.

Also you can leave a comment, of course! 

 

 

Oracle Workflow for eBS DBA’s (Part 1)

For many Oracle eBS-DBA’s workflow is a strange and hardly understood module.Still it is widely used in 11i and 12i. So let’s dive into its workings in more detail.

This is part one of a so far unlimited series. Don’t be put off by the lack of code in this part. We first need to go through the basics. In the next parts we’ll get more action.
During this series, I used an 11.5.10 instance on a 9.2.0.8 database. The basic statements in these articles will still hold for earlier and later versions, but small modifications may be needed.
In this first part we go into the definitions and the basics of the Workflow engine. We start with some definitions, and then we build a simple basic workflow.
We’ll see how this relates to the wf_tables in the database.

The basic terminology
 

First. What is a workflow? A workflow is a sequence of functions and events that follow a certain path based on decisions made during the progress of the sequence.
Most of us know the pictures from workflow builder. With the pictograms for functions joined together with lines.
That set is a definition of a workflow. In the Oracle workflow world it is called a ‘process’. The nodes in the process can be functions, processes or notifications.

All these are grouped together in containers that Oracle called an ‘Itemtype’. The itemtype is very important, since it will become part of the primary key in the underlying tables.
The actual workflows that are running according to the definition of the itemtype are called ‘Items´. The item is started as a specific process within an ‘itemtype’. And it is uniquely identified by the ‘itemtype’ and an ‘itemkey’.

Every process consists of 2 or more nodes, which are joined together by transitions. At least 2 nodes are required, because a process needs a ’start’ and a ’stop’-node.
Ok. We talked enough for now. Let’s build a process and find out the rest along the way.
By the way, all the definitions above will be linked to a glossary later on.

Getting Started

To start building our process, we first need the itemtype. To create an itemtype, we use ‘Workflow builder’. In workflow builder, when we click the new button we are greeted with this screen:

wf builder start

On right clicking the ‘untitled’ map, we can create a new itemtype.

New_item_type

Internal name is the unique name that will be used in the table keys for this itemtype and its items. It is limited to 8 characters. So choose wisely!
Display name is the name that will be shown to users when they need to interact with items from this itemtype.
The description…….. you can guess that one.
We will discuss the other three fields in a later article.

My first Itemtype

I choose to start building a flow that will do some DBA checks and tries to fix problems or notify the DBA if there is a problem.
During the course of building this flow, we’ll stumble on different features of the workflow engine.
The first step is to build the itemtype.
I called it: DBA_TYPE.
With a display name: DBA Itemtype
And a description: Itemtype to hold DBA processes and functions.

dba_item_type

When you open your newly created itemtype, you see the components that can be created within this itemtype.
You’ll remember that the flow definition was called a process. So next we create a new ‘Process’: 

dba_main_process

Because this is a process that we will only be calling from our client, we have no use for the result type at the moment.
Later on, we’ll see nested processes, where the result of a process will determine the direction of the calling process.
When we go to the Process Detail (right click the process). We again have a virgin drawing board.
This will be where the actual flow is created. Every process consists of activities (functions, notifications and processes) and the transitions between them (based on the results of the activities).
Also every process has to start with a ‘Start’ Activity and finish with an ‘End’ activity. (Take care to avoid loose ends, since the end finalizes the flow and gives back control, or makes the flow purgeable).
So first we create a new function to start our flow.

start_1

Note the wf_standard.noop for the function. This is a dummy function because the only purpose of this node is to indicate the starting point for the process.
Even though we named this function ‘START’, we still need to flag it as a ‘Start’ function. That is in the node tab.

start_2

We then create an ‘END’ function in the same way.
Finally we create our own function.
 

Now we have an item_type with 1 process, and 3 functions. It’s time to connect the functions together.
Right click START, and drag to INITIALIZE_FLOW. Then right click there and drag to END. The result should be like:  

process-1

Now we have a runnable flow.

You can even start it already, if you create an empty packaged function: ‘ XXX_WF_DBA.init’.

But there is more work to be done.
 

First we are going to see how this is recorded in the wf_ tables in our database in part 2 of our series.
 

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.

 

Workflow for eBS DBA’s (Part 3)

Workflow for ebs DBA’s (part 3) In this part of our series (previous parts to be found here and here) we are going to look into what I call workflow constructs. These are the constructs that determine the direction of your item. Like if (or case for that matter), and/or, loops, waits / holds.

If / Case

We pick up our initial workflow from part 2. And the first thing to do is a check if we are on the right database. Remember we assigned the db_name to an item_attribute. Now we will check if this is the version we want. To follow the progress of the item, I use a ‘track_flow_progress’ function, that inserts into a table the timestamp and some information from the process_activity that it is called from. Table creation:

 

  create table xxx_track_flow (id number
  		 	  ,item_type varchar2(8)
			  ,item_key varchar2(240)
			  ,activity_name varchar2(30)
			  ,activity_version number
			  ,process_name varchar2(30)
			  ,instance_id number
			  ,instance_label varchar2(30)
			  ,funcmode varchar2(30)
		      );

     

 

create sequence xxx_track_flow_s start with 1 increment by 1;

  And the procedure itself.

 

  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);
  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 wpa.process_version=wa.version)
	   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);
  end;

  This will record the basic information about the process activity being called. We put this in a new function in our item type:   track_flow Now we can define a function to check the db_name. For comparison on item attribute values, Oracle delivers a standard function:   create compare text This can be copied from the STANDARD itemtype that is delivered with the workflow engine. It is available from an eBS database, or on your workflow builder client from $ORACLE_HOME\wf\data\US\WFSTD.wft. We already see some interesting things about this function. First it has a ‘Result Type’. The ‘Result Type’ indicates the possible exits from our function. It refers to a lookup type that is also defined in the STANDARD itemtype. When you expand the lookup type, you see that it has 4 lookup codes: ‘Equal’, ‘Greater Than’, ‘Less Than’ and ‘NULL’. These codes are the possible return values for our function. At this point we can copy the lookup type and the function from the ‘STANDARD’ itemtype. Later we’ll build our own lookup types. To make a comparison, you need at least 2 parameters. The value to be tested, and the reference value that it will be compared to. When you expand the function ‘Compare Text’, there are 2 attributes defined for this function. These attributes are not the same as the item attributes that we used in the previous article. The attributes linked to the function are ‘Activity Attributes’. We can find them in the wf_activity_attributes(_tl) table:

 

select waa.activity_item_type
,	  waa.activity_name
,	  waa.activity_version
,	  waa.name
,	  sequence
,	  type
,	  value_type
,	  format
,	  display_name
,	  description
from wf_activity_attributes waa join wf_activity_attributes_tl waat
on (waa.activity_item_type=waat.activity_item_type
    and waa.activity_name=waat.activity_name
    and waa.activity_version=waat.activity_version
    and waa.name=waat.name
    and waat.language='US')
where waa.activity_item_type='DBA_TYPE'
and waa.activity_name='COMPARETEXT'
and waa.activity_version=1;

ACTIVITY ACTIVITY_NAME   ACTIVITY_VERSION NAME        SEQUENCE   TYPE     VALUE_TY FORMAT DISPLAY_NAME    DESCRIPTION
-------- --------------- ---------------- ----------- ---------- -------- -------- ------ --------------- ---------------
DBA_TYPE COMPARETEXT                    1 VALUE1               0 VARCHAR2 CONSTANT        Test value      Value to comp
DBA_TYPE COMPARETEXT                    1 VALUE2               1 VARCHAR2 CONSTANT 2000   Reference value Standard a

  The sequence is used to indicate the order in which the attributes are shown in the workflow builder. It is not relevant for us any more. I just put it there to indicate the purpose.   Now we can put the function in our process. I put it between ‘initialize flow’ and the ‘End’.   create compare text   When you draw the transitions, you’ll find that you are asked for the result that applies to this transition. I chose to draw one transition for result ‘Equal’. Leading to a ‘track flow’ function and then ‘End’. And another transition for result ‘Default’. Leading also to a ‘track flow’ function and then ‘End’. The result looking like this. process with compare On the ‘Compare Text’ function we now have to fill in the reference and test values. This is done on the node attribute tab of the activity. Here we choose item attribute ‘Database instance name’ for the test value (we assigned the DB_NAME to this item attribute in the init function. For the reference value we use TSTEBST1. (the db_name for the test database I used). Node attributes compare Before we run the process, we will first look at the process definition so far. Let’s go back to our ‘process definition’ query. I wrote in part 2 that I had taken a shortcut. And this is where it comes into play. Without adjustment, the query would not allow for multiple possibilities. So I adjusted the query a bit and included a result_code_origin, that will show which path we followed:

 

select wpa3.process_item_type
, 	   wpa3.process_name
, 	   wpa3.process_version
, 	   wpa3.instance_id
,	   wa3.item_type
,	   wa3.type
,	   wa3.name
,	   wpa3.instance_label
,	   wa3.function
,	   trans.result_code
,	   trans.p_from_act
,	   trans.p_res_code from_res_code
from   (select r*100+l2 cntr
	    ,      wat2.result_code
		,	   wat2.p_res_code
		,	   wat2.p_from_act
	   , 	   case when wat2.start_end = 'END'
	   		    and d.l2=2
			then wat2.to_process_activity
			else wat2.from_process_activity
			   end e_proc
	   from (select wat1.from_process_activity
	   		 ,		wat1.to_process_activity
			 , 		wat1.result_code
			 ,		wpa2.start_end
			 ,		rownum r
			 , 		prior wat1.result_code p_res_code
			 , 		prior wat1.from_process_activity p_from_act
	  	     from   wf_activity_transitions wat1
			 join   wf_process_activities wpa2
			 on 	(wat1.to_process_activity=wpa2.instance_id)
			 connect by prior wat1.to_process_activity = wat1.from_process_activity
			 start with wat1.from_process_activity=(select wpa1.instance_id
				  	 	 	    					from   wf_process_activities wpa1
											 		join   wf_activities wa1
											  		on     (wpa1.process_item_type=wa1.item_type
											  		 		and wpa1.process_name=wa1.name
											  		 		and wpa1.process_version=wa1.version
											  		 		and wa1.end_date is null)
											  		 where  wpa1.process_item_type='OEOL'
											  		 and    wpa1.process_name='XXR_BILL_ONLY_IB'
											  		 and    wpa1.start_end='START'
											        )
	        ) wat2
		join  (select level l2
			   from dual
			   connect by level<3) d
	    on ((start_end='END' and d.l2=2) or d.l2=1)
		) trans
join    wf_process_activities wpa3
on 		(trans.e_proc = wpa3.instance_id)
join    wf_activities wa3
on 		(wpa3.activity_item_type=wa3.item_type
		 and wpa3.activity_name=wa3.name
		 and wa3.end_date is null)
order by cntr;

     

 

  L1 PROCESS_ PROCESS_NAME    PROCESS_VERSION INSTANCE_ID RESU RESU ITEM_TYP NAME            INSTANCE_LABEL  TYPE     FUNCTION
---- -------- --------------- --------------- ----------- ---- ---- -------- --------------- --------------- -------- -------------------------
   1 DBA_TYPE DBA_MAIN_PROCES              11      244904 *         DBA_TYPE START           START           FUNCTION WF_STANDARD.NOOP
   2 DBA_TYPE DBA_MAIN_PROCES              11      244902 *    *    DBA_TYPE INITIALIZE_FLOW INITIALIZE_FLOW FUNCTION XXX_WF_DBA.init
   3 DBA_TYPE DBA_MAIN_PROCES              11      244906 *    *    DBA_TYPE COMPARETEXT     COMPARETEXT     FUNCTION WF_STANDARD.COMPARE
   3 DBA_TYPE DBA_MAIN_PROCES              11      244906 EQ   *    DBA_TYPE COMPARETEXT     COMPARETEXT     FUNCTION WF_STANDARD.COMPARE
   4 DBA_TYPE DBA_MAIN_PROCES              11      244908 *    EQ   DBA_TYPE TRACK_FLOW      TRACK_FLOW      FUNCTION TRACK_FLOW_PROGRESS
   4 DBA_TYPE DBA_MAIN_PROCES              11      244910 *    EQ   DBA_TYPE END             END             FUNCTION WF_STANDARD.NOOP
   4 DBA_TYPE DBA_MAIN_PROCES              11      244912 *    *    DBA_TYPE TRACK_FLOW      TRACK_FLOW-1    FUNCTION TRACK_FLOW_PROGRESS
   4 DBA_TYPE DBA_MAIN_PROCES              11      244914 *    *    DBA_TYPE END             END-1           FUNCTION WF_STANDARD.NOOP

  So far so good. Now let’s run the process, and see if the split is working as designed.  

 

begin
wf_engine.launchprocess(itemtype=>'DBA_TYPE',itemkey=>'4',process=>'DBA_MAIN_PROCESS');
end;

     

 

select item_type,item_key,activity_name,process_name,instance_id,instance_label,funcmode
from   xxx_track_flow
where  item_type='DBA_TYPE'
and    item_key='4';

     

 

ITEM_TYP ITEM_KEY   ACTIVITY_NAME   PROCESS_NAME    INSTANCE_ID INSTANCE_LABEL  FUNCMODE
-------- ---------- --------------- --------------- ----------- --------------- --------
DBA_TYPE 4          TRACK_FLOW      DBA_MAIN_PROCES      244899 TRACK_FLOW      RUN

  I included the instance_label because it shows the difference between the EQ and * transitions. In this case, the activity with instance_label ‘TRACK_FLOW’ is called through the EQ transition. The instance_label with ‘TRACK_FLOW-1’ is called trough the * transition. You can see the label from the node-tab in Workflow Builder. As you see, the use of result_types enables you to split a flow. However it is worth noting that only one transition is called at a time. When this transition leads to an ‘End’-node, the other transitions will not be called.

The AND/OR functions

We will now make a split based on the number of invalid objects. When there are invalid objects (my database has 10), we will call a recompile function. Another transition continues with the process doing the main checks that we want to perform. After compilation of these objects (I just use a dummy function for this) we want both transitions to join again. First I created the required function for checking if there are any invalids. This function returns a ‘Y’ or ‘N’. For this, we of course need a new result type: lookup_type With a lookup code for ‘N’ and ‘Y’ (only ‘N’ is shown here). lookup_code_n   And a function to check for invalids  

 

CREATE OR REPLACE PROCEDURE XXX_CHECK_INVALIDS(p_item_type IN VARCHAR2
  				 				,p_item_key IN VARCHAR2
				 				,p_actid IN NUMBER
				 				,p_funcmode IN VARCHAR2
				 				,p_result OUT VARCHAR2) IS
v_invalids varchar2(1) := 'N';
BEGIN
	 IF p_funcmode='RUN' then
	 	select case when count(*)>0 then 'Y' else 'N' end inv
		into v_invalids
		from all_objects
		where status='INVALID';
     END IF;
	 p_result := v_invalids;

END XXX_CHECK_INVALIDS;
/

  And a function in our item_type: check_invalids I will first show you what happens when we split the process flow, without any further action: check_invalid1 The transition straight up will become our ‘MAIN’ process. While the diversion on the ‘Yes’ transition will merge back after the requested action. So what happens when we run this:  

 

select item_type,item_key,activity_name,process_name,instance_id,instance_label,funcmode
from xxx_track_flow
where item_type='DBA_TYPE'
and item_key='5';

 

 

ITEM_TYPE	ITEM_KEY	ACTIVITY_NAME	PROCESS_NAME	INSTANCE_ID	INSTANCE_LABEL	FUNCMODE
DBA_TYPE	5	TRACK_FLOW	DBA_MAIN_PROCESS	245051	TRACK_FLOW-2	RUN

  This will be a bit unexpected for most. The transition for ‘Any’ and ‘Yes’ are both valid, so 2 rows In the track_flow table would be expected, right? Let’s see what wf_item_activity_statuses has to say.  

 

select wias.item_type
,      wias.item_key
,	   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
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='5'
order by wias.begin_date,wias.execution_time;

 

 

ITEM_TYPE	ITEM_KEY	INSTANCE_LABEL	FUNCTION		BEGIN_DATE	END_DATE		STATUS	RESULT
DBA_TYPE	5	DBA_MAIN_PROCESS			22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE	#NULL
DBA_TYPE	5	START		START		22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE	#NULL
DBA_TYPE	5	INITIALIZE_FLOW	XXX_WF_DBA.init	22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE	COMPLETE
DBA_TYPE	5	COMPARETEXT	WF_STANDARD.COMPARE	22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE	EQ
DBA_TYPE	5	CHECK_INVALIDS	XXX_CHECK_INVALIDS	22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE	Y
DBA_TYPE	5	TRACK_FLOW-2	TRACK_FLOW_PROGRESS	22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE
DBA_TYPE	5	END-1		END		22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE	#NULL
DBA_TYPE	5	TRACK_FLOW-1	TRACK_FLOW_PROGRESS	22-7-2009 13:07:13	22-7-2009 13:07:13	COMPLETE	#FORCE

  Now that’s a surprise. We see that both TRACK_FLOW’s have executed. But ‘TRACK_FLOW-1’ has a result code of ‘#FORCE’. The workflow engine has taken the first applicable transition. In this case the ‘Any’. This led to an ‘END’-node. So the engine ‘forced’ the second transition to abort. The only way to make sure every transition is completely executed, is to put an ‘AND’ function in. Copy the function from the standard itemtype, and build the following flow: check_invalid2 Now when we run the process, we see the following results:  

 

ITEM_TYPE	ITEM_KEY	ACTIVITY_NAME	PROCESS_NAME	INSTANCE_ID	INSTANCE_LABEL	FUNCMODE
DBA_TYPE	6	TRACK_FLOW	DBA_MAIN_PROCESS	245120	TRACK_FLOW-2	RUN
DBA_TYPE	6	TRACK_FLOW	DBA_MAIN_PROCESS	245118	TRACK_FLOW-1	RUN

    And for the activity_statuses:

 

ITEM_TYPE	ITEM_KEY	INSTANCE_LABEL	FUNCTION		BEGIN_DATE	END_DATE		STATUS	RESULT
DBA_TYPE	6	DBA_MAIN_PROCESS			22-7-2009 13:57:58	22-7-2009 13:57:59	COMPLETE	#NULL
DBA_TYPE	6	START	START			22-7-2009 13:57:58	22-7-2009 13:57:58	COMPLETE	#NULL
DBA_TYPE	6	INITIALIZE_FLOW	XXX_WF_DBA.init	22-7-2009 13:57:58	22-7-2009 13:57:58	COMPLETE	COMPLETE
DBA_TYPE	6	COMPARETEXT	WF_STANDARD.COMPARE	22-7-2009 13:57:58	22-7-2009 13:57:58	COMPLETE	EQ
DBA_TYPE	6	CHECK_INVALIDS	XXX_CHECK_INVALIDS	22-7-2009 13:57:58	22-7-2009 13:57:59	COMPLETE	Y
DBA_TYPE	6	TRACK_FLOW-2	TRACK_FLOW_PROGRESS	22-7-2009 13:57:59	22-7-2009 13:57:59	COMPLETE
DBA_TYPE	6	TRACK_FLOW-1	TRACK_FLOW_PROGRESS	22-7-2009 13:57:59	22-7-2009 13:57:59	COMPLETE
DBA_TYPE	6	AND		WF_STANDARD.ANDJOIN	22-7-2009 13:57:59	22-7-2009 13:57:59	COMPLETE	#NULL
DBA_TYPE	6	END-1		END		22-7-2009 13:57:59	22-7-2009 13:57:59	COMPLETE	#NULL

  This time both transitions were executed, and completed normally. In the ‘WF_STANDARD’ itemtype there is also an ‘OR’ function. This will continue as soon as any transition arrives there. In our case it would have the same result as the first process (a ‘#FORCE’ result on the activity in the second transition). Last but not least, we will look into loops. Loops are easily implemented in workflow. Even though it is often misused. We will build a loop that touches every invalid object in the database, and records information about it. For this we use a package (the nice thing about them is that they allow global use of a PL/SQL table):  

 

CREATE OR REPLACE PACKAGE XXX_WF_UTILS AS

  TYPE r_invalids IS RECORD  (owner all_objects.owner%type
  	    	 		 ,object_name all_objects.object_name%type
				 ,object_type all_objects.object_type%type);

  TYPE t_invalids IS TABLE OF r_invalids INDEX BY BINARY_INTEGER;

  v_invalids t_invalids;

  Procedure get_invalids (p_item_type IN VARCHAR2
   			   ,p_item_key IN VARCHAR2
			   ,p_actid IN NUMBER
		    	   ,p_funcmode IN VARCHAR2
			   ,p_result OUT VARCHAR2);

END XXX_WF_UTILS;
/

 

 

CREATE OR REPLACE PACKAGE BODY XXX_WF_UTILS AS

  Procedure get_invalids (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
          select owner,object_name,object_type bulk collect into xxx_wf_utils.v_invalids from dba_objects where status='INVALID';
          wf_engine.SetItemAttrNumber(itemtype=>p_item_type,itemkey=>p_item_key
                                 ,aname=>'NO_INVALID_OBJECTS',avalue=>xxx_wf_utils.v_invalids.count);
     End if;
  end;  

END XXX_WF_UTILS;
/

  This procedure will collect the invalid objects in a PL/SQL table and write the number of records into a new attribute (‘NO_INVALID_OBJECTS’). This is a new attribute with type number. After this, we create a loop in the workflow. In the Standard itemtype, there is a function ‘Loop Counter’ that does this very neatly. Finally we need a processor to handle the selected values. For this we create a table and expand the ‘XXX_WF_UTILS’ with a second procedure.

 

create table xxx_wf_invalids (id, owner, object_name, object_type)
as select object_id,owner,object_name,object_type from dba_objects where 1=0;

 

 

Procedure process_invalids (p_item_type IN VARCHAR2
  				 		 ,p_item_key IN VARCHAR2
				 		 ,p_actid IN NUMBER
				 		 ,p_funcmode IN VARCHAR2
				 		 ,p_result OUT VARCHAR2) is
  v_counter number;
  begin
       if p_funcmode=’RUN’ then
  	   v_counter:=wf_engine.GetItemAttrNumber(itemtype=>p_item_type,itemkey=>p_item_key,aname=>'COUNT_LOOP');
	   insert into xxx_wf_invalids (id, owner, object_name, object_type)
	   values (v_counter
	   		  ,xxx_wf_utils.v_invalids(v_counter).owner
	   		  ,xxx_wf_utils.v_invalids(v_counter).object_name
	   		  ,xxx_wf_utils.v_invalids(v_counter).object_type);
	    wf_engine.SetItemAttrNumber(itemtype=>p_item_type,itemkey=>p_item_key,aname=>'COUNT_LOOP',avalue=>v_counter+1);
       end if;
  end;

  As you can see, we also need a new item_attribute to count the number of executions: ‘COUNT_LOOP’. This is defined with a default value of 1. count_loop During every execution, the counter is increased. And it serves as a pointer for the pl/sql table. Now we add the ‘Loop Counter’ and ‘Process Invalid’ functions to our process: invalids loop The ‘Loop Counter’ activity has an activity-attribute: ‘Loop Limit’. This indicates the number of times the loop will be traversed. We stored the number of invalid objects in attribute ‘Number of Invalid Objects’. So we can use that to set the attribute on the process activity. loop_item_attribute The ‘Loop Limit’ attribute is also stored in the ‘WF_ITEM_ATTRIBUTE_VALUES’ table. But since it is an activity attribute, it has a name that is dynamically created. In this case the name is: ‘LOOP_COUNT:245195’ where ‘LOOP_COUNT’ is the first 10 characters of the activity name. And 245195 is the instance_id from the process_activity. Because it is not easy to query the attribute value (The name changes with different versions of the workflow, so we would need an initialization function to get the correct name), we created the ‘COUNT_LOOP’ attribute. Only one thing is remaining now. The workflow engine needs to know how to handle the process activities when they are called in a loop. For this, there is an ‘On Revisit’ field on the Detail tab of the process activity. Since ‘LOOPCOUNTER’ is the first activity to be revisited, it is called the ‘Pivot’ activity. The On Revisit property is only effective for this activity. Let’s see what happens on the different settings for On Revisit. First we start with ‘Loop’. on_revisit_loop Now when we run the workflow, we see the following in XXX_WF_INVALIDS:

 

ID	OWNER	OBJECT_NAME		OBJECT_TYPE
1	SYS	DBMS_SUMADV		PACKAGE BODY
2	ORDSYS	ORDIMGEXTCODEC_PKG		PACKAGE BODY
3	ORDSYS	ORDIMG_PKG		PACKAGE BODY
4	CTXSYS	DRIDISP			PACKAGE BODY
5	CTXSYS	CTX_DOC			PACKAGE BODY
6	APPS	ECE_PO_ARCHIVE_PKG		PACKAGE BODY
7	APPS	PA_MC_CURRENCY_PKG		PACKAGE BODY
8	APPS	CN_FORMULA_GEN_PKG		PACKAGE BODY
9	APPS	AMS_DCF_DEFAULT_VALUES	PACKAGE BODY
10	APPS	AMS_DCF_TITLE		PACKAGE BODY

  So the workflow has ran as expected. We can verify this in wf_item_activity_statuses:  

 

ITEM_TYPE	KEY	INSTANCE_LABEL	FUNCTION			BEGIN_DATE	END_DATE		STATUS    RESULT
DBA_TYPE	7	DBA_MAIN_PROCESS				22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE	#NULL
DBA_TYPE	7	START		START			22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE	#NULL
DBA_TYPE	7	INITIALIZE_FLOW	XXX_WF_DBA.init		22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE COMPLETE
DBA_TYPE	7	COMPARETEXT	WF_STANDARD.COMPARE		22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE	EQ
DBA_TYPE	7	CHECK_INVALIDS	XXX_CHECK_INVALIDS		22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE	Y
DBA_TYPE	7	TRACK_FLOW-2	TRACK_FLOW_PROGRESS		22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE
DBA_TYPE	7	GET_INVALIDS	XXX_WF_UTILS.get_invalids	22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE
DBA_TYPE	7	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE	EXIT
DBA_TYPE	7	AND		WF_STANDARD.ANDJOIN		22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE	#NULL
DBA_TYPE	7	END-1		END			22-7-2009 16:12:23	22-7-2009 16:12:23	COMPLETE	#NULL

  You can see here that ‘GET_INVALIDS’ and ‘LOOPCOUNTER’ have been executed. But they are supposed to have been executed 10 times, right? Since the primary key of wf_item_activity_statuses is item_type, item_key, process_activity this information can not be recorded here. When a process_activity is called more than once, the original information from wf_item_activity_statuses is moved to a history table: ‘wf_item_activity_statuses_h’. I used the same query but now on ‘wf_item_activity_statuses_h’ and I reduced the rowcount to 10.  

 

ITEM_TYPE	ITEM_KEY	INSTANCE_LABEL	FUNCTION	BEGIN_DATE	END_DATE	STATUS	RESULT
DBA_TYPE	7	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE	LOOP
DBA_TYPE	7	PROCESS_INVALIDS	XXX_WF_UTILS.process_invalids	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE
DBA_TYPE	7	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE	LOOP
DBA_TYPE	7	PROCESS_INVALIDS	XXX_WF_UTILS.process_invalids	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE
DBA_TYPE	7	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE	LOOP
DBA_TYPE	7	PROCESS_INVALIDS	XXX_WF_UTILS.process_invalids	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE
DBA_TYPE	7	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE	LOOP
DBA_TYPE	7	PROCESS_INVALIDS	XXX_WF_UTILS.process_invalids	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE
DBA_TYPE	7	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE	LOOP
DBA_TYPE	7	PROCESS_INVALIDS	XXX_WF_UTILS.process_invalids	22-7-2009 16:21:43	22-7-2009 16:21:43	COMPLETE

  Now let’s go back to the On Revisit property and set it to ‘Ignore’. After running the process we see:  

 

ITEM_TYPE	ITEM_KEY	INSTANCE_LABEL	FUNCTION			BEGIN_DATE	END_DATE	STATUS	RESULT
DBA_TYPE	8	DBA_MAIN_PROCESS				22-7-2009 16:27:23			ACTIVE	#NULL
DBA_TYPE	8	START		START			22-7-2009 16:27:23	22-7-2009 16:27:23	COMPLETE	#NULL
DBA_TYPE	8	INITIALIZE_FLOW	XXX_WF_DBA.init		22-7-2009 16:27:23	22-7-2009 16:27:23	COMPLETE COMPLETE
DBA_TYPE	8	COMPARETEXT	WF_STANDARD.COMPARE		22-7-2009 16:27:23	22-7-2009 16:27:23	COMPLETE	EQ
DBA_TYPE	8	CHECK_INVALIDS	XXX_CHECK_INVALIDS		22-7-2009 16:27:23	22-7-2009 16:27:23	COMPLETE	Y
DBA_TYPE	8	TRACK_FLOW-2	TRACK_FLOW_PROGRESS		22-7-2009 16:27:23	22-7-2009 16:27:23	COMPLETE
DBA_TYPE	8	AND		WF_STANDARD.ANDJOIN		22-7-2009 16:27:23			WAITING
DBA_TYPE	8	GET_INVALIDS	XXX_WF_UTILS.get_invalids	22-7-2009 16:27:23	22-7-2009 16:27:24	COMPLETE
DBA_TYPE	8	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	22-7-2009 16:27:24	22-7-2009 16:27:24	COMPLETE	LOOP
DBA_TYPE	8	PROCESS_INVALIDS	XXX_WF_UTILS.process_invalids	22-7-2009 16:27:24	22-7-2009 16:27:24	COMPLETE

  That doesn’t look too good. Our item is hanging, without any process_activities to be run. The last process to complete was ‘Process_invalids’. When it came back to ‘Loop Counter’, we told the engine to ignore the process. The engine could no longer determine where to continue, and stopped the processing. The last option is to set On Revisit to ‘Reset’. When we try that, we see that it completed the process again:

 

ITEM_TYPE	ITEM_KEY	INSTANCE_LABEL	FUNCTION			BEGIN_DATE	END_DATE		STATUS	RESULT
DBA_TYPE	9	DBA_MAIN_PROCESS				23-7-2009 9:21:45	23-7-2009 9:21:46	COMPLETE	#NULL
DBA_TYPE	9	START		START			23-7-2009 9:21:45	23-7-2009 9:21:45	COMPLETE	#NULL
DBA_TYPE	9	INITIALIZE_FLOW	XXX_WF_DBA.init		23-7-2009 9:21:45	23-7-2009 9:21:45	COMPLETE COMPLETE
DBA_TYPE	9	COMPARETEXT	WF_STANDARD.COMPARE		23-7-2009 9:21:45	23-7-2009 9:21:45	COMPLETE	EQ
DBA_TYPE	9	CHECK_INVALIDS	XXX_CHECK_INVALIDS		23-7-2009 9:21:45	23-7-2009 9:21:46	COMPLETE	Y
DBA_TYPE	9	TRACK_FLOW-2	TRACK_FLOW_PROGRESS		23-7-2009 9:21:46	23-7-2009 9:21:46	COMPLETE
DBA_TYPE	9	GET_INVALIDS	XXX_WF_UTILS.get_invalids	23-7-2009 9:21:46	23-7-2009 9:21:46	COMPLETE
DBA_TYPE	9	LOOPCOUNTER	WF_STANDARD.LOOPCOUNTER	23-7-2009 9:21:46	23-7-2009 9:21:46	COMPLETE	EXIT
DBA_TYPE	9	AND		WF_STANDARD.ANDJOIN		23-7-2009 9:21:46	23-7-2009 9:21:46	COMPLETE	#NULL
DBA_TYPE	9	END-1		END			23-7-2009 9:21:46	23-7-2009 9:21:46	COMPLETE	#NULL

  But you will see the difference between ‘Reset’ and ‘Loop’ when you check ‘XXX_TRACK_PROGRESS’:    

 

ID	ITEM_TYPE	ITEM_KEY	ACTIVITY_NAME	ACTIVITY_VERSION	PROCESS_NAME	INSTANCE_ID	INSTANCE_LABEL	FUNCMODE
87	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245443	TRACK_FLOW-2	RUN
88	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	RUN
89	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	CANCEL
90	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	RUN
91	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	CANCEL
92	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	RUN
93	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	CANCEL
94	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	RUN
95	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	CANCEL
96	DBA_TYPE	9	TRACK_FLOW	32	DBA_MAIN_PROCESS	245453	TRACK_FLOW-1	RUN

  I limited the output to 10 records. But as you can see, a new funcmode showed up. Every time the ‘LOOPCOUNTER’ was revisited, it reran the process activities in the loop with a funcmode of ‘Cancel’. This is a feature that will allow you to undo the process activities, before they will be done again. That is just one of the reasons to always check the funcmode in your procedures! It may be clear that the ‘Loop’ is the correct setting for the On Revisit property in this case. With that we end part 3 of our series. The next part will be about the workflow controls, like ‘Defer’, ‘Wait’, ‘Wait for Flow’, and external calls.  

Workflow for eBS DBA’s. (Part 2)

This is part 2 of our series Workflow for eBS DBA’s. The first part can be found here.

In this part, we are going to see how the process we built in the first part can be started. And how we find information about it in the wf_ tables.

In the previous part we built an itemtype and a process. You’ll remember that the itemtype is the container that holds the other elements.

We can find the itemtype in our database with:
select wi.name
,      wi.persistence_type
,      wi.persistence_days
,      wit.display_name
,      wit.description
From   wf_item_types wi
,      wf_item_types_tl wit
where  wi.name=wit.name
and    wit.language='US'
and    wi.name='DBA_TYPE';

NAME     PERSISTE PERSISTENCE_DAYS DISPLAY_NAME    DESCRIPTION
-------- -------- ---------------- --------------- -------------------------------------------------
DBA_TYPE TEMP     0                DBA Itemtype    Itemtype to hold DBA processes and functions

So what can we find out about this item_type. We created one process within this item_type. Because processes can also be activities of another process, we need to identify the runnable processes. This is done by looking for the ‘ROOT’ processes:

select process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      instance_label
From   wf_process_activities
where  process_item_type='DBA_TYPE'
and    process_name='ROOT';

PROCESS_NAME       PROCESS_VERSION    ACTIVITY   ACTIVITY_NAME                  INSTANCE_ID   INSTANCE_LABEL
------------------ ------------------ ---------- ------------------------------ ------------- -----------------
ROOT                                1 DBA_TYPE   DBA_MAIN_PROCESS                      244747 DBA_MAIN_PROCESS

This shows us that there is a runnable process within this item_type, called: ‘DBA_MAIN_PROCESS’.

So how do we find out the definition of this process? First we need to find the right process_version.

select item_type
,      name
,      version
,      type
,      begin_date
,      end_date
From   wf_activities
where  name ='DBA_MAIN_PROCESS'
and    end_date is null;

Note that I selected the version (which is 1 now). And I added ‘end_date is null’. When we updated the process_definition, workflow will add new versions and end_date the previous one. This datamodel allows workflow to run it’s own versioning.

Once a process is started (actually an item created), it will check for the latest version of the process. And it will keep that definition of the process. Even if halfway the runtime of the item, a new definition of the process is loaded to the database.

Now that we know the version (1), we can start looking for the starting point. This is where the start (and end) activities come into the play.

select process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      instance_label
From   wf_process_activities
where  process_item_type='DBA_TYPE'
and    process_name='DBA_MAIN_PROCESS'
and    process_version=1
and    start_end='START';

PROCESS_NAME                   PROCESS_VERSION ACTIVITY ACTIVITY_NAME                  INSTANCE_ID INSTANCE_LABEL
------------------------------ --------------- -------- ------------------------------ ----------- --------------
DBA_MAIN_PROCESS                             1 DBA_TYPE START                               244748 START

This selects the first activity for our version of the process.  I also selected the instance_id. The instance_id is unique for the combination of item_type, process_name, process_version and activity_name. In other words it uniquely defines our activity in time (and space / item_type).

From this point we can follow the process through wf_activity_transitions:

select from_process_activity
,      to_process_activity
,      result_code
from   wf_activity_transitions wit
connect by from_process_activity=prior to_process_activity
start with from_process_activity=244748;

FROM_PROCESS_ACTIVITY TO_PROCESS_ACTIVITY RESULT_CODE
--------------------- ------------------- ------------------------------
244748              244752 *
244752               244750 *

This table includes the process_activities connected to each other. The default result_code is ‘*’. Later on we will see different result_codes.

In our flow, we see that the flow goes from process_activity instance_id  244748  to 244752 to 244750. In your system, the id’s will be different, of course.

Here is a query to show the flow, including the detail on the process_activities:

select l1
,      process_item_type
,      process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      result_code
,      to_process_activity
from (select t2.*
,       case when l1=mx and l2=2 then to_process_activity
else from_process_activity
end pa
from (select t1.*
,      count(l1) over () mx
from   (select level l1
,       from_process_activity
,       result_code
,       to_process_activity
From    wf_activity_transitions wat
connect by prior wat.to_process_activity=wat.from_process_activity
start with wat.from_process_activity=244748
) t1
) t2
join (select level l2 from dual connect by level<3) dummy on (l2=1 or l1=mx)
) wf_proc
,     wf_process_activities wpa
where wf_proc.pa=wpa.instance_id;

Even though it is interesting to see the flow. We still can’t see what is actually being done. That information is stored in the wf_activities:

select l1
,      process_item_type
,      process_name
,      process_version
,      wpa.instance_id
,      wf_proc.result_code
,      wa.item_type
,      wa.name
,      wa.type
,      wa.function
from  (select t2.*
,      case when l1=mx and l2=2 then to_process_activity
else from_process_activity
end pa
from (select t1.*
,      count(l1) over () mx
from   (select level l1
,      from_process_activity
,      result_code
,      to_process_activity
From   wf_activity_transitions wat
connect by prior wat.to_process_activity=wat.from_process_activity
start with wat.from_process_activity=244748
) t1
) t2
join (select level l2 from dual connect by level<3) dummy on (l2=1 or l1=mx)
) wf_proc
,    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 wf_proc.pa=wpa.instance_id

I took a little shortcut here. Can you see which one? Let’s have a quiz to see who which additions and perfections you can make to this query.

So far we have seen the definition of the process. There are a few items left out. Remember that we created an attribute in the previous part? This is stored in wf_attributes.

Now that we have seen the definition of the process. It’s time to make it run. To run an item, we call the wf_engine.

The easiest procedure is the ‘launchprocess’. This creates an item, and starts executing it. Alternatively you can use the separate ‘createprocess’ and ‘startprocess’ procedures. For this article, I stick to the ‘launchprocess’.

The parameters for the procedure are: itemtype, itemkey, process, userkey and owner.

Itemtype is the itemtype to which our process belongs.

Itemkey is a unique identifier for items within this itemtype.

Process is the name of the process that we want to run. Later on, we’ll see how we can let the workflow engine choose the process automatically.

Userkey is an optional extra key to the item.

Owner is an optional ‘owner role’. We’ll see more about this later again.

Before we actually start the workflow, we still need to build the xxx_wf_dba.init procedure that is used as one of our activities. For now, I used an empty function:

CREATE OR REPLACE PACKAGE BODY XXX_WF_DBA AS
PROCEDURE init (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS
Begin
NULL;
p_result:=wf_engine.eng_completed;
END;
END XXX_WF_DBA;

(Obviously you’ll have to create the package header too).

Finally we can start our workflow:

Begin
Wf_engine.launchprocess(itemtype=>’DBA_TYPE’
,itemkey=>’1’
,process=>’DBA_MAIN_PROCESS’);
End;

The workflow engine does not issue a commit.

Note that the itemkey is a varchar2 value. This is important to remember when querying the tables, to avoid implicit type-conversions.

Of course we just had a dummy process, but we can see that it has actually run. First an item was created:

select item_type
,      item_key
,      root_activity
,      root_activity_version
,      begin_date
,      end_date
,      user_key
from   wf_items
where  item_type='DBA_TYPE';

ITEM_TYP ITEM_KEY   ROOT_ACTIVITY                  ROOT_ACTIVITY_VERSION BEGIN_DA END_DATE USER_KEY
-------- ---------- ------------------------------ --------------------- -------- -------- ---------
DBA_TYPE 1          DBA_MAIN_PROCESS                                   1 20-07-09 20-07-09

So we see that our item has ran and finished.

To see what was actually executed, we need to look at wf_item_activity_statuses:

select item_type
,      item_key
,      process_activity
,      activity_status
,      activity_result
,      begin_date
,      end_date
,      execution_time
from wf_item_activity_statuses
where item_type='DBA_TYPE'
and item_key='1'
order by begin_date,execution_time;

Here we see the activities that were executed. Already from the 4 rows you can deduce that the process itself is also executed.

Some things to note: The process_activity refers to the instance_id on wf_process_activities. This is part of the workflow versioning.

The execution_time is set on creation and update of the item_activity_status. It is a ‘global’ counter for the number of times an item_activity_status is updated during the runtime of the workflow engine. So an update of an item_activity_status for any workflow item will update the counter.

That way you will always get a correct sequence from wf_item_activity_statuses by ordering on begin_date,execution_time. (Assuming it takes more than a second to bounce your application server).

A little bit more complete query to see the progress of the item:

select wias.item_type
,      wias.item_key
,      wat.display_name
,      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
,      wias.activity_result_code
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)
join wf_activities_tl wat on (wa.item_type=wat.item_type and wa.name=wat.name and wat.language='US')
where wias.item_type='DBA_TYPE'
order by wias.begin_date,wias.execution_time

At the end of this article a few items remain.

We still need to see the versioning in action. And it’s time to look at item_attributes. Item_attributes are the variables for the workflow engine.

Of course one follows from the other, because when we add item_attributes we will also create a new version. To demonstrate the effect, we first create an item, without starting it:

Begin
Wf_engine.createprocess(itemtype=>’DBA_TYPE’, itemkey=>’2’,process=>’DBA_MAIN_PROCESS’);
End;
Commit;

Now we return to the workflow builder. Here we create an attribute:

We will then update our dummy procedure to assign a value to this attribute.

CREATE OR REPLACE PACKAGE BODY XXX_WF_DBA AS
PROCEDURE init (p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_actid IN NUMBER
,p_funcmode IN VARCHAR2
,p_result OUT VARCHAR2) IS
v_process_version number;
Begin
if p_funcmode='RUN' then
select process_version
into   v_process_version
from   wf_process_activities
where  process_item_type=p_item_type
and    instance_id=p_actid;

if  v_process_version >= 2 then
wf_engine.SetItemAttrText(itemtype=>p_item_type
,itemkey =>p_item_key
,aname=>’INSTANCE_NAME’
,avalue=>sys_context(‘USERENV’,’DB_NAME’));
end if;
end if;
p_result:=wf_engine.eng_completed;
END;
END XXX_WF_DBA;

The test for p_funcmode will be explained further later on in this series. Basically, the workflow engine can call functions in different modes likes ‘RUN’ or ‘CANCEL’. Even though at the moment our function will only be ran in ‘RUN’ mode, it is good practice to test for the mode.

Then we have to test for the version of the workflow. This is needed, because of course the first version of the workflow did not have any item_attributes. So assigning a value to it would cause the workflow to error. The p_actid contains the instance_id from the process_activity. We can then get the process version.

The final interesting thing is the call to the workflow engine to assign a value to an item attribute. The WF_ENGINE has functions for all possible types of item_attributes. In this case we used the one for text assignment. The itemtype and itemkey are already passed from the workflow engine.

We still need to assign the attribute name (aname) and the value to be assigned (in this case the DB_NAME).

In the same way it is possible to read item_attributes with wf_engine.GetItemAttrText. This is a function that will return the value (so don’t use an avalue).

Now when we load the itemtype to the database, we will see a different version:

select process_name
,      process_version
,      activity_item_type
,      activity_name
,      instance_id
,      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 wpa.process_version=wa.version)
where  process_item_type='DBA_TYPE'
and    process_name='DBA_MAIN_PROCESS'
and    start_end='START'
and    wa.end_date is null;

PROCESS_NAME                   PROCESS_VERSION ACTIVITY ACTIVITY_NAME                  INSTANCE_ID INSTANCE_LABEL
------------------------------ --------------- -------- ------------------------------ ----------- --------------
DBA_MAIN_PROCESS                             2 DBA_TYPE START                               244755 START

As you can see the process version is now 2.

Also pay attention to the join condition. We now have to add the version, since both the activity as the process_activity have a new version.

Based on this query, I  also extended our query to see the item_type definition:

select l1
,      process_item_type
,      process_name
,      process_version
,      wpa.instance_id
,      wf_proc.result_code
,      wa.item_type
,      wa.name
,      wa.type
,      wa.function
from  (select t2.*
,      case when l1=mx and l2=2 then to_process_activity
else from_process_activity
end pa
from (select t1.*
,      count(l1) over () mx
from   (select level l1
,      from_process_activity
,      result_code
,      to_process_activity
From   wf_activity_transitions wat
connect by prior wat.to_process_activity=wat.from_process_activity
start with wat.from_process_activity=(select instance_id
from   wf_process_activities wpa
join   wf_activities wa
on (wpa.activity_item_type=wa.item_type
and wpa.activity_name=wa.name
and wpa.process_version=wa.version)
where  process_item_type='DBA_TYPE'
and    process_name='DBA_MAIN_PROCESS'
and    start_end='START'
and    wa.end_date is null)
) t1
) t2
join (select level l2 from dual connect by level<3) dummy on (l2=1 or l1=mx)
) wf_proc
,    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 wf_proc.pa=wpa.instance_id;

We can also already see the newly created attribute.

select item_type,name,type,format,text_default
from   wf_item_attributes
where  item_type='DBA_TYPE';

ITEM_TYP NAME                           TYPE     FORMAT   TEXT_DEFAULT
-------- ------------------------------ -------- -------- --------------------
DBA_TYPE INSTANCE_NAME                  VARCHAR2 10

But of course there are no values assigned to it yet. Let’s first run the item that we created a few minutes ago. This item will still run the first version of the process:

begin
wf_engine.startprocess(itemtype=>'DBA_TYPE',itemkey=>'2');
end;

Then we can use a slightly modified version of our script:

select wias.item_type
,      wias.item_key
,      wa.version
,      wat.display_name
,      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
,      wias.activity_result_code
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 wpa.process_version=wa.version)
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='2'
order by wias.begin_date,wias.execution_time

ITEM_TYP ITEM_KEY     VERSION DISPLAY_NAME     FUNCTION        BEGIN_DA END_DATE ACTIVITY ACTIVITY_RES
-------- ---------- --------- ---------------- --------------- -------- -------- -------- -----------
DBA_TYPE 2                  1 DBA Main process                 20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 2                  1 Start            START           20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 2                  1 Initialize flow  XXX_WF_DBA.init 20-07-09 20-07-09 COMPLETE COMPLETE
DBA_TYPE 2                  1 End              END             20-07-09 20-07-09 COMPLETE #NULL

Here we see that the version is still 1. If we would query the process_activities we would see that the instance_id’s are still the same as in the first item (item_key=’1’).

Of course we still need to see the item_attribute. So we start a new item.

begin
wf_engine.launchprocess(itemtype=>'DBA_TYPE',itemkey=>'3',process=>'DBA_MAIN_PROCESS');
end;

Now when we run the query above we see that it is indeed version 2

ITEM_TYP ITEM_KEY     VERSION DISPLAY_NAME     FUNCTION        BEGIN_DA END_DATE ACTIVITY ACTIVITY_RES
-------- ---------- --------- ---------------  --------------- -------- -------- -------- -----------
DBA_TYPE 3                  1 DBA Main process                 20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 3                  2 Start            START           20-07-09 20-07-09 COMPLETE #NULL
DBA_TYPE 3                  2 Initialize flow  XXX_WF_DBA.init 20-07-09 20-07-09 COMPLETE COMPLETE
DBA_TYPE 3                  2 End              END             20-07-09 20-07-09 COMPLETE #NULL

Only the root process has kept it’s original version. The others are indeed version 2.

That would mean that a value has been assigned to the item_attribute. Let’s take a look:

select item_type
,      item_key
,      name
,      text_value
from   wf_item_attribute_values
where  item_type='DBA_TYPE'
and    item_key in ('2','3')
and    name='INSTANCE_NAME'

ITEM_TYP ITEM_KEY   NAME               TEXT_VALUE
-------- ---------- ------------------ --------------------------
DBA_TYPE 3          INSTANCE_NAME      CRMOTPO2

With that we end part 2 of this series. In the next part we are going to look at the workflow constructs.

Workflow for eBS DBA’s. (Part 1)

For many eBS-DBA’s workflow is a strange and hardly understood module. Still it is widely used in 11i and 12i. So let’s dive into it’s workings in more detail.

This is part one of a series of 3. During this series, I used a 11.5.10 instance on a 9.2.0.8 database. The basics will still hold for earlier and later versions, but small modifications may be needed.

In this part we go into the definitions and the basics of Workflow. We start with some definitions, then build a simple basic workflow. And we see how this relates to the wf_tables in the database.

 

The basic terminology

First. What is a workflow? A workflow is a sequence of functions and events that follow a certain path based on decisions made during the progress of the sequence.

Most of us know the pictures from workflow builder. With the pictograms for functions joined together with lines.

That set is a definition of a workflow. That definition in Oracle is called a ‘process’. The nodes in the process can be functions, processes or notifications.

All these are grouped together in containers that Oracle called an ‘Itemtype’. The itemtype is very important, since it will become part of the primary key in the underlying tables.

The actual workflows that are running according to the definition of the itemtype are called ‘Item’s. The item is started as an itemtype and a process. And it is uniquely identified by itemtype and an itemkey.

Every process consists of 2 or more nodes, that are joined together by transitions. At least 2 nodes are required, because a process needs a ’start’ and a ’stop’-node.

Ok. Enough talking. Let’s build a process and find out the rest along the way. By the way. All the definitions above will be linked to a glossary later on.

 

Getting Started

To start building our process, we first need the itemtype.

To create an itemtype, we use ‘Workflow builder’. In workflow builder, when we click the new button we are greeted with this screen:

 wf builder start

 

On rightclicking the untitled map, we can create a new itemtype.

 New_item_type

Internal name is the unique name that will be used in the table keys for this itemtype and it’s items. It is limited to 8 characters. So choose wisely!

Display name is the name that will be shown to users when they need to interact with items from this itemtype.

The description…….. you can guess that one.

We will discuss the other three fields in a later article.

 

My first Itemtype

I choose to start building a flow that will do some DBA checks and tries to fix problems or notify the DBA if there is a problem. During the course of building this flow, we’ll stumble on different features of the workflow engine.

The first step is to build the itemtype. I called it: DBA_TYPE.

With a display name: DBA Itemtype

And a description: Itemtype to hold DBA processes and functions.

 dba_item_type

When you open your newly created itemtype, you see the components that can be created within this itemtype.

You’ll remember that the flow definition was called a processes. So next we create a new ‘Process’:

 dba_main_process

 

Because this is a process that we will only be calling from our client, we have no use for the result type at the moment. Later on, we’ll see nested processes, where the result of a process will determine the direction of the calling process.

When we go to the Process Detail (right click the process). We again have a virgin drawing board. This will be where the actual flow is created.

Every process consists of activities (functions, notifications and processes) and the transitions between them (based on the results of the activities).

Also every process has to start with a ‘Start’ Activity and finish with an ‘End’ activity. (Take care to avoid loose ends, since the end finalizes the flow and gives back control, or makes the flow purgeable).

So first we create a new function to start our flow.

 start_1

Note the wf_standard.noop for the function. This is a dummy function. Because the only purpose of this node is to indicate the starting point for the process.

Even though we named this function ‘START’, we still need to flag it as a ‘Start’ function. That is in the node tab.

 start_2

 

 We then create an ‘END’ function in the same way.

Finally we create our own function.

 init_function

Now we have an item_type with 1 process, and 3 functions.

Time to connect the functions together. Right click START, and drag to INITIALIZE_FLOW. Then right click there and drag to END.

The result should be like:

 process-1

Now we have a runnable flow. You can even start it already, if you create an empty packaged function: XXX_WF_DBA.init.

But there is more work to be done.

First we are going to see how this is recorded in the wf_ tables in our database. But that is part 2 of our series.