ORB Report Columns

➜ Back to Library
Description
Categories: ORBIT
Report to List the Orbit Report Columns for the Report inclusing the physical column name and logical model object it uses
select 
repcolmo.parent_id report_id,
olf_name.property_value report_name,
repcolmo.id column_id,
repcolmo.obj_key column_obj_key,
repcolname.property_value column_name,
repcoldesc.property_value column_desc,
to_clob((select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='tc'))column_name_sql,
repcol.column_order column_order,
repcol.dimension_type dimension_type,
repcol.category_id category_id,
repcolcategorykey.obj_key category_obj_key,
repcol.logical_column_id logical_column_id,
repcollogicalkey.obj_key column_logicalkey,
repcollogicalkeyparent.id logical_model_obj_id,
repcollogicalkeyparent.obj_key logical_model_obj_key,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='tct')column_type,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='sqlt')column_datatype,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='srtpe')column_sortorder,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='aggregation')column_aggregation
from xxorb.orb_meta_object repcolmo,
xxorb.orb_report_column repcol ,
xxorb.orb_meta_object repcolcategorykey ,
xxorb.orb_meta_object repcollogicalkey,
xxorb.orb_meta_object repcollogicalkeyparent,
xxorb.orb_localizedstring_flat repcolname,
xxorb.orb_localizedstring_flat repcoldesc,
xxorb.orb_report orr,
xxorb.orb_localizedstring_flat olf_name
where 1=1 and
repcolmo.obj_type = 15 and
repcol.id = repcolmo.id and
repcollogicalkey.id=repcol.logical_column_id and 
repcolcategorykey.id=repcol.category_id and
repcollogicalkeyparent.id=repcollogicalkey.parent_id and
olf_name.meta_object_id = orr.id and
olf_name.property_key = 'name' and
orr.id=repcolmo.parent_id and
repcolname.meta_object_id(+) = repcol.id and
repcoldesc.meta_object_id(+) = repcol.id and
repcolname.locale = repcoldesc.locale(+) and
repcolname.property_key(+) = 'name' and
repcoldesc.property_key(+) = 'description'
union all 
select 
repcolmo.parent_id report_id,
olf_name.property_value report_name,
repcolmo.id column_id,
repcolmo.obj_key column_obj_key,
repcolname.property_value column_name,
repcoldesc.property_value column_desc,
to_clob(xxen_orb_util.derive_formula_column(repcolmo.id ,repcollogicalkeyparent.id)||' '||xxen_xdo.column_name(repcolname.property_value)) column_name_sql,
repcol.column_order column_order,
repcol.dimension_type dimension_type,
repcol.category_id category_id,
null category_obj_key,
repcol.logical_column_id logical_column_id,
null column_logicalkey,
repcollogicalkeyparent.id logical_model_obj_id,
repcollogicalkeyparent.obj_key logical_model_obj_key,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='tct')column_type,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='sqlt')column_datatype,
(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='srtpe')column_sortorder,
nvl((select case when opf.property_value like 'EVALUATE_AGGR%' then 'SUM' else null end property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='formula'),(select property_value from xxorb.orb_properties_flat opf where opf.meta_object_id=repcolmo.id and opf.property_key='aggregation'))column_aggregation
from xxorb.orb_meta_object repcolmo,
xxorb.orb_report_column repcol ,
xxorb.orb_meta_object repcollogicalkeyparent,
xxorb.orb_localizedstring_flat repcolname,
xxorb.orb_localizedstring_flat repcoldesc,
xxorb.orb_report orr,
xxorb.orb_localizedstring_flat olf_name
where 1=1 and
repcolmo.obj_type = 15 and
repcol.id = repcolmo.id and
repcol.logical_column_id is null and 
olf_name.meta_object_id = orr.id and
olf_name.property_key = 'name' and
orr.id=repcolmo.parent_id and
repcollogicalkeyparent.id=xxen_orb_util.get_rep_col_logical_model_id(repcolmo.id) and
repcolname.meta_object_id(+) = repcol.id and
repcoldesc.meta_object_id(+) = repcol.id and
repcolname.locale = repcoldesc.locale(+) and
repcolname.property_key(+) = 'name' and
repcoldesc.property_key(+) = 'description' 
order by 1,8
Parameter NameSQL textValidation
Report Name
orr.id in (
select
orr.id
from
xxorb.orb_report orr,
xxorb.orb_localizedstring_flat olf_name
where
olf_name.meta_object_id = orr.id and
olf_name.property_key = 'name' and
olf_name.property_value=:report_name
)
LOV
Download
Related Blitz Reports
ORB Logical and Physical Model Details(Data Report) ORB Report Parameters ORB Report and Details ORB Report Assignment Details ORB Business Object and Assignments ORB Report Execution Summary ORB Report Execution History RK: AR Transactions and Lines AR Transactions and Lines 11i
Blitz Report™