Wednesday 21 April 2021

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.