1 -- move approver tasks to PushTaskLog
2
3 DECLARE @approverID NVARCHAR(100) = ''
4 DECLARE @remark NVARCHAR(100) = CONVERT(NVARCHAR(19), GETDATE(), 120)
5 DECLARE @applicationID NVARCHAR(100) = ''
6 DECLARE @extensionID NVARCHAR(100) = ''
7
8 BEGIN TRY
9 IF OBJECT_ID('tempdb..#tmpTask') IS NOT NULL
10 DROP TABLE #tmpTask
11
12 SELECT *
13 INTO #tmpTask
14 FROM dbo.Task
15 WHERE ApproverID = @approverID
16 AND ApplicationID = @applicationID
17 AND ExtensionID = @extensionID
18
19 BEGIN TRAN
20 INSERT dbo.PushTaskLog
21 (
22 ID,
23 ApplicationID,
24 ExtensionID,
25 TaskID,
26 Approver,
27 Requestor,
28 Title,
29 SubmittedDate,
30 ApproverLevel,
31 XmlBody,
32 CreatedDate,
33 TaskStatus,
34 LogStatus
35 )
36 SELECT NEWID(),
37 ApplicationID,
38 ExtensionID,
39 TaskID,
40 ApproverID,
41 ApplicantName,
42 Title,
43 SubmittedDate,
44 ApproverLevel,
45 NativeApplicationData,
46 CreatedDate,
47 @remark,
48 -1
49 FROM #tmpTask
50
51 DELETE main
52 FROM dbo.Task main
53 INNER JOIN #tmpTask tmp
54 ON tmp.ID = main.ID
55 COMMIT TRAN
56
57 DROP TABLE #tmpTask
58 END TRY
59 BEGIN CATCH
60 PRINT ERROR_MESSAGE()
61 IF @@TRANCOUNT > 0
62 BEGIN
63 ROLLBACK TRAN
64 END
65
66 END CATCH