· 5 years ago · Jul 26, 2020, 05:02 AM
1ALTER PROCEDURE [rpt].[usp_RegisterDetailed_wCostAlloc_report]
2 (
3 @payoutUID VARCHAR(36),
4 @employeeTypeUIDs NVARCHAR(MAX),
5 @departmentUIDs NVARCHAR(MAX),
6 @registerDetails NVARCHAR(MAX),
7 @filterBy INT,
8 @inputValue INT
9 )
10AS
11 BEGIN
12 --DECLARE
13 -- @employeeTypeUIDs NVARCHAR(MAX) = NULL,
14 -- @departmentUIDs NVARCHAR(MAX) = NULL,
15 -- @payoutUID VARCHAR(36) = 'F1E11473-F440-46EE-B2B1-6103F588C872',
16 -- @registerDetails NVARCHAR(MAX) = NULL,
17 -- @filterBy INT = null,
18 -- @inputValue INT = null;
19
20 DECLARE @registerDetailsList TABLE
21 (
22 registerDetails NVARCHAR(MAX)
23 );
24
25 DECLARE @employeeTypeIDList TABLE
26 (
27 EmployeeTypeID BIGINT
28 );
29 DECLARE @departmentIDList TABLE
30 (
31 DepartmentID BIGINT
32 );
33
34 INSERT INTO @registerDetailsList
35 SELECT
36 *
37 FROM
38 [dbo].[SplitByComma](@registerDetails) AS sbc
39 WHERE
40 Data != '';
41
42 INSERT INTO @employeeTypeIDList
43 SELECT
44 TET.EmpTypeID
45 FROM
46 [dbo].[SplitByComma](@employeeTypeUIDs) AS SBC
47 INNER JOIN
48 dbo.tbl_EmpType AS TET
49 ON TET.EmpTypeUID = SBC.Data
50 WHERE
51 Data != '';
52
53 INSERT INTO @departmentIDList
54 SELECT
55 TD.DepartmentID
56 FROM
57 [dbo].[SplitByComma](@departmentUIDs) AS SBC
58 INNER JOIN
59 cc.tbl_Department AS TD
60 ON TD.DepartmentUID = SBC.Data
61 WHERE
62 Data != '';
63
64 --Filtering Starts here
65 SELECT
66 'Normal' AS [ReportType];
67 /***================================*/
68
69
70 DECLARE @reportDetail TABLE
71 (
72 ReportName NVARCHAR(MAX),
73 ReportDescription NVARCHAR(MAX),
74 ReportSubDescription NVARCHAR(MAX)
75 ); --report header
76 DECLARE @reportHeaders TABLE
77 (
78 Title NVARCHAR(255) NOT NULL,
79 DataType NVARCHAR(255) NULL, -- SQL DataType (NVARCHAR(n), DECIMAL(18,4), etc..)
80 Width INT
81 DEFAULT (100), -- in pixel
82 AggregateFn VARCHAR(15) NULL
83 ); -- report columns
84 DECLARE @reportFooter TABLE
85 (
86 DataKey NVARCHAR(MAX),
87 DataValue NVARCHAR(MAX),
88 PosX INT,
89 PosY FLOAT
90 );
91
92 DECLARE
93 @clientName VARCHAR(MAX),
94 @payoutid BIGINT;
95 DECLARE @clientID INT;
96 DECLARE @payperiod VARCHAR(100);
97 DECLARE @payoutRemarks VARCHAR(200);
98
99
100 SELECT
101 @payoutid = PayoutID,
102 @payperiod = CONVERT(VARCHAR(20), PeriodStart, 107) + ' to ' + CONVERT(VARCHAR(20), PeriodEnd, 107),
103 @payoutRemarks = Remarks
104 FROM
105 dbo.tbl_Payout
106 WHERE
107 PayoutUID = @payoutUID;
108
109 SELECT
110 @clientID = TC.ClientID,
111 @clientName = REPLACE(TC.ClientName, '&', '&')
112 FROM
113 dbo.tbl_Payout AS TP
114 INNER JOIN
115 dbo.tbl_Client AS TC
116 ON TC.ClientID = TP.ClientID
117 WHERE
118 PayoutID = @payoutid;
119
120
121
122 INSERT INTO @reportDetail
123 VALUES
124 (
125 @clientName, 'Payroll Register Detailed - ' + @payoutRemarks ,@payperiod
126 );
127 SELECT
128 RD.ReportName,
129 RD.ReportDescription,
130 RD.ReportSubDescription
131 FROM
132 @reportDetail AS RD;
133 DECLARE @sql VARCHAR(MAX);
134 DECLARE @columns VARCHAR(MAX);
135
136
137 DECLARE @AETable TABLE
138 (
139 clientempid INT
140 );
141
142 IF @filterBy IN (
143 1, -1
144 )
145 BEGIN
146 INSERT INTO @AETable
147 SELECT
148 ClientEmpID
149 FROM
150 dbo.tbl_ClientEmp AS tce
151 INNER JOIN
152 dbo.tbl_EmpInfo AS TEI
153 ON TEI.EmpID = tce.EmpID
154 WHERE
155 ClientID = @clientID
156 AND FullName LIKE '[A-E]%';
157 END;
158
159 ELSE IF @filterBy IN (
160 2, -2
161 )
162 BEGIN
163 INSERT INTO @AETable
164 SELECT
165 ClientEmpID
166 FROM
167 dbo.tbl_ClientEmp AS tce
168 INNER JOIN
169 dbo.tbl_EmpInfo AS TEI
170 ON TEI.EmpID = tce.EmpID
171 WHERE
172 ClientID = @clientID
173 AND FullName LIKE '[F-J]%';
174 END;
175 ELSE IF @filterBy IN (
176 3, -3
177 )
178 BEGIN
179 INSERT INTO @AETable
180 SELECT
181 ClientEmpID
182 FROM
183 dbo.tbl_ClientEmp AS tce
184 INNER JOIN
185 dbo.tbl_EmpInfo AS TEI
186 ON TEI.EmpID = tce.EmpID
187 WHERE
188 ClientID = @clientID
189 AND FullName LIKE '[K-O]%';
190 END;
191 ELSE IF @filterBy IN (
192 4, -4
193 )
194 BEGIN
195 INSERT INTO @AETable
196 SELECT
197 ClientEmpID
198 FROM
199 dbo.tbl_ClientEmp AS tce
200 INNER JOIN
201 dbo.tbl_EmpInfo AS TEI
202 ON TEI.EmpID = tce.EmpID
203 WHERE
204 ClientID = @clientID
205 AND FullName LIKE '[P-T]%';
206 END;
207 ELSE IF @filterBy IN (
208 5, -5
209 )
210 BEGIN
211 INSERT INTO @AETable
212 SELECT
213 ClientEmpID
214 FROM
215 dbo.tbl_ClientEmp AS tce
216 INNER JOIN
217 dbo.tbl_EmpInfo AS TEI
218 ON TEI.EmpID = tce.EmpID
219 WHERE
220 ClientID = @clientID
221 AND FullName LIKE '[U-Z]%';
222 END;
223
224
225 ELSE
226 BEGIN
227 INSERT INTO @AETable
228 SELECT
229 ClientEmpID
230 FROM
231 dbo.tbl_ClientEmp AS tce
232 INNER JOIN
233 dbo.tbl_EmpInfo AS TEI
234 ON TEI.EmpID = tce.EmpID
235 WHERE
236 ClientID = @clientID;
237 END;
238
239
240
241
242 ----Filtering
243
244
245
246 DECLARE
247 @rowFrom INT,
248 @rowTo INT,
249 @rowTotalCount BIGINT;
250
251
252 SET @rowTotalCount =
253 (
254 SELECT
255 COUNT(AT.clientempid)
256 FROM
257 @AETable AS AT
258 );
259
260
261 SET @rowTo = IIF(@inputValue IS NOT NULL, @inputValue * 3000, @rowTotalCount);
262
263 SET @rowFrom = IIF(@inputValue IS NOT NULL, IIF(@inputValue = 1, 1,(@rowTo - 3000) + 1) , 1);
264
265
266
267
268
269
270 IF (OBJECT_ID('tempdb..#clientEmpID_list') IS NOT NULL)
271 BEGIN
272 DROP TABLE #clientEmpID_list;
273 END;
274
275 CREATE TABLE #clientEmpID_list
276 (
277 clientEmpID BIGINT,
278 FullName NVARCHAR(150),
279 ClientEmpCode NVARCHAR(50)
280 );
281
282 INSERT INTO #clientEmpID_list
283 SELECT
284 CLE.ClientEmpID,
285 CLE.FullName,
286 CLE.ClientEmpCode
287 FROM
288 (
289 SELECT
290 ROW_NUMBER() OVER (ORDER BY
291 TEI.FullName
292 ) RowNo,
293 TTM.ClientEmpID,
294 TEI.FullName,
295 TCE.ClientEmpCode
296 FROM
297 trn.tbl_TranMain AS TTM
298 INNER JOIN
299 dbo.tbl_ClientEmp AS TCE
300 ON TCE.ClientEmpID = TTM.ClientEmpID
301 INNER JOIN
302 dbo.tbl_EmpInfo AS TEI
303 ON TEI.EmpID = TCE.EmpID
304 INNER JOIN
305 @AETable AS AT
306 ON AT.clientempid = TCE.ClientEmpID
307 WHERE
308 PayoutID = @payoutid
309 ) CLE
310 WHERE
311 CLE.RowNo
312 BETWEEN @rowFrom AND @rowTo;
313
314
315
316 --Filtering Ends Here
317
318 --------------------------------------------- Create table in Register Begin Here -----------------------------------------------------
319 IF OBJECT_ID('tempdb..#registerSummary') IS NOT NULL
320 /*Then it exists*/
321 DROP TABLE #registerSummary;
322
323 CREATE TABLE #registerSummary
324 (
325 ClientEmpID BIGINT,
326 [Employee Number] VARCHAR(20),
327 [Employee Name] VARCHAR(MAX),
328 [Address] VARCHAR(MAX),
329 [Position] VARCHAR(MAX),
330 [Program Name] VARCHAR(MAX),
331 [Cost Allocation] VARCHAR(MAX),
332 [Co] VARCHAR(30),
333 [Lawson Site] VARCHAR(30),
334 [Dept] VARCHAR(30),
335 [Client] VARCHAR(30),
336 [IC] VARCHAR(30),
337 [IC Site] VARCHAR(30),
338 [Proj] VARCHAR(30),
339 [Department Code] VARCHAR(MAX),
340 [Department Name] VARCHAR(MAX),
341 [Section Code] VARCHAR(MAX),
342 [Section Name] VARCHAR(MAX),
343 [Site Code] VARCHAR(MAX),
344 [Site Name] VARCHAR(MAX),
345 [Birthdate] DATETIME,
346 [Emp Group] VARCHAR(MAX),
347 [Emp Status] VARCHAR(MAX),
348 [Tax Type] VARCHAR(MAX),
349 [Gender] VARCHAR(10),
350 [Civil Status] VARCHAR(30),
351 [Emp Type] VARCHAR(MAX),
352 [Factor Days] INT,
353 [Date Hired] DATETIME,
354 [Date Separated] DATETIME,
355 [TIN] INT,
356 [SSS] INT,
357 [PHIC] INT,
358 [HDMF] INT,
359 [Bank Name] VARCHAR(MAX),
360 [Account Type] VARCHAR(MAX),
361 [Account No] VARCHAR(MAX),
362 [Tax Code] VARCHAR(10),
363 [Basic Pay] DECIMAL(25, 5),
364 [Overtime] DECIMAL(25, 5),
365
366 -- [Taxable Bonus] DECIMAL(25, 2) ,
367 [GROSS TAXABLE INCOME] DECIMAL(25, 5),
368 [GROSS PAY] DECIMAL(25, 5),
369 [WithholdingTax] DECIMAL(25, 5),
370 [SSS Contribution] DECIMAL(25, 5),
371 [PHIC Contribution] DECIMAL(25, 5),
372 [HDMF Contribution] DECIMAL(25, 5),
373 [TOTAL DEDUCTIONS] DECIMAL(25, 5),
374
375 -- [Non Taxable Bonus] DECIMAL(25, 2) ,
376 [NET PAY] DECIMAL(25, 5),
377 [SSS Contribution - ER] DECIMAL(25, 5),
378 [ECC] DECIMAL(25, 5),
379 [PHIC Contribution - ER] DECIMAL(25, 5),
380 [HDMF Contribution - ER] DECIMAL(25, 5),
381 [Department] VARCHAR(MAX),
382 [Section] VARCHAR(MAX),
383 [Site] VARCHAR(MAX)
384 );
385
386
387 --------------------------------------------- Create table in Register End Here -----------------------------------------------------
388
389
390 --------------------------------------------- Other Taxable Bonus Begin here----------------------------------------------------------
391
392 DECLARE @columns_taxableBonus VARCHAR(MAX);
393
394
395
396
397 SELECT
398 @columns_taxableBonus = COALESCE(@columns_taxableBonus + ',', '') + QUOTENAME(CONCAT(BonusName, '( T)'))
399 FROM
400 (
401 SELECT DISTINCT
402 RTRIM(Bonus_BonusTypeName) BonusName
403 FROM
404 trn.tbl_FTBonusDetails AS TFBD
405 INNER JOIN
406 trn.tbl_TranMain AS TTM
407 ON TTM.TranMainID = TFBD.TranMainID
408 WHERE
409 PayoutID = @payoutid
410 AND TFBD.TaxableBonus != 0
411 ) AS B
412 ORDER BY
413 B.BonusName;
414
415
416 IF OBJECT_ID('tempdb..#temp_TaxableBonus') IS NOT NULL
417 /*Then it exists*/
418 DROP TABLE #temp_TaxableBonus;
419
420 CREATE TABLE #temp_TaxableBonus
421 (
422 [Employee_code31] VARCHAR(20),
423 costAllocID31 BIGINT
424 );
425
426 DECLARE @Bonuscounter INT;
427 DECLARE @Bonuscount INT;
428 SET @Bonuscount =
429 (
430 SELECT
431 COUNT(*)
432 FROM
433 [dbo].[ufn_CSVToTable](@columns_taxableBonus, ',')
434 );
435 DECLARE @ScriptBonus AS VARCHAR(8000);
436 --DECLARE @Script_prepare AS VARCHAR(8000);
437 DECLARE @fieldNameBonus VARCHAR(MAX);
438
439 SET @Bonuscounter = 1;
440 WHILE @Bonuscounter <= @Bonuscount
441 BEGIN
442 SET @fieldNameBonus =
443 (
444 SELECT
445 String
446 FROM
447 [dbo].[ufn_CSVToTable](@columns_taxableBonus, ',')
448 WHERE
449 ID = @Bonuscounter
450 );
451 SET @ScriptBonus = 'Alter table #temp_TaxableBonus Add ' + @fieldNameBonus + ' decimal (20,5)';
452 EXEC (@ScriptBonus);
453 SET @Bonuscounter = @Bonuscounter + 1;
454 END;
455
456 ALTER TABLE #temp_TaxableBonus
457 ADD
458 [TOTAL TAXABLE BONUS] DECIMAL(15, 5);
459 -- add Total column
460
461 DECLARE @SQLBONUST VARCHAR(MAX);
462 SET @SQLBONUST
463 = 'INSERT INTO #temp_TaxableBonus
464 SELECT *,0 FROM
465(Select TTM.clientEmpcode, TCA.CostAllocID, concat(Bonus_BonusTypeName,' + CHAR(39) + '( T)' + CHAR(39)
466 + ') BonusName, ISNULL(TFBD.taxableBonus / 100 * TCA.AllocPercentage, TFBD.TaxableBonus) amount from trn.tbl_FTBonusDetails AS TFBD INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFBD.TranMainID
467inner join dbo.tbl_Payout AS TP on TP.payoutid = TTM.payoutid LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID where TFBD.taxableBonus != 0 and TP.payoutid = '
468 --+ CHAR(39) + @payoutUid + CHAR(39) +
469 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
470 AS a
471PIVOT (SUM(amount) FOR BonusName
472IN (' + @columns_taxableBonus + ') ) AS b ';
473
474 -- PIVOT (SUM(amount) FOR DEDNAME
475
476 EXEC (@SQLBONUST);
477
478 UPDATE
479 a
480 SET
481 a.[TOTAL TAXABLE BONUS] = b.total
482 FROM
483 (
484 SELECT
485 [Employee_code31],
486 costAllocID31,
487 [TOTAL TAXABLE BONUS]
488 FROM
489 #temp_TaxableBonus
490 ) AS a
491 INNER JOIN
492 (
493 SELECT
494 TTM.ClientEmpCode,
495 TCA.CostAllocID,
496 SUM(TFBD.TaxableBonus) total
497 FROM
498 trn.tbl_FTBonusDetails AS TFBD
499 INNER JOIN
500 trn.tbl_TranMain AS TTM
501 ON TTM.TranMainID = TFBD.TranMainID
502 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
503 WHERE
504 PayoutID = @payoutid
505 AND TFBD.TaxableBonus != 0
506 GROUP BY
507 TTM.ClientEmpCode,
508 TCA.CostAllocID
509 ) AS b
510 ON a.[Employee_code31] = b.ClientEmpCode
511 AND (b.CostAllocID IS NULL) OR (a.costAllocID31 = b.CostAllocID);
512
513 --SELECT *
514 --FROM #temp_TaxableBonus AS TTB
515
516
517 --------------------------------------------- Other Taxable Bonus End here----------------------------------------------------------
518
519 --------------------------------------------- Other Taxable non taxable Bonus Begin here----------------------------------------------------------
520
521
522 DECLARE @columns_nontaxableBonus VARCHAR(MAX);
523
524 SELECT
525 @columns_nontaxableBonus
526 = COALESCE(@columns_nontaxableBonus + ',', '') + QUOTENAME(CONCAT(BonusName, '( NT)'))
527 FROM
528 (
529 SELECT DISTINCT
530 RTRIM(Bonus_BonusTypeName) BonusName
531 FROM
532 trn.tbl_FTBonusDetails AS TFBD
533 INNER JOIN
534 trn.tbl_TranMain AS TTM
535 ON TTM.TranMainID = TFBD.TranMainID
536 WHERE
537 PayoutID = @payoutid
538 AND TFBD.NonTaxableBonus != 0
539 ) AS B
540 ORDER BY
541 B.BonusName;
542
543
544 IF OBJECT_ID('tempdb..#temp_nontaxableBonus') IS NOT NULL
545 /*Then it exists*/
546 DROP TABLE #temp_nontaxableBonus;
547
548 CREATE TABLE #temp_nontaxableBonus
549 (
550 [Employee_code22] VARCHAR(20),
551 costAllocID22 BIGINT
552 );
553
554 DECLARE @BonuscounterNT INT;
555 DECLARE @BonuscountNT INT;
556 SET @BonuscountNT =
557 (
558 SELECT
559 COUNT(*)
560 FROM
561 [dbo].[ufn_CSVToTable](@columns_nontaxableBonus, ',')
562 );
563 DECLARE @ScriptBonusNT AS VARCHAR(8000);
564 --DECLARE @Script_prepare AS VARCHAR(8000);
565 DECLARE @fieldNameBonusNT VARCHAR(MAX);
566
567 SET @BonuscounterNT = 1;
568 WHILE @BonuscounterNT <= @BonuscountNT
569 BEGIN
570 SET @fieldNameBonus =
571 (
572 SELECT
573 String
574 FROM
575 [dbo].[ufn_CSVToTable](@columns_nontaxableBonus, ',')
576 WHERE
577 ID = @BonuscounterNT
578 );
579 SET @ScriptBonus = 'Alter table #temp_nontaxableBonus Add ' + @fieldNameBonus + ' decimal (20,5)';
580 EXEC (@ScriptBonus);
581 SET @BonuscounterNT = @BonuscounterNT + 1;
582 END;
583
584 ALTER TABLE #temp_nontaxableBonus
585 ADD
586 [TOTAL NONTAXABLE BONUS] DECIMAL(15, 5);
587 -- add Total column
588
589 DECLARE @SQLBONUSNT VARCHAR(MAX);
590 SET @SQLBONUSNT
591 = 'INSERT INTO #temp_nontaxableBonus
592 SELECT *,0 FROM
593(Select TTM.clientEmpcode, TCA.CostAllocID, concat(Bonus_BONUSTypeName,' + CHAR(39) + '( NT)' + CHAR(39)
594 + ') BonusName, ISNULL(TFBD.NonTaxableBonus / 100 * TCA.AllocPercentage, TFBD.NonTaxableBonus) amount from trn.tbl_FTBonusDetails AS TFBD INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFBD.TranMainID
595inner join dbo.tbl_Payout AS TP on TP.payoutid = TTM.payoutid LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID where TFBD.nontaxableBonus != 0 and tp.payoutid = '
596 --+ CHAR(39) + @payoutuid + CHAR(39) +
597 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
598 AS a
599PIVOT (SUM(amount) FOR BonusName
600IN (' + @columns_nontaxableBonus + ') ) AS b ';
601
602 -- PIVOT (SUM(amount) FOR DEDNAME
603
604
605 EXEC (@SQLBONUSNT);
606
607 UPDATE
608 a
609 SET
610 a.[TOTAL NONTAXABLE BONUS] = b.total
611 FROM
612 (
613 SELECT
614 [Employee_code22],
615 costAllocID22,
616 [TOTAL NONTAXABLE BONUS]
617 FROM
618 #temp_nontaxableBonus
619 ) AS a
620 INNER JOIN
621 (
622 SELECT
623 TTM.ClientEmpCode,
624 TCA.CostAllocID,
625 SUM(ISNULL(TFBD.NonTaxableBonus / 100 * TCA.AllocPercentage, TFBD.NonTaxableBonus)) total
626 FROM
627 trn.tbl_FTBonusDetails AS TFBD
628 INNER JOIN
629 trn.tbl_TranMain AS TTM
630 ON TTM.TranMainID = TFBD.TranMainID
631 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
632 WHERE
633 PayoutID = @payoutid
634 AND TFBD.NonTaxableBonus != 0
635 GROUP BY
636 TTM.ClientEmpCode,
637 TCA.CostAllocID
638 ) AS b
639 ON a.[Employee_code22] = b.ClientEmpCode
640 AND (b.CostAllocID IS NULL) OR (a.costAllocID22 = b.CostAllocID);
641
642 --SELECT *
643 --FROM #temp_nontaxableBonus AS TTB
644
645 --------------------------------------------- Other Taxable non taxable Bonus End here----------------------------------------------------------
646
647 --------------------------------------------- Other Taxable Income Begin here----------------------------------------------------------
648
649 DECLARE @columns_taxableEarnings VARCHAR(MAX);
650
651 --SET @payoutuid = '3C812BF6-CAD0-43D8-B3AC-3D712586BE7B'
652
653 SELECT
654 @columns_taxableEarnings = COALESCE(@columns_taxableEarnings + ',', '') + QUOTENAME(EarnName)
655 FROM
656 (
657 SELECT DISTINCT
658 RTRIM(TFED.Earn_EarnName) EarnName
659 FROM
660 trn.tbl_FTEarnDetails AS TFED
661 INNER JOIN
662 trn.tbl_TranMain AS TTM
663 ON TTM.TranMainID = TFED.TranMainID
664 INNER JOIN
665 dbo.tbl_Payout AS TP
666 ON TP.PayoutID = TTM.PayoutID
667 WHERE
668 TTM.PayoutID = @payoutid
669 AND Earn_IsTaxable = 1
670 ) AS B
671 ORDER BY
672 B.EarnName;
673 --PRINT @columns_taxableEarnings
674
675
676 IF OBJECT_ID('tempdb..#temp_TaxableEarnings') IS NOT NULL
677 /*Then it exists*/
678 DROP TABLE #temp_TaxableEarnings;
679
680 CREATE TABLE #temp_TaxableEarnings
681 (
682 [Employee_code1] VARCHAR(20),
683 costAllocID1 BIGINT
684 );
685 DECLARE @counter INT;
686 DECLARE @count INT;
687 SET @count =
688 (
689 SELECT
690 COUNT(*)
691 FROM
692 [dbo].[ufn_CSVToTable](@columns_taxableEarnings, ',')
693 );
694 DECLARE @ScriptEarn AS VARCHAR(8000);
695 --DECLARE @Script_prepare AS VARCHAR(8000);
696 DECLARE @fieldNameEarn VARCHAR(MAX);
697
698 SET @counter = 1;
699 WHILE @counter <= @count
700 BEGIN
701 SET @fieldNameEarn =
702 (
703 SELECT
704 String
705 FROM
706 [dbo].[ufn_CSVToTable](@columns_taxableEarnings, ',')
707 WHERE
708 ID = @counter
709 );
710 SET @ScriptEarn = 'Alter table #temp_TaxableEarnings Add ' + @fieldNameEarn + ' decimal (20,5)';
711
712 EXEC (@ScriptEarn);
713 SET @counter = @counter + 1;
714 END;
715
716 ALTER TABLE #temp_TaxableEarnings
717 ADD
718 [TOTAL OTHER TAXABLE EARNINGS] DECIMAL(15, 5); -- add Total column
719
720 DECLARE @SQLEARNT VARCHAR(MAX);
721 SET @SQLEARNT
722 = 'INSERT INTO #temp_TaxableEarnings
723 SELECT *,0 FROM
724 (Select TTM.clientEmpcode,TCA.CostAllocID, Earn_EarnName EarnName, ISNULL(amount / 100 * TCA.AllocPercentage, TFED.Amount) amount from trn.tbl_FTEarnDetails AS TFED
725 INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFED.TranMainID
726 INNER JOIN dbo.tbl_Payout AS TP ON TP.PayoutID = TTM.PayoutID
727 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
728 where Earn_IsTaxable = 1 and tp.payoutid = '
729 --+ CHAR(39) + @payoutuid + CHAR(39)
730 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
731 AS a
732PIVOT (SUM(amount) FOR EarnName
733IN (' + @columns_taxableEarnings + ')) AS b ';
734
735 -- PIVOT (SUM(amount) FOR DEDNAME
736
737
738 EXEC (@SQLEARNT);
739
740 UPDATE
741 a
742 SET
743 a.[Total Other Taxable Earnings] = b.total
744 FROM
745 (
746 SELECT
747 [Employee_code1],
748 costAllocID1,
749 [Total Other Taxable Earnings]
750 FROM
751 #temp_TaxableEarnings
752 ) AS a
753 INNER JOIN
754 (
755 SELECT
756 TTM.ClientEmpCode,
757 TCA.CostAllocID,
758 SUM(ISNULL(TFED.Amount / 100 * TCA.AllocPercentage,TFED.Amount)) total
759 FROM
760 trn.tbl_FTEarnDetails AS TFED
761 INNER JOIN
762 trn.tbl_TranMain AS TTM
763 ON TTM.TranMainID = TFED.TranMainID
764 INNER JOIN
765 dbo.tbl_Payout AS TP
766 ON TP.PayoutID = TTM.PayoutID
767 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
768 WHERE
769 TTM.PayoutID = @payoutid
770 AND Earn_IsTaxable = 1
771 GROUP BY
772 TTM.ClientEmpCode,
773 TCA.CostAllocID
774 ) AS b
775 ON a.[Employee_code1] = b.ClientEmpCode
776 AND (b.CostAllocID IS NULL) OR (a.costAllocID1 = b.CostAllocID);
777 --SELECT *
778 --FROM #temp_TaxableEarnings
779
780
781
782 -------------------------------------------------- Other Taxable Income End here----------------------------------------------------------------
783
784 ---------------------------------------------------Other Non taxable Income Starts here ------------------------------------------------------------
785 DECLARE @columns_NontaxableEarnings VARCHAR(MAX);
786
787
788 SELECT
789 @columns_NontaxableEarnings = COALESCE(@columns_NontaxableEarnings + ',', '') + QUOTENAME(EarnName)
790 FROM
791 (
792 SELECT DISTINCT
793 RTRIM(Earn_EarnName) EarnName
794 FROM
795 trn.tbl_FTEarnDetails AS TFED
796 INNER JOIN
797 trn.tbl_TranMain AS TTM
798 ON TTM.TranMainID = TFED.TranMainID
799 INNER JOIN
800 dbo.tbl_Payout AS TP
801 ON TP.PayoutID = TTM.PayoutID
802 WHERE
803 TTM.PayoutID = @payoutid
804 AND Earn_IsTaxable = 0
805 AND Earn_IsReceivable = 0
806 ) AS B
807 ORDER BY
808 B.EarnName;
809
810
811
812
813 IF OBJECT_ID('tempdb..#temp_NonTaxableEarnings') IS NOT NULL
814 /*Then it exists*/
815 DROP TABLE #temp_NonTaxableEarnings;
816
817 CREATE TABLE #temp_NonTaxableEarnings
818 (
819 [Employee_code2] VARCHAR(20),
820 costAllocID2 BIGINT
821 );
822 DECLARE @counterNT INT;
823 DECLARE @countNT INT;
824 SET @countNT =
825 (
826 SELECT
827 COUNT(*)
828 FROM
829 [dbo].[ufn_CSVToTable](@columns_NontaxableEarnings, ',')
830 );
831 DECLARE @ScriptEarnNT AS VARCHAR(8000);
832 DECLARE @Script_prepareNT AS VARCHAR(8000);
833 DECLARE @fieldNameEarnNT VARCHAR(MAX);
834
835 SET @counterNT = 1;
836 WHILE @counterNT <= @countNT
837 BEGIN
838 SET @fieldNameEarnNT =
839 (
840 SELECT
841 String
842 FROM
843 [dbo].[ufn_CSVToTable](@columns_NontaxableEarnings, ',')
844 WHERE
845 ID = @counterNT
846 );
847 SET @ScriptEarnNT = 'Alter table #temp_NonTaxableEarnings Add ' + @fieldNameEarnNT + ' decimal (20,5)';
848 EXEC (@ScriptEarnNT);
849 SET @counterNT = @counterNT + 1;
850 END;
851
852 ALTER TABLE #temp_NonTaxableEarnings
853 ADD
854 [TOTAL OTHER NON TAXABLE EARNINGS] DECIMAL(15, 5); -- add Total column
855
856 DECLARE @SQLEARNONTAX VARCHAR(MAX);
857 SET @SQLEARNONTAX
858 = 'INSERT INTO #temp_NonTaxableEarnings
859 SELECT *,0 FROM
860(Select TTM.clientEmpcode,TCA.CostAllocID, Earn_EarnName EarnName, ISNULL(TFED.Amount / 100 * TCA.AllocPercentage,amount) amount from
861 trn.tbl_FTEarnDetails AS TFED
862 INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFED.TranMainID
863 INNER JOIN dbo.tbl_Payout AS TP ON TP.PayoutID = TTM.PayoutID LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID where Earn_IsTaxable = 0 and Earn_isReceivable = 0 and tp.payoutid = '
864 --+ CHAR(39) + @payoutuid + CHAR(39)
865 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
866 AS a
867PIVOT (SUM(amount) FOR EarnName
868IN (' + @columns_NontaxableEarnings + ') ) AS b ';
869
870 -- PIVOT (SUM(amount) FOR DEDNAME
871
872
873 EXEC (@SQLEARNONTAX);
874
875 UPDATE
876 a
877 SET
878 a.[Total Other Non Taxable Earnings] = b.total
879 FROM
880 (
881 SELECT
882 [Employee_code2],
883 costAllocID2,
884 [Total Other Non Taxable Earnings]
885 FROM
886 #temp_NonTaxableEarnings
887 ) AS a
888 INNER JOIN
889 (
890 SELECT
891 TTM.ClientEmpCode,
892 TCA.CostAllocID,
893 SUM(ISNULL(TFED.Amount / 100 * TCA.AllocPercentage,TFED.Amount)) total
894 FROM
895 trn.tbl_FTEarnDetails AS TFED
896 INNER JOIN
897 trn.tbl_TranMain AS TTM
898 ON TTM.TranMainID = TFED.TranMainID
899 INNER JOIN
900 dbo.tbl_Payout AS TP
901 ON TP.PayoutID = TTM.PayoutID
902 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
903 WHERE
904 TTM.PayoutID = @payoutid
905 AND Earn_IsTaxable = 0
906 AND Earn_IsReceivable = 0
907 GROUP BY
908 TTM.ClientEmpCode,
909 TCA.CostAllocID
910 ) AS b
911 ON a.[Employee_code2] = b.ClientEmpCode
912 AND (B.CostAllocID IS NULL) OR (b.CostAllocID = a.costAllocID2);
913
914 --------------------------------------------------- Other Non taxable income ends here --------------------------------------------------------------
915
916 --------------------------------------------------- Government Loans Starts here --------------------------------------------------------------
917
918 DECLARE @sqlGov VARCHAR(MAX);
919 DECLARE @columnsGov VARCHAR(MAX);
920
921
922 SELECT
923 @columnsGov = COALESCE(@columnsGov + ',', '') + QUOTENAME(GovernmentLoanTypeName)
924 FROM
925 (
926 SELECT DISTINCT
927 RTRIM(TFLD.EmpLoan_LoanName) GovernmentLoanTypeName
928 FROM
929 trn.tbl_FTLoanDetails AS TFLD
930 INNER JOIN
931 trn.tbl_TranMain AS TTM
932 ON TTM.TranMainID = TFLD.TranMainID
933 INNER JOIN
934 dbo.tbl_Payout AS TP
935 ON TP.PayoutID = TTM.PayoutID
936 WHERE
937 TP.PayoutID = @payoutid
938 AND TFLD.EmpLoan_GoverntmentLoanTypeID IS NOT NULL
939 ) AS B
940 ORDER BY
941 B.GovernmentLoanTypeName;
942 --PRINT @columns
943
944 -- CREATE DYNAMIC TEMPORARY TABLE
945
946 IF OBJECT_ID('tempdb..#tempGov') IS NOT NULL
947 /*Then it exists*/
948 DROP TABLE #tempGov;
949
950 CREATE TABLE #tempGov
951 (
952 [Employee_code3] VARCHAR(20),
953 costAllocID3 BIGINT
954 );
955 DECLARE @counterGov INT;
956 DECLARE @countGov INT;
957 SET @countGov =
958 (
959 SELECT
960 COUNT(*)
961 FROM
962 [dbo].[ufn_CSVToTable](@columnsGov, ',')
963 );
964 DECLARE @ScriptGov AS VARCHAR(8000);
965 DECLARE @Script_prepareGov AS VARCHAR(8000);
966 DECLARE @fieldNameGov VARCHAR(MAX);
967
968 SET @counterGov = 1;
969 WHILE @counterGov <= @countGov
970 BEGIN
971 SET @fieldNameGov =
972 (
973 SELECT
974 String
975 FROM
976 [dbo].[ufn_CSVToTable](@columnsGov, ',')
977 WHERE
978 ID = @counterGov
979 );
980 SET @ScriptGov = 'Alter table #tempGov Add ' + @fieldNameGov + ' decimal (15,5)';
981 EXEC (@ScriptGov);
982 SET @counterGov = @counterGov + 1;
983 END;
984
985 ALTER TABLE #tempGov
986 ADD
987 [TOTAL GOVERNMENT LOANS] DECIMAL(15, 5); -- add Total column
988
989
990 SET @sqlGov
991 = 'INSERT INTO #tempGov
992 SELECT *,0 FROM
993(Select clientEmpcode, TCA.CostAllocID,EmpLoan_LoanName LoanName, ISNULL(Amount / 100 * TCA.AllocPercentage,Amount) EditedAmount from
994 trn.tbl_FTLoanDetails AS TFLD
995 INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFLD.TranMainID
996 INNER JOIN dbo.tbl_Payout AS TP ON TP.PayoutID = TTM.PayoutID
997 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
998 WHERE TFLD.EmpLoan_GoverntmentLoanTypeID IS NOT NULL and tp.payoutid = '
999 --+ CHAR(39) + @payoutuid + CHAR(39)
1000 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
1001 AS a
1002PIVOT (SUM(EditedAmount) FOR LoanName
1003IN (' + @columnsGov + ') ) AS b ';
1004
1005 -- PIVOT (SUM(EditedAmount) FOR DEDNAME
1006
1007
1008 EXEC (@sqlGov);
1009 -- update total (temporary)
1010 UPDATE
1011 a
1012 SET
1013 a.[TOTAL GOVERNMENT LOANS] = b.total
1014 FROM
1015 (
1016 SELECT
1017 [Employee_code3],
1018 costAllocID3,
1019 [TOTAL GOVERNMENT LOANS]
1020 FROM
1021 #tempGov
1022 ) AS a
1023 INNER JOIN
1024 (
1025 SELECT
1026 ClientEmpCode,
1027 TCA.CostAllocID,
1028 SUM(ISNULL(TFLD.Amount / 100 * TCA.AllocPercentage,TFLD.Amount)) total
1029 FROM
1030 trn.tbl_FTLoanDetails AS TFLD
1031 INNER JOIN
1032 trn.tbl_TranMain AS TTM
1033 ON TTM.TranMainID = TFLD.TranMainID
1034 INNER JOIN
1035 dbo.tbl_Payout AS TP
1036 ON TP.PayoutID = TTM.PayoutID
1037 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
1038 WHERE
1039 TTM.PayoutID = @payoutid
1040 AND TFLD.EmpLoan_GoverntmentLoanTypeID IS NOT NULL
1041 GROUP BY
1042 ClientEmpCode,
1043 TCA.CostAllocID
1044 ) AS b
1045 ON a.[Employee_code3] = b.ClientEmpCode
1046 AND (b.CostAllocID IS NULL) OR (b.CostAllocID = a.costAllocID3);
1047
1048
1049 --------------------------------------------------- Government Loans Ends here --------------------------------------------------------------
1050
1051
1052 --------------------------------------------------- Company Loans Starts Here -----------------------------------------------------------------
1053
1054
1055 DECLARE @sqlCompany VARCHAR(MAX);
1056 DECLARE @columnsCompany VARCHAR(MAX);
1057
1058
1059 SELECT
1060 @columnsCompany = COALESCE(@columnsCompany + ',', '') + QUOTENAME(CompanyernmentLoanTypeName)
1061 FROM
1062 (
1063 SELECT DISTINCT
1064 RTRIM(TFLD.EmpLoan_LoanName) CompanyernmentLoanTypeName
1065 FROM
1066 trn.tbl_FTLoanDetails AS TFLD
1067 INNER JOIN
1068 trn.tbl_TranMain AS TTM
1069 ON TTM.TranMainID = TFLD.TranMainID
1070 INNER JOIN
1071 dbo.tbl_Payout AS TP
1072 ON TP.PayoutID = TTM.PayoutID
1073 WHERE
1074 TP.PayoutID = @payoutid
1075 AND TFLD.EmpLoan_GoverntmentLoanTypeID IS NULL
1076 ) AS B
1077 ORDER BY
1078 B.CompanyernmentLoanTypeName;
1079 --PRINT @columns
1080
1081 -- CREATE DYNAMIC TEMPORARY TABLE
1082
1083 IF OBJECT_ID('tempdb..#tempCompany') IS NOT NULL
1084 /*Then it exists*/
1085 DROP TABLE #tempCompany;
1086
1087 CREATE TABLE #tempCompany
1088 (
1089 [Employee_code4] VARCHAR(20),
1090 costAllocID4 BIGINT
1091 );
1092 DECLARE @counterCompany INT;
1093 DECLARE @countCompany INT;
1094 SET @countCompany =
1095 (
1096 SELECT
1097 COUNT(*)
1098 FROM
1099 [dbo].[ufn_CSVToTable](@columnsCompany, ',')
1100 );
1101 DECLARE @ScriptCompany AS VARCHAR(8000);
1102 DECLARE @Script_prepareCompany AS VARCHAR(8000);
1103 DECLARE @fieldNameCompany VARCHAR(MAX);
1104
1105 SET @counterCompany = 1;
1106 WHILE @counterCompany <= @countCompany
1107 BEGIN
1108 SET @fieldNameCompany =
1109 (
1110 SELECT
1111 String
1112 FROM
1113 [dbo].[ufn_CSVToTable](@columnsCompany, ',')
1114 WHERE
1115 ID = @counterCompany
1116 );
1117 SET @ScriptCompany = 'Alter table #tempCompany Add ' + @fieldNameCompany + ' decimal (15,5)';
1118 EXEC (@ScriptCompany);
1119 SET @counterCompany = @counterCompany + 1;
1120 END;
1121
1122 ALTER TABLE #tempCompany
1123 ADD
1124 [TOTAL COMPANY LOANS] DECIMAL(15, 5); -- add Total column
1125
1126
1127 SET @sqlCompany
1128 = 'INSERT INTO #tempCompany
1129 SELECT *,0 FROM
1130(Select clientEmpcode, TCA.CostAllocID, TFLD.EmpLoan_LoanName LoanName, ISNULL(Amount / 100 * TCA.AllocPercentage,Amount) EditedAmount from trn.tbl_FTLoanDetails AS TFLD
1131 INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFLD.TranMainID
1132 INNER JOIN dbo.tbl_Payout AS TP ON TP.PayoutID = TTM.PayoutID
1133 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
1134 where TFLD.EmpLoan_GoverntmentLoanTypeID IS NULL and TP.payoutid = '
1135 --+ CHAR(39) + @payoutuid + CHAR(39)
1136 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
1137 AS a
1138PIVOT (SUM(EditedAmount) FOR LoanName
1139IN (' + @columnsCompany + ') ) AS b ';
1140
1141 -- PIVOT (SUM(EditedAmount) FOR DEDNAME
1142
1143
1144 EXEC (@sqlCompany);
1145 -- update total (temporary)
1146 UPDATE
1147 a
1148 SET
1149 a.[TOTAL COMPANY LOANS] = b.total
1150 FROM
1151 (
1152 SELECT
1153 [Employee_code4],
1154 costAllocID4,
1155 [TOTAL COMPANY LOANS]
1156 FROM
1157 #tempCompany
1158 ) AS a
1159 INNER JOIN
1160 (
1161 SELECT
1162 ClientEmpCode,
1163 TCA.CostAllocID,
1164 SUM(ISNULL(Amount / 100 * TCA.AllocPercentage,Amount)) total
1165 FROM
1166 trn.tbl_FTLoanDetails AS TFLD
1167 INNER JOIN
1168 trn.tbl_TranMain AS TTM
1169 ON TTM.TranMainID = TFLD.TranMainID
1170 INNER JOIN
1171 dbo.tbl_Payout AS TP
1172 ON TP.PayoutID = TTM.PayoutID
1173 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
1174 WHERE
1175 TTM.PayoutID = @payoutid
1176 AND TFLD.EmpLoan_GoverntmentLoanTypeID IS NULL
1177 GROUP BY
1178 ClientEmpCode,
1179 TCA.CostAllocID
1180 ) AS b
1181 ON a.[Employee_code4] = b.ClientEmpCode
1182 AND (b.CostAllocID IS NULL) OR (a.costAllocID4 = b.CostAllocID);
1183
1184
1185 --------------------------------------------------- Company Loans Ends Here -------------------------------------------------------------------
1186
1187 --------------------------------------------------- Company Deduction Starts Here -------------------------------------------------------------------
1188 DECLARE @sqlDeduction VARCHAR(MAX);
1189 DECLARE @columnsDeduction VARCHAR(MAX);
1190
1191 -- DEDUCTIONS
1192 SELECT
1193 @columnsDeduction = COALESCE(@columnsDeduction + ',', '') + QUOTENAME(dedname)
1194 FROM
1195 (
1196 SELECT DISTINCT
1197 RTRIM(TFDD.Ded_DedName) dedname
1198 FROM
1199 trn.tbl_FTDedDetails AS TFDD
1200 INNER JOIN
1201 trn.tbl_TranMain AS TTM
1202 ON TTM.TranMainID = TFDD.TranMainID
1203 INNER JOIN
1204 dbo.tbl_Payout AS TP
1205 ON TP.PayoutID = TTM.PayoutID
1206 WHERE
1207 TTM.PayoutID = @payoutid
1208 ) AS B
1209 ORDER BY
1210 B.dedname;
1211 --PRINT @columns
1212
1213 -- CREATE DYNAMIC TEMPORARY TABLE
1214
1215 IF OBJECT_ID('tempdb..#tempDeduction') IS NOT NULL
1216 /*Then it exists*/
1217 DROP TABLE #tempDeduction;
1218
1219 CREATE TABLE #tempDeduction
1220 (
1221 [Employee_code5] VARCHAR(20),
1222 costAllocID5 BIGINT
1223 );
1224 DECLARE @counterDeduction INT;
1225 DECLARE @countDeduction INT;
1226 SET @countDeduction =
1227 (
1228 SELECT
1229 COUNT(*)
1230 FROM
1231 [dbo].[ufn_CSVToTable](@columnsDeduction, ',')
1232 );
1233 DECLARE @ScriptDeduction AS VARCHAR(8000);
1234 DECLARE @Script_prepareDeduction AS VARCHAR(8000);
1235 DECLARE @fieldNameDeduction VARCHAR(MAX);
1236
1237 SET @counterDeduction = 1;
1238 WHILE @counterDeduction <= @countDeduction
1239 BEGIN
1240 SET @fieldNameDeduction =
1241 (
1242 SELECT
1243 String
1244 FROM
1245 [dbo].[ufn_CSVToTable](@columnsDeduction, ',')
1246 WHERE
1247 ID = @counterDeduction
1248 );
1249 SET @ScriptDeduction = 'Alter table #tempDeduction Add ' + @fieldNameDeduction + ' decimal (15,5)';
1250 EXEC (@ScriptDeduction);
1251 SET @counterDeduction = @counterDeduction + 1;
1252 END;
1253
1254 ALTER TABLE #tempDeduction
1255 ADD
1256 [TOTAL COMPANY DEDUCTIONS] DECIMAL(15, 5); -- add Total column
1257
1258 SET @sqlDeduction
1259 = 'INSERT INTO #tempDeduction
1260 SELECT *,0 FROM
1261(Select TTM.clientEmpcode,TCA.CostAllocID,Ded_DedName DEDNAME, ISNULL(Amount / 100 * TCA.AllocPercentage,Amount) EditedAmount from
1262 trn.tbl_FTDedDetails AS TFDD
1263 INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFDD.TranMainID
1264 INNER JOIN dbo.tbl_Payout AS TP ON TP.PayoutID = TTM.PayoutID
1265 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
1266where TP.payoutid = '
1267 --+ CHAR(39) + @payoutuid + CHAR(39)
1268 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
1269 AS a
1270PIVOT (SUM(EditedAmount) FOR DEDNAME
1271IN (' + @columnsDeduction + ') ) AS b ';
1272
1273 EXEC (@sqlDeduction);
1274
1275
1276 UPDATE
1277 a
1278 SET
1279 a.[TOTAL COMPANY DEDUCTIONS] = b.total
1280 FROM
1281 (
1282 SELECT
1283 [Employee_code5],
1284 costAllocID5,
1285 [TOTAL COMPANY DEDUCTIONS]
1286 FROM
1287 #tempDeduction
1288 ) AS a
1289 INNER JOIN
1290 (
1291 SELECT
1292 TTM.ClientEmpCode,
1293 TCA.CostAllocID,
1294 SUM(ISNULL(Amount / 100 * TCA.AllocPercentage,Amount)) total
1295 FROM
1296 trn.tbl_FTDedDetails AS TFDD
1297 INNER JOIN
1298 trn.tbl_TranMain AS TTM
1299 ON TTM.TranMainID = TFDD.TranMainID
1300 INNER JOIN
1301 dbo.tbl_Payout AS TP
1302 ON TP.PayoutID = TTM.PayoutID
1303 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
1304 WHERE
1305 TTM.PayoutID = @payoutid
1306 GROUP BY
1307 TTM.ClientEmpCode,
1308 TCA.CostAllocID
1309 ) AS b
1310 ON a.[Employee_code5] = b.ClientEmpCode
1311 AND (B.CostAllocID IS NULL) OR (A.costAllocID5 =B.CostAllocID);
1312
1313 --------------------------------------------------- Company Deduction Ends Here -------------------------------------------------------------------
1314
1315
1316 ---------------------------------------------------Other Receivable Income Starts here ------------------------------------------------------------
1317 DECLARE @columns_ReceivableEarnings VARCHAR(MAX);
1318
1319
1320 SELECT
1321 @columns_ReceivableEarnings = COALESCE(@columns_ReceivableEarnings + ',', '') + QUOTENAME(EarnName)
1322 FROM
1323 (
1324 SELECT DISTINCT
1325 RTRIM(Earn_EarnName) EarnName
1326 FROM
1327 trn.tbl_FTEarnDetails AS TFED
1328 INNER JOIN
1329 trn.tbl_TranMain AS TTM
1330 ON TTM.TranMainID = TFED.TranMainID
1331 INNER JOIN
1332 dbo.tbl_Payout AS TP
1333 ON TP.PayoutID = TTM.PayoutID
1334 WHERE
1335 TTM.PayoutID = @payoutid
1336 AND TFED.Earn_IsTaxable = 0
1337 AND TFED.Earn_IsReceivable = 1
1338 ) AS B
1339 ORDER BY
1340 B.EarnName;
1341
1342
1343
1344
1345 IF OBJECT_ID('tempdb..#temp_ReceivableEarnings') IS NOT NULL
1346 /*Then it exists*/
1347 DROP TABLE #temp_ReceivableEarnings;
1348
1349 CREATE TABLE #temp_ReceivableEarnings
1350 (
1351 [Employee_code6] VARCHAR(20),
1352 costAllocID6 BIGINT
1353 );
1354 DECLARE @counterNTRC INT;
1355 DECLARE @countNTRC INT;
1356 SET @countNTRC =
1357 (
1358 SELECT
1359 COUNT(*)
1360 FROM
1361 [dbo].[ufn_CSVToTable](@columns_ReceivableEarnings, ',')
1362 );
1363 DECLARE @ScriptEarnNTRC AS VARCHAR(8000);
1364 DECLARE @Script_prepareNTRC AS VARCHAR(8000);
1365 DECLARE @fieldNameEarnNTRC VARCHAR(MAX);
1366
1367 SET @counterNTRC = 1;
1368 WHILE @counterNTRC <= @countNTRC
1369 BEGIN
1370 SET @fieldNameEarnNTRC =
1371 (
1372 SELECT
1373 String
1374 FROM
1375 [dbo].[ufn_CSVToTable](@columns_ReceivableEarnings, ',')
1376 WHERE
1377 ID = @counterNTRC
1378 );
1379 SET @ScriptEarnNTRC
1380 = 'Alter table #temp_ReceivableEarnings Add ' + @fieldNameEarnNTRC + ' decimal (20,5)';
1381 EXEC (@ScriptEarnNTRC);
1382 SET @counterNTRC = @counterNTRC + 1;
1383 END;
1384
1385 ALTER TABLE #temp_ReceivableEarnings
1386 ADD
1387 [TOTAL RECEIVABLE EARNINGS] DECIMAL(15, 5); -- add Total column
1388
1389 DECLARE @SQLEARNONTAXRC VARCHAR(MAX);
1390 SET @SQLEARNONTAXRC
1391 = 'INSERT INTO #temp_ReceivableEarnings
1392 SELECT *,0 FROM
1393(Select TTM.clientEmpcode,TCA.CostAllocID,Earn_EarnName EarnName, ISNULL(Amount / 100 * TCA.AllocPercentage,amount) amount from
1394 trn.tbl_FTEarnDetails AS TFED
1395 INNER JOIN trn.tbl_TranMain AS TTM ON TTM.TranMainID = TFED.TranMainID
1396 INNER JOIN dbo.tbl_Payout AS TP ON TP.PayoutID = TTM.PayoutID
1397 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID where Earn_IsTaxable = 0 and Earn_isReceivable = 1 and TP.payoutid = '
1398 --+ CHAR(39) + @payoutuid + CHAR(39)
1399 + CAST(@payoutid AS NVARCHAR(MAX)) + ')
1400 AS a
1401PIVOT (SUM(amount) FOR EarnName
1402IN (' + @columns_ReceivableEarnings + ') ) AS b ';
1403
1404
1405 EXEC (@SQLEARNONTAXRC);
1406
1407 UPDATE
1408 a
1409 SET
1410 a.[TOTAL RECEIVABLE EARNINGS] = b.total
1411 FROM
1412 (
1413 SELECT
1414 [Employee_code6],
1415 costAllocID6,
1416 [TOTAL RECEIVABLE EARNINGS]
1417 FROM
1418 #temp_ReceivableEarnings
1419 ) AS a
1420 INNER JOIN
1421 (
1422 SELECT
1423 TTM.ClientEmpCode,
1424 TCA.CostAllocID,
1425 SUM(ISNULL(TFED.Amount / 100 * TCA.AllocPercentage,Amount)) total
1426 FROM
1427 trn.tbl_FTEarnDetails AS TFED
1428 INNER JOIN
1429 trn.tbl_TranMain AS TTM
1430 ON TTM.TranMainID = TFED.TranMainID
1431 INNER JOIN
1432 dbo.tbl_Payout AS TP
1433 ON TP.PayoutID = TTM.PayoutID
1434 LEFT JOIN ce.tbl_CostAlloc AS TCA ON TCA.ClientEmpID = TTM.ClientEmpID
1435 WHERE
1436 TP.PayoutID = @payoutid
1437 AND Earn_IsTaxable = 0
1438 AND Earn_IsReceivable = 1
1439 GROUP BY
1440 TTM.ClientEmpCode,
1441 TCA.CostAllocID
1442 ) AS b
1443 ON a.[Employee_code6] = b.ClientEmpCode
1444 AND (b.CostAllocID IS NULL) OR (a.costAllocID6 = b.CostAllocID);
1445
1446 --------------------------------------------------- Other Receivable income ends here --------------------------------------------------------------
1447
1448 -----------------------------------------------------------------------------------------------------------------------------------------------------
1449
1450 DECLARE @AllowDed TABLE
1451 (
1452 [Employee Number] VARCHAR(20),
1453
1454 --TranMainID BIGINT ,
1455 [SSS Contribution] DECIMAL(12, 2),
1456 [SSS Contribution - ER] DECIMAL(12, 2),
1457 [ECC] DECIMAL(12, 2),
1458 [PHIC Contribution] DECIMAL(12, 2),
1459 [PHIC Contribution - ER] DECIMAL(12, 2),
1460 [HDMF Contribution] DECIMAL(12, 2),
1461 [HDMF Contribution - ER] DECIMAL(12, 2)
1462 );
1463
1464 INSERT INTO @AllowDed
1465 EXEC [rpt].[usp__AllowableDeduction_reportWithERShare]
1466 @payoutUID;
1467
1468 --SELECT * FROM @AllowDed
1469
1470 INSERT INTO #registerSummary
1471 SELECT
1472 main.ClientEmpID,
1473 ClientEmpCode [Employee Number],
1474 FullName [Employee Name],
1475 NULL [Address],
1476 NULL [Position],
1477 NULL [Program Name],
1478 NULL [Cost Allocation],
1479 NULL [Co],
1480 NULL [Lawson Site],
1481 NULL [Dept],
1482 NULL [Client],
1483 NULL [IC],
1484 NULL [IC Site],
1485 NULL [Proj],
1486 NULL [Department Code],
1487 NULL [Department Name],
1488 NULL [Section Code],
1489 NULL [Section Name],
1490 NULL [Site Code],
1491 NULL [Site Name],
1492 NULL [Birthdate],
1493 NULL [Emp Group],
1494 NULL [Emp Status],
1495 NULL [Tax Type],
1496 NULL [Gender],
1497 NULL [Civil Status],
1498 NULL [Emp Type],
1499 NULL [Factor Days],
1500 NULL [Date Hired],
1501 NULL [Date Separated],
1502 NULL [TIN],
1503 NULL [SSS],
1504 NULL [PHIC],
1505 NULL [HDMF],
1506 NULL [Bank Name],
1507 NULL [Account Type],
1508 NULL [Account No],
1509 TaxExemptionCode [Tax Code],
1510 BasicPay [Basic Pay],
1511 Overtime,
1512
1513 -- TaxableBonus [Taxable Bonus] ,
1514 GrossTaxableIncome [Gross Taxable Income],
1515 TotalIncome [Gross pay],
1516 WitholdingTax [WithholdingTax],
1517 [SSS Contribution],
1518 [PHIC Contribution],
1519 [HDMF Contribution],
1520 TotalDeductions [Total Deductions],
1521
1522 --NonTaxableBonus [Non Taxable Bonus] ,
1523 NetPay [Net Pay],
1524 [SSS Contribution - ER],
1525 [ECC],
1526 [PHIC Contribution - ER],
1527 [HDMF Contribution - ER],
1528 Division,
1529 SectionName,
1530 SiteName
1531 FROM
1532 (
1533 SELECT
1534 TranMainID,
1535 M.ClientEmpID,
1536 ClientEmpCode,
1537 FullName,
1538 CASE
1539 WHEN ISNULL(IsMinimumWage, 0) = 0
1540 THEN TaxExemptionCode
1541 ELSE
1542 CONCAT('MWE-', TaxExemptionCode)
1543 END TaxExemptionCode,
1544 BasicPay,
1545 Overtime,
1546
1547 --TaxableBonus ,
1548 GrossTaxableIncome,
1549 TotalIncome,
1550 WitholdingTax,
1551 TotalDeductions,
1552
1553 --NonTaxableBonus ,
1554 NetPay,
1555 Division,
1556 SectionName,
1557 SiteName
1558 FROM
1559 trn.tbl_TranMain M
1560 INNER JOIN
1561 dbo.tbl_Payout P
1562 ON P.PayoutID = M.PayoutID
1563 WHERE
1564 P.PayoutID = @payoutid
1565 AND
1566 (
1567 @employeeTypeUIDs IS NULL
1568 OR EmployeeTypeID IN (
1569 SELECT
1570 EmployeeTypeID
1571 FROM
1572 @employeeTypeIDList AL
1573 )
1574 )
1575 AND
1576 (
1577 @departmentUIDs IS NULL
1578 OR DepartmentID IN (
1579 SELECT
1580 DepartmentID
1581 FROM
1582 @departmentIDList AL
1583 )
1584 )
1585 ) main
1586 LEFT JOIN
1587 (
1588 SELECT
1589 *
1590 FROM
1591 @AllowDed
1592 ) Allowded
1593 ON main.ClientEmpCode = Allowded.[Employee Number];
1594
1595 --SELECT * FROM dbo.tbl_EmpType
1596 --------------------- List all Fields ----------------------------------
1597 IF OBJECT_ID('tempdb..#tempList') IS NOT NULL
1598 /*Then it exists*/
1599 DROP TABLE #tempList;
1600
1601 CREATE TABLE #tempList
1602 (
1603 ID INT IDENTITY(1, 1) PRIMARY KEY,
1604 Name VARCHAR(50)
1605 );
1606
1607 INSERT INTO #tempList
1608 SELECT
1609 name
1610 FROM
1611 tempdb.sys.columns
1612 WHERE
1613 name IN (
1614 'Employee Number'
1615 )
1616 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1617 INSERT INTO #tempList
1618 SELECT
1619 name
1620 FROM
1621 tempdb.sys.columns
1622 WHERE
1623 name IN (
1624 'Employee Name'
1625 )
1626 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1627
1628 --Allocation info
1629 INSERT INTO #tempList
1630 SELECT
1631 name
1632 FROM
1633 tempdb.sys.columns
1634 WHERE
1635 name IN (
1636 'Position'
1637 )
1638 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1639
1640 INSERT INTO #tempList
1641 SELECT
1642 name
1643 FROM
1644 tempdb.sys.columns
1645 WHERE
1646 name IN (
1647 'Site Name'
1648 )
1649 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1650
1651 INSERT INTO #tempList
1652 SELECT
1653 name
1654 FROM
1655 tempdb.sys.columns
1656 WHERE
1657 name IN (
1658 'Program Name'
1659 )
1660 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1661 INSERT INTO #tempList
1662 SELECT
1663 name
1664 FROM
1665 tempdb.sys.columns
1666 WHERE
1667 name IN (
1668 'Cost Allocation'
1669 )
1670 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1671 INSERT INTO #tempList
1672 SELECT
1673 name
1674 FROM
1675 tempdb.sys.columns
1676 WHERE
1677 name IN (
1678 'Co'
1679 )
1680 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1681 INSERT INTO #tempList
1682 SELECT
1683 name
1684 FROM
1685 tempdb.sys.columns
1686 WHERE
1687 name IN (
1688 'Lawson Site'
1689 )
1690 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1691 INSERT INTO #tempList
1692 SELECT
1693 name
1694 FROM
1695 tempdb.sys.columns
1696 WHERE
1697 name IN (
1698 'Dept'
1699 )
1700 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1701 INSERT INTO #tempList
1702 SELECT
1703 name
1704 FROM
1705 tempdb.sys.columns
1706 WHERE
1707 name IN (
1708 'Client'
1709 )
1710 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1711 INSERT INTO #tempList
1712 SELECT
1713 name
1714 FROM
1715 tempdb.sys.columns
1716 WHERE
1717 name IN (
1718 'IC'
1719 )
1720 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1721 INSERT INTO #tempList
1722 SELECT
1723 name
1724 FROM
1725 tempdb.sys.columns
1726 WHERE
1727 name IN (
1728 'IC Site'
1729 )
1730 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1731 INSERT INTO #tempList
1732 SELECT
1733 name
1734 FROM
1735 tempdb.sys.columns
1736 WHERE
1737 name IN (
1738 'Proj'
1739 )
1740 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1741 --End Allocation info
1742
1743 IF EXISTS
1744 (
1745 SELECT
1746 *
1747 FROM
1748 @registerDetailsList
1749 WHERE
1750 registerDetails IN (
1751 'Address'
1752 )
1753 )
1754 BEGIN
1755 INSERT INTO #tempList
1756 SELECT
1757 name
1758 FROM
1759 tempdb.sys.columns
1760 WHERE
1761 name IN (
1762 'Address'
1763 )
1764 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1765 END;
1766
1767 IF EXISTS
1768 (
1769 SELECT
1770 *
1771 FROM
1772 @registerDetailsList
1773 WHERE
1774 registerDetails IN (
1775 'Position'
1776 )
1777 )
1778 BEGIN
1779 INSERT INTO #tempList
1780 SELECT
1781 name
1782 FROM
1783 tempdb.sys.columns
1784 WHERE
1785 name IN (
1786 'Position'
1787 )
1788 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1789 END;
1790
1791 IF EXISTS
1792 (
1793 SELECT
1794 *
1795 FROM
1796 @registerDetailsList
1797 WHERE
1798 registerDetails IN (
1799 'Dept Code'
1800 )
1801 )
1802 BEGIN
1803 INSERT INTO #tempList
1804 SELECT
1805 name
1806 FROM
1807 tempdb.sys.columns
1808 WHERE
1809 name IN (
1810 'Department Code'
1811 )
1812 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1813 END;
1814
1815 IF EXISTS
1816 (
1817 SELECT
1818 *
1819 FROM
1820 @registerDetailsList
1821 WHERE
1822 registerDetails IN (
1823 'Dept Name'
1824 )
1825 )
1826 BEGIN
1827 INSERT INTO #tempList
1828 SELECT
1829 name
1830 FROM
1831 tempdb.sys.columns
1832 WHERE
1833 name IN (
1834 'Department Name'
1835 )
1836 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1837 END;
1838
1839 IF EXISTS
1840 (
1841 SELECT
1842 *
1843 FROM
1844 @registerDetailsList
1845 WHERE
1846 registerDetails IN (
1847 'Section Code'
1848 )
1849 )
1850 BEGIN
1851 INSERT INTO #tempList
1852 SELECT
1853 name
1854 FROM
1855 tempdb.sys.columns
1856 WHERE
1857 name IN (
1858 'Section Code'
1859 )
1860 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1861 END;
1862
1863 IF EXISTS
1864 (
1865 SELECT
1866 *
1867 FROM
1868 @registerDetailsList
1869 WHERE
1870 registerDetails IN (
1871 'Section Name'
1872 )
1873 )
1874 BEGIN
1875 INSERT INTO #tempList
1876 SELECT
1877 name
1878 FROM
1879 tempdb.sys.columns
1880 WHERE
1881 name IN (
1882 'Section Name'
1883 )
1884 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1885 END;
1886
1887 IF EXISTS
1888 (
1889 SELECT
1890 *
1891 FROM
1892 @registerDetailsList
1893 WHERE
1894 registerDetails IN (
1895 'Site Code'
1896 )
1897 )
1898 BEGIN
1899 INSERT INTO #tempList
1900 SELECT
1901 name
1902 FROM
1903 tempdb.sys.columns
1904 WHERE
1905 name IN (
1906 'Site Code'
1907 )
1908 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1909 END;
1910
1911 IF EXISTS
1912 (
1913 SELECT
1914 *
1915 FROM
1916 @registerDetailsList
1917 WHERE
1918 registerDetails IN (
1919 'Site Name'
1920 )
1921 )
1922 BEGIN
1923 INSERT INTO #tempList
1924 SELECT
1925 name
1926 FROM
1927 tempdb.sys.columns
1928 WHERE
1929 name IN (
1930 'Site Name'
1931 )
1932 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1933 END;
1934
1935 IF EXISTS
1936 (
1937 SELECT
1938 *
1939 FROM
1940 @registerDetailsList
1941 WHERE
1942 registerDetails IN (
1943 'Birthdate'
1944 )
1945 )
1946 BEGIN
1947 INSERT INTO #tempList
1948 SELECT
1949 name
1950 FROM
1951 tempdb.sys.columns
1952 WHERE
1953 name IN (
1954 'Birthdate'
1955 )
1956 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1957 END;
1958
1959 IF EXISTS
1960 (
1961 SELECT
1962 *
1963 FROM
1964 @registerDetailsList
1965 WHERE
1966 registerDetails IN (
1967 'Emp Group'
1968 )
1969 )
1970 BEGIN
1971 INSERT INTO #tempList
1972 SELECT
1973 name
1974 FROM
1975 tempdb.sys.columns
1976 WHERE
1977 name IN (
1978 'Emp Group'
1979 )
1980 AND object_id = OBJECT_ID('tempdb..#registerSummary');
1981 END;
1982
1983 IF EXISTS
1984 (
1985 SELECT
1986 *
1987 FROM
1988 @registerDetailsList
1989 WHERE
1990 registerDetails IN (
1991 'Emp Status'
1992 )
1993 )
1994 BEGIN
1995 INSERT INTO #tempList
1996 SELECT
1997 name
1998 FROM
1999 tempdb.sys.columns
2000 WHERE
2001 name IN (
2002 'Emp Status'
2003 )
2004 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2005 END;
2006
2007 IF EXISTS
2008 (
2009 SELECT
2010 *
2011 FROM
2012 @registerDetailsList
2013 WHERE
2014 registerDetails IN (
2015 'Tax Type'
2016 )
2017 )
2018 BEGIN
2019 INSERT INTO #tempList
2020 SELECT
2021 name
2022 FROM
2023 tempdb.sys.columns
2024 WHERE
2025 name IN (
2026 'Tax Type'
2027 )
2028 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2029 END;
2030
2031 IF EXISTS
2032 (
2033 SELECT
2034 *
2035 FROM
2036 @registerDetailsList
2037 WHERE
2038 registerDetails IN (
2039 'Gender'
2040 )
2041 )
2042 BEGIN
2043 INSERT INTO #tempList
2044 SELECT
2045 name
2046 FROM
2047 tempdb.sys.columns
2048 WHERE
2049 name IN (
2050 'Gender'
2051 )
2052 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2053 END;
2054
2055 IF EXISTS
2056 (
2057 SELECT
2058 *
2059 FROM
2060 @registerDetailsList
2061 WHERE
2062 registerDetails IN (
2063 'Civil Status'
2064 )
2065 )
2066 BEGIN
2067 INSERT INTO #tempList
2068 SELECT
2069 name
2070 FROM
2071 tempdb.sys.columns
2072 WHERE
2073 name IN (
2074 'Civil Status'
2075 )
2076 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2077 END;
2078
2079 IF EXISTS
2080 (
2081 SELECT
2082 *
2083 FROM
2084 @registerDetailsList
2085 WHERE
2086 registerDetails IN (
2087 'Emp Type'
2088 )
2089 )
2090 BEGIN
2091 INSERT INTO #tempList
2092 SELECT
2093 name
2094 FROM
2095 tempdb.sys.columns
2096 WHERE
2097 name IN (
2098 'Emp Type'
2099 )
2100 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2101 END;
2102
2103 IF EXISTS
2104 (
2105 SELECT
2106 *
2107 FROM
2108 @registerDetailsList
2109 WHERE
2110 registerDetails IN (
2111 'Factor Days'
2112 )
2113 )
2114 BEGIN
2115 INSERT INTO #tempList
2116 SELECT
2117 name
2118 FROM
2119 tempdb.sys.columns
2120 WHERE
2121 name IN (
2122 'Factor Days'
2123 )
2124 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2125 END;
2126
2127 IF EXISTS
2128 (
2129 SELECT
2130 *
2131 FROM
2132 @registerDetailsList
2133 WHERE
2134 registerDetails IN (
2135 'Date Hired'
2136 )
2137 )
2138 BEGIN
2139 INSERT INTO #tempList
2140 SELECT
2141 name
2142 FROM
2143 tempdb.sys.columns
2144 WHERE
2145 name IN (
2146 'Date Hired'
2147 )
2148 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2149 END;
2150
2151 IF EXISTS
2152 (
2153 SELECT
2154 *
2155 FROM
2156 @registerDetailsList
2157 WHERE
2158 registerDetails IN (
2159 'Date Separated'
2160 )
2161 )
2162 BEGIN
2163 INSERT INTO #tempList
2164 SELECT
2165 name
2166 FROM
2167 tempdb.sys.columns
2168 WHERE
2169 name IN (
2170 'Date Separated'
2171 )
2172 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2173 END;
2174
2175 IF EXISTS
2176 (
2177 SELECT
2178 *
2179 FROM
2180 @registerDetailsList
2181 WHERE
2182 registerDetails IN (
2183 'TIN'
2184 )
2185 )
2186 BEGIN
2187 INSERT INTO #tempList
2188 SELECT
2189 name
2190 FROM
2191 tempdb.sys.columns
2192 WHERE
2193 name IN (
2194 'TIN'
2195 )
2196 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2197 END;
2198
2199 IF EXISTS
2200 (
2201 SELECT
2202 *
2203 FROM
2204 @registerDetailsList
2205 WHERE
2206 registerDetails IN (
2207 'SSS'
2208 )
2209 )
2210 BEGIN
2211 INSERT INTO #tempList
2212 SELECT
2213 name
2214 FROM
2215 tempdb.sys.columns
2216 WHERE
2217 name IN (
2218 'SSS'
2219 )
2220 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2221 END;
2222
2223 IF EXISTS
2224 (
2225 SELECT
2226 *
2227 FROM
2228 @registerDetailsList
2229 WHERE
2230 registerDetails IN (
2231 'PHIC'
2232 )
2233 )
2234 BEGIN
2235 INSERT INTO #tempList
2236 SELECT
2237 name
2238 FROM
2239 tempdb.sys.columns
2240 WHERE
2241 name IN (
2242 'PHIC'
2243 )
2244 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2245 END;
2246
2247 IF EXISTS
2248 (
2249 SELECT
2250 *
2251 FROM
2252 @registerDetailsList
2253 WHERE
2254 registerDetails IN (
2255 'HDMF'
2256 )
2257 )
2258 BEGIN
2259 INSERT INTO #tempList
2260 SELECT
2261 name
2262 FROM
2263 tempdb.sys.columns
2264 WHERE
2265 name IN (
2266 'HDMF'
2267 )
2268 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2269 END;
2270
2271 IF EXISTS
2272 (
2273 SELECT
2274 *
2275 FROM
2276 @registerDetailsList
2277 WHERE
2278 registerDetails IN (
2279 'Bank Name'
2280 )
2281 )
2282 BEGIN
2283 INSERT INTO #tempList
2284 SELECT
2285 name
2286 FROM
2287 tempdb.sys.columns
2288 WHERE
2289 name IN (
2290 'Bank Name'
2291 )
2292 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2293 END;
2294
2295 IF EXISTS
2296 (
2297 SELECT
2298 *
2299 FROM
2300 @registerDetailsList
2301 WHERE
2302 registerDetails IN (
2303 'Account Type'
2304 )
2305 )
2306 BEGIN
2307 INSERT INTO #tempList
2308 SELECT
2309 name
2310 FROM
2311 tempdb.sys.columns
2312 WHERE
2313 name IN (
2314 'Account Type'
2315 )
2316 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2317 END;
2318
2319 IF EXISTS
2320 (
2321 SELECT
2322 *
2323 FROM
2324 @registerDetailsList
2325 WHERE
2326 registerDetails IN (
2327 'Account Number'
2328 )
2329 )
2330 BEGIN
2331 INSERT INTO #tempList
2332 SELECT
2333 name
2334 FROM
2335 tempdb.sys.columns
2336 WHERE
2337 name IN (
2338 'Account No'
2339 )
2340 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2341 END;
2342
2343 IF EXISTS
2344 (
2345 SELECT
2346 *
2347 FROM
2348 @registerDetailsList
2349 WHERE
2350 registerDetails IN (
2351 'Tax Code'
2352 )
2353 )
2354 BEGIN
2355 INSERT INTO #tempList
2356 SELECT
2357 name
2358 FROM
2359 tempdb.sys.columns
2360 WHERE
2361 name IN (
2362 'Tax Code'
2363 )
2364 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2365 END;
2366 --Jude End
2367
2368 INSERT INTO #tempList
2369 SELECT
2370 name
2371 FROM
2372 tempdb.sys.columns
2373 WHERE
2374 name IN (
2375 'Basic Pay'
2376 )
2377 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2378 INSERT INTO #tempList
2379 SELECT
2380 name
2381 FROM
2382 tempdb.sys.columns
2383 WHERE
2384 name IN (
2385 'Overtime'
2386 )
2387 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2388 INSERT INTO #tempList
2389 SELECT
2390 name
2391 FROM
2392 tempdb.sys.columns
2393 WHERE
2394 object_id = OBJECT_ID('tempdb..#temp_TaxableBonus');
2395 INSERT INTO #tempList
2396 SELECT
2397 name
2398 FROM
2399 tempdb.sys.columns
2400 WHERE
2401 object_id = OBJECT_ID('tempdb..#temp_TaxableEarnings');
2402
2403 INSERT INTO #tempList
2404 SELECT
2405 name
2406 FROM
2407 tempdb.sys.columns
2408 WHERE
2409 name IN (
2410 'Gross Taxable Income'
2411 )
2412 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2413 INSERT INTO #tempList
2414 SELECT
2415 name
2416 FROM
2417 tempdb.sys.columns
2418 WHERE
2419 object_id = OBJECT_ID('tempdb..#temp_nonTaxableBonus');
2420 INSERT INTO #tempList
2421 SELECT
2422 name
2423 FROM
2424 tempdb.sys.columns
2425 WHERE
2426 object_id = OBJECT_ID('tempdb..#temp_NonTaxableEarnings');
2427 INSERT INTO #tempList
2428 SELECT
2429 name
2430 FROM
2431 tempdb.sys.columns
2432 WHERE
2433 object_id = OBJECT_ID('tempdb..#temp_ReceivableEarnings');
2434 INSERT INTO #tempList
2435 SELECT
2436 name
2437 FROM
2438 tempdb.sys.columns
2439 WHERE
2440 name IN (
2441 'Gross Pay'
2442 )
2443 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2444 INSERT INTO #tempList
2445 SELECT
2446 name
2447 FROM
2448 tempdb.sys.columns
2449 WHERE
2450 name IN (
2451 'WithholdingTax'
2452 )
2453 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2454 INSERT INTO #tempList
2455 SELECT
2456 name
2457 FROM
2458 tempdb.sys.columns
2459 WHERE
2460 name IN (
2461 'SSS Contribution'
2462 )
2463 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2464 INSERT INTO #tempList
2465 SELECT
2466 name
2467 FROM
2468 tempdb.sys.columns
2469 WHERE
2470 name IN (
2471 'PHIC Contribution'
2472 )
2473 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2474 INSERT INTO #tempList
2475 SELECT
2476 name
2477 FROM
2478 tempdb.sys.columns
2479 WHERE
2480 name IN (
2481 'HDMF Contribution'
2482 )
2483 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2484 INSERT INTO #tempList
2485 SELECT
2486 name
2487 FROM
2488 tempdb.sys.columns
2489 WHERE
2490 object_id = OBJECT_ID('tempdb..#tempGov');
2491 INSERT INTO #tempList
2492 SELECT
2493 name
2494 FROM
2495 tempdb.sys.columns
2496 WHERE
2497 object_id = OBJECT_ID('tempdb..#tempCompany');
2498 INSERT INTO #tempList
2499 SELECT
2500 name
2501 FROM
2502 tempdb.sys.columns
2503 WHERE
2504 object_id = OBJECT_ID('tempdb..#tempDeduction');
2505 INSERT INTO #tempList
2506 SELECT
2507 name
2508 FROM
2509 tempdb.sys.columns
2510 WHERE
2511 name IN (
2512 'Total Deductions'
2513 )
2514 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2515
2516 INSERT INTO #tempList
2517 SELECT
2518 name
2519 FROM
2520 tempdb.sys.columns
2521 WHERE
2522 name IN (
2523 'Net Pay'
2524 )
2525 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2526 INSERT INTO #tempList
2527 SELECT
2528 name
2529 FROM
2530 tempdb.sys.columns
2531 WHERE
2532 name IN (
2533 'SSS Contribution - ER'
2534 )
2535 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2536 INSERT INTO #tempList
2537 SELECT
2538 name
2539 FROM
2540 tempdb.sys.columns
2541 WHERE
2542 name IN (
2543 'ECC'
2544 )
2545 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2546 INSERT INTO #tempList
2547 SELECT
2548 name
2549 FROM
2550 tempdb.sys.columns
2551 WHERE
2552 name IN (
2553 'PHIC Contribution - ER'
2554 )
2555 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2556 INSERT INTO #tempList
2557 SELECT
2558 name
2559 FROM
2560 tempdb.sys.columns
2561 WHERE
2562 name IN (
2563 'HDMF Contribution - ER'
2564 )
2565 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2566
2567 INSERT INTO #tempList
2568 SELECT
2569 name
2570 FROM
2571 tempdb.sys.columns
2572 WHERE
2573 name IN (
2574 'Department'
2575 )
2576 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2577
2578 INSERT INTO #tempList
2579 SELECT
2580 name
2581 FROM
2582 tempdb.sys.columns
2583 WHERE
2584 name IN (
2585 'Section'
2586 )
2587 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2588
2589 INSERT INTO #tempList
2590 SELECT
2591 name
2592 FROM
2593 tempdb.sys.columns
2594 WHERE
2595 name IN (
2596 'Site'
2597 )
2598 AND object_id = OBJECT_ID('tempdb..#registerSummary');
2599
2600
2601 SELECT
2602 Name Title,
2603 CASE
2604 WHEN Name IN (
2605 'Employee Number'
2606 )
2607 THEN 'VARCHAR(70)'
2608 WHEN Name LIKE ('Employee_code%')
2609 THEN 'VARCHAR(70)'
2610 WHEN Name LIKE ('costAllocID%')
2611 THEN 'VARCHAR(70)'
2612 WHEN Name = ('Tax Code')
2613 THEN 'VARCHAR(10)'
2614 WHEN Name = 'Employee Name'
2615 THEN 'VARCHAR(300)'
2616 WHEN Name IN ('Co', 'Lawson Site', 'Dept', 'Client', 'IC', 'IC Site', 'Proj', 'Cost Allocation')
2617 THEN 'VARCHAR(70)'
2618 WHEN Name IN (
2619 'Address', 'Position', 'Department Code', 'Department Name', 'Section Code',
2620 'Section Name', 'Site Name', 'Site Code', 'Emp Group', 'Emp Status', 'Tax Type',
2621 'Gender', 'Civil Status', 'Emp Type', 'Factor Days', 'TIN', 'SSS', 'PHIC', 'HDMF',
2622 'Bank Name', 'Account Type', 'Account No', 'Program Name'
2623 )
2624 THEN 'VARCHAR(MAX)'
2625 WHEN Name IN (
2626 'Birthdate', 'Date Hired', 'Date Separated'
2627 )
2628 THEN 'DATETIME'
2629 ELSE
2630 'DECIMAL (15,2)'
2631 END [datatype],
2632 CASE
2633 WHEN Name = 'Employee Name'
2634 THEN 300
2635 WHEN Name IN ('Program Name', 'Site Name', 'Position')
2636 THEN 200
2637 WHEN Name = 'Address'
2638 THEN 500
2639 WHEN Name LIKE 'Employee_code%'
2640 THEN 10
2641 WHEN Name LIKE 'costAllocID%'
2642 THEN 0
2643 WHEN Name IN (
2644 'Employee Number', 'Gender', 'Civil Status', 'Department Code', 'Section Code',
2645 'Factor Days', 'Account Type'
2646 )
2647 THEN 75
2648 WHEN Name = 'Tax Code'
2649 THEN 60
2650 WHEN Name IN ('Cost Allocation','Co', 'Lawson Site', 'Dept', 'Client', 'IC', 'IC Site', 'Proj')
2651 THEN 75
2652 WHEN Name = 'Bank name'
2653 THEN 180
2654 WHEN Name IN ('Department Name')
2655 THEN 130
2656 ELSE
2657 100
2658 END width,
2659 CASE
2660 WHEN Name IN (
2661 'Employee Name', 'Employee Number', 'Tax Code', 'Address', 'Position',
2662 'Department Code', 'Cost Allocation','Co', 'Lawson Site', 'Dept', 'Client', 'IC', 'IC Site', 'Proj', 'Program Name'
2663 )
2664 THEN NULL
2665 -- WHEN name LIKE 'Total%' THEN NULL
2666 WHEN Name LIKE ('Employee_code%')
2667 THEN NULL
2668 WHEN Name LIKE ('costAllocID%')
2669 THEN NULL
2670 ELSE
2671 'SUM'
2672 END AggregateFn
2673 FROM
2674 #tempList
2675 GROUP BY
2676 ID,
2677 Name
2678 ORDER BY
2679 ID,
2680 Name;
2681
2682
2683 --SELECT * FROM #tempList
2684
2685 --------------------- List all Fields ends here ----------------------------------
2686
2687 SELECT
2688 Main.ClientEmpID,
2689 Main.[Employee Number],
2690 Main.[Employee Name],
2691 vrd.Address [Address],
2692 vrd.PositionName [Position],
2693 vrd.[AllocName] [Program Name],
2694 CONCAT(CONVERT(DECIMAL(18,2),vrd.[AllocPercentage]), '%') [Cost Allocation],
2695 vrd.Segment1 [Co],
2696 vrd.Segment2 [Lawson Site],
2697 vrd.Segment3 [Dept],
2698 IIF(VRD.DeptType = 'OPS', vrd.Segment6, '000000') [Client],
2699 '000' [IC],
2700 '00000' [IC Site],
2701 '00000' [Proj],
2702 vrd.DepartmentCode [Deparment Code],
2703 vrd.DepartmentName [Deparment Name],
2704 vrd.SectionCode [Section Code],
2705 vrd.SectionName [Section Name],
2706 vrd.SiteCode [Site Code],
2707 vrd.SiteName [Site Name],
2708 vrd.Birthdate,
2709 vrd.ConfEmpGrpName [Emp Group],
2710 vrd.EmpStatusName [Emp Status],
2711 vrd.TaxTypeName [Tax Type],
2712 vrd.Gender,
2713 vrd.[Civil Status],
2714 vrd.EmpTypeName [Emp Type],
2715 vrd.[Factor Days],
2716 vrd.HiredOn [Date Hired],
2717 vrd.SeparatedOn [Date Separated],
2718 vrd.TIN,
2719 vrd.SSS,
2720 vrd.PHIC,
2721 vrd.HDMF,
2722 vrd.BankName [Bank Name],
2723 vrd.[Account Type] [Account Type],
2724 vrd.AccountNo [Account No],
2725 [Tax Code],
2726 ROUND(CONVERT(DECIMAL(15,5), Main.[Basic Pay]) / 100 * vrd.[AllocPercentage], 2) [Basic Pay],
2727 ROUND(CONVERT(DECIMAL(15,5), Main.[Overtime]) / 100 * vrd.[AllocPercentage], 2) [Overtime],
2728 OTaxE.*,
2729 TaxableBonus.*,
2730 ROUND(CONVERT(DECIMAL(15,5),[GROSS TAXABLE INCOME] / 100 * vrd.[AllocPercentage]), 2) [GROSS TAXABLE INCOME],
2731 ROUND(CONVERT(DECIMAL(15,5),[GROSS PAY] / 100 * vrd.[AllocPercentage]), 2) [GROSS PAY],
2732 ROUND(CONVERT(DECIMAL(15,5),[WithholdingTax] / 100 * vrd.[AllocPercentage]), 2) [WithholdingTax],
2733 ROUND(CONVERT(DECIMAL(15,5),[SSS Contribution] / 100 * vrd.[AllocPercentage]), 2) [SSS Contribution],
2734 ROUND(CONVERT(DECIMAL(15,5),[PHIC Contribution] / 100 * vrd.[AllocPercentage]), 2) [PHIC Contribution],
2735 ROUND(CONVERT(DECIMAL(15,5),[HDMF Contribution] / 100 * vrd.[AllocPercentage]), 2) [HDMF Contribution],
2736 GovLoan.*,
2737 CompanyLoan.*,
2738 CompanyDeduction.*,
2739 ROUND(CONVERT(DECIMAL(15,5),[TOTAL DEDUCTIONS] / 100 * vrd.[AllocPercentage]), 2) [TOTAL DEDUCTIONS],
2740 ONonTaxE.*,
2741 nonTaxableBonus.*,
2742 Receivable.*,
2743 ROUND(CONVERT(DECIMAL(15,5),[NET PAY] / 100 * vrd.[AllocPercentage]), 2) [NET PAY],
2744 ROUND(CONVERT(DECIMAL(15,5),[SSS Contribution - ER] / 100 * vrd.[AllocPercentage]), 2) [SSS Contribution - ER],
2745 ROUND(CONVERT(DECIMAL(15,5),[ECC] / 100 * vrd.[AllocPercentage]), 2) [ECC],
2746 ROUND(CONVERT(DECIMAL(15,5),[PHIC Contribution - ER] / 100 * vrd.[AllocPercentage]), 2) [PHIC Contribution - ER],
2747 ROUND(CONVERT(DECIMAL(15,5),[HDMF Contribution - ER] / 100 * vrd.[AllocPercentage]), 2) [HDMF Contribution - ER],
2748 REPLACE(Department, '&', '&') Department,
2749 Section,
2750 Site
2751 FROM
2752 #registerSummary Main
2753 LEFT JOIN
2754 dbo.vw_registerDetails_wCostAlloc AS vrd
2755 ON Main.[Employee Number] = vrd.EmpID
2756 LEFT JOIN
2757 #temp_TaxableEarnings OTaxE
2758 ON Main.[Employee Number] = OTaxE.[Employee_code1]
2759 AND (vrd.CostAllocID IS NULL) OR (OTaxE.costAllocID1 = vrd.CostAllocID)
2760 LEFT JOIN
2761 #temp_TaxableBonus TaxableBonus
2762 ON Main.[Employee Number] = TaxableBonus.[Employee_code31]
2763 AND (vrd.CostAllocID IS NULL) OR (TaxableBonus.costAllocID31 = vrd.CostAllocID)
2764 LEFT JOIN
2765 #temp_nontaxableBonus nonTaxableBonus
2766 ON Main.[Employee Number] = nonTaxableBonus.[Employee_code22]
2767 AND (vrd.CostAllocID IS NULL) OR (nonTaxableBonus.costAllocID22 = vrd.CostAllocID)
2768 LEFT JOIN
2769 #temp_NonTaxableEarnings ONonTaxE
2770 ON Main.[Employee Number] = ONonTaxE.[Employee_code2]
2771 AND (vrd.CostAllocID IS NULL) OR (ONonTaxE.costAllocID2 = vrd.CostAllocID)
2772 LEFT JOIN
2773 #tempGov GovLoan
2774 ON Main.[Employee Number] = GovLoan.[Employee_code3]
2775 AND (vrd.CostAllocID IS NULL) OR (GovLoan.costAllocID3 = vrd.CostAllocID)
2776 LEFT JOIN
2777 #tempCompany CompanyLoan
2778 ON Main.[Employee Number] = CompanyLoan.[Employee_code4]
2779 AND (vrd.CostAllocID IS NULL) OR (CompanyLoan.costAllocID4 = vrd.CostAllocID)
2780 LEFT JOIN
2781 #tempDeduction CompanyDeduction
2782 ON Main.[Employee Number] = CompanyDeduction.[Employee_code5]
2783 AND (VRD.CostAllocID IS NULL) OR (CompanyDeduction.costAllocID5 = vrd.CostAllocID)
2784 LEFT JOIN
2785 #temp_ReceivableEarnings Receivable
2786 ON Main.[Employee Number] = Receivable.[Employee_code6]
2787 AND (vrd.CostAllocID IS NULL) OR (Receivable.costAllocID6 = vrd.CostAllocID)
2788 WHERE
2789 vrd.ClientID = @clientID
2790 AND Main.ClientEmpID IN (
2791 SELECT
2792 CEIL.clientEmpID
2793 FROM
2794 #clientEmpID_list AS CEIL
2795 )
2796 ORDER BY
2797 Main.[Employee Name];
2798
2799 INSERT INTO @reportFooter
2800 (
2801 DataKey,
2802 DataValue,
2803 PosX,
2804 PosY
2805 )
2806 --VALUES(0,0,0,0)
2807 VALUES
2808 (
2809 'KEY0', 'Grand Total', 0, 1
2810 ),
2811 (
2812 'KEY1', '="Total Number of Employees" + ": " + Count(Fields.[Employee number])', 0, 1
2813 );
2814 --, ( 'KEY2', '=Count(Fields.[Employee number])', 370, 1 )
2815
2816
2817
2818 SELECT
2819 *
2820 FROM
2821 @reportFooter AS RF;
2822
2823
2824 DECLARE @reportSetting TABLE
2825 (
2826 Orientation NVARCHAR(30) NULL,
2827 SIZE NVARCHAR(30) NULL,
2828 Width NUMERIC(22, 8) NULL,
2829 Height NUMERIC(22, 8) NULL,
2830 MarginTop NUMERIC(22, 8) NULL,
2831 MarginBottom NUMERIC(22, 8) NULL,
2832 MarginLeft NUMERIC(22, 8) NULL,
2833 MarginRight NUMERIC(22, 8) NULL,
2834 scale FLOAT NULL
2835 );
2836
2837 INSERT INTO @reportSetting
2838 (
2839 Orientation,
2840 SIZE,
2841 Width,
2842 Height,
2843 MarginTop,
2844 MarginBottom,
2845 MarginLeft,
2846 MarginRight,
2847 scale
2848 )
2849 VALUES
2850 (
2851 'Landscape', 'Legal', '8.5', '13', '0.3', '0.5', '0.3', '0.3', '0'
2852 );
2853
2854
2855 SELECT
2856 *
2857 FROM
2858 @reportSetting AS RS;
2859
2860 --*/
2861
2862 END;
2863GO
2864