Monday, May 23, 2016

How to trigger a Request set from backend

How to submit / launch a concurrent request set from backend
submit concurrent request set from backend
I always had a doubt as how to call a concurrent request set from backend. I got this useful material while googling. So taught of sharing with you ppl. Hope you will enjoy.


When programmatically launching a request set, base it on the following skeleton code. Couple of points to note first:
  1. When a concurrent program has parameters, you must pass a value (or null) for each parameter that is on the Concurrent Program Definition - it is NOT the parameters that you see in the Request Set Definition as ones that are not displayed cannot be seen there, but pro-grammatically are required.
  2. Default values that are set up in the concurrent program definition, or the request set definition are not calculated for you - you must pass them in pro-grammatically.
  3. ALL stages of the request set must be pro grammatically dealt with - failure to do so will prevent the request set from running and you will not see ANY of it (the request set is effectively rolled back).
  4. The values of the parameters that you pass must correspond to the values seen in the Parameters field in the Requests Windows, when you manually launch the job.

APIs that are required to identify the Request set and set the over all context:
  
FND_SUBMIT.SET_MODE

      Syntax:

         function FND_SUBMIT.SET_MODE( db_trigger IN boolean)
         return boolean;


      Description:

         Call this function before calling FND_SUBMIT.SET_REQUEST_SET
         from a database trigger. Note that a failure in the database
         trigger call of FND_SUBMIT.SUBMIT_SET does not rollback changes.

      Arguments:

         db_trigger       Set to TRUE if request set is submitted from a
                          database trigger.


  FND_SUBMIT.SET_REL_CLASS_OPTIONS

      Syntax:    function FND_SUBMIT.SET_REL_CLASS_OPTIONS
                 (application     IN varchar2 default NULL,
                  class_name      IN varchar2 default NULL,
                  cancel_or_hold  IN varchar2 default 'H',
                  stale_date      IN varchar2 default NULL)
                 return boolean;

      Description:

         Call this function before calling FND_SUBMIT.SET_REQUEST_SET
         to use the advanced scheduling. If both set_rel_class_options
         and set_repeat_options were set then set_rel_class_options will
         take the percedence. Returns TRUE on succesful completion, and
         FALSE otherwise.


      Arguments:

         application      Short name of the application associated with
                          the   release class.
         class_name       Developer name of the release class.
         cancel_or_hold   cancel or hold flag.
         stale_date       Cancel this request on or after this time if
                          the request not run.

   
  FND_SUBMIT.SET_REPEAT_OPTIONS

      Syntax:  function FND_SUBMIT.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)
               return boolean;

      Description:

         Optionally call before submitting a concurrent request set to
         set repeat options. If both set_rel_class_options and
         set_repeat_options were set then set_rel_class_options will
         take the percedence.Returns TRUE on succesful
         completion, and FALSE otherwise.


      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
         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


  FND_SUBMIT.SET_REQUEST_SET *

      Syntax:   function FND_SUBMIT.SET_REQUEST_SET
                         (application                IN VARCHAR2,
                          request_set                IN VARCHAR2)
                return  boolean;

      Description:

         This function will set the request set context. Call this
         function at very beginning of the submission of a concurrent
         request set transaction. Call this function after calling the
         optional functions SET_MODE, SET_REL_CLASS_OPTIONS,
         SET_REPEAT_OPTIONS. It returns TRUE on sucessful completion,
         and FALSE otherwise.

      Arguments:

         request_set           The short name of the request set
                               (developer name of the request set)
         application           The short name of the application that owns
                               the request set.


APIs to set Request set programs and their attributes:


  FND_SUBMIT.SET_PRINT_OPTIONS

      Syntax:    function FND_SUBMIT.SET_PRINT_OPTIONS
                          (printer           IN varchar2  default NULL,
                           style             IN varchar2  default NULL,
                           copies            IN number    default NULL,
                           save_output       IN boolean   default TRUE,
                           print_together    IN varchar2  default 'N')
                 return boolean;

      Description:

         Called before submitting request if the printing of output
         has to be controlled with specific printer/style/copies etc.,
         Optionally call for each program in the request set. Returns
         TRUE on sucessful completion, and FALSE otherwise.


      Arguments:

         printer        - Printer name where the request o/p should be sent
         style          - Print style that needs to be used for printing
         copies         - Number of copies to print
         save_output    - Should the output file be saved after printing
                          Default is TRUE.TRUE/FALSE
         print_together - Applies only for sub requests. If 'Y',
                          output will not be printed until all the sub
                          requests complete. Default is 'N'. ( Y/N )
   

  FND_SUBMIT.ADD_PRINTER

      Syntax: function FND_SUBMIT.ADD_PRINTER
                       (printer IN varchar2 default null,
                        copies  IN number   default null)
              return boolean;

      Description:

         Called after set print options to add a printer to the print
         list.Optionally call for each program in the request set.
         Returns TRUE on sucessful completion, and FALSE otherwise


      Arguments:

         printer     -  Printer name where the request o/p should be sent
         copies      -  Number of copies to print


  FND_SUBMIT.ADD_NOTIFICATION

      Syntax: function FND_SUBMIT.ADD_NOTIFICATION (user IN varchar2)
              return boolean;

      Description:

         Called before submission to add a user to the notify list.
         Optionally call for each program in the reques set. Returns
         TRUE on sucessful completion, and FALSE otherwise.


      Arguments:

         User                  -            User name.
   

  FND_SUBMIT.SET_NLS_OPTIONS

      Syntax: function FND_SUBMIT.SET_NLS_OPTIONS
                       (language  IN varchar2 default NULL,
                        territory IN varchar2 default NULL)
              return boolean;

      Description:

         Called before submitting request to set request attributes.
         Optionally call for each program in the request set. Returns
         TRUE on sucessful completion, and FALSE otherwise.


      Arguments:

         implicit    - nature of the request to be submitted
                       NO/YES/ERROR/WARNING
         protected   - Is the request protected against updates YES/NO
                       Default is NO
         language    - NLS language
         territory   - Language territory
   

  FND_SUBMIT.SUBMIT_PROGRAM *

      Syntax:    function FND_SUBMIT.SUBMIT_PROGRAM
                          (application            IN varchar2,
                           program              IN varchar2,
                           stage                   IN varchar2,
                           argument1, ....argument100)
                 return boolean;

      Description:

         Call FND_SUBMIT.SET_REQUEST_SET function before calling this
         function to set the context for the report set submission.
         Before calling this function you may want to call the optional
         functions SET_PRINT_OPTIONS, ADD_PRINTER, ADD_NOTIFICATION,
         SET_NLS_OPTIONS. Call this (submit_program) function for each
         program (report) in the request set. You must call
         set_request_set before calling this function. You have to call
         set_request_set only once for all the submit_program
         calls for that request set.

         This function returns TRUE on successful completion, and FALSE
         otherwise.

      Arguments:

         application     Short name of the application associated with
                         the program with in a report set
         program         Name of the program with in the report set.
         stage           Name of the stage that the program belongs to
                         (developer name of the stage).
         argument1..100  Arguments for the program.


API to submit Request Set:


  FND_SUBMIT.SUBMIT_SET *

      Syntax:    function FND_SUBMIT.SUBMIT_SET
                          (start_time     IN varchar2 default null,
                           sub_request   IN boolean default FALSE)
                 return integer;

      Description:

         Call this function to submit the request set which is set by
         using the SET_REQUEST_SET.  If the Request set submission is
         successfully, this function returns the concurrent request ID;
         otherwise; it returns 0.


      Arguments:

         start_time   Time at which the request should start running,
                      formated as HH24:MI or HH24:MI:SS.
         sub_request  Set to TRUE if the request is submitted from
                      another request and should be treated as a sub-request.


         Call this function before calling FND_SUBMIT.SET_REQUEST_SET
         from a database trigger. Note that a failure in the database
         trigger call of FND_SUBMIT.SUBMIT_SET does not rollback changes.

      Arguments:

         db_trigger       Set to TRUE if request set is submitted from a
                          database trigger.


Examples for Request Set submission:

l_action := 'Launching Request Set';
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.set_request_set
(application => 'XX'
,request_set => 'XX_SAMPLE'
);
-- ------------------------------------
-- Stage 1 with 2 requests in the stage
-- -----------------------------------
IF l_ok AND l_success = 0 THEN
-- ----------------------------------------------------
-- SQL*Load the Ship To Addresses
-- ----------------------------------------------------
l_action := '1st job - 1st stage 1st request';
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
(application => 'XX'
,program => 'XX_CONC_PROG1'
,stage => 'RS_STAGE_10'
,argument1 => 'conc prog params here'
); 
ELSE
l_success := -100;
END IF;
IF l_ok AND l_success = 0 THEN
-- ----------------------------------------------------
-- SQL*Load the Invoices
-- ----------------------------------------------------
l_action := '2nd job - 1st stage 2nd request';
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
(application => 'XX'
,program => 'XX_CONC_PROG2'
,stage => 'RS_STAGE_10'
,argument1 => 'conc prog params here'
); 
ELSE
l_success := -110;
END IF;
-- --------------------------------------
-- New stage with 1 request
-- --------------------------------------
IF l_ok AND l_success = 0 THEN
l_action := '3rd job - 2nd stage 1st request';
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
(application => 'XX'
,program => 'XX_CONC_PROG3'
,stage => 'RS_STAGE_20'
,argument1 => 'conc prog params here'
); 
ELSE
l_success := -120;
END IF;
-- --------------------------------------
-- New stage with 1 request with LOTS of
-- parameters
-- --------------------------------------
IF l_ok AND l_success = 0 THEN
l_action := '4th job - 3rd stage 1st request';
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
(application => 'AR'
,program => 'RAXMTR'
,stage => 'INV_INTERIM_60'
,argument1 => '1'
,argument2 => TO_CHAR(l_batch_source_id)
,argument3 => 'MP KRYTON'
,argument4 => TO_CHAR(TRUNC((SYSDATE - 0.5)),'RRRR/MM/DD HH24:MI:SS')
,argument5 => NULL
,argument6 => NULL
,argument7 => NULL
,argument8 => NULL
,argument9 => NULL
,argument10 => NULL
,argument11 => NULL
,argument12 => NULL
,argument13 => NULL
,argument14 => NULL
,argument15 => NULL
,argument16 => NULL
,argument17 => NULL
,argument18 => NULL
,argument19 => NULL
,argument20 => NULL
,argument21 => NULL
,argument22 => NULL
,argument23 => NULL
,argument24 => NULL
,argument25 => 'Y'
,argument26 => NULL
,argument27 => fnd_profile.VALUE('ORG_ID')
); 
ELSE
l_success := -145;
END IF;
-- -----------------------------------------------
-- All requests in the set have been submitted now
-- -----------------------------------------------
IF l_ok AND l_success = 0 THEN
-- ----------------------------------------------------
-- Run the job and then wait until all requests
-- have completed processing - we have to wait because
-- when we exit here the file is moved to a different
-- directory.
-- ----------------------------------------------------
l_request_id := fnd_submit.submit_set(NULL,FALSE);
DBMS_OUTPUT.PUT_LINE('Request_id = '||l_request_id);
COMMIT;
l_complete := fnd_concurrent.wait_for_request 
(request_id => l_request_id
,INTERVAL => 2
,max_wait => 120
,phase => l_phase
,status => l_status
,dev_phase => l_dev_phase
,dev_status => l_dev_status
,message => l_message
);
ELSE
l_success := -150;
END IF;
IF l_success = 0 THEN
p_success := l_request_id; 
ELSE
DBMS_OUTPUT.PUT_LINE('Error: '||l_success||' - Problem with '||l_action);
p_success := l_success;
END IF;

==============

 XML report publisher concurrent program from backend.
XML report publisher
At times you might need to take the xml output of an existing program and apply an XML Publisher / BI Publisher Template to

it. The standard use case is if the output is generated by pro*c code/ a spawned or host concurrent program. The XML Report

Publisher concurrent program can help achieve this.

The Report takes the Concurrent request id, template application id, template name, template locale, template type and

output type as parameters.

A sample piece of code is shown below.

DECLARE
  l_req_id NUMBER;
BEGIN
  fnd_global.apps_initialize(6087,
                             20420,
                             1,
                             0);
  l_req_id := fnd_request.submit_request('XDO',
                                         'XDOREPPB',
                                         NULL,
                                         NULL,
                                         FALSE,
                                         FND_GLOBAL.CONC_REQUEST_ID,
                                         1919318,
                                         20003, -- Receivables
                                         'XXGILGMDWOPICKLIST', -- Statement Generate
                                         'en-US', -- English
                                         'N',
                                         'RTF',
                                         'PDF');
  dbms_output.put_line(l_req_id);
     commit;
END;



==================

 Submitting Concurrent Program from Back-end
We first need to initialize oracle applications session using:

fnd_global.apps_initialize(user_id,responsibility_id,application_responsibility_id)
and then run fnd_request.submit_request

If you are directly running from the database using the TOAD, SQL NAVIGATOR or SQL*PLUS etc. Then you need to initialize

the Apps. In this case use the above API to Initialize the APPS.

DECLARE
  l_request_id NUMBER(30);

BEGIN


 FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 59966, resp_appl_id => 20064);

  l_request_id:= FND_REQUEST.SUBMIT_REQUEST
('XXMZ' --Application Short name,
 'VENDOR_FORM'-- Concurrent Program Short Name );
                       
  DBMS_OUTPUT.PUT_LINE(l_request_id);
  commit;
END;

**************************************************************
If you are using same code in some procedure and running directly from application then you don't need to initialize. Then

you can comment the fnd_global.apps_initialize API.

DECLARE
l_success NUMBER;
BEGIN
BEGIN

fnd_global.apps_initialize( user_id => 2572694, resp_id => 50407, resp_appl_id => 20003);

l_success :=
fnd_request.submit_request
('XXAPP', -- Application Short name of the Concurrent Program.
'XXPRO_RPT', -- Program Short Name.
'Program For testing the backend Report', -- Description of the Program.
SYSDATE, -- Submitted date. Always give the SYSDATE.
FALSE, -- Always give the FLASE.
'1234' -- Passing the Value to the First Parameter of the report.
);
COMMIT;

-- Note:- In the above request Run, I have created the Report, which has one parameter.

IF l_success = 0
THEN
-- fnd_file.put_line (fnd_file.LOG, 'Request submission For this store FAILED' );
DBMS_OUTPUT.PUT_LINE( 'Request submission For this store FAILED' );
ELSE
-- fnd_file.put_line (fnd_file.LOG, 'Request submission for this store SUCCESSFUL');
DBMS_OUTPUT.PUT_LINE( 'Request submission For this store SUCCESSFUL' );
END IF;

END;

Note: If you are running directly from database, use DBMS API to display. If you are running directly from application,

then Use the fnd_file API to write the message in the log file.

==============

 How to submit a concurrent program from pl sql
How to submit a concurrent program from backend:
Using FND_REQUEST.SUBMIT_REQUEST function & by passing the required parameters to it we can submit a concurrent program

from backend.
But before doing so, we have to set the environment of the user submitting the request.

