Query to find Concurrent Program related information

The following query takes the concurrent program name (for example, "Active Users") and returns its related information (i.e. Reports, Created By, etc.).

Note: Mr. Rajkumar Reddy had pointed out that I could use the "lookup" as opposed to my previous hard-coded "decode" for finding the Execution Method (or program type), which is very efficient. I thank Mr. Raj for sharing this.

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

-- Query to find Concurrent Program related information

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

SELECT cpv.user_concurrent_program_name  "Concurrent Program Name",

       cpv.concurrent_program_name       "Program Short Name",

       efv.application_name              "Application Name",

       cpv.enabled_flag                  "Enabled Flag",

       cpv.output_file_type              "Output Format",

       fu.user_name                      "Created By (userid)",

       (SELECT meaning

          FROM FND_LOOKUP_VALUES_VL flv

         WHERE UPPER (flv.lookup_type) = 'CP_EXECUTION_METHOD_CODE'

           AND flv.Lookup_code = efv.execution_method_code

       )                                 "Execution Method",

       efv.executable_name               "Executable Name",

       efv.execution_file_name           "Execution Filename"

  FROM FND_CONCURRENT_PROGRAMS_VL  cpv,

       FND_EXECUTABLES_FORM_V      efv,

       FND_USER                    fu

 WHERE efv.executable_id  = cpv.executable_id

   AND efv.application_id = cpv.application_id

   AND cpv.created_by     = fu.user_id

   AND cpv.user_concurrent_program_name = 'Active Users' -- // change it

 ORDER BY cpv.user_concurrent_program_name;

Query to find Request Group for concurrent program

Following query finds the associated request group and the application module name (Payables, Receivables, etc.) for a concurrent program.

In this example, I used "Active Users" as concurrent program name. With a little modification to the query, you should be able to find concurrent request set name also.

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

-- Query to find request group and application name for a concurrent program

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

SELECT fcp.user_concurrent_program_name    "Concurrent Program Name",

       fcp.concurrent_program_name         "Concurrent Program Short Name",

       fr.responsibility_name              "Responsibility Name",

       frg.request_group_name              "Request Group Name",

       luv.meaning                         "Request Unit Type",

       fa.application_name                 "Application Name",

       fa.application_short_name           "Application Short Name",

       fa.basepath                         "Basepath"

  FROM FND_CONCURRENT_PROGRAMS_VL  fcp,

       FND_RESPONSIBILITY_VL       fr,

       FND_REQUEST_GROUPS          frg,

       FND_REQUEST_GROUP_UNITS     frgu,

       (SELECT lookup_code, meaning

          FROM FND_LOOKUP_VALUES

         WHERE UPPER (lookup_type) = 'SRS_REQUEST_UNIT_TYPES') luv,

       FND_APPLICATION_VL          fa

 WHERE frg.request_group_id = fr.request_group_id

   AND frgu.request_group_id = frg.request_group_id

   AND fcp.concurrent_program_id = frgu.request_unit_id

   AND frgu.request_unit_type = luv.lookup_code

   AND frg.application_id = fa.application_id

   AND fcp.user_concurrent_program_name = 'Active Users'

 ORDER BY fcp.user_concurrent_program_name, 

       fr.responsibility_name, 

       frg.request_group_name;

Query to find concurrent request status

The following query finds the concurrent process status and its related information (such as, completion phase, responsibility used, which user submitted the program, etc.).

In the following example, I used "Autoinvoice Import Program" as Concurrent Program Name. You will need to run the query by changing the program name as per your requirement. You can also uncomment the "FCR.REQUEST_ID" condition (at the bottom of the query) for a specific Request ID.

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

-- Query to find concurrent request status related information

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

SELECT fu.user_name                        "User ID",

       fr.responsibility_name              "Responsibility Used",

       fcr.request_id                      "Request Id",

       fcp.user_concurrent_program_name    "Concurrent Program Name",

       fl_ph.meaning                       "Phase",

       fl_st.meaning                       "Status",

       fcr.request_date                    "Request Date",

       fcr.requested_start_date            "Request Start Date",

       fcr.parent_request_id               "Parent Request Id"

  FROM FND_USER                    fu,

       FND_RESPONSIBILITY_VL       fr,

       FND_CONCURRENT_REQUESTS     fcr,

       FND_CONCURRENT_PROGRAMS_VL  fcp,

       FND_LOOKUP_VALUES           fl_ph,

       FND_LOOKUP_VALUES           fl_st

 WHERE 1=1

   --

   AND fl_ph.lookup_code = fcr.phase_code

   AND fl_ph.lookup_type = 'CP_PHASE_CODE'

   AND fl_ph.enabled_flag = 'Y'

   AND fl_ph.view_application_id = 283

   --

   AND fl_st.lookup_code = fcr.status_code

   AND fl_st.lookup_type = 'CP_STATUS_CODE'

   AND fl_st.enabled_flag = 'Y'

   AND fl_st.view_application_id = 283

   -- 

   AND fu.user_id = fcr.requested_by

   AND fcr.concurrent_program_id = fcp.concurrent_program_id

   AND fcr.responsibility_id = fr.responsibility_id

   --

   AND fcp.user_concurrent_program_name = 

       'Payables Open Interface Import'  -- <change it>

   AND TRUNC(fcr.request_date) >= TRUNC(SYSDATE-7)  -- <change it>

 ORDER BY fu.user_name, fcr.request_date DESC;

