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