AnsweredAssumed Answered

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

Question asked by mtognetti on Jan 3, 2017
Latest reply on Jan 4, 2017 by 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

Outcomes