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 Package

This package is used for submitting sub-requests from PL/SQL concurrent programs.

FND_CONC_GLOBAL.REQUEST_DATA

Variable Description
Summary
function FND_CONC_GLOBAL.REQUEST_DATA return varchar2;
Variable Description
Description FND_CONC_GLOBAL.REQUEST_DATA retrieves the value of the REQUEST_DATA global.

FND_CONC_GLOBAL.SET_REQ_GLOBALS

Variable Description
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);           
Variable Description
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

Variable Description
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

Variable Description
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)

Variable Description
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.
Variable Description
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.
Variable Description
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:

dev_phase dev_status Description
PENDING NORMAL Request is waiting for the next available manager.
PENDING STANDBY A constrained request (i.e. 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 WAITING 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 cancelled.
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)

Variable Description
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.
Variable Description
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.
Variable Description
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)

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
which Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
buff Text to write.

FND_FILE.PUT_LINE

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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; 
Variable Description
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

Variable Description
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

Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
Summary
procedure FND_PROGRAM.DELETE_EXECUTABLE

(executable             IN      varchar2,
        application             IN varchar2);
Variable Description
Description Use this procedure to delete a concurrent program executable. An executable that is assigned to a concurrent program cannot be deleted.
Variable Description
executable The short name of the executable to delete.
application The short name of the executable's application, for example 'FND'.

FND_PROGRAM.REGISTER

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.

Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
Summary
procedure FND_PROGRAM.DELETE_GROUP

(group                  IN      VARCHAR2,
 application IN VARCHAR2);
Description Use this procedure to delete a request group.
Variable Description
request_group The name of the request group to delete.
application The application that owns the request group.

FND_PROGRAM.ADD_TO_GROUP

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
Summary
function FND_PROGRAM.PROGRAM_EXISTS

(program                IN VARCHAR2,
 application    IN VARCHAR2)
return boolean;
Description Returns TRUE if a concurrent program exists.
Variable Description
program The short name of the program
application Application short name of the program.

FND_PROGRAM.PARAMETER_EXISTS

Variable Description
Summary
function FND_PROGRAM.PARAMETER_EXISTS

(program_short_name     IN VARCHAR2,
        application                             IN      VARCHAR2,
        parameteR                               IN      VARCHAR2)
return boolean;
Variable Description
Description Returns TRUE if a program parameter exists.
Variable Description
program The short name of the program
application Application short name of the program.
parameter Name of the parameter.

FND_PROGRAM.INCOMPATIBILITY_EXISTS

Variable Description
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.
Variable Description
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

Variable Description
Summary
function FND_PROGRAM.EXECUTABLE_EXISTS

(executable_short_name  IN VARCHAR2,
 application                                    IN      VARCHAR2)
return boolean;
Description Returns TRUE if program executable exists.
Variable Description
program The name of the executable.
application Application short name of the executable.

FND_PROGRAM.REQUEST_GROUP_EXISTS

Variable Description
Summary
function FND_PROGRAM.REQUEST_GROUP_EXISTS

(request_group  IN VARCHAR2,
 application            IN      VARCHAR2)
return boolean;
Description Returns TRUE if request group exists.
Variable Description
program The name of the executable.
application Application short name of the request group.

FND_PROGRAM.PROGRAM_IN_GROUP

Variable Description
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.
Variable Description
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

Variable Description
Syntax
procedure FND_PROGRAM_ENABLE_PROGRAM

(short_name     IN VARCHAR2,
 application    IN      VARCHAR2,
 ENABLED                IN      VARCHAR2);
Variable Description
Description Use this procedure to enable or disable a concurrent program.
Variable Description
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)

Variable Description
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.
Variable Description
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)

Variable Description
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.
Variable Description
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)

Variable Description
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.

Variable Description
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)

Variable Description
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.

Variable Description
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.SET_MODE (Server)

Variable Description
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.

Variable Description
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 setting (".," 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

Variable Description
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.
Variable Description
name The name of the parameter of the request's concurrent program.

FND_REQUEST_INFO.GET_PARAM_INFO

Variable Description
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.
Variable Description
param_num The number of the parameter of the request's concurrent program.

FND_REQUEST_INFO.GET_PROGRAM

Variable Description
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.
Variable Description
prog_name The name of the concurrent program.
prog_app_name The concurrent program's application short name.

FND_REQUEST_INFO.GET_PARAMETER

Variable Description
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.
Variable Description
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

Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
request_set The short name of the request set to delete.
application The application that owns the request set.

FND_SET.ADD_PROGRAM

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.

Variable Description
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

Variable Description
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.
Variable Description
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

  1. Set X is incompatible with program Y:

    fnd_set.incompatibility(request_set=>'X'
                            application=>'APPX'
                            inc_prog_short_name=>'Y',                                    inc_prog_application=>'APPY'); 
    
  2. Set X is incompatible with set Y:

    fnd_set.incompatibility(request_set=>'X',
                            application=>'APPX',
                            inc_request_set=>'Y',
                            inc_set_application=>'APPY');
    
  3. 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);
    
  4. 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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
db_trigger Set to TRUE if the request set is submitted from a database trigger.

FND_SUBMIT.SET_REL_CLASS_OPTIONS

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
printer Printer name where the request output should be sent.
copies Number of copies to print.

FND_SUBMIT.ADD_NOTIFICATION

Variable Description
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.
Variable Description
user User name.

FND_SUBMIT.SET_NLS_OPTIONS

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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

Variable Description
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.
Variable Description
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;