[Home] [Help]
PACKAGE BODY: APPS.BEN_CRT_BUS
Source
1 Package Body ben_crt_bus as
2 /* $Header: becrtrhi.pkb 115.11 2004/06/22 07:52:16 rpgupta ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_crt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_crt_ordr_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 -- crt_ordr_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_crt_ordr_id(p_crt_ordr_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_crt_ordr_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_crt_shd.api_updating
47 (p_crt_ordr_id => p_crt_ordr_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_crt_ordr_id,hr_api.g_number)
52 <> ben_crt_shd.g_old_rec.crt_ordr_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_crt_shd.constraint_error('BEN_CRT_ORDR_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_crt_ordr_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_crt_shd.constraint_error('BEN_CRT_ORDR_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_crt_ordr_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_person_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_crt_ordr_id PK
89 -- p_person_id ID of FK column
90 -- p_effective_date Session Date of record
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_person_id (p_crt_ordr_id in number,
103 p_person_id in number,
104 p_effective_date in date,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_person_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from per_all_people_f a
114 where a.person_id = p_person_id
115 and p_effective_date
116 between a.effective_start_date
117 and a.effective_end_date;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_crt_shd.api_updating
124 (p_crt_ordr_id => p_crt_ordr_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_person_id,hr_api.g_number)
129 <> nvl(ben_crt_shd.g_old_rec.person_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if person_id value exists in per_all_people_f table
133 --
134 open c1;
135 --
136 fetch c1 into l_dummy;
137 if c1%notfound then
138 --
139 close c1;
140 --
141 -- raise error as FK does not relate to PK in per_all_people_f
142 -- table.
143 --
144 ben_crt_shd.constraint_error('BEN_CRT_ORDR_DT1');
145 --
146 end if;
147 --
148 close c1;
149 --
150 end if;
151 --
152 hr_utility.set_location('Leaving:'||l_proc,10);
153 --
154 End chk_person_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------< chk_pl_id >------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- (A) This procedure checks that a referenced foreign key actually exists
162 -- in the referenced table.
163 -- (B) This procedure checks that the plan selected is ACTIVE on the effective
164 -- date.
165 --
166 -- Pre-Conditions
167 -- None.
168 --
169 -- In Parameters
170 -- p_crt_ordr_id PK
171 -- p_pl_id ID of FK column
172 -- p_effective_date Session Date of record
173 -- p_object_version_number object version number
174 --
175 -- Post Success
176 -- Processing continues
177 --
178 -- Post Failure
179 -- Error raised.
180 --
181 -- Access Status
182 -- Internal table handler use only.
183 --
184 Procedure chk_pl_id (p_crt_ordr_id in number,
185 p_pl_id in number,
186 p_effective_date in date,
187 p_object_version_number in number) is
188 --
189 l_proc varchar2(72) := g_package||'chk_pl_id';
190 l_api_updating boolean;
191 l_dummy varchar2(1);
192 --
193 cursor c1 is
194 select null
195 from ben_pl_f a
196 where a.pl_id = p_pl_id
197 and p_effective_date
198 between a.effective_start_date
199 and a.effective_end_date;
200
201 cursor c2 is
202 select null
203 from ben_pl_F pln
204 where pln.pl_id = p_pl_id
205 and p_effective_date
206 between pln.effective_start_date
207 and pln.effective_end_date
208 and pln.pl_stat_cd = 'A';
209 --
210 Begin
211 --
212 hr_utility.set_location('Entering:'||l_proc,5);
213 --
214 l_api_updating := ben_crt_shd.api_updating
215 (p_crt_ordr_id => p_crt_ordr_id,
216 p_object_version_number => p_object_version_number);
217 --
218 if (l_api_updating
219 and nvl(p_pl_id,hr_api.g_number)
220 <> nvl(ben_crt_shd.g_old_rec.pl_id,hr_api.g_number)
221 or not l_api_updating)
222 and p_pl_id is not null then
223 --
224 -- check if pl_id value exists in ben_pl_f table
225 --
226 open c1;
227 --
228 fetch c1 into l_dummy;
229 if c1%notfound then
230 --
231 close c1;
232 --
233 -- raise error as FK does not relate to PK in ben_pl_f
234 -- table.
235 --
236 ben_crt_shd.constraint_error('BEN_CRT_ORDR_DT2');
237 --
238 end if;
239 --
240 close c1;
241 --
242 -- Bug : 3616686
243 -- check if selected plan is active
244 --
245 open c2;
246 --
247 fetch c2 into l_dummy;
248 if c2%notfound then
249 --
250 close c2;
251 --
252 -- Raise error as inactive plan is selected
253 --
254 fnd_message.set_name('BEN','BEN_93965_SELECT_ACTIVE_PLAN');
255 fnd_message.raise_error;
256 --
257 end if;
258 --
259 close c2;
260 --
261 end if;
262 --
263 hr_utility.set_location('Leaving:'||l_proc,10);
264 --
265 End chk_pl_id;
266 --
267 -- ----------------------------------------------------------------------------
268 -- |--------------------< chk_strt_dt_precedes_end_dt>------------------------|
269 -- ----------------------------------------------------------------------------
270 --
271 -- Description
272 -- This procedure is used to check the start date and end date
273 -- are entered properly. The following condition will be checked:
274 -- * Start date always precedes End date.
275 -- Bug : 3616686
276 --
277 -- Pre Conditions
278 -- None.
279 --
280 -- In Parameters
281 -- p_Start Date - Program/Plan year period's Start Date
282 -- p_End Date - Program/Plan year period End Date
283 --
284 --
285 Procedure chk_strt_dt_precedes_end_dt(p_start_date in date,
286 p_end_date in date ) is
287 --
288 l_proc varchar2(72) := g_package||'chk_strt_dt_precedes_end_dt';
289 --
290 Begin
291 --
292 hr_utility.set_location('Entering:'||l_proc, 5);
293
294 if p_start_Date is not null and p_end_date is not null and p_start_date > p_end_date then
295 --
296 -- raise error Start Date must precede End date
297 --
298 fnd_message.set_name('BEN','BEN_92688_RT_STRT_DT_GT_END_DT'); -- 3709010
299 hr_utility.set_message_token('START',to_char(p_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
300 hr_utility.set_message_token('END',to_char(p_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
301 fnd_message.raise_error;
302 --
303 end if;
304 --
305 hr_utility.set_location('Leaving:'||l_proc, 10);
306 --
307 End chk_strt_dt_precedes_end_dt;
308 --
309 --
310 -- ----------------------------------------------------------------------------
311 -- |------< chk_pl_typ_id >------|
312 -- ----------------------------------------------------------------------------
313 --
314 -- Description
315 -- This procedure checks that a referenced foreign key actually exists
316 -- in the referenced table.
317 --
318 -- Pre-Conditions
319 -- None.
320 --
321 -- In Parameters
322 -- p_crt_ordr_id PK
323 -- p_pl_typ_id ID of FK column
324 -- p_effective_date Session Date of record
325 -- p_object_version_number object version number
326 --
327 -- Post Success
328 -- Processing continues
329 --
330 -- Post Failure
331 -- Error raised.
332 --
333 -- Access Status
334 -- Internal table handler use only.
335 --
336 Procedure chk_pl_typ_id (p_crt_ordr_id in number,
337 p_pl_typ_id in number,
338 p_effective_date in date,
339 p_object_version_number in number) is
340 --
341 l_proc varchar2(72) := g_package||'chk_pl_typ_id';
342 l_api_updating boolean;
343 l_dummy varchar2(1);
344 --
345 cursor c1 is
346 select null
347 from ben_pl_typ_f a
348 where a.pl_typ_id = p_pl_typ_id
349 and p_effective_date
350 between a.effective_start_date
351 and a.effective_end_date;
352 --
353 Begin
354 --
355 hr_utility.set_location('Entering:'||l_proc,5);
356 --
357 l_api_updating := ben_crt_shd.api_updating
358 (p_crt_ordr_id => p_crt_ordr_id,
359 p_object_version_number => p_object_version_number);
360 --
361 if (l_api_updating
362 and nvl(p_pl_typ_id,hr_api.g_number)
363 <> nvl(ben_crt_shd.g_old_rec.pl_typ_id,hr_api.g_number)
364 or not l_api_updating)
365 and p_pl_typ_id is not null then
366 --
367 -- check if pl_typ_id value exists in ben_pl_typ_f table
368 --
369 open c1;
370 --
371 fetch c1 into l_dummy;
372 if c1%notfound then
373 --
374 close c1;
375 --
376 -- raise error as FK does not relate to PK in ben_pl_typ_f
377 -- table.
378 --
379 ben_crt_shd.constraint_error('BEN_CRT_ORDR_DT3');
380 --
381 end if;
382 --
383 close c1;
384 --
385 end if;
386 --
387 hr_utility.set_location('Leaving:'||l_proc,10);
388 --
389 End chk_pl_typ_id;
390 --
391 -- ----------------------------------------------------------------------------
392 -- |------< chk_pl_pltyp_id >------|
393 -- ----------------------------------------------------------------------------
394 --
395 -- Description
396 -- This procedure enforces the arc relationship
397 --
398 -- Pre-Conditions
399 -- None.
400 --
401 -- In Parameters
402 -- p_pl_typ_id
403 -- p_pl_id ID
404 --
405 -- Post Success
406 -- Processing continues
407 --
408 -- Post Failure
409 -- Error raised.
410 --
411 -- Access Status
412 -- Internal table handler use only.
413 --
414 Procedure chk_pl_pltyp_id ( p_pl_id in number,
415 p_pl_typ_id in number) is
416 --
417 l_proc varchar2(72) := g_package||'chk_pl_pltyp_id';
418 --
419 Begin
420 --
421 hr_utility.set_location('Entering:'||l_proc,5);
422 --
423 if p_pl_id is null and
424 p_pl_typ_id is null then
425 --
426 -- raise error as at least one must exist
427 fnd_message.set_name('BEN','BEN_92398_PL_PLTYP_BNULL');
428 fnd_message.raise_error;
429 --
430 --
431 elsif p_pl_id is not null and
432 p_pl_typ_id is not null then
433 --
434 -- raise error as only one may exist
435 fnd_message.set_name('BEN','BEN_92399_PL_PLTYP_BVAL');
436 fnd_message.raise_error;
437 end if;
438 --
439 hr_utility.set_location('Leaving:'||l_proc,10);
440 --
441 End chk_pl_pltyp_id;
442 --
446 --
443 -- ----------------------------------------------------------------------------
444 -- |------< chk_qdro_dstr_mthd_cd >------|
445 -- ----------------------------------------------------------------------------
447 -- Description
448 -- This procedure is used to check that the lookup value is valid.
449 --
450 -- Pre Conditions
451 -- None.
452 --
453 -- In Parameters
454 -- crt_ordr_id PK of record being inserted or updated.
455 -- qdro_dstr_mthd_cd Value of lookup code.
456 -- effective_date effective date
457 -- object_version_number Object version number of record being
458 -- inserted or updated.
459 --
460 -- Post Success
461 -- Processing continues
462 --
463 -- Post Failure
464 -- Error handled by procedure
465 --
466 -- Access Status
467 -- Internal table handler use only.
468 --
469 Procedure chk_qdro_dstr_mthd_cd(p_crt_ordr_id in number,
470 p_qdro_dstr_mthd_cd in varchar2,
471 p_effective_date in date,
472 p_object_version_number in number) is
473 --
474 l_proc varchar2(72) := g_package||'chk_qdro_dstr_mthd_cd';
475 l_api_updating boolean;
476 --
477 Begin
478 --
479 hr_utility.set_location('Entering:'||l_proc, 5);
480 --
481 l_api_updating := ben_crt_shd.api_updating
482 (p_crt_ordr_id => p_crt_ordr_id,
483 p_object_version_number => p_object_version_number);
484 --
485 if (l_api_updating
486 and p_qdro_dstr_mthd_cd
487 <> nvl(ben_crt_shd.g_old_rec.qdro_dstr_mthd_cd,hr_api.g_varchar2)
488 or not l_api_updating)
489 and p_qdro_dstr_mthd_cd is not null then
490 --
491 -- check if value of lookup falls within lookup type.
492 --
493 --
494 if hr_api.not_exists_in_hr_lookups
495 (p_lookup_type => 'BEN_QDRO_DSTR_MTHD',
496 p_lookup_code => p_qdro_dstr_mthd_cd,
497 p_effective_date => p_effective_date) then
498 --
499 -- raise error as does not exist as lookup
500 --
501 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
502 fnd_message.set_token('FIELD','p_qdro_dstr_mthd_cd');
503 fnd_message.set_token('TYPE','BEN_QDRO_DSTR_MTHD');
504 fnd_message.raise_error;
505 --
506 end if;
507 --
508 end if;
509 --
510 hr_utility.set_location('Leaving:'||l_proc,10);
511 --
512 end chk_qdro_dstr_mthd_cd;
513 --
514 -- ----------------------------------------------------------------------------
515 -- |------< chk_qdro_per_perd_cd >------|
516 -- ----------------------------------------------------------------------------
517 --
518 -- Description
519 -- This procedure is used to check that the lookup value is valid.
520 --
521 -- Pre Conditions
522 -- None.
523 --
524 -- In Parameters
525 -- crt_ordr_id PK of record being inserted or updated.
526 -- qdro_per_perd_cd Value of lookup code.
527 -- effective_date effective date
528 -- object_version_number Object version number of record being
529 -- inserted or updated.
530 --
531 -- Post Success
532 -- Processing continues
533 --
534 -- Post Failure
535 -- Error handled by procedure
536 --
537 -- Access Status
538 -- Internal table handler use only.
539 --
540 Procedure chk_qdro_per_perd_cd(p_crt_ordr_id in number,
541 p_qdro_per_perd_cd in varchar2,
542 p_effective_date in date,
543 p_object_version_number in number) is
544 --
545 l_proc varchar2(72) := g_package||'chk_qdro_per_perd_cd';
546 l_api_updating boolean;
547 --
548 Begin
549 --
550 hr_utility.set_location('Entering:'||l_proc, 5);
551 --
552 l_api_updating := ben_crt_shd.api_updating
553 (p_crt_ordr_id => p_crt_ordr_id,
554 p_object_version_number => p_object_version_number);
555 --
556 if (l_api_updating
557 and p_qdro_per_perd_cd
558 <> nvl(ben_crt_shd.g_old_rec.qdro_per_perd_cd,hr_api.g_varchar2)
559 or not l_api_updating)
560 and p_qdro_per_perd_cd is not null then
561 --
562 -- check if value of lookup falls within lookup type.
563 --
564 --
565 if hr_api.not_exists_in_hr_lookups
566 (p_lookup_type => 'BEN_BNDRY_PERD',
567 p_lookup_code => p_qdro_per_perd_cd,
568 p_effective_date => p_effective_date) then
569 --
570 -- raise error as does not exist as lookup
571 --
572 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
573 fnd_message.set_token('FIELD','p_qdro_per_perd_cd');
574 fnd_message.set_token('TYPE','BEN_BNDRY_PERD');
575 fnd_message.raise_error;
576 --
577 end if;
578 --
579 end if;
580 --
581 hr_utility.set_location('Leaving:'||l_proc,10);
582 --
583 end chk_qdro_per_perd_cd;
584 --
588 --
585 -- ----------------------------------------------------------------------------
586 -- |------< chk_crt_ordr_typ_cd >------|
587 -- ----------------------------------------------------------------------------
589 -- Description
590 -- This procedure is used to check that the lookup value is valid.
591 --
592 -- Pre Conditions
593 -- None.
594 --
595 -- In Parameters
596 -- crt_ordr_id PK of record being inserted or updated.
597 -- crt_ordr_typ_cd Value of lookup code.
598 -- effective_date effective date
599 -- object_version_number Object version number of record being
600 -- inserted or updated.
601 --
602 -- Post Success
603 -- Processing continues
604 --
605 -- Post Failure
606 -- Error handled by procedure
607 --
608 -- Access Status
609 -- Internal table handler use only.
610 --
611 Procedure chk_crt_ordr_typ_cd(p_crt_ordr_id in number,
612 p_crt_ordr_typ_cd in varchar2,
613 p_effective_date in date,
614 p_object_version_number in number) is
615 --
616 l_proc varchar2(72) := g_package||'chk_crt_ordr_typ_cd';
617 l_api_updating boolean;
618 --
619 Begin
620 --
621 hr_utility.set_location('Entering:'||l_proc, 5);
622 --
623 l_api_updating := ben_crt_shd.api_updating
624 (p_crt_ordr_id => p_crt_ordr_id,
625 p_object_version_number => p_object_version_number);
626 --
627 if (l_api_updating
628 and p_crt_ordr_typ_cd
629 <> nvl(ben_crt_shd.g_old_rec.crt_ordr_typ_cd,hr_api.g_varchar2)
630 or not l_api_updating) then
631 --
632 -- check if value of lookup falls within lookup type.
633 --
634 --
635 if hr_api.not_exists_in_hr_lookups
636 (p_lookup_type => 'BEN_CRT_ORDR_TYP',
637 p_lookup_code => p_crt_ordr_typ_cd,
638 p_effective_date => p_effective_date) then
639 --
640 -- raise error as does not exist as lookup
641 --
642 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
643 fnd_message.set_token('FIELD','p_crt_ordr_typ_cd');
644 fnd_message.set_token('TYPE','BEN_CRT_ORDR_TYP');
645 fnd_message.raise_error;
646 --
647 end if;
648 --
649 end if;
650 --
651 hr_utility.set_location('Leaving:'||l_proc,10);
652 --
653 end chk_crt_ordr_typ_cd;
654 --
655 --
656 -- ----------------------------------------------------------------------------
657 -- |------< chk_rqd_by_typ >------|
658 -- ----------------------------------------------------------------------------
659 --
660 -- Description
661 -- This procedure is used to check that based on what type is used,
662 -- certain fields are required. p_apls_perd_strtg_dt is required.
663 -- If type is QDRO then qdro_amt and
664 -- uom are required OR qdro_pct is required.
665 --
666 -- Pre Conditions
667 -- None.
668 --
669 -- In Parameters
670 -- crt_ordr_id PK of record being inserted or updated.
671 -- crt_ordr_typ_cd Value of lookup code.
672 -- effective_date effective date
673 -- object_version_number Object version number of record being
674 -- inserted or updated.
675 --
676 -- Post Success
677 -- Processing continues
678 --
679 -- Post Failure
680 -- Error handled by procedure
681 --
682 -- Access Status
683 -- Internal table handler use only.
684 --
685 Procedure chk_rqd_by_typ(p_crt_ordr_id in number,
686 p_crt_ordr_typ_cd in varchar2,
687 p_apls_perd_strtg_dt in date,
688 p_apls_perd_endg_dt in date,
689 p_qdro_amt in number,
690 p_qdro_pct in number,
691 p_uom in varchar2,
692 p_effective_date in date,
693 p_object_version_number in number) is
694 --
695 l_proc varchar2(72) := g_package||'chk_rqd_by_typ';
696 l_api_updating boolean;
697 --
698 Begin
699 --
700 hr_utility.set_location('Entering:'||l_proc, 5);
701 --
702 l_api_updating := ben_crt_shd.api_updating
703 (p_crt_ordr_id => p_crt_ordr_id,
704 p_object_version_number => p_object_version_number);
705 --
706 If p_apls_perd_strtg_dt is null then
707 fnd_message.set_name('BEN','BEN_91977_DT_RQD_CRTORDR');
708 fnd_message.raise_error;
709 End If;
710
711 If p_crt_ordr_typ_cd = 'QDRO' then
712 If p_qdro_amt is null and p_qdro_pct is null then
713 fnd_message.set_name('BEN','BEN_91978_AMT_PCT_RQD_QDRO');
714 fnd_message.raise_error;
715 elsif p_qdro_amt is not null then
716 If p_uom is null then
717 fnd_message.set_name('BEN','BEN_91979_AMT_UOM_RQD_QDRO');
718 fnd_message.raise_error;
719 End If;
720 If p_qdro_pct is not null then
724 elsif p_qdro_pct is not null then
721 fnd_message.set_name('BEN','BEN_91980_AMT_PCT_MUTEXCL');
722 fnd_message.raise_error;
723 End If;
725 If p_qdro_amt is not null or p_uom is not null then
726 fnd_message.set_name('BEN','BEN_91981_PCT_AMTUOM_MUTEXCL');
727 fnd_message.raise_error;
728 End if;
729 End if;
730 End if;
731
732 --
733 hr_utility.set_location('Leaving:'||l_proc,10);
734 --
735 end chk_rqd_by_typ;
736 --
737 -- ----------------------------------------------------------------------------
738 -- |---------------------------< insert_validate >----------------------------|
739 -- ----------------------------------------------------------------------------
740 Procedure insert_validate(p_rec in ben_crt_shd.g_rec_type
741 ,p_effective_date in date) is
742 --
743 l_proc varchar2(72) := g_package||'insert_validate';
744 --
745 Begin
746 hr_utility.set_location('Entering:'||l_proc, 5);
747 --
748 -- Call all supporting business operations
749 --
750 --
751 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
752 --
753 chk_crt_ordr_id
754 (p_crt_ordr_id => p_rec.crt_ordr_id,
755 p_object_version_number => p_rec.object_version_number);
756 --
757 chk_pl_id
758 (p_crt_ordr_id => p_rec.crt_ordr_id,
759 p_pl_id => p_rec.pl_id,
760 p_effective_date => p_effective_date,
761 p_object_version_number => p_rec.object_version_number);
762 --
763 chk_pl_typ_id
764 (p_crt_ordr_id => p_rec.crt_ordr_id,
765 p_pl_typ_id => p_rec.pl_typ_id,
766 p_effective_date => p_effective_date,
767 p_object_version_number => p_rec.object_version_number);
768 --
769 chk_person_id
770 (p_crt_ordr_id => p_rec.crt_ordr_id,
771 p_person_id => p_rec.person_id,
772 p_effective_date => p_effective_date,
773 p_object_version_number => p_rec.object_version_number);
774 --
775 chk_pl_pltyp_id
776 (p_pl_id => p_rec.pl_id,
777 p_pl_typ_id => p_rec.pl_typ_id);
778 --
779 chk_qdro_per_perd_cd
780 (p_crt_ordr_id => p_rec.crt_ordr_id,
781 p_qdro_per_perd_cd => p_rec.qdro_per_perd_cd,
782 p_effective_date => p_effective_date,
783 p_object_version_number => p_rec.object_version_number);
784 --
785 chk_qdro_dstr_mthd_cd
786 (p_crt_ordr_id => p_rec.crt_ordr_id,
787 p_qdro_dstr_mthd_cd => p_rec.qdro_dstr_mthd_cd,
788 p_effective_date => p_effective_date,
789 p_object_version_number => p_rec.object_version_number);
790 --
791 chk_crt_ordr_typ_cd
792 (p_crt_ordr_id => p_rec.crt_ordr_id,
793 p_crt_ordr_typ_cd => p_rec.crt_ordr_typ_cd,
794 p_effective_date => p_effective_date,
795 p_object_version_number => p_rec.object_version_number);
796 --
797 chk_rqd_by_typ
798 (p_crt_ordr_id => p_rec.crt_ordr_id,
799 p_crt_ordr_typ_cd => p_rec.crt_ordr_typ_cd,
800 p_apls_perd_strtg_dt => p_rec.apls_perd_strtg_dt,
801 p_apls_perd_endg_dt => p_rec.apls_perd_endg_dt,
802 p_qdro_amt => p_rec.qdro_amt,
803 p_qdro_pct => p_rec.qdro_pct,
804 p_uom => p_rec.uom,
805 p_effective_date => p_effective_date,
806 p_object_version_number => p_rec.object_version_number);
807 --
808 chk_strt_dt_precedes_end_dt
809 (p_start_date => p_rec.apls_perd_strtg_dt,
810 p_end_date => p_rec.apls_perd_endg_dt);
811 --
812 hr_utility.set_location(' Leaving:'||l_proc, 10);
813 End insert_validate;
814 --
815 -- ----------------------------------------------------------------------------
816 -- |---------------------------< update_validate >----------------------------|
817 -- ----------------------------------------------------------------------------
818 Procedure update_validate(p_rec in ben_crt_shd.g_rec_type
819 ,p_effective_date in date) is
820 --
821 l_proc varchar2(72) := g_package||'update_validate';
822 --
823 Begin
824 hr_utility.set_location('Entering:'||l_proc, 5);
825 --
826 -- Call all supporting business operations
827 --
828 --
829 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
830 --
831 chk_crt_ordr_id
832 (p_crt_ordr_id => p_rec.crt_ordr_id,
833 p_object_version_number => p_rec.object_version_number);
834 --
835 chk_pl_id
836 (p_crt_ordr_id => p_rec.crt_ordr_id,
837 p_pl_id => p_rec.pl_id,
838 p_effective_date => p_effective_date,
839 p_object_version_number => p_rec.object_version_number);
840 --
841 chk_pl_typ_id
842 (p_crt_ordr_id => p_rec.crt_ordr_id,
843 p_pl_typ_id => p_rec.pl_typ_id,
844 p_effective_date => p_effective_date,
845 p_object_version_number => p_rec.object_version_number);
846 --
847 chk_person_id
848 (p_crt_ordr_id => p_rec.crt_ordr_id,
849 p_person_id => p_rec.person_id,
853 chk_pl_pltyp_id
850 p_effective_date => p_effective_date,
851 p_object_version_number => p_rec.object_version_number);
852 --
854 (p_pl_id => p_rec.pl_id,
855 p_pl_typ_id => p_rec.pl_typ_id);
856 --
857 chk_qdro_per_perd_cd
858 (p_crt_ordr_id => p_rec.crt_ordr_id,
859 p_qdro_per_perd_cd => p_rec.qdro_per_perd_cd,
860 p_effective_date => p_effective_date,
861 p_object_version_number => p_rec.object_version_number);
862 --
863 chk_qdro_dstr_mthd_cd
864 (p_crt_ordr_id => p_rec.crt_ordr_id,
865 p_qdro_dstr_mthd_cd => p_rec.qdro_dstr_mthd_cd,
866 p_effective_date => p_effective_date,
867 p_object_version_number => p_rec.object_version_number);
868 --
869 chk_crt_ordr_typ_cd
870 (p_crt_ordr_id => p_rec.crt_ordr_id,
871 p_crt_ordr_typ_cd => p_rec.crt_ordr_typ_cd,
872 p_effective_date => p_effective_date,
873 p_object_version_number => p_rec.object_version_number);
874 --
875 chk_rqd_by_typ
876 (p_crt_ordr_id => p_rec.crt_ordr_id,
877 p_crt_ordr_typ_cd => p_rec.crt_ordr_typ_cd,
878 p_apls_perd_strtg_dt => p_rec.apls_perd_strtg_dt,
879 p_apls_perd_endg_dt => p_rec.apls_perd_endg_dt,
880 p_qdro_amt => p_rec.qdro_amt,
881 p_qdro_pct => p_rec.qdro_pct,
882 p_uom => p_rec.uom,
883 p_effective_date => p_effective_date,
884 p_object_version_number => p_rec.object_version_number);
885 --
886 chk_strt_dt_precedes_end_dt
887 (p_start_date => p_rec.apls_perd_strtg_dt,
888 p_end_date => p_rec.apls_perd_endg_dt);
889 --
890 hr_utility.set_location(' Leaving:'||l_proc, 10);
891 End update_validate;
892 --
893 -- ----------------------------------------------------------------------------
894 -- |---------------------------< delete_validate >----------------------------|
895 -- ----------------------------------------------------------------------------
896 Procedure delete_validate(p_rec in ben_crt_shd.g_rec_type
897 ,p_effective_date in date) is
898 --
899 l_proc varchar2(72) := g_package||'delete_validate';
900 --
901 Begin
902 hr_utility.set_location('Entering:'||l_proc, 5);
903 --
904 -- Call all supporting business operations
905 --
906 hr_utility.set_location(' Leaving:'||l_proc, 10);
907 End delete_validate;
908 --
909 --
910 -- ---------------------------------------------------------------------------
911 -- |---------------------< return_legislation_code >-------------------------|
912 -- ---------------------------------------------------------------------------
913 --
914 function return_legislation_code
915 (p_crt_ordr_id in number) return varchar2 is
916 --
917 -- Declare cursor
918 --
919 cursor csr_leg_code is
920 select a.legislation_code
921 from per_business_groups a,
922 ben_crt_ordr b
923 where b.crt_ordr_id = p_crt_ordr_id
924 and a.business_group_id = b.business_group_id;
925 --
926 -- Declare local variables
927 --
928 l_legislation_code varchar2(150);
929 l_proc varchar2(72) := g_package||'return_legislation_code';
930 --
931 begin
932 --
933 hr_utility.set_location('Entering:'|| l_proc, 10);
934 --
935 -- Ensure that all the mandatory parameter are not null
936 --
937 hr_api.mandatory_arg_error(p_api_name => l_proc,
938 p_argument => 'crt_ordr_id',
939 p_argument_value => p_crt_ordr_id);
940 --
941 open csr_leg_code;
942 --
943 fetch csr_leg_code into l_legislation_code;
944 --
945 if csr_leg_code%notfound then
946 --
947 close csr_leg_code;
948 --
949 -- The primary key is invalid therefore we must error
950 --
951 fnd_message.set_name('BEN','HR_7220_INVALID_PRIMARY_KEY');
952 fnd_message.raise_error;
953 --
954 end if;
955 --
956 close csr_leg_code;
957 --
958 hr_utility.set_location(' Leaving:'|| l_proc, 20);
959 --
960 return l_legislation_code;
961 --
962 end return_legislation_code;
963 --
964 end ben_crt_bus;