Concurrent Processing APIs for PL/SQL Procedures

This POSTdescribes concurrent processing APIs you can use in both client–side and server–side PL/SQL procedures. This section also includes example PL/SQL procedure code using these concurrent processing APIs.
The following packages are described:
FND_CONC_GLOBAL.REQUEST_DATA: Sub–request Submission
FND_CONCURRENT: Information on Submitted Requests
FND_FILE: PL/SQL: File I/O
FND_PROGRAM: Concurrent Program Loaders
FND_SET: Request Set Creation
FND_REQUEST: Concurrent Program Submission
FND_REQUEST_INFO: Request Information
FND_SUBMIT: Request Set Submission

FND_CONC_GLOBAL Package
This package is used for submitting sub–requests from PL/SQL concurrent programs.
FND_CONC_GLOBAL.REQUEST_DATA
Summary  :-function FND_CONC_GLOBAL.REQUEST_DATA return varchar2;
Description :- FND_CONC_GLOBAL.REQUEST_DATA retrieves the value of the REQUEST_DATA global.
FND_CONC_GLOBAL.SET_REQ_GLOBALS
Summary   :- function FND_CONC_GLOBAL.SET_REQ_GLOBALS();
Description :-  FND_CONC_GLOBAL.REQUEST_DATA retrieves the value of the REQUEST_DATA global.
Example
/*
* This is sample PL/SQL concurrent program submits 10 * sub–requests. The sub–requests are submitted one at a
* time. Each time a sub–request is submitted, the parent * exits to the Running/Paused state, so that it does not
* consume any resources while waiting for the child * request, to complete. When the child completes the
* parent is restarted.
*/
create or replace procedure parent (errbuf out varchar2, retcode out number) is
i number;
req_data varchar2(10);
r number;
begin
––
–– Read the value from REQUEST_DATA. If this is the

–– first run of the program, then this value will be
–– null.
–– Otherwise, this will be the value that we passed to
–– SET_REQ_GLOBALS on the previous run.
––
req_data := fnd_conc_global.request_data;
––
–– If this is the first run, we’ll set i = 1.
–– Otherwise, we’ll set i = request_data + 1, and we’ll
–– exit if we’re done.
––
if (req_data is not null) then
i := to_number(req_data);
i := i + 1;
if (i < 11 ) then
errbuf := ’Done!’;
retcode := 0 ;
return;
end if;
else
i := 1;
end if;
––
–– Submit the child request. The sub_request parameter
–– must be set to ’Y’.
––
r := fnd_request.submit_request(’FND’, ’CHILD’,
’Child ’ || to_char(i), NULL,
TRUE, fnd_conc_global.printer);
if r = 0 then
––
–– If request submission failed, exit with error.
––
errbuf := fnd_message.get;
retcode := 2;
else
––
–– Here we set the globals to put the program into the
–– PAUSED status on exit, and to save the state in
–– request_data.
––
fnd_conc_global.set_req_globals(conc_status => ’PAUSED’,
request_data => to_char(i));
errbuf := ’Sub–Request submitted!’;
retcode := 0 ;
end if;
return;
end;

FND_CONCURRENT Package

FND_CONCURRENT.AF_COMMIT
Summary  :- function FND_CONCURRENT.AF_COMMIT;
Description :-  FND_CONCURRENT.AF_COMMIT is used by concurrent programs that use a particular rollback segment. This rollback segment must be  defined in the Define Concurrent Program form.
FND_CONCURRENT.AF_COMMIT executes the COMMIT command for the specified rollback segment. FND_CONCURRENT.AF_COMMIT has no arguments.


FND_CONCURRENT.AF_ROLLBACK
Summary :-  function FND_CONCURRENT.AF_ROLLBACK;
Description:-  FND_CONCURRENT.AF_ROLLBACK is used by concurrent programs that use a particular rollback segment. This rollback segment must be defined in the Define Concurrent Program form. FND_CONCURRENT.AF_ROLLBACK executes the ROLLBACK command for the specified rollback segment.
FND_CONCURRENT.AF_ROLLBACK has no arguments.



FND_CONCURRENT.GET_REQUEST_STATUS (Client or Server)
Summary:-  function FND_CONCURRENT.GET_REQUEST_STATUS
(request_id IN OUT number,
application IN varchar2 default NULL,
program IN varchar2 default NULL,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2) return boolean;

