How to Submit concurrent Program from PL/SQL - Oracle

We can use FND_REQUEST.SUBMIT_REQUEST to submit the concurrent program from backend. The following code will submit the oracle seeded program ‘Reserve Orders’.
Submitting Concurrent Program From PL SQL
Declare
   v_user_id                                 NUMBER;
   v_app_id                                  NUMBER;
   v_resp_id                                 NUMBER;
   v_resp_name                           VARCHAR2 (100);
   v_request_id                             NUMBER;
   v_app_name                            VARCHAR2 (20);
   v_req_phase                             VARCHAR2 (100);
   v_req_status                             VARCHAR2 (100);
   v_req_dev_phase                     VARCHAR2 (100);
   v_req_dev_status                     VARCHAR2 (100);
   v_req_message                        VARCHAR2 (100);
   v_req_return_status                 BOOLEAN;
   use_reservation_time_fence   VARCHAR2 (10);
   resertvaton_run_types            VARCHAR2 (100);
   item                                         VARCHAR2 (100);
   warehouse                               VARCHAR2 (100);
   booked                                    VARCHAR2 (100);
   reservation_mode                    VARCHAR2 (500);

   order_by                                  VARCHAR2 (100);

BEGIN

/*First we need to initialize the apps, this will automatically done when we submit the program from application directly*/

   BEGIN
      SELECT user_id
        INTO v_user_id
        FROM fnd_user
       WHERE user_name = 'USER NAME';
   EXCEPTION
      WHEN OTHERS
      THEN
         v_user_id := NULL;
   END;

   BEGIN
      SELECT fa.application_id, fr.responsibility_id,
             fr.responsibility_name, fa.application_short_name
        INTO v_app_id, v_resp_id,
             v_resp_name, v_app_name
        FROM fnd_responsibility_vl fr, fnd_application fa
       WHERE responsibility_name LIKE 'Order Management Super User'
         AND fr.application_id = fa.application_id;

     
   EXCEPTION
      WHEN OTHERS
      THEN
      v_app_id:=null;
      v_resp_id:=null;
      v_resp_name:=null;
      v_app_name:=null;
   END;

-- Initializing apps
   fnd_global.apps_initialize (user_id           => v_user_id,
                               resp_id           => v_resp_id,
                               resp_appl_id      => v_app_id
                              );

/*After  Initializing we can straightaway submit the concurrent program */
-- Submitting Request

   use_reservation_time_fence : = 'Y';
   resertvaton_run_types :          = 'RESERVE';
   booked :                                  = 'Y';
   reservation_mode :                 = 'PARTIAL';
   order_by :                               = 'ORDERED_DATE';
   item :                                      = “Inventory_item_id”;
   warehouse :                            = “Organization_id”;

/*These are the parameters of the reserve orders program which will passed as arguments through fnd_request.submit_request function*/

   v_request_id :=
      fnd_request.submit_request (v_app_name,                       /* Short name of the application under which the program is registered in this it will be ONT since the Reserve Order program is registered under order management*/
                    'OMRSVORD',                -- Short name of the concurrent program needs to submitted
                                  NULL,               -- concurrent program description OPTIONAL parameter
                                  SYSDATE,        /* Start time parameter which can be used to mention the actual start of the concurrent program which once again OPTIONAL*/
                                  FALSE,              /* Sub request parameters if this program is need to be submitted from another running request then TRUE needs to passed, but default is FALSE and an OPTIONAL parameter*/
                                  argument1        => use_reservation_time_fence,
                                  argument2        => '',
                                  argument3        => '',
                                  argument4        => '',
                                  argument5        => '',
                                  argument6        => '',
                                  argument7        => warehouse,
                                  argument8        => item,
                                  argument9        => '',
                                  argument10      => '',
                                  argument11      => '',
                                  argument12      => '',
                                  argument13      => '',
                                  argument14      => '',
                                  argument15      => '',
                                  argument16      => '',
                                  argument17      => '',
                                  argument18      => '',
                                  argument19      => booked,
                                  argument20      => reservation_mode,
                                  argument21      => '',
                                  argument22      => '',
                                  argument23      => '',
                                  argument24      => '',
                                  argument25      => resertvaton_run_types,
                                  argument26      => '',
                                  argument27      => '',
                                  argument28      => order_by,
                                  argument29      => '',
                                  argument30      => '',
                                  argument31      => ''
                                 );

  • 100 arguments are available through which additional parameters can be passed to the concurrent program. 
  • In case of Reserve Order concurrent program it is mandatory to pass 31 arguments to it and the order of the parameters passing here must match the order defined in the concurrent program.
  • Passing unequal number (or) unmatched additional parameters will complete the concurrent program with ERROR Status.

   COMMIT;

Only after the explicit commit the submitted requested will be available in the fnd_concurrent_request table and in the Oracle apps.
  
   DBMS_OUTPUT.put_line ('Concurrent Program Req ID' || v_request_id);

/* fnd_concurrent.wait_for_request  function can be used to get the result of the submitted concurrent program.
This is just like we are refreshing the concurrent program in Application until we get its phase as completed */

-- Concurrent Program Status
   IF v_request_id <> 0 AND v_request_id IS NOT NULL
   THEN
      LOOP
         v_req_return_status :
  fnd_concurrent.wait_for_request (v_request_id,                   -- request ID for which results need to be known
                                             60,                      --interval parameter  time between checks default is 60 Seconds
                                             0,                        -- Max wait default is 0 , this is the maximum amount of time a program should wait for it to get completed      
                                             v_req_phase,
                                             v_req_status,
                                             v_req_dev_phase,
                                             v_req_dev_status,
                                             v_req_message
                                            );
/*Others are output parameters to get the phase and status of the submitted concurrent program*/
         EXIT WHEN UPPER (v_req_phase) = 'COMPLETED'
               OR UPPER (v_req_status) IN
                                         ('CANCELLED', 'ERROR', 'TERMINATED');
      END LOOP;

      DBMS_OUTPUT.put_line ('Concurrent Program Status' || v_req_status);

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'Main Exception'
                            || SQLERRM
                            || DBMS_UTILITY.format_error_backtrace
                           );
End;

If you have any queries related to this, do comment below we are ready to rectify your queries in ease..
Tags: Oracle apps, FND_REQUEST.SUBMIT_REQUEST, fnd_concurrent.wait_for_request, plsql for submitting concurrent program, fnd packages

No comments :

Post a Comment

Related Posts Plugin for WordPress, Blogger...
 

Do not Copy this Web Page Content

Copyrighted.com Registered & Protected  0K43-TWEU-OGOE-SNU2 DMCA.com Protection Status
The content displayed on this website are copyright protected, reproducing the article in any kind is strictly prohibited.
Copyright © . How to Comp - Instant Solution for Computer and Mobile Problems | All Rights Reserved.
Designed By Way2themes | True Tech Tips | Teentack