微软项目管理[EPM]数据库应用举例2: 取得一个项目的某大纲代码的值
microsoft project server
enterprise project outline code
涉及的表
表名 |
说明 |
MSP_WEB_PROJECTS |
项目表 |
MSP_CODE_FIELDS |
大纲代码Field_ID和Code_UID对应表 |
MSP_OUTLINE_CODES |
大纲代码表 |
MSP_FIELD_ATTRIBUTES |
定义域属性;该表包含与域属性(如自定义 WBS、自定义域名称别名和自定义域公式等)有关的数据。 |
MSP_ATTRIBUTE_STRINGS |
该表存储在 MSP_FIELD_ATTRIBUTES 中所定义的自定义 WBS 代码定义、别名和公式。 |
-- 先取得FieldID
declare @tFieldValue varchar(300)
set @tFieldValue = 'Product Development Manager'
declare @FieldID int
declare @GlobalProjID int
SELECT @GlobalProjID = PROJ_ID FROM MSP_PROJECTS WITH (NOLOCK, INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 2
SELECT @FieldID = A.ATTRIB_FIELD_ID
FROM MSP_FIELD_ATTRIBUTES A,MSP_ATTRIBUTE_STRINGS B
WHERE
A.PROJ_ID = B.PROJ_ID
AND A.AS_ID = B.AS_ID
AND A.ATTRIB_FIELD_ID >= 188744529
AND A.ATTRIB_FIELD_ID <= 188744768
AND A.ATTRIB_ID = 206
AND A.PROJ_ID = @GlobalProjID
and b.AS_VALUE = @tFieldValue
--ORDER BY A.ATTRIB_FIELD_ID
print 'fieldid : ' + cast(@FieldID as varchar(30))
--在表MSP_CODE_FIELDS根据FieldID和项目ID得到code_uid
declare @code_uid int
SELECT @code_uid = CODE_UID FROM MSP_CODE_FIELDS
WHERE PROJ_ID = 22
and
code_Field_ID = @FieldID
--ORDER BY CODE_FIELD_ID,CODE_REF_UID
print @code_uid
--回到表MSP_OUTLINE_CODES找到对应值
select
OC_NAME
FROM MSP_OUTLINE_CODES
WHERE
PROJ_ID = @GlobalProjID
and code_uid =@code_uid
--AND OC_CACHED_IS_VALID > 0 AND OC_FIELD_ID IN (@FieldID) AND CODE_UID > 0
--ORDER BY OC_FIELD_ID,CODE_UID