By Jonathan Sofer
MSPS 2007 has a new feature called Timesheets. Resources can use timesheets to track hours spent on projects, activities as well as administrative time. This is done through “My Timesheets” under “My Work”. Before your resources can create their timesheets, an administrator must first define the organization’s timesheet periods. This is done in PWA under Server Settings>Timesheet Periods.
The advantage of timesheet periods is that they can be closed by the organization to prevent resources from editing historical information that might be used for finance systems as an example.
There is an issue discovered in MSPS 2007 that even after timesheet periods have been closed, the resource still has the ability to recall and then delete their timesheets. They will not have the ability to edit the timesheets in the closed time periods or re-create the timesheet once they have deleted it. However, the fact that they can recall and delete the timesheet can be a serious issue as historical reported hours can be lost.
Below are instructions on how an administrator can lock all timesheets in the closed time periods. Locking the timesheet means that the resource will not be able to recall and delete it. We accomplish this by setting the “TS_IS_PROCESSED” field to 1 on MSP_TIMESHEETS table directly in the published database.
Note: Microsoft does not recommend making updates to the published database so be aware that you are doing this at your own risk. However, Microsoft has also not released any fixes for this issue to date.
Closing Timesheet Periods:
Below are the SQL statements used to perform the lockdown of the timesheet periods. This is all performed on the Published database.
The first SELECT query shows a count by timesheet status of all timesheets in the system up to a specific date. This article assumes that your organization has performed all the required submissions and approvals of timesheets and that all timesheet that have been created in the past have been submitted and approved. The end state you wish to achieve is that all timesheets show up as “Approved” which is the value 3.
The “Status ID” of the timesheet. “TimesheetEnum.Status” enumeration values are “In Progress”=0, “Submitted”=1, “Acceptable”=2, “Approved”=3, and “Rejected”=4.
SELECT TOP (100) PERCENT COUNT(dbo.MSP_TIMESHEETS.TS_STATUS_ENUM) AS Counts,
FROM dbo.MSP_TIMESHEETS INNER JOIN
dbo.MSP_WEB_TIME_PERIODS ON dbo.MSP_TIMESHEETS.WPRD_UID =
WHERE (dbo.MSP_WEB_TIME_PERIODS.WPRD_FINISH_DATE <= CONVERT(DATETIME, '2008-04-30 00:00:00', 102))
GROUP BY dbo.MSP_TIMESHEETS.TS_STATUS_ENUM
ORDER BY dbo.MSP_TIMESHEETS.TS_STATUS_ENUM
Once you have verified that all the timesheets up to a specific date are “Approved” you can then close those time periods through the PWA interface.
- Go to Server Settings>Timesheet Periods
- Set the “Status” field to “Closed” for the appropriate timesheet periods
- Click “Save”
- If you have verified with the SQL statement that all timesheets have been “Approved” then you should receive no warning messages here.
- If you do receive warning messages about timesheets that are still being processed then cancel out of the closing and resolve the timesheets in question. Start in step 1 once all timesheets have been “Approved”
Locking Timesheets in Closed Time Periods:
The SQL statement below is used to perform the lockdown of the timesheet periods. This is all performed on the PUBLISHED database.
This update statement below is used to lock the timesheets in the periods we just closed. It accomplishes this by setting the MSP_TIMESHEETS.TS_IS_PROCESSED field to 1 for all “Approved” timesheets and where the TS_IS_PROCESSED flag is currently 0
*It would be a good idea to test this process out on a dev or sandbox environment first before proceeding to production.
UPDATE STATEMENT 1:
SET dbo.MSP_TIMESHEETS.TS_IS_PROCESSED = 1
WHERE ts_uid in (
SELECT distinct dbo.MSP_TIMESHEETS.TS_UID
FROM dbo.MSP_TIMESHEETS INNER JOIN dbo.MSP_WEB_TIME_PERIODS ON
dbo.MSP_TIMESHEETS.WPRD_UID = dbo.MSP_WEB_TIME_PERIODS.WPRD_UID
WHERE ((dbo.MSP_TIMESHEETS.TS_IS_PROCESSED = 0) –-- timesheet not yet locked
and (dbo.MSP_WEB_TIME_PERIODS.WPRD_STATE_ENUM = 1) --closed timesheet period
AND (dbo.MSP_TIMESHEETS.TS_STATUS_ENUM = 3))) –-approved timesheets only