|
|
| |
|
Enterprise Resource Rates:
The following query displays the list all enterprise resources with their standard rate and also indicates whether resource is real resource or generic resource.
How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database.
SQL Query:
SELECT B.res_uid AS RES_UID,
convert(int,[ResourceEnterpriseNumber1]) as RSRC_ID,
CONVERT(VARCHAR(1000),RES_NAME) as RSRC_NM,
B.RES_STD_RATE as RES_RATE,
CASE
WHEN ResourceEnterpriseGeneric = 1 THEN 'Generic'
WHEN ResourceEnterpriseGeneric = 0 THEN 'Real'
END AS RES_TYPE
FROM
dbo.MSP_VIEW_RES_ENT A
JOIN
dbo.MSP_RESOURCES B
ON A.ENT_ResourceUniqueID=B.res_uid and b.proj_id=1
Enterprise resource pool always shows the Standard rate for current period. The future rates will not show until that day. All future rates in cost tables are saved in MSP_Resource_Rates table in binary format. Microsoft using the internal application layer to read and display binary date in resource sheet views of the project
plan and in enterprise resource pool. The good thing here is, MSP_Resource_Rates always holds the project resources information with all details like standard rate, effective date, cost table name etc. We can take advantage of this feature.
Here is the workaround to get the cost rates table information using SQL query. You have to create the dummy project with all enterprise resource names. Just add all enterprise resource names in project plan and save it to server. No tasks and assignments required. It's just an blank plan with all enterprise resource names.
Please make sure, whenever new enterprise resource added to pool, add
the same name in this project plan.
Steps to find the enterprise resource cost table rates:
- Create a dummy project called “EPM Resource Rates”
- Add all the enterprise resources using Build Team or similar
method
- Save project plan in to server
- Find the PROJ_ID for EPM Resource Rates by running following
SQL Query
SELECT PROJ_ID FROM MSP_PROJECTS
WHERE PROJ_NAME = ‘EPM Resource Rates’
- Assume, you have PROJ_ID is 603; Run the following SQL to get the enterprise resource rate info for each cost table with effective dates
SELECT MSP_RESOURCES.RES_NAME,
CASE MSP_RESOURCE_RATES.RR_RATE_TABLE
WHEN 0 THEN 'Cost Table A'
WHEN 1 THEN 'Cost Table B'
WHEN 2 THEN 'Cost Table C'
WHEN 3 THEN 'Cost Table D'
WHEN 4 THEN 'Cost Table E'
END AS RATE_TABLE,
MSP_RESOURCE_RATES.RR_FROM_DATE AS EFFECTIVE_DATE,
MSP_RESOURCE_RATES.RR_TO_DATE AS TILL_DATE,
MSP_RESOURCE_RATES.RR_STD_RATE, MSP_RESOURCE_RATES.RR_OVT_RATE
FROM MSP_RESOURCE_RATES INNER JOIN
MSP_RESOURCES ON MSP_RESOURCE_RATES.PROJ_ID = MSP_RESOURCES.PROJ_ID
WHERE (MSP_RESOURCE_RATES.PROJ_ID = 603) AND
(MSP_RESOURCES.RES_NAME IS NOT NULL)
ORDER BY MSP_RESOURCES.RES_NAME
| |
|
|