Description :- Returns the status of a concurrent request. If the request has already completed, also returns a completion message.
FND_CONCURRENT.GET_REQUEST_STATUS returns both
“user–friendly” (i.e., translatable) phase and status values, as well as “developer” phase and status values that can drive program logic.
Arguments (input)  request_id  The request ID of the program to be checked.
Application :- Short name of the application associated with the concurrent program. This parameter is necessary
only when the request_id is not specified.
Program :- Name of the concurrent program. This parameter is necessary only when the request_id is not
specified. When application and program are provided, the request ID of the last request for this
program is returned in request_id.

Arguments (output)
Phase :- The user–friendly request phase from
FND_LOOKUPS.
Status The user–friendly request status from
FND_LOOKUPS.
dev_phase  The request phase as a constant string that can be used for program logic comparisons.
dev_status The request status as a constant string that can be used for program logic comparisons.
Message  The completion message supplied if the request has completed.
Example
begin
call_status boolean;
rphase varchar2(80);
rstatus varchar2(80);
dphase varchar2(30);
dstatus varchar2(30);
message varchar2(240);
call_status :=
FND_CONCURRENT.GET_REQUEST_STATUS(<Request_ID>, ’’, ’’,
rphase,rstatus,dphase,dstatus, message);
end;
In the above example, rphase and rstatus receive the same phase and
status values as are displayed on the Concurrent Requests form. The
completion text of a completed request returns in a message.
Any developer who wishes to control the flow of a program based on a
request’s outcome should use the following values to compare the
request’s phase and status.
Possible values for dev_phase and dev_status are:



_
FND_FILE: PL/SQL File I/O
The FND_FILE package contains procedures to write text to log and output files. In Release 11i, these procedures are supported in all types of concurrent programs. For testing and debugging, you can use the procedures
FND_FILE.PUT_NAMES and FND_FILE.CLOSE. Note that these two procedures should not be called from a concurrent program.
FND_FILE supports a maximum buffer line size of 32K for both log and output files.
Attention: This package is not designed for generic PL/SQL text I/O, but rather only for writing to request log and output files.

FND_FILE.PUT
Summary ;- procedure FND_FILE.PUT
(which IN NUMBER,
buff IN VARCHAR2);
Description  :- Use this procedure to write text to a file (without a new line character).
Multiple calls to FND_FILE.PUT will produce concatenated text.
Typically used with FND_FILE.NEW_LINE.
Arguments (input)
which :  Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
buff  : Text to write.

FND_FILE.PUT_LINE
Summary : - procedure FND_FILE.PUT_LINE
(which IN NUMBER,
buff IN VARCHAR2);
Description :- Use this procedure to write a line of text to a file (followed by a new
line character). You will use this utility most often.


Arguments (input)
Which  Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
Buff  Text to write.
Example
Using Message Dictionary to retrieve a message already set up on the server and putting it in the log file (allows the log file to contain a translated message):
FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
Putting a line of text in the log file directly (message cannot be translated because it is hardcoded in English; not recommended):
fnd_file.put_line(FND_FILE.LOG,’Warning: Employee ’||
l_log_employee_name||’ (’||
l_log_employee_num ||
’) does not have a manager.’);



FND_REQUEST Package

FND_REQUEST.SUBMIT_REQUEST (Client or Server)
Summary : function FND_REQUEST.SUBMIT_REQUEST
(application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE
argument1,
argument2, ..., argument99,
argument100) return number;
Description : - _
Submits a concurrent request for processing by a concurrent manager. If the request completes successfully, this function returns the concurrent request ID; otherwise, it returns 0.
Attention: FND_REQUEST must know information about the user and responsibility from which the request is submitted. Therefore, this function only works from concurrent programs or forms within Oracle Applications. The FND_REQUEST.SUBMIT_REQUEST function returns the concurrent request ID upon successful completion. It is then up to the caller to issue a commit to complete the request submission. Your code should retrieve and handle the error message generated if there is a submission problem (the concurrent request ID returned is 0). Use FND_MESSAGE.RETRIEVE and FND_MESSAGE.ERROR to retrieve and display the error (if the request is submitted from the client side).
You must call FND_REQUEST.SET_MODE before calling FND_REQUEST.SUBMIT_REQUEST from a database trigger. If FND_REQUEST.SUBMIT_REQUEST fails from anywhere but a database trigger, database changes are rolled back up to the point of the function call. After a call to the FND_REQUEST.SUBMIT_REQUEST function, all
setup parameters are reset to their default values.
Arguments (input)  application  Short name of the application associated with the
concurrent request to be submitted.
Program :-  Name of the concurrent program for which the
request should be submitted.
description  :- Description of the request that is displayed in the Concurrent Requests form (Optional.)
start_time  Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.)
sub_request  Set to TRUE if the request is submitted from another request and should be treated as a sub–request.
Starting with Release 11, this parameter can be used if you are submitting requests from within a PL/SQL stored procedure concurrent program.
argument1...100 Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle
Forms, you must specify all 100 arguments.

