[Home] [Help]
PACKAGE BODY: APPS.BEN_ENT_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_ent_bus as
2 /* $Header: beentrhi.pkb 120.2 2006/03/31 00:04:59 gsehgal noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ent_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_enrld_anthr_ptip_rt_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 -- enrld_anthr_ptip_rt_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_enrld_anthr_ptip_rt_id(p_enrld_anthr_ptip_rt_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_enrld_anthr_ptip_rt_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_ent_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_enrld_anthr_ptip_rt_id => p_enrld_anthr_ptip_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_enrld_anthr_ptip_rt_id,hr_api.g_number)
55 <> ben_ent_shd.g_old_rec.enrld_anthr_ptip_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_ent_shd.constraint_error('BEN_enrld_anthr_ptip_rt_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_enrld_anthr_ptip_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ent_shd.constraint_error('BEN_enrld_anthr_ptip_rt_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_enrld_anthr_ptip_rt_id;
78
79
80 --
81 -- ----------------------------------------------------------------------------
82 -- |------< chk_dup_record >------|
83 -- ----------------------------------------------------------------------------
84 --
85 -- Description
86 -- This procedure is used to check that there is no duplicate record
87 --
88 -- Pre Conditions
89 -- None.
90 --
91 -- In Parameters
92 -- <> PK of record being inserted or updated.
93 -- <> Value of lookup code.
94 -- p_vrbl_rt_prfl_id FK of the record
95 -- p_effective_date effective date
96 -- p_object_version_number Object version number of record being
97 -- inserted or updated.
98 -- p_business_group_id business_group_id of the record
99 -- p_validation_start_date validation_start_date of record
100 -- p_validation_end_date validation_end_date of record
101 --
102 -- Post Success
103 -- Processing continues
104 --
105 -- Post Failure
106 -- Error handled by procedure
107 --
108 -- Access Status
109 -- Internal table handler use only.
110 --
111 Procedure chk_dup_record
112 (p_enrld_anthr_ptip_rt_id in number,
113 p_ptip_id in number,
114 p_enrl_det_dt_cd in varchar2,
115 p_vrbl_rt_prfl_id in number,
116 p_effective_date in date,
117 p_object_version_number in number,
118 p_business_group_id in number,
119 p_validation_start_date in date,
120 p_validation_end_date in date )
121 is
122 --
123 l_proc varchar2(72) := g_package||'chk_dup_record';
124 l_api_updating boolean;
125 l_exists varchar2(1);
126 --
127 cursor c_dup is
128 select null
129 from ben_enrld_anthr_ptip_rt_f
130 where ptip_id = p_ptip_id
131 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
132 and enrl_det_dt_cd = p_enrl_det_dt_cd
133 and enrld_anthr_ptip_rt_id <> nvl(p_enrld_anthr_ptip_rt_id,hr_api.g_number)
134 and business_group_id + 0 = p_business_group_id
135 and p_validation_start_date <= effective_end_date
136 and p_validation_end_date >= effective_start_date;
137 --
138 BEGIN
139 --
140 hr_utility.set_location('Entering:'||l_proc, 5);
141 --
142 l_api_updating := ben_ent_shd.api_updating
146 --
143 (p_enrld_anthr_ptip_rt_id => p_enrld_anthr_ptip_rt_id,
144 p_effective_date => p_effective_date,
145 p_object_version_number => p_object_version_number);
147 if (l_api_updating
148 and
149 (p_ptip_id <> nvl(ben_ent_shd.g_old_rec.ptip_id,hr_api.g_number) or
150 p_enrl_det_dt_cd <> nvl(ben_ent_shd.g_old_rec.enrl_det_dt_cd,hr_api.g_varchar2)
151 )
152 or not l_api_updating) then
153
154 open c_dup;
155 fetch c_dup into l_exists;
156 if c_dup%found then
157 close c_dup;
158 --
159 -- raise error as this < > criteria already exists for this profile
160 --
161 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
162 fnd_message.set_token('VAR1','Enrolled in Another Plan Type in Program criteria',TRUE);
163 fnd_message.set_token('VAR2','Variable Rate Profile',TRUE);
164 fnd_message.raise_error;
165 --
166 end if;
167 close c_dup;
168 --
169 end if;
170 hr_utility.set_location('Leaving:'||l_proc,10);
171 --
172 END chk_dup_record;
173
174 --
175 -- ----------------------------------------------------------------------------
176 -- |------< chk_only_pls_subj_cobra_flag >------|
177 -- ----------------------------------------------------------------------------
178 --
179 -- Description
180 -- This procedure is used to check that the lookup value is valid.
181 --
182 -- Pre Conditions
183 -- None.
184 --
185 -- In Parameters
186 -- enrld_anthr_ptip_rt_id PK of record being inserted or updated.
187 -- only_pls_subj_cobra_flag Value of lookup code.
188 -- effective_date effective date
189 -- object_version_number Object version number of record being
190 -- inserted or updated.
191 --
192 -- Post Success
193 -- Processing continues
194 --
195 -- Post Failure
196 -- Error handled by procedure
197 --
198 -- Access Status
199 -- Internal table handler use only.
200 --
201 Procedure chk_only_pls_subj_cobra_flag(p_enrld_anthr_ptip_rt_id in number,
202 p_only_pls_subj_cobra_flag in varchar2,
203 p_effective_date in date,
204 p_object_version_number in number) is
205 --
206 l_proc varchar2(72) := g_package||'chk_only_pls_subj_cobra_flag';
207 l_api_updating boolean;
208 --
209 Begin
210 --
211 hr_utility.set_location('Entering:'||l_proc, 5);
212 --
213 l_api_updating := ben_ent_shd.api_updating
214 (p_enrld_anthr_ptip_rt_id => p_enrld_anthr_ptip_rt_id,
215 p_effective_date => p_effective_date,
216 p_object_version_number => p_object_version_number);
217 --
218 if (l_api_updating
219 and p_only_pls_subj_cobra_flag
220 <> nvl(ben_ent_shd.g_old_rec.only_pls_subj_cobra_flag,hr_api.g_varchar2)
221 or not l_api_updating) then
222 --
223 -- check if value of lookup falls within lookup type.
224 --
225 --
226 if hr_api.not_exists_in_hr_lookups
227 (p_lookup_type => 'YES_NO',
228 p_lookup_code => p_only_pls_subj_cobra_flag,
229 p_effective_date => p_effective_date) then
230 --
231 -- raise error as does not exist as lookup
232 --
233 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
234 fnd_message.set_token('FIELD','p_only_pls_subj_cobra_flag');
235 fnd_message.set_token('TYPE','YES_NO');
236 fnd_message.raise_error;
237 --
238 end if;
239 --
240 end if;
241 --
242 hr_utility.set_location('Leaving:'||l_proc,10);
243 --
244 end chk_only_pls_subj_cobra_flag;
245 --
246 -- ----------------------------------------------------------------------------
247 -- |------< chk_enrl_det_dt_cd >------|
248 -- ----------------------------------------------------------------------------
249 --
250 -- Description
251 -- This procedure is used to check that the lookup value is valid.
252 --
253 -- Pre Conditions
254 -- None.
255 --
256 -- In Parameters
257 -- enrld_anthr_ptip_rt_id PK of record being inserted or updated.
258 -- enrl_det_dt_cd Value of lookup code.
259 -- effective_date effective date
260 -- object_version_number Object version number of record being
261 -- inserted or updated.
262 --
263 -- Post Success
264 -- Processing continues
265 --
266 -- Post Failure
267 -- Error handled by procedure
268 --
269 -- Access Status
270 -- Internal table handler use only.
271 --
272 Procedure chk_enrl_det_dt_cd(p_enrld_anthr_ptip_rt_id in number,
273 p_enrl_det_dt_cd in varchar2,
274 p_effective_date in date,
275 p_object_version_number in number) is
276 --
277 l_proc varchar2(72) := g_package||'chk_enrl_det_dt_cd';
278 l_api_updating boolean;
279 --
280 Begin
281 --
282 hr_utility.set_location('Entering:'||l_proc, 5);
283 --
284 l_api_updating := ben_ent_shd.api_updating
285 (p_enrld_anthr_ptip_rt_id => p_enrld_anthr_ptip_rt_id,
286 p_effective_date => p_effective_date,
287 p_object_version_number => p_object_version_number);
288 --
289 if (l_api_updating
290 and p_enrl_det_dt_cd
291 <> nvl(ben_ent_shd.g_old_rec.enrl_det_dt_cd,hr_api.g_varchar2)
292 or not l_api_updating)
293 and p_enrl_det_dt_cd is not null then
294 --
298 (p_lookup_type => 'BEN_ENRL_DET_DT',
295 -- check if value of lookup falls within lookup type.
296 --
297 if hr_api.not_exists_in_hr_lookups
299 p_lookup_code => p_enrl_det_dt_cd,
300 p_effective_date => p_effective_date) then
301 --
302 -- raise error as does not exist as lookup
303 --
304 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
305 fnd_message.set_token('FIELD','p_enrl_det_dt_cd');
306 fnd_message.set_token('TYPE','BEN_ENRL_DET_DT');
307 fnd_message.raise_error;
308 --
309 end if;
310 --
311 end if;
312 --
313 hr_utility.set_location('Leaving:'||l_proc,10);
314 --
315 end chk_enrl_det_dt_cd;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |------< chk_excld_flag >------|
319 -- ----------------------------------------------------------------------------
320 --
321 -- Description
322 -- This procedure is used to check that the lookup value is valid.
323 --
324 -- Pre Conditions
325 -- None.
326 --
327 -- In Parameters
328 -- enrld_anthr_ptip_rt_id PK of record being inserted or updated.
329 -- excld_flag Value of lookup code.
330 -- effective_date effective date
331 -- object_version_number Object version number of record being
332 -- inserted or updated.
333 --
334 -- Post Success
335 -- Processing continues
336 --
337 -- Post Failure
338 -- Error handled by procedure
339 --
340 -- Access Status
341 -- Internal table handler use only.
342 --
343 Procedure chk_excld_flag(p_enrld_anthr_ptip_rt_id in number,
344 p_excld_flag in varchar2,
345 p_effective_date in date,
346 p_object_version_number in number) is
347 --
348 l_proc varchar2(72) := g_package||'chk_excld_flag';
349 l_api_updating boolean;
350 --
351 Begin
352 --
353 hr_utility.set_location('Entering:'||l_proc, 5);
354 --
355 l_api_updating := ben_ent_shd.api_updating
356 (p_enrld_anthr_ptip_rt_id => p_enrld_anthr_ptip_rt_id,
357 p_effective_date => p_effective_date,
358 p_object_version_number => p_object_version_number);
359 --
360 if (l_api_updating
361 and p_excld_flag
362 <> nvl(ben_ent_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
363 or not l_api_updating) then
364 --
365 -- check if value of lookup falls within lookup type.
366 --
367 --
368 if hr_api.not_exists_in_hr_lookups
369 (p_lookup_type => 'YES_NO',
370 p_lookup_code => p_excld_flag,
371 p_effective_date => p_effective_date) then
372 --
373 -- raise error as does not exist as lookup
374 --
375 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
376 fnd_message.set_token('FIELD','p_excld_flag');
377 fnd_message.set_token('TYPE','YES_NO');
378 fnd_message.raise_error;
379 --
380 end if;
381 --
382 end if;
383 --
384 hr_utility.set_location('Leaving:'||l_proc,10);
385 --
386 --
387 end chk_excld_flag;
388 --
389 --
390 --
391 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
392 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
393 -- ----------------------------------------------------------------------------
394 --
395 -- Description
396 --
397 -- Pre Conditions
398 -- None.
399 --
400 -- In Parameters
401 -- p_enrld_anthr_ptip_rt_id
402 -- p_vrbl_rt_prfl_id
403 -- p_ordr_num
404 -- p_effective_date
405 -- p_business_group_id
406 --
407 -- Post Success
408 -- Processing continues
409 --
410 -- Post Failure
411 -- Errors handled by the procedure
412 --
413 -- Access Status
414 -- Internal table handler use only.
415 --
416 -- ----------------------------------------------------------------------------
417
418
419 procedure chk_duplicate_ordr_num
420 (p_vrbl_rt_prfl_id in number
421 ,p_enrld_anthr_ptip_rt_id in number
422 ,p_ordr_num in number
423 ,p_validation_start_date in date
424 ,p_validation_end_date in date
425 ,p_business_group_id in number)
426 is
427 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
428 l_dummy char(1);
429 cursor c1 is select null
430 from ben_enrld_anthr_ptip_rt_f
431 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
432 -- changed against bug: 5113011
433 and enrld_anthr_ptip_rt_id <> nvl(p_enrld_anthr_ptip_rt_id ,-1)
434 -- and ptip_id <> nvl(p_ptip_id ,-1)
435 --and p_effective_date between effective_start_date
436 -- and effective_end_date
437 and p_validation_start_date <= effective_end_date
438 and p_validation_end_date >= effective_start_date
439 and business_group_id + 0 = p_business_group_id
440 and ordr_num = p_ordr_num;
441 --
442 Begin
443 hr_utility.set_location('Entering:'||l_proc, 5);
444
445 --
446 open c1;
447 fetch c1 into l_dummy;
448 --
449 if c1%found then
450 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
451 fnd_message.raise_error;
452 end if;
453 close c1;
454 --
458
455 hr_utility.set_location('Leaving:'||l_proc, 15);
456 End chk_duplicate_ordr_num;
457
459
460 --
461 -- ----------------------------------------------------------------------------
462 -- |--------------------------< dt_update_validate >--------------------------|
463 -- ----------------------------------------------------------------------------
464 -- {Start Of Comments}
465 --
466 -- Description:
467 -- This procedure is used for referential integrity of datetracked
468 -- parent entities when a datetrack update operation is taking place
469 -- and where there is no cascading of update defined for this entity.
470 --
471 -- Prerequisites:
472 -- This procedure is called from the update_validate.
473 --
474 -- In Parameters:
475 --
476 -- Post Success:
477 -- Processing continues.
478 --
479 -- Post Failure:
480 --
481 -- Developer Implementation Notes:
482 -- This procedure should not need maintenance unless the HR Schema model
483 -- changes.
484 --
485 -- Access Status:
486 -- Internal Row Handler Use Only.
487 --
488 -- {End Of Comments}
489 -- ----------------------------------------------------------------------------
490 Procedure dt_update_validate
491 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
492 p_ptip_id in number default hr_api.g_number,
493 p_datetrack_mode in varchar2,
494 p_validation_start_date in date,
495 p_validation_end_date in date) Is
496 --
497 l_proc varchar2(72) := g_package||'dt_update_validate';
498 l_integrity_error Exception;
499 l_table_name all_tables.table_name%TYPE;
500 --
501 Begin
502 hr_utility.set_location('Entering:'||l_proc, 5);
503 --
504 -- Ensure that the p_datetrack_mode argument is not null
505 --
506 hr_api.mandatory_arg_error
507 (p_api_name => l_proc,
508 p_argument => 'datetrack_mode',
509 p_argument_value => p_datetrack_mode);
510 --
511 -- Only perform the validation if the datetrack update mode is valid
512 --
513 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
514 --
515 --
516 -- Ensure the arguments are not null
517 --
518 hr_api.mandatory_arg_error
519 (p_api_name => l_proc,
520 p_argument => 'validation_start_date',
521 p_argument_value => p_validation_start_date);
522 --
523 hr_api.mandatory_arg_error
524 (p_api_name => l_proc,
525 p_argument => 'validation_end_date',
526 p_argument_value => p_validation_end_date);
527 --
528 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
529 NOT (dt_api.check_min_max_dates
530 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
531 p_base_key_column => 'vrbl_rt_prfl_id',
532 p_base_key_value => p_vrbl_rt_prfl_id,
533 p_from_date => p_validation_start_date,
534 p_to_date => p_validation_end_date))) Then
535 l_table_name := 'ben_vrbl_rt_prfl_f';
536 Raise l_integrity_error;
537 End If;
538 If ((nvl(p_ptip_id, hr_api.g_number) <> hr_api.g_number) and
539 NOT (dt_api.check_min_max_dates
540 (p_base_table_name => 'ben_ptip_f',
541 p_base_key_column => 'ptip_id',
542 p_base_key_value => p_ptip_id,
543 p_from_date => p_validation_start_date,
544 p_to_date => p_validation_end_date))) Then
545 l_table_name := 'ben_ptip_f';
546 Raise l_integrity_error;
547 End If;
548 --
549 End If;
550 --
551 hr_utility.set_location(' Leaving:'||l_proc, 10);
552 Exception
553 When l_integrity_error Then
554 --
555 -- A referential integrity check was violated therefore
556 -- we must error
557 --
558 ben_utility.parent_integrity_error(p_table_name => l_table_name);
559 --
560 When Others Then
561 --
562 -- An unhandled or unexpected error has occurred which
563 -- we must report
564 --
565 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
566 fnd_message.set_token('PROCEDURE', l_proc);
567 fnd_message.set_token('STEP','15');
568 fnd_message.raise_error;
569 End dt_update_validate;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |--------------------------< dt_delete_validate >--------------------------|
573 -- ----------------------------------------------------------------------------
574 -- {Start Of Comments}
575 --
576 -- Description:
577 -- This procedure is used for referential integrity of datetracked
578 -- child entities when either a datetrack DELETE or ZAP is in operation
579 -- and where there is no cascading of delete defined for this entity.
580 -- For the datetrack mode of DELETE or ZAP we must ensure that no
581 -- datetracked child rows exist between the validation start and end
582 -- dates.
583 --
584 -- Prerequisites:
585 -- This procedure is called from the delete_validate.
586 --
587 -- In Parameters:
588 --
589 -- Post Success:
590 -- Processing continues.
591 --
592 -- Post Failure:
593 -- If a row exists by determining the returning Boolean value from the
594 -- generic dt_api.rows_exist function then we must supply an error via
595 -- the use of the local exception handler l_rows_exist.
596 --
597 -- Developer Implementation Notes:
601 -- Access Status:
598 -- This procedure should not need maintenance unless the HR Schema model
599 -- changes.
600 --
602 -- Internal Row Handler Use Only.
603 --
604 -- {End Of Comments}
605 -- ----------------------------------------------------------------------------
606 Procedure dt_delete_validate
607 (p_enrld_anthr_ptip_rt_id in number,
608 p_datetrack_mode in varchar2,
609 p_validation_start_date in date,
610 p_validation_end_date in date) Is
611 --
612 l_proc varchar2(72) := g_package||'dt_delete_validate';
613 l_rows_exist Exception;
614 l_table_name all_tables.table_name%TYPE;
615 --
616 Begin
617 hr_utility.set_location('Entering:'||l_proc, 5);
618 --
619 -- Ensure that the p_datetrack_mode argument is not null
620 --
621 hr_api.mandatory_arg_error
622 (p_api_name => l_proc,
623 p_argument => 'datetrack_mode',
624 p_argument_value => p_datetrack_mode);
625 --
626 -- Only perform the validation if the datetrack mode is either
627 -- DELETE or ZAP
628 --
629 If (p_datetrack_mode = 'DELETE' or
630 p_datetrack_mode = 'ZAP') then
631 --
632 --
633 -- Ensure the arguments are not null
634 --
635 hr_api.mandatory_arg_error
636 (p_api_name => l_proc,
637 p_argument => 'validation_start_date',
638 p_argument_value => p_validation_start_date);
639 --
640 hr_api.mandatory_arg_error
641 (p_api_name => l_proc,
642 p_argument => 'validation_end_date',
643 p_argument_value => p_validation_end_date);
644 --
645 hr_api.mandatory_arg_error
646 (p_api_name => l_proc,
647 p_argument => 'enrld_anthr_ptip_rt_id',
648 p_argument_value => p_enrld_anthr_ptip_rt_id);
649 --
650 --
651 --
652 End If;
653 --
654 hr_utility.set_location(' Leaving:'||l_proc, 10);
655 Exception
656 When l_rows_exist Then
657 --
658 -- A referential integrity check was violated therefore
659 -- we must error
660 --
661 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
662 fnd_message.set_token('TABLE_NAME', l_table_name);
663 fnd_message.raise_error;
664 When Others Then
665 --
666 -- An unhandled or unexpected error has occurred which
667 -- we must report
668 --
669 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
670 fnd_message.set_token('PROCEDURE', l_proc);
671 fnd_message.set_token('STEP','15');
672 fnd_message.raise_error;
673 End dt_delete_validate;
674 --
675 -- ----------------------------------------------------------------------------
676 -- |---------------------------< insert_validate >----------------------------|
677 -- ----------------------------------------------------------------------------
678 Procedure insert_validate
679 (p_rec in ben_ent_shd.g_rec_type,
680 p_effective_date in date,
681 p_datetrack_mode in varchar2,
682 p_validation_start_date in date,
683 p_validation_end_date in date) is
684 --
685 l_proc varchar2(72) := g_package||'insert_validate';
686 --
687 Begin
688 hr_utility.set_location('Entering:'||l_proc, 5);
689 --
690 -- Call all supporting business operations
691 --
692 --
693 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
694 --
695 chk_enrld_anthr_ptip_rt_id
696 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
697 p_effective_date => p_effective_date,
698 p_object_version_number => p_rec.object_version_number);
699 --
700 chk_only_pls_subj_cobra_flag
701 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
702 p_only_pls_subj_cobra_flag => p_rec.only_pls_subj_cobra_flag,
703 p_effective_date => p_effective_date,
704 p_object_version_number => p_rec.object_version_number);
705 --
706 chk_enrl_det_dt_cd
707 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
708 p_enrl_det_dt_cd => p_rec.enrl_det_dt_cd,
709 p_effective_date => p_effective_date,
710 p_object_version_number => p_rec.object_version_number);
711 --
712 chk_excld_flag
713 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
714 p_excld_flag => p_rec.excld_flag,
715 p_effective_date => p_effective_date,
716 p_object_version_number => p_rec.object_version_number);
717 --
718 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
719 chk_duplicate_ordr_num
720 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
721 ,p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id
722 ,p_ordr_num => p_rec.ordr_num
723 ,p_validation_start_date => p_validation_start_date
724 ,p_validation_end_date => p_validation_end_date
725 ,p_business_group_id => p_rec.business_group_id);
726 --
727 chk_dup_record
728 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
729 p_ptip_id => p_rec.ptip_id,
730 p_enrl_det_dt_cd => p_rec.enrl_det_dt_cd,
731 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
732 p_effective_date => p_effective_date,
733 p_object_version_number => p_rec.object_version_number,
734 p_business_group_id => p_rec.business_group_id,
735 p_validation_start_date => p_validation_start_date,
736 p_validation_end_date => p_validation_end_date);
737 --
741 hr_utility.set_location(' Leaving:'||l_proc, 10);
738
739
740 --
742 End insert_validate;
743 --
744 -- ----------------------------------------------------------------------------
745 -- |---------------------------< update_validate >----------------------------|
746 -- ----------------------------------------------------------------------------
747 Procedure update_validate
748 (p_rec in ben_ent_shd.g_rec_type,
749 p_effective_date in date,
750 p_datetrack_mode in varchar2,
751 p_validation_start_date in date,
752 p_validation_end_date in date) is
753 --
754 l_proc varchar2(72) := g_package||'update_validate';
755 --
756 Begin
757 hr_utility.set_location('Entering:'||l_proc, 5);
758 --
759 -- Call all supporting business operations
760 --
761 --
762 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
763 --
764 chk_enrld_anthr_ptip_rt_id
765 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
766 p_effective_date => p_effective_date,
767 p_object_version_number => p_rec.object_version_number);
768 --
769 chk_only_pls_subj_cobra_flag
770 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
771 p_only_pls_subj_cobra_flag => p_rec.only_pls_subj_cobra_flag,
772 p_effective_date => p_effective_date,
773 p_object_version_number => p_rec.object_version_number);
774 --
775 chk_enrl_det_dt_cd
776 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
777 p_enrl_det_dt_cd => p_rec.enrl_det_dt_cd,
778 p_effective_date => p_effective_date,
779 p_object_version_number => p_rec.object_version_number);
780 --
781 chk_excld_flag
782 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
783 p_excld_flag => p_rec.excld_flag,
784 p_effective_date => p_effective_date,
785 p_object_version_number => p_rec.object_version_number);
786 --
787 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
788 chk_duplicate_ordr_num
789 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
790 ,p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id
791 ,p_ordr_num => p_rec.ordr_num
792 ,p_validation_start_date => p_validation_start_date
793 ,p_validation_end_date => p_validation_end_date
794 ,p_business_group_id => p_rec.business_group_id);
795
796
797 --
798 -- Call the datetrack update integrity operation
799 --
800
801 chk_dup_record
802 (p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id,
803 p_ptip_id => p_rec.ptip_id,
804 p_enrl_det_dt_cd => p_rec.enrl_det_dt_cd,
805 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
806 p_effective_date => p_effective_date,
807 p_object_version_number => p_rec.object_version_number,
808 p_business_group_id => p_rec.business_group_id,
809 p_validation_start_date => p_validation_start_date,
810 p_validation_end_date => p_validation_end_date);
811
812 dt_update_validate
813 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
814 p_ptip_id => p_rec.ptip_id,
815 p_datetrack_mode => p_datetrack_mode,
816 p_validation_start_date => p_validation_start_date,
817 p_validation_end_date => p_validation_end_date);
818 --
819 hr_utility.set_location(' Leaving:'||l_proc, 10);
820 End update_validate;
821 --
822 -- ----------------------------------------------------------------------------
823 -- |---------------------------< delete_validate >----------------------------|
824 -- ----------------------------------------------------------------------------
825 Procedure delete_validate
826 (p_rec in ben_ent_shd.g_rec_type,
827 p_effective_date in date,
828 p_datetrack_mode in varchar2,
829 p_validation_start_date in date,
830 p_validation_end_date in date) is
831 --
832 l_proc varchar2(72) := g_package||'delete_validate';
833 --
834 Begin
835 hr_utility.set_location('Entering:'||l_proc, 5);
836 --
837 -- Call all supporting business operations
838 --
839 dt_delete_validate
840 (p_datetrack_mode => p_datetrack_mode,
841 p_validation_start_date => p_validation_start_date,
842 p_validation_end_date => p_validation_end_date,
843 p_enrld_anthr_ptip_rt_id => p_rec.enrld_anthr_ptip_rt_id);
844 --
845 hr_utility.set_location(' Leaving:'||l_proc, 10);
846 End delete_validate;
847 --
848 --
849 -- ---------------------------------------------------------------------------
850 -- |---------------------< return_legislation_code >-------------------------|
851 -- ---------------------------------------------------------------------------
852 --
853 function return_legislation_code
854 (p_enrld_anthr_ptip_rt_id in number) return varchar2 is
855 --
856 -- Declare cursor
857 --
858 cursor csr_leg_code is
859 select a.legislation_code
860 from per_business_groups a,
861 ben_enrld_anthr_ptip_rt_f b
862 where b.enrld_anthr_ptip_rt_id = p_enrld_anthr_ptip_rt_id
863 and a.business_group_id = b.business_group_id;
864 --
865 -- Declare local variables
866 --
867 l_legislation_code varchar2(150);
868 l_proc varchar2(72) := g_package||'return_legislation_code';
869 --
870 begin
871 --
872 hr_utility.set_location('Entering:'|| l_proc, 10);
873 --
877 p_argument => 'enrld_anthr_ptip_rt_id',
874 -- Ensure that all the mandatory parameter are not null
875 --
876 hr_api.mandatory_arg_error(p_api_name => l_proc,
878 p_argument_value => p_enrld_anthr_ptip_rt_id);
879 --
880 open csr_leg_code;
881 --
882 fetch csr_leg_code into l_legislation_code;
883 --
884 if csr_leg_code%notfound then
885 --
886 close csr_leg_code;
887 --
888 -- The primary key is invalid therefore we must error
889 --
890 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
891 fnd_message.raise_error;
892 --
893 end if;
894 --
895 close csr_leg_code;
896 --
897 hr_utility.set_location(' Leaving:'|| l_proc, 20);
898 --
899 return l_legislation_code;
900 --
901 end return_legislation_code;
902 --
903 end ben_ent_bus;