In Project Web Access for Project Server 2003, users receive task messages about unsubmitted time sheets or unsubmitted tasks for closed time periods (897769)


The information in this article applies to:

  • Microsoft Office Project Server 2003
  • Microsoft Office Project Server 2003, Service Pack 1 (SP1)

SYMPTOMS

When you log on to Microsoft Office Project Web Access 2003, you receive the following message in the Tasks section of your home page:Your timesheet has not been submitted for the period date range for time period.The message includes unsubmitted tasks or unsubmitted timesheets for a time period that is closed.

Note The messages do not appear for time periods that are three months before the current date or older.

CAUSE

This problem occurs because Microsoft Office Project Server 2003 and Project Web Access 2003 do not let you modify the kind of task messages (alerts) that are displayed on the Project Web Access 2003 home page. Project Web Access 2003 uses the following stored procedures to obtain task message information:
  • MSP_WEB_SP_QRY_CountOfNewTasks
  • MSP_WEB_SP_QRY_CountOfOverdueStatusReports
  • MSP_WEB_SP_QRY_CountOfOverdueTasks
  • MSP_WEB_SP_QRY_CountOfPendingApprovals
  • MSP_WEB_SP_QRY_CountOfUnsubmittedTasks
  • MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets

WORKAROUND

To work around this problem, manually modify the Microsoft SQL Server stored procedures that Project Web Access 2003 uses to obtain task messages. The example scripts in this section support two variations, depending on what you want to filter. Depending on the lines that you comment out in the script, you can filter the task messages on many values. These include a static date and whether a time period is closed.

Warning Serious problems might occur if you manually modify the Project Server database. These problems might require that you reinstall Project Server or SQL Server. We cannot guarantee that you can solve problems that occur after you incorrectly modify the database. Manually modify the database at your own risk. We recommend that you perform a backup of the Project Server database before you follow these steps.

To modify the stored procedures that Project Web Access 2003 uses, follow these steps on the computer that is running SQL Server:
  1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  2. On the Query menu, click Change Database.
  3. Click the Project Server database, and then click OK.
  4. Copy the appropriate Unsubmitted Tasks script that is provided later in this article. Then, paste the script into the Query window. The Project Server 2003 original release version of the script and the Microsoft Project Server 2003 Service Pack 1 (SP1) version of the script are provided later in this article.
  5. On the Query menu, click Execute. After the script is completed, click Clear Window on the Edit menu.
  6. Copy the appropriate Unsubmitted Time Sheets script that is provided later in this article. Then, paste the script into the Query window. The Project Server 2003 original release version of the script and the Project Server 2003 SP1 version of the script are provided later in this article.
  7. On the Query menu, click Execute.
Use one of the following scripts, depending on the level of Project Server 2003 that you are running. For example, if you are running Project Server 2003 without a service pack, use the following scripts:
  • Unsubmitted Tasks on the original release version of Project Server 2003
  • Unsubmitted Time Sheets on the original release version of Project Server 2003
The scripts that are provided in this article specifically configure the stored procedures for unsubmitted tasks and for unsubmitted time sheets. You can modify these scripts to configure other stored procedure for other types of task messages. To modify the scripts, replace "MSP_WEB_SP_QRY_CountOfUnsubmittedTasks" and "MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets" in the scripts with the stored procedures for the other task messages that you want to configure. The stored procedures are listed in the "Cause" section.

Scripts

Unsubmitted Tasks on the original release version of Project Server 2003 

--******************************
-- This Script modifies the existing stored procedure
-- for unsubmitted tasks on Project Server 2003 RTM
--******************************
if exists(select * from sysobjects where name = 'MSP_WEB_SP_QRY_CountOfUnsubmittedTasks')
drop procedure dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks
go
CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks
@p0 INT,
@p1 DATETIME
AS
SELECT DISTINCT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE
FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON
(WTP.WPRD_START_DATE<WA.ASSN_FINISH_DATE AND WTP.WPRD_FINISH_DATE>
WA.ASSN_START_DATE AND WTP.WPRD_ID>=100) INNER JOIN MSP_WEB_PROJECTS WP ON
(WA.WPROJ_ID = WP.WPROJ_ID)
WHERE WA.WRES_ID =@p0
AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE
AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE
WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND
WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE AND WAA.WAPPROVAL_STATUS <> 2)
AND WA.TASK_IS_SUMMARY = 0
AND WP.WPROJ_TYPE <> 3
AND WA.ASSN_BOOKING_TYPE = 0
AND WA.WASSN_REMOVED_BY_RESOURCE=0
AND (WA.WASSN_IS_CONFIRMED<>0 or WP.WPROJ_ADMINPROJECT = 1)
AND WA.WASSN_DELETED_IN_PROJ = 0
--*******************************
--the first option filters out alerts the fall before the date specified
--the second option filters out alerts that fall with in a closed time period
AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000'
AND WTP.WPRD_STATE = 0
--*******************************

ORDER BY WTP.WPRD_START_DATE

RETURN
go
grant execute on MSP_WEB_SP_QRY_CountOfUnsubmittedTasks to MSProjectServerRole
go

Unsubmitted Tasks on Project Server 2003 SP1

