1 PACKAGE BODY per_gb_absence_rules AS
2 /* $Header: pegbabsr.pkb 120.16 2010/08/10 07:33:35 npannamp noship $ */
3
4
5 -------------------------------------------------------------------------------
6 -- CHECK_ABS_OVERLAP
7 ---------------------------------------------------------------------------------
8 -- Bug 6708992
9 -- Procedure to raise error if overlapping absences of same type are present
10 /*
11 -- Bug 7447080
12 -- The below procedure is commented as c_abs_overlap_another cursor errors for different NLS calendar settings.
13 -- The cursor is modified so that the to_date and to_char functions are removed and handled in for loop of the cursor.
14 PROCEDURE check_abs_overlap( p_person_id IN NUMBER
15 ,p_date_start IN DATE
16 ,p_date_end IN DATE
17 ,p_time_start IN VARCHAR2
18 ,p_time_end IN VARCHAR2
19 ,p_absence_attendance_id IN NUMBER
20 --,p_absence_attendance_type_id IN NUMBER) IS --Absence category instead of Absence type 6888892
21 ,p_absence_category IN VARCHAR2) IS
22
23 -- 6888892 Changed this cursor, so that the check is based on Absence Categories
24 cursor c_abs_overlap_another is
25 select 1
26 from per_absence_attendances abs, per_absence_attendance_types paat
27 where paat.absence_category = p_absence_category
28 and paat.absence_attendance_type_id = abs.absence_attendance_type_id
29 and abs.person_id = p_person_id
30 and (p_absence_attendance_id is null or
31 p_absence_attendance_id <> abs.absence_attendance_id)
32 and abs.date_start is not null
33 and p_date_start is not null
34 and ((
35 to_date(to_char(abs.date_start,'YYYY-MM-DD')|| ' ' ||
36 nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
37 between
41 to_date(to_char(nvl(p_date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
38 to_date(to_char(p_date_start,'YYYY-MM-DD')|| ' ' ||
39 nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
40 AND
42 nvl(p_time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS'))
43 OR
44 (
45 to_date(to_char(p_date_start,'YYYY-MM-DD')|| ' ' ||
46 nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
47 between
48 to_date(to_char(abs.date_start,'YYYY-MM-DD')|| ' ' ||
49 nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
50 AND
51 to_date(to_char(nvl(abs.date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
52 nvl(abs.time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS')
53 )
54 );
55
56 l_exists NUMBER ;
57 BEGIN
58 open c_abs_overlap_another;
59 fetch c_abs_overlap_another INTO l_exists;
60 if c_abs_overlap_another%found then
61 close c_abs_overlap_another;
62 hr_utility.set_message(804,'SSP_35084_SIMILAR_ABS_OVERLAP');
63 hr_utility.raise_error;
64 else
65 close c_abs_overlap_another;
66 end if ;
67 END check_abs_overlap;
68 */
69 PROCEDURE check_abs_overlap( p_person_id IN NUMBER
70 ,p_date_start IN DATE
71 ,p_date_end IN DATE
72 ,p_time_start IN VARCHAR2
73 ,p_time_end IN VARCHAR2
74 ,p_absence_attendance_id IN NUMBER
75 --,p_absence_attendance_type_id IN NUMBER) IS --Absence category instead of Absence type 6888892
76 ,p_absence_category IN VARCHAR2) IS
77
78 cursor c_abs_overlap_another is
79 select nvl(abs.time_start,'00:00') start_time, nvl(abs.time_end,'23:59') end_time
80 from per_absence_attendances abs, per_absence_attendance_types paat
81 where paat.absence_category = p_absence_category
82 and paat.absence_attendance_type_id = abs.absence_attendance_type_id
83 and abs.person_id = p_person_id
84 and (p_absence_attendance_id is null or
85 p_absence_attendance_id <> abs.absence_attendance_id)
86 and abs.date_start is not null
87 and p_date_start is not null
88 and (abs.date_start between p_date_start AND nvl(p_date_end,hr_api.g_eot)
89 OR
90 p_date_start between abs.date_start and nvl(abs.date_end,hr_api.g_eot) )
91 order by 1;
92
93 b_over_lap boolean := false;
94
95 begin
96 hr_utility.trace('Entering check_abs_overlap ');
97 hr_utility.trace('check_abs_overlap p_person_id:'||p_person_id);
98 hr_utility.trace('check_abs_overlap p_date_start:'||p_date_start);
99 hr_utility.trace('check_abs_overlap p_date_end:'||p_date_end);
100 hr_utility.trace('check_abs_overlap p_time_start:'||nvl(p_time_start,'NULL'));
101 hr_utility.trace('check_abs_overlap p_time_end:'||nvl(p_time_end,'NULL'));
102 hr_utility.trace('check_abs_overlap p_absence_attendance_id:'||p_absence_attendance_id);
103 hr_utility.trace('check_abs_overlap hr_api.g_eot:'||hr_api.g_eot);
104
105 for i in c_abs_overlap_another
106 loop
107 hr_utility.trace('check_abs_overlap i.end_time:'||i.end_time);
108 if nvl(p_time_start,'00:00') <= i.end_time then
109 b_over_lap := true;
110 exit;
111 end if;
112 end loop;
113
114 if b_over_lap then
115 hr_utility.trace('check_abs_overlap Overlapping Exists');
116 hr_utility.set_message(804,'SSP_35084_SIMILAR_ABS_OVERLAP');
117 hr_utility.raise_error;
118 else
119 hr_utility.trace('check_abs_overlap No Overlap');
120 end if;
121 hr_utility.trace('check_abs_overlap Completed.');
122 exception
123 when others then
124 hr_utility.trace('check_abs_overlap Exception:'||sqlerrm);
125 raise;
126 end check_abs_overlap;
127 --
128 --
129
130 PROCEDURE sickness_date_update
131 (p_absence_attendance_id IN NUMBER
132 ) IS
133 --
134 l_proc VARCHAR2(30) ;
135 --
136 CURSOR get_abs_category IS
137 SELECT paat.absence_category
138 FROM per_absence_attendance_types paat,
139 per_absence_attendances paa
140 WHERE paa.absence_attendance_id = p_absence_attendance_id
141 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id;
142 --
143 l_abs_category per_absence_attendance_types.absence_category%TYPE;
144 --
145 BEGIN
146 --
147 -- Added for GSI Bug 5472781
148 --
149 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
150 --
151 l_proc := 'PER_GB_ABSENCE_RULES';
152
153 hr_utility.set_location('Entering:'|| l_proc, 10);
154 hr_utility.trace(l_proc||': Opening get_abs_category'||
155 ', p_absence_attendance_id='||p_absence_attendance_id);
156 --
157 -- Get absence_category
158 --
159 OPEN get_abs_category;
160 FETCH get_abs_category INTO l_abs_category;
161 CLOSE get_abs_category;
162 --
163 hr_utility.trace(l_proc||': Closed get_abs_category'||
164 ', l_abs_category='||l_abs_category);
165 --
166 --7157943 when this procedure is called from after delete hook l_abs_category will be NULL
167 --IF l_abs_category = 'S' THEN
168 IF nvl(l_abs_category,'S') = 'S' THEN
169 -- call recalculate_SSP_and_SMP to create/update stoppages and/or element entries.
170
171 ssp_smp_support_pkg.recalculate_SSP_and_SMP(p_deleting=>FALSE);
172
176 --
173 END IF;
174 --
175 END IF;
177 hr_utility.set_location('Leaving:'|| l_proc, 200);
178 --
179 END sickness_date_update;
180 --
181
182 -------------------------------------------------------------------------------
183 -- VALIDATE_ABS_CREATE
184 -------------------------------------------------------------------------------
185 PROCEDURE validate_abs_create(p_business_group_id IN NUMBER
186 ,p_person_id IN NUMBER
187 ,p_date_start IN DATE
188 ,p_date_end IN DATE -- Bug 6708992
189 ,p_time_start IN VARCHAR2 -- Bug 6708992
190 ,p_time_end IN VARCHAR2 -- Bug 6708992
191 ,p_absence_attendance_type_id IN NUMBER
192 ) IS
193
194 CURSOR get_abs_category IS
195 SELECT paat.absence_category
196 FROM per_absence_attendance_types paat
197 WHERE paat.absence_attendance_type_id = p_absence_attendance_type_id
198 AND paat.business_group_id = p_business_group_id;
199
200 CURSOR csr_absences
201 IS
202 SELECT 1
203 FROM per_absence_attendances PAA
204 WHERE PAA.person_id = p_person_id
205 and PAA.business_group_id = p_business_group_id
206 AND PAA.sickness_start_date is not null
207 AND p_date_start < (select max(ABS.sickness_start_date)
208 from per_absence_attendances ABS
209 where ABS.business_group_id = p_business_group_id
210 and ABS.person_id = p_person_id);
211
212 l_absence NUMBER;
213 l_abs_category per_absence_attendance_types.absence_category%TYPE;
214
215 BEGIN
216 --
217 -- Added for GSI Bug 5472781
218 --
219 hr_utility.trace(' Entering PER_GB_ABSENCE_RULES.VALIDATE_ABS_CREATE ');
220 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
221 --
222 -- Get absence_category
223 --
224 OPEN get_abs_category;
225 FETCH get_abs_category INTO l_abs_category;
226 CLOSE get_abs_category;
227 --
228 --
229 IF l_abs_category = 'S' THEN
230
231 OPEN csr_absences;
232 FETCH csr_absences INTO l_absence;
233 if csr_absences%found then
234 close csr_absences;
235 hr_utility.set_message(804,'SSP_35037_PIW_BROKEN');
236 hr_utility.raise_error;
237 else
238 close csr_absences;
239 end if;
240 END IF;
241
242 -- Bug 6708992
243 -- Raise error if overlapping absences of same type are present
244 -- Bug 6888892 begin
245 -- To check only in case below mentioned absence categories also
246 -- changed the check to be based on Absence Category
247 if l_abs_category in ('S','M','GB_ADO','GB_PAT_BIRTH','GB_PAT_ADO') then
248 check_abs_overlap( p_person_id, p_date_start, p_date_end
249 ,p_time_start, p_time_end, null
250 --,p_absence_attendance_type_id) ;
251 ,l_abs_category);
252 end if;
253 -- Bug 6888892 begin
254
255 END IF;
256 hr_utility.trace(' Leaving PER_GB_ABSENCE_RULES.VALIDATE_ABS_CREATE ');
257 END validate_abs_create;
258
259 -------------------------------------------------------------------------------
260 -- VALIDATE_ABS_UPDATE
261 -------------------------------------------------------------------------------
262 PROCEDURE validate_abs_update(p_date_start IN DATE,
263 p_date_end IN DATE,
264 p_time_start IN VARCHAR2, -- Bug 6708992
265 p_time_end IN VARCHAR2, -- Bug 6708992
266 p_sickness_start_date in date, -- Bug 9864927
267 p_sickness_end_date in date, -- Bug 9864927
268 p_absence_attendance_id IN NUMBER) IS
269
270 cursor csr_abs_details is
271 select absence_attendance_type_id,
272 business_group_id,
273 person_id,
274 sickness_start_date,
275 sickness_end_date
276 --7287548 begin
277 ,date_start,time_start,date_end,time_end
278 --7287548 End
279 from per_absence_attendances
280 where absence_attendance_id = p_absence_attendance_id;
281
282 CURSOR get_abs_category(p_abs_type_id number,
283 p_bus_group number) IS
284 SELECT paat.absence_category
285 FROM per_absence_attendance_types paat
286 WHERE paat.absence_attendance_type_id = p_abs_type_id
287 AND paat.business_group_id = p_bus_group;
288
289 CURSOR csr_absences(p_person_id number,
290 p_business_group_id number,
291 p_start date)IS
292 SELECT 1
293 FROM per_absence_attendances PAA
294 WHERE PAA.person_id = p_person_id
295 and PAA.business_group_id = p_business_group_id
296 AND PAA.sickness_start_date is not null
297 AND PAA.sickness_start_date > p_start
298 AND PAA.absence_attendance_id <> p_absence_attendance_id;
299
300 l_absence number;
301 l_business_group_id number;
302 l_person_id number;
303 l_abs_type_id number;
304 l_param_start date;
305 l_current_start date;
306 l_current_end date;
310 l_time_start varchar2(5);
307 l_abs_category per_absence_attendance_types.absence_category%TYPE;
308 --7287548 begin
309 l_date_start date;
311 l_date_end date;
312 l_time_end varchar2(5);
313 v_date_start date;
314 v_time_start varchar2(5);
315 v_date_end date;
316 v_time_end varchar2(5);
317 --7287548 End
318
319 BEGIN
320 --
321 hr_utility.trace(' Entering PER_GB_ABSENCE_RULES.VALIDATE_ABS_UPDATE ');
322 hr_utility.trace(' p_date_start '||p_date_start );
323 hr_utility.trace(' p_date_end '|| p_date_end );
324 hr_utility.trace(' p_time_start '||p_time_start);
325 hr_utility.trace(' p_time_end '|| p_time_end);
326 hr_utility.trace(' p_absence_attendance_id '||p_absence_attendance_id);
327 /* Commented the debugging message with to_date function.
328 hr_utility.trace(' From date '|| to_date(to_char(p_date_start,'YYYY-MM-DD')|| ' ' ||nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS'));
329 hr_utility.trace(' to date ' || to_date(to_char(nvl(p_date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||nvl(p_time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS'));
330 */
331 -- Added for GSI Bug 5472781
332 --
333 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
334 -- Get absence details
335 hr_utility.trace(' before opening cursor csr_abs_details');
336 open csr_abs_details;
337 fetch csr_abs_details into l_abs_type_id,
338 l_business_group_id,
339 l_person_id,
340 l_current_start,
341 l_current_end
342 --7287548 begin
343 ,l_date_start
344 ,l_time_start
345 ,l_date_end
346 ,l_time_end;
347 --7287548 End
348 close csr_abs_details;
349 --
350 hr_utility.trace(' after closing cursor csr_abs_details');
351
352 -- Check if default date is passed in for start/end date
353 -- logic
354 -- if not updating start or end date then skip
355 -- if not updating start or end date, but date is pass in then skip
356 -- if updating start/end and exists a future absence then error
357 if trunc(p_date_start) = trunc(hr_api.g_date) and
358 trunc(p_date_end) = trunc(hr_api.g_date) then
359 -- not updating sickness start/end date so skip
360 hr_utility.trace('Satisified IF');
361 null;
362 else
363 hr_utility.trace('Satisfied ELSE');
364 /* Bug Fix 9864927 Start
365 Scenario: Absence Start/End date need not be same as Sickness Start/End Date.
366 For ex., Absence End date can be one day more than the Sickness End Date.
367 So below condition modified.
368 if trunc(l_current_start) = trunc(p_date_start) and
369 trunc(l_current_end) = trunc(p_date_end) then
370 -- not updating sickness start/end date so skip */
371
372 if (
373 (trunc(l_current_start) = trunc(p_sickness_start_date) and
374 trunc(l_current_end) = trunc(p_sickness_end_date))
375 and
376 (trunc(l_date_start) = trunc(p_date_start) and
377 trunc(l_date_end) = trunc(p_date_end))
378 ) then
379 -- not updating sickness start/end date so skip
380 /* Bug Fix 9864927 End */
381 hr_utility.trace(' not updating sickness start/end date so skip ');
382 null;
383 else
384 -- going to update start or end date, check for future absence
385 -- Get absence_category
386 --
387 hr_utility.trace(' going to update start or end date, check for future absence ');
388
389 OPEN get_abs_category(l_abs_type_id, l_business_group_id);
390 FETCH get_abs_category INTO l_abs_category;
391 CLOSE get_abs_category;
392 --
393 --
394 hr_utility.trace(' after fetching abs category '||l_abs_category);
395
396 IF l_abs_category = 'S' THEN
397 hr_utility.trace(' for sickness S');
398 if trunc(p_date_start) = trunc(hr_api.g_date) then
399 l_param_start := trunc(l_current_start);
400 else
401 l_param_start := trunc(p_date_start);
402 end if;
403 hr_utility.trace(' before csr_absences open');
404 OPEN csr_absences(l_person_id, l_business_group_id,l_param_start);
405 FETCH csr_absences INTO l_absence;
406 if csr_absences%found then
407 close csr_absences;
408 hr_utility.set_message(804,'SSP_35037_PIW_BROKEN');
409 hr_utility.raise_error;
410 else
411 hr_utility.trace('csr_absences not found');
412 close csr_absences;
413 end if;
414 END IF;
415 end if;
416 end if;
417
418 --7287548 begin
419 If (p_date_start = hr_api.g_date) then
420 hr_utility.trace(' p_date_start value is defaulted '||p_date_start||' Repl by'||l_date_start);
421 v_date_start := l_date_start;
422 else
423 v_date_start := p_date_start;
424 End If;
425
426 If (p_date_end = hr_api.g_date) then
427 hr_utility.trace(' p_date_end value is defaulted '||p_date_end||' Repl by'||l_date_end);
428 v_date_end := l_date_end;
429 else
430 v_date_end := p_date_end;
431 End If;
432
433 If (p_time_start = hr_api.g_varchar2) then
434 hr_utility.trace(' p_time_start value is defaulted '||p_time_start||' Repl by'||l_time_start);
435 v_time_start := l_time_start;
436 else
437 v_time_start := p_time_start;
438 End If;
439
440 If (p_time_end = hr_api.g_varchar2) then
444 v_time_end := p_time_end;
441 hr_utility.trace(' p_time_end value is defaulted '||p_time_end||' Repl by'||l_time_end);
442 v_time_end := l_time_end;
443 else
445 End If;
446
447 --7287548 end
448
449
450 -- Bug 6708992
451 -- Raise error if overlapping absences of same type are present
452 -- Bug 6888892 begin
453 -- To check only in case below mentioned absence categories also
454 -- changed the check to be based on Absence Category
455 if l_abs_category in ('S','M','GB_ADO','GB_PAT_BIRTH','GB_PAT_ADO') then
456 hr_utility.trace(' calling check_abs_overlap');
457 check_abs_overlap( l_person_id, v_date_start, v_date_end
458 ,v_time_start, v_time_end, p_absence_attendance_id
459 -- ,l_abs_type_id) ;
460 ,l_abs_category);
461 hr_utility.trace(' After check_abs_overlap ');
462 end if;
463 -- Bug 6888892 begin
464
465 END IF;
466 hr_utility.trace('Leaving validate_abs_update ');
467 END validate_abs_update;
468
469 -------------------------------------------------------------------------------
470 -- VALIDATE_ABS_DELETE
471 -------------------------------------------------------------------------------
472 PROCEDURE validate_abs_delete(p_absence_attendance_id IN NUMBER
473 ) IS
474
475
476
477 CURSOR get_abs_category IS
478 SELECT paat.absence_category
479 FROM per_absence_attendance_types paat,
480 per_absence_attendances paa
481 WHERE paa.absence_attendance_id = p_absence_attendance_id
482 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id;
483
484
485 CURSOR csr_absences IS
486 SELECT 1
487 FROM per_absence_attendances PAA
488 WHERE PAA.absence_attendance_id = p_absence_attendance_id
489 AND PAA.sickness_start_date is not null
490 AND PAA.sickness_start_date < (select max(ABS.sickness_start_date)
491 from per_absence_attendances ABS
492 where ABS.person_id =PAA.person_id);
493
494 l_absence NUMBER;
495 l_abs_category per_absence_attendance_types.absence_category%TYPE;
496
497 --
498 BEGIN
499 --
500 -- Added for GSI Bug 5472781
501 --
502 hr_utility.trace(' Entering PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE ');
503 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
504 --
505 -- Get absence_category
506 --
507 hr_utility.trace('PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE : Fetching absence category');
508 OPEN get_abs_category;
509 FETCH get_abs_category INTO l_abs_category;
510 CLOSE get_abs_category;
511 hr_utility.trace('PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE : absence category :'||l_abs_category);
512
513 IF l_abs_category = 'S' THEN
514 OPEN csr_absences;
515 FETCH csr_absences INTO l_absence;
516 if csr_absences%found then
517 close csr_absences;
518 hr_utility.set_message(804,'SSP_35037_PIW_BROKEN');
519 hr_utility.raise_error;
520 else
521 close csr_absences;
522 end if;
523 END IF;
524 END IF;
525 hr_utility.trace(' Leaving PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE ');
526 END validate_abs_delete;
527 --
528 END per_gb_absence_rules;
[Home] [Help]