· 4 years ago · Jun 16, 2021, 06:52 AM
1-- ALTER PROCEDURE [dbo].[astp_CostMgmt_WorkPacksSpread_ImportVerifiedData](
2BEGIN TRANSACTION
3 DECLARE
4
5 @Domain AS NVARCHAR(128) = 'A240_CCM',
6 @ImportID AS INT = '65',
7 @Append AS BIT = 0
8-- )
9 -- AS
10
11
12 DECLARE @ImportData AS NVARCHAR(20)
13 DECLARE @InputType AS NVARCHAR(20)
14 DECLARE @PhasingType AS NVARCHAR(20)
15 DECLARE @Source AS NVARCHAR(20)
16 DECLARE @vCurrentCutOff AS DATE
17
18 SELECT @ImportData = I.ImportData, @InputType = I.InputType, @PhasingType =I.PhasingType, @Source = I.[Source], @vCurrentCutOff = C.CurrentCutOff
19 FROM dbo.atbl_CostMgmt_PhasingImport AS I WITH (NOLOCK)
20 INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
21 ON C.Domain = I.Domain
22 WHERE I.Domain = @Domain
23 AND I.ImportID = @ImportID
24
25--CHECKS FOR CAPABILITIES AND PHASING LOCKS --
26
27DECLARE @Error AS BIT = 0
28
29IF EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_Settings AS S WITH (NOLOCK)
30 WHERE S.SpreadLocked = 1 AND S.Domain = @Domain)
31BEGIN
32 RAISERROR ('Updates of Phasing Data have been locked. Please contact Baseline Administrator.', 18, 1)
33 SET @Error = 1
34END
35
36IF @ImportData = 'Forecast'
37BEGIN
38 IF NOT EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_MyCapabilities_Domains AS MCD
39 WHERE MCD.Domain = @Domain AND CapabilityCode='cost_phasing_updateforecast')
40 AND EXISTS (SELECT 1 FROM dbo.stbl_System_CapabilityCodes WITH (NOLOCK) WHERE Code = 'cost_phasing_updateforecast')
41 BEGIN
42 RAISERROR ('You do not have capability to update Forecast data. Please contact Baseline Administrator.', 18, 1)
43 SET @Error = 1
44 END
45
46 IF EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_WF_ME_MyCostControllersCapabilities
47 WHERE CapabilityCode='cost_phasing_updateforecast'
48 AND Domain = @Domain
49 AND IsChecked = 0)
50 AND EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_Settings AS S WITH (NOLOCK) WHERE S.Domain = @Domain AND S.UseWF_MonthEnd = 1)
51 BEGIN
52 RAISERROR ('According to Month-end workflow Forecast Data updates are locked. Please contact Baseline Administrator.', 18, 1)
53 SET @Error = 1
54 END
55END
56
57IF @ImportData = 'Planned'
58BEGIN
59 IF NOT EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_MyCapabilities_Domains AS MCD
60 WHERE MCD.Domain = @Domain AND CapabilityCode='cost_phasing_updateplanned')
61 AND EXISTS (SELECT 1 FROM dbo.stbl_System_CapabilityCodes WITH (NOLOCK) WHERE Code = 'cost_phasing_updateplanned')
62 BEGIN
63 RAISERROR ('You do not have capability to update Planned data. Please contact Baseline Administrator.', 18, 1)
64 SET @Error = 1
65 END
66
67 IF EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_WF_ME_MyCostControllersCapabilities
68 WHERE CapabilityCode='cost_phasing_updateplanned'
69 AND Domain = @Domain
70 AND IsChecked = 0)
71 AND EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_Settings AS S WITH (NOLOCK) WHERE S.Domain = @Domain AND S.UseWF_MonthEnd = 1)
72 BEGIN
73 RAISERROR ('According to Month-end workflow Planned Data updates are locked. Please contact Baseline Administrator.', 18, 1)
74 SET @Error = 1
75 END
76END
77
78IF @ImportData = 'Actual'
79BEGIN
80 IF NOT EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_MyCapabilities_Domains AS MCD
81 WHERE MCD.Domain = @Domain AND CapabilityCode='cost_phasing_updatepactuals')
82 AND EXISTS (SELECT 1 FROM dbo.stbl_System_CapabilityCodes WITH (NOLOCK) WHERE Code = 'cost_phasing_updatepactuals')
83 BEGIN
84 RAISERROR ('You do not have capability to update Actuals data. Please contact Baseline Administrator.', 18, 1)
85 SET @Error = 1
86 END
87
88 IF EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_WF_ME_MyCostControllersCapabilities
89 WHERE CapabilityCode='cost_phasing_updatepactuals'
90 AND Domain = @Domain
91 AND IsChecked = 0)
92 AND EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_Settings AS S WITH (NOLOCK) WHERE S.Domain = @Domain AND S.UseWF_MonthEnd = 1)
93 BEGIN
94 RAISERROR ('According to Month-end workflow Actuals Data updates are locked. Please contact Baseline Administrator.', 18, 1)
95 SET @Error = 1
96 END
97END
98
99IF @ImportData = 'Invoiced'
100BEGIN
101 IF NOT EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_MyCapabilities_Domains AS MCD
102 WHERE MCD.Domain = @Domain AND CapabilityCode='cost_phasing_updateinvoiced')
103 AND EXISTS (SELECT 1 FROM dbo.stbl_System_CapabilityCodes WITH (NOLOCK) WHERE Code = 'cost_phasing_updateinvoiced')
104 BEGIN
105 RAISERROR ('You do not have capability to update Invoiced data. Please contact Baseline Administrator.', 18, 1)
106 SET @Error = 1
107 END
108
109 IF EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_WF_ME_MyCostControllersCapabilities
110 WHERE CapabilityCode='cost_phasing_updateinvoiced'
111 AND Domain = @Domain
112 AND IsChecked = 0)
113 AND EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_Settings AS S WITH (NOLOCK) WHERE S.Domain = @Domain AND S.UseWF_MonthEnd = 1)
114 BEGIN
115 RAISERROR ('According to Month-end workflow Invoiced Data updates are locked. Please contact Baseline Administrator.', 18, 1)
116 SET @Error = 1
117 END
118END
119
120IF @ImportData = 'Booked'
121BEGIN
122 IF NOT EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_MyCapabilities_Domains AS MCD
123 WHERE MCD.Domain = @Domain AND CapabilityCode='cost_phasing_updatebooked')
124 AND EXISTS (SELECT 1 FROM dbo.stbl_System_CapabilityCodes WITH (NOLOCK) WHERE Code = 'cost_phasing_updatebooked')
125 BEGIN
126 RAISERROR ('You do not have capability to update Booked data. Please contact Baseline Administrator.', 18, 1)
127 SET @Error = 1
128 END
129
130 IF EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_WF_ME_MyCostControllersCapabilities
131 WHERE CapabilityCode='cost_phasing_updatebooked'
132 AND Domain = @Domain
133 AND IsChecked = 0)
134 AND EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_Settings AS S WITH (NOLOCK) WHERE S.Domain = @Domain AND S.UseWF_MonthEnd = 1)
135 BEGIN
136 RAISERROR ('According to Month-end workflow Booked Data updates are locked. Please contact Baseline Administrator.', 18, 1)
137 SET @Error = 1
138 END
139END
140
141IF @ImportData = 'Cash'
142BEGIN
143 IF NOT EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_MyCapabilities_Domains AS MCD
144 WHERE MCD.Domain = @Domain AND CapabilityCode='cost_phasing_updateCash')
145 AND EXISTS (SELECT 1 FROM dbo.stbl_System_CapabilityCodes WITH (NOLOCK) WHERE Code = 'cost_phasing_updateCash')
146 BEGIN
147 RAISERROR ('You do not have capability to update Cash data. Please contact Baseline Administrator.', 18, 1)
148 SET @Error = 1
149 END
150
151 IF EXISTS (SELECT 1 FROM dbo.aviw_CostMgmt_WF_ME_MyCostControllersCapabilities
152 WHERE CapabilityCode='cost_phasing_updateCash'
153 AND Domain = @Domain
154 AND IsChecked = 0)
155 AND EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_Settings AS S WITH (NOLOCK) WHERE S.Domain = @Domain AND S.UseWF_MonthEnd = 1)
156 BEGIN
157 RAISERROR ('According to Month-end workflow Cash Data updates are locked. Please contact Baseline Administrator.', 18, 1)
158 SET @Error = 1
159 END
160END
161
162IF EXISTS (SELECT 1 FROM dbo.atbl_CostMgmt_PhasingImportData AS PID WITH (NOLOCK)
163 WHERE PID.Domain = @Domain AND PID.ImportID = @ImportID AND PID.CutOff < @vCurrentCutOff
164 )
165BEGIN
166 IF NOT EXISTS(SELECT 1 FROM dbo.aviw_CostMgmt_MyCapabilities_Domains AS MCD
167 WHERE MCD.Domain = @Domain AND CapabilityCode='cost_phasing_updatehistorical')
168 AND EXISTS (SELECT 1 FROM dbo.stbl_System_CapabilityCodes WITH (NOLOCK) WHERE Code = 'cost_phasing_updatehistorical')
169 BEGIN
170 DECLARE @vErrorMsg AS NVARCHAR(MAX) = 'You do not have capability to update historical ' + @ImportData + ' data. Please contact Baseline Administrator.'
171 RAISERROR (@vErrorMsg ,18,1)
172 SET @Error = 1
173 END
174END
175
176IF @Error = 1
177BEGIN
178 RETURN
179END
180
181
182
183 CREATE TABLE #vTable
184 (
185 Ref1 NVARCHAR(100) COLLATE Latin1_General_CI_AS,
186 Domain NVARCHAR(128) COLLATE Latin1_General_CI_AS,
187 CutOff SMALLDATETIME,
188 ImportData DECIMAL(19, 7),
189 Cumulative DECIMAL(19, 7),
190 CumulativeNew DECIMAL(19,7),
191 Periodic DECIMAL(19, 7),
192 PeriodicNew DECIMAL(19,7),
193 MinCutOff SMALLDATETIME,
194 MaxCutOff SMALLDATETIME,
195 PrevCutOff SMALLDATETIME,
196 NextCutOff SMALLDATETIME
197 );
198
199
200 ;WITH CTE AS (
201 SELECT PID.Ref1, PID.CutOff,
202 CASE WHEN @PhasingType = 'Cumulative' THEN PID.Cumulative
203 WHEN @PhasingType = 'Periodic' THEN PID.Periodic
204 END AS [Value]
205 FROM dbo.atbl_CostMgmt_PhasingImportData AS PID WITH (NOLOCK)
206 WHERE PID.Domain = @Domain AND PID.ImportID = @ImportID
207 ), WPS AS (
208 SELECT Ref1, CutOff, SUM([Value]) AS [Value]
209 FROM CTE
210 GROUP BY Ref1, CutOff
211 ), MINMAX AS
212 (
213 SELECT Ref1, MIN(CutOff) AS MinCutOff, MAX(CutOff) AS MaxCutOff
214 FROM WPS
215 GROUP BY Ref1
216 )
217
218 INSERT INTO #vTable (Domain, Ref1, CutOff, ImportData, Cumulative, CumulativeNew, Periodic, PeriodicNew, MinCutOff, MaxCutOff, PrevCutOff, NextCutOff)
219 -- COST
220 SELECT @Domain, WPS2.WorkPackID, WPS2.CutOff, WPS.[Value],
221 CASE WHEN @ImportData = 'Forecast' THEN WPS2.CumForecast
222 WHEN @ImportData = 'Planned' THEN WPS2.CumPlanned
223 WHEN @ImportData = 'Cash' THEN WPS2.CumCashForecast
224 WHEN @ImportData = 'Invoice' THEN WPS2.CumInvoiced
225 WHEN @ImportData = 'Booked' THEN WPS2.CumBooked
226 WHEN @ImportData = 'Actual' THEN WPS2.CumActual
227 END AS Cumulative,
228 CASE WHEN @Append = 1 AND @PhasingType = 'Cumulative'
229 THEN ISNULL(CASE WHEN @ImportData = 'Forecast' THEN WPS2.CumForecast
230 WHEN @ImportData = 'Planned' THEN WPS2.CumPlanned
231 WHEN @ImportData = 'Cash' THEN WPS2.CumCashForecast
232 WHEN @ImportData = 'Invoice' THEN WPS2.CumInvoiced
233 WHEN @ImportData = 'Booked' THEN WPS2.CumBooked
234 WHEN @ImportData = 'Actual' THEN WPS2.CumActual
235 END,0) + ISNULL(WPS.[Value],0)
236 WHEN @Append = 0 AND @PhasingType = 'Cumulative'
237 THEN WPS.[Value]
238 END AS CumulativeNew,
239 CASE WHEN @ImportData = 'Forecast' THEN WPS2.PerForecast
240 WHEN @ImportData = 'Planned' THEN WPS2.PerPlanned
241 WHEN @ImportData = 'Cash' THEN WPS2.PerCashForecast
242 WHEN @ImportData = 'Invoice' THEN WPS2.PerInvoiced
243 WHEN @ImportData = 'Booked' THEN WPS2.PerBooked
244 WHEN @ImportData = 'Actual' THEN WPS2.PerActual
245 END AS Periodic,
246 CASE WHEN @Append = 1 AND @PhasingType = 'Periodic'
247 THEN ISNULL(CASE WHEN @ImportData = 'Forecast' THEN WPS2.PerForecast
248 WHEN @ImportData = 'Planned' THEN WPS2.PerPlanned
249 WHEN @ImportData = 'Cash' THEN WPS2.PerCashForecast
250 WHEN @ImportData = 'Invoice' THEN WPS2.PerInvoiced
251 WHEN @ImportData = 'Booked' THEN WPS2.PerBooked
252 WHEN @ImportData = 'Actual' THEN WPS2.PerActual
253 END, 0) + ISNULL(WPS.[Value], 0)
254 WHEN @Append = 0 AND @PhasingType = 'Periodic'
255 THEN WPS.[Value]
256 END AS PeriodicNew,
257 (SELECT MIN(M.MinCutOff) FROM MINMAX AS M WHERE WPS2.WorkPackID = M.Ref1) AS MinCutOff,
258 (SELECT MIN(M.MaxCutOff) FROM MINMAX AS M WHERE WPS2.WorkPackID = M.Ref1) AS MaxCutOff,
259 WPS2.PrevCutOff,
260 WPS2.NextCutOff
261 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS2 WITH (NOLOCK)
262 LEFT JOIN WPS ON WPS.Ref1 = WPS2.WorkPackID AND WPS.CutOff = WPS2.CutOff
263 WHERE EXISTS (SELECT 1 FROM WPS WHERE Ref1 = WPS2.WorkPackID)
264 AND WPS2.Domain = @Domain
265 AND @InputType = 'Cost'
266 AND @Source = 'WorkPacks'
267
268
269 UNION ALL
270
271 ------ PROGRESS --
272 SELECT @Domain, WPS2.WorkPackID, WPS2.CutOff, WPS.[Value],
273 CASE WHEN @ImportData = 'Forecast' THEN WPS2.CumForecastProg
274 WHEN @ImportData = 'Planned' THEN WPS2.CumPlannedProg
275 WHEN @ImportData = 'Actual' THEN WPS2.CumActualProg
276 END AS Cumulative,
277 CASE WHEN @Append = 1 AND @PhasingType = 'Cumulative'
278 THEN ISNULL(CASE WHEN @ImportData = 'Forecast' THEN WPS2.CumForecastProg
279 WHEN @ImportData = 'Planned' THEN WPS2.CumPlannedProg
280 WHEN @ImportData = 'Actual' THEN WPS2.CumActualProg
281 END,0) + ISNULL(WPS.[Value],0)
282 WHEN @Append = 0 AND @PhasingType = 'Cumulative'
283 THEN WPS.[Value]
284 END AS CumulativeNew,
285 CASE WHEN @ImportData = 'Forecast' THEN WPS2.PerForecastProg
286 WHEN @ImportData = 'Planned' THEN WPS2.PerPlannedProg
287 WHEN @ImportData = 'Actual' THEN WPS2.PerActualProg
288 END AS Periodic,
289 CASE WHEN @Append = 1 AND @PhasingType = 'Periodic'
290 THEN ISNULL(CASE WHEN @ImportData = 'Forecast' THEN WPS2.PerForecastProg
291 WHEN @ImportData = 'Planned' THEN WPS2.PerPlannedProg
292 WHEN @ImportData = 'Actual' THEN WPS2.PerActualProg
293 END, 0) + ISNULL(WPS.[Value], 0)
294 WHEN @Append = 0 AND @PhasingType = 'Periodic'
295 THEN WPS.[Value]
296 END AS PeriodicNew,
297 (SELECT MIN(M.MinCutOff) FROM MINMAX AS M WHERE WPS2.WorkPackID = M.Ref1) AS MinCutOff,
298 (SELECT MIN(M.MaxCutOff) FROM MINMAX AS M WHERE WPS2.WorkPackID = M.Ref1) AS MaxCutOff,
299 WPS2.PrevCutOff,
300 WPS2.NextCutOff
301 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS2 WITH (NOLOCK)
302 LEFT JOIN WPS ON WPS.Ref1 = WPS2.WorkPackID AND WPS.CutOff = WPS2.CutOff
303 WHERE EXISTS (SELECT 1 FROM WPS WHERE Ref1 = WPS2.WorkPackID)
304 AND WPS2.Domain = @Domain
305 AND @InputType = 'Progress'
306 AND @Source = 'WorkPacks'
307
308 UNION ALL
309
310 ------ Hours hrs --
311 SELECT @Domain, WPS2.WorkPackID, WPS2.CutOff, WPS.[Value],
312 CASE WHEN @ImportData = 'Forecast' THEN WPS2.CumForecastHrs
313 WHEN @ImportData = 'Planned' THEN WPS2.CumPlannedHrs
314 WHEN @ImportData = 'Actual' THEN WPS2.CumActualHrs
315 END AS Cumulative,
316 CASE WHEN @Append = 1 AND @PhasingType = 'Cumulative'
317 THEN ISNULL(CASE WHEN @ImportData = 'Forecast' THEN WPS2.CumForecastHrs
318 WHEN @ImportData = 'Planned' THEN WPS2.CumPlannedHrs
319 WHEN @ImportData = 'Actual' THEN WPS2.CumActualHrs
320 END,0) + ISNULL(WPS.[Value],0)
321 WHEN @Append = 0 AND @PhasingType = 'Cumulative'
322 THEN WPS.[Value]
323 END AS CumulativeNew,
324 CASE WHEN @ImportData = 'Forecast' THEN WPS2.PerForecastHrs
325 WHEN @ImportData = 'Planned' THEN WPS2.PerPlannedHrs
326 WHEN @ImportData = 'Actual' THEN WPS2.PerActualHrs
327 END AS Periodic,
328 CASE WHEN @Append = 1 AND @PhasingType = 'Periodic'
329 THEN ISNULL(CASE WHEN @ImportData = 'Forecast' THEN WPS2.PerForecastHrs
330 WHEN @ImportData = 'Planned' THEN WPS2.PerPlannedHrs
331 WHEN @ImportData = 'Actual' THEN WPS2.PerActualHrs
332 END, 0) + ISNULL(WPS.[Value], 0)
333 WHEN @Append = 0 AND @PhasingType = 'Periodic'
334 THEN WPS.[Value]
335 END AS PeriodicNew,
336 (SELECT MIN(M.MinCutOff) FROM MINMAX AS M WHERE WPS2.WorkPackID = M.Ref1) AS MinCutOff,
337 (SELECT MIN(M.MaxCutOff) FROM MINMAX AS M WHERE WPS2.WorkPackID = M.Ref1) AS MaxCutOff,
338 WPS2.PrevCutOff,
339 WPS2.NextCutOff
340 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS2 WITH (NOLOCK)
341 LEFT JOIN WPS ON WPS.Ref1 = WPS2.WorkPackID AND WPS.CutOff = WPS2.CutOff
342 WHERE EXISTS (SELECT 1 FROM WPS WHERE Ref1 = WPS2.WorkPackID)
343 AND WPS2.Domain = @Domain
344 AND @InputType = 'Hours'
345 AND @Source = 'WorkPacks'
346
347
348
349 IF @PhasingType = 'Periodic'
350 BEGIN
351 UPDATE T
352 SET T.PeriodicNew = T.Periodic
353 FROM #vTable AS T
354 WHERE T.PeriodicNew IS NULL
355 AND CutOff <= MaxCutOff
356 AND @Append = 0
357
358 UPDATE T
359 SET T.PeriodicNew = 0
360 FROM #vTable AS T
361 WHERE CutOff > MaxCutOff AND @Append = 0
362
363 UPDATE T
364 SET T.CumulativeNew = (SELECT SUM(ISNULL(W2.PeriodicNew, W2.Periodic))
365 FROM #vTable AS W2
366 WHERE W2.CutOff <= T.CutOff
367 AND W2.Ref1 = T.Ref1)
368 FROM #vTable AS T
369
370 END
371 ELSE
372 IF @PhasingType = 'Cumulative'
373 BEGIN
374 UPDATE T
375 SET T.CumulativeNew = (SELECT CumulativeNew
376 FROM #vTable T2
377 WHERE T2.Ref1 = T.Ref1 AND T2.CutOff = MaxCutOff),
378 T.PeriodicNew = 0
379 FROM #vTable AS T
380 WHERE CutOff > MaxCutOff;
381
382 UPDATE T
383 SET T.CumulativeNew = ISNULL(T.CumulativeNew, T.Cumulative)
384 FROM #vTable AS T WHERE CutOff < MaxCutOff
385
386 UPDATE T
387 SET T.PeriodicNew = ISNULL(T.CumulativeNew, 0) - ISNULL(TP.CumulativeNew, 0)
388 FROM #vTable AS T
389 LEFT JOIN #vTable AS TP ON TP.Ref1 = T.Ref1
390 AND TP.CutOff = T.PrevCutOff
391 END
392
393 IF @Source = 'WorkPacks'
394 BEGIN
395 IF @InputType = 'Cost'
396 BEGIN
397 UPDATE WPS
398 SET WPS.CumForecast = VT.CumulativeNew,
399 WPS.PerForecast = VT.PeriodicNew,
400 WPS.CUT = 0
401 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
402 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
403 WHERE @ImportData = 'Forecast'
404
405 UPDATE WPS
406 SET WPS.CumActual = VT.CumulativeNew,
407 WPS.PerActual = VT.PeriodicNew,
408 WPS.CUT = 0
409 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
410 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
411 WHERE @ImportData = 'Actual'
412 AND WPS.CutOff <= @vCurrentCutOff -- only allows to import up to current cut-off
413
414 UPDATE WPS
415 SET WPS.CumPlanned = VT.CumulativeNew,
416 WPS.PerPlanned = VT.PeriodicNew,
417 WPS.CUT = 0
418 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
419 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
420 WHERE @ImportData = 'Planned'
421
422 UPDATE WPS
423 SET WPS.CumCashForecast = VT.CumulativeNew,
424 WPS.PerCashForecast = VT.PeriodicNew,
425 WPS.CUT = 0
426 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
427 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
428 WHERE @ImportData = 'Cash'
429
430 UPDATE WPS
431 SET WPS.CumInvoiced = VT.CumulativeNew,
432 WPS.PerInvoiced = VT.PeriodicNew,
433 WPS.CUT = 0
434 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
435 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
436 WHERE @ImportData = 'Invoiced'
437 AND WPS.CutOff <= @vCurrentCutOff
438
439 UPDATE WPS
440 SET WPS.CumBooked = VT.CumulativeNew,
441 WPS.PerBooked = VT.PeriodicNew,
442 WPS.CUT = 0
443 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
444 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1
445 AND WPS.CutOff = VT.CutOff
446 WHERE @ImportData = 'Booked'
447 AND WPS.CutOff <= @vCurrentCutOff
448
449 --Actual--
450 UPDATE WPS
451 SET WPS.CumActualProg = CAST(WPS.CumActual/NULLIF(WPSCurr.TotPlanned,0) AS DECIMAL(38,17)),
452 WPS.CUT = 0
453 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
454 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
455 ON WP.Domain = WPS.Domain
456 AND WP.WorkPackID = WPS.WorkPackID
457 INNER JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSCurr WITH (NOLOCK)
458 ON WPS.Domain = WPSCurr.Domain
459 AND WPS.WorkPackID = WPSCurr.WorkPackID
460 INNER JOIN #vTable AS VT
461 ON WPS.Domain = VT.Domain
462 AND WPS.WorkPackID = VT.Ref1
463 AND WPS.CutOff = VT.CutOff
464 INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
465 ON C.Domain = WPS.Domain
466 AND C.CurrentCutOff = WPSCurr.CutOff
467 WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0)
468 AND @ImportData = 'Actual'
469 AND WPS.CutOff <= @vCurrentCutOff
470
471
472 UPDATE WPS
473 SET PerActualProg = WPS.CumActualProg - ISNULL(WPSP.CumActualProg,0),
474 CUT = 0
475 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
476 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
477 ON WP.Domain = WPS.Domain
478 AND WP.WorkPackID = WPS.WorkPackID
479 LEFT JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSP WITH (NOLOCK)
480 ON WPSP.Domain = WPS.Domain
481 AND WPSP.WorkPackID = WPS.WorkPackID
482 AND WPSP.CutOff = WPS.PrevCutOff
483 INNER JOIN #vTable AS VT
484 ON WPS.Domain = VT.Domain
485 AND WPS.WorkPackID = VT.Ref1
486 WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0)
487 AND WPS.PrevCutOff <= VT.CutOff
488 AND VT.CutOff <= @vCurrentCutOff
489 AND @ImportData = 'Actual'
490
491 -- AUTO UPDATE OF EARNED WHEN ACTUAL IS IMPORTED--
492 UPDATE WPS
493 SET CumEarned = CASE WHEN WP.Measurable = 1
494 THEN dbo.afnc_CostMgmt_CalculateEarned(WPS.Domain, WPS.WorkPackID, WPS.CutOff, WPS.CumActualProg, WP.LinkCostProg, WP.CurrentCostEstimate, WP.Measurable, WPS.CumActual, WPS.CumPlanned)
495 ELSE WPS.CumActual END,
496 CUT = 0
497 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
498 INNER JOIN #vTable AS VT
499 ON WPS.Domain = VT.Domain
500 AND WPS.WorkPackID = VT.Ref1
501 AND WPS.CutOff = VT.CutOff
502 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew (NOLOCK) AS WP
503 ON WP.Domain = WPS.Domain
504 AND WP.WorkPackID = WPS.WorkPackID
505 AND WPS.CutOff <= @vCurrentCutOff
506 WHERE @ImportData = 'Actual'
507
508 UPDATE WPS
509 SET PerEarned = WPS.CumEarned - ISNULL(WPSP.CumEarned,0),
510 CUT = 0
511 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
512 LEFT JOIN dbo.atbl_CostMgmt_WorkPacksSpread (NOLOCK) WPSP
513 ON WPSP.Domain = WPS.Domain
514 AND WPSP.WorkPackID = WPS.WorkPackID
515 AND WPSP.CutOff = WPS.PrevCutOff
516 INNER JOIN #vTable AS VT
517 ON WPS.Domain = VT.Domain
518 AND WPS.WorkPackID = VT.Ref1
519 INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
520 ON C.Domain = WPS.Domain
521 WHERE WPS.PrevCutOff <= VT.CutOff
522 AND VT.CutOff <= C.CurrentCutOff
523 AND @ImportData = 'Actual'
524
525 -- AUTO UPDATE OF FORECAST WHEN ACTUAL IS IMPORTED--
526 UPDATE WPS
527 SET WPS.CumForecast = WPS.CumActual,
528 WPS.CUT = 0
529 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
530 INNER JOIN #vTable AS VT
531 ON WPS.Domain = VT.Domain
532 AND WPS.WorkPackID = VT.Ref1
533 AND WPS.CutOff = VT.CutOff
534 AND WPS.CutOff <= @vCurrentCutOff
535
536 UPDATE WPS
537 SET PerForecast = WPS.CumForecast - ISNULL(WPSP.CumForecast,0),
538 CUT = 0
539 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
540 LEFT JOIN dbo.atbl_CostMgmt_WorkPacksSpread (NOLOCK) WPSP
541 ON WPSP.Domain = WPS.Domain
542 AND WPSP.WorkPackID = WPS.WorkPackID
543 AND WPSP.CutOff = WPS.PrevCutOff
544 INNER JOIN #vTable AS VT
545 ON WPS.Domain = VT.Domain
546 AND WPS.WorkPackID = VT.Ref1
547 INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
548 ON C.Domain = WPS.Domain
549 WHERE WPS.PrevCutOff <= VT.CutOff
550 AND VT.CutOff <= C.CurrentCutOff
551 AND @ImportData = 'Actual'
552
553 --Forecast--
554
555 UPDATE WPS
556 SET WPS.CumForecastProg = CAST(WPS.CumForecast/NULLIF(WPSCurr.TotForecast,0) AS DECIMAL(38,17)),
557 WPS.CUT = 0
558 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
559 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
560 ON WP.Domain = WPS.Domain
561 AND WP.WorkPackID = WPS.WorkPackID
562 INNER JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSCurr WITH (NOLOCK)
563 ON WPS.Domain = WPSCurr.Domain
564 AND WPS.WorkPackID = WPSCurr.WorkPackID
565 INNER JOIN #vTable AS VT
566 ON WPS.Domain = VT.Domain
567 AND WPS.WorkPackID = VT.Ref1
568 AND WPS.CutOff = VT.CutOff
569 INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
570 ON C.Domain = WPS.Domain
571 AND C.CurrentCutOff = WPSCurr.CutOff
572 WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0)
573 AND @ImportData = 'Forecast'
574
575
576 UPDATE WPS
577 SET PerForecastProg = WPS.CumForecastProg - ISNULL(WPSP.CumForecastProg,0),
578 CUT = 0
579 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
580 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
581 ON WP.Domain = WPS.Domain
582 AND WP.WorkPackID = WPS.WorkPackID
583 LEFT JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSP WITH (NOLOCK)
584 ON WPSP.Domain = WPS.Domain
585 AND WPSP.WorkPackID = WPS.WorkPackID
586 AND WPSP.CutOff = WPS.PrevCutOff
587 INNER JOIN #vTable AS VT
588 ON WPS.Domain = VT.Domain
589 AND WPS.WorkPackID = VT.Ref1
590 WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0)
591 AND WPS.PrevCutOff <= VT.CutOff
592 AND VT.CutOff <= @vCurrentCutOff
593 AND @ImportData = 'Forecast'
594
595 --Planned--
596
597
598 BEGIN TRY
599
600 -- UPDATE WPS
601 -- SET
602 SELECT
603 -- WPS.CumPlannedProg = CAST(NULLIF(WPS.CumPlanned/ISNULL(WPSCurr.TotPlanned,0),0) AS DECIMAL(38,19)),
604 -- CAST(WPS.CumPlanned/NULLIF(WPSCurr.TotPlanned,0) AS DECIMAL(38,17))
605 -- CAST(ISNULL(CAST(WPSCurr.CumPlanned AS DECIMAL(38,17)),0)/NULLIF(CAST(WPSCurr.TotPlanned AS DECIMAL(38,17)),0) AS DECIMAL(38,17)) AS Result
606 -- WPS.CUT = 0
607 WPS.CumPlanned,
608 WPSCurr.TotPlanned
609
610 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
611 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
612 ON WP.Domain = WPS.Domain
613 AND WP.WorkPackID = WPS.WorkPackID
614 INNER JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSCurr WITH (NOLOCK)
615 ON WPS.Domain = WPSCurr.Domain
616 AND WPS.WorkPackID = WPSCurr.WorkPackID
617 INNER JOIN #vTable AS VT
618 ON WPS.Domain = VT.Domain
619 AND WPS.WorkPackID = VT.Ref1
620 AND WPS.CutOff = VT.CutOff
621 INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
622 ON C.Domain = WPS.Domain
623 AND C.CurrentCutOff = WPSCurr.CutOff
624 WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0)
625 AND @ImportData = 'Planned'
626 END TRY
627 BEGIN CATCH
628 SELECT
629 ERROR_NUMBER() AS ErrorNumber
630 ,ERROR_SEVERITY() AS ErrorSeverity
631 ,ERROR_STATE() AS ErrorState
632 ,ERROR_PROCEDURE() AS ErrorProcedure
633 ,ERROR_LINE() AS ErrorLine
634 ,ERROR_MESSAGE() AS ErrorMessage;
635 THROW
636 END CATCH;
637
638 -- SELECT WPS.WorkPackID, ISNULL(WPS.CumPlanned,0), WPSCurr.TotPlanned
639 -- FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
640 -- INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
641 -- ON WP.Domain = WPS.Domain
642 -- AND WP.WorkPackID = WPS.WorkPackID
643 -- INNER JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSCurr WITH (NOLOCK)
644 -- ON WPS.Domain = WPSCurr.Domain
645 -- AND WPS.WorkPackID = WPSCurr.WorkPackID
646 -- INNER JOIN #vTable AS VT
647 -- ON WPS.Domain = VT.Domain
648 -- AND WPS.WorkPackID = VT.Ref1
649 -- AND WPS.CutOff = VT.CutOff
650 -- INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
651 -- ON C.Domain = WPS.Domain
652 -- AND C.CurrentCutOff = WPSCurr.CutOff
653 -- WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0) AND WPSCurr.TotPlanned <> 0
654 -- AND @ImportData = 'Planned'
655
656 -- SELECT
657 -- WPS.WorkPackID,
658 -- WPS.CumPlanned,
659 -- WPSCurr.TotPlanned
660 -- -- CASE WHEN TRY_CAST(WPS.CumPlanned/NULLIF(WPSCurr.TotPlanned,0) AS DECIMAL(38,17)) IS NULL
661 -- -- THEN WPS.WorkpackID
662 -- -- ELSE WPS.CumPlanned + ' ' + WPSCurr.TotPlanned
663 -- -- END
664 -- FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
665 -- INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
666 -- ON WP.Domain = WPS.Domain
667 -- AND WP.WorkPackID = WPS.WorkPackID
668 -- INNER JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSCurr WITH (NOLOCK)
669 -- ON WPS.Domain = WPSCurr.Domain
670 -- AND WPS.WorkPackID = WPSCurr.WorkPackID
671 -- INNER JOIN #vTable AS VT
672 -- ON WPS.Domain = VT.Domain
673 -- AND WPS.WorkPackID = VT.Ref1
674 -- AND WPS.CutOff = VT.CutOff
675 -- INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
676 -- ON C.Domain = WPS.Domain
677 -- AND C.CurrentCutOff = WPSCurr.CutOff
678 -- WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0) AND WPSCurr.TotPlanned <> 0
679 -- AND @ImportData = 'Planned'
680 -- AND TRY_CAST(WPS.CumPlanned/NULLIF(WPSCurr.TotPlanned,0) AS DECIMAL(38,17)) IS NULL
681
682 UPDATE WPS
683 -- SET WPS.CumPlannedProg = CAST(ISNULL(TRY_CAST(WPS.CumPlanned AS DECIMAL(38,17)),0)/NULLIF(TRY_CAST(WPSCurr.TotPlanned AS DECIMAL(38,17)),0) AS DECIMAL(38,17)),
684 SET WPS.CumPlannedProg = CAST(ISNULL(WPS.CumPlanned,0)/NULLIF(WPSCurr.TotPlanned,0) AS DECIMAL(38,17)),
685 WPS.CUT = 0
686 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
687 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
688 ON WP.Domain = WPS.Domain
689 AND WP.WorkPackID = WPS.WorkPackID
690 INNER JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSCurr WITH (NOLOCK)
691 ON WPS.Domain = WPSCurr.Domain
692 AND WPS.WorkPackID = WPSCurr.WorkPackID
693 INNER JOIN #vTable AS VT
694 ON WPS.Domain = VT.Domain
695 AND WPS.WorkPackID = VT.Ref1
696 AND WPS.CutOff = VT.CutOff
697 INNER JOIN dbo.atbl_CostMgmt_Constants AS C WITH (NOLOCK)
698 ON C.Domain = WPS.Domain
699 AND C.CurrentCutOff = WPSCurr.CutOff
700 WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0)
701 AND @ImportData = 'Planned'
702
703
704 UPDATE WPS
705 SET PerPlannedProg = WPS.CumPlannedProg - ISNULL(WPSP.CumPlannedProg,0),
706 CUT = 0
707 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH (NOLOCK)
708 INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WP WITH (NOLOCK)
709 ON WP.Domain = WPS.Domain
710 AND WP.WorkPackID = WPS.WorkPackID
711 LEFT JOIN dbo.atbl_CostMgmt_WorkPacksSpread AS WPSP WITH (NOLOCK)
712 ON WPSP.Domain = WPS.Domain
713 AND WPSP.WorkPackID = WPS.WorkPackID
714 AND WPSP.CutOff = WPS.PrevCutOff
715 INNER JOIN #vTable AS VT
716 ON WPS.Domain = VT.Domain
717 AND WPS.WorkPackID = VT.Ref1
718 WHERE (WP.LinkCostProg = 1 OR WP.Measurable = 0)
719 AND WPS.PrevCutOff <= VT.CutOff
720 AND VT.CutOff <= @vCurrentCutOff
721 AND @ImportData = 'Planned'
722
723 END
724
725 IF @InputType = 'Progress'
726 BEGIN
727 UPDATE WPS
728 SET WPS.CumForecastProg = VT.CumulativeNew,
729 WPS.PerForecastProg = VT.PeriodicNew,
730 WPS.CUT = 0
731 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
732 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
733 WHERE @ImportData = 'Forecast'
734
735 UPDATE WPS
736 SET WPS.CumActualProg = VT.CumulativeNew,
737 WPS.PerActualProg = VT.PeriodicNew,
738 WPS.CUT = 0
739 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
740 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
741 WHERE @ImportData = 'Actual'
742 AND WPS.CutOff <= @vCurrentCutOff
743
744 UPDATE WPS
745 SET WPS.CumPlannedProg = VT.CumulativeNew,
746 WPS.PerPlannedProg = VT.PeriodicNew,
747 WPS.CUT = 0
748 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
749 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
750 WHERE @ImportData = 'Planned'
751
752 -- -- INITIATING TRIGGER UPDATES ON FORECAST --
753 -- UPDATE WPS
754 -- SET WPS.CumForecastProg = WPS.CumForecastProg
755 -- FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
756 -- INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
757 -- WHERE @ImportData = 'Forecast'
758
759 -- -- INITIATING TRIGGER UPDATES ON ACTUAL --
760 -- UPDATE WPS
761 -- SET WPS.CumActualProg = WPS.CumActualProg
762 -- FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
763 -- INNER JOIN dbo.atbl_CostMgmt_WorkPacksNew AS WPN WITH (NOLOCK)
764 -- ON WPN.Domain = WPS.Domain AND WPN.WorkPackID = WPS.WorkPackID
765 -- INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
766 -- WHERE @ImportData = 'Actual'
767 -- AND WPS.CutOff <= (SELECT CurrentCutoff FROM dbo.atbl_CostMgmt_Constants WITH (NOLOCK) WHERE Domain = @Domain) -- only allows to import up to current cut-off
768
769 -- -- INITIATING TRIGGER UPDATES ON PLANNED --
770 -- UPDATE WPS
771 -- SET WPS.CumPlannedProg = WPS.CumPlannedProg
772 -- FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
773 -- INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
774 -- WHERE @ImportData = 'Planned'
775 END
776
777 IF @InputType = 'Hours'
778 BEGIN
779 UPDATE WPS
780 SET WPS.CumForecastHrs = VT.CumulativeNew,
781 WPS.PerForecastHrs = VT.PeriodicNew,
782 WPS.CUT = 0
783 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
784 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
785 WHERE @ImportData = 'Forecast'
786
787 UPDATE WPS
788 SET WPS.CumActualHrs = VT.CumulativeNew,
789 WPS.PerActualHrs = VT.PeriodicNew,
790 WPS.CUT = 0
791 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
792 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
793 WHERE @ImportData = 'Actual'
794 AND WPS.CutOff <= @vCurrentCutOff
795
796 UPDATE WPS
797 SET WPS.CumPlannedHrs = VT.CumulativeNew,
798 WPS.PerPlannedHrs = VT.PeriodicNew,
799 WPS.CUT = 0
800 FROM dbo.atbl_CostMgmt_WorkPacksSpread AS WPS WITH(NOLOCK)
801 INNER JOIN #vTable VT ON WPS.Domain = VT.Domain AND WPS.WorkPackID = VT.Ref1 AND WPS.CutOff = VT.CutOff
802 WHERE @ImportData = 'Planned'
803 END
804 END
805
806 DROP TABLE IF EXISTS #vTable
807
808 UPDATE I
809 SET I.[Status] = 'Imported',
810 I.CUT = 0
811 FROM dbo.atbl_CostMgmt_PhasingImport AS I WITH (NOLOCK)
812 WHERE I.Domain = @Domain
813 AND I.ImportID = @ImportID
814 AND ISNULL(I.[Status],'') <> 'Imported'
815
816
817ROLLBACK TRANSACTION