· 8 years ago · Dec 18, 2017, 06:44 PM
1CREATE PROCEDURE [dbo].[p_import_payrolls]
2 @JobQueueID INT,
3 @ProjectID INT,
4 @UserID INT
5AS
6BEGIN
7
8 SET NOCOUNT ON;
9
10 -- Staging variables
11 DECLARE @STG_ID INT
12 DECLARE @STG_Type NVARCHAR(255)
13 DECLARE @STG_JobNumber NVARCHAR(255)
14 DECLARE @STG_PhaseCode NVARCHAR(255)
15 DECLARE @STG_WbsCode NVARCHAR(255)
16 DECLARE @STG_DisciplineCode NVARCHAR(255)
17 DECLARE @STG_ActivityCode NVARCHAR(255)
18 DECLARE @STG_CostTypeCode NVARCHAR(255)
19 DECLARE @STG_IsProgressPerformance BIT
20 DECLARE @STG_IsCommitAsExpended BIT
21 DECLARE @STG_IsReserveAccount BIT
22 DECLARE @STG_IsApproved BIT
23 DECLARE @STG_EmployeeNumber NVARCHAR(255)
24 DECLARE @STG_EmployeeName NVARCHAR(255)
25 DECLARE @STG_TransactionDate DATETIME
26 DECLARE @STG_RegularHours NUMERIC(18,2)
27 DECLARE @STG_OtHours NUMERIC(18,2)
28 DECLARE @STG_DtHours NUMERIC(18,2)
29 DECLARE @STG_TotalHours NUMERIC(18,2)
30 DECLARE @STG_RegularCost NUMERIC(18,2)
31 DECLARE @STG_OtCost NUMERIC(18,2)
32 DECLARE @STG_DtCost NUMERIC(18,2)
33 DECLARE @STG_TotalCost NUMERIC(18,2)
34 DECLARE @STG_Reference_1 NVARCHAR(255)
35 DECLARE @STG_Reference_2 NVARCHAR(255)
36 DECLARE @STG_DetailSequence NVARCHAR(255)
37 DECLARE @STG_FileDate NVARCHAR(10)
38 DECLARE @STG_FileTime NVARCHAR(5)
39 DECLARE @STG_IsValid INT --
40 DECLARE @STG_ProjectID INT
41 DECLARE @STG_ControlBudgetID INT
42 DECLARE @STG_PayrollTypeID INT
43 DECLARE @STG_ProjectPhaseID INT
44 DECLARE @STG_WBSID INT
45 DECLARE @STG_DisciplineID INT
46 DECLARE @STG_ActivityTypeID INT
47 DECLARE @STG_CostTypeID INT
48 DECLARE @STG_BillingCodeID INT
49 DECLARE @STG_BillingGroupID INT
50 DECLARE @STG_ManualRevenue NUMERIC(18,2)
51 DECLARE @STG_IsBillingReferenceInherited INT
52 DECLARE @STG_PaymentDate DATE
53 -- Primary keys
54 DECLARE @PayrollEventID INT
55
56 --variables
57 DECLARE @automaticallyExtendProject BIT;
58 DECLARE @ProjectEndDate DATETIME;
59 DECLARE @ProjectStartDate DATETIME;
60 DECLARE @automaticallyExtendProjectStartDate BIT;
61
62 DECLARE @v_oldTotalCost NUMERIC(18,2)
63 DECLARE @v_IsValid BIT = 0
64 DECLARE @v_LastInserted INT
65 DECLARE @v_UserID INT
66 DECLARE @v_CommitmentItemID INT
67 DECLARE @v_ValueToCommit numeric(18,2)
68 DECLARE @v_TotalExpended NUMERIC (18,2)
69 DECLARE @v_BudgetIsCommitAsExpended BIT = 0
70 DECLARE @v_FileDate DATETIME
71 DECLARE @ClientID INT
72 DECLARE @v_ReturnValues TABLE (Val int)
73
74 SELECT @ClientID = ClientID
75 FROM JobQueue
76 WHERE [JobQueueID] = @JobQueueID
77
78 -- Delete all unapproved payrolls
79 -- The deletion of unapproved payrolls happens in the DAL layer.
80
81 -- Start update staging payroll to Invalid by default.
82 -- Set the valid state for all stg payrolls to 0 in case of re-run or resume
83 -- after critical error.
84
85 -- NEW Moved to top of validation script.
86
87 UPDATE STG_Payrolls
88 SET IsValid = 0
89 WHERE JobQueueID = @JobQueueID
90
91 --- End update staging payroll to Invalid by default.
92
93
94 -- Start Validate staging records block
95
96 -- NEW this is our validation script.
97 EXEC p_import_payrolls_validate @JobQueueID, @ProjectID, @UserID;
98 -- End Validate staging records block.
99
100 --- Start select only validated Payrolls block
101 --- NEW done by filter in 5.2
102
103 DECLARE @MyCursor CURSOR
104 SET @MyCursor = CURSOR FAST_FORWARD
105 FOR SELECT STG.ID,
106 STG.[Type],
107 STG.JobNumber,
108 STG.PhaseCode,
109 STG.WbsCode,
110 STG.DisciplineCode,
111 STG.ActivityCode,
112 STG.CostTypeCode,
113 STG.IsProgressPerformance,
114 STG.IsCommitAsExpended,
115 STG.IsReserveAccount,
116 STG.IsApproved,
117 STG.EmployeeNumber,
118 STG.EmployeeName,
119 STG.TransactionDate,
120 STG.RegularHours,
121 STG.OTHours,
122 STG.DTHours,
123 STG.TotalHours,
124 STG.RegularCost,
125 STG.OTCost,
126 STG.DTCost,
127 STG.TotalCost,
128 STG.Reference_1,
129 STG.Reference_2,
130 STG.Detail_Sequence,
131 STG.FileDate,
132 STG.FileTime,
133 STG.IsValid, --
134 STG.ProjectID,
135 STG.ControlBudgetID,
136 STG.PayrollTypeID,
137 STG.ProjectPhaseID,
138 STG.WBSID,
139 STG.DisciplineID,
140 STG.ActivityTypeID,
141 STG.CostTypeID,
142 STG.BillingCodeID,
143 STG.BillingGroupID,
144 STG.ManualRevenue,
145 STG.IsBillingReferenceInherited,
146 STG.PaymentDate
147 FROM STG_Payrolls STG
148 WHERE STG.JobQueueID = @JobQueueID
149 AND [ErrorCodeID] IS NULL
150 AND IsValid = 1
151 --- End select only validated Payrolls block
152
153 OPEN @MyCursor
154
155 FETCH NEXT FROM @MyCursor
156 INTO @STG_ID,
157 @STG_Type,
158 @STG_JobNumber,
159 @STG_PhaseCode,
160 @STG_WbsCode,
161 @STG_DisciplineCode,
162 @STG_ActivityCode,
163 @STG_CostTypeCode,
164 @STG_IsProgressPerformance,
165 @STG_IsCommitAsExpended,
166 @STG_IsReserveAccount,
167 @STG_IsApproved,
168 @STG_EmployeeNumber,
169 @STG_EmployeeName,
170 @STG_TransactionDate,
171 @STG_RegularHours,
172 @STG_OtHours,
173 @STG_DtHours,
174 @STG_TotalHours,
175 @STG_RegularCost,
176 @STG_OtCost,
177 @STG_DtCost,
178 @STG_TotalCost,
179 @STG_Reference_1,
180 @STG_Reference_2,
181 @STG_DetailSequence,
182 @STG_FileDate,
183 @STG_FileTime,
184 @STG_IsValid, --
185 @STG_ProjectID,
186 @STG_ControlBudgetID,
187 @STG_PayrollTypeID,
188 @STG_ProjectPhaseID,
189 @STG_WBSID,
190 @STG_DisciplineID,
191 @STG_ActivityTypeID,
192 @STG_CostTypeID,
193 @STG_BillingCodeID,
194 @STG_BillingGroupID,
195 @STG_ManualRevenue,
196 @STG_IsBillingReferenceInherited,
197 @STG_PaymentDate
198 WHILE @@FETCH_STATUS = 0
199 BEGIN
200 BEGIN TRY
201 BEGIN TRANSACTION
202 SET @PayrollEventID = NULL
203 SET @v_IsValid = 0
204 SET @v_LastInserted = 0
205 SET @v_UserID = NULL
206 SET @v_BudgetIsCommitAsExpended = 0
207 SET @v_TotalExpended = 0
208 SET @v_oldTotalCost = NULL;
209 SET @v_FileDate = NULL;
210 SET @automaticallyExtendProject = 0;
211 SET @ProjectEndDate = NULL;
212 SET @ProjectStartDate = NULL;
213 SET @v_FileDate = NULL;
214 SET @automaticallyExtendProjectStartDate = 0;
215
216 SET @v_FileDate = CONVERT(datetime, @STG_FileDate, 12) + CONVERT(datetime, STUFF(@STG_FileTime, 3, 0,':') + ':00', 8)
217
218 --- Start of Employee name block.
219 ---
220 --- NEW - I don't think we need all the below regarding adding new Employees.
221 --- We have stand-alone Employee import script.
222 ---
223
224 IF (@STG_Type = 'PR')
225 BEGIN
226 -- Find Employee by number
227 SELECT @v_UserID = UserID
228 FROM Users
229 WHERE ClientID = @ClientID
230 AND EmployeeNumber = @STG_EmployeeNumber
231 AND IsActive = 1
232 AND PersonType = 2
233
234 IF (@v_UserID IS NOT NULL AND @v_UserID <> 0)
235 BEGIN
236 IF (@STG_EmployeeName IS NOT NULL AND @STG_EmployeeName <> (SELECT DisplayName FROM Users WHERE UserID = @v_UserID))
237 BEGIN
238 -- If the name is different, update it
239 UPDATE Users
240 SET DisplayName = @STG_EmployeeName
241 WHERE UserID = @v_UserID
242 END
243 END
244 ELSE IF (@STG_EmployeeNumber IS NOT NULL AND @STG_EmployeeName IS NOT NULL)
245 BEGIN
246 -- If employee not found, create it
247 INSERT INTO Users (
248 EmployeeNumber,
249 DisplayName,
250 ClientID,
251 IsActive,
252 PersonType
253 )
254 VALUES (
255 @STG_EmployeeNumber,
256 @STG_EmployeeName,
257 @ClientID,
258 1,
259 2
260 )
261
262 SET @v_UserID = @@IDENTITY
263 END
264 END
265 ------
266
267
268 ELSE IF (@STG_Type = 'JC')
269 BEGIN
270 -- Find Employee by number
271 SELECT @v_UserID = UserID
272 FROM Users
273 WHERE ClientID = @ClientID
274 AND EmployeeNumber = @STG_Reference_1
275 AND IsActive = 1
276 AND PersonType = 2
277 END
278
279 ---- End of Employee Name block
280
281 --- Start of Project Dates block
282 ---
283 --- NEW Moved to its own script "4"
284
285
286 SELECT @ProjectEndDate = ProjectEndDate,
287 @ProjectStartDate = ProjectStartDate,
288 @automaticallyExtendProjectStartDate = AutomaticallyExtendStartDate,
289 @automaticallyExtendProject = AutomaticallyExtendEndDate
290 FROM Projects
291 WHERE ProjectID = @STG_ProjectID
292
293 DECLARE @IsExtendProject BIT = 0;
294 IF(@STG_TransactionDate IS NOT NULL AND @STG_TransactionDate > @ProjectEndDate AND @automaticallyExtendProject = 1)
295 BEGIN
296 SET @IsExtendProject = 1;
297
298 END ELSE IF @STG_TransactionDate > @ProjectEndDate
299 BEGIN
300 RAISERROR('The transaction date is after the end of the project', 11, 1) WITH NOWAIT;
301 END
302
303 IF(@STG_TransactionDate IS NOT NULL AND @STG_TransactionDate < @ProjectStartDate AND @automaticallyExtendProjectStartDate = 1)
304 BEGIN
305 SET @IsExtendProject = 1;
306
307 END ELSE IF @STG_TransactionDate < @ProjectStartDate
308 BEGIN
309 RAISERROR('The transaction date is prior to project start date', 11, 1) WITH NOWAIT;
310 END
311
312 IF(@IsExtendProject = 1)
313 BEGIN
314 EXEC p_extend_project_date @STG_ProjectID, @STG_TransactionDate;
315 END
316
317 --- End of Project Dates Block.
318
319
320
321 -- Start of commit as expended block
322 --- NEW We don't need to do anyting with Commit as Expend at all.
323
324 SELECT @v_BudgetIsCommitAsExpended = IsCommitAsExpended
325 FROM ControlBudgets
326 WHERE ControlBudgetID = @STG_ControlBudgetID
327
328 --- End Commit as Expend block
329
330
331 --- Start find Staged Payroll records that already exist in Expenditures by ID block
332 --- NEW this processs is done in 5.2
333 SELECT @PayrollEventID = ExpenditureID
334 FROM Expenditures
335 WHERE ProjectID = @STG_ProjectID
336 AND PayrollTypeID IS NOT NULL
337 AND ControlBudgetID = @STG_ControlBudgetID
338 AND (UserID = @v_UserID OR (UserID IS NULL AND @v_UserID IS NULL))
339 AND (PayrollTypeID = @STG_PayrollTypeID OR (PayrollTypeID IS NULL AND @STG_PayrollTypeID IS NULL))
340 AND ExpendedDate = @STG_TransactionDate
341 AND (UserDefined1 = @STG_Reference_1 OR (UserDefined1 IS NULL AND @STG_Reference_1 IS NULL))
342 AND (UserDefined2 = @STG_Reference_2 OR (UserDefined2 IS NULL AND @STG_Reference_2 IS NULL))
343 AND (UserDefined3 = @STG_DetailSequence OR (UserDefined3 IS NULL AND @STG_DetailSequence IS NULL))
344 AND IsActive = 1
345
346 --- End find Staged Payroll records that already exist in Expenditures by ID block
347
348 --- Start Insert new Expenditures block
349 --- NEW This is done in 5.2
350
351 IF (@PayrollEventID IS NULL)
352 BEGIN
353
354 INSERT INTO Expenditures(
355 ControlBudgetID,--1
356 IsActive,--2
357 UserDefined1,--3
358 UserDefined2,--4
359 UserDefined3,--5
360 Hours,--6
361 IsApproved,--7
362 Cost,--8
363 ExpendedDate,--9
364 UserID,--10
365 PayrollTypeID,--11
366 ProjectID,--12
367 VendorID,--13
368 ProjectWeekID,--14
369 BillingCodeID,--15
370 BillingGroupID,--16
371 Revenue,--17
372 PaymentDate--18
373 )
374 VALUES(
375 @STG_ControlBudgetID,--1
376 1,--2
377 @STG_Reference_1,--3
378 @STG_Reference_2,--4
379 @STG_DetailSequence,--5
380 ISNULL(@STG_TotalHours, 0),--6
381 @STG_IsApproved,--7
382 @STG_TotalCost,--8
383 @STG_TransactionDate,--9
384 @v_UserID,--10
385 @STG_PayrollTypeID,--11
386 @STG_ProjectID,--12
387 null,--13 Do we support vendor import?
388 dbo.GetProjectWeekID(@STG_ProjectID,@STG_TransactionDate),--14
389 @STG_BillingCodeID,--15
390 @STG_BillingGroupID,--16
391 @STG_ManualRevenue,--17
392 @STG_PaymentDate--18
393 )
394 END
395 -- END Insert new Expenditures block
396 ELSE
397
398 -- Start update the payroll values (hours and expended) block
399 -- New I don't think we need this, as we treat any non-matched payroll as a "new" record and delete the old non-matched one.
400 BEGIN
401 UPDATE Expenditures
402 SET
403 [Hours] = @STG_TotalHours,
404 [Cost] = @STG_TotalCost,
405 [BillingCodeID] = @STG_BillingCodeID,
406 [BillingGroupID] = @STG_BillingGroupID,
407 [Revenue] = @STG_ManualRevenue
408 WHERE ExpenditureID = @PayrollEventID
409 -- Start update the payroll values (hours and expended) block
410
411 END -- End payroll record exists
412
413
414 COMMIT TRANSACTION
415
416 RAISERROR('IMPORT_PROGRESS_NOTIFY:ADDED', 0, 1) WITH NOWAIT;
417 END TRY
418 BEGIN CATCH
419 ROLLBACK TRANSACTION
420
421 UPDATE dbo.STG_Payrolls
422 SET
423 [ErrorCodeID] = 66666,
424 ErrorMessage = FORMATMESSAGE('An error occured. Error number: %d, %s. ', ERROR_NUMBER(), ERROR_MESSAGE()),
425 IsValid = 0
426 WHERE ID = @STG_ID
427 RAISERROR('IMPORT_PROGRESS_NOTIFY:ROW_ERROR',0,1) WITH NOWAIT;
428 END CATCH
429
430 FETCH NEXT FROM @MyCursor
431 INTO @STG_ID,
432 @STG_Type,
433 @STG_JobNumber,
434 @STG_PhaseCode,
435 @STG_WbsCode,
436 @STG_DisciplineCode,
437 @STG_ActivityCode,
438 @STG_CostTypeCode,
439 @STG_IsProgressPerformance,
440 @STG_IsCommitAsExpended,
441 @STG_IsReserveAccount,
442 @STG_IsApproved,
443 @STG_EmployeeNumber,
444 @STG_EmployeeName,
445 @STG_TransactionDate,
446 @STG_RegularHours,
447 @STG_OtHours,
448 @STG_DtHours,
449 @STG_TotalHours,
450 @STG_RegularCost,
451 @STG_OtCost,
452 @STG_DtCost,
453 @STG_TotalCost,
454 @STG_Reference_1,
455 @STG_Reference_2,
456 @STG_DetailSequence,
457 @STG_FileDate,
458 @STG_FileTime,
459 @STG_IsValid, --
460 @STG_ProjectID,
461 @STG_ControlBudgetID,
462 @STG_PayrollTypeID,
463 @STG_ProjectPhaseID,
464 @STG_WBSID,
465 @STG_DisciplineID,
466 @STG_ActivityTypeID,
467 @STG_CostTypeID,
468 @STG_BillingCodeID,
469 @STG_BillingGroupID,
470 @STG_ManualRevenue,
471 @STG_IsBillingReferenceInherited,
472 @STG_PaymentDate
473 END
474
475 CLOSE @MyCursor
476 DEALLOCATE @MyCursor
477 RAISERROR('IMPORT_PROGRESS_NOTIFY:COMPLETED',0,1) WITH NOWAIT;
478END