1 Package Body per_abs_bus as
2 /* $Header: peabsrhi.pkb 120.27.12020000.3 2012/11/05 11:44:22 srannama ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_abs_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_absence_attendance_id number default null;
15
16 --
17 -- ---------------------------------------------------------------------------
18 -- |---------------------< get_running_totals >----------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 -- Description:
22 -- This procedure gets the year to date totals and running totals for
23 -- both days and hours.
24 --
25 -- Pre-conditions:
26 -- None.
27 --
28 -- In Arguments:
29 -- p_person_id
30 -- p_absence_attendance_type_id
31 -- p_effective_date
32 --
33 -- Out Arguments:
34 -- p_running_total_hours
35 -- p_running_total_days
36 -- p_year_to_date_hours
37 -- p_year_to_date_days
38 --
39 -- Post Success:
40 -- If validation passes, processing continues.
41 --
42 -- Post Failure:
43 -- If validation fails, an error is raised and processing stops.
44 --
45 -- Access Status:
46 -- Internal Table Handler Use Only. API updating is not required as this
47 -- is called from other chk procedures that use API updating.
48 --
49 -- {End Of Comments}
50 -- ----------------------------------------------------------------------------
51 --
52 procedure get_running_totals
53 (p_person_id in number
54 ,p_absence_attendance_type_id in number
55 ,p_effective_date in date
56 ,p_running_total_hours out nocopy number
57 ,p_running_total_days out nocopy number
58 ,p_year_to_date_hours out nocopy number
59 ,p_year_to_date_days out nocopy number)
60 is
61
62 l_proc varchar2(72) := g_package||'get_running_totals';
63 l_absence_hours number;
64 l_absence_days number;
65 l_absence_year date;
66 l_effective_year date;
67 l_hours_or_days varchar2(1);
68 l_increasing_or_decreasing varchar2(1);
69 l_screen_entry_value number;
70 l_effective_start_date date;
71 l_effective_end_date date;
72
73 cursor c_get_running_totals is
74 select nvl(abs.absence_hours, 0),
75 nvl(abs.absence_days, 0),
76 to_date('01/01/'||
77 to_char(abs.date_end,'YYYY'),'DD/MM/YYYY'),
78 abt.hours_or_days,
79 abt.increasing_or_decreasing_flag
80 from per_absence_attendances abs,
81 per_absence_attendance_types abt
82 where abs.person_id = p_person_id
83 and abs.absence_attendance_type_id = abt.absence_attendance_type_id
84 and abs.date_end is not null
85 and abs.date_end <= p_effective_date
86 and abt.input_value_id is not null
87 and abt.input_value_id = (select abt2.input_value_id
88 from per_absence_attendance_types abt2
89 where abt2.absence_attendance_type_id
90 = p_absence_attendance_type_id);
91
92 cursor c_get_hours_or_days is
93 select abt.hours_or_days
94 from per_absence_attendance_types abt
95 where abt.absence_attendance_type_id = p_absence_attendance_type_id;
96
97 cursor c_get_upload_elements is
98 -- select /*+ leading(PAA) */ nvl(fnd_number.canonical_to_number(pev.screen_entry_value), 0),
99 select nvl(fnd_number.canonical_to_number(pev.screen_entry_value), 0), -- bug 7579341, hint removed
100 pev.effective_start_date,
101 pev.effective_end_date
102 from pay_element_entry_values_f pev,
103 pay_element_entries_f pee,
104 per_all_assignments_f paa,
105 per_absence_attendance_types abt
106 where pev.element_entry_id = pee.element_entry_id
107 and pev.input_value_id = abt.input_value_id
108 and pee.assignment_id = paa.assignment_id
109 and paa.person_id = p_person_id
110 and abt.absence_attendance_type_id = p_absence_attendance_type_id
111 -- and pee.creator_type <> 'A' -- Bug 4422696
112 and pee.creator_type not in('A','EE','NR','PR','R','RR') -- Bug 4422696
113 and pee.element_type_id =
114 (select pet.element_type_id
115 from pay_element_types_f pet,
116 pay_input_values_f piv
117 where abt.input_value_id = piv.input_value_id
118 and piv.element_type_id = pet.element_type_id
119 and p_effective_date between piv.effective_start_date
120 and piv.effective_end_date
121 and p_effective_date between pet.effective_start_date
122 and pet.effective_end_date)
123 and p_effective_date between paa.effective_start_date
124 and paa.effective_end_date
125 and paa.primary_flag = 'Y';
126
127 begin
128
129 hr_utility.set_location('Entering:'|| l_proc, 10);
130
131 --
132 -- Initialise all out parameters to zero.
133 --
134 p_running_total_hours := 0;
135 p_running_total_days := 0;
136 p_year_to_date_hours := 0;
137 p_year_to_date_days := 0;
138
139 --
140 -- Set the effective year.
141 --
142 l_effective_year := to_date('01/01/'||
143 to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY');
144
145 hr_utility.set_location(l_proc, 20);
146
147 --
148 -- Get the running totals for this element type for this person.
149 -- Both the year to dates and running totals are collected in the
150 -- same loop construct.
151 -- A loop is required because for each absence, its type could
152 -- have a different unit of measure and a different increasing
153 -- or decreasing flag.
154 --
155 open c_get_running_totals;
156 loop
157
158 fetch c_get_running_totals into l_absence_hours,
159 l_absence_days,
160 l_absence_year,
161 l_hours_or_days,
162 l_increasing_or_decreasing;
163
164 exit when c_get_running_totals%notfound;
165
166 if l_hours_or_days = 'H'
167 and l_increasing_or_decreasing = 'D' then
168
169 hr_utility.set_location(l_proc, 25);
170
171 p_running_total_hours := p_running_total_hours - l_absence_hours;
172
173 --
174 -- If the current absence is in the same year as the effective
175 -- date, the year to date balance is adjusted.
176 --
177 if l_absence_year = l_effective_year then
178 p_year_to_date_hours := p_year_to_date_hours - l_absence_hours;
179 end if;
180
181 elsif l_hours_or_days = 'H'
182 and l_increasing_or_decreasing = 'I' then
183
184 hr_utility.set_location(l_proc, 30);
185
186 p_running_total_hours := p_running_total_hours + l_absence_hours;
187
188 --
189 -- If the current absence is in the same year as the effective
190 -- date, the year to date balance is adjusted.
191 --
192 if l_absence_year = l_effective_year then
193 p_year_to_date_hours := p_year_to_date_hours + l_absence_hours;
194 end if;
195
196 elsif l_hours_or_days is null
197 and l_increasing_or_decreasing = 'I' then
198
199 hr_utility.set_location(l_proc, 35);
200
201 p_running_total_hours := p_running_total_hours + l_absence_hours;
202 p_running_total_days := p_running_total_days + l_absence_days;
203
204 --
205 -- If the current absence is in the same year as the effective
206 -- date, the year to date balance is adjusted.
207 --
208 if l_absence_year = l_effective_year then
209 p_year_to_date_hours := p_year_to_date_hours + l_absence_hours;
210 p_year_to_date_days := p_year_to_date_days + l_absence_days;
211 end if;
212
213 elsif l_hours_or_days = 'D'
214 and l_increasing_or_decreasing = 'D' then
215
216 hr_utility.set_location(l_proc, 40);
217
218 p_running_total_days := p_running_total_days - l_absence_days;
219
220 --
221 -- If the current absence is in the same year as the effective
222 -- date, the year to date balance is adjusted.
223 --
224 if l_absence_year = l_effective_year then
225 p_year_to_date_days := p_year_to_date_days - l_absence_days;
226 end if;
227
228 elsif l_hours_or_days = 'D'
229 and l_increasing_or_decreasing = 'I' then
230
231 hr_utility.set_location(l_proc, 45);
232
233 p_running_total_days := p_running_total_days + l_absence_days;
234
235 --
236 -- If the current absence is in the same year as the effective
237 -- date, the year to date balance is adjusted.
238 --
239 if l_absence_year = l_effective_year then
240 p_year_to_date_days := p_year_to_date_days + l_absence_days;
241 end if;
242
243 elsif l_hours_or_days is null
244 and l_increasing_or_decreasing = 'D' then
245
246 hr_utility.set_location(l_proc, 50);
247
248 p_running_total_hours := p_running_total_hours - l_absence_hours;
249 p_running_total_days := p_running_total_days - l_absence_days;
250
251 --
252 -- If the current absence is in the same year as the effective
253 -- date, the year to date balance is adjusted.
254 --
255 if l_absence_year = l_effective_year then
256 p_year_to_date_hours := p_year_to_date_hours - l_absence_hours;
257 p_year_to_date_days := p_year_to_date_days - l_absence_days;
258 end if;
259
260 end if;
261
262 end loop;
263 close c_get_running_totals;
264
265 hr_utility.set_location(l_proc, 55);
266
267 --
268 -- Is this absence type in hours or days?
269 --
270 open c_get_hours_or_days;
271 fetch c_get_hours_or_days into l_hours_or_days;
272 close c_get_hours_or_days;
273
274 hr_utility.set_location(l_proc, 60);
275
276 --
277 -- Add any upload elements to the balance. Upload elements
278 -- are elements linked to the absence type but do not
279 -- have an associated absence record. We ADD the upload
280 -- elements to the balance regardless of whether the
281 -- absence type is increasing or decreasing. This is
282 -- because the element could be linked to n number of absence
283 -- types (1/2 could be increasing and 1/2 could be decreasing).
284 --
285 open c_get_upload_elements;
286 loop
287
288 fetch c_get_upload_elements into l_screen_entry_value
289 ,l_effective_start_date
290 ,l_effective_end_date;
291
292 exit when c_get_upload_elements%notfound;
293
294 --
295 -- Add to the running total balance.
296 --
297 if l_hours_or_days = 'D' then
298
299 hr_utility.set_location(l_proc, 65);
300 p_running_total_days := p_running_total_days + l_screen_entry_value;
301
302 elsif l_hours_or_days = 'H' then
303
304 hr_utility.set_location(l_proc, 70);
305 p_running_total_hours := p_running_total_hours + l_screen_entry_value;
306
307 end if;
308
309 --
310 -- If its in the current year add the year to date balance.
311 --
312 if to_char(l_effective_start_date, 'YYYY') = to_char(p_effective_date, 'YYYY')
313 and to_char(l_effective_end_date, 'YYYY') = to_char(p_effective_date, 'YYYY')
314 then
315
316 hr_utility.set_location(l_proc, 75);
317
318 if l_hours_or_days = 'D' then
319 p_year_to_date_days := p_year_to_date_days + l_screen_entry_value;
320
321 elsif l_hours_or_days = 'H' then
322 p_year_to_date_hours := p_year_to_date_hours + l_screen_entry_value;
323
324 end if;
325
326 end if;
327
328 end loop;
329
330 hr_utility.set_location(l_proc, 80);
331 --
332 -- Null out any irrelevant balances.
333 --
334 if l_hours_or_days = 'D' then
335 p_running_total_hours := null;
336 p_year_to_date_hours := null;
337
338 elsif l_hours_or_days = 'H' then
339 p_running_total_days := null;
340 p_year_to_date_days := null;
341
342 else
343 p_running_total_hours := null;
344 p_year_to_date_hours := null;
345 p_running_total_days := null;
346 p_year_to_date_days := null;
347
348 end if;
349
350 hr_utility.set_location('Leaving:'|| l_proc, 85);
351
352 end get_running_totals;
353 --
354 -- ---------------------------------------------------------------------------
355 -- |---------------------< per_valid_for_absence >-------------------------|
356 -- ---------------------------------------------------------------------------
357 --
358 -- Description:
359 -- This function validates that the person exists and that they have
360 -- a valid period of service or period of placement for the entire absence
361 -- duration.
362 --
363 -- Pre-conditions:
364 -- None.
365 --
366 -- In Arguments:
367 -- p_person_id
368 -- p_business_group_id
369 -- p_person_type
370 -- p_date_projected_start
371 -- p_date_projected_end
372 -- p_date_start
373 -- p_date_end
374 --
375 -- Post Success:
376 -- If validation passes, the function returns TRUE.
377 --
378 -- Post Failure:
379 -- IF validation fails, the function returns FALSE.
380 --
381 -- Access Status:
382 -- Internal Table Handler Use Only. API updating is not required as this
383 -- is called from other chk procedures that use API updating.
384 --
385 -- {End Of Comments}
386 -- ----------------------------------------------------------------------------
387 function per_valid_for_absence
388 (p_person_id in number
389 ,p_business_group_id in number
390 ,p_date_projected_start in date
391 ,p_date_projected_end in date
392 ,p_date_start in date
393 ,p_date_end in date) return boolean
394 is
395
396 --
397 -- Check the person is valid for the entire absence term.
398 -- For contingent workers there is a join to per_all_workforce_v
399 -- so that contingent workers are only included when the profile
400 -- option HR_TREAT_CWK_AS_EMP is Yes.
401 --
402 cursor c_per_valid_for_absence is
403 select null
404 from per_all_people_f ppf,
405 per_periods_of_service pos
406 where ppf.person_id = p_person_id
407 and ppf.person_id = pos.person_id
408 and ppf.current_employee_flag = 'Y'
409 and ((nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' and
410 ppf.business_group_id = p_business_group_id)
411 or nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'Y')
412 and (p_date_projected_start is null or p_date_projected_start
413 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
414 and (p_date_projected_end is null or p_date_projected_end
415 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
416 and (p_date_start is null or p_date_start
417 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
418 and (p_date_end is null or p_date_end
419 between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
420 union select null
421 from per_all_people_f ppf,
422 per_periods_of_placement pop,
423 per_all_workforce_v pawv
424 where ppf.person_id = p_person_id
425 and ppf.person_id = pop.person_id
426 and ppf.person_id = pawv.person_id
427 and ppf.current_npw_flag = 'Y'
428 and ((nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' and
429 ppf.business_group_id = p_business_group_id)
430 or nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'Y')
431 and (p_date_projected_start is null or p_date_projected_start
432 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
433 and (p_date_projected_end is null or p_date_projected_end
434 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
435 and (p_date_start is null or p_date_start
436 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
437 and (p_date_end is null or p_date_end
438 between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot));
439
440 --
441 l_proc varchar2(72) := g_package||'per_valid_for_absence';
442 l_exists varchar2(1);
443 --
444
445 begin
446
447 hr_utility.set_location('Entering:'|| l_proc, 10);
448
449 --
450 -- Check that the person exists and that they have a valid period of
451 -- service for the entire absence duration.
452 --
453 open c_per_valid_for_absence;
454 fetch c_per_valid_for_absence into l_exists;
455
456 if c_per_valid_for_absence%found then
457 --
458 -- Person is found and they have a valid period of service.
459 --
460 return TRUE;
461 else
462 --
463 -- Person is invalid or the period of service is not valid
464 -- for the duration of the absence.
465 --
466 return FALSE;
467 end if;
468
469 close c_per_valid_for_absence;
470
471 --
472 hr_utility.set_location('Leaving:'|| l_proc, 20);
473
474 end per_valid_for_absence;
475 --
476 -- ---------------------------------------------------------------------------
477 -- |---------------------------< convert_to_minutes >-----------------------|
478 -- ---------------------------------------------------------------------------
479 --
480 -- Description:
481 -- Converts two times into duration minutes.
482 --
483 -- Pre-conditions:
484 --
485 -- In Arguments:
486 -- p_time_start
487 -- p_time_end
488 --
489 -- Post Success:
490 -- The function returns duration minutes and processing continues.
491 --
492 -- Post Failure:
493 -- The function errors and processing stops.
494 --
495 -- Access Status:
496 -- Internal Development Use Only.
497 --
498 -- {End Of Comments}
499 -- ----------------------------------------------------------------------------
500 function convert_to_minutes
501 (p_time_start in varchar2
502 ,p_time_end in varchar2) return number
503 is
504 --
505 l_proc varchar2(72) := g_package||'convert_to_minutes';
506 l_time_duration number;
507
508 cursor c_get_time_duration is
509 select ((substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2)) -
510 ((substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2))
511 from dual;
512 --
513
514 begin
515
516 hr_utility.set_location('Entering:'|| l_proc, 10);
517
518 --
519 -- Calculate the total time in minutes between the start time
520 -- and the end time.
521 --
522 open c_get_time_duration;
523 fetch c_get_time_duration into l_time_duration;
524 close c_get_time_duration;
525
526 hr_utility.set_location(' Leaving:'|| l_proc, 20);
527
528 return l_time_duration;
529
530 end convert_to_minutes;
531 --
532 -- ---------------------------------------------------------------------------
533 -- |---------------------------< chk_time_format >--------------------------|
534 -- ---------------------------------------------------------------------------
535 --
536 -- Description:
537 -- Checks that the time format is valid.
538 --
539 -- Pre-conditions:
540 --
541 -- In Arguments:
542 -- p_time
543 --
544 -- Post Success:
545 -- If the time format is valid, processing continues.
546 --
547 -- Post Failure:
548 -- If the time format is invalid processing stops and an error is raised.
549 --
550 -- Access Status:
551 -- Internal Development Use Only.
552 --
553 -- {End Of Comments}
554 -- ----------------------------------------------------------------------------
555 --
556 procedure chk_time_format
557 (p_time in varchar2)
558 is
559 --
560 l_proc varchar2(72) := g_package||'chk_time_format';
561 --
562
563 begin
564 hr_utility.set_location('Entering:'|| l_proc, 10);
565 --
566
567 if p_time is not null then
568 if not (substr(p_time,1,2) between '00' and '23'
569 and substr(p_time,4,2) between '00' and '59'
570 and substr(p_time,3,1) = ':'
571 and length(p_time) = 5) then
572 fnd_message.set_name('PAY','HR_6004_ALL_FORMAT_HHMM');
573 fnd_message.raise_error;
574 end if;
575 end if;
576
577 --
578 hr_utility.set_location(' Leaving:'|| l_proc, 50);
579 end chk_time_format;
580 --
581 -- +-------------------------------------------------------------------------+
582 -- |-----------------< good_time_format >-------------------------|
583 -- +-------------------------------------------------------------------------+
584 -- Description:
585 -- Tests CHAR values for valid time.
586 --
587 -- Pre-conditions:
588 -- None.
589 --
590 -- In Arguments:
591 -- p_time VARCHAR2
592 --
593 -- Out Arguments:
594 -- BOOLEAN
595 --
596 -- Post Success:
597 -- Returns TRUE or FALSE depending on valid time or not.
598 --
599 -- Post Failure:
600 -- Returns FALSE for invalid time.
601 --
602 -- Access Status:
603 -- Internal Development Use Only.
604 --
605 -- {End Of Comments}
606 -- ----------------------------------------------------------------------------
607 --
608 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
609 --
610 BEGIN
611 --
612 IF p_time IS NOT NULL THEN
613 --
614 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
615 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
616 SUBSTR(p_time,3,1) = ':' AND
617 LENGTH(p_time) = 5) THEN
618 RETURN FALSE;
619 ELSE
620 RETURN TRUE;
621 END IF;
622 --
623 ELSE
624 RETURN FALSE;
625 END IF;
626 --
627 EXCEPTION
628 --
629 WHEN OTHERS THEN
630 RETURN FALSE;
631 --
632 END good_time_format;
633 --
634 -- +-------------------------------------------------------------------------+
635 -- |-----------------< calc_sch_based_dur >-------------------------|
636 -- +-------------------------------------------------------------------------+
637 -- Description:
638 -- Calculate the absence duration in hours/days based on the work schedule.
639 --
640 -- Pre-conditions:
641 -- None.
642 --
643 -- In Arguments:
644 -- p_days_or_hours VARCHAR2
645 -- p_date_start DATE
646 -- p_date_end DATE
647 -- p_time_start VARCHAR2
648 -- p_time_end VARCHAR2
649 -- p_assignment_id NUMBER
650 --
651 -- Out Arguments:
652 -- p_duration NUMBER
653 --
654 -- Post Success:
655 -- Value returned for absence duration.
656 --
657 -- Post Failure:
658 -- If a failure occurs, an application error is raised and
659 -- processing terminates.
660 --
661 -- Access Status:
662 -- Internal Development Use Only.
663 --
664 -- {End Of Comments}
665 -- ----------------------------------------------------------------------------
666 --
667 PROCEDURE calc_sch_based_dur ( p_days_or_hours IN VARCHAR2,
668 p_date_start IN DATE,
669 p_date_end IN DATE,
670 p_time_start IN VARCHAR2,
671 p_time_end IN VARCHAR2,
672 p_assignment_id IN NUMBER,
673 p_duration IN OUT NOCOPY NUMBER
674 ) IS
675 --
676 l_idx NUMBER;
677 l_ref_date DATE;
678 l_first_band BOOLEAN;
679 l_day_start_time VARCHAR2(5);
680 l_day_end_time VARCHAR2(5);
681 l_start_time VARCHAR2(5);
682 l_end_time VARCHAR2(5);
683 --
684 l_start_date DATE;
685 l_end_date DATE;
686 l_schedule cac_avlblty_time_varray;
687 l_schedule_source VARCHAR2(10);
688 l_return_status VARCHAR2(1);
689 l_return_message VARCHAR2(2000);
690 --
691 l_time_start VARCHAR2(5);
692 l_time_end VARCHAR2(5);
693 --
694 e_bad_time_format EXCEPTION;
695 --
696 BEGIN
697 hr_utility.set_location('Entering '||g_package||'.calc_sch_based_dur',10);
698 p_duration := 0;
699 l_time_start := p_time_start;
700 l_time_end := p_time_end;
701 --
702 IF l_time_start IS NULL THEN
703 l_time_start := '00:00';
704 ELSE
705 IF NOT good_time_format(l_time_start) THEN
706 RAISE e_bad_time_format;
707 END IF;
708 END IF;
709 /*
710 IF l_time_end IS NULL THEN
711 l_time_end := '23:59'; --changed for bug #6274821
712 ELSE
713 IF NOT good_time_format(l_time_end) THEN
714 RAISE e_bad_time_format;
715 END IF;
716 END IF;
717 */
718 -- fix for the bug 6711896
719 IF l_time_end IS NULL THEN
720
721 IF p_days_or_hours = 'D' THEN
722 l_time_end := '00:00';
723 else
724 l_time_end := '23:59';
725 -- l_time_end := '00:00';
726 END IF;
727
728 ELSE
729 IF NOT good_time_format(l_time_end) THEN
730 RAISE e_bad_time_format;
731 END IF;
732 END IF;
733 --fix for the bug 6711896
734 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
735 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
736 IF p_days_or_hours = 'D' THEN
737 l_end_date := l_end_date + 1;
738 END IF;
739 --
740 -- Fetch the work schedule
741 --
742 hr_wrk_sch_pkg.get_per_asg_schedule
743 ( p_person_assignment_id => p_assignment_id
744 , p_period_start_date => l_start_date
745 , p_period_end_date => l_end_date
746 , p_schedule_category => NULL
747 , p_include_exceptions => 'Y'
748 , p_busy_tentative_as => 'FREE'
749 , x_schedule_source => l_schedule_source
750 , x_schedule => l_schedule
751 , x_return_status => l_return_status
752 , x_return_message => l_return_message
753 );
754 --
755 IF l_return_status = '0' THEN
756 --
757 -- Calculate duration
758 --
759 l_idx := l_schedule.first;
760 --
761 IF p_days_or_hours = 'D' THEN
762 --
763 l_first_band := TRUE;
764 l_ref_date := NULL;
765 WHILE l_idx IS NOT NULL
766 LOOP
767 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
768 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
769 IF l_first_band THEN
770 l_first_band := FALSE;
771 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
772 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
773 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
774 ELSE
775 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
776 END IF;
777 ELSE -- not first time
778 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
779 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
780 ELSE
781 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
782 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
783 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
784 ELSE
785 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
786 END IF;
787 END IF;
788 END IF;
789 END IF;
790 END IF;
791 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
792 END LOOP;
793 --
794 ELSE -- p_days_or_hours is 'H'
795 --
796 l_day_start_time := '00:00';
797 l_day_end_time := '23:59';
798 WHILE l_idx IS NOT NULL
799 LOOP
800 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
801 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
802 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
803 -- Skip this invalid slot which ends before it starts
804 NULL;
805 ELSE
806 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
807 -- Start and End on different days
808 --
809 -- Get first day hours
810 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
811 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
812 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
813 INTO p_duration
814 FROM DUAL;
815 --
816 -- Get last day hours
817 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
818 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
819 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
820 INTO p_duration
821 FROM DUAL;
822 --
823 -- Get between full day hours
824 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
825 INTO p_duration
826 FROM DUAL;
827 ELSE
828 -- Start and End on same day
829 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
830 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
831 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
832 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
833 INTO p_duration
834 FROM DUAL;
835 END IF;
836 END IF;
837 END IF;
838 END IF;
839 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
840 END LOOP;
841 p_duration := ROUND(p_duration,2);
842 --
843 END IF;
844 END IF;
845 --
846 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',20);
847 EXCEPTION
848 --
849 WHEN e_bad_time_format THEN
850 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',30);
851 hr_utility.set_location(SQLERRM,35);
852 RAISE;
853 --
854 WHEN OTHERS THEN
855 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',40);
856 hr_utility.set_location(SQLERRM,45);
857 RAISE;
858 --
859 END calc_sch_based_dur;
860 --
861 -- ---------------------------------------------------------------------------
862 -- |-----------------< calculate_absence_duration -old>-------------------------|
863 -- ---------------------------------------------------------------------------
864 --
865 -- Description:
866 -- Calculates the absence duration in hours and / or days and sets
867 -- the duration.
868 --
869 -- Pre-conditions:
870 -- None.
871 --
872 -- In Arguments:
873 -- p_absence_attendance_id
874 -- p_absence_attendance_type_id
875 -- p_business_group_id
876 -- p_object_version_number
877 -- p_effective_date
878 -- p_person_id
879 -- p_date_start
880 -- p_date_end
881 -- p_time_start
882 -- p_time_end
883 --
884 -- Out Arguments:
885 -- p_absence_days
886 -- p_absence_hours
887 -- p_use_formula
888 --
889 -- Post Success:
890 -- The absence duration in days and hours is returned.
891 --
892 -- Post Failure:
893 -- If a failure occurs, an application error is raised and
894 -- processing terminates.
895 --
896 -- Access Status:
897 -- Internal Development Use Only.
898 --
899 -- {End Of Comments}
900 -- ---------------------------------------------------------------------------
901 -- |-----------------< calculate_absence_duration -old>-------------------------|
902 -- ---------------------------------------------------------------------------
903 --
904 --
905 procedure calculate_absence_duration
906 (p_absence_attendance_id in number
907 ,p_absence_attendance_type_id in number
908 ,p_business_group_id in number
909 ,p_object_version_number in number
910 ,p_effective_date in date
911 ,p_person_id in number
912 ,p_date_start in date
913 ,p_date_end in date
914 ,p_time_start in varchar2
915 ,p_time_end in varchar2
916 ,p_absence_days out nocopy number
917 ,p_absence_hours out nocopy number
918 ,p_use_formula out nocopy boolean)
919 is
920
921 l_proc varchar2(72) := g_package||
922 'calculate_absence_duration';
923 l_exists varchar2(1);
924 l_api_updating boolean;
925 l_assignment_id number;
926 l_hours_or_days varchar2(1);
927 l_element_type_id number;
928 l_legislation_code varchar2(150);
929 l_formula_id number;
930 l_inputs ff_exec.inputs_t;
931 l_outputs ff_exec.outputs_t;
932 l_user_message varchar2(1) := 'N';
933 l_invalid_message fnd_new_messages.message_text%TYPE;
934
935 /*Added for the bug 6790565 - starts here*/
936 l_invalid_message_txt fnd_new_messages.message_text%TYPE;
937 l_invalid_message_num number;
938 /*Added for the bug 6790565 - ends here*/
939
940 wrong_parameters exception;
941 l_normal_time_start varchar2(5);
942 l_normal_time_end varchar2(5);
943 l_normal_day_minutes number;
944 l_first_day_minutes number;
945 l_last_day_minutes number;
946 l_same_day_minutes number;
947 l_absence_days number;
948 l_absence_hours number;
949
950 -- For schedule based calculation
951 l_sch_based_dur VARCHAR2(1);
952 l_sch_based_dur_found BOOLEAN;
953 l_absence_duration NUMBER;
954
955 --3093970 starts here. comment out the code introduced by 2820155.
956 --2820155 change starts
957 -- l_eff_time_start varchar2(5);
958 -- l_eff_time_end varchar2(5);
959 --2820155 change ends
960 --3093970 ends here.
961
962 cursor c_get_absence_info is
963 select abt.hours_or_days,
964 piv.element_type_id
965 from per_absence_attendance_types abt,
966 pay_input_values_f piv
967 where abt.absence_attendance_type_id = p_absence_attendance_type_id
968 and abt.input_value_id = piv.input_value_id(+);
969 --
970 cursor c_get_normal_hours (p_assignment_id in number) is
971 select nvl(nvl(asg.time_normal_start, pbg.default_start_time), '00:00'),
972 nvl(nvl(asg.time_normal_finish, pbg.default_end_time), '23:59')
973 FROM per_all_assignments_f asg,
974 per_business_groups pbg
975 WHERE asg.assignment_id = p_assignment_id
976 AND asg.business_group_id = pbg.business_group_id
977 AND p_effective_date between asg.effective_start_date
978 and asg.effective_end_date;
979
980 --
981
982 l_use_formula boolean;
983
984 begin
985
986 hr_utility.set_location('Entering Old:'|| l_proc, 10);
987 per_abs_bus.calculate_absence_duration
988 (p_absence_attendance_id => p_absence_attendance_id
989 ,p_absence_attendance_type_id => p_absence_attendance_type_id
990 ,p_business_group_id => p_business_group_id
991 ,p_object_version_number => p_object_version_number
992 ,p_effective_date => p_effective_date
993 ,p_person_id => p_person_id
994 ,p_date_start => p_date_start
995 ,p_date_end => p_date_end
996 ,p_time_start => p_time_start
997 ,p_time_end => p_time_end
998 ,p_ABS_INFORMATION_CATEGORY => NULL
999 ,p_ABS_INFORMATION1 => NULL
1000 ,p_ABS_INFORMATION2 => NULL
1001 ,p_ABS_INFORMATION3 => NULL
1002 ,p_ABS_INFORMATION4 => NULL
1003 ,p_ABS_INFORMATION5 => NULL
1004 ,p_ABS_INFORMATION6 => NULL
1005 ,p_absence_days => l_absence_days
1006 ,p_absence_hours => l_absence_hours
1007 ,p_use_formula => l_use_formula);
1008
1009 p_absence_hours := l_absence_hours;
1010 p_absence_days := l_absence_days;
1011 p_use_formula :=l_use_formula;
1012
1013 hr_utility.set_location('leaving old:'|| l_proc, 10);
1014 end;
1015 -- ---------------------------------------------------------------------------
1016 -- |-----------------< calculate_absence_duration -new>-------------------------|
1017 -- ---------------------------------------------------------------------------
1018 --
1019 -- Description:
1020 -- Calculates the absence duration in hours and / or days and sets
1021 -- the duration.
1022 --
1023 -- Pre-conditions:
1024 -- None.
1025 --
1026 -- In Arguments:
1027 -- p_absence_attendance_id
1028 -- p_absence_attendance_type_id
1029 -- p_business_group_id
1030 -- p_object_version_number
1031 -- p_effective_date
1032 -- p_person_id
1033 -- p_date_start
1034 -- p_date_end
1035 -- p_time_start
1036 -- p_time_end
1037 -- p_ABS_INFORMATION_CATEGORY
1038 -- p_ABS_INFORMATION1
1039 -- p_ABS_INFORMATION2
1040 -- p_ABS_INFORMATION3
1041 -- p_ABS_INFORMATION4
1042 -- p_ABS_INFORMATION5
1043 -- p_ABS_INFORMATION6
1044
1045 -- Out Arguments:
1046 -- p_absence_days
1047 -- p_absence_hours
1048 -- p_use_formula
1049 --
1050 -- Post Success:
1051 -- The absence duration in days and hours is returned.
1052 --
1053 -- Post Failure:
1054 -- If a failure occurs, an application error is raised and
1055 -- processing terminates.
1056 --
1057 -- Access Status:
1058 -- Internal Development Use Only.
1059 --
1060 -- {End Of Comments}
1061 -- ----------------------------------------------------------------------------
1062 --
1063 procedure calculate_absence_duration
1064 (p_absence_attendance_id in number
1065 ,p_absence_attendance_type_id in number
1066 ,p_business_group_id in number
1067 ,p_object_version_number in number
1068 ,p_effective_date in date
1069 ,p_person_id in number
1070 ,p_date_start in date
1071 ,p_date_end in date
1072 ,p_time_start in varchar2
1073 ,p_time_end in varchar2
1074 ,p_ABS_INFORMATION_CATEGORY in varchar2
1075 ,p_ABS_INFORMATION1 in varchar2
1076 ,p_ABS_INFORMATION2 in varchar2
1077 ,p_ABS_INFORMATION3 in varchar2
1078 ,p_ABS_INFORMATION4 in varchar2
1079 ,p_ABS_INFORMATION5 in varchar2
1080 ,p_ABS_INFORMATION6 in varchar2
1081 ,p_absence_days out nocopy number
1082 ,p_absence_hours out nocopy number
1083 ,p_use_formula out nocopy boolean)
1084 is
1085
1086 l_proc varchar2(72) := g_package||
1087 'calculate_absence_duration';
1088 l_exists varchar2(1);
1089 l_api_updating boolean;
1090 l_assignment_id number;
1091 l_hours_or_days varchar2(1);
1092 l_element_type_id number;
1093 l_legislation_code varchar2(150);
1094 l_formula_id number;
1095 l_inputs ff_exec.inputs_t;
1096 l_outputs ff_exec.outputs_t;
1097 l_user_message varchar2(1) := 'N';
1098 l_invalid_message fnd_new_messages.message_text%TYPE;
1099
1100 /*Added for the bug 6790565 - starts here*/
1101 l_invalid_message_txt fnd_new_messages.message_text%TYPE;
1102 l_invalid_message_num number;
1103 /*Added for the bug 6790565 - ends here*/
1104
1105 wrong_parameters exception;
1106 l_normal_time_start varchar2(5);
1107 l_normal_time_end varchar2(5);
1108 l_normal_day_minutes number;
1109 l_first_day_minutes number;
1110 l_last_day_minutes number;
1111 l_same_day_minutes number;
1112 l_absence_days number;
1113 l_absence_hours number;
1114
1115 -- For schedule based calculation
1116 l_sch_based_dur VARCHAR2(1);
1117 l_sch_based_dur_found BOOLEAN;
1118 l_absence_duration NUMBER;
1119
1120 --3093970 starts here. comment out the code introduced by 2820155.
1121 --2820155 change starts
1122 -- l_eff_time_start varchar2(5);
1123 -- l_eff_time_end varchar2(5);
1124 --2820155 change ends
1125 --3093970 ends here.
1126
1127 cursor c_get_absence_info is
1128 select abt.hours_or_days,
1129 piv.element_type_id
1130 from per_absence_attendance_types abt,
1131 pay_input_values_f piv
1132 where abt.absence_attendance_type_id = p_absence_attendance_type_id
1133 and abt.input_value_id = piv.input_value_id(+);
1134 --
1135 cursor c_get_normal_hours (p_assignment_id in number) is
1136 select nvl(nvl(asg.time_normal_start, pbg.default_start_time), '00:00'),
1137 nvl(nvl(asg.time_normal_finish, pbg.default_end_time), '23:59')
1138 FROM per_all_assignments_f asg,
1139 per_business_groups pbg
1140 WHERE asg.assignment_id = p_assignment_id
1141 AND asg.business_group_id = pbg.business_group_id
1142 AND p_effective_date between asg.effective_start_date
1143 and asg.effective_end_date;
1144
1145 --
1146 begin
1147
1148 hr_utility.set_location('Entering:'|| l_proc, 10);
1149 --
1150 -- Only proceed with validation if :
1151 -- a) The current g_old_rec is current and
1152 -- b) The date values have changed
1153 --
1154 l_api_updating := per_abs_shd.api_updating
1155 (p_absence_attendance_id => p_absence_attendance_id
1156 ,p_object_version_number => p_object_version_number);
1157 --
1158 if (l_api_updating
1159 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
1160 = nvl(p_date_start, hr_api.g_date)
1161 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
1162 = nvl(p_date_end, hr_api.g_date)
1163 and nvl(per_abs_shd.g_old_rec.time_start, hr_api.g_varchar2)
1164 = nvl(p_time_start, hr_api.g_varchar2)
1165 and nvl(per_abs_shd.g_old_rec.time_end, hr_api.g_varchar2)
1166 = nvl(p_time_end, hr_api.g_varchar2)
1167 and nvl(per_abs_shd.g_old_rec.absence_days, hr_api.g_number)
1168 = nvl(p_absence_days, hr_api.g_number)
1169 and nvl(per_abs_shd.g_old_rec.absence_hours, hr_api.g_number)
1170 = nvl(p_absence_hours, hr_api.g_number)) then
1171 return;
1172 end if;
1173
1174 hr_utility.set_location(l_proc, 15);
1175
1176 --
1177 -- See if a Fast Formula exists. Here the Fast Formula names
1178 -- are hard-coded. Fast Formulas with these exact names can
1179 -- be defined at one of three levels to default the absence
1180 -- duration:
1181 --
1182 -- 1. Business group (customer-definable)
1183 -- 2. Legislation (Oracle internal legislation-specific)
1184 -- 3. Core (Oracle internal core product)
1185 --
1186
1187 --
1188 -- Get the varous additional values that are required for use later.
1189 --
1190
1191 l_assignment_id := hr_person_absence_api.get_primary_assignment
1192 (p_person_id => p_person_id
1193 ,p_effective_date => p_effective_date);
1194
1195 l_legislation_code := hr_api.return_legislation_code
1196 (p_business_group_id => p_business_group_id);
1197
1198 open c_get_absence_info;
1199 fetch c_get_absence_info into l_hours_or_days,
1200 l_element_type_id;
1201 close c_get_absence_info;
1202
1203 l_sch_based_dur := NVL(FND_PROFILE.Value('HR_SCH_BASED_ABS_CALC'),'N');
1204 l_sch_based_dur_found := FALSE;
1205 --
1206 IF l_sch_based_dur = 'Y' THEN
1207 --
1208 hr_utility.set_location(l_proc, 16);
1209 p_use_formula := TRUE; -- set to display
1210 --
1211 calc_sch_based_dur (p_days_or_hours => l_hours_or_days,
1212 p_date_start => p_date_start,
1213 p_date_end => p_date_end,
1214 p_time_start => p_time_start,
1215 p_time_end => p_time_end,
1216 p_assignment_id => l_assignment_id,
1217 p_duration => l_absence_duration
1218 );
1219 --
1220 IF l_absence_duration IS NOT NULL THEN
1221 --
1222 l_sch_based_dur_found := TRUE;
1223 --
1224 IF l_hours_or_days = 'H' THEN
1225 hr_utility.set_location(l_proc, 17);
1226 p_absence_hours := l_absence_duration;
1227 ELSIF l_hours_or_days = 'D' THEN
1228 hr_utility.set_location(l_proc, 18);
1229 p_absence_days := l_absence_duration;
1230 ELSE
1231 hr_utility.set_location(l_proc, 19);
1232 l_sch_based_dur_found := FALSE;
1233 END IF;
1234 --
1235 END IF;
1236 --
1237 END IF; -- sch_based_dur is 'Y'
1238
1239 IF l_sch_based_dur <> 'Y' OR (l_sch_based_dur = 'Y' AND NOT l_sch_based_dur_found) THEN
1240 --
1241 hr_utility.set_location(l_proc, 20);
1242
1243 begin
1244 --
1245 -- Look for a customer-defined formula
1246 --
1247 select ff.formula_id
1248 into l_formula_id
1249 from ff_formulas_f ff
1250 where ff.formula_name = 'BG_ABSENCE_DURATION'
1251 and ff.business_group_id = p_business_group_id
1252 and p_effective_date between ff.effective_start_date and
1253 ff.effective_end_date;
1254 exception
1255
1256 when no_data_found then
1257 --
1258 -- There is no customer defined formula so look for
1259 -- a legislative formula.
1260 --
1261 begin
1262
1263 hr_utility.set_location(l_proc, 25);
1264
1265 select ff.formula_id
1266 into l_formula_id
1267 from ff_formulas_f ff
1268 where ff.formula_name = 'LEGISLATION_ABSENCE_DURATION'
1269 and ff.legislation_code = l_legislation_code
1270 and ff.business_group_id is null
1271 and p_effective_date between ff.effective_start_date and
1272 ff.effective_end_date;
1273
1274 exception
1275
1276 when no_data_found then
1277 --
1278 -- If none of the two above then select the core formula
1279 --
1280 begin
1281
1282 hr_utility.set_location(l_proc, 30);
1283
1284 select ff.formula_id
1285 into l_formula_id
1286 from ff_formulas_f ff
1287 where ff.formula_name = 'CORE_ABSENCE_DURATION'
1288 and ff.legislation_code is null
1289 and ff.business_group_id is null
1290 and p_effective_date between ff.effective_start_date and
1291 ff.effective_end_date;
1292
1293 exception
1294
1295 when no_data_found then
1296 --
1297 -- No formula is found. We capture the error and do nothing.
1298 --
1299 null;
1300
1301 end;
1302 end;
1303 end;
1304
1305 hr_utility.set_location(l_proc, 35);
1306
1307 if l_formula_id is not null then
1308 --
1309 -- An absence duration Fast Formula should be used so the
1310 -- formula is called. First, the formula is initialised.
1311 --
1312 p_use_formula := TRUE;
1313
1314 hr_utility.set_location(l_proc, 40);
1315
1316 --
1317 ff_exec.reset_caches; -- bug fix 9337533 to clear the cache..
1318
1319 -- Initalise the formula.
1320 --
1321 ff_exec.init_formula
1322 (p_formula_id => l_formula_id
1323 ,p_effective_date => p_effective_date
1324 ,p_inputs => l_inputs
1325 ,p_outputs => l_outputs);
1326
1327 hr_utility.set_location(l_proc, 45);
1328
1329 --
1330 -- Assign the inputs.
1331 --
1332 for i_input in l_inputs.first..l_inputs.last
1333 loop
1334
1335 if l_inputs(i_input).name = 'DAYS_OR_HOURS' then
1336 l_inputs(i_input).value := l_hours_or_days;
1337 elsif l_inputs(i_input).name = 'DATE_START' then
1338 l_inputs(i_input).value := fnd_date.date_to_canonical(p_date_start);
1339 elsif l_inputs(i_input).name = 'DATE_END' then
1340 l_inputs(i_input).value := fnd_date.date_to_canonical(p_date_end);
1341 elsif l_inputs(i_input).name = 'TIME_START' then
1342 l_inputs(i_input).value := p_time_start;
1343 elsif l_inputs(i_input).name = 'TIME_END' then
1344 l_inputs(i_input).value := p_time_end;
1345 elsif l_inputs(i_input).name = 'DATE_EARNED' then
1346 l_inputs(i_input).value := fnd_date.date_to_canonical
1347 (p_effective_date);
1348 elsif l_inputs(i_input).name = 'BUSINESS_GROUP_ID' then
1349 l_inputs(i_input).value := p_business_group_id;
1350 elsif l_inputs(i_input).name = 'LEGISLATION_CODE' then
1351 l_inputs(i_input).value := l_legislation_code;
1352 elsif l_inputs(i_input).name = 'ASSIGNMENT_ID' then
1353 l_inputs(i_input).value := l_assignment_id;
1354 elsif l_inputs(i_input).name = 'ELEMENT_TYPE_ID' then
1355 l_inputs(i_input).value := l_element_type_id;
1356 elsif l_inputs(i_input).name = 'ABSENCE_ATTENDANCE_TYPE_ID' then
1357 l_inputs(i_input).value := p_absence_attendance_type_id;
1358 elsif l_inputs(i_input).name = 'ABS_INFORMATION_CATEGORY' then
1359 l_inputs(i_input).value := p_ABS_INFORMATION_CATEGORY;
1360 elsif l_inputs(i_input).name = 'ABS_INFORMATION1' then
1361 l_inputs(i_input).value := p_ABS_INFORMATION1;
1362 elsif l_inputs(i_input).name = 'ABS_INFORMATION2' then
1363 l_inputs(i_input).value := p_ABS_INFORMATION2;
1364 elsif l_inputs(i_input).name = 'ABS_INFORMATION3' then
1365 l_inputs(i_input).value := p_ABS_INFORMATION3;
1366 elsif l_inputs(i_input).name = 'ABS_INFORMATION4' then
1367 l_inputs(i_input).value := p_ABS_INFORMATION4;
1368 elsif l_inputs(i_input).name = 'ABS_INFORMATION5' then
1369 l_inputs(i_input).value := p_ABS_INFORMATION5;
1370 elsif l_inputs(i_input).name = 'ABS_INFORMATION6' then
1371 l_inputs(i_input).value := p_ABS_INFORMATION6;
1372
1373
1374 else
1375 raise wrong_parameters;
1376 end if;
1377
1378 end loop;
1379
1380 hr_utility.set_location(l_proc, 50);
1381
1382 --
1383 -- Run the formula.
1384 --
1385 ff_exec.run_formula(l_inputs, l_outputs);
1386
1387 hr_utility.set_location(l_proc, 55);
1388
1389 --
1390 -- Assign the outputs.
1391 --
1392 for i_output in l_outputs.first..l_outputs.last
1393 loop
1394
1395 if l_outputs(i_output).name = 'DURATION' then
1396
1397 if l_outputs(i_output).value = 'FAILED' then
1398 l_user_message := 'Y';
1399 else
1400 --
1401 -- The absence hours / days out parameter is set. If no UOM
1402 -- is set but the start or end time have been entered, the output
1403 -- is returned in hours.
1404 --
1405 if l_hours_or_days = 'H'
1406 or (p_time_start is not null and p_time_end is not null) then
1407 p_absence_hours := round(fnd_number.canonical_to_number(l_outputs(i_output).value),2);
1408 else
1409 p_absence_days := round(fnd_number.canonical_to_number(l_outputs(i_output).value),2);
1410 end if;
1411 end if;
1412
1413 elsif l_outputs(i_output).name = 'INVALID_MSG' then
1414 --
1415 -- Here any customer-defined messages are set and
1416 -- raised after this loop.
1417 --
1418 l_invalid_message := l_outputs(i_output).value;
1419
1420 null;
1421 else
1422 raise wrong_parameters;
1423 end if;
1424
1425 end loop;
1426
1427 hr_utility.set_location(l_proc, 60);
1428 hr_utility.trace('l_user_message: '||l_user_message);
1429 hr_utility.trace('l_invalid_message: '||l_invalid_message);
1430
1431
1432 /*Added for the bug 6790565 - starts here*/
1433 -- Displays the error message text if text is given
1434 -- directly in the fast formula
1435 -- Displays the error message text if the error message
1436 -- name is given
1437 -- This is done as SSHR supports both text as well as name
1438 select instr(l_invalid_message,' ',1,1)
1439 into l_invalid_message_num from dual;
1440
1441 if l_invalid_message_num = 0
1442 then
1443 l_invalid_message_txt := fnd_message.get_string('PER',l_invalid_message);
1444 else
1445 l_invalid_message_txt := l_invalid_message;
1446 end if;
1447
1448 /*Added for the bug 6790565 - ends here*/
1449 --
1450 -- If the Fast Formula raises a user-defined error message,
1451 -- raise the error back to the user.
1452 --
1453 if l_user_message = 'Y' then
1454 -- Start of fix 3553741
1455 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1456 /*Added for the bug 6790565 - starts here*/
1457 -- hr_utility.set_message_token('FORMULA_TEXT', l_invalid_message);
1458 hr_utility.set_message_token('FORMULA_TEXT', l_invalid_message_txt);
1459 /*Added for the bug 6790565 - ends here*/
1460 hr_utility.raise_error;
1461 -- End of fix 3553741
1462 end if;
1463
1464 hr_utility.set_location(l_proc, 63);
1465
1466 else
1467 --
1468 -- No formula could be located so we calculate based on the
1469 -- standard hours of the assignment or business group.
1470 --
1471 p_use_formula := FALSE;
1472
1473 hr_utility.set_location(l_proc, 65);
1474
1475 --
1476 -- Get the default start and end times. First check the assignment, then
1477 -- the business group. If neither of these, assume 24 hours a day.
1478 --
1479 open c_get_normal_hours (l_assignment_id);
1480 fetch c_get_normal_hours into l_normal_time_start,
1481 l_normal_time_end;
1482 close c_get_normal_hours;
1483
1484 -- #2734822: verify the time format
1485
1486 hr_dbchkfmt.is_db_format(p_value => l_normal_time_start
1487 ,p_formatted_output => l_normal_time_start
1488 ,p_arg_name => 'time_normal_start'
1489 ,p_format => 'TIMES');
1490
1491
1492 hr_dbchkfmt.is_db_format(p_value => l_normal_time_end
1493 ,p_formatted_output => l_normal_time_end
1494 ,p_arg_name => 'time_normal_finish'
1495 ,p_format => 'TIMES');
1496
1497 -- end #2734822
1498
1499 hr_utility.set_location(l_proc, 70);
1500
1501 --
1502 -- Calculate the number of minutes in each day.
1503 --
1504 -- 3093970 starts here. comment out code introduced by 2820155.
1505 --2820155 changes start
1506 -- Start time and end time should be adjusted to fall between
1507 -- normal start time and end time.
1508 /*
1509 l_eff_time_start :=p_time_start;
1510 l_eff_time_end :=p_time_end;
1511
1512 IF (
1513 ( (substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2)) <
1514 ( (substr(l_normal_time_start,1,2) * 60) + substr(l_normal_time_start,4,2))
1515 ) THEN
1516 l_eff_time_start := l_normal_time_start;
1517
1518 END IF;
1519
1520 IF (
1521 ( (substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2)) >
1522 ( (substr(l_normal_time_end,1,2) * 60) + substr(l_normal_time_end,4,2))
1523 ) THEN
1524 l_eff_time_start := l_normal_time_end;
1525
1526 END IF;
1527
1528
1529 IF (
1530 ( (substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2)) <
1531 ( (substr(l_normal_time_start,1,2) * 60) + substr(l_normal_time_start,4,2))
1532 ) THEN
1533 l_eff_time_end := l_normal_time_start;
1534
1535 END IF;
1536
1537
1538 IF (
1539 ( (substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2)) >
1540 ( (substr(l_normal_time_end,1,2) * 60) + substr(l_normal_time_end,4,2))
1541 ) THEN
1542 l_eff_time_end := l_normal_time_end;
1543
1544 END IF;
1545
1546 -- Pass effective start and end time instead of received start and end time
1547
1548 l_normal_day_minutes := convert_to_minutes(l_normal_time_start,
1549 l_normal_time_end);
1550 l_first_day_minutes := convert_to_minutes(nvl(l_eff_time_start,
1551 l_normal_time_start),
1552 l_normal_time_end);
1553 l_last_day_minutes := convert_to_minutes(l_normal_time_start,
1554 nvl(l_eff_time_end,
1555 l_normal_time_end));
1556 l_same_day_minutes := convert_to_minutes(nvl(l_eff_time_start,
1557 l_normal_time_start),
1558 nvl(l_eff_time_end,
1559 l_normal_time_end));
1560
1561 --2820155 changes end
1562 */
1563 --
1564 -- Calculate the number of minutes in each day.
1565 --
1566 l_normal_day_minutes := convert_to_minutes(l_normal_time_start,
1567 l_normal_time_end);
1568 l_first_day_minutes := convert_to_minutes(nvl(p_time_start,
1569 l_normal_time_start),
1570 l_normal_time_end);
1571 l_last_day_minutes := convert_to_minutes(l_normal_time_start,
1572 nvl(p_time_end,
1573 l_normal_time_end));
1574 --
1575 -- Bug3093970 starts here.
1576 --
1577 if l_first_day_minutes <= 0 OR l_first_day_minutes > l_normal_day_minutes
1578 OR l_last_day_minutes <= 0 OR l_last_day_minutes > l_normal_day_minutes THEN
1579 --
1580 -- The leave timings are out off the standard timings.
1581 -- So use 24 hours rule to calculate the first day and last day minutes.
1582 --
1583 hr_utility.set_location(l_proc, 72);
1584 l_first_day_minutes := convert_to_minutes(nvl(p_time_start,
1585 l_normal_time_start),
1586 '24:00');
1587 l_last_day_minutes := convert_to_minutes('00:00', nvl(p_time_end,
1588 l_normal_time_end));
1589
1590 end if;
1591 --
1592 -- Bug3093970 ends here.
1593 --
1594 l_same_day_minutes := convert_to_minutes(nvl(p_time_start,
1595 l_normal_time_start),
1596 nvl(p_time_end,
1597 l_normal_time_end));
1598 --2943479 changes start
1599 if l_normal_time_end = '23:59'
1600 then
1601 l_normal_day_minutes := l_normal_day_minutes +1;
1602 l_first_day_minutes := l_first_day_minutes +1;
1603 --3075512 changes start
1604 if (p_time_end is null or p_time_end = '') then
1605 l_last_day_minutes := l_last_day_minutes +1;
1606 l_same_day_minutes := l_same_day_minutes +1;
1607 end if;
1608 --3075512 changes end
1609 end if;
1610 --2943479 changes end
1611
1612 hr_utility.trace('Normal Day Minutes: ' || to_char(l_normal_day_minutes));
1613 hr_utility.trace('First Day Minutes: ' || to_char(l_first_day_minutes));
1614 hr_utility.trace('Last Day Minutes: ' || to_char(l_last_day_minutes));
1615 hr_utility.trace('Same Day Minutes: ' || to_char(l_same_day_minutes));
1616
1617 hr_utility.set_location(l_proc, 75);
1618
1619 --
1620 -- Calculate the absence days.
1621 --
1622 l_absence_days := (p_date_end - p_date_start) + 1;
1623
1624 hr_utility.trace('Absence Days: ' || to_char(l_absence_days));
1625
1626 --
1627 -- Calculate the absence hours.
1628 --
1629 if l_absence_days = 1 then
1630 --
1631 -- The absence starts and ends on the same day.
1632 --
1633 l_absence_hours := l_same_day_minutes / 60;
1634
1635 elsif l_absence_days = 2 then
1636 --
1637 -- The absence ends the day after another.
1638 --
1639 l_absence_hours := (l_first_day_minutes + l_last_day_minutes) / 60;
1640
1641 else
1642 --
1643 -- The absence is n number of days.
1644 --
1645 l_absence_hours := (l_first_day_minutes + l_last_day_minutes +
1646 ((l_absence_days - 2) * l_normal_day_minutes)) / 60;
1647
1648 end if;
1649
1650 hr_utility.set_location(l_proc, 80);
1651
1652 --
1653 -- Check that the absence hours are not less than zero. This could
1654 -- happen if the entered start time is after the normal start time or
1655 -- the entered end time is after the normal end time.
1656 --
1657 If l_absence_hours < 0 then
1658 l_absence_hours := 0;
1659 end if;
1660
1661 --
1662 -- Set the absence days and hours out parameters.
1663 --
1664 if l_hours_or_days = 'H' then
1665 -- Start of fix 3156665
1666 /* If the standard working hours is not defined at Assignment or
1667 Organization level, then system will take the default start time
1668 as 00:00 and end time as 23:59. Duration of this times will reach
1669 upto 23.983333' only. So if system is using default timings then
1670 rounding the calculated hours to get the default as 24 hours.
1671 Else rounding with 2 decimal places.
1672 */
1673 --
1674 --p_absence_hours := round(l_absence_hours,2);
1675 if p_time_start = '00:00' and p_time_end = '23:59' then
1676 p_absence_hours := round(l_absence_hours);
1677 else
1678 p_absence_hours := round(l_absence_hours,2);
1679 end if;
1680 -- End of fix 3156665
1681 elsif l_hours_or_days = 'D' then
1682 p_absence_days := round(l_absence_days,2);
1683
1684 else
1685 p_absence_hours := round(l_absence_hours,2);
1686 p_absence_days := round(l_absence_days,2);
1687
1688 end if;
1689
1690 end if;
1691
1692 END IF; -- Schedule based calculation not used
1693
1694 hr_utility.set_location(' Leaving:'|| l_proc, 85);
1695
1696 exception
1697
1698 when wrong_parameters then
1699 --
1700 -- The inputs / outputs of the Fast Formula are incorrect
1701 -- so raise an error.
1702 --
1703 hr_utility.set_location(l_proc, 90);
1704
1705 hr_utility.set_message(800,'HR_34964_BAD_FF_DEFINITION');
1706 hr_utility.raise_error;
1707
1708 end calculate_absence_duration;
1709 --
1710 -- ---------------------------------------------------------------------------
1711 -- |---------------------------< chk_person_id >----------------------------|
1712 -- ---------------------------------------------------------------------------
1713 --
1714 -- Description:
1715 -- Validates that the person exists, that they have a valid period of
1716 -- service and that they match the business group id being passed.
1717 --
1718 -- Pre-conditions:
1719 --
1720 -- In Arguments:
1721 -- p_absence_attendance_id
1722 -- p_person_id
1723 -- p_business_group_id
1724 --
1725 -- Post Success:
1726 -- If the person and their period of service are valid, processing
1727 -- continues.
1728 --
1729 -- Post Failure:
1730 -- An application error will be raised and processing is terminated.
1731 --
1732 -- Access Status:
1733 -- Internal Development Use Only.
1734 --
1735 -- {End Of Comments}
1736 -- ----------------------------------------------------------------------------
1737 --
1738 procedure chk_person_id
1739 (p_absence_attendance_id in number
1740 ,p_person_id in number
1741 ,p_business_group_id in number
1742 ,p_object_version_number in number
1743 ,p_date_projected_start in date
1744 ,p_date_projected_end in date
1745 ,p_date_start in date
1746 ,p_date_end in date)
1747 is
1748
1749 --
1750 l_proc varchar2(72) := g_package||'chk_person_id';
1751 l_api_updating boolean;
1752 --
1753
1754 begin
1755
1756 hr_utility.set_location('Entering:'|| l_proc, 10);
1757 --
1758 hr_api.mandatory_arg_error
1759 (p_api_name => l_proc
1760 ,p_argument => 'p_person_id'
1761 ,p_argument_value => p_person_id
1762 );
1763 hr_api.mandatory_arg_error
1764 (p_api_name => l_proc
1765 ,p_argument => 'p_business_group_id'
1766 ,p_argument_value => p_business_group_id
1767 );
1768
1769 --
1770 -- Only proceed with validation if :
1771 -- a) The current g_old_rec is current and
1772 -- b) The date values have changed
1773 --
1774 l_api_updating := per_abs_shd.api_updating
1775 (p_absence_attendance_id => p_absence_attendance_id
1776 ,p_object_version_number => p_object_version_number);
1777 --
1778 if (l_api_updating
1779 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
1780 = nvl(p_date_projected_start, hr_api.g_date)
1781 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
1782 = nvl(p_date_projected_end, hr_api.g_date)
1783 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
1784 = nvl(p_date_start, hr_api.g_date)
1785 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
1786 = nvl(p_date_end, hr_api.g_date)) then
1787 return;
1788 end if;
1789
1790 --
1791 -- Check that the person exists and that their period of service
1792 -- is valid for the entire absence duration.
1793 --
1794 if not per_valid_for_absence
1795 (p_person_id => p_person_id
1796 ,p_business_group_id => p_business_group_id
1797 ,p_date_projected_start => p_date_projected_start
1798 ,p_date_projected_end => p_date_projected_end
1799 ,p_date_start => p_date_start
1800 ,p_date_end => p_date_end)
1801 then
1802
1803 fnd_message.set_name('PER', 'PER_7715_ABS_TERM_PROJ_DATE');
1804 fnd_message.raise_error;
1805
1806 end if;
1807
1808 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1809
1810 end chk_person_id;
1811 --
1812 -- ---------------------------------------------------------------------------
1813 -- |-----------------< chk_absence_attendance_type_id >---------------------|
1814 -- ---------------------------------------------------------------------------
1815 --
1816 -- Description:
1817 -- Validates that the absence_attendance_type_id exists in
1818 -- per_absence_attendance_types for the same business group and that it
1819 -- is effective for the entire absence duration.
1820 --
1821 -- Pre-conditions:
1822 -- None.
1823 --
1824 -- In Arguments:
1825 -- p_absence_attendance_id
1826 -- p_business_group_id
1827 -- p_absence_attendance_type_id
1828 -- p_object_version_number
1829 --
1830 -- Post Success:
1831 -- If absence_attendance_type_id exists and is valid,
1832 -- processing continues.
1833 --
1834 -- Post Failure:
1835 -- If absence_attendance_type_id is invalid,
1836 -- an application error is raised and processing terminates.
1837 --
1838 -- Access Status:
1839 -- Internal Development Use Only.
1840 --
1841 -- {End Of Comments}
1842 -- ----------------------------------------------------------------------------
1843 --
1844 procedure chk_absence_attendance_type_id
1845 (p_absence_attendance_id in number
1846 ,p_absence_attendance_type_id in number
1847 ,p_business_group_id in number
1848 ,p_object_version_number in number
1849 ,p_date_projected_start in date
1850 ,p_date_projected_end in date
1851 ,p_date_start in date
1852 ,p_date_end in date)
1853 is
1854
1855 l_exists varchar2(1);
1856 l_proc varchar2(72) := g_package||'chk_absence_attendance_type_id';
1857 l_api_updating boolean;
1858 --
1859
1860 cursor c_absence_within_type_dates is
1861 select null
1862 from per_absence_attendance_types abt
1863 where abt.absence_attendance_type_id = p_absence_attendance_type_id
1864 and abt.business_group_id = p_business_group_id
1865 and (p_date_projected_start is null or p_date_projected_start
1866 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
1867 and (p_date_projected_end is null or p_date_projected_end
1868 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
1869 and (p_date_start is null or p_date_start
1870 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
1871 and (p_date_end is null or p_date_end
1872 between abt.date_effective and nvl(abt.date_end,hr_api.g_eot));
1873
1874 --
1875 begin
1876
1877 hr_utility.set_location('Entering:'|| l_proc, 10);
1878 --
1879 --
1880 -- Check mandatory parameters have been set
1881 --
1882 hr_api.mandatory_arg_error
1883 (p_api_name => l_proc
1884 ,p_argument => 'p_absence_attendance_type_id'
1885 ,p_argument_value => p_absence_attendance_type_id
1886 );
1887
1888 --
1889 -- Only proceed with validation if :
1890 -- a) The current g_old_rec is current and
1891 -- b) The date values have changed
1892 --
1893 l_api_updating := per_abs_shd.api_updating
1894 (p_absence_attendance_id => p_absence_attendance_id
1895 ,p_object_version_number => p_object_version_number);
1896 --
1897 if (l_api_updating
1898 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
1899 = nvl(p_date_projected_start, hr_api.g_date)
1900 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
1901 = nvl(p_date_projected_end, hr_api.g_date)
1902 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
1903 = nvl(p_date_start, hr_api.g_date)
1904 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
1905 = nvl(p_date_end, hr_api.g_date)) then
1906 return;
1907 end if;
1908
1909 hr_utility.set_location(l_proc, 15);
1910
1911 --
1912 -- Check that all the dates are within the effective dates of the
1913 -- absence type.
1914 --
1915 open c_absence_within_type_dates;
1916 fetch c_absence_within_type_dates into l_exists;
1917
1918 if c_absence_within_type_dates%notfound then
1919 fnd_message.set_name('PER', 'HR_6457_ABS_DET_DATES');
1920 fnd_message.raise_error;
1921 end if;
1922
1923 close c_absence_within_type_dates;
1924
1925 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1926
1927 end chk_absence_attendance_type_id;
1928 --
1929 -- ---------------------------------------------------------------------------
1930 -- |-----------------< chk_abs_attendance_reason_id >-----------------------|
1931 -- ---------------------------------------------------------------------------
1932 --
1933 -- Description:
1934 -- Validates that an abs_attendance_reason_id exists in table
1935 -- per_abs_attendance_reasons, also valid in hr_lookups
1936 -- where lookup_type is 'ABSENCE_REASON' and enabled_flag is 'Y'
1937 -- and effective_date is between the active dates (if they are not null).
1938 --
1939 -- Pre-conditions:
1940 -- absence_attendance_type_id must be valid.
1941 -- business_group_id must be valid.
1942 -- effective_date must be valid.
1943 --
1944 -- In Arguments:
1945 -- p_absence_attendance_id
1946 -- p_absence_attendance_type_id
1947 -- p_abs_attendance_reason_id
1948 -- p_business_group_id
1949 -- p_object_version_number
1950 -- p_effective_date
1951 --
1952 -- Post Success:
1953 -- If a row does exist; processing continues.
1954 --
1955 -- Post Failure:
1956 -- If a row does not exist in per_abs_attendance_reason and hr_lookups for
1957 -- a given reason id then an error will be raised and processing terminated.
1958 --
1959 -- Access Status:
1960 -- Internal Table Handler Use Only.
1961 --
1962 -- {End Of Comments}
1963 -- ----------------------------------------------------------------------------
1964 --
1965 procedure chk_abs_attendance_reason_id
1966 (p_absence_attendance_id in number
1967 ,p_absence_attendance_type_id in number
1968 ,p_abs_attendance_reason_id in number
1969 ,p_business_group_id in number
1970 ,p_object_version_number in number
1971 ,p_effective_date in date
1972 )
1973 is
1974 --
1975 l_exists varchar2(1);
1976 l_proc varchar2(72) := g_package||'chk_abs_attendance_reason_id';
1977 --
1978 l_api_updating boolean;
1979 l_business_group_id number;
1980 --
1981 cursor csr_valid_abs_reason is
1982 select null
1983 from per_abs_attendance_reasons abr,
1984 hr_lookups hrl
1985 where abr.business_group_id = p_business_group_id
1986 and abr.absence_attendance_type_id = p_absence_attendance_type_id
1987 and abr.abs_attendance_reason_id = p_abs_attendance_reason_id
1988 and abr.name = hrl.lookup_code
1989 and hrl.lookup_type = 'ABSENCE_REASON'
1990 and p_effective_date between
1991 nvl(hrl.start_date_active,hr_api.g_sot)
1992 and nvl(hrl.end_date_active,hr_api.g_eot)
1993 and hrl.enabled_flag = 'Y';
1994 --
1995 begin
1996
1997 hr_utility.set_location('Entering:'|| l_proc, 10);
1998
1999 if p_abs_attendance_reason_id is null then
2000 return;
2001 end if;
2002
2003 --
2004 -- Only proceed with validation if :
2005 -- a) The current g_old_rec is current and
2006 -- b) The value for abs_attendance_reason_id has changed
2007 --
2008 l_api_updating := per_abs_shd.api_updating
2009 (p_absence_attendance_id => p_absence_attendance_id
2010 ,p_object_version_number => p_object_version_number);
2011 --
2012 if (l_api_updating and nvl(per_abs_shd.g_old_rec.abs_attendance_reason_id,
2013 hr_api.g_number) = nvl(p_abs_attendance_reason_id, hr_api.g_number)) then
2014 return;
2015 end if;
2016
2017 open csr_valid_abs_reason;
2018 fetch csr_valid_abs_reason into l_exists;
2019 if csr_valid_abs_reason%notfound then
2020 --
2021 fnd_message.set_name('PER', 'PER_52749_ABS_REASON_INVALID');
2022 fnd_message.raise_error;
2023 --
2024 end if;
2025 close csr_valid_abs_reason;
2026
2027 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2028
2029 end chk_abs_attendance_reason_id;
2030 --
2031 -- ---------------------------------------------------------------------------
2032 -- |-----------------< chk_absence_period old>---------------------------------|
2033 -- ---------------------------------------------------------------------------
2034 --
2035 -- Description:
2036 -- Validates the projected dates, actual dates, times and the duration.
2037 --
2038 -- Pre-conditions:
2039 -- absence_attendance_type_id must be valid.
2040 -- business_group_id must be valid.
2041 -- effective_date must be valid.
2042 --
2043 -- In Arguments:
2044 -- p_absence_attendance_id
2045 -- p_absence_attendance_type_id
2046 -- p_business_group_id
2047 -- p_object_version_number
2048 -- p_effective_date
2049 -- p_person_id
2050 -- p_date_projected_start
2051 -- p_time_projected_start
2052 -- p_date_projected_end
2053 -- p_time_projected_end
2054 -- p_date_start
2055 -- p_time_start
2056 -- p_date_end
2057 -- p_time_end
2058 --
2059 -- In Out Arguments:
2060 -- p_absence_days
2061 -- p_absence_hours
2062 --
2063 -- Post Success:
2064 -- If validation passes, processing continues.
2065 --
2066 -- Post Failure:
2067 -- IF validation fails, the appropriate error or warning is raised.
2068 --
2069 -- Access Status:
2070 -- Internal Table Handler Use Only.
2071 --
2072 -- {End Of Comments}
2073
2074 procedure chk_absence_period
2075 (p_absence_attendance_id in number
2076 ,p_absence_attendance_type_id in number
2077 ,p_business_group_id in number
2078 ,p_object_version_number in number
2079 ,p_effective_date in date
2080 ,p_person_id in number
2081 ,p_date_projected_start in date
2082 ,p_time_projected_start in varchar2
2083 ,p_date_projected_end in date
2084 ,p_time_projected_end in varchar2
2085 ,p_date_start in date
2086 ,p_time_start in varchar2
2087 ,p_date_end in date
2088 ,p_time_end in varchar2
2089 ,p_absence_days in out nocopy number
2090 ,p_absence_hours in out nocopy number
2091 ,p_dur_dys_less_warning out nocopy boolean
2092 ,p_dur_hrs_less_warning out nocopy boolean
2093 ,p_exceeds_pto_entit_warning out nocopy boolean
2094 ,p_exceeds_run_total_warning out nocopy boolean
2095 ,p_abs_overlap_warning out nocopy boolean
2096 ,p_abs_day_after_warning out nocopy boolean
2097 ,p_dur_overwritten_warning out nocopy boolean)
2098 is
2099
2100 --
2101 l_proc varchar2(72) := g_package||'chk_absence_period';
2102 l_api_updating boolean;
2103 l_exists varchar2(1);
2104 l_hours_or_days varchar2(1);
2105 l_increasing_or_decreasing varchar2(1);
2106 l_absence_days number;
2107 l_absence_hours number;
2108 l_use_formula boolean;
2109 l_auto_overwrite varchar(30);
2110 l_assignment_id number;
2111 l_payroll_id number;
2112 l_accrual_plan_id number;
2113 l_dummy_date date;
2114 l_dummy_number number;
2115 l_net_entitlement number;
2116 l_accrual_msg boolean;
2117 l_running_total_hours number;
2118 l_running_total_days number;
2119 l_year_to_date_hours number;
2120 l_year_to_date_days number;
2121 l_absence_overlap_flag varchar2(1);
2122 --
2123 -- ************* Added for Bug 272978 *************
2124 -- ********* Added for AU,NZ by APAC team *********
2125 l_legislation_code varchar2(3);
2126 l_apac_dummy_number number;
2127 l_apac_entitlement number;
2128 l_apac_accrual number;
2129
2130 cursor csr_legislation(p_business_group_id number) is
2131 select pbg.legislation_code
2132 from per_business_groups pbg
2133 where pbg.business_group_id = p_business_group_id;
2134
2135 -- ********* End Bug 272978 *************
2136
2137 -- fix for the bug 8492746 starts here
2138 l_dur_ent_hours number;
2139 l_dur_calc_hours number;
2140 -- fix for the bug 8492746 ends here
2141
2142
2143 begin
2144
2145 hr_utility.set_location('Entering: old'|| l_proc, 5);
2146
2147 --
2148 chk_absence_period
2149 (p_absence_attendance_id => p_absence_attendance_id
2150 ,p_absence_attendance_type_id => p_absence_attendance_type_id
2151 ,p_business_group_id => p_business_group_id
2152 ,p_object_version_number => p_object_version_number
2153 ,p_effective_date => p_effective_date
2154 ,p_person_id => p_person_id
2155 ,p_date_projected_start => p_date_projected_start
2156 ,p_time_projected_start => p_time_projected_start
2157 ,p_date_projected_end => p_date_projected_end
2158 ,p_time_projected_end => p_time_projected_end
2159 ,p_date_start => p_date_start
2160 ,p_time_start => p_time_start
2161 ,p_date_end => p_date_end
2162 ,p_time_end => p_time_end
2163 ,p_ABS_INFORMATION_CATEGORY => NULL
2164 ,p_ABS_INFORMATION1 => NULL
2165 ,p_ABS_INFORMATION2 => NULL
2166 ,p_ABS_INFORMATION3 => NULL
2167 ,p_ABS_INFORMATION4 => NULL
2168 ,p_ABS_INFORMATION5 => NULL
2169 ,p_ABS_INFORMATION6 => NULL
2170 ,p_absence_days => per_abs_shd.g_absence_days
2171 ,p_absence_hours => per_abs_shd.g_absence_hours
2172 ,p_dur_dys_less_warning => p_dur_dys_less_warning
2173 ,p_dur_hrs_less_warning => p_dur_hrs_less_warning
2174 ,p_exceeds_pto_entit_warning => p_exceeds_pto_entit_warning
2175 ,p_exceeds_run_total_warning => p_exceeds_run_total_warning
2176 ,p_abs_overlap_warning => p_abs_overlap_warning
2177 ,p_abs_day_after_warning => p_abs_day_after_warning
2178 ,p_dur_overwritten_warning => p_dur_overwritten_warning);
2179
2180
2181
2182 hr_utility.set_location('Leaving old:'|| l_proc, 5);
2183 end;
2184 -- ---------------------------------------------------------------------------
2185 -- |-----------------< chk_absence_period -new >---------------------------------|
2186 -- ---------------------------------------------------------------------------
2187 --
2188 -- Description:
2189 -- Validates the projected dates, actual dates, times and the duration.
2190 --
2191 -- Pre-conditions:
2192 -- absence_attendance_type_id must be valid.
2193 -- business_group_id must be valid.
2194 -- effective_date must be valid.
2195 --
2196 -- In Arguments:
2197 -- p_absence_attendance_id
2198 -- p_absence_attendance_type_id
2199 -- p_business_group_id
2200 -- p_object_version_number
2201 -- p_effective_date
2202 -- p_person_id
2203 -- p_date_projected_start
2204 -- p_time_projected_start
2205 -- p_date_projected_end
2206 -- p_time_projected_end
2207 -- p_date_start
2208 -- p_time_start
2209 -- p_date_end
2210 -- p_time_end
2211 -- p_ABS_INFORMATION_CATEGORY
2212 -- p_ABS_INFORMATION1
2213 -- p_ABS_INFORMATION2
2214 -- p_ABS_INFORMATION3
2215 -- p_ABS_INFORMATION4
2216 -- p_ABS_INFORMATION5
2217 -- p_ABS_INFORMATION6
2218 --
2219 -- In Out Arguments:
2220 -- p_absence_days
2221 -- p_absence_hours
2222 --
2223 -- Post Success:
2224 -- If validation passes, processing continues.
2225 --
2226 -- Post Failure:
2227 -- IF validation fails, the appropriate error or warning is raised.
2228 --
2229 -- Access Status:
2230 -- Internal Table Handler Use Only.
2231 --
2232 -- {End Of Comments}
2233 -- ----------------------------------------------------------------------------
2234 --
2235 procedure chk_absence_period
2236 (p_absence_attendance_id in number
2237 ,p_absence_attendance_type_id in number
2238 ,p_business_group_id in number
2239 ,p_object_version_number in number
2240 ,p_effective_date in date
2241 ,p_person_id in number
2242 ,p_date_projected_start in date
2243 ,p_time_projected_start in varchar2
2244 ,p_date_projected_end in date
2245 ,p_time_projected_end in varchar2
2246 ,p_date_start in date
2247 ,p_time_start in varchar2
2248 ,p_date_end in date
2249 ,p_time_end in varchar2
2250 ,p_ABS_INFORMATION_CATEGORY in varchar2
2251 ,p_ABS_INFORMATION1 in varchar2
2252 ,p_ABS_INFORMATION2 in varchar2
2253 ,p_ABS_INFORMATION3 in varchar2
2254 ,p_ABS_INFORMATION4 in varchar2
2255 ,p_ABS_INFORMATION5 in varchar2
2256 ,p_ABS_INFORMATION6 in varchar2
2257 ,p_absence_days in out nocopy number
2258 ,p_absence_hours in out nocopy number
2259 ,p_dur_dys_less_warning out nocopy boolean
2260 ,p_dur_hrs_less_warning out nocopy boolean
2261 ,p_exceeds_pto_entit_warning out nocopy boolean
2262 ,p_exceeds_run_total_warning out nocopy boolean
2263 ,p_abs_overlap_warning out nocopy boolean
2264 ,p_abs_day_after_warning out nocopy boolean
2265 ,p_dur_overwritten_warning out nocopy boolean)
2266 is
2267
2268 cursor c_get_absence_type_info is
2269 select abt.hours_or_days,
2270 abt.increasing_or_decreasing_flag,
2271 abt.absence_overlap_flag
2272 from per_absence_attendance_types abt
2273 where abt.absence_attendance_type_id = p_absence_attendance_type_id;
2274
2275 -- Added for Bug11902652
2276 cursor c_abs_overlap_another is
2277 select nvl(abs.time_start,'00:00') start_time,
2278 nvl(abs.time_end,'23:59') end_time
2279 from per_absence_attendances abs
2280 where abs.person_id = p_person_id
2281 and (p_absence_attendance_id is null or
2282 p_absence_attendance_id <> abs.absence_attendance_id)
2283 and abs.date_start is not null
2284 and p_date_start is not null
2285 and (abs.date_start between p_date_start AND nvl(p_date_end,hr_api.g_eot)
2286 OR
2287 p_date_start between abs.date_start and nvl(abs.date_end,hr_api.g_eot) )
2288 order by 1;
2289 -- Added for Bug11902652
2290
2291 cursor c_abs_day_after_another is
2292 select null
2293 from per_absence_attendances abs,
2294 per_absence_attendance_types abt
2295 where abs.person_id = p_person_id
2296 and abs.absence_attendance_type_id = abt.absence_attendance_type_id
2297 and (p_absence_attendance_id is null or
2298 p_absence_attendance_id <> abs.absence_attendance_id)
2299 and abs.date_end = p_date_end -1
2300 and abt.absence_category = 'S';
2301
2302 cursor c_get_accrual_plans (p_assignment_id in number) is
2303 select pap.accrual_plan_id, asg.payroll_id
2304 from pay_element_entries_f pee,
2305 pay_element_links_f pel,
2306 pay_element_types_f pet,
2307 pay_input_values_f piv,
2308 per_all_assignments_f asg,
2309 per_absence_attendance_types abt,
2310 pay_accrual_plans pap
2311 where abt.absence_attendance_type_id = p_absence_attendance_type_id
2312 and abt.input_value_id = piv.input_value_id
2313 and piv.input_value_id = pap.pto_input_value_id
2314 and asg.assignment_id = p_assignment_id
2315 and pee.assignment_id = asg.assignment_id
2316 and pee.element_link_id = pel.element_link_id
2317 and pel.element_type_id = pet.element_type_id
2318 and pet.element_type_id = pap.accrual_plan_element_type_id
2319 and p_effective_date between asg.effective_start_date and
2320 asg.effective_end_date
2321 and p_effective_date between pee.effective_start_date and
2322 pee.effective_end_date
2323 and p_effective_date between pel.effective_start_date and
2324 pel.effective_end_date
2325 and p_effective_date between pet.effective_start_date and
2326 pet.effective_end_date
2327 and p_effective_date between piv.effective_start_date and
2328 piv.effective_end_date;
2329
2330 --
2331
2332 -- code change for ER 7688779 starts here
2333
2334 cursor csr_get_abs_type_name is
2335 select abtl.name from PER_ABS_ATTENDANCE_TYPES_TL abtl
2336 where abtl.absence_attendance_type_id = p_absence_attendance_type_id
2337 and abtl.language = userenv('LANG');
2338
2339 p_abs_type_name varchar2(30);
2340
2341 -- code change for ER 7688779 ends here
2342
2343 l_proc varchar2(72) := g_package||'chk_absence_period';
2344 l_api_updating boolean;
2345 l_exists varchar2(1);
2346 l_hours_or_days varchar2(1);
2347 l_increasing_or_decreasing varchar2(1);
2348 l_absence_days number;
2349 l_absence_hours number;
2350 l_use_formula boolean;
2351 l_auto_overwrite varchar(30);
2352 l_assignment_id number;
2353 l_payroll_id number;
2354 l_accrual_plan_id number;
2355 l_dummy_date date;
2356 l_dummy_number number;
2357 l_net_entitlement number;
2358 l_accrual_msg boolean;
2359 l_running_total_hours number;
2360 l_running_total_days number;
2361 l_year_to_date_hours number;
2362 l_year_to_date_days number;
2363 l_absence_overlap_flag varchar2(1);
2364 --
2365 -- ************* Added for Bug 272978 *************
2366 -- ********* Added for AU,NZ by APAC team *********
2367 l_legislation_code varchar2(3);
2368 l_apac_dummy_number number;
2369 l_apac_entitlement number;
2370 l_apac_accrual number;
2371
2372 cursor csr_legislation(p_business_group_id number) is
2373 select pbg.legislation_code
2374 from per_business_groups pbg
2375 where pbg.business_group_id = p_business_group_id;
2376
2377 -- ********* End Bug 272978 *************
2378
2379 -- fix for the bug 8492746 starts here
2380 l_dur_ent_hours number;
2381 l_dur_calc_hours number;
2382 -- fix for the bug 8492746 ends here
2383
2384
2385 begin
2386
2387 hr_utility.set_location('Entering:'|| l_proc, 5);
2388
2389 --
2390 -- Only proceed with validation if :
2391 -- a) The current g_old_rec is current and
2392 -- b) The date and time values have changed
2393 --
2394 l_api_updating := per_abs_shd.api_updating
2395 (p_absence_attendance_id => p_absence_attendance_id
2396 ,p_object_version_number => p_object_version_number);
2397 --
2398 if (l_api_updating
2399 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
2400 = nvl(p_date_projected_start, hr_api.g_date)
2401 and nvl(per_abs_shd.g_old_rec.time_projected_start, hr_api.g_varchar2)
2402 = nvl(p_time_projected_start, hr_api.g_varchar2)
2403 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
2404 = nvl(p_date_projected_end, hr_api.g_date)
2405 and nvl(per_abs_shd.g_old_rec.time_projected_end, hr_api.g_varchar2)
2406 = nvl(p_time_projected_end, hr_api.g_varchar2)
2407 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
2408 = nvl(p_date_start, hr_api.g_date)
2409 and nvl(per_abs_shd.g_old_rec.time_start, hr_api.g_varchar2)
2410 = nvl(p_time_start, hr_api.g_varchar2)
2411 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
2412 = nvl(p_date_end, hr_api.g_date)
2413 and nvl(per_abs_shd.g_old_rec.time_end, hr_api.g_varchar2)
2414 = nvl(p_time_end, hr_api.g_varchar2)
2415 and nvl(per_abs_shd.g_old_rec.absence_days, hr_api.g_number)
2416 = nvl(p_absence_days, hr_api.g_number)
2417 and nvl(per_abs_shd.g_old_rec.absence_hours, hr_api.g_number)
2418 = nvl(p_absence_hours, hr_api.g_number)) then
2419 return;
2420 end if;
2421
2422 --
2423 -- Initialise the warning messages to false.
2424 --
2425 p_dur_dys_less_warning := FALSE;
2426 p_dur_hrs_less_warning := FALSE;
2427 p_exceeds_pto_entit_warning := FALSE;
2428 p_exceeds_run_total_warning := FALSE;
2429 p_abs_overlap_warning := FALSE;
2430 p_abs_day_after_warning := FALSE;
2431 p_dur_overwritten_warning := FALSE;
2432
2433 --
2434 -- Get the absence type values for use later.
2435 --
2436 open c_get_absence_type_info;
2437 fetch c_get_absence_type_info into l_hours_or_days,
2438 l_increasing_or_decreasing,
2439 l_absence_overlap_flag;
2440 close c_get_absence_type_info;
2441
2442 hr_utility.set_location(l_proc, 10);
2443
2444 --
2445 -- Check the time formats
2446 --
2447 chk_time_format (p_time => p_time_projected_start);
2448 chk_time_format (p_time => p_time_projected_end);
2449 chk_time_format (p_time => p_time_start);
2450 chk_time_format (p_time => p_time_end);
2451
2452 --
2453 -- Check that the start dates are entered if the end dates are entered.
2454 --
2455 if (p_date_projected_start is null and p_date_projected_end is not null)
2456 or (p_date_start is null and p_date_end is not null) then
2457 fnd_message.set_name('PER','HR_289294_ABS_SD_NOT_ENTERED');
2458 fnd_message.raise_error;
2459 end if;
2460
2461 hr_utility.set_location(l_proc, 15);
2462
2463 --
2464 -- Check that the end dates are after the start dates. If they are the same
2465 -- day, check that the end time is after the start time.
2466 --
2467 if p_date_projected_end < p_date_projected_start then
2468 fnd_message.set_name('PER','PAY_7617_EMP_ABS_DATE_AFTER');
2469 fnd_message.raise_error;
2470
2471 elsif p_date_end < p_date_start then
2472 fnd_message.set_name('PER','PAY_7616_EMP_ABS_DATE_AFTER');
2473 fnd_message.raise_error;
2474
2475 elsif p_date_projected_end = p_date_projected_start
2476 and p_time_projected_end < p_time_projected_start then
2477 fnd_message.set_name('PER','PER_7619_EMP_ABS_END_TIME');
2478 fnd_message.raise_error;
2479
2480 elsif p_date_end = p_date_start
2481 and p_time_end < p_time_start then
2482 fnd_message.set_name('PER','PER_7618_EMP_ABS_END_TIME');
2483 fnd_message.raise_error;
2484
2485 end if;
2486
2487 hr_utility.set_location(l_proc, 20);
2488
2489 --
2490 -- Check that times have / have not been entered depending on the
2491 -- UOM of the absence type.
2492 --
2493 if l_hours_or_days = 'D' and
2494 (p_time_projected_start is not null or
2495 p_time_projected_end is not null or
2496 p_time_start is not null or
2497 p_time_end is not null) then
2498 --
2499 -- Times should not have been entered.
2500 --
2501 fnd_message.set_name('PER','HR_289299_ABS_TIME_DISALLOWED');
2502 fnd_message.raise_error;
2503
2504 else
2505 --
2506 -- The unit of measure is either just hours or both days and hours
2507 -- so check that the times have only been entered when the dates have.
2508 --
2509 if p_time_projected_start is not null
2510 and p_date_projected_start is null then
2511 fnd_message.set_name('PER','HR_289297_ABS_PROJ_START_DATE'); -- Fix 2647747
2512 fnd_message.raise_error;
2513
2514 elsif p_time_projected_end is not null
2515 and p_date_projected_end is null then
2516 fnd_message.set_name('PER','PER_7621_EMP_ABS_END_TIME');
2517 fnd_message.raise_error;
2518
2519 elsif p_time_start is not null and p_date_start is null then
2520 fnd_message.set_name('PER','PER_7143_EMP_ABS_START_TIME');
2521 fnd_message.raise_error;
2522
2523 elsif p_time_end is not null and p_date_end is null then
2524 fnd_message.set_name('PER','PER_7620_EMP_ABS_END_TIME');
2525 fnd_message.raise_error;
2526
2527 end if;
2528
2529 end if;
2530
2531 hr_utility.set_location(l_proc, 25);
2532
2533 --
2534 -- If the end date is entered, the duration in days and / or hours
2535 -- must be entered. This can be calculated automatically in some
2536 -- circumstances.
2537 --
2538 if p_date_end is not null then
2539 --
2540 -- Calculate the absence duration.
2541 --
2542 calculate_absence_duration
2543 (p_absence_attendance_id => p_absence_attendance_id
2544 ,p_absence_attendance_type_id => p_absence_attendance_type_id
2545 ,p_business_group_id => p_business_group_id
2546 ,p_object_version_number => p_object_version_number
2547 ,p_effective_date => p_effective_date
2548 ,p_person_id => p_person_id
2549 ,p_date_start => p_date_start
2550 ,p_date_end => p_date_end
2551 ,p_time_start => p_time_start
2552 ,p_time_end => p_time_end
2553 ,p_ABS_INFORMATION_CATEGORY => p_ABS_INFORMATION_CATEGORY
2554 ,p_ABS_INFORMATION1 => p_ABS_INFORMATION1
2555 ,p_ABS_INFORMATION2 => p_ABS_INFORMATION2
2556 ,p_ABS_INFORMATION3 => p_ABS_INFORMATION3
2557 ,p_ABS_INFORMATION4 => p_ABS_INFORMATION4
2558 ,p_ABS_INFORMATION5 => p_ABS_INFORMATION5
2559 ,p_ABS_INFORMATION6 => p_ABS_INFORMATION6
2560 ,p_absence_days => l_absence_days
2561 ,p_absence_hours => l_absence_hours
2562 ,p_use_formula => l_use_formula);
2563
2564 hr_utility.trace ('Calc dys: '||to_char(l_absence_days));
2565 hr_utility.trace ('Calc hrs: '||to_char(l_absence_hours));
2566
2567 --
2568 -- The absence duration is only set if the results returned are
2569 -- from the Fast Formula and the durations are null or
2570 -- auto-overwrite is set to Yes.
2571 --
2572 -- Bug 14659075
2573 l_auto_overwrite :=
2574 nvl(fnd_profile.value('PER_ABSENCE_DURATION_AUTO_OVERWRITE'),'X');
2575
2576 hr_utility.set_location(l_proc, 30);
2577
2578 If (l_use_formula)
2579 and ((l_auto_overwrite = 'Y') or
2580 (p_absence_days is null and p_absence_hours is null)) then
2581 --
2582 -- Set the absence duration in days and hours. If the UOM is set
2583 -- to days, only days are populated, if the UOM is hours, only
2584 -- hours are populated and if no UOM is set, both days and hours
2585 -- are set.
2586 --
2587
2588 hr_utility.trace ('Use Formula = TRUE');
2589
2590 p_absence_days := l_absence_days;
2591 p_absence_hours := l_absence_hours;
2592 p_dur_overwritten_warning := TRUE;
2593
2594 end if;
2595
2596 hr_utility.set_location(l_proc, 35);
2597
2598 -- Bug 14659075
2599 IF l_auto_overwrite ='N' THEN
2600
2601 hr_utility.set_location('per_abs_shd.g_absence_days '||per_abs_shd.g_absence_days, 36);
2602 hr_utility.set_location('per_abs_shd.g_absence_hours '||per_abs_shd.g_absence_hours, 36);
2603
2604 p_absence_days :=per_abs_shd.g_absence_days;
2605 p_absence_hours:=per_abs_shd.g_absence_hours;
2606 p_dur_overwritten_warning := TRUE;
2607
2608 END IF;
2609 --
2610 -- Check that the duration days and / or hours have been entered if the
2611 -- element type is not recurring (recurring entries do not have an
2612 -- input value so do not require the duration days / hours).
2613 --
2614 if hr_person_absence_api.get_processing_type
2615 (p_absence_attendance_type_id) <> 'R' then
2616
2617 if l_hours_or_days = 'D' then
2618 --
2619 -- The UOM is Days so the days duration should be entered, but not
2620 -- the hours duration.
2621 --
2622 if p_absence_days is null then
2623 fnd_message.set_name('PER','HR_51059_ABS_DUR_NOT_ENTERED');
2624 fnd_message.raise_error;
2625
2626 elsif p_absence_hours is not null then
2627 fnd_message.set_name('PER','HR_289298_ABS_HRS_DISALLOWED');
2628 fnd_message.raise_error;
2629
2630 end if;
2631
2632 elsif l_hours_or_days = 'H' then
2633 --
2634 -- The UOM is Hours so the hours duration should be entered, but not
2635 -- the days duration.
2636 --
2637 if p_absence_hours is null then
2638 fnd_message.set_name('PER','HR_51059_ABS_DUR_NOT_ENTERED');
2639 fnd_message.raise_error;
2640
2641 elsif p_absence_days is not null then
2642 fnd_message.set_name('PER','HR_289300_ABS_DYS_DISALLOWED');
2643 fnd_message.raise_error;
2644
2645 end if;
2646
2647 else
2648 --
2649 -- No UOM is set so either days or hours can be entered (or both).
2650 --
2651 if p_absence_hours is null and p_absence_days is null then
2652 fnd_message.set_name('PER','HR_51059_ABS_DUR_NOT_ENTERED');
2653 fnd_message.raise_error;
2654 end if;
2655
2656 end if;
2657
2658 end if;
2659
2660 end if;
2661
2662 hr_utility.set_location(l_proc, 40);
2663
2664 -- fix for the bug 8492746 starts here
2665
2666 l_dur_ent_hours := nvl(p_absence_hours,l_absence_hours);
2667
2668 l_dur_calc_hours := (fffunc.days_between(p_date_end,p_date_start)+1)*24;
2669
2670 if (l_dur_ent_hours > l_dur_calc_hours)
2671 then
2672
2673 fnd_message.set_name('PER','PER_449852_ABS_HOUR_DUR');
2674 hr_utility.set_message_token('START', p_date_start);
2675 hr_utility.set_message_token('END', p_date_end);
2676 fnd_message.raise_error;
2677
2678 end if;
2679
2680 -- fix for the bug 8492746 ends here
2681
2682 --
2683 -- Check that the dates and / or times have been entered if the duration
2684 -- has been entered.
2685 --
2686 if p_absence_days is not null
2687 and (p_date_start is null or p_date_end is null) then
2688 fnd_message.set_name('PER','PER_7714_ABS_CALC_DURATION');
2689 fnd_message.raise_error;
2690 /*
2691 elsif p_absence_hours is not null
2692 and (p_time_start is null or p_time_end is null) then
2693 fnd_message.set_name('PER','PER_7145_EMP_ABS_UNPAID_HOURS');
2694 fnd_message.raise_error;
2695 */
2696 end if;
2697
2698 hr_utility.set_location(l_proc, 45);
2699
2700 --
2701 -- Check if the absence duration in days differs from
2702 -- the amount of time absent.
2703 --
2704 if (p_absence_days <> l_absence_days) then
2705 --
2706 -- Set the warning message.
2707 --
2708 p_dur_dys_less_warning := TRUE;
2709
2710
2711 -- Commented as a fix for the bug 4606467
2712 /*
2713 elsif p_absence_days > l_absence_days then
2714 --
2715 -- Raise the error message. The duration cannot be greater than
2716 -- the system calculated duration.
2717 --
2718 fnd_message.set_name('PER','PER_7622_EMP_ABS_LONG_DURATION');
2719 fnd_message.raise_error;*/
2720
2721 end if;
2722
2723 --
2724 -- Check if the absence duration in hours differs from
2725 -- the amount of time absent.
2726 --
2727 if (p_absence_hours <> l_absence_hours) then
2728 --
2729 -- Set the warning message.
2730 --
2731 p_dur_hrs_less_warning := TRUE;
2732
2733
2734 -- Commented as a fix for the bug 4606467
2735 /* elsif p_absence_hours > l_absence_hours then
2736 --
2737 -- Raise the error message. The duration cannot be greater than
2738 -- the system calculated duration.
2739 --
2740 fnd_message.set_name('PER','PER_7623_EMP_ABS_LONG_DURATION');
2741 fnd_message.raise_error;*/
2742
2743 end if;
2744
2745 hr_utility.set_location(l_proc, 50);
2746
2747 --
2748 -- Check if this absence exceeds the net entitlement on any of the
2749 -- employee's accrual plans. First the assignment id and plan id(s)
2750 -- are required.
2751 --
2752 if p_absence_days is not null or p_absence_hours is not null then
2753
2754 l_assignment_id := hr_person_absence_api.get_primary_assignment
2755 (p_person_id => p_person_id
2756 ,p_effective_date => p_effective_date);
2757
2758 declare
2759
2760 l_message_text fnd_new_messages.message_text%TYPE;
2761
2762 begin
2763
2764 open c_get_accrual_plans (l_assignment_id);
2765 loop
2766
2767 fetch c_get_accrual_plans into l_accrual_plan_id, l_payroll_id;
2768 exit when c_get_accrual_plans%notfound;
2769
2770 --
2771 -- Get the net entitlement of each plan.
2772 --
2773
2774 -- ****************** Added for Bug 2729784 ***************
2775 -- ************** Added for AU,NZ by APAC team ************
2776
2777 open csr_legislation(p_business_group_id);
2778 fetch csr_legislation into l_legislation_code;
2779 if csr_legislation%notfound
2780 then
2781 close csr_legislation;
2782 --
2783 -- The primary key is invalid therefore we must error
2784 --
2785 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2786 fnd_message.raise_error;
2787 end if;
2788 close csr_legislation;
2789
2790 if l_legislation_code = 'AU'
2791 then
2792 hr_utility.set_location('APAC code - '||l_proc,52);
2793 l_net_entitlement := hr_au_holidays.get_net_accrual
2794 (p_assignment_id => l_assignment_id
2795 ,p_payroll_id => l_payroll_id
2796 ,p_business_group_id => p_business_group_id
2797 ,p_plan_id => l_accrual_plan_id
2798 ,p_calculation_date => nvl(p_date_start, p_effective_date)
2799 );
2800 elsif l_legislation_code = 'NZ'
2801 then
2802 hr_utility.set_location(l_proc,53);
2803 l_net_entitlement := hr_nz_holidays.get_net_accrual
2804 (p_assignment_id => l_assignment_id
2805 ,p_payroll_id => l_payroll_id
2806 ,p_business_group_id => p_business_group_id
2807 ,p_plan_id => l_accrual_plan_id
2808 ,p_calculation_date => nvl(p_date_start, p_effective_date)
2809 );
2810
2811 else
2812 hr_utility.set_location('APAC code - '||l_proc,54);
2813 -- ********** existing code wrapped by if statement from bug 2729784 *************
2814 per_accrual_calc_functions.get_net_accrual
2815 (p_assignment_id => l_assignment_id
2816 ,p_plan_id => l_accrual_plan_id
2817 ,p_payroll_id => l_payroll_id
2818 ,p_business_group_id => p_business_group_id
2819 ,p_calculation_date => nvl(p_date_start, p_effective_date)
2820 ,p_start_date => l_dummy_date
2821 ,p_end_date => l_dummy_date
2822 ,p_accrual_end_date => l_dummy_date
2823 ,p_accrual => l_dummy_number
2824 ,p_net_entitlement => l_net_entitlement);
2825 -- ********** end existing code ***************
2826
2827 end if;
2828 hr_utility.trace('Ent = '||to_char(l_net_entitlement));
2829
2830 -- ****************** End Bug 2729784 *********************
2831
2832
2833
2834 --
2835 -- The duration value in g_old_rec is first added to the net
2836 -- entitlement. This prevents absence records that are being
2837 -- updated from being double counted (get_net_accrual has already
2838 -- included the absence in the calculation of the net entitlement).
2839 --
2840 if l_hours_or_days = 'H' then
2841 l_net_entitlement := l_net_entitlement
2842 + nvl(per_abs_shd.g_old_rec.absence_hours, 0)
2843 - p_absence_hours;
2844
2845 elsif l_hours_or_days = 'D' then
2846 l_net_entitlement := l_net_entitlement
2847 + nvl(per_abs_shd.g_old_rec.absence_days, 0)
2848 - p_absence_days;
2849 end if;
2850
2851 --
2852 -- Check if the new net entitlement is less than zero.
2853 --
2854 if l_net_entitlement < 0 then
2855 --
2856 -- Instead of raising the message here, the boolean value is set.
2857 -- This prevents the message appearing multiple times.
2858 --
2859 l_accrual_msg := TRUE;
2860 end if;
2861
2862 end loop;
2863 close c_get_accrual_plans;
2864
2865 exception
2866
2867 when others then
2868
2869 l_message_text := fnd_message.get;
2870 hr_utility.trace('Unable to execute the PTO formula. '||l_message_text);
2871
2872 end;
2873
2874 if (l_accrual_msg) then
2875 -- code change for 7688779 starts here
2876 if (nvl(fnd_profile.value('HR_ALLOW_NEG_ABS_BAL'),'Y') = 'N') then
2877
2878 open csr_get_abs_type_name;
2879 fetch csr_get_abs_type_name into p_abs_type_name;
2880 close csr_get_abs_type_name;
2881
2882 fnd_message.set_name('PER','PER_449875_ABS_NEGBAL');
2883 fnd_message.set_token('ABSTYPE',p_abs_type_name);
2884 fnd_message.raise_error;
2885 else
2886
2887 p_exceeds_pto_entit_warning := TRUE;
2888 end if;
2889 -- code change for 7688779 ends here
2890 end if;
2891
2892 end if;
2893
2894 hr_utility.set_location(l_proc, 55);
2895
2896 --
2897 -- Get the running totals and check that the values do not
2898 -- decrease to less than zero.
2899 --
2900 get_running_totals
2901 (p_person_id => p_person_id
2902 ,p_absence_attendance_type_id => p_absence_attendance_type_id
2903 ,p_effective_date => p_effective_date
2904 ,p_running_total_hours => l_running_total_hours
2905 ,p_running_total_days => l_running_total_days
2906 ,p_year_to_date_hours => l_year_to_date_hours
2907 ,p_year_to_date_days => l_year_to_date_days);
2908
2909 --
2910 -- Here the value of g_old_rec is subtracted first. This prevents
2911 -- records already included in l_running_total_hours / days
2912 -- being double counted (this would occur during update only).
2913 --
2914 if l_increasing_or_decreasing = 'D'
2915 and (l_running_total_hours +
2916 nvl(per_abs_shd.g_old_rec.absence_hours, 0) - p_absence_hours < 0
2917 or l_running_total_days +
2918 nvl(per_abs_shd.g_old_rec.absence_days, 0) - p_absence_days < 0)
2919 then
2920
2921 -- code change for 7688779 starts here
2922 if (nvl(fnd_profile.value('HR_ALLOW_NEG_ABS_BAL'),'Y') = 'N') then
2923
2924 open csr_get_abs_type_name;
2925 fetch csr_get_abs_type_name into p_abs_type_name;
2926 close csr_get_abs_type_name;
2927
2928 fnd_message.set_name('PER','PER_449875_ABS_NEGBAL');
2929 fnd_message.set_token('ABSTYPE',p_abs_type_name);
2930 fnd_message.raise_error;
2931 else
2932
2933 p_exceeds_run_total_warning := TRUE;
2934 end if;
2935 -- code change for 7688779 ends here
2936
2937 hr_utility.set_location(l_proc, 57);
2938
2939 end if;
2940
2941 hr_utility.set_location(l_proc, 60);
2942
2943 --
2944 -- Check if this absence overlaps another absence for the same person.
2945 --
2946 -- Fix for the Bug11902652
2947
2948 for i in c_abs_overlap_another
2949 loop
2950 hr_utility.trace('check_abs_overlap i.end_time:'||i.end_time);
2951
2952 if nvl(p_time_start,'00:00') <= i.end_time
2953 OR i.start_time <= p_time_start
2954 then
2955
2956 if nvl(l_absence_overlap_flag,'N') = 'N' then -- bug 8269612
2957
2958 p_abs_overlap_warning := TRUE;
2959 exit;
2960
2961 end if;
2962
2963 end if;
2964
2965 end loop;
2966 -- Fix for the Bug11902652
2967
2968 hr_utility.set_location(l_proc, 65);
2969
2970 --
2971 -- Check if this is a sickness absence that starts the day after another
2972 -- sickness absence for this person.
2973 --
2974 open c_abs_day_after_another;
2975 fetch c_abs_day_after_another into l_exists;
2976
2977 if c_abs_day_after_another%found then
2978 --
2979 -- Set the warning message
2980 --
2981 p_abs_day_after_warning := TRUE;
2982
2983 end if;
2984
2985 --
2986 hr_utility.set_location('Leaving:'|| l_proc, 70);
2987
2988 end chk_absence_period;
2989 --
2990 -- ---------------------------------------------------------------------------
2991 -- |----------------------< chk_replacement_person_id >---------------------|
2992 -- ---------------------------------------------------------------------------
2993 --
2994 -- Description:
2995 -- Validates that the person exists, that they have a valid period of
2996 -- service and that they match the business group id being passed.
2997 --
2998 -- Pre-conditions:
2999 --
3000 -- In Arguments:
3001 -- p_absence_attendance_id
3002 -- p_replacement_person_id
3003 -- p_business_group_id
3004 --
3005 -- Post Success:
3006 -- If the person and their period of service are valid, processing
3007 -- continues.
3008 --
3009 -- Post Failure:
3010 -- An application error will be raised and processing is terminated.
3011 --
3012 -- Access Status:
3013 -- Internal Development Use Only.
3014 --
3015 -- {End Of Comments}
3016 -- ----------------------------------------------------------------------------
3017 --
3018 procedure chk_replacement_person_id
3019 (p_absence_attendance_id in number
3020 ,p_replacement_person_id in number
3021 ,p_business_group_id in number
3022 ,p_object_version_number in number
3023 ,p_date_projected_start in date
3024 ,p_date_projected_end in date
3025 ,p_date_start in date
3026 ,p_date_end in date)
3027 is
3028
3029 --
3030 l_proc varchar2(72) := g_package||'chk_replacement_person_id';
3031 l_api_updating boolean;
3032 --
3033
3034 begin
3035
3036 hr_utility.set_location('Entering:'|| l_proc, 10);
3037 --
3038 hr_api.mandatory_arg_error
3039 (p_api_name => l_proc
3040 ,p_argument => 'p_business_group_id'
3041 ,p_argument_value => p_business_group_id
3042 );
3043
3044 --
3045 -- Only proceed with validation if :
3046 -- a) The current g_old_rec is current and
3047 -- b) The date values have changed
3048 --
3049 l_api_updating := per_abs_shd.api_updating
3050 (p_absence_attendance_id => p_absence_attendance_id
3051 ,p_object_version_number => p_object_version_number);
3052 --
3053 if (l_api_updating
3054 and nvl(per_abs_shd.g_old_rec.replacement_person_id, hr_api.g_number)
3055 = nvl(p_replacement_person_id, hr_api.g_number)
3056 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
3057 = nvl(p_date_projected_start, hr_api.g_date)
3058 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
3059 = nvl(p_date_projected_end, hr_api.g_date)
3060 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
3061 = nvl(p_date_start, hr_api.g_date)
3062 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
3063 = nvl(p_date_end, hr_api.g_date)) then
3064 return;
3065 end if;
3066
3067 if p_replacement_person_id is not null then
3068 --
3069 -- Check that the replacement exists and that their period of service
3070 -- is valid for the entire absence duration.
3071 --
3072 if not per_valid_for_absence
3073 (p_person_id => p_replacement_person_id
3074 ,p_business_group_id => p_business_group_id
3075 ,p_date_projected_start => p_date_projected_start
3076 ,p_date_projected_end => p_date_projected_end
3077 ,p_date_start => p_date_start
3078 ,p_date_end => p_date_end)
3079 then
3080
3081 fnd_message.set_name('PER', 'HR_7553_ASS_REP_INVALID');
3082 fnd_message.raise_error;
3083
3084 end if;
3085
3086 end if;
3087
3088 hr_utility.set_location(' Leaving:'|| l_proc, 20);
3089
3090 end chk_replacement_person_id;
3091 --
3092 --
3093 -- ---------------------------------------------------------------------------
3094 -- |----------------------< chk_authorising_person_id >---------------------|
3095 -- ---------------------------------------------------------------------------
3096 --
3097 -- Description:
3098 -- Validates that the person exists, that they have a valid period of
3099 -- service and that they match the business group id being passed.
3100 --
3101 -- Pre-conditions:
3102 --
3103 -- In Arguments:
3104 -- p_absence_attendance_id
3105 -- p_replacement_person_id
3106 -- p_business_group_id
3107 --
3108 -- Post Success:
3109 -- If the person and their period of service are valid, processing
3110 -- continues.
3111 --
3112 -- Post Failure:
3113 -- An application error will be raised and processing is terminated.
3114 --
3115 -- Access Status:
3116 -- Internal Development Use Only.
3117 --
3118 -- {End Of Comments}
3119 -- ----------------------------------------------------------------------------
3120 --
3121 procedure chk_authorising_person_id
3122 (p_absence_attendance_id in number
3123 ,p_authorising_person_id in number
3124 ,p_business_group_id in number
3125 ,p_object_version_number in number
3126 ,p_date_projected_start in date
3127 ,p_date_projected_end in date
3128 ,p_date_start in date
3129 ,p_date_end in date)
3130 is
3131
3132 --
3133 l_proc varchar2(72) := g_package||'chk_authorising_person_id';
3134 l_api_updating boolean;
3135 --
3136
3137 begin
3138
3139 hr_utility.set_location('Entering:'|| l_proc, 10);
3140 --
3141 hr_api.mandatory_arg_error
3142 (p_api_name => l_proc
3143 ,p_argument => 'p_business_group_id'
3144 ,p_argument_value => p_business_group_id
3145 );
3146
3147 --
3148 -- Only proceed with validation if :
3149 -- a) The current g_old_rec is current and
3150 -- b) The date values have changed
3151 --
3152 l_api_updating := per_abs_shd.api_updating
3153 (p_absence_attendance_id => p_absence_attendance_id
3154 ,p_object_version_number => p_object_version_number);
3155 --
3156 if (l_api_updating
3157 and nvl(per_abs_shd.g_old_rec.authorising_person_id, hr_api.g_number)
3158 = nvl(p_authorising_person_id, hr_api.g_number)
3159 and nvl(per_abs_shd.g_old_rec.date_projected_start, hr_api.g_date)
3160 = nvl(p_date_projected_start, hr_api.g_date)
3161 and nvl(per_abs_shd.g_old_rec.date_projected_end, hr_api.g_date)
3162 = nvl(p_date_projected_end, hr_api.g_date)
3163 and nvl(per_abs_shd.g_old_rec.date_start, hr_api.g_date)
3164 = nvl(p_date_start, hr_api.g_date)
3165 and nvl(per_abs_shd.g_old_rec.date_end, hr_api.g_date)
3166 = nvl(p_date_end, hr_api.g_date)) then
3167 return;
3168 end if;
3169
3170 if p_authorising_person_id is not null then
3171 --
3172 -- Check that the authorisor exists and that their period of service
3173 -- is valid for the entire absence duration.
3174 --
3175 if not per_valid_for_absence
3176 (p_person_id => p_authorising_person_id
3177 ,p_business_group_id => p_business_group_id
3178 ,p_date_projected_start => p_date_projected_start
3179 ,p_date_projected_end => p_date_projected_end
3180 ,p_date_start => p_date_start
3181 ,p_date_end => p_date_end)
3182 then
3183
3184 fnd_message.set_name('PER', 'HR_7552_ASS_AUTH_INVALID');
3185 fnd_message.raise_error;
3186
3187 end if;
3188
3189 end if;
3190
3191 hr_utility.set_location(' Leaving:'|| l_proc, 20);
3192
3193 end chk_authorising_person_id;
3194 --
3195 -- ---------------------------------------------------------------------------
3196 -- |----------------------< set_security_group_id >--------------------------|
3197 -- ---------------------------------------------------------------------------
3198 --
3199 Procedure set_security_group_id
3200 (p_absence_attendance_id in number
3201 ) is
3202 --
3203 -- Declare cursor
3204 --
3205 cursor csr_sec_grp is
3206 select pbg.security_group_id
3207 from per_business_groups pbg
3208 , per_absence_attendances abs
3209 where abs.absence_attendance_id = p_absence_attendance_id
3210 and pbg.business_group_id = abs.business_group_id;
3211 --
3212 -- Declare local variables
3213 --
3214 l_security_group_id number;
3215 l_proc varchar2(72) := g_package||'set_security_group_id';
3216 --
3217 begin
3218 --
3219 hr_utility.set_location('Entering:'|| l_proc, 10);
3220 --
3221 -- Ensure that all the mandatory parameter are not null
3222 --
3223 hr_api.mandatory_arg_error
3224 (p_api_name => l_proc
3225 ,p_argument => 'absence_attendance_id'
3226 ,p_argument_value => p_absence_attendance_id
3227 );
3228 --
3229 open csr_sec_grp;
3230 fetch csr_sec_grp into l_security_group_id;
3231 --
3232 if csr_sec_grp%notfound then
3233 --
3234 close csr_sec_grp;
3235 --
3236 -- The primary key is invalid therefore we must error
3237 --
3238 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
3239 fnd_message.raise_error;
3240 --
3241 end if;
3242 close csr_sec_grp;
3243 --
3244 -- Set the security_group_id in CLIENT_INFO
3245 --
3246 hr_api.set_security_group_id
3247 (p_security_group_id => l_security_group_id
3248 );
3249 --
3250 hr_utility.set_location(' Leaving:'|| l_proc, 20);
3251 --
3252 end set_security_group_id;
3253 --
3254 -- ---------------------------------------------------------------------------
3255 -- |---------------------< return_legislation_code >-------------------------|
3256 -- ---------------------------------------------------------------------------
3257 --
3258 Function return_legislation_code
3259 (p_absence_attendance_id in number
3260 )
3261 Return Varchar2 Is
3262 --
3263 -- Declare cursor
3264 --
3265 cursor csr_leg_code is
3266 select pbg.legislation_code
3267 from per_business_groups pbg
3268 , per_absence_attendances abs
3269 where abs.absence_attendance_id = p_absence_attendance_id
3270 and pbg.business_group_id = abs.business_group_id;
3271 --
3272 -- Declare local variables
3273 --
3274 l_legislation_code varchar2(150);
3275 l_proc varchar2(72) := g_package||'return_legislation_code';
3276 --
3277 Begin
3278 --
3279 hr_utility.set_location('Entering:'|| l_proc, 10);
3280 --
3281 -- Ensure that all the mandatory parameter are not null
3282 --
3283 hr_api.mandatory_arg_error
3284 (p_api_name => l_proc
3285 ,p_argument => 'absence_attendance_id'
3286 ,p_argument_value => p_absence_attendance_id
3287 );
3288 --
3289 if ( nvl(per_abs_bus.g_absence_attendance_id, hr_api.g_number)
3290 = p_absence_attendance_id) then
3291 --
3292 -- The legislation code has already been found with a previous
3293 -- call to this function. Just return the value in the global
3294 -- variable.
3295 --
3296 l_legislation_code := per_abs_bus.g_legislation_code;
3297 hr_utility.set_location(l_proc, 20);
3298 else
3299 --
3300 -- The ID is different to the last call to this function
3301 -- or this is the first call to this function.
3302 --
3303 open csr_leg_code;
3304 fetch csr_leg_code into l_legislation_code;
3305 --
3306 if csr_leg_code%notfound then
3307 --
3308 -- The primary key is invalid therefore we must error
3309 --
3310 close csr_leg_code;
3311 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
3312 fnd_message.raise_error;
3313 end if;
3314 hr_utility.set_location(l_proc,30);
3315 --
3316 -- Set the global variables so the values are
3317 -- available for the next call to this function.
3318 --
3319 close csr_leg_code;
3320 per_abs_bus.g_absence_attendance_id:= p_absence_attendance_id;
3321 per_abs_bus.g_legislation_code := l_legislation_code;
3322 end if;
3323 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3324 return l_legislation_code;
3325 end return_legislation_code;
3326 --
3327 -- ----------------------------------------------------------------------------
3328 -- |-----------------------------< chk_ddf >----------------------------------|
3329 -- ----------------------------------------------------------------------------
3330 --
3331 -- Description:
3332 -- Validates all the Developer Descriptive Flexfield values.
3333 --
3334 -- Prerequisites:
3335 -- All other columns have been validated. Must be called as the
3336 -- last step from insert_validate and update_validate.
3337 --
3338 -- In Arguments:
3339 -- p_rec
3340 --
3341 -- Post Success:
3342 -- If the Developer Descriptive Flexfield structure column and data values
3343 -- are all valid this procedure will end normally and processing will
3344 -- continue.
3345 --
3346 -- Post Failure:
3347 -- If the Developer Descriptive Flexfield structure column value or any of
3348 -- the data values are invalid then an application error is raised as
3349 -- a PL/SQL exception.
3350 --
3351 -- Access Status:
3352 -- Internal Row Handler Use Only.
3353 --
3354 -- ----------------------------------------------------------------------------
3355 --
3356 procedure chk_ddf
3357 (p_rec in per_abs_shd.g_rec_type
3358 ) is
3359 --
3360 l_proc varchar2(72) := g_package || 'chk_ddf';
3361 --
3362 begin
3363 hr_utility.set_location('Entering:'||l_proc,10);
3364 --
3365 if ((p_rec.absence_attendance_id is not null) and (
3366 nvl(per_abs_shd.g_old_rec.abs_information_category, hr_api.g_varchar2) <>
3367 nvl(p_rec.abs_information_category, hr_api.g_varchar2) or
3368 nvl(per_abs_shd.g_old_rec.abs_information1, hr_api.g_varchar2) <>
3369 nvl(p_rec.abs_information1, hr_api.g_varchar2) or
3370 nvl(per_abs_shd.g_old_rec.abs_information2, hr_api.g_varchar2) <>
3371 nvl(p_rec.abs_information2, hr_api.g_varchar2) or
3372 nvl(per_abs_shd.g_old_rec.abs_information3, hr_api.g_varchar2) <>
3373 nvl(p_rec.abs_information3, hr_api.g_varchar2) or
3374 nvl(per_abs_shd.g_old_rec.abs_information4, hr_api.g_varchar2) <>
3375 nvl(p_rec.abs_information4, hr_api.g_varchar2) or
3376 nvl(per_abs_shd.g_old_rec.abs_information5, hr_api.g_varchar2) <>
3377 nvl(p_rec.abs_information5, hr_api.g_varchar2) or
3378 nvl(per_abs_shd.g_old_rec.abs_information6, hr_api.g_varchar2) <>
3379 nvl(p_rec.abs_information6, hr_api.g_varchar2) or
3380 nvl(per_abs_shd.g_old_rec.abs_information7, hr_api.g_varchar2) <>
3381 nvl(p_rec.abs_information7, hr_api.g_varchar2) or
3382 nvl(per_abs_shd.g_old_rec.abs_information8, hr_api.g_varchar2) <>
3383 nvl(p_rec.abs_information8, hr_api.g_varchar2) or
3384 nvl(per_abs_shd.g_old_rec.abs_information9, hr_api.g_varchar2) <>
3385 nvl(p_rec.abs_information9, hr_api.g_varchar2) or
3386 nvl(per_abs_shd.g_old_rec.abs_information10, hr_api.g_varchar2) <>
3387 nvl(p_rec.abs_information10, hr_api.g_varchar2) or
3388 nvl(per_abs_shd.g_old_rec.abs_information11, hr_api.g_varchar2) <>
3389 nvl(p_rec.abs_information11, hr_api.g_varchar2) or
3390 nvl(per_abs_shd.g_old_rec.abs_information12, hr_api.g_varchar2) <>
3391 nvl(p_rec.abs_information12, hr_api.g_varchar2) or
3392 nvl(per_abs_shd.g_old_rec.abs_information13, hr_api.g_varchar2) <>
3393 nvl(p_rec.abs_information13, hr_api.g_varchar2) or
3394 nvl(per_abs_shd.g_old_rec.abs_information14, hr_api.g_varchar2) <>
3395 nvl(p_rec.abs_information14, hr_api.g_varchar2) or
3396 nvl(per_abs_shd.g_old_rec.abs_information15, hr_api.g_varchar2) <>
3397 nvl(p_rec.abs_information15, hr_api.g_varchar2) or
3398 nvl(per_abs_shd.g_old_rec.abs_information16, hr_api.g_varchar2) <>
3399 nvl(p_rec.abs_information16, hr_api.g_varchar2) or
3400 nvl(per_abs_shd.g_old_rec.abs_information17, hr_api.g_varchar2) <>
3401 nvl(p_rec.abs_information17, hr_api.g_varchar2) or
3402 nvl(per_abs_shd.g_old_rec.abs_information18, hr_api.g_varchar2) <>
3403 nvl(p_rec.abs_information18, hr_api.g_varchar2) or
3404 nvl(per_abs_shd.g_old_rec.abs_information19, hr_api.g_varchar2) <>
3405 nvl(p_rec.abs_information19, hr_api.g_varchar2) or
3406 nvl(per_abs_shd.g_old_rec.abs_information20, hr_api.g_varchar2) <>
3407 nvl(p_rec.abs_information20, hr_api.g_varchar2) or
3408 nvl(per_abs_shd.g_old_rec.abs_information21, hr_api.g_varchar2) <>
3409 nvl(p_rec.abs_information21, hr_api.g_varchar2) or
3410 nvl(per_abs_shd.g_old_rec.abs_information22, hr_api.g_varchar2) <>
3411 nvl(p_rec.abs_information22, hr_api.g_varchar2) or
3412 nvl(per_abs_shd.g_old_rec.abs_information23, hr_api.g_varchar2) <>
3413 nvl(p_rec.abs_information23, hr_api.g_varchar2) or
3414 nvl(per_abs_shd.g_old_rec.abs_information24, hr_api.g_varchar2) <>
3415 nvl(p_rec.abs_information24, hr_api.g_varchar2) or
3416 nvl(per_abs_shd.g_old_rec.abs_information25, hr_api.g_varchar2) <>
3417 nvl(p_rec.abs_information25, hr_api.g_varchar2) or
3418 nvl(per_abs_shd.g_old_rec.abs_information26, hr_api.g_varchar2) <>
3419 nvl(p_rec.abs_information26, hr_api.g_varchar2) or
3420 nvl(per_abs_shd.g_old_rec.abs_information27, hr_api.g_varchar2) <>
3421 nvl(p_rec.abs_information27, hr_api.g_varchar2) or
3422 nvl(per_abs_shd.g_old_rec.abs_information28, hr_api.g_varchar2) <>
3423 nvl(p_rec.abs_information28, hr_api.g_varchar2) or
3424 nvl(per_abs_shd.g_old_rec.abs_information29, hr_api.g_varchar2) <>
3425 nvl(p_rec.abs_information29, hr_api.g_varchar2) or
3426 nvl(per_abs_shd.g_old_rec.abs_information30, hr_api.g_varchar2) <>
3427 nvl(p_rec.abs_information30, hr_api.g_varchar2) ))
3428 or (p_rec.absence_attendance_id is null) then
3429 --
3430 -- Only execute the validation if absolutely necessary:
3431 -- a) During update, the structure column value or any
3432 -- of the attribute values have actually changed.
3433 -- b) During insert.
3434 --
3435 hr_dflex_utility.ins_or_upd_descflex_attribs
3436 (p_appl_short_name => 'PER'
3437 ,p_descflex_name => 'PER_ABS_DEVELOPER_DF'
3438 ,p_attribute_category => p_rec.abs_information_category
3439 ,p_attribute1_name => 'ABS_INFORMATION1'
3440 ,p_attribute1_value => p_rec.abs_information1
3441 ,p_attribute2_name => 'ABS_INFORMATION2'
3442 ,p_attribute2_value => p_rec.abs_information2
3443 ,p_attribute3_name => 'ABS_INFORMATION3'
3444 ,p_attribute3_value => p_rec.abs_information3
3445 ,p_attribute4_name => 'ABS_INFORMATION4'
3446 ,p_attribute4_value => p_rec.abs_information4
3447 ,p_attribute5_name => 'ABS_INFORMATION5'
3448 ,p_attribute5_value => p_rec.abs_information5
3449 ,p_attribute6_name => 'ABS_INFORMATION6'
3450 ,p_attribute6_value => p_rec.abs_information6
3451 ,p_attribute7_name => 'ABS_INFORMATION7'
3452 ,p_attribute7_value => p_rec.abs_information7
3453 ,p_attribute8_name => 'ABS_INFORMATION8'
3454 ,p_attribute8_value => p_rec.abs_information8
3455 ,p_attribute9_name => 'ABS_INFORMATION9'
3456 ,p_attribute9_value => p_rec.abs_information9
3457 ,p_attribute10_name => 'ABS_INFORMATION10'
3458 ,p_attribute10_value => p_rec.abs_information10
3459 ,p_attribute11_name => 'ABS_INFORMATION11'
3460 ,p_attribute11_value => p_rec.abs_information11
3461 ,p_attribute12_name => 'ABS_INFORMATION12'
3462 ,p_attribute12_value => p_rec.abs_information12
3463 ,p_attribute13_name => 'ABS_INFORMATION13'
3464 ,p_attribute13_value => p_rec.abs_information13
3465 ,p_attribute14_name => 'ABS_INFORMATION14'
3466 ,p_attribute14_value => p_rec.abs_information14
3467 ,p_attribute15_name => 'ABS_INFORMATION15'
3468 ,p_attribute15_value => p_rec.abs_information15
3469 ,p_attribute16_name => 'ABS_INFORMATION16'
3470 ,p_attribute16_value => p_rec.abs_information16
3471 ,p_attribute17_name => 'ABS_INFORMATION17'
3472 ,p_attribute17_value => p_rec.abs_information17
3473 ,p_attribute18_name => 'ABS_INFORMATION18'
3474 ,p_attribute18_value => p_rec.abs_information18
3475 ,p_attribute19_name => 'ABS_INFORMATION19'
3476 ,p_attribute19_value => p_rec.abs_information19
3477 ,p_attribute20_name => 'ABS_INFORMATION20'
3478 ,p_attribute20_value => p_rec.abs_information20
3479 ,p_attribute21_name => 'ABS_INFORMATION21'
3480 ,p_attribute21_value => p_rec.abs_information21
3481 ,p_attribute22_name => 'ABS_INFORMATION22'
3482 ,p_attribute22_value => p_rec.abs_information22
3483 ,p_attribute23_name => 'ABS_INFORMATION23'
3484 ,p_attribute23_value => p_rec.abs_information23
3485 ,p_attribute24_name => 'ABS_INFORMATION24'
3486 ,p_attribute24_value => p_rec.abs_information24
3487 ,p_attribute25_name => 'ABS_INFORMATION25'
3488 ,p_attribute25_value => p_rec.abs_information25
3489 ,p_attribute26_name => 'ABS_INFORMATION26'
3490 ,p_attribute26_value => p_rec.abs_information26
3491 ,p_attribute27_name => 'ABS_INFORMATION27'
3492 ,p_attribute27_value => p_rec.abs_information27
3493 ,p_attribute28_name => 'ABS_INFORMATION28'
3494 ,p_attribute28_value => p_rec.abs_information28
3495 ,p_attribute29_name => 'ABS_INFORMATION29'
3496 ,p_attribute29_value => p_rec.abs_information29
3497 ,p_attribute30_name => 'ABS_INFORMATION30'
3498 ,p_attribute30_value => p_rec.abs_information30
3499 );
3500 end if;
3501 --
3502 hr_utility.set_location(' Leaving:'||l_proc,20);
3503 end chk_ddf;
3504 --
3505 -- ----------------------------------------------------------------------------
3506 -- |------------------------------< chk_df >----------------------------------|
3507 -- ----------------------------------------------------------------------------
3508 --
3509 -- Description:
3510 -- Validates all the Descriptive Flexfield values.
3511 --
3512 -- Prerequisites:
3513 -- All other columns have been validated. Must be called as the
3514 -- last step from insert_validate and update_validate.
3515 --
3516 -- In Arguments:
3517 -- p_rec
3518 --
3519 -- Post Success:
3520 -- If the Descriptive Flexfield structure column and data values are
3521 -- all valid this procedure will end normally and processing will
3522 -- continue.
3523 --
3524 -- Post Failure:
3525 -- If the Descriptive Flexfield structure column value or any of
3526 -- the data values are invalid then an application error is raised as
3527 -- a PL/SQL exception.
3528 --
3529 -- Access Status:
3530 -- Internal Row Handler Use Only.
3531 --
3532 -- ----------------------------------------------------------------------------
3533 --
3534 procedure chk_df
3535 (p_rec in per_abs_shd.g_rec_type
3536 ) is
3537 --
3538 l_proc varchar2(72) := g_package || 'chk_df';
3539 --
3540 begin
3541 hr_utility.set_location('Entering:'||l_proc,10);
3542 --
3543 if ((p_rec.absence_attendance_id is not null) and (
3544 nvl(per_abs_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
3545 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
3546 nvl(per_abs_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
3547 nvl(p_rec.attribute1, hr_api.g_varchar2) or
3548 nvl(per_abs_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
3549 nvl(p_rec.attribute2, hr_api.g_varchar2) or
3550 nvl(per_abs_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
3551 nvl(p_rec.attribute3, hr_api.g_varchar2) or
3552 nvl(per_abs_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
3553 nvl(p_rec.attribute4, hr_api.g_varchar2) or
3554 nvl(per_abs_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
3555 nvl(p_rec.attribute5, hr_api.g_varchar2) or
3556 nvl(per_abs_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
3557 nvl(p_rec.attribute6, hr_api.g_varchar2) or
3558 nvl(per_abs_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
3559 nvl(p_rec.attribute7, hr_api.g_varchar2) or
3560 nvl(per_abs_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
3561 nvl(p_rec.attribute8, hr_api.g_varchar2) or
3562 nvl(per_abs_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
3563 nvl(p_rec.attribute9, hr_api.g_varchar2) or
3564 nvl(per_abs_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
3565 nvl(p_rec.attribute10, hr_api.g_varchar2) or
3566 nvl(per_abs_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
3567 nvl(p_rec.attribute11, hr_api.g_varchar2) or
3568 nvl(per_abs_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
3569 nvl(p_rec.attribute12, hr_api.g_varchar2) or
3570 nvl(per_abs_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
3571 nvl(p_rec.attribute13, hr_api.g_varchar2) or
3572 nvl(per_abs_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
3573 nvl(p_rec.attribute14, hr_api.g_varchar2) or
3574 nvl(per_abs_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
3575 nvl(p_rec.attribute15, hr_api.g_varchar2) or
3576 nvl(per_abs_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
3577 nvl(p_rec.attribute16, hr_api.g_varchar2) or
3578 nvl(per_abs_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
3579 nvl(p_rec.attribute17, hr_api.g_varchar2) or
3580 nvl(per_abs_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
3581 nvl(p_rec.attribute18, hr_api.g_varchar2) or
3582 nvl(per_abs_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
3583 nvl(p_rec.attribute19, hr_api.g_varchar2) or
3584 nvl(per_abs_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
3585 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
3586 or (p_rec.absence_attendance_id is null) then
3587 --
3588 -- Only execute the validation if absolutely necessary:
3589 -- a) During update, the structure column value or any
3590 -- of the attribute values have actually changed.
3591 -- b) During insert.
3592 --
3593 hr_dflex_utility.ins_or_upd_descflex_attribs
3594 (p_appl_short_name => 'PER'
3595 ,p_descflex_name => 'PER_ABSENCE_ATTENDANCES'
3596 ,p_attribute_category => p_rec.attribute_category
3597 ,p_attribute1_name => 'ATTRIBUTE1'
3598 ,p_attribute1_value => p_rec.attribute1
3599 ,p_attribute2_name => 'ATTRIBUTE2'
3600 ,p_attribute2_value => p_rec.attribute2
3601 ,p_attribute3_name => 'ATTRIBUTE3'
3602 ,p_attribute3_value => p_rec.attribute3
3603 ,p_attribute4_name => 'ATTRIBUTE4'
3604 ,p_attribute4_value => p_rec.attribute4
3605 ,p_attribute5_name => 'ATTRIBUTE5'
3606 ,p_attribute5_value => p_rec.attribute5
3607 ,p_attribute6_name => 'ATTRIBUTE6'
3608 ,p_attribute6_value => p_rec.attribute6
3609 ,p_attribute7_name => 'ATTRIBUTE7'
3610 ,p_attribute7_value => p_rec.attribute7
3611 ,p_attribute8_name => 'ATTRIBUTE8'
3612 ,p_attribute8_value => p_rec.attribute8
3613 ,p_attribute9_name => 'ATTRIBUTE9'
3614 ,p_attribute9_value => p_rec.attribute9
3615 ,p_attribute10_name => 'ATTRIBUTE10'
3616 ,p_attribute10_value => p_rec.attribute10
3617 ,p_attribute11_name => 'ATTRIBUTE11'
3618 ,p_attribute11_value => p_rec.attribute11
3619 ,p_attribute12_name => 'ATTRIBUTE12'
3620 ,p_attribute12_value => p_rec.attribute12
3621 ,p_attribute13_name => 'ATTRIBUTE13'
3622 ,p_attribute13_value => p_rec.attribute13
3623 ,p_attribute14_name => 'ATTRIBUTE14'
3624 ,p_attribute14_value => p_rec.attribute14
3625 ,p_attribute15_name => 'ATTRIBUTE15'
3626 ,p_attribute15_value => p_rec.attribute15
3627 ,p_attribute16_name => 'ATTRIBUTE16'
3628 ,p_attribute16_value => p_rec.attribute16
3629 ,p_attribute17_name => 'ATTRIBUTE17'
3630 ,p_attribute17_value => p_rec.attribute17
3631 ,p_attribute18_name => 'ATTRIBUTE18'
3632 ,p_attribute18_value => p_rec.attribute18
3633 ,p_attribute19_name => 'ATTRIBUTE19'
3634 ,p_attribute19_value => p_rec.attribute19
3635 ,p_attribute20_name => 'ATTRIBUTE20'
3636 ,p_attribute20_value => p_rec.attribute20
3637 );
3638 end if;
3639 --
3640 hr_utility.set_location(' Leaving:'||l_proc,20);
3641 end chk_df;
3642 --
3643 -- ----------------------------------------------------------------------------
3644 -- |-----------------------< chk_non_updateable_args >------------------------|
3645 -- ----------------------------------------------------------------------------
3646 -- {Start Of Comments}
3647 --
3648 -- Description:
3649 -- This procedure is used to ensure that non updateable attributes have
3650 -- not been updated. If an attribute has been updated an error is generated.
3651 --
3652 -- Pre Conditions:
3653 -- g_old_rec has been populated with details of the values currently in
3654 -- the database.
3655 --
3656 -- In Arguments:
3657 -- p_rec has been populated with the updated values the user would like the
3658 -- record set to.
3659 --
3660 -- Post Success:
3661 -- Processing continues if all the non updateable attributes have not
3662 -- changed.
3663 --
3664 -- Post Failure:
3665 -- An application error is raised if any of the non updatable attributes
3666 -- have been altered.
3667 --
3668 -- {End Of Comments}
3669 -- ----------------------------------------------------------------------------
3670 Procedure chk_non_updateable_args
3671 (p_effective_date in date
3672 ,p_rec in per_abs_shd.g_rec_type
3673 ) IS
3674 --
3675 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
3676 l_error EXCEPTION;
3677 l_argument varchar2(30);
3678 --
3679 Begin
3680 --
3681 -- Only proceed with the validation if a row exists for the current
3682 -- record in the HR Schema.
3683 --
3684 IF NOT per_abs_shd.api_updating
3685 (p_absence_attendance_id => p_rec.absence_attendance_id
3686 ,p_object_version_number => p_rec.object_version_number
3687 ) THEN
3688 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
3689 fnd_message.set_token('PROCEDURE ', l_proc);
3690 fnd_message.set_token('STEP ', '5');
3691 fnd_message.raise_error;
3692 END IF;
3693 --
3694 hr_utility.set_location(l_proc, 2);
3695 if nvl(p_rec.absence_attendance_id,hr_api.g_number) <>
3696 per_abs_shd.g_old_rec.absence_attendance_id then
3697 l_argument := 'absence_attendance_id';
3698 raise l_error;
3699 end if;
3700 --
3701 hr_utility.set_location(l_proc, 3);
3702 if nvl(p_rec.business_group_id, hr_api.g_number) <>
3703 per_abs_shd.g_old_rec.business_group_id then
3704 l_argument := 'business_group_id';
3705 raise l_error;
3706 end if;
3707 --
3708 hr_utility.set_location(l_proc, 4);
3709 if nvl(p_rec.person_id, hr_api.g_number) <>
3710 per_abs_shd.g_old_rec.person_id then
3711 l_argument := 'person_id';
3712 raise l_error;
3713 end if;
3714 --
3715 hr_utility.set_location(l_proc, 5);
3716 if nvl(p_rec.absence_attendance_type_id,hr_api.g_number) <>
3717 per_abs_shd.g_old_rec.absence_attendance_type_id then
3718 l_argument := 'absence_attendance_type_id';
3719 raise l_error;
3720 end if;
3721 --
3722 hr_utility.set_location(l_proc, 6);
3723 if nvl(p_rec.occurrence,hr_api.g_number) <>
3724 per_abs_shd.g_old_rec.occurrence then
3725 l_argument := 'occurrence';
3726 raise l_error;
3727 end if;
3728 --
3729 EXCEPTION
3730 WHEN l_error THEN
3731 hr_api.argument_changed_error
3732 (p_api_name => l_proc
3733 ,p_argument => l_argument);
3734 WHEN OTHERS THEN
3735 RAISE;
3736 End chk_non_updateable_args;
3737 --
3738 -- ----------------------------------------------------------------------------
3739 -- |---------------------------< insert_validate >----------------------------|
3740 -- ----------------------------------------------------------------------------
3741 Procedure insert_validate
3742 (p_effective_date in date
3743 ,p_rec in per_abs_shd.g_rec_type
3744 ,p_dur_dys_less_warning out nocopy boolean
3745 ,p_dur_hrs_less_warning out nocopy boolean
3746 ,p_exceeds_pto_entit_warning out nocopy boolean
3747 ,p_exceeds_run_total_warning out nocopy boolean
3748 ,p_abs_overlap_warning out nocopy boolean
3749 ,p_abs_day_after_warning out nocopy boolean
3750 ,p_dur_overwritten_warning out nocopy boolean
3751 ) is
3752 --
3753 l_proc varchar2(72) := g_package||'insert_validate';
3754 l_effective_date date; -- Added for bug 3371960
3755
3756 ----Check the gender of the person in case of maternity leave bug# 6505054
3757
3758 cursor c_absence_cat(p_absence_attendance_type_id in number) is
3759 select absence_category from per_absence_attendance_types
3760 where absence_attendance_type_id = p_absence_attendance_type_id;
3761
3762 l_ssp_installed boolean;
3763 l_absence_category varchar2(30);
3764
3765 --
3766 Begin
3767
3768 hr_utility.set_location('Entering:'||l_proc, 5);
3769
3770 --
3771 -- Assign the durations to the global variables.
3772 --
3773 per_abs_shd.g_absence_days := p_rec.absence_days;
3774 per_abs_shd.g_absence_hours := p_rec.absence_hours;
3775
3776 --
3777 -- Fix for bug 3371960 starts here. Use the l_effective_date
3778 -- in chk procedures.
3779 --
3780 l_effective_date := NVL(p_rec.date_start, p_effective_date);
3781 --
3782 -- Fix for bug 3371960 ends here.
3783 --
3784 -- Check the business group id.
3785 --
3786 hr_api.validate_bus_grp_id(p_rec.business_group_id);
3787
3788 hr_utility.set_location(l_proc, 10);
3789
3790 ----Check the gender of the person in case of maternity leave bug# 6505054
3791
3792 open c_absence_cat(p_rec.absence_attendance_type_id);
3793 fetch c_absence_cat into l_absence_category;
3794 close c_absence_cat;
3795
3796 l_ssp_installed := ssp_ssp_pkg.ssp_is_installed;
3797
3798 if l_ssp_installed and l_absence_category = 'M' then
3799
3800 ssp_mat_bus.validate_female_sex(p_rec.person_id);
3801
3802 end if;
3803
3804 -- end for bug# 6505054
3805
3806 --
3807 -- Check person ID.
3808 --
3809 chk_person_id
3810 (p_absence_attendance_id => p_rec.absence_attendance_id
3811 ,p_person_id => p_rec.person_id
3812 ,p_business_group_id => p_rec.business_group_id
3813 ,p_object_version_number => p_rec.object_version_number
3814 ,p_date_projected_start => p_rec.date_projected_start
3815 ,p_date_projected_end => p_rec.date_projected_end
3816 ,p_date_start => p_rec.date_start
3817 ,p_date_end => p_rec.date_end);
3818
3819 --
3820 -- Check absence attendance type ID
3821 --
3822 chk_absence_attendance_type_id
3823 (p_absence_attendance_id => p_rec.absence_attendance_id
3824 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3825 ,p_business_group_id => p_rec.business_group_id
3826 ,p_object_version_number => p_rec.object_version_number
3827 ,p_date_projected_start => p_rec.date_projected_start
3828 ,p_date_projected_end => p_rec.date_projected_end
3829 ,p_date_start => p_rec.date_start
3830 ,p_date_end => p_rec.date_end);
3831
3832 --
3833 -- Check the absence reason ID.
3834 --
3835 chk_abs_attendance_reason_id
3836 (p_absence_attendance_id => p_rec.absence_attendance_id
3837 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3838 ,p_abs_attendance_reason_id => p_rec.abs_attendance_reason_id
3839 ,p_business_group_id => p_rec.business_group_id
3840 ,p_object_version_number => p_rec.object_version_number
3841 ,p_effective_date => l_effective_date);
3842
3843 --
3844 -- Check absence period.
3845 --
3846 -- Check the absence period (this includes all duration validation).
3847 -- The durations are in out parameters and are assigned to global
3848 -- variables. This is because they will be over-written during
3849 -- pre insert / pre update if the Fast Formula Auto-Overwrite duration
3850 -- profile option is set to yes during pre_insert.
3851 --
3852 chk_absence_period
3853 (p_absence_attendance_id => p_rec.absence_attendance_id
3854 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3855 ,p_business_group_id => p_rec.business_group_id
3856 ,p_object_version_number => p_rec.object_version_number
3857 ,p_effective_date => l_effective_date
3858 ,p_person_id => p_rec.person_id
3859 ,p_date_projected_start => p_rec.date_projected_start
3860 ,p_time_projected_start => p_rec.time_projected_start
3861 ,p_date_projected_end => p_rec.date_projected_end
3862 ,p_time_projected_end => p_rec.time_projected_end
3863 ,p_date_start => p_rec.date_start
3864 ,p_time_start => p_rec.time_start
3865 ,p_date_end => p_rec.date_end
3866 ,p_time_end => p_rec.time_end
3867 ,p_ABS_INFORMATION_CATEGORY =>p_rec.ABS_INFORMATION_CATEGORY
3868 ,p_ABS_INFORMATION1 =>p_rec.ABS_INFORMATION1
3869 ,p_ABS_INFORMATION2 =>p_rec.ABS_INFORMATION2
3870 ,p_ABS_INFORMATION3 =>p_rec.ABS_INFORMATION3
3871 ,p_ABS_INFORMATION4 =>p_rec.ABS_INFORMATION4
3872 ,p_ABS_INFORMATION5 =>p_rec.ABS_INFORMATION5
3873 ,p_ABS_INFORMATION6 =>p_rec.ABS_INFORMATION6
3874 ,p_absence_days => per_abs_shd.g_absence_days
3875 ,p_absence_hours => per_abs_shd.g_absence_hours
3876 ,p_dur_dys_less_warning => p_dur_dys_less_warning
3877 ,p_dur_hrs_less_warning => p_dur_hrs_less_warning
3878 ,p_exceeds_pto_entit_warning => p_exceeds_pto_entit_warning
3879 ,p_exceeds_run_total_warning => p_exceeds_run_total_warning
3880 ,p_abs_overlap_warning => p_abs_overlap_warning
3881 ,p_abs_day_after_warning => p_abs_day_after_warning
3882 ,p_dur_overwritten_warning => p_dur_overwritten_warning);
3883
3884 --
3885 -- Check the replacement person ID
3886 --
3887 chk_replacement_person_id
3888 (p_absence_attendance_id => p_rec.absence_attendance_id
3889 ,p_replacement_person_id => p_rec.replacement_person_id
3890 ,p_business_group_id => p_rec.business_group_id
3891 ,p_object_version_number => p_rec.object_version_number
3892 ,p_date_projected_start => p_rec.date_projected_start
3893 ,p_date_projected_end => p_rec.date_projected_end
3894 ,p_date_start => p_rec.date_start
3895 ,p_date_end => p_rec.date_end);
3896
3897 --
3898 -- Check the authorising person ID
3899 --
3900 chk_authorising_person_id
3901 (p_absence_attendance_id => p_rec.absence_attendance_id
3902 ,p_authorising_person_id => p_rec.authorising_person_id
3903 ,p_business_group_id => p_rec.business_group_id
3904 ,p_object_version_number => p_rec.object_version_number
3905 ,p_date_projected_start => p_rec.date_projected_start
3906 ,p_date_projected_end => p_rec.date_projected_end
3907 ,p_date_start => p_rec.date_start
3908 ,p_date_end => p_rec.date_end);
3909
3910
3911 hr_utility.set_location(l_proc, 24);
3912 --
3913 per_abs_bus.chk_ddf(p_rec);
3914 --
3915 per_abs_bus.chk_df(p_rec);
3916 --
3917 hr_utility.set_location(' Leaving:'||l_proc, 10);
3918 End insert_validate;
3919 --
3920 -- ----------------------------------------------------------------------------
3921 -- |---------------------------< update_validate >----------------------------|
3922 -- ----------------------------------------------------------------------------
3923 Procedure update_validate
3924 (p_effective_date in date
3925 ,p_rec in per_abs_shd.g_rec_type
3926 ,p_dur_dys_less_warning out nocopy boolean
3927 ,p_dur_hrs_less_warning out nocopy boolean
3928 ,p_exceeds_pto_entit_warning out nocopy boolean
3929 ,p_exceeds_run_total_warning out nocopy boolean
3930 ,p_abs_overlap_warning out nocopy boolean
3931 ,p_abs_day_after_warning out nocopy boolean
3932 ,p_dur_overwritten_warning out nocopy boolean
3933 ) is
3934 --
3935 l_proc varchar2(72) := g_package||'update_validate';
3936 l_effective_date date; -- Added for bug 3371960.
3937 --
3938 Begin
3939
3940 hr_utility.set_location('Entering:'||l_proc, 5);
3941 --
3942 -- Fix for bug 3371960 starts here.
3943 -- Use l_effective_date in different chk proceudre.
3944 --
3945 l_effective_date := NVL(p_rec.date_start, p_effective_date);
3946 --
3947 -- Fix for bug 3371960 ends here.
3948 --
3949 -- Assign the durations to the global variables.
3950 --
3951 per_abs_shd.g_absence_days := p_rec.absence_days;
3952 per_abs_shd.g_absence_hours := p_rec.absence_hours;
3953
3954 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3955 --
3956 chk_non_updateable_args
3957 (p_effective_date => p_effective_date
3958 ,p_rec => p_rec
3959 );
3960 --
3961 hr_utility.set_location(l_proc, 14);
3962
3963 --
3964 -- Check person ID. This procedure is called during update_validate
3965 -- because the person must be valid for the entire absence period
3966 -- (which can be updated).
3967 --
3968 chk_person_id
3969 (p_absence_attendance_id => p_rec.absence_attendance_id
3970 ,p_person_id => p_rec.person_id
3971 ,p_business_group_id => p_rec.business_group_id
3972 ,p_object_version_number => p_rec.object_version_number
3973 ,p_date_projected_start => p_rec.date_projected_start
3974 ,p_date_projected_end => p_rec.date_projected_end
3975 ,p_date_start => p_rec.date_start
3976 ,p_date_end => p_rec.date_end);
3977
3978 --
3979 -- Check absence attendance type ID. This is called from
3980 -- update_validate because the absence type must be valid for
3981 -- the entire absence period (which can be updated).
3982 --
3983 chk_absence_attendance_type_id
3984 (p_absence_attendance_id => p_rec.absence_attendance_id
3985 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3986 ,p_business_group_id => p_rec.business_group_id
3987 ,p_object_version_number => p_rec.object_version_number
3988 ,p_date_projected_start => p_rec.date_projected_start
3989 ,p_date_projected_end => p_rec.date_projected_end
3990 ,p_date_start => p_rec.date_start
3991 ,p_date_end => p_rec.date_end);
3992
3993 --
3994 -- Check the absence reason ID.
3995 --
3996 chk_abs_attendance_reason_id
3997 (p_absence_attendance_id => p_rec.absence_attendance_id
3998 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
3999 ,p_abs_attendance_reason_id => p_rec.abs_attendance_reason_id
4000 ,p_business_group_id => p_rec.business_group_id
4001 ,p_object_version_number => p_rec.object_version_number
4002 ,p_effective_date => l_effective_date);
4003
4004 --
4005 -- Check absence period.
4006 --
4007 -- Check the absence period (this includes all duration validation).
4008 -- The durations are in out parameters and are assigned to global
4009 -- variables. This is because they will be over-written during
4010 -- pre insert / pre update if the Fast Formula Auto-Overwrite duration
4011 -- profile option is set to yes during pre-update.
4012 --
4013 chk_absence_period
4014 (p_absence_attendance_id => p_rec.absence_attendance_id
4015 ,p_absence_attendance_type_id => p_rec.absence_attendance_type_id
4016 ,p_business_group_id => p_rec.business_group_id
4017 ,p_object_version_number => p_rec.object_version_number
4018 ,p_effective_date => l_effective_date
4019 ,p_person_id => p_rec.person_id
4020 ,p_date_projected_start => p_rec.date_projected_start
4021 ,p_time_projected_start => p_rec.time_projected_start
4022 ,p_date_projected_end => p_rec.date_projected_end
4023 ,p_time_projected_end => p_rec.time_projected_end
4024 ,p_date_start => p_rec.date_start
4025 ,p_time_start => p_rec.time_start
4026 ,p_date_end => p_rec.date_end
4027 ,p_time_end => p_rec.time_end
4028 ,p_ABS_INFORMATION_CATEGORY =>p_rec.ABS_INFORMATION_CATEGORY
4029 ,p_ABS_INFORMATION1 =>p_rec.ABS_INFORMATION1
4030 ,p_ABS_INFORMATION2 =>p_rec.ABS_INFORMATION2
4031 ,p_ABS_INFORMATION3 =>p_rec.ABS_INFORMATION3
4032 ,p_ABS_INFORMATION4 =>p_rec.ABS_INFORMATION4
4033 ,p_ABS_INFORMATION5 =>p_rec.ABS_INFORMATION5
4034 ,p_ABS_INFORMATION6 =>p_rec.ABS_INFORMATION6
4035 ,p_absence_days => per_abs_shd.g_absence_days
4036 ,p_absence_hours => per_abs_shd.g_absence_hours
4037 ,p_dur_dys_less_warning => p_dur_dys_less_warning
4038 ,p_dur_hrs_less_warning => p_dur_hrs_less_warning
4039 ,p_exceeds_pto_entit_warning => p_exceeds_pto_entit_warning
4040 ,p_exceeds_run_total_warning => p_exceeds_run_total_warning
4041 ,p_abs_overlap_warning => p_abs_overlap_warning
4042 ,p_abs_day_after_warning => p_abs_day_after_warning
4043 ,p_dur_overwritten_warning => p_dur_overwritten_warning);
4044
4045
4046 --
4047 -- Check the replacement person ID
4048 --
4049 chk_replacement_person_id
4050 (p_absence_attendance_id => p_rec.absence_attendance_id
4051 ,p_replacement_person_id => p_rec.replacement_person_id
4052 ,p_business_group_id => p_rec.business_group_id
4053 ,p_object_version_number => p_rec.object_version_number
4054 ,p_date_projected_start => p_rec.date_projected_start
4055 ,p_date_projected_end => p_rec.date_projected_end
4056 ,p_date_start => p_rec.date_start
4057 ,p_date_end => p_rec.date_end);
4058
4059 --
4060 -- Check the authorising person ID
4061 --
4062 chk_authorising_person_id
4063 (p_absence_attendance_id => p_rec.absence_attendance_id
4064 ,p_authorising_person_id => p_rec.authorising_person_id
4065 ,p_business_group_id => p_rec.business_group_id
4066 ,p_object_version_number => p_rec.object_version_number
4067 ,p_date_projected_start => p_rec.date_projected_start
4068 ,p_date_projected_end => p_rec.date_projected_end
4069 ,p_date_start => p_rec.date_start
4070 ,p_date_end => p_rec.date_end);
4071
4072
4073 hr_utility.set_location(l_proc, 24);
4074 --
4075 per_abs_bus.chk_ddf(p_rec);
4076 --
4077 per_abs_bus.chk_df(p_rec);
4078 --
4079 hr_utility.set_location(' Leaving:'||l_proc, 10);
4080 End update_validate;
4081 --
4082 -- ----------------------------------------------------------------------------
4083 -- |---------------------------< delete_validate >----------------------------|
4084 -- ----------------------------------------------------------------------------
4085 Procedure delete_validate
4086 (p_rec in per_abs_shd.g_rec_type
4087 ) is
4088 --
4089 l_proc varchar2(72) := g_package||'delete_validate';
4090 --
4091 Begin
4092 hr_utility.set_location('Entering:'||l_proc, 5);
4093 --
4094 -- Call all supporting business operations
4095 --
4096 hr_utility.set_location(' Leaving:'||l_proc, 10);
4097 End delete_validate;
4098 --
4099 end per_abs_bus;
[Home] [Help]