Query to get concurrent program, data definition, template and rtf name name from executable

SELECT DISTINCT user_concurrent_program_name
              , fcp.concurrent_program_name
              , fet.user_executable_name executable_name
              , fe.executable_name executable_short_name
              , fet.description
              , fe.execution_file_name
              , fcp.enabled_flag
              , xtt.template_name
              , xddv.data_source_name
             , xddv.data_source_code
              , xl.file_name
              , xl.lob_code
              , xl.LANGUAGE
              , xl.territory
              , xl.creation_date
              , xl.last_update_date
           FROM fnd_concurrent_programs_tl fcpt
              , fnd_concurrent_programs fcp
              , xdo_templates_b xtb
              , xdo_templates_tl xtt
              , xdo_ds_definitions_vl xddv
              , xdo_lobs xl
              , apps.fnd_executables fe
              , apps.fnd_executables_tl fet
          WHERE fcpt.concurrent_program_id = fcp.concurrent_program_id
            AND xtb.data_source_code = fcp.concurrent_program_name
            AND xtb.template_code = xtt.template_code
            AND xtb.data_source_code = xddv.data_source_code
            AND xl.lob_code = xtb.template_code
            AND xl.xdo_file_type = xtb.template_type_code
            AND fet.application_id = fe.application_id
            AND fet.executable_id = fe.executable_id
            AND fcp.application_id = fe.application_id
            AND fcp.executable_id = fe.executable_id
            AND UPPER (fe.execution_file_name) IN  ('XXX_SUBS_QUOTE_RPT')

Query to find lock records

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine,
   b.* 
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

Query to check privilege on package or table

SELECT *
FROM dba_tab_privs
WHERE 1 = 1
AND table_name IN  ('XXX_PACKAGE_NAME')

Query to get Concurrent Program Name from rtf name (link between XDO_lobs and fnd_concurrent_program))

SELECT DISTINCT user_concurrent_program_name
              , fcp.concurrent_program_name
              , xtt.template_name
              , xddv.data_source_name
              , xddv.data_source_code
              , xl.file_name
              , xl.lob_code
              , xl.LANGUAGE
              , xl.territory
              , xl.creation_date
              , xl.last_update_date
           FROM fnd_concurrent_programs_tl fcpt
              , fnd_concurrent_programs fcp
              , xdo_templates_b xtb
              , xdo_templates_tl xtt
              , xdo_ds_definitions_vl xddv
              , xdo_lobs xl
WHERE           fcpt.concurrent_program_id = fcp.concurrent_program_id
            AND xtb.data_source_code = fcp.concurrent_program_name
            AND xtb.template_code = xtt.template_code
            AND xtb.data_source_code = xddv.data_source_code
            AND xl.lob_code = xtb.template_code
            AND xl.xdo_file_type = xtb.template_type_code
            AND xl.file_name LIKE '%XXX_RAXINV_RTF_JAPAN_V16.rtf%'