Designing Simple Master/Deatil Oracle workflow

When you spawn a detail process from a master process in Oracle Workflow, you are

in effect creating a separate process with its own unique item type and item key. Oracle Workflow engine has provided an API “SetItemParent API” to define a master/detail relationship between the two processess. This APIshould be called after we called the CreateProcess API for the DETAIL process, BUT before a call has made toStartprocess API for detail process.

In the workflow we have two activities (one will be define in master and other in detail) to coordinate flow between master and detail process. As explained below,

One activity lets you pause a process and the other signals the halted process to continue. To use these activities, you place one activity in the master process and the other in each detail process.





These activities are

1. Wait for Flow Activity
2. Continue Flow Activity

Both activities contain two activity attributes that you use to identify the coordinating

activity in the other process(es).



Wait for Flow Activity – This activity can be in master /Detail and will pause the flow until the corresponding detail/master process completes for this activity. pl/sql procedure for Wait for Flow activity isWF_STANDARD.WAITFORFLOW.

Based on our business requirements we can have any number of Wait for Flow activities in the same workflowprocess.One very Important thing that should be keep in consideration when we design Mater /Detail relationship, If we have ONE master and ONE detail process, but MASTER process wait for the DETAIL process at multiple stages of its (master) flow, in that case master process should have separate Wait for Flow activity node for each time it wait for detail process. Similarly DETAIL process should have separate Continue Flow Activity corresponding to each Wait for Flow activity in Master process.





Wait for Flow activity contains 2 activity level attributes

1. 
   
   

Continuation Flow - specify whether this activity is waiting for a corresponding

"Master" or "Detail" process to complete.

Continuation Activity - specify the label of the activity node that must complete in

the corresponding process before the current process continues. The default value isCONTINUEFLOW.

Continue Flow Activity



Use this activity to mark the position in the corresponding detail or master process

where, upon completion, you want the halted process to continue. This activity calls a

PL/SQL procedure named WF_STANDARD.CONTINUEFLOW.

When a Continue Flow activity is executed, the WF_STANDARD.CONTINUEFLOW

procedure checks whether the corresponding Wait for Flow activity is associated with

any other processes that have not yet completed their Continue Flow activities. If so, the

waiting process keeps waiting for those other processes. If the Wait for Flow activity

is not waiting for any other processes, then the WF_STANDARD.CONTINUEFLOW

procedure completes the Wait for Flow activity so that the process that was waiting

now continues to the next activity.





Activity Attributes

The Continue Flow activity contains two activity attributes:

1. Waiting Flow - specify whether the halted process that is waiting for this activity to
   
   complete is a "Master" or "Detail" flow.
2. Waiting Activity - specify the label of the activity node in the halted process that
   
   is waiting for this activity to complete.

Below is example for how to call the master/detail workflow from pl/sql





Master Workflow (Calling Detail Flow with "Waiting Activity Label = Name of Child Activity waiting for this master to complete)









Master Workflow (received call from Detail activity with Continutation Activity Label = Name of Child Activity, that this Master is waiting in Order to progress further.)

Detail Flow (Received Call from master flow with "Continutation Activity Label = Name of Parent Activity that this Detail is waiting in Order to progress further)

Detail Workflow (Calling master flow with "Waiting Activity Label = Name of Parent Activity Waiting for this Child activity to complete)

declare

l_ckey varchar2(10) :='615';

l_pkey varchar2(10) :=l_key; --(can be any value)

l_ptype varchar2(10) :='SR_TYPE'; -- Parent type

l_ctype varchar2(10) :=’SR_INTER’; -- Child type

l_pprocess varchar2(100) := 'SR_PROCESS'; -- Parent process

l_cprocess varchar2(100) := 'SR_INT_PROCESS'; -- Child process

l_url varchar2(300);

begin

wf_engine.createprocess(l_ptype,l_pkey, l_pprocess,NULL,NULL);

wf_engine.createprocess(l_ctype,l_pkey, , l_cprocess,NULL,NULL);

wf_engine.SetItemParent(l_ctype,l_ckey,l_ptype,l_pkey,NULL,TRUE);

wf_engine.startprocess(l_ptype,l_pkey);

wf_engine.startprocess(l_ctype,l_ckey);



l_url:=wf_engine.getitemAttrText(l_ptype,l_pkey,'RECEIVER',TRUE);

dbms_output.put_line(l_url);



end;