How to submit / launch a concurrent request set from backend
When programmatically
launching a request set, base it on the following skeleton code. Couple of
points to note first:
- 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.
- 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.
- 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).
- 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:
Syntax:
function FND_SUBMIT.SET_MODE(
db_trigger IN boolean)
Description:
Call this function before calling
FND_SUBMIT.SET_REQUEST_SET
Arguments:
db_trigger Set to TRUE if request set is submitted
from a
FND_SUBMIT.SET_REL_CLASS_OPTIONS
Syntax:
function FND_SUBMIT.SET_REL_CLASS_OPTIONS
Description:
Call this function before calling
FND_SUBMIT.SET_REQUEST_SET
Arguments:
application Short name of the application associated
with
Syntax:
function FND_SUBMIT.SET_REPEAT_OPTIONS
Description:
Optionally call before submitting a
concurrent request set to
Arguments:
repeat_time - Time of day at which it has to be
repeated
FND_SUBMIT.SET_REQUEST_SET *
Syntax:
function FND_SUBMIT.SET_REQUEST_SET
Description:
This function will set the request set
context. Call this
Arguments:
request_set The short name of the request set
APIs to set Request
set programs and their attributes:
FND_SUBMIT.SET_PRINT_OPTIONS
Syntax:
function FND_SUBMIT.SET_PRINT_OPTIONS
Description:
Called before submitting request if
the printing of output
Arguments:
printer - Printer name where the request o/p
should be sent
FND_SUBMIT.ADD_PRINTER
Syntax: function FND_SUBMIT.ADD_PRINTER
Description:
Called after set print options to add
a printer to the print
Arguments:
printer -
Printer name where the request o/p should be sent
FND_SUBMIT.ADD_NOTIFICATION
Syntax: function
FND_SUBMIT.ADD_NOTIFICATION (user IN varchar2)
Description:
Called before submission to add a user
to the notify list.
Arguments:
User - User name.
FND_SUBMIT.SET_NLS_OPTIONS
Syntax: function
FND_SUBMIT.SET_NLS_OPTIONS
Description:
Called before submitting request to
set request attributes.
Arguments:
implicit - nature of the request to be submitted
FND_SUBMIT.SUBMIT_PROGRAM *
Syntax:
function FND_SUBMIT.SUBMIT_PROGRAM
Description:
Call FND_SUBMIT.SET_REQUEST_SET
function before calling this
This function returns TRUE on
successful completion, and FALSE
Arguments:
application Short name of the application associated
with
API to submit Request
Set:
FND_SUBMIT.SUBMIT_SET *
Syntax: function FND_SUBMIT.SUBMIT_SET
Description:
Call this function to submit the
request set which is set by
Arguments:
start_time Time at which the request should start
running,
Call this function before calling
FND_SUBMIT.SET_REQUEST_SET
Arguments:
db_trigger Set to TRUE if request set is submitted
from a
Examples for Request Set
submission:
l_action := 'Launching
Request Set';
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.set_request_set
,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
-- ----------------------------------------------------
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,program => 'XX_CONC_PROG1'
,stage => 'RS_STAGE_10'
,argument1 => 'conc prog params here'
);
IF l_ok AND l_success = 0 THEN
-- SQL*Load the Invoices
-- ----------------------------------------------------
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,program => 'XX_CONC_PROG2'
,stage => 'RS_STAGE_10'
,argument1 => 'conc prog params here'
);
-- --------------------------------------
-- New stage with 1 request
-- --------------------------------------
IF l_ok AND l_success = 0 THEN
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,program => 'XX_CONC_PROG3'
,stage => 'RS_STAGE_20'
,argument1 => 'conc prog params here'
);
-- --------------------------------------
-- New stage with 1 request with LOTS of
-- parameters
-- --------------------------------------
IF l_ok AND l_success = 0 THEN
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,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')
);
-- -----------------------------------------------
-- 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.
-- ----------------------------------------------------
DBMS_OUTPUT.PUT_LINE('Request_id = '||l_request_id);
COMMIT;
l_complete := fnd_concurrent.wait_for_request
,INTERVAL => 2
,max_wait => 120
,phase => l_phase
,status => l_status
,dev_phase => l_dev_phase
,dev_status => l_dev_status
,message => l_message
);
IF l_success = 0 THEN
p_success := l_success;
==============
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 Oracle meta
link id # Doc ID
756452.1 patch
needs to be applied to fix this .
- Oracle E-Business Suite 11i:
- FNDRSRUN.fmb 115.172
- Oracle E-Business Suite Release
12.0:
- FNDRSRUN.fmb 120.29.12000000.6
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.set_request_set
,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
-- ----------------------------------------------------
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,program => 'XX_CONC_PROG1'
,stage => 'RS_STAGE_10'
,argument1 => 'conc prog params here'
);
IF l_ok AND l_success = 0 THEN
-- SQL*Load the Invoices
-- ----------------------------------------------------
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,program => 'XX_CONC_PROG2'
,stage => 'RS_STAGE_10'
,argument1 => 'conc prog params here'
);
-- --------------------------------------
-- New stage with 1 request
-- --------------------------------------
IF l_ok AND l_success = 0 THEN
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,program => 'XX_CONC_PROG3'
,stage => 'RS_STAGE_20'
,argument1 => 'conc prog params here'
);
-- --------------------------------------
-- New stage with 1 request with LOTS of
-- parameters
-- --------------------------------------
IF l_ok AND l_success = 0 THEN
DBMS_OUTPUT.PUT_LINE(l_action);
l_ok := fnd_submit.submit_program
,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')
);
-- -----------------------------------------------
-- 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.
-- ----------------------------------------------------
DBMS_OUTPUT.PUT_LINE('Request_id = '||l_request_id);
COMMIT;
l_complete := fnd_concurrent.wait_for_request
,INTERVAL => 2
,max_wait => 120
,phase => l_phase
,status => l_status
,dev_phase => l_dev_phase
,dev_status => l_dev_status
,message => l_message
);
IF l_success = 0 THEN
p_success := l_success;
==============
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
- FNDRSRUN.fmb 115.172
- FNDRSRUN.fmb 120.29.12000000.6
