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.  

One thought on “Workflow for eBS DBA’s (Part 3)

  1. Pingback: Oracle Tales » Oracle Workflow for eBS DBA’s (Part 4)

Leave a Reply

Your email address will not be published. Required fields are marked *