1 PACKAGE BODY pqp_absence_plan_process AS
2 /* $Header: pqabproc.pkb 120.0.12020000.2 2012/10/22 09:56:51 achandwa ship $ */
3 --
4 --
5 --
6 g_package_name VARCHAR2(31) := 'pqp_absence_plan_process.';
7 g_debug BOOLEAN:= hr_utility.debug_enabled;
8
9 --
10 --
11 --
12 CURSOR csr_absence_dates(p_absence_attendance_id IN NUMBER)
13 IS
14 SELECT paa.absence_attendance_id
15 ,paa.date_start
16 ,paa.date_end
17 ,paa.absence_attendance_type_id
18 FROM per_absence_attendances paa
19 WHERE paa.absence_attendance_id = p_absence_attendance_id;
20 --
21 --writting a seperate cursor to get absence type as this is required
22 -- only when there is a overlap.
23 CURSOR csr_absence_type(p_absence_attendance_type_id IN NUMBER)
24 IS
25 SELECT paat.NAME
26 FROM per_absence_attendance_types paat
27 WHERE paat.absence_attendance_type_id = p_absence_attendance_type_id ;
28 --
29 --
30 --
31 PROCEDURE debug(
32 p_trace_message IN VARCHAR2
33 ,p_trace_location IN NUMBER DEFAULT NULL
34 )
35 IS
36 BEGIN
37 pqp_utilities.debug(p_trace_message, p_trace_location);
38 END debug;
39
40 --
41 --
42 --
43 PROCEDURE debug(p_trace_number IN NUMBER)
44 IS
45 BEGIN
46 pqp_utilities.debug(fnd_number.number_to_canonical(p_trace_number));
47 END debug;
48
49 --
50 --
51 --
52 PROCEDURE debug(p_trace_date IN DATE)
53 IS
54 BEGIN
55 pqp_utilities.debug(fnd_date.date_to_canonical(p_trace_date));
56 END debug;
57
58 --
59 --
60 --
61 PROCEDURE debug_enter(
62 p_proc_name IN VARCHAR2
63 ,p_trace_on IN VARCHAR2 DEFAULT NULL
64 )
65 IS
66 -- l_trace_options VARCHAR2(200);
67 BEGIN
68 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
69 END debug_enter;
70
71 --
72 --
73 --
74 PROCEDURE debug_exit(
75 p_proc_name IN VARCHAR2
76 ,p_trace_off IN VARCHAR2 DEFAULT NULL
77 )
78 IS
79 BEGIN
80 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
81 END debug_exit;
82
83 --
84 --
85 PROCEDURE debug_others(
86 p_proc_name IN VARCHAR2
87 ,p_proc_step IN VARCHAR2 DEFAULT NULL
88 )
89 IS
90 BEGIN
91 pqp_utilities.debug_others(p_proc_name, p_proc_step);
92 END debug_others;
93 --
94 --
95 --
96 PROCEDURE clear_cache
97 IS
98 BEGIN
99 NULL;
100 END clear_cache;
101 --
102 --
103 --
104 PROCEDURE create_absence_plan_details(
105 p_person_id IN NUMBER
106 ,p_assignment_id IN NUMBER
107 ,p_business_group_id IN NUMBER
108 ,p_legislation_code IN VARCHAR2
109 ,p_effective_date IN DATE
110 ,p_element_type_id IN NUMBER
111 ,p_pl_id IN NUMBER
112 ,p_pl_typ_id IN NUMBER
113 ,p_ler_id IN NUMBER
114 ,p_per_in_ler_id IN NUMBER
115 ,p_absence_attendance_id IN NUMBER
116 ,p_effective_start_date IN DATE
117 ,p_effective_end_date IN DATE
118 ,p_formula_outputs IN ff_exec.outputs_t
119 ,p_error_code OUT NOCOPY NUMBER
120 ,p_error_message OUT NOCOPY VARCHAR2
121 )
122 IS
123 l_absence_dates csr_absence_dates%ROWTYPE;
124 l_proc_step NUMBER(20,10);
125 l_proc_name VARCHAR2(61)
126 := g_package_name || 'create_absence_plan_details';
127 l_is_overlapped BOOLEAN ;
128 l_absence_type csr_absence_type%ROWTYPE;
129
130 BEGIN
131 g_debug := hr_utility.debug_enabled;
132
133 IF g_debug THEN
134 debug_enter(l_proc_name);
135 END IF;
136 OPEN csr_absence_dates(p_absence_attendance_id);
137 FETCH csr_absence_dates INTO l_absence_dates;
138 CLOSE csr_absence_dates;
139 IF g_debug THEN
140 l_proc_step:=10;
141 debug(l_proc_name, l_proc_step);
142 END IF;
143
144 IF l_absence_dates.absence_attendance_id IS NOT NULL
145 THEN
146 IF g_debug THEN
147 l_proc_step:=20;
148 debug(l_proc_name, l_proc_step);
149 END IF;
150
151
152 l_is_overlapped := is_absence_overlapped
153 (
154 p_absence_attendance_id =>
155 l_absence_dates.absence_attendance_id
156 ) ;
157
158 IF l_is_overlapped THEN
159 IF g_debug THEN
160 l_proc_step := 21;
161 debug(l_proc_name, l_proc_step);
162 END IF;
163 -- get Absence Type to display in Error Message
164 OPEN csr_absence_type(l_absence_dates.absence_attendance_type_id);
165 FETCH csr_absence_type INTO l_absence_type ;
166 CLOSE csr_absence_type ;
167
168 fnd_message.set_name('PQP', 'PQP_230183_ABS_OVERLAP');
169 fnd_message.set_token('ABSTYPE',l_absence_type.name);
170 fnd_message.set_token('STARTDATE',l_absence_dates.date_start);
171 fnd_message.set_token('ENDDATE',l_absence_dates.date_end);
172 fnd_message.raise_error ;
173 END IF ;
174
175
176 IF p_legislation_code = 'GB'
177 THEN
178 IF g_debug THEN
179 l_proc_step:=30;
180 debug(l_proc_name, l_proc_step);
181 END IF;
182 pqp_gb_absence_plan_process.create_absence_plan_details(
183 p_person_id => p_person_id
184 ,p_assignment_id => p_assignment_id
185 ,p_business_group_id => p_business_group_id
186 ,p_legislation_code => p_legislation_code
187 ,p_effective_date => p_effective_date
188 ,p_element_type_id => p_element_type_id
189 ,p_pl_id => p_pl_id
190 ,p_pl_typ_id => p_pl_typ_id
191 ,p_ler_id => p_ler_id
192 ,p_per_in_ler_id => p_per_in_ler_id
193 ,p_absence_attendance_id => p_absence_attendance_id
194 ,p_absence_date_start => l_absence_dates.date_start
195 ,p_absence_date_end => NVL(
196 l_absence_dates.date_end
197 ,hr_api.g_eot
198 )
199 ,p_effective_start_date => p_effective_start_date
200 ,p_effective_end_date => p_effective_end_date
201 ,p_formula_outputs => p_formula_outputs
202 ,p_error_code => p_error_code
203 ,p_error_message => p_error_message
204 );
205 END IF; -- p_legislation_code = 'GB' then
206 END IF; -- IF l_absence_dates.absence_attendance_id IS NOT NULL
207
208 IF g_debug THEN
209 debug_exit(l_proc_name);
210 END IF;
211 EXCEPTION
212 WHEN OTHERS
213 THEN
214 IF SQLCODE <> hr_utility.hr_error_number
215 THEN
216 debug_others(l_proc_name, l_proc_step);
217
218 IF g_debug
219 THEN
220 debug('Leaving: ' || l_proc_name, -999);
221 END IF;
222
223 fnd_message.raise_error;
224 ELSE
225 RAISE;
226 END IF;
227 END create_absence_plan_details ;
228
229 --
230 --
231 --
232 PROCEDURE update_absence_plan_details(
233 p_person_id IN NUMBER
234 ,p_assignment_id IN NUMBER
235 ,p_business_group_id IN NUMBER
236 ,p_legislation_code IN VARCHAR2
237 ,p_effective_date IN DATE
238 ,p_element_type_id IN NUMBER
239 ,p_pl_id IN NUMBER
240 ,p_pl_typ_id IN NUMBER
241 ,p_ler_id IN NUMBER
242 ,p_per_in_ler_id IN NUMBER
243 ,p_absence_attendance_id IN NUMBER
244 ,p_effective_start_date IN DATE
245 ,p_effective_end_date IN DATE -- NULL 31-DEC-4712
246 ,p_formula_outputs IN ff_exec.outputs_t
247 ,p_error_code OUT NOCOPY NUMBER
248 ,p_error_message OUT NOCOPY VARCHAR2
249 )
250 IS
251 l_absence_dates csr_absence_dates%ROWTYPE;
252 l_proc_step NUMBER(20,10);
253 l_proc_name VARCHAR2(61):=
254 g_package_name || 'update_absence_plan_details';
255 l_is_overlapped BOOLEAN ;
256 l_absence_type csr_absence_type%ROWTYPE;
257
258 BEGIN
259 IF g_debug THEN
260 debug_enter(l_proc_name);
261 END IF;
262 OPEN csr_absence_dates(p_absence_attendance_id);
263 FETCH csr_absence_dates INTO l_absence_dates;
264 CLOSE csr_absence_dates;
265 IF g_debug THEN
266 l_proc_step:=10;
267 debug(l_proc_name, l_proc_step);
268 END IF;
269
270 IF l_absence_dates.absence_attendance_id IS NOT NULL -- absence not been deleted
271 THEN
272 IF g_debug THEN
273 l_proc_step:=20;
274 debug(l_proc_name, l_proc_step);
275 END IF;
276 -- Commenting the overlap check as this is triggered during backout processing
277 -- and will not allow backout of overlaps if there are any existing. ideally
278 -- once the overlap check is in place in create porcess there cant be any
279 -- overlaps processed in the data.
280 -- l_is_overlapped := is_absence_overlapped
281 -- (
282 -- p_absence_attendance_id => l_absence_dates.absence_attendance_id
283 -- ) ;
284 -- IF l_is_overlapped THEN
285 -- IF g_debug THEN
286 -- l_proc_step := 21;
287 -- debug(l_proc_name, l_proc_step);
288 -- END IF;
289 -- get Absence Type to display in Error Message
290 -- OPEN csr_absence_type(l_absence_dates.absence_attendance_type_id);
291 -- FETCH csr_absence_type INTO l_absence_type ;
292 -- CLOSE csr_absence_type ;
293
294 -- fnd_message.set_name('PQP', 'PQP_230183_ABS_OVERLAP');
295 -- fnd_message.set_token('ABSTYPE',l_absence_type.name);
296 -- fnd_message.set_token('STARTDATE',l_absence_dates.date_start);
297 -- fnd_message.set_token('ENDDATE',l_absence_dates.date_end);
298 -- fnd_message.raise_error ;
299 -- END IF ;
300
301
302 IF p_legislation_code = 'GB'
303 THEN
304 IF g_debug THEN
305 l_proc_step:=30;
306 debug(l_proc_name, l_proc_step);
307 END IF;
308 pqp_gb_absence_plan_process.update_absence_plan_details(
309 p_person_id => p_person_id
310 ,p_assignment_id => p_assignment_id
311 ,p_business_group_id => p_business_group_id
312 ,p_legislation_code => p_legislation_code
313 ,p_effective_date => p_effective_date
314 ,p_absence_attendance_id => p_absence_attendance_id
315 ,p_absence_date_start => l_absence_dates.date_start
316 ,p_absence_date_end => NVL(
317 l_absence_dates.date_end
318 ,hr_api.g_eot
319 )
320 ,p_pl_id => p_pl_id
321 ,p_pl_typ_id => p_pl_typ_id
322 ,p_element_type_id => p_element_type_id
323 ,p_effective_start_date => p_effective_start_date
324 ,p_effective_end_date => p_effective_end_date
325 ,p_ler_id => p_ler_id
326 ,p_per_in_ler_id => p_per_in_ler_id
327 ,p_formula_outputs => p_formula_outputs
328 ,p_error_code => p_error_code
329 ,p_error_message => p_error_message
330 );
331 END IF; -- IF p_legislation_code='GB' THEN
332 END IF; -- IF l_absence_dates.absence_attendance_id IS NOT NULL
333
334 IF g_debug THEN
335 debug_exit(l_proc_name);
336 END IF;
337 EXCEPTION
338 WHEN OTHERS
339 THEN
340 IF SQLCODE <> hr_utility.hr_error_number
341 THEN
342 debug_others(l_proc_name, l_proc_step);
343
344 IF g_debug
345 THEN
346 debug('Leaving: ' || l_proc_name, -999);
347 END IF;
348
349 fnd_message.raise_error;
350 ELSE
351 RAISE;
352 END IF;
353 END update_absence_plan_details;
354
355 --
356 --
357 --
358 PROCEDURE delete_absence_plan_details(
359 p_assignment_id IN NUMBER
360 ,p_business_group_id IN NUMBER
361 ,p_legislation_code IN VARCHAR2
362 ,p_effective_date IN DATE
363 ,p_pl_id IN NUMBER
364 ,p_pl_typ_id IN NUMBER
365 ,p_ler_id IN NUMBER
366 ,p_per_in_ler_id IN NUMBER
367 ,p_absence_attendance_id IN NUMBER
368 ,p_effective_start_date IN DATE
369 ,p_effective_end_date IN DATE -- NULL 31-DEC-4712
370 ,p_formula_outputs IN ff_exec.outputs_t
371 ,p_error_code OUT NOCOPY NUMBER
372 ,p_error_message OUT NOCOPY VARCHAR2
373 ,p_element_type_id IN NUMBER DEFAULT NULL
374 )
375 IS
376 l_proc_step NUMBER(20,10);
377 l_proc_name VARCHAR2(61)
378 := g_package_name || 'update_absence_plan_details';
379 BEGIN
380 IF g_debug THEN
381 debug_enter(l_proc_name);
382 END IF;
383
384 -- a delete at this level needs no check for absences start and end date
385 -- as this is only called when an absence has been physically deleted
386 -- where as the lower level counterparts of delete_absence_plan_details
387 -- get called from update also
388
389 IF p_legislation_code = 'GB'
390 THEN
391 l_proc_step:=10;
392 IF g_debug THEN
393 debug(l_proc_name, l_proc_step);
394 END IF;
395 pqp_gb_absence_plan_process.delete_absence_plan_details(
396 p_assignment_id => p_assignment_id
397 ,p_business_group_id => p_business_group_id
398 ,p_legislation_code => p_legislation_code
399 ,p_effective_date => p_effective_date
400 ,p_pl_id => p_pl_id
401 ,p_pl_typ_id => p_pl_typ_id
402 ,p_ler_id => p_ler_id
403 ,p_per_in_ler_id => p_per_in_ler_id
404 ,p_absence_attendance_id => p_absence_attendance_id
405 ,p_effective_start_date => p_effective_start_date
406 ,p_effective_end_date => p_effective_end_date
407 ,p_formula_outputs => p_formula_outputs
408 ,p_error_code => p_error_code
409 ,p_error_message => p_error_message
410 );
411 END IF; -- IF p_legislation_code='GB' THEN
412
413 IF g_debug THEN
414 debug_exit(l_proc_name);
415 END IF;
416
417 EXCEPTION
418 WHEN OTHERS
419 THEN
420 IF SQLCODE <> hr_utility.hr_error_number
421 THEN
422 debug_others(l_proc_name, l_proc_step);
423
424 IF g_debug
425 THEN
426 debug('Leaving: ' || l_proc_name, -999);
427 END IF;
428
429 fnd_message.raise_error;
430 ELSE
431 RAISE;
432 END IF;
433 END delete_absence_plan_details;
434
435
436 FUNCTION is_gap_absence_type(
437 p_absence_attendance_type_id IN NUMBER
438 )
439 RETURN NUMBER IS
440
441 CURSOR csr_gap_absence_type(p_absence_type_id NUMBER) IS
442 SELECT 1
443 FROM hr_lookups
444 WHERE lookup_type = 'PQP_GAP_ABSENCE_TYPES_LIST'
445 AND lookup_code = p_absence_type_id ;
446
447 l_exists NUMBER ;
448 l_proc_name VARCHAR2(61)
449 := g_package_name || 'is_gap_absence_type';
450 l_proc_step NUMBER(20,10);
451
452 BEGIN
453
454 IF g_debug THEN
455 debug_enter(l_proc_name);
456 debug('p_absence_attendance_type_id:'||p_absence_attendance_type_id);
457 END IF ;
458
459 l_exists := 0 ;
460
461 OPEN csr_gap_absence_type (p_absence_attendance_type_id) ;
462 FETCH csr_gap_absence_type INTO l_exists ;
463 CLOSE csr_gap_absence_type ;
464
465 IF l_exists = 1 THEN
466 l_proc_step := 10 ;
467 IF g_debug THEN
468 debug(l_proc_name,l_proc_step);
469 debug_exit(l_proc_name);
470 END IF;
471 RETURN 1 ;
472 ELSE
473 l_proc_step := 20 ;
474 IF g_debug THEN
475 debug(l_proc_name,l_proc_step);
476 debug_exit(l_proc_name);
477 END IF ;
478 RETURN 0 ;
479 END IF ;
480
481 EXCEPTION
482 WHEN OTHERS
483 THEN
484 IF SQLCODE <> hr_utility.hr_error_number
485 THEN
486 debug_others(l_proc_name, l_proc_step);
487
488 IF g_debug
489 THEN
490 debug('Leaving: ' || l_proc_name, -999);
491 END IF;
492
493 fnd_message.raise_error;
494 ELSE
495 RAISE;
496 END IF;
497
498 END is_gap_absence_type ;
499
500
501
502 FUNCTION is_absence_overlapped(
503 p_absence_attendance_id IN NUMBER
504 )
505 RETURN BOOLEAN IS
506
507 CURSOR csr_overlap_absence (p_absence_attendance_id NUMBER) IS
508 SELECT 1
509 FROM per_absence_attendances a, per_absence_attendance_types paat
510 WHERE a.absence_Attendance_id = p_absence_attendance_id
511 AND paat.absence_attendance_type_id = a. absence_attendance_type_id -- kvinayku
512 AND EXISTS ( SELECT 1
513 FROM per_absence_attendances b, per_absence_attendance_types paat1
514 WHERE a.person_id = b.person_id
515 AND (
516 (a.date_start BETWEEN b.date_start
517 AND NVL(b.date_end,hr_api.g_eot) )
518 OR (NVL(a.date_end,hr_api.g_eot) BETWEEN b.date_start
519 AND NVL(b.date_end,hr_api.g_eot) )
520 OR (b.date_start BETWEEN a.date_start
521 AND NVL(a.date_end,hr_api.g_eot))
522 )
523 AND is_gap_absence_type(b.absence_attendance_type_id) = 1
524 -- checking only for the absence_type_id in table, it is assumed that a
525 -- is the driving absence for which the process is triggered and if that
526 -- has reached this point of code means its a gap absence type
527 AND a.absence_attendance_id <> b.absence_attendance_id
528 AND paat1.absence_attendance_type_id = b. absence_attendance_type_id -- kvinayku
529 AND paat.absence_category = paat1.absence_category -- kvinayku
530 ) ;
531
532 l_exists NUMBER ;
533 l_proc_step NUMBER(20,10);
534 l_proc_name VARCHAR2(61)
535 := g_package_name || 'is_absence_overlapped';
536
537 BEGIN
538
539 IF g_debug THEN
540 debug_enter(l_proc_name) ;
541 debug('p_absence_attendance_id:'||p_absence_attendance_id);
542 END IF ;
543
544 OPEN csr_overlap_absence (p_absence_attendance_id) ;
545 FETCH csr_overlap_absence INTO l_exists ;
546 CLOSE csr_overlap_absence ;
547
548 IF l_exists = 1 THEN
549 l_proc_step := 10 ;
550 IF g_debug THEN
551 debug(l_proc_name,l_proc_step);
552 debug_exit(l_proc_name);
553 END IF ;
554 RETURN TRUE ;
555 ELSE
556 l_proc_step := 20 ;
557 IF g_debug THEN
558 debug(l_proc_name,l_proc_step);
559 debug_exit(l_proc_name);
560 END IF ;
561 RETURN FALSE ;
562 END IF ;
563
564 EXCEPTION
565 WHEN OTHERS
566 THEN
567 IF SQLCODE <> hr_utility.hr_error_number
568 THEN
569 debug_others(l_proc_name, l_proc_step);
570
571 IF g_debug
572 THEN
573 debug('Leaving: ' || l_proc_name, -999);
574 END IF;
575
576 fnd_message.raise_error;
577 ELSE
578 RAISE;
579 END IF;
580
581 END is_absence_overlapped ;
582
583 END pqp_absence_plan_process;
[Home] [Help]