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