submit concurrent 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 programmatically 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 programmatically.
3. ALL stages of the request set must be programmatically 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.

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