[Home] [Help]
PACKAGE BODY: APPS.GHR_NLA_BUS
Source
1 Package Body ghr_nla_bus as
2 /* $Header: ghnlarhi.pkb 120.1.12010000.1 2009/03/26 10:12:16 utokachi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_nla_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_noac_la_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- noac_la_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_noac_la_id(p_noac_la_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72);
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 l_proc := g_package||'chk_noac_la_id';
45 hr_utility.set_location('Entering:'||l_proc, 5);
46 --
47 l_api_updating := ghr_nla_shd.api_updating
48 (p_noac_la_id => p_noac_la_id,
49 p_object_version_number => p_object_version_number);
50 --
51 if (l_api_updating
52 and nvl(p_noac_la_id,hr_api.g_number)
53 <> ghr_nla_shd.g_old_rec.noac_la_id) then
54 --
55 -- raise error as PK has changed
56 --
57 ghr_nla_shd.constraint_error('GHR_NOAC_LAS_PK');
58 --
59 elsif not l_api_updating then
60 --
61 -- check if PK is null
62 --
63 if p_noac_la_id is not null then
64 --
68 --
65 -- raise error as PK is not null
66 --
67 ghr_nla_shd.constraint_error('GHR_NOAC_LAS_PK');
69 end if;
70 --
71 end if;
72 --
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 --
75 End chk_noac_la_id;
76 --
77 -- ----------------------------------------------------------------------------
78 -- |------< chk_nature_of_action_id >------|
79 -- ----------------------------------------------------------------------------
80 --
81 -- Description
82 -- This procedure checks that a referenced foreign key actually exists
83 -- in the referenced table.
84 --
85 -- Pre-Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- p_noac_la_id PK
90 -- p_nature_of_action_id ID of FK column
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_nature_of_action_id (p_noac_la_id in number,
103 p_nature_of_action_id in number,
104 p_object_version_number in number) is
105 --
106 l_proc varchar2(72) := g_package||'chk_nature_of_action_id';
107 l_api_updating boolean;
108 l_dummy varchar2(1);
109 --
110 cursor c1 is
111 select null
112 from ghr_nature_of_actions a
113 where a.nature_of_action_id = p_nature_of_action_id;
114 --
115 Begin
116 --
117 hr_utility.set_location('Entering:'||l_proc,5);
118 --
119 l_api_updating := ghr_nla_shd.api_updating
120 (p_noac_la_id => p_noac_la_id,
121 p_object_version_number => p_object_version_number);
122 --
123 if (l_api_updating
124 and nvl(p_nature_of_action_id,hr_api.g_number)
125 <> nvl(ghr_nla_shd.g_old_rec.nature_of_action_id,hr_api.g_number)
126 or not l_api_updating) then
127 --
128 -- check if nature_of_action_id value exists in ghr_nature_of_actions table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in ghr_nature_of_actions
138 -- table.
139 --
140 ghr_nla_shd.constraint_error('GHR_NOAC_LAS_FK1');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 End chk_nature_of_action_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_enabled_flag >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure is used to check that the lookup value is valid.
158 --
159 -- Pre Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- noac_la_id PK of record being inserted or updated.
164 -- enabled_flag Value of lookup code.
165 -- effective_date effective date
166 -- object_version_number Object version number of record being
167 -- inserted or updated.
168 --
169 -- Post Success
170 -- Processing continues
171 --
172 -- Post Failure
173 -- Error handled by procedure
174 --
175 -- Access Status
176 -- Internal table handler use only.
177 --
178 Procedure chk_enabled_flag(p_noac_la_id in number,
179 p_enabled_flag in varchar2,
180 p_effective_date in date,
181 p_object_version_number in number) is
182 --
183 l_proc varchar2(72);
184 l_api_updating boolean;
185 --
186 Begin
187 --
188 l_proc := g_package||'chk_enabled_flag';
189 hr_utility.set_location('Entering:'||l_proc, 5);
190 --
191 l_api_updating := ghr_nla_shd.api_updating
192 (p_noac_la_id => p_noac_la_id,
193 p_object_version_number => p_object_version_number);
194 --
195 if (l_api_updating
196 and p_enabled_flag
197 <> nvl(ghr_nla_shd.g_old_rec.enabled_flag,hr_api.g_varchar2)
198 or not l_api_updating) then
199 --
200 -- check if value of lookup falls within lookup type.
201 --
202 --
203 if hr_api.not_exists_in_hr_lookups
204 (p_lookup_type => 'YES_NO',
205 p_lookup_code => p_enabled_flag,
206 p_effective_date => p_effective_date) then
207 --
208 -- raise error as does not exist as lookup
209 --
210 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
211 hr_utility.raise_error;
212 --
213 end if;
214 --
215 end if;
216 --
217 hr_utility.set_location('Leaving:'||l_proc,10);
218 --
219 end chk_enabled_flag;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |------< chk_valid_second_lac_flag >------|
223 -- ----------------------------------------------------------------------------
224 --
225 -- Description
226 -- This procedure is used to check that the lookup value is valid.
227 --
228 -- Pre Conditions
229 -- None.
230 --
231 -- In Parameters
232 -- noac_la_id PK of record being inserted or updated.
233 -- valid_second_lac_flag Value of lookup code.
237 --
234 -- effective_date effective date
235 -- object_version_number Object version number of record being
236 -- inserted or updated.
238 -- Post Success
239 -- Processing continues
240 --
241 -- Post Failure
242 -- Error handled by procedure
243 --
244 -- Access Status
245 -- Internal table handler use only.
246 --
247 Procedure chk_valid_second_lac_flag(p_noac_la_id in number,
248 p_valid_second_lac_flag in varchar2,
249 p_effective_date in date,
250 p_object_version_number in number) is
251 --
252 l_proc varchar2(72) ;
253 l_api_updating boolean;
254 --
255 Begin
256 --
257 l_proc := g_package||'chk_valid_second_lac_flag';
258 hr_utility.set_location('Entering:'||l_proc, 5);
259 --
260 l_api_updating := ghr_nla_shd.api_updating
261 (p_noac_la_id => p_noac_la_id,
262 p_object_version_number => p_object_version_number);
263 --
264 if (l_api_updating
265 and p_valid_second_lac_flag
266 <> nvl(ghr_nla_shd.g_old_rec.valid_second_lac_flag,hr_api.g_varchar2)
267 or not l_api_updating) then
268 --
269 -- check if value of lookup falls within lookup type.
270 --
271 --
272 if hr_api.not_exists_in_hr_lookups
273 (p_lookup_type => 'YES_NO',
274 p_lookup_code => p_valid_second_lac_flag,
275 p_effective_date => p_effective_date) then
276 --
277 -- raise error as does not exist as lookup
278 --
279 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
280 hr_utility.raise_error;
281 --
282 end if;
283 --
284 end if;
285 --
286 hr_utility.set_location('Leaving:'||l_proc,10);
287 --
288 end chk_valid_second_lac_flag;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |------< chk_valid_first_lac_flag >------|
292 -- ----------------------------------------------------------------------------
293 --
294 -- Description
295 -- This procedure is used to check that the lookup value is valid.
296 --
297 -- Pre Conditions
298 -- None.
299 --
300 -- In Parameters
301 -- noac_la_id PK of record being inserted or updated.
302 -- valid_first_lac_flag Value of lookup code.
303 -- effective_date effective date
304 -- object_version_number Object version number of record being
305 -- inserted or updated.
306 --
307 -- Post Success
308 -- Processing continues
309 --
310 -- Post Failure
311 -- Error handled by procedure
312 --
313 -- Access Status
314 -- Internal table handler use only.
315 --
316 Procedure chk_valid_first_lac_flag(p_noac_la_id in number,
317 p_valid_first_lac_flag in varchar2,
318 p_effective_date in date,
319 p_object_version_number in number) is
320 --
321 l_proc varchar2(72);
322 l_api_updating boolean;
323 --
324 Begin
325 --
326 l_proc := g_package||'chk_valid_first_lac_flag';
327 hr_utility.set_location('Entering:'||l_proc, 5);
328 --
329 l_api_updating := ghr_nla_shd.api_updating
330 (p_noac_la_id => p_noac_la_id,
331 p_object_version_number => p_object_version_number);
332 --
333 if (l_api_updating
334 and p_valid_first_lac_flag
335 <> nvl(ghr_nla_shd.g_old_rec.valid_first_lac_flag,hr_api.g_varchar2)
336 or not l_api_updating) then
337 --
338 -- check if value of lookup falls within lookup type.
339 --
340 --
341 if hr_api.not_exists_in_hr_lookups
342 (p_lookup_type => 'YES_NO',
343 p_lookup_code => p_valid_first_lac_flag,
344 p_effective_date => p_effective_date) then
345 --
346 -- raise error as does not exist as lookup
347 --
348 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
349 hr_utility.raise_error;
350 --
351 end if;
352 --
353 end if;
354 --
355 hr_utility.set_location('Leaving:'||l_proc,10);
356 --
357 end chk_valid_first_lac_flag;
358 --
359 -- ----------------------------------------------------------------------------
360 -- |------< chk_date_from >------|
361 -- ----------------------------------------------------------------------------
362 --
363 -- Description
364 -- This procedure is used to check that the mandatory date_from is entered
365 -- If the date_to parameter is not null then this procedure checks that it
366 -- is greater than or equal to the p_date_from
367 --
368 -- Pre Conditions
369 -- None.
370 --
371 -- In Parameters
372 -- noac_la_id PK of record being inserted or updated.
373 -- date_from date from
374 -- date_to date to
375 -- object_version_number Object version number of record being
376 -- inserted or updated.
377 --
378 -- Post Success
379 -- Processing continues
380 --
381 -- Post Failure
382 -- Error handled by procedure
383 --
384 -- Access Status
385 -- Internal table handler use only.
386 --
387
388 Procedure chk_date_from ( p_noac_la_id in number,
389 p_date_from in date,
393 l_proc varchar2(72) ;
390 p_date_to in date,
391 p_object_version_number in number) is
392 --
394 l_api_updating boolean;
395 --
396 Begin
397 --
398 l_proc := g_package||'chk_date_from';
399 hr_utility.set_location('Entering:'||l_proc, 5);
400 --
401 l_api_updating := ghr_nla_shd.api_updating
402 (p_noac_la_id => p_noac_la_id,
403 p_object_version_number => p_object_version_number);
404 --
405 if (l_api_updating
406 and p_date_from
407 <> nvl(ghr_nla_shd.g_old_rec.date_from,hr_api.g_date)
408 or not l_api_updating) then
409 --
410 -- check if value of date_from is not null
411 --
412 hr_api.mandatory_arg_error
413 (p_api_name => l_proc
414 ,p_argument => 'date_from'
415 ,p_argument_value => p_date_from
416 );
417 end if;
418 --
419 -- if date_to is not null then we check that date_to is
420 -- greater than or equal to date_from
421 --
422 if p_date_to is not null then
423 if trunc(p_date_from) > trunc(p_date_to) then
424 hr_utility.set_message(8301,'GHR_38196_TO_DATE_LESSER');
425 hr_utility.raise_error;
426 end if;
427 end if;
428
429
430 hr_utility.set_location('Leaving:'||l_proc,10);
431 --
432 end chk_date_from;
433 --
434 -- ----------------------------------------------------------------------------
435 -- |------< chk_unique_act_lac_cd >------|
436 -- ----------------------------------------------------------------------------
437 --
438 -- Description
439 -- This procedure is used to check that the combination of nature_of_action_id
440 -- and lac_lookup_code is unique
441 --
442 -- Pre Conditions
443 -- None.
444 --
445 -- In Parameters
446 -- noac_la_id PK of record being inserted or updated.
447 -- nature_of_action_id
448 -- lac_lookup_code
449 -- object_version_number Object version number of record being
450 -- inserted or updated.
451 --
452 -- Post Success
453 -- Processing continues
454 --
455 -- Post Failure
456 -- Error handled by procedure
457 --
458 -- Access Status
459 -- Internal table handler use only.
460 --
461
462 Procedure chk_unique_act_lac_cd ( p_noac_la_id in number,
463 p_nature_of_action_id in number,
464 p_lac_lookup_code in varchar2,
465 p_object_version_number in number) is
466 --
467 l_proc varchar2(72) ;
468 l_api_updating boolean;
469 l_dummy varchar2(1);
470 --
471 cursor c1 is
472 select null
473 from ghr_noac_las a
474 where a.nature_of_action_id = p_nature_of_action_id
475 and a.lac_lookup_code = p_lac_lookup_code;
476 --
477 Begin
478 --
479 l_proc := g_package||'chk_unique_act_lac_cd';
480 hr_utility.set_location('Entering:'||l_proc, 5);
481 --
482 l_api_updating := ghr_nla_shd.api_updating
483 (p_noac_la_id => p_noac_la_id,
484 p_object_version_number => p_object_version_number);
485 --
486 hr_utility.set_location('Entering:'||l_proc, 10);
487 --
488 if (l_api_updating
489 and ( p_nature_of_action_id
490 <> nvl(ghr_nla_shd.g_old_rec.nature_of_action_id,hr_api.g_number)
491 or
492 p_lac_lookup_code
493 <> nvl(ghr_nla_shd.g_old_rec.lac_lookup_code,hr_api.g_varchar2)
494 )
495 or not l_api_updating) then
496 --
497 -- check if the combination of action_id and lac_lookup_code already exits
498 --
499 --
500 hr_utility.set_location('Entering:'||l_proc, 15);
501 --
502 open c1;
503 --
504 --
505 hr_utility.set_location('Entering:'||l_proc, 16);
506 --
507 fetch c1 into l_dummy;
508 --
509 hr_utility.set_location('Entering:'||l_proc, 17);
510 --
511 if c1%found then
512 --
513 close c1;
514 --
515 --
516 hr_utility.set_location('Entering:'||l_proc, 18);
517 --
518 -- raise error as the combination already exists
519 --
520 hr_utility.set_message(8301,'GHR_NOAC_LAC_DUPLICATE');
521 hr_utility.raise_error;
522 end if;
523 --
524 close c1;
525
526 end if;
527 --
528 hr_utility.set_location('Leaving:'||l_proc,25);
529 --
530 end chk_unique_act_lac_cd;
531 --
532 -- ----------------------------------------------------------------------------
533 -- |------< chk_lac_lookup_code >------|
534 -- ----------------------------------------------------------------------------
535 --
536 -- Description
537 -- This procedure is used to check that the lookup value is valid.
538 --
539 -- Pre Conditions
540 -- None.
541 --
542 -- In Parameters
543 -- noac_la_id PK of record being inserted or updated.
544 -- lac_lookup_code Value of lookup code.
545 -- effective_date effective date
546 -- object_version_number Object version number of record being
547 -- inserted or updated.
548 --
549 -- Post Success
550 -- Processing continues
551 --
552 -- Post Failure
553 -- Error handled by procedure
557 --
554 --
555 -- Access Status
556 -- Internal table handler use only.
558 Procedure chk_lac_lookup_code(p_noac_la_id in number,
559 p_lac_lookup_code in varchar2,
560 p_effective_date in date,
561 p_object_version_number in number) is
562 --
563 l_proc varchar2(72) ;
564 l_api_updating boolean;
565 --
566 Begin
567 --
568 l_proc := g_package||'chk_lac_lookup_code';
569 hr_utility.set_location('Entering:'||l_proc, 5);
570 --
571 l_api_updating := ghr_nla_shd.api_updating
572 (p_noac_la_id => p_noac_la_id,
573 p_object_version_number => p_object_version_number);
574 --
575 if (l_api_updating
576 and p_lac_lookup_code
577 <> nvl(ghr_nla_shd.g_old_rec.lac_lookup_code,hr_api.g_varchar2)
578 or not l_api_updating) then
579 --
580 -- check if value of lookup falls within lookup type.
581 --
582 --
583 if hr_api.not_exists_in_hr_lookups
584 (p_lookup_type => 'GHR_US_LEGAL_AUTHORITY',
585 p_lookup_code => p_lac_lookup_code,
586 p_effective_date => p_effective_date) then
587 --
588 -- raise error as does not exist as lookup
589 --
590 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
591 hr_utility.raise_error;
592 --
593 end if;
594 --
595 end if;
596 --
597 hr_utility.set_location('Leaving:'||l_proc,10);
598 --
599 end chk_lac_lookup_code;
600 --
601 -- ----------------------------------------------------------------------------
602 -- |---------------------------< insert_validate >----------------------------|
603 -- ----------------------------------------------------------------------------
604 Procedure insert_validate(p_rec in ghr_nla_shd.g_rec_type
605 ,p_effective_date in date) is
606 --
607 l_proc varchar2(72);
608 --
609 Begin
610 l_proc := g_package||'insert_validate';
611 hr_utility.set_location('Entering:'||l_proc, 5);
612 --
613 -- Set up the CLIENT_INFO
614 --
615 ghr_utility.set_client_info;
616 --
617 -- Call all supporting business operations
618 --
619 chk_noac_la_id
620 (p_noac_la_id => p_rec.noac_la_id,
621 p_object_version_number => p_rec.object_version_number);
622 --
623 chk_nature_of_action_id
624 (p_noac_la_id => p_rec.noac_la_id,
625 p_nature_of_action_id => p_rec.nature_of_action_id,
626 p_object_version_number => p_rec.object_version_number);
627 --
628 chk_enabled_flag
629 (p_noac_la_id => p_rec.noac_la_id,
630 p_enabled_flag => p_rec.enabled_flag,
631 p_effective_date => p_effective_date,
632 p_object_version_number => p_rec.object_version_number);
633 --
634 chk_valid_second_lac_flag
635 (p_noac_la_id => p_rec.noac_la_id,
636 p_valid_second_lac_flag => p_rec.valid_second_lac_flag,
637 p_effective_date => p_effective_date,
638 p_object_version_number => p_rec.object_version_number);
639 --
640 chk_valid_first_lac_flag
641 (p_noac_la_id => p_rec.noac_la_id,
642 p_valid_first_lac_flag => p_rec.valid_first_lac_flag,
643 p_effective_date => p_effective_date,
644 p_object_version_number => p_rec.object_version_number);
645 --
646 --
647 chk_date_from
648 (p_noac_la_id => p_rec.noac_la_id,
649 p_date_from => p_rec.date_from,
650 p_date_to => p_rec.date_to,
651 p_object_version_number => p_rec.object_version_number);
652 --
653 chk_unique_act_lac_cd
654 (p_noac_la_id => p_rec.noac_la_id,
655 p_nature_of_action_id => p_rec.nature_of_action_id,
656 p_lac_lookup_code => p_rec.lac_lookup_code,
657 p_object_version_number => p_rec.object_version_number);
658 --
659 chk_lac_lookup_code
660 (p_noac_la_id => p_rec.noac_la_id,
661 p_lac_lookup_code => p_rec.lac_lookup_code,
662 p_effective_date => p_effective_date,
663 p_object_version_number => p_rec.object_version_number);
664 --
665 --
666 --
667 hr_utility.set_location(' Leaving:'||l_proc, 10);
668 End insert_validate;
669 --
670 -- ----------------------------------------------------------------------------
671 -- |---------------------------< update_validate >----------------------------|
672 -- ----------------------------------------------------------------------------
673 Procedure update_validate(p_rec in ghr_nla_shd.g_rec_type
674 ,p_effective_date in date) is
675 --
676 l_proc varchar2(72);
677 --
678 Begin
679 l_proc := g_package||'update_validate';
680 hr_utility.set_location('Entering:'||l_proc, 5);
681 --
682 -- Set up the CLIENT_INFO
683 --
684 ghr_utility.set_client_info;
685 --
686 -- Call all supporting business operations
687 --
688 chk_noac_la_id
689 (p_noac_la_id => p_rec.noac_la_id,
690 p_object_version_number => p_rec.object_version_number);
691 --
692 chk_nature_of_action_id
693 (p_noac_la_id => p_rec.noac_la_id,
694 p_nature_of_action_id => p_rec.nature_of_action_id,
695 p_object_version_number => p_rec.object_version_number);
696 --
697 chk_enabled_flag
698 (p_noac_la_id => p_rec.noac_la_id,
699 p_enabled_flag => p_rec.enabled_flag,
700 p_effective_date => p_effective_date,
701 p_object_version_number => p_rec.object_version_number);
702 --
703 chk_valid_second_lac_flag
704 (p_noac_la_id => p_rec.noac_la_id,
705 p_valid_second_lac_flag => p_rec.valid_second_lac_flag,
706 p_effective_date => p_effective_date,
707 p_object_version_number => p_rec.object_version_number);
708 --
709 chk_valid_first_lac_flag
710 (p_noac_la_id => p_rec.noac_la_id,
711 p_valid_first_lac_flag => p_rec.valid_first_lac_flag,
712 p_effective_date => p_effective_date,
713 p_object_version_number => p_rec.object_version_number);
714 --
715 --
716 chk_date_from
717 (p_noac_la_id => p_rec.noac_la_id,
718 p_date_from => p_rec.date_from,
719 p_date_to => p_rec.date_to,
720 p_object_version_number => p_rec.object_version_number);
721 --
722 chk_unique_act_lac_cd
723 (p_noac_la_id => p_rec.noac_la_id,
724 p_nature_of_action_id => p_rec.nature_of_action_id,
725 p_lac_lookup_code => p_rec.lac_lookup_code,
726 p_object_version_number => p_rec.object_version_number);
727 --
728 chk_lac_lookup_code
729 (p_noac_la_id => p_rec.noac_la_id,
730 p_lac_lookup_code => p_rec.lac_lookup_code,
731 p_effective_date => p_effective_date,
732 p_object_version_number => p_rec.object_version_number);
733 --
734 --
735 --
736 hr_utility.set_location(' Leaving:'||l_proc, 10);
737 End update_validate;
738 --
739 -- ----------------------------------------------------------------------------
740 -- |---------------------------< delete_validate >----------------------------|
741 -- ----------------------------------------------------------------------------
742 Procedure delete_validate(p_rec in ghr_nla_shd.g_rec_type
743 ,p_effective_date in date) is
744 --
745 l_proc varchar2(72) ;
746 --
747 Begin
748 l_proc := g_package||'delete_validate';
749 hr_utility.set_location('Entering:'||l_proc, 5);
750 --
751 -- Call all supporting business operations
752 --
753 hr_utility.set_location(' Leaving:'||l_proc, 10);
754 End delete_validate;
755 --
756 end ghr_nla_bus;