Example Request Submissions
Example 1     /* Submit a request from a form and commit*/
:parameter.req_id :=
FND_REQUEST.SUBMIT_REQUEST (
:blockname.appsname,
:blockname.program,
:blockname.description,
:blockname.start_time,
:blockname.sub_req = ’Y’,
123, NAME_IN(’ORDERS.ORDER_ID’), ’abc’,
chr(0), ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’,
’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’, ’’);
IF :parameter.req_id = 0 THEN
FND_MESSAGE.RETRIEVE;
FND_MESSAGE.ERROR;
ELSE
IF :SYSTEM.FORM_STATUS != ’CHANGED’ THEN
IF app_form.quietcommit THEN
/*form commits without asking user to save changes*/
fnd_message.set_name(’SQLGL’,
’GL_REQUEST_SUBMITTED’);
fnd_message.set_TOKEN(’REQUEST_ID’,
TO_CHAR(:PARAMETER.REQ_ID), FALSE);
fnd_message.show;
ELSE
fnd_message.set_name(’FND’,
’CONC–REQUEST SUBMISSION FAILED’);
fnd_message.error;
END IF;
ELSE
DO_KEY(’COMMIT_FORM’);
IF :SYSTEM.FORM_STATUS != ’CHANGED’ THEN
/*commit was successful*/
fnd_message.set_name(’SQLGL’,
’GL_REQUEST_SUBMITTED’);
fnd_message.set_TOKEN(’REQUEST_ID’,
TO_CHAR(:PARAMETER.REQ_ID), FALSE);
fnd_message.show;
END IF;
END IF;
END IF;
Example 2
/* Submit a request where no setup is required */
declare
req_id number;
begin
req_id := FND_REQUEST.SUBMIT_REQUEST (’FND’,
’FNDMDGEN’, ’Message File Generator’,
’01–NOV–02 00:00:00’, FALSE, ...arguments...);
if (req_id = 0) then
/* Handle submission error */
FND_MESSAGE.RETRIEVE;
FND_MESSAGE.ERROR;
else
commit;
end if;
end;
Example 3
/* Submit a request from a database trigger */
result := FND_REQUEST.SET_MODE(TRUE);
req_id := FND_REQUEST.SUBMIT_REQUEST (FND’,
’FNDMDGEN’, ’Message File Generator’,
’01–NOV–02 00:00:00’, FALSE, ...arguments...);
Example 4
/* Submit a request inserting NULL arguments.
This call inserts 6 arguments with arguments 1, 3,
4, and 6 being NULL */
req_id := FND_REQUEST.SUBMIT_REQUEST (’FND’,
’FNDPROG’,
’Description of FNDPROG’,
’01–FEB–01 00:00:00’, FALSE,
’’, ’arg2’, ’’, NULL, arg5, ’’);
Example 5
/* Submit a repeating request */
result := FND_REQUEST.SET_REPEAT_OPTIONS (’’, 4, ’HOURS’,
’END’);
req_id := FND_REQUEST.SUBMIT_REQUEST (’CUS’,
’CUSPOST’, ’Custom Posting’,
’01–APR–01 00:00:00’, FALSE,
...arguments...);
Attention: You may not want to submit a request if
FND_REQUEST.SET_REPEAT_OPTIONS returns failure.
Thus, you may wish to test the result of
FND_REQUEST.SET_REPEAT_OPTIONS before issuing the
call to FND_REQUEST.SUBMIT_REQUEST.
Example 6
/* Submit a request for 5 copies of a menu report */
result := FND_REQUEST.SET_PRINT_OPTIONS (’hqunx138’,
’Landscape’,
5,
’Yes’,
FALSE);
req_id := FND_REQUEST.SUBMIT_REQUEST (’FND’,
’FNDMNRMT’,
’’,
’’,
’N’, 0, 101);
Example 7
/* Submit a protected request that repeats at noon */
result := FND_REQUEST.SET_OPTIONS (’YES’);
result := FND_REQUEST.SET_REPEAT_OPTIONS (’12:00’);
req_id := FND_REQUEST.SUBMIT_REQUEST (’CUS’,
’CUSPOST’, ’Custom Posting’,
’01–APR–01 00:00:00’, FALSE,
... args ...);