Queries to Get a Complete List of Available XML Publisher Reports

Different products of the Oracle E-Business Suite use XML Publisher Reports. Often it may be required to obtain a comprehensive list of all the XML Publisher Reports in the instance scattered across the different products.
Below are the queries that can be helpful in identifying all such reports in the instance:

A] XML Publisher Reports (related to Concurrent Program):

 

SELECT fat.application_name,

         fcpt.user_concurrent_program_name conc_prog_user_name,

         fcp.concurrent_program_name conc_prog_name,

         xddt.data_source_name data_definition_name,

         xdd.application_short_name data_definition_app_code,

         xdd.data_source_code data_definition_code,

         xtbt.template_name template_name,

         xtb.application_short_name template_app_code,

         xtb.template_code template_code,

         xtbt.description template_description

    FROM xdo_ds_definitions_b xdd,

         xdo_ds_definitions_tl xddt,

         xdo_templates_b xtb,

         xdo_templates_tl xtbt,

         fnd_concurrent_programs fcp,

         fnd_concurrent_programs_tl fcpt,

         fnd_application fa,

         fnd_application fa2,

         fnd_application_tl fat

   WHERE     xdd.application_short_name = xddt.application_short_name

         AND xdd.data_source_code = xddt.data_source_code

         AND xddt.LANGUAGE = 'US'

         AND xtb.application_short_name = xtbt.application_short_name

         AND xtb.template_code = xtbt.template_code

         AND xtbt.LANGUAGE = 'US'

         AND xtb.ds_app_short_name = xdd.application_short_name

         AND xtb.data_source_code = xdd.data_source_code

         AND fcp.application_id = fcpt.application_id

         AND fcp.concurrent_program_id = fcpt.concurrent_program_id

         AND fcpt.LANGUAGE = 'US'

         AND fcp.output_file_type = 'XML'

         AND xdd.application_short_name = fa.application_short_name

         AND fa.application_id = fcp.application_id

         AND xdd.data_source_code = fcp.concurrent_program_name

         AND fa2.application_id = fcp.application_id

         AND fat.application_id = fa2.application_id

         AND fat.LANGUAGE = 'US'

ORDER BY fat.application_name,

         fcpt.user_concurrent_program_name,

         xddt.data_source_name,

         xtbt.template_name;

 

B] XML Publisher Reports (Not related to Concurrent Program):

 

SELECT fat.application_name,

         xddt.data_source_name data_definition_name,

         xdd.application_short_name data_definition_app_code,

         xdd.data_source_code data_definition_code,

         xtbt.template_name template_name,

         xtb.application_short_name template_app_code,

         xtb.template_code template_code,

         xtbt.description template_description

    FROM xdo_ds_definitions_b xdd,

         xdo_ds_definitions_tl xddt,

         xdo_templates_b xtb,

         xdo_templates_tl xtbt,

         fnd_application fa2,

         fnd_application_tl fat

   WHERE     xdd.application_short_name = xddt.application_short_name

         AND xdd.data_source_code = xddt.data_source_code

         AND xddt.LANGUAGE = 'US'

         AND xtb.application_short_name = xtbt.application_short_name

         AND xtb.template_code = xtbt.template_code

         AND xtbt.LANGUAGE = 'US'

         AND xtb.ds_app_short_name = xdd.application_short_name

         AND xtb.data_source_code = xdd.data_source_code

         AND fat.application_id = fa2.application_id

         AND fat.LANGUAGE = 'US'

         AND fa2.application_short_name = xdd.application_short_name

         AND NOT EXISTS

                    (SELECT 'x'

                       FROM fnd_concurrent_programs fcp, fnd_application fa

                      WHERE fcp.output_file_type = 'XML'

                            AND fa.application_id = fcp.application_id

                            AND xdd.data_source_code =

                                   fcp.concurrent_program_name

                            AND xdd.application_short_name =

                                   fa.application_short_name)

ORDER BY fat.application_name,

         xddt.data_source_name,

         xtbt.template_name;