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