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 / CaseWe 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: 
Now we can define a function to check the db_name. For comparison on item attribute values, Oracle delivers a standard function: 
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’. 
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. 
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). 
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 functionsWe 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: 
With a lookup code for ‘N’ and ‘Y’ (only ‘N’ is shown here). 
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: 
I will first show you what happens when we split the process flow, without any further action: 
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: 
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. 
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: 
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. 
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’. 
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.
Pingback: Oracle Tales » Oracle Workflow for eBS DBA’s (Part 4)