[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-10-21
1
Medium Priority
?
7,819 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
Comment
Question by:PETERJORDAN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 5

Accepted Solution

by:
Pontis earned 2000 total points
ID: 12388032

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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

649 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