[Home] [Help]
PACKAGE BODY: APPS.BEN_OPT_BUS
Source
1 Package Body ben_opt_bus as
2 /* $Header: beoptrhi.pkb 120.0 2005/05/28 09:56:38 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_opt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_opt_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- opt_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_opt_id(p_opt_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_opt_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_opt_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_opt_id => p_opt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_opt_id,hr_api.g_number)
55 <> ben_opt_shd.g_old_rec.opt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_opt_shd.constraint_error('BEN_OPT_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_opt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_opt_shd.constraint_error('BEN_OPT_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_opt_id;
78
79
80
81 Procedure chk_opt_group_child(p_opt_id in number ,
82 p_name in varchar2, /* Bug 4057566 */
83 p_opt_typ_cd in varchar2 ,
84 p_effective_date in date) is
85
86
87
88 cursor c_opt_cwb is
89 select opt_typ_cd
90 from ben_pl_typ_f plt,
91 ben_pl_typ_opt_typ_f pto
92 where pto.opt_id = p_opt_id
93 and plt.pl_typ_id = pto.pl_typ_id
94 and p_effective_date between plt.effective_start_date
95 and plt.effective_end_Date
96 and p_effective_date between pto.effective_start_date
97 and pto.effective_end_Date ;
98 --
99 cursor c_child_exist is
100 select 'x'
101 from ben_opt_f
102 where group_opt_id = p_opt_id
103 and opt_id <> p_opt_id
104 and effective_end_date > p_effective_date
105 ;
106 -- dont validate the date
107 l_dummy varchar2(1) ;
108 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%type ;
109 l_proc varchar2(72) := g_package||'chk_opt_group_child';
110
111 Begin
112
113 hr_utility.set_location('Entering:'||l_proc, 5);
114
115 l_opt_typ_cd := p_opt_typ_cd ;
116 if p_opt_typ_cd is null then
117 open c_opt_cwb ;
118 fetch c_opt_cwb into l_opt_typ_cd ;
119 close c_opt_cwb ;
120 end if ;
121 if l_opt_typ_cd = 'CWB' then
122 open c_child_exist ;
123 fetch c_child_exist into l_dummy ;
124 if c_child_exist%found then
125 close c_child_exist ;
126 fnd_message.set_name('BEN','BEN_93724_CWB_CHILD_EXIST');
127 fnd_message.set_name('NAME', p_name); /* Bug 4057566 */
128 fnd_message.raise_error;
129 end if ;
130 close c_child_exist ;
131 end if ;
132 --
136 end chk_opt_group_child ;
133 hr_utility.set_location('Leaving:'||l_proc, 10);
134 --
135
137
138
139
140
141 Procedure chk_opt_group_id(p_opt_id in number,
142 p_group_opt_id in number,
143 p_effective_date in date,
144 p_name in varchar2 default null
145 ) is
146 --
147 l_proc varchar2(72) := g_package||'chk_opt_group_id';
148 l_api_updating boolean;
149 --
150 cursor c_parent_opt is
151 select 'x'
152 from ben_opt_f
153 where opt_id = p_group_opt_id
154 and opt_id = group_opt_id
155 and p_effective_date between effective_start_date
156 and effective_end_Date ;
157
158
159 cursor c_opt_cwb is
160 select opt_typ_cd
161 from ben_pl_typ_opt_typ_f pto ,
162 ben_pl_typ_f plt
163 where pto.opt_id = p_opt_id
164 and plt.pl_typ_id = pto.pl_typ_id
165 and plt.opt_typ_cd = 'CWB'
166 and p_effective_date between plt.effective_start_date
167 and plt.effective_end_Date
168 and p_effective_date between pto.effective_start_date
169 and pto.effective_end_Date ;
170
171
172
173 l_dummy varchar2(1) ;
174 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%type ;
175
176 --
177 Begin
178 --
179 hr_utility.set_location('Entering:'||l_proc, 5);
180 hr_utility.set_location('p_group_opt_id:'||p_group_opt_id, 5);
181
182 -- if the plan type is cwb and group_pl_id null throw the error
183 /* removed the validation as per ty
184 open c_opt_cwb ;
185 fetch c_opt_cwb into l_opt_typ_cd ;
186 if c_opt_cwb%found then
187 if p_group_opt_id is null then
188 close c_opt_cwb ;
189 fnd_message.set_name('BEN','BEN_93725_CWB_GROUP_PLN_NULL');
190 fnd_message.raise_error;
191 end if ;
192 end if ;
193 close c_opt_cwb ;
194 */
195
196 if p_group_opt_id is not null then
197
198 /*
199 --check whether the option belongs to CWB if not throw the error
200 open c_opt_cwb ;
201 fetch c_opt_cwb into l_opt_typ_cd ;
202 if c_opt_cwb%notfound then
203 close c_opt_cwb ;
204 fnd_message.set_name('BEN','BEN_93725_CWB_GROUP_PLN_NULL');
205 fnd_message.raise_error;
206 end if ;
207 close c_opt_cwb ;
208 */
209
210 -- when the plan is child check the parent is real parent
211 if p_opt_id <> p_group_opt_id then
212 open c_parent_opt ;
213 fetch c_parent_opt into l_dummy ;
214 if c_parent_opt%notfound then
215 close c_parent_opt ;
216 fnd_message.set_name('BEN','BEN_93726_CWB_PRTN_PLN_ERROR');
217 fnd_message.raise_error;
218 end if ;
219 close c_parent_opt ;
220
221 chk_opt_group_child(p_opt_id => p_opt_id ,
222 p_name => p_name, /* Bug 4057566 */
223 p_opt_typ_cd => l_opt_typ_cd ,
224 p_effective_date => p_effective_date) ;
225 end if ;
226
227 end if ;
228 -- if the type got changed from cwb to non cwb validate the child
229 if ben_opt_shd.g_old_rec.group_opt_id is not null and p_group_opt_id is null then
230 chk_opt_group_child(p_opt_id => p_opt_id ,
231 p_name => p_name, /* Bug 4057566 */
232 p_opt_typ_cd => 'CWB' ,
233 p_effective_date => p_effective_date) ;
234 end if ;
235 --
236 hr_utility.set_location('Leaving:'||l_proc, 10);
237 --
238 End chk_opt_group_id;
239
240
241 --
242 -- ----------------------------------------------------------------------------
243 -- |------< chk_invk_wv_opt_flag >------|
244 -- ----------------------------------------------------------------------------
245 --
246 -- Description
247 -- This procedure is used to check that the lookup value is valid.
248 --
249 -- Pre Conditions
250 -- None.
251 --
252 -- In Parameters
253 -- opt_id PK of record being inserted or updated.
254 -- invk_wv_opt_flag Value of lookup code.
255 -- effective_date effective date
256 -- object_version_number Object version number of record being
257 -- inserted or updated.
258 --
259 -- Post Success
260 -- Processing continues
261 --
262 -- Post Failure
263 -- Error handled by procedure
264 --
265 -- Access Status
266 -- Internal table handler use only.
267 --
268 Procedure chk_invk_wv_opt_flag(p_opt_id in number,
269 p_invk_wv_opt_flag in varchar2,
270 p_effective_date in date,
271 p_object_version_number in number) is
272 --
273 l_proc varchar2(72) := g_package||'chk_invk_wv_opt_flag';
274 l_api_updating boolean;
275 --
276 Begin
277 --
278 hr_utility.set_location('Entering:'||l_proc, 5);
279 --
280 l_api_updating := ben_opt_shd.api_updating
281 (p_opt_id => p_opt_id,
282 p_effective_date => p_effective_date,
283 p_object_version_number => p_object_version_number);
284 --
285 if (l_api_updating
286 and p_invk_wv_opt_flag
287 <> nvl(ben_opt_shd.g_old_rec.invk_wv_opt_flag,hr_api.g_varchar2)
291 -- check if value of lookup falls within lookup type.
288 or not l_api_updating)
289 and p_invk_wv_opt_flag is not null then
290 --
292 --
293 if hr_api.not_exists_in_hr_lookups
294 (p_lookup_type => 'YES_NO',
295 p_lookup_code => p_invk_wv_opt_flag,
296 p_effective_date => p_effective_date) then
297 --
298 -- raise error as does not exist as lookup
299 --
300 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
301 hr_utility.raise_error;
302 --
303 end if;
304 --
305 end if;
306 --
307 hr_utility.set_location('Leaving:'||l_proc,10);
308 --
309 end chk_invk_wv_opt_flag;
310 --
311 -- ----------------------------------------------------------------------------
312 -- |------< chk_rqd_perd_enrt_nenrt_rl >------|
313 -- ----------------------------------------------------------------------------
314 --
315 -- Description
316 -- This procedure is used to check that the Formula Rule is valid.
317 --
318 -- Pre Conditions
319 -- None.
320 --
321 -- In Parameters
322 -- opt_id PK of record being inserted or updated.
323 -- rqd_perd_enrt_nenrt_rl Value of formula rule id.
324 -- effective_date effective date
325 -- object_version_number Object version number of record being
326 -- inserted or updated.
327 --
328 -- Post Success
329 -- Processing continues
330 --
331 -- Post Failure
332 -- Error handled by procedure
333 --
334 -- Access Status
335 -- Internal table handler use only.
336 --
337 Procedure chk_rqd_perd_enrt_nenrt_rl(p_opt_id in number,
338 p_rqd_perd_enrt_nenrt_rl in number,
339 p_effective_date in date,
340 p_object_version_number in number,
341 p_business_group_id in number) is
342 --
343 l_proc varchar2(72) := g_package||'chk_rqd_perd_enrt_nenrt_rl';
344 l_api_updating boolean;
345 l_dummy varchar2(1);
346 --
347 cursor c1 is
348 select null
349 from ff_formulas_f ff,
350 per_business_groups pbg
351 where ff.formula_id = p_rqd_perd_enrt_nenrt_rl
352 and ff.formula_type_id = -513
353 and pbg.business_group_id = p_business_group_id
354 and nvl(ff.business_group_id,p_business_group_id) =
355 p_business_group_id
356 and nvl(ff.legislation_code,pbg.legislation_code) =
357 pbg.legislation_code
358 and p_effective_date
359 between ff.effective_start_date
360 and ff.effective_end_date;
361 --
362 Begin
363 --
364 hr_utility.set_location('Entering:'||l_proc, 5);
365 --
366 l_api_updating := ben_opt_shd.api_updating
367 (p_opt_id => p_opt_id,
368 p_effective_date => p_effective_date,
369 p_object_version_number => p_object_version_number);
370 --
371 if (l_api_updating
372 and nvl(p_rqd_perd_enrt_nenrt_rl,hr_api.g_number)
373 <> nvl(ben_opt_shd.g_old_rec.rqd_perd_enrt_nenrt_rl,hr_api.g_number)
374 or not l_api_updating)
375 and p_rqd_perd_enrt_nenrt_rl is not null then
376 --
377 -- check if value of formula rule is valid.
378 --
379 open c1;
380 --
381 -- fetch value from cursor if it returns a record then the
382 -- formula is valid otherwise its invalid
383 --
384 fetch c1 into l_dummy;
385 if c1%notfound then
386 --
387 close c1;
388 --
389 -- raise error
390 --
391 hr_utility.set_message(801,'FORMULA_DOES_NOT_EXIST');
392 hr_utility.raise_error;
393 --
394 end if;
395 --
396 close c1;
397 --
398 end if;
399 --
400 hr_utility.set_location('Leaving:'||l_proc,10);
401 --
402 end chk_rqd_perd_enrt_nenrt_rl;
403 --
404 -- ----------------------------------------------------------------------------
405 -- |------< chk_rqd_perd_enrt_nenrt_uom >------|
406 -- ----------------------------------------------------------------------------
407 --
408 -- Description
409 -- This procedure is used to check that the lookup value is valid.
410 --
411 -- Pre Conditions
412 -- None.
413 --
414 -- In Parameters
415 -- opt_id PK of record being inserted or updated.
416 -- rqd_perd_enrt_nenrt_uom Value of lookup code.
417 -- effective_date effective date
418 -- object_version_number Object version number of record being
419 -- inserted or updated.
420 --
421 -- Post Success
422 -- Processing continues
423 --
424 -- Post Failure
425 -- Error handled by procedure
426 --
427 -- Access Status
428 -- Internal table handler use only.
429 --
430 Procedure chk_rqd_perd_enrt_nenrt_uom(p_opt_id in number,
431 p_rqd_perd_enrt_nenrt_uom in varchar2,
432 p_effective_date in date,
433 p_object_version_number in number) is
434 --
435 l_proc varchar2(72) := g_package||'chk_rqd_perd_enrt_nenrt_uom';
436 l_api_updating boolean;
437 --
438 Begin
439 --
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 l_api_updating := ben_opt_shd.api_updating
443 (p_opt_id => p_opt_id,
444 p_effective_date => p_effective_date,
448 and p_rqd_perd_enrt_nenrt_uom
445 p_object_version_number => p_object_version_number);
446 --
447 if (l_api_updating
449 <> nvl(ben_opt_shd.g_old_rec.rqd_perd_enrt_nenrt_uom,hr_api.g_varchar2)
450 or not l_api_updating)
451 and p_rqd_perd_enrt_nenrt_uom is not null then
452 --
453 -- check if value of lookup falls within lookup type.
454 --
455 if hr_api.not_exists_in_hr_lookups
456 (p_lookup_type => 'BEN_RQD_PERD_ENRT_NENRT_TM_UOM',
457 p_lookup_code => p_rqd_perd_enrt_nenrt_uom,
458 p_effective_date => p_effective_date) then
459 --
460 -- raise error as does not exist as lookup
461 --
462 fnd_message.set_name('BEN','BEN_91230_INV_RQD_PRD_ENRT_UOM');
463 fnd_message.raise_error;
464 --
465 end if;
466 --
467 end if;
468 --
469 hr_utility.set_location('Leaving:'||l_proc,10);
470 --
471 end chk_rqd_perd_enrt_nenrt_uom;
472 --
473 -- ----------------------------------------------------------------------------
474 -- |------< chk_name >------|
475 -- ----------------------------------------------------------------------------
476 --
477 -- Description
478 -- This procedure is used to check that the name fiels is unique within busine
479 -- :ss group
480 -- on insert and update.
481 --
482 -- Pre Conditions
483 -- None.
484 --
485 -- In Parameters
486 -- opt_id PK of record being inserted or updated
487 -- name for the record beeing inserted or updated
488 -- business_group_id of the record beeing inserted or updated
489 -- effective_date effective date of the session
490 -- object_version_number Object version number of record being
491 -- inserted or updated.
492 --
493 -- Post Success
494 -- Processing continues
495 --
496 -- Post Failure
497 -- Error handled by procedure
498 --
499 -- Access Status
500 -- Internal table handler use only.
501 --
502 Procedure chk_name(p_name in varchar2,
503 p_opt_id in number,
504 p_effective_date in date,
505 p_validation_start_date in date,
506 p_validation_end_date in date,
507 p_business_group_id in number) is
508 --
509 l_proc varchar2(72) := g_package||'chk_name';
510 l_api_updating boolean;
511 l_exists varchar2(1);
512 --
513 --
514 cursor csr_name is
515 select null
516 from ben_opt_f
517 where name = p_name
518 and business_group_id = p_business_group_id
519 and opt_id <> nvl(p_opt_id,-1)
520 and p_validation_start_date <= effective_end_date
521 and p_validation_end_date >= effective_start_date;
522 --
523 Begin
524 --
525 hr_utility.set_location('Entering:'||l_proc, 5);
526 --
527 -- check if this name already exist
528 --
529 open csr_name;
530 fetch csr_name into l_exists;
531 if csr_name%found then
532 --
533 -- raise error as UK1 is violated
534 --
535 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
536 fnd_message.raise_error;
537 --ben_opt_shd.constraint_error('BEN_OPT_UK1');
538 --
539 end if;
540 --
541 close csr_name;
542 --
543 --
544 hr_utility.set_location('Leaving:'||l_proc, 20);
545 --
546 End chk_name;
547 --
548
549 -- ----------------------------------------------------------------------------
550 -- |------< chk_comp_reason >------|
551 -- ----------------------------------------------------------------------------
552 --
553 -- Description
554 -- This procedure is used to check that the lookup value is valid.
555 --
556 -- Pre Conditions
557 -- None.
558 --
559 -- In Parameters
560 -- opt_id PK of record being inserted or updated.
561 -- component_reason Value of lookup code.
562 -- effective_date effective date
563 -- object_version_number Object version number of record being
564 -- inserted or updated.
565 --
566 -- Post Success
567 -- Processing continues
568 --
569 -- Post Failure
570 -- Error handled by procedure
571 --
572 -- Access Status
573 -- Internal table handler use only.
574 --
575 Procedure chk_comp_reason (p_opt_id in number,
576 p_component_reason in varchar2,
577 p_effective_date in date,
578 p_object_version_number in number) is
579 --
580 l_proc varchar2(72) := g_package||'chk_comp_reason';
581 l_api_updating boolean;
582 --
583 Begin
584 --
585 hr_utility.set_location('Entering:'||l_proc, 5);
586 --
587 l_api_updating := ben_opt_shd.api_updating
588 (p_opt_id => p_opt_id,
589 p_effective_date => p_effective_date,
590 p_object_version_number => p_object_version_number);
591 --
592 if (l_api_updating
593 and p_component_reason
594 <> nvl(ben_opt_shd.g_old_rec.component_reason,hr_api.g_varchar2)
595 or not l_api_updating)
596 and p_component_reason is not null then
597 --
598 -- check if value of lookup falls within lookup type.
599 --
600 if hr_api.not_exists_in_hr_lookups
601 (p_lookup_type => 'PROPOSAL_REASON',
602 p_lookup_code => p_component_reason,
603 p_effective_date => p_effective_date) then
604 --
605 -- raise error as does not exist as lookup
606 --
607 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
608 fnd_message.raise_error;
609 --
610 end if;
611 --
612 end if;
613 --
614 hr_utility.set_location('Leaving:'||l_proc,10);
615 --
616 end chk_comp_reason;
617 --
618 -- ----------------------------------------------------------------------------
619 -- |------< chk_mapping_table_name >------|
620 -- ----------------------------------------------------------------------------
621 --
622 -- Description
623 -- This procedure is used to check that the lookup value is valid.
624 --
625 -- Pre Conditions
626 -- None.
627 --
628 -- In Parameters
629 -- opt_id PK of record being inserted or updated.
630 -- mapping_table_name Value of lookup code.
631 -- effective_date effective date
632 -- object_version_number Object version number of record being
633 -- inserted or updated.
634 --
635 -- Post Success
636 -- Processing continues
637 --
638 -- Post Failure
639 -- Error handled by procedure
640 --
641 -- Access Status
642 -- Internal table handler use only.
643 --
644 Procedure chk_mapping_table_name(p_opt_id in number,
645 p_mapping_table_name in varchar2,
646 p_effective_date in date,
647 p_object_version_number in number) is
648 --
649 l_proc varchar2(72) := g_package||'chk_mapping_table_name';
650 l_api_updating boolean;
651 --
652 Begin
653 --
654 hr_utility.set_location('Entering:'||l_proc, 5);
655 --
656 l_api_updating := ben_opt_shd.api_updating
657 (p_opt_id => p_opt_id,
658 p_effective_date => p_effective_date,
659 p_object_version_number => p_object_version_number);
660 --
661 if (l_api_updating
662 and p_mapping_table_name
663 <> nvl(ben_opt_shd.g_old_rec.mapping_table_name,hr_api.g_varchar2)
664 or not l_api_updating)
665 and p_mapping_table_name is not null then
666 --
667 -- check if value of lookup falls within lookup type.
668 --
669 if hr_api.not_exists_in_hr_lookups
670 (p_lookup_type => 'BEN_MAPPING_OPTION_TABLE',
671 p_lookup_code => p_mapping_table_name,
672 p_effective_date => p_effective_date) then
673 --
674 -- raise error as does not exist as lookup
675 --
676 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
677 fnd_message.set_token('FIELD', 'p_mapping_table_name');
678 fnd_message.set_token('VALUE', p_mapping_table_name);
679 fnd_message.set_token('TYPE', 'BEN_MAPPING_OPTION_TABLE');
680 fnd_message.raise_error;
681 --
682 end if;
683 --
684 end if;
685 --
686 hr_utility.set_location('Leaving:'||l_proc,10);
687 --
688 end chk_mapping_table_name;
689 --
690 -- ----------------------------------------------------------------------------
691 -- |------< chk_mapping_table_pk_id >------|
692 -- ----------------------------------------------------------------------------
693 --
694 -- Description
695 -- This procedure is used to check that the Mapping Table Primary Key Id is valid.
696 --
697 -- Pre Conditions
698 -- None.
699 --
700 -- In Parameters
701 -- opt_id PK of record being inserted or updated.
702 -- mapping_table_pk_id Value of primary key id of mapping table.
703 -- effective_date effective date
704 -- object_version_number Object version number of record being
705 -- inserted or updated.
706 --
707 -- Post Success
708 -- Processing continues
709 --
710 -- Post Failure
711 -- Error handled by procedure
712 --
713 -- Access Status
714 -- Internal table handler use only.
715 --
716 Procedure chk_mapping_table_pk_id(p_opt_id in number,
717 p_mapping_table_pk_id in number,
718 p_mapping_table_name in varchar2,
719 p_effective_date in date,
720 p_object_version_number in number,
721 p_business_group_id in number) is
722 --
723 l_proc varchar2(72) := g_package||'chk_mapping_table_pk_id';
724 l_api_updating boolean;
725 l_dummy varchar2(1);
726 --
727 cursor c_per_spinal_points is
728 select null
729 from per_spinal_points spt
730 where spt.spinal_point_id = p_mapping_table_pk_id
731 and spt.business_group_id = p_business_group_id;
732 --
733 Begin
734 --
735 hr_utility.set_location('Entering:'||l_proc, 5);
736 --
737 l_api_updating := ben_opt_shd.api_updating
738 (p_opt_id => p_opt_id,
739 p_effective_date => p_effective_date,
740 p_object_version_number => p_object_version_number);
741 --
742 if (l_api_updating
743 and nvl(p_mapping_table_pk_id,hr_api.g_number)
744 <> nvl(ben_opt_shd.g_old_rec.mapping_table_pk_id,hr_api.g_number)
745 or not l_api_updating)
746 and p_mapping_table_pk_id is not null then
747 --
748 -- check if value of Mapping Table Primary Key Id is valid.
749 --
750
751 if p_mapping_table_name = 'PER_SPINAL_POINTS' then
752 open c_per_spinal_points;
753 --
754 -- fetch value from cursor if it returns a record then the
755 -- mapping_table_pk_id is valid otherwise its invalid
756 --
757 fetch c_per_spinal_points into l_dummy;
758 if c_per_spinal_points%notfound then
759 --
760 close c_per_spinal_points;
761 --
762 -- raise error
763 --
764 hr_utility.set_message(805,'BEN_93322_INV_SPINAL_POINT_ID');
765 hr_utility.raise_error;
766 --
767 end if;
768 --
769 close c_per_spinal_points;
770 --
771 end if;
772 end if;
773 --
774 hr_utility.set_location('Leaving:'||l_proc,10);
775 --
776 end chk_mapping_table_pk_id;
777 --
778 --
779 -- ----------------------------------------------------------------------------
780 -- |------< chk_mapping_unique >------|
781 -- ----------------------------------------------------------------------------
782 --
783 -- Description
784 -- This procedure is used to check that only one option is linked to a given
785 -- mapping_table_name, mapping_table_pk_id and effective_date
786 -- on insert and update.
787 --
788 -- Pre Conditions
789 -- None.
790 --
791 -- In Parameters
792 -- opt_id PK of record being inserted or updated
793 -- mapping_table_name for the record beeing inserted or updated
794 -- mapping_table_pk_id for the record beeing inserted or updated
795 -- business_group_id of the record beeing inserted or updated
796 -- effective_date effective date of the session
797 -- object_version_number Object version number of record being
798 -- inserted or updated.
799 --
800 -- Post Success
801 -- Processing continues
802 --
803 -- Post Failure
804 -- Error handled by procedure
805 --
806 -- Access Status
807 -- Internal table handler use only.
808 --
809 Procedure chk_mapping_unique(
810 p_mapping_table_name in varchar2,
811 p_mapping_table_pk_id in number,
812 p_opt_id in number,
813 p_effective_date in date,
814 p_validation_start_date in date,
815 p_validation_end_date in date,
816 p_business_group_id in number) is
817 --
818 l_proc varchar2(72) := g_package||'chk_mapping_unique';
819 l_api_updating boolean;
820 l_exists varchar2(1);
821 --
822 --
823 cursor csr_mapping is
824 select null
825 from ben_opt_f
826 where mapping_table_name = p_mapping_table_name
827 and mapping_table_pk_id = p_mapping_table_pk_id
828 and opt_id <> nvl(p_opt_id,-1)
829 and p_validation_start_date <= effective_end_date
830 and p_validation_end_date >= effective_start_date;
831 --
832 Begin
833 --
834 hr_utility.set_location('Entering:'||l_proc, 5);
835 --
836 -- check if this mapping already exists
837 --
838 open csr_mapping;
839 fetch csr_mapping into l_exists;
840 if csr_mapping%found then
841 --
842 -- raise error
843 --
844 fnd_message.set_name('BEN','BEN_93323_OPT_MAPPING_NOT_UNIQ');
845 fnd_message.raise_error;
846 --
847 end if;
848 --
849 close csr_mapping;
850 --
851 --
852 hr_utility.set_location('Leaving:'||l_proc, 20);
853 --
854 End chk_mapping_unique;
855 --
856 -- ----------------------------------------------------------------------------
857 -- |--------------------------< dt_update_validate >--------------------------|
858 -- ----------------------------------------------------------------------------
859 -- {Start Of Comments}
860 --
861 -- Description:
862 -- This procedure is used for referential integrity of datetracked
863 -- parent entities when a datetrack update operation is taking place
864 -- and where there is no cascading of update defined for this entity.
865 --
866 -- Prerequisites:
867 -- This procedure is called from the update_validate.
868 --
869 -- In Parameters:
870 --
871 -- Post Success:
872 -- Processing continues.
873 --
874 -- Post Failure:
875 --
876 -- Developer Implementation Notes:
877 -- This procedure should not need maintenance unless the HR Schema model
878 -- changes.
879 --
880 -- Access Status:
881 -- Internal Row Handler Use Only.
882 --
883 -- {End Of Comments}
884 -- ----------------------------------------------------------------------------
885 Procedure dt_update_validate
886 (p_cmbn_ptip_opt_id in number default hr_api.g_number,
887 p_datetrack_mode in varchar2,
888 p_validation_start_date in date,
889 p_validation_end_date in date) Is
890 --
891 l_proc varchar2(72) := g_package||'dt_update_validate';
892 l_integrity_error Exception;
893 l_table_name all_tables.table_name%TYPE;
894 --
895 Begin
896 hr_utility.set_location('Entering:'||l_proc, 5);
897 --
898 -- Ensure that the p_datetrack_mode argument is not null
899 --
900 hr_api.mandatory_arg_error
901 (p_api_name => l_proc,
902 p_argument => 'datetrack_mode',
903 p_argument_value => p_datetrack_mode);
904 --
905 -- Only perform the validation if the datetrack update mode is valid
906 --
907 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
908 --
909 --
910 -- Ensure the arguments are not null
911 --
912 hr_api.mandatory_arg_error
913 (p_api_name => l_proc,
914 p_argument => 'validation_start_date',
915 p_argument_value => p_validation_start_date);
916 --
917 hr_api.mandatory_arg_error
918 (p_api_name => l_proc,
919 p_argument => 'validation_end_date',
920 p_argument_value => p_validation_end_date);
921 --
922 /* If ((nvl(p_cmbn_ptip_opt_id, hr_api.g_number) <> hr_api.g_number) and
923 NOT (dt_api.check_min_max_dates
924 (p_base_table_name => 'ben_cmbn_ptip_opt_f',
925 p_base_key_column => 'cmbn_ptip_opt_id',
926 p_base_key_value => p_cmbn_ptip_opt_id,
927 p_from_date => p_validation_start_date,
928 p_to_date => p_validation_end_date))) Then
929 l_table_name := 'ben_cmbn_ptip_opt_f';
930 Raise l_integrity_error;
931 End If;*/
932 --
933 End If;
934 --
935 hr_utility.set_location(' Leaving:'||l_proc, 10);
936 Exception
937 When l_integrity_error Then
938 --
939 -- A referential integrity check was violated therefore
940 -- we must error
941 --
942 ben_utility.parent_integrity_error(p_table_name => l_table_name);
943 When Others Then
944 --
945 -- An unhandled or unexpected error has occurred which
946 -- we must report
947 --
948 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
949 hr_utility.set_message_token('PROCEDURE', l_proc);
950 hr_utility.set_message_token('STEP','15');
951 hr_utility.raise_error;
952 End dt_update_validate;
953 --
954 -- ----------------------------------------------------------------------------
955 -- |--------------------------< dt_delete_validate >--------------------------|
956 -- ----------------------------------------------------------------------------
957 -- {Start Of Comments}
958 --
959 -- Description:
960 -- This procedure is used for referential integrity of datetracked
961 -- child entities when either a datetrack DELETE or ZAP is in operation
962 -- and where there is no cascading of delete defined for this entity.
963 -- For the datetrack mode of DELETE or ZAP we must ensure that no
964 -- datetracked child rows exist between the validation start and end
965 -- dates.
966 --
967 -- Prerequisites:
968 -- This procedure is called from the delete_validate.
969 --
970 -- In Parameters:
971 --
972 -- Post Success:
973 -- Processing continues.
974 --
975 -- Post Failure:
976 -- If a row exists by determining the returning Boolean value from the
977 -- generic dt_api.rows_exist function then we must supply an error via
978 -- the use of the local exception handler l_rows_exist.
979 --
980 -- Developer Implementation Notes:
981 -- This procedure should not need maintenance unless the HR Schema model
982 -- changes.
983 --
984 -- Access Status:
985 -- Internal Row Handler Use Only.
986 --
987 -- {End Of Comments}
988 -- ----------------------------------------------------------------------------
989 Procedure dt_delete_validate
990 (p_opt_id in number,
991 p_datetrack_mode in varchar2,
992 p_validation_start_date in date,
993 p_validation_end_date in date,
994 p_name in varchar2) Is
995 --
996 l_proc varchar2(72) := g_package||'dt_delete_validate';
997 l_rows_exist Exception;
998 l_table_name all_tables.table_name%TYPE;
999 --
1000 Begin
1001 hr_utility.set_location('Entering:'||l_proc, 5);
1002 --
1003 -- Ensure that the p_datetrack_mode argument is not null
1004 --
1005 hr_api.mandatory_arg_error
1006 (p_api_name => l_proc,
1007 p_argument => 'datetrack_mode',
1008 p_argument_value => p_datetrack_mode);
1009 --
1010 -- Only perform the validation if the datetrack mode is either
1011 -- DELETE or ZAP
1012 --
1013 If (p_datetrack_mode = 'DELETE' or
1014 p_datetrack_mode = 'ZAP') then
1015 --
1016 --
1017 -- Ensure the arguments are not null
1018 --
1019 hr_api.mandatory_arg_error
1020 (p_api_name => l_proc,
1021 p_argument => 'validation_start_date',
1022 p_argument_value => p_validation_start_date);
1023 --
1024 hr_api.mandatory_arg_error
1025 (p_api_name => l_proc,
1026 p_argument => 'validation_end_date',
1027 p_argument_value => p_validation_end_date);
1028 --
1029 hr_api.mandatory_arg_error
1030 (p_api_name => l_proc,
1031 p_argument => 'opt_id',
1032 p_argument_value => p_opt_id);
1033 --
1034 If (dt_api.rows_exist
1035 (p_base_table_name => 'ben_elig_per_opt_f',
1036 p_base_key_column => 'opt_id',
1037 p_base_key_value => p_opt_id,
1038 p_from_date => p_validation_start_date,
1039 p_to_date => p_validation_end_date)) Then
1040 l_table_name := 'ben_elig_per_opt_f';
1041 Raise l_rows_exist;
1042 End If;
1043 If (dt_api.rows_exist
1044 (p_base_table_name => 'ben_oipl_f',
1045 p_base_key_column => 'opt_id',
1046 p_base_key_value => p_opt_id,
1047 p_from_date => p_validation_start_date,
1048 p_to_date => p_validation_end_date)) Then
1049 l_table_name := 'ben_oipl_f';
1050 Raise l_rows_exist;
1051 End If;
1052 If (dt_api.rows_exist
1053 (p_base_table_name => 'ben_dsgn_rqmt_f',
1054 p_base_key_column => 'opt_id',
1055 p_base_key_value => p_opt_id,
1056 p_from_date => p_validation_start_date,
1057 p_to_date => p_validation_end_date)) Then
1058 l_table_name := 'ben_dsgn_rqmt_f';
1059 Raise l_rows_exist;
1060 End If;
1061 If (dt_api.rows_exist
1062 (p_base_table_name => 'ben_pl_typ_opt_typ_f',
1063 p_base_key_column => 'opt_id',
1064 p_base_key_value => p_opt_id,
1065 p_from_date => p_validation_start_date,
1066 p_to_date => p_validation_end_date)) Then
1067 l_table_name := 'ben_pl_typ_opt_typ_f';
1068 Raise l_rows_exist;
1069 End If;
1070 --
1071 End If;
1072 --
1073 hr_utility.set_location(' Leaving:'||l_proc, 10);
1074 Exception
1075 When l_rows_exist Then
1076 --
1077 -- A referential integrity check was violated therefore
1078 -- we must error
1079 --
1080 ben_utility.child_exists_error(p_table_name => l_table_name,
1081 p_parent_table_name => 'BEN_OPT_F', /* Bug 4057566 */
1082 p_parent_entity_name => p_name); /* Bug 4057566 */
1083 --
1084 When Others Then
1085 --
1086 -- An unhandled or unexpected error has occurred which
1087 -- we must report
1088 --
1089 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1090 hr_utility.set_message_token('PROCEDURE', l_proc);
1091 hr_utility.set_message_token('STEP','15');
1092 hr_utility.raise_error;
1093 End dt_delete_validate;
1094 --
1095 -- ----------------------------------------------------------------------------
1096 -- |---------------------------< insert_validate >----------------------------|
1097 -- ----------------------------------------------------------------------------
1098 Procedure insert_validate
1099 (p_rec in ben_opt_shd.g_rec_type,
1100 p_effective_date in date,
1101 p_datetrack_mode in varchar2,
1102 p_validation_start_date in date,
1103 p_validation_end_date in date) is
1104 --
1105 l_proc varchar2(72) := g_package||'insert_validate';
1106 --
1107 Begin
1108 hr_utility.set_location('Entering:'||l_proc, 5);
1109 --
1110 -- Call all supporting business operations
1111 --
1112 --
1113 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
1114 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1115 end if;
1116 --
1117 chk_opt_id
1118 (p_opt_id => p_rec.opt_id,
1119 p_effective_date => p_effective_date,
1120 p_object_version_number => p_rec.object_version_number);
1121 --
1122 chk_invk_wv_opt_flag
1123 (p_opt_id => p_rec.opt_id,
1124 p_invk_wv_opt_flag => p_rec.invk_wv_opt_flag,
1125 p_effective_date => p_effective_date,
1126 p_object_version_number => p_rec.object_version_number);
1127 --
1128 chk_rqd_perd_enrt_nenrt_rl
1129 (p_opt_id => p_rec.opt_id,
1133 p_business_group_id => p_rec.business_group_id);
1130 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nenrt_rl,
1131 p_effective_date => p_effective_date,
1132 p_object_version_number => p_rec.object_version_number,
1134 --
1135 chk_rqd_perd_enrt_nenrt_uom
1136 (p_opt_id => p_rec.opt_id,
1137 p_rqd_perd_enrt_nenrt_uom => p_rec.rqd_perd_enrt_nenrt_uom,
1138 p_effective_date => p_effective_date,
1139 p_object_version_number => p_rec.object_version_number);
1140 --
1141 chk_name(p_name => p_rec.name,
1142 p_opt_id => p_rec.opt_id,
1143 p_effective_date => p_effective_date,
1144 p_validation_start_date => p_validation_start_date,
1145 p_validation_end_date => p_validation_end_date,
1146 p_business_group_id => p_rec.business_group_id);
1147 --
1148 chk_comp_reason (p_opt_id => p_rec.opt_id,
1149 p_component_reason => p_rec.component_reason,
1150 p_effective_date => p_effective_date,
1151 p_object_version_number =>p_rec.object_version_number);
1152
1153 --
1154 chk_mapping_table_name(p_opt_id => p_rec.opt_id,
1155 p_mapping_table_name => p_rec.mapping_table_name,
1156 p_effective_date => p_effective_date,
1157 p_object_version_number => p_rec.object_version_number);
1158 --
1159 chk_mapping_table_pk_id(p_opt_id => p_rec.opt_id,
1160 p_mapping_table_pk_id => p_rec.mapping_table_pk_id,
1161 p_mapping_table_name => p_rec.mapping_table_name,
1162 p_effective_date => p_effective_date,
1163 p_object_version_number => p_rec.object_version_number,
1164 p_business_group_id => p_rec.business_group_id);
1165 --
1166 chk_mapping_unique(p_mapping_table_name => p_rec.mapping_table_name,
1167 p_mapping_table_pk_id => p_rec.mapping_table_pk_id,
1168 p_opt_id => p_rec.opt_id,
1169 p_effective_date => p_effective_date,
1170 p_validation_start_date => p_validation_start_date,
1171 p_validation_end_date => p_validation_end_date,
1172 p_business_group_id => p_rec.business_group_id);
1173
1174 chk_opt_group_id (p_opt_id => p_rec.opt_id,
1175 p_group_opt_id => p_rec.group_opt_id,
1176 p_effective_date => p_effective_date,
1177 p_name => p_rec.name
1178 );
1179 --
1180 hr_utility.set_location(' Leaving:'||l_proc, 10);
1181 End insert_validate;
1182 --
1183 -- ----------------------------------------------------------------------------
1184 -- |---------------------------< update_validate >----------------------------|
1185 -- ----------------------------------------------------------------------------
1186 Procedure update_validate
1187 (p_rec in ben_opt_shd.g_rec_type,
1188 p_effective_date in date,
1189 p_datetrack_mode in varchar2,
1190 p_validation_start_date in date,
1191 p_validation_end_date in date) is
1192 --
1193 l_proc varchar2(72) := g_package||'update_validate';
1194 --
1195 Begin
1196 hr_utility.set_location('Entering:'||l_proc, 5);
1197 --
1198 -- Call all supporting business operations
1199 --
1200 --
1201 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
1202 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1203 end if;
1204 --
1205 chk_opt_id
1206 (p_opt_id => p_rec.opt_id,
1207 p_effective_date => p_effective_date,
1208 p_object_version_number => p_rec.object_version_number);
1209 --
1210 chk_invk_wv_opt_flag
1211 (p_opt_id => p_rec.opt_id,
1212 p_invk_wv_opt_flag => p_rec.invk_wv_opt_flag,
1213 p_effective_date => p_effective_date,
1214 p_object_version_number => p_rec.object_version_number);
1215 --
1216 chk_rqd_perd_enrt_nenrt_rl
1217 (p_opt_id => p_rec.opt_id,
1218 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nenrt_rl,
1219 p_effective_date => p_effective_date,
1220 p_object_version_number => p_rec.object_version_number,
1221 p_business_group_id => p_rec.business_group_id);
1222 --
1223 chk_rqd_perd_enrt_nenrt_uom
1224 (p_opt_id => p_rec.opt_id,
1225 p_rqd_perd_enrt_nenrt_uom => p_rec.rqd_perd_enrt_nenrt_uom,
1226 p_effective_date => p_effective_date,
1227 p_object_version_number => p_rec.object_version_number);
1228 --
1229 chk_name(p_name => p_rec.name,
1230 p_opt_id => p_rec.opt_id,
1231 p_effective_date => p_effective_date,
1232 p_validation_start_date => p_validation_start_date,
1233 p_validation_end_date => p_validation_end_date,
1234 p_business_group_id => p_rec.business_group_id);
1235
1236 chk_comp_reason (p_opt_id => p_rec.opt_id,
1237 p_component_reason => p_rec.component_reason,
1238 p_effective_date => p_effective_date,
1239 p_object_version_number =>p_rec.object_version_number);
1240
1241 --
1242 chk_mapping_table_name(p_opt_id => p_rec.opt_id,
1243 p_mapping_table_name => p_rec.mapping_table_name,
1247 chk_mapping_table_pk_id(p_opt_id => p_rec.opt_id,
1244 p_effective_date => p_effective_date,
1245 p_object_version_number => p_rec.object_version_number);
1246 --
1248 p_mapping_table_pk_id => p_rec.mapping_table_pk_id,
1249 p_mapping_table_name => p_rec.mapping_table_name,
1250 p_effective_date => p_effective_date,
1251 p_object_version_number => p_rec.object_version_number,
1252 p_business_group_id => p_rec.business_group_id);
1253 --
1254 chk_mapping_unique(p_mapping_table_name => p_rec.mapping_table_name,
1255 p_mapping_table_pk_id => p_rec.mapping_table_pk_id,
1256 p_opt_id => p_rec.opt_id,
1257 p_effective_date => p_effective_date,
1258 p_validation_start_date => p_validation_start_date,
1259 p_validation_end_date => p_validation_end_date,
1260 p_business_group_id => p_rec.business_group_id);
1261
1262 chk_opt_group_id (p_opt_id => p_rec.opt_id,
1263 p_group_opt_id => p_rec.group_opt_id,
1264 p_effective_date => p_effective_date,
1265 p_name => p_rec.name
1266 );
1267
1268 --
1269 -- Call the datetrack update integrity operation
1270 --
1271 dt_update_validate
1272 (p_cmbn_ptip_opt_id => p_rec.cmbn_ptip_opt_id,
1273 p_datetrack_mode => p_datetrack_mode,
1274 p_validation_start_date => p_validation_start_date,
1275 p_validation_end_date => p_validation_end_date);
1276 --
1277 hr_utility.set_location(' Leaving:'||l_proc, 10);
1278 End update_validate;
1279 --
1280 -- ----------------------------------------------------------------------------
1281 -- |---------------------------< delete_validate >----------------------------|
1282 -- ----------------------------------------------------------------------------
1283 Procedure delete_validate
1284 (p_rec in ben_opt_shd.g_rec_type,
1285 p_effective_date in date,
1286 p_datetrack_mode in varchar2,
1287 p_validation_start_date in date,
1288 p_validation_end_date in date) is
1289 --
1290 l_proc varchar2(72) := g_package||'delete_validate';
1291 l_opt_name ben_opt_f.name%type;
1292 --
1293 -- Bug 4057566
1294 --
1295 CURSOR c_opt_name
1296 IS
1297 SELECT opt.NAME
1298 FROM ben_opt_f opt
1299 WHERE opt.opt_id = p_rec.opt_id
1300 AND p_effective_date BETWEEN opt.effective_start_date
1301 AND opt.effective_end_date;
1302 --
1303 Begin
1304 hr_utility.set_location('Entering:'||l_proc, 5);
1305 --
1306 -- Bug 4057566
1307 --
1308 open c_opt_name ;
1309 --
1310 fetch c_opt_name into l_opt_name;
1311 --
1312 close c_opt_name;
1313 --
1314 --
1315 -- Call all supporting business operations
1316 --
1317
1318 chk_opt_group_id (p_opt_id => p_rec.opt_id,
1319 p_name => l_opt_name,
1320 p_group_opt_id => p_rec.group_opt_id,
1321 p_effective_date => p_effective_date
1322 );
1323
1324 dt_delete_validate
1325 (p_datetrack_mode => p_datetrack_mode,
1326 p_validation_start_date => p_validation_start_date,
1327 p_validation_end_date => p_validation_end_date,
1328 p_name => l_opt_name,
1329 p_opt_id => p_rec.opt_id);
1330
1331
1332 --
1333 hr_utility.set_location(' Leaving:'||l_proc, 10);
1334 End delete_validate;
1335 --
1336 --
1337 -- ---------------------------------------------------------------------------
1338 -- |---------------------< return_legislation_code >-------------------------|
1339
1340 -- ---------------------------------------------------------------------------
1341 --
1342 function return_legislation_code
1343 (p_opt_id in number) return varchar2 is
1344 --
1345 -- Declare cursor
1346 --
1347 cursor csr_leg_code is
1348 select a.legislation_code
1349 from per_business_groups a,
1350 ben_opt_f b
1351 where b.opt_id = p_opt_id
1352 and a.business_group_id = b.business_group_id;
1353 --
1354 -- Declare local variables
1355 --
1356 l_legislation_code varchar2(150);
1357 l_proc varchar2(72) := g_package||'return_legislation_code';
1358 --
1359 begin
1360 --
1361 hr_utility.set_location('Entering:'|| l_proc, 10);
1362 --
1363 -- Ensure that all the mandatory parameter are not null
1364 --
1365 hr_api.mandatory_arg_error(p_api_name => l_proc,
1366 p_argument => 'opt_id',
1367 p_argument_value => p_opt_id);
1368 --
1369 open csr_leg_code;
1370 --
1371 fetch csr_leg_code into l_legislation_code;
1372
1373 --
1374 if csr_leg_code%notfound then
1375 --
1376 close csr_leg_code;
1377 --
1378 -- The primary key is invalid therefore we must error
1379 --
1380 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1381 hr_utility.raise_error;
1382 --
1383 end if;
1384 --
1385 close csr_leg_code;
1386 --
1390
1387 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1388 --
1389 return l_legislation_code;
1391 --
1392 end return_legislation_code;
1393
1394 --
1395 end ben_opt_bus;