EPM Central Project Server Tutorials
Home Deployment    MS Project PWA SharePoint Articles Administrators FAQs Consulting Search Site Map
  Error Messages

Project Server 2003

PWA Login DropDown

Inactive vs. Delete

Actual Work Protected

  2003 SQL Queries

  Enterprise Fields
    Project Fields
    Resource Fields
    Sample Queries

Admin Tools

Re-associate Site

Project Archival

  VBA

 
 
 
Administration -> MS Project Server 2003 -> DB Scripts
PWA Timesheet

*******************************************************************************

1) Resource entered task update in time sheet and saved, but not yet sumbitted

How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database.

Script:

SELECT MSP_WEB_PROJECTS.PROJ_NAME, MSP_WEB_RESOURCES.RES_NAME,
MSP_WEB_RESOURCES.WRES_EMAIL,
MSP_WEB_ASSIGNMENTS.TASK_NAME,
MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_NEEDED,
MSP_WEB_ASSIGNMENTS.WASSN_ACTUALS_PENDING,
MSP_WEB_ASSIGNMENTS.WASSN_UPDATE_STATUS,
MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_DATE
FROM MSP_WEB_ASSIGNMENTS INNER JOIN
MSP_WEB_PROJECTS ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN
MSP_WEB_RESOURCES ON MSP_WEB_ASSIGNMENTS.WRES_ID = MSP_WEB_RESOURCES.WRES_ID
WHERE (MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_NEEDED = 1) AND
(MSP_WEB_ASSIGNMENTS.WASSN_ACTUALS_PENDING = 0) AND
(MSP_WEB_ASSIGNMENTS.WASSN_UPDATE_STATUS = 1) AND
(MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_DATE IS NULL)



***********************************************************************************

2) Resource Subimitted Task Update, but not yet approved by PM

How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database.

Script:

SELECT MSP_WEB_PROJECTS.WPROJ_ID, MSP_WEB_PROJECTS.PROJ_NAME, MSP_WEB_RESOURCES_1.RES_NAME AS OWNER,
MSP_WEB_RESOURCES.RES_NAME, MSP_WEB_ASSIGNMENTS.WASSN_ID, MSP_WEB_ASSIGNMENTS.TASK_NAME,
MSP_WEB_ASSIGNMENTS.ASSN_ACT_WORK / 60000 AS ACTL_WORK,
MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_DATE AS UPDATE_TIME,
MSP_WEB_RESOURCES_2.RES_NAME AS ASSIGNMENT_MGR
FROM MSP_WEB_ASSIGNMENTS INNER JOIN
MSP_WEB_PROJECTS ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN
MSP_WEB_RESOURCES ON MSP_WEB_ASSIGNMENTS.WRES_ID = MSP_WEB_RESOURCES.WRES_ID INNER JOIN
MSP_WEB_RESOURCES MSP_WEB_RESOURCES_1 ON MSP_WEB_PROJECTS.WRES_ID = MSP_WEB_RESOURCES_1.WRES_ID INNER JOIN
MSP_WEB_RESOURCES MSP_WEB_RESOURCES_2 ON MSP_WEB_ASSIGNMENTS.WRES_ID_MGR = MSP_WEB_RESOURCES_2.WRES_ID
WHERE (MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_NEEDED = 1) AND (MSP_WEB_ASSIGNMENTS.WASSN_ACTUALS_PENDING = 1) AND
(MSP_WEB_ASSIGNMENTS.WASSN_UPDATE_STATUS = 1) AND (MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_DATE IS NOT NULL)


*********************************************************************************

3) Task Updates approved by PM

How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database.

Script:

SELECT MSP_WEB_PROJECTS.PROJ_NAME, MSP_WEB_RESOURCES.RES_NAME,
MSP_WEB_ASSIGNMENTS.TASK_NAME AS TaskName,
MSP_WEB_ASSIGNMENTS.WASSN_ACTUALS_PENDING,
MSP_WEB_ASSIGNMENTS.WASSN_UPDATE_STATUS,
MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_DATE
FROM MSP_WEB_ASSIGNMENTS INNER JOIN
MSP_WEB_PROJECTS ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN
MSP_WEB_RESOURCES ON MSP_WEB_ASSIGNMENTS.WRES_ID = MSP_WEB_RESOURCES.WRES_ID
WHERE (MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_NEEDED = 0) AND
(MSP_WEB_ASSIGNMENTS.WASSN_ACTUALS_PENDING = 0) AND
(MSP_WEB_ASSIGNMENTS.WASSN_UPDATE_STATUS = 0) AND
(MSP_WEB_ASSIGNMENTS.WASSN_SEND_UPDATE_DATE IS NOT NULL)


********************************************************************************



Copyright 2013. EPM Central. All rights reserved.