Tag Archives: abort

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.