微软项目管理[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