Reports2017-11-18T12:27:27+00:00

XDO Publisher Datasources

Description
Categories: Application, Enginatics, Setup
XML Publisher datasources, associated concurrent programs, executables and templates. To also show template information, enter a template name or wildcard % This report also shows the source data SQL statement extracted from the publisher data template XML file which is quite useful to quickly access and review the SQL from Oracle standard XML reports and use them as templates for new blitz repor... XML Publisher datasources, associated concurrent programs, executables and templates.
To also show template information, enter a template name or wildcard %

This report also shows the source data SQL statement extracted from the publisher data template XML file which is quite useful to quickly access and review the SQL from Oracle standard XML reports and use them as templates for new blitz report creations.
Parameter 'Data Definition Search Text' performs a full text search through the data template file so that you could for example enter a particular table name to list all XML publisher reports and their SQLs selecting from that table.
  more

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,
xl.file_name data_template,
(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,
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,
xxen_util.meaning(fev.execution_method_code,'CP_EXECUTION_METHOD_CODE',0) execution_method,
fev.execution_file_name,
fev.execution_file_path,
&col_template
x.*,
&col_sql
--xl.text data_template_xml
xl.application_short_name
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,
(select xtv.* from xdo_templates_vl xtv where '&enable_template'='Y') xtv,
(select
xl.*,
xxen_util.blob_to_clob(xl.file_data) text,
xmltype(xxen_util.blob_to_clob(xl.file_data)) xml
from
xdo_lobs xl
where
xl.lob_type='DATA_TEMPLATE' and
xl.language='00' and
xl.territory='00' and
xl.file_data is not null and
xxen_util.is_xml(xxen_util.blob_to_clob(xl.file_data))='Y'
) xl,
xmltable('/dataTemplate' passing xl.xml columns
data_template_name path '@name',
data_template_description path '@description',
package_name path '@defaultPackage'
)(+) x,
xmltable('/dataTemplate["&enable_sql"="Y"]/dataQuery/*[fn:lower-case(name())="sqlstatement"]' passing xl.xml columns
sql_number for ordinality,
sql_name path '@name',
sql_text clob path 'text()[1]'
)(+) y
where
1=1 and
xddv.application_short_name=xtv.ds_app_short_name(+) and
xddv.data_source_code=xtv.data_source_code(+) and
xtv.template_status(+)='E' 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(+) and
xddv.application_short_name=xl.application_short_name(+) and
xddv.data_source_code=xl.lob_code(+)

Parameter Name SQL text Validation
Show Datasource SQLs
Y
LOV
Show Active only
sysdate between xddv.start_date and nvl(xddv.end_date,sysdate)
LOV
Show enabled concurrents only
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)
)
LOV
Datasource Code
xddv.data_source_code=:data_source_code
LOV
Show Datasource SQLs
y.sql_number,
y.sql_name,
trim(chr(10) from y.sql_text) sql_text,
Show Active only
not (sysdate between xddv.start_date and nvl(xddv.end_date,sysdate))
Template Name
Y
Template Name
xtv.template_name,
xtv.template_code,
xtv.template_type_code,
xtv.default_language,
xtv.default_territory,
(
select
xl.file_name
from
xdo_lobs xl
where
xtv.template_code=xl.lob_code and
xtv.application_short_name=xl.application_short_name and
(
(
xl.lob_type='TEMPLATE' and
xl.xdo_file_type<>'RTF' and
xtv.template_type_code=xl.xdo_file_type
or
xl.lob_type='TEMPLATE_SOURCE' and
xl.xdo_file_type in ('RTF','RTF-ETEXT')
) and
xtv.default_language=xl.language and
xtv.default_territory=xl.territory
or
xl.lob_type='TEMPLATE_SOURCE' and
xl.xdo_file_type='RTF' and
xtv.mls_language=xl.language and
xtv.mls_territory=xl.territory and
exists (
select null from xdo_lobs xl2
where xl2.lob_type='MLS_TEMPLATE' and
xtv.application_short_name=xl2.application_short_name and
xtv.template_code=xl2.lob_code and
xtv.default_language=xl2.language and
xtv.default_territory=xl2.territory) and
not exists (
select null from xdo_lobs xl3
where
xl3.lob_type='TEMPLATE_SOURCE' and
xtv.application_short_name=xl3.application_short_name and
xtv.template_code=xl3.lob_code and
xtv.default_language=xl3.language and
xtv.default_territory=xl3.territory)
)
) default_template_file,
(select xl.file_name from xdo_lobs xl where xl.lob_type='TEMPLATE_SOURCE' and xtv.application_short_name=xl.application_short_name and xtv.template_code=xl.lob_code and xtv.mls_language=xl.language and xtv.mls_territory=xl.territory) mls_template_file,
(select filv.name from fnd_iso_languages_vl filv where xtv.default_language=filv.iso_language_2) default_file_lang,
decode(xtv.default_territory,'00','',(select ftv.territory_short_name from fnd_territories_vl ftv where xtv.default_territory=ftv.territory_code)) default_file_territory,
Data Definition Search Text
xddv.data_source_code in (
select
xl.lob_code
from
xdo_lobs xl
where
(
dbms_lob.instr(xl.file_data,utl_raw.cast_to_raw(trim('%' from :search_text)))>0 or
dbms_lob.instr(xl.file_data,utl_raw.cast_to_raw(trim('%' from upper(:search_text))))>0 or
dbms_lob.instr(xl.file_data,utl_raw.cast_to_raw(trim('%' from lower(:search_text))))>0 or
dbms_lob.instr(xl.file_data,utl_raw.cast_to_raw(trim('%' from initcap(:search_text))))>0
) and
xl.lob_type='DATA_TEMPLATE'
)
Template Name
upper(xtv.template_name) like upper(:template_name)
LOV
Concurrent Program Name
upper(fcpv.user_concurrent_program_name) like upper(:user_concurrent_program_name)
LOV
Exclude Datasource Codes like
lower(xddv.data_source_code) not like lower(:data_source_code)
LOV
Datasource Name
upper(xddv.data_source_name) like upper(:data_source_name)
LOV

By continuing to use the site, you agree to the use of cookies. Accept