select
orr.report_id,
orr.report_name,
orr.report_description,
orr.report_folder_name,
orr.domain_name,
orr.report_type,
orr.widget_type,
orr.inherited_object_key business_object_key,
ordv.category_name,
ordv.logical_model_name,
ordv.physical_model_name,
ordv.datasource_name,
orr.report_language,
orr.created_by,
orr.created_date,
orr.last_modified_by,
orr.last_modified_date
from (
select
orr.id report_id,
olf_repname.property_value report_name,
olf_repdesc.property_value report_description,
olf_repfolname.property_value report_folder_name,
olf_domain.property_value domain_name,
case
when orr.widget_type = 'oper' then 'Data Report'
when orr.widget_type in ('Pivot-grid', 'pivot-grid') then 'Pivot Report'
when orr.widget_type = 'custom' then 'Custom Report'
when orr.widget_type = 'dashboard' then 'Dashboard Report'
when orr.widget_type = 'script' then 'Script Report'
when orr.widget_type = 'pub' then 'Publisher Report'
else 'Analysis Report'
end report_type,
orr.widget_type,
omo_inh_obj.obj_key inherited_object_key,
olf_repname.locale report_language,
xxen_orb_util.orb_user(omo_rep.created_by) created_by,
xxen_util.client_time(omo_rep.created_date) created_date,
xxen_orb_util.orb_user(omo_rep.last_modified_by) last_modified_by,
xxen_util.client_time(omo_rep.last_modified_date) last_modified_date
from
xxorb.orb_report orr,
xxorb.orb_meta_object omo_rep,
xxorb.orb_meta_object omo_inh_obj,
xxorb.orb_meta_object omo_lm,
xxorb.orb_meta_object omo_domain,
xxorb.orb_localizedstring_flat olf_repname,
xxorb.orb_meta_object omo_rep_folder,
xxorb.orb_localizedstring_flat olf_repdesc,
xxorb.orb_localizedstring_flat olf_repfolname,
xxorb.orb_localizedstring_flat olf_domain
where
omo_rep.id = orr.id and
omo_rep.parent_id = omo_rep_folder.id(+) and
olf_repname.meta_object_id = omo_rep.id and
omo_rep.inherited_meta_obj_id = omo_inh_obj.id and
omo_inh_obj.parent_id = omo_lm.id and
omo_lm.parent_id = omo_domain.id and
olf_domain.meta_object_id = omo_domain.id and
olf_domain.property_key = 'name' and
olf_repname.property_key = 'name' and
omo_rep.id = olf_repdesc.meta_object_id(+) and
olf_repdesc.property_key(+) = 'description' and
omo_rep_folder.id = olf_repfolname.meta_object_id(+) and
olf_repfolname.property_key(+) = 'name' and
omo_rep.obj_type = 13 and
omo_rep_folder.obj_type(+) = 12 and
omo_inh_obj.obj_type = 10 and
omo_lm.obj_type = 6 and
omo_domain.obj_type = 1
union all
select
orr.id report_id,
olf_repname.property_value report_name,
olf_repdesc.property_value report_description,
olf_repfolname.property_value report_folder_name,
null domain_name,
case
when orr.widget_type = 'oper' then 'Data Report'
when orr.widget_type in ('Pivot-grid', 'pivot-grid') then 'Pivot Report'
when orr.widget_type = 'custom' then 'Custom Report'
when orr.widget_type = 'dashboard' then 'Dashboard Report'
when orr.widget_type = 'script' then 'Script Report'
when orr.widget_type = 'pub' then 'Publisher Report'
else 'Analysis Report'
end report_type,
orr.widget_type,
null inherited_object_key,
olf_repname.locale report_language,
xxen_orb_util.orb_user(omo_rep.created_by) created_by,
xxen_util.client_time(omo_rep.created_date) created_date,
xxen_orb_util.orb_user(omo_rep.last_modified_by) last_modified_by,
xxen_util.client_time(omo_rep.last_modified_date) last_modified_date
from
xxorb.orb_report orr,
xxorb.orb_meta_object omo_rep,
xxorb.orb_localizedstring_flat olf_repname,
xxorb.orb_meta_object omo_rep_folder,
xxorb.orb_localizedstring_flat olf_repdesc,
xxorb.orb_localizedstring_flat olf_repfolname
where
omo_rep.id = orr.id and
omo_rep.parent_id = omo_rep_folder.id(+) and
olf_repname.meta_object_id(+) = omo_rep.id and
omo_rep.inherited_meta_obj_id is null and
olf_repname.property_key(+) = 'name' and
omo_rep.id = olf_repdesc.meta_object_id(+) and
olf_repdesc.property_key(+) = 'description' and
omo_rep_folder.id = olf_repfolname.meta_object_id(+) and
olf_repfolname.property_key(+) = 'name' and
omo_rep.obj_type = 13 and
orr.widget_type<>'oper' and
omo_rep_folder.obj_type(+) = 12
) orr,
xxorb.orb_report_domain_v ordv
where 1=1 and
orr.report_id=ordv.report_id(+) |
