Parent Child concurrent request in Oracle Apps

How to make Parent concurrent request to wait until all child concurrent programs complete in Oracle Apps? So what we are looking at here is a Parent program that submits multiple child requests at once and waits till all child programs complete either successfully.

Oracle apps provide a built-in package FND_CONC_GLOBAL and FND_REQUEST to achieve parent-child programs using PL/SQL.

Note:=- Below example should work on Oracle Apps 1i as well as the R12 version.

Parent Concurrent Program

We will create a PL/SQL Based concurrent program to demonstrate. Go ahead and compile the below package in the database.

This creates a package parent_prg in the database with 2 out parameters.

CREATE OR REPLACE PROCEDURE parent_prg(
      p_errbuf OUT VARCHAR2,
      p_retcode OUT VARCHAR2)
IS
l_req_data VARCHAR2(30);
l_request_id NUMBER;

BEGIN
 --Get value of global variable. It is null initially.
   l_req_data   := fnd_conc_global.request_data;
   
 -- If equals to 'END', exit the program with return code '0'. 
   IF l_req_data ='END' THEN
      RETURN;
   END IF;
   
-- Set paranet program status as 'PAUSED' and set global variable value to 'END'   
   fnd_conc_global.set_req_globals(conc_status => 'PAUSED'
                                 , request_data => 'END');
   
   fnd_file.put_line(fnd_file.log,'Dear workers done my work. Please finish task assigned to you and acknowledge back');
   
   FOR i IN 1..5
   LOOP
-- Submit child workers   
      l_request_id   := fnd_request.submit_request('XXINV'
                                                 , 'CHILD_PRG'
					 	 , 'I am child '|| i
						 , NULL
						 , TRUE );

	  
      IF l_request_id > 0 THEN
         fnd_file.put_line(fnd_file.log,'Successfully submitted request ' || l_request_id);
      ELSE
         fnd_file.put_line(fnd_file.log,'Error while submitting request ' || fnd_message.get);
      END IF;
	  
   END LOOP;
   EXCEPTION
WHEN OTHERS THEN
   fnd_file.put_line(fnd_file.log,'Unexpected error in procedure ' || SQLERRM);
END; 
/

 

Child Concurrent Program

Master program spawns these programs to complete a task.

CREATE OR REPLACE PROCEDURE child_prg(
      p_errbuf OUT VARCHAR2,
      p_retcode OUT VARCHAR2)
IS
l_req_data VARCHAR2(30);
l_request_id NUMBER;

BEGIN
 
   
   fnd_file.put_line(fnd_file.log,'Thanks for opportunity. Let me complete task and update you');
   --Here you can write logic for child program. We will simply put wait
   
   dbms_lock.sleep(20);  -- Wait for some time and exit   
   
   fnd_file.put_line(fnd_file.log,'Complete my work. Passing control back to you');
	 
   EXCEPTION
WHEN OTHERS THEN
   fnd_file.put_line(fnd_file.log,'Unexpected error in procedure ' || SQLERRM);
END; 
/

This feature is very useful when you want to submit multiple concurrent programs in parallel. It is like Threading in JAVA.

This is how it submits concurrent programs.

 

FND_CONC_GLOBAL.SET_REQ_GLOBALS – sets the value of the REQUEST_DATA global variable.conc_status – Status of concurrent program like ‘PAUSED’, ‘RUNNING’
request_data – Value of the global variable
conc_restart_time – Concurrent restart time
release_sub_request – Release subrequest
FND_CONC_GLOBAL.REQUEST_DATA – retrieves the value of the REQUEST_DATA global
FND_REQUEST.SUBMIT_REQUEST – API to submit a concurrent program from PL/SQL.application – short name of the application associated with the concurrent request
program – short name of the program
description – description of the request that is displayed in the Concurrent Requests form. You can specify any valid description.
start_time – when the request should start formatted as HH24: MI or HH24: MI: SS
sub_request – TRUE – when the request is sub-request/child required of calling program.
argument1…100 – valid input parameter for the program