|
1
|
--USE [MinovaES_Rohto_Prod]
|
|
2
|
--GO
|
|
3
|
--/****** Object: StoredProcedure [dbo].[WFLeaveBlockDetail] Script Date: 26/07/2022 15:49:44 ******/
|
|
4
|
--SET ANSI_NULLS ON
|
|
5
|
--GO
|
|
6
|
--SET QUOTED_IDENTIFIER ON
|
|
7
|
--GO
|
|
8
|
----USE [MinovaHR_ESS_Indomobil_Production]
|
|
9
|
----GO
|
|
10
|
----/****** Object: StoredProcedure [dbo].[Wf_leave_block_detail] Script Date: 02/27/2017 19:01:31 ******/
|
|
11
|
----SET ANSI_NULLS ON
|
|
12
|
----GO
|
|
13
|
----SET QUOTED_IDENTIFIER ON
|
|
14
|
----GO
|
|
15
|
|
|
16
|
|
|
17
|
--ALTER PROCEDURE [dbo].[WFLeaveBlockDetail]
|
|
18
|
-- (
|
|
19
|
-- @empId AS VARCHAR(8) ,
|
|
20
|
-- @stLeave AS VARCHAR(8) ,
|
|
21
|
-- @endLeave AS VARCHAR(8) ,
|
|
22
|
-- @absenceType AS VARCHAR(8) ,
|
|
23
|
-- @regId AS INT ,
|
|
24
|
-- @langid AS VARCHAR(8) ,
|
|
25
|
-- @leaveBlockList AS VARCHAR(MAX)
|
|
26
|
-- )
|
|
27
|
--AS
|
|
28
|
|
|
29
|
DECLARE @empId AS VARCHAR(8) = '00000058'
|
|
30
|
DECLARE @stLeave AS VARCHAR(8) = '20220720'
|
|
31
|
DECLARE @endLeave AS VARCHAR(8) = '20220720'
|
|
32
|
DECLARE @absenceType AS VARCHAR(8) = '1000'
|
|
33
|
DECLARE @instance AS VARCHAR(5) = '00000'
|
|
34
|
DECLARE @regId AS VARCHAR(10) = '0'
|
|
35
|
DECLARE @langid AS VARCHAR(10) = 'EN'
|
|
36
|
|
|
37
|
|
|
38
|
----00006322 20140226
|
|
39
|
----19940006 20140301
|
|
40
|
----20040016 20140101
|
|
41
|
----20050010 20140301
|
|
42
|
|
|
43
|
|
|
44
|
|
|
45
|
DECLARE @quota_taken AS INT
|
|
46
|
DECLARE @quota_taken_ori AS INT
|
|
47
|
DECLARE @quota_type AS VARCHAR(8)
|
|
48
|
DECLARE @sdt VARCHAR(8)
|
|
49
|
DECLARE @edt VARCHAR(8)
|
|
50
|
DECLARE @sded VARCHAR(8)
|
|
51
|
DECLARE @eded VARCHAR(8)
|
|
52
|
DECLARE @seql VARCHAR(8)
|
|
53
|
DECLARE @oquot INT
|
|
54
|
DECLARE @rquot INT
|
|
55
|
DECLARE @rquot_total INT
|
|
56
|
DECLARE @lvlcont INT
|
|
57
|
DECLARE @qtaken INT
|
|
58
|
DECLARE @ws_type AS VARCHAR(20) = ''
|
|
59
|
/*declare all get data for frontend*/
|
|
60
|
DECLARE @main_leave_quota AS VARCHAR(20) = ''
|
|
61
|
DECLARE @main_remaining_quota AS VARCHAR(20) = ''
|
|
62
|
DECLARE @main_remaining_quota_prorate AS VARCHAR(20) = ''
|
|
63
|
DECLARE @main_quota_type AS VARCHAR(20) = ''
|
|
64
|
DECLARE @main_quota_start_date AS VARCHAR(20) = ''
|
|
65
|
DECLARE @main_quota_end_date AS VARCHAR(20) = ''
|
|
66
|
DECLARE @main_quota_seq AS VARCHAR(20) = ''
|
|
67
|
DECLARE @main_quota_sded AS VARCHAR(20) = ''
|
|
68
|
DECLARE @main_quota_eded AS VARCHAR(20) = ''
|
|
69
|
DECLARE @main_quota_lb_cont AS VARCHAR(20) = ''
|
|
70
|
DECLARE @main_total_remaining_quota INT
|
|
71
|
DECLARE @main_total_remaining_quota_prorate INT = 0
|
|
72
|
DECLARE @main_quota_taken INT
|
|
73
|
DECLARE @main_leave_quota_2 AS VARCHAR(20) = ''
|
|
74
|
DECLARE @main_remaining_quota_2 AS VARCHAR(20) = ''
|
|
75
|
DECLARE @main_remaining_quota_prorate_2 AS VARCHAR(20) = ''
|
|
76
|
DECLARE @main_quota_type_2 AS VARCHAR(20) = ''
|
|
77
|
DECLARE @main_quota_start_date_2 AS VARCHAR(20) = ''
|
|
78
|
DECLARE @main_quota_end_date_2 AS VARCHAR(20) = ''
|
|
79
|
DECLARE @main_quota_seq_2 AS VARCHAR(20) = ''
|
|
80
|
DECLARE @main_quota_sded_2 AS VARCHAR(20) = ''
|
|
81
|
DECLARE @main_quota_eded_2 AS VARCHAR(20) = ''
|
|
82
|
DECLARE @main_quota_lb_cont_2 AS VARCHAR(20) = ''
|
|
83
|
DECLARE @main_total_remaining_quota_2 AS INT
|
|
84
|
DECLARE @main_total_remaining_quota_prorate_2 AS INT
|
|
85
|
DECLARE @main_quota_taken_2 INT
|
|
86
|
DECLARE @Warning_Type AS VARCHAR(1) = 0
|
|
87
|
DECLARE @Warning_Msg AS VARCHAR(MAX) = ''
|
|
88
|
|
|
89
|
|
|
90
|
SELECT @ws_type = md25.WorkScheduleType
|
|
91
|
FROM PHRPA0025 md25
|
|
92
|
WHERE md25.EmployeeID = @empId
|
|
93
|
AND md25.StartDate <= @stLeave
|
|
94
|
AND md25.EndDate >= @stLeave
|
|
95
|
IF ( @ws_type != '' )
|
|
96
|
BEGIN
|
|
97
|
SET @main_total_remaining_quota = 0
|
|
98
|
SET @main_quota_taken = 0
|
|
99
|
SET @main_total_remaining_quota_2 = 0
|
|
100
|
SET @main_quota_taken_2 = 0
|
|
101
|
SELECT @quota_taken = COUNT(EmployeeID)
|
|
102
|
FROM PHRPA0025 md25 ,
|
|
103
|
dbo.PHRTMWSCAL c
|
|
104
|
WHERE md25.EmployeeID = @empId
|
|
105
|
AND md25.StartDate <= @stLeave
|
|
106
|
AND md25.EndDate >= @stLeave
|
|
107
|
AND c.WorkScheduleType = md25.WorkScheduleType
|
|
108
|
AND c.DateSpecified >= @stLeave
|
|
109
|
AND c.DateSpecified <= @endLeave
|
|
110
|
AND 'WORK' = ( SELECT TOP ( 1 )
|
|
111
|
Flag
|
|
112
|
FROM dbo.PHRTMDAYTYP t
|
|
113
|
WHERE t.DayType = c.DayType
|
|
114
|
)
|
|
115
|
SET @quota_taken_ori = @quota_taken
|
|
116
|
DECLARE @aq AS TABLE
|
|
117
|
(
|
|
118
|
id INT IDENTITY(1, 1) ,
|
|
119
|
sdate VARCHAR(8) ,
|
|
120
|
end_date VARCHAR(8) ,
|
|
121
|
start_deduction VARCHAR(8) ,
|
|
122
|
end_deduction VARCHAR(8) ,
|
|
123
|
seq VARCHAR(8) ,
|
|
124
|
original_quota INT ,
|
|
125
|
remaining_quota INT ,
|
|
126
|
lv_block_continue INT
|
|
127
|
)
|
|
128
|
SELECT @quota_type = qt.AbsenceQuotaType
|
|
129
|
FROM PHRTMABSTYP qt
|
|
130
|
WHERE qt.AbsenceType = @absenceType
|
|
131
|
DECLARE @list_regID AS TABLE ( regid INT )
|
|
132
|
DECLARE @rbizData AS TABLE
|
|
133
|
(
|
|
134
|
regid INT ,
|
|
135
|
fid VARCHAR(100) ,
|
|
136
|
val VARCHAR(100)
|
|
137
|
)
|
|
138
|
INSERT INTO @list_regID
|
|
139
|
SELECT dt.RegID
|
|
140
|
FROM dbo.PCMWFRUNMAIN AS main WITH ( NOLOCK ) ,
|
|
141
|
dbo.PCMWFRUNBIZDATA dt WITH ( NOLOCK )
|
|
142
|
WHERE main.RegID = dt.RegID
|
|
143
|
AND dt.RegID != @regId
|
|
144
|
AND EmpIDAs = @empId
|
|
145
|
AND ( main.BizflowID LIKE '%MD_Leave%' )
|
|
146
|
AND main.WorkflowStatus <> 'F'
|
|
147
|
AND dt.FieldID = 'AbsenceQuotaType'
|
|
148
|
AND dt.Value = @quota_type
|
|
149
|
AND dt.Sequence = ( SELECT MAX(dt2.Sequence)
|
|
150
|
FROM dbo.PCMWFRUNBIZDATA dt2
|
|
151
|
WHERE dt2.RegID = dt.RegID
|
|
152
|
AND dt2.FieldID = dt.FieldID
|
|
153
|
)
|
|
154
|
INSERT INTO @rbizData
|
|
155
|
SELECT dt.RegID ,
|
|
156
|
dt.FieldID ,
|
|
157
|
dt.Value
|
|
158
|
FROM @list_regID AS lr ,
|
|
159
|
dbo.PCMWFRUNBIZDATA dt WITH ( NOLOCK )
|
|
160
|
WHERE lr.regid = dt.RegID
|
|
161
|
AND dt.Sequence = ( SELECT MAX(dt2.Sequence)
|
|
162
|
FROM dbo.PCMWFRUNBIZDATA dt2
|
|
163
|
WHERE dt2.RegID = dt.RegID
|
|
164
|
AND dt2.FieldID = dt.FieldID
|
|
165
|
)
|
|
166
|
INSERT INTO @aq
|
|
167
|
SELECT StartDate ,
|
|
168
|
EndDate ,
|
|
169
|
StartDeduction ,
|
|
170
|
EndDeduction ,
|
|
171
|
Sequence ,
|
|
172
|
OriginalQuota ,
|
|
173
|
RemainQuota ,
|
|
174
|
0 AS lvblk
|
|
175
|
FROM dbo.PHRPA0017
|
|
176
|
WHERE EmployeeID = @empId
|
|
177
|
AND StartDeduction <= @stLeave
|
|
178
|
AND EndDeduction >= @stLeave
|
|
179
|
AND AbsenceQuotaType = @quota_type
|
|
180
|
DECLARE @aq_count AS INT
|
|
181
|
DECLARE @intFlag AS INT = 1
|
|
182
|
SELECT @aq_count = COUNT(start_deduction)
|
|
183
|
FROM @aq
|
|
184
|
DECLARE @lv_taken AS INT
|
|
185
|
|
|
186
|
WHILE ( @intFlag <= @aq_count )
|
|
187
|
BEGIN
|
|
188
|
SET @lv_taken = 0
|
|
189
|
SELECT @sdt = sdate ,
|
|
190
|
@edt = end_date ,
|
|
191
|
@sded = start_deduction ,
|
|
192
|
@eded = end_deduction ,
|
|
193
|
@seql = seq ,
|
|
194
|
@oquot = original_quota ,
|
|
195
|
@rquot = remaining_quota
|
|
196
|
FROM @aq
|
|
197
|
WHERE id = @intFlag
|
|
198
|
DECLARE @valwf INT
|
|
199
|
DECLARE @list_1 AS TABLE ( regid INT )
|
|
200
|
DECLARE @list_2 AS TABLE ( regid INT )
|
|
201
|
SET @lvlcont = 0;
|
|
202
|
DECLARE @cuti_taken_meded AS INT = 0
|
|
203
|
IF ( @eded < @endLeave
|
|
204
|
AND @intFlag = 1
|
|
205
|
AND @aq_count > 1
|
|
206
|
)
|
|
207
|
BEGIN
|
|
208
|
SELECT @cuti_taken_meded = COUNT(EmployeeID)
|
|
209
|
FROM dbo.PHRPA0025 md25 ,
|
|
210
|
dbo.PHRTMWSCAL c
|
|
211
|
WHERE md25.EmployeeID = @empId
|
|
212
|
AND md25.StartDate <= @stLeave
|
|
213
|
AND md25.EndDate >= @stLeave
|
|
214
|
AND c.WorkScheduleType = md25.WorkScheduleType
|
|
215
|
AND c.DateSpecified >= @stLeave
|
|
216
|
AND c.DateSpecified <= @eded
|
|
217
|
AND 'WORK' = ( SELECT TOP ( 1 )
|
|
218
|
Flag
|
|
219
|
FROM dbo.PHRTMDAYTYP t
|
|
220
|
WHERE t.DayType = c.DayType
|
|
221
|
)
|
|
222
|
SET @quota_taken_ori = @quota_taken
|
|
223
|
END
|
|
224
|
|
|
225
|
DECLARE @value AS TABLE ( val INT )
|
|
226
|
INSERT INTO @list_1
|
|
227
|
SELECT rb.regid
|
|
228
|
FROM @rbizData rb
|
|
229
|
WHERE rb.fid = 'QuotaStartDate'
|
|
230
|
AND rb.val = @sdt
|
|
231
|
INSERT INTO @value
|
|
232
|
SELECT rb.val
|
|
233
|
FROM @rbizData rb ,
|
|
234
|
@list_1 lr
|
|
235
|
WHERE rb.regid = lr.regid
|
|
236
|
AND rb.fid = 'QuotaTaken'
|
|
237
|
INSERT INTO @list_2
|
|
238
|
SELECT rb.regid
|
|
239
|
FROM @rbizData rb
|
|
240
|
WHERE rb.fid = 'QuotaStartDate2'
|
|
241
|
AND rb.val = @sdt
|
|
242
|
INSERT INTO @value
|
|
243
|
SELECT rb.val
|
|
244
|
FROM @rbizData rb ,
|
|
245
|
@list_2 lr
|
|
246
|
WHERE rb.regid = lr.regid
|
|
247
|
AND rb.fid = 'QuotaTaken2'
|
|
248
|
IF ( ( SELECT *
|
|
249
|
FROM @value
|
|
250
|
WHERE val >= 5
|
|
251
|
) IS NOT NULL )
|
|
252
|
BEGIN
|
|
253
|
SET @lvlcont = 1
|
|
254
|
END
|
|
255
|
DECLARE @num_blk AS INT
|
|
256
|
SELECT @num_blk = COUNT(y.StartDate)
|
|
257
|
FROM ( SELECT *
|
|
258
|
FROM ( SELECT md18.StartDate ,
|
|
259
|
md18.EndDate ,
|
|
260
|
( SELECT TOP ( 1 )
|
|
261
|
COUNT(c.DateSpecified)
|
|
262
|
FROM dbo.PHRPA0025 md25 ,
|
|
263
|
dbo.PHRTMWSCAL c
|
|
264
|
WHERE md25.EmployeeID = @empId
|
|
265
|
AND md25.StartDate <= md18.StartDate
|
|
266
|
AND md25.EndDate >= md18.StartDate
|
|
267
|
AND c.WorkScheduleType = md25.WorkScheduleType
|
|
268
|
AND c.DateSpecified >= md18.StartDate
|
|
269
|
AND c.DateSpecified <= md18.EndDate
|
|
270
|
AND 'WORK' = ( SELECT TOP ( 1 )
|
|
271
|
Flag
|
|
272
|
FROM
|
|
273
|
dbo.PHRTMDAYTYP t
|
|
274
|
WHERE
|
|
275
|
t.DayType = c.DayType
|
|
276
|
)
|
|
277
|
) AS c_wday
|
|
278
|
FROM dbo.PHRPA0018 md18 ,
|
|
279
|
dbo.PHRTMABSTYP at
|
|
280
|
WHERE md18.EmployeeID = @empId
|
|
281
|
AND md18.StartDate >= @sdt
|
|
282
|
AND md18.StartDate <= @edt
|
|
283
|
AND at.AbsenceQuotaType = @quota_type
|
|
284
|
AND at.AbsenceType = md18.AbsenceType
|
|
285
|
) x
|
|
286
|
WHERE x.c_wday >= 5
|
|
287
|
) y
|
|
288
|
IF ( @num_blk > 0 )
|
|
289
|
BEGIN
|
|
290
|
SET @lvlcont = 1
|
|
291
|
END
|
|
292
|
IF ( ( SELECT COUNT(val)
|
|
293
|
FROM @value
|
|
294
|
) > 0 )
|
|
295
|
BEGIN
|
|
296
|
SELECT @valwf = SUM(val)
|
|
297
|
FROM @value
|
|
298
|
SET @rquot = @rquot - @valwf
|
|
299
|
END
|
|
300
|
|
|
301
|
SET @rquot_total = @rquot
|
|
302
|
IF ( @intFlag = @aq_count )
|
|
303
|
BEGIN
|
|
304
|
SET @rquot_total = @rquot_total - @quota_taken
|
|
305
|
SET @qtaken = @quota_taken
|
|
306
|
END
|
|
307
|
ELSE
|
|
308
|
IF ( @rquot_total > @quota_taken )
|
|
309
|
BEGIN
|
|
310
|
IF ( @cuti_taken_meded > 0 )
|
|
311
|
BEGIN
|
|
312
|
SET @rquot_total = @rquot_total
|
|
313
|
- @cuti_taken_meded
|
|
314
|
SET @qtaken = @cuti_taken_meded
|
|
315
|
SET @quota_taken = @quota_taken
|
|
316
|
- @cuti_taken_meded
|
|
317
|
END
|
|
318
|
ELSE
|
|
319
|
BEGIN
|
|
320
|
SET @rquot_total = @rquot_total
|
|
321
|
- @quota_taken
|
|
322
|
SET @qtaken = @quota_taken
|
|
323
|
SET @quota_taken = 0
|
|
324
|
END
|
|
325
|
|
|
326
|
END
|
|
327
|
ELSE
|
|
328
|
IF ( @rquot_total < @quota_taken )
|
|
329
|
BEGIN
|
|
330
|
IF ( @rquot_total >= @cuti_taken_meded
|
|
331
|
AND @cuti_taken_meded > 0
|
|
332
|
)
|
|
333
|
BEGIN
|
|
334
|
SET @rquot_total = @rquot_total
|
|
335
|
- @cuti_taken_meded
|
|
336
|
SET @qtaken = @cuti_taken_meded
|
|
337
|
SET @quota_taken = @quota_taken
|
|
338
|
- @cuti_taken_meded
|
|
339
|
END
|
|
340
|
ELSE
|
|
341
|
IF ( @rquot_total < @cuti_taken_meded
|
|
342
|
AND @cuti_taken_meded > 0
|
|
343
|
)
|
|
344
|
BEGIN
|
|
345
|
SET @qtaken = @rquot_total
|
|
346
|
SET @quota_taken = @quota_taken
|
|
347
|
- @rquot_total
|
|
348
|
SET @rquot_total = 0
|
|
349
|
END
|
|
350
|
ELSE
|
|
351
|
BEGIN
|
|
352
|
SET @quota_taken = @quota_taken
|
|
353
|
- @rquot_total
|
|
354
|
SET @qtaken = @rquot_total
|
|
355
|
SET @rquot_total = 0
|
|
356
|
END
|
|
357
|
END
|
|
358
|
ELSE
|
|
359
|
IF ( @rquot_total = @quota_taken )
|
|
360
|
BEGIN
|
|
361
|
IF ( @rquot_total > @cuti_taken_meded
|
|
362
|
AND @cuti_taken_meded > 0
|
|
363
|
)
|
|
364
|
BEGIN
|
|
365
|
SET @rquot_total = @rquot_total
|
|
366
|
- @cuti_taken_meded
|
|
367
|
SET @qtaken = @cuti_taken_meded
|
|
368
|
SET @quota_taken = @quota_taken
|
|
369
|
- @cuti_taken_meded
|
|
370
|
END
|
|
371
|
ELSE
|
|
372
|
BEGIN
|
|
373
|
SET @qtaken = @quota_taken
|
|
374
|
SET @quota_taken = 0
|
|
375
|
SET @rquot_total = 0
|
|
376
|
END
|
|
377
|
END
|
|
378
|
DELETE FROM @value
|
|
379
|
DELETE FROM @list_1
|
|
380
|
DELETE FROM @list_2
|
|
381
|
IF ( @intFlag = 1 )
|
|
382
|
BEGIN
|
|
383
|
SET @main_leave_quota = @oquot
|
|
384
|
SET @main_remaining_quota = @rquot
|
|
385
|
SET @main_quota_type = @quota_type
|
|
386
|
SET @main_quota_start_date = @sdt
|
|
387
|
SET @main_quota_end_date = @edt
|
|
388
|
SET @main_quota_seq = @seql
|
|
389
|
SET @main_quota_sded = @sded
|
|
390
|
SET @main_quota_eded = @eded
|
|
391
|
IF ( @absenceType = '1000'
|
|
392
|
OR @absenceType = '1100'
|
|
393
|
OR @absenceType = '1200'
|
|
394
|
)
|
|
395
|
BEGIN
|
|
396
|
SET @main_quota_lb_cont = @lvlcont
|
|
397
|
END
|
|
398
|
ELSE
|
|
399
|
BEGIN
|
|
400
|
SET @main_quota_lb_cont = '1'
|
|
401
|
END
|
|
402
|
|
|
403
|
|
|
404
|
SET @main_total_remaining_quota = @rquot_total
|
|
405
|
SET @main_quota_taken = @qtaken
|
|
406
|
END
|
|
407
|
ELSE
|
|
408
|
BEGIN
|
|
409
|
SET @main_leave_quota_2 = @oquot
|
|
410
|
SET @main_remaining_quota_2 = @rquot
|
|
411
|
SET @main_quota_type_2 = @quota_type
|
|
412
|
SET @main_quota_start_date_2 = @sdt
|
|
413
|
SET @main_quota_end_date_2 = @edt
|
|
414
|
SET @main_quota_seq_2 = @seql
|
|
415
|
SET @main_quota_sded_2 = @sded
|
|
416
|
SET @main_quota_eded_2 = @eded
|
|
417
|
IF ( @absenceType = '1000'
|
|
418
|
OR @absenceType = '1100'
|
|
419
|
OR @absenceType = '1200'
|
|
420
|
)
|
|
421
|
BEGIN
|
|
422
|
SET @main_quota_lb_cont_2 = @lvlcont
|
|
423
|
END
|
|
424
|
ELSE
|
|
425
|
BEGIN
|
|
426
|
SET @main_quota_lb_cont_2 = '1'
|
|
427
|
END
|
|
428
|
|
|
429
|
SET @main_total_remaining_quota_2 = @rquot_total
|
|
430
|
SET @main_quota_taken_2 = @qtaken
|
|
431
|
END
|
|
432
|
SET @intFlag = @intFlag + 1
|
|
433
|
END
|
|
434
|
END
|
|
435
|
IF ( @main_leave_quota = '' )
|
|
436
|
BEGIN
|
|
437
|
SET @Warning_Type = 1
|
|
438
|
SET @Warning_Msg = ( SELECT MessageID
|
|
439
|
FROM dbo.SMESSAGECATALOG
|
|
440
|
WHERE MessageID = 'TMWSCEK'
|
|
441
|
AND LangId = @langid
|
|
442
|
)
|
|
443
|
END
|
|
444
|
/*START PRORATE CUTI MNC BY WARA 20170309*/
|
|
445
|
DECLARE @periodMonthLeave AS VARCHAR(2)= ( SELECT DATEPART(MM, @endLeave)
|
|
446
|
)
|
|
447
|
DECLARE @periodYearLeave AS VARCHAR(2)= ( SELECT DATEPART(YYYY,
|
|
448
|
@stLeave)
|
|
449
|
)
|
|
450
|
DECLARE @adadata AS INT = ( SELECT CAST(@main_leave_quota AS INT)
|
|
451
|
- ( SELECT CAST(@main_remaining_quota AS INT)
|
|
452
|
)
|
|
453
|
)
|
|
454
|
DECLARE @adadata2 AS INT = ( SELECT CAST(@main_leave_quota_2 AS INT)
|
|
455
|
- ( SELECT CAST(@main_remaining_quota_2 AS INT)
|
|
456
|
)
|
|
457
|
)
|
|
458
|
DECLARE @takeQuota AS VARCHAR(8)= ( DATEDIFF(yyyy, @endLeave,
|
|
459
|
@main_quota_eded) )
|
|
460
|
SET @main_remaining_quota_prorate_2 = ( SELECT @main_remaining_quota_2
|
|
461
|
)
|
|
462
|
--SELECT @main_quota_eded AS main_quota_eded
|
|
463
|
--SELECT @main_quota_eded_2 AS main_quota_eded_2
|
|
464
|
--SELECT @main_quota_sded AS main_quota_sded
|
|
465
|
--SELECT @main_quota_sded_2 AS main_quota_sded_2
|
|
466
|
--SELECT @endLeave AS tglcuti
|
|
467
|
--SELECT @takeQuota AS takeQuota
|
|
468
|
IF ( (@main_quota_sded_2 <> ''
|
|
469
|
AND @main_quota_eded_2 <> '')
|
|
470
|
)
|
|
471
|
BEGIN
|
|
472
|
SET @main_remaining_quota_prorate = ( SELECT @main_remaining_quota
|
|
473
|
)
|
|
474
|
SET @main_remaining_quota_prorate_2 = ( SELECT AccumulateAbsence
|
|
475
|
FROM dbo.CPHRTMABSPRT
|
|
476
|
WHERE AbsenceQuotaType = @main_quota_type_2
|
|
477
|
AND Month = @periodMonthLeave
|
|
478
|
) - @adadata2
|
|
479
|
END
|
|
480
|
ELSE
|
|
481
|
BEGIN
|
|
482
|
IF ( @takeQuota = '0' )
|
|
483
|
SET @main_remaining_quota_prorate = ( SELECT AccumulateAbsence
|
|
484
|
FROM dbo.CPHRTMABSPRT
|
|
485
|
WHERE AbsenceQuotaType = @main_quota_type
|
|
486
|
AND Month = '13'
|
|
487
|
) - @adadata
|
|
488
|
ELSE
|
|
489
|
SET @main_remaining_quota_prorate = ( SELECT AccumulateAbsence
|
|
490
|
FROM dbo.CPHRTMABSPRT
|
|
491
|
WHERE AbsenceQuotaType = @main_quota_type
|
|
492
|
AND Month = @periodMonthLeave
|
|
493
|
) - @adadata
|
|
494
|
END
|
|
495
|
SET @main_total_remaining_quota_prorate = ( SELECT @main_remaining_quota_prorate
|
|
496
|
- @main_quota_taken
|
|
497
|
)
|
|
498
|
SET @main_total_remaining_quota_prorate_2 = ( SELECT @main_remaining_quota_prorate_2
|
|
499
|
- @main_quota_taken_2
|
|
500
|
)
|
|
501
|
/*END*/
|
|
502
|
|
|
503
|
SELECT @quota_taken_ori TotalAbsenceTaken ,
|
|
504
|
@main_leave_quota OriginalQuota ,
|
|
505
|
@main_leave_quota_2 OriginalQuota2 ,
|
|
506
|
@main_remaining_quota RemainQuota ,
|
|
507
|
@main_remaining_quota_prorate RemainingQuotaProRate ,
|
|
508
|
@main_remaining_quota_2 RemainQuota2 ,
|
|
509
|
@main_remaining_quota_prorate_2 RemainingQuotaProRate2 ,
|
|
510
|
@main_quota_type AbsenceQuotaType ,
|
|
511
|
@main_quota_type_2 AbsenceQuotaType2 ,
|
|
512
|
@main_quota_start_date QuotaStartDate ,
|
|
513
|
@main_quota_start_date_2 QuotaStartDate2 ,
|
|
514
|
@main_quota_end_date QuotaEndDate ,
|
|
515
|
@main_quota_end_date_2 QuotaEndDate2 ,
|
|
516
|
@main_quota_seq QuotaSeq ,
|
|
517
|
@main_quota_seq_2 QuotaSeq2 ,
|
|
518
|
@main_quota_sded QuotaStDate ,
|
|
519
|
@main_quota_sded_2 QuotaStDate2 ,
|
|
520
|
@main_quota_eded QuotaEnDate ,
|
|
521
|
@main_quota_eded_2 QuotaEnDate2 ,
|
|
522
|
@main_quota_lb_cont QuotaBlockCont ,
|
|
523
|
@main_quota_lb_cont_2 QuotaBlockCont2 ,
|
|
524
|
@main_total_remaining_quota TotalRemainingQuota ,
|
|
525
|
@main_total_remaining_quota_prorate TotalRemainingQuotaProRate ,
|
|
526
|
@main_total_remaining_quota_2 TotalRemainingQuota2 ,
|
|
527
|
@main_total_remaining_quota_prorate_2 TotalRemainingQuotaProRate2 ,
|
|
528
|
@main_quota_taken QuotaTaken ,
|
|
529
|
@main_quota_taken_2 QuotaTaken2 ,
|
|
530
|
@Warning_Type WarningType ,
|
|
531
|
@Warning_Msg WarningText
|
|
532
|
|
