Query for database table

[Anon Anon]

I am creating a data dictionary of sort for Clarity attributes for our users.   I have created a portlet that will provide me  with Object Name, Page Name, Section Name, Attribute Label, and Database Column name.   I would like to be able to also provide the Database Table name for a given attribute - much like what you would find in the Object Definition: Attributes page view of an object in Clarity Admin Tool.   Anyone have an idea where I could find this data?

[Deactivated User]

Have you looked at Tech Ref and also the ER diagrams in it for starters?  Martti K.

[Anon Anon]

Martti: yes, I have look at the Tech Ref, there was nothing there unless I overlook something.  Paul: I am using MSSQL and have look at the system tables.   Dave hit it on the head, the problem is matching Object/Attribute to data Table/Column.  For example, if I am looking at the attribute "name" on the project object, I would like to be able to tell that the attribute resides on the inv_investments table.

[Anon Anon]

I don't unfortunately have an instant solution to hand - but would like one :-) and may not get time to research either right now.  May I suggest something?   Set sql trace on then bring-up the attribute list for Project and turn off sql trace. That screen reveals attribute to table and field. You might also repeat this for the filter 'seeded attributes' and separately for 'custom attributes' (I always try to keep sql traces to just the minimum)  Not promising anything - but these traces may assist.

[Deactivated User]

Well that is good then you can look for more in cmn_attributes, cmn_captions_nls, CMN_view_ ... cmn_object_view, cmn_portlet_views, in MS SQL either sysobjects or pressing ALT F1 in query analyzerand if English is not your language then the resource files and even the xml and xds files if you need to know the XOG attributes  You could also copy the views with object attributes   in Admin tool   which would give a list for each object with the display name, field name and table namein many cases.  As Paul and Dave say   it varies   depending on the database and even if you know all there is to know about the db you still have to relate that to what the users see in the GUI (when it is customixed and translated to antoher language) The ER diagrams and the tech ref help you in cases like the the project manager: does it come from the user or resource table and is it linked with userId, resourceID, table ID or PRUID.  There is no button for instant total knowledge without wisdom.  Are you trying to create a custom BO universe? If so did you catch the Clarity GUC webinar yesterday?  Martti K.

[Anon Anon]

I missed the webinar yesterday but am interested to see it.   Is there a recording of that session somewhere?

[furrelkt7]

Here is the PDF from yesterday. Don't know where to get the recorded session yet! :-)

Attachment(s)

Global User Community Discussion.pdf   617 KB 1 version

[Anon Anon]

Thanks and kudos for the pdf!

[Anon Anon]

Hi,

I need to find out that what all tables get updated when you do any task assignment to any resource or role(assignment to role does not make an entry in PRASSIGNMENT) , as You suggested for SQl trace for finding out the tables getting updated.

can you please tell me the steps to activate the Sql trace.


Regards,
Vivek

[Deactivated User]

See Tech docs from support site Knowledge base


How to read SQL Trace Log Timings (Niku KB ID: 7807)

How to read SQL Trace Log Timings. I have generated a SQL...

Type: Tech Doc
Product: Multiple
Release: Multiple
ID: TEC439280
Last Update: 12/06/2012


Using Clarity v13 SQLTrace

... , will allow the Clarity Administrator to execute steps to...

Type: Tech Doc
Product: Multiple
Release: Multiple
ID: TEC573491
Last Update: 12/26/2012

or


Installation Guide Release 13.1.00, Second Edition - SQL Logging

SQL logging (previously known as SQL trace) is enabled by...

Type: Documentation
Size: 2 KB
Last Update: 09/28/2012


Or in pre v13 simply execute the first to enable (for your ID) and the second to disable
-- UPDATE niku.CMN_SEC_USERS SET SQLTRACE_ACTIVE = 7 WHERE USER_NAME = 'kinnunenad' select SQLTRACE_ACTIVE from niku.CMN_SEC_USERS WHERE USER_NAME = 'kinnunenad' select USER_NAME, SQLTRACE_ACTIVE from niku.CMN_SEC_USERS WHERE (SQLTRACE_ACTIVE is not null or SQLTRACE_ACTIVE = 0)
-- UPDATE niku.CMN_SEC_USERS SET SQLTRACE_ACTIVE = NULL WHERE USER_NAME = 'kinnunenad' select SQLTRACE_ACTIVE from niku.CMN_SEC_USERS WHERE USER_NAME = 'kinnunenad' select USER_NAME, SQLTRACE_ACTIVE from niku.CMN_SEC_USERS WHERE (SQLTRACE_ACTIVE is not null or SQLTRACE_ACTIVE = 0)

These will set the trace active and display that is was set and set it inactive and display if there are traces still active.
Put these on the desktop on you db server for easy access



Martti K.

[Anon Anon]

which database type? MSSQL or Oracle?  each have their own OOTB data dictionaries - which you can leverage in queries (and hence nsql also)  e.g.MSSQLOracle    

[Anon Anon]

The problem is matching the Clarity OBJECT/ATTRIBUTE to the database TABLE/COLUMN though.  And that is not so simple?

[Anon Anon]

Hi,       I agreed with paul, dave and marrti statement. its difficult to get the attribute name, column name and table name for system and custom attributes. but we can easily get the attribute name, column name and table name which  we have created in clarity system (Custom Attributes).  Custom attribute created in system.   select gcc .NAME " Attribute Name " , COLUMN_NAME " Column Name " , DATA_TYPE " Data Type " , 'ODF_CA_' || UPPER( OBJECT_NAME ) "Table_Name" , object_name from odf_custom_attributes att , cmn_captions_nls gcc where att .id = gcc . pk_id --and object_name = 'project'      and language_code = 'en' and TABLE_NAME = 'ODF_CUSTOM_ATTRIBUTES' order by object_name         Thanks  Senthil.

[Anon Anon]

Hi,  i have taken the custom attributes with database object details for the objects but the system attributes not yet.  i tried now but not with no luck but will soon try again.  If your requirement is urgent then My suggestion would be do an manual work (3 hours Max) as this is going to be an one time activity.The system objects will be an max of 10,you can copy in a excel sheet and then create an temp table with the desired columns and insert it.Then put an union with custom attributes and show in the portlet.The system object attributeswill not going to change.  i took sql trace and my findings(???) don't hit me..  Here in the object if we take project,Asset,Application are all pointing to same (iam still in 7.5.2) 2 tables(srm& prj projects).  custom attributes:-  select nls.*, 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 pk_id  The sytem attributes are not stored in odf_attributes.But the the attributes which are pointing to an lookup is stored in odf_display_mappings where by passing the object we can get all theatrributes pointing to an lookup.     select   *
                                                      from       odf_mappings m, odf_display_mappings dm, cmn_lookups l
                                                      where     object = 'project'
                                                      and         attribute = 'actuals'
                                                      and         display_mappings_id = dm.id
                                                      and         l.lookup_code = m.mapping_code    Regards,sundar

[System]

Hi All,  Replay information was emailed out to all CA Clarity Global Community members today.   If you are not a member you will need to go to causergroups.ca.com and Join the CA Clarity Global Community in order to access the News section where the replay is located as well as the Product Management ERQ Voting Survey.  Regards,Chris

[Anon Anon]

