PL/SQL APIs for Concurrent Processing

Overview

This chapter describes concurrent processing APIs you can use in your PL/SQL procedures. It also includes example PL/SQL code using these concurrent processing APIs.

The following concurrent processing packages are covered:

• 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 Loader

• 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

function FND_CONC_GLOBAL.REQUEST_DATA return varchar2;

FND_CONC_GLOBAL.SET_REQ_GLOBALS

procedure SET_REQ_GLOBALS	(conc_status	in varchar2 default null,
									  	 request_data	in varchar2 default null, 
										 conc_restart_time in varchar2 default null,     
									 	 release_sub_request in varchar2 default null);           

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

function FND_CONCURRENT.AF_COMMIT;

FND_CONCURRENT.AF_COMMIT executes the COMMIT command for the specified rollback segment.

FND_CONCURRENT.AF_COMMIT has no arguments.

FND_CONCURRENT.AF_ROLLBACK

function FND_CONCURRENT.AF_ROLLBACK;

FND_CONCURRENT.GET_REQUEST_STATUS (Client or Server)

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;

Example

 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 listed and described in the following table:

FND_CONCURRENT.WAIT_FOR_REQUEST (Client or Server)

function FND_CONCURRENT.WAIT_FOR_REQUEST  

(request_id IN number default NULL,
          interval   IN number default 60,
          max_wait   IN number default 0,
          phase      OUT varchar2,
          status     OUT varchar2,
          dev_phase  OUT varchar2,
          dev_status OUT varchar2,
          message    OUT varchar2) return  boolean;

FND_CONCURRENT.SET_COMPLETION_STATUS (Server)

function FND_CONCURRENT.SET_COMPLETION_STATUS  

(status		IN varchar2,
 message	IN varchar2) return boolean;

FND_FILE: PL/SQL File I/O

The FND_FILE package contains procedures to write text to log and output files. 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.

Tip: This package is not designed for generic PL/SQL text I/O, but rather only for writing to request log and output files.

See: PL/SQL File I/O Processing

FND_FILE.PUT

procedure FND_FILE.PUT

 (which  IN NUMBER,
  buff	 IN VARCHAR2);

FND_FILE.PUT_LINE

procedure FND_FILE.PUT_LINE
 (which	IN NUMBER,
  buff	IN	VARCHAR2);

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_FILE.NEW_LINE

procedure FND_FILE.NEW_LINE
  (which	IN NUMBER,
	 LINES 	IN NATURAL := 1);

Example

To write two new line characters:

 fnd_file.new_line(FND_FILE.LOG,2);

FND_FILE.PUT_NAMES

procedure FND_FILE.PUT_NAMES
 	(p_log IN VARCHAR2,
   p_out IN VARCHAR2,
  (p_dir IN VARCHAR2);

Tip: FND_FILE.PUT_NAMES is meant for testing and debugging from SQL*Plus; it does nothing if called from a concurrent program.

BEGIN
	fnd_file.put_names(‘test.log‘, ‘test.out‘,
		‘/local/db/8.0.4/db-temp-dir/‘); 	
	fnd_file.put_line(fnd_file.output,‘Called stored 			
procedure‘); 
	/* Some logic here... */ 
	fnd_file.put_line(fnd_file.output, ‘Reached point A‘); 
	/* More logic, etc... */ 
	fnd_file.close;
END; 

Example

BEGIN
 fnd_file.put_names(‘test.log‘, ‘test.out‘,
  ‘/local/db/8.0.4/db-temp-dir/‘); 
 fnd_file.put_line(fnd_file.output,‘Called stored  
procedure‘); 
 /* Some logic here... */ 
 fnd_file.put_line(fnd_file.output, ‘Reached point A‘); 
 /* More logic, etc... */ 
 fnd_file.close;
END; 

FND_FILE.CLOSE

procedure FND_FILE.CLOSE;

Example

BEGIN
 fnd_file.put_names(‘test.log‘, ‘test.out‘,
  ‘/local/db/8.0.4/db-temp-dir/‘);    fnd_file.put_line(fnd_file.output,‘Called stored 
procedure‘); 
 /* Some logic here... */ 
 fnd_file.put_line(fnd_file.output, ‘Reached point A‘); 
 /* More logic, etc... */ 
 fnd_file.close;
END; 

Error Handling

The FND_FILE package can raise one exception, FND_FILE.UTL_FILE_ERROR, which is raised to indicate an UTL_FILE error condition. Specifically, the procedures FND_FILE.PUT, FND_FILE.PUT_LINE and FND_FILE.NEW_LINE can raise FND_FILE.UTL_FILE_ERROR if there is an error. In addition to this package exception, FND_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

FND_FILE will raise a UTL_FILE_ERROR if it is not able to open or write to a temporary file. It is up to the concurrent program to error out or complete normally, after the FND_FILE.UTL_FILE_ERROR exception is raised. FND_FILE keeps the translated message in the message stack before raising the UTL_FILE_ERROR exception. Developers can get the message for FND_FILE errors and use it as a Request Completion text. It is up to the caller to get the message from the message stack by using the FND_MESSAGE routine within an exception handler.

The concurrent manager will keep all the temporary file creation errors in the request log file.

FND_PROGRAM: Concurrent Program Loaders

The FND_PROGRAM package includes procedures for creating concurrent program executables, concurrent programs with parameters and incompatibility rules, request sets, and request groups. The FND_PROGRAM package also contains functions you can use to check for the existence of concurrent programs, executables, parameters, and incompatibility rules.

The arguments passed to the procedures correspond to the fields in the Oracle Application Object Library forms, with minor exceptions. In general, first enter the parameters to these procedures into the forms for validation and debugging.

If an error is detected, ORA-06501: PL/SQL: internal error is raised. The error message can be retrieved by a call to the function fnd_program.message().

Some errors are not trapped by the package, notably "duplicate value on index".

Note that an exception is raised if bad foreign key information is provided. For example, delete_program() does not fail if the program does not exist, but does fail if given a bad application name.

FND_PROGRAM.MESSAGE

function FND_PROGRAM.MESSAGE return VARCHAR2;

FND_PROGRAM.EXECUTABLE

procedure FND_PROGRAM.EXECUTABLE

(executable     			IN	VARCHAR2,
	application				IN VARCHAR2,
	description				IN VARCHAR2 DEFAULT NULL,
	execution_method		IN	VARCHAR2,
	execution_file_name	IN	VARCHAR2 DEFAULT NULL,
	subroutine_name		IN VARCHAR2 DEFAULT NULL,
	icon_name				IN	VARCHAR2 DEFAULT NULL,
	language_code			IN VARCHAR2 DEFAULT ‘US‘);

FND_PROGRAM.DELETE_EXECUTABLE

procedure FND_PROGRAM.DELETE_EXECUTABLE

(executable		IN	varchar2,
	application		IN varchar2);

FND_PROGRAM.REGISTER

procedure FND_PROGRAM.REGISTER
 		(program							IN VARCHAR2,
		application  					IN VARCHAR2,
		enabled       					IN VARCHAR2,
		short_name	  					IN VARCHAR2,
		description	 					IN VARCHAR2 DEFAULT NULL,
		executable_name				IN VARCHAR2,
		executable_application 	IN VARCHAR2,
		execution_options				IN VARCHAR2 DEFAULT NULL,
		priority							IN NUMBER   DEFAULT NULL,
		save_output						IN VARCHAR2 DEFAULT ‘Y‘,
		print								IN VARCHAR2 DEFAULT ‘Y‘,
		cols								IN NUMBER   DEFAULT NULL,
		rows								IN NUMBER   DEFAULT NULL,
		style 							IN VARCHAR2 DEFAULT NULL,
		style_required					IN VARCHAR2 DEFAULT ‘N‘,
		printer							IN VARCHAR2 DEFAULT NULL,
		request_type					IN VARCHAR2 DEFAULT NULL,
		request_type_application 	IN VARCHAR2 DEFAULT NULL,
		use_in_srs						IN VARCHAR2 DEFAULT ‘N‘,
		allow_disabled_values		IN VARCHAR2 DEFAULT ‘N‘,
		run_alone						IN VARCHAR2 DEFAULT ‘N‘,
		output_type						IN VARCHAR2 DEFAULT ‘TEXT‘,
		enable_trace					IN VARCHAR2 DEFAULT ‘N‘,
		restart							IN VARCHAR2 DEFAULT ‘Y‘,
		nls_compliant					IN VARCHAR2 DEFAULT ‘N‘,
		icon_name						IN VARCHAR2 DEFAULT NULL,
		language_code					IN VARCHAR2 DEFAULT ‘US‘
		mls_function_short_name	IN VARCHAR2,
		mls_function_application	IN VARCHAR2,
		incrementor						IN VARCHAR2);

FND_PROGRAM.DELETE_PROGRAM

procedure FND_PROGRAM.DELETE_PROGRAM

(program_short_name	IN	varchar2,
    application			IN varchar2);

FND_PROGRAM.PARAMETER

procedure FND_PROGRAM.PARAMETER

(program_short_name 	IN	VARCHAR2,
	application				IN VARCHAR2,
	sequence  				IN NUMBER,
	parameter   			IN VARCHAR2,
	description 			IN VARCHAR2 DEFAULT NULL,
	enabled 					IN VARCHAR2 DEFAULT ‘Y‘,
	value_set 				IN VARCHAR2,
	default_type 			IN VARCHAR2 DEFAULT NULL,
	default_value   		IN VARCHAR2 DEFAULT NULL,
	required 				IN VARCHAR2 DEFAULT ‘N‘,
	enable_security 		IN VARCHAR2 DEFAULT ‘N‘,
	range 					IN VARCHAR2 DEFAULT NULL,
	display 					IN VARCHAR2 DEFAULT ‘Y‘,
	display_size 			IN NUMBER,
	description_size		IN NUMBER,
	concatenated_description_size IN NUMBER,
	prompt 					IN VARCHAR2 DEFAULT NULL,
	token  					IN VARCHAR2 DEFAULT NULL);

Tip: A newly added parameter does not show up in the SRS form until the descriptive flexfields are compiled. The program $FND_TOP/$APPLBIN/fdfcmp compiles the descriptive flexfields.

FND_PROGRAM.DELETE_PARAMETER

procedure FND_PROGRAM.DELETE_PARAMETER

(program_short_name 	IN varchar2,
   	application			IN varchar2
	   parameter	      IN	varchar2);

FND_PROGRAM.INCOMPATIBILITY

procedure FND_PROGRAM.INCOMPATIBILITY

(program_short_name 		IN VARCHAR2,
	application					IN VARCHAR2
	inc_prog_short_name		IN	VARCHAR2,
	inc_prog_application	IN VARCHAR2,
	scope             		IN VARCHAR2 DEFAULT ‘Set‘);

FND_PROGRAM.DELETE_INCOMPATIBILITY

procedure FND_PROGRAM.DELETE_INCOMPATIBILITY

 (program_short_name			IN	VARCHAR2,
		application	 				IN VARCHAR2,
		inc_prog_short_name  	IN VARCHAR2,
		inc_prog_application 	IN VARCHAR2);

FND_PROGRAM.REQUEST_GROUP

procedure FND_PROGRAM.REQUEST_GROUP

(request_group 	IN VARCHAR2,
 application		IN VARCHAR2,
	code 				IN VARCHAR2 DEFAULT NULL,
	description		IN VARCHAR2 DEFAULT NULL);

FND_PROGRAM.DELETE_GROUP

procedure FND_PROGRAM.DELETE_GROUP

(group			IN	VARCHAR2,
 application IN VARCHAR2);

FND_PROGRAM.ADD_TO_GROUP

procedure FND_PROGRAM.ADD_TO_GROUP

(program_short_name		IN	VARCHAR2,
    program_application	IN VARCHAR2,
    request_group			IN VARCHAR2,
    group_application		IN VARCHAR2);

FND_PROGRAM.REMOVE_FROM_GROUP

procedure FND_PROGRAM.REMOVE_FROM_GROUP

(program_short_name		IN	VARCHAR2,
    program_application	IN VARCHAR2,
    request_group			IN VARCHAR2,
    group_application		IN VARCHAR2);

FND_PROGRAM.PROGRAM_EXISTS

function FND_PROGRAM.PROGRAM_EXISTS

(program 		IN VARCHAR2,
 application	IN VARCHAR2)
return boolean;

FND_PROGRAM.PARAMETER_EXISTS

function FND_PROGRAM.PARAMETER_EXISTS

(program_short_name	IN VARCHAR2,
	application				IN	VARCHAR2,
	parameteR				IN	VARCHAR2)
return boolean;

FND_PROGRAM.INCOMPATIBILITY_EXISTS

function FND_PROGRAM.INCOMPATIBILITY_EXISTS

(program_short_name 		IN VARCHAR2,
	application 				IN	VARCHAR2,
	inc_prog_short_name		IN	VARCHAR2,
	inc_prog_application 	IN	VARCHAR2)
return boolean;

FND_PROGRAM.EXECUTABLE_EXISTS

function FND_PROGRAM.EXECUTABLE_EXISTS

(executable_short_name	IN VARCHAR2,
 application 					IN	VARCHAR2)
return boolean;

FND_PROGRAM.REQUEST_GROUP_EXISTS

function FND_PROGRAM.REQUEST_GROUP_EXISTS

(request_group	IN VARCHAR2,
 application		IN	VARCHAR2)
return boolean;

FND_PROGRAM.PROGRAM_IN_GROUP

function FND_PROGRAM.INCOMPATIBILITY_EXISTS

(program_short_name 	IN VARCHAR2,
	application				IN	VARCHAR2,
	request_group			IN	VARCHAR2,
	group_application		IN	VARCHAR2)
return boolean;

FND_PROGRAM.ENABLE_PROGRAM

procedure FND_PROGRAM_ENABLE_PROGRAM

(short_name 	IN VARCHAR2,
 application 	IN	VARCHAR2,
 ENABLED		IN	VARCHAR2);

FND_REQUEST Package

FND_REQUEST.SET_OPTIONS (Client or Server)

function FND_REQUEST.SET_OPTIONS

(implicit  IN varchar2 default ‘NO‘,
          protected IN varchar2 default ‘NO‘,
          language  IN varchar2 default NULL,
          territory IN varchar2 default NULL)
          return boolean;