|
1
|
ALTER PROCEDURE CBIATMTOPYPANENTARIF
|
|
2
|
(
|
|
3
|
@EmployeeID VARCHAR(20)
|
|
4
|
,@StartDate VARCHAR(20)
|
|
5
|
,@EndDate VARCHAR(20)
|
|
6
|
,@WSType VARCHAR(20)
|
|
7
|
)
|
|
8
|
AS
|
|
9
|
|
|
10
|
--DECLARE @EmployeeID VARCHAR(20) = '06120011'
|
|
11
|
--DECLARE @StartDate VARCHAR(20) = '20221101'
|
|
12
|
--DECLARE @EndDate VARCHAR(20) = '20221130'
|
|
13
|
--DECLARE @WSType VARCHAR(20) = '5D8H'
|
|
14
|
|
|
15
|
DECLARE @wscal TABLE
|
|
16
|
(
|
|
17
|
EmployeeID VARCHAR(20)
|
|
18
|
,DateSpecified VARCHAR(20)
|
|
19
|
,DayType VARCHAR(20)
|
|
20
|
,FlagDay VARCHAR(20)
|
|
21
|
)
|
|
22
|
INSERT INTO @wscal
|
|
23
|
SELECT DISTINCT @EmployeeID, cal.DateSpecified, cal.DayType, dt.Flag
|
|
24
|
FROM dbo.PHRTMWSCAL AS cal
|
|
25
|
LEFT JOIN dbo.PHRTMDAYTYP AS dt
|
|
26
|
ON dt.DayType = cal.DayType
|
|
27
|
WHERE cal.WorkScheduleType = @WSType
|
|
28
|
AND (cal.DateSpecified BETWEEN @StartDate AND @EndDate)
|
|
29
|
|
|
30
|
DECLARE @MDPanen TABLE
|
|
31
|
(
|
|
32
|
StartDate VARCHAR(20)
|
|
33
|
,EndDate VARCHAR(20)
|
|
34
|
,EmployeeID VARCHAR(20)
|
|
35
|
,BlockID VARCHAR(20)
|
|
36
|
,TBS DECIMAL(18,2)
|
|
37
|
,BRD DECIMAL(18,2)
|
|
38
|
,DateSpecified VARCHAR(20)
|
|
39
|
,FlagDay VARCHAR(20)
|
|
40
|
,BJR DECIMAL(18,2)
|
|
41
|
,UpahPokok DECIMAL(22,0)
|
|
42
|
,PremiBasis DECIMAL(22,0)
|
|
43
|
,PremiBRD DECIMAL(22,0)
|
|
44
|
)
|
|
45
|
INSERT INTO @MDPanen
|
|
46
|
SELECT DISTINCT md.StartDate
|
|
47
|
,md.EndDate
|
|
48
|
,md.EmployeeID
|
|
49
|
,md.BlockID
|
|
50
|
,md.TBS
|
|
51
|
,md.BRD
|
|
52
|
,wscal.DateSpecified
|
|
53
|
,wscal.FlagDay
|
|
54
|
,periodbjr.bjr
|
|
55
|
,tarif.UpahPokok
|
|
56
|
,tarif.PremiBasis
|
|
57
|
,tarif.PremiBRD
|
|
58
|
FROM dbo.CUSPYPREMIPANEN AS md
|
|
59
|
LEFT JOIN @wscal AS wscal
|
|
60
|
ON md.EmployeeID = wscal.EmployeeID
|
|
61
|
LEFT JOIN dbo.CUSPERIODBJR AS periodbjr
|
|
62
|
ON md.BlockID = periodbjr.BlockID
|
|
63
|
LEFT JOIN dbo.CTARIFBRDPANEN AS tarif
|
|
64
|
ON tarif.StartDate <= @EndDate AND tarif.EndDate >= @EndDate
|
|
65
|
WHERE md.EmployeeID = @EmployeeID
|
|
66
|
AND (wscal.DateSpecified BETWEEN md.StartDate AND md.EndDate)
|
|
67
|
AND periodbjr.StartDate <= @EndDate AND periodbjr.EndDate >= @EndDate
|
|
68
|
|
|
69
|
DECLARE @DataAmountPanen TABLE
|
|
70
|
(
|
|
71
|
EmployeeID VARCHAR(20)
|
|
72
|
,DateSpecified VARCHAR(20)
|
|
73
|
,BlockID VARCHAR(20)
|
|
74
|
,FlagDay VARCHAR(20)
|
|
75
|
,TBD DECIMAL(22,0)
|
|
76
|
,BasisTandan DECIMAL(22,0)
|
|
77
|
,UpahPokok DECIMAL(22,0)
|
|
78
|
,PremiBasis DECIMAL(22,0)
|
|
79
|
,AmountUpahPokok DECIMAL(22,0)
|
|
80
|
,AmountPremiBasis DECIMAL(22,0)
|
|
81
|
,OverTBS DECIMAL(22,0)
|
|
82
|
,OverBasis1 DECIMAL(22,0)
|
|
83
|
,OverBasis2 DECIMAL(22,0)
|
|
84
|
,OverBasis3 DECIMAL(22,0)
|
|
85
|
,AmountOverBasis DECIMAL(22,0)
|
|
86
|
,BRD DECIMAL(22,0)
|
|
87
|
,PremiBRD DECIMAL(22,0)
|
|
88
|
,AmountBRD DECIMAL(22,0)
|
|
89
|
)
|
|
90
|
|
|
91
|
INSERT INTO @DataAmountPanen
|
|
92
|
SELECT DISTINCT mdpanen.EmployeeID, mdpanen.DateSpecified ,mdpanen.BlockID ,mdpanen.FlagDay
|
|
93
|
,mdpanen.TBS ,bjr.BasisTandan ,mdpanen.UpahPokok ,mdpanen.PremiBasis
|
|
94
|
,CASE
|
|
95
|
WHEN mdpanen.FlagDay <> 'FREE' THEN (CASE WHEN mdpanen.TBS < bjr.basisTandan THEN (mdpanen.TBS/bjr.BasisTandan) * mdpanen.UpahPokok ELSE mdpanen.UpahPokok END)
|
|
96
|
ELSE 0
|
|
97
|
END AS AmountUpahPokok
|
|
98
|
,CASE
|
|
99
|
WHEN mdpanen.FlagDay <> 'FREE' THEN (CASE WHEN mdpanen.TBS >= bjr.BasisTandan THEN mdpanen.PremiBasis ELSE 0 END)
|
|
100
|
ELSE 0
|
|
101
|
END AS AmountPremiBasis
|
|
102
|
,CASE WHEN mdpanen.TBS > bjr.BasisTandan THEN mdpanen.TBS - bjr.BasisTandan ELSE 0 END OverTBS
|
|
103
|
,CASE
|
|
104
|
WHEN mdpanen.FlagDay <> 'FREE' THEN (CASE WHEN mdpanen.TBS > bjr.OverBasis1Mula AND mdpanen.TBS <= bjr.OverBasis1Akhir THEN bjr.PremiOverBasis1 ELSE 0 END)
|
|
105
|
ELSE CASE WHEN mdpanen.TBS <= bjr.OverBasis1Akhir THEN bjr.PremiOverBasis1 ELSE 0 END
|
|
106
|
END AS OverBasis1
|
|
107
|
,CASE WHEN mdpanen.TBS > bjr.OverBasis2Mula AND mdpanen.TBS <= bjr.OverBasis2Akhir THEN bjr.PremiOverBasis2 ELSE 0 END OverBasis2
|
|
108
|
,CASE WHEN mdpanen.TBS > bjr.OverBasis3Mula AND mdpanen.TBS <= bjr.OverBasis3Akhir THEN bjr.PremiOverBasis3 ELSE 0 END OverBasis3
|
|
109
|
,CASE
|
|
110
|
WHEN mdpanen.FlagDay <> 'FREE' THEN
|
|
111
|
(CASE
|
|
112
|
WHEN mdpanen.TBS > bjr.OverBasis1Mula AND mdpanen.TBS <= bjr.OverBasis1Akhir THEN bjr.PremiOverBasis1
|
|
113
|
WHEN mdpanen.TBS > bjr.OverBasis2Mula AND mdpanen.TBS <= bjr.OverBasis2Akhir THEN bjr.PremiOverBasis2
|
|
114
|
WHEN mdpanen.TBS > bjr.OverBasis3Mula AND mdpanen.TBS <= bjr.OverBasis3Akhir THEN bjr.PremiOverBasis3
|
|
115
|
ELSE 0
|
|
116
|
END) * (CASE WHEN mdpanen.TBS > bjr.BasisTandan THEN mdpanen.TBS - bjr.BasisTandan ELSE 0 END)
|
|
117
|
ELSE
|
|
118
|
(CASE
|
|
119
|
WHEN mdpanen.TBS > bjr.OverBasis1Mula AND mdpanen.TBS <= bjr.OverBasis1Akhir THEN bjr.PremiOverBasis1
|
|
120
|
WHEN mdpanen.TBS > bjr.OverBasis2Mula AND mdpanen.TBS <= bjr.OverBasis2Akhir THEN bjr.PremiOverBasis2
|
|
121
|
WHEN mdpanen.TBS > bjr.OverBasis3Mula AND mdpanen.TBS <= bjr.OverBasis3Akhir THEN bjr.PremiOverBasis3
|
|
122
|
WHEN mdpanen.TBS <= bjr.BasisTandan THEN bjr.PremiOverBasis1
|
|
123
|
END) * mdpanen.TBS
|
|
124
|
END AS AmountOverBasis
|
|
125
|
,mdpanen.BRD ,mdpanen.PremiBRD
|
|
126
|
,(mdpanen.BRD * mdpanen.PremiBRD) AS AmountBRD
|
|
127
|
FROM @MDPanen AS mdpanen
|
|
128
|
LEFT JOIN dbo.CTARIFBJRPEMANEN AS bjr
|
|
129
|
ON (mdpanen.BJR > bjr.BjrMula AND mdpanen.BJR <= bjr.BjrAkhir)
|
|
130
|
|
|
131
|
SELECT DISTINCT DtPanen.EmployeeID
|
|
132
|
,SUM(DtPanen.AmountUpahPokok) AS AmountUpahPokok
|
|
133
|
,SUM(DtPanen.AmountPremiBasis) AS AmountPremiBasis
|
|
134
|
,SUM(DtPanen.AmountOverBasis) AS AmountUpahPokok
|
|
135
|
,SUM(DtPanen.AmountBRD) AS AmountBRD
|
|
136
|
,SUM(DtPanen.AmountUpahPokok) + SUM(DtPanen.AmountPremiBasis) + SUM(DtPanen.AmountOverBasis) + SUM(DtPanen.AmountBRD) AS TotalAmountPanen
|
|
137
|
FROM @DataAmountPanen AS DtPanen
|
|
138
|
GROUP BY DtPanen.EmployeeID
|
|
139
|
|