Georgy N Joseph

Where All A Particular Lookup Is In Use

Blog Post created by Georgy N Joseph on Jan 27, 2016

Hi Friends,

 

We use lookups in many places in CA PPM. Sometimes we attach a lookup to a new custom attribute in any object or to a NSQL query attribute so that it becomes available in the Grid portlets made from that NSQL query.

 

So if you want to modify a lookup, how do you know where all that lookup is currently in use? Or if you want to delete a custom lookup, how will you check which all are the impacted items? The following Oracle SQL queries will help you in this mission.

 

Object Attributes Using A Particular Lookup

 

SELECT

lookup_type "Lookup ID",

object_name "Object ID",

column_name "Attribute ID",

data_type "Data Type",

CASE is_active WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END "Active?"

FROM

ODF_CUSTOM_ATTRIBUTES

WHERE

lookup_type='Your Lookup ID'

ORDER BY object_name, column_name

 

Object Based Portlets Using A Particular Lookup

 

As you very well know, any portlet created from a Clarity Object can use any of the Object attributes. So the only additional information the below query will give you is the list of all those Object based portlets where the lookup attribute is currently available for use.

 

SELECT

oca.lookup_type "Lookup ID",

cp.portlet_code "Portlet ID",

oca.object_name "Source Object ID",

oca.column_name "Attribute ID",

oca.data_type "Data Type",

CASE oca.is_active WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END "Active?"

FROM

CMN_PORTLETS cp

INNER JOIN CMN_GRIDS cg ON (cg.portlet_id=cp.id AND cg.principal_type='SYSTEM')

INNER JOIN ODF_CUSTOM_ATTRIBUTES oca ON oca.object_name=cg.dal_code

WHERE

oca.lookup_type='Your Lookup ID'

ORDER BY cp.portlet_code, oca.object_name, oca.column_name

 

NSQL Query Based Portlets Using A Particular Lookup

 

SELECT

cnqf.lov "Lookup ID",

cnqf.filter_association "Attribute ID",

cgnq.query_code "Query ID",

cp.portlet_code "Portlet ID",

CASE cp.is_active WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END "Active?"

FROM

CMN_NSQL_QUERIES cnq

INNER JOIN CMN_GG_NSQL_QUERIES cgnq ON cgnq.cmn_nsql_queries_id=cnq.id

INNER JOIN CMN_NSQL_QUERY_FILTERS cnqf ON cnqf.cmn_nsql_queries_id=cnq.id

LEFT OUTER JOIN CMN_GRIDS cg ON (cg.dal_id=cnq.id AND cg.principal_type='SYSTEM')

LEFT OUTER JOIN CMN_PORTLETS cp ON cp.id=cg.portlet_id

WHERE

cnqf.lov='Your Lookup ID'

ORDER BY cgnq.query_code, cnqf.filter_association, cp.portlet_code

 

 

I hope all the above SQL queries will help you in solving the lookups-used-where mystery which we are facing right now in CA PPM. Feel free to modify them as per your needs.

 

Regards,

Georgy

Outcomes