Reocrds in timesheet lines table without records in tasks table

[JBG]

Below is a query I executed and the results was 181 records.

The purpose of the first query is to actually extract all timesheet line
records approved for timesheet class type=0 (tasks related to project plans)
and the timesheet being approved (status=3) for January 2008.

The purpose of the second query is to actually link the timesheet_lines
results with the tasks table to enable me in retrieving more information
about the specific tasks that a timesheet was linked to.

This gives me the impression that there is actually tasks in the timesheet
lines table with a specific task_uid, and the corresponding task_uid in the
tasks table does not exist. Any help on this would be appreciated.

SELECT distinct b.TS_LINE_CACHED_PROJ_NAME AS
view_ts_proj_noaxapta_projectname,
b.TS_LINE_CACHED_ASSIGN_NAME AS view_ts_proj_noaxapta_taskname

FROM dbo.MSP_TIMESHEETS AS a INNER JOIN
dbo.MSP_TIMESHEET_LINES AS b ON a.TS_UID = b.TS_UID INNER JOIN
dbo.MSP_TIMESHEET_CLASSES AS c ON b.TS_LINE_CLASS_UID =
c.TS_LINE_CLASS_UID INNER JOIN
dbo.MSP_TIMESHEET_ACTUALS AS d ON b.TS_LINE_UID = d.TS_LINE_UID
INNER JOIN
dbo.MSP_RESOURCES AS e ON a.RES_UID = e.RES_UID

WHERE (c.TS_LINE_CLASS_TYPE = 0) AND
(a.TS_STATUS_ENUM = 3) AND
(d.TS_ACT_VALUE + d.TS_ACT_NON_BILLABLE_VALUE > 0) AND
YEAR(d.TS_ACT_START_DATE) = 2008 AND
MOnth(d.TS_ACT_START_DATE) = 1

except

SELECT distinct b.TS_LINE_CACHED_PROJ_NAME AS
view_ts_proj_noaxapta_projectname,
b.TS_LINE_CACHED_ASSIGN_NAME AS view_ts_proj_noaxapta_taskname

FROM dbo.MSP_TIMESHEETS AS a INNER JOIN
dbo.MSP_TIMESHEET_LINES AS b ON a.TS_UID = b.TS_UID INNER JOIN
dbo.MSP_TIMESHEET_CLASSES AS c ON b.TS_LINE_CLASS_UID =
c.TS_LINE_CLASS_UID INNER JOIN
dbo.MSP_TIMESHEET_ACTUALS AS d ON b.TS_LINE_UID = d.TS_LINE_UID
INNER JOIN
dbo.MSP_RESOURCES AS e ON a.RES_UID = e.RES_UID INNER JOIN
dbo.msp_tasks as f ON b.task_uid = f.task_uid

WHERE (c.TS_LINE_CLASS_TYPE = 0) AND
(a.TS_STATUS_ENUM = 3) AND
(d.TS_ACT_VALUE + d.TS_ACT_NON_BILLABLE_VALUE > 0) AND
YEAR(d.TS_ACT_START_DATE) = 2008 AND
MOnth(d.TS_ACT_START_DATE) = 1


order by b.TS_LINE_CACHED_PROJ_NAME

 

[Rod Gill]

The time sheet tables record data on Tasks exactly as they were at the time
of the timesheet creation. If tasks in Project are then deleted, or someone
cuts and pastes then the original task in the Tasks table is gone, but the
original timesheet data has not. You may therefore get a mismatch.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[JBG]

Hi Rod

Thanks for the reply. Indeed you are correct as I have noticed this in the
past.

My concern is that this query extracted the same projectname, taskname for
various resources working on the same day, and each of the task_uid's were
different. It is a task in a generic project, with an end date way in the
future, with which we have enabled people to book hours against certain
generic (not administrative) tasks.

Your assistance is appreciated.

 

[Rod Gill]

I don't understand your projects here. The timesheets should only show tasks
active at the time they are created and they should only be created just
before filling them in. A Task keeps the UID for life. Cut and paste deletes
it as does deleting. A copied task also gets a new UID. If you take a
project then save a new copy then each task will get a new UID as every
Project, Task, Resource and Assignment has a unique UID within your Project
Server instance.


--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[JBG]

Rod

Thanks for the reply. It is also very strange to me, as we do not change
the information once tasks have been published and/or assigned to resources.
I am not sure if it is relevant, but at some stage my queue got stuck and
after some trouble the consultant assiting with the initial rollout restarted
the queue and event services. By doing this, the queue actually got going
again, but every know and again it got stuck, due to some arithmetic
overflow error (see a previous question of mine, on which I received no
reply). To ensure we could continue, I then manually restarted the services
per day and at some stage had a script that restarted the service every 15
minutes. After assistance from Microsoft I managed to get the queue back in
a normal state. I am not sure if these actions could have caused problems we
are experiencing know.

I have actually changed the query and are using the assignment field in
stead of task field. This has provided much better results, although it is
not 100% correct yet as I got some assn_uid's without assignment records.

One more question. What would trigger the assign_uid to be re-calculated?

 

[Rod Gill]

I think only cut and paste of the Task complete with assignments.

Stopping and starting the queue every 15 minutes would cause problems. I
just have it automatically start early morning well after any backup or cube
building will have finished.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[JBG]

Thanks Rod

I know realize the sensitivity of tasks and timesheets. It basically means
once a timsheet is booked against a task, that task must stay put. You can
not even add additional staff to that task. Strange behaviour, but OK.

A final question. The reason for me trying to get back to the original
task, was because I have custom fields against that task which I use to
report on. After getting your much appreciated help on the subject, I have
stumbled across the table MSP_TIMESHEET_CUSTOM_FIELD_VALUES, which almost
made me glad as I saw the link between custom fields and timesheets.
Unfortunately I notied that this table only contains a couple of records way
back in the past, so it is not being popluated at the moment. What is the
purpose of this field and how could it be populated?

 

[Rod Gill]

Don't touch any of the tables in the draft or publish dbs as Microsoft
reserves the right to change those as needed in hotfixes and SP's. Instead
use the Reporting db. The Views ending with _userview all have the custom
fields added to them automatically.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx