SQL query to get concurrent program details in Oracle Apps

Run the below query and enter program name

select 
      a.concurrent_program_id,
      a.concurrent_program_name, 
      b.user_concurrent_program_name,
      c.executable_name,
      c.execution_method_code,
      (   select meaning  FROM apps.fnd_lookups a
          where lookup_type like 'CP_EXECUTION_METHOD_CODE' 

          and lookup_code= c.execution_method_code
      ) type,
      c.execution_file_name
from 
      apps.fnd_concurrent_programs a,
      apps.fnd_concurrent_programs_tl b,
      apps.fnd_executables c
where 
      a.concurrent_program_id              = b.concurrent_program_id
      and a.executable_id                  = c.executable_id

      and a.executable_application_id      = c.application_id
      and b.user_concurrent_program_name   = '&Program_Name'; 

Following query gets list of execution method code available

select 
      meaning,
      lookup_code,
      enabled_flag
from 
      apps.fnd_lookups 
where 
      lookup_type like 'CP_EXECUTION_METHOD_CODE';

To also get application name and top location in case of programs of type host (shell scripts), use the below script. Host files will be stored in TOP_LOCATION\bin folder

select distinct
      a.concurrent_program_id,
      a.concurrent_program_name, 
      b.user_concurrent_program_name,
      c.executable_name,
      c.execution_method_code,
      (   select meaning FROM apps.fnd_lookups a
          where lookup_type like 'CP_EXECUTION_METHOD_CODE' 

          and lookup_code= c.execution_method_code
      ) type,
      c.execution_file_name,
      d.application_short_name,
      e.application_name,
      d.basepath top_name,
      f.value top_location
from 
      apps.fnd_concurrent_programs a,
      apps.fnd_concurrent_programs_tl b,
      apps.fnd_executables c,
      apps.fnd_application d,
      apps.fnd_application_tl e,
      apps.fnd_env_context f
where 
      a.application_id                    = d.application_id
      and e.application_id                = d.application_id

      and a.executable_application_id     = c.application_id
      and d.basepath                      = f.variable_name
      and a.concurrent_program_id         = b.concurrent_program_id
      and a.executable_id                 = c.executable_id
      and b.user_concurrent_program_name  ='&program_name';