|
1
|
DECLARE @now VARCHAR(8)
|
|
2
|
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
|
|
3
|
|
|
4
|
|
|
5
|
DECLARE @t_emp TABLE
|
|
6
|
(
|
|
7
|
Rowse INT,
|
|
8
|
EmployeeID VARCHAR(80),
|
|
9
|
ExternalID VARCHAR(80),
|
|
10
|
FullName VARCHAR(MAX),
|
|
11
|
JoinDate VARCHAR(80),
|
|
12
|
Level VARCHAR(MAX),
|
|
13
|
Division VARCHAR(MAX),
|
|
14
|
LOB VARCHAR(MAX),
|
|
15
|
LOBc VARCHAR(MAX),
|
|
16
|
Divisionc VARCHAR(MAX),
|
|
17
|
EmpSubType VARCHAR(MAX),
|
|
18
|
Levelc VARCHAR(MAX)
|
|
19
|
|
|
20
|
)
|
|
21
|
|
|
22
|
|
|
23
|
|
|
24
|
DECLARE @t_emp_ABS TABLE
|
|
25
|
(
|
|
26
|
EmployeeID VARCHAR(80),
|
|
27
|
ExternalID VARCHAR(80),
|
|
28
|
FullName VARCHAR(MAX),
|
|
29
|
JoinDate VARCHAR(80),
|
|
30
|
Level VARCHAR(MAX),
|
|
31
|
Division VARCHAR(MAX),
|
|
32
|
LOB VARCHAR(MAX),
|
|
33
|
LOBc VARCHAR(MAX),
|
|
34
|
Divisionc VARCHAR(MAX),
|
|
35
|
EmpSubType VARCHAR(MAX),
|
|
36
|
Levelc VARCHAR(MAX),
|
|
37
|
AbsenceType VARCHAR(MAX),
|
|
38
|
AbstypeDesc VARCHAR(MAX),
|
|
39
|
AbsQuota VARCHAR(MAX),
|
|
40
|
QuotaDeduction VARCHAR(MAX)
|
|
41
|
)
|
|
42
|
|
|
43
|
|
|
44
|
DECLARE @t_emp_ABS_rows TABLE
|
|
45
|
(
|
|
46
|
Rowse INT,
|
|
47
|
notes VARCHAR(80),
|
|
48
|
EmployeeID VARCHAR(80),
|
|
49
|
ExternalID VARCHAR(80),
|
|
50
|
FullName VARCHAR(MAX),
|
|
51
|
JoinDate VARCHAR(80),
|
|
52
|
Level VARCHAR(MAX),
|
|
53
|
Division VARCHAR(MAX),
|
|
54
|
LOB VARCHAR(MAX),
|
|
55
|
LOBc VARCHAR(MAX),
|
|
56
|
Divisionc VARCHAR(MAX),
|
|
57
|
EmpSubType VARCHAR(MAX),
|
|
58
|
Levelc VARCHAR(MAX),
|
|
59
|
AbsenceType VARCHAR(MAX),
|
|
60
|
AbstypeDesc VARCHAR(MAX),
|
|
61
|
AbsQuota VARCHAR(MAX),
|
|
62
|
QuotaDeduction VARCHAR(MAX)
|
|
63
|
)
|
|
64
|
|
|
65
|
|
|
66
|
|
|
67
|
|
|
68
|
|
|
69
|
|
|
70
|
|
|
71
|
|
|
72
|
|
|
73
|
|
|
74
|
|
|
75
|
|
|
76
|
|
|
77
|
INSERT INTO @t_emp
|
|
78
|
SELECT DISTINCT
|
|
79
|
ROW_NUMBER() OVER(ORDER BY pa2.EmployeeID ASC) AS rw,
|
|
80
|
pa2.EmployeeID,
|
|
81
|
pa1.ExternalID,
|
|
82
|
pa1.FullName,
|
|
83
|
dbo.fn_formatdatetime(pa15.HiringDate, 'dd-mmm-yyyy') AS JoinDate,
|
|
84
|
pyg.PayrollGradeDescription AS LEVEL,
|
|
85
|
coalesce(div.DivDescription,'-') Division,
|
|
86
|
lob.LobDescription LOB,
|
|
87
|
pa2.LOB as LOBc,
|
|
88
|
pa2.Division Divisionc,
|
|
89
|
pa2.EmployeeSubType,
|
|
90
|
pa3.payrollgrade Levelc
|
|
91
|
FROM dbo.PHRPA0001 pa1
|
|
92
|
LEFT JOIN dbo.PHRPA0002 pa2
|
|
93
|
ON pa2.EmployeeID = pa1.EmployeeID
|
|
94
|
AND pa2.StartDate <= @now
|
|
95
|
AND pa2.EndDate >= @now
|
|
96
|
|
|
97
|
LEFT JOIN PHRPA0015 pa15
|
|
98
|
ON pa1.EmployeeID = pa15.EmployeeID
|
|
99
|
|
|
100
|
LEFT JOIN dbo.PHRPA0003 pa3
|
|
101
|
ON pa1.EmployeeID = pa3.EmployeeID
|
|
102
|
AND pa3.StartDate <= @now
|
|
103
|
AND pa3.EndDate >= @now
|
|
104
|
LEFT JOIN CHRPACOMBODIV div
|
|
105
|
ON pa2.Division = div.Div
|
|
106
|
LEFT JOIN CHRPACOMBOLOB lob
|
|
107
|
ON pa2.LOB = lob.Lob
|
|
108
|
LEFT JOIN PHRPYPGRAD pyg
|
|
109
|
ON pa3.PayrollGrade = pyg.PayrollGrade
|
|
110
|
|
|
111
|
ORDER BY EmployeeID AsC
|
|
112
|
|
|
113
|
DECLARE @Counter INT
|
|
114
|
SET @Counter=1
|
|
115
|
WHILE ( @Counter <= (SELECT MAX(Rowse) FROM @t_emp))
|
|
116
|
BEGIN
|
|
117
|
|
|
118
|
|
|
119
|
|
|
120
|
INSERT INTO @t_emp_ABS
|
|
121
|
|
|
122
|
SELECT (SELECT EmployeeID FROM @t_emp Where Rowse=@Counter),
|
|
123
|
(SELECT ExternalID FROM @t_emp Where Rowse=@Counter),
|
|
124
|
(SELECT FullName FROM @t_emp Where Rowse=@Counter),
|
|
125
|
(SELECT JoinDate FROM @t_emp Where Rowse=@Counter),
|
|
126
|
(SELECT Level FROM @t_emp Where Rowse=@Counter),
|
|
127
|
(SELECT Division FROM @t_emp Where Rowse=@Counter),
|
|
128
|
(SELECT LOB FROM @t_emp Where Rowse=@Counter),
|
|
129
|
(SELECT LOBc FROM @t_emp Where Rowse=@Counter),
|
|
130
|
(SELECT Divisionc FROM @t_emp Where Rowse=@Counter),
|
|
131
|
(SELECT EmpSubType FROM @t_emp Where Rowse=@Counter),
|
|
132
|
(SELECT Levelc FROM @t_emp Where Rowse=@Counter),
|
|
133
|
tabsence.AbsenceType ,
|
|
134
|
tabsence.AbsenceTypeDesc ,
|
|
135
|
tquota.AbsenceQuotaType ,
|
|
136
|
tabsence.QuotaDeduction
|
|
137
|
FROM dbo.PHRTMABSTYP AS tabsence
|
|
138
|
LEFT JOIN dbo.PHRTMQUOTYP AS tquota ON tquota.AbsenceQuotaType = tabsence.AbsenceQuotaType
|
|
139
|
WHERE tabsence.QuotaDeduction = 'N'
|
|
140
|
UNION
|
|
141
|
SELECT (SELECT EmployeeID FROM @t_emp Where Rowse=@Counter),
|
|
142
|
(SELECT ExternalID FROM @t_emp Where Rowse=@Counter),
|
|
143
|
(SELECT FullName FROM @t_emp Where Rowse=@Counter),
|
|
144
|
(SELECT JoinDate FROM @t_emp Where Rowse=@Counter),
|
|
145
|
(SELECT Level FROM @t_emp Where Rowse=@Counter),
|
|
146
|
(SELECT Division FROM @t_emp Where Rowse=@Counter),
|
|
147
|
(SELECT LOB FROM @t_emp Where Rowse=@Counter),
|
|
148
|
(SELECT LOBc FROM @t_emp Where Rowse=@Counter),
|
|
149
|
(SELECT Divisionc FROM @t_emp Where Rowse=@Counter),
|
|
150
|
(SELECT EmpSubType FROM @t_emp Where Rowse=@Counter),
|
|
151
|
(SELECT Levelc FROM @t_emp Where Rowse=@Counter),
|
|
152
|
tabsence.AbsenceType ,
|
|
153
|
tabsence.AbsenceTypeDesc ,
|
|
154
|
tquota.AbsenceQuotaType ,
|
|
155
|
tabsence.QuotaDeduction
|
|
156
|
FROM dbo.PHRTMABSTYP AS tabsence
|
|
157
|
LEFT JOIN dbo.PHRTMQUOTYP AS tquota ON tquota.AbsenceQuotaType = tabsence.AbsenceQuotaType
|
|
158
|
RIGHT JOIN dbo.PHRPA0017 AS md17 ON md17.AbsenceQuotaType = tabsence.AbsenceQuotaType
|
|
159
|
AND md17.EmployeeID = (SELECT EmployeeID FROM @t_emp Where Rowse=@Counter)
|
|
160
|
AND md17.StartDeduction <= @now
|
|
161
|
AND md17.EndDeduction >= @now
|
|
162
|
SET @Counter = @Counter + 1
|
|
163
|
|
|
164
|
END
|
|
165
|
|
|
166
|
|
|
167
|
|
|
168
|
INSERT INTO @t_emp_ABS_rows
|
|
169
|
|
|
170
|
SELECT ROW_NUMBER() OVER(ORDER BY tempabs.EmployeeID ASC) AS rw,'' as notes,* FROM @t_emp_ABS tempabs
|
|
171
|
|
|
172
|
DECLARE @Counter2 INT
|
|
173
|
SET @Counter2=1
|
|
174
|
WHILE ( @Counter2 <= (SELECT MAX(Rowse) FROM @t_emp_ABS_rows))
|
|
175
|
BEGIN
|
|
176
|
IF
|
|
177
|
(SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpLvStart =(SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)) IS NULL OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpLvStart =(SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)) < 0 OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpLvStart =(SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)) =''
|
|
178
|
BEGIN
|
|
179
|
UPDATE @t_emp_ABS_rows
|
|
180
|
SET notes = 'Tidak Ada Tipe Cuti ' + (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) + ' Untuk grade ' + (SELECT Levelc FROM @t_emp_ABS_rows Where Rowse=@Counter2)
|
|
181
|
WHERE Rowse = @Counter2
|
|
182
|
END
|
|
183
|
IF
|
|
184
|
|
|
185
|
|
|
186
|
(SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpSubType =(SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)) IS NULL OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpSubType =(SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)) < 0 OR (SELECT TOP(1)MaxNoApp FROM WFMATRAPP WHERE AbsenceType = (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) And EmpSubType =(SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)) =''
|
|
187
|
|
|
188
|
BEGIN
|
|
189
|
|
|
190
|
|
|
191
|
UPDATE @t_emp_ABS_rows
|
|
192
|
|
|
193
|
SET notes = 'Tidak Ada Tipe Cuti ' + (SELECT AbsenceType FROM @t_emp_ABS_rows Where Rowse=@Counter2) + ' Untuk EmployeeSubType' + (SELECT EmpSubType FROM @t_emp_ABS_rows Where Rowse=@Counter2)
|
|
194
|
|
|
195
|
|
|
196
|
WHERE Rowse = @Counter2
|
|
197
|
|
|
198
|
END
|
|
199
|
SET @Counter2 = @Counter2 + 1
|
|
200
|
END
|
|
201
|
|
|
202
|
|
|
203
|
SELECT * FROM @t_emp_ABS_rows WHERE notes <>''
|
