[Home] [Help]
PACKAGE BODY: APPS.BEN_XER_BUS
Source
1 Package Body ben_xer_bus as
2 /* $Header: bexerrhi.pkb 120.1 2006/03/22 13:57:32 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xer_bus.'; -- Global package name
9 g_legislation_code varchar2(150) default null;
10 --
11 -- ---------------------------------------------------------------------------
12 -- |----------------------< set_security_group_id >--------------------------|
13 -- ---------------------------------------------------------------------------
14 --
15 Procedure set_security_group_id
16 (p_ext_data_elmt_in_rcd_id in number
17 ) is
18 --
19 -- Declare cursor
20 --
21 cursor csr_sec_grp is
22 select pbg.security_group_id
23 from per_business_groups pbg
24 , ben_ext_data_elmt_in_rcd bde
25 where bde.ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
26 and pbg.business_group_id = bde.business_group_id;
27 --
28 -- Declare local variables
29 --
30 l_security_group_id number;
31 l_proc varchar2(72) := g_package||'set_security_group_id';
32 --
33 begin
34 --
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 --
37 -- Ensure that all the mandatory parameter are not null
38 --
39 hr_api.mandatory_arg_error
40 (p_api_name => l_proc
41 ,p_argument => 'ext_data_elmt_in_rcd_id'
42 ,p_argument_value => p_ext_data_elmt_in_rcd_id
43 );
44 --
45 open csr_sec_grp;
46 fetch csr_sec_grp into l_security_group_id;
47 --
48 if csr_sec_grp%notfound then
49 --
50 close csr_sec_grp;
51 --
52 -- The primary key is invalid therefore we must error
53 --
54 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
55 fnd_message.raise_error;
56 --
57 end if;
58 close csr_sec_grp;
59 --
60 -- Set the security_group_id in CLIENT_INFO
61 --
62 hr_api.set_security_group_id
63 (p_security_group_id => l_security_group_id
64 );
65 --
66 hr_utility.set_location(' Leaving:'|| l_proc, 20);
67 --
68 end set_security_group_id;
69 --
70 --
71 -- ----------------------------------------------------------------------------
72 -- |------< chk_ext_data_elmt_in_rcd_id >------|
73 -- ----------------------------------------------------------------------------
74 --
75 -- Description
76 -- This procedure is used to check that the primary key for the table
77 -- is created properly. It should be null on insert and
78 -- should not be able to be updated.
79 --
80 -- Pre Conditions
81 -- None.
82 --
83 -- In Parameters
84 -- ext_data_elmt_in_rcd_id PK of record being inserted or updated.
85 -- object_version_number Object version number of record being
86 -- inserted or updated.
87 --
88 -- Post Success
89 -- Processing continues
90 --
91 -- Post Failure
92 -- Errors handled by the procedure
93 --
94 -- Access Status
95 -- Internal table handler use only.
96 --
97 Procedure chk_ext_data_elmt_in_rcd_id(p_ext_data_elmt_in_rcd_id in number,
98 p_object_version_number in number) is
99 --
100 l_proc varchar2(72) := g_package||'chk_ext_data_elmt_in_rcd_id';
101 l_api_updating boolean;
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'||l_proc, 5);
106 --
107 l_api_updating := ben_xer_shd.api_updating
108 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
109 p_object_version_number => p_object_version_number);
110 --
111 if (l_api_updating
112 and nvl(p_ext_data_elmt_in_rcd_id,hr_api.g_number)
113 <> ben_xer_shd.g_old_rec.ext_data_elmt_in_rcd_id) then
114 --
115 -- raise error as PK has changed
116 --
117 ben_xer_shd.constraint_error('BEN_EXT_DATA_ELMT_IN_RCD_PK');
118 --
119 elsif not l_api_updating then
120 --
121 -- check if PK is null
122 --
123 if p_ext_data_elmt_in_rcd_id is not null then
124 --
125 -- raise error as PK is not null
126 --
127 ben_xer_shd.constraint_error('BEN_EXT_DATA_ELMT_IN_RCD_PK');
128 --
129 end if;
130 --
131 end if;
132 --
133 hr_utility.set_location('Leaving:'||l_proc, 10);
134 --
135 End chk_ext_data_elmt_in_rcd_id;
136 --
137 -- ----------------------------------------------------------------------------
138 -- |------< chk_ext_rcd_id >------|
139 -- ----------------------------------------------------------------------------
140 --
141 -- Description
142 -- This procedure checks that a referenced foreign key actually exists
143 -- in the referenced table.
144 --
145 -- Pre-Conditions
146 -- None.
147 --
148 -- In Parameters
149 -- p_ext_data_elmt_in_rcd_id PK
150 -- p_ext_rcd_id ID of FK column
151 -- p_object_version_number object version number
152 --
153 -- Post Success
154 -- Processing continues
155 --
156 -- Post Failure
157 -- Error raised.
158 --
159 -- Access Status
160 -- Internal table handler use only.
161 --
162 Procedure chk_ext_rcd_id (p_ext_data_elmt_in_rcd_id in number,
163 p_ext_rcd_id in number,
164 p_object_version_number in number) is
165 --
166 l_proc varchar2(72) := g_package||'chk_ext_rcd_id';
167 l_api_updating boolean;
168 l_dummy varchar2(1);
169 --
170 cursor c1 is
171 select null
172 from ben_ext_rcd a
173 where a.ext_rcd_id = p_ext_rcd_id;
174 --
175 Begin
176 --
177 hr_utility.set_location('Entering:'||l_proc,5);
178 --
179 l_api_updating := ben_xer_shd.api_updating
180 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
181 p_object_version_number => p_object_version_number);
182 --
183 if (l_api_updating
184 and nvl(p_ext_rcd_id,hr_api.g_number)
185 <> nvl(ben_xer_shd.g_old_rec.ext_rcd_id,hr_api.g_number)
186 or not l_api_updating) then
187 --
188 -- check if ext_rcd_id value exists in ben_ext_rcd table
189 --
190 open c1;
191 --
192 fetch c1 into l_dummy;
193 if c1%notfound then
194 --
195 close c1;
196 --
197 -- raise error as FK does not relate to PK in ben_ext_rcd
198 -- table.
199 --
200 ben_xer_shd.constraint_error('BEN_EXT_DATA_ELMT_IN_RCD_FK2');
201 --
202 end if;
203 --
204 close c1;
205 --
206 end if;
207 --
208 hr_utility.set_location('Leaving:'||l_proc,10);
209 --
210 End chk_ext_rcd_id;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |------< chk_ext_data_elmt_id >------|
214 -- ----------------------------------------------------------------------------
215 --
216 -- Description
217 -- This procedure checks that a referenced foreign key actually exists
218 -- in the referenced table.
219 --
220 -- Pre-Conditions
221 -- None.
222 --
223 -- In Parameters
224 -- p_ext_data_elmt_in_rcd_id PK
225 -- p_ext_data_elmt_id ID of FK column
226 -- p_object_version_number object version number
227 --
228 -- Post Success
229 -- Processing continues
230 --
231 -- Post Failure
232 -- Error raised.
233 --
234 -- Access Status
235 -- Internal table handler use only.
236 --
237 Procedure chk_ext_data_elmt_id (p_ext_data_elmt_in_rcd_id in number,
238 p_ext_data_elmt_id in number,
239 p_object_version_number in number) is
240 --
241 l_proc varchar2(72) := g_package||'chk_ext_data_elmt_id';
242 l_api_updating boolean;
243 l_dummy varchar2(1);
244 --
245 cursor c1 is
246 select null
247 from ben_ext_data_elmt a
248 where a.ext_data_elmt_id = p_ext_data_elmt_id;
249 --
250 Begin
251 --
252 hr_utility.set_location('Entering:'||l_proc,5);
253 --
254 l_api_updating := ben_xer_shd.api_updating
255 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
256 p_object_version_number => p_object_version_number);
257 --
258 if (l_api_updating
259 and nvl(p_ext_data_elmt_id,hr_api.g_number)
260 <> nvl(ben_xer_shd.g_old_rec.ext_data_elmt_id,hr_api.g_number)
261 or not l_api_updating) then
262 --
263 -- check if ext_data_elmt_id value exists in ben_ext_data_elmt table
264 --
265 open c1;
266 --
267 fetch c1 into l_dummy;
268 if c1%notfound then
269 --
270 close c1;
271 --
272 -- raise error as FK does not relate to PK in ben_ext_data_elmt
273 -- table.
274 --
275 ben_xer_shd.constraint_error('BEN_EXT_DATA_ELMT_IN_RCD_FK1');
276 --
277 end if;
278 --
279 close c1;
280 --
281 end if;
282 --
283 hr_utility.set_location('Leaving:'||l_proc,10);
284 --
285 End chk_ext_data_elmt_id;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |------< chk_seq_num >------|
289 -- ----------------------------------------------------------------------------
290 --
291 -- Description
292 -- This procedure is used to check that the sequence number is valid.
293 -- Pre Conditions
294 -- None.
295 --
296 -- In Parameters
297 -- seq_num of record being inserted or updated.
298 --
299 -- Post Success
300 -- Processing continues
301 --
302 -- Post Failure
303 -- Error handled by procedure
304 --
305 -- Access Status
306 -- Internal table handler use only.
307 --
308 Procedure chk_seq_num(p_seq_num in number
309 ,p_ext_rcd_id in number
310 ,p_business_group_id in number
311 ,p_legislation_code in varchar2)
312 is
313 --
314 l_proc varchar2(72) := g_package||'chk_seq_num';
315 l_dummy char(1);
316 cursor c1 is select 1
317 from ben_ext_data_elmt_in_rcd
318 Where ext_rcd_id = p_ext_rcd_id
319 and seq_num = p_seq_num
320 -- and business_group_id = p_business_group_id
321 and ( (business_group_id is null -- is global
322 and legislation_code is null
323 )
324 or -- is legilsation specific
325 (legislation_code is not null
326 and legislation_code = p_legislation_code)
327 or -- is business group specific
328 (business_group_id is not null
329 and business_group_id = p_business_group_id)
330 )
331 ;
332 --
333 Begin
334 --
335 hr_utility.set_location('Entering:'||l_proc, 5);
336 --
337 --
338 if p_seq_num is null or p_seq_num < 1 or p_seq_num > 300
339 then
340 fnd_message.set_name('BEN','BEN_91863_INVLD_SEQ_NUM');
341 fnd_message.raise_error;
342 end if;
343 --
344 hr_utility.set_location('Leaving:'||l_proc,10);
345 --
346 end chk_seq_num;
347 --
348 -- ----------------------------------------------------------------------------
349 -- |------< chk_seq_num_unq >------|
350 -- ----------------------------------------------------------------------------
351 --
352 -- Description
353 -- This procedure is used to check that the sequence number is unique.
354 -- Pre Conditions
355 -- None.
356 --
357 -- In Parameters
358 -- seq_num of record being inserted or updated.
359 --
360 -- Post Success
361 -- Processing continues
362 --
363 -- Post Failure
364 -- Error handled by procedure
365 --
366 -- Access Status
367 -- Internal table handler use only.
368 --
369 Procedure chk_seq_num_unq (p_ext_data_elmt_in_rcd_id in number
370 ,p_seq_num in number
371 ,p_ext_rcd_id in number
372 ,p_business_group_id in number
373 ,p_legislation_code in varchar2)
374 is
375 --
376 l_proc varchar2(72) := g_package||'chk_seq_num_unq';
377 l_dummy char(1);
378 cursor c1 is select null
379 from ben_ext_data_elmt_in_rcd
380 Where ext_rcd_id = p_ext_rcd_id
381 and ext_data_elmt_in_rcd_id <> nvl(p_ext_data_elmt_in_rcd_id,-1)
382 and seq_num = p_seq_num
383 -- and business_group_id = p_business_group_id
384 and ( (business_group_id is null -- is global
385 and legislation_code is null
386 )
387 or -- is legilsation specific
388 (legislation_code is not null
389 and legislation_code = p_legislation_code)
390 or -- is business group specific
391 (business_group_id is not null
392 and business_group_id = p_business_group_id)
393 )
394 ;
395 --
396 Begin
397 --
398 hr_utility.set_location('Entering:'||l_proc, 5);
399 --
400 --
401 open c1;
402 fetch c1 into l_dummy;
403 if c1%found then
404 close c1;
405 fnd_message.set_name('BEN','BEN_91954_SEQ_NOT_UNIQUE');
406 fnd_message.raise_error;
407 end if;
408 hr_utility.set_location('Leaving:'||l_proc,10);
409 --
410 end chk_seq_num_unq;
411 -- ----------------------------------------------------------------------------
412 -- |------< chk_strt_pos >------|
413 -- ----------------------------------------------------------------------------
414 --
415 -- Description
416 -- This procedure is used to check that the strt position is valid.
417 -- Pre Conditions
418 -- None.
419 --
420 -- In Parameters
421 -- strt_pos of record being inserted or updated.
422 --
423 -- Post Success
424 -- Processing continues
425 --
426 -- Post Failure
427 -- Error handled by procedure
428 --
429 -- Access Status
430 -- Internal table handler use only.
431 --
432 Procedure chk_strt_pos(p_strt_pos in number) is
433 --
434 l_proc varchar2(72) := g_package||'chk_strt_pos';
435 --
436 Begin
437 --
438 hr_utility.set_location('Entering:'||l_proc, 5);
439 --
440 --
441 if p_strt_pos =0 or p_strt_pos <0 then
442 fnd_message.set_name('BEN','BEN_91864_INVLD_STRT_POS');
443 fnd_message.raise_error;
444 end if;
445 --
446 hr_utility.set_location('Leaving:'||l_proc,10);
447 --
448 end chk_strt_pos;
449 --
450 -- ----------------------------------------------------------------------------
451 -- |------< chk_sprs_cd >------|
452 -- ----------------------------------------------------------------------------
453 --
454 -- Description
455 -- This procedure is used to check that the lookup value is valid.
456 --
457 -- Pre Conditions
458 -- None.
459 --
460 -- In Parameters
461 -- ext_data_elmt_in_rcd_id PK of record being inserted or updated.
465 -- inserted or updated.
462 -- sprs_cd Value of lookup code.
463 -- effective_date effective date
464 -- object_version_number Object version number of record being
466 --
467 -- Post Success
468 -- Processing continues
469 --
470 -- Post Failure
471 -- Error handled by procedure
472 --
473 -- Access Status
474 -- Internal table handler use only.
475 --
476 Procedure chk_sprs_cd(p_ext_data_elmt_in_rcd_id in number,
477 p_ext_rcd_id in number,
478 p_sprs_cd in varchar2,
479 p_effective_date in date,
480 p_business_group_id in number,
481 p_object_version_number in number) is
482 --
483 l_proc varchar2(72) := g_package||'chk_sprs_cd';
484 l_api_updating boolean;
485
486 --
487 Begin
488 --
489 hr_utility.set_location('Entering:'||l_proc, 5);
490 --
491 l_api_updating := ben_xer_shd.api_updating
492 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
493 p_object_version_number => p_object_version_number);
494 --
495 if (l_api_updating
496 and p_sprs_cd
497 <> nvl(ben_xer_shd.g_old_rec.sprs_cd,hr_api.g_varchar2)
498 or not l_api_updating)
499 and p_sprs_cd is not null then
500 --
501 -- check if value of lookup falls within lookup type.
502 --
503 if p_business_group_id is not null then
504 if hr_api.not_exists_in_hr_lookups
505 (p_lookup_type => 'BEN_EXT_SPRS',
506 p_lookup_code => p_sprs_cd,
507 p_effective_date => p_effective_date) then
508 --
509 -- raise error as does not exist as lookup
510 --
511 --
512 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
513 fnd_message.set_token('FIELD','p_sprs_cd');
514 fnd_message.set_token('TYPE','BEN_EXT_SPRS');
515 fnd_message.raise_error;
516 --
517 end if;
518 else
519 if hr_api.not_exists_in_hrstanlookups
520 (p_lookup_type => 'BEN_EXT_SPRS',
521 p_lookup_code => p_sprs_cd,
522 p_effective_date => p_effective_date) then
523 --
524 -- raise error as does not exist as lookup
525 --
526 --
527 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
528 fnd_message.set_token('FIELD','p_sprs_cd');
529 fnd_message.set_token('TYPE','BEN_EXT_SPRS');
530 fnd_message.raise_error;
531 --
532 end if;
533 --
534 end if;
535 --
536 end if;
537
538 --
539 hr_utility.set_location('Leaving:'||l_proc,10);
540 --
541 end chk_sprs_cd;
542 --
543 -- ----------------------------------------------------------------------------
544 -- |------< chk_rqd_flag >------|
545 -- ----------------------------------------------------------------------------
546 --
547 -- Description
548 -- This procedure is used to check that the lookup value is valid.
549 --
550 -- Pre Conditions
551 -- None.
552 --
553 -- In Parameters
554 -- ext_data_elmt_in_rcd_id PK of record being inserted or updated.
555 -- rqd_flag Value of lookup code.
556 -- effective_date effective date
557 -- object_version_number Object version number of record being
558 -- inserted or updated.
559 --
560 -- Post Success
561 -- Processing continues
562 --
563 -- Post Failure
564 -- Error handled by procedure
565 --
566 -- Access Status
567 -- Internal table handler use only.
568 --
569 Procedure chk_rqd_flag(p_ext_data_elmt_in_rcd_id in number,
570 p_rqd_flag in varchar2,
571 p_effective_date in date,
572 p_business_group_id in number,
573 p_object_version_number in number) is
574 --
575 l_proc varchar2(72) := g_package||'chk_rqd_flag';
576 l_api_updating boolean;
577 --
578 Begin
579 --
580 hr_utility.set_location('Entering:'||l_proc, 5);
581 --
582 l_api_updating := ben_xer_shd.api_updating
583 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
584 p_object_version_number => p_object_version_number);
585 --
586 if (l_api_updating
587 and p_rqd_flag
588 <> nvl(ben_xer_shd.g_old_rec.rqd_flag,hr_api.g_varchar2)
589 or not l_api_updating) then
590 --
591 -- check if value of lookup falls within lookup type.
592 --
593 --
594 if p_business_group_id is not null then
595 if hr_api.not_exists_in_hr_lookups
596 (p_lookup_type => 'YES_NO',
597 p_lookup_code => p_rqd_flag,
598 p_effective_date => p_effective_date) then
599 --
600 -- raise error as does not exist as lookup
601 --
602 --
603 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
604 fnd_message.set_token('FIELD','p_rqd_flag');
608 end if;
605 fnd_message.set_token('TYPE','YES_NO');
606 fnd_message.raise_error;
607 --
609 else
610 if hr_api.not_exists_in_hrstanlookups
611 (p_lookup_type => 'YES_NO',
612 p_lookup_code => p_rqd_flag,
613 p_effective_date => p_effective_date) then
614 --
615 -- raise error as does not exist as lookup
616 --
617 --
618 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
619 fnd_message.set_token('FIELD','p_rqd_flag');
620 fnd_message.set_token('TYPE','YES_NO');
621 fnd_message.raise_error;
622 --
623 end if;
624 --
625 end if;
626 --
627 end if;
628 --
629 hr_utility.set_location('Leaving:'||l_proc,10);
630 --
631 end chk_rqd_flag;
632 --
633 -- ----------------------------------------------------------------------------
634 -- |------< chk_any_or_all_cd >------|
635 -- ----------------------------------------------------------------------------
636 --
637 -- Description
638 -- This procedure is used to check that the lookup value is valid.
639 --
640 -- Pre Conditions
641 -- None.
642 --
643 -- In Parameters
644 -- ext_data_elmt_in_rcd_id PK of record being inserted or updated.
645 -- any_or_all_cd Value of lookup code.
646 -- effective_date effective date
647 -- object_version_number Object version number of record being
648 -- inserted or updated.
649 --
650 -- Post Success
651 -- Processing continues
652 --
653 -- Post Failure
654 -- Error handled by procedure
655 --
656 -- Access Status
657 -- Internal table handler use only.
658 --
659 Procedure chk_any_or_all_cd(p_ext_data_elmt_in_rcd_id in number,
660 p_any_or_all_cd in varchar2,
661 p_effective_date in date,
662 p_business_group_id in number,
663 p_object_version_number in number) is
664 --
665 l_proc varchar2(72) := g_package||'chk_any_or_all_cd';
666 l_api_updating boolean;
667 --
668 Begin
669 --
670 hr_utility.set_location('Entering:'||l_proc, 5);
671 --
672 l_api_updating := ben_xer_shd.api_updating
673 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
674 p_object_version_number => p_object_version_number);
675 --
676 if (l_api_updating
677 and p_any_or_all_cd
678 <> nvl(ben_xer_shd.g_old_rec.any_or_all_cd,hr_api.g_varchar2)
679 or not l_api_updating)
680 and p_any_or_all_cd is not null then
681 --
682 -- check if value of lookup falls within lookup type.
683 --
684 if p_business_group_id is not null then
685 if hr_api.not_exists_in_hr_lookups
686 (p_lookup_type => 'BEN_EXT_ANY_OR_ALL',
687 p_lookup_code => p_any_or_all_cd,
688 p_effective_date => p_effective_date) then
689 --
690 -- raise error as does not exist as lookup
691 --
692 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
693 fnd_message.set_token('FIELD','p_any_or_all_cd');
694 fnd_message.set_token('TYPE','BEN_EXT_ANY_OR_ALL');
695 fnd_message.raise_error;
696 --
697 end if;
698 else
699 if hr_api.not_exists_in_hrstanlookups
700 (p_lookup_type => 'BEN_EXT_ANY_OR_ALL',
701 p_lookup_code => p_any_or_all_cd,
702 p_effective_date => p_effective_date) then
703 --
704 -- raise error as does not exist as lookup
705 --
706 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
707 fnd_message.set_token('FIELD','p_any_or_all_cd');
708 fnd_message.set_token('TYPE','BEN_EXT_ANY_OR_ALL');
709 fnd_message.raise_error;
710 --
711 end if;
712 --
713 end if;
714 --
715 end if;
716 --
717 hr_utility.set_location('Leaving:'||l_proc,10);
718 --
719 end chk_any_or_all_cd;
720 --
721 -- ----------------------------------------------------------------------------
722 -- |------< chk_hide_flag >------|
723 -- ----------------------------------------------------------------------------
724 --
725 -- Description
726 -- This procedure is used to check that the lookup value is valid.
727 --
728 -- Pre Conditions
729 -- None.
730 --
731 -- In Parameters
732 -- ext_data_elmt_in_rcd_id PK of record being inserted or updated.
733 -- hide_flag Value of lookup code.
734 -- effective_date effective date
735 -- object_version_number Object version number of record being
736 -- inserted or updated.
737 --
738 -- Post Success
739 -- Processing continues
740 --
741 -- Post Failure
742 -- Error handled by procedure
743 --
744 -- Access Status
745 -- Internal table handler use only.
746 --
747 Procedure chk_hide_flag(p_ext_data_elmt_in_rcd_id in number,
751 p_object_version_number in number) is
748 p_hide_flag in varchar2,
749 p_effective_date in date,
750 p_business_group_id in number,
752 --
753 l_proc varchar2(72) := g_package||'chk_hide_flag';
754 l_api_updating boolean;
755 --
756 Begin
757 --
758 hr_utility.set_location('Entering:'||l_proc, 5);
759 --
760 l_api_updating := ben_xer_shd.api_updating
761 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
762 p_object_version_number => p_object_version_number);
763 --
764 if (l_api_updating
765 and p_hide_flag
766 <> nvl(ben_xer_shd.g_old_rec.hide_flag,hr_api.g_varchar2)
767 or not l_api_updating) then
768 --
769 -- check if value of lookup falls within lookup type.
770 --
771 --
772 if p_business_group_id is not null then
773 if hr_api.not_exists_in_hr_lookups
774 (p_lookup_type => 'YES_NO',
775 p_lookup_code => p_hide_flag,
776 p_effective_date => p_effective_date) then
777 --
778 -- raise error as does not exist as lookup
779 --
780 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
781 fnd_message.set_token('FIELD','p_hide_flag');
782 fnd_message.set_token('TYPE','YES_NO');
783 fnd_message.raise_error;
784 --
785 end if;
786 else
787 if hr_api.not_exists_in_hrstanlookups
788 (p_lookup_type => 'YES_NO',
789 p_lookup_code => p_hide_flag,
790 p_effective_date => p_effective_date) then
791 --
792 -- raise error as does not exist as lookup
793 --
794 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
795 fnd_message.set_token('FIELD','p_hide_flag');
796 fnd_message.set_token('TYPE','YES_NO');
797 fnd_message.raise_error;
798 --
799 end if;
800 --
801 end if;
802 --
803 end if;
804 --
805 hr_utility.set_location('Leaving:'||l_proc,10);
806 --
807 end chk_hide_flag;
808 --
809 -- ----------------------------------------------------------------------------
810 -- |------< chk_elmt_id >------|
811 -- ----------------------------------------------------------------------------
812 --
813 -- Description
814 -- This procedure is used to check that the data element id is not null.
815 -- Pre Conditions
816 -- None.
817 --
818 -- In Parameters
819 -- ext_data_elmt_id of record being inserted or updated.
820 --
821 -- Post Success
822 -- Processing continues
823 --
824 -- Post Failure
825 -- Error handled by procedure
826 --
827 -- Access Status
828 -- Internal table handler use only.
829 --
830 Procedure chk_elmt_id(p_ext_data_elmt_id in number) is
831 --
832 l_proc varchar2(72) := g_package||'chk_elmt_id';
833 --
834 Begin
835 --
836 hr_utility.set_location('Entering:'||l_proc, 5);
837 --
838 --
839 if p_ext_data_elmt_id is null
840 then
841 fnd_message.set_name('BEN','BEN_92116_DATA_ELMT_NULL');
842 fnd_message.raise_error;
843 end if;
844 --
845 hr_utility.set_location('Leaving:'||l_proc,10);
846 --
847 end chk_elmt_id;
848 --
849 -- ----------------------------------------------------------------------------
850 -- |------< chk_rcd_type >------|
851 -- ----------------------------------------------------------------------------
852 --
853 -- Description
854 -- This procedure is used to check whether the record type is consistent with
855 -- the data elemnt type.
856 --
857 -- Pre Conditions
858 -- None.
859 --
860 -- In Parameters
861 -- ext_data_elmt_in_rcd_id PK of record being inserted or updated.
862 -- ext_data_elmt_id data elmt PK of the record.
863 -- object_version_number Object version number of record being
864 -- inserted or updated.
865 --
866 -- Post Success
867 -- Processing continues
868 --
869 -- Post Failure
870 -- Error handled by procedure
871 --
872 -- Access Status
873 -- Internal table handler use only.
874 --
875 Procedure chk_rcd_type(p_ext_data_elmt_in_rcd_id in number,
876 p_ext_rcd_id in number,
877 p_ext_data_elmt_id in number,
878 p_object_version_number in number) is
879 --
880 l_proc varchar2(72) := g_package||'chk_rcd_type';
881 l_api_updating boolean;
882
883 cursor c1 is select rcd_type_cd
884 from ben_ext_rcd
885 where ext_rcd_id = p_ext_rcd_id;
886
887 cursor c2 is select data_elmt_typ_cd, alwd_in_rcd_cd
888 from ben_ext_data_elmt a, ben_ext_fld b
889 where a.ext_data_elmt_id = p_ext_data_elmt_id
890 and a.ext_fld_id = b.ext_fld_id;
891
892 typ_cd ben_ext_rcd.rcd_type_cd%type;
893 data_typ_cd ben_ext_data_elmt.data_elmt_typ_cd%type;
894 alwd_cd ben_ext_fld.alwd_in_rcd_cd%type;
898 hr_utility.set_location('Entering:'||l_proc, 5);
895 --
896 Begin
897 --
899 --
900 l_api_updating := ben_xer_shd.api_updating
901 (p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id,
902 p_object_version_number => p_object_version_number);
903 --
904 if (l_api_updating
905 and p_ext_data_elmt_id
906 <> nvl(ben_xer_shd.g_old_rec.ext_data_elmt_id,hr_api.g_number)
907 or not l_api_updating)
908 and p_ext_data_elmt_id is not null then
909 --
910 open c1;
911 fetch c1 into typ_cd;
912 close c1;
913 open c2;
914 fetch c2 into data_typ_cd, alwd_cd;
915 if data_typ_cd in ('D','F') then
916 if alwd_cd in ('D','H','T') then
917 if not ( alwd_cd = typ_cd or typ_cd in ('S','L') ) then
918 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
919 fnd_message.raise_error;
920 end if;
921 elsif alwd_cd = 'B' then
922 if typ_cd not in ('H','T' ,'L') then
923 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
924 fnd_message.raise_error;
925 end if;
926 end if;
927 elsif data_typ_cd = 'R' then
928 if typ_cd <> 'D' then
929 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
930 fnd_message.raise_error;
931 end if;
932 elsif data_typ_cd = 'T' then
933 if typ_cd not in ('H','T','L') then
934 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
935 fnd_message.raise_error;
936 end if;
937 end if;
938
939 close c2;
940 end if;
941
942 --
943 hr_utility.set_location('Leaving:'||l_proc,10);
944 --
945 end chk_rcd_type;
946 --
947 -- ----------------------------------------------------------------------------
948 -- |------< chk_rqd_flag >------|
949 -- ----------------------------------------------------------------------------
950 --
951 -- Description
952 -- This procedure is used to check that the lookup value is valid.
953 --
954 -- Pre Conditions
955 -- None.
956 --
957 -- ----------------------------------------------------------------------------
958 -- |----------------------< chk_startup_action >------------------------------|
959 -- ----------------------------------------------------------------------------
960 --
961 -- Description:
962 -- This procedure will check that the current action is allowed according
963 -- to the current startup mode.
964 --
965 -- ----------------------------------------------------------------------------
966 PROCEDURE chk_startup_action
967 (p_insert IN boolean
968 ,p_business_group_id IN number
969 ,p_legislation_code IN varchar2
970 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
971 --
972 BEGIN
973 --
974 -- Call the supporting procedure to check startup mode
975 --
976 IF (p_insert) THEN
977 --
978 -- Call procedure to check startup_action for inserts.
979 --
980 hr_startup_data_api_support.chk_startup_action
981 (p_generic_allowed => TRUE
982 ,p_startup_allowed => TRUE
983 ,p_user_allowed => TRUE
984 ,p_business_group_id => p_business_group_id
985 ,p_legislation_code => p_legislation_code
986 ,p_legislation_subgroup => p_legislation_subgroup
987 );
988 ELSE
989 --
990 -- Call procedure to check startup_action for updates and deletes.
991 --
992 hr_startup_data_api_support.chk_upd_del_startup_action
993 (p_generic_allowed => TRUE
994 ,p_startup_allowed => TRUE
995 ,p_user_allowed => TRUE
996 ,p_business_group_id => p_business_group_id
997 ,p_legislation_code => p_legislation_code
998 ,p_legislation_subgroup => p_legislation_subgroup
999 );
1000 END IF;
1001 --
1002 END chk_startup_action;
1003 --
1004 --bug 2804169 -- check if child records exist
1005 -- ----------------------------------------------------------------------------
1006 -- |------< chk_child_recs >------|
1007 -- ----------------------------------------------------------------------------
1008 --
1009 -- Description
1010 -- This procedure is used to check whether the data element has any child records
1011 --
1012 -- Pre Conditions
1013 -- None.
1014 --
1015 -- In Parameters
1016 -- ext_data_elmt_in_rcd_id PK of record being inserted or updated.
1017 --
1018 -- Post Success
1019 -- Processing continues
1020 --
1021 -- Post Failure
1022 -- Error handled by procedure
1023 --
1024 -- Access Status
1025 -- Internal table handler use only.
1026 --
1027 Procedure chk_child_recs(p_ext_data_elmt_in_rcd_id in number
1028 ) is
1029 --
1030 l_proc varchar2(72) := g_package||'chk_child_recs';
1031
1032
1033 cursor c_ext_where_clause is
1034 select ext_where_clause_id
1035 from ben_ext_where_clause
1036 where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
1037
1038 cursor c_ext_incl_chg_id is
1039 select ext_incl_chg_id
1040 from ben_ext_incl_chg
1044 l_ext_incl_chg_id ben_ext_incl_chg.ext_incl_chg_id%TYPE;
1041 where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
1042
1043 l_ext_where_clause_id ben_ext_where_clause.ext_where_clause_id%TYPE;
1045
1046
1047 cursor c_ext_file_group is
1048 select 'x'
1049 from ben_ext_file
1050 where EXT_DATA_ELMT_IN_RCD_ID1 = p_ext_data_elmt_in_rcd_id
1051 or EXT_DATA_ELMT_IN_RCD_ID2 = p_ext_data_elmt_in_rcd_id
1052 ;
1053
1054 l_dummy varchar2(1) ;
1055
1056 --
1057 Begin
1058 --
1059 hr_utility.set_location('Entering:'||l_proc, 5);
1060 --
1061 open c_ext_where_clause;
1062 fetch c_ext_where_clause into l_ext_where_clause_id;
1063 close c_ext_where_clause;
1064
1065 if l_ext_where_clause_id is not null then
1066 fnd_message.set_name('PER','HR_7215_DT_CHILD_EXISTS');
1067 fnd_message.set_token('TABLE_NAME','ben_ext_where_clause');
1068 fnd_message.raise_error;
1069 end if;
1070
1071
1072
1073 open c_ext_incl_chg_id;
1074 fetch c_ext_incl_chg_id into l_ext_incl_chg_id;
1075 close c_ext_incl_chg_id;
1076
1077 if l_ext_incl_chg_id is not null then
1078 fnd_message.set_name('PER','HR_7215_DT_CHILD_EXISTS');
1079 fnd_message.set_token('TABLE_NAME','ben_ext_incl_chg');
1080 fnd_message.raise_error;
1081
1082 end if;
1083
1084
1085 open c_ext_file_group ;
1086 fetch c_ext_file_group into l_dummy ;
1087 if c_ext_file_group%found then
1088 close c_ext_file_group ;
1089 fnd_message.set_name('PER','HR_7215_DT_CHILD_EXISTS');
1090 fnd_message.set_token('TABLE_NAME','ben_ext_file.grouping');
1091 fnd_message.raise_error;
1092
1093 end if ;
1094 close c_ext_file_group ;
1095
1096 --
1097 hr_utility.set_location('Leaving:'||l_proc,10);
1098 --
1099 end chk_child_recs;
1100 --
1101
1102 -- ----------------------------------------------------------------------------
1103 -- ----------------------------------------------------------------------------
1104 -- |---------------------------< insert_validate >----------------------------|
1105 -- ----------------------------------------------------------------------------
1106 Procedure insert_validate(p_rec in ben_xer_shd.g_rec_type
1107 ,p_effective_date in date) is
1108 --
1109 l_proc varchar2(72) := g_package||'insert_validate';
1110 --
1111 Begin
1112 hr_utility.set_location('Entering:'||l_proc, 5);
1113 --
1114 -- Call all supporting business operations
1115 --
1116 --
1117 chk_startup_action(True
1118 ,p_rec.business_group_id
1119 ,p_rec.legislation_code);
1120 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1121 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1122 END IF;
1123 --
1124 chk_ext_data_elmt_in_rcd_id
1125 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1126 p_object_version_number => p_rec.object_version_number);
1127 --
1128 chk_ext_rcd_id
1129 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1130 p_ext_rcd_id => p_rec.ext_rcd_id,
1131 p_object_version_number => p_rec.object_version_number);
1132 --
1133 chk_ext_data_elmt_id
1134 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1135 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1136 p_object_version_number => p_rec.object_version_number);
1137 --
1138 chk_sprs_cd
1139 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1140 p_ext_rcd_id => p_rec.ext_rcd_id,
1141 p_sprs_cd => p_rec.sprs_cd,
1142 p_effective_date => p_effective_date,
1143 p_business_group_id => p_rec.business_group_id,
1144 p_object_version_number => p_rec.object_version_number);
1145 --
1146 chk_rqd_flag
1147 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1148 p_rqd_flag => p_rec.rqd_flag,
1149 p_effective_date => p_effective_date,
1150 p_business_group_id => p_rec.business_group_id,
1151 p_object_version_number => p_rec.object_version_number);
1152 --
1153 chk_any_or_all_cd
1154 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1155 p_any_or_all_cd => p_rec.any_or_all_cd,
1156 p_effective_date => p_effective_date,
1157 p_business_group_id => p_rec.business_group_id,
1158 p_object_version_number => p_rec.object_version_number);
1159 --
1160 chk_hide_flag
1161 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1162 p_hide_flag => p_rec.hide_flag,
1163 p_effective_date => p_effective_date,
1164 p_business_group_id => p_rec.business_group_id,
1165 p_object_version_number => p_rec.object_version_number);
1166 --
1167 chk_seq_num
1168 (p_seq_num => p_rec.seq_num
1169 ,p_ext_rcd_id => p_rec.ext_rcd_id
1170 ,p_business_group_id => p_rec.business_group_id
1171 ,p_legislation_code => p_rec.legislation_code);
1172 --
1173 chk_seq_num_unq
1174 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1178 p_legislation_code => p_rec.legislation_code);
1175 p_seq_num => p_rec.seq_num,
1176 p_ext_rcd_id => p_rec.ext_rcd_id,
1177 p_business_group_id => p_rec.business_group_id,
1179 --
1180 chk_elmt_id(p_ext_data_elmt_id => p_rec.ext_data_elmt_id);
1181 --
1182 chk_strt_pos(p_strt_pos => p_rec.strt_pos);
1183 --
1184 chk_rcd_type
1185 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1186 p_ext_rcd_id => p_rec.ext_rcd_id,
1187 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1188 p_object_version_number => p_rec.object_version_number);
1189 --
1190 hr_utility.set_location(' Leaving:'||l_proc, 10);
1191 End insert_validate;
1192 --
1193 -- ----------------------------------------------------------------------------
1194 -- |---------------------------< update_validate >----------------------------|
1195 -- ----------------------------------------------------------------------------
1196 Procedure update_validate(p_rec in ben_xer_shd.g_rec_type
1197 ,p_effective_date in date) is
1198 --
1199 l_proc varchar2(72) := g_package||'update_validate';
1200 --
1201 Begin
1202 hr_utility.set_location('Entering:'||l_proc, 5);
1203 --
1204 -- Call all supporting business operations
1205 --
1206 --
1207 chk_startup_action(False
1208 ,p_rec.business_group_id
1209 ,p_rec.legislation_code);
1210 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1211 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1212 END IF;
1213 --
1214 chk_ext_data_elmt_in_rcd_id
1215 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1216 p_object_version_number => p_rec.object_version_number);
1217 --
1218 chk_ext_rcd_id
1219 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1220 p_ext_rcd_id => p_rec.ext_rcd_id,
1221 p_object_version_number => p_rec.object_version_number);
1222 --
1223 chk_ext_data_elmt_id
1224 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1225 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1226 p_object_version_number => p_rec.object_version_number);
1227 --
1228 chk_sprs_cd
1229 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1230 p_ext_rcd_id => p_rec.ext_rcd_id,
1231 p_sprs_cd => p_rec.sprs_cd,
1232 p_effective_date => p_effective_date,
1233 p_business_group_id => p_rec.business_group_id,
1234 p_object_version_number => p_rec.object_version_number);
1235 --
1236 chk_rqd_flag
1237 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1238 p_rqd_flag => p_rec.rqd_flag,
1239 p_effective_date => p_effective_date,
1240 p_business_group_id => p_rec.business_group_id,
1241 p_object_version_number => p_rec.object_version_number);
1242 --
1243 chk_rqd_flag
1244 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1245 p_rqd_flag => p_rec.rqd_flag,
1246 p_effective_date => p_effective_date,
1247 p_business_group_id => p_rec.business_group_id,
1248 p_object_version_number => p_rec.object_version_number);
1249 --
1250 chk_any_or_all_cd
1251 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1252 p_any_or_all_cd => p_rec.any_or_all_cd,
1253 p_effective_date => p_effective_date,
1254 p_business_group_id => p_rec.business_group_id,
1255 p_object_version_number => p_rec.object_version_number);
1256 --
1257 chk_seq_num
1258 (p_seq_num => p_rec.seq_num
1259 ,p_ext_rcd_id => p_rec.ext_rcd_id
1260 ,p_business_group_id => p_rec.business_group_id
1261 ,p_legislation_code => p_rec.legislation_code);
1262 --
1263 chk_seq_num_unq
1264 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1265 p_seq_num => p_rec.seq_num,
1266 p_ext_rcd_id => p_rec.ext_rcd_id,
1267 p_business_group_id => p_rec.business_group_id,
1268 p_legislation_code => p_rec.legislation_code);
1269 --
1270 chk_elmt_id(p_ext_data_elmt_id => p_rec.ext_data_elmt_id);
1271 --
1272 chk_strt_pos(p_strt_pos => p_rec.strt_pos);
1273 --
1274 chk_rcd_type
1275 (p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1276 p_ext_rcd_id => p_rec.ext_rcd_id,
1277 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1278 p_object_version_number => p_rec.object_version_number);
1279 --
1280 hr_utility.set_location(' Leaving:'||l_proc, 10);
1281 End update_validate;
1282 --
1283 -- ----------------------------------------------------------------------------
1284 -- |---------------------------< delete_validate >----------------------------|
1285 -- ----------------------------------------------------------------------------
1286 Procedure delete_validate(p_rec in ben_xer_shd.g_rec_type
1287 ,p_effective_date in date) is
1288 --
1289 l_proc varchar2(72) := g_package||'delete_validate';
1290 --
1291 Begin
1292 hr_utility.set_location('Entering:'||l_proc, 5);
1293 --
1294 -- Call all supporting business operations
1295 --
1296 --bug 2804169 -- check if child records exist
1297 chk_child_recs ( p_rec.ext_data_elmt_in_rcd_id);
1298 --
1299 chk_startup_action(False
1300 ,ben_xer_shd.g_old_rec.business_group_id
1301 ,ben_xer_shd.g_old_rec.legislation_code);
1302 --
1303 hr_utility.set_location(' Leaving:'||l_proc, 10);
1304 End delete_validate;
1305 --
1306 --
1307 -- ---------------------------------------------------------------------------
1308 -- |---------------------< return_legislation_code >-------------------------|
1309 -- ---------------------------------------------------------------------------
1310 --
1311 function return_legislation_code
1312 (p_ext_data_elmt_in_rcd_id in number) return varchar2 is
1313 --
1314 -- Declare cursor
1315 --
1316 cursor csr_leg_code is
1317 select a.legislation_code
1318 from per_business_groups a,
1319 ben_ext_data_elmt_in_rcd b
1320 where b.ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
1321 and a.business_group_id(+) = b.business_group_id;
1322 --
1323 -- Declare local variables
1324 --
1325 l_legislation_code varchar2(150);
1326 l_proc varchar2(72) := g_package||'return_legislation_code';
1327 --
1328 begin
1329 --
1330 hr_utility.set_location('Entering:'|| l_proc, 10);
1331 --
1332 -- Ensure that all the mandatory parameter are not null
1333 --
1334 hr_api.mandatory_arg_error(p_api_name => l_proc,
1335 p_argument => 'ext_data_elmt_in_rcd_id',
1336 p_argument_value => p_ext_data_elmt_in_rcd_id);
1337 --
1338 open csr_leg_code;
1339 --
1340 fetch csr_leg_code into l_legislation_code;
1341 --
1342 if csr_leg_code%notfound then
1343 --
1344 close csr_leg_code;
1345 --
1346 -- The primary key is invalid therefore we must error
1347 --
1348 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1349 fnd_message.raise_error;
1350 --
1351 end if;
1352 --
1353 close csr_leg_code;
1354 --
1355 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1356 --
1357 return l_legislation_code;
1358 --
1359 end return_legislation_code;
1360 --
1361 end ben_xer_bus;