Oracle Apps: How to Schedule Concurrent Program to run at a specific time from back-end

In my previous posts I have explained about how can we make the parent concurrent program wait for all the child programs to complete and how to submit a concurrent program from the backend periodically etc.... In this one I thought of giving an example of how to submit a concurrent program to run at a specific time.

Inorder to do that we just need to specify the 'start_date' parameter in the procedure fnd_request.submit_request. But the thing which we need to be careful is, this parameter is of type VARCHAR2. So if don't pass a date variable to this value, it will truncate the date and the time component will be removed. It will result in the program starting straight away if the date is on the same day or exactly 12:00 AM of the date (if the date is a future date). When you pass the parameter pass the date and time in the format 'DD-Mon-YYYY HH24:MI:SS'

A Sample can be like below. I have just added 3 hours to current date and converted into varchar using TO_CHAR function. You can also specify the time directly like '29-Jun-2021 10:00:00'

DECLARE
 
   v_request_id        NUMBER;
   v_status            BOOLEAN;
 
BEGIN
   --Initialize the session with appropirate values
   fnd_global.apps_initialize (user_id=>100
                              ,resp_id=>100
                              ,resp_appl_id=>100);
 
   --Submit the Request
   v_request_id := fnd_request.submit_request ( application => 'XXAJ'
                                              , program     => 'XXAJ_PROGRAM'
                                              , start_time  => TO_CHAR(SYSDATE + 3/24,'DD-Mon-YYYY HH24:MI:SS')
                                              , sub_request => FALSE);
   COMMIT;
 
   IF v_request_id = 0 THEN
      DBMS_OUTPUT.put_line('Request not submitted: '|| fnd_message.get);
   ELSE
      DBMS_OUTPUT.put_line('Request submitted successfully. Request id: ' || v_request_id);
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Exception: ' || SQLERRM);   
END;
 

Reference: 

Oracle Apps: How to set Concurrent Program Parameter Disabled / Readonly

Oracle Apps: How to submit a Concurrent Program from back-end and wait for that to complete

Oracle Apps: How make the Parent Concurrent Program to wait till all the Child Concurrent Program Completes

Feel free to point out if anything is wrong/missing in this blog.

Oracle Apps: How to Schedule Concurrent Program to run periodically from back-end

In my previous posts I have explained about how can we make the parent concurrent program wait for all the child programs to complete and how to submit a concurrent program from the backend. In this one I thought of giving an example of how to submit a concurrent program and schedule that to run periodically.

For this you will have to use the function set_repeat_options in the package fnd_request.

  
  --
  -- Name
  --   set_repeat_options
  -- Purpose
  --   Called before submitting request if the request to be submitted
  --   is a repeating request.
  --
  -- Arguments
  --   repeat_time      - Time of day at which it has to be repeated
  --   repeat_interval  - Frequency at which it has to be repeated
  --                    - This will be used/applied only when repeat_time
  --                    - is NULL ( non null repeat_interval overrides )
  --   repeat_unit      - Unit for repeat interval. Default is DAYS.
  --                    - MONTHS/DAYS/HOURS/MINUTES
  --   repeat_type      - Apply repeat interval from START or END of request
  --                    - default is START. START/END
  --   repeat_end_time  - Time at which the repetition should be stopped
  --   incrment_dates   - 'Y' if dates should be incremented each run,
  --                      otherwise 'N'
  --
  function set_repeat_options (repeat_time      IN varchar2 default NULL,
                               repeat_interval  IN number   default NULL,
                               repeat_unit      IN varchar2 default 'DAYS',
                               repeat_type      IN varchar2 default 'START',
                               repeat_end_time  IN varchar2 default NULL,
                               increment_dates  IN varchar2 default NULL)
                               return boolean is

Below is an example to schedule a job to run every 5 minutes.

DECLARE
 
   v_request_id        NUMBER;
   v_status            BOOLEAN;
 
BEGIN
   --Initialize the session with appropirate values
   fnd_global.apps_initialize (user_id=>100
                              ,resp_id=>100
                              ,resp_appl_id=>100);
 
   --Set the Repeat Options
   v_status := fnd_request.set_repeat_options ( repeat_interval => 5
                                              , repeat_unit     => 'MINUTES'
                                              , repeat_type     => 'START');
   --Submit the Request
   v_request_id := fnd_request.submit_request ( application => 'XXAJ'
                                              , program     => 'XXAJ_CHILD'
                                              , start_time  => SYSDATE
                                              , sub_request => FALSE);
   COMMIT;
 
   IF v_request_id = 0 THEN
      DBMS_OUTPUT.put_line('Request not submitted: '|| fnd_message.get);
   ELSE
      DBMS_OUTPUT.put_line('Request submitted successfully. Request id: ' || v_request_id);
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Exception: ' || SQLERRM);   
END;
 

Feel free to point out if anything is wrong/missing in this blog.

Oracle Apps: How to submit a Concurrent Program from back-end and wait for that to complete

In my previous post I have explained about how can we make the parent concurrent program wait for all the child programs to complete. In this one I thought of giving an example of how to submit a concurrent program from the back-end and wait for the completion of that.

DECLARE
 
   v_request_id        NUMBER;
   v_req_return_status BOOLEAN;
   v_phase             VARCHAR2(100);
   v_status            VARCHAR2(100);
   v_dev_phase         VARCHAR2(100);
   v_dev_status        VARCHAR2(100);
   v_message           VARCHAR2(100);
 
BEGIN
   --Initialize the session with appropirate values
   fnd_global.apps_initialize (user_id=>100
                              ,resp_id=>100
                              ,resp_appl_id=>100);
 
   --Submit the Request
   v_request_id := fnd_request.submit_request ( application => 'XXAJ'
                                              , program     => 'XXAJ_CHILD'
                                              , start_time  => SYSDATE
                                              , sub_request => FALSE);
   COMMIT;
 
   IF v_request_id = 0 THEN
      DBMS_OUTPUT.put_line('Request not submitted: '|| fnd_message.get);
   ELSE
      DBMS_OUTPUT.put_line('Request submitted successfully. Request id: ' || v_request_id);
 
      v_req_return_status := fnd_concurrent.wait_for_request 
                                            (request_id      => v_request_id
                                            ,INTERVAL        => 2 --interval Number of seconds to wait between checks
                                            -- ,max_wait     => 5 --Maximum number of seconds to wait for the request completion
                                             -- out arguments
                                            ,phase           => v_phase
                                            ,status          => v_status
                                            ,dev_phase       => v_dev_phase
                                            ,dev_status      => v_dev_status
                                            ,message         => v_message
                                            );   
      DBMS_OUTPUT.put_line('Request status: ' || v_status); 
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Exception: ' || SQLERRM);   
END;
 

Note:- If you want to wait only for x seconds, specify the value for the parameter max_wait.

Feel free to point out if anything is wrong/missing in this blog.