SQL Script to find Concurrent program details with Path, program type, Executable details
SELECT fcp.concurrent_program_id
,fcp.concurrent_program_name
,fcpt.user_concurrent_program_name usr_conc_program
,fcp.enabled_flag flag
,fcp.output_file_type output_TYPE
,fe.executable_name
,fcp.execution_method_code program_method_code
,(SELECT meaning FROM apps.fnd_lookup_values WHERE lookup_code = fcp.execution_method_code AND lookup_type = ‘CP_EXECUTION_METHOD_CODE’) prog_method
,fe.execution_method_code executable_method_code
,(SELECT meaning FROM apps.fnd_lookup_values WHERE lookup_code = fe.execution_method_code AND lookup_type = ‘CP_EXECUTION_METHOD_CODE’) exec_method
,fe.execution_file_name
,fa.application_short_name
,fat.application_name
,fa.basepath top
,(select value from apps.fnd_env_context where variable_name = fa.basepath and concurrent_process_id = (select max(concurrent_process_id) from apps.fnd_env_context)) ||’/reports/US’ path
,fcp.creation_date
,(select user_name||’ : ‘||description from apps.fnd_user where user_id = fcp.created_by) created_by
,fcp.last_update_date
,(select user_name||’ : ‘||description from apps.fnd_user where user_id = fcp.last_updated_by) updated_by
FROM apps.fnd_concurrent_programs_tl fcpt
,apps.fnd_concurrent_programs fcp
,apps.fnd_executables fe
,apps.fnd_application_tl fat
,apps.fnd_application fa
WHERE 1 = 1
and fcpt.application_id = fcp.application_id
and fcpt.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fe.application_id = fa.application_id
and fat.application_id = fa.application_id
AND fcp.enabled_flag = ‘Y’
and upper(fcpt.user_concurrent_program_name) = ‘&prog_name_upper_case’–like ‘%EVENT%’
;