Oracle Apps: How make the Parent Concurrent Program to wait till all the Child Concurrent Program Completes

This note gives a sample code in which a parent program submits multiple child programs waits for all the child programs to complete, without consuming any resources.

Ref: Oracle E-Business Suite Developer's Guide: PL/SQL APIs for Concurrent Processing

Sample Code for the Parent Program and the Child Program. In the sample code, the parent program submits 5 child programs which just wait for 10 seconds and the parent program waits for all the child programs to finish.

Note:- Once the child program completes the parent program gets restarted. Please have a closer look at the first few lines in the Parent Program code.

Package Spec

CREATE OR REPLACE PACKAGE xxaj_master_child_cp  
 AS  
  PROCEDURE parent_prg(p_errbuf OUT VARCHAR2  
            ,p_retcode OUT VARCHAR2);  
 
  PROCEDURE child_prg(p_errbuf OUT VARCHAR2  
            ,p_retcode OUT VARCHAR2);  
 
 END xxaj_master_child_cp;  
 /  

Package Body

CREATE OR REPLACE PACKAGE BODY xxaj_master_child_cp
AS
  PROCEDURE parent_prg(p_errbuf OUT VARCHAR2
                      ,p_retcode OUT VARCHAR2)
  IS
    v_req_data   VARCHAR2(30);
    v_request_id NUMBER;
 
  BEGIN
 
    -- Read the value from REQUEST_DATA.  
    --If this is the first run of the program, then this value will be null.
    -- Otherwise, this will be the value that we passed to SET_REQ_GLOBALS on the previous run.
    v_req_data   := fnd_conc_global.request_data;
    fnd_file.put_line(fnd_file.LOG,'v_req_data : '||v_req_data);
 
    -- If equals to 'EXIT_AJ_PARENT', exit the program. 
    IF v_req_data ='EXIT_XXAJ_PARENT' THEN
       RETURN;
    END IF;
 
    FOR i IN 1..5
    LOOP
      -- Submit child program   
      v_request_id   := fnd_request.submit_request(application => 'XXAJ'
                                                  ,program     => 'XXAJ_CHILD'
                                                 ,sub_request => TRUE); --This is the parameter which treats this request as the child request   
 
      fnd_file.put_line(fnd_file.LOG,'Child Request Id '||(i)||':' || v_request_id);
    END LOOP;
 
    -- Here we set the globals to put the program into the PAUSED status on exit
    -- and to save the state in request_data. 
    fnd_conc_global.set_req_globals(conc_status => 'PAUSED'
                                  , request_data => 'EXIT_XXAJ_PARENT');
 
 
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.LOG,'Exception: ' || SQLERRM);
  END parent_prg;
 
  PROCEDURE child_prg(p_errbuf OUT VARCHAR2
                     ,p_retcode OUT VARCHAR2)
  IS
  BEGIN
 
    fnd_file.put_line(fnd_file.LOG,'Start');
    DBMS_LOCK.sleep(10);  -- Wait for some time to see the behaviour :)
    fnd_file.put_line(fnd_file.LOG,'End!!!!');
 
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.LOG,'Exception: ' || SQLERRM);
  END child_prg; 
 
END  xxaj_master_child_cp;
/

This is how the Find Requests window looks like when the program is submitted.

You can see that the Parent program is in the Paused status.

Feel free to point out if anything is wrong/missing in this blog.