[EBS] How to Get Concurrent Program Details from a Request Set in Oracle Apps R12?

 

In Oracle E-Business Suite R12, Request Sets are used to group multiple concurrent programs and run them together in a defined sequence. Sometimes, as a technical consultant or support developer, we need to identify which concurrent programs are attached to a specific Request Set, along with their executable details, execution file name, execution method, and application name.

This post will show how to get Concurrent Program Details from a Request Set in Oracle Apps R12

• Log in to Oracle E-Business Suite using your application user credentials.
• Navigate to the System Administrator responsibility.

• Go to the navigation below:

            Concurrent > Set

• This will open the Request Set definition form.

• Press F11 to enter query mode. 
• Enter the Request Set name in the Set field.

• Then press Ctrl + F11 to execute the query.
• Oracle EBS will display the Request Set definition if it exists.

• Once the Request Set is displayed, you can review the basic details such as:
• Request Set Name
• Application
• Description
• Active Dates
• Owner
• Print Options
• This confirms that you are checking the correct Request Set.

• In the Request Set form, check the Stages section by clicking on Define Stages button.

• A Request Set can have one or more stages. Each stage controls how the programs are grouped and executed. 
• You can review details such as:
• Display Sequence
• Stage Name
• Description
• Stage Details
• Function
• Select the required stage and click on the Requests button.
• This will show the list of concurrent programs attached to that particular stage in Stage Requests window.

• In the Stage Requests window, you can see the concurrent programs attached to the selected Request Set stage.
• You can review details such as:
• Program Name
• Application
• Sequence
• Description
• Parameters
• Print Options
• This is the same functional information that we are fetching from backend tables using the SQL query.

SQL Query to Find Concurrent Programs in a Request Set

SELECT rs.user_request_set_name        "Request Set",

       rss.display_sequence           seq,

       cp.user_concurrent_program_name "Concurrent Program",

       e.executable_name,

       e.execution_file_name,

       lv.meaning                      file_type,

       fat.application_name            "Application Name"

FROM   fnd_request_sets_vl          rs,

       fnd_req_set_stages_form_v    rss,

       fnd_request_set_programs     rsp,

       fnd_concurrent_programs_vl   cp,

       fnd_executables              e,

       fnd_lookup_values            lv,

       fnd_application_tl           fat

WHERE  1 = 1

AND    rs.application_id = rss.set_application_id

AND    rs.request_set_id = rss.request_set_id

AND    rs.user_request_set_name = '<Name of Concurrent Request Set>'

AND    e.application_id = fat.application_id

AND    rss.set_application_id = rsp.set_application_id

AND    rss.request_set_id = rsp.request_set_id

AND    rss.request_set_stage_id = rsp.request_set_stage_id

AND    rsp.program_application_id = cp.application_id

AND    rsp.concurrent_program_id = cp.concurrent_program_id

AND    cp.executable_id = e.executable_id

AND    cp.executable_application_id = e.application_id

AND    lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'

AND    lv.lookup_code = e.execution_method_code

AND    lv.language = 'US'

AND    fat.language = 'US'

AND    rs.end_date_active IS NULL;

SQL Query to Find Concurrent Programs in a Request Set

Explanation of Important Tables Used

1. FND_REQUEST_SETS_VL This view stores Request Set information in Oracle EBS. It contains details such as:

• Request Set Name
• Request Set ID
• Application ID
• Active or inactive status

In this query, it is used to identify the Request Set: AND    rs.user_request_set_name = '<Name of Concurrent Request Set>'

2. FND_REQ_SET_STAGES_FORM_V This view stores the stages defined inside a Request Set. A Request Set can have one or more stages, and each stage can contain one or more concurrent programs. The column display_sequence helps identify the order in which stages or programs are displayed.

3. FND_REQUEST_SET_PROGRAMS This table stores the mapping between Request Set stages and concurrent programs. It helps identify which concurrent programs are attached to each Request Set stage.

4. FND_CONCURRENT_PROGRAMS_VL This view stores concurrent program details. It provides the user-friendly concurrent program name using: cp.user_concurrent_program_name "Concurrent Program" This is the same name usually visible from the Concurrent Program definition screen.

5. FND_EXECUTABLES This table stores executable details of concurrent programs. It provides important technical information such as:

• Executable Name
• Execution File Name
• Execution Method Code

For example, the execution file can be a PL/SQL package, host script, Java program,

SQL*Plus file, or reports file depending on the execution method.

6. FND_LOOKUP_VALUES This table is used to derive the meaning of the execution method code. The lookup type used is: CP_EXECUTION_METHOD_CODE This helps display a readable file type or execution method instead of only showing the internal code.

7. FND_APPLICATION_TL This table stores translated application names. It helps identify the application under which the executable is registered. Example application names can be:

• Application Object Library
• Payables
• Receivables
• Purchasing
• Custom Application