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