Query to find Application Short Name of a module

The following query lists all the applications related information. This query can be used to find the APPLICATION_SHORT_NAME of a module (eg. Payables, Receivables, Order Management, etc.) that are often used for downloading FNDLOAD LDT files, adding responsibility to a user and many more.

You can uncomment the FAT.APPLICATION_NAME condition (very bottom line of the query) to learn about a particular module. In this case, I used "Payables".

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

-- Query to find all APPLICATION (module) information

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

SELECT fa.application_id           "Application ID",

       fat.application_name        "Application Name",

       fa.application_short_name   "Application Short Name",

       fa.basepath                 "Basepath"

  FROM fnd_application     fa,

       fnd_application_tl  fat

 WHERE fa.application_id = fat.application_id

   AND fat.language      = USERENV('LANG')

   -- AND fat.application_name = 'Payables'  -- <change it>

 ORDER BY fat.application_name;

Query to find runtime of a concurrent program

The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little modification to this query, you can track which concurrent programs take (very) long time to complete, and may need performance tuning.

Change the concurrent program name (tl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program is "Autoinvoice Import Program". You can also uncomment the "&Start_Date" line to get the list for a specific date.

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

-- Query to find runtime for a concurrent program

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

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date -

           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

   AND rq.actual_start_date IS NOT NULL

   AND rq.actual_completion_date IS NOT NULL

   AND tl.user_concurrent_program_name = 'Autoinvoice Import Program'  -- <change it>

   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date

 ORDER BY rq.request_id DESC;

Query to find Concurrent Programs associated with a Value Set

Following query finds all the concurrent program(s) that are associated with a value set. Thus, before you want to make any change to that value set, you should be able to find what concurrent programs will be affected by your modification, so you can notify the concerned parties.

Change the value set name (ffvs.flex_value_set_name, see below) according to your search criteria. In this example, I used "CONV_TYPES" as my value set name.

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

-- Query to find Concurrent Programs associated with a Value Set

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

SELECT fdfcuv.flex_value_set_id           "Value Set ID",

       ffvs.flex_value_set_name           "Value Set Name",

       flv.meaning                        "Default Type",

       fdfcuv.default_value               "Default Value",

       fcpl.user_concurrent_program_name  "Concurrent Program Name",

       fcp.concurrent_program_name        "Program Short Name",

       fdfcuv.column_seq_num              "Column Seq #",

       fdfcuv.end_user_column_name        "Parameter Name",

       fdfcuv.form_left_prompt            "Prompt Name",

       fdfcuv.enabled_flag                "Enabled Flag",

       fdfcuv.required_flag               "Required Flag",

       fdfcuv.display_flag                "Display Flag"

  FROM fnd_concurrent_programs      fcp,

       fnd_concurrent_programs_tl   fcpl,

       fnd_descr_flex_col_usage_vl  fdfcuv,

       fnd_flex_value_sets          ffvs,

       fnd_lookup_values            flv

 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id

   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name

   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id

   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'

   AND flv.lookup_code(+)                 =  fdfcuv.default_type

   AND fcpl.LANGUAGE                      =  USERENV('LANG')

   AND flv.LANGUAGE(+)                    =  USERENV('LANG')

   AND fdfcuv.enabled_flag                =  'Y'

   AND ffvs.flex_value_set_name           LIKE  '%CONV_TYPES'  -- <change it>

 ORDER BY fcpl.user_concurrent_program_name;

Query to find Parameters and Value Sets associated with a Concurrent Program

Following query finds the parameters and the value sets that are associated with a Concurrent Program. Change concurrent program name (fcpl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program name is "XX AR Conversion Program".

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

-- Query to find Parameters and Value Sets associated with a Concurrent Program

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

SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",

       fcp.concurrent_program_name        "Program Short Name",

       fdfcuv.column_seq_num              "Column Seq #",

       fdfcuv.end_user_column_name        "Parameter Name",

       fdfcuv.form_left_prompt            "Prompt Name",

       fdfcuv.enabled_flag                "Enabled Flag",

       fdfcuv.required_flag               "Required Flag",

       fdfcuv.display_flag                "Display Flag",

       fdfcuv.flex_value_set_id           "Value Set ID",

       ffvs.flex_value_set_name           "Value Set Name",

       flv.meaning                        "Default Type",

       fdfcuv.default_value               "Default Value"

  FROM fnd_concurrent_programs      fcp,

       fnd_concurrent_programs_tl   fcpl,

       fnd_descr_flex_col_usage_vl  fdfcuv,

       fnd_flex_value_sets          ffvs,

       fnd_lookup_values            flv

 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id

   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name

   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id

   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'

   AND flv.lookup_code(+)                 =  fdfcuv.default_type

   AND fcpl.LANGUAGE                      =  USERENV('LANG')

   AND flv.LANGUAGE(+)                    =  USERENV('LANG')

   AND fdfcuv.enabled_flag                =  'Y'

   AND fcpl.user_concurrent_program_name  =  'Conc Prog Name'  -- <change it>

 ORDER BY fdfcuv.column_seq_num;