[Home] [Help]
PACKAGE BODY: APPS.BEN_LPL_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_lpl_bus as
2 /* $Header: belplrhi.pkb 120.0 2005/05/28 03:31:15 appldev noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_lpl_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ler_per_info_cs_ler_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 -- ler_per_info_cs_ler_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_ler_per_info_cs_ler_id(p_ler_per_info_cs_ler_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_ler_per_info_cs_ler_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_lpl_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_ler_per_info_cs_ler_id => p_ler_per_info_cs_ler_id,
51 p_object_version_number => p_object_version_number);
52 --
53
54 if (l_api_updating
55 and nvl(p_ler_per_info_cs_ler_id,hr_api.g_number)
56 <> ben_lpl_shd.g_old_rec.ler_per_info_cs_ler_id) then
57 --
58 -- raise error as PK has changed
59 --
60 ben_lpl_shd.constraint_error('BEN_LER_PER_INFO_CS_LER_F_PK');
61 --
62 elsif not l_api_updating then
63 --
64 -- check if PK is null
65 --
66 if p_ler_per_info_cs_ler_id is not null then
67 --
68 -- raise error as PK is not null
69 --
70 ben_lpl_shd.constraint_error('BEN_LER_PER_INFO_CS_LER_F_PK');
71 --
72 end if;
76 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 end if;
75 --
77 --
78 End chk_ler_per_info_cs_ler_id;
79 --
80 -- ----------------------------------------------------------------------------
81 -- |------< chk_per_info_chg_cs_ler_id >------|
82 -- ----------------------------------------------------------------------------
83 --
84 -- Description
85 -- This procedure checks that a referenced foreign key actually exists
86 -- in the referenced table, the key is required and is unique for this
87 -- life event.
88 --
89 -- Pre-Conditions
90 -- None.
91 --
92 -- In Parameters
93 -- p_ler_per_info_cs_ler_id PK
94 -- p_per_info_chg_cs_ler_id ID of FK column
95 -- p_effective_date session date
96 -- p_object_version_number object version number
97 --
98 -- Post Success
99 -- Processing continues
100 --
101 -- Post Failure
102 -- Error raised.
103 --
104 -- Access Status
105 -- Internal table handler use only.
106 --
107 Procedure chk_per_info_chg_cs_ler_id
108 (p_ler_per_info_cs_ler_id in number,
109 p_per_info_chg_cs_ler_id in number,
110 p_ler_id in number,
111 p_validation_start_date in date,
112 p_validation_end_date in date,
113 p_effective_date in date,
114 p_business_group_id in number,
115 p_object_version_number in number) is
116 --
117 l_proc varchar2(72) := g_package||'chk_per_info_chg_cs_ler_id';
118 l_api_updating boolean;
119 l_dummy varchar2(1);
120 --
121 cursor c1 is
122 select null
123 from ben_per_info_chg_cs_ler_f a
124 where a.per_info_chg_cs_ler_id = p_per_info_chg_cs_ler_id
125 and p_effective_date between effective_start_date
126 and effective_end_date;
127
128 CURSOR c2 (p_ler_per_info_cs_ler_id number
129 ,p_ler_id number
130 ,p_per_info_chg_cs_ler_id number
131 ,p_business_group_id number
132 ,p_validation_start_date date
133 ,p_validation_end_date date) IS
134 SELECT 'x'
135 FROM ben_ler_per_info_cs_ler_f
136 WHERE ler_per_info_cs_ler_id <> nvl(p_ler_per_info_cs_ler_id, hr_api.g_number)
137 AND per_info_chg_cs_ler_id = p_per_info_chg_cs_ler_id
138 AND ler_id = p_ler_id
139 AND business_group_id + 0 = p_business_group_id
140 AND p_validation_start_date <= effective_end_date
141 AND p_validation_end_date >= effective_start_date;
142 --
143 Begin
144 --
145 hr_utility.set_location('Entering:'||l_proc,5);
146 --
147 if p_per_info_chg_cs_ler_id is null then
148 fnd_message.set_name('BEN', 'BEN_91016_PERSON_CHANGE_REQ');
149 fnd_message.raise_error;
150 end if;
151
152 l_api_updating := ben_lpl_shd.api_updating
153 (p_ler_per_info_cs_ler_id => p_ler_per_info_cs_ler_id,
154 p_effective_date => p_effective_date,
155 p_object_version_number => p_object_version_number);
156
157 --
158 if (l_api_updating
159 and nvl(p_per_info_chg_cs_ler_id,hr_api.g_number)
160 <> nvl(ben_lpl_shd.g_old_rec.per_info_chg_cs_ler_id,hr_api.g_number)
161 or not l_api_updating) then
162 --
163 -- check if per_info_chg_cs_ler_id value exists in
164 -- ben_per_info_chg_cs_ler_f table
165 open c1;
166 fetch c1 into l_dummy;
167 if c1%notfound then
168 close c1;
169 -- raise error as FK does not relate to PK in ben_per_info_chg_cs_ler
170 -- table.
171 ben_lpl_shd.constraint_error('BEN_LER_PER_INFO_CS_LER_FK2');
172 end if;
173 close c1;
174 --
175 -- check if per_info_chg_cs_ler_id is unique for this ler.
176 open c2
177 (p_ler_per_info_cs_ler_id => p_ler_per_info_cs_ler_id
178 ,p_ler_id => p_ler_id
179 ,p_per_info_chg_cs_ler_id => p_per_info_chg_cs_ler_id
180 ,p_business_group_id => p_business_group_id
181 ,p_validation_start_date => p_validation_start_date
182 ,p_validation_end_date => p_validation_end_date) ;
183
184 fetch c2 into l_dummy;
185 if c2%found then
186 close c2;
187 fnd_message.set_name('BEN', 'BEN_91017_PERSON_CHANGE_UNIQUE');
188 fnd_message.raise_error;
189 end if;
190 close c2;
191 --
192 end if;
193 --
194 hr_utility.set_location('Leaving:'||l_proc,10);
195 --
196 End chk_per_info_chg_cs_ler_id;
197 --
198 -- ----------------------------------------------------------------------------
199 -- |------< chk_ler_per_info_cs_ler_rl >------|
200 -- ----------------------------------------------------------------------------
201 --
202 -- Description
203 -- This procedure is used to check that the Formula Rule is valid.
204 --
205 -- Pre Conditions
206 -- None.
207 --
208 -- In Parameters
209 -- ler_per_info_cs_ler_id PK of record being inserted or updated.
210 -- ler_per_info_cs_ler_rl Value of formula rule id.
211 -- effective_date effective date
212 -- object_version_number Object version number of record being
213 -- inserted or updated.
214 --
215 -- Post Success
216 -- Processing continues
220 --
217 --
218 -- Post Failure
219 -- Error handled by procedure
221 -- Access Status
222 -- Internal table handler use only.
223 --
224 Procedure chk_ler_per_info_cs_ler_rl(p_ler_per_info_cs_ler_id in number,
225 p_ler_per_info_cs_ler_rl in number,
226 p_business_group_id in number,
227 p_effective_date in date,
228 p_object_version_number in number) is
229 --
230 l_proc varchar2(72) := g_package||'chk_ler_per_info_cs_ler_rl';
231 l_api_updating boolean;
232 l_dummy varchar2(1);
233 --
234 cursor c1 is
235 select null
236 from ff_formulas_f ff
237 ,per_business_groups pbg
238 where ff.formula_id = p_ler_per_info_cs_ler_rl
239 and ff.formula_type_id = -46 -- Person Information Causes Life Event
240 and pbg.business_group_id = p_business_group_id
241 and nvl(ff.business_group_id, p_business_group_id) = p_business_group_id
242 and nvl(ff.legislation_code, pbg.legislation_code) = pbg.legislation_code
243 and p_effective_date
244 between ff.effective_start_date
245 and ff.effective_end_date;
246 --
247 Begin
248 --
249 hr_utility.set_location('Entering:'||l_proc, 5);
250 --
251 l_api_updating := ben_lpl_shd.api_updating
252 (p_ler_per_info_cs_ler_id => p_ler_per_info_cs_ler_id,
253 p_effective_date => p_effective_date,
254 p_object_version_number => p_object_version_number);
255 --
256 if (l_api_updating
257 and nvl(p_ler_per_info_cs_ler_rl,hr_api.g_number)
258 <> ben_lpl_shd.g_old_rec.ler_per_info_cs_ler_rl
259 or not l_api_updating)
260 and p_ler_per_info_cs_ler_rl is not null then
261 --
262 -- check if value of formula rule is valid.
263 --
264 open c1;
265 --
266 -- fetch value from cursor if it returns a record then the
267 -- formula is valid otherwise its invalid
268 --
269 fetch c1 into l_dummy;
270 if c1%notfound then
271 --
272 close c1;
273 --
274 -- raise error
275 --
276 fnd_message.set_name('BEN','BEN_91007_INVALID_RULE');
277 fnd_message.raise_error;
278 --
279 end if;
280 --
281 close c1;
282 --
283 end if;
284 --
285 hr_utility.set_location('Leaving:'||l_proc,10);
286 --
287 end chk_ler_per_info_cs_ler_rl;
288 -- ----------------------------------------------------------------------------
289 -- |------< chk_ler_id >------|
290 -- ----------------------------------------------------------------------------
291 --
292 -- Description
293 -- This procedure checks that the ler for which this child record is being
294 -- created is not of certain delivered types.
295 --
296 -- This procedure is called from other APIs, so do not add additional logic
297 -- here unless it's needed by other modules too.
298 --
299 -- Pre-Conditions
300 -- None.
301 --
302 -- In Parameters
303 -- p_ler_id
304 -- p_effective_date session date
305 -- p_object_version_number object version number
306 --
307 -- Post Success
308 -- Processing continues
309 --
310 -- Post Failure
311 -- Error raised.
312 --
313 -- Access Status
314 -- Internal table handler use only - but called from other table handlers.
315 --
316 Procedure chk_ler_id
317 (p_ler_id in number,
318 p_effective_date in date,
319 p_validation_start_date in date,
320 p_validation_end_date in date,
321 p_business_group_id in number,
322 p_object_version_number in number) is
323 --
324 l_proc varchar2(72) := g_package||'chk_ler_id';
325 l_api_updating boolean;
326 l_dummy varchar2(1);
327 --
328 cursor c1 is
329 select 'x'
330 from ben_ler_f a
331 where a.ler_id = p_ler_id
332 AND a.business_group_id + 0 = p_business_group_id
333 AND p_validation_start_date <= a.effective_end_date
334 AND p_validation_end_date >= a.effective_start_date
335 and a.typ_cd in ('DRVDAGE', 'DRVDLOS', 'DRVDCAL',
336 'DRVDHRW', 'DRVDCMP', 'DRVDTPF', 'SCHEDDO','SCHEDDA','SCHEDDU');
337 --
338 Begin
339 --
340 hr_utility.set_location('Entering:'||l_proc,5);
341 --
342
343 --
344 -- check if the parent ler is of certain types
345
346 open c1;
347 fetch c1 into l_dummy;
348 if c1%found then
349 close c1;
350 fnd_message.set_name('BEN','BEN_91425_DELIVERED_TYPE_CHILD');
351 fnd_message.raise_error;
352 end if;
353 close c1;
354 --
355 --
356 hr_utility.set_location('Leaving:'||l_proc,10);
357 --
358 End chk_ler_id;
359 --
360 -- ----------------------------------------------------------------------------
361 -- |------< chk_ler_typ_cd >------|
362 -- ----------------------------------------------------------------------------
363 --
364 -- Description
365 -- This procedure checks the ler for which this child record is being
366 -- created is of type ABS for OSB customers.
367 --
368 --
369 -- Pre-Conditions
370 -- None.
371 --
375 -- p_object_version_number object version number
372 -- In Parameters
373 -- p_ler_id
374 -- p_effective_date session date
376 --
377 -- Post Success
378 -- Processing continues
379 --
380 -- Post Failure
381 -- Error raised.
382 --
383 -- Access Status
384 -- Internal table handler use only - but called from other table handlers.
385 --
386 Procedure chk_ler_typ_cd
387 (p_ler_id in number,
388 p_effective_date in date,
389 p_validation_start_date in date,
390 p_validation_end_date in date,
391 p_business_group_id in number,
392 p_object_version_number in number) is
393 --
394 l_proc varchar2(72) := g_package||'chk_ler_typ_cd';
395 l_api_updating boolean;
396 l_status varchar2(1);
397 l_dummy ben_ler_f.typ_cd%type ; -- varchar2(1); bug 3067285
398 l_exist varchar2(1);
399 --
400 cursor c1 is
401 select status
402 from fnd_product_installations
403 where application_id = 805;
404 --
405 cursor c2 is
406 select typ_cd
407 from ben_ler_f a
408 where a.ler_id = p_ler_id
409 AND a.typ_cd = 'ABS'
410 AND a.business_group_id = p_business_group_id
411 AND p_validation_start_date <= a.effective_end_date
412 AND p_validation_end_date >= a.effective_start_date;
413 --
414 --iRec
415 cursor c3 is
416 select null
417 from ben_ler_f ler
418 where ler.ler_id = p_ler_id
419 and p_validation_start_date <= ler.effective_end_date
420 and p_validation_end_date >= ler.effective_start_date
421 and typ_cd = 'IREC';
422 --iRec
423 Begin
424 --
425 hr_utility.set_location('Entering:'||l_proc,5);
426 --
427 --iRec
428 open c3;
429 fetch c3 into l_dummy;
430 if c3%found then
431 --
432 close c3;
433 --
434 --Raise error : Life events with type = iRecruitment cannot have person changes
435 --associated.
436 --
437 fnd_message.set_name('BEN','BEN_93927_NOASSC_LPL_IREC_LER');
438 fnd_message.raise_error;
439 --
440 end if;
441 --
442 close c3;
443 --
444 --iRec
445
446 -- check if oab/osb customer
447 open c1;
448 fetch c1 into l_status;
449 close c1;
450
451 -- For osb customers, allow child record only if ler typ is ABS
452 if (nvl(l_status,'N') <> 'I')
453 then
454 -- check if the ler is of type ABS
455 open c2;
456 fetch c2 into l_dummy;
457 if (c2%notfound)
458 then
459 close c2;
460 fnd_message.set_name('BEN','BEN_91425_CHILD_NOT_ALLOWED');
461 fnd_message.raise_error;
462 end if;
463 close c2;
464 end if;
465 --
466 --
467 hr_utility.set_location('Leaving:'||l_proc,10);
468 --
469 End chk_ler_typ_cd;
470 -- ----------------------------------------------------------------------------
471 -- |-----------------------< chk_not_multiple_tables >------------------------|
472 -- ----------------------------------------------------------------------------
473 --
474 -- Description
475 -- This procedure is used to check that the attached per_info_chg_cs_ler_id
476 -- does not conflict with any other table.
477 --
478 -- Pre Conditions
479 -- None.
480 --
481 -- In Parameters
482 -- ler_per_info_cs_ler_id PK of record being inserted or updated.
483 -- per_info_chg_cs_ler_id Value of linked record.
484 -- ler_id Value of life event being linked.
485 -- effective_date effective date
486 -- object_version_number Object version number of record being
487 -- inserted or updated.
488 --
489 -- Post Success
490 -- Processing continues
491 --
492 -- Post Failure
493 -- Error handled by procedure
494 --
495 -- Access Status
496 -- Internal table handler use only.
497 --
498 Procedure chk_not_multiple_tables
499 (p_ler_per_info_cs_ler_id in number,
500 p_per_info_chg_cs_ler_id in number,
501 p_ler_id in number,
502 p_effective_date in date,
503 p_object_version_number in number) is
504 --
505 l_proc varchar2(72) := g_package||'chk_not_multiple_tables';
506 l_api_updating boolean;
507 l_dummy varchar2(1);
508 --
509 cursor c_source_table is
510 select psl.source_table
511 from ben_per_info_chg_cs_ler_f psl
512 where psl.per_info_chg_cs_ler_id = p_per_info_chg_cs_ler_id
513 and p_effective_date
514 between psl.effective_start_date
515 and psl.effective_end_date;
516 --
517 l_source_table varchar2(30);
518 --
519 cursor c_check_multiple(p_source_table varchar2) is
520 select null
521 from ben_ler_per_info_cs_ler_f lpl,
522 ben_per_info_chg_cs_ler_f psl
523 where lpl.ler_per_info_cs_ler_id <> nvl(p_ler_per_info_cs_ler_id,-1)
524 and lpl.ler_id = p_ler_id
525 and p_effective_date
526 between lpl.effective_start_date
527 and lpl.effective_end_date
528 and lpl.per_info_chg_cs_ler_id = psl.per_info_chg_cs_ler_id
529 and p_effective_date
530 between psl.effective_start_date
531 and psl.effective_end_date
532 and psl.source_table <> p_source_table;
533 --
534 Begin
535 --
536 hr_utility.set_location('Entering:'||l_proc, 5);
537 --
538 l_api_updating := ben_lpl_shd.api_updating
539 (p_ler_per_info_cs_ler_id => p_ler_per_info_cs_ler_id,
540 p_effective_date => p_effective_date,
541 p_object_version_number => p_object_version_number);
542 --
543 if (l_api_updating
544 and nvl(p_per_info_chg_cs_ler_id,hr_api.g_number)
545 <> ben_lpl_shd.g_old_rec.per_info_chg_cs_ler_id
546 or not l_api_updating)
547 and p_per_info_chg_cs_ler_id is not null then
548 --
549 open c_source_table;
550 --
551 fetch c_source_table into l_source_table;
552 --
553 close c_source_table;
554 --
555 -- check if the triggering logic uses multiple tables.
556 --
557 open c_check_multiple(l_source_table);
558 --
559 fetch c_check_multiple into l_dummy;
560 if c_check_multiple%found then
561 --
562 close c_check_multiple;
563 --
564 -- raise error
565 --
566 fnd_message.set_name('BEN','BEN_92559_MULTIPLE_TABLES');
567 fnd_message.raise_error;
568 --
569 end if;
570 --
571 close c_check_multiple;
572 --
573 end if;
574 --
575 hr_utility.set_location('Leaving:'||l_proc,10);
576 --
577 end chk_not_multiple_tables;
578 --
579 -- ----------------------------------------------------------------------------
580 -- |--------------------------< dt_update_validate >--------------------------|
581 -- ----------------------------------------------------------------------------
582 -- {Start Of Comments}
583 --
584 -- Description:
585 -- This procedure is used for referential integrity of datetracked
586 -- parent entities when a datetrack update operation is taking place
587 -- and where there is no cascading of update defined for this entity.
588 --
589 -- Prerequisites:
590 -- This procedure is called from the update_validate.
591 --
592 -- In Parameters:
593 --
594 -- Post Success:
595 -- Processing continues.
596 --
597 -- Post Failure:
598 --
599 -- Developer Implementation Notes:
600 -- This procedure should not need maintenance unless the HR Schema model
601 -- changes.
602 --
603 -- Access Status:
604 -- Internal Row Handler Use Only.
605 --
606 -- {End Of Comments}
607 -- ----------------------------------------------------------------------------
608 Procedure dt_update_validate
609 (p_formula_id in number default hr_api.g_number,
610 p_datetrack_mode in varchar2,
611 p_validation_start_date in date,
612 p_validation_end_date in date) Is
613 --
614 l_proc varchar2(72) := g_package||'dt_update_validate';
615 l_integrity_error Exception;
616 l_table_name all_tables.table_name%TYPE;
617 --
618 Begin
619 hr_utility.set_location('Entering:'||l_proc, 5);
620 --
621 -- Ensure that the p_datetrack_mode argument is not null
622 --
623 hr_api.mandatory_arg_error
624 (p_api_name => l_proc,
625 p_argument => 'datetrack_mode',
626 p_argument_value => p_datetrack_mode);
627 --
628 -- Only perform the validation if the datetrack update mode is valid
629 --
630 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) 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 If ((nvl(p_formula_id, hr_api.g_number) <> hr_api.g_number) and
646 NOT (dt_api.check_min_max_dates
647 (p_base_table_name => 'ff_formulas_f',
648 p_base_key_column => 'formula_id',
649 p_base_key_value => p_formula_id,
650 p_from_date => p_validation_start_date,
651 p_to_date => p_validation_end_date))) Then
652 l_table_name := 'ff_formulas_f';
653 Raise l_integrity_error;
654 End If;
655 --
656 End If;
657 --
658 hr_utility.set_location(' Leaving:'||l_proc, 10);
659 Exception
660 When l_integrity_error Then
661 --
665 ben_utility.parent_integrity_error(p_table_name => l_table_name);
662 -- A referential integrity check was violated therefore
663 -- we must error
664 --
666 When Others Then
667 --
668 -- An unhandled or unexpected error has occurred which
669 -- we must report
670 --
671 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
672 fnd_message.set_token('PROCEDURE', l_proc);
673 fnd_message.set_token('STEP','15');
674 fnd_message.raise_error;
675 End dt_update_validate;
676 --
677 -- ----------------------------------------------------------------------------
678 -- |--------------------------< dt_delete_validate >--------------------------|
679 -- ----------------------------------------------------------------------------
680 -- {Start Of Comments}
681 --
682 -- Description:
683 -- This procedure is used for referential integrity of datetracked
684 -- child entities when either a datetrack DELETE or ZAP is in operation
685 -- and where there is no cascading of delete defined for this entity.
686 -- For the datetrack mode of DELETE or ZAP we must ensure that no
687 -- datetracked child rows exist between the validation start and end
688 -- dates.
689 --
690 -- Prerequisites:
691 -- This procedure is called from the delete_validate.
692 --
693 -- In Parameters:
694 --
695 -- Post Success:
696 -- Processing continues.
697 --
698 -- Post Failure:
699 -- If a row exists by determining the returning Boolean value from the
700 -- generic dt_api.rows_exist function then we must supply an error via
701 -- the use of the local exception handler l_rows_exist.
702 --
703 -- Developer Implementation Notes:
704 -- This procedure should not need maintenance unless the HR Schema model
705 -- changes.
706 --
707 -- Access Status:
708 -- Internal Row Handler Use Only.
709 --
710 -- {End Of Comments}
711 -- ----------------------------------------------------------------------------
712 Procedure dt_delete_validate
713 (p_ler_per_info_cs_ler_id in number,
714 p_datetrack_mode in varchar2,
715 p_validation_start_date in date,
716 p_validation_end_date in date) Is
717 --
718 l_proc varchar2(72) := g_package||'dt_delete_validate';
719 l_rows_exist Exception;
720 l_table_name all_tables.table_name%TYPE;
721 --
722 Begin
723 hr_utility.set_location('Entering:'||l_proc, 5);
724 --
725 -- Ensure that the p_datetrack_mode argument is not null
726 --
727 hr_api.mandatory_arg_error
728 (p_api_name => l_proc,
729 p_argument => 'datetrack_mode',
730 p_argument_value => p_datetrack_mode);
731 --
732 -- Only perform the validation if the datetrack mode is either
733 -- DELETE or ZAP
734 --
735 If (p_datetrack_mode = 'DELETE' or
736 p_datetrack_mode = 'ZAP') then
737 --
738 --
739 -- Ensure the arguments are not null
740 --
741 hr_api.mandatory_arg_error
742 (p_api_name => l_proc,
743 p_argument => 'validation_start_date',
744 p_argument_value => p_validation_start_date);
745 --
746 hr_api.mandatory_arg_error
747 (p_api_name => l_proc,
748 p_argument => 'validation_end_date',
749 p_argument_value => p_validation_end_date);
750 --
751 hr_api.mandatory_arg_error
752 (p_api_name => l_proc,
753 p_argument => 'ler_per_info_cs_ler_id',
754 p_argument_value => p_ler_per_info_cs_ler_id);
755 --
756 --
757 --
758 End If;
759 --
760 hr_utility.set_location(' Leaving:'||l_proc, 10);
761 Exception
762 When l_rows_exist Then
763 --
764 -- A referential integrity check was violated therefore
765 -- we must error
766 --
767 ben_utility.child_exists_error(p_table_name => l_table_name);
768 When Others Then
769 --
770 -- An unhandled or unexpected error has occurred which
771 -- we must report
772 --
773 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
774 fnd_message.set_token('PROCEDURE', l_proc);
775 fnd_message.set_token('STEP','15');
776 fnd_message.raise_error;
777 End dt_delete_validate;
778 --
779 -- ----------------------------------------------------------------------------
780 -- |---------------------------< insert_validate >----------------------------|
781 -- ----------------------------------------------------------------------------
782 Procedure insert_validate
783 (p_rec in ben_lpl_shd.g_rec_type,
784 p_effective_date in date,
785 p_datetrack_mode in varchar2,
786 p_validation_start_date in date,
787 p_validation_end_date in date) is
788 --
789 l_proc varchar2(72) := g_package||'insert_validate';
790 --
791 Begin
792 hr_utility.set_location('Entering:'||l_proc, 5);
793 --
794 -- Call all supporting business operations
795 --
796 --
797 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
798 --
799 chk_ler_per_info_cs_ler_id
800 (p_ler_per_info_cs_ler_id => p_rec.ler_per_info_cs_ler_id,
801 p_effective_date => p_effective_date,
802 p_object_version_number => p_rec.object_version_number);
803 --
804 chk_per_info_chg_cs_ler_id
805 (p_ler_per_info_cs_ler_id => p_rec.ler_per_info_cs_ler_id,
806 p_per_info_chg_cs_ler_id => p_rec.per_info_chg_cs_ler_id,
807 p_ler_id => p_rec.ler_id,
808 p_validation_start_date => p_validation_start_date,
809 p_validation_end_date => p_validation_end_date,
813 --
810 p_effective_date => p_effective_date,
811 p_business_group_id => p_rec.business_group_id,
812 p_object_version_number => p_rec.object_version_number);
814 chk_ler_id
815 (p_ler_id => p_rec.ler_id,
816 p_validation_start_date => p_validation_start_date,
817 p_validation_end_date => p_validation_end_date,
818 p_effective_date => p_effective_date,
819 p_business_group_id => p_rec.business_group_id,
820 p_object_version_number => p_rec.object_version_number);
821 --
822 chk_ler_typ_cd
823 (p_ler_id => p_rec.ler_id,
824 p_validation_start_date => p_validation_start_date,
825 p_validation_end_date => p_validation_end_date,
826 p_effective_date => p_effective_date,
827 p_business_group_id => p_rec.business_group_id,
828 p_object_version_number => p_rec.object_version_number);
829 --
830 chk_ler_per_info_cs_ler_rl
831 (p_ler_per_info_cs_ler_id => p_rec.ler_per_info_cs_ler_id,
832 p_ler_per_info_cs_ler_rl => p_rec.ler_per_info_cs_ler_rl,
833 p_business_group_id => p_rec.business_group_id,
834 p_effective_date => p_effective_date,
835 p_object_version_number => p_rec.object_version_number);
836 --
837 hr_utility.set_location(' Leaving:'||l_proc, 10);
838 End insert_validate;
839 --
840 -- ----------------------------------------------------------------------------
841 -- |---------------------------< update_validate >----------------------------|
842 -- ----------------------------------------------------------------------------
843 Procedure update_validate
844 (p_rec in ben_lpl_shd.g_rec_type,
845 p_effective_date in date,
846 p_datetrack_mode in varchar2,
847 p_validation_start_date in date,
848 p_validation_end_date in date) is
849 --
850 l_proc varchar2(72) := g_package||'update_validate';
851 --
852 Begin
853 hr_utility.set_location('Entering:'||l_proc, 5);
854 --
855 -- Call all supporting business operations
856 --
857 --
858 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
859 --
860 chk_ler_per_info_cs_ler_id
861 (p_ler_per_info_cs_ler_id => p_rec.ler_per_info_cs_ler_id,
862 p_effective_date => p_effective_date,
863 p_object_version_number => p_rec.object_version_number);
864 --
865 chk_per_info_chg_cs_ler_id
866 (p_ler_per_info_cs_ler_id => p_rec.ler_per_info_cs_ler_id,
867 p_per_info_chg_cs_ler_id => p_rec.per_info_chg_cs_ler_id,
868 p_ler_id => p_rec.ler_id,
869 p_validation_start_date => p_validation_start_date,
870 p_validation_end_date => p_validation_end_date,
871 p_effective_date => p_effective_date,
872 p_business_group_id => p_rec.business_group_id,
873 p_object_version_number => p_rec.object_version_number);
874 --
875 chk_ler_id
876 (p_ler_id => p_rec.ler_id,
877 p_validation_start_date => p_validation_start_date,
878 p_validation_end_date => p_validation_end_date,
879 p_effective_date => p_effective_date,
880 p_business_group_id => p_rec.business_group_id,
881 p_object_version_number => p_rec.object_version_number);
882
883 --
884 chk_ler_per_info_cs_ler_rl
885 (p_ler_per_info_cs_ler_id => p_rec.ler_per_info_cs_ler_id,
886 p_ler_per_info_cs_ler_rl => p_rec.ler_per_info_cs_ler_rl,
887 p_business_group_id => p_rec.business_group_id,
888 p_effective_date => p_effective_date,
889 p_object_version_number => p_rec.object_version_number);
890 --
891 -- Call the datetrack update integrity operation
892 --
893 dt_update_validate
894 (p_formula_id => p_rec.ler_per_info_cs_ler_rl,
895 p_datetrack_mode => p_datetrack_mode,
896 p_validation_start_date => p_validation_start_date,
897 p_validation_end_date => p_validation_end_date);
898 --
899 hr_utility.set_location(' Leaving:'||l_proc, 10);
900 End update_validate;
901 --
902 -- ----------------------------------------------------------------------------
903 -- |---------------------------< delete_validate >----------------------------|
904 -- ----------------------------------------------------------------------------
905 Procedure delete_validate
906 (p_rec in ben_lpl_shd.g_rec_type,
907 p_effective_date in date,
908 p_datetrack_mode in varchar2,
909 p_validation_start_date in date,
910 p_validation_end_date in date) is
911 --
912 l_proc varchar2(72) := g_package||'delete_validate';
913 --
914 Begin
915 hr_utility.set_location('Entering:'||l_proc, 5);
916 --
917 -- Call all supporting business operations
918 --
919 dt_delete_validate
920 (p_datetrack_mode => p_datetrack_mode,
921 p_validation_start_date => p_validation_start_date,
922 p_validation_end_date => p_validation_end_date,
923 p_ler_per_info_cs_ler_id => p_rec.ler_per_info_cs_ler_id);
924 --
925 hr_utility.set_location(' Leaving:'||l_proc, 10);
926 End delete_validate;
927 --
928 --
929 -- ---------------------------------------------------------------------------
930 -- |---------------------< return_legislation_code >-------------------------|
931 -- ---------------------------------------------------------------------------
932 --
933 function return_legislation_code
934 (p_ler_per_info_cs_ler_id in number) return varchar2 is
935 --
936 -- Declare cursor
937 --
938 cursor csr_leg_code is
939 select a.legislation_code
940 from per_business_groups a,
941 ben_ler_per_info_cs_ler_f b
942 where b.ler_per_info_cs_ler_id = p_ler_per_info_cs_ler_id
943 and a.business_group_id = b.business_group_id;
944 --
945 -- Declare local variables
946 --
947 l_legislation_code varchar2(150);
948 l_proc varchar2(72) := g_package||'return_legislation_code';
949 --
950 begin
951 --
952 hr_utility.set_location('Entering:'|| l_proc, 10);
953 --
954 -- Ensure that all the mandatory parameter are not null
955 --
956 hr_api.mandatory_arg_error(p_api_name => l_proc,
957 p_argument => 'ler_per_info_cs_ler_id',
958 p_argument_value => p_ler_per_info_cs_ler_id);
959 --
960 open csr_leg_code;
961 --
962 fetch csr_leg_code into l_legislation_code;
963 --
964 if csr_leg_code%notfound then
965 --
966 close csr_leg_code;
967 --
968 -- The primary key is invalid therefore we must error
969 --
970 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
971 fnd_message.raise_error;
972 --
973 end if;
974 --
975 close csr_leg_code;
976 --
977 hr_utility.set_location(' Leaving:'|| l_proc, 20);
978 --
979 return l_legislation_code;
980 --
981 end return_legislation_code;
982 --
983 end ben_lpl_bus;