1 Reply Latest reply on May 3, 2020 8:03 AM by Andy Haack

    List of EBS reports for a responsibility (Oracle reports, XML Publisher and BI Publisher)

    4227594

      We are on 12.1.X and needs to give a list of various reports available for a responsibility /application

       

      For Traditional  oracle reports , I can use the  FND_EXECUTABLES_FORM_V and EXECUTION_METHOD_CODE = 'P'

      will give the traditional reports. Does any one know the sql for the Bi Publisher/XML report publisher

       

      Thanks

      Rajesh Alex

        • 1. Re: List of EBS reports for a responsibility (Oracle reports, XML Publisher and BI Publisher)
          Andy Haack

          Hi Rajesh, BI/XML Publisher reports are found in xdo_ds_definitions_vl, which is the data definition that you can see in the XML Publisher responsibility. In addition to the data definition, BI Publisher reports also require a concurrent program to be setup with the same code. You can use the following SQL, which is a simplified versison of our XDO Publisher Data Definitions blitz report

           

          select

          xddv.data_source_name,

          xddv.data_source_code,

          xddv.application_name application,

          case when sysdate between xddv.start_date and nvl(xddv.end_date,sysdate) then 'Yes' else 'No' end active,

          (select distinct listagg(xl0.file_name,', ') within group (order by xl0.lob_type) over (partition by xl0.application_short_name, xl0.lob_code) files from xdo_lobs xl0 where xddv.application_short_name=xl0.application_short_name and xddv.data_source_code=xl0.lob_code and xl0.lob_type='XML_SCHEMA') xml_schema,

          (select distinct listagg(xl0.file_name,', ') within group (order by xl0.lob_type) over (partition by xl0.application_short_name, xl0.lob_code) files from xdo_lobs xl0 where xddv.application_short_name=xl0.application_short_name and xddv.data_source_code=xl0.lob_code and xl0.lob_type='XML_SAMPLE') preview_data,

          (select distinct listagg(xl0.file_name,', ') within group (order by xl0.lob_type) over (partition by xl0.application_short_name, xl0.lob_code) files from xdo_lobs xl0 where xddv.application_short_name=xl0.application_short_name and xddv.data_source_code=xl0.lob_code and xl0.lob_type='BURSTING_FILE') bursting_control_file,

          fcpv.user_concurrent_program_name,

          case when

          fcpv.srs_flag in ('Y','Q') and

          fcpv.enabled_flag='Y' and

          (

          exists (select null from fnd_request_group_units frgu where frgu.request_unit_type='P' and fcpv.application_id=frgu.unit_application_id and fcpv.concurrent_program_id=frgu.request_unit_id) or

          exists (select null from fnd_request_group_units frgu where frgu.request_unit_type='A' and fcpv.application_id=frgu.unit_application_id and fcpv.application_id=frgu.request_unit_id)

          )

          then 'Yes' end conc_enabled,

          fcpv.output_file_type,

          fev.executable_name,

          (select flv.meaning from fnd_lookup_values flv where fev.execution_method_code=flv.lookup_code and flv.lookup_type='CP_EXECUTION_METHOD_CODE' and flv.language=userenv('lang') and flv.view_application_id=0 and flv.security_group_id=0) execution_method,

          case when fev.description is null and fev.user_executable_name<>fev.executable_name then fev.user_executable_name else fev.description end executable_description,

          fev.execution_file_name,

          fev.execution_file_path,

          (select fu.user_name from fnd_user fu where xddv.created_by=fu.user_id) created_by,

          xddv.creation_date,

          (select fu.user_name from fnd_user fu where xddv.last_updated_by=fu.user_id) last_updated_by,

          xddv.last_update_date

          from

          (select xddv.*, fav.application_name, fav.application_id from xdo_ds_definitions_vl xddv, fnd_application_vl fav where xddv.application_short_name=fav.application_short_name) xddv,

          fnd_concurrent_programs_vl fcpv,

          fnd_executables_vl fev

          where

          sysdate between xddv.start_date and nvl(xddv.end_date,sysdate) and

          1=1 and

          xddv.data_source_code=fcpv.concurrent_program_name(+) and

          xddv.application_id=fcpv.application_id(+) and

          fcpv.executable_id=fev.executable_id(+) and

          fcpv.executable_application_id=fev.application_id(+)

           

          To check from which responsibilities these BI Publisher reports can be run, you can use the FND Access Control report