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:
- 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:
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:
- Oracle E-Business Suite Release
12.0:
- FNDRSRUN.fmb 120.29.12000000.6