We have to initialize the following parameters using FND_GLOBAL.APPS_INITIALIZE procedure:
           ·                                 USER_ID
           ·                                 RESPONSIBILITY_ID
           ·                                 RESPONSIBILITY_APPLICATION_ID


Syntax:
FND_GLOBAL.APPS_INITIALIZE:
procedure APPS_INITIALIZE(user_id in number,
                                      resp_id in number,
                                      resp_appl_id in number);


FND_REQUEST.SUBMIT_REQUEST:
REQ_ID := FND_REQUEST.SUBMIT_REQUEST ( application => 'Application Name', program => 'Program Name', description => NULL,

start_time => NULL, sub_request => FALSE, argument1 => 1 argument2 => ....argument n );

Where, REQ_ID is the concurrent request ID upon successful completion.
And concurrent request ID returns 0 for any submission problems.

Example:
First get the USER_ID and RESPONSIBILITY_ID by which we have to submit the program:

SELECT USER_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_APPLICATION_ID,
SECURITY_GROUP_ID
FROM FND_USER_RESP_GROUPS
WHERE USER_ID = (SELECT USER_ID
                             FROM FND_USER
                             WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID = (SELECT RESPONSIBILITY_ID
                                      FROM FND_RESPONSIBILITY_VL
                                      WHERE RESPONSIBILITY_NAME = '&resp_name');


Now create this procedure
CREATE OR REPLACE PROCEDURE APPS.CALL_RACUST (p_return_code OUT NUMBER,
                                                                         p_org_id NUMBER, -- This is required in R12
                                                                         p_return_msg OUT VARCHAR2)
IS
v_request_id VARCHAR2(100) ;
p_create_reciprocal_flag varchar2(1) := 'N'; -- This is value of create reciprocal customer
                                                  -- Accounts parameter, defaulted to N
BEGIN


-- First set the environment of the user submitting the request by submitting
-- Fnd_global.apps_initialize().
-- The procedure requires three parameters
-- Fnd_Global.apps_initialize(userId, responsibilityId, applicationId)
-- Replace the following code with correct value as get from sql above

Fnd_Global.apps_initialize(10081, 5559, 220);

v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST('AR','RACUST','',
                                                       '',FALSE,p_create_reciprocal_flag,p_org_id,
                                                        chr(0) -- End of parameters);

p_return_msg := 'Request submitted. ID = ' || v_request_id;
p_return_code := 0; commit ;


EXCEPTION

when others then
          p_return_msg := 'Request set submission failed - unknown error: ' || sqlerrm;
          p_return_code := 2;
END;


Output:
DECLARE
V_RETURN_CODE NUMBER;
V_RETURN_MSG VARCHAR2(200);
V_ORG_ID NUMBER := 204;
BEGIN
CALL_RACUST(
P_RETURN_CODE => V_RETURN_CODE,
P_ORG_Id => V_ORG_ID,
P_RETURN_MSG => V_RETURN_MSG
);
DBMS_OUTPUT.PUT_LINE('V_RETURN_CODE = ' || V_RETURN_CODE);
DBMS_OUTPUT.PUT_LINE('V_RETURN_MSG = ' || V_RETURN_MSG);
END;


If Return Code is 0(zero) then it has submitted the Customer Interface program successfully and the request id will appear

in Return Message as :
V_RETURN_CODE = 0
V_RETURN_MSG = Request submitted. ID = 455789


How to submit xml reports concurrent Request set from backend?
As per my knowledge when publishing this post xml templates are not attaching when a xml report is triggered via a request set from back end, even though add_layout is added before call to the xml publisher report .

As per Oracle meta link id # Doc ID 756452.1 patch needs to be applied to fix this . 
and works fine for the below version , but how far this will work i am not sure This is under investigation , will keep you posted once i get the info .

  • Oracle E-Business Suite 11i:
    • FNDRSRUN.fmb 115.172
  • Oracle E-Business Suite Release 12.0:
    • FNDRSRUN.fmb 120.29.12000000.6





Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)