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