Solved

How can I execute a oracle Request Set trought a PL/SQL ? (500 pts)

Posted on 2004-10-21
7,436 Views
Last Modified: 2011-10-03
Is there a command so I can execute a entire Request Set trought a PL/SQL ?

I know about FND_REQUEST.SUBMIT_REQUEST but this command only execute a single concurrent.

Best Regards,

Peter.
0
Question by:PETERJORDAN
    1 Comment
     
    LVL 5

    Accepted Solution

    by:

    See the Metalink Note on how to do it....

    Doc ID:       Note:104260.1
    Subject:       How to Submit a Report Set from a Custom Form
    Type:       PROBLEM
    Status:       PUBLISHED
          
    Content Type:       TEXT/PLAIN
    Creation Date:       03-APR-2000
    Last Revision Date:       14-NOV-2001

    Problem Description

    -------------------

    You want to submit a report set from a custom form. You know there is

    an API , FND_REQUEST.SUBMIT_REQUEST, to submit individual requests , but

    what about report sets ?

    Solution Description

    --------------------

    A new API was developed for 11i , FND_SUBMIT, to submit report sets via a

    custom application. This feature was backported to R11 through patch 914123.

    You can apply patch 1113909 to get the latest version of the API.

    Explanation

    -----------

    This is the readme from patch 914123 which explains how to use FND_SUBMIT :

    This document describes the PL/SQL APIs for the Request set submission.

    The APIs are described in the order the developer has to call. Some of them may

    be optional APIs. The APIs that are having * are mandatory APIs to complete the

    submission of Request set.

    APIs that are required to identify the Request set and set the over all context:

    FND_SUBMIT.SET_MODE

    Syntax:

    function FND_SUBMIT.SET_MODE( db_trigger IN boolean)

    return boolean;

    Description:

    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.

    Arguments:

    db_trigger Set to TRUE if request set is submitted from a

    database trigger.

    FND_SUBMIT.SET_REL_CLASS_OPTIONS

    Syntax: 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. If both set_rel_class_options

    and set_repeat_options were set then set_rel_class_options will

    take the percedence. Returns TRUE on succesful completion, and

    FALSE otherwise.

    Arguments:

    application 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 time if

    the request not run.

    FND_SUBMIT.SET_REPEAT_OPTIONS

    Syntax: 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 before submitting a concurrent request set to

    set repeat options. If both set_rel_class_options and

    set_repeat_options were set then set_rel_class_options will

    take the percedence.Returns TRUE on succesful

    completion, and FALSE otherwise.

    Arguments:

    repeat_time - Time of day at which it has to be repeated

    repeat_interval - Frequency at which it has to be repeated.

    This will be used/applied only when

    repeat_time is NULL

    repeat_unit - Unit for repeat interval. Default is

    DAYS. MONTHS/DAYS/HOURS/MINUTES

    repeat_type - Apply repeat interval from START or

    END of request default is START. START/END

    repeat_end_time - Time at which the repetition should be

    stopped

    FND_SUBMIT.SET_REQUEST_SET *

    Syntax: function FND_SUBMIT.SET_REQUEST_SET

    (application IN VARCHAR2,

    request_set IN VARCHAR2)

    return boolean;

    Description:

    This function will set the request set context. Call this

    function at very beginning of the submission of a concurrent

    request set transaction. Call this function after calling the

    optional functions SET_MODE, SET_REL_CLASS_OPTIONS,

    SET_REPEAT_OPTIONS. It returns TRUE on sucessful completion,

    and FALSE otherwise.

    Arguments:

    request_set The short name of the request set

    (developer name of the request set)

    application The short name of the application that owns

    the request set.

    APIs to set Request set programs and their attributes:

    FND_SUBMIT.SET_PRINT_OPTIONS

    Syntax: 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 TRUE,

    print_together IN varchar2 default 'N')

    return boolean;

    Description:

    Called before submitting 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 sucessful completion, and FALSE otherwise.

    Arguments:

    printer - Printer name where the request o/p should be sent

    style - Print style that needs to be used for printing

    copies - Number of copies to print

    save_output - Should the output file be saved after printing

    Default is TRUE.TRUE/FALSE

    print_together - Applies only for sub requests. If 'Y',

    output will not be printed until all the sub

    requests complete. Default is 'N'. ( Y/N )

    FND_SUBMIT.ADD_PRINTER

    Syntax: function FND_SUBMIT.ADD_PRINTER

    (printer IN varchar2 default null,

    copies IN number default null)

    return boolean;

    Description:

    Called after set print options to add a printer to the print

    list.Optionally call for each program in the request set.

    Returns TRUE on sucessful completion, and FALSE otherwise

    Arguments:

    printer - Printer name where the request o/p should be sent

    copies - Number of copies to print

    FND_SUBMIT.ADD_NOTIFICATION

    Syntax: function FND_SUBMIT.ADD_NOTIFICATION (user IN varchar2)

    return boolean;

    Description:

    Called before submission to add a user to the notify list.

    Optionally call for each program in the reques set. Returns

    TRUE on sucessful completion, and FALSE otherwise.

    Arguments:

    User - User name.

    FND_SUBMIT.SET_NLS_OPTIONS

    Syntax: function FND_SUBMIT.SET_NLS_OPTIONS

    (language IN varchar2 default NULL,

    territory IN varchar2 default NULL)

    return boolean;

    Description:

    Called before submitting request to set request attributes.

    Optionally call for each program in the request set. Returns

    TRUE on sucessful completion, and FALSE otherwise.

    Arguments:

    implicit - nature of the request to be submitted

    NO/YES/ERROR/WARNING

    protected - Is the request protected against updates YES/NO

    Default is NO

    language - NLS language

    territory - Language territory

    FND_SUBMIT.SUBMIT_PROGRAM *

    Syntax: 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 (submit_program) function for each

    program (report) in the request set. You must call

    set_request_set before calling this function. You have to call

    set_request_set only once for all the submit_program

    calls for that request set.

    This function returns TRUE on successful completion, and FALSE

    otherwise.

    Arguments:

    application Short name of the application associated with

    the program with in a report set

    program Name of the program with in the report set.

    stage Name of the stage that the program belongs to

    (developer name of the stage).

    argument1..100 Arguments for the program.

    API to submit Request Set:

    FND_SUBMIT.SUBMIT_SET *

    Syntax: 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

    successfully, this function returns the concurrent request ID;

    otherwise; it returns 0.

    Arguments:

    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 for 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 with repeating request set.

    Request set FNDRSTEST is having STAGE1 and STAGE2. STAGE1 is having 'FNDSCARU'

    and 'FNDPRNEV' programs. STAGE2 is having '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 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;

    /* 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;

    References

    ----------

    BUG:914123

    BUG:1113909

    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    This article describes some very basic things about SQL Server filegroups.
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now