Importing timesheets to tasks

[BudOlly]

Hello,

Thanks goes to anyone who takes time to read this!

In PWA, we are going to have everyone update tasks by importing their
timesheets. I want to be able to send out reminders for people to do this.
Does anyone know of a 'flag' field in PWA's "Published" database that would
indicate whether or not someone has imported their timesheet?

I'd like to be able to check the database for this and send out e-mails to
those who have NOT imported and updated their tasks.

 

[Barbara - Austria]

Hi BudOlly,

I am not aware of any flag like this. Perhaps there is anyone else?

The only idea I have is to comepare TimeByDay data of Timesheets and
Tasks in the database. Unfortunately imported hours has also to be
submitted, accepted and published to be available for this comparison:

SELECT dbo.MSP_TimesheetActual_OlapView.TimeByDay,
dbo.MSP_TimesheetPeriod_OlapView.PeriodName,
dbo.MSP_TimesheetProject_OlapView.ProjectName,
dbo.MSP_TimesheetTask_OlapView.TaskName,
dbo.MSP_TimesheetResource_OlapView.ResourceName,
ISNULL(dbo.MSP_EpmAssignmentByDay_UserView.AssignmentActualWork, 0) AS
AssignmentActualWork,
ISNULL(dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable, 0) AS
ActualWorkBillable
FROM dbo.MSP_EpmAssignmentByDay_UserView RIGHT OUTER JOIN
dbo.MSP_Timesheet_OlapView INNER JOIN
dbo.MSP_TimesheetLine_OlapView ON
dbo.MSP_Timesheet_OlapView.TimesheetUID =
dbo.MSP_TimesheetLine_OlapView.TimesheetUID INNER JOIN
dbo.MSP_TimesheetPeriod_OlapView ON
dbo.MSP_Timesheet_OlapView.PeriodUID =
dbo.MSP_TimesheetPeriod_OlapView.PeriodUID INNER JOIN
dbo.MSP_TimesheetActual_OlapView ON
dbo.MSP_TimesheetLine_OlapView.TimesheetLineUID =
dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID INNER JOIN
dbo.MSP_TimesheetProject_OlapView ON
dbo.MSP_TimesheetLine_OlapView.ProjectNameUID =
dbo.MSP_TimesheetProject_OlapView.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetTask_OlapView ON
dbo.MSP_TimesheetLine_OlapView.TaskNameUID =
dbo.MSP_TimesheetTask_OlapView.TaskNameUID INNER JOIN
dbo.MSP_TimesheetResource_OlapView ON
dbo.MSP_Timesheet_OlapView.OwnerResourceNameUID =
dbo.MSP_TimesheetResource_OlapView.ResourceNameUID ON
dbo.MSP_EpmAssignmentByDay_UserView.AssignmentUID =
dbo.MSP_TimesheetLine_OlapView.AssignmentUID AND
dbo.MSP_EpmAssignmentByDay_UserView.TimeByDay =
dbo.MSP_TimesheetActual_OlapView.TimeByDay
WHERE (dbo.MSP_TimesheetActual_OlapView.TimeByDay < { fn NOW() }) AND
(ISNULL(dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable, 0) -
ISNULL(dbo.MSP_EpmAssignmentByDay_UserView.AssignmentActualWork, 0) <> 0)

It's just an idea ...
Regards
Barbara

Am 23.02.2010 18:14, schrieb BudOlly:

 

[Inquisit1]

Barbara,

Thanks for you suggestions...I was thinking of doing something similar by
finding and comparing multiple fields from multiple tables. It's just a
matter of finding the relevant fields....

 

[Inquisit1]

Hello,

After looking at several tables in my "Published" database I think I've
found a possible combination that I can look at to see if a user needs to
import hours from their timesheet:

Looking at the dbo.MSP_TIMESHEET_LINES table I can find out which lines have
relevant data. Grabbing the TS_LINE_UID I can look at the
dbo.MSP_TIMESHEET_ACTUALS table and add up the TS_ACT_VALUE fields for all
the records corresponding to the TS_LINE_UID (this should be the total number
of recorded hours for a single line on the users timesheet)

Once I have that value, I can compare it to the
dbo.MSP_ASSIGNMENTS_SAVED.ASSN_ACT_WORK field for the record corresponding to
the ASSN_UID, and if the ASSN_ACT_WORK field is less than the total I got
from the TS_ACT_VALUE fields I should know that the user has more hours
recorded on their timesheet for that assignment and therefore needs to import
those hours to their task.

I think this is on the right track, but my intuition tells me I'm going to
run into problems when a task spans across multiple timesheets. I may have
to run an additional process to check for ALL timesheets that contain data
for a given task for a given user, or check for a date range or something.

If anybody has any thoughts or suggestions about this, please let me know,
but I'm going to proceed in this direction for now.