Friday 30 May 2014

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;

 

1 comment:

  1. Madhya Pradesh Board Class 1st Text books are the perfect source for students to help with their overall development, as it explains about various topics under it.The MP Board Class 1st Mathematics, English Reader, Hind Textbook covers topics in depth, providing a foundation in the subject for the students. MP Board 1st Class Revised Syllabus Students can also download here MP Board Class 1st All Subject Textbook, these on their laptops, mobile phones or other portable devices. These topics in the MP Board class 1st Maths textbooks are based on the MP Board Syllabus Class 1st.

    ReplyDelete