· 5 years ago · Jul 26, 2020, 05:04 AM
1ALTER PROCEDURE rpt.usp_PayrollEntry_Sykes_reportTRDX
2(
3 @payoutUID VARCHAR(36)
4)
5AS
6BEGIN
7 --DECLARE @payoutUID UNIQUEIDENTIFIER = '7DB5D3F0-429E-4D21-8DAD-937797450CD4';
8
9 --Filtering Starts here
10 SELECT 'Normal' AS [ReportType];
11 /***================================*/
12 DECLARE @reportDetail TABLE
13 (
14 ReportName NVARCHAR(MAX),
15 ReportDescription NVARCHAR(MAX),
16 ReportSubDescription NVARCHAR(MAX)
17 ); --report header
18 DECLARE @reportHeaders TABLE
19 (
20 Title NVARCHAR(255) NOT NULL,
21 DataType NVARCHAR(255) NULL, -- SQL DataType (NVARCHAR(n), DECIMAL(18,4), etc..)
22 Width INT
23 DEFAULT (100), -- in pixel
24 AggregateFn VARCHAR(15) NULL
25 ); -- report columns
26 DECLARE @reportFooter TABLE
27 (
28 DataKey NVARCHAR(MAX),
29 DataValue NVARCHAR(MAX),
30 PosX INT,
31 PosY FLOAT
32 );
33
34
35 DECLARE @clientName VARCHAR(MAX),
36 @payoutid BIGINT;
37 DECLARE @clientID INT;
38 DECLARE @payperiod VARCHAR(100);
39 DECLARE @payoutRemarks VARCHAR(200);
40
41
42 SELECT @payoutid = PayoutID,
43 @payperiod = CONVERT(VARCHAR(20), PeriodStart, 107) + ' to ' + CONVERT(VARCHAR(20), PeriodEnd, 107),
44 @payoutRemarks = Remarks
45 FROM dbo.tbl_Payout
46 WHERE PayoutUID = @payoutUID;
47
48 SELECT @clientID = TC.ClientID,
49 @clientName = REPLACE(TC.ClientName, '&', '&')
50 FROM dbo.tbl_Payout AS TP
51 INNER JOIN dbo.tbl_Client AS TC
52 ON TC.ClientID = TP.ClientID
53 WHERE PayoutID = @payoutid;
54
55
56 INSERT INTO @reportDetail
57 VALUES
58 (@clientName, 'Sykes Payroll Entry - ' + @payoutRemarks, @payperiod);
59 SELECT RD.ReportName,
60 RD.ReportDescription,
61 RD.ReportSubDescription
62 FROM @reportDetail AS RD;
63
64 IF OBJECT_ID('tempdb..#tmpTblCr') IS NOT NULL
65 BEGIN
66 DROP TABLE #tmpTblCr;
67 END;
68
69 CREATE TABLE #tmpTblCr
70 (
71 CompanyCode NVARCHAR(55),
72 SiteCode NVARCHAR(55),
73 DeptCode NVARCHAR(55),
74 DeptName NVARCHAR(55),
75 Acct NVARCHAR(55),
76 Sub NVARCHAR(55),
77 Client NVARCHAR(55),
78 Debit DECIMAL(18, 5),
79 Credit DECIMAL(18, 5),
80 LineDesc NVARCHAR(255),
81 SiteName NVARCHAR(50),
82 PayrollName NVARCHAR(50),
83 OrderKey INT,
84 Type INT
85 );
86 IF OBJECT_ID('tempdb..#tmpTblDr') IS NOT NULL
87 BEGIN
88 DROP TABLE #tmpTblDr;
89 END;
90
91 CREATE TABLE #tmpTblDr
92 (
93 CompanyCode NVARCHAR(55),
94 SiteCode NVARCHAR(55),
95 DeptCode NVARCHAR(55),
96 DeptName NVARCHAR(55),
97 Acct NVARCHAR(55),
98 Sub NVARCHAR(55),
99 Client NVARCHAR(55),
100 Debit DECIMAL(18, 5),
101 Credit DECIMAL(18, 5),
102 LineDesc NVARCHAR(255),
103 SiteName NVARCHAR(55),
104 PayrollName NVARCHAR(55),
105 OrderKey INT,
106 Type INT
107 );
108 IF OBJECT_ID('tempdb..#tmpTblPayrollEntry') IS NOT NULL
109 BEGIN
110 DROP TABLE #tmpTblPayrollEntry;
111 END;
112 CREATE TABLE #tmpTblPayrollEntry
113 (
114 CompanyCode NVARCHAR(55),
115 SiteCode NVARCHAR(55),
116 DeptCode NVARCHAR(55),
117 DeptName NVARCHAR(55),
118 Acct NVARCHAR(55),
119 Sub NVARCHAR(55),
120 Client NVARCHAR(55),
121 Debit DECIMAL(18, 5),
122 Credit DECIMAL(18, 5),
123 LineDesc NVARCHAR(255),
124 SiteName NVARCHAR(55),
125 PayrollName NVARCHAR(55),
126 Type INT
127 );
128
129 BEGIN
130 --DECLARE @payoutID BIGINT;
131 SELECT @payoutid = PayoutID
132 FROM dbo.tbl_Payout AS TP
133 WHERE PayoutUID = @payoutUID;
134
135 --Payroll column
136 BEGIN
137 INSERT INTO #tmpTblPayrollEntry
138 SELECT VSDWCAS.Segment1 [Co],
139 VSDWCAS.Segment2 [Site],
140 IIF(
141 IIF(CHARINDEX('-', TAC.AcctName) = 0,
142 '',
143 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
144 '990',
145 VSDWCAS.Segment3) [Dept],
146 VSDWCAS.DepartmentName [DeptName],
147 --IIF(
148 -- IIF(CHARINDEX('-', TAC.AcctName) = 0,
149 -- '',
150 -- SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
151 -- TAC.AcctNo,
152 -- CONCAT(ISNULL(VSDWCAS.Prefix, ''), TAC.AcctNo)) [Acct],
153 IIF(
154 IIF(CHARINDEX('-', TAC.AcctName) = 0,
155 '',
156 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
157 TAC.AcctNo,
158 CONCAT(VSDWCAS.Prefix,
159 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
160 CASE
161 WHEN VSDWCAS.Prefix = '5' THEN
162 SUBSTRING(TAC.AcctNo,0,CHARINDEX(IIF(CHARINDEX('/', TAC.AcctNo) = 0, '-', '/'),TAC.AcctNo,0))
163 WHEN VSDWCAS.Prefix = '6' THEN
164 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
165 TAC.AcctNo,
166 CHARINDEX('/', TAC.AcctNo) + 1,
167 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
168 ))
169 WHEN VSDWCAS.Prefix = '7' THEN
170 SUBSTRING(
171 TAC.AcctNo,
172 CHARINDEX('-', TAC.AcctNo) + 1,
173 IIF(CHARINDEX('/', TAC.AcctNo) = 0, CHARINDEX('-', TAC.AcctNo), CHARINDEX('/', TAC.AcctNo)) - CHARINDEX('.', TAC.AcctNo) - 1)
174 ELSE
175 TSAC.SubAcctChartCode
176 END
177 ))) [Acct],
178 IIF(
179 IIF(CHARINDEX('-', TAC.AcctName) = 0,
180 '',
181 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
182 TSAC.SubAcctChartCode,
183 CASE
184 WHEN VSDWCAS.Prefix = '5' THEN
185 TSAC.SubAcctChartCode
186 WHEN VSDWCAS.Prefix = '6' THEN
187 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
188 TSAC.SubAcctChartDescription,
189 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
190 WHEN VSDWCAS.Prefix = '7' THEN
191 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
192 TSAC.SubAcctChartDescription,
193 SUBSTRING(
194 TSAC.SubAcctChartDescription,
195 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
196 LEN(TSAC.SubAcctChartDescription) + 1
197 ))
198 ELSE
199 TSAC.SubAcctChartCode
200 END) [sub],
201 IIF(
202 --VSDWCAS.DeptType = 'OPS'
203 --AND
204 IIF(CHARINDEX('-', TAC.AcctName) = 0,
205 '',
206 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
207 VSDWCAS.Segment6,
208 '000000') [Client],
209 SUM( (IIF(
210 (
211 VCPCACM.AcctEntry = 1
212 AND (PC.Amount) > 0
213 )
214 OR
215 (
216 VCPCACM.AcctEntry = 2
217 AND (PC.Amount) < 0
218 ),
219 ((PC.Amount / 100 * VSDWCAS.AllocPercentage) * IIF(VCPCACM.AcctEntry = 2, -1, 1)),
220 NULL)
221 )
222 ) Debit,
223 SUM( IIF(
224 (
225 VCPCACM.AcctEntry = 2
226 AND (PC.Amount) > 0
227 )
228 OR
229 (
230 VCPCACM.AcctEntry = 1
231 AND (PC.Amount) < 0
232 ),
233 ((PC.Amount / 100 * VSDWCAS.AllocPercentage) * IIF(VCPCACM.AcctEntry = 1, -1, 1)),
234 NULL)
235 ) Credit,
236 CONCAT(PC.SiteName, ' ', VCPCACM.PayrollColumnName) [Line Description],
237 PC.SiteName,
238 VCPCACM.PayrollColumnName,
239 1
240 FROM
241 (
242 SELECT TTM.ClientID,
243 TTM.ClientEmpID,
244 TCE.DepartmentID,
245 TS.SiteID,
246 TS.SiteName,
247 TCE.PositionID,
248 CAST(NetPay AS DECIMAL(18, 5)) NetPay,
249 CAST(WitholdingTax AS DECIMAL(18, 5)) WitholdingTax,
250 CAST(Overtime AS DECIMAL(18, 5)) Overtime,
251 CAST(BasicPay AS DECIMAL(18, 5)) BasicPay
252 FROM trn.tbl_TranMain AS TTM
253 INNER JOIN dbo.tbl_Payout AS TP
254 ON TP.PayoutID = TTM.PayoutID
255 INNER JOIN c.tbl_ConfEmp AS TCE
256 ON TCE.ClientEmpID = TTM.ClientEmpID
257 LEFT JOIN cc.tbl_Site AS TS
258 ON TS.SiteID = TCE.SiteID
259 WHERE TP.PayoutID = @payoutid
260 ) AS TM UNPIVOT(Amount FOR PayrollColumn IN([NetPay], [WitholdingTax], [Overtime], [BasicPay]))PC
261 INNER JOIN cc.vw__CCPayrollColAcctChartMap AS VCPCACM
262 ON VCPCACM.ClientID = PC.ClientID
263 AND VCPCACM.PayrollColumnName = PC.PayrollColumn
264 LEFT JOIN cc.tbl_AcctChart AS TAC
265 ON TAC.AcctChartID = VCPCACM.AcctChartID
266 LEFT JOIN cc.tbl_SubAcctChart AS TSAC
267 ON TSAC.SubAcctChartID = VCPCACM.SubAcctChartID
268 INNER JOIN dbo.vw_segmentDetails_wCostAlloc_Sykes AS VSDWCAS
269 ON VSDWCAS.ClientEmpID = PC.ClientEmpID
270 GROUP BY VSDWCAS.Segment1,
271 VSDWCAS.Segment2,
272 IIF(
273 IIF(CHARINDEX('-', TAC.AcctName) = 0,
274 '',
275 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
276 '990',
277 VSDWCAS.Segment3),
278 VSDWCAS.DepartmentName,
279 IIF(
280 IIF(CHARINDEX('-', TAC.AcctName) = 0,
281 '',
282 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
283 TAC.AcctNo,
284 CONCAT(VSDWCAS.Prefix,
285 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
286 CASE
287 WHEN VSDWCAS.Prefix = '5' THEN
288 SUBSTRING(TAC.AcctNo,0,CHARINDEX(IIF(CHARINDEX('/', TAC.AcctNo) = 0, '-', '/'),TAC.AcctNo,0))
289 WHEN VSDWCAS.Prefix = '6' THEN
290 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
291 TAC.AcctNo,
292 CHARINDEX('/', TAC.AcctNo) + 1,
293 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
294 ))
295 WHEN VSDWCAS.Prefix = '7' THEN
296 SUBSTRING(
297 TAC.AcctNo,
298 CHARINDEX('-', TAC.AcctNo) + 1,
299 IIF(CHARINDEX('/', TAC.AcctNo) = 0, CHARINDEX('-', TAC.AcctNo), CHARINDEX('/', TAC.AcctNo)) - CHARINDEX('.', TAC.AcctNo) - 1)
300 ELSE
301 TSAC.SubAcctChartCode
302 END
303 ))),
304 IIF(
305 IIF(CHARINDEX('-', TAC.AcctName) = 0,
306 '',
307 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
308 TSAC.SubAcctChartCode,
309 CASE
310 WHEN VSDWCAS.Prefix = '5' THEN
311 TSAC.SubAcctChartCode
312 WHEN VSDWCAS.Prefix = '6' THEN
313 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
314 TSAC.SubAcctChartDescription,
315 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription)
316 - 1))
317 WHEN VSDWCAS.Prefix = '7' THEN
318 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
319 TSAC.SubAcctChartDescription,
320 SUBSTRING(
321 TSAC.SubAcctChartDescription,
322 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
323 LEN(TSAC.SubAcctChartDescription) + 1
324 ))
325 ELSE
326 TSAC.SubAcctChartCode
327 END),
328 IIF(
329 --VSDWCAS.DeptType = 'OPS'
330 --AND
331 IIF(CHARINDEX('-', TAC.AcctName) = 0,
332 '',
333 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
334 VSDWCAS.Segment6,
335 '000000'),
336 CONCAT(PC.SiteName, ' ', VCPCACM.PayrollColumnName),
337 PC.SiteName,
338 VCPCACM.PayrollColumnName;
339 END;
340
341 BEGIN
342 --Earnings
343
344 INSERT INTO #tmpTblPayrollEntry
345 SELECT VSDWCAS.Segment1 [Co],
346 VSDWCAS.Segment2 [Site],
347 IIF(
348 IIF(CHARINDEX('-', TAC.AcctName) = 0,
349 '',
350 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
351 '990',
352 VSDWCAS.Segment3) [Dept],
353 VSDWCAS.DepartmentName [DeptName],
354 --IIF(
355 -- IIF(CHARINDEX('-', TAC.AcctName) = 0,
356 -- '',
357 -- SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
358 -- TAC.AcctNo,
359 -- CONCAT(ISNULL(VSDWCAS.Prefix, ''), TAC.AcctNo)) [Acct],
360 IIF(
361 IIF(CHARINDEX('-', TAC.AcctName) = 0,
362 '',
363 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
364 TAC.AcctNo,
365 CONCAT(VSDWCAS.Prefix,
366 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
367 CASE
368 WHEN VSDWCAS.Prefix = '5' THEN
369 SUBSTRING(TAC.AcctNo,0,CHARINDEX(IIF(CHARINDEX('/', TAC.AcctNo) = 0, '-', '/'),TAC.AcctNo,0))
370 WHEN VSDWCAS.Prefix = '6' THEN
371 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
372 TAC.AcctNo,
373 CHARINDEX('/', TAC.AcctNo) + 1,
374 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
375 ))
376 WHEN VSDWCAS.Prefix = '7' THEN
377 SUBSTRING(
378 TAC.AcctNo,
379 CHARINDEX('-', TAC.AcctNo) + 1,
380 IIF(CHARINDEX('/', TAC.AcctNo) = 0, CHARINDEX('-', TAC.AcctNo), CHARINDEX('/', TAC.AcctNo)) - CHARINDEX('.', TAC.AcctNo) - 1)
381 ELSE
382 TSAC.SubAcctChartCode
383 END
384 ))) [Acct],
385 IIF(
386 IIF(CHARINDEX('-', TAC.AcctName) = 0,
387 '',
388 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
389 TSAC.SubAcctChartCode,
390 CASE
391 WHEN VSDWCAS.Prefix = '5' THEN
392 TSAC.SubAcctChartCode
393 WHEN VSDWCAS.Prefix = '6' THEN
394 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
395 TSAC.SubAcctChartDescription,
396 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
397 WHEN VSDWCAS.Prefix = '7' THEN
398 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
399 TSAC.SubAcctChartDescription,
400 SUBSTRING(
401 TSAC.SubAcctChartDescription,
402 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
403 LEN(TSAC.SubAcctChartDescription) + 1
404 ))
405 ELSE
406 TSAC.SubAcctChartCode
407 END) [sub],
408 IIF(
409 --VSDWCAS.DeptType = 'OPS'
410 --AND
411 IIF(CHARINDEX('-', TAC.AcctName) = 0,
412 '',
413 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
414 VSDWCAS.Segment6,
415 '000000') [Client],
416 IIF(SUM(TFED.Amount / 100 * VSDWCAS.AllocPercentage) > 0,
417 SUM(TFED.Amount / 100 * VSDWCAS.AllocPercentage),
418 NULL) Debit,
419 IIF(SUM(TFED.Amount / 100 * VSDWCAS.AllocPercentage) < 0,
420 SUM(TFED.Amount / 100 * VSDWCAS.AllocPercentage) * -1,
421 NULL) Credit,
422 CONCAT(TS.SiteName, ' ', TE.EarnName) [Line Description],
423 TS.SiteName,
424 TE.EarnName,
425 1
426 FROM trn.tbl_TranMain AS TTM
427 INNER JOIN dbo.tbl_Payout AS TP
428 ON TP.PayoutID = TTM.PayoutID
429 INNER JOIN trn.tbl_FTEarnDetails AS TFED
430 ON TFED.TranMainID = TTM.TranMainID
431 INNER JOIN cc.tbl_Earn AS TE
432 ON TE.EarnID = TFED.EarnID
433 LEFT JOIN cc.tbl_AcctChart AS TAC
434 ON TAC.AcctChartID = TE.AcctChartID
435 LEFT JOIN cc.tbl_AcctChartBrkdn AS TACB
436 ON TACB.AcctChartBrkdnID = TE.AcctChartBrkdnID
437 INNER JOIN c.tbl_ConfEmp AS TCE
438 ON TCE.ClientEmpID = TTM.ClientEmpID
439 LEFT JOIN cc.tbl_Site AS TS
440 ON TS.SiteID = TCE.SiteID
441 LEFT JOIN cc.tbl_SubAcctChart AS TSAC
442 ON TSAC.SubAcctChartID = TE.SubAcctChartID
443 INNER JOIN dbo.vw_segmentDetails_wCostAlloc_Sykes AS VSDWCAS
444 ON VSDWCAS.ClientEmpID = TTM.ClientEmpID
445 WHERE TP.PayoutID = @payoutid
446 GROUP BY VSDWCAS.Segment1,
447 VSDWCAS.Segment2,
448 IIF(
449 IIF(CHARINDEX('-', TAC.AcctName) = 0,
450 '',
451 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
452 '990',
453 VSDWCAS.Segment3),
454 VSDWCAS.DepartmentName,
455 IIF(
456 IIF(CHARINDEX('-', TAC.AcctName) = 0,
457 '',
458 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
459 TAC.AcctNo,
460 CONCAT(VSDWCAS.Prefix,
461 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
462 CASE
463 WHEN VSDWCAS.Prefix = '5' THEN
464 SUBSTRING(TAC.AcctNo,0,CHARINDEX(IIF(CHARINDEX('/', TAC.AcctNo) = 0, '-', '/'),TAC.AcctNo,0))
465 WHEN VSDWCAS.Prefix = '6' THEN
466 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
467 TAC.AcctNo,
468 CHARINDEX('/', TAC.AcctNo) + 1,
469 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
470 ))
471 WHEN VSDWCAS.Prefix = '7' THEN
472 SUBSTRING(
473 TAC.AcctNo,
474 CHARINDEX('-', TAC.AcctNo) + 1,
475 IIF(CHARINDEX('/', TAC.AcctNo) = 0, CHARINDEX('-', TAC.AcctNo), CHARINDEX('/', TAC.AcctNo)) - CHARINDEX('.', TAC.AcctNo) - 1)
476 ELSE
477 TSAC.SubAcctChartCode
478 END
479 ))),
480 IIF(
481 IIF(CHARINDEX('-', TAC.AcctName) = 0,
482 '',
483 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
484 TSAC.SubAcctChartCode,
485 CASE
486 WHEN VSDWCAS.Prefix = '5' THEN
487 TSAC.SubAcctChartCode
488 WHEN VSDWCAS.Prefix = '6' THEN
489 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
490 TSAC.SubAcctChartDescription,
491 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription)
492 - 1))
493 WHEN VSDWCAS.Prefix = '7' THEN
494 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
495 TSAC.SubAcctChartDescription,
496 SUBSTRING(
497 TSAC.SubAcctChartDescription,
498 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
499 LEN(TSAC.SubAcctChartDescription) + 1
500 ))
501 ELSE
502 TSAC.SubAcctChartCode
503 END),
504 IIF(
505 --VSDWCAS.DeptType = 'OPS'
506 --AND
507 IIF(CHARINDEX('-', TAC.AcctName) = 0,
508 '',
509 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
510 VSDWCAS.Segment6,
511 '000000'),
512 CONCAT(TS.SiteName, ' ', TE.EarnName),
513 TS.SiteName,
514 TE.EarnName;
515 END;
516
517 BEGIN
518 --Bonus TAXABLE
519
520 INSERT INTO #tmpTblPayrollEntry
521 SELECT VSDWCAS.Segment1 [Co],
522 VSDWCAS.Segment2 [Site],
523 IIF(
524 IIF(CHARINDEX('-', TAC.AcctName) = 0,
525 '',
526 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
527 '990',
528 VSDWCAS.Segment3) [Dept],
529 VSDWCAS.DepartmentName [DeptName],
530 IIF(IIF(CHARINDEX('-', TAC.AcctName) = 0,
531 '',
532 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
533 TAC.AcctNo,
534 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
535 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
536 CASE
537 WHEN VSDWCAS.Prefix = '5' THEN
538 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
539 WHEN VSDWCAS.Prefix = '6' THEN
540 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
541 TAC.AcctNo,
542 CHARINDEX('/', TAC.AcctNo) + 1,
543 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
544 ))
545 WHEN VSDWCAS.Prefix = '7' THEN
546 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
547 ELSE
548 TAC.AcctNo
549 END
550 ))) [Acct],
551 IIF(
552 IIF(CHARINDEX('-', TAC.AcctName) = 0,
553 '',
554 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
555 TSAC.SubAcctChartCode,
556 CASE
557 WHEN VSDWCAS.Prefix = '5' THEN
558 TSAC.SubAcctChartCode
559 WHEN VSDWCAS.Prefix = '6' THEN
560 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
561 TSAC.SubAcctChartDescription,
562 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
563 WHEN VSDWCAS.Prefix = '7' THEN
564 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
565 TSAC.SubAcctChartDescription,
566 SUBSTRING(
567 TSAC.SubAcctChartDescription,
568 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
569 LEN(TSAC.SubAcctChartDescription) + 1
570 ))
571 ELSE
572 TSAC.SubAcctChartCode
573 END) [sub],
574 IIF(
575 --VSDWCAS.DeptType = 'OPS'
576 --AND
577 IIF(CHARINDEX('-', TAC.AcctName) = 0,
578 '',
579 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
580 VSDWCAS.Segment6,
581 '000000') [Client],
582 SUM(IIF(TFED.TaxableBonus > 0, TFED.TaxableBonus / 100 * VSDWCAS.AllocPercentage, NULL)) Debit,
583 SUM(IIF(TFED.TaxableBonus < 0, TFED.TaxableBonus / 100 * VSDWCAS.AllocPercentage * -1, NULL)) Credit,
584 CONCAT(TS.SiteName, ' ', CONCAT(TFED.Bonus_BonusTypeName, '( T)')) [Line Description],
585 --CONCAT(TFED.Bonus_BonusTypeName, '( T)') [Line Description],
586 TS.SiteName,
587 CONCAT(TFED.Bonus_BonusTypeName, '( T)'),
588 1
589 FROM trn.tbl_TranMain AS TTM
590 INNER JOIN dbo.tbl_Payout AS TP
591 ON TP.PayoutID = TTM.PayoutID
592 INNER JOIN trn.tbl_FTBonusDetails TFED
593 ON TFED.TranMainID = TTM.TranMainID
594 INNER JOIN cc.tbl_Bonus Te
595 ON Te.BonusID = TFED.BonusID
596 LEFT JOIN cc.tbl_AcctChart AS TAC
597 ON TAC.AcctChartID = Te.AcctChartID
598 LEFT JOIN cc.tbl_AcctChartBrkdn AS TACB
599 ON TACB.AcctChartBrkdnID = Te.AcctChartBrkdnID
600 INNER JOIN c.tbl_ConfEmp AS TCE
601 ON TCE.ClientEmpID = TTM.ClientEmpID
602 LEFT JOIN cc.tbl_Site AS TS
603 ON TS.SiteID = TCE.SiteID
604 LEFT JOIN cc.tbl_SubAcctChart AS TSAC
605 ON TSAC.AcctChartID = Te.AcctChartID
606 INNER JOIN dbo.vw_segmentDetails_wCostAlloc_Sykes AS VSDWCAS
607 ON VSDWCAS.ClientEmpID = TTM.ClientEmpID
608 WHERE TP.PayoutID = @payoutid
609 GROUP BY VSDWCAS.Segment1,
610 VSDWCAS.Segment2,
611 IIF(
612 IIF(CHARINDEX('-', TAC.AcctName) = 0,
613 '',
614 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
615 '990',
616 VSDWCAS.Segment3),
617 VSDWCAS.DepartmentName,
618 IIF(
619 IIF(CHARINDEX('-', TAC.AcctName) = 0,
620 '',
621 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
622 TAC.AcctNo,
623 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
624 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
625 CASE
626 WHEN VSDWCAS.Prefix = '5' THEN
627 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
628 WHEN VSDWCAS.Prefix = '6' THEN
629 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
630 TAC.AcctNo,
631 CHARINDEX('/', TAC.AcctNo) + 1,
632 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
633 ))
634 WHEN VSDWCAS.Prefix = '7' THEN
635 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
636 ELSE
637 TAC.AcctNo
638 END
639 ))),
640 IIF(
641 IIF(CHARINDEX('-', TAC.AcctName) = 0,
642 '',
643 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
644 TSAC.SubAcctChartCode,
645 CASE
646 WHEN VSDWCAS.Prefix = '5' THEN
647 TSAC.SubAcctChartCode
648 WHEN VSDWCAS.Prefix = '6' THEN
649 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
650 TSAC.SubAcctChartDescription,
651 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription)
652 - 1))
653 WHEN VSDWCAS.Prefix = '7' THEN
654 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
655 TSAC.SubAcctChartDescription,
656 SUBSTRING(
657 TSAC.SubAcctChartDescription,
658 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
659 LEN(TSAC.SubAcctChartDescription) + 1
660 ))
661 ELSE
662 TSAC.SubAcctChartCode
663 END),
664 IIF(
665 --VSDWCAS.DeptType = 'OPS'
666 --AND
667 IIF(CHARINDEX('-', TAC.AcctName) = 0,
668 '',
669 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
670 VSDWCAS.Segment6,
671 '000000'),
672 CONCAT(TS.SiteName, ' ', CONCAT(TFED.Bonus_BonusTypeName, '( T)')),
673 TS.SiteName,
674 CONCAT(TFED.Bonus_BonusTypeName, '( T)');
675 END;
676
677 BEGIN
678 --Bonus NON TAXABLE
679
680 INSERT INTO #tmpTblPayrollEntry
681 SELECT VSDWCAS.Segment1 [Co],
682 VSDWCAS.Segment2 [Site],
683 IIF(
684 IIF(CHARINDEX('-', TAC.AcctName) = 0,
685 '',
686 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
687 '990',
688 VSDWCAS.Segment3) [Dept],
689 VSDWCAS.DepartmentName [DeptName],
690 IIF(
691 IIF(CHARINDEX('-', TAC.AcctName) = 0,
692 '',
693 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
694 TAC.AcctNo,
695 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
696 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
697 CASE
698 WHEN VSDWCAS.Prefix = '5' THEN
699 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
700 WHEN VSDWCAS.Prefix = '6' THEN
701 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
702 TAC.AcctNo,
703 CHARINDEX('/', TAC.AcctNo) + 1,
704 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
705 ))
706 WHEN VSDWCAS.Prefix = '7' THEN
707 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
708 ELSE
709 TAC.AcctNo
710 END
711 ))) [Acct],
712 IIF(
713 IIF(CHARINDEX('-', TAC.AcctName) = 0,
714 '',
715 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
716 TSAC.SubAcctChartCode,
717 CASE
718 WHEN VSDWCAS.Prefix = '5' THEN
719 TSAC.SubAcctChartCode
720 WHEN VSDWCAS.Prefix = '6' THEN
721 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
722 TSAC.SubAcctChartDescription,
723 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
724 WHEN VSDWCAS.Prefix = '7' THEN
725 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
726 TSAC.SubAcctChartDescription,
727 SUBSTRING(
728 TSAC.SubAcctChartDescription,
729 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
730 LEN(TSAC.SubAcctChartDescription) + 1
731 ))
732 ELSE
733 TSAC.SubAcctChartCode
734 END) [sub],
735 IIF(
736 --VSDWCAS.DeptType = 'OPS'
737 --AND
738 IIF(CHARINDEX('-', TAC.AcctName) = 0,
739 '',
740 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
741 VSDWCAS.Segment6,
742 '000000') [Client],
743 SUM(IIF(TFED.NonTaxableBonus > 0, TFED.NonTaxableBonus / 100 * VSDWCAS.AllocPercentage, NULL)) Debit,
744 SUM(IIF(TFED.NonTaxableBonus < 0, TFED.NonTaxableBonus / 100 * VSDWCAS.AllocPercentage * -1, NULL)) Credit,
745 CONCAT(TS.SiteName, ' ', CONCAT(TFED.Bonus_BonusTypeName, '( NT)')) [Line Description],
746 TS.SiteName,
747 CONCAT(TFED.Bonus_BonusTypeName, '( NT)'),
748 1
749 FROM trn.tbl_TranMain AS TTM
750 INNER JOIN dbo.tbl_Payout AS TP
751 ON TP.PayoutID = TTM.PayoutID
752 INNER JOIN trn.tbl_FTBonusDetails TFED
753 ON TFED.TranMainID = TTM.TranMainID
754 INNER JOIN cc.tbl_Bonus Te
755 ON Te.BonusID = TFED.BonusID
756 LEFT JOIN cc.tbl_AcctChart AS TAC
757 ON TAC.AcctChartID = Te.AcctChartID
758 LEFT JOIN cc.tbl_AcctChartBrkdn AS TACB
759 ON TACB.AcctChartBrkdnID = Te.AcctChartBrkdnID
760 INNER JOIN c.tbl_ConfEmp AS TCE
761 ON TCE.ClientEmpID = TTM.ClientEmpID
762 LEFT JOIN cc.tbl_Site AS TS
763 ON TS.SiteID = TCE.SiteID
764 LEFT JOIN cc.tbl_SubAcctChart AS TSAC
765 ON TSAC.AcctChartID = Te.AcctChartID
766 INNER JOIN dbo.vw_segmentDetails_wCostAlloc_Sykes AS VSDWCAS
767 ON VSDWCAS.ClientEmpID = TTM.ClientEmpID
768 WHERE TP.PayoutID = @payoutid
769 GROUP BY VSDWCAS.Segment1,
770 VSDWCAS.Segment2,
771 IIF(
772 IIF(CHARINDEX('-', TAC.AcctName) = 0,
773 '',
774 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
775 '990',
776 VSDWCAS.Segment3),
777 VSDWCAS.DepartmentName,
778 IIF(
779 IIF(CHARINDEX('-', TAC.AcctName) = 0,
780 '',
781 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
782 TAC.AcctNo,
783 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
784 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
785 CASE
786 WHEN VSDWCAS.Prefix = '5' THEN
787 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
788 WHEN VSDWCAS.Prefix = '6' THEN
789 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
790 TAC.AcctNo,
791 CHARINDEX('/', TAC.AcctNo) + 1,
792 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
793 ))
794 WHEN VSDWCAS.Prefix = '7' THEN
795 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
796 ELSE
797 TAC.AcctNo
798 END
799 ))),
800 IIF(
801 IIF(CHARINDEX('-', TAC.AcctName) = 0,
802 '',
803 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
804 TSAC.SubAcctChartCode,
805 CASE
806 WHEN VSDWCAS.Prefix = '5' THEN
807 TSAC.SubAcctChartCode
808 WHEN VSDWCAS.Prefix = '6' THEN
809 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
810 TSAC.SubAcctChartDescription,
811 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription)
812 - 1))
813 WHEN VSDWCAS.Prefix = '7' THEN
814 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
815 TSAC.SubAcctChartDescription,
816 SUBSTRING(
817 TSAC.SubAcctChartDescription,
818 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
819 LEN(TSAC.SubAcctChartDescription) + 1
820 ))
821 ELSE
822 TSAC.SubAcctChartCode
823 END),
824 IIF(
825 --VSDWCAS.DeptType = 'OPS'
826 --AND
827 IIF(CHARINDEX('-', TAC.AcctName) = 0,
828 '',
829 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
830 VSDWCAS.Segment6,
831 '000000'),
832 CONCAT(TS.SiteName, ' ', CONCAT(TFED.Bonus_BonusTypeName, '( NT)')),
833 TS.SiteName,
834 CONCAT(TFED.Bonus_BonusTypeName, '( NT)');
835 END;
836
837 BEGIN
838 --Deductions
839
840 INSERT INTO #tmpTblPayrollEntry
841 SELECT VSDWCAS.Segment1 [Co],
842 VSDWCAS.Segment2 [Site],
843 IIF(
844 IIF(CHARINDEX('-', TAC.AcctName) = 0,
845 '',
846 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
847 '990',
848 VSDWCAS.Segment3) [Dept],
849 VSDWCAS.DepartmentName [DeptName],
850 IIF(IIF(CHARINDEX('-', TAC.AcctName) = 0,
851 '',
852 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
853 TAC.AcctNo,
854 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
855 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
856 CASE
857 WHEN VSDWCAS.Prefix = '5' THEN
858 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
859 WHEN VSDWCAS.Prefix = '6' THEN
860 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
861 TAC.AcctNo,
862 CHARINDEX('/', TAC.AcctNo) + 1,
863 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
864 ))
865 WHEN VSDWCAS.Prefix = '7' THEN
866 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
867 ELSE
868 TAC.AcctNo
869 END
870 ))) [Acct],
871 IIF(
872 IIF(CHARINDEX('-', TAC.AcctName) = 0,
873 '',
874 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
875 TSAC.SubAcctChartCode,
876 CASE
877 WHEN VSDWCAS.Prefix = '5' THEN
878 TSAC.SubAcctChartCode
879 WHEN VSDWCAS.Prefix = '6' THEN
880 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
881 TSAC.SubAcctChartDescription,
882 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
883 WHEN VSDWCAS.Prefix = '7' THEN
884 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
885 TSAC.SubAcctChartDescription,
886 SUBSTRING(
887 TSAC.SubAcctChartDescription,
888 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
889 LEN(TSAC.SubAcctChartDescription) + 1
890 ))
891 ELSE
892 TSAC.SubAcctChartCode
893 END) [sub],
894 IIF(
895 --VSDWCAS.DeptType = 'OPS'
896 --AND
897 IIF(CHARINDEX('-', TAC.AcctName) = 0,
898 '',
899 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
900 VSDWCAS.Segment6,
901 '000000') [Client],
902 IIF(SUM(TFDD.Amount / 100 * VSDWCAS.AllocPercentage) < 0,
903 SUM(TFDD.Amount / 100 * VSDWCAS.AllocPercentage) * -1,
904 NULL) Debit,
905 IIF(SUM(TFDD.Amount / 100 * VSDWCAS.AllocPercentage) > 0,
906 SUM(TFDD.Amount / 100 * VSDWCAS.AllocPercentage),
907 NULL) Credit,
908 CONCAT(TS.SiteName, ' ', TDed.DedName) [LineDesc],
909 TS.SiteName,
910 TDed.DedName,
911 1
912 FROM trn.tbl_TranMain AS TTM
913 INNER JOIN dbo.tbl_Payout AS TP
914 ON TP.PayoutID = TTM.PayoutID
915 INNER JOIN trn.tbl_FTDedDetails AS TFDD
916 ON TFDD.TranMainID = TTM.TranMainID
917 INNER JOIN cc.tbl_Ded AS TDed
918 ON TDed.DedID = TFDD.DedID
919 LEFT JOIN cc.tbl_AcctChart AS TAC
920 ON TAC.AcctChartID = TDed.AcctChartID
921 LEFT JOIN cc.tbl_AcctChartBrkdn AS TACB
922 ON TACB.AcctChartBrkdnID = TDed.AcctChartBrkdnID
923 INNER JOIN c.tbl_ConfEmp AS TCE
924 ON TCE.ClientEmpID = TTM.ClientEmpID
925 LEFT JOIN cc.tbl_Site AS TS
926 ON TS.SiteID = TCE.SiteID
927 LEFT JOIN cc.tbl_SubAcctChart AS TSAC
928 ON TSAC.SubAcctChartID = TDed.SubAcctChartID
929 INNER JOIN dbo.vw_segmentDetails_wCostAlloc_Sykes AS VSDWCAS
930 ON VSDWCAS.ClientEmpID = TTM.ClientEmpID
931 WHERE TP.PayoutID = @payoutid
932 GROUP BY VSDWCAS.Segment1,
933 VSDWCAS.Segment2,
934 IIF(
935 IIF(CHARINDEX('-', TAC.AcctName) = 0,
936 '',
937 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
938 '990',
939 VSDWCAS.Segment3),
940 VSDWCAS.DepartmentName,
941 IIF(IIF(CHARINDEX('-', TAC.AcctName) = 0,
942 '',
943 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
944 TAC.AcctNo,
945 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
946 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
947 CASE
948 WHEN VSDWCAS.Prefix = '5' THEN
949 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
950 WHEN VSDWCAS.Prefix = '6' THEN
951 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
952 TAC.AcctNo,
953 CHARINDEX('/', TAC.AcctNo) + 1,
954 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
955 ))
956 WHEN VSDWCAS.Prefix = '7' THEN
957 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
958 ELSE
959 TAC.AcctNo
960 END
961 ))),
962 IIF(
963 IIF(CHARINDEX('-', TAC.AcctName) = 0,
964 '',
965 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
966 TSAC.SubAcctChartCode,
967 CASE
968 WHEN VSDWCAS.Prefix = '5' THEN
969 TSAC.SubAcctChartCode
970 WHEN VSDWCAS.Prefix = '6' THEN
971 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
972 TSAC.SubAcctChartDescription,
973 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription)
974 - 1))
975 WHEN VSDWCAS.Prefix = '7' THEN
976 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
977 TSAC.SubAcctChartDescription,
978 SUBSTRING(
979 TSAC.SubAcctChartDescription,
980 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
981 LEN(TSAC.SubAcctChartDescription) + 1
982 ))
983 ELSE
984 TSAC.SubAcctChartCode
985 END),
986 IIF(
987 --VSDWCAS.DeptType = 'OPS'
988 --AND
989 IIF(CHARINDEX('-', TAC.AcctName) = 0,
990 '',
991 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
992 VSDWCAS.Segment6,
993 '000000'),
994 CONCAT(TS.SiteName, ' ', TDed.DedName),
995 TS.SiteName,
996 TDed.DedName;
997 END;
998
999 BEGIN
1000 --Loans
1001 INSERT INTO #tmpTblPayrollEntry
1002 SELECT VSDWCAS.Segment1 [Co],
1003 VSDWCAS.Segment2 [Site],
1004 IIF(
1005 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1006 '',
1007 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
1008 '990',
1009 VSDWCAS.Segment3) [Dept],
1010 VSDWCAS.DepartmentName [DeptName],
1011 --IIF(
1012 -- IIF(CHARINDEX('-', TAC.AcctName) = 0,
1013 -- '',
1014 -- SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
1015 -- TAC.AcctNo,
1016 -- CONCAT(ISNULL(VSDWCAS.Prefix, ''), TAC.AcctNo)) [Acct],
1017 IIF(IIF(CHARINDEX('-', TAC.AcctName) = 0,
1018 '',
1019 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
1020 TAC.AcctNo,
1021 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
1022 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
1023 CASE
1024 WHEN VSDWCAS.Prefix = '5' THEN
1025 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
1026 WHEN VSDWCAS.Prefix = '6' THEN
1027 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
1028 TAC.AcctNo,
1029 CHARINDEX('/', TAC.AcctNo) + 1,
1030 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
1031 ))
1032 WHEN VSDWCAS.Prefix = '7' THEN
1033 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
1034 ELSE
1035 TAC.AcctNo
1036 END
1037 ))) [Acct],
1038 IIF(
1039 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1040 '',
1041 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
1042 TSAC.SubAcctChartCode,
1043 CASE
1044 WHEN VSDWCAS.Prefix = '5' THEN
1045 TSAC.SubAcctChartCode
1046 WHEN VSDWCAS.Prefix = '6' THEN
1047 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1048 TSAC.SubAcctChartDescription,
1049 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
1050 WHEN VSDWCAS.Prefix = '7' THEN
1051 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1052 TSAC.SubAcctChartDescription,
1053 SUBSTRING(
1054 TSAC.SubAcctChartDescription,
1055 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
1056 LEN(TSAC.SubAcctChartDescription) + 1
1057 ))
1058 ELSE
1059 TSAC.SubAcctChartCode
1060 END) [sub],
1061 IIF(
1062 --VSDWCAS.DeptType = 'OPS'
1063 --AND
1064 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1065 '',
1066 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
1067 VSDWCAS.Segment6,
1068 '000000') [Client],
1069 IIF(SUM(TFLD.Amount / 100 * VSDWCAS.AllocPercentage) < 0,
1070 SUM(TFLD.Amount / 100 * VSDWCAS.AllocPercentage) * -1,
1071 NULL) Debit,
1072 IIF(SUM(TFLD.Amount / 100 * VSDWCAS.AllocPercentage) > 0,
1073 SUM(TFLD.Amount / 100 * VSDWCAS.AllocPercentage),
1074 NULL) Credit,
1075 CONCAT(TS.SiteName, ' ', TL.LoanName) [LineDesc],
1076 TS.SiteName,
1077 TL.LoanName,
1078 1
1079 FROM trn.tbl_TranMain AS TTM
1080 INNER JOIN dbo.tbl_Payout AS TP
1081 ON TP.PayoutID = TTM.PayoutID
1082 INNER JOIN trn.tbl_FTLoanDetails AS TFLD
1083 ON TFLD.TranMainID = TTM.TranMainID
1084 INNER JOIN ccg.tbl_Loan AS TL
1085 ON TL.LoanID = TFLD.EmpLoan_LoanID
1086 INNER JOIN dbo.tbl_LoanType AS TLT
1087 ON TLT.LoanTypeID = TL.LoanTypeID
1088 LEFT JOIN cc.tbl_AcctChart AS TAC
1089 ON TAC.AcctChartID = TL.AcctChartID
1090 LEFT JOIN cc.tbl_AcctChartBrkdn AS TACB
1091 ON TACB.AcctChartBrkdnID = TL.AcctChartBrkdnID
1092 LEFT JOIN cc.tbl_Site AS TS
1093 ON TS.SiteID = TTM.SiteID
1094 LEFT JOIN cc.tbl_SubAcctChart AS TSAC
1095 ON TSAC.SubAcctChartID = TL.SubAcctChartID
1096 INNER JOIN dbo.vw_segmentDetails_wCostAlloc_Sykes AS VSDWCAS
1097 ON VSDWCAS.ClientEmpID = TTM.ClientEmpID
1098 WHERE TP.PayoutID = @payoutid
1099 GROUP BY VSDWCAS.Segment1,
1100 VSDWCAS.Segment2,
1101 IIF(
1102 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1103 '',
1104 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
1105 '990',
1106 VSDWCAS.Segment3),
1107 VSDWCAS.DepartmentName,
1108 IIF(IIF(CHARINDEX('-', TAC.AcctName) = 0,
1109 '',
1110 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
1111 TAC.AcctNo,
1112 CONCAT(ISNULL(VSDWCAS.Prefix, ''),
1113 IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
1114 CASE
1115 WHEN VSDWCAS.Prefix = '5' THEN
1116 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
1117 WHEN VSDWCAS.Prefix = '6' THEN
1118 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
1119 TAC.AcctNo,
1120 CHARINDEX('/', TAC.AcctNo) + 1,
1121 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
1122 ))
1123 WHEN VSDWCAS.Prefix = '7' THEN
1124 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
1125 ELSE
1126 TAC.AcctNo
1127 END
1128 ))),
1129 IIF(
1130 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1131 '',
1132 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS',
1133 TSAC.SubAcctChartCode,
1134 CASE
1135 WHEN VSDWCAS.Prefix = '5' THEN
1136 TSAC.SubAcctChartCode
1137 WHEN VSDWCAS.Prefix = '6' THEN
1138 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1139 TSAC.SubAcctChartDescription,
1140 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription)
1141 - 1))
1142 WHEN VSDWCAS.Prefix = '7' THEN
1143 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1144 TSAC.SubAcctChartDescription,
1145 SUBSTRING(
1146 TSAC.SubAcctChartDescription,
1147 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
1148 LEN(TSAC.SubAcctChartDescription) + 1
1149 ))
1150 ELSE
1151 TSAC.SubAcctChartCode
1152 END),
1153 IIF(
1154 --VSDWCAS.DeptType = 'OPS'
1155 --AND
1156 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1157 '',
1158 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS',
1159 VSDWCAS.Segment6,
1160 '000000'),
1161 CONCAT(TS.SiteName, ' ', TL.LoanName),
1162 TS.SiteName,
1163 TL.LoanName;
1164 END;
1165
1166 BEGIN
1167 --STATUTORY
1168 INSERT INTO #tmpTblPayrollEntry
1169 SELECT VSDWCAS.Segment1 [Co],
1170 VSDWCAS.Segment2 [Site],
1171 IIF(
1172 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1173 '',
1174 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS'
1175 AND PT.isPayrollTax = 0,
1176 '990',
1177 VSDWCAS.Segment3) [Dept],
1178 VSDWCAS.DepartmentName [DeptName],
1179 --IIF(
1180 -- IIF(CHARINDEX('-', TAC.AcctName) = 0,
1181 -- '',
1182 -- SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS'
1183 -- AND PT.isPayrollTax = 0,
1184 -- TAC.AcctNo,
1185 -- IIF(PT.isPayrollTax = 1, CONCAT(VSDWCAS.Prefix, '0350'), CONCAT(ISNULL(VSDWCAS.Prefix, ''), TAC.AcctNo))) [Acct],
1186 IIF(
1187 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1188 '',
1189 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS'
1190 AND PT.isPayrollTax = 0,
1191 TAC.AcctNo,
1192 IIF(PT.isPayrollTax = 1, CONCAT(VSDWCAS.Prefix, '0350'), CONCAT(ISNULL(VSDWCAS.Prefix, ''), IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
1193 CASE
1194 WHEN VSDWCAS.Prefix = '5' THEN
1195 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
1196 WHEN VSDWCAS.Prefix = '6' THEN
1197 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
1198 TAC.AcctNo,
1199 CHARINDEX('/', TAC.AcctNo) + 1,
1200 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
1201 ))
1202 WHEN VSDWCAS.Prefix = '7' THEN
1203 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
1204 ELSE
1205 TAC.AcctNo
1206 END
1207 )))) [Acct],
1208 IIF(
1209 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1210 '',
1211 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS'
1212 AND PT.isPayrollTax = 0,
1213 TSAC.SubAcctChartCode,
1214 IIF(PT.isPayrollTax = 1, '0000',
1215 CASE
1216 WHEN VSDWCAS.Prefix = '5' THEN
1217 TSAC.SubAcctChartCode
1218 WHEN VSDWCAS.Prefix = '6' THEN
1219 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1220 TSAC.SubAcctChartDescription,
1221 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
1222 WHEN VSDWCAS.Prefix = '7' THEN
1223 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1224 TSAC.SubAcctChartDescription,
1225 SUBSTRING(
1226 TSAC.SubAcctChartDescription,
1227 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
1228 LEN(TSAC.SubAcctChartDescription) + 1
1229 ))
1230 ELSE
1231 TSAC.SubAcctChartCode
1232 END)) [sub],
1233 IIF(
1234 --VSDWCAS.DeptType = 'OPS'
1235 --AND
1236 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1237 '',
1238 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS'
1239 OR PT.isPayrollTax=1,
1240 --AND PT.isPayrollTax = 0
1241 VSDWCAS.Segment6,
1242 '000000') [Client],
1243 NULLIF(SUM(IIF((CA.Type IN ( 7, 8 ) AND PC.Amount > 0) OR (CA.Type NOT IN (7, 8) AND PC.Amount < 0)
1244 , IIF(PC.Amount < 0, PC.Amount * -1, PC.Amount) , NULL) / 100 * VSDWCAS.AllocPercentage) , 0) Debit,
1245 NULLIF(SUM(IIF((CA.Type IN ( 4, 5, 6 ) AND PC.Amount > 0) OR (CA.Type NOT IN ( 4, 5, 6 ) AND PC.Amount < 0)
1246 , IIF(PC.Amount < 0, PC.Amount * -1, PC.Amount) ,
1247 NULL)
1248 / 100 * VSDWCAS.AllocPercentage ), 0) Credit,
1249 CONCAT( PC.SiteName,
1250 ' ',
1251 CASE
1252 WHEN CA.Type = 6 THEN
1253 CONCAT(VCPCACM.PayrollColumnName, ' - EE Share')
1254 WHEN CA.Type = 5 THEN
1255 CONCAT(VCPCACM.PayrollColumnName, ' - ER Share')
1256 WHEN CA.Type = 4 THEN
1257 CONCAT(VCPCACM.PayrollColumnName, ' - ECC Share')
1258 WHEN CA.Type IN ( 7, 8 ) THEN
1259 CONCAT('Payroll Taxes - ', VCPCACM.PayrollColumnName)
1260 ELSE
1261 VCPCACM.PayrollColumnName
1262 END
1263 ) [LineDesc],
1264 PC.SiteName,
1265 CASE
1266 WHEN CA.Type = 6 THEN
1267 CONCAT(VCPCACM.PayrollColumnName, ' - EE Share')
1268 WHEN CA.Type = 5 THEN
1269 CONCAT(VCPCACM.PayrollColumnName, ' - ER Share')
1270 WHEN CA.Type = 4 THEN
1271 CONCAT(VCPCACM.PayrollColumnName, ' - ECC Share')
1272 WHEN CA.Type IN ( 7, 8 ) THEN
1273 CONCAT('Payroll Taxes - ', VCPCACM.PayrollColumnName)
1274 ELSE
1275 VCPCACM.PayrollColumnName
1276 END [PayrollColumnName],
1277 1
1278 FROM
1279 (
1280 SELECT TTM.ClientID,
1281 TTM.ClientEmpID,
1282 TCE.DepartmentID,
1283 TS.SiteID,
1284 TS.SiteName,
1285 TFGDD_TEMP.StatutoryTableKeyID,
1286 TFGDD_TEMP.StatutoryTableKeyName,
1287 CAST(TFGDD_TEMP.EYeeShare AS DECIMAL(18, 5)) Payable,
1288 CAST(TFGDD_TEMP.EYerShare AS DECIMAL(18, 5)) PayablePremium,
1289 CAST(ISNULL(TFGDD_TEMP.ExtraValue, 0) AS DECIMAL(18, 5)) EC,
1290 CAST(TFGDD_TEMP.EYerShare AS DECIMAL(18, 5)) Premium,
1291 CAST(ISNULL(TFGDD_TEMP.ExtraValue, 0) AS DECIMAL(18, 5)) PayableEC,
1292 TCE.PositionID,
1293 TTM.PositionCode
1294 FROM trn.tbl_TranMain AS TTM
1295 INNER JOIN c.tbl_ConfEmp AS TCE
1296 ON TCE.ClientEmpID = TTM.ClientEmpID
1297 LEFT JOIN cc.tbl_Site AS TS
1298 ON TS.SiteID = TTM.SiteID
1299 INNER JOIN dbo.tbl_Payout AS TP
1300 ON TP.PayoutID = TTM.PayoutID
1301 INNER JOIN trn.tbl_FTGovtDedDetails AS TFGDD_TEMP
1302 ON TFGDD_TEMP.TranMainID = TTM.TranMainID
1303 WHERE TP.PayoutID = @payoutid
1304 ) AS TFGDD UNPIVOT(Amount FOR PayrollColumnTemp IN( --[EYeeShare] , [EYerShare] , [ExtraValue]
1305 [Payable], [Premium], [EC], [PayablePremium], [PayableEC])) AS PC
1306 CROSS APPLY
1307 (
1308 SELECT CASE PC.PayrollColumnTemp
1309 WHEN 'Payable' THEN
1310 6
1311 WHEN 'Premium' THEN
1312 7
1313 WHEN 'PayablePremium' THEN
1314 5
1315 WHEN 'PayableEC' THEN
1316 4
1317 ELSE
1318 8
1319 END [Type]
1320 ) AS CA
1321 CROSS APPLY
1322 (
1323 SELECT CASE
1324 WHEN CA.Type IN ( 7, 8 ) THEN
1325 1
1326 ELSE
1327 0
1328 END [isPayrollTax]
1329 ) AS PT
1330 INNER JOIN cc.vw__CCPayrollColAcctChartMap AS VCPCACM
1331 ON VCPCACM.ClientID = PC.ClientID
1332 AND VCPCACM.PayrollColumnName = CONCAT(
1333 PC.StatutoryTableKeyName,
1334 ' ',
1335 CASE CA.Type
1336 WHEN 6 THEN
1337 'Premium Payable'
1338 WHEN 7 THEN
1339 'Employer Share'
1340 WHEN 5 THEN
1341 'Premium Payable'
1342 WHEN 4 THEN
1343 'EC'
1344 ELSE
1345 'EC'
1346 END
1347 )
1348 LEFT JOIN cc.tbl_AcctChart AS TAC
1349 ON TAC.AcctChartID = VCPCACM.AcctChartID
1350 LEFT JOIN cc.tbl_SubAcctChart AS TSAC
1351 ON TSAC.SubAcctChartID = VCPCACM.SubAcctChartID
1352 INNER JOIN dbo.vw_segmentDetails_wCostAlloc_Sykes AS VSDWCAS
1353 ON VSDWCAS.ClientEmpID = PC.ClientEmpID
1354 GROUP BY VSDWCAS.Segment1,
1355 VSDWCAS.Segment2,
1356 IIF(
1357 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1358 '',
1359 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS'
1360 AND PT.isPayrollTax = 0,
1361 '990',
1362 VSDWCAS.Segment3),
1363 VSDWCAS.DepartmentName,
1364 IIF(
1365 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1366 '',
1367 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS'
1368 AND PT.isPayrollTax = 0,
1369 TAC.AcctNo,
1370 IIF(PT.isPayrollTax = 1, CONCAT(VSDWCAS.Prefix, '0350'), CONCAT(ISNULL(VSDWCAS.Prefix, ''), IIF(CHARINDEX('/', TAC.AcctNo) = 0 AND CHARINDEX('-', TAC.AcctNo) = 0, TAC.AcctNo,
1371 CASE
1372 WHEN VSDWCAS.Prefix = '5' THEN
1373 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', LEFT(ISNULL(TAC.AcctNo, '/'), CHARINDEX('/', ISNULL(TAC.AcctNo, '/')) - 1))
1374 WHEN VSDWCAS.Prefix = '6' THEN
1375 IIF(CHARINDEX('/', TAC.AcctNo) = 0, '', SUBSTRING(
1376 TAC.AcctNo,
1377 CHARINDEX('/', TAC.AcctNo) + 1,
1378 IIF(CHARINDEX('-', TAC.AcctNo) = 0, CHARINDEX('/', TAC.AcctNo) - 1, CHARINDEX('-', TAC.AcctNo) - CHARINDEX('/', TAC.AcctNo) - 1)
1379 ))
1380 WHEN VSDWCAS.Prefix = '7' THEN
1381 IIF(CHARINDEX('-', TAC.AcctNo) = 0, '', SUBSTRING(TAC.AcctNo, CHARINDEX('-', TAC.AcctNo) + 1, LEN(TAC.AcctNo)))
1382 ELSE
1383 TAC.AcctNo
1384 END
1385 )))),
1386 IIF(
1387 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1388 '',
1389 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) = 'BS'
1390 AND PT.isPayrollTax = 0,
1391 TSAC.SubAcctChartCode,
1392 IIF(PT.isPayrollTax = 1, '0000',
1393 CASE
1394 WHEN VSDWCAS.Prefix = '5' THEN
1395 TSAC.SubAcctChartCode
1396 WHEN VSDWCAS.Prefix = '6' THEN
1397 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1398 TSAC.SubAcctChartDescription,
1399 LEFT(TSAC.SubAcctChartDescription, CHARINDEX('/', TSAC.SubAcctChartDescription) - 1))
1400 WHEN VSDWCAS.Prefix = '7' THEN
1401 IIF(CHARINDEX('/', TSAC.SubAcctChartDescription) = 0,
1402 TSAC.SubAcctChartDescription,
1403 SUBSTRING(
1404 TSAC.SubAcctChartDescription,
1405 CHARINDEX('/', TSAC.SubAcctChartDescription) + 1,
1406 LEN(TSAC.SubAcctChartDescription) + 1
1407 ))
1408 ELSE
1409 TSAC.SubAcctChartCode
1410 END)),
1411 IIF(
1412 --VSDWCAS.DeptType = 'OPS'
1413 --AND
1414 IIF(CHARINDEX('-', TAC.AcctName) = 0,
1415 '',
1416 SUBSTRING(TAC.AcctName, CHARINDEX('-', TAC.AcctName) + 1, LEN(TAC.AcctName))) != 'BS'
1417 OR PT.isPayrollTax = 1,
1418 --AND PT.isPayrollTax = 0,
1419 VSDWCAS.Segment6,
1420 '000000'),
1421 CONCAT( PC.SiteName,
1422 ' ',
1423 CASE
1424 WHEN CA.Type = 6 THEN
1425 CONCAT(VCPCACM.PayrollColumnName, ' - EE Share')
1426 WHEN CA.Type = 5 THEN
1427 CONCAT(VCPCACM.PayrollColumnName, ' - ER Share')
1428 WHEN CA.Type = 4 THEN
1429 CONCAT(VCPCACM.PayrollColumnName, ' - ECC Share')
1430 WHEN CA.Type IN ( 7, 8 ) THEN
1431 CONCAT('Payroll Taxes - ', VCPCACM.PayrollColumnName)
1432 ELSE
1433 VCPCACM.PayrollColumnName
1434 END
1435 ),
1436 PC.SiteName,
1437 CASE
1438 WHEN CA.Type = 6 THEN
1439 CONCAT(VCPCACM.PayrollColumnName, ' - EE Share')
1440 WHEN CA.Type = 5 THEN
1441 CONCAT(VCPCACM.PayrollColumnName, ' - ER Share')
1442 WHEN CA.Type = 4 THEN
1443 CONCAT(VCPCACM.PayrollColumnName, ' - ECC Share')
1444 WHEN CA.Type IN ( 7, 8 ) THEN
1445 CONCAT('Payroll Taxes - ', VCPCACM.PayrollColumnName)
1446 ELSE
1447 VCPCACM.PayrollColumnName
1448 END;
1449
1450
1451 END;
1452
1453 IF OBJECT_ID('tempdb..#tempList') IS NOT NULL
1454 /*Then it exists*/
1455 DROP TABLE #tempList;
1456
1457 CREATE TABLE #tempList
1458 (
1459 ID INT IDENTITY(1, 1) PRIMARY KEY,
1460 Name VARCHAR(50)
1461 );
1462
1463 INSERT INTO #tempList
1464 SELECT name
1465 FROM tempdb.sys.columns
1466 WHERE name IN ( 'CompanyCode' )
1467 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1468 INSERT INTO #tempList
1469 SELECT name
1470 FROM tempdb.sys.columns
1471 WHERE name IN ( 'SiteCode' )
1472 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1473
1474 INSERT INTO #tempList
1475 SELECT name
1476 FROM tempdb.sys.columns
1477 WHERE name IN ( 'DeptCode' )
1478 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1479 INSERT INTO #tempList
1480 SELECT name
1481 FROM tempdb.sys.columns
1482 WHERE name IN ( 'DeptName' )
1483 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1484 INSERT INTO #tempList
1485 SELECT name
1486 FROM tempdb.sys.columns
1487 WHERE name IN ( 'Acct' )
1488 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1489 INSERT INTO #tempList
1490 SELECT name
1491 FROM tempdb.sys.columns
1492 WHERE name IN ( 'Sub' )
1493 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1494 INSERT INTO #tempList
1495 SELECT name
1496 FROM tempdb.sys.columns
1497 WHERE name IN ( 'Client' )
1498 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1499 INSERT INTO #tempList
1500 SELECT name
1501 FROM tempdb.sys.columns
1502 WHERE name IN ( 'IC' )
1503 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1504 INSERT INTO #tempList
1505 SELECT name
1506 FROM tempdb.sys.columns
1507 WHERE name IN ( 'ICSite' )
1508 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1509 INSERT INTO #tempList
1510 SELECT name
1511 FROM tempdb.sys.columns
1512 WHERE name IN ( 'ProjCode' )
1513 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1514 INSERT INTO #tempList
1515 SELECT name
1516 FROM tempdb.sys.columns
1517 WHERE name IN ( 'Debit' )
1518 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1519 INSERT INTO #tempList
1520 SELECT name
1521 FROM tempdb.sys.columns
1522 WHERE name IN ( 'Credit' )
1523 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1524 INSERT INTO #tempList
1525 SELECT name
1526 FROM tempdb.sys.columns
1527 WHERE name IN ( 'LineDesc' )
1528 AND object_id = OBJECT_ID('tempdb..#tmpTblPayrollEntry');
1529
1530 SELECT Name Title,
1531 CASE
1532 WHEN Name IN ( 'CompanyCode', 'SiteCode', 'DeptCode', 'DeptName', 'Acct', 'Sub', 'Client', 'IC', 'ICSite',
1533 'ProjCode'
1534 ) THEN
1535 'VARCHAR(10)'
1536 WHEN Name IN ( 'Debit', 'Credit' ) THEN
1537 'DECIMAL(25, 5)'
1538 WHEN Name IN ( 'LineDesc' ) THEN
1539 'VARCHAR(255)'
1540 ELSE
1541 'VARCHAR(255)'
1542 END [datatype],
1543 CASE
1544 WHEN Name IN ( 'CompanyCode', 'SiteCode', 'DeptCode', 'DeptName', 'Acct', 'Sub', 'Client', 'IC', 'ICSite',
1545 'ProjCode'
1546 ) THEN
1547 75
1548 WHEN Name IN ( 'Debit', 'Credit' ) THEN
1549 150
1550 WHEN Name = 'LineDesc' THEN
1551 500
1552 ELSE
1553 100
1554 END width,
1555 CASE
1556 WHEN Name IN ( 'Debit', 'Credit' ) THEN
1557 'SUM'
1558 ELSE
1559 NULL
1560 END AggregateFn
1561 FROM #tempList
1562 GROUP BY ID,
1563 Name
1564 ORDER BY ID,
1565 Name;
1566
1567 UPDATE #tmpTblPayrollEntry
1568 SET Debit = IIF((ISNULL(Debit,0) - ISNULL(Credit,0)) > 0, ISNULL(Debit,0) - ISNULL(Credit,0), NULL),
1569 Credit = IIF((ISNULL(Debit,0) - ISNULL(Credit,0)) < 0, (ISNULL(Debit,0) - ISNULL(Credit,0)) * -1, NULL);
1570
1571 INSERT INTO #tmpTblCr
1572 SELECT PE.CompanyCode,
1573 PE.SiteCode,
1574 PE.DeptCode,
1575 PE.DeptName,
1576 PE.Acct,
1577 PE.Sub,
1578 PE.Client,
1579 PE.Debit,
1580 PE.Credit,
1581 PE.LineDesc,
1582 PE.SiteName,
1583 PE.PayrollName,
1584 1 [orderKey],
1585 PE.Type
1586 FROM #tmpTblPayrollEntry AS PE
1587 WHERE PE.Debit IS NULL
1588 AND PE.Credit IS NOT NULL;
1589
1590 INSERT INTO #tmpTblDr
1591 SELECT PE.CompanyCode,
1592 PE.SiteCode,
1593 PE.DeptCode,
1594 PE.DeptName,
1595 PE.Acct,
1596 PE.Sub,
1597 PE.Client,
1598 PE.Debit,
1599 PE.Credit,
1600 PE.LineDesc,
1601 PE.SiteName,
1602 PE.PayrollName,
1603 2 [orderKey],
1604 PE.Type
1605 FROM #tmpTblPayrollEntry AS PE
1606 WHERE PE.Credit IS NULL
1607 AND PE.Debit IS NOT NULL;
1608
1609
1610 SELECT ISNULL(d.CompanyCode, '000') [CompanyCode],
1611 ISNULL(d.SiteCode, '0000') [SiteCode],
1612 ISNULL(d.DeptCode, '000') [DeptCode],
1613 ISNULL(d.DeptName, '') [DeptName],
1614 ISNULL(d.Acct, '00000') [Acct],
1615 ISNULL(NULLIF(d.Sub, ''), '0000') [Sub],
1616 ISNULL(NULLIF(d.Client, '0'), '000000') [Client],
1617 '000' [IC],
1618 '0000' [ICSite],
1619 '0000' [ProjCode],
1620 d.Debit Debit,
1621 d.Credit Credit,
1622 d.LineDesc,
1623 PayrollName,
1624 d.SiteName
1625 FROM
1626 (
1627 SELECT CompanyCode,
1628 SiteCode,
1629 DeptCode,
1630 DeptName,
1631 Acct,
1632 Sub,
1633 Client,
1634 Debit,
1635 Credit,
1636 LineDesc,
1637 SiteName,
1638 PayrollName,
1639 OrderKey
1640 FROM #tmpTblCr
1641 WHERE (
1642 (Debit IS NULL)
1643 AND (Credit IS NOT NULL)
1644 )
1645 OR
1646 (
1647 (Credit IS NULL)
1648 AND (Debit IS NOT NULL)
1649 )
1650 UNION ALL
1651 SELECT CompanyCode,
1652 SiteCode,
1653 DeptCode,
1654 DeptName,
1655 Acct,
1656 Sub,
1657 Client,
1658 Debit,
1659 Credit,
1660 LineDesc,
1661 SiteName,
1662 PayrollName,
1663 OrderKey
1664 FROM #tmpTblDr
1665 WHERE (
1666 (Debit IS NULL)
1667 AND (Credit IS NOT NULL)
1668 )
1669 OR
1670 (
1671 (Credit IS NULL)
1672 AND (Debit IS NOT NULL)
1673 )
1674 ) AS d
1675 --ORDER BY OrderKey,
1676 -- CompanyCode,
1677 -- PayrollName,
1678 -- SiteName;
1679 ORDER BY d.PayrollName, Acct
1680 END;
1681
1682 INSERT INTO @reportFooter
1683 (
1684 DataKey,
1685 DataValue,
1686 PosX,
1687 PosY
1688 )
1689 --VALUES(0,0,0,0)
1690 VALUES
1691 ('KEY0', 'Grand Total', 0, 1)
1692 --('KEY1', '="Total Number of Employees" + ": " + Count(Fields.[Employee number])', 0, 1);
1693 --, ( 'KEY2', '=Count(Fields.[Employee number])', 370, 1 )
1694
1695
1696
1697 SELECT *
1698 FROM @reportFooter AS RF;
1699
1700
1701 DECLARE @reportSetting TABLE
1702 (
1703 Orientation NVARCHAR(30) NULL,
1704 SIZE NVARCHAR(30) NULL,
1705 Width NUMERIC(22, 8) NULL,
1706 Height NUMERIC(22, 8) NULL,
1707 MarginTop NUMERIC(22, 8) NULL,
1708 MarginBottom NUMERIC(22, 8) NULL,
1709 MarginLeft NUMERIC(22, 8) NULL,
1710 MarginRight NUMERIC(22, 8) NULL,
1711 scale FLOAT NULL
1712 );
1713
1714 INSERT INTO @reportSetting
1715 (
1716 Orientation,
1717 SIZE,
1718 Width,
1719 Height,
1720 MarginTop,
1721 MarginBottom,
1722 MarginLeft,
1723 MarginRight,
1724 scale
1725 )
1726 VALUES
1727 ('Landscape', 'Legal', '8.5', '13', '0.3', '0.5', '0.3', '0.3', '0');
1728
1729
1730 SELECT *
1731 FROM @reportSetting AS RS;
1732
1733
1734END;
1735GO
1736