--******************************
-- This Script modifies the existing stored procedure
-- for unsubmitted tasks on Project Server 2003 SP1
--******************************
CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks
@p0 INT,
@p1 DATETIME
AS
SELECT DISTINCT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE
FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON
(WTP.WPRD_START_DATE< WA.ASSN_FINISH_DATE AND WTP.WPRD_FINISH_DATE>
WA.ASSN_START_DATE AND WTP.WPRD_ID>=100) INNER JOIN MSP_WEB_PROJECTS WP ON
(WA.WPROJ_ID = WP.WPROJ_ID)
WHERE WA.WRES_ID =@p0
AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE
AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE
WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND
WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE AND WAA.WAPPROVAL_STATUS <> 2)
AND WA.TASK_IS_SUMMARY = 0
AND WP.WPROJ_TYPE <> 3
AND WA.ASSN_BOOKING_TYPE = 0
AND WA.WASSN_REMOVED_BY_RESOURCE=0
AND (WA.WASSN_IS_CONFIRMED<>0 or WP.WPROJ_ADMINPROJECT = 1)
AND WA.WASSN_DELETED_IN_PROJ = 0
--*******************************
--the first option filters out alerts the fall before the date specified
--the second option filters out alerts that fall with in a closed time period
AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000'
AND WTP.WPRD_STATE = 0
--*******************************
ORDER BY WTP.WPRD_START_DATE
RETURN
GO
GRANT EXECUTE ON dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks TO
MSProjectServerRole
GO

Unsubmitted Time Sheets on the original release version of Project Server 2003

--******************************
-- This Script modifies the existing stored procedure
-- for unsubmitted time sheets on Project Server 2003 RTM
--******************************
if exists(select * from sysobjects where name =
'MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets')
drop procedure dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets
go
CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets 
@p0 INT,
@p1 DATETIME
AS
SELECT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE, COUNT (DISTINCT WA.WRES_ID) AS
TCOUNT
FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON
(WTP.WPRD_ID>=100 AND ((WTP.WPRD_START_DATE< WA.ASSN_FINISH_DATE AND
WTP.WPRD_FINISH_DATE> WA.ASSN_START_DATE) OR (((WA.WASSN_ACTUALS_PENDING <> 0) OR
(WA.WASSN_PCT_COMP< 100 AND WASSN_PCT_COMP>0)) AND WA.ASSN_START_DATE<=
WTP.WPRD_FINISH_DATE)))
INNER JOIN MSP_WEB_PROJECTS ON (WA.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID)

WHERE
(WA.WASSN_IS_CONFIRMED<>0 OR MSP_WEB_PROJECTS.WPROJ_ADMINPROJECT<>0)
AND WA.WASSN_REMOVED_BY_RESOURCE=0
AND MSP_WEB_PROJECTS.WPROJ_TYPE <> 3
AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE
AND WA.WRES_ID in (
-- Permission 212: Approve timesheet permission
select WRES_ID from
MSP_WEB_FN_SEC_GetAllResourcesResCanViewByViewID(@p0, 212, -1)
)
AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE
WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND
WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE )
--*******************************
--the first option filters out alerts the fall before the date specified
--the second option filters out alerts that fall with in a closed time period
AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000'
AND WTP.WPRD_STATE = 0
--*******************************
GROUP BY WTP.WPRD_START_DATE,WTP.WPRD_FINISH_DATE
ORDER BY WTP.WPRD_START_DATE
RETURN
go
grant execute on MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets to
MSProjectServerRole
go

Unsubmitted Time Sheets on Project Server 2003 SP1

--****************************** 
-- This Script modifies the existing stored procedure 
-- for unsubmitted time sheets on Project Server 2003 SP1 
--******************************
CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets
@p0 INT,
@p1 DATETIME
AS
SELECT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE, COUNT (DISTINCT WA.WRES_ID) AS
TCOUNT
FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON
(WTP.WPRD_ID>=100 AND WTP.WPRD_START_DATE<WA.ASSN_FINISH_DATE AND
WTP.WPRD_FINISH_DATE>WA.ASSN_START_DATE)
INNER JOIN MSP_WEB_PROJECTS ON (WA.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID)
WHERE
(WA.WASSN_IS_CONFIRMED<>0 OR MSP_WEB_PROJECTS.WPROJ_ADMINPROJECT<>0)
AND (WA.WASSN_IS_CONFIRMED<>0 or MSP_WEB_PROJECTS.WPROJ_ADMINPROJECT = 1)
AND WA.WASSN_DELETED_IN_PROJ = 0
AND WA.TASK_IS_SUMMARY = 0
AND (WA.WASSN_REMOVED_BY_RESOURCE=0 OR WA.WASSN_LOCKDOWN_BY_MANAGER<>0)
AND WA.ASSN_BOOKING_TYPE = 0
AND MSP_WEB_PROJECTS.WPROJ_TYPE <> 3
AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE
AND WA.WRES_ID in (
-- Permission 212: Approve timesheet permission
select WRES_ID from
MSP_WEB_FN_SEC_GetAllResourcesResCanViewByViewID(@p0, 212, -1)
)
AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE
WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND
WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE)
--*******************************
--the first option filters out alerts the fall before the date specified
--the second option filters out alerts that fall with in a closed time period
AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000'
AND WTP.WPRD_STATE = 0
--*******************************
GROUP BY WTP.WPRD_START_DATE,WTP.WPRD_FINISH_DATE
ORDER BY WTP.WPRD_START_DATE
RETURN
GO
GRANT EXECUTE ON dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets TO
MSProjectServerRole
GO

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Modification Type:MinorLast Reviewed:7/27/2006
Keywords:kbStoredProc kbDBase kbtshoot kbDatabase kbUser kbSysSettings KB897769 kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.