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 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 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  =  'XX AR Conversion Program'  -- <change it>

 ORDER BY fdfcuv.column_seq_num;

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 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 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",

       frt.responsibility_name                "Responsibility Used",

       fcr.request_id                         "Request ID",

       fcpt.user_concurrent_program_name      "Concurrent Program Name",

       flv2.meaning                           "Phase",

       flv1.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_tl       frt,

       fnd_concurrent_requests     fcr,

       fnd_concurrent_programs_tl  fcpt,

       fnd_lookup_values           flv1,

       fnd_lookup_values           flv2

 WHERE

       1=1

   --

   AND flv1.lookup_code           =  fcr.status_code

   AND flv1.lookup_type           =  'CP_STATUS_CODE'

   AND flv1.enabled_flag          =  'Y'

   AND flv1.view_application_id   <> 0

   --

   AND flv2.lookup_code           =  fcr.phase_code

   AND flv2.lookup_type           =  'CP_PHASE_CODE'

   AND flv2.enabled_flag          =  'Y'

   AND flv2.view_application_id   <> 0

   --

   AND fu.user_id                 =  fcr.requested_by

   AND fcr.concurrent_program_id  =  fcpt.concurrent_program_id

   AND fcr.responsibility_id      =  frt.responsibility_id

   AND frt.LANGUAGE               =  USERENV ('LANG')

   AND fcpt.LANGUAGE              =  USERENV ('LANG')

   -- AND fcr.request_id = 7137350  -- <change it>

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

 ORDER BY fcr.request_date DESC;