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
