[Home] [Help]
PACKAGE BODY: APPS.BEN_PRT_BUS
Source
1 Package Body ben_prt_bus as
2 /* $Header: beprtrhi.pkb 120.0.12010000.3 2008/08/25 13:51:57 ppentapa ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_prt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_poe_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 -- poe_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_poe_rt_id(p_poe_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_poe_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_prt_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_poe_rt_id => p_poe_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_poe_rt_id,hr_api.g_number)
55 <> ben_prt_shd.g_old_rec.poe_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_prt_shd.constraint_error('BEN_POE_RT_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_poe_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_prt_shd.constraint_error('BEN_POE_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_poe_rt_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_poe_nnmntry_uom >------|
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 -- poe_rt_id PK of record being inserted or updated.
91 -- poe_nnmntry_uom 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_poe_nnmntry_uom(p_poe_rt_id in number,
109 --
106 p_poe_nnmntry_uom in varchar2,
107 p_effective_date in date,
108 p_object_version_number in number) is
110 l_proc varchar2(72) := g_package||'chk_poe_nnmntry_uom';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := ben_prt_shd.api_updating
118 (p_poe_rt_id => p_poe_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_poe_nnmntry_uom
124 <> nvl(ben_prt_shd.g_old_rec.poe_nnmntry_uom,hr_api.g_varchar2)
125 or not l_api_updating)
126 and p_poe_nnmntry_uom 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_RQD_PERD_ENRT_NENRT_TM_UOM',
132 p_lookup_code => p_poe_nnmntry_uom,
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_poe_nnmntry_uom');
139 fnd_message.set_token('VALUE', p_poe_nnmntry_uom);
140 fnd_message.set_token('TYPE','BEN_RQD_PERD_ENRT_NENRT_TM_UOM');
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_poe_nnmntry_uom;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_rndg_rl >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure is used to check that the Formula Rule is valid.
157 --
158 -- Pre Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- poe_rt_id PK of record being inserted or updated.
163 -- rndg_rl Value of formula rule id.
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_rndg_rl(p_poe_rt_id in number,
178 p_rndg_rl in number,
179 p_effective_date in date,
180 p_object_version_number in number) is
181 --
182 l_proc varchar2(72) := g_package||'chk_rndg_rl';
183 l_api_updating boolean;
184 l_dummy varchar2(1);
185 --
186 cursor c1 is
187 select null
188 from ff_formulas_f ff
189 where ff.formula_id = p_rndg_rl
190 and p_effective_date
191 between ff.effective_start_date
192 and ff.effective_end_date;
193 --
194 Begin
195 --
196 hr_utility.set_location('Entering:'||l_proc, 5);
197 --
198 l_api_updating := ben_prt_shd.api_updating
199 (p_poe_rt_id => p_poe_rt_id,
200 p_effective_date => p_effective_date,
201 p_object_version_number => p_object_version_number);
202 --
203 if (l_api_updating
204 and nvl(p_rndg_rl,hr_api.g_number)
205 <> ben_prt_shd.g_old_rec.rndg_rl
206 or not l_api_updating)
207 and p_rndg_rl is not null then
208 --
209 -- check if value of formula rule is valid.
210 --
211 open c1;
212 --
213 -- fetch value from cursor if it returns a record then the
214 -- formula is valid otherwise its invalid
215 --
216 fetch c1 into l_dummy;
217 if c1%notfound then
218 --
219 close c1;
220 --
221 -- raise error
222 --
223 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
224 fnd_message.raise_error;
225 --
226 end if;
227 --
228 close c1;
229 --
230 end if;
231 --
232 hr_utility.set_location('Leaving:'||l_proc,10);
233 --
234 end chk_rndg_rl;
235 --
236 -- ----------------------------------------------------------------------------
237 -- |------< chk_rndg_cd >------|
238 -- ----------------------------------------------------------------------------
239 --
240 -- Description
241 -- This procedure is used to check that the lookup value is valid.
242 --
243 -- Pre Conditions
244 -- None.
245 --
246 -- In Parameters
247 -- poe_rt_id PK of record being inserted or updated.
248 -- rndg_cd Value of lookup code.
249 -- effective_date effective date
250 -- object_version_number Object version number of record being
251 -- inserted or updated.
252 --
253 -- Post Success
254 -- Processing continues
255 --
256 -- Post Failure
257 -- Error handled by procedure
258 --
259 -- Access Status
260 -- Internal table handler use only.
261 --
262 Procedure chk_rndg_cd(p_poe_rt_id in number,
263 p_rndg_cd in varchar2,
264 p_effective_date in date,
268 l_api_updating boolean;
265 p_object_version_number in number) is
266 --
267 l_proc varchar2(72) := g_package||'chk_rndg_cd';
269 --
270 Begin
271 --
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274 l_api_updating := ben_prt_shd.api_updating
275 (p_poe_rt_id => p_poe_rt_id,
276 p_effective_date => p_effective_date,
277 p_object_version_number => p_object_version_number);
278 --
279 if (l_api_updating
280 and p_rndg_cd
281 <> nvl(ben_prt_shd.g_old_rec.rndg_cd,hr_api.g_varchar2)
282 or not l_api_updating)
283 and p_rndg_cd is not null then
284 --
285 -- check if value of lookup falls within lookup type.
286 --
287 if hr_api.not_exists_in_hr_lookups
288 (p_lookup_type => 'BEN_RNDG',
289 p_lookup_code => p_rndg_cd,
290 p_effective_date => p_effective_date) then
291 --
292 -- raise error as does not exist as lookup
293 --
294 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
295 fnd_message.raise_error;
296 --
297 end if;
298 --
299 end if;
300 --
301 hr_utility.set_location('Leaving:'||l_proc,10);
302 --
303 end chk_rndg_cd;
304 --
305 -- ----------------------------------------------------------------------------
306 -- |------< chk_no_mx_poe_flag >------|
307 -- ----------------------------------------------------------------------------
308 --
309 -- Description
310 -- This procedure is used to check that the lookup value is valid.
311 --
312 -- Pre Conditions
313 -- None.
314 --
315 -- In Parameters
316 -- poe_rt_id PK of record being inserted or updated.
317 -- no_mx_poe_flag Value of lookup code.
318 -- effective_date effective date
319 -- object_version_number Object version number of record being
320 -- inserted or updated.
321 --
322 -- Post Success
323 -- Processing continues
324 --
325 -- Post Failure
326 -- Error handled by procedure
327 --
328 -- Access Status
329 -- Internal table handler use only.
330 --
331 Procedure chk_no_mx_poe_flag(p_poe_rt_id in number,
332 p_no_mx_poe_flag in varchar2,
333 p_effective_date in date,
334 p_object_version_number in number) is
335 --
336 l_proc varchar2(72) := g_package||'chk_no_mx_poe_flag';
337 l_api_updating boolean;
338 --
339 Begin
340 --
341 hr_utility.set_location('Entering:'||l_proc, 5);
342 --
343 l_api_updating := ben_prt_shd.api_updating
344 (p_poe_rt_id => p_poe_rt_id,
345 p_effective_date => p_effective_date,
346 p_object_version_number => p_object_version_number);
347 --
348 if (l_api_updating
349 and p_no_mx_poe_flag
350 <> nvl(ben_prt_shd.g_old_rec.no_mx_poe_flag,hr_api.g_varchar2)
351 or not l_api_updating) then
352 --
353 -- check if value of lookup falls within lookup type.
354 --
355 --
356 if hr_api.not_exists_in_hr_lookups
357 (p_lookup_type => 'YES_NO',
358 p_lookup_code => p_no_mx_poe_flag,
359 p_effective_date => p_effective_date) then
360 --
361 -- raise error as does not exist as lookup
362 --
363 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
364 fnd_message.raise_error;
365 --
366 end if;
367 --
368 end if;
369 --
370 hr_utility.set_location('Leaving:'||l_proc,10);
371 --
372 end chk_no_mx_poe_flag;
373 --
374 -- ----------------------------------------------------------------------------
375 -- |------< chk_no_mn_poe_flag >------|
376 -- ----------------------------------------------------------------------------
377 --
378 -- Description
379 -- This procedure is used to check that the lookup value is valid.
380 --
381 -- Pre Conditions
382 -- None.
383 --
384 -- In Parameters
385 -- poe_rt_id PK of record being inserted or updated.
386 -- no_mn_poe_flag Value of lookup code.
387 -- effective_date effective date
388 -- object_version_number Object version number of record being
389 -- inserted or updated.
390 --
391 -- Post Success
392 -- Processing continues
393 --
394 -- Post Failure
395 -- Error handled by procedure
396 --
397 -- Access Status
398 -- Internal table handler use only.
399 --
400 Procedure chk_no_mn_poe_flag(p_poe_rt_id in number,
401 p_no_mn_poe_flag in varchar2,
402 p_effective_date in date,
403 p_object_version_number in number) is
404 --
405 l_proc varchar2(72) := g_package||'chk_no_mn_poe_flag';
406 l_api_updating boolean;
407 --
408 Begin
409 --
410 hr_utility.set_location('Entering:'||l_proc, 5);
411 --
412 l_api_updating := ben_prt_shd.api_updating
413 (p_poe_rt_id => p_poe_rt_id,
414 p_effective_date => p_effective_date,
415 p_object_version_number => p_object_version_number);
416 --
417 if (l_api_updating
418 and p_no_mn_poe_flag
419 <> nvl(ben_prt_shd.g_old_rec.no_mn_poe_flag,hr_api.g_varchar2)
420 or not l_api_updating) then
421 --
422 -- check if value of lookup falls within lookup type.
423 --
424 --
425 if hr_api.not_exists_in_hr_lookups
426 (p_lookup_type => 'YES_NO',
427 p_lookup_code => p_no_mn_poe_flag,
428 p_effective_date => p_effective_date) then
429 --
430 -- raise error as does not exist as lookup
431 --
432 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
433 fnd_message.raise_error;
434 --
435 end if;
436 --
437 end if;
438 --
439 hr_utility.set_location('Leaving:'||l_proc,10);
440 --
441 end chk_no_mn_poe_flag;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |------< chk_cbr_dsblty_apls_flag >------|
445 -- ----------------------------------------------------------------------------
446 --
447 -- Description
448 -- This procedure is used to check that the lookup value is valid.
449 --
450 -- Pre Conditions
451 -- None.
452 --
453 -- In Parameters
454 -- poe_rt_id PK of record being inserted or updated.
455 -- cbr_dsblty_apls_flag Value of lookup code.
456 -- effective_date effective date
457 -- object_version_number Object version number of record being
458 -- inserted or updated.
459 --
460 -- Post Success
461 -- Processing continues
462 --
463 -- Post Failure
464 -- Error handled by procedure
465 --
466 -- Access Status
467 -- Internal table handler use only.
468 --
469 Procedure chk_cbr_dsblty_apls_flag(p_poe_rt_id in number,
470 p_cbr_dsblty_apls_flag in varchar2,
471 p_effective_date in date,
472 p_object_version_number in number) is
473 --
474 l_proc varchar2(72) := g_package||'chk_cbr_dsblty_apls_flag';
475 l_api_updating boolean;
476 --
477 Begin
478 --
479 hr_utility.set_location('Entering:'||l_proc, 5);
480 --
481 l_api_updating := ben_prt_shd.api_updating
482 (p_poe_rt_id => p_poe_rt_id,
483 p_effective_date => p_effective_date,
484 p_object_version_number => p_object_version_number);
485 --
486 if (l_api_updating
487 and p_cbr_dsblty_apls_flag
488 <> nvl(ben_prt_shd.g_old_rec.cbr_dsblty_apls_flag,hr_api.g_varchar2)
489 or not l_api_updating) then
490 --
491 -- check if value of lookup falls within lookup type.
492 --
493 --
494 if hr_api.not_exists_in_hr_lookups
495 (p_lookup_type => 'YES_NO',
496 p_lookup_code => p_cbr_dsblty_apls_flag,
497 p_effective_date => p_effective_date) then
498 --
499 -- raise error as does not exist as lookup
500 --
501 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
502 fnd_message.raise_error;
503 --
504 end if;
505 --
506 end if;
507 --
508 hr_utility.set_location('Leaving:'||l_proc,10);
509 --
510 end chk_cbr_dsblty_apls_flag;
511 --
512 ----------------------------------------------------------------------------
513 --|-------------------------< chk_mn_mx_poe_num >--------------------------|
514 ----------------------------------------------------------------------------
515 --
516 -- Description
517 -- This procedure is used to check that minimum poe number is always less
518 -- than max poe number and either the flag is set or the number.
519 --
520 -- Pre Conditions
521 -- None.
522 --
523 -- In Parameters
524 -- poe_rt_id PK of record being inserted or updated.
525 -- mn_poe_num Value of Minimum value.
526 -- mx_poe_num Value of Maximum value.
527 -- no_mn_poe_flag Value of Minimum flag.
528 -- no_mx_poe_flag Value of Maximum flag.
529 -- effective_date effective date
530 -- object_version_number Object version number of record being
531 -- inserted or updated.
532 --
533 -- Post Success
534 -- Processing continues
535 --
536 -- Post Failure
537 -- Error handled by procedure
538 --
539 -- Access Status
540 -- Internal table handler use only.
541 --
542 Procedure chk_mn_mx_poe_num(p_poe_rt_id in number,
543 p_no_mn_poe_flag in varchar2,
544 p_mn_poe_num in number,
545 p_no_mx_poe_flag in varchar2,
546 p_mx_poe_num in number,
547 p_object_version_number in number) is
548 --
549 l_proc varchar2(72) := g_package || 'chk_mn_mx_poe_num';
550 l_api_updating boolean;
551 l_dummy varchar2(1);
552 --
553 Begin
554 --
555 hr_utility.set_location('Entering:'||l_proc, 5);
556 --
557 -- Minimum Poe Number must be < Maximum Poe Number,
558 -- if both are used.
559 --
560 if p_mn_poe_num is not null and p_mx_poe_num is not null then
561 --
562 -- raise error if max value not greater than min value
563 --
564 if p_mx_poe_num <= p_mn_poe_num then
565 --
566 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
567 fnd_message.raise_error;
568 --
569 end if;
570 --
571 end if;
572 --
573 -- If No Minimum poe flag set to "on" (Y),
574 -- then minimum poe number must be blank.
575 --
576 if p_no_mn_poe_flag = 'Y' and
577 p_mn_poe_num is not null then
578 --
579 fnd_message.set_name('BEN','BEN_91054_MIN_VAL_NOT_NULL');
580 fnd_message.raise_error;
581 --
582 elsif p_no_mn_poe_flag = 'N' and
583 p_mn_poe_num is null then
584 --
585 fnd_message.set_name('BEN','BEN_91055_MIN_VAL_REQUIRED');
586 fnd_message.raise_error;
587 --
588 end if;
589 --
590 -- If No Maximum poe flag set to "on" (Y),
591 -- then maximum poe number must be blank.
592 --
593 if p_no_mx_poe_flag = 'Y' and
594 p_mx_poe_num is not null then
595 --
596 fnd_message.set_name('BEN','BEN_91056_MAX_VAL_NOT_NULL');
597 fnd_message.raise_error;
598 --
599 elsif p_no_mx_poe_flag = 'N' and
600 p_mx_poe_num is null then
601 --
602 fnd_message.set_name('BEN','BEN_91057_MAX_VAL_REQUIRED');
603 fnd_message.raise_error;
604 --
605 end if;
606 --
607 hr_utility.set_location('Leaving:'||l_proc,10);
608 --
609 end chk_mn_mx_poe_num;
610 -- ----------------------------------------------------------------------------
611 -- |--------------------------< dt_update_validate >--------------------------|
612 -- ----------------------------------------------------------------------------
613 -- {Start Of Comments}
614 --
615 -- Description:
616 -- This procedure is used for referential integrity of datetracked
617 -- parent entities when a datetrack update operation is taking place
618 -- and where there is no cascading of update defined for this entity.
619 --
620 -- Prerequisites:
621 -- This procedure is called from the update_validate.
622 --
623 -- In Parameters:
624 --
625 -- Post Success:
626 -- Processing continues.
627 --
628 -- Post Failure:
629 --
630 -- Developer Implementation Notes:
631 -- This procedure should not need maintenance unless the HR Schema model
632 -- changes.
633 --
634 -- Access Status:
635 -- Internal Row Handler Use Only.
636 --
637 -- {End Of Comments}
638 -- ----------------------------------------------------------------------------
639 Procedure dt_update_validate
640 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
641 p_datetrack_mode in varchar2,
642 p_validation_start_date in date,
643 p_validation_end_date in date) Is
644 --
645 l_proc varchar2(72) := g_package||'dt_update_validate';
646 l_integrity_error Exception;
647 l_table_name all_tables.table_name%TYPE;
648 --
649 Begin
650 hr_utility.set_location('Entering:'||l_proc, 5);
651 --
652 -- Ensure that the p_datetrack_mode argument is not null
653 --
654 hr_api.mandatory_arg_error
655 (p_api_name => l_proc,
656 p_argument => 'datetrack_mode',
657 p_argument_value => p_datetrack_mode);
658 --
659 -- Only perform the validation if the datetrack update mode is valid
660 --
661 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
662 --
663 --
664 -- Ensure the arguments are not null
665 --
669 p_argument_value => p_validation_start_date);
666 hr_api.mandatory_arg_error
667 (p_api_name => l_proc,
668 p_argument => 'validation_start_date',
670 --
671 hr_api.mandatory_arg_error
672 (p_api_name => l_proc,
673 p_argument => 'validation_end_date',
674 p_argument_value => p_validation_end_date);
675 --
676 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
677 NOT (dt_api.check_min_max_dates
678 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
679 p_base_key_column => 'vrbl_rt_prfl_id',
680 p_base_key_value => p_vrbl_rt_prfl_id,
681 p_from_date => p_validation_start_date,
682 p_to_date => p_validation_end_date))) Then
683 l_table_name := 'ben_vrbl_rt_prfl_f';
684 Raise l_integrity_error;
685 End If;
686 --
687 End If;
688 --
689 hr_utility.set_location(' Leaving:'||l_proc, 10);
690 Exception
691 When l_integrity_error Then
692 --
693 -- A referential integrity check was violated therefore
694 -- we must error
695 --
696 ben_utility.parent_integrity_error(p_table_name => l_table_name);
697 --
698 When Others Then
699 --
700 -- An unhandled or unexpected error has occurred which
701 -- we must report
702 --
703 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
704 fnd_message.set_token('PROCEDURE', l_proc);
705 fnd_message.set_token('STEP','15');
706 fnd_message.raise_error;
707 End dt_update_validate;
708 --
709 -- ----------------------------------------------------------------------------
710 -- |--------------------------< dt_delete_validate >--------------------------|
711 -- ----------------------------------------------------------------------------
712 -- {Start Of Comments}
713 --
714 -- Description:
715 -- This procedure is used for referential integrity of datetracked
716 -- child entities when either a datetrack DELETE or ZAP is in operation
717 -- and where there is no cascading of delete defined for this entity.
718 -- For the datetrack mode of DELETE or ZAP we must ensure that no
719 -- datetracked child rows exist between the validation start and end
720 -- dates.
721 --
722 -- Prerequisites:
723 -- This procedure is called from the delete_validate.
724 --
725 -- In Parameters:
726 --
727 -- Post Success:
728 -- Processing continues.
729 --
730 -- Post Failure:
731 -- If a row exists by determining the returning Boolean value from the
732 -- generic dt_api.rows_exist function then we must supply an error via
733 -- the use of the local exception handler l_rows_exist.
734 --
735 -- Developer Implementation Notes:
736 -- This procedure should not need maintenance unless the HR Schema model
737 -- changes.
738 --
739 -- Access Status:
740 -- Internal Row Handler Use Only.
741 --
742 -- {End Of Comments}
743 -- ----------------------------------------------------------------------------
744 Procedure dt_delete_validate
745 (p_poe_rt_id in number,
746 p_datetrack_mode in varchar2,
747 p_validation_start_date in date,
748 p_validation_end_date in date) Is
749 --
750 l_proc varchar2(72) := g_package||'dt_delete_validate';
751 l_rows_exist Exception;
752 l_table_name all_tables.table_name%TYPE;
753 --
754 Begin
755 hr_utility.set_location('Entering:'||l_proc, 5);
756 --
757 -- Ensure that the p_datetrack_mode argument is not null
758 --
759 hr_api.mandatory_arg_error
760 (p_api_name => l_proc,
761 p_argument => 'datetrack_mode',
762 p_argument_value => p_datetrack_mode);
763 --
764 -- Only perform the validation if the datetrack mode is either
765 -- DELETE or ZAP
766 --
767 If (p_datetrack_mode = 'DELETE' or
768 p_datetrack_mode = 'ZAP') then
769 --
770 --
771 -- Ensure the arguments are not null
772 --
773 hr_api.mandatory_arg_error
774 (p_api_name => l_proc,
775 p_argument => 'validation_start_date',
776 p_argument_value => p_validation_start_date);
777 --
778 hr_api.mandatory_arg_error
779 (p_api_name => l_proc,
780 p_argument => 'validation_end_date',
781 p_argument_value => p_validation_end_date);
782 --
783 hr_api.mandatory_arg_error
784 (p_api_name => l_proc,
785 p_argument => 'poe_rt_id',
786 p_argument_value => p_poe_rt_id);
787 --
788 --
789 --
790 End If;
791 --
792 hr_utility.set_location(' Leaving:'||l_proc, 10);
793 Exception
794 When l_rows_exist Then
795 --
796 -- A referential integrity check was violated therefore
797 -- we must error
798 --
799 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
800 fnd_message.set_token('TABLE_NAME', l_table_name);
801 fnd_message.raise_error;
802 When Others Then
803 --
804 -- An unhandled or unexpected error has occurred which
805 -- we must report
806 --
807 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
808 fnd_message.set_token('PROCEDURE', l_proc);
809 fnd_message.set_token('STEP','15');
810 fnd_message.raise_error;
811 End dt_delete_validate;
812 --
813 -- ----------------------------------------------------------------------------
814 -- |---------------------------< insert_validate >----------------------------|
815 -- ----------------------------------------------------------------------------
816 Procedure insert_validate
820 p_validation_start_date in date,
817 (p_rec in ben_prt_shd.g_rec_type,
818 p_effective_date in date,
819 p_datetrack_mode in varchar2,
821 p_validation_end_date in date) is
822 --
823 l_proc varchar2(72) := g_package||'insert_validate';
824 --
825 Begin
826 hr_utility.set_location('Entering:'||l_proc, 5);
827 --
828 -- Call all supporting business operations
829 --
830 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
831 --
832 chk_poe_rt_id
833 (p_poe_rt_id => p_rec.poe_rt_id,
834 p_effective_date => p_effective_date,
835 p_object_version_number => p_rec.object_version_number);
836 --
837 chk_poe_nnmntry_uom
838 (p_poe_rt_id => p_rec.poe_rt_id,
839 p_poe_nnmntry_uom => p_rec.poe_nnmntry_uom,
840 p_effective_date => p_effective_date,
841 p_object_version_number => p_rec.object_version_number);
842 --
843 chk_rndg_rl
844 (p_poe_rt_id => p_rec.poe_rt_id,
845 p_rndg_rl => p_rec.rndg_rl,
846 p_effective_date => p_effective_date,
847 p_object_version_number => p_rec.object_version_number);
848 --
849 chk_rndg_cd
850 (p_poe_rt_id => p_rec.poe_rt_id,
851 p_rndg_cd => p_rec.rndg_cd,
852 p_effective_date => p_effective_date,
853 p_object_version_number => p_rec.object_version_number);
854 --
855 chk_no_mx_poe_flag
856 (p_poe_rt_id => p_rec.poe_rt_id,
857 p_no_mx_poe_flag => p_rec.no_mx_poe_flag,
858 p_effective_date => p_effective_date,
859 p_object_version_number => p_rec.object_version_number);
860 --
861 chk_no_mn_poe_flag
862 (p_poe_rt_id => p_rec.poe_rt_id,
863 p_no_mn_poe_flag => p_rec.no_mn_poe_flag,
864 p_effective_date => p_effective_date,
865 p_object_version_number => p_rec.object_version_number);
866 --
867 chk_cbr_dsblty_apls_flag
868 (p_poe_rt_id => p_rec.poe_rt_id,
869 p_cbr_dsblty_apls_flag => p_rec.cbr_dsblty_apls_flag,
870 p_effective_date => p_effective_date,
871 p_object_version_number => p_rec.object_version_number);
872 --
873 chk_mn_mx_poe_num
874 (p_poe_rt_id => p_rec.poe_rt_id,
875 p_no_mn_poe_flag => p_rec.no_mn_poe_flag,
876 p_mn_poe_num => p_rec.mn_poe_num,
877 p_no_mx_poe_flag => p_rec.no_mx_poe_flag,
878 p_mx_poe_num => p_rec.mx_poe_num,
879 p_object_version_number => p_rec.object_version_number);
880 --
881 hr_utility.set_location(' Leaving:'||l_proc, 10);
882 End insert_validate;
883 --
884 -- ----------------------------------------------------------------------------
885 -- |---------------------------< update_validate >----------------------------|
886 -- ----------------------------------------------------------------------------
887 Procedure update_validate
888 (p_rec in ben_prt_shd.g_rec_type,
889 p_effective_date in date,
890 p_datetrack_mode in varchar2,
891 p_validation_start_date in date,
892 p_validation_end_date in date) is
893 --
894 l_proc varchar2(72) := g_package||'update_validate';
895 --
896 Begin
897 hr_utility.set_location('Entering:'||l_proc, 5);
898 --
899 -- Call all supporting business operations
900 --
901 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
902 --
903 chk_poe_rt_id
904 (p_poe_rt_id => p_rec.poe_rt_id,
905 p_effective_date => p_effective_date,
906 p_object_version_number => p_rec.object_version_number);
907 --
908 chk_poe_nnmntry_uom
909 (p_poe_rt_id => p_rec.poe_rt_id,
910 p_poe_nnmntry_uom => p_rec.poe_nnmntry_uom,
911 p_effective_date => p_effective_date,
912 p_object_version_number => p_rec.object_version_number);
913 --
914 chk_rndg_rl
915 (p_poe_rt_id => p_rec.poe_rt_id,
916 p_rndg_rl => p_rec.rndg_rl,
917 p_effective_date => p_effective_date,
918 p_object_version_number => p_rec.object_version_number);
919 --
920 chk_rndg_cd
921 (p_poe_rt_id => p_rec.poe_rt_id,
922 p_rndg_cd => p_rec.rndg_cd,
923 p_effective_date => p_effective_date,
924 p_object_version_number => p_rec.object_version_number);
925 --
926 chk_no_mx_poe_flag
927 (p_poe_rt_id => p_rec.poe_rt_id,
928 p_no_mx_poe_flag => p_rec.no_mx_poe_flag,
929 p_effective_date => p_effective_date,
930 p_object_version_number => p_rec.object_version_number);
931 --
932 chk_no_mn_poe_flag
933 (p_poe_rt_id => p_rec.poe_rt_id,
934 p_no_mn_poe_flag => p_rec.no_mn_poe_flag,
935 p_effective_date => p_effective_date,
936 p_object_version_number => p_rec.object_version_number);
937 --
938 chk_cbr_dsblty_apls_flag
939 (p_poe_rt_id => p_rec.poe_rt_id,
940 p_cbr_dsblty_apls_flag => p_rec.cbr_dsblty_apls_flag,
941 p_effective_date => p_effective_date,
942 p_object_version_number => p_rec.object_version_number);
943 --
944 -- Call the datetrack update integrity operation
945 --
946 dt_update_validate
947 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
948 p_datetrack_mode => p_datetrack_mode,
949 p_validation_start_date => p_validation_start_date,
950 p_validation_end_date => p_validation_end_date);
951 --
952 chk_mn_mx_poe_num
953 (p_poe_rt_id => p_rec.poe_rt_id,
954 p_no_mn_poe_flag => p_rec.no_mn_poe_flag,
955 p_mn_poe_num => p_rec.mn_poe_num,
956 p_no_mx_poe_flag => p_rec.no_mx_poe_flag,
957 p_mx_poe_num => p_rec.mx_poe_num,
958 p_object_version_number => p_rec.object_version_number);
959 --
960 hr_utility.set_location(' Leaving:'||l_proc, 10);
961 End update_validate;
962 --
963 -- ----------------------------------------------------------------------------
964 -- |---------------------------< delete_validate >----------------------------|
965 -- ----------------------------------------------------------------------------
966 Procedure delete_validate
967 (p_rec in ben_prt_shd.g_rec_type,
968 p_effective_date in date,
969 p_datetrack_mode in varchar2,
970 p_validation_start_date in date,
971 p_validation_end_date in date) is
972 --
973 l_proc varchar2(72) := g_package||'delete_validate';
974 --
975 Begin
976 hr_utility.set_location('Entering:'||l_proc, 5);
977 --
978 -- Call all supporting business operations
979 --
980 dt_delete_validate
981 (p_datetrack_mode => p_datetrack_mode,
982 p_validation_start_date => p_validation_start_date,
983 p_validation_end_date => p_validation_end_date,
984 p_poe_rt_id => p_rec.poe_rt_id);
985 --
986 hr_utility.set_location(' Leaving:'||l_proc, 10);
987 End delete_validate;
988 --
989 --
990 -- ---------------------------------------------------------------------------
991 -- |---------------------< return_legislation_code >-------------------------|
992 -- ---------------------------------------------------------------------------
993 --
994 function return_legislation_code
995 (p_poe_rt_id in number) return varchar2 is
996 --
997 -- Declare cursor
998 --
999 cursor csr_leg_code is
1000 select a.legislation_code
1001 from per_business_groups a,
1002 ben_poe_rt_f b
1003 where b.poe_rt_id = p_poe_rt_id
1004 and a.business_group_id = b.business_group_id;
1005 --
1006 -- Declare local variables
1007 --
1008 l_legislation_code varchar2(150);
1009 l_proc varchar2(72) := g_package||'return_legislation_code';
1010 --
1011 begin
1012 --
1013 hr_utility.set_location('Entering:'|| l_proc, 10);
1014 --
1015 -- Ensure that all the mandatory parameter are not null
1016 --
1017 hr_api.mandatory_arg_error(p_api_name => l_proc,
1018 p_argument => 'poe_rt_id',
1019 p_argument_value => p_poe_rt_id);
1020 --
1021 open csr_leg_code;
1022 --
1023 fetch csr_leg_code into l_legislation_code;
1024 --
1025 if csr_leg_code%notfound then
1026 --
1027 close csr_leg_code;
1028 --
1029 -- The primary key is invalid therefore we must error
1030 --
1031 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1032 fnd_message.raise_error;
1033 --
1034 end if;
1035 --
1036 close csr_leg_code;
1037 --
1038 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1039 --
1040 return l_legislation_code;
1041 --
1042 end return_legislation_code;
1043 --
1044 end ben_prt_bus;