1 Package per_abs_bus as
2 /* $Header: peabsrhi.pkh 120.3.12010000.1 2008/07/28 04:01:13 appldev ship $ */
3 --
4 -- ---------------------------------------------------------------------------
5 -- |---------------------< get_running_totals >----------------------------|
6 -- ---------------------------------------------------------------------------
7 --
8 -- Description:
9 -- This procedure gets the year to date totals and running totals for
10 -- both days and hours.
11 --
12 -- Pre-conditions:
13 -- None.
14 --
15 -- In Arguments:
16 -- p_person_id
17 -- p_absence_attendance_type_id
18 -- p_effective_date
19 --
20 -- Out Arguments:
21 -- p_running_total_hours
22 -- p_running_total_days
23 -- p_year_to_date_hours
24 -- p_year_to_date_days
25 --
26 -- Post Success:
27 -- If validation passes, processing continues.
28 --
29 -- Post Failure:
30 -- If validation fails, an error is raised and processing stops.
31 --
32 -- Access Status:
33 -- Internal Table Handler Use Only. API updating is not required as this
34 -- is called from other chk procedures that use API updating.
35 --
36 -- {End Of Comments}
37 -- ----------------------------------------------------------------------------
38 procedure get_running_totals
39 (p_person_id in number
40 ,p_absence_attendance_type_id in number
41 ,p_effective_date in date
42 ,p_running_total_hours out nocopy number
43 ,p_running_total_days out nocopy number
44 ,p_year_to_date_hours out nocopy number
45 ,p_year_to_date_days out nocopy number);
46 --
47 -- ---------------------------------------------------------------------------
48 -- |---------------------< per_valid_for_absence >-------------------------|
49 -- ---------------------------------------------------------------------------
50 --
51 -- Description:
52 -- This function validates that the person exists and that they have
53 -- a valid period of service for the entire absence duration.
54 --
55 -- Pre-conditions:
56 -- None.
57 --
58 -- In Arguments:
59 -- p_person_id
60 -- p_business_group_id
61 -- p_person_type
62 -- p_date_projected_start
63 -- p_date_projected_end
64 -- p_date_start
65 -- p_date_end
66 --
67 -- Post Success:
68 -- If validation passes, the function returns TRUE.
69 --
70 -- Post Failure:
71 -- IF validation fails, the function returns FALSE.
72 --
73 -- Access Status:
74 -- Internal Table Handler Use Only. API updating is not required as this
75 -- is called from other chk procedures that use API updating.
76 --
77 -- {End Of Comments}
78 -- ----------------------------------------------------------------------------
79 function per_valid_for_absence
80 (p_person_id in number
81 ,p_business_group_id in number
82 ,p_date_projected_start in date
83 ,p_date_projected_end in date
84 ,p_date_start in date
85 ,p_date_end in date) return boolean;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------------< convert_to_minutes >-----------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 -- Description:
92 -- Converts two times into duration minutes.
93 --
94 -- Pre-conditions:
95 --
96 -- In Arguments:
97 -- p_time_start
98 -- p_time_end
99 --
100 -- Post Success:
101 -- The function returns duration minutes and processing continues.
102 --
103 -- Post Failure:
104 -- The function errors and processing stops.
105 --
106 -- Access Status:
107 -- Internal Development Use Only.
108 --
109 -- {End Of Comments}
110 -- ----------------------------------------------------------------------------
111 function convert_to_minutes
112 (p_time_start in varchar2
113 ,p_time_end in varchar2) return number;
114 --
115 -- ---------------------------------------------------------------------------
116 -- |---------------------------< chk_time_format >--------------------------|
117 -- ---------------------------------------------------------------------------
118 --
119 -- Description:
120 -- Checks that the time format is valid.
121 --
122 -- Pre-conditions:
123 --
124 -- In Arguments:
125 -- p_time
126 --
127 -- Post Success:
128 -- If the time format is valid, processing continues.
129 --
130 -- Post Failure:
131 -- If the time format is invalid processing stops and an error is raised.
132 --
133 -- Access Status:
134 -- Internal Development Use Only.
135 --
136 -- {End Of Comments}
137 -- ----------------------------------------------------------------------------
138 procedure chk_time_format
139 (p_time in varchar2);
140 --
141 -- ---------------------------------------------------------------------------
142 -- |-----------------< calculate_absence_duration >-------------------------|
143 -- ---------------------------------------------------------------------------
144 --
145 -- Description:
146 -- Calculates the absence duration in hours and / or days and sets
147 -- the duration.
148 --
149 -- Pre-conditions:
150 -- None.
151 --
152 -- In Arguments:
153 -- p_absence_attendance_id
154 -- p_absence_attendance_type_id
155 -- p_business_group_id
156 -- p_object_version_number
157 -- p_effective_date
158 -- p_person_id
159 -- p_date_start
160 -- p_date_end
161 -- p_time_start
162 -- p_time_end
163 --
164 -- Out Arguments:
165 -- p_absence_days
166 -- p_absence_hours
167 -- p_use_formula
168 --
169 -- Post Success:
170 -- The absence duration in days and hours is returned.
171 --
172 -- Post Failure:
173 -- If a failure occurs, an application error is raised and
174 -- processing terminates.
175 --
176 -- Access Status:
177 -- Internal Development Use Only.
178 --
179 -- {End Of Comments}
180 -- ----------------------------------------------------------------------------
181 procedure calculate_absence_duration
182 (p_absence_attendance_id in number
183 ,p_absence_attendance_type_id in number
184 ,p_business_group_id in number
185 ,p_object_version_number in number
186 ,p_effective_date in date
187 ,p_person_id in number
188 ,p_date_start in date
189 ,p_date_end in date
190 ,p_time_start in varchar2
191 ,p_time_end in varchar2
192 ,p_absence_days out nocopy number
193 ,p_absence_hours out nocopy number
194 ,p_use_formula out nocopy boolean);
195 --
196 -- ---------------------------------------------------------------------------
197 -- |---------------------------< chk_person_id >----------------------------|
198 -- ---------------------------------------------------------------------------
199 --
200 -- Description:
201 -- Validates that the person exists, that they have a valid period of
202 -- service and that they match the business group id being passed.
203 --
204 -- Pre-conditions:
205 --
206 -- In Arguments:
207 -- p_absence_attendance_id
208 -- p_person_id
209 -- p_business_group_id
210 --
211 -- Post Success:
212 -- If the person and their period of service are valid, processing
213 -- continues.
214 --
215 -- Post Failure:
216 -- An application error will be raised and processing is terminated.
217 --
218 -- Access Status:
219 -- Internal Development Use Only.
220 --
221 -- {End Of Comments}
222 -- ----------------------------------------------------------------------------
223 procedure chk_person_id
224 (p_absence_attendance_id in number
225 ,p_person_id in number
226 ,p_business_group_id in number
227 ,p_object_version_number in number
228 ,p_date_projected_start in date
229 ,p_date_projected_end in date
230 ,p_date_start in date
231 ,p_date_end in date);
232 --
233 -- ---------------------------------------------------------------------------
234 -- |-----------------< chk_absence_attendance_type_id >---------------------|
235 -- ---------------------------------------------------------------------------
236 --
237 -- Description:
238 -- Validates that the absence_attendance_type_id exists in
239 -- per_absence_attendance_types for the same business group and that it
240 -- is effective for the entire absence duration.
241 --
242 -- Pre-conditions:
243 -- None.
244 --
245 -- In Arguments:
246 -- p_absence_attendance_id
247 -- p_business_group_id
248 -- p_absence_attendance_type_id
249 -- p_object_version_number
250 --
251 -- Post Success:
252 -- If absence_attendance_type_id exists and is valid,
253 -- processing continues.
254 --
255 -- Post Failure:
256 -- If absence_attendance_type_id is invalid,
257 -- an application error is raised and processing terminates.
258 --
259 -- Access Status:
260 -- Internal Development Use Only.
261 --
262 -- {End Of Comments}
263 -- ----------------------------------------------------------------------------
264 procedure chk_absence_attendance_type_id
265 (p_absence_attendance_id in number
266 ,p_absence_attendance_type_id in number
267 ,p_business_group_id in number
268 ,p_object_version_number in number
269 ,p_date_projected_start in date
270 ,p_date_projected_end in date
271 ,p_date_start in date
272 ,p_date_end in date);
273 --
274 -- ---------------------------------------------------------------------------
275 -- |-----------------< chk_abs_attendance_reason_id >-----------------------|
276 -- ---------------------------------------------------------------------------
277 --
278 -- Description:
279 -- Validates that an abs_attendance_reason_id exists in table
280 -- per_abs_attendance_reasons, also valid in hr_lookups
281 -- where lookup_type is 'ABSENCE_REASON' and enabled_flag is 'Y'
282 -- and effective_date is between the active dates (if they are not null).
283 --
284 -- Pre-conditions:
285 -- absence_attendance_type_id must be valid.
286 -- business_group_id must be valid.
287 -- effective_date must be valid.
288 --
289 -- In Arguments:
290 -- p_absence_attendance_id
291 -- p_absence_attendance_type_id
292 -- p_abs_attendance_reason_id
293 -- p_business_group_id
294 -- p_object_version_number
295 -- p_effective_date
296 --
297 -- Post Success:
298 -- If a row does exist; processing continues.
299 --
300 -- Post Failure:
301 -- If a row does not exist in per_abs_attendance_reason and hr_lookups for
302 -- a given reason id then an error will be raised and processing terminated.
303 --
304 -- Access Status:
305 -- Internal Table Handler Use Only.
306 --
307 -- {End Of Comments}
308 -- ----------------------------------------------------------------------------
309 procedure chk_abs_attendance_reason_id
310 (p_absence_attendance_id in number
311 ,p_absence_attendance_type_id in number
312 ,p_abs_attendance_reason_id in number
313 ,p_business_group_id in number
314 ,p_object_version_number in number
315 ,p_effective_date in date);
316 --
317 -- ---------------------------------------------------------------------------
318 -- |-----------------< chk_absence_period >---------------------------------|
319 -- ---------------------------------------------------------------------------
320 --
321 -- Description:
322 -- Validates the projected dates, actual dates, times and the duration.
323 --
324 -- Pre-conditions:
325 -- absence_attendance_type_id must be valid.
326 -- business_group_id must be valid.
327 -- effective_date must be valid.
328 --
329 -- In Arguments:
330 -- p_absence_attendance_id
331 -- p_absence_attendance_type_id
332 -- p_business_group_id
333 -- p_object_version_number
334 -- p_effective_date
335 -- p_person_id
336 -- p_date_projected_start
337 -- p_time_projected_start
338 -- p_date_projected_end
339 -- p_time_projected_end
340 -- p_date_start
341 -- p_time_start
342 -- p_date_end
343 -- p_time_end
344 --
345 -- In Out Arguments:
346 -- p_absence_days
347 -- p_absence_hours
348 --
349 -- Post Success:
350 -- If validation passes, processing continues.
351 --
352 -- Post Failure:
353 -- IF validation fails, the appropriate error or warning is raised.
354 --
355 -- Access Status:
356 -- Internal Table Handler Use Only.
357 --
358 -- {End Of Comments}
359 -- ----------------------------------------------------------------------------
360 procedure chk_absence_period
361 (p_absence_attendance_id in number
362 ,p_absence_attendance_type_id in number
363 ,p_business_group_id in number
364 ,p_object_version_number in number
365 ,p_effective_date in date
366 ,p_person_id in number
367 ,p_date_projected_start in date
368 ,p_time_projected_start in varchar2
369 ,p_date_projected_end in date
370 ,p_time_projected_end in varchar2
371 ,p_date_start in date
372 ,p_time_start in varchar2
373 ,p_date_end in date
374 ,p_time_end in varchar2
375 ,p_absence_days in out nocopy number
376 ,p_absence_hours in out nocopy number
377 ,p_dur_dys_less_warning out nocopy boolean
378 ,p_dur_hrs_less_warning out nocopy boolean
379 ,p_exceeds_pto_entit_warning out nocopy boolean
380 ,p_exceeds_run_total_warning out nocopy boolean
381 ,p_abs_overlap_warning out nocopy boolean
382 ,p_abs_day_after_warning out nocopy boolean
383 ,p_dur_overwritten_warning out nocopy boolean);
384 --
385 -- ---------------------------------------------------------------------------
386 -- |----------------------< chk_replacement_person_id >---------------------|
387 -- ---------------------------------------------------------------------------
388 --
389 -- Description:
390 -- Validates that the person exists, that they have a valid period of
391 -- service and that they match the business group id being passed.
392 --
393 -- Pre-conditions:
394 --
395 -- In Arguments:
396 -- p_absence_attendance_id
397 -- p_replacement_person_id
398 -- p_business_group_id
399 --
400 -- Post Success:
401 -- If the person and their period of service are valid, processing
402 -- continues.
403 --
404 -- Post Failure:
405 -- An application error will be raised and processing is terminated.
406 --
407 -- Access Status:
408 -- Internal Development Use Only.
409 --
410 -- {End Of Comments}
411 -- ----------------------------------------------------------------------------
412 procedure chk_replacement_person_id
413 (p_absence_attendance_id in number
414 ,p_replacement_person_id in number
415 ,p_business_group_id in number
416 ,p_object_version_number in number
417 ,p_date_projected_start in date
418 ,p_date_projected_end in date
419 ,p_date_start in date
420 ,p_date_end in date);
424 -- ---------------------------------------------------------------------------
421 --
422 -- ---------------------------------------------------------------------------
423 -- |----------------------< chk_authorising_person_id >---------------------|
425 --
426 -- Description:
427 -- Validates that the person exists, that they have a valid period of
428 -- service and that they match the business group id being passed.
429 --
430 -- Pre-conditions:
431 --
432 -- In Arguments:
433 -- p_absence_attendance_id
434 -- p_replacement_person_id
435 -- p_business_group_id
436 --
437 -- Post Success:
438 -- If the person and their period of service are valid, processing
439 -- continues.
440 --
441 -- Post Failure:
442 -- An application error will be raised and processing is terminated.
443 --
444 -- Access Status:
445 -- Internal Development Use Only.
446 --
447 -- {End Of Comments}
448 -- ----------------------------------------------------------------------------
449 procedure chk_authorising_person_id
450 (p_absence_attendance_id in number
451 ,p_authorising_person_id in number
452 ,p_business_group_id in number
453 ,p_object_version_number in number
454 ,p_date_projected_start in date
455 ,p_date_projected_end in date
456 ,p_date_start in date
457 ,p_date_end in date);
458 --
459 -- ---------------------------------------------------------------------------
460 -- |----------------------< set_security_group_id >--------------------------|
461 -- ---------------------------------------------------------------------------
462 -- {Start Of Comments}
463 --
464 -- Description:
465 -- Sets the security_group_id in CLIENT_INFO for the appropriate business
466 -- group context.
467 --
468 -- Prerequisites:
469 -- The primary key identified by p_absence_attendance_id
470 -- already exists.
471 --
472 -- In Arguments:
473 -- p_absence_attendance_id
474 --
475 --
476 -- Post Success:
477 -- The security_group_id will be set in CLIENT_INFO.
478 --
479 -- Post Failure:
480 -- An error is raised if the value does not exist.
481 --
482 -- Access Status:
483 -- Internal Development Use Only.
484 --
485 -- {End Of Comments}
486 -- ---------------------------------------------------------------------------
487 procedure set_security_group_id
488 (p_absence_attendance_id in number
489 );
490 --
491 -- ---------------------------------------------------------------------------
492 -- |---------------------< return_legislation_code >-------------------------|
493 -- ---------------------------------------------------------------------------
494 -- {Start Of Comments}
495 --
496 -- Description:
497 -- Return the legislation code for a specific primary key value
498 --
499 -- Prerequisites:
500 -- The primary key identified by p_absence_attendance_id
501 -- already exists.
502 --
503 -- In Arguments:
504 -- p_absence_attendance_id
505 --
506 --
507 -- Post Success:
508 -- The business group's legislation code will be returned.
509 --
510 -- Post Failure:
511 -- An error is raised if the value does not exist.
512 --
513 -- Access Status:
514 -- Internal Development Use Only.
515 --
516 -- {End Of Comments}
517 -- ---------------------------------------------------------------------------
518 FUNCTION return_legislation_code
519 (p_absence_attendance_id in number
520 ) RETURN varchar2;
521 --
522 -- ----------------------------------------------------------------------------
523 -- |---------------------------< insert_validate >----------------------------|
524 -- ----------------------------------------------------------------------------
525 -- {Start of comments}
526 --
527 -- Description:
528 -- This procedure controls the execution of all insert business rules
529 -- validation.
530 --
531 -- Prerequisites:
532 -- This private procedure is called from ins procedure.
533 --
534 -- In Parameters:
535 -- A Pl/Sql record structure.
536 --
537 -- Out Parameters:
538 -- p_dur_dys_less_warning - true, when HR_EMP_ABS_SHORT_DURATION warning
539 -- is raised.
540 -- p_dur_hrs_less_warning - true, when HR_ABS_HOUR_LESS_DURATION warning
541 -- is raised.
542 -- p_exceeds_pto_entit_warning - true, when HR_EMP_NOT_ENTITLED warning
543 -- is raised.
544 -- p_exceeds_run_total_warning - true, when HR_ABS_DET_RUNNING_ZERO warning
545 -- is raised.
546 -- p_abs_overlap_warning - true, when HR_ABS_DET_OVERLAP warning is
547 -- raised.
548 -- p_abs_day_after_warning - true, when HR_ABS_DET_ABS_DAY_AFTER warning
549 -- is raised.
550 -- p_dur_overwritten_warning true, when the absence durations have been
551 -- overwritten by the Fast Formula values.
552 --
553 -- Post Success:
554 -- Processing continues.
555 --
556 -- Post Failure:
557 -- If a business rules fails the error will not be handled by this procedure
558 -- unless explicity coded.
559 --
560 -- Developer Implementation Notes:
561 -- For insert, your business rules should be executed from this procedure and
562 -- should ideally (unless really necessary) just be straight procedure or
563 -- function calls. Try and avoid using conditional branching logic.
564 --
565 -- Access Status:
566 -- Internal Row Handler Use Only.
567 --
568 -- {End of comments}
569 -- ----------------------------------------------------------------------------
570 Procedure insert_validate
571 (p_effective_date in date
572 ,p_rec in per_abs_shd.g_rec_type
573 ,p_dur_dys_less_warning out nocopy boolean
574 ,p_dur_hrs_less_warning out nocopy boolean
575 ,p_exceeds_pto_entit_warning out nocopy boolean
576 ,p_exceeds_run_total_warning out nocopy boolean
577 ,p_abs_overlap_warning out nocopy boolean
578 ,p_abs_day_after_warning out nocopy boolean
579 ,p_dur_overwritten_warning out nocopy boolean
580 );
581 --
582 -- ----------------------------------------------------------------------------
583 -- |---------------------------< update_validate >----------------------------|
584 -- ----------------------------------------------------------------------------
585 -- {Start Of Comments}
586 --
587 -- Description:
588 -- This procedure controls the execution of all update business rules
589 -- validation.
590 --
591 -- Prerequisites:
592 -- This private procedure is called from upd procedure.
593 --
594 -- In Parameters:
595 -- A Pl/Sql record structure.
596 --
597 -- Out Parameters:
598 -- p_dur_dys_less_warning - true, when HR_EMP_ABS_SHORT_DURATION warning
599 -- is raised.
600 -- p_dur_hrs_less_warning - true, when HR_ABS_HOUR_LESS_DURATION warning
601 -- is raised.
602 -- p_exceeds_pto_entit_warning - true, when HR_EMP_NOT_ENTITLED warning
603 -- is raised.
604 -- p_exceeds_run_total_warning - true, when HR_ABS_DET_RUNNING_ZERO warning
605 -- is raised.
606 -- p_abs_overlap_warning - true, when HR_ABS_DET_OVERLAP warning is
607 -- raised.
608 -- p_abs_day_after_warning - true, when HR_ABS_DET_ABS_DAY_AFTER warning
609 -- is raised.
610 -- p_dur_overwritten_warning true, when the absence durations have been
611 -- overwritten by the Fast Formula values.
612 --
613 -- Post Success:
614 -- Processing continues.
615 --
616 -- Post Failure:
617 -- If a business rules fails the error will not be handled by this procedure
618 -- unless explicity coded.
619 --
620 -- Developer Implementation Notes:
621 -- For update, your business rules should be executed from this procedure and
622 -- should ideally (unless really necessary) just be straight procedure or
623 -- function calls. Try and avoid using conditional branching logic.
624 --
625 -- Access Status:
626 -- Internal Row Handler Use Only.
627 --
628 -- {End Of Comments}
629 -- ----------------------------------------------------------------------------
630 Procedure update_validate
631 (p_effective_date in date
632 ,p_rec in per_abs_shd.g_rec_type
633 ,p_dur_dys_less_warning out nocopy boolean
634 ,p_dur_hrs_less_warning out nocopy boolean
635 ,p_exceeds_pto_entit_warning out nocopy boolean
636 ,p_exceeds_run_total_warning out nocopy boolean
637 ,p_abs_overlap_warning out nocopy boolean
638 ,p_abs_day_after_warning out nocopy boolean
639 ,p_dur_overwritten_warning out nocopy boolean
640 );
641 --
642 -- ----------------------------------------------------------------------------
643 -- |---------------------------< delete_validate >----------------------------|
644 -- ----------------------------------------------------------------------------
645 -- {Start Of Comments}
646 --
647 -- Description:
648 -- This procedure controls the execution of all delete business rules
649 -- validation.
650 --
651 -- Prerequisites:
655 -- A Pl/Sql record structure.
652 -- This private procedure is called from del procedure.
653 --
654 -- In Parameters:
656 --
657 -- Post Success:
658 -- Processing continues.
659 --
660 -- Post Failure:
661 -- If a business rules fails the error will not be handled by this procedure
662 -- unless explicity coded.
663 --
664 -- Developer Implementation Notes:
665 -- For delete, your business rules should be executed from this procedure and
666 -- should ideally (unless really necessary) just be straight procedure or
667 -- function calls. Try and avoid using conditional branching logic.
668 --
669 -- Access Status:
670 -- Internal Row Handler Use Only.
671 --
672 -- {End Of Comments}
673 -- ----------------------------------------------------------------------------
674 Procedure delete_validate
675 (p_rec in per_abs_shd.g_rec_type
676 );
677 --
678 end per_abs_bus;
[Home] [Help]