[Home] [Help]
PACKAGE BODY: APPS.BEN_EPT_BUS
Source
1 Package Body ben_ept_bus as
2 /* $Header: beeptrhi.pkb 120.1 2006/03/13 05:03:41 rgajula noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ept_bus.'; -- Global package name
9
10
11 Procedure chk_valid_fctr (p_eligy_prfl_id in number
12 ,p_person_type_id in number) is
13 --
14 l_proc varchar2(72) := g_package||'chk_valid_fctr';
15 l_person_type_cd varchar2(30);
16 l_dummy varchar2(1);
17 --
18 -- Bug:1631182 - Not supporing per_typ_cd, instead use person_type_id for
19 -- supporting user created person types
20 --
21
22 /*--
23 cursor c1 is
24 select null
25 from ben_elig_per_typ_prte_f b
26 where b.eligy_prfl_id = p_eligy_prfl_id
27 and b.per_typ_cd NOT IN ('EMP', 'EMP_APL')
28 and b.excld_flag = 'N';
29 -- */
30 --
31 cursor c1 is
32 select null
33 from ben_elig_comp_lvl_prte_f b
34 where b.eligy_prfl_id = p_eligy_prfl_id;
35
36 cursor c2 is
37 select ppt.system_person_type
38 from per_person_types ppt
39 where ppt.person_type_id = p_person_type_id;
40
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc,5);
45 --
46 open c2;
47 fetch c2 into l_person_type_cd;
48 close c2;
49
50 if (l_person_type_cd not in ('EMP', 'EMP_APL')) then
51
52 open c1;
53 --
54 fetch c1 into l_dummy;
55 if c1%found then
56 --
57 close c1;
58 --
59 -- raise error as this eligibility factor is invalid
60 -- create generic message here
61 --
62 fnd_message.set_name('BEN', 'BEN_94611_INV_FCTR');
63 --fnd_message.set_token('PROCEDURE', l_proc);
64 --fnd_message.set_token('STEP','5');
65 fnd_message.raise_error;
66 --
67 end if;
68 --
69 close c1;
70 --
71 --
72 end if;
73
74 hr_utility.set_location('Leaving:'||l_proc,10);
75 --
76 End chk_valid_fctr;
77 --
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_elig_per_typ_prte_id >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the primary key for the table
85 -- is created properly. It should be null on insert and
86 -- should not be able to be updated.
87 --
88 -- Pre Conditions
89 -- None.
90 --
91 -- In Parameters
92 -- elig_per_typ_prte_id PK of record being inserted or updated.
93 -- effective_date Effective Date of session
94 -- object_version_number Object version number of record being
95 -- inserted or updated.
96 --
97 -- Post Success
98 -- Processing continues
99 --
100 -- Post Failure
101 -- Errors handled by the procedure
102 --
103 -- Access Status
104 -- Internal table handler use only.
105 --
106 Procedure chk_elig_per_typ_prte_id(p_elig_per_typ_prte_id in number,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_elig_per_typ_prte_id';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := ben_ept_shd.api_updating
118 (p_effective_date => p_effective_date,
119 p_elig_per_typ_prte_id => p_elig_per_typ_prte_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_elig_per_typ_prte_id,hr_api.g_number)
124 <> ben_ept_shd.g_old_rec.elig_per_typ_prte_id) then
125 --
126 -- raise error as PK has changed
127 --
128 ben_ept_shd.constraint_error('BEN_ELIG_PER_TYP_PK');
129 --
130 elsif not l_api_updating then
131 --
132 -- check if PK is null
133 --
134 if p_elig_per_typ_prte_id is not null then
135 --
136 -- raise error as PK is not null
137 --
138 ben_ept_shd.constraint_error('BEN_ELIG_PER_TYP_PK');
139 --
140 end if;
141 --
142 end if;
143 --
144 hr_utility.set_location('Leaving:'||l_proc, 10);
145 --
146 End chk_elig_per_typ_prte_id;
147 --
148 --
149 /*-- ----------------------------------------------------------------------------
150 -- |------< chk_person_type_id >------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- Description
154 -- This procedure checks that a referenced foreign key actually exists
155 -- in the referenced table.
156 -- Additionally this procedure will check that person_type_id is unique
157 -- within the Eligibility profile.
158 -- This procedure will also check that Per type of 'not Employee'
159 -- can not be set for any profile for which the following eligibility
160 -- factors are present: grade, assignment set, org unit, barg unit,
161 -- percent full time, job, ....
162 --
163 -- Pre-Conditions
164 -- None.
165 --
166 -- In Parameters
167 -- p_elig_per_typ_prte_id PK
168 -- p_per_typ_cd ID of FK column
169 -- p_eligy_prfl_id
170 -- p_business_group_id
171 -- p_effective_date session date
172 -- p_object_version_number object version number
173 --
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 -- Not supporting per_typ_cd
185 --
186 Procedure chk_person_type_id (p_elig_per_typ_prte_id in number,
187 --p_per_typ_cd in varchar2,
188 p_eligy_prfl_id in number,
189 p_excld_flag in varchar2,
190 p_validation_start_date in date,
191 p_validation_end_date in date,
192 p_business_group_id in number,
193 p_effective_date in date,
194 p_object_version_number in number) is
195 --
196 l_proc varchar2(72) := g_package||'chk_person_type_id';
197 l_api_updating boolean;
198 l_system_person_type varchar2(30);
199 l_dummy varchar2(1);
200 l_exists varchar2(1);
201 --
202 --
203 cursor c3 is
204 select null
205 from ben_elig_per_typ_prte_f
206 where per_typ_cd = p_per_typ_cd
207 and eligy_prfl_id = p_eligy_prfl_id
208 and elig_per_typ_prte_id <> nvl(p_elig_per_typ_prte_id,hr_api.g_number)
209 and business_group_id + 0 = p_business_group_id
210 and p_validation_start_date <= effective_end_date
211 and p_validation_end_date >= effective_start_date
212 ;
213
214 --
215 cursor c1 is
216 select system_person_type
217 from per_person_types a
218 where a.person_type_id =to_number( p_per_typ_cd)
219 and a.business_group_id = p_business_group_id;
220 --
221 cursor c2 is
222 select null from ben_elig_asnt_set_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
223 union
224 select null from ben_elig_grd_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
225 union
226 select null from ben_elig_org_unit_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
227 union
228 select null from ben_elig_brgng_unit_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
229 union
230 select null from ben_elig_pct_fl_tm_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
231 union
232 select null from ben_elig_hrly_slrd_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
233 union
234 select null from ben_elig_comp_lvl_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
235 union
236 select null from ben_elig_los_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
237 union
238 select null from ben_elig_cmbn_age_los_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
239 union
240 select null from ben_elig_loa_rsn_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
241 union
242 select null from ben_elig_hrs_wkd_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
243 union
244 select null from ben_elig_wk_loc_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
245 union
246 select null from ben_elig_lbr_mmbr_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
247 union
248 select null from ben_elig_pyrl_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
249 union
250 select null from ben_elig_schedd_hrs_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id
251 union
252 select null from ben_elig_py_bss_prte_f a where a.eligy_prfl_id = p_eligy_prfl_id;
253 --
254 Begin
255 --
256 hr_utility.set_location('Entering:'||l_proc,5);
257 --
258 l_api_updating := ben_ept_shd.api_updating
259 (p_elig_per_typ_prte_id => p_elig_per_typ_prte_id,
260 p_effective_date => p_effective_date,
261 p_object_version_number => p_object_version_number);
262 --
263 if (l_api_updating
264 and nvl(p_per_typ_cd,hr_api.g_number)
265 <> nvl(ben_ept_shd.g_old_rec.per_typ_cd,hr_api.g_number)
266 or not l_api_updating) then
267 --
268 -- check if per_typ_cd value exists in per_person_types table
269 --
270 open c1;
271 --
272 fetch c1 into l_system_person_type;
273 if c1%notfound then
274 --
275 close c1;
276 --
277 -- raise error as FK does not relate to PK in per_person_types
278 -- table.
279 --
280 ben_ept_shd.constraint_error('BEN_ELIG_PER_TYP_PRTE_FK2');
281 --
282 end if;
283 --
284 close c1;
285 --
286 --
287 open c3;
288 fetch c3 into l_exists;
289 if c3%found then
290 close c3;
291 --
292 -- raise error as this per type already exists for this profile
293 --
294 fnd_message.set_name('BEN', 'BEN_91349_DUP_ELIG_CRITERIA');
295 fnd_message.raise_error;
296 --
297 end if;
298 close c3;
299 --
300 -- additionally check rules for not Employee
301 --
302 if (l_system_person_type <> 'EMP' and l_system_person_type <> 'EMP_APL'
303 and p_excld_flag = 'N') then
304 open c2;
305 fetch c2 into l_dummy;
306 if c2%found then
307 --
308 close c2;
309 --
310 -- raise error as person type can not be set to not employee
311 -- need to create message
312 -- ben_ept_shd.constraint_error('BEN_ELIG_PER_TYP_PRTE_FK2');
313 fnd_message.set_name('BEN', 'BEN_91387_INV_PER_TYPE');
314 fnd_message.raise_error;
315 --
316 end if;
317 --
318 close c2;
319 end if;
320 --
321 end if;
322 --
323 hr_utility.set_location('Leaving:'||l_proc,10);
324 --
325 End chk_person_type_id;
326 --*/
327 -- ----------------------------------------------------------------------------
328 -- |------< chk_per_typ_cd >---------|
329 -- ----------------------------------------------------------------------------
330 --
331 -- Description
332 -- This procedure is used to check that the lookup value is valid.
333 --
334 -- Pre Conditions
335 -- None.
336 --
337 -- In Parameters
338 -- elig_per_typ_prte_id PK of record being inserted or updated.
339 -- per_typ_cd Value of lookup code.
340 -- effective_date effective date
341 -- object_version_number Object version number of record being
342 -- inserted or updated.
343 --
344 -- Post Success
345 -- Processing continues
346 --
347 -- Post Failure
348 -- Error handled by procedure
349 --
350 -- Access Status
351 -- Internal table handler use only.
352 --
353 -- Not supporting per_typ_cd, instead use person_type_id
354 --
355 Procedure chk_per_typ_cd(p_elig_per_typ_prte_id in number,
356 --p_per_typ_cd in varchar2,
357 p_person_type_id in number,
358 p_business_group_id in number,
359 p_effective_date in date,
360 p_object_version_number in number) is
361 --
362 l_proc varchar2(72) := g_package||'chk_per_typ_cd';
363 l_api_updating boolean;
364 l_dummy varchar2(1);
365 --
366 -- Bug 1631182 - To support user created person types needed to use
367 -- person_type_id insted of per_typ_cd
368 --
369 /* --
370 cursor c1 is
371 select null
372 from per_person_types ppt
373 where ppt.system_person_type = p_per_typ_cd
374 and ppt.active_flag = 'Y'
375 and ppt.business_group_id+0 = p_business_group_id;
376 -- */
377 --
378 cursor c1 is
379 select null
380 from per_person_types ppt
381 where ppt.person_type_id = p_person_type_id
382 and ppt.active_flag = 'Y'
383 and ppt.business_group_id+0 = p_business_group_id;
384 --
385 Begin
386 --
387 hr_utility.set_location('Entering:'||l_proc, 5);
388 --
389 l_api_updating := ben_ept_shd.api_updating
390 (p_effective_date =>p_effective_date,
391 p_elig_per_typ_prte_id => p_elig_per_typ_prte_id,
392 p_object_version_number =>p_object_version_number);
393 --
394 -- Bug:1631182 - To support user created person types needed to use
395 -- person_type_id insted of per_typ_cd
396 --
397 /* --
398 if (l_api_updating
399 and p_per_typ_cd
400 <>nvl(ben_ept_shd.g_old_rec.per_typ_cd,hr_api.g_varchar2)
401 or not l_api_updating)
402 and p_per_typ_cd is not null then
403 -- */
404 --
405 if (l_api_updating
406 and p_person_type_id
407 <>nvl(ben_ept_shd.g_old_rec.person_type_id,hr_api.g_number)
408 or not l_api_updating)
409 and p_person_type_id is not null then
410 --
411 open c1;
412 --
413 fetch c1 into l_dummy;
414 if c1%notfound then
415 --
416 -- Raise error as system person type can not be found
417 --
418 close c1;
419 fnd_message.set_name('PER','HR_7513_PER_TYPE_INVALID');
420 fnd_message.raise_error;
421 --
422 end if;
423 --
424 close c1;
425 --
426 end if;
427 --
428 hr_utility.set_location('Leaving:'||l_proc,10);
429 --
430 end chk_per_typ_cd;
431 --
432 -- ----------------------------------------------------------------------------
433 -- |------< chk_excld_flag >------|
434 -- ----------------------------------------------------------------------------
435 --
436 -- Description
437 -- This procedure is used to check that the lookup value is valid.
438 --
439 -- Pre Conditions
440 -- None.
441 --
442 -- In Parameters
443 -- elig_per_typ_prte_id PK of record being inserted or updated.
444 -- excld_flag Value of lookup code.
445 -- effective_date effective date
446 -- object_version_number Object version number of record being
447 -- inserted or updated.
448 --
449 -- Post Success
450 -- Processing continues
451 --
452 -- Post Failure
453 -- Error handled by procedure
454 --
455 -- Access Status
456 -- Internal table handler use only.
457 --
458 Procedure chk_excld_flag(p_elig_per_typ_prte_id in number,
459 p_excld_flag in varchar2,
460 p_effective_date in date,
461 p_object_version_number in number) is
462 --
463 l_proc varchar2(72) := g_package||'chk_excld_flag';
464 l_api_updating boolean;
465 --
466 Begin
467 --
468 hr_utility.set_location('Entering:'||l_proc, 5);
469 --
470 l_api_updating := ben_ept_shd.api_updating
471 (p_elig_per_typ_prte_id => p_elig_per_typ_prte_id,
472 p_effective_date => p_effective_date,
473 p_object_version_number => p_object_version_number);
474 --
475 if (l_api_updating
476 and p_excld_flag
477 <> nvl(ben_ept_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
478 or not l_api_updating) then
479 --
480 -- check if value of lookup falls within lookup type.
481 --
482 --
483 if hr_api.not_exists_in_hr_lookups
484 (p_lookup_type => 'YES_NO',
485 p_lookup_code => p_excld_flag,
486 p_effective_date => p_effective_date) then
487 --
488 -- raise error as does not exist as lookup
489 --
490 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
491 fnd_message.raise_error;
492 --
493 end if;
494 --
495 end if;
496 --
497 hr_utility.set_location('Leaving:'||l_proc,10);
498 --
499 end chk_excld_flag;
500 --
501 -- ---------------------------------------------------------------------------
502 -- |-----------------------< chk_duplicate_ordr_num >---------------------------| --Bug 2358518
503 -- ---------------------------------------------------------------------------
504 --
505 -- Description
506 -- Ensure that the Sequence Number is unique
507 -- within business_group
508 --
509 -- Pre Conditions
510 -- None.
511 --
512 -- In Parameters
513 -- p_elig_per_typ_prte_id elig_per_typ_prte_id
514 -- p_eligy_prfl_id eligy_prfl_id
515 -- p_ordr_num Sequence Number
516 -- p_business_group_id
517 --
518 -- Post Success
519 -- Processing continues
520 --
521 -- Post Failure
522 -- Errors handled by the procedure
523 --
524 -- Access Status
525 -- Internal table handler use only
526 --
527 -- ----------------------------------------------------------------------------
528 Procedure chk_duplicate_ordr_num
529 ( p_elig_per_typ_prte_id in number
530 ,p_eligy_prfl_id in number
531 ,p_ordr_num in number
532 ,p_business_group_id in number)
533 is
534 l_proc varchar2(72) := g_package||'chk_duplicate_ordr_num';
535 l_dummy char(1);
536 cursor c1 is
537 select null
538 from ben_elig_per_typ_prte_f
539 where elig_per_typ_prte_id <> nvl(p_elig_per_typ_prte_id,-1)
540 and eligy_prfl_id = p_eligy_prfl_id
541 and ordr_num = p_ordr_num
542 and business_group_id = p_business_group_id;
543 --
544 Begin
545 hr_utility.set_location('Entering:'||l_proc, 5);
546 --
547 open c1;
548 fetch c1 into l_dummy;
549 if c1%found then
550 close c1;
551 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
552 fnd_message.raise_error;
553 end if;
554 close c1;
555 --
556 hr_utility.set_location('Leaving:'||l_proc, 15);
557 End chk_duplicate_ordr_num;
558 --
559 -- ----------------------------------------------------------------------------
560 -- |--------------------------< dt_update_validate >--------------------------|
561 -- ----------------------------------------------------------------------------
562 -- {Start Of Comments}
563 --
564 -- Description:
565 -- This procedure is used for referential integrity of datetracked
566 -- parent entities when a datetrack update operation is taking place
567 -- and where there is no cascading of update defined for this entity.
568 --
569 -- Prerequisites:
570 -- This procedure is called from the update_validate.
571 --
572 -- In Parameters:
573 --
574 -- Post Success:
575 -- Processing continues.
576 --
577 -- Post Failure:
578 --
579 -- Developer Implementation Notes:
580 -- This procedure should not need maintenance unless the HR Schema model
581 -- changes.
582 --
583 -- Access Status:
584 -- Internal Row Handler Use Only.
585 --
586 -- {End Of Comments}
587 -- ----------------------------------------------------------------------------
588 Procedure dt_update_validate
589 (p_eligy_prfl_id in number default hr_api.g_number,
590 p_datetrack_mode in varchar2,
591 p_validation_start_date in date,
592 p_validation_end_date in date) Is
593 --
594 l_proc varchar2(72) := g_package||'dt_update_validate';
595 l_integrity_error Exception;
596 l_table_name all_tables.table_name%TYPE;
597 --
598 Begin
599 hr_utility.set_location('Entering:'||l_proc, 5);
600 --
601 -- Ensure that the p_datetrack_mode argument is not null
602 --
603 hr_api.mandatory_arg_error
604 (p_api_name => l_proc,
605 p_argument => 'datetrack_mode',
606 p_argument_value => p_datetrack_mode);
607 --
608 -- Only perform the validation if the datetrack update mode is valid
609 --
610 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
611 --
612 --
613 -- Ensure the arguments are not null
614 --
615 hr_api.mandatory_arg_error
616 (p_api_name => l_proc,
617 p_argument => 'validation_start_date',
618 p_argument_value => p_validation_start_date);
619 --
620 hr_api.mandatory_arg_error
621 (p_api_name => l_proc,
622 p_argument => 'validation_end_date',
623 p_argument_value => p_validation_end_date);
624 --
625 If ((nvl(p_eligy_prfl_id, hr_api.g_number) <> hr_api.g_number) and
626 NOT (dt_api.check_min_max_dates
627 (p_base_table_name => 'ben_eligy_prfl_f',
628 p_base_key_column => 'eligy_prfl_id',
629 p_base_key_value => p_eligy_prfl_id,
630 p_from_date => p_validation_start_date,
631 p_to_date => p_validation_end_date))) Then
632 l_table_name := 'ben_eligy_prfl_f';
633 Raise l_integrity_error;
634 End If;
635 --
636 End If;
637 --
638 hr_utility.set_location(' Leaving:'||l_proc, 10);
639 Exception
640 When l_integrity_error Then
641 --
642 -- A referential integrity check was violated therefore
643 -- we must error
644 --
645 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
646 fnd_message.set_token('TABLE_NAME', l_table_name);
647 fnd_message.raise_error;
648 When Others Then
649 --
650 -- An unhandled or unexpected error has occurred which
651 -- we must report
652 --
653 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
654 fnd_message.set_token('PROCEDURE', l_proc);
655 fnd_message.set_token('STEP','15');
656 fnd_message.raise_error;
657 End dt_update_validate;
658 --
659 -- ----------------------------------------------------------------------------
660 -- |--------------------------< dt_delete_validate >--------------------------|
661 -- ----------------------------------------------------------------------------
662 -- {Start Of Comments}
663 --
664 -- Description:
665 -- This procedure is used for referential integrity of datetracked
666 -- child entities when either a datetrack DELETE or ZAP is in operation
667 -- and where there is no cascading of delete defined for this entity.
668 -- For the datetrack mode of DELETE or ZAP we must ensure that no
669 -- datetracked child rows exist between the validation start and end
670 -- dates.
671 --
672 -- Prerequisites:
673 -- This procedure is called from the delete_validate.
674 --
675 -- In Parameters:
676 --
677 -- Post Success:
678 -- Processing continues.
679 --
680 -- Post Failure:
681 -- If a row exists by determining the returning Boolean value from the
682 -- generic dt_api.rows_exist function then we must supply an error via
683 -- the use of the local exception handler l_rows_exist.
684 --
685 -- Developer Implementation Notes:
686 -- This procedure should not need maintenance unless the HR Schema model
687 -- changes.
688 --
689 -- Access Status:
690 -- Internal Row Handler Use Only.
691 --
692 -- {End Of Comments}
693 -- ----------------------------------------------------------------------------
694 Procedure dt_delete_validate
695 (p_elig_per_typ_prte_id in number,
696 p_datetrack_mode in varchar2,
697 p_validation_start_date in date,
698 p_validation_end_date in date) Is
699 --
700 l_proc varchar2(72) := g_package||'dt_delete_validate';
701 l_rows_exist Exception;
702 l_table_name all_tables.table_name%TYPE;
703 --
704 Begin
705 hr_utility.set_location('Entering:'||l_proc, 5);
706 --
707 -- Ensure that the p_datetrack_mode argument is not null
708 --
709 hr_api.mandatory_arg_error
710 (p_api_name => l_proc,
711 p_argument => 'datetrack_mode',
712 p_argument_value => p_datetrack_mode);
713 --
714 -- Only perform the validation if the datetrack mode is either
715 -- DELETE or ZAP
716 --
717 If (p_datetrack_mode = 'DELETE' or
718 p_datetrack_mode = 'ZAP') then
719 --
720 --
721 -- Ensure the arguments are not null
722 --
723 hr_api.mandatory_arg_error
724 (p_api_name => l_proc,
725 p_argument => 'validation_start_date',
726 p_argument_value => p_validation_start_date);
727 --
728 hr_api.mandatory_arg_error
729 (p_api_name => l_proc,
730 p_argument => 'validation_end_date',
731 p_argument_value => p_validation_end_date);
732 --
733 hr_api.mandatory_arg_error
734 (p_api_name => l_proc,
735 p_argument => 'elig_per_typ_prte_id',
736 p_argument_value => p_elig_per_typ_prte_id);
737 --
738 --
739 --
740 End If;
741 --
742 hr_utility.set_location(' Leaving:'||l_proc, 10);
743 Exception
744 When l_rows_exist Then
745 --
746 -- A referential integrity check was violated therefore
747 -- we must error
748 --
749 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
750 fnd_message.set_token('TABLE_NAME', l_table_name);
751 fnd_message.raise_error;
752 When Others Then
753 --
754 -- An unhandled or unexpected error has occurred which
755 -- we must report
756 --
757 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
758 fnd_message.set_token('PROCEDURE', l_proc);
759 fnd_message.set_token('STEP','15');
760 fnd_message.raise_error;
761 End dt_delete_validate;
762 --
763 -- ----------------------------------------------------------------------------
764 -- |---------------------------< insert_validate >----------------------------|
765 -- ----------------------------------------------------------------------------
766 Procedure insert_validate
767 (p_rec in ben_ept_shd.g_rec_type,
768 p_effective_date in date,
769 p_datetrack_mode in varchar2,
770 p_validation_start_date in date,
771 p_validation_end_date in date) is
772 --
773 l_proc varchar2(72) := g_package||'insert_validate';
774 --
775 Begin
776 hr_utility.set_location('Entering:'||l_proc, 5);
777 --
778 -- Call all supporting business operations
779 --
780 --
781 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
782 --
783
784 --Bug No 5054294
785 chk_valid_fctr(p_eligy_prfl_id => p_rec.eligy_prfl_id,
786 p_person_type_id => p_rec.person_type_id);
787 -- End Bug No 5054294
788 chk_elig_per_typ_prte_id
789 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
790 p_effective_date => p_effective_date,
791 p_object_version_number => p_rec.object_version_number);
792 --
793 /*chk_person_type_id (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
794 p_per_typ_cd => p_rec.per_typ_cd,
795 p_eligy_prfl_id => p_rec.eligy_prfl_id,
796 p_excld_flag => p_rec.excld_flag,
797 p_validation_start_date => p_validation_start_date,
798 p_validation_end_date => p_validation_end_date,
799 p_business_group_id => p_rec.business_group_id,
800 p_effective_date => p_effective_date,
801 p_object_version_number => p_rec.object_version_number);*/
802 --
803 chk_per_typ_cd
804 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
805 --p_per_typ_cd => p_rec.per_typ_cd,
806 p_person_type_id => p_rec.person_type_id,
807 p_business_group_id => p_rec.business_group_id,
808 p_effective_date =>p_effective_date,
809 p_object_version_number =>p_rec.object_version_number);
810 --
811 chk_excld_flag
812 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
813 p_excld_flag => p_rec.excld_flag,
814 p_effective_date => p_effective_date,
815 p_object_version_number => p_rec.object_version_number);
816 --
817 chk_duplicate_ordr_num --Bug 2358518
818 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
819 p_eligy_prfl_id => p_rec.eligy_prfl_id,
820 p_ordr_num => p_rec.ordr_num,
821 p_business_group_id => p_rec.business_group_id);
822 --
823 hr_utility.set_location(' Leaving:'||l_proc, 10);
824 End insert_validate;
825 --
826 -- ----------------------------------------------------------------------------
827 -- |---------------------------< update_validate >----------------------------|
828 -- ----------------------------------------------------------------------------
829 Procedure update_validate
830 (p_rec in ben_ept_shd.g_rec_type,
831 p_effective_date in date,
832 p_datetrack_mode in varchar2,
833 p_validation_start_date in date,
834 p_validation_end_date in date) is
835 --
836 l_proc varchar2(72) := g_package||'update_validate';
837 --
838 Begin
839 hr_utility.set_location('Entering:'||l_proc, 5);
840 --
841 -- Call all supporting business operations
842 --
843 --
844 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
845 --
846
847 --Bug 5054294
848 chk_valid_fctr(p_eligy_prfl_id => p_rec.eligy_prfl_id,
849 p_person_type_id => p_rec.person_type_id);
850 --End Bug 5054294
851 chk_elig_per_typ_prte_id
852 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
853 p_effective_date => p_effective_date,
854 p_object_version_number => p_rec.object_version_number);
855 --
856 /* chk_person_type_id (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
857 p_per_typ_cd => p_rec.per_typ_cd,
858 p_eligy_prfl_id => p_rec.eligy_prfl_id,
859 p_excld_flag => p_rec.excld_flag,
860 p_validation_start_date => p_validation_start_date,
861 p_validation_end_date => p_validation_end_date,
862 p_business_group_id => p_rec.business_group_id,
863 p_effective_date => p_effective_date,
864 p_object_version_number => p_rec.object_version_number);*/
865
866 chk_per_typ_cd
867 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
868 -- p_per_typ_cd => p_rec.per_typ_cd,
869 p_person_type_id => p_rec.person_type_id,
870 p_business_group_id => p_rec.business_group_id,
871 p_effective_date =>p_effective_date,
872 p_object_version_number =>p_rec.object_version_number);
873 --
874 chk_excld_flag
875 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
876 p_excld_flag => p_rec.excld_flag,
877 p_effective_date => p_effective_date,
878 p_object_version_number => p_rec.object_version_number);
879 --
880 chk_duplicate_ordr_num --Bug 2358518
881 (p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id,
882 p_eligy_prfl_id => p_rec.eligy_prfl_id,
883 p_ordr_num => p_rec.ordr_num,
884 p_business_group_id => p_rec.business_group_id);
885 --
886 -- Call the datetrack update integrity operation
887 --
888 dt_update_validate
889 (p_eligy_prfl_id => p_rec.eligy_prfl_id,
890 p_datetrack_mode => p_datetrack_mode,
891 p_validation_start_date => p_validation_start_date,
892 p_validation_end_date => p_validation_end_date);
893 --
894 hr_utility.set_location(' Leaving:'||l_proc, 10);
895 End update_validate;
896 --
897 -- ----------------------------------------------------------------------------
898 -- |---------------------------< delete_validate >----------------------------|
899 -- ----------------------------------------------------------------------------
900 Procedure delete_validate
901 (p_rec in ben_ept_shd.g_rec_type,
902 p_effective_date in date,
903 p_datetrack_mode in varchar2,
904 p_validation_start_date in date,
905 p_validation_end_date in date) is
906 --
907 l_proc varchar2(72) := g_package||'delete_validate';
908 --
909 Begin
910 hr_utility.set_location('Entering:'||l_proc, 5);
911 --
912 -- Call all supporting business operations
913 --
914 dt_delete_validate
915 (p_datetrack_mode => p_datetrack_mode,
916 p_validation_start_date => p_validation_start_date,
917 p_validation_end_date => p_validation_end_date,
918 p_elig_per_typ_prte_id => p_rec.elig_per_typ_prte_id);
919 --
920 hr_utility.set_location(' Leaving:'||l_proc, 10);
921 End delete_validate;
922 --
923 --
924 -- ---------------------------------------------------------------------------
925 -- |---------------------< return_legislation_code >-------------------------|
926 -- ---------------------------------------------------------------------------
927 --
928 function return_legislation_code
929 (p_elig_per_typ_prte_id in number) return varchar2 is
930 --
931 -- Declare cursor
932 --
933 cursor csr_leg_code is
934 select a.legislation_code
935 from per_business_groups a,
936 ben_elig_per_typ_prte_f b
937 where b.elig_per_typ_prte_id = p_elig_per_typ_prte_id
938 and a.business_group_id = b.business_group_id;
939 --
940 -- Declare local variables
941 --
942 l_legislation_code varchar2(150);
943 l_proc varchar2(72) := g_package||'return_legislation_code';
944 --
945 begin
946 --
947 hr_utility.set_location('Entering:'|| l_proc, 10);
948 --
949 -- Ensure that all the mandatory parameter are not null
950 --
951 hr_api.mandatory_arg_error(p_api_name => l_proc,
952 p_argument => 'elig_per_typ_prte_id',
953 p_argument_value => p_elig_per_typ_prte_id);
954 --
955 open csr_leg_code;
956 --
957 fetch csr_leg_code into l_legislation_code;
958 --
959 if csr_leg_code%notfound then
960 --
961 close csr_leg_code;
962 --
963 -- The primary key is invalid therefore we must error
964 --
965 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
966 fnd_message.raise_error;
967 --
968 end if;
969 --
970 close csr_leg_code;
971 --
972 hr_utility.set_location(' Leaving:'|| l_proc, 20);
973 --
974 return l_legislation_code;
975 --
976 end return_legislation_code;
977 --
978 end ben_ept_bus;