ORB Report Columns

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

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