[Home] [Help]
PACKAGE BODY: APPS.BEN_DDR_BUS
Source
1 Package Body ben_ddr_bus as
2 /* $Header: beddrrhi.pkb 115.9 2003/03/12 07:36:29 rpgupta ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ddr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< chk_dsgn_rqmt_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 -- dsgn_rqmt_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_dsgn_rqmt_id(p_dsgn_rqmt_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_dsgn_rqmt_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_ddr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_dsgn_rqmt_id => p_dsgn_rqmt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_dsgn_rqmt_id,hr_api.g_number)
55 <> ben_ddr_shd.g_old_rec.dsgn_rqmt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_ddr_shd.constraint_error('BEN_DSGN_RQMT_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_dsgn_rqmt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ddr_shd.constraint_error('BEN_DSGN_RQMT_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_dsgn_rqmt_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |--------------------------< chk_dsgn_typ_cd >-----------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the lookup value is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- dsgn_rqmt_id PK of record being inserted or updated.
91 -- dsgn_typ_cd Value of lookup code.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_dsgn_typ_cd(p_dsgn_rqmt_id in number,
106 p_dsgn_typ_cd in varchar2,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_dsgn_typ_cd';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := ben_ddr_shd.api_updating
118 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
119 p_effective_date => p_effective_date,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and p_dsgn_typ_cd
124 <> nvl(ben_ddr_shd.g_old_rec.dsgn_typ_cd,hr_api.g_varchar2)
125 or not l_api_updating)
126 and p_dsgn_typ_cd is not null then
127 --
128 -- check if value of lookup falls within lookup type.
129 --
130 if hr_api.not_exists_in_hr_lookups
131 (p_lookup_type => 'BEN_DSGN_TYP',
132 p_lookup_code => p_dsgn_typ_cd,
133 p_effective_date => p_effective_date) then
134 --
135 -- raise error as does not exist as lookup
136 --
137 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
138 hr_utility.raise_error;
139 --
140 end if;
141 --
142 end if;
143 --
144 hr_utility.set_location('Leaving:'||l_proc,10);
145 --
146 end chk_dsgn_typ_cd;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |--------------------< chk_no_mn_dpnts_rqd_or_mn_num >---------------------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- Description
153 -- This procedure is used to check that the no_mn_num_dfnd_flag and
154 -- mn_dpnts_rqd_num items are mutually exclusive.
155 -- When the flag is set 'Y' then the value of mn_dpnts_rqd_num
156 -- must be null. When the flag is set to 'N' then there must be a value in
157 -- mn_dpnts_rqd_num
158 --
159 -- Pre Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- dsgn_rqmt_id PK of record being inserted or updated.
164 -- no_mn_num_dfnd_flag.
165 -- mn_dpnts_rqd_num.
166 -- effective_date effective date
167 -- object_version_number Object version number of record being
168 -- inserted or updated.
169 --
170 -- Post Success
171 -- Processing continues
172 --
173 -- Post Failure
174 -- Error handled by procedure
175 --
176 -- Access Status
177 -- Internal table handler use only.
178 --
179 Procedure chk_no_mn_dpnts_rqd_or_mn_num(p_dsgn_rqmt_id in number,
180 p_no_mn_num_dfnd_flag in varchar2,
181 p_mn_dpnts_rqd_num in number,
182 p_effective_date in date,
183 p_object_version_number in number) is
184 --
185 l_proc varchar2(72) := g_package||'chk_no_mn_dpnts_rqd_or_mn_num';
186
187 l_api_updating boolean;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc, 5);
192 --
193 --
194
195 l_api_updating := ben_ddr_shd.api_updating
196 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
197 p_effective_date => p_effective_date,
198 p_object_version_number => p_object_version_number);
199 --
200 -- If no_mn_num_dfnd_flag is "on", then mn_dpnts_rqd_num
201 -- must be null.
202 If p_no_mn_num_dfnd_flag = 'Y' and p_mn_dpnts_rqd_num is not null then
203 fnd_message.set_name('BEN','BEN_91788_NO_MIN_OR_MIN_NUM');
204 fnd_message.raise_error;
205 end if;
206 If p_no_mn_num_dfnd_flag = 'N' and p_mn_dpnts_rqd_num is null then
207 fnd_message.set_name('BEN','BEN_91788_NO_MIN_OR_MIN_NUM');
208 fnd_message.raise_error;
209 end if;
210 --
211 --
212 hr_utility.set_location('Leaving:'||l_proc,10);
213 --
214 end chk_no_mn_dpnts_rqd_or_mn_num;
215 --
216 -- ----------------------------------------------------------------------------
217 -- |--------------------< chk_no_mx_dpnts_or_mx_num_alwd >---------------------|
218 -- ----------------------------------------------------------------------------
219 --
220 -- Description
221 -- This procedure is used to check that the no_mx_num_dfnd_flag and
222 -- mx_dpnts_alwd_num items are mutually exclusive.
223 -- When the flag is set 'Y' then the value of mx_dpnts_alwd_num
224 -- must be null. When the flag is set to 'N' then there must be a value in
225 -- mx_dpnts_alwd_num.
226 --
227 -- Pre Conditions
228 -- None.
229 --
230 -- In Parameters
231 -- dsgn_rqmt_id PK of record being inserted or updated.
232 -- no_mx_num_dfnd_flag.
233 -- mx_dpnts_alwd_num.
234 -- effective_date effective date
235 -- object_version_number Object version number of record being
236 -- inserted or updated.
237 --
238 -- Post Success
239 -- Processing continues
240 --
241 -- Post Failure
242 -- Error handled by procedure
243 --
244 -- Access Status
245 -- Internal table handler use only.
246 --
247 Procedure chk_no_mx_dpnts_or_mx_num_alwd(p_dsgn_rqmt_id in number,
248 p_no_mx_num_dfnd_flag in varchar2,
249 p_mx_dpnts_alwd_num in number,
250 p_effective_date in date,
251 p_object_version_number in number) is
252 --
253 l_proc varchar2(72) := g_package||'chk_no_mx_dpnts_or_mx_num_alwd';
254
255 l_api_updating boolean;
256 --
257 Begin
258 --
259 hr_utility.set_location('Entering:'||l_proc, 5);
260 --
261 --
262
263 l_api_updating := ben_ddr_shd.api_updating
264 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
265 p_effective_date => p_effective_date,
266 p_object_version_number => p_object_version_number);
267 --
268 -- If no_mx_num_dfnd_flag is "on", then mx_dpnts_alwd_num
269 -- must be null.
270 If p_no_mx_num_dfnd_flag = 'Y' and p_mx_dpnts_alwd_num is not null then
271 fnd_message.set_name('BEN','BEN_91789_NO_MAX_OR_MAX_NUM');
272 fnd_message.raise_error;
273 end if;
274 If p_no_mx_num_dfnd_flag = 'N' and p_mx_dpnts_alwd_num is null then
275 fnd_message.set_name('BEN','BEN_91789_NO_MAX_OR_MAX_NUM');
276 fnd_message.raise_error;
277 end if;
278
279 --
280 --
281 hr_utility.set_location('Leaving:'||l_proc,10);
282 --
283 end chk_no_mx_dpnts_or_mx_num_alwd;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |------------------------< chk_cvr_all_elig_flag >-------------------------|
287 -- ----------------------------------------------------------------------------
288 --
289 -- Description
290 -- This procedure is used to check that the lookup value is valid.
291 --
292 -- Pre Conditions
293 -- None.
294 --
295 -- In Parameters
296 -- dsgn_rqmt_id PK of record being inserted or updated.
297 -- cvr_all_elig_flag Value of lookup code.
298 -- effective_date effective date
299 -- object_version_number Object version number of record being
300 -- inserted or updated.
301 --
302 -- Post Success
303 -- Processing continues
304 --
305 -- Post Failure
306 -- Error handled by procedure
307 --
308 -- Access Status
309 -- Internal table handler use only.
310 --
311 Procedure chk_cvr_all_elig_flag(p_dsgn_rqmt_id in number,
312 p_cvr_all_elig_flag in varchar2,
313 p_effective_date in date,
314 p_object_version_number in number) is
315 --
316 l_proc varchar2(72) := g_package||'chk_cvr_all_elig_flag';
317 l_api_updating boolean;
318 --
319 Begin
320 --
321 hr_utility.set_location('Entering:'||l_proc, 5);
322 --
323 l_api_updating := ben_ddr_shd.api_updating
324 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
325 p_effective_date => p_effective_date,
326 p_object_version_number => p_object_version_number);
327 --
328 if (l_api_updating
329 and p_cvr_all_elig_flag
330 <> nvl(ben_ddr_shd.g_old_rec.cvr_all_elig_flag,hr_api.g_varchar2)
331 or not l_api_updating) then
332 --
333 -- check if value of lookup falls within lookup type.
334 --
335 --
336 if hr_api.not_exists_in_hr_lookups
337 (p_lookup_type => 'YES_NO',
338 p_lookup_code => p_cvr_all_elig_flag,
339 p_effective_date => p_effective_date) then
340 --
341 -- raise error as does not exist as lookup
342 --
343 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
344 hr_utility.raise_error;
345 --
346 end if;
347 --
348 end if;
349 --
350 hr_utility.set_location('Leaving:'||l_proc,10);
351 --
352 end chk_cvr_all_elig_flag;
353 --
354 -- ----------------------------------------------------------------------------
355 -- |-----------------------< chk_no_mx_num_dfnd_flag >------------------------|
356 -- ----------------------------------------------------------------------------
357 --
358 -- Description
359 -- This procedure is used to check that the lookup value is valid.
360 --
361 -- Pre Conditions
362 -- None.
363 --
364 -- In Parameters
365 -- dsgn_rqmt_id PK of record being inserted or updated.
366 -- no_mx_num_dfnd_flag Value of lookup code.
367 -- effective_date effective date
368 -- object_version_number Object version number of record being
369 -- inserted or updated.
370 --
371 -- Post Success
372 -- Processing continues
373 --
374 -- Post Failure
375 -- Error handled by procedure
376 --
377 -- Access Status
378 -- Internal table handler use only.
379 --
380 Procedure chk_no_mx_num_dfnd_flag(p_dsgn_rqmt_id in number,
381 p_no_mx_num_dfnd_flag in varchar2,
382 p_effective_date in date,
383 p_object_version_number in number) is
384 --
385 l_proc varchar2(72) := g_package||'chk_no_mx_num_dfnd_flag';
386 l_api_updating boolean;
387 --
388 Begin
389 --
390 hr_utility.set_location('Entering:'||l_proc, 5);
391 --
392 l_api_updating := ben_ddr_shd.api_updating
393 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
394 p_effective_date => p_effective_date,
395 p_object_version_number => p_object_version_number);
396 --
397 if (l_api_updating
398 and p_no_mx_num_dfnd_flag
399 <> nvl(ben_ddr_shd.g_old_rec.no_mx_num_dfnd_flag,hr_api.g_varchar2)
400 or not l_api_updating) then
401 --
402 -- check if value of lookup falls within lookup type.
403 --
404 --
405 if hr_api.not_exists_in_hr_lookups
406 (p_lookup_type => 'YES_NO',
407 p_lookup_code => p_no_mx_num_dfnd_flag,
408 p_effective_date => p_effective_date) then
409 --
410 -- raise error as does not exist as lookup
411 --
412 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
413 hr_utility.raise_error;
414 --
415 end if;
416 --
417 end if;
418 --
419 hr_utility.set_location('Leaving:'||l_proc,10);
420 --
421 end chk_no_mx_num_dfnd_flag;
422 --
423 -- ----------------------------------------------------------------------------
424 -- |-------------------< chk_no_mn_num_dfnd_flag >----------------------------|
425 -- ----------------------------------------------------------------------------
426 --
427 -- Description
428 -- This procedure is used to check that the lookup value is valid.
429 --
430 -- Pre Conditions
431 -- None.
432 --
433 -- In Parameters
434 -- dsgn_rqmt_id PK of record being inserted or updated.
435 -- no_mn_num_dfnd_flag Value of lookup code.
436 -- effective_date effective date
437 -- object_version_number Object version number of record being
438 -- inserted or updated.
439 --
440 -- Post Success
441 -- Processing continues
442 --
443 -- Post Failure
444 -- Error handled by procedure
445 --
446 -- Access Status
447 -- Internal table handler use only.
448 --
449 Procedure chk_no_mn_num_dfnd_flag(p_dsgn_rqmt_id in number,
450 p_no_mn_num_dfnd_flag in varchar2,
451 p_effective_date in date,
452 p_object_version_number in number) is
453 --
454 l_proc varchar2(72) := g_package||'chk_no_mn_num_dfnd_flag';
455 l_api_updating boolean;
456 --
457 Begin
458 --
459 hr_utility.set_location('Entering:'||l_proc, 5);
460 --
461 l_api_updating := ben_ddr_shd.api_updating
462 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
463 p_effective_date => p_effective_date,
464 p_object_version_number => p_object_version_number);
465 --
466 if (l_api_updating
467 and p_no_mn_num_dfnd_flag
468 <> nvl(ben_ddr_shd.g_old_rec.no_mn_num_dfnd_flag,hr_api.g_varchar2)
469 or not l_api_updating) then
470 --
471 -- check if value of lookup falls within lookup type.
472 --
473 --
474 if hr_api.not_exists_in_hr_lookups
475 (p_lookup_type => 'YES_NO',
476 p_lookup_code => p_no_mn_num_dfnd_flag,
477 p_effective_date => p_effective_date) then
478 --
479 -- raise error as does not exist as lookup
480 --
481 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
482 hr_utility.raise_error;
483 --
484 end if;
485 --
486 end if;
487 --
488 hr_utility.set_location('Leaving:'||l_proc,10);
489 --
490 end chk_no_mn_num_dfnd_flag;
491 --
492 -- ----------------------------------------------------------------------------
493 -- |----------------------< chk_grp_rlshp_cd >--------------------------------|
494 -- ----------------------------------------------------------------------------
495 --
496 -- Description
497 -- This procedure is used to check that the lookup value is valid.
498 --
499 -- Pre Conditions
500 -- None.
501 --
502 -- In Parameters
503 -- dsgn_rqmt_id PK of record being inserted or updated.
504 -- grp_rlshp_cd Value of lookup code.
505 -- effective_date effective date
506 -- object_version_number Object version number of record being
507 -- inserted or updated.
508 --
509 -- Post Success
510 -- Processing continues
511 --
512 -- Post Failure
513 -- Error handled by procedure
514 --
515 -- Access Status
516 -- Internal table handler use only.
517 --
518 Procedure chk_grp_rlshp_cd(p_dsgn_rqmt_id in number,
519 p_grp_rlshp_cd in varchar2,
520 p_effective_date in date,
521 p_object_version_number in number) is
522 --
523 l_proc varchar2(72) := g_package||'chk_grp_rlshp_cd';
524 l_api_updating boolean;
525 --
526 Begin
527 --
528 hr_utility.set_location('Entering:'||l_proc, 5);
529 --
530 l_api_updating := ben_ddr_shd.api_updating
531 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
532 p_effective_date => p_effective_date,
533 p_object_version_number => p_object_version_number);
534 --
535 if (l_api_updating
536 and p_grp_rlshp_cd
537 <> nvl(ben_ddr_shd.g_old_rec.grp_rlshp_cd,hr_api.g_varchar2)
538 or not l_api_updating)
539 and p_grp_rlshp_cd is not null then
540 --
541 -- check if value of lookup falls within lookup type.
542 --
543 if hr_api.not_exists_in_hr_lookups
544 (p_lookup_type => 'BEN_GRP_RLSHP',
545 p_lookup_code => p_grp_rlshp_cd,
546 p_effective_date => p_effective_date) then
547 --
548 -- raise error as does not exist as lookup
549 --
550 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
551 hr_utility.raise_error;
552 --
553 end if;
554 --
555 end if;
556 --
557 hr_utility.set_location('Leaving:'||l_proc,10);
558 --
559 end chk_grp_rlshp_cd;
560 -- ----------------------------------------------------------------------------
561 -- |------------------------< chk_pln_oipl_opt_mutexcl >----------------------|
562 -- ----------------------------------------------------------------------------
563 --
564 -- Description
565 -- This procedure is used to enforce the ARC relationship existing on
566 -- ben_dsgn_rqmt_f between plan, oipl, and opt.
567 --
568 -- Pre Conditions
569 -- None.
570 --
571 -- In Parameters
572 -- dsgn_rqmt_id PK of record being inserted or updated.
573 -- pl_id
574 -- oipl_id
575 -- opt_id
576 -- effective_date Session date of record.
577 -- business_group_id Business group id of record being inserted.
578 -- object_version_number Object version number of record being
579 -- inserted or updated.
580 --
581 -- Post Success
582 -- Processing continues
583 --
584 -- Post Failure
585 -- Errors handled by the procedure
586 --
587 -- Access Status
588 -- Internal table handler use only.
589 --
590 Procedure chk_pln_oipl_opt_mutexcl(p_dsgn_rqmt_id in number,
591 p_pl_id in number,
592 p_oipl_id in number,
593 p_opt_id in number,
594 p_effective_date in date,
595 p_business_group_id in number,
596 p_object_version_number in number) is
597 --
598 l_proc varchar2(72) := g_package||'chk_pln_oipl_opt_mutexcl';
599 l_api_updating boolean;
600 l_dummy varchar2(1);
601 l_pl_id ben_pl_f.pl_id%TYPE;
602 l_oipl_id ben_oipl_f.oipl_id%TYPE;
603 l_opt_id ben_opt_f.opt_id%TYPE;
604
605 --
606 cursor c1 is
607 select a.pl_id, a.oipl_id, a.opt_id
608 -- into l_pl_id, l_oipl_id, l_opt_id
609 from ben_dsgn_rqmt_f a
610 where a.business_group_id +0 = p_business_group_id
611 and a.dsgn_rqmt_id = p_dsgn_rqmt_id
612 and p_effective_date
613 between a.effective_start_date
614 and a.effective_end_date;
615 --
616 Begin
617 --
618 hr_utility.set_location('Entering:'||l_proc, 5);
619 --
620 l_api_updating := ben_ddr_shd.api_updating
621 (p_dsgn_rqmt_id => p_dsgn_rqmt_id,
622 p_effective_date => p_effective_date,
623 p_object_version_number => p_object_version_number);
624 --
625 if (l_api_updating
626 or not l_api_updating) then
627 --
628 -- Check if there is mutually exclusivity.
629 --
630 open c1;
631 --
632 fetch c1 into l_pl_id, l_oipl_id, l_opt_id;
633 if c1%found then
634 --
635 if p_pl_id is not null and
636 (l_opt_id is not null or
637 l_oipl_id is not null) then
638 fnd_message.set_name('BEN','BEN_91926_PLN_OIPL_OPT_MUTEXC');
639 fnd_message.raise_error;
640 elsif p_opt_id is not null and
641 (l_pl_id is not null or
642 l_oipl_id is not null) then
643 fnd_message.set_name('BEN','BEN_91927_OIPL_PLN_OPT_MUTEXC');
644 fnd_message.raise_error;
645 elsif p_oipl_id is not null and
646 (l_opt_id is not null or
647 l_pl_id is not null) then
648 fnd_message.set_name('BEN','BEN_91928_OPT_PLN_OIPL_MUTEXC');
649 fnd_message.raise_error;
650 end if;
651 --
652 end if;
653 close c1;
654 --
655 end if;
656 --
657 hr_utility.set_location('Leaving:'||l_proc, 10);
658 --
659 End chk_pln_oipl_opt_mutexcl;
660 --
661 -- bug 2837189
662 -- validate duplication of records
663 -- ----------------------------------------------------------------------------
664 -- |------------------------< chk_dsgn_rqmt_uniq >----------------------|
665 -- ----------------------------------------------------------------------------
666 --
667 -- Description
668 -- This procedure is used to enforce uniquness of the designation requirements
669 --
670 -- Pre Conditions
671 -- None.
672 --
673 -- In Parameters
674 -- dsgn_rqmt_id PK of record being inserted or updated.
675 -- pl_id Plan id
676 -- oipl_id Option in Plan id
677 -- effective_date Session date of record.
678 -- grp_rlshp_cd Group Relationship code
679 -- dsgn_typ_cd Designation Type code
680 -- business_group_id Business group id of record being inserted.
681 -- object_version_number Object version number of record being
682 -- inserted or updated.
683 --
684 -- Post Success
685 -- Processing continues
686 --
687 -- Post Failure
688 -- Errors handled by the procedure
689 --
690 -- Access Status
691 -- Internal table handler use only.
692 --
693 Procedure chk_dsgn_rqmt_uniq(p_dsgn_rqmt_id in number,
694 p_pl_id in number,
695 p_oipl_id in number,
696 p_effective_date in date,
697 p_business_group_id in number,
698 p_grp_rlshp_cd in varchar2,
699 p_dsgn_typ_cd in varchar2,
700 p_object_version_number in number) is
701 --
702 l_proc varchar2(72) := g_package||'chk_dsgn_rqmt_uniq';
703 l_dummy varchar2(1);
704
705 --
706 cursor c_chk_uniq_dsgn_rqmt is
707 select 'X'
708 from ben_dsgn_rqmt_f
709 where business_group_id = p_business_group_id
710 and grp_rlshp_cd = p_grp_rlshp_cd
711 and dsgn_typ_cd = p_dsgn_typ_cd
712 and p_effective_date
713 between effective_start_date
714 and effective_end_date
715 and (pl_id = p_pl_id
716 or
717 oipl_id = p_oipl_id)
718 and dsgn_rqmt_id <> nvl(p_dsgn_rqmt_id, -999);
719
720 --
721 --
722 Begin
723 --
724 hr_utility.set_location('Entering:'||l_proc, 5);
725 --
726 --
727 -- Check if the record already exists
728 --
729 open c_chk_uniq_dsgn_rqmt;
730 fetch c_chk_uniq_dsgn_rqmt into l_dummy;
731 close c_chk_uniq_dsgn_rqmt;
732
733 if l_dummy is not null then
734 --
735 fnd_message.set_name('BEN','BEN_93354_GRP_RLSHP_TYP_UNIQUE');
736 fnd_message.raise_error;
737 --
738 end if;
739 --
740
741 --
742
743 --
744 hr_utility.set_location('Leaving:'||l_proc, 10);
745 --
746 End chk_dsgn_rqmt_uniq;
747
748 -- end bug 2837189
749
750
751 -- ----------------------------------------------------------------------------
752 -- |--------------------------< dt_update_validate >--------------------------|
753 -- ----------------------------------------------------------------------------
754 -- {Start Of Comments}
755 --
756 -- Description:
757 -- This procedure is used for referential integrity of datetracked
758 -- parent entities when a datetrack update operation is taking place
759 -- and where there is no cascading of update defined for this entity.
760 --
761 -- Prerequisites:
762 -- This procedure is called from the update_validate.
763 --
764 -- In Parameters:
765 --
766 -- Post Success:
767 -- Processing continues.
768 --
769 -- Post Failure:
770 --
771 -- Developer Implementation Notes:
772 -- This procedure should not need maintenance unless the HR Schema model
773 -- changes.
774 --
775 -- Access Status:
776 -- Internal Row Handler Use Only.
777 --
778 -- {End Of Comments}
779 -- ----------------------------------------------------------------------------
780 Procedure dt_update_validate
781 (p_oipl_id in number default hr_api.g_number,
782 p_opt_id in number default hr_api.g_number,
783 p_pl_id in number default hr_api.g_number,
784 p_datetrack_mode in varchar2,
785 p_validation_start_date in date,
786 p_validation_end_date in date) Is
787 --
788 l_proc varchar2(72) := g_package||'dt_update_validate';
789 l_integrity_error Exception;
790 l_table_name all_tables.table_name%TYPE;
791 --
792 Begin
793 hr_utility.set_location('Entering:'||l_proc, 5);
794 --
795 -- Ensure that the p_datetrack_mode argument is not null
796 --
797 hr_api.mandatory_arg_error
798 (p_api_name => l_proc,
799 p_argument => 'datetrack_mode',
800 p_argument_value => p_datetrack_mode);
801 --
802 -- Only perform the validation if the datetrack update mode is valid
803 --
804 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
805 --
806 --
807 -- Ensure the arguments are not null
808 --
809 hr_api.mandatory_arg_error
810 (p_api_name => l_proc,
811 p_argument => 'validation_start_date',
812 p_argument_value => p_validation_start_date);
813 --
814 hr_api.mandatory_arg_error
815 (p_api_name => l_proc,
816 p_argument => 'validation_end_date',
817 p_argument_value => p_validation_end_date);
818 --
819 If ((nvl(p_oipl_id, hr_api.g_number) <> hr_api.g_number) and
820 NOT (dt_api.check_min_max_dates
821 (p_base_table_name => 'ben_oipl_f',
822 p_base_key_column => 'oipl_id',
823 p_base_key_value => p_oipl_id,
824 p_from_date => p_validation_start_date,
825 p_to_date => p_validation_end_date))) Then
826 l_table_name := 'ben_oipl_f';
827 Raise l_integrity_error;
828 End If;
829 If ((nvl(p_opt_id, hr_api.g_number) <> hr_api.g_number) and
830 NOT (dt_api.check_min_max_dates
831 (p_base_table_name => 'ben_opt_f',
832 p_base_key_column => 'opt_id',
833 p_base_key_value => p_opt_id,
834 p_from_date => p_validation_start_date,
835 p_to_date => p_validation_end_date))) Then
836 l_table_name := 'ben_opt_f';
837 Raise l_integrity_error;
838 End If;
839 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
840 NOT (dt_api.check_min_max_dates
841 (p_base_table_name => 'ben_pl_f',
842 p_base_key_column => 'pl_id',
843 p_base_key_value => p_pl_id,
844 p_from_date => p_validation_start_date,
845 p_to_date => p_validation_end_date))) Then
846 l_table_name := 'ben_pl_f';
847 Raise l_integrity_error;
848 End If;
849 --
850 End If;
851 --
852 hr_utility.set_location(' Leaving:'||l_proc, 10);
853 Exception
854 When l_integrity_error Then
855 --
856 -- A referential integrity check was violated therefore
857 -- we must error
858 --
859 ben_utility.parent_integrity_error(p_table_name => l_table_name);
860 When Others Then
861 --
862 -- An unhandled or unexpected error has occurred which
863 -- we must report
864 --
865 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
866 hr_utility.set_message_token('PROCEDURE', l_proc);
867 hr_utility.set_message_token('STEP','15');
868 hr_utility.raise_error;
869 End dt_update_validate;
870 --
871 -- ----------------------------------------------------------------------------
872 -- |--------------------------< dt_delete_validate >--------------------------|
873 -- ----------------------------------------------------------------------------
874 -- {Start Of Comments}
875 --
876 -- Description:
877 -- This procedure is used for referential integrity of datetracked
878 -- child entities when either a datetrack DELETE or ZAP is in operation
879 -- and where there is no cascading of delete defined for this entity.
880 -- For the datetrack mode of DELETE or ZAP we must ensure that no
881 -- datetracked child rows exist between the validation start and end
882 -- dates.
883 --
884 -- Prerequisites:
885 -- This procedure is called from the delete_validate.
886 --
887 -- In Parameters:
888 --
889 -- Post Success:
890 -- Processing continues.
891 --
892 -- Post Failure:
893 -- If a row exists by determining the returning Boolean value from the
894 -- generic dt_api.rows_exist function then we must supply an error via
895 -- the use of the local exception handler l_rows_exist.
896 --
897 -- Developer Implementation Notes:
898 -- This procedure should not need maintenance unless the HR Schema model
899 -- changes.
900 --
901 -- Access Status:
902 -- Internal Row Handler Use Only.
903 --
904 -- {End Of Comments}
905 -- ----------------------------------------------------------------------------
906 Procedure dt_delete_validate
907 (p_dsgn_rqmt_id in number,
908 p_datetrack_mode in varchar2,
909 p_validation_start_date in date,
910 p_validation_end_date in date) Is
911 --
912 l_proc varchar2(72) := g_package||'dt_delete_validate';
913 l_rows_exist Exception;
914 l_table_name all_tables.table_name%TYPE;
915 --
916 Begin
917 hr_utility.set_location('Entering:'||l_proc, 5);
918 --
919 -- Ensure that the p_datetrack_mode argument is not null
920 --
921 hr_api.mandatory_arg_error
922 (p_api_name => l_proc,
923 p_argument => 'datetrack_mode',
924 p_argument_value => p_datetrack_mode);
925 --
926 -- Only perform the validation if the datetrack mode is either
927 -- DELETE or ZAP
928 --
929 If (p_datetrack_mode = 'DELETE' or
930 p_datetrack_mode = 'ZAP') then
931 --
932 --
933 -- Ensure the arguments are not null
934 --
935 hr_api.mandatory_arg_error
936 (p_api_name => l_proc,
937 p_argument => 'validation_start_date',
938 p_argument_value => p_validation_start_date);
939 --
940 hr_api.mandatory_arg_error
941 (p_api_name => l_proc,
942 p_argument => 'validation_end_date',
943 p_argument_value => p_validation_end_date);
944 --
945 hr_api.mandatory_arg_error
946 (p_api_name => l_proc,
947 p_argument => 'dsgn_rqmt_id',
948 p_argument_value => p_dsgn_rqmt_id);
949 --
950 --
951 --
952 End If;
953 --
954 hr_utility.set_location(' Leaving:'||l_proc, 10);
955 Exception
956 When l_rows_exist Then
957 --
958 -- A referential integrity check was violated therefore
959 -- we must error
960 --
961 ben_utility.child_exists_error(p_table_name => l_table_name);
962 --
963 When Others Then
964 --
965 -- An unhandled or unexpected error has occurred which
966 -- we must report
967 --
968 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
969 hr_utility.set_message_token('PROCEDURE', l_proc);
970 hr_utility.set_message_token('STEP','15');
971 hr_utility.raise_error;
972 End dt_delete_validate;
973 --
974 -- ----------------------------------------------------------------------------
975 -- |---------------------------< insert_validate >----------------------------|
976 -- ----------------------------------------------------------------------------
977 Procedure insert_validate
978 (p_rec in ben_ddr_shd.g_rec_type,
979 p_effective_date in date,
980 p_datetrack_mode in varchar2,
981 p_validation_start_date in date,
982 p_validation_end_date in date) is
983 --
984 l_proc varchar2(72) := g_package||'insert_validate';
985 --
986 Begin
987 hr_utility.set_location('Entering:'||l_proc, 5);
988 --
989 -- Call all supporting business operations
990 --
991 --
992 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
993 --
994 chk_dsgn_rqmt_id
995 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
996 p_effective_date => p_effective_date,
997 p_object_version_number => p_rec.object_version_number);
998 --
999 chk_dsgn_typ_cd
1000 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1001 p_dsgn_typ_cd => p_rec.dsgn_typ_cd,
1002 p_effective_date => p_effective_date,
1003 p_object_version_number => p_rec.object_version_number);
1004 --
1005 chk_no_mn_dpnts_rqd_or_mn_num
1006 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1007 p_no_mn_num_dfnd_flag => p_rec.no_mn_num_dfnd_flag,
1008 p_mn_dpnts_rqd_num => p_rec.mn_dpnts_rqd_num,
1009 p_effective_date => p_effective_date,
1010 p_object_version_number => p_rec.object_version_number);
1011 --
1012 chk_no_mx_dpnts_or_mx_num_alwd
1013 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1014 p_no_mx_num_dfnd_flag => p_rec.no_mx_num_dfnd_flag,
1015 p_mx_dpnts_alwd_num => p_rec.mx_dpnts_alwd_num,
1016 p_effective_date => p_effective_date,
1017 p_object_version_number => p_rec.object_version_number);
1018 --
1019 chk_cvr_all_elig_flag
1020 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1021 p_cvr_all_elig_flag => p_rec.cvr_all_elig_flag,
1022 p_effective_date => p_effective_date,
1023 p_object_version_number => p_rec.object_version_number);
1024 --
1025 chk_no_mx_num_dfnd_flag
1026 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1027 p_no_mx_num_dfnd_flag => p_rec.no_mx_num_dfnd_flag,
1028 p_effective_date => p_effective_date,
1029 p_object_version_number => p_rec.object_version_number);
1030 --
1031 chk_no_mn_num_dfnd_flag
1032 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1033 p_no_mn_num_dfnd_flag => p_rec.no_mn_num_dfnd_flag,
1034 p_effective_date => p_effective_date,
1035 p_object_version_number => p_rec.object_version_number);
1036 --
1037 chk_grp_rlshp_cd
1038 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1039 p_grp_rlshp_cd => p_rec.grp_rlshp_cd,
1040 p_effective_date => p_effective_date,
1041 p_object_version_number => p_rec.object_version_number);
1042 --
1043 chk_pln_oipl_opt_mutexcl
1044 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1045 p_pl_id => p_rec.pl_id,
1046 p_oipl_id => p_rec.oipl_id,
1047 p_opt_id => p_rec.opt_id,
1048 p_effective_date => p_effective_date,
1049 p_business_group_id => p_rec.business_group_id,
1050 p_object_version_number => p_rec.object_version_number);
1051 --
1052 --
1053
1054 --bug 2837189
1055 chk_dsgn_rqmt_uniq
1056 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id ,
1057 p_effective_date => p_effective_date ,
1058 p_business_group_id => p_rec.business_group_id ,
1059 p_grp_rlshp_cd => p_rec.grp_rlshp_cd,
1060 p_dsgn_typ_cd => p_rec.dsgn_typ_cd,
1061 p_pl_id => p_rec.pl_id,
1062 p_oipl_id => p_rec.oipl_id,
1063 p_object_version_number => p_rec.object_version_number ) ;
1064 --
1065 hr_utility.set_location(' Leaving:'||l_proc, 10);
1066 End insert_validate;
1067 --
1068 -- ----------------------------------------------------------------------------
1069 -- |---------------------------< update_validate >----------------------------|
1070 -- ----------------------------------------------------------------------------
1071 Procedure update_validate
1072 (p_rec in ben_ddr_shd.g_rec_type,
1073 p_effective_date in date,
1074 p_datetrack_mode in varchar2,
1075 p_validation_start_date in date,
1076 p_validation_end_date in date) is
1077 --
1078 l_proc varchar2(72) := g_package||'update_validate';
1079 --
1080 Begin
1081 hr_utility.set_location('Entering:'||l_proc, 5);
1082 --
1083 -- Call all supporting business operations
1084 --
1085 --
1086 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1087 --
1088 chk_dsgn_rqmt_id
1089 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1090 p_effective_date => p_effective_date,
1091 p_object_version_number => p_rec.object_version_number);
1092 --
1093 chk_dsgn_typ_cd
1094 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1095 p_dsgn_typ_cd => p_rec.dsgn_typ_cd,
1096 p_effective_date => p_effective_date,
1097 p_object_version_number => p_rec.object_version_number);
1098 --
1099 chk_no_mn_dpnts_rqd_or_mn_num
1100 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1101 p_no_mn_num_dfnd_flag => p_rec.no_mn_num_dfnd_flag,
1102 p_mn_dpnts_rqd_num => p_rec.mn_dpnts_rqd_num,
1103 p_effective_date => p_effective_date,
1104 p_object_version_number => p_rec.object_version_number);
1105 --
1106 chk_no_mx_dpnts_or_mx_num_alwd
1107 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1108 p_no_mx_num_dfnd_flag => p_rec.no_mx_num_dfnd_flag,
1109 p_mx_dpnts_alwd_num => p_rec.mx_dpnts_alwd_num,
1110 p_effective_date => p_effective_date,
1111 p_object_version_number => p_rec.object_version_number);
1112 --
1113 chk_cvr_all_elig_flag
1114 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1115 p_cvr_all_elig_flag => p_rec.cvr_all_elig_flag,
1116 p_effective_date => p_effective_date,
1117 p_object_version_number => p_rec.object_version_number);
1118 --
1119 chk_no_mx_num_dfnd_flag
1120 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1121 p_no_mx_num_dfnd_flag => p_rec.no_mx_num_dfnd_flag,
1122 p_effective_date => p_effective_date,
1123 p_object_version_number => p_rec.object_version_number);
1124 --
1125 chk_no_mn_num_dfnd_flag
1126 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1127 p_no_mn_num_dfnd_flag => p_rec.no_mn_num_dfnd_flag,
1128 p_effective_date => p_effective_date,
1129 p_object_version_number => p_rec.object_version_number);
1130 --
1131 chk_grp_rlshp_cd
1132 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1133 p_grp_rlshp_cd => p_rec.grp_rlshp_cd,
1134 p_effective_date => p_effective_date,
1135 p_object_version_number => p_rec.object_version_number);
1136 --
1137 chk_pln_oipl_opt_mutexcl
1138 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
1139 p_pl_id => p_rec.pl_id,
1140 p_oipl_id => p_rec.oipl_id,
1141 p_opt_id => p_rec.opt_id,
1142 p_effective_date => p_effective_date,
1143 p_business_group_id => p_rec.business_group_id,
1144 p_object_version_number => p_rec.object_version_number);
1145 --
1146 --bug 2837189
1147 chk_dsgn_rqmt_uniq
1148 (p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id ,
1149 p_effective_date => p_effective_date ,
1150 p_business_group_id => p_rec.business_group_id ,
1151 p_grp_rlshp_cd => p_rec.grp_rlshp_cd,
1152 p_dsgn_typ_cd => p_rec.dsgn_typ_cd,
1153 p_pl_id => p_rec.pl_id,
1154 p_oipl_id => p_rec.oipl_id,
1155 p_object_version_number => p_rec.object_version_number ) ;
1156 --
1157
1158 -- Call the datetrack update integrity operation
1159 --
1160 dt_update_validate
1161 (p_oipl_id => p_rec.oipl_id,
1162 p_opt_id => p_rec.opt_id,
1163 p_pl_id => p_rec.pl_id,
1164 p_datetrack_mode => p_datetrack_mode,
1165 p_validation_start_date => p_validation_start_date,
1166 p_validation_end_date => p_validation_end_date);
1167 --
1168 hr_utility.set_location(' Leaving:'||l_proc, 10);
1169 End update_validate;
1170 --
1171 -- ----------------------------------------------------------------------------
1172 -- |---------------------------< delete_validate >----------------------------|
1173 -- ----------------------------------------------------------------------------
1174 Procedure delete_validate
1175 (p_rec in ben_ddr_shd.g_rec_type,
1176 p_effective_date in date,
1177 p_datetrack_mode in varchar2,
1178 p_validation_start_date in date,
1179 p_validation_end_date in date) is
1180 --
1181 l_proc varchar2(72) := g_package||'delete_validate';
1182 l_dummy varchar2(30);
1183 --
1184 cursor chk_dsgn_rqmt_rlshp_typ is
1185 select null
1186 from ben_dsgn_rqmt_rlshp_typ
1187 where dsgn_rqmt_id = p_rec.dsgn_rqmt_id;
1188 --
1189 Begin
1190 hr_utility.set_location('Entering:'||l_proc, 5);
1191 --
1192 --
1193 -- Check for existence of child records in BEN_DSGN_RQMT_RLSHP_TYP
1194 -- table when PURGING.
1195 --
1196 open chk_dsgn_rqmt_rlshp_typ;
1197 --
1198 fetch chk_dsgn_rqmt_rlshp_typ into l_dummy;
1199 --
1200 if chk_dsgn_rqmt_rlshp_typ%found and
1201 p_datetrack_mode = 'ZAP' then
1202 --
1203 close chk_dsgn_rqmt_rlshp_typ;
1204 fnd_message.set_name('BEN', 'BEN_91970_GRP_RLSHP_TYP');
1205 fnd_message.raise_error;
1206 --
1207 end if;
1208 --
1209 close chk_dsgn_rqmt_rlshp_typ;
1210 --
1211 -- Call all supporting business operations
1212 --
1213 dt_delete_validate
1214 (p_datetrack_mode => p_datetrack_mode,
1215 p_validation_start_date => p_validation_start_date,
1216 p_validation_end_date => p_validation_end_date,
1217 p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id);
1218 --
1219 hr_utility.set_location(' Leaving:'||l_proc, 10);
1220 End delete_validate;
1221 --
1222 --
1223 -- ---------------------------------------------------------------------------
1224 -- |---------------------< return_legislation_code >-------------------------|
1225 -- ---------------------------------------------------------------------------
1226 --
1227 function return_legislation_code
1228 (p_dsgn_rqmt_id in number) return varchar2 is
1229 --
1230 -- Declare cursor
1231 --
1232 cursor csr_leg_code is
1233 select a.legislation_code
1234 from per_business_groups a,
1235 ben_dsgn_rqmt_f b
1236 where b.dsgn_rqmt_id = p_dsgn_rqmt_id
1237 and a.business_group_id = b.business_group_id;
1238 --
1239 -- Declare local variables
1240 --
1241 l_legislation_code varchar2(150);
1242 l_proc varchar2(72) := g_package||'return_legislation_code';
1243 --
1244 begin
1245 --
1246 hr_utility.set_location('Entering:'|| l_proc, 10);
1247 --
1248 -- Ensure that all the mandatory parameter are not null
1249 --
1250 hr_api.mandatory_arg_error(p_api_name => l_proc,
1251 p_argument => 'dsgn_rqmt_id',
1252 p_argument_value => p_dsgn_rqmt_id);
1253 --
1254 open csr_leg_code;
1255 --
1256 fetch csr_leg_code into l_legislation_code;
1257 --
1258 if csr_leg_code%notfound then
1259 --
1260 close csr_leg_code;
1261 --
1262 -- The primary key is invalid therefore we must error
1263 --
1264 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1265 hr_utility.raise_error;
1266 --
1267 end if;
1268 --
1269 close csr_leg_code;
1270 --
1271 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1272 --
1273 return l_legislation_code;
1274 --
1275 end return_legislation_code;
1276 --
1277 end ben_ddr_bus;