30/48
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
| 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 |
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); |
| Description |
FND_CONC_GLOBAL .SET_REQ_GLOBALS sets the values for special globals. |
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 the "developer" phase and status values that can drive program logic. |
| 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 |
Short name of the concurrent program (not the executable). 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. |
| 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
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:
Possible Values for dev_phase and dev_status
| PENDING |
NORMAL |
Request is waiting for the next available manager. |
| PENDING |
STANDBY |
A constrained request (for example, a request for a program that is incompatible with currently running or actively pending programs) is waiting for the Internal concurrent manager to release it. |
| PENDING |
SCHEDULED |
Request is scheduled to start at a future time or date. |
| PENDING |
PAUSED |
Child request is waiting for its Parent request to mark it ready to run. For example, a report in a report set that runs sequentially must wait for a prior report to complete. |
| RUNNING |
NORMAL |
Request is being processed. |
| RUNNING |
PAUSED |
Parent request is waiting for its sub-requests to complete. |
| RUNNING |
RESUMING |
Parent request is waiting to restart after its sub-requests have completed. |
| RUNNING |
TERMINATING |
A user has requested to terminate this running request. |
| COMPLETE |
NORMAL |
Request completed successfully. |
| COMPLETE |
ERROR |
Request failed to complete successfully. |
| COMPLETE |
WARNING |
Request completed with warnings. For example, a report is generated successfully but failed to print. |
| COMPLETE |
CANCELLED |
Pending or Inactive request was canceled. |
| COMPLETE |
TERMINATED |
Running request was terminated. |
| INACTIVE |
DISABLED |
Concurrent program associated with the request is disabled. |
| INACTIVE |
ON_HOLD |
Pending request placed on hold. |
| INACTIVE |
NO_ MANAGER |
No manager is defined to run the request. |
| INACTIVE |
SUSPENDED |
This value is included for upward compatibility. It indicates that a user has paused the request at the OS level. |
FND_CONCURRENT.WAIT_FOR_REQUEST (Client or Server)
| Summary |
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;
|
| Description |
Waits for request completion, then returns the request phase/status and completion message to the caller. Goes to sleep between checks for request completion. |
| request_id |
The request ID of the request to wait on. |
| interval |
Number of seconds to wait between checks (i.e., number of seconds to sleep.) |
| max_wait |
The maximum time in seconds to wait for the request's completion. |
| phase |
The user-friendly request phase from the FND_LOOKUPS table. |
| status |
The user-friendly request status from the FND_LOOKUPS table. |
| 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 already completed. |
FND_CONCURRENT.SET_COMPLETION_STATUS (Server)
| Summary |
function FND_CONCURRENT.SET_COMPLETION_STATUS
(status IN varchar2,
message IN varchar2) return boolean;
|
| Description |
Call SET_COMPLETION_STATUS from a concurrent program to set its completion status. The function returns TRUE on success, otherwise FALSE. |
| status |
The status to set the concurrent program to. Either NORMAL, WARNING, or ERROR. |
| message |
An optional message. |
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.
Important: 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
| 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. |
| 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. |
| 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_FILE.NEW_LINE
| Summary |
procedure FND_FILE.NEW_LINE
(which IN NUMBER,
LINES IN NATURAL := 1);
|
| Description |
Use this procedure to write line terminators (new line characters) to a file. |
| which |
Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT. |
| lines |
Number of line terminators to write. |
Example
To write two new line characters:
fnd_file.new_line(FND_FILE.LOG,2);
FND_FILE.PUT_NAMES
| Summary |
procedure FND_FILE.PUT_NAMES
(p_log IN VARCHAR2,
p_out IN VARCHAR2,
(p_dir IN VARCHAR2);
|
| Description |
Sets the temporary log and out filenames and the temp directory to the user-specified values. DIR must be a directory to which the database can write. FND_FILE.PUT_NAMES should be called before calling any other FND_FILE function, and only once per session. |
Important: 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;
| p_log |
Temporary log filename. |
| p_out |
Temporary output filename. |
| p_dir |
Temporary directory name. |
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
| Summary |
procedure FND_FILE.CLOSE; |
| Description |
Use this procedure to close open files.
Important: Use FND_FILE.CLOSE only in command lines sessions. FND_FILE.CLOSE should not be called from a concurrent program.
|
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
| Summary |
function FND_PROGRAM.MESSAGE return VARCHAR2; |
| Description |
Use the message function to return an error message. Messages are set when any validation (program) errors occur. |
FND_PROGRAM.EXECUTABLE
| Summary |
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');
|
| Description |
Use this procedure to define a concurrent program executable. This procedure corresponds to the "Concurrent Program Executable" window accessible from the System Administrator and Application Developer responsibilities. |
| executable |
Name of executable (for example, 'FNDSCRMT'). |
| application |
The short name of the executable's application, for example, 'FND'. |
| description |
Optional description of the executable. |
| execution_ method |
The type of program this executable uses. Possible values are 'Host', 'Immediate', 'Oracle Reports', 'PL/SQL Stored Procedure', 'Spawned', 'SQL*Loader', 'SQL*Plus'. |
| execution_ file_name |
The operating system name of the file. Required for all but Immediate programs. This file name should not include spaces or periods unless the file is a PL/SQL stored procedure. |
| subroutine_name |
Used only by Immediate programs. Cannot contain spaces or periods. |
| icon_name |
Reserved for future use by internal developers only. Specify NULL. |
| language_code |
Language code for the name and description, for example, 'US'. |
FND_PROGRAM.DELETE_EXECUTABLE
| Summary |
procedure FND_PROGRAM.DELETE_EXECUTABLE
(executable IN varchar2,
application IN varchar2);
|
| Description |
Use this procedure to delete a concurrent program executable. An executable that is assigned to a concurrent program cannot be deleted. |
| executable |
The short name of the executable to delete. |
| application |
The short name of the executable's application, for example 'FND'. |
FND_PROGRAM.REGISTER
| Summary |
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);
|
| Description |
Use this procedure to define a concurrent program. This procedure corresponds to the "Concurrent Program" window accessible from the System Administrator and Application Developer responsibilities. |
| program |
The user-visible program name, for example 'Menu Report'. |
| application |
The short name of the application that owns the program. The program application determines the Oracle user name used by the program. |
| enabled |
Specify either "Y" or "N". |
| short_name |
The internal developer program name. |
| description |
An optional description of the program. |
| executable_name |
The short name of the registered concurrent program executable. |
| executable_ application |
The short name of the application under which the executable is registered. |
| execution_ options |
Any special option string, used by certain executables such as Oracle Reports. |
| priority |
An optional program level priority. |
| save_output |
Indicate with "Y" or "N" whether to save the output. |
| print |
Allow printing by specifying "Y", otherwise "N". |
| cols |
The page width of report columns. |
| rows |
The page length of report rows. |
| style |
The default print style name. |
| style_required |
Specify whether to allow changing the default print style from the Submit Requests window. |
| printer |
Force output to the specified printer. |
| request_type |
A user-defined request type. |
| request_type_ application |
The short name of the application owning the request type. |
| use_in_srs |
Specify "Y" to allow users to submit the program from the Submit Requests window, otherwise "N". |
| allow_ disabled_values |
Specify "Y" to allow parameters based on outdated value sets to validate anyway. Specify "N" to require current values. |
| run_alone |
Program must have the whole system to itself. ("Y" or "N") |
| output_type |
The type of output generated by the concurrent program. Either "HTML", "PS", "TEXT" or "PDF". |
| enable_trace |
Specify "Y" if you want to always enable SQL trace for this program, "N" if not. |
| nls_compliant |
Reserved for use by internal developers only. Use "N". |
| icon_name |
Reserved for use by internal developers only. Use NULL. |
| language_code |
Language code for the name and description. |
| mls_function_ short_name |
The name of the registered MLS function. |
| mls_function_ application |
The short name of the application under which the MLS function is registered. |
| incrementor |
The incrementor PL/SQL function name. |
FND_PROGRAM.DELETE_PROGRAM
| Summary |
procedure FND_PROGRAM.DELETE_PROGRAM
(program_short_name IN varchar2,
application IN varchar2);
|
| Description |
Use this procedure to delete a concurrent program. All references to the program are deleted as well. |
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| application |
The application that owns the concurrent program. |
FND_PROGRAM.PARAMETER
| Summary |
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);
|
| Description |
Creates a new parameter for a specified concurrent program. This procedure corresponds to the "Concurrent Program Parameters" window accessible from the System Administrator and Application Developer responsibilities. |
Important: 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.
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| application |
The short name of the application that owns the concurrent program. |
| sequence |
The parameter sequence number that determines the order of the parameters. |
| parameter |
The parameter name. |
| description |
An optional parameter description. |
| enabled |
"Y" for enabled parameters; "N" for disabled parameters. |
| value_set |
The value set to use with this parameter. |
| default_type |
An optional default type. Possible values are 'Constant', 'Profile', 'SQL Statement', or 'Segment'. |
| default_value |
Only required if the default_type is not NULL. |
| required |
"Y" for required parameters, "N" for optional ones. |
| enable_security |
"Y" enables value security if the value set permits it. "N" prevents value security from operating on this parameter. |
| range |
Optionally specify "High", "Low", or "Pair". |
| display |
"Y" to display the parameter, "N" to hide it. |
| display_size |
The length of the item in the parameter window. |
| description_size |
The length of the item's description in the parameter window. |
| concatenated_ description_size |
The Length of the description in the concatenated parameters field. |
| prompt |
The item prompt in the parameter window. |
| token |
The Oracle Reports token (only used with Oracle Reports programs). |
FND_PROGRAM.DELETE_PARAMETER
| Summary |
procedure FND_PROGRAM.DELETE_PARAMETER
(program_short_name IN varchar2,
application IN varchar2
parameter IN varchar2);
|
| Description |
Call this procedure to remove a parameter from a concurrent program. |
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| application |
The application that owns the concurrent program. |
| parameter |
The parameter to delete. |
FND_PROGRAM.INCOMPATIBILITY
| Summary |
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');
|
| Description |
Use this procedure to register an incompatibility for a specified concurrent program. This procedure corresponds to the "Incompatible Programs" window accessible from the System Administrator and Application Developer responsibilities. |
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| application |
The short name of the application that owns the concurrent program |
| inc_prog_ short_name |
The short name of the incompatible program. |
| inc_prog_ application |
Application that owns the incompatible program. |
| scope |
Either "Set" or "Program Only" |
FND_PROGRAM.DELETE_INCOMPATIBILITY
| Summary |
procedure FND_PROGRAM.DELETE_INCOMPATIBILITY
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
inc_prog_short_name IN VARCHAR2,
inc_prog_application IN VARCHAR2);
|
| Description |
Use this procedure to delete a concurrent program incompatibility rule. |
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| application |
Application that owns the concurrent program |
| inc_prog_ short_name |
Short name of the incompatible program to delete. |
| inc_prog_ application |
Application that owns the incompatible program. |
FND_PROGRAM.REQUEST_GROUP
| Summary |
procedure FND_PROGRAM.REQUEST_GROUP
(request_group IN VARCHAR2,
application IN VARCHAR2,
code IN VARCHAR2 DEFAULT NULL,
description IN VARCHAR2 DEFAULT NULL);
|
| Description |
Use this procedure to create a new request group. This procedure corresponds to the master region of the "Request Groups" window in the System Administration responsibility. |
| request_group |
The name of the request group |
| application |
The application that owns the request group. |
| code |
An optional code for the request group. |
| description |
An optional description of the request group. |
FND_PROGRAM.DELETE_GROUP
| Summary |
procedure FND_PROGRAM.DELETE_GROUP
(group IN VARCHAR2,
application IN VARCHAR2);
|
| Description |
Use this procedure to delete a request group. |
| request_group |
The name of the request group to delete. |
| application |
The application that owns the request group. |
FND_PROGRAM.ADD_TO_GROUP
| Summary |
procedure FND_PROGRAM.ADD_TO_GROUP
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
|
| Description |
Use this procedure to add a concurrent program to a request group. This procedure corresponds to the "Requests" region in the "Request Groups" window in System Administration. |
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| program_ application |
The application that owns the concurrent program. |
| request_group |
The request group to which to add the concurrent program. |
| group_ application |
The application that owns the request group. |
FND_PROGRAM.REMOVE_FROM_GROUP
| Summary |
procedure FND_PROGRAM.REMOVE_FROM_GROUP
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
|
| Description |
Use this procedure to remove a concurrent program from a request group. |
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| program_ application |
The application that owns the concurrent program. |
| request_group |
The request group from which to delete the concurrent program. |
| group_ application |
The application that owns the request group. |
FND_PROGRAM.PROGRAM_EXISTS
| Summary |
function FND_PROGRAM.PROGRAM_EXISTS
(program IN VARCHAR2,
application IN VARCHAR2)
return boolean;
|
| Description |
Returns TRUE if a concurrent program exists. |
| program |
The short name of the program |
| application |
Application short name of the program. |
FND_PROGRAM.PARAMETER_EXISTS
| Summary |
function FND_PROGRAM.PARAMETER_EXISTS
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
parameteR IN VARCHAR2)
return boolean;
|
| Description |
Returns TRUE if a program parameter exists. |
| program |
The short name of the program |
| application |
Application short name of the program. |
| parameter |
Name of the parameter. |
FND_PROGRAM.INCOMPATIBILITY_EXISTS
| Summary |
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;
|
| Description |
Returns TRUE if a program incompatibility exists. |
| program |
The short name of the first program |
| application |
Application short name of the program. |
| inc_prog_short_ name |
Short name of the incompatible program. |
| inc_prog_ applicatoin |
Application short name of the incompatible program. |
FND_PROGRAM.EXECUTABLE_EXISTS
| Summary |
function FND_PROGRAM.EXECUTABLE_EXISTS
(executable_short_name IN VARCHAR2,
application IN VARCHAR2)
return boolean;
|
| Description |
Returns TRUE if program executable exists. |
| program |
The name of the executable. |
| application |
Application short name of the executable. |
FND_PROGRAM.REQUEST_GROUP_EXISTS
| Summary |
function FND_PROGRAM.REQUEST_GROUP_EXISTS
(request_group IN VARCHAR2,
application IN VARCHAR2)
return boolean;
|
| Description |
Returns TRUE if request group exists. |
| program |
The name of the executable. |
| application |
Application short name of the request group. |
FND_PROGRAM.PROGRAM_IN_GROUP
| Summary |
function FND_PROGRAM.INCOMPATIBILITY_EXISTS
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2)
return boolean;
|
| Description |
Returns TRUE if a program is in a request group. |
| program |
The short name of the first program. |
| application |
Application short name of the program. |
| request_group |
Name of the request group. |
| group_ application |
Application short name of the request group. |
FND_PROGRAM.ENABLE_PROGRAM
| Syntax |
procedure FND_PROGRAM_ENABLE_PROGRAM
(short_name IN VARCHAR2,
application IN VARCHAR2,
ENABLED IN VARCHAR2);
|
| Description |
Use this procedure to enable or disable a concurrent program. |
| short_name |
The shortname of the program. |
| application |
Application short name of the program. |
| enabled |
Specify 'Y' to enable the program and 'N' to disable the program. |
FND_REQUEST Package
FND_REQUEST.SET_OPTIONS (Client or Server)
| Syntax |
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;
|
| Description |
Optionally call before submitting a concurrent request to set request options. Returns TRUE on successful completion, and FALSE otherwise. |
| implicit |
Determines whether to display this concurrent request in the end-user Concurrent Requests form. (All requests are automatically displayed in the System Administrator's privileged Concurrent Requests form, regardless of the value of this argument.) Specify 'NO', 'YES', 'ERROR', or 'WARNING'.
'NO' allows the request to be viewed on the end-user Concurrent Requests form.
'YES' means that the request may be viewed only from the System Administrator's privileged Concurrent Requests form.
'ERROR' causes the request to be displayed in the end user Concurrent Requests form only if it fails.
'WARNING' allows the request to display in the end-user Concurrent Requests form only if it completes with a warning or an error. |
| protected |
Indicates whether this concurrent request is protected against updates made using the Concurrent Requests form. 'YES' means the request is protected against updates; 'NO' means the request is not protected. |
| language |
Indicates the NLS language. If left NULL, defaults to the current language. |
| territory |
Indicates the language territory. If left NULL, defaults to the current language territory. |
FND_REQUEST.SET_REPEAT_OPTIONS (Client or Server)
| Summary |
function FND_REQUEST.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 to set repeat options. Returns TRUE on successful completion, and FALSE otherwise. |
| repeat_time |
Time of day to repeat the concurrent request, formatted as HH24:MI or HH24:MI:SS. The only other parameter you may use with repeat_time is repeat_end_time. |
| repeat_interval |
Interval between resubmissions of the request. Use this parameter along with repeat_unit to specify the time between resubmissions. This parameter applies only when repeat_time is NULL. |
| repeat_unit |
The unit of time used along with repeat_interval to specify the time between resubmissions of the request. The available units are 'MINUTES', 'HOURS', 'DAYS', and 'MONTHS'. This parameter applies only when repeat_time is NULL. |
| repeat_type |
Determines whether to apply the resubmission interval from either the 'START' or the 'END' of the request's execution. This parameter applies only when repeat_time is NULL. |
| repeat_end_time |
The date and time to stop resubmitting the concurrent request, formatted as either: 'DD-MON-YYYY HH24:MI:SS' or 'DD-MON-RR HH24:MI:SS' |
FND_REQUEST.SET_PRINT_OPTIONS (Client or Server)
| Summary |
function FND_REQUEST.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 |
Optionally call before submitting a concurrent request to set print options. Returns TRUE on successful completion, and FALSE otherwise. |
Important: Some print options that are set at the program level (i.e., using the Concurrent Programs form) cannot be overridden using this procedure. See the following argument descriptions to determine which print options can be overridden.
| printer |
Name of the printer to which concurrent request output should be sent. You cannot override this print option if it was already set using the Concurrent Programs form. |
| style |
Style used to print request output, for example 'Landscape' or 'Portrait'. (Valid print styles are defined using the Print Styles form.) If the Style option was already set using the Concurrent Programs form, and the Style Required check box is checked, you cannot override this print option. |
| copies |
Number of copies of request output to print. You can override this print option even if it was already set using the Concurrent Programs form. |
| save_output |
Indicates whether to save the output file. Valid values are TRUE and FALSE. You can override this print option even if it was already set using the Concurrent Programs form. |
| print_together |
This parameter applies only to requests that contain sub-requests. 'Y' indicates that output of sub-requests should not be printed until all sub-requests complete. 'N' indicates that the output of each sub-request should be printed as it completes. |
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.
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).
See: Overview of Message Dictionary for more information.
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. |
Important: 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 E-Business Suite.
| application |
Short name of the application associated with the concurrent request to be submitted. |
| program |
Short name of the concurrent program (not the executable) 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.
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.
Note: After the last non-null argument, pass a chr(0). The rest of the parameters can be submitted as empty strings (''). For an example, see: Example Request Submissions.
|
FND_REQUEST.ADD_NOTIFICATION (Client or Server)
| Summary |
function FND_REQUEST.ADD_NOTIFICATION
(user IN varchar2) return boolean; |
| Description |
This function is called before submission to add a user to the notification list. This function returns TRUE on successful completion, and FALSE otherwise. |
FND_REQUEST.SET_MODE (Server)
| Summary |
function FND_REQUEST.SET_MODE
(db_trigger IN boolean) return boolean;
|
| Description |
Call this function before calling FND_REQUEST.SUBMIT_REQUEST from a database trigger. |
Note that a failure in the database trigger call of FND_REQUEST.SUBMIT_REQUEST does not roll back changes.
| db_trigger |
Set to TRUE if request is submitted from a database trigger. |
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...);
Important: 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 ...);
FND_REQUEST_INFO and Multiple Language Support (MLS)
FND_REQUEST_INFO APIs can be used in multi-language support functions (MLS functions) to get information for a request.
A multi-language support function is a function that supports running concurrent programs in multiple languages, as well as territories and numeric character settings (".," for example). A user can submit a single request for a concurrent program and have that program run several times, each time with different values for the parameters for language, territory, and numeric character setting.
To enable this functionality, a developer creates an MLS function as a stored function in the database. When called, the function determines which languages are to be used for the concurrent program's data set and returns the list of language codes as a comma-delimited string. The string is then used by the concurrent manager to submit child requests for the concurrent program for each target language. The MLS function can do the same for territory and numeric character setting.
Note: The fnd_concurrent_request should reflect the three values for language, territory, and numeric character setting passed in a string format. This string format consists of a language code, optional territory, and optional numeric character separated by colons and a delimiter of a semicolon as shown in following examples:
“US:US:,.”
“US::.,;JA:JP:;KO:KR:.,”
“US: : ”
The MLS function can use the FND_REQUEST_INFO APIs to retrieve the concurrent program application short name, the concurrent program short name, and the concurrent request parameters if needed.
The developer registers the MLS function in the Concurrent Program Executable form, and then associates the registered MLS function with a concurrent program in the Concurrent Programs form.
FND_REQUEST_INFO.GET_PARAM_NUMBER
| Summary |
function GET_PARAM_NUMBER
(name IN VARCHAR2,
param_num OUT NUMBER);
|
| Description |
Use this function to retrieve the parameter number for a given parameter name. The function will return -1 if it fails to retrieve the parameter number. |
| name |
The name of the parameter of the request's concurrent program. |
FND_REQUEST_INFO.GET_PARAM_INFO
| Summary |
function GET_PARAM_INFO
(param_num IN NUMBER,
name OUT VARCHAR2);
|
| Description |
Use this function to retrieve the parameter name for a given parameter number. The function will return -1 if it fails to retrieve the parameter name. |
| param_num |
The number of the parameter of the request's concurrent program. |
FND_REQUEST_INFO.GET_PROGRAM
| Summary |
procedure GET_PROGRAM
(program_name OUT VARCHAR2,
program_app_name OUT VARCHAR2);
|
| Description |
This procedure returns the developer concurrent program name and application short name. |
| prog_name |
The name of the concurrent program. |
| prog_app_name |
The concurrent program's application short name. |
FND_REQUEST_INFO.GET_PARAMETER
| Summary |
function GET_PARAMETER
(param_num IN NUMBER)
return varchar2;
|
| Description |
This function returns the concurrent request's parameter value for a given parameter number. The function will return the value as varchar2. |
| param_num |
The number of the parameter of the request's concurrent program. |
Example MLS Function
Suppose you have a concurrent program that will send each employee a report of his or her available vacation days. Assume that the concurrent program will accept a range of employee numbers as parameters. The employees all want to receive the vacation days report in their preferred language. Without an MLS function, the user who submits this concurrent program has to guess at all the preferred languages for the given range of employee numbers and select those languages while submitting the request. Selecting all installed languages might be a waste of resources, because output may not be required in all installed languages.
Assume you have an employees table (emp) with the following columns:
emp_no number(15),
...
preferred_lang_code varchar2(4),
...
Your concurrent program has two parameters for the range of employee numbers: parameter 1 is the starting emp_no and parameter 2 is the ending emp_no.
This MLS function could be used for other concurrent programs that have the same parameters for starting and ending employee numbers.
Example
CREATE OR REPLACE FUNCTION EMPLOYEE_LANG_FUNCTION RETURN VARCHAR2 IS
language_string varchar2(240);
start_value varchar2(240);
end_value varchar2(240);
CURSOR language_cursor (starting number, ending number) IS
SELECT DISTINCT(preferred_lang_code) language_code
FROM emp
WHERE emp_no BETWEEN starting AND ending
AND preferred_lang_code IS NOT NULL;
BEGIN
-- Initialize the language string
language_string := null;
-- Get parameter values for starting and
-- ending EMP_NO
start_value := FND_REQUEST_INFO.GET_PARAMETER(1);
end_value := FND_REQUEST_INFO.GET_PARAMETER(2);
FOR languages IN language_cursor(
to_number(start_value),
to_number(end_value)) LOOP
IF( language_string IS NULL ) THEN
language_string := languages.language_code;
ELSE
language_string := language_string || ',' ||
languages.language_code;
END IF;
END LOOP;
RETURN (language_string);
END EMPLOYEE_LANG_FUNCTION;
FND_SET: Request Set Loaders
The FND_SET package includes procedures for creating concurrent program request sets, adding programs to a request set, deleting programs from a request set. and defining parameters for request sets.
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.
See: Overview of Request Sets
FND_SET.MESSAGE
| Summary |
function FND_SET.MESSAGE return VARCHAR2; |
| Description |
Use the message function to return an error message. Messages are set when any validation (program) errors occur. |
FND_SET.CREATE_SET
| Summary |
procedure FND_SET.CREATE_SET
(name IN VARCHAR2,
short_name IN VARCHAR2,
application IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
owner IN VARCHAR2 DEFAULT NULL,
start_date IN DATE DEFAULT SYSDATE,
end_date IN DATE DEFAULT NULL,
print_together IN VARCHAR2 DEFAULT 'N',
incompatibilities_allowed IN VARCHAR2 DEFAULT 'N',
language_code IN VARCHAR2 DEFAULT 'US');
|
| Description |
Use this procedure to register a Request Set. This procedure corresponds to the master region of the "Request Set" window. |
| name |
The name of the new request set. |
| short_name |
The short name for the request set. |
| application |
The application that owns the request set. |
| description |
An optional description of the set. |
| owner |
An optional Oracle E-Business Suite user ID identifying the set owner, for example SYSADMIN. |
| start_date |
The date the set becomes effective. |
| end_date |
An optional date on which the set becomes outdated. |
| print_together |
Specify "Y" or "N" to indication whether all the reports in a set should print at the same time. |
| incompatibilities _allowed |
Specify "Y" or "N" to indicate whether to allow incompatibilities for this set. |
| language_code |
Language code for the above data, for example "US". |
FND_SET.DELETE_SET
| Summary |
procedure FND_SET.DELETE_SET
(request_set IN VARCHAR2,
application IN VARCHAR2);
|
| Description |
Use this procedure to delete a request set and references to that set. |
| request_set |
The short name of the request set to delete. |
| application |
The application that owns the request set. |
FND_SET.ADD_PROGRAM
| Summary |
procedure FND_SET.ADD_PROGRAM
(program IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2,
program_sequence IN NUMBER,
critical IN VARCHAR2 DEFAULT 'Y',
number_of_copies IN NUMBER DEFAULT 0,
save_output IN VARCHAR2 DEFAULT 'Y',
style IN VARCHAR2 DEFAULT NULL,
printer IN VARCHAR2 DEFAULT NULL);
|
| Description |
Use this procedure to add a concurrent program to a request set stage. This procedure corresponds to the "Programs" region in the "Stage Requests" window of the "Request Set" form. |
| program_short_ name |
The short name used as the developer name of the concurrent program, for example 'FNDSCRMT'. |
| program_ application |
The short name of the application that owns the concurrent program. |
| request_set |
The short name of the request set. |
| set_application |
The application that owns the request set. |
| stage |
The short name of the stage. |
| program_ sequence |
The sequence number of this program in the stage. All programs in a stage require a unique sequence number. |
| critical |
Specify 'Y' if this program can affect the stage's outcome, and 'N' if not. |
| number_of_ copies |
An optional default for the number of copies to print. |
| save_output |
Specify 'Y' to allow users to save output, or 'N' if the default is not to save the output. |
| style |
Optionally provide a default print style. |
| printer |
Optionally provide a default printer. |
FND_SET.REMOVE_PROGRAM
| Summary |
procedure FND_SET.REMOVE_PROGRAM
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2,
program_sequence IN NUMBER);
|
| Description |
Use this procedure to remove a concurrent program from a request set. |
| program_short_ name |
The short name used as the developer name of the concurrent program. |
| program_ application |
The short name of the application that owns the concurrent program. |
| request_set |
The short name of the request set. |
| set_application |
The short name of the application that owns the request set. |
| program_ sequence |
The sequence number of this program in the stage. All programs in a stage require a unique sequence number. |
FND_SET.PROGRAM_PARAMETER
| Summary |
procedure FND_SET.PROGRAM_PARAMETER
(program IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2.
program_sequence IN NUMBER,
parameter IN VARCHAR2,
display IN VARCHAR2 DEFAULT 'Y',
modify IN VARCHAR2 DEFAULT 'Y',
shared_parameter IN VARCHAR2 DEFAULT NULL,
default_type IN VARCHAR2 DEFAULT NULL,
default_value IN VARCHAR2 DEFAULT NULL);
|
| Description |
This procedure registers shared parameter information and the request set level overrides of program parameter attributes. This procedure corresponds to the "Request Parameters" window of the "Request Sets" form. |
| program |
The short name used as the developer name of the concurrent program. |
| program_ application |
The short name of the application that owns the concurrent program. |
| request_set |
The short name of the request set. |
| set_application |
The short name of the application that owns the request set. |
| program_ sequence |
The sequence number of this program in the stage. |
| parameter |
The name of the program parameter. |
| display |
"Y" to display the parameter, "N" to hide it. |
| modify |
"Y" to allow users to modify the parameter value, "N" to prevent it. |
| shared_parameter |
If the parameter uses a shared parameter, enter the shared parameter name here. |
| default_type |
If the parameter uses a default, enter the type here. Valid types are 'Constant', 'Profile', 'SQL Statement', or 'Segment'. |
| default_value |
If the parameter uses a default, enter a value appropriate for the default type here. This argument is required if default_type is not null. |
FND_SET.DELETE_PROGRAM_PARAMETER
| Summary |
procedure FND_SET.DELETE_SET_PARAMETER
program IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2 DEFAULT NULL,
stage IN VARCHAR2,
set_application IN VARCHAR2,
program_sequence IN NUMBER,
parameter IN VARCHAR2);
|
| Description |
This procedure removes a concurrent program request set parameter from a request set definition. |
| program |
The short name used as the developer name of the concurrent program. |
| program_ application |
The short name of the application that owns the concurrent program. |
| request_set |
The short name of the request set. |
| set_application |
The short name of the application that owns the request set. |
| program_ sequence |
The sequence number of this program in the set. All programs in a stage require a unique sequence number. |
| parameter |
The name of the program parameter to delete. |
FND_SET.ADD_STAGE
| Summary |
procedure FND_SET.ADD_STAGE
(name IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
short_name IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
display_sequence IN NUMBER,
function_short_name IN VARCHAR2 DEFAULT 'FNDRSSTE'
function_application IN VARCHAR2 DEFAULT 'FND',
critical IN VARCHAR2 DEFAULT 'N',
incompatibilities_allowed IN VARCHAR2 DEFAULT 'N',
start_stage IN VARCHAR2 DEFAULT 'N',
language_code IN VARCHAR2 DEFAULT 'US');
|
| Description |
Adds a stage to a request set. This procedure corresponds to the "Stages" window of the "Request Sets" window. |
| name |
The name of the stage. |
| request_set |
The short name of the request set. |
| set_application |
The application that owns the request set. |
| short_name |
The stage short (non-translated) name. |
| description |
Description of the stage. |
| function_ short_name |
Accept the default, "FNDRSSTE", the Standard Stage Evaluation function. |
| function_ application |
The short name of the application owning the function. The default is "FND". |
| function_ application |
Accept the default, "FND". |
| critical |
Specify "Y" if the return value of this stage affects the completion status of the request set, and "N" if it does not. |
| start_stage |
Specify "Y" or "N" to indicate whether this stage is the start stage for the set. |
| incompatibilities _allowed |
Specify "Y" or "N" to indicate whether to allow incompatibilities for this stage. |
| language_code |
The language code for the above data. |
FND_SET.REMOVE_STAGE
| Summary |
procedure FND_SET.REMOVE_STAGE
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2);
|
| Description |
Use this procedure to delete a stage from a request set. |
| request_set |
The short name of the request set. |
| set_application |
The short name of the application that owns the request set. |
| stage |
The short name of the stage to be removed. |
FND_SET.LINK_STAGES
| Summary |
procedure FND_SET.LINK_STAGES
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
from_stage IN VARCHAR2,
to_stage IN VARCHAR2 DEFAULT NULL,
success IN VARCHAR2 DEFAULT 'N',
warning IN VARCHAR2 DEFAULT 'N',
error IN VARCHAR2 DEFAULT 'N');
|
| Description |
Use this procedure to link two stages. |
Important: This procedure updates the specified links. Sets created by FND_SET.CREATE_SET have null links between stages by default.
| request_set |
The short name of the request set. |
| set_application |
The application that owns the request set. |
| from_stage |
The short name of the "from" stage. |
| to_stage |
The short name of the "to" stage. |
| success |
Create success link. Specify 'Y' or 'N'. |
| warning |
Create warning link. Specify 'Y' or 'N'. |
| error |
Create error link. Specify 'Y' or 'N'. |
FND_SET.INCOMPATIBILITY
| Summary |
procedure FND_SET.INCOMPATIBILITY
(request_set IN VARCHAR2,
application IN VARCHAR2,
stage IN VARCHAR2 DEFAULT NULL,
inc_prog IN VARCHAR2 DEFAULT NULL
inc_prog_application IN VARCHAR2 DEFAULT NULL,
inc_request_set IN VARCHAR2 DEFAULT NULL,
inc_set_application IN VARCHAR2 DEFAULT NULL,
inc_stage IN VARCHAR2 DEFAULT NULL);
|
| Description |
Use this procedure to register an incompatibility for a set or stage. Examples are given below. |
| request_set |
The short name of the request set. |
| application |
The short name of the application that owns the request set. |
| stage |
The short name of the stage (for stage incompatibility). |
| inc_prog |
Short name of the incompatible program. |
| inc_prog_ application |
Application that owns the incompatible program. |
| inc_request_set |
Short name of the incompatible request set. |
| inc_set_ application |
The short name of the application that owns the incompatible request set. |
| inc_stage |
Short name of the incompatible stage. |
Examples
-
Set X is incompatible with program Y:
fnd_set.incompatibility(request_set=>'X'
application=>'APPX'
inc_prog_short_name=>'Y', inc_prog_application=>'APPY');
-
Set X is incompatible with set Y:
fnd_set.incompatibility(request_set=>'X',
application=>'APPX',
inc_request_set=>'Y',
inc_set_application=>'APPY');
-
Set X is incompatible with stage 2 of set Y:
fnd_set.incompatibility(request_set=>'X',
application=>'APPX',
inc_request_set=>'Y',
inc_set_application=>'APPY',
inc_stage_number=>2);
-
Stage 3 of set X is incompatible with program Y:
fnd_set.incompatibility(request_set=>'X',
application=>'APPX',
stage_number=>3,
inc_prog_short_name=>'Y',
inc_prog_application=>'APPY');
FND_SET.DELETE_INCOMPATIBILITY
| Summary |
procedure FND_SET.DELETE_INCOMPATIBILITY
(request_set IN VARCHAR2,
application IN VARCHAR2,
stage IN VARCHAR2 DEFAULT NULL,
inc_prog IN VARCHAR2 DEFAULT NULL
inc_prog_application IN VARCHAR2 DEFAULT NULL,
inc_request_set IN VARCHAR2 DEFAULT NULL,
inc_set_application IN VARCHAR2 DEFAULT NULL,
inc_stage IN VARCHAR2 DEFAULT NULL);
|
| Description |
Use this procedure to delete a request set incompatibility rule. |
| request_set |
The short name of the request set. |
| application |
The short name of the application that owns the request set. |
| stage |
The short name of the stage (for stage incompatibility). |
| inc_prog |
Short name of the incompatible program. |
| inc_prog_ application |
Application that owns the incompatible program. |
| inc_request_set |
Short name of the incompatible request set. |
| inc_set_ application |
The short name of the application that owns the incompatible request set. |
| inc_stage |
Short name of the incompatible stage. |
FND_SET.ADD_SET_TO_GROUP
| Summary |
procedure FND_SET.ADD_SET_TO_GROUP
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
|
| Description |
Use this procedure to add a request set to a request group. This procedure corresponds to the "Requests" region in the "Request Groups" window in System Administration. |
| request_set |
The short name of the request set to add to the request group. |
| set_application |
The application that owns the request set. |
| request_group |
The request group to which to add the request set. |
| group_ application |
The application that owns the request group. |
FND_SET.REMOVE_SET_FROM_GROUP
| Summary |
procedure FND_SET.REMOVE_SET_FROM_GROUP
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
|
| Description |
Use this procedure to remove a request set from a request group. |
| request_set |
The short name of the request set to remove from the request group. |
| set_application |
The application that owns the request set. |
| request_group |
The request group from which to remove the request set. |
| group_ application |
The application that owns the request group. |
FND_SUBMIT: Request Set Submission
This document describes the FND_SUBMIT APIs for request set submission. The APIs are described in the order that they would be called. Some of these APIs are optional.
FND_SUBMIT.SET_MODE
| Summary |
function FND_SUBMIT.SET_MODE (db_trigger IN boolean) return boolean; |
| Description |
Use this optional procedure to set the mode if the request set is submitted from a database trigger. 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. |
| db_trigger |
Set to TRUE if the request set is submitted from a database trigger. |
FND_SUBMIT.SET_REL_CLASS_OPTIONS
| Summary |
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 feature. If both FND_SUBMIT.SET_REL_CLASS_OPTIONS and FND_SUBMIT.SET_REPEAT_OPTIONS are set then FND_SUBMIT.SET_REL_CLASS_OPTIONS will take precedence. This function returns TRUE on successful completion, and FALSE otherwise. |
| application |
The 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 date if the request has not yet run. |
FND_SUBMIT.SET_REPEAT_OPTIONS
| Summary |
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 this function to set the repeat options for the request set before submitting a concurrent request set. If both FND_SUBMIT.SET_REL_CLASS_OPTIONS and FND_SUBMIT.SET_REPEAT_OPTIONS were set then FND_SUBMIT.SET_REL_CLASS_OPTIONS will take the percedence. Returns TRUE on successful completion, and FALSE otherwise. |
| repeat_time |
Time of day at which the request set is to be repeated. |
| repeat_interval |
Frequency at which the request set is to be repeated. |
| repeat_unit |
Unit for the repeat interval. The default is DAYS. Valid values are MONTHS, DAYS, HOURS, and MINUTES. |
| repeat_type |
The repeat type specifies whether the repeat interval should apply from the start or end of the previous request. Valid values are START or END. Default value is START. |
| repeat_end_time |
Time at which the repetitions should end. |
FND_SUBMIT_SET.REQUEST_SET
| Summary |
function FND_SUBMIT.SET_REQUEST_SET
(application IN VARCHAR2,
request_set IN VARCHAR2) return boolean; |
| Description |
This function sets the request set context. Call this function at the very beginning of the submission of a concurrent request set transaction. Call this function after calling the optional functions FND_SUBMIT.SET_MODE, FND_SUBMIT.SET_REL_CLASS, FND_SUBMIT.SET_REPEAT_OPTIONS. It returns TRUE on successful completion, and FALSE otherwise. |
| request_set |
The short name of request set (the developer name of the request set). |
| application |
The short name of the application that owns the request set. |
FND_SUBMIT.SET_PRINT_OPTIONS
| Summary |
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
print_together IN varchar2 default 'N') return boolean; |
| Description |
Call this function before submitting the 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 successful completion, and FALSE otherwise. |
| printer |
Printer name for the output. |
| style |
Print style to be used for printing. |
| copies |
Number of copies to print. |
| save_output |
Specify TRUE if the output should be saved after printing, otherwise FALSE. The default is TRUE. |
| print_together |
This argument applies only for subrequests. If 'Y', then output will not be printed untill all the subrequests are completed. The default is 'N'. |
FND_SUBMIT.ADD_PRINTER
| Summary |
function FND_SUBMIT.SET.ADD_PRINTER
(printer IN varchar2 default null,
copies IN number default null) return boolean; |
| Description |
Call this function after you set print options to add a printer to the printer list. Optionally call for each program in the request set. Returns TRUE on successful completion, and FALSE otherwise. |
| printer |
Printer name where the request output should be sent. |
| copies |
Number of copies to print. |
FND_SUBMIT.ADD_NOTIFICATION
| Summary |
function FND_SUBMIT.ADD_NOTIFICATION
(user IN varchar2) return boolean; |
| Description |
This function is called before submission to add a user to the notification list. Optionally call for each program in the request set. This function returns TRUE on successful completion, and FALSE otherwise. |
FND_SUBMIT.SET_NLS_OPTIONS
| Summary |
function FND_SUBMIT.SET_NLS_OPTIONS
(language IN varchar2 default NULL,
territory IN varchar2 default NULL) return boolean; |
| Description |
Call this function before submitting request. This function sets request attributes. Optionally call for each program in the request set. This function returns TRUE on successful completion, and FALSE otherwise. |
| implicit |
Nature of the request to be submitted. |
| protected |
Whether the request is protected against updates. |
| language |
The NLS language. |
| territory |
The language territory. |
FND_SUBMIT.SUBMIT_PROGRAM
| Summary |
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 function for each program (report) in the request set. You must call fnd_submits.set_request_set before calling this function. You have to call fnd_submit.set_request_set only once for all the submit_program calls for that request set. |
| application |
Short name of the application associated with the program within a report set. |
| program |
Name of the program with the report set. |
| stage |
Developer name of the request set stage that the program belongs to. |
| argument1...100 |
Arguments for the program |
FND_SUBMIT.SUBMIT_SET
| Summary |
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 successful, this function returns the concurrent request ID; otherwise; it returns 0. |
| 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. |
Examples of Request Set Submission
/* Example 1 */
/* To submit a Request set which is having STAGE1 and
STAGE2. STAGE1 is having 'FNDSCARU' and 'FNDPRNEV'
programs. STAGE2 is having 'FNDSCURS'. */
/* set the context for the request set FNDRSTEST */
success := fnd_submit.set_request_set('FND', 'FNDRSTEST');
if ( success ) then
/* submit program FNDSCARU which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDSCARU',
'STAGE1', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDPRNEV which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDPRNEV',
'STAGE1','','','','','','','','','','',
CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDSCURS which is in stage STAGE2 */
success := fnd_submit.submit_program('FND','FNDSCURS',
'STAGE2', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* Submit the Request Set */
req_id := fnd_submit.submit_set(null,FALSE);
end if;
/* Example 2 */
/* To submit a request set FNDRSTEST as a repeating request set.
Request set FNDRSTEST has STAGE1 and STAGE2.
STAGE1 contains 'FNDSCARU' and 'FNDPRNEV' programs.
STAGE2 has 'FNDSCURS'. */
/* set the repeating options for the request set before
calling the set_request_set */
success := fnd_submit.set_repeat_options( '', 4, 'HOURS', 'END');
/* set the context for the request set FNDRSTEST */
success := fnd_submit.set_request_set('FND',
'FNDRSTEST');
if ( success ) then
/* submit program FNDSCARU which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDSCARU',
'STAGE1', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDPRNEV which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDPRNEV',
'STAGE1','','','','','','','','','','',
CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDSCURS which is in stage STAGE2 */
success := fnd_submit.submit_program('FND','FNDSCURS',
'STAGE2', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* Submit the Request set */
req_id := fnd_submit.submit_set(null,FALSE);
end if;
/* Example 3 */
/* To submit a Request set FNDRSTEST with 5 copies of the Print environment variables report. Request set FNDRSTEST has STAGE1 and STAGE2. STAGE1 has 'FNDSCARU' and 'FNDPRNEV' programs. STAGE2 has 'FNDSCURS'. */
/* set the context for the request set FNDRSTEST */
success := fnd_submit.set_request_set('FND', 'FNDRSTEST');
if ( success ) then
/* submit program FNDSCARU which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDSCARU',
'STAGE1', CHR(0),'','','','','','','','','', ...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* set the print options for the program */
success := fnd_submit.set_print_options( 'hqunx138', 'Landscape', 5,
'Yes', FALSE);
/* submit program FNDPRNEV which is in stage STAGE1 */
success:= fnd_submit.submit_program('FND','FNDPRNEV',
'STAGE1','','','','','','','','','','',
CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDSCURS which is in stage STAGE2 */
success := fnd_submit.submit_program('FND','FNDSCURS', 'STAGE2', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* Submit the Request set */
req_id := fnd_submit.submit_set(null,FALSE);
end if;