Need to Export All Attributes Related to an Object to an Excel File

Products

• Clarity PPM On Premise

Issue/Introduction

Need to export all attributes related to an object to an excel file, Please advise.

Environment

Clarity Application

Resolution

For user-defined attributes, try run the following queries, the odf_custom_attributes table will provide the custom attributes along with those applied from PMO and other addins.

1) /* Custom attributes defined on an object */
select nls.*, data_type, extended_type, ca.internal_name from cmn_captions_nls nls, odf_custom_attributes ca
where nls.pk_id=ca.id and ca.object_name = 'project' and table_name='ODF_CUSTOM_ATTRIBUTES'
and language_code = 'en'
order by nls.created_date desc;

(You can change the object_name to another object (it is case-sensitive). As you will see if you go to the Object Attributes listing page, the attributes come from different tables.)


2) select ( select name from cmn_captions_nls where language_code = 'en'
and table_name = 'ODF_CUSTOM_ATTRIBUTES' and pk_id = A.id ) AS "Attribute"
,( select description from cmn_captions_nls where language_code = 'en'
and table_name = 'ODF_CUSTOM_ATTRIBUTES' and pk_id = A.id ) AS "Description"
, initcap(data_type) as "Data Type" , default_value AS "Default"
, internal_name AS "Database Column / Attribute ID"
, decode(lookup_type,null,'','Lookup name '''||
(select C.name from cmn_captions_nls C , cmn_lookup_types L
where L.lookup_type = A.lookup_type and C.pk_id = L.id
and language_code = 'en' and table_name = 'CMN_LOOKUP_TYPES')||' / '||(lookup_type)||''' ')
|| decode(A.is_editable,0,'Read-only "Checked" ',null)
|| decode(A.extended_type,'lookup',null
,decode(A.data_type,'string','Maximum Size: '||data_size||' ',null))
|| decode(A.extended_type,'lookup',null
,decode(A.data_type,'number','Decimal places: '||scale,null)) AS "Miscellaneous"
from odf_custom_attributes A where object_name = 'project'
and internal_name != 'partition_code'
order by A.created_date desc

(Replacing 'project' with an object name, for example 'task')

-------------------------------------------------------------------------

This query will cross-check the total # attributes for each object.

select count(1)
from odf_custom_attributes a
join CMN_CAPTIONS_NLS N on a.id = N.PK_ID
and N.TABLE_NAME = 'ODF_CUSTOM_ATTRIBUTES'
where 1=1
and N.LANGUAGE_CODE = 'en'
and a.OBJECT_NAME = 'project'
order by a.last_updated_date desc