select
orb_lm_details.report_id,
orb_lm_details.report_name,
orb_lm_details.domain_name,
orb_lm_details.business_object_name,
orb_lm_details.logical_model_name,
orb_lm_details.physical_model_name,
orb_lm_details.datasource_name,
orb_lm_details.logical_model_obj_name,
orb_lm_details.physical_model_obj_name,
orb_lm_details.logical_model_obj_key,
orb_lm_details.physical_model_obj_key,
orb_lm_details.logical_model_obj_id,
orb_lm_details.physical_model_obj_id,
orb_lm_details.table_type,
orb_lm_details.target_table_type,
orb_lm_details.target_schema,
case when lengthb(orb_lm_details.target_table)=3999 and orb_lm_details.target_table_type='INLINE_SQL' then
xxen_orb_util.get_inline_sql_obj_blob(orb_lm_details.physical_model_obj_id)
when lengthb(orb_lm_details.target_table)<3999 and orb_lm_details.target_table_type='INLINE_SQL' then
to_clob(orb_lm_details.target_table)
else
to_clob(orb_lm_details.target_table)
end target_table
from
(
select
report.report_id,
report.report_name,
inherited_obj.obj_key business_object_key,
category_name.property_value business_object_name,
domain_name.property_value domain_name,
logical_physical_model_link.id logical_model_obj_id,
logical_physical_model_link.obj_key logical_model_obj_key,
logical_model_base.id logical_model_id,
logical_model.obj_key logical_model_key,
logical_model_name.property_value logical_model_name,
physical_model_object.id physical_model_obj_id,
physical_model_object.obj_key physical_model_obj_key,
physical_model_obj_name.property_value physical_model_obj_name,
logical_model_obj_name.property_value logical_model_obj_name,
physical_model_name.property_value physical_model_name,
physical_model.id physical_model_id,
physical_model.obj_key physical_model_key,
datasource_name.property_value datasource_name,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=physical_model_object.id and opf.property_key='tt')table_type,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=physical_model_object.id and opf.property_key='sts')target_schema,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=physical_model_object.id and opf.property_key='stt')target_table,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=physical_model_object.id and opf.property_key='sttt')target_table_type
from
xxorb.orb_report_v report,
xxorb.orb_meta_object inherited_obj,
xxorb.orb_meta_object logical_model,
xxorb.orb_logical_model logical_model_base,
xxorb.orb_meta_object logical_physical_model_link,
xxorb.orb_meta_object physical_model_object,
xxorb.orb_meta_object physical_model,
xxorb.orb_meta_object domain,
xxorb.orb_sql_physical_model physical_model_base,
xxorb.orb_meta_object datasource,
xxorb.orb_localizedstring_flat domain_name,
xxorb.orb_localizedstring_flat category_name,
xxorb.orb_localizedstring_flat logical_model_name,
xxorb.orb_localizedstring_flat physical_model_name,
xxorb.orb_localizedstring_flat physical_model_obj_name,
xxorb.orb_localizedstring_flat logical_model_obj_name,
xxorb.orb_localizedstring_flat datasource_name
where 1=1 and
--report.report_type='Data Report'
report.inherited_meta_obj_id = inherited_obj.id and
exists (select null from xxen_orb_report_columns_v xorc where xorc.report_id=report.report_id and xorc.logical_model_obj_id=logical_physical_model_link.id ) and
inherited_obj.parent_id = logical_model.id and
logical_model_base.id=logical_model.id and
logical_model.parent_id = domain.id and
domain_name.meta_object_id = domain.id and
logical_model_name.meta_object_id = logical_model.id and
inherited_obj.id = category_name.meta_object_id and
logical_physical_model_link.parent_id=logical_model.id and
logical_physical_model_link.id<>inherited_obj.id and
physical_model_object.id=logical_physical_model_link.physical_meta_obj_id and
physical_model_base.id=physical_model_object.parent_id and
physical_model.id=physical_model_object.parent_id and
physical_model_name.meta_object_id = physical_model_base.id and
datasource.id = physical_model_base.datasource_id and
datasource_name.meta_object_id = datasource.id and
physical_model_obj_name.meta_object_id=physical_model_object.id and
logical_model_obj_name.meta_object_id=logical_physical_model_link.id and
inherited_obj.obj_type = 10 and
logical_model.obj_type = 6 and
physical_model.obj_type = 2 and
--datasource.obj_type = 3 and
domain.obj_type = 1 and
category_name.property_key = 'name' and
domain_name.property_key = 'name' and
logical_model_name.property_key = 'name' and
physical_model_name.property_key = 'name' and
datasource_name.property_key = 'name' and
physical_model_obj_name.property_key = 'name' and
logical_model_obj_name.property_key = 'name'
) orb_lm_details
order by orb_lm_details.domain_name,orb_lm_details.report_id |
