Tag Archives: item

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.