How to develop Master – Detail workflow process

I was looking for a way to keep a workflow on hold and having it wait on another workflow. I discovered a built in functionality given by Oracle to exactly that. I have created Master-Detail workflow whereby I have spawned the Detail workflow process from the Master workflow process. While the Detail workflow is in process the Master workflow will wait. Once the Detail process ends the Master workflow will continue. In case the Master process spawns multiple Detail process then the Master process will wait until all the Detail processes are completed.

Below is the development and testing process for this kind of workflow

Step 1: Create the workflow

I have created a single workflow with a Master process and a Child process.

The master process

The Master process will spawn the child processes and wait for all the Child processes to complete. The function, Spawn Lines, spawns the child processes using a PL/SQL procedure (given below). After spawning the Child processes the Master workflow calls a standard workflow function WAIT FOR FLOW and it waits until all the Child processes have completed.

Check the properties of Wait for Flow function

Click on Node Attributes tab.

Click on the name, Continuation Activity Label.

The name of the activity on which this process will restart from the wait state is named CONTINUEFLOW.

Note: CONTINUEFLOW is the short name of the function Continue Flow within the detail flow.

Click on Continuation Flow attribute.

This value is set to Detail. This denotes that the current, WAIT, function is within the Master flow.

The child process

The Child process sends a notification to a user and then calls the standard workflow function CONTINUE FLOW to send the indicator back to the Master process that the Child process is complete and the Master process can move forward.

Check the properties of the Continue Flow function.

Click on Node Attributes tab.

Click on the attribute name, Waiting Flow.

The value of the Waiting Flow attribute is set to Master. This means the process that is waiting on this process is named, Master.

Check the properties of the Master process.

Therefore when the child process executes the Continue Flow function, it activates the Master process.

Step 2: Write PL/SQL code to execute the Master process from PL/SQL

DECLARE
wfsequence NUMBER;
BEGIN
   -- Kick off the workflow
wf_engine.launchprocess (itemtype => 'XXTEST',
itemkey => '1234-5',
                            process                       => 'MASTER',
userkey => '01',
owner => 'SA1'
                           );
   COMMIT;
END;

Step 3: Write PL/SQL code to spawn the Child processes

This PL/SQL procedure will be executed from a function in the Master process.

CREATE OR REPLACE PROCEDURE xx_spawn_lines (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
RESULT OUT NOCOPY VARCHAR2
)
IS
wfsequence VARCHAR2 (20);
BEGIN
   -- Spawn 2 child processes
   FOR i IN 1 .. 2
   LOOP
      -- Stop the program for 10 seconds
      -- so that there is a gap between
      -- the 2 child processes
      DBMS_LOCK.sleep (seconds                       => 10);

SELECT TO_CHAR (SYSDATE, 'HH24MISS')
INTO wfsequence
        FROM DUAL;

      -- Create the workflow process instance
wf_engine.createprocess (itemtype => 'XXTEST',
itemkey => wfsequence,
                               process                       => 'CHILD',
                               user_key                      => NULL,
                               owner_role                    => NULL
                              );

      -- Set the user key of the workflow
wf_engine.setitemuserkey (itemtype => 'XXTEST',
itemkey => wfsequence,
userkey => '11'
                               );

      -- Set the workflow item owner
wf_engine.setitemowner (itemtype => 'XXTEST',
itemkey => wfsequence,
owner => 'SA1'
                             );

-- If the workflow we are about to execute is going to be a child process
      -- for another workflow then we need to connect the running parent process
wf_engine.setitemparent (itemtype => 'XXTEST',
itemkey => wfsequence,
parent_itemtype => 'XXTEST',
parent_itemkey => itemkey,
                               parent_context                => NULL
                              );

      -- Kick off the workflow
wf_engine.startprocess (itemtype => 'XXTEST', itemkey => wfsequence);
      COMMIT;
   END LOOP;
END;

Let us now test the workflow

We shall execute the Master process by executing the PL/SQL code given in Step 2. After execution, we shall check the workflow in Status Monitor of Workflow Administrator responsibility.

We can see that the Master process is in process and the 2 Child processes have been executed from the Master process.

Let us check the Activity History of Child process 1, i.e. Item key 162129.

Open the notification by clicking on the Notification icon

Enter some information in Note and click on Submit. The workflow will complete. Check the workflows in Status Monitor.

Note that the first Child process has completed but the 2^nd Child process and the Master process are still active. We shall now enter something in the 2^nd Child process to complete it.

After entering some text and pressing Submit in the notification of the 2^nd Child process workflow the workflow ends.

When we check the workflows in the Status Monitor we find that all the workflows, including the Master and the 2 Child processes have completed.

This workflow can be used in a requirement like the following:

A transaction is created, say an invoice with multiple distributions. Now we need to have the invoice lines approved by multiple people based on say, values of an accounting segment. When all the lines are approved only then the invoice can go to the next stage.

Cheers!

Related articles

• Send workflow notifications from PL/SQL without executing the workflow (oraclemaniac.com)
• How to send/test workflow notifications in Production instances (oraclemaniac.com)
• Send emails to non Oracle Apps users through workflow (oraclemaniac.com)
• Use WFLOAD to UPLOAD/DOWNLOAD workflow to/from the database (oraclemaniac.com)
• How to start a workflow using PL/SQL (oraclemaniac.com)
• View process flows within Oracle Apps (oraclemaniac.com)
• How to enforce a user to enter a comment when he rejects a workflow notification (oraclemaniac.com)
• Handling loops in workflow (oraclemaniac.com)
• How to invoke an Oracle apps form from a workflow notification (oraclemaniac.com)