· 7 years ago · Sep 11, 2018, 06:54 AM
1USE [lime_test]
2GO
3/****** Object: StoredProcedure [dbo].[csp_agreement_createFromWebForms_V3] Script Date: 2018-09-10 15:10:57 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10
11-- =============================================
12-- Author: TLN (Lundalogik)
13-- Create date: 2015-04-14
14-- Description: Used to create agreements from web_form that's been imported from CGI.
15-- =============================================
16
17-- =============================================
18-- Author: CHS (Lundalogik)
19-- Create date: 2015-11-11
20-- Description: Added portions of code which will add Agreement supplements as new agreementandsuplemment records instead of information in fields.
21-- =============================================
22
23-- =============================================
24-- Author: CHS (Lundalogik)
25-- Create date: 2016-12-14
26-- Description: Edited code to support Ineras new XML provider
27-- =============================================
28
29
30-- =============================================
31-- Author: ARM (Lundalogik)
32-- Create date: 2018-09-10
33-- Description: Edited code to dynamicly support /f:Values/f:Avtalnummer/f:Avtalsnummer)[1]
34-- =============================================
35ALTER PROCEDURE [dbo].[csp_agreement_createFromWebForms_V3]
36AS
37BEGIN
38 -- SET NOCOUNT ON added to prevent extra result sets from
39 -- interfering with SELECT statements.
40 SET NOCOUNT ON;
41
42 --- CATEGORY IDs
43
44 DECLARE @newLine nvarchar(4) = CHAR(13) + CHAR(10)
45
46 DECLARE @idcategory_agreementtype INT = dbo.lfn_getidcategory('agreement_type','agreement')
47 DECLARE @idcategory_assignment INT = dbo.lfn_getidcategory('assignment','agreement')
48
49 DECLARE @authorOptionAssignmentAgreement INT = ( SELECT TOP 1 [idstring]
50 FROM [dbo].[string]
51 WHERE [idcategory] = @idcategory_assignment
52 AND [key] = 'author' )
53
54 DECLARE @examinerOptionAssignmentAgreement INT = ( SELECT TOP 1 [idstring]
55 FROM [dbo].[string]
56 WHERE [idcategory] = @idcategory_assignment
57 AND [key] = 'examiner' )
58
59 DECLARE @emptyOprionAssignmentAgreement INT = ( SELECT TOP 1 [idstring]
60 FROM [dbo].[string]
61 WHERE [idcategory] = @idcategory_assignment
62 AND [key] = 'empty' )
63
64
65 DECLARE @companyIsMotherId INT = ( SELECT TOP 1 [idstring]
66 FROM [dbo].[string]
67 WHERE [idcategory] = dbo.lfn_getidcategory('motherdaughter','company')
68 AND [key] = 'mother' )
69
70 DECLARE @formStatusIdCategory INT = dbo.lfn_getidcategory('form_status','web_form')
71 DECLARE @formStatus_newId INT = ( SELECT TOP 1 [idstring]
72 FROM [dbo].[string]
73 WHERE [idcategory] = @formStatusIdCategory
74 AND [key] = 'new' )
75
76 DECLARE @formStatus_errorId INT = ( SELECT TOP 1 [idstring]
77 FROM [dbo].[string]
78 WHERE [idcategory] = @formStatusIdCategory
79 AND [key] = 'error' )
80
81 DECLARE @formStatus_doneId INT = ( SELECT TOP 1 [idstring]
82 FROM [dbo].[string]
83 WHERE [idcategory] = @formStatusIdCategory
84 AND [key] = 'done' )
85
86 DECLARE @formStatus_warningId INT = ( SELECT TOP 1 [idstring]
87 FROM [dbo].[string]
88 WHERE [idcategory] = @formStatusIdCategory
89 AND [key] = 'warning' )
90
91 DECLARE @webFormTypeIdCategory INT = dbo.lfn_getidcategory('web_form_type','web_form')
92 DECLARE @webFormTypeAgreement INT = ( SELECT TOP 1 [idstring]
93 FROM [dbo].[string]
94 WHERE [idcategory] = @webFormTypeIdCategory
95 AND [key] = 'agreement' ) -- Type = Agreement/Avtal
96 ---
97
98
99
100 IF OBJECT_ID('tempdb..#TempNewForms') IS NOT NULL
101 BEGIN
102 DROP TABLE #TempNewForms
103 END
104
105
106 ;WITH cte_readxml AS
107 (
108 SELECT
109 wf.idweb_form
110 ,CAST(REPLACE(wf.form_xml, 'UTF-8', 'UTF-16') AS XML).query('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:FlowInstance/f:Header)[1]') AS header
111 ,CAST(REPLACE(wf.form_xml, 'UTF-8', 'UTF-16') AS XML).query('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:FlowInstance/f:Values)[1]') AS form_values
112 ,CAST(REPLACE(wf.form_xml, 'UTF-8', 'UTF-16') AS XML).query('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:FlowInstance/f:SigningEvents)[1]') AS form_signing
113 ,CAST(REPLACE(wf.form_xml, 'UTF-8', 'UTF-16') AS XML).query('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:FlowInstance/f:Parameters)[1]') AS form_parameters
114 FROM web_form wf
115 WHERE [form_status] = @formStatus_newId -- Nyinkommet
116 AND [web_form_type] = @webFormTypeAgreement -- Type = Agreement/Avtal
117 AND [status] = 0
118 ), cte_values AS
119 (
120 SELECT
121 idweb_form
122 ,header.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Header/f:Flow/f:FlowID)[1]', 'NVARCHAR(100)') AS [formid]
123 ,header.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Header/f:FirstSubmitted)[1]', 'DATETIME') AS [agreementtimestamp]
124 ,header.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Header/f:Flow/f:Name)[1]', 'NVARCHAR(100)') AS [agreementname]
125 ,ISNULL(form_signing.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:SigningEvents/f:SignEvent/f:Signer/f:Firstname)[1]', 'NVARCHAR(128)') + N' ' + form_signing.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:SigningEvents/f:SignEvent/f:Signer/f:Lastname)[1]', 'NVARCHAR(128)'), N'') AS [signername]
126 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Servicearticle"]/@Value)[1]', 'NVARCHAR(10)') AS [serviceid]
127 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Coworker_contact/f:E-postadress)[1]', 'NVARCHAR(100)') AS [internal_contact]
128 ,CASE WHEN form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:App_Journalen/f:Value)[1]', 'NVARCHAR(100)') IS NULL THEN 0 ELSE 1 END AS [app_journalen]
129 ,CASE WHEN form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:SMS_Pascal/f:Value)[1]', 'NVARCHAR(100)') IS NULL THEN 0 ELSE 1 END AS [onetimepass_sms]
130 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Coworker_responsable"]/@Value)[1]', 'NVARCHAR(100)') AS [internal_responsable]
131 ,CASE WHEN ISNULL(form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Avtalnummer/f:Avtalsnummer)[1]', 'INT'), 0) > 1 THEN 1 ELSE 0 END AS [has_avtalnummer]
132 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Company/f:Personnummer_eller_organisationsnummer)[1]', 'NVARCHAR(20)') AS [external_registrationno]
133 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Person_responsable/f:Fornamn)[1]', 'NVARCHAR(100)') AS [external_responsable_firstname]
134 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Person_responsable/f:Efternamn)[1]', 'NVARCHAR(100)') AS [external_responsable_lastname]
135 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Person_responsable/f:E-postadress)[1]', 'NVARCHAR(100)') AS [external_responsable_email]
136 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Person_contact/f:Fornamn)[1]', 'NVARCHAR(100)') AS [external_contact_firstname]
137 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Person_contact/f:Efternamn)[1]', 'NVARCHAR(100)') AS [external_contact_lastname]
138 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Person_contact/f:E-postadress)[1]', 'NVARCHAR(100)') AS [external_contact_email]
139 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Avtalnummer/f:Avtalsnummer)[1]', 'NVARCHAR(100)') AS [agreementid]
140 ,form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Assignment/f:Value)[1]', 'NVARCHAR(100)') AS [assignment]
141 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Periodofnotice"]/@Value)[1]', 'NVARCHAR(100)') AS [periodofnotice]
142 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementperiod"]/@Value)[1]', 'NVARCHAR(100)') AS [agreementperiod]
143 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement1"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement1_id]
144 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement2"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement2_id]
145 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement3"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement3_id]
146 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement4"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement4_id]
147 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement5"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement5_id]
148 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement6"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement6_id]
149 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement7"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement7_id]
150 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementsupplement8"]/@Value)[1]', 'NVARCHAR(10)') AS [supplement8_id]
151 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreement_type"]/@Value)[1]', 'NVARCHAR(10)') AS [agreementtype]
152 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreement_id"]/@Value)[1]', 'NVARCHAR(10)') AS [agreementarticleid]
153 ,form_parameters.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Parameters/f:Parameter[@Name="Agreementperiod_value"]/@Value)[1]', 'NVARCHAR(10)') AS [agreementperiod_value]
154 ,CASE WHEN LEN(form_values.value('declare namespace f="http://www.oeplatform.org/version/2.0/schemas/flowinstance";(/f:Values/f:Person_contact/f:E-postadress)[1]', 'NVARCHAR(100)')) > 0 THEN 1 ELSE 0 END AS [has_contactperson]
155 FROM cte_readxml
156 )
157
158 SELECT NEWID() AS [RowID], cv.*
159 INTO #TempNewForms
160 FROM cte_values cv
161
162 SELECT * FROM #TempNewForms form
163
164
165 DECLARE @idrowData NVARCHAR(64)
166 ,@idagreement INT
167 ,@transactionID NVARCHAR(64)
168 ,@iduser INT
169 ,@isNew BIT
170 ,@retval INT
171 DECLARE @relationData TABLE ( idrelationdata INT NOT NULL IDENTITY(1, 1), table1 NVARCHAR(256), field1 NVARCHAR(256), idrecord1 INT, idrecord2 INT )
172
173 SET @iduser = 1
174
175 DECLARE formCursor CURSOR READ_ONLY STATIC FORWARD_ONLY LOCAL
176 FOR SELECT [RowID] FROM #TempNewForms
177 OPEN formCursor
178 FETCH NEXT FROM formCursor INTO @idrowData
179 WHILE @@FETCH_STATUS = 0
180 BEGIN
181 SET @idagreement = NULL
182
183 BEGIN TRANSACTION trans_formImport
184
185 BEGIN TRY
186
187 DECLARE @warningMessage NVARCHAR(MAX)
188 ,@idcompany INT
189
190 SET @warningMessage = N''
191
192 IF OBJECT_ID('tempdb..#TempRelations') IS NOT NULL
193 BEGIN
194 DROP TABLE #TempRelations
195 END
196
197 ;WITH cte_relations AS
198 (
199 SELECT N'web_form' AS [fieldname], form.idweb_form AS [idrecord]
200 FROM #TempNewForms form
201 WHERE form.RowID = @idrowData
202 UNION ALL
203 SELECT N'company', c.idcompany
204 FROM #TempNewForms form
205 LEFT JOIN company c ON
206 REPLACE(c.registrationno, '-', '') = REPLACE(form.[external_registrationno], '-', '') AND
207 c.motherdaughter = @companyIsMotherId /*IsMother*/ AND
208 c.[status] = 0
209 WHERE form.RowID = @idrowData
210 UNION ALL
211 SELECT N'servicearticle', s.idservicearticle
212 FROM #TempNewForms form
213 LEFT JOIN servicearticle s ON LOWER(s.serviceid) = LOWER(form.serviceid) AND s.[status] = 0
214 WHERE form.RowID = @idrowData
215 UNION ALL
216 SELECT N'agreementarticle', a.idagreementarticle
217 FROM #TempNewForms form
218 LEFT JOIN agreementarticle a ON LOWER(a.id) = LOWER(form.agreementarticleid) AND a.[status] = 0 AND a.[inactive] = 0
219 WHERE form.RowID = @idrowData
220 UNION ALL
221 SELECT N'coworker_contact', c.idcoworker
222 FROM #TempNewForms form
223 LEFT JOIN coworker c ON c.email = form.internal_contact AND c.[status] = 0
224 WHERE form.RowID = @idrowData
225 UNION ALL
226 SELECT N'coworker_responsable', c.idcoworker
227 FROM #TempNewForms form
228 LEFT JOIN coworker c ON c.email = form.internal_responsable AND c.[status] = 0
229 WHERE form.RowID = @idrowData
230 UNION ALL
231 SELECT N'person_responsable', p.idperson
232 FROM #TempNewForms form
233 LEFT JOIN person p ON p.email = form.[external_responsable_email] AND p.[status] = 0
234 WHERE form.RowID = @idrowData
235 UNION ALL
236 SELECT N'person_contact', p.idperson
237 FROM #TempNewForms form
238 LEFT JOIN person p ON p.email = form.[external_contact_email] AND p.[status] = 0
239 WHERE form.RowID = @idrowData AND form.has_contactperson = 1
240 UNION ALL
241 -- Used for connecting pdf document to agreement and company --
242 SELECT N'pdfdocument', d.idagreement_document
243 FROM #TempNewForms form
244 LEFT JOIN agreement_document d ON d.web_form = form.idweb_form AND d.[status] = 0
245 INNER JOIN [file] f ON d.document = f.idfile AND f.[status] = 0 AND f.[fileextension] = 'PDF'
246 WHERE form.RowID = @idrowData
247 -- START: AgreementSupplements INNER JOIN so that it won't give a warning if no match (See addRelation part below)
248 UNION ALL
249 SELECT N'agreementsupplement1', a.idagreementsupplement
250 FROM #TempNewForms form
251 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement1_id AND a.[status] = 0
252 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
253 UNION ALL
254 SELECT N'agreementsupplement2', a.idagreementsupplement
255 FROM #TempNewForms form
256 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement2_id AND a.[status] = 0
257 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
258 UNION ALL
259 SELECT N'agreementsupplement3', a.idagreementsupplement
260 FROM #TempNewForms form
261 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement3_id AND a.[status] = 0
262 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
263 UNION ALL
264 SELECT N'agreementsupplement4', a.idagreementsupplement
265 FROM #TempNewForms form
266 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement4_id AND a.[status] = 0
267 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
268 UNION ALL
269 SELECT N'agreementsupplement5', a.idagreementsupplement
270 FROM #TempNewForms form
271 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement5_id AND a.[status] = 0
272 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
273 UNION ALL
274 SELECT N'agreementsupplement6', a.idagreementsupplement
275 FROM #TempNewForms form
276 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement6_id AND a.[status] = 0
277 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
278 UNION ALL
279 SELECT N'agreementsupplement7', a.idagreementsupplement
280 FROM #TempNewForms form
281 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement7_id AND a.[status] = 0
282 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
283 UNION ALL
284 SELECT N'agreementsupplement8', a.idagreementsupplement
285 FROM #TempNewForms form
286 INNER JOIN [agreementsupplement] a ON a.agreementsupplementid = form.supplement8_id AND a.[status] = 0
287 WHERE form.RowID = @idrowData AND ISNULL(LTRIM(RTRIM(a.agreementsupplementid)), '') <> ''
288 -- END: AgreementSupplements INNER JOIN so that it won't give a warning if no match (See addRelation part below)
289 )
290 SELECT *
291 INTO #TempRelations
292 FROM cte_relations cr
293
294 -- START: Getting IDs for setting relation to the PDF-agreement
295 SELECT TOP 1 @idcompany = rel.idrecord
296 FROM #TempRelations rel
297 WHERE rel.fieldname = N'company'
298 -- END: Getting IDs for setting relation to the PDF-agreement
299
300 -- START: Look for multiple matches in relations
301 DECLARE @fieldname NVARCHAR(256)
302 ,@multipleMatchMessage NVARCHAR(512)
303 ,@tableLocalName NVARCHAR(256)
304 ,@fieldLocalName NVARCHAR(256)
305
306 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@tableName = @tableLocalName OUTPUT, @@fieldName = NULL, @@field = NULL
307 SET @multipleMatchMessage = N'Hittar flertal matchningar på fältet: ' + @tableLocalName + N'.'
308
309 SET @fieldname = N'company'
310 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname) > 1
311 BEGIN
312 IF LEN(@warningMessage) > 0
313 BEGIN
314 SET @warningMessage = @warningMessage + @newLine
315 END
316 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
317 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
318 END
319 SET @fieldname = N'servicearticle'
320 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname) > 1
321 BEGIN
322 IF LEN(@warningMessage) > 0
323 BEGIN
324 SET @warningMessage = @warningMessage + @newLine
325 END
326 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
327 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
328 END
329
330 SET @fieldname = N'agreementarticle'
331 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname) > 1
332 BEGIN
333 IF LEN(@warningMessage) > 0
334 BEGIN
335 SET @warningMessage = @warningMessage + @newLine
336 END
337 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
338 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
339 END
340 SET @fieldname = N'coworker_contact'
341 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname ) > 1
342 BEGIN
343 IF LEN(@warningMessage) > 0
344 BEGIN
345 SET @warningMessage = @warningMessage + @newLine
346 END
347 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
348 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
349 END
350
351 SET @fieldname = N'coworker_responsable'
352 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname ) > 1
353 BEGIN
354 IF LEN(@warningMessage) > 0
355 BEGIN
356 SET @warningMessage = @warningMessage + @newLine
357 END
358 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
359 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
360 END
361
362 SET @fieldname = N'person_responsable'
363 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname ) > 1
364 BEGIN
365 IF LEN(@warningMessage) > 0
366 BEGIN
367 SET @warningMessage = @warningMessage + @newLine
368 END
369 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
370 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
371 END
372
373 SET @fieldname = N'person_contact'
374 IF (SELECT COUNT(rel.idrecord)
375 FROM #TempRelations rel
376 WHERE rel.fieldname = @fieldname
377 ) > 1
378 BEGIN
379 IF LEN(@warningMessage) > 0
380 SET @warningMessage = @warningMessage + @newLine
381
382 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
383 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
384 END
385
386 SET @fieldname = N'agreementsupplement1'
387 IF (SELECT COUNT(rel.idrecord)
388 FROM #TempRelations rel
389 WHERE rel.fieldname = @fieldname
390 ) > 1
391 BEGIN
392 IF LEN(@warningMessage) > 0
393 SET @warningMessage = @warningMessage + @newLine
394
395 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
396 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
397 END
398
399 SET @fieldname = N'agreementsupplement2'
400 IF (SELECT COUNT(rel.idrecord)
401 FROM #TempRelations rel
402 WHERE rel.fieldname = @fieldname
403 ) > 1
404 BEGIN
405 IF LEN(@warningMessage) > 0
406 SET @warningMessage = @warningMessage + @newLine
407
408 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
409 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
410 END
411
412 SET @fieldname = N'agreementsupplement3'
413 IF (SELECT COUNT(rel.idrecord)
414 FROM #TempRelations rel
415 WHERE rel.fieldname = @fieldname
416 ) > 1
417 BEGIN
418 IF LEN(@warningMessage) > 0
419 SET @warningMessage = @warningMessage + @newLine
420
421 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
422 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
423 END
424
425 SET @fieldname = N'agreementsupplement4'
426 IF (SELECT COUNT(rel.idrecord)
427 FROM #TempRelations rel
428 WHERE rel.fieldname = @fieldname
429 ) > 1
430 BEGIN
431 IF LEN(@warningMessage) > 0
432 SET @warningMessage = @warningMessage + @newLine
433
434 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
435 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
436 END
437
438 SET @fieldname = N'agreementsupplement5'
439 IF (SELECT COUNT(rel.idrecord)
440 FROM #TempRelations rel
441 WHERE rel.fieldname = @fieldname
442 ) > 1
443 BEGIN
444 IF LEN(@warningMessage) > 0
445 BEGIN
446 SET @warningMessage = @warningMessage + @newLine
447 END
448 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
449 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
450 END
451
452 SET @fieldname = N'agreementsupplement6'
453 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname) > 1
454 BEGIN
455 IF LEN(@warningMessage) > 0
456 BEGIN
457 SET @warningMessage = @warningMessage + @newLine
458 END
459 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
460 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
461 END
462
463 SET @fieldname = N'agreementsupplement7'
464 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname) > 1
465 BEGIN
466 IF LEN(@warningMessage) > 0
467 BEGIN
468 SET @warningMessage = @warningMessage + @newLine
469 END
470 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
471 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
472 END
473
474 SET @fieldname = N'agreementsupplement8'
475 IF (SELECT COUNT(rel.idrecord) FROM #TempRelations rel WHERE rel.fieldname = @fieldname) > 1
476 BEGIN
477 IF LEN(@warningMessage) > 0
478 SET @warningMessage = @warningMessage + @newLine
479
480 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
481 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
482 END
483
484 -- varna om det finns fält med samma Avtalnummer.Avtalsnummer från tabelen #TempRelations
485 IF EXISTS(
486 SELECT
487 a.idagreement
488 FROM #TempNewForms form
489 INNER JOIN [agreement] a ON
490 a.agreementid = CAST(form.agreementid AS INT)
491 AND a.[status] = 0
492 WHERE
493 ISNUMERIC(ISNULL(form.agreementid, '')) = 1
494 AND form.RowID = @idrowData
495 )
496 BEGIN
497 SET @fieldname = N'agreementid'
498 IF LEN(@warningMessage) > 0
499 BEGIN
500 SET @warningMessage = @warningMessage + @newLine
501 END
502
503 -- Se om det finns fält med namnet agreementid från databasen tabelen "agreement"
504 EXEC csp_getLocalName @@lang = N'sv', @@table = N'agreement', @@field = @fieldname, @@fieldName = @fieldLocalName OUTPUT, @@tableName = NULL
505 SET @warningMessage = @warningMessage + @multipleMatchMessage + @fieldLocalName
506 END
507
508
509
510
511
512 -- END: Look for multiple matches in relations
513
514 EXEC @retval = lsp_beginupdate @@transactionid = @transactionID OUTPUT
515 IF @retval <> 0
516 RAISERROR(N'error - beginupdate.', 16, 1)
517
518 SELECT @isNew = CASE WHEN @idagreement IS NULL THEN 1 ELSE 0 END
519
520 IF @isNew = 1
521 EXEC @retval = lsp_newrecord @@table = N'agreement', @@iduser = @iduser, @@idrecord = @idagreement OUTPUT
522
523 IF @retval <> 0
524 RAISERROR(N'error - newrecord.', 16, 1)
525
526 EXEC @retval = lsp_addupdatelog @@table = N'agreement', @@idrecord = @idagreement, @@transactionid = @transactionID, @@iduser = @iduser, @@new = @isNew, @@updated = 1
527 IF @retval <> 0
528 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
529
530 DECLARE @agreementperiod_value INT
531 SET @agreementperiod_value = (SELECT SUM(CASE WHEN ISNUMERIC(form.[agreementperiod_value]) = 1 THEN CAST(CAST(form.[agreementperiod_value] AS FLOAT)AS INT) ELSE 0 END) FROM #TempNewForms form WHERE form.RowID = @idrowData)
532
533
534
535
536
537 UPDATE a SET
538 [agreementid] = CASE WHEN ISNULL(form.agreementid, '') <> '' THEN cast(form.agreementid as int) ELSE NULL END
539 ,[name] = form.[agreementname]
540 ,[agreementtimestamp] = form.[agreementtimestamp]
541 ,[agreementperiod] = ISNULL(form.[agreementperiod], N'')
542 ,[onetimepass_sms] = form.[onetimepass_sms]
543 ,[application_journal] = form.[app_journalen]
544 ,[periodofnotice] = form.[periodofnotice]
545 ,[signername] = form.[signername]
546 ,[imported_cgi] = 1
547 ,[skip_sendconfirmation] = form.[has_avtalnummer]
548 ,[assignment] = CASE
549 WHEN form.[assignment] = N'Författare' THEN @authorOptionAssignmentAgreement
550 WHEN form.[assignment] = N'Faktagranskare' THEN
551 (SELECT idstring FROM [string] WHERE idcategory = @idcategory_assignment AND [key] = N'examiner')
552 ELSE
553 (SELECT idstring FROM [string] WHERE idcategory = @idcategory_assignment AND [key] = N'empty')
554 END
555 ,[agreement_type] = ISNULL((SELECT idstring FROM [string] WHERE idcategory = @idcategory_agreementtype AND [key] = LOWER(form.agreementtype)), (SELECT idstring FROM [string] WHERE idcategory = @idcategory_agreementtype AND [key] = N'empty'))
556 ,[agreementperiod_value] = @agreementperiod_value
557 ,[person_contact_email_cgi] = CASE WHEN form.[has_contactperson] = 1 THEN form.[external_contact_email] ELSE '' END
558 ,[person_contact_name_cgi] = CASE WHEN form.[has_contactperson] = 1 THEN form.[external_contact_firstname] + N' ' + form.[external_contact_lastname] ELSE '' END
559 ,[person_responsable_email_cgi] = ISNULL(form.external_responsable_email, N'')
560 ,[person_responsable_name_cgi] = ISNULL(form.external_responsable_firstname, N'') + N' ' + ISNULL(form.external_responsable_lastname, N'')
561 ,[supplement1_timestamp] = CASE WHEN ISNULL(form.supplement1_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
562 ,[supplement2_timestamp] = CASE WHEN ISNULL(form.supplement2_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
563 ,[supplement3_timestamp] = CASE WHEN ISNULL(form.supplement3_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
564 ,[supplement4_timestamp] = CASE WHEN ISNULL(form.supplement4_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
565 ,[supplement5_timestamp] = CASE WHEN ISNULL(form.supplement5_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
566 ,[supplement6_timestamp] = CASE WHEN ISNULL(form.supplement6_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
567 ,[supplement7_timestamp] = CASE WHEN ISNULL(form.supplement7_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
568 ,[supplement8_timestamp] = CASE WHEN ISNULL(form.supplement8_id, '') <> '' THEN form.[agreementtimestamp] ELSE NULL END
569 FROM agreement a
570 INNER JOIN #TempNewForms form ON form.RowID = @idrowData
571 LEFT JOIN person p ON p.email = form.external_responsable_email
572 WHERE a.idagreement = @idagreement
573
574 --SAVE TIMESTAMP TO VARIABLE
575 DECLARE @supp_timestamp DATETIME
576 SELECT @supp_timestamp = [agreementtimestamp] FROM #TempNewForms form
577
578 EXEC @retval = lsp_enddataupdate @@transactionid = @transactionID, @@iduser = @iduser
579 IF @retval <> 0
580 BEGIN
581 RAISERROR(N'error - enddataupdate.', 16, 1)
582 END
583 INSERT INTO @relationData(table1, field1, idrecord1, idrecord2)
584 SELECT N'agreement', N'company', @idagreement, @idcompany
585 UNION ALL
586 SELECT TOP 1 N'agreement', N'web_form', @idagreement, form.idweb_form
587 FROM #TempNewForms form
588 WHERE form.RowID = @idrowData
589 UNION ALL
590 SELECT TOP 1 N'agreement', N'servicearticle', @idagreement, rel.idrecord
591 FROM #TempRelations rel
592 WHERE rel.fieldname = N'servicearticle'
593 UNION ALL
594 SELECT TOP 1 N'agreement', N'agreementarticle', @idagreement, rel.idrecord
595 FROM #TempRelations rel
596 WHERE rel.fieldname = N'agreementarticle'
597 UNION ALL
598 SELECT TOP 1 N'agreement', N'coworker_contact', @idagreement, rel.idrecord
599 FROM #TempRelations rel
600 WHERE rel.fieldname = N'coworker_contact'
601 UNION ALL
602 SELECT TOP 1 N'agreement', N'coworker_responsable', @idagreement, rel.idrecord
603 FROM #TempRelations rel
604 WHERE rel.fieldname = N'coworker_responsable'
605 UNION ALL
606 SELECT TOP 1 N'agreement', N'person_responsable', @idagreement, rel.idrecord
607 FROM #TempRelations rel
608 WHERE rel.fieldname = N'person_responsable'
609 UNION ALL
610 SELECT TOP 1 N'agreement', N'person_contact', @idagreement, rel.idrecord
611 FROM #TempRelations rel
612 WHERE rel.fieldname = N'person_contact'
613 UNION ALL
614 SELECT TOP 1 N'agreement', N'agreementsupplement1', @idagreement, rel.idrecord
615 FROM #TempRelations rel
616 WHERE rel.fieldname = N'agreementsupplement1'
617 UNION ALL
618 SELECT TOP 1 N'agreement', N'agreementsupplement2', @idagreement, rel.idrecord
619 FROM #TempRelations rel
620 WHERE rel.fieldname = N'agreementsupplement2'
621 UNION ALL
622 SELECT TOP 1 N'agreement', N'agreementsupplement3', @idagreement, rel.idrecord
623 FROM #TempRelations rel
624 WHERE rel.fieldname = N'agreementsupplement3'
625 UNION ALL
626 SELECT TOP 1 N'agreement', N'agreementsupplement4', @idagreement, rel.idrecord
627 FROM #TempRelations rel
628 WHERE rel.fieldname = N'agreementsupplement4'
629 UNION ALL
630 SELECT TOP 1 N'agreement', N'agreementsupplement5', @idagreement, rel.idrecord
631 FROM #TempRelations rel
632 WHERE rel.fieldname = N'agreementsupplement5'
633 UNION ALL
634 SELECT TOP 1 N'agreement', N'agreementsupplement6', @idagreement, rel.idrecord
635 FROM #TempRelations rel
636 WHERE rel.fieldname = N'agreementsupplement6'
637 UNION ALL
638 SELECT TOP 1 N'agreement', N'agreementsupplement7', @idagreement, rel.idrecord
639 FROM #TempRelations rel
640 WHERE rel.fieldname = N'agreementsupplement7'
641 UNION ALL
642 SELECT TOP 1 N'agreement', N'agreementsupplement8', @idagreement, rel.idrecord
643 FROM #TempRelations rel
644 WHERE rel.fieldname = N'agreementsupplement8'
645
646 ;WITH
647 supplementversion1 AS (
648 SELECT TOP 1
649 N'agreement' AS [table1],
650 N'agreementsupplementversion1' AS [field1],
651 @idagreement AS [idrecord1],
652 av.idagreementsupplementversion AS [idrecord2]
653 FROM @relationData relData
654 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
655 WHERE relData.field1 = N'agreementsupplement1'
656 ORDER BY av.releasedate DESC, av.createdtime DESC
657 ),
658 supplementversion2 AS (
659 SELECT TOP 1
660 N'agreement' AS [table1],
661 N'agreementsupplementversion2' AS [field1],
662 @idagreement AS [idrecord1],
663 av.idagreementsupplementversion AS [idrecord2]
664 FROM @relationData relData
665 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
666 WHERE relData.field1 = N'agreementsupplement2'
667 ORDER BY av.releasedate DESC, av.createdtime DESC
668 ),
669 supplementversion3 AS (
670 SELECT TOP 1
671 N'agreement' AS [table1],
672 N'agreementsupplementversion3' AS [field1],
673 @idagreement AS [idrecord1],
674 av.idagreementsupplementversion AS [idrecord2]
675 FROM @relationData relData
676 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
677 WHERE relData.field1 = N'agreementsupplement3'
678 ORDER BY av.releasedate DESC, av.createdtime DESC
679 ),
680 supplementversion4 AS (
681 SELECT TOP 1
682 N'agreement' AS [table1],
683 N'agreementsupplementversion4' AS [field1],
684 @idagreement AS [idrecord1],
685 av.idagreementsupplementversion AS [idrecord2]
686 FROM @relationData relData
687 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
688 WHERE relData.field1 = N'agreementsupplement4'
689 ORDER BY av.releasedate DESC, av.createdtime DESC
690 ),
691 supplementversion5 AS (
692 SELECT TOP 1
693 N'agreement' AS [table1],
694 N'agreementsupplementversion5' AS [field1],
695 @idagreement AS [idrecord1],
696 av.idagreementsupplementversion AS [idrecord2]
697 FROM @relationData relData
698 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
699 WHERE relData.field1 = N'agreementsupplement5'
700 ORDER BY av.releasedate DESC, av.createdtime DESC
701 ),
702 supplementversion6 AS (
703 SELECT TOP 1
704 N'agreement' AS [table1],
705 N'agreementsupplementversion6' AS [field1],
706 @idagreement AS [idrecord1],
707 av.idagreementsupplementversion AS [idrecord2]
708 FROM @relationData relData
709 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
710 WHERE relData.field1 = N'agreementsupplement6'
711 ORDER BY av.releasedate DESC, av.createdtime DESC
712 ),
713 supplementversion7 AS (
714 SELECT TOP 1
715 N'agreement' AS [table1],
716 N'agreementsupplementversion7' AS [field1],
717 @idagreement AS [idrecord1],
718 av.idagreementsupplementversion AS [idrecord2]
719 FROM @relationData relData
720 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
721 WHERE relData.field1 = N'agreementsupplement7'
722 ORDER BY av.releasedate DESC, av.createdtime DESC
723 ),
724 supplementversion8 AS (
725 SELECT TOP 1
726 N'agreement' AS [table1],
727 N'agreementsupplementversion8' AS [field1],
728 @idagreement AS [idrecord1],
729 av.idagreementsupplementversion AS [idrecord2]
730 FROM @relationData relData
731 INNER JOIN agreementsupplementversion av ON av.agreementsupplement = relData.idrecord2
732 WHERE relData.field1 = N'agreementsupplement8'
733 ORDER BY av.releasedate DESC, av.createdtime DESC
734 )
735 -- START: Connecting supplement version (Is found through which supplement that is connected)
736 INSERT INTO @relationData(table1, field1, idrecord1, idrecord2)
737 SELECT * FROM supplementversion1
738 UNION ALL
739 SELECT * FROM supplementversion2
740 UNION ALL
741 SELECT * FROM supplementversion3
742 UNION ALL
743 SELECT * FROM supplementversion4
744 UNION ALL
745 SELECT * FROM supplementversion5
746 UNION ALL
747 SELECT * FROM supplementversion6
748 UNION ALL
749 SELECT * FROM supplementversion7
750 UNION ALL
751 SELECT * FROM supplementversion8
752 -- END: Connecting supplement version (Is found through which supplement that is connected)
753
754 -- START: DEAL WITH CREATING NEW RECORDS FOR EACH AGREEMENTSUPPLEMENT, ADDED BY: CHS 11/11/2015
755 -- NOTE: SUPPORT MAXIMUM OF 8 SUPPLEMENTS, HARDCODED
756 DECLARE @tid AS UNIQUEIDENTIFIER
757 , @agreementsupp1id INT
758 , @agreementsupp2id INT
759 , @agreementsupp3id INT
760 , @agreementsupp4id INT
761 , @agreementsupp5id INT
762 , @agreementsupp6id INT
763 , @agreementsupp7id INT
764 , @agreementsupp8id INT
765 , @agreementsuppverid INT
766 , @agreementandsuppID INT
767 , @transerrormessage NVARCHAR(1024)
768
769 SET @agreementsupp1id = NULL
770 SET @agreementsupp2id = NULL
771 SET @agreementsupp3id = NULL
772 SET @agreementsupp4id = NULL
773 SET @agreementsupp5id = NULL
774 SET @agreementsupp6id = NULL
775 SET @agreementsupp7id = NULL
776 SET @agreementsupp8id = NULL
777
778 SELECT @agreementsupp1id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement1'
779 SELECT @agreementsupp2id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement2'
780 SELECT @agreementsupp3id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement3'
781 SELECT @agreementsupp4id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement4'
782 SELECT @agreementsupp5id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement5'
783 SELECT @agreementsupp6id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement6'
784 SELECT @agreementsupp7id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement7'
785 SELECT @agreementsupp8id = idrecord2 FROM @relationdata WHERE field1 = N'agreementsupplement8'
786
787 --HANDLE AGREEMENT SUPPLEMENT 1 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
788 SET @agreementandsuppID = NULL
789 SET @agreementsuppverid = NULL
790 SET @tid = NULL
791 IF @agreementsupp1id IS NOT NULL
792 BEGIN
793 BEGIN TRANSACTION trans_agreementsupp1
794 BEGIN TRY
795 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
796 IF @retval <> 0
797 RAISERROR(N'error - beginupdate.', 16, 1)
798
799 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion1'
800
801 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
802 IF @retval <> 0
803 RAISERROR(N'error - newrecord.', 16, 1)
804
805 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
806 IF @retval <> 0
807 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
808
809 UPDATE agreementandsupplement
810 SET [status] = 0
811 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
812 WHERE idagreementandsupplement = @agreementandsuppID
813
814 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
815 IF @retval <> 0
816 RAISERROR(N'error - enddataupdate.', 16, 1)
817
818 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp1id, @@transactionid = @tid, @@username = N'llabadmin'
819 IF @retval <> 0
820 RAISERROR(N'error - addrelationrecord.', 16, 1)
821 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
822 IF @retval <> 0
823 RAISERROR(N'error - addrelationrecord.', 16, 1)
824
825 IF @agreementsuppverid IS NOT NULL
826 BEGIN
827 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
828 IF @retval <> 0
829 RAISERROR(N'error - addrelationrecord.', 16, 1)
830 END
831
832 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
833 IF @retval <> 0
834 RAISERROR(N'error - endupdate.', 16, 1)
835
836 COMMIT TRANSACTION trans_agreementsupp1
837
838 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
839 IF @retval <> 0
840 RAISERROR(N'error - updatecompleted.', 16, 1)
841 END TRY
842 BEGIN CATCH
843 ROLLBACK TRANSACTION trans_agreementsupp1
844
845 SELECT @transerrormessage = ERROR_MESSAGE()
846 PRINT N'TRANSACTION FAILED AGREEMENTSUPP1. ERROR: ' + @transerrormessage
847 END CATCH
848 END
849
850 --HANDLE AGREEMENT SUPPLEMENT 2 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
851 SET @agreementandsuppID = NULL
852 SET @agreementsuppverid = NULL
853 SET @tid = NULL
854 IF @agreementsupp2id IS NOT NULL
855 BEGIN
856 BEGIN TRANSACTION trans_agreementsupp2
857 BEGIN TRY
858 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
859 IF @retval <> 0
860 RAISERROR(N'error - beginupdate.', 16, 1)
861
862 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion2'
863
864 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
865 IF @retval <> 0
866 RAISERROR(N'error - newrecord.', 16, 1)
867
868 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
869 IF @retval <> 0
870 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
871
872 UPDATE agreementandsupplement
873 SET [status] = 0
874 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
875 WHERE idagreementandsupplement = @agreementandsuppID
876
877 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
878 IF @retval <> 0
879 RAISERROR(N'error - enddataupdate.', 16, 1)
880
881 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp2id, @@transactionid = @tid, @@username = N'llabadmin'
882 IF @retval <> 0
883 RAISERROR(N'error - addrelationrecord.', 16, 1)
884 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
885 IF @retval <> 0
886 RAISERROR(N'error - addrelationrecord.', 16, 1)
887
888 IF @agreementsuppverid IS NOT NULL
889 BEGIN
890 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
891 IF @retval <> 0
892 RAISERROR(N'error - addrelationrecord.', 16, 1)
893 END
894
895 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
896 IF @retval <> 0
897 RAISERROR(N'error - endupdate.', 16, 1)
898
899 COMMIT TRANSACTION trans_agreementsupp2
900
901 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
902 IF @retval <> 0
903 RAISERROR(N'error - updatecompleted.', 16, 1)
904 END TRY
905 BEGIN CATCH
906 ROLLBACK TRANSACTION trans_agreementsupp2
907
908 SELECT @transerrormessage = ERROR_MESSAGE()
909 PRINT N'TRANSACTION FAILED AGREEMENTSUPP2. ERROR: ' + @transerrormessage
910 END CATCH
911 END
912
913 --HANDLE AGREEMENT SUPPLEMENT 3 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
914 SET @agreementandsuppID = NULL
915 SET @agreementsuppverid = NULL
916 SET @tid = NULL
917 IF @agreementsupp3id IS NOT NULL
918 BEGIN
919 BEGIN TRANSACTION trans_agreementsupp3
920 BEGIN TRY
921 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
922 IF @retval <> 0
923 RAISERROR(N'error - beginupdate.', 16, 1)
924
925 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion3'
926
927 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
928 IF @retval <> 0
929 RAISERROR(N'error - newrecord.', 16, 1)
930
931 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
932 IF @retval <> 0
933 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
934
935 UPDATE agreementandsupplement
936 SET [status] = 0
937 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
938 WHERE idagreementandsupplement = @agreementandsuppID
939
940 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
941 IF @retval <> 0
942 RAISERROR(N'error - enddataupdate.', 16, 1)
943
944 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp3id, @@transactionid = @tid, @@username = N'llabadmin'
945 IF @retval <> 0
946 RAISERROR(N'error - addrelationrecord.', 16, 1)
947 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
948 IF @retval <> 0
949 RAISERROR(N'error - addrelationrecord.', 16, 1)
950
951 IF @agreementsuppverid IS NOT NULL
952 BEGIN
953 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
954 IF @retval <> 0
955 RAISERROR(N'error - addrelationrecord.', 16, 1)
956 END
957
958 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
959 IF @retval <> 0
960 RAISERROR(N'error - endupdate.', 16, 1)
961
962 COMMIT TRANSACTION trans_agreementsupp3
963
964 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
965 IF @retval <> 0
966 RAISERROR(N'error - updatecompleted.', 16, 1)
967 END TRY
968 BEGIN CATCH
969 ROLLBACK TRANSACTION trans_agreementsupp3
970
971 SELECT @transerrormessage = ERROR_MESSAGE()
972 PRINT N'TRANSACTION FAILED AGREEMENTSUPP3. ERROR: ' + @transerrormessage
973 END CATCH
974 END
975
976 --HANDLE AGREEMENT SUPPLEMENT 4 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
977 SET @agreementandsuppID = NULL
978 SET @agreementsuppverid = NULL
979 SET @tid = NULL
980 IF @agreementsupp4id IS NOT NULL
981 BEGIN
982 BEGIN TRANSACTION trans_agreementsupp4
983 BEGIN TRY
984 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
985 IF @retval <> 0
986 RAISERROR(N'error - beginupdate.', 16, 1)
987
988 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion4'
989
990 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
991 IF @retval <> 0
992 RAISERROR(N'error - newrecord.', 16, 1)
993
994 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
995 IF @retval <> 0
996 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
997
998 UPDATE agreementandsupplement
999 SET [status] = 0
1000 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
1001 WHERE idagreementandsupplement = @agreementandsuppID
1002
1003 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
1004 IF @retval <> 0
1005 RAISERROR(N'error - enddataupdate.', 16, 1)
1006
1007 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp4id, @@transactionid = @tid, @@username = N'llabadmin'
1008 IF @retval <> 0
1009 RAISERROR(N'error - addrelationrecord.', 16, 1)
1010 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
1011 IF @retval <> 0
1012 RAISERROR(N'error - addrelationrecord.', 16, 1)
1013
1014 IF @agreementsuppverid IS NOT NULL
1015 BEGIN
1016 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
1017 IF @retval <> 0
1018 RAISERROR(N'error - addrelationrecord.', 16, 1)
1019 END
1020
1021 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
1022 IF @retval <> 0
1023 RAISERROR(N'error - endupdate.', 16, 1)
1024
1025 COMMIT TRANSACTION trans_agreementsupp4
1026
1027 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
1028 IF @retval <> 0
1029 RAISERROR(N'error - updatecompleted.', 16, 1)
1030 END TRY
1031 BEGIN CATCH
1032 ROLLBACK TRANSACTION trans_agreementsupp4
1033
1034 SELECT @transerrormessage = ERROR_MESSAGE()
1035 PRINT N'TRANSACTION FAILED AGREEMENTSUPP4. ERROR: ' + @transerrormessage
1036 END CATCH
1037 END
1038
1039 --HANDLE AGREEMENT SUPPLEMENT 5 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
1040 SET @agreementandsuppID = NULL
1041 SET @agreementsuppverid = NULL
1042 SET @tid = NULL
1043 IF @agreementsupp5id IS NOT NULL
1044 BEGIN
1045 BEGIN TRANSACTION trans_agreementsupp5
1046 BEGIN TRY
1047 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
1048 IF @retval <> 0
1049 RAISERROR(N'error - beginupdate.', 16, 1)
1050
1051 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion5'
1052
1053 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
1054 IF @retval <> 0
1055 RAISERROR(N'error - newrecord.', 16, 1)
1056
1057 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
1058 IF @retval <> 0
1059 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
1060
1061 UPDATE agreementandsupplement
1062 SET [status] = 0
1063 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
1064 WHERE idagreementandsupplement = @agreementandsuppID
1065
1066 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
1067 IF @retval <> 0
1068 RAISERROR(N'error - enddataupdate.', 16, 1)
1069
1070 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp5id, @@transactionid = @tid, @@username = N'llabadmin'
1071 IF @retval <> 0
1072 RAISERROR(N'error - addrelationrecord.', 16, 1)
1073 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
1074 IF @retval <> 0
1075 RAISERROR(N'error - addrelationrecord.', 16, 1)
1076
1077 IF @agreementsuppverid IS NOT NULL
1078 BEGIN
1079 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
1080 IF @retval <> 0
1081 RAISERROR(N'error - addrelationrecord.', 16, 1)
1082 END
1083
1084 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
1085 IF @retval <> 0
1086 RAISERROR(N'error - endupdate.', 16, 1)
1087
1088 COMMIT TRANSACTION trans_agreementsupp5
1089
1090 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
1091 IF @retval <> 0
1092 RAISERROR(N'error - updatecompleted.', 16, 1)
1093 END TRY
1094 BEGIN CATCH
1095 ROLLBACK TRANSACTION trans_agreementsupp5
1096
1097 SELECT @transerrormessage = ERROR_MESSAGE()
1098 PRINT N'TRANSACTION FAILED AGREEMENTSUPP5. ERROR: ' + @transerrormessage
1099 END CATCH
1100 END
1101
1102 --HANDLE AGREEMENT SUPPLEMENT 6 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
1103 SET @agreementandsuppID = NULL
1104 SET @agreementsuppverid = NULL
1105 SET @tid = NULL
1106 IF @agreementsupp6id IS NOT NULL
1107 BEGIN
1108 BEGIN TRANSACTION trans_agreementsupp6
1109 BEGIN TRY
1110 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
1111 IF @retval <> 0
1112 RAISERROR(N'error - beginupdate.', 16, 1)
1113
1114 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion6'
1115
1116 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
1117 IF @retval <> 0
1118 RAISERROR(N'error - newrecord.', 16, 1)
1119
1120 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
1121 IF @retval <> 0
1122 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
1123
1124 UPDATE agreementandsupplement
1125 SET [status] = 0
1126 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
1127 WHERE idagreementandsupplement = @agreementandsuppID
1128
1129 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
1130 IF @retval <> 0
1131 RAISERROR(N'error - enddataupdate.', 16, 1)
1132
1133 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp6id, @@transactionid = @tid, @@username = N'llabadmin'
1134 IF @retval <> 0
1135 RAISERROR(N'error - addrelationrecord.', 16, 1)
1136 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
1137 IF @retval <> 0
1138 RAISERROR(N'error - addrelationrecord.', 16, 1)
1139
1140 IF @agreementsuppverid IS NOT NULL
1141 BEGIN
1142 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
1143 IF @retval <> 0
1144 RAISERROR(N'error - addrelationrecord.', 16, 1)
1145 END
1146
1147 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
1148 IF @retval <> 0
1149 RAISERROR(N'error - endupdate.', 16, 1)
1150
1151 COMMIT TRANSACTION trans_agreementsupp6
1152
1153 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
1154 IF @retval <> 0
1155 RAISERROR(N'error - updatecompleted.', 16, 1)
1156 END TRY
1157 BEGIN CATCH
1158 ROLLBACK TRANSACTION trans_agreementsupp6
1159
1160 SELECT @transerrormessage = ERROR_MESSAGE()
1161 PRINT N'TRANSACTION FAILED AGREEMENTSUPP6. ERROR: ' + @transerrormessage
1162 END CATCH
1163 END
1164
1165 --HANDLE AGREEMENT SUPPLEMENT 7 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
1166 SET @agreementandsuppID = NULL
1167 SET @agreementsuppverid = NULL
1168 SET @tid = NULL
1169 IF @agreementsupp7id IS NOT NULL
1170 BEGIN
1171 BEGIN TRANSACTION trans_agreementsupp7
1172 BEGIN TRY
1173 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
1174 IF @retval <> 0
1175 RAISERROR(N'error - beginupdate.', 16, 1)
1176
1177 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion7'
1178
1179 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
1180 IF @retval <> 0
1181 RAISERROR(N'error - newrecord.', 16, 1)
1182
1183 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
1184 IF @retval <> 0
1185 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
1186
1187 UPDATE agreementandsupplement
1188 SET [status] = 0
1189 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
1190 WHERE idagreementandsupplement = @agreementandsuppID
1191
1192 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
1193 IF @retval <> 0
1194 RAISERROR(N'error - enddataupdate.', 16, 1)
1195
1196 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp7id, @@transactionid = @tid, @@username = N'llabadmin'
1197 IF @retval <> 0
1198 RAISERROR(N'error - addrelationrecord.', 16, 1)
1199 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
1200 IF @retval <> 0
1201 RAISERROR(N'error - addrelationrecord.', 16, 1)
1202
1203 IF @agreementsuppverid IS NOT NULL
1204 BEGIN
1205 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
1206 IF @retval <> 0
1207 RAISERROR(N'error - addrelationrecord.', 16, 1)
1208 END
1209
1210 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
1211 IF @retval <> 0
1212 RAISERROR(N'error - endupdate.', 16, 1)
1213
1214 COMMIT TRANSACTION trans_agreementsupp7
1215
1216 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
1217 IF @retval <> 0
1218 RAISERROR(N'error - updatecompleted.', 16, 1)
1219 END TRY
1220 BEGIN CATCH
1221 ROLLBACK TRANSACTION trans_agreementsupp7
1222
1223 SELECT @transerrormessage = ERROR_MESSAGE()
1224 PRINT N'TRANSACTION FAILED AGREEMENTSUPP7. ERROR: ' + @transerrormessage
1225 END CATCH
1226 END
1227
1228 --HANDLE AGREEMENT SUPPLEMENT 8 - CREATE NEW AGREMENTANDSUPPLMENT RECORD AND SET RELATION FIELDS
1229 SET @agreementandsuppID = NULL
1230 SET @agreementsuppverid = NULL
1231 SET @tid = NULL
1232 IF @agreementsupp8id IS NOT NULL
1233 BEGIN
1234 BEGIN TRANSACTION trans_agreementsupp8
1235 BEGIN TRY
1236 EXEC @retval = lsp_beginupdate @@transactionid = @tid OUTPUT
1237 IF @retval <> 0
1238 RAISERROR(N'error - beginupdate.', 16, 1)
1239
1240 SELECT @agreementsuppverid = idrecord2 FROM @relationData WHERE field1 = N'agreementsupplementversion8'
1241
1242 EXEC @retval = lsp_newrecord @@table = N'agreementandsupplement', @@user = 2001, @@idrecord = @agreementandsuppID OUTPUT
1243 IF @retval <> 0
1244 RAISERROR(N'error - newrecord.', 16, 1)
1245
1246 EXEC @retval = [dbo].[lsp_addupdatelog] @@table = N'agreementandsupplement', @@idrecord = @agreementandsuppID, @@transactionid = @tid, @@username = N'llabadmin', @@new = 1, @@updated = 1
1247 IF @retval <> 0
1248 RAISERROR(N'error - addupadtelog(agreement).', 16, 1)
1249
1250 UPDATE agreementandsupplement
1251 SET [status] = 0
1252 , [supplement_timestamp] = CONVERT(DATE, @supp_timestamp)
1253 WHERE idagreementandsupplement = @agreementandsuppID
1254
1255 EXEC @retval = lsp_enddataupdate @@transactionid = @tid, @@username = N'llabadmin'
1256 IF @retval <> 0
1257 RAISERROR(N'error - enddataupdate.', 16, 1)
1258
1259 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsupp8id, @@transactionid = @tid, @@username = N'llabadmin'
1260 IF @retval <> 0
1261 RAISERROR(N'error - addrelationrecord.', 16, 1)
1262 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreement', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @idagreement, @@transactionid = @tid, @@username = N'llabadmin'
1263 IF @retval <> 0
1264 RAISERROR(N'error - addrelationrecord.', 16, 1)
1265
1266 IF @agreementsuppverid IS NOT NULL
1267 BEGIN
1268 EXEC @retval = lsp_addrelationrecord @@table1 = N'agreementandsupplement', @@field1 = N'agreementsupplementversion', @@idrecord1 = @agreementandsuppID, @@idrecord2 = @agreementsuppverid, @@transactionid = @tid, @@username = N'llabadmin'
1269 IF @retval <> 0
1270 RAISERROR(N'error - addrelationrecord.', 16, 1)
1271 END
1272
1273 EXEC @retval = lsp_endupdate @@transactionid = @tid, @@username = N'llabadmin'
1274 IF @retval <> 0
1275 RAISERROR(N'error - endupdate.', 16, 1)
1276
1277 COMMIT TRANSACTION trans_agreementsupp8
1278
1279 EXEC @retval = lsp_updatecompleted @@transactionid = @tid
1280 IF @retval <> 0
1281 RAISERROR(N'error - updatecompleted.', 16, 1)
1282 END TRY
1283 BEGIN CATCH
1284 ROLLBACK TRANSACTION trans_agreementsupp8
1285
1286 SELECT @transerrormessage = ERROR_MESSAGE()
1287 PRINT N'TRANSACTION FAILED AGREEMENTSUPP8. ERROR: ' + @transerrormessage
1288 END CATCH
1289 END
1290
1291
1292 --END: DEAL WITH CREATING NEW RECORDS FOR EACH AGREEMENTSUPPLEMENT, ADDED BY: CHS 11/11/2015
1293
1294 -- START: Set relationdata via limetransaction
1295 DECLARE @table1 NVARCHAR(256),
1296 @field1 NVARCHAR(256),
1297 @idrecord1 INT,
1298 @idrecord2 INT,
1299 @idrelationdata INT,
1300 @tblName NVARCHAR(256),
1301 @fldName NVARCHAR(256)
1302
1303 IF CURSOR_STATUS('local', 'relationdata_cursor') >= -1
1304 BEGIN
1305 IF CURSOR_STATUS('local', 'relationdata_cursor') > -1
1306 BEGIN
1307 CLOSE relationdata_cursor
1308 END
1309 DEALLOCATE relationdata_cursor
1310 END
1311
1312 DECLARE relationdata_cursor CURSOR LOCAL READ_ONLY STATIC FORWARD_ONLY
1313 FOR SELECT idrelationdata FROM @relationData
1314 OPEN relationdata_cursor
1315 FETCH NEXT FROM relationdata_cursor INTO @idrelationdata
1316 WHILE @@FETCH_STATUS = 0
1317 BEGIN
1318 SELECT @table1 = table1
1319 ,@field1 = field1
1320 ,@idrecord1 = idrecord1
1321 ,@idrecord2 = idrecord2
1322 FROM @relationData
1323 WHERE idrelationdata = @idrelationdata
1324
1325 IF ISNULL(@table1, N'') <> N'' AND ISNULL(@field1, N'') <> N'' AND @idrecord1 IS NOT NULL AND @idrecord2 IS NOT NULL
1326 BEGIN
1327 EXEC @retval = lsp_addrelationrecord @@table1 = @table1, @@field1 = @field1, @@idrecord1 = @idrecord1
1328 ,@@idrecord2 = @idrecord2, @@transactionid = @transactionID, @@iduser = @iduser
1329 IF @retval <> 0
1330 RAISERROR(N'error - addrelationrecord.', 16, 1)
1331 END
1332 ELSE
1333 BEGIN
1334 IF LEN(@warningMessage) > 0
1335 SET @warningMessage = @warningMessage + @newLine
1336
1337 EXEC csp_getLocalName @@lang = N'sv', @@table = @table1, @@field = @field1, @@tableName = @tblName OUTPUT, @@fieldName = @fldName OUTPUT
1338 SET @warningMessage = @warningMessage + N'Hittar inte matchning på fältet: ' + @tblName + N'.' + @fldName
1339 END
1340
1341 FETCH NEXT FROM relationdata_cursor INTO @idrelationdata
1342 END
1343 CLOSE relationdata_cursor
1344 DEALLOCATE relationdata_cursor
1345
1346 DELETE FROM @relationData
1347
1348 -- END: Set relationdata via limetransaction
1349
1350 EXEC @retval = lsp_endupdate @@transactionid = @transactionID, @@iduser = @iduser
1351 IF @retval <> 0
1352 RAISERROR(N'error - endupdate.', 16, 1)
1353
1354 COMMIT TRANSACTION trans_formImport
1355
1356 EXEC @retval = lsp_updatecompleted @@transactionid = @transactionID
1357 IF @retval <> 0
1358 RAISERROR(N'error - updatecompleted.', 16, 1)
1359
1360 -- START: Logging eventual warnings
1361 UPDATE wf SET
1362 [form_status] = CASE WHEN LEN(@warningMessage) > 0 THEN @formStatus_warningId /* Varning */ ELSE @formStatus_doneId/* Inläst */ END
1363 ,[result_txt] = LEFT(@warningMessage, 4000)
1364 ,[save_date] = GETDATE()
1365 FROM web_form wf
1366 INNER JOIN #TempNewForms form ON form.RowID = @idrowData
1367 WHERE wf.idweb_form = form.idweb_form
1368 -- END: Logging eventual warnings
1369
1370 -- START: Add relation to pdf-document
1371 UPDATE agreement_document SET
1372 [company] = @idcompany
1373 ,[agreement] = @idagreement
1374 ,[type] = 357701
1375 WHERE [idagreement_document] IN
1376 (
1377 SELECT rel.idrecord
1378 FROM #TempRelations rel
1379 WHERE rel.fieldname = N'pdfdocument'
1380 )
1381 -- END: Add relation to pdf-document
1382 END TRY
1383 BEGIN CATCH
1384 ROLLBACK TRANSACTION trans_formImport
1385
1386 DECLARE @errormessage NVARCHAR(1024)
1387 SELECT @errormessage = ERROR_MESSAGE()
1388
1389 UPDATE wf SET
1390 [form_status] = @formStatus_errorId -- Fel vid inläsning
1391 ,[result_txt] = @errormessage
1392 FROM web_form wf
1393 INNER JOIN #TempNewForms form ON form.RowID = @idrowData
1394 WHERE wf.idweb_form = form.idweb_form
1395
1396 END CATCH
1397
1398 IF OBJECT_ID('tempdb..#TempRelations') IS NOT NULL
1399 DROP TABLE #TempRelations
1400
1401 FETCH NEXT FROM formCursor INTO @idrowData
1402 END
1403 CLOSE formCursor
1404 DEALLOCATE formCursor
1405
1406 IF OBJECT_ID('tempdb..#TempNewForms') IS NOT NULL
1407 DROP TABLE #TempNewForms
1408
1409END