How to make a Concurrent Program WAIT untill completion of another Concurrent Program execution?

While working on interfaces we face some requirements like submitting a concurrent program sequentially one after the other. In some scenarios we need to submit 2nd concurrent program only when 1st current program is successfully completed. In such scenarios we need to wait for completion of the 1st program execution and then decide whether we need to submit the 2nd program or not. We can achieve this requirement using Oracle seeded API FND_CONCURRENT.WAIT_FOR_REQUEST.

What does API FND_CONCURRENT.WAIT_FOR_REQUEST do?

This API takes request ID as a parameter and waits for the request completion, returns phase/status and completion text to the caller. Calls SLEEP between db checks for waiting.

Below is the sample piece of code to achieve this requirement:

DECLARE
  --
  -- +=========================================================================
  -- | Purpose : How to make a Concurrent Program WAIT till completion another Concurrent Program execution?
  -- | Author  : Shailender Thallam
  -- +=========================================================================
  --
  lv_request_id       NUMBER;
  lc_phase            VARCHAR2(50);
  lc_status           VARCHAR2(50);
  lc_dev_phase        VARCHAR2(50);
  lc_dev_status       VARCHAR2(50);
  lc_message          VARCHAR2(50);
  l_req_return_status BOOLEAN;
BEGIN
  --
  --Setting Context
  --
   fnd_global.apps_initialize (
      user_id             => fnd_profile.VALUE ('USER_ID'),
      resp_id             => fnd_profile.VALUE ('RESP_ID'),
      resp_appl_id        => fnd_profile.VALUE ('RESP_APPL_ID'),
      security_group_id   => 0);
  --
  -- Submitting XX_PROGRAM_1;
  --
    lv_request_id := fnd_request.submit_request ( 
                            application   => 'XXCUST', 
                            program       => 'XX_PROGRAM_1', 
                            description   => 'XX_PROGRAM_1', 
                            start_time    => sysdate, 
                            sub_request   => FALSE);
   COMMIT;

  IF lv_request_id = 0 THEN
    dbms_output.put_line( 'Request Not Submitted due to "' || fnd_message.get || '".');
  ELSE
    dbms_output.put_line( 'The Program PROGRAM_1 submitted successfully – Request id :' || lv_request_id);
  END IF;
  IF lv_request_id > 0 THEN
    LOOP
 --
      --To make process execution to wait for 1st program to complete
      --
         l_req_return_status :=
            fnd_concurrent.wait_for_request (request_id      => lv_request_id
                                            ,interval        => 5 --interval Number of seconds to wait between checks
                                            ,max_wait        => 60 --Maximum number of seconds to wait for the request completion
                                             -- out arguments
                                            ,phase           => lc_phase
                                            ,status          => lc_status
                                            ,dev_phase       => lc_dev_phase
                                            ,dev_status      => lc_dev_status
                                            ,message         => lc_message
                                            );						
      EXIT
    WHEN UPPER (lc_phase) = 'COMPLETED' OR UPPER (lc_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');
    END LOOP;
    --
    --
    IF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'ERROR' THEN
      dbms_output.put_line('The XX_PROGRAM_1 completed in error. Oracle request id: '||lv_request_id ||' '||SQLERRM);
    ELSIF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'NORMAL' THEN
      dbms_output.put_line( 'The XX_PROGRAM_1 request successful for request id: ' || lv_request_id);
      --
      --Submitting Second Concurrent Program XX_PROGRAM_2
      --
	  BEGIN
	  --
		lv_request_id := fnd_request.submit_request ( 
                            application   => 'XXCUST', 
                            program       => 'XX_PROGRAM_2', 
                            description   => 'XX_PROGRAM_2', 
                            start_time    => sysdate, 
                            sub_request   => FALSE);	
		--
		COMMIT;
		--	
      EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line( 'OTHERS exception while submitting XX_PROGRAM_2: ' || SQLERRM);
      END;
    ELSE
      dbms_output.put_line( 'The XX_PROGRAM_1 request failed. Oracle request id: ' || lv_request_id ||' '||SQLERRM);
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line( 'OTHERS exception while submitting XX_PROGRAM_1: ' || sqlerrm);
END;