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

       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_executables_form_v      efv,

       fnd_concurrent_programs_vl  cpv,

       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;