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