· 6 years ago · Jan 14, 2020, 10:58 AM
1USE [AssentDataMyACM]
2GO
3/****** Object: StoredProcedure [dbo].[gcc_SupplierActionLogDownsync] Script Date: 1/14/2020 12:53:47 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [dbo].[gcc_SupplierActionLogDownsync]
9 @DataBatchWID BIGINT
10 ,@LinkedServerName_on_ACM SYSNAME = 'GCC'
11 ,@GccDbName SYSNAME = 'ComplianceCentral'
12 ,@GccExportProcedureName sysname = 'gcc_stage.export_02282_From_gcc_SupplierActionLog_OrgViewWID'
13 ,@debug BIT = 0
14AS
15/*
16
17set xact_abort on
18begin transaction
19
20 exec [dbo].[gcc_SupplierActionLogDownsync]
21 @DataBatchWID = 31359
22 ,@LinkedServerName_on_ACM = 'GCC'
23 ,@GccDbName = 'GCC_AlexI_NOV_11'
24 ,@GccExportProcedureName = 'gcc_stage.export_02282_From_gcc_SupplierActionLog_OrgViewWID'
25 ,@debug = 1
26
27rollback
28*/
29BEGIN
30
31 declare @step nvarchar(max) = 'Start'
32
33 BEGIN TRY
34
35 DECLARE @ExportProcedureName NVARCHAR(500) = @LinkedServerName_on_ACM + '.' + @GccDbName + '.' + @GccExportProcedureName
36
37 create table #SupplierNotes
38 (
39 [SupplierID] INT NULL,
40 [UserID] INT NULL,
41 [ActionDate] DATETIME NULL,
42 [Action] NVARCHAR(50) NULL,
43 [Note] NVARCHAR(4000) NULL,
44 [HashValue] AS (CONVERT(VARBINARY(32), HASHBYTES('SHA2_256', CONCAT([SupplierId], [UserId], [ActionDate], [Action], [Note]))))
45 );
46
47 create table #SupplierActionLogsGCC
48 (
49 [Date] DATETIME NOT NULL,
50 [UserEmail] NVARCHAR(300) NOT NULL,
51 [ActionType] NVARCHAR(50) NOT NULL,
52 [Message] NVARCHAR(4000) NOT NULL
53 ,OrgViewWID int not null
54 )
55
56 DECLARE @SqlString NVARCHAR(MAX)
57 DECLARE @ParmDefinition nvarchar(500);
58 SET @SqlString = 'EXEC ' + @ExportProcedureName + ' @DataBatchId'
59 SET @ParmDefinition = N'@DataBatchId BIGINT';
60
61
62 set @step = 'INSERT #SupplierActionLogsGCC exec ' + @ExportProcedureName
63 if @debug = 1 print @step
64 --------------
65
66 INSERT #SupplierActionLogsGCC (
67 [Date],
68 [UserEmail],
69 [ActionType],
70 [Message]
71 ,OrgViewWID
72 )
73 EXECUTE sp_executesql
74 @SQLString, @ParmDefinition,
75 @DataBatchId = @DataBatchWID;
76
77 set @step = 'INSERT #SupplierNotes '
78 if @debug = 1 print @step
79 --------------
80 ;WITH Users_CTE
81 (
82 [UserId],
83 [Email],
84 [PartitionRow]
85 )
86 AS
87 (
88 SELECT
89 a.UserId,
90 ISNULL(a.Email, '') AS Email,
91 ROW_NUMBER() OVER (PARTITION BY a.Email ORDER BY a.UserId)AS PartitionRow
92 FROM dbo.tbluser AS a
93 --Match only client users
94 WHERE a.Access = 'Client'
95 )
96 INSERT INTO #SupplierNotes
97 (
98 [SupplierID],
99 [UserID],
100 [ActionDate],
101 [Action],
102 [Note]
103 )
104 SELECT
105 Supplier.supplierID
106 ,Users_CTE.[UserId] AS [UserID]
107 ,SupplierActionLogsGCC.[Date] AS [ActionDate]
108 ,SupplierActionLogsGCC.[ActionType] AS [Action]
109 ,CASE
110 WHEN Users_CTE.[UserId] IS NOT NULL THEN SupplierActionLogsGCC.[Message]
111 ELSE CONCAT(SupplierActionLogsGCC.UserEmail, ' - ', SupplierActionLogsGCC.[Message])
112 END
113 FROM #SupplierActionLogsGCC AS SupplierActionLogsGCC
114 join
115 dbo.Supplier on Supplier.OrgViewWID = SupplierActionLogsGCC.OrgViewWID
116 LEFT
117 JOIN Users_CTE ON ISNULL(Users_CTE.[Email], N'') = ISNULL(SupplierActionLogsGCC.[UserEmail], N'')
118 AND Users_CTE.PartitionRow = 1
119
120 set @step = 'Insert [dbo].[tblSupplierNote] AS trg'
121 if @debug = 1 print @step
122 --------------
123 ----Insert tblSupplierNote
124 INSERT [dbo].[tblSupplierNote] ([SupplierID], [UserId], [ActionDate], [Action], [Note], [DateSubmitted])
125 SELECT src.[SupplierID], src.[UserID], src.[ActionDate], src.[Action], src.[Note], GETUTCDATE()
126 FROM #SupplierNotes AS src
127 WHERE NOT EXISTS
128 (
129 SELECT 1
130 FROM [dbo].[tblSupplierNote] AS trg
131 WHERE trg.HashValue = src.HashValue
132 );
133
134 IF @Debug = 1
135 BEGIN
136 PRINT 'Execution complete [dbo].[gcc_SupplierActionLogDownsync].'
137 END
138 END TRY
139 BEGIN CATCH
140 DECLARE @Error_Message NVARCHAR(4000) = ERROR_MESSAGE();
141 DECLARE @Error_Severity INT = ERROR_SEVERITY();
142 DECLARE @Error_State INT = ERROR_STATE();
143 DECLARE @Error_Number INT = ERROR_NUMBER();
144 DECLARE @Error_SpId INT = @@SPID;
145 DECLARE @Error_Line INT = ERROR_LINE();
146 DECLARE @Error_ObjectName NVARCHAR(500) = OBJECT_NAME(@@PROCID);
147 DECLARE @ErrorMessage NVARCHAR(MAX) =
148 ISNULL(@Error_Message, '') + '.' +
149 ' Error: ' + CAST(ISNULL(@Error_Number, 0) as varchar(20)) +
150 ', @step: ' + isnull(@step,'null') +
151 ', Line: ' + CAST(ISNULL(@Error_Line, 0) as varchar(20)) +
152 ', SpId: ' + CAST(ISNULL(@Error_SpId, 0) as varchar(20)) +
153 ', Object: ' + ISNULL(@Error_ObjectName, '')
154
155 SET @Error_Number = @Error_Number + 50000;
156 SET @ErrorMessage = LEFT(@ErrorMessage, 2048);
157
158 ;THROW @Error_Number, @ErrorMessage, 255
159
160 END CATCH
161END