Tuesday, September 11, 2007

Spawn concurrent request from pl/sql

When developing pl/sql code for oracle applications you would like to start concurrent requests directly from a pl/sql package. To do so Oracle has provided the option to use fnd_request.submit_request. Fnd_requests is a package in the apps schema.


Please note the example below where we use fnd_request.submit_request to start the concurrent request OEOIMP.

v_request_id := fnd_request.submit_request('ONT','OEOIMP','Order Import','' ,FALSE,'','','','N','1','4','','','','Y','N',CHR(0));
COMMIT;

IF v_request_id > 0
THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DEBUG, Successfully submitted');
ELSE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DEBUG, Not Submitted');
END IF;

The parameters needed to start submit_request are the following:

- Application (varchar2) (shortname of the application)
- Program (varchar2) (shortname of the concurrent request)
- Description (varchar2) (description of the concurrent request)
- Start_time (varchar2) (time to start, if null then immediate)
- Sub_request (Boolean) (is this a sub request true/false)

And you have argument1 until argument100. You could enter all the 100 arguments even if they are null or you could use after the last argument you need the following: ,CHR(0) By using this there will only by used the arguments you need and you do not have to fill up the number of arguments with null values.

To find out all the short descriptions of all the concurrent requests you can use this query:

SELECT
conpro.CONCURRENT_PROGRAM_ID
,conpro.CONCURRENT_PROGRAM_NAME
,conpro.DESCRIPTION
,conpro.APPLICATION_ID
FROM
FND_CONCURRENT_PROGRAMS_VL conpro


To find all the short names of the applications on your system you can use this query:
SELECT
appview.APPLICATION_ID
,appview.APPLICATION_SHORT_NAME
,appview.APPLICATION_NAME
,appview.DESCRIPTION
FROM
fnd_application_all_view appview




No comments: