we have a project outline code project Status and type which has following values
More than 10 projects has values of Closed.B. So I need to collect the projects with this outline code value.
What I did.
I opened the MPS_Outline_Codes tables and scroll down to see the value in OC_CACHED_FULL_NAME. If value is Closed.B then I copied MSP_CODE_FIELDS.CODE_FIELD_ID = 188744608) AND MSP_CODE_FIELDS.CODE_UID = 49.
I then move to MSP_Code_Fields table and look for the projects that match these both values using
select * from msp_code_fields Where CODE_Field_ID=188744608 and Code_UID=49
Then I move towards the msp_projects table and then use the view creation screen to create the following View.
SELECT MSP_PROJECTS.PROJ_NAME, MSP_PROJECTS.PROJ_INFO_CAL_NAME, MSP_CODE_FIELDS.CODE_FIELD_ID, MSP_OUTLINE_CODES.PROJ_ID,
FROM MSP_CODE_FIELDS INNER JOIN
MSP_PROJECTS ON MSP_CODE_FIELDS.PROJ_ID = MSP_PROJECTS.PROJ_ID INNER JOIN
MSP_OUTLINE_CODES ON MSP_CODE_FIELDS.CODE_UID = MSP_OUTLINE_CODES.CODE_UID
WHERE (MSP_CODE_FIELDS.CODE_FIELD_ID = 188744608) AND (MSP_CODE_FIELDS.CODE_UID = 49)
This Query will return the following
Project Name Calendar Code ID Project ID Field Value CODE UID
ProjectName.Published Standard 188744608 194 Closed.Site B 49