[Home] [Help]
PACKAGE BODY: APPS.BEN_XRC_BUS
Source
1 Package Body ben_xrc_bus as
2 /* $Header: bexrcrhi.pkb 120.0 2005/05/28 12:37:58 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xrc_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 --
67 -- Declare cursor
63 Procedure set_security_group_id
64 (p_ext_rcd_id in number
65 ) is
66 --
68 --
69 cursor csr_sec_grp is
70 select pbg.security_group_id
71 from per_business_groups pbg
72 , ben_ext_rcd xrc
73 where xrc.ext_rcd_id = p_ext_rcd_id
74 and pbg.business_group_id = xrc.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_id'
90 ,p_argument_value => p_ext_rcd_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_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_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_id(p_ext_rcd_id in number,
146 p_object_version_number in number) is
147 --
148 l_proc varchar2(72) := g_package||'chk_ext_rcd_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_xrc_shd.api_updating
156 (p_ext_rcd_id => p_ext_rcd_id,
157 p_object_version_number => p_object_version_number);
158 --
159 if (l_api_updating
160 and nvl(p_ext_rcd_id,hr_api.g_number)
161 <> ben_xrc_shd.g_old_rec.ext_rcd_id) then
162 --
163 -- raise error as PK has changed
164 --
165 ben_xrc_shd.constraint_error('BEN_EXT_RCD_PK');
166 --
167 elsif not l_api_updating then
168 --
169 -- check if PK is null
170 --
171 if p_ext_rcd_id is not null then
172 --
173 -- raise error as PK is not null
174 --
175 ben_xrc_shd.constraint_error('BEN_EXT_RCD_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_id;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |------------------------< chk_name_unique >---------------------------------|
187 -- ----------------------------------------------------------------------------
188 --
189 -- Description
190 -- ensure that an extract record must have a name and not two extract record have the same name
191 --
192 -- Pre Conditions
193 -- None.
194 --
195 -- In Parameters
196 -- p_name is extract name
197 -- p_ext_rcd_id is extract record id
198 -- p_business_group_id
199 -- p_legislation_code
200 --
201 -- Post Success
202 -- Processing continues
203 --
204 -- Post Failure
205 -- Errors handled by the procedure
206 --
207 -- Access Status
208 -- Internal table handler use only.
209 --
210 -- ----------------------------------------------------------------------------
211 Procedure chk_name_unique
212 ( p_ext_rcd_id in number
213 ,p_name in varchar2
214 ,p_business_group_id in number
215 ,p_legislation_code in varchar2)
216 is
217 l_proc varchar2(72) := g_package||'chk_name_unique';
218 l_dummy char(1);
219 cursor c1 is select null
220 from ben_ext_rcd
221 Where ext_rcd_id <> nvl(p_ext_rcd_id,-1)
222 and name = p_name
223 and ((business_group_id is null and legislation_code is null)
224 or (legislation_code is not null
225 and business_group_id is null
226 and legislation_code = p_legislation_code)
227 or (business_group_id is not null
231 Begin
228 and business_group_id = p_business_group_id)
229 );
230 --
232 hr_utility.set_location('Entering:'||l_proc, 5);
233 --
234 if p_name is null then
235 fnd_message.set_name('BEN','BEN_91783_NAME_NULL');
236 fnd_message.raise_error;
237 end if;
238 open c1;
239 fetch c1 into l_dummy;
240 if c1%found then
241 close c1;
242 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
243 fnd_message.raise_error;
244 end if;
245 --
246 hr_utility.set_location('Leaving:'||l_proc, 15);
247 End chk_name_unique;
248 --
249 -- ----------------------------------------------------------------------------
250 -- |------< chk_rcd_type_cd >------|
251 -- ----------------------------------------------------------------------------
252 --
253 -- Description
254 -- This procedure is used to check that the lookup value is valid.
255 --
256 -- Pre Conditions
257 -- None.
258 --
259 -- In Parameters
260 -- ext_rcd_id PK of record being inserted or updated.
261 -- rcd_type_cd Value of lookup code.
262 -- effective_date effective date
263 -- object_version_number Object version number of record being
264 -- inserted or updated.
265 --
266 -- Post Success
267 -- Processing continues
268 --
269 -- Post Failure
270 -- Error handled by procedure
271 --
272 -- Access Status
273 -- Internal table handler use only.
274 --
275 Procedure chk_rcd_type_cd(p_ext_rcd_id in number,
276 p_rcd_type_cd in varchar2,
277 p_effective_date in date,
278 p_business_group_id in number,
279 p_object_version_number in number) is
280 --
281 l_proc varchar2(72) := g_package||'chk_rcd_type_cd';
282 l_api_updating boolean;
283 --
284 Begin
285 --
286 hr_utility.set_location('Entering:'||l_proc, 5);
287 --
288 l_api_updating := ben_xrc_shd.api_updating
289 (p_ext_rcd_id => p_ext_rcd_id,
290 p_object_version_number => p_object_version_number);
291 --
292 if (l_api_updating
293 and p_rcd_type_cd
294 <> nvl(ben_xrc_shd.g_old_rec.rcd_type_cd,hr_api.g_varchar2)
295 or not l_api_updating)
296 and p_rcd_type_cd is not null then
297 --
298 -- check if value of lookup falls within lookup type.
299 --
300 if p_business_group_id is not null then
301 /* BG is set, so use the existing call, with no modifications*/
302 if hr_api.not_exists_in_hr_lookups
303 (p_lookup_type => 'BEN_EXT_RCD_TYP',
304 p_lookup_code => p_rcd_type_cd,
305 p_effective_date => p_effective_date) then
306 --
307 -- raise error as does not exist as lookup
308 --
309 --
310 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
311 fnd_message.set_token('FIELD','p_rcd_type_cd');
312 fnd_message.set_token('TYPE','BEN_EXT_RCD_TYP');
313 fnd_message.raise_error;
314 --
315 end if;
316 else
317 /* BG is null, so alternative call is required */
318 if hr_api.not_exists_in_hrstanlookups
319 (p_lookup_type => 'BEN_EXT_RCD_TYP',
320 p_lookup_code => p_rcd_type_cd,
321 p_effective_date => p_effective_date) then
322 --
323 -- raise error as does not exist as lookup
324 --
325 --
326 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
327 fnd_message.set_token('FIELD','p_rcd_type_cd');
328 fnd_message.set_token('TYPE','BEN_EXT_RCD_TYP');
329 fnd_message.raise_error;
330 --
331 end if;
332 end if;
333 --
334 end if; /* if (l_api_updating... */
335 --
336 hr_utility.set_location('Leaving:'||l_proc,10);
337 --
338 end chk_rcd_type_cd;
339 --
340 -- ----------------------------------------------------------------------------
341 -- |------< chk_low_lvl_cd >------|
342 -- ----------------------------------------------------------------------------
343 --
344 -- Description
345 -- This procedure is used to check that the lookup value is valid.
346 --
347 -- Pre Conditions
348 -- None.
349 --
350 -- In Parameters
351 -- ext_rcd_id PK of record being inserted or updated.
352 -- low_lvl_cd Value of lookup code.
353 -- effective_date effective date
354 -- object_version_number Object version number of record being
355 -- inserted or updated.
356 --
357 -- Post Success
358 -- Processing continues
359 --
360 -- Post Failure
361 -- Error handled by procedure
362 --
363 -- Access Status
364 -- Internal table handler use only.
365 --
366 Procedure chk_low_lvl_cd(p_ext_rcd_id in number,
367 p_low_lvl_cd in varchar2,
368 p_effective_date in date,
369 p_business_group_id in number,
370 p_object_version_number in number) is
371 --
372 l_proc varchar2(72) := g_package||'chk_low_lvl_cd';
373 l_api_updating boolean;
374 --
375 Begin
376 --
380 (p_ext_rcd_id => p_ext_rcd_id,
377 hr_utility.set_location('Entering:'||l_proc, 5);
378 --
379 l_api_updating := ben_xrc_shd.api_updating
381 p_object_version_number => p_object_version_number);
382 --
383 if (l_api_updating
384 and p_low_lvl_cd
385 <> nvl(ben_xrc_shd.g_old_rec.low_lvl_cd,hr_api.g_varchar2)
386 or not l_api_updating)
387 and p_low_lvl_cd is not null then
388 --
389 -- check if value of lookup falls within lookup type.
390 --
391 if p_business_group_id is not null then
392 /* BG is set, so use the existing call, with no modifications*/
393 if hr_api.not_exists_in_hr_lookups
394 (p_lookup_type => 'BEN_EXT_LVL',
395 p_lookup_code => p_low_lvl_cd,
396 p_effective_date => p_effective_date) then
397 --
398 -- raise error as does not exist as lookup
399 --
400 --
401 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
402 fnd_message.set_token('FIELD','p_low_lvl_cd');
403 fnd_message.set_token('TYPE','BEN_EXT_LVL');
404 fnd_message.raise_error;
405 --
406 end if;
407 else
408 /* BG is null, so alternative call is required */
409 if hr_api.not_exists_in_hrstanlookups
410 (p_lookup_type => 'BEN_EXT_LVL',
411 p_lookup_code => p_low_lvl_cd,
412 p_effective_date => p_effective_date) then
413 --
414 -- raise error as does not exist as lookup
415 --
416 --
417 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
418 fnd_message.set_token('FIELD','p_low_lvl_cd');
419 fnd_message.set_token('TYPE','BEN_EXT_LVL');
420 fnd_message.raise_error;
421 --
422 end if;
423 end if;
424 --
425 end if; /* if (l_api_updating... */
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 --
429 end chk_low_lvl_cd;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |------< chk_rcd_data_typ >------|
433 -- ----------------------------------------------------------------------------
434 --
435 -- Description
436 -- This procedure is used to check that the record type is consistent with
437 -- the data element type.
438 --
439 -- Pre Conditions
440 -- None.
441 --
442 -- In Parameters
443 -- ext_rcd_id PK of record being inserted or updated.
444 -- rcd_type_cd Value of record type.
445 -- object_version_number Object version number of record being
446 -- inserted or updated.
447 --
448 -- Post Success
449 -- Processing continues
450 --
451 -- Post Failure
452 -- Error handled by procedure
453 --
454 -- Access Status
455 -- Internal table handler use only.
456 --
457 Procedure chk_rcd_data_typ(p_ext_rcd_id in number,
458 p_rcd_type_cd in varchar2,
459 p_object_version_number in number) is
460 --
461 l_proc varchar2(72) := g_package||'chk_rcd_data_typ';
462 l_api_updating boolean;
463
464 cursor c1 is select data_elmt_typ_cd, alwd_in_rcd_cd
465 from ben_ext_data_elmt a, ben_ext_fld b, ben_ext_data_elmt_in_rcd c
466 where a.ext_fld_id = b.ext_fld_id
467 and a.ext_data_elmt_id = c.ext_data_elmt_id
468 and c.ext_rcd_id = p_ext_rcd_id;
469 --
470 Begin
471 --
472 hr_utility.set_location('Entering:'||l_proc, 5);
473 --
474 l_api_updating := ben_xrc_shd.api_updating
475 (p_ext_rcd_id => p_ext_rcd_id,
476 p_object_version_number => p_object_version_number);
477 --
478 if (l_api_updating
479 and p_rcd_type_cd
480 <> nvl(ben_xrc_shd.g_old_rec.rcd_type_cd,hr_api.g_varchar2)
481 or not l_api_updating)
482 and p_rcd_type_cd is not null then
483 --
484 -- check if value of lookup falls within lookup type.
485 for r1 in c1
486 loop
487 if r1.data_elmt_typ_cd in ('D','F') then
488 if r1.alwd_in_rcd_cd in ('D','H','T') then
489 if p_rcd_type_cd <> r1.alwd_in_rcd_cd then
490 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
491 fnd_message.raise_error;
492 end if;
493 elsif r1.alwd_in_rcd_cd = 'B' then
494 if p_rcd_type_cd not in ('H','T','L') then
495 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
496 fnd_message.raise_error;
497 end if;
498 end if;
499 elsif r1.data_elmt_typ_cd = 'R' then
500 if p_rcd_type_cd <> 'D' then
501 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
502 fnd_message.raise_error;
503 end if;
504 elsif r1.data_elmt_typ_cd = 'T' then
505 if p_rcd_type_cd not in ('H','T', 'L') then
506 fnd_message.set_name('BEN','BEN_92198_CHK_RCD_TYP');
507 fnd_message.raise_error;
508 end if;
509 end if;
510 end loop;
511 --
512 --
513 end if;
514 --
515 hr_utility.set_location('Leaving:'||l_proc,10);
516 --
517 end chk_rcd_data_typ;
518 -- ----------------------------------------------------------------------------
519 -- |---------------------------< insert_validate >----------------------------|
520 -- ----------------------------------------------------------------------------
524 l_proc varchar2(72) := g_package||'insert_validate';
521 Procedure insert_validate(p_rec in ben_xrc_shd.g_rec_type
522 ,p_effective_date in date) is
523 --
525 --
526 Begin
527 hr_utility.set_location('Entering:'||l_proc, 5);
528 --
529 -- Call all supporting business operations
530 --
531 --
532 chk_startup_action(True
533 ,p_rec.business_group_id
534 ,p_rec.legislation_code);
535 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
536 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
537 END IF;
538 --
539 chk_ext_rcd_id
540 (p_ext_rcd_id => p_rec.ext_rcd_id,
541 p_object_version_number => p_rec.object_version_number);
542 --
543 chk_name_unique
544 (p_ext_rcd_id => p_rec.ext_rcd_id,
545 p_name => p_rec.name,
546 p_business_group_id => p_rec.business_group_id,
547 p_legislation_code => p_rec.legislation_code);
548
549 chk_rcd_type_cd
550 (p_ext_rcd_id => p_rec.ext_rcd_id,
551 p_rcd_type_cd => p_rec.rcd_type_cd,
552 p_effective_date => p_effective_date,
553 p_business_group_id => p_rec.business_group_id,
554 p_object_version_number => p_rec.object_version_number);
555 --
556 chk_low_lvl_cd
557 (p_ext_rcd_id => p_rec.ext_rcd_id,
558 p_low_lvl_cd => p_rec.low_lvl_cd,
559 p_effective_date => p_effective_date,
560 p_business_group_id => p_rec.business_group_id,
561 p_object_version_number => p_rec.object_version_number);
562 --
563 hr_utility.set_location(' Leaving:'||l_proc, 10);
564 End insert_validate;
565 --
566 -- ----------------------------------------------------------------------------
567 -- |---------------------------< update_validate >----------------------------|
568 -- ----------------------------------------------------------------------------
569 Procedure update_validate(p_rec in ben_xrc_shd.g_rec_type
570 ,p_effective_date in date) is
571 --
572 l_proc varchar2(72) := g_package||'update_validate';
573 --
574 Begin
575 hr_utility.set_location('Entering:'||l_proc, 5);
576 --
577 -- Call all supporting business operations
578 --
579 --
580 chk_startup_action(False
581 ,p_rec.business_group_id
582 ,p_rec.legislation_code);
583 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
584 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
585 END IF;
586 --
587 chk_ext_rcd_id
588 (p_ext_rcd_id => p_rec.ext_rcd_id,
589 p_object_version_number => p_rec.object_version_number);
590 --
591 chk_name_unique
592 (p_ext_rcd_id => p_rec.ext_rcd_id,
593 p_name => p_rec.name,
594 p_business_group_id => p_rec.business_group_id,
595 p_legislation_code => p_rec.legislation_code);
596
597 chk_rcd_type_cd
598 (p_ext_rcd_id => p_rec.ext_rcd_id,
599 p_rcd_type_cd => p_rec.rcd_type_cd,
600 p_effective_date => p_effective_date,
601 p_business_group_id => p_rec.business_group_id,
602 p_object_version_number => p_rec.object_version_number);
603 --
604 chk_low_lvl_cd
605 (p_ext_rcd_id => p_rec.ext_rcd_id,
606 p_low_lvl_cd => p_rec.low_lvl_cd,
607 p_effective_date => p_effective_date,
608 p_business_group_id => p_rec.business_group_id,
609 p_object_version_number => p_rec.object_version_number);
610 --
611 chk_rcd_data_typ
612 (p_ext_rcd_id => p_rec.ext_rcd_id,
613 p_rcd_type_cd => p_rec.rcd_type_cd,
614 p_object_version_number => p_rec.object_version_number);
615 --
616 hr_utility.set_location(' Leaving:'||l_proc, 10);
617 End update_validate;
618 --
619 -- ----------------------------------------------------------------------------
620 -- |---------------------------< delete_validate >----------------------------|
621 -- ----------------------------------------------------------------------------
622 Procedure delete_validate(p_rec in ben_xrc_shd.g_rec_type
623 ,p_effective_date in date) is
624 --
625 l_proc varchar2(72) := g_package||'delete_validate';
626 --
627 Begin
628 hr_utility.set_location('Entering:'||l_proc, 5);
629 --
630 -- Call all supporting business operations
631 --
632 chk_startup_action(False
633 ,ben_xrc_shd.g_old_rec.business_group_id
634 ,ben_xrc_shd.g_old_rec.legislation_code);
635 --
636 hr_utility.set_location(' Leaving:'||l_proc, 10);
637 End delete_validate;
638 --
639 --
640 -- ---------------------------------------------------------------------------
641 -- |---------------------< return_legislation_code >-------------------------|
642 -- ---------------------------------------------------------------------------
643 --
644 function return_legislation_code
645 (p_ext_rcd_id in number) return varchar2 is
646 --
647 -- Declare cursor
648 --
649 cursor csr_leg_code is
650 select a.legislation_code
651 from per_business_groups a,
652 ben_ext_rcd b
653 where b.ext_rcd_id = p_ext_rcd_id
654 and a.business_group_id(+) = b.business_group_id;
655 --
656 -- Declare local variables
657 --
658 l_legislation_code per_business_groups.legislation_code%type ;
659 l_proc varchar2(72) := g_package||'return_legislation_code';
660 --
661 begin
662 --
663 hr_utility.set_location('Entering:'|| l_proc, 10);
667 hr_api.mandatory_arg_error(p_api_name => l_proc,
664 --
665 -- Ensure that all the mandatory parameter are not null
666 --
668 p_argument => 'ext_rcd_id',
669 p_argument_value => p_ext_rcd_id);
670 --
671 open csr_leg_code;
672 --
673 fetch csr_leg_code into l_legislation_code;
674 --
675 if csr_leg_code%notfound then
676 --
677 close csr_leg_code;
678 --
679 -- The primary key is invalid therefore we must error
680 --
681 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
682 fnd_message.raise_error;
683 --
684 end if;
685 --
686 close csr_leg_code;
687 --
688 hr_utility.set_location(' Leaving:'|| l_proc, 20);
689 --
690 return l_legislation_code;
691 --
692 end return_legislation_code;
693 --
694 end ben_xrc_bus;