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 Apr 27, 2020 7:24 PM

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 May 3, 2020 8:03 AM (in response to 4227594)

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