TriniDBA

Problem: I want to create a custom concurrent program based on a SQL script, but do not know how to pass date parameters to the concurrent program.

Solution: Use ‘&1’, ‘&2’ etc. and format it using ‘yyyy-mm-dd hh24:mi:ss’ in the SQL program (i.e. to_date(‘&1’, ‘yyyy-mm-dd hh24:mi:ss’). In the Concurrent Program parameter setup, set the Value Set as FND_STANDARD_DATE.

Firstly, in the SQL script that you wish to set up as the concurrent program, the date parameters must be correctly formatted. For example:

and default_effective_date between to_date('&1','yyyy-mm-dd hh24:mi:ss')
and to_date('&2','yyyy-mm-dd hh24:mi:ss')

You must determine the application under which you are going to run the concurrent program and then place the SQL script in that location. In this example, the custom concurrent program will be run from General Ledger, so the SQL script must be placed in the $GL_TOP/sql (%GL_TOP%\sql) directory.

You must first set up the concurrent program executable, and then define the concurrent program. To set up a new concurrent program executable, navigate to System Administrator > Concurrent > Program > Executable.

The Concurrent Program Executable form appears. Enter the required fields. Ensure you enter the correct application and execution method (SQL*Plus), and then click Save.

Next, define the custom concurrent program. Navigate to System Administrator > Concurrent > Program > Define.

The Concurrent Program form appears. Enter the fields as necessary. In the Executable Name field, select the concurrent program executable you just defined. Save the form, and then click the Parameters button.

In the Concurrent Program Parameters form, you do not need to specify ‘&1’. However, the parameters should be specified in the order in which they appear in the SQL script (that is, Start_Date appears before End_Date). I used the FND_STANDARD_DATE as the Value Set for the date parameters. After entering the parameters, save the form and exit.

Next, you must associate this custom concurrent program with a responsibility. To do, you must add the custom concurrent program to the request group for the responsibility. To find out which request group is tied to a responsibility, navigate to System Administrator > Security > Responsibility > Define.

The Responsibilities form appears. In the Responsibility Name field, search for the responsibility that you want to add the report to (using F11, search string, Ctrl+F11). Make a note of request group name and application.

Next, navigate to System Administrator > Security > User > Request.

The Request Groups form appears. Query the Group and Application fields using the group and application noted above. Click in the Requests area, click New to insert a new row, and add the name of the new request to add to the request group. Then click Save. When you switch to the responsibility, the custom concurrent program now appears in the concurrent request submission form.