Concurrent Programs – Definitions

Previous SQLs have focussed on extracting data relating to when concurrent programs run – where the data is held in the fnd_concurrent_requests table.

Sometimes you might want to review how a concurrent program is defined, in terms of which executables it is linked to, and so on. I find this handy if I know a program is linked to a PL/SQL Stored Procedure, as you can extract that info via this SQL, and then go away and extract the definition for that code.

-- ##############################################################################
--       CONCURRENT REQUEST BASIC DETAILS
-- ############################################################################*/

SELECT fcpt.user_concurrent_program_name
     , fcp.creation_date
     , fcp.application_id
     , fcp.concurrent_program_id
     , fcp.enabled_flag
     , fcp.concurrent_program_name
     , fcp.last_update_date up_dt
     , fu.description up_by
     , fcp.output_file_type
     , fcp.enable_trace
     , fcp.execution_options
     , fat.application_name application
     , fcpt.description prog_description
     , fe.executable_name
     , DECODE (fe.execution_method_code , 'A', 'Spawned' , 'B', 'Request Set Stage Function' , 'E', 'Perl Concurrent Program' , 'H', 'Host' , 'I', 'PL/SQL Stored Procedure' , 'J', 'Java Stored Procedure' , 'K', 'Java Concurrent Program' , 'L', 'SQL*Loader' , 'M', 'Multi Language Function' , 'P', 'Oracle Reports' , 'Q', 'SQL*Plus' , 'S', 'Immediate' , 'Other') execution_method_code
     , fet.user_executable_name
     , fet.description executable_description
     , fe.execution_file_name
     , (SELECT COUNT(*) FROM fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) job_count
  FROM fnd_concurrent_programs fcp
       JOIN fnd_concurrent_programs_tl fcpt ON fcp.concurrent_program_id = fcpt.concurrent_program_id
       JOIN fnd_application_tl fat           ON fcp.application_id =        fat.application_id
  LEFT JOIN fnd_executables fe               ON fcp.executable_id =         fe.executable_id AND fe.application_id = fcp.application_id
  LEFT JOIN fnd_executables_tl fet           ON fe.executable_id =           fet.executable_id AND fet.application_id = fe.application_id
  JOIN fnd_user fu                      ON fcp.last_updated_by =       fu.user_id 
 WHERE 1 = 1
   AND fcpt.user_concurrent_program_name LIKE '%Confirm%'
--   AND fcp.application_id = 222
--   AND fcp.creation_date > '01-JAN-2016'
--   AND fcp.enable_trace = 'Y'
--   AND fat.application_name = 'General Ledger'
   AND 1 = 1;