The following are the three stored procedures i got from the microsoft site
--To Check if the Assignment has actuals
CREATE PROCEDURE dbo.MS_PSS_GetWebWorkTotalByWassnID
@WebAssignmentID int,
@WebWorkTotal float OUTPUT,
@LastDayAWTracked datetime OUTPUT
AS
SET NOCOUNT ON
DECLARE @WASSN_ID int
DECLARE @WWORK_START datetime
DECLARE @WWORK_FINISH datetime
DECLARE @WWORK_TYPE int
DECLARE @WWORK_VALUE decimal
DECLARE @WebWorkTotal_Local decimal
SET @WebWorkTotal = 0
SET @LastDayAWTracked = NULL
CREATE TABLE #WebWork
(
WASSN_ID int,
WWORK_START datetime,
WWORK_FINISH datetime,
WWORK_TYPE int,
WWORK_VALUE decimal
)
INSERT #WebWork
SELECT WASSN_ID,
WWORK_START,
WWORK_FINISH,
WWORK_TYPE,
WWORK_VALUE
FROM MSP_WEB_WORK WHERE WASSN_ID = @WebAssignmentID AND WWORK_TYPE = 1
--Type 1 is Actual Work
DECLARE WebWork CURSOR FOR
SELECT WASSN_ID,
WWORK_START,
WWORK_FINISH,
WWORK_TYPE,
WWORK_VALUE
FROM #WebWork
OPEN WebWork
FETCH NEXT FROM WebWork INTO @WASSN_ID,
@WWORK_START,
@WWORK_FINISH,
@WWORK_TYPE,
@WWORK_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @NumberOfDaysWorkedPerRecord int
SET @NumberOfDaysWorkedPerRecord =
(DATEDIFF(day,@WWORK_START,@WWORK_FINISH)+1)
SET @WebWorkTotal = @WebWorkTotal + (@WWORK_VALUE *
@NumberOfDaysWorkedPerRecord)
SET @LastDayAWTracked = @WWORK_FINISH
FETCH NEXT FROM WebWork INTO @WASSN_ID,
@WWORK_START,
@WWORK_FINISH,
@WWORK_TYPE,
@WWORK_VALUE
END
CLOSE WebWork
DEALLOCATE WebWork
DROP TABLE #WebWork
--To get the duplicate web-assignments
CREATE PROCEDURE dbo.MS_PSS_GetDuplicateWebAssignmentList_Details
AS
CREATE TABLE #DuplicateAssignmentsList
(
ProjectName nvarchar(500),
ResourceName nvarchar(500),
Assignment nvarchar(500),
WASSN_ID int,
WASSN_REMOVED_BY_RESOURCE int,
CreatedViaSelfAssign bit,
WASSN_CREATED_BY_RES int,
ASSN_ACT_WORK float,
WebActualWorkTotal float,
LastDayAWTracked datetime,
WASSN_SEND_UPDATE_DATE datetime,
NumberOfDuplicates int,
ProjectManager nvarchar(500),
PendingPMApproval int,
PendingResourceSubmission int,
PROJ_CREATION_DATE datetime,
WPROJ_LAST_PUB datetime
)
DECLARE @WASSN_ID int
DECLARE @WPROJ_ID int
DECLARE @WRES_ID int
DECLARE @TASK_NAME varchar(500)
DECLARE @TASK_UID int
DECLARE @TASK_UID_SUMMARY int
DECLARE @TASK_IS_SUMMARY int
DECLARE @WASSN_REMOVED_BY_RESOURCE int
DECLARE @WASSN_DELETED_IN_PROJ int
DECLARE @WASSN_ASSIGNED_TO_EXISTING int
DECLARE @WASSN_CREATED_BY_RES int
DECLARE @ASSN_ACT_WORK decimal
DECLARE @WASSN_SEND_UPDATE_DATE datetime
DECLARE @WRES_ID_MGR int --PM id
DECLARE @WASSN_ACTUALS_PENDING int --manager has pending updates
DECLARE @WASSN_UPDATE_STATUS int -- resource has updated, but not send to
manager
DECLARE WebAssignments CURSOR FOR
SELECT WASSN_ID,
WPROJ_ID,
WRES_ID,
TASK_NAME,
TASK_UID,
TASK_UID_SUMMARY,
TASK_IS_SUMMARY
, WASSN_REMOVED_BY_RESOURCE,
WASSN_DELETED_IN_PROJ,
WASSN_ASSIGNED_TO_EXISTING,
WASSN_CREATED_BY_RES,
ASSN_ACT_WORK,
WASSN_SEND_UPDATE_DATE,
WRES_ID_MGR,
WASSN_ACTUALS_PENDING,
WASSN_UPDATE_STATUS FROM MSP_WEB_ASSIGNMENTS
OPEN WebAssignments
FETCH NEXT FROM WebAssignments
INTO @WASSN_ID,
@WPROJ_ID,
@WRES_ID,
@TASK_NAME,
@TASK_UID,
@TASK_UID_SUMMARY,
@TASK_IS_SUMMARY,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_DELETED_IN_PROJ,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK,
@WASSN_SEND_UPDATE_DATE,
@WRES_ID_MGR,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DuplicateCount int
SET @DuplicateCount = 0
IF @WASSN_DELETED_IN_PROJ <> 1 AND @TASK_IS_SUMMARY = 0
BEGIN
SET @DuplicateCount = (SELECT COUNT(*) FROM MSP_WEB_ASSIGNMENTS WHERE
TASK_UID = @TASK_UID
--AND (TASK_UID_SUMMARY = @TASK_UID_SUMMARY OR @TASK_UID_SUMMARY IS
NULL)
AND TASK_IS_SUMMARY = 0
AND WPROJ_ID = @WPROJ_ID
AND WRES_ID = @WRES_ID
AND WASSN_DELETED_IN_PROJ = 0)
END
--temp code to test by deleting all triplicates/quadruplicates
IF (@DuplicateCount >2)
BEGIN
declare @temp int
--UPDATE MSP_WEB_ASSIGNMENTS SET RESERVED_DATA1 = 121212 WHERE WASSN_ID
= @WASSN_ID
--DELETE FROM MSP_WEB_ASSIGNMENTS WHERE RESERVED_DATA1 = 121212
END
IF (@DuplicateCount >1)
BEGIN
DECLARE @PROJECT_NAME nvarchar(500)
SET @PROJECT_NAME = (SELECT PROJ_NAME FROM MSP_WEB_PROJECTS WHERE
WPROJ_ID = @WPROJ_ID)
DECLARE @RESOURCE_NAME nvarchar(500)
SET @RESOURCE_NAME = (SELECT RES_NAME FROM MSP_WEB_RESOURCES WHERE
WRES_ID = @WRES_ID)
DECLARE @ProjectManager nvarchar(500)
SET @ProjectManager = (SELECT RES_NAME FROM MSP_WEB_RESOURCES WHERE
WRES_ID = @WRES_ID_MGR)
DECLARE @WebActualWorkTotal float
DECLARE @LastDayAWTracked datetime
DECLARE @WPROJ_LAST_PUB datetime
SET @WPROJ_LAST_PUB = (SELECT WPROJ_LAST_PUB FROM MSP_WEB_PROJECTS WHERE
WPROJ_ID = @WPROJ_ID)
DECLARE @PROJ_CREATION_DATE datetime
SET @PROJ_CREATION_DATE = (SELECT PROJ_CREATION_DATE FROM MSP_PROJECTS
WHERE PROJ_ID = (SELECT PROJ_ID FROM MSP_WEB_PROJECTS WHERE WPROJ_ID =
@WPROJ_ID))
-- SELECT PROJ_CREATION_DATE FROM MSP_PROJECTS WHERE PROJ_ID = -1
EXEC MS_PSS_GetWebWorkTotalByWassnID @WASSN_ID, @WebActualWorkTotal
OUTPUT, @LastDayAWTracked OUTPUT
INSERT INTO #DuplicateAssignmentsList
(
ProjectName,
ResourceName,
Assignment,
WASSN_ID,
WASSN_REMOVED_BY_RESOURCE,
CreatedViaSelfAssign,
WASSN_CREATED_BY_RES,
ASSN_ACT_WORK,
WebActualWorkTotal,
LastDayAWTracked,
WASSN_SEND_UPDATE_DATE,
NumberOfDuplicates,
ProjectManager,
PendingPMApproval,
PendingResourceSubmission,
PROJ_CREATION_DATE,
WPROJ_LAST_PUB
)
VALUES(
@PROJECT_NAME,
@RESOURCE_NAME,
@TASK_NAME,
@WASSN_ID,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK/60/1000,
@WebActualWorkTotal/60/1000,
@LastDayAWTracked,
@WASSN_SEND_UPDATE_DATE,
@DuplicateCount,
@ProjectManager,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS,
@PROJ_CREATION_DATE,
@WPROJ_LAST_PUB
)
END
FETCH NEXT FROM WebAssignments
INTO @WASSN_ID,
@WPROJ_ID,
@WRES_ID,
@TASK_NAME,
@TASK_UID,
@TASK_UID_SUMMARY,
@TASK_IS_SUMMARY,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_DELETED_IN_PROJ,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK,
@WASSN_SEND_UPDATE_DATE,
@WRES_ID_MGR,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS
END
CLOSE WebAssignments
DEALLOCATE WebAssignments
SELECT NumberOfDuplicates,
ProjectName,
ResourceName,
Assignment,
WASSN_ID,
CreatedViaSelfAssign,
WASSN_CREATED_BY_RES,
WebActualWorkTotal,
LastDayAWTracked,
ProjectManager,
PendingPMApproval,
PendingResourceSubmission,
WASSN_SEND_UPDATE_DATE,
PROJ_CREATION_DATE,
WPROJ_LAST_PUB
FROM #DuplicateAssignmentsList
ORDER BY NumberOfDuplicates, ProjectName, ResourceName, Assignment asc
DROP TABLE #DuplicateAssignmentsList
--To Delete the duplicate web-assignements
CREATE PROCEDURE dbo.MS_PSS_DeleteWebAssignmentByAssnID
@wassn_id_remove varchar(200)
AS
DELETE FROM MSP_WEB_ASSIGNMENTS WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK_ADJUSTED WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK_APPROVAL WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORKGROUP_FIELDS WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_TRANSACTIONS WHERE WASSN_ID=@wassn_id_remove
