[Home] [Help]
PACKAGE BODY: APPS.PER_NAA_BUS
Source
1 Package Body per_naa_bus as
2 /* $Header: penaarhi.pkb 120.1 2006/04/25 06:01:33 niljain noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_naa_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_action_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< chk_absence_attendance_id >---------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 -- Description:
21 -- Validates that the absence exists.
22 --
23 -- Pre-conditions:
24 --
25 -- In Arguments:
26 -- p_absence_attendance_id
27 -- p_absence_action_id
28 -- p_object_version_number
29 --
30 -- Post Success:
31 -- If the absence exists, processing continues.
32 --
33 -- Post Failure:
34 -- An application error will be raised and processing is terminated.
35 --
36 -- Access Status:
37 -- Internal Development Use Only.
38 --
39 -- {End Of Comments}
40 -- ----------------------------------------------------------------------------
41 --
42 procedure chk_absence_attendance_id
43 (p_absence_action_id in number
44 ,p_absence_attendance_id in number
45 ,p_object_version_number in number
46 )
47 is
48 --
49 -- Declare cursor
50 --
51 cursor cur_chk_abs_atte_id is
52 select 1
53 from per_absence_attendances paa
54 where absence_attendance_id = p_absence_attendance_id;
55 --
56 -- local variables
57 l_proc varchar2(72) := g_package||'chk_absence_attendance_id';
58 l_exists varchar2(1) := null;
59 l_api_updating boolean;
60 --
61 begin
62 hr_utility.set_location('Entering:'|| l_proc, 10);
63 --
64 hr_api.mandatory_arg_error
65 (p_api_name => l_proc
66 ,p_argument => 'p_absence_attendance_id'
67 ,p_argument_value => p_absence_attendance_id
68 );
69 --
70 -- Check that the absence exists.
71 --
72 open cur_chk_abs_atte_id;
73 fetch cur_chk_abs_atte_id into l_exists;
74 close cur_chk_abs_atte_id;
75 if l_exists is null then
76
77 fnd_message.set_name('PER', 'HR_NL_INVALID_ABSENCE_ID');
78 fnd_message.raise_error;
79
80 end if;
81
82 hr_utility.set_location(' Leaving:'|| l_proc, 20);
83
84 end chk_absence_attendance_id;
85 --
86 -- ---------------------------------------------------------------------------
87 -- |----------------------< chk_abs_cat_user_tables >----------------------|
88 -- ---------------------------------------------------------------------------
89 --
90 -- Description:
91 -- Validates that the user table exists for absence category.
92 --
93 -- Pre-conditions:
94 --
95 -- In Arguments:
96 -- p_absence_attendance_id
97 --
98 -- Post Success:
99 -- If the user table exits for absence category exists, processing
100 -- continues.
101 --
102 -- Post Failure:
103 -- An application error will be raised and processing is terminated.
104 --
105 -- Access Status:
106 -- Internal Development Use Only.
107 --
108 -- {End Of Comments}
109 -- ----------------------------------------------------------------------------
110 --
111 procedure chk_abs_cat_user_tables
112 (p_absence_attendance_id in number
113 )
114 is
115 --
116 -- Declare cursor
117 --
118 cursor cur_chk_abs_cat_user_tables is
119 select 1
120 from pay_user_tables put,
121 per_absence_attendances paa,
122 per_absence_attendance_types paat
123 where put.user_table_name = 'NL_ABS_ACTION_'||paat.absence_category
124 and paa.absence_attendance_type_id = paat.absence_attendance_type_id
125 and paa.absence_attendance_id = p_absence_attendance_id
126 and put.business_group_id = paat.business_group_id;
127 --
128 -- local variables
129 l_proc varchar2(72) := g_package||'chk_abs_cat_user_tables';
130 l_exists varchar2(1) := null;
131 l_api_updating boolean;
132 --
133 begin
134 hr_utility.set_location('Entering:'|| l_proc, 10);
135 --
136 -- Check that the user table for absence category exists.
137 --
138 open cur_chk_abs_cat_user_tables;
139 fetch cur_chk_abs_cat_user_tables into l_exists;
140 close cur_chk_abs_cat_user_tables;
141 if l_exists is null then
142
143 fnd_message.set_name('PER', 'HR_NL_ACTION_MISSING_SETUP');
144 fnd_message.raise_error;
145
146 end if;
147
148 hr_utility.set_location(' Leaving:'|| l_proc, 20);
149
150 end chk_abs_cat_user_tables;
151 --
152 -- ---------------------------------------------------------------------------
153 -- |--------------------------< chk_expected_date >-------------------------|
154 -- ---------------------------------------------------------------------------
155 --
156 -- Description:
157 -- Validates that the expected date is not null and
158 -- Validates that the expected date is always greater than or equal to
159 -- absence start date.
160 --
161 -- Pre-conditions:
162 --
163 -- In Arguments:
164 -- p_expected_date
165 -- p_absence_action_id
166 -- p_absence_attendance_id
167 -- p_object_version_number
168 --
169 -- Post Success:
170 -- If the expected date exists and it is valid, processing continues.
171 --
172 -- Post Failure:
173 -- An application error will be raised and processing is terminated.
174 --
175 -- Access Status:
176 -- Internal Development Use Only.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 --
181 procedure chk_expected_date
182 (p_absence_action_id in number
183 ,p_absence_attendance_id in number
184 ,p_expected_date in date
185 ,p_object_version_number in number
186 ) is
187 --
188 -- Declare cursor
189 --
190 cursor cur_chk_expected_date is -- Changes as per bug 2637189
191 select nvl(date_start,date_projected_start) absence_date
192 from per_absence_attendances paa
193 where absence_attendance_id = p_absence_attendance_id;
194 --
195 -- local variables
196 l_proc varchar2(72) := g_package||'chk_expected_date';
197 l_api_updating boolean;
198 l_absence_date date;
199 --
200 begin
201 hr_utility.set_location('Entering:'|| l_proc, 10);
202 --
203 -- Check expected date for null
204 --
205 hr_api.mandatory_arg_error
206 (p_api_name => l_proc
207 ,p_argument => 'p_expected_date'
208 ,p_argument_value => p_expected_date
209 );
210 --
211 -- Start changes as per bug 2637189
212 -- Check expected date is greater than absence start date
213 --
214 open cur_chk_expected_date;
215 fetch cur_chk_expected_date into l_absence_date;
216 close cur_chk_expected_date;
217 --
218 if p_expected_date < l_absence_date then
219 fnd_message.set_name('PER', 'HR_NL_INVALID_ACTION_EXP_DATE');
220 fnd_message.raise_error;
221 end if;
222 -- end changes as per bug 2637189
223 --
224 hr_utility.set_location(' Leaving:'|| l_proc, 20);
225 --
226 end chk_expected_date;
227 --
228 -- ---------------------------------------------------------------------------
229 -- |--------------------------< chk_description >---------------------------|
230 -- ---------------------------------------------------------------------------
231 --
232 -- Description:
233 -- Validates that the description is not null.
234 --
235 -- Pre-conditions:
236 --
237 -- In Arguments:
238 -- p_description
239 -- p_absence_action_id
240 -- p_object_version_number
241 --
242 -- Post Success:
243 -- If the description exists, processing continues.
244 --
245 -- Post Failure:
246 -- An application error will be raised and processing is terminated.
247 --
248 -- Access Status:
249 -- Internal Development Use Only.
250 --
251 -- {End Of Comments}
252 -- ----------------------------------------------------------------------------
253 --
254 procedure chk_description
255 (p_absence_action_id in number
256 ,p_description in varchar2
257 ,p_object_version_number in number
258 ) is
259 --
260 -- Declare cursor
261 --
262 -- local variables
263 l_proc varchar2(72) := g_package||'chk_description';
264 l_api_updating boolean;
265 --
266 begin
267 hr_utility.set_location('Entering:'|| l_proc, 10);
268 --
269 hr_api.mandatory_arg_error
270 (p_api_name => l_proc
271 ,p_argument => 'p_description'
272 ,p_argument_value => p_description
273 );
274 --
275 hr_utility.set_location(' Leaving:'|| l_proc, 20);
276
277 end chk_description;
278 --
279 --
280 -- ---------------------------------------------------------------------------
281 -- |------------------------< chk_start_and_end_dates >---------------------|
282 -- ---------------------------------------------------------------------------
283 --
284 -- Description:
285 -- Validates that the actual start and end dates is not null.
286 --
287 -- Pre-conditions:
288 --
289 -- In Arguments:
290 -- p_actual_start_date
291 -- p_actual_end_date
292 -- p_absence_action_id
293 -- p_object_version_number
294 --
295 -- Post Success:
296 -- If the dates are valid, processing continues.
297 --
298 -- Post Failure:
299 -- An application error will be raised and processing is terminated.
300 --
301 -- Access Status:
302 -- Internal Development Use Only.
303 --
304 -- {End Of Comments}
305 -- ----------------------------------------------------------------------------
306 --
307 procedure chk_start_and_end_dates
308 (p_absence_action_id in number
309 ,p_actual_start_date in date
310 ,p_actual_end_date in date
311 ,p_object_version_number in number
312 ) is
313 --
314 -- Declare cursor
315 --
316 -- local variables
317 l_proc varchar2(72) := g_package||'chk_start_and_end_dates';
318 l_api_updating boolean;
319 --
320 begin
321 hr_utility.set_location('Entering:'|| l_proc, 10);
322 --
323 if p_actual_start_date is not null and
324 p_actual_end_date is not null then
325 if p_actual_start_date > p_actual_end_date then
326 fnd_message.set_name('PER', 'HR_NL_INVALID_ABS_ACTION_DATES');
327 fnd_message.raise_error;
328 end if;
329 end if;
330 --
331 hr_utility.set_location(' Leaving:'|| l_proc, 20);
332
333 end chk_start_and_end_dates;
334 --
335 -- ---------------------------------------------------------------------------
336 -- |----------------------< chk_absence_action_id >---------------------|
337 -- ---------------------------------------------------------------------------
338 --
339 -- Description:
340 -- Validates that the absence action exists.
341 --
342 -- Pre-conditions:
343 --
344 -- In Arguments:
345 -- p_absence_action_id
346 -- p_object_version_number
347 --
348 -- Post Success:
349 -- If the absence action exists, processing continues.
350 --
351 -- Post Failure:
352 -- An application error will be raised and processing is terminated.
353 --
354 -- Access Status:
355 -- Internal Development Use Only.
356 --
357 -- {End Of Comments}
358 -- ----------------------------------------------------------------------------
359 --
360 procedure chk_absence_action_id
361 (p_absence_action_id in number
362 ,p_object_version_number in number
363 )
364 is
365 --
366 -- Declare cursor
367 --
368 cursor cur_chk_abs_act_id is
369 select object_version_number
370 from per_nl_absence_actions paa
371 where absence_action_id = p_absence_action_id;
372 --
373 -- local variables
374 l_proc varchar2(72) := g_package||'chk_absence_action_id';
375 l_obj_num number(9) := null;
376 l_api_updating boolean;
377 --
378 begin
379 hr_utility.set_location('Entering:'|| l_proc, 10);
380 --
381 hr_api.mandatory_arg_error
382 (p_api_name => l_proc
383 ,p_argument => 'p_absence_action_id'
384 ,p_argument_value => p_absence_action_id
385 );
386 --
387 -- Check that the absence exists.
388 --
389 open cur_chk_abs_act_id;
390 fetch cur_chk_abs_act_id into l_obj_num;
391 close cur_chk_abs_act_id;
392 --
393 if l_obj_num is null then
394
395 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
396 fnd_message.raise_error;
397 elsif l_obj_num <> p_object_version_number then
398
399 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
400 fnd_message.raise_error;
401 end if;
402
403 hr_utility.set_location(' Leaving:'|| l_proc, 20);
404
405 end chk_absence_action_id;
406 --
407 -- ---------------------------------------------------------------------------
408 -- |----------------------< set_security_group_id >--------------------------|
409 -- ---------------------------------------------------------------------------
410 --
411 Procedure set_security_group_id
412 (p_absence_action_id in number
413 ) is
414 --
415 -- Declare cursor
416 --
417 cursor csr_sec_grp is
418 select pbg.security_group_id
419 from per_business_groups pbg
420 , per_nl_absence_actions naa
421 , per_absence_attendances paa
422 where naa.absence_action_id = p_absence_action_id
423 and naa.absence_attendance_id = paa.absence_attendance_id
424 and pbg.business_group_id = paa.business_group_id;
425 --
426 -- Declare local variables
427 --
428 l_security_group_id number;
429 l_proc varchar2(72) := g_package||'set_security_group_id';
430 --
431 begin
432 --
433 hr_utility.set_location('Entering:'|| l_proc, 10);
434 --
435 -- Ensure that all the mandatory parameter are not null
436 --
437 hr_api.mandatory_arg_error
438 (p_api_name => l_proc
439 ,p_argument => 'absence_action_id'
440 ,p_argument_value => p_absence_action_id
441 );
442 --
443 open csr_sec_grp;
444 fetch csr_sec_grp into l_security_group_id;
445 --
446 if csr_sec_grp%notfound then
447 --
448 close csr_sec_grp;
449 --
450 -- The primary key is invalid therefore we must error
451 --
452 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
453 fnd_message.raise_error;
454 end if;
455 --
456 -- Set the security_group_id in CLIENT_INFO
460 );
457 --
458 hr_api.set_security_group_id
459 (p_security_group_id => l_security_group_id
461 close csr_sec_grp;
462 --
463 hr_utility.set_location(' Leaving:'|| l_proc, 20);
464 --
465 end set_security_group_id;
466 --
467 -- ---------------------------------------------------------------------------
468 -- |---------------------< return_legislation_code >-------------------------|
469 -- ---------------------------------------------------------------------------
470 --
471 Function return_legislation_code
472 (p_absence_action_id in number
473 )
474 Return Varchar2 Is
475 --
476 -- Declare cursor
477 --
478 cursor csr_leg_code is
479 select pbg.legislation_code
480 from per_business_groups pbg
481 , per_nl_absence_actions naa
482 , per_absence_attendances paa
483 where naa.absence_action_id = p_absence_action_id
484 and paa.absence_attendance_id = naa.absence_attendance_id
485 and pbg.business_group_id = paa.business_group_id;
486 --
487 -- Declare local variables
488 --
489 l_legislation_code varchar2(150);
490 l_proc varchar2(72) := g_package||'return_legislation_code';
491 --
492 Begin
493 --
494 hr_utility.set_location('Entering:'|| l_proc, 10);
495 --
496 -- Ensure that all the mandatory parameter are not null
497 --
498 hr_api.mandatory_arg_error
499 (p_api_name => l_proc
500 ,p_argument => 'absence_action_id'
501 ,p_argument_value => p_absence_action_id
502 );
503 --
504 if ( nvl(per_naa_bus.g_absence_action_id, hr_api.g_number)
505 = p_absence_action_id) then
506 --
507 -- The legislation code has already been found with a previous
508 -- call to this function. Just return the value in the global
509 -- variable.
510 --
511 l_legislation_code := per_naa_bus.g_legislation_code;
512 hr_utility.set_location(l_proc, 20);
513 else
514 --
515 -- The ID is different to the last call to this function
516 -- or this is the first call to this function.
517 --
518 open csr_leg_code;
519 fetch csr_leg_code into l_legislation_code;
520 --
521 if csr_leg_code%notfound then
522 --
523 -- The primary key is invalid therefore we must error
524 --
525 close csr_leg_code;
526 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
527 fnd_message.raise_error;
528 end if;
529 hr_utility.set_location(l_proc,30);
530 --
531 -- Set the global variables so the values are
532 -- available for the next call to this function.
533 --
534 close csr_leg_code;
535 per_naa_bus.g_absence_action_id := p_absence_action_id;
536 per_naa_bus.g_legislation_code := l_legislation_code;
537 end if;
538 hr_utility.set_location(' Leaving:'|| l_proc, 40);
539 return l_legislation_code;
540 end return_legislation_code;
541 --
542 -- ----------------------------------------------------------------------------
543 -- |-----------------------< chk_non_updateable_args >------------------------|
544 -- ----------------------------------------------------------------------------
545 -- {Start Of Comments}
546 --
547 -- Description:
548 -- This procedure is used to ensure that non updateable attributes have
549 -- not been updated. If an attribute has been updated an error is generated.
550 --
551 -- Pre Conditions:
552 -- g_old_rec has been populated with details of the values currently in
553 -- the database.
554 --
555 -- In Arguments:
556 -- p_rec has been populated with the updated values the user would like the
557 -- record set to.
558 --
559 -- Post Success:
560 -- Processing continues if all the non updateable attributes have not
561 -- changed.
562 --
563 -- Post Failure:
564 -- An application error is raised if any of the non updatable attributes
565 -- have been altered.
566 --
567 -- {End Of Comments}
568 -- ----------------------------------------------------------------------------
569 Procedure chk_non_updateable_args
570 (p_rec in per_naa_shd.g_rec_type
571 ) IS
572 --
573 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
574 --
575 Begin
576 --
577 -- Only proceed with the validation if a row exists for the current
578 -- record in the HR Schema.
579 --
580 IF NOT per_naa_shd.api_updating
581 (p_absence_action_id => p_rec.absence_action_id
582 ,p_absence_attendance_id => p_rec.absence_attendance_id
583 ,p_object_version_number => p_rec.object_version_number
584 ) THEN
585 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
586 fnd_message.set_token('PROCEDURE ', l_proc);
587 fnd_message.set_token('STEP ', '5');
588 fnd_message.raise_error;
589 END IF;
590 --
591 --
592 End chk_non_updateable_args;
593 --
594 -- ----------------------------------------------------------------------------
595 -- |---------------------------< insert_validate >----------------------------|
599 ) is
596 -- ----------------------------------------------------------------------------
597 Procedure insert_validate
598 (p_rec in per_naa_shd.g_rec_type
600 --
601 l_proc varchar2(72) := g_package||'insert_validate';
602 --
603 Begin
604 hr_utility.set_location('Entering:'||l_proc, 5);
605 --
606 -- Check Absence attendance id
607 --
608 chk_absence_attendance_id
609 (p_absence_action_id => p_rec.absence_action_id
610 ,p_absence_attendance_id => p_rec.absence_attendance_id
611 ,p_object_version_number => p_rec.object_version_number
612 );
613 --
614 -- Check User table for Absence Category Lookup Type.
615 --
616 chk_abs_cat_user_tables
617 (p_absence_attendance_id => p_rec.absence_attendance_id
618 );
619 --
620
621 -- Check Expected date
622 -- changes as per bug 2637189
623 -- Bug# 5031662, Reverse the changes made for bug 2637189
624 /* chk_expected_date
625 (p_absence_action_id => p_rec.absence_action_id
626 ,p_absence_attendance_id => p_rec.absence_attendance_id
627 ,p_expected_date => p_rec.expected_date
628 ,p_object_version_number => p_rec.object_version_number
629 ); */
630 --
631 -- Check description
632 --
633 chk_description
634 (p_absence_action_id => p_rec.absence_action_id
635 ,p_description => p_rec.description
636 ,p_object_version_number => p_rec.object_version_number
637 );
638 --
639
640 -- Check actual start date and actual end date.
641 --
642 chk_start_and_end_dates
643 (p_absence_action_id => p_rec.absence_action_id
644 ,p_actual_start_date => p_rec.actual_start_date
645 ,p_actual_end_date => p_rec.actual_end_date
646 ,p_object_version_number => p_rec.object_version_number
647 );
648 --
649 -- Validate Dependent Attributes
650 --
651 --
652 hr_utility.set_location(' Leaving:'||l_proc, 10);
653 End insert_validate;
654 --
655 -- ----------------------------------------------------------------------------
656 -- |---------------------------< update_validate >----------------------------|
657 -- ----------------------------------------------------------------------------
658 Procedure update_validate
659 (p_rec in per_naa_shd.g_rec_type
660 ) is
661 --
662 l_proc varchar2(72) := g_package||'update_validate';
663 --
664 Begin
665 hr_utility.set_location('Entering:'||l_proc, 5);
666 --
667 -- Call all supporting business operations
668 --
669 -- Check absence is there with right version
670 --
671 chk_absence_action_id
672 (p_absence_action_id => p_rec.absence_action_id
673 ,p_object_version_number => p_rec.object_version_number
674 );
675 --
676 -- Check Absence attendance id
677 --
678 chk_absence_attendance_id
679 (p_absence_action_id => p_rec.absence_action_id
680 ,p_absence_attendance_id => p_rec.absence_attendance_id
681 ,p_object_version_number => p_rec.object_version_number
682 );
683 --
684 -- Check Expected date
685 -- changes as per bug 2637189
686 -- Bug# 5031662, Reverse the changes made for bug 2637189
687 /*chk_expected_date
688 (p_absence_action_id => p_rec.absence_action_id
689 ,p_absence_attendance_id => p_rec.absence_attendance_id
690 ,p_expected_date => p_rec.expected_date
691 ,p_object_version_number => p_rec.object_version_number
692 );*/
693 --
694 -- Check description
695 --
696 chk_description
697 (p_absence_action_id => p_rec.absence_action_id
698 ,p_description => p_rec.description
699 ,p_object_version_number => p_rec.object_version_number
700 );
701 --
702 -- Check actual start date and actual end date.
703 --
704 chk_start_and_end_dates
705 (p_absence_action_id => p_rec.absence_action_id
706 ,p_actual_start_date => p_rec.actual_start_date
707 ,p_actual_end_date => p_rec.actual_end_date
708 ,p_object_version_number => p_rec.object_version_number
709 );
710 --
711 -- Validate Dependent Attributes
712 --
713 chk_non_updateable_args
714 (p_rec => p_rec
715 );
716 --
717 --
718 hr_utility.set_location(' Leaving:'||l_proc, 10);
719 End update_validate;
720 --
721 -- ----------------------------------------------------------------------------
722 -- |---------------------------< delete_validate >----------------------------|
723 -- ----------------------------------------------------------------------------
724 Procedure delete_validate
725 (p_rec in per_naa_shd.g_rec_type
726 ) is
727 --
728 l_proc varchar2(72) := g_package||'delete_validate';
729 --
730 Begin
731 hr_utility.set_location('Entering:'||l_proc, 5);
732 --
733 -- Call all supporting business operations
734 --
735 -- Check absence is there with right version
736 --
737 chk_absence_action_id
738 (p_absence_action_id => p_rec.absence_action_id
739 ,p_object_version_number => p_rec.object_version_number
740 );
741 --
742 --
743 hr_utility.set_location(' Leaving:'||l_proc, 10);
744 End delete_validate;
745 --
746 end per_naa_bus;