Master-Child Concurrent Program Using FND_CONC_GLOBAL

Let’s see how to design concurrent program which spawns multiple child concurrent programs. The master program waits till all child programs are complete.Oracle provides built-in package FND_CONC_GLOBAL and FND_REQUEST to achieve parent-child program using PL/SQL.

  • 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 sub request
  • 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

Let’s design two concurrent programs.

Contents

Master Concurrent Program

This is a master program. It does its processing and then launch child concurrent program using fnd_request.submit_request API. It goes in Paused state waiting response from child programs.

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 acknowldge 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 program 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 complte 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_demo


Advertisement

Related Post