Concurrent Program Details including Template Details if exists

SELECT ROWNUM

,fat.application_name application_name

,fat.application_short_name appl_short_name

,fcpt.user_concurrent_program_name concurrent_program_name

,fcp.concurrent_program_name concurrent_program_short_name

,fcp.output_file_type output_type

, fe.executable_name executable_name

,flv.meaning execution_method_type

,fat1.application_name executable_application_name

,fat1.application_short_name executable_appl_short_name

,xddt.data_source_name data_definition_name

,xtb.template_code template_code

, xtb.template_type_code template_type

,xl.file_content_type file_content

, xl.file_name rtf_file_name

, (SELECT user_name

     FROM fnd_user

     WHERE user_id = xtb.created_by) creation_user

  FROM fnd_concurrent_programs_tl fcpt,

       fnd_concurrent_programs fcp,

       fnd_executables fe,

       fnd_application_vl fat,

       fnd_application_vl fat1,

       fnd_lookup_values flv,

       xdo_ds_definitions_tl xddt,

       xdo_templates_b xtb,

       xdo_lobs xl

 WHERE fcp.concurrent_program_id = fcpt.concurrent_program_id

   AND fat.application_id = fcpt.application_id

   AND fcp.executable_id = fe.executable_id

   AND fcp.executable_application_id = fe.application_id

   AND fat1.application_id = fe.application_id

   AND xddt.data_source_code(+) = fcp.concurrent_program_name

   AND flv.lookup_type(+) = ‘CP_EXECUTION_METHOD_CODE’

   AND flv.lookup_code(+) = fe.execution_method_code

   AND xtb.data_source_code(+) = xddt.data_source_code

   AND xl.lob_code(+) = xtb.template_code

   AND xl.lob_type(+) = ‘TEMPLATE_SOURCE’

   AND flv.enabled_flag = ‘Y’

   –AND fcp.output_file_type=’XML’

   AND fcp.enabled_flag=’Y’

   AND NVL (flv.end_date_active, SYSDATE) >= SYSDATE 

   AND fcpt.user_concurrent_program_name=:p_concurrent_program_name