Hi,           If you enabled sql trace, You can identify the custom attribute where will be stored in database. not system attributes. But we can identify the table name and column name which we have created or system created (system and custom attributes) by using data dictionary. I have made some query, how to fetch the database table name, column name, datatype, datalength based on object in oracle. not sql server. you can build a similar logic in sql server. I think below query may be use for you.    Clarity 8.X and Above  SELECT COL.TABLE_NAME,COL.COLUMN_NAME,COL.DATA_TYPE, COL.DATA_LENGTH,COL.DATA_PRECISION,COL.COLUMN_ID,Initcap(:OBJECT_CODE) OBJECT_NAME FROM USER_TAB_COLUMNS COL WHERE TABLE_NAME = 'INV_INVESTMENTS' AND 1 = (
SELECT DISTINCT 1 INV FROM USER_TABLES TAB, USER_TAB_COLUMNS COL,
(SELECT CODE, REPLACE(OBS_CODE,'SRM','INV')OBS_CODE FROM ODF_OBJECTS WHERE IS_CUSTOM = 0 AND OBS_CODE IS NOT NULL AND IS_CUSTOMIZABLE=1
AND PAGE_INSTANCE_TYPE IS NOT NULL) OBJ WHERE TAB.TABLE_NAME = COL.TABLE_NAME AND
OBJ.CODE=:object_code AND TAB.TABLE_NAME NOT LIKE 'TEMP%'
AND OBS_CODE=TAB.TABLE_NAME)
UNION
SELECT COL.TABLE_NAME,COL.COLUMN_NAME,COL.DATA_TYPE, COL.DATA_LENGTH,COL.DATA_PRECISION,COL.COLUMN_ID,Initcap(:OBJECT_CODE) OBJECT_NAME FROM USER_TABLES TAB, USER_TAB_COLUMNS COL WHERE TAB.TABLE_NAME = COL.TABLE_NAME AND
COL.TABLE_NAME LIKE ('%'||upper(:object_code)||'%') AND (TAB.TABLE_NAME LIKE 'SRM%'OR TAB.TABLE_NAME = 'ODF_CA_'||upper(:object_code)
OR TAB.TABLE_NAME LIKE 'PAC_MNT%' OR TAB.TABLE_NAME = 'PRJ_'||upper(:object_code) ||'S' OR TAB.TABLE_NAME LIKE 'INV_%'
OR TAB.TABLE_NAME LIKE 'RSM_REQ_'||upper(:object_code)||'S' OR TAB.TABLE_NAME LIKE 'RIM%')
UNION
SELECT COL.TABLE_NAME,COL.COLUMN_NAME,COL.DATA_TYPE, COL.DATA_LENGTH,COL.DATA_PRECISION,COL.COLUMN_ID,Initcap(:OBJECT_CODE) OBJECT_NAME FROM USER_TAB_COLUMNS COL WHERE TABLE_NAME = 'CMN_SEC_USERS' AND 'RESOURCE' = upper(:object_code)
ORDER BY TABLE_NAME,COLUMN_ID  Clarity 7.5.X      SELECT COL.TABLE_NAME,COL.COLUMN_NAME,COL.DATA_TYPE, COL.DATA_LENGTH,COL.DATA_PRECISION,COL.COLUMN_ID,Initcap(:OBJECT_CODE) OBJECT_NAME FROM USER_TABLES TAB, USER_TAB_COLUMNS COL WHERE TAB.TABLE_NAME = COL.TABLE_NAME AND
COL.TABLE_NAME LIKE ('%'||upper(:object_code)||'%') AND (TAB.TABLE_NAME LIKE 'SRM%'OR TAB.TABLE_NAME = 'ODF_CA_'||upper(:object_code)
OR TAB.TABLE_NAME LIKE 'PAC_MNT%' OR TAB.TABLE_NAME = 'PRJ_'||upper(:object_code) ||'S' OR TAB.TABLE_NAME LIKE 'INV_%'
OR TAB.TABLE_NAME LIKE 'RSM_REQ_'||upper(:object_code)||'S' OR TAB.TABLE_NAME LIKE 'RIM%')
UNION
SELECT COL.TABLE_NAME,COL.COLUMN_NAME,COL.DATA_TYPE, COL.DATA_LENGTH,COL.DATA_PRECISION,COL.COLUMN_ID,Initcap(:OBJECT_CODE) OBJECT_NAME FROM USER_TAB_COLUMNS COL WHERE TABLE_NAME = 'CMN_SEC_USERS' AND 'RESOURCE' = upper(:object_code)
ORDER BY TABLE_NAME,COLUMN_ID  Note: Please pass the paramter as object code. Object Code should be lower case  ThanksSenthil.

[Anon Anon]

Hi,  I have enclosed the objects.xml where all Queries in sql trace is from there.  The below Query needs to be changed if it is needs to be  work for all objects.  SELECT COL.TABLE_NAME,COL.COLUMN_NAME,COL.DATA_TYPE, COL.DATA_LENGTH,COL.DATA_PRECISION,COL.COLUMN_ID,Initcap(:OBJECT_CODE) OBJECT_NAME FROM USER_TABLES TAB, USER_TAB_COLUMNS COL WHERE TAB.TABLE_NAME = COL.TABLE_NAME AND
COL.TABLE_NAME LIKE ( '%'||upper(:object_code)||'%') AND (TAB.TABLE_NAME LIKE 'SRM%'OR TAB.TABLE_NAME = 'ODF_CA_'||upper(:object_code)
OR TAB.TABLE_NAME LIKE 'PAC_MNT%' OR TAB.TABLE_NAME = 'PRJ_'||upper(:object_code) ||'S' OR TAB.TABLE_NAME LIKE 'INV_%'
OR TAB.TABLE_NAME LIKE 'RSM_REQ_'||upper(:object_code)||'S' OR TAB.TABLE_NAME LIKE 'RIM% ')  In the highlighted ones it is matched with the object and the tables only for  these objects.For Task   & Team(only odf_ca_task,odf_ca_team is coming but prtask & prteam is missing),assignment,timesheet and action  as only for these objects the table is mapped in the OR Condition.The other objects You need to provide all the combination in the OR conditions or might be in case conditions.The mapping combination needs to be  provided for all  objects with the Database tables then it will work.  But the  core question remains  1. Where i can find the seeded attributes of an project stored in database.2. How can i see bring the virtual attributes which are not stored in database.  For you if you need you can use the Queries in Sql Server.(Long Back worked in sql,still having sql2000 only)       declare @object_code varchar(30)
    set @object_code = UPPER('Freight')
    select table_name,column_name,data_type from information_schema.columns
    WHERE (column_name LIKE 'ord%'OR column_name = 'ODF_CA_'+ upper(@object_code))
    union
    select table_name,column_name,data_type from information_schema.columns
    WHERE tABLE_NAME = 'orders' AND upper(column_name) =     @object_code
    ORDER BY TABLE_NAME,COLUMN_name Note:-   Pls replace the system tables with clarity tables and all all the or/and conditions   Dear Kum,  i think the portlet even though developed is not much use for the business or users and must be restricted to admin or support guys.What is the advantage that userswill get knowing about the Object-->Data Mapping.May be as an administartor it will be useful for us.But if it is an requirement and if  we can't convince the Business / users (That's the way it happens often) then that's it.   But i will  share an useful tip provided by sangeet Chorey (Thanks to Him)  for Clarity developer   Microsoft IE Developer Toolbar is great tool when it comes to Clarity Development & Support. I use it quite frequently primarily to identify Display field's internal database name, find lookup name, lookup values etc.. It eliminates the need of doing "view source" of Web page and digging into HTML code to extract relevant info.Step 1 : Install Microsoft IE Toolbar and it will added as add-in in IE
Step 2 : Open any Clarity page.
Step 3: Click on IE Toolbar icon in toolbar (It shows up in lower pan)
Step 4: Click Find > 'Select Element by Click'
Step 5: Click on desired display field and it will show it's internal name.
Step 6. After having done with that Click Find > 'Select Element by Click' option to uncheck it (This will return webpage to normal display mode)Tip : You can also click the first icon in place of Find > 'Select element by click'Pls  find  enclosed the snapshot.  Regards,sundar    

Attachment(s)

Object.zip   99 KB 1 version

[Anon Anon]

Hi,  Small correction in sql query.i have used column_name instead of table_name and missed upper() that's it.Pls check it and modify accordingly..       declare @object_code varchar(30)
    set @object_code = UPPER('Freight')
    select table_name,column_name,data_type from information_schema.columns
    WHERE (table_name LIKE 'ord%'OR table_name = 'ODF_CA_'+ upper(@object_code))
    union
    select table_name,column_name,data_type from information_schema.columns
    WHERE table_name = 'orders' AND upper(table_name) =     upper(@object_code)
     order by  table_name,column_name  regards,sundar

[Anon Anon]

this may help? I have used similar logic in the past, it uses cmn_db_history & prj_blb_slicerequests to arrive at "what is OOTB", which may then be limited to tables  
/* ORACLE: an approach to locating OOTB databse objects */
select
*
from (
select distinct o.object_type, o.object_name
from user_objects o
join (select replace(name,'.xml') as db_name from cmn_db_history) h on o.object_name = h.db_name
union
select distinct o.object_type, o.object_name
from user_objects o
join (select distinct table_name from prj_blb_slicerequests) s on o.object_name = s.table_name
) sql_wrap
where object_type = 'TABLE' -- e.g. limit output for tables

/* MSSQL: an approach to locating OOTB databse objects */
select
*
from (
select distinct o.type, o.name
from sys.objects o
join (select replace(name,'.xml','') as db_name from cmn_db_history) h on o.name = h.db_name
union
select distinct o.type, o.name
from sys.objects o
join (select distinct table_name from prj_blb_slicerequests) s on o.name = s.table_name
) sql_wrap
where type = 'U' -- e.g. limit output for tables

 

[Anon Anon]

by the way, the queries above aren't be as efficient as they could be.  the oracle only queries below are here as "possible avenues" only.
/* 1 getting captions*/
select
oo.code, ov.code, ova.attribute_code, cap.name, oo.*
from odf_views ov
join odf_view_attributes ova on ov.id = ova.view_id
join odf_objects oo on ov.object_code = oo.code
left join cmn_captions_nls cap on ova.id = cap.pk_id and cap.table_name = 'ODF_VIEW_ATTRIBUTES' and language_code = 'en'
order by oo.code, ov.code, ova.attribute_code
go

/* 2 use of cmn_schema_descriptions, tables/views */

select distinct csd.PARENT_OBJECT_NAME, csd.object_name, PARENT_OBJECT_TYPE as object_type, csd.description
from CMN_SCHEMA_DESCRIPTIONS csd
join user_tables ut on csd.PARENT_OBJECT_NAME = ut.table_name
where csd.PARENT_OBJECT_TYPE = 'TABLE'
and csd.object_type not in('DOCUMENTATION_URL', 'COLUMN')

union

select distinct csd.PARENT_OBJECT_NAME, csd.object_name, uo.object_type, csd.description
from CMN_SCHEMA_DESCRIPTIONS csd
left join user_tables ut on csd.PARENT_OBJECT_NAME = ut.table_name
join user_objects uo on csd.PARENT_OBJECT_NAME = uo.object_name
where csd.PARENT_OBJECT_TYPE = 'TABLE'
and csd.object_type not in('DOCUMENTATION_URL', 'COLUMN')
and ut.table_name is null
go

/* 3 use of cmn_schema_descriptions, column information*/

select csd.*
from CMN_SCHEMA_DESCRIPTIONS csd
left join user_tables ut on csd.PARENT_OBJECT_NAME = ut.table_name
left JOIN USER_TAB_COLUMNS utc ON (ut.TABLE_NAME = utc.TABLE_NAME AND utc.COLUMN_NAME = csd.object_name and csd.object_type='COLUMN')
go

/* 4 use of odf_custom_attributes */

select
'odf_ca_' || object_name db_table_name, oca.*
from odf_custom_attributes oca
go

 

[Anon Anon]

Hi,       I agreed paul mail. you can see system table information cmn_schema_descriptions table. but I can't see all system table information from this table like 'RIM_CHANGE_REQUESTS','RIM_RISKS_AND_ISSUES' and etc. Even i could't see the system attribute information, where will be stored and how to fectch from clarity database.                     1) All Virtual attribute will be stored in odf_custom_attributes when you have to create (IS_VIRTUAL=1).  I have builded some logic, to fetch the system attributes and custom attributes which we have created or systed created and also table and column information.Clarity 7.5.X  -- To fecth table_name and column name which we have created a custom attributes for system object or custom objectSELECT OBJECT_NAME,'ODF_CA_'||UPPER(:OBJECT_CODE) TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_SIZE,DECODE(IS_VIRTUAL,1,'Yes','No') IS_VIRTUAL,IS_ACTIVE FROM ODF_CUSTOM_ATTRIBUTESWHERE OBJECT_NAME = lower(:OBJECT_CODE)UNION-- To fetch the table and column information for master object (Only Master Table)SELECT OBJECT_CODE,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH DATA_SIZE, 'No' IS_VIRTUAL, 1 IS_ACTIVE FROM USER_TAB_COLUMNS SCHEMA,(SELECT O.CODE OBJECT_CODE,DECODE(NVL(O.OBS_CODE,O.CODE),'actionitem','CAL_ACTION_ITEMS','team','PRTEAM','baseline','PRJ_BASELINES','assignment','PRASSIGNMENT','change','RIM_CHANGE_REQUESTS','issue','RIM_RISKS_AND_ISSUES','risk','RIM_RISKS_AND_ISSUES','task','PRTASK','idea','PMA_IDEAS','requisition','RSM_REQ_REQUISITIONS','portfolio','PMA_PORTFOLIOS','incident','IMM_INCIDENTS',O.OBS_CODE) OBS_CODE FROM ODF_OBJECTS_V O, ODF_OBJECTS_V PARENT, (SELECT CODE, NAME FROM CMN_PARTITION_MODELS PMI, CMN_CAPTIONS_NLS CWHERE PMI.ID = C.PK_ID AND PMI.IS_ACTIVE = 1 AND C.TABLE_NAME = 'CMN_PARTITION_MODELS' AND C.LANGUAGE_CODE = 'en') PM WHERE O.LANGUAGE_CODE = 'en' AND O.IS_CUSTOMIZABLE = 1 AND PARENT.CODE (+)= O.PARENT_OBJECT_CODEAND PARENT.LANGUAGE_CODE (+)= 'en' AND PM.CODE(+) = O.PARTITION_MODEL_CODE AND O.IS_CUSTOM = 0) OBJECT WHERE SCHEMA.TABLE_NAME = OBJECT.OBS_CODE AND OBJECT.OBJECT_CODE = LOWER(:OBJECT_CODE)UNION-- Below query is applicatble for project and resource object (PAC_MNT_RESOURCES & PAC_MNT_PROJECTS table doesn't     --how in object attribute section)SELECT :OBJECT_CODE,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH DATA_SIZE, 'No' IS_VIRTUAL, 1 IS_ACTIVE FROM USER_TAB_COLUMNS WHERE LOWER(TABLE_NAME) IN ('prj_'||LOWER(:OBJECT_CODE)||'s','pac_mnt_'||LOWER(:OBJECT_CODE)||'s')UNION-- It's applicable for resource object. CMN_SEC_USERS table doesn't show in object attribute section.SELECT :OBJECT_CODE,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH DATA_SIZE, 'No' IS_VIRTUAL, 1 IS_ACTIVE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'CMN_SEC_USERS' AND 'resource' = ((SELECT LOWER(:OBJECT_CODE) FROM ODF_OBJECTS WHERE CODE=LOWER(:OBJECT_CODE)))  Clarity 8.X  -- To fecth table_name and column name which we have created a custom attributes for system object or custom objectSELECT OBJECT_NAME,'ODF_CA_'||UPPER(:OBJECT_CODE) TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_SIZE,DECODE(IS_VIRTUAL,1,'Yes','No') IS_VIRTUAL,IS_ACTIVE FROM ODF_CUSTOM_ATTRIBUTESWHERE OBJECT_NAME = lower(:OBJECT_CODE)UNION-- To fetch the table and column information for master object (Only Master Table)SELECT OBJECT_CODE,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH DATA_SIZE, 'No' IS_VIRTUAL, 1 IS_ACTIVE FROM USER_TAB_COLUMNS SCHEMA,(SELECT O.CODE OBJECT_CODE,CASE WHEN NVL(O.OBS_CODE,O.CODE) LIKE 'INV%' THEN O.OBS_CODE||'S'WHEN NVL(O.OBS_CODE,O.CODE) = 'actionitem' THEN 'CAL_ACTION_ITEMS'WHEN NVL(O.OBS_CODE,O.CODE) = 'team' THEN 'PRTEAM'WHEN NVL(O.OBS_CODE,O.CODE) = 'baseline' THEN 'PRJ_BASELINES'WHEN NVL(O.OBS_CODE,O.CODE) = 'assignment' THEN 'PRASSIGNMENT'WHEN NVL(O.OBS_CODE,O.CODE) = 'change' THEN 'RIM_CHANGE_REQUESTS'WHEN NVL(O.OBS_CODE,O.CODE) IN ('issue','risk') THEN 'RIM_RISKS_AND_ISSUES'WHEN NVL(O.OBS_CODE,O.CODE) = 'task' THEN 'PRTASK'WHEN NVL(O.OBS_CODE,O.CODE) = 'requisition' THEN 'RSM_REQ_REQUISITIONS'WHEN NVL(O.OBS_CODE,O.CODE) IN ('benefitplan','costplan') THEN 'FIN_PLANS'WHEN NVL(O.OBS_CODE,O.CODE) = 'benefitplandetail' THEN 'FIN_BENEFIT_PLAN_DETAILS'WHEN NVL(O.OBS_CODE,O.CODE) = 'burdeningclass' THEN 'FIN_BURDEN_CLASSES'WHEN NVL(O.OBS_CODE,O.CODE) = 'burdeninglevel' THEN 'FIN_BURDEN_LEVELS'WHEN NVL(O.OBS_CODE,O.CODE) = 'burdeningmatrix' THEN 'FIN_BURDEN_MATRICES'WHEN NVL(O.OBS_CODE,O.CODE) = 'contract' THEN 'PROJCNTRTS'WHEN NVL(O.OBS_CODE,O.CODE) = 'costplandetail' THEN 'FIN_COST_PLAN_DETAILS'WHEN NVL(O.OBS_CODE,O.CODE) = 'department' THEN ('DEPARTMENTS')WHEN NVL(O.OBS_CODE,O.CODE) = 'financials' THEN 'FIN_FINANCIALS'WHEN NVL(O.OBS_CODE,O.CODE) IN ('npiofinproperties','projfinproperties') THEN 'PAC_MNT_PROJECTS'WHEN NVL(O.OBS_CODE,O.CODE) = 'fiscaltimeperiod' THEN 'BIZ_COM_PERIODS'WHEN NVL(O.OBS_CODE,O.CODE) = 'glaccount' THEN 'CBK_GL_ACCOUNT'WHEN NVL(O.OBS_CODE,O.CODE) IN ('glallocation','resourcecredit') THEN 'CBK_GL_ALLOCATION'WHEN NVL(O.OBS_CODE,O.CODE) IN ('glallocationdetail','resourcecreditdetail') THEN ('CBK_GL_ALLOCATION_DETAIL')WHEN NVL(O.OBS_CODE,O.CODE) = 'incident' THEN 'IMM_INCIDENTS'WHEN NVL(O.OBS_CODE,O.CODE) = 'inv' THEN 'INV_INVESTMENTS'WHEN NVL(O.OBS_CODE,O.CODE) IN ('investmentallocation','investmenthierarchy') THEN 'INV_HIERARCHIES'WHEN NVL(O.OBS_CODE,O.CODE) = 'invoice' THEN 'CBK_INVOICE'WHEN NVL(O.OBS_CODE,O.CODE) = 'keymetric' THEN 'DPT_KEYMETRICS'WHEN NVL(O.OBS_CODE,O.CODE) = 'location' THEN ('LOCATIONS')WHEN NVL(O.OBS_CODE,O.CODE) = 'portfolio' THEN 'PMA_PORTFOLIOS'WHEN NVL(O.OBS_CODE,O.CODE) = 'reqresource' THEN 'RSM_REQ_RESOURCES'WHEN NVL(O.OBS_CODE,O.CODE) = 'SRM_PROJECTS' THEN 'INV_PROJECTS'WHEN NVL(O.OBS_CODE,O.CODE) = 'subscription' THEN 'DPT_SUBSCRIPTIONS'ELSE NVL(O.OBS_CODE,O.CODE)END OBS_CODE FROM ODF_OBJECTS_V O, ODF_OBJECTS_V PARENT, (SELECT CODE, NAME FROM CMN_PARTITION_MODELS PMI, CMN_CAPTIONS_NLS CWHERE PMI.ID = C.PK_ID AND PMI.IS_ACTIVE = 1 AND C.TABLE_NAME = 'CMN_PARTITION_MODELS' AND C.LANGUAGE_CODE = 'en') PM WHERE O.LANGUAGE_CODE = 'en' AND O.IS_CUSTOMIZABLE = 1 AND PARENT.CODE (+)= O.PARENT_OBJECT_CODEAND PARENT.LANGUAGE_CODE (+)= 'en' AND PM.CODE(+) = O.PARTITION_MODEL_CODE AND O.IS_CUSTOM = 0) OBJECT WHERE SCHEMA.TABLE_NAME = OBJECT.OBS_CODE AND OBJECT.OBJECT_CODE = LOWER(:OBJECT_CODE)UNION-- Below query is applicatble for project and resource object (PAC_MNT_RESOURCES & PAC_MNT_PROJECTS table doesn't     --show in object attribute section)SELECT :OBJECT_CODE,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH DATA_SIZE, 'No' IS_VIRTUAL, 1 IS_ACTIVE FROM USER_TAB_COLUMNS WHERE LOWER(TABLE_NAME) IN ('prj_'||LOWER(:OBJECT_CODE)||'s','pac_mnt_'||LOWER(:OBJECT_CODE)||'s')UNION-- It's applicable for resource object. CMN_SEC_USERS table doesn't show in object attribute section.SELECT :OBJECT_CODE,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH DATA_SIZE, 'No' IS_VIRTUAL, 1 IS_ACTIVE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'CMN_SEC_USERS' AND 'resource' = ((SELECT LOWER(:OBJECT_CODE) FROM ODF_OBJECTS WHERE CODE=LOWER(:OBJECT_CODE)))  ThanksSenthil Message Edited by senthil on 09-14-2009 08:04 AM [left]

[Anon Anon]

First of all, I want to thanks everyone that contributed to this thread.   After meeting with the business customer, they agreed that information regarding what table an attribute was located in would not be useful for them.   The main issue they were trying to resolve, identifying where attributes were located within Clarity objects was easily resolved by the query below.   Out of my own, curiosity, I think I will attempt to join this info back to identify the table.   SELECT ODF_VIEW_ATTRIBUTES . ID , CMN_CAPTIONS_NLS . NAME AS Object , ODF_VIEWS . VIEW_SUBTYPE AS Mode , CMN_CAPTIONS_NLS_1 . NAME AS [Sub-Page] , ODF_VIEW_SECTIONS . LABEL AS [Section Name] ,  CASE WHEN ODF_VIEW_ATTRIBUTES . LABEL = N 'in captions nls' THEN ( SELECT NAME    FROM CMN_CAPTIONS_NLS  WHERE ( PK_ID = ODF_VIEW_ATTRIBUTES . LABEL_PK_ID ) AND ( LANGUAGE_CODE = N 'en'  AND ( CMN_CAPTIONS_NLS . TABLE_NAME = N 'ODF_VIEW_ATTRIBUTES' ))) ELSE  ODF_VIEW_ATTRIBUTES . LABEL END as [Attribute Label] , ODF_VIEW_ATTRIBUTES . ATTRIBUTE_CODE AS [Attribute Name] ,  ODF_VIEW_ATTRIBUTES . WIDGET_TYPE AS [Attribute Type]    FROM ODF_VIEW_ATTRIBUTES INNER JOIN  ODF_VIEWS ON ODF_VIEW_ATTRIBUTES . VIEW_ID = ODF_VIEWS . ID INNER JOIN  ODF_VIEW_SECTIONS ON ODF_VIEW_ATTRIBUTES . SECTION_ID = ODF_VIEW_SECTIONS . ID INNER JOIN  ODF_OBJECTS ON ODF_VIEWS . OBJECT_CODE = ODF_OBJECTS . CODE INNER JOIN  CMN_CAPTIONS_NLS ON ODF_OBJECTS . ID = CMN_CAPTIONS_NLS . PK_ID INNER JOIN  CMN_CAPTIONS_NLS AS CMN_CAPTIONS_NLS_1 ON ODF_VIEWS . ID = CMN_CAPTIONS_NLS_1 . PK_ID  WHERE ( ODF_VIEWS . VIEW_SUBTYPE = N 'create' OR  ODF_VIEWS . VIEW_SUBTYPE = N 'update' ) AND ( CMN_CAPTIONS_NLS . LANGUAGE_CODE = N 'en' ) AND  ( CMN_CAPTIONS_NLS . TABLE_NAME = N 'odf_objects' ) AND  ( CMN_CAPTIONS_NLS_1 . LANGUAGE_CODE = N 'en' ) AND ( CMN_CAPTIONS_NLS_1 . TABLE_NAME = N 'ODF_VIEWS' )  ORDER BY CMN_CAPTIONS_NLS . NAME , Mode , ODF_VIEWS . CAPTION , [Section Name] , ODF_VIEW_ATTRIBUTES . LABEL