Script to clean up orphaned assignments

[Reid McTaggart]

I need a script or some method that would enable me to find and fix “orphaned
assignments.â€

Here is the customer’s scenario:

Published a plan and assignments.
Deleted some tasks that had assignments .
Did NOT immediately republish.
Resources reported time against the deleted assignments.
Manager later processed updates and got failure messages on the deleted
assignments.
Now, the deleted assignments are still in the resources’ timesheets, and the
numbers they reported are black, not red, so they are hard to identify.

It is extremely difficult and time-consuming to examine every resource’s
timesheet, match task names to valid tasks in projects, and then hide invalid
assignments from the resource’s timesheet. There are about 100 resources and
100 projects, so a manual approach could take a week or more and would surely
make my eyes cross and my brain implode.

It would be great if there were a SQL script or method to scan the database,
find orphaned assignments, and clean them up.

BTW, this client uses managed time periods, so I am very limited when
working with Actual Work Protected.

Any solution would have to be tried and tested since we are working on a
Production environment.

Many thanks,

 

[Gaurav Wason]

UPDATE MSP_WEB_ASSIGNMENTS
SET WASSN_REMOVED_BY_RESOURCE = 1
WHERE WASSN_Deleted_In_Proj = 1
and task_is_Summary = 0