Getting from Here to there -- CA PPM 14.1 ERD Question

[mtognetti]

I have been searching the the ERD, google, and the community to resolve my question but I have not had any luck.  I am new to CA PPM, but not to databases.  What I am trying to do is to pull the PROJECT_TYPE value as seen on  the user interface screen (Project: Clarity - Reports Mgmt - Properties - Main - Project Summary) and link it to my project.

 

I have my query written and it is working in some, but not all cases.  I am sure they reason behind this is my lack of knowledge base in CA PPM and the underlying data structure.  In addition to pulling the information (accurately) I would like to ask for guidance on proving, the information pulled is correct.

 

The information I am looking for is in niku.CMN_LOOKUPS_V   I believe the join to be (where prj is niku prj_projects):

 

LEFT OUTER JOIN

SELECT LOOKUP_ENUM, LOOKUP_CODE, LOOKUP_TYPE, NAME

FROM niku.CMN_LOOKUPS_V

WHERE LANGUAGE_CODE = 'EN' AND LOOKUP_TYPE in ('OBJ_IDEA_PROJECT_TYPE', 'PH_PROJECTSUBTYPE')

) AS lu

ON prj.PRCPMTYPE = lu.LOOKUP_ENUM

 

Am I close?

 

Thank you.

 

MT

[mtognetti]

After further research I can see that my join is completely incorrect.  I am researching further, but If anyone has any helpful information I would be glad to read it.

 

Thank you.

 

MT.

[David Morton]

The PPM database is a bit fiddly - it does follow patterns in its structure but (for historical reasons about how the product was brought together from different original products) there are at least 2 (probably 3) different "standards" that it follows, which can be interesting to get used to .

 

But to trace a lookup value, usually you need to look at the Object definition in the tool itself - look at the [Fields] section to translate a 'screen name' to an 'attribute name', then look at the Attributes section of the Object definition to map an attribute to a database-column - this normally is enough to find the underlying data column (and the attribute definition in the tool will also tell you which lookup to reference if the attribute is a lookup-type).

 

For static valued lookups, then your "screen value" will be in the CMN_LOOKUPS table (or the CMN_LOOKUPS_V view) - using the view is easier as it flattens the NLS translation of the "screen value" - if NLS is important to you then you should be using a language code as well as the lookup type and value (either _ENUM or _CODE depending on whether its a numeric lookup value or a character one). If its a dynamic lookup, then you need to look at the SQL logic behind the dynamic-lookup itself and recreate that same logic in your new SQL.

 

(there are some complications/exceptions to the above tho'  )

[mtognetti]

David,

 

Thank you for this information.  I can see that I am "close" as I am using CMN_Lookups_V and I have changed the lookup value to be _code.  I am just missing the in-between link between the two (projects and CMN_Lookups_v.  I believe that your road map will get me there; however, I do not see a Fields or Attributes linkage and think that may have to do with the permission levels of the account I am logged in with.  I will be logging in as the ADMIN and checking again.

 

Again, Thank you.

 

MT

[David Morton]

Yes, you will need "admin" access to be able to get to the object definition settings in the tool.