Unsubmitted Timesheets

[Rowan Patterson]

Hi All,

I've been reading along the threads in microsoft.public.project.server
discussing whether to use managed time periods, and also about having
resource managers authorise timesheets in parallel to project managers
authorising project updates. All extremely interesting - a good topic
for an MVP to write a "How to ..." white paper maybe?

One item from the discussion which I'm still grappling with is the "You
have 3 resources that have unsubmitted timesheets for the period
26/06/2005 - 2/07/2005." style of message on the home page of project
web access. There's been a lot of discussion on this as well, with
several remedies for the problem of "phantom" warnings suggested. What
I'd like to know is more about the underlying database fields which
track these timesheet statuses.

I've tracked through the Web Access DB schema, and am guessing that the
tables MSP_WEB_WORK_APPROVAL and MSP_WEB_WORK and fields
WRES_ID_APPROVER, WAPPROVAL_STATUS and WWORK_APPROVAL_STATUS are a key
part of the project update approval side of things. Where are timesheet
approvals stored versus project update approvals? How is the
"submitted" status of a timesheet calculated (or where is it stored)?

Any responses gratefully received! Partial answers warmly welcomed!

Thanks & Regards,
Rowan Patterson PMP
Brisbane

 

[Ed Morrison]

Rowan,
msp_web_work_approval is used for the "Approve Timesheet for Resources"
permission.
Project update approvals (when the PM accepts the changes into their plan)
is in the msp_web_work table. When WWORK_UPDATE_STATUS equals 1, the
resource submitted their time, but it has not been accepted by the PM. When
the status equals 0 and WWORK_APPROVAL_STATUS equals 1, the update was
accepted by the PM.

For more information check PJSVRDB.htm in \Program Files\Microsoft Office
Project Server 2003\HELP\1033 on the Project Server.

Hope this helps.

 

[rajanmisra]

Not exactly sure about your requirement.

Did you have a look at the link below

http://support.microsoft.com/default.aspx?scid=kb;en-us;897769

Hope that helps
- Rajan

 

[Rowan Patterson]

Excellent responses, thanks Ed and Rajan.

The PJSVRDB.htm help file data reference is exactly what I've been
searching for - which DB fields do what.

The knowledge base article was new for me as well.

Thanks again,
Rowan.

 

[Rowan Patterson]

Further question please? Is there a sample query for detemining a
resource's resource manager from the RBS? So for example on an
unapproved timesheet, how does one query the resource manager from whom
approval is being requested?

Thanks again,
Rowan Patterson PMP
Brisbane Australia

 

[izzydogg701]

Further question please: i just want to find out the table name an
column name that stores the data when the resource book hours worked o
PWA, just after pressing the "Save changes" button before the Update al
or update selected rows in web access, because it says changes was save
on project server.

Thanks in advance


-
izzydogg70

 

[Rowan Patterson]

-- Return a table of managers and their staff
-- Variables
declare @resource_wres_id as int
declare @manager_wres_id as int

-- Create temporary manager-staff mapping table
create table #org_data (mgr_wres_id int, resource_wres_id int)

-- Define cursor to step through managers
declare mgr cursor for
select
m.wres_id
from
msp_web_resources m
where
m.wres_is_manager = 1
open mgr

-- Step though managers and find staff
fetch next from mgr into @manager_wres_id
while @@fetch_status <> -1
begin
insert into #org_data
select @manager_wres_id, wres_id
from MSP_WEB_FN_SEC_GetResourcesManagedByResID(@manager_wres_id,1,1)
fetch next from mgr into @manager_wres_id
end

-- Clean up cursor
close mgr
deallocate mgr

-- Return result set from temporary table
Select
mgr_wres_id,
m.res_name,
resource_wres_id,
r.res_name
from #org_data, msp_web_resources m, msp_web_resources r
where
mgr_wres_id = m.wres_id
AND resource_wres_id = r.wres_id
and r.res_name not like 'generic%' -- filter out generic resources
and r.res_name not like 'team%' -- filter out test resources
and m.res_name not like 'team%' -- filter out test resources
and r.wres_is_enabled = 1
and m.wres_is_enabled = 1


--clean up temp table
drop table #org_data