[Home] [Help]
PACKAGE BODY: APPS.BEN_LGE_BUS
Source
1 Package Body ben_lge_bus as
2 /* $Header: belgerhi.pkb 120.0 2005/05/28 03:23:02 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_lge_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ler_chg_pgm_enrt_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_chg_pgm_enrt_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_chg_pgm_enrt_id(p_ler_chg_pgm_enrt_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_chg_pgm_enrt_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_lge_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_ler_chg_pgm_enrt_id,hr_api.g_number)
55 <> ben_lge_shd.g_old_rec.ler_chg_pgm_enrt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_lge_shd.constraint_error('BEN_LER_CHG_PGM_ENRT_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_ler_chg_pgm_enrt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_lge_shd.constraint_error('BEN_LER_CHG_PGM_ENRT_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_ler_chg_pgm_enrt_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_dflt_enrt_rl >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the Formula Rule is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
91 -- dflt_enrt_rl Value of formula rule id.
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_dflt_enrt_rl(p_ler_chg_pgm_enrt_id in number,
106 p_dflt_enrt_rl in number,
107 p_effective_date in date,
108 p_business_group_id in number,
109 p_object_version_number in number) is
110 --
111 l_proc varchar2(72) := g_package||'chk_dflt_enrt_rl';
112 l_api_updating boolean;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc, 5);
117 --
118 l_api_updating := ben_lge_shd.api_updating
119 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
120 p_effective_date => p_effective_date,
121 p_object_version_number => p_object_version_number);
122 --
123 if (l_api_updating
124 and nvl(p_dflt_enrt_rl,hr_api.g_number)
125 <> ben_lge_shd.g_old_rec.dflt_enrt_rl
126 or not l_api_updating)
127 and p_dflt_enrt_rl is not null then
128 --
129 -- check if value of formula rule is valid.
130 --
131 if not benutils.formula_exists
132 (p_formula_id => p_dflt_enrt_rl,
133 p_formula_type_id => -32,
134 p_business_group_id => p_business_group_id,
135 p_effective_date => p_effective_date) then
136 --
137 -- raise error
138 --
139 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
140 fnd_message.set_token('ID',p_dflt_enrt_rl);
141 fnd_message.set_token('TYPE_ID',-32);
142 fnd_message.raise_error;
143 --
144 end if;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 end chk_dflt_enrt_rl;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_dflt_enrt_cd >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure is used to check that the lookup value is valid.
158 --
159 -- Pre Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
164 -- dflt_enrt_cd Value of lookup code.
165 -- effective_date effective date
166 -- object_version_number Object version number of record being
167 -- inserted or updated.
168 --
169 -- Post Success
170 -- Processing continues
171 --
172 -- Post Failure
173 -- Error handled by procedure
174 --
175 -- Access Status
176 -- Internal table handler use only.
177 --
178 Procedure chk_dflt_enrt_cd(p_ler_chg_pgm_enrt_id in number,
179 p_dflt_enrt_cd in varchar2,
180 p_effective_date in date,
181 p_object_version_number in number) is
182 --
183 l_proc varchar2(72) := g_package||'chk_dflt_enrt_cd';
184 l_api_updating boolean;
185 --
186 Begin
187 --
188 hr_utility.set_location('Entering:'||l_proc, 5);
189 --
190 l_api_updating := ben_lge_shd.api_updating
191 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
192 p_effective_date => p_effective_date,
193 p_object_version_number => p_object_version_number);
194 --
195 if (l_api_updating
196 and p_dflt_enrt_cd
197 <> nvl(ben_lge_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2)
198 or not l_api_updating)
199 and p_dflt_enrt_cd is not null then
200 --
201 -- check if value of lookup falls within lookup type.
202 --
203 if hr_api.not_exists_in_hr_lookups
204 (p_lookup_type => 'BEN_DFLT_ENRT',
205 p_lookup_code => p_dflt_enrt_cd,
206 p_effective_date => p_effective_date) then
207 --
208 -- raise error as does not exist as lookup
209 --
210 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
211 fnd_message.set_token('FIELD','p_dflt_enrt_cd');
212 fnd_message.set_token('TYPE','BEN_DFLT_ENRT');
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_dflt_enrt_cd;
222 --
223 -- ----------------------------------------------------------------------------
224 -- |------< chk_crnt_enrt_prclds_chg_flag >------|
225 -- ----------------------------------------------------------------------------
226 --
227 -- Description
228 -- This procedure is used to check that the lookup value is valid.
229 --
230 -- Pre Conditions
231 -- None.
232 --
233 -- In Parameters
234 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
235 -- crnt_enrt_prclds_chg_flag Value of lookup code.
236 -- effective_date effective date
237 -- object_version_number Object version number of record being
238 -- inserted or updated.
239 --
240 -- Post Success
241 -- Processing continues
242 --
243 -- Post Failure
244 -- Error handled by procedure
245 --
246 -- Access Status
247 -- Internal table handler use only.
248 --
249 Procedure chk_crnt_enrt_prclds_chg_flag(p_ler_chg_pgm_enrt_id in number,
250 p_crnt_enrt_prclds_chg_flag in varchar2,
251 p_effective_date in date,
252 p_object_version_number in number) is
253 --
254 l_proc varchar2(72) := g_package||'chk_crnt_enrt_prclds_chg_flag';
255 l_api_updating boolean;
256 --
257 Begin
258 --
259 hr_utility.set_location('Entering:'||l_proc, 5);
260 --
261 l_api_updating := ben_lge_shd.api_updating
262 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
263 p_effective_date => p_effective_date,
264 p_object_version_number => p_object_version_number);
265 --
266 if (l_api_updating
267 and p_crnt_enrt_prclds_chg_flag
268 <> nvl(ben_lge_shd.g_old_rec.crnt_enrt_prclds_chg_flag,hr_api.g_varchar2)
269 or not l_api_updating) then
270 --
271 -- check if value of lookup falls within lookup type.
272 --
273 --
274 if hr_api.not_exists_in_hr_lookups
275 (p_lookup_type => 'YES_NO',
276 p_lookup_code => p_crnt_enrt_prclds_chg_flag,
277 p_effective_date => p_effective_date) then
278 --
279 -- raise error as does not exist as lookup
280 --
281 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
282 fnd_message.set_token('FIELD','p_crnt_enrt_prclds_chg_flag');
283 fnd_message.set_token('TYPE','YES_NO');
284 fnd_message.raise_error;
285 --
286 end if;
287 --
288 end if;
289 --
290 hr_utility.set_location('Leaving:'||l_proc,10);
291 --
292 end chk_crnt_enrt_prclds_chg_flag;
293 --
294 -- ----------------------------------------------------------------------------
295 -- |------< chk_auto_enrt_mthd_rl >------|
296 -- ----------------------------------------------------------------------------
297 --
298 -- Description
299 -- This procedure is used to check that the Formula Rule is valid.
300 --
301 -- Pre Conditions
302 -- None.
303 --
304 -- In Parameters
305 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
306 -- auto_enrt_mthd_rl Value of formula rule id.
307 -- effective_date effective date
308 -- object_version_number Object version number of record being
309 -- inserted or updated.
310 --
311 -- Post Success
312 -- Processing continues
313 --
314 -- Post Failure
315 -- Error handled by procedure
316 --
317 -- Access Status
318 -- Internal table handler use only.
319 --
320 Procedure chk_auto_enrt_mthd_rl(p_ler_chg_pgm_enrt_id in number,
321 p_auto_enrt_mthd_rl in number,
322 p_effective_date in date,
323 p_business_group_id in number,
324 p_object_version_number in number) is
325 --
326 l_proc varchar2(72) := g_package||'chk_auto_enrt_mthd_rl';
327 l_api_updating boolean;
328 --
329 Begin
330 --
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 --
333 l_api_updating := ben_lge_shd.api_updating
334 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
335 p_effective_date => p_effective_date,
336 p_object_version_number => p_object_version_number);
337 --
338 if (l_api_updating
339 and nvl(p_auto_enrt_mthd_rl,hr_api.g_number)
340 <> ben_lge_shd.g_old_rec.auto_enrt_mthd_rl
341 or not l_api_updating)
342 and p_auto_enrt_mthd_rl is not null then
343 --
344 -- check if value of formula rule is valid.
345 --
346 if not benutils.formula_exists
347 (p_formula_id => p_auto_enrt_mthd_rl,
348 p_formula_type_id => -146,
349 p_business_group_id => p_business_group_id,
350 p_effective_date => p_effective_date) then
351 --
352 -- raise error
353 --
354 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
355 fnd_message.set_token('ID',p_auto_enrt_mthd_rl);
356 fnd_message.set_token('TYPE_ID',-146);
357 fnd_message.raise_error;
358 --
359 end if;
360 --
361 end if;
362 --
363 hr_utility.set_location('Leaving:'||l_proc,10);
364 --
365 end chk_auto_enrt_mthd_rl;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |------< chk_enrt_mthd_cd >------|
369 -- ----------------------------------------------------------------------------
370 --
371 -- Description
372 -- This procedure is used to check that the lookup value is valid.
373 --
374 -- Pre Conditions
375 -- None.
376 --
377 -- In Parameters
378 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
379 -- enrt_mthd_cd Value of lookup code.
380 -- effective_date effective date
381 -- object_version_number Object version number of record being
382 -- inserted or updated.
383 --
384 -- Post Success
385 -- Processing continues
386 --
387 -- Post Failure
388 -- Error handled by procedure
389 --
390 -- Access Status
391 -- Internal table handler use only.
392 --
393 Procedure chk_enrt_mthd_cd(p_ler_chg_pgm_enrt_id in number,
394 p_enrt_mthd_cd in varchar2,
395 p_effective_date in date,
396 p_object_version_number in number) is
397 --
398 l_proc varchar2(72) := g_package||'chk_enrt_mthd_cd';
399 l_api_updating boolean;
400 --
401 Begin
402 --
403 hr_utility.set_location('Entering:'||l_proc, 5);
404 --
405 l_api_updating := ben_lge_shd.api_updating
406 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
407 p_effective_date => p_effective_date,
408 p_object_version_number => p_object_version_number);
409 --
410 if (l_api_updating
411 and p_enrt_mthd_cd
415 --
412 <> nvl(ben_lge_shd.g_old_rec.enrt_mthd_cd,hr_api.g_varchar2)
413 or not l_api_updating)
414 and p_enrt_mthd_cd is not null then
416 -- check if value of lookup falls within lookup type.
417 --
418 if hr_api.not_exists_in_hr_lookups
419 (p_lookup_type => 'BEN_ENRT_MTHD',
420 p_lookup_code => p_enrt_mthd_cd,
421 p_effective_date => p_effective_date) then
422 --
423 -- raise error as does not exist as lookup
424 --
425 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
426 fnd_message.set_token('FIELD','p_enrt_mthd_cd');
427 fnd_message.set_token('TYPE','BEN_ENRT_MTHD');
428 fnd_message.raise_error;
429 --
430 end if;
431 --
432 end if;
433 --
434 hr_utility.set_location('Leaving:'||l_proc,10);
435 --
436 end chk_enrt_mthd_cd;
437 --
438 -- ----------------------------------------------------------------------------
439 -- |------< chk_enrt_cd >------|
440 -- ----------------------------------------------------------------------------
441 --
442 -- Description
443 -- This procedure is used to check that the lookup value is valid.
444 --
445 -- Pre Conditions
446 -- None.
447 --
448 -- In Parameters
449 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
450 -- enrt_cd Value of lookup code.
451 -- effective_date effective date
452 -- object_version_number Object version number of record being
453 -- inserted or updated.
454 --
455 -- Post Success
456 -- Processing continues
457 --
458 -- Post Failure
459 -- Error handled by procedure
460 --
461 -- Access Status
462 -- Internal table handler use only.
463 --
464 Procedure chk_enrt_cd(p_ler_chg_pgm_enrt_id in number,
465 p_enrt_cd in varchar2,
466 p_effective_date in date,
467 p_object_version_number in number) is
468 --
469 l_proc varchar2(72) := g_package||'chk_enrt_cd';
470 l_api_updating boolean;
471 --
472 Begin
473 --
474 hr_utility.set_location('Entering:'||l_proc, 5);
475 --
476 l_api_updating := ben_lge_shd.api_updating
477 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
478 p_effective_date => p_effective_date,
479 p_object_version_number => p_object_version_number);
480 --
481 if (l_api_updating
482 and p_enrt_cd
483 <> nvl(ben_lge_shd.g_old_rec.enrt_cd,hr_api.g_varchar2)
484 or not l_api_updating)
485 and p_enrt_cd is not null then
486 --
487 -- check if value of lookup falls within lookup type.
488 --
489 if hr_api.not_exists_in_hr_lookups
490 (p_lookup_type => 'BEN_ENRT',
491 p_lookup_code => p_enrt_cd,
492 p_effective_date => p_effective_date) then
493 --
494 -- raise error as does not exist as lookup
495 --
496 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
497 fnd_message.set_token('FIELD','p_enrt_cd');
498 fnd_message.set_token('TYPE','BEN_ENRT');
499 fnd_message.raise_error;
500 --
501 end if;
502 --
503 end if;
504 --
505 hr_utility.set_location('Leaving:'||l_proc,10);
506 --
507 end chk_enrt_cd;
508 --
509 -- ----------------------------------------------------------------------------
510 -- |------< chk_stl_elig_cant_chg_flag >------|
511 -- ----------------------------------------------------------------------------
512 --
513 -- Description
514 -- This procedure is used to check that the lookup value is valid.
515 --
516 -- Pre Conditions
517 -- None.
518 --
519 -- In Parameters
520 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
521 -- stl_elig_cant_chg_flag Value of lookup code.
522 -- effective_date effective date
523 -- object_version_number Object version number of record being
524 -- inserted or updated.
525 --
526 -- Post Success
527 -- Processing continues
528 --
529 -- Post Failure
530 -- Error handled by procedure
531 --
532 -- Access Status
533 -- Internal table handler use only.
534 --
535 Procedure chk_stl_elig_cant_chg_flag(p_ler_chg_pgm_enrt_id in number,
536 p_stl_elig_cant_chg_flag in varchar2,
537 p_effective_date in date,
538 p_object_version_number in number) is
539 --
540 l_proc varchar2(72) := g_package||'chk_stl_elig_cant_chg_flag';
541 l_api_updating boolean;
542 --
543 Begin
544 --
545 hr_utility.set_location('Entering:'||l_proc, 5);
546 --
547 l_api_updating := ben_lge_shd.api_updating
548 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
549 p_effective_date => p_effective_date,
550 p_object_version_number => p_object_version_number);
551 --
552 if (l_api_updating
553 and p_stl_elig_cant_chg_flag
557 -- check if value of lookup falls within lookup type.
554 <> nvl(ben_lge_shd.g_old_rec.stl_elig_cant_chg_flag,hr_api.g_varchar2)
555 or not l_api_updating) then
556 --
558 --
559 --
560 if hr_api.not_exists_in_hr_lookups
561 (p_lookup_type => 'YES_NO',
562 p_lookup_code => p_stl_elig_cant_chg_flag,
563 p_effective_date => p_effective_date) then
564 --
565 -- raise error as does not exist as lookup
566 --
567 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
568 fnd_message.set_token('FIELD','p_stl_elig_cant_chg_flag');
569 fnd_message.set_token('TYPE','YES_NO');
570 fnd_message.raise_error;
571 --
572 end if;
573 --
574 end if;
575 --
576 hr_utility.set_location('Leaving:'||l_proc,10);
577 --
578 end chk_stl_elig_cant_chg_flag;
579 --
580 -- ----------------------------------------------------------------------------
581 -- |------< chk_enrt_rl >------|
582 -- ----------------------------------------------------------------------------
583 --
584 -- Description
585 -- This procedure is used to check that the Formula Rule is valid.
586 --
587 -- Pre Conditions
588 -- None.
589 --
590 -- In Parameters
591 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
592 -- enrt_rl Value of formula rule id.
593 -- effective_date effective date
594 -- object_version_number Object version number of record being
595 -- inserted or updated.
596 --
597 -- Post Success
598 -- Processing continues
599 --
600 -- Post Failure
601 -- Error handled by procedure
602 --
603 -- Access Status
604 -- Internal table handler use only.
605 --
606 Procedure chk_enrt_rl(p_ler_chg_pgm_enrt_id in number,
607 p_enrt_rl in number,
608 p_effective_date in date,
609 p_business_group_id in number,
610 p_object_version_number in number) is
611 --
612 l_proc varchar2(72) := g_package||'chk_enrt_rl';
613 l_api_updating boolean;
614 --
615 Begin
616 --
617 hr_utility.set_location('Entering:'||l_proc, 5);
618 --
619 l_api_updating := ben_lge_shd.api_updating
620 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
621 p_effective_date => p_effective_date,
622 p_object_version_number => p_object_version_number);
623 --
624 if (l_api_updating
625 and nvl(p_enrt_rl,hr_api.g_number)
626 <> ben_lge_shd.g_old_rec.enrt_rl
627 or not l_api_updating)
628 and p_enrt_rl is not null then
629 --
630 -- check if value of formula rule is valid.
631 --
632 if not benutils.formula_exists
633 (p_formula_id => p_enrt_rl,
634 p_formula_type_id => -393,
635 p_business_group_id => p_business_group_id,
636 p_effective_date => p_effective_date) then
637 --
638 -- raise error
639 --
640 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
641 fnd_message.set_token('ID',p_enrt_rl);
642 fnd_message.set_token('TYPE_ID',-393);
643 fnd_message.raise_error;
644 --
645 end if;
646 --
647 end if;
648 --
649 hr_utility.set_location('Leaving:'||l_proc,10);
650 --
651 end chk_enrt_rl;
652 --
653 -- ----------------------------------------------------------------------------
654 -- |-----------------< chk_code_rule_depencency >-----------------|
655 -- ----------------------------------------------------------------------------
656 --
657 -- Description
658 -- This procedure is used to check the code/rule dependency as following:
659 -- If code = 'Rule' then rule must be selected.
660 -- If code <> 'Rule' then code must not be selected.
661 --
662 -- Pre Conditions
663 -- None.
664 --
665 -- In Parameters
666 -- ler_chg_pgm_enrt_id PK of record being inserted or updated.
667 -- enrt_cd Value of lookup code.
668 -- enrt_rl
669 -- dflt_enrt_cd
670 -- dflt_enrt_rl
671 -- effective_date effective date
672 -- object_version_number Object version number of record being
673 -- inserted or updated.
674 --
675 -- Post Success
676 -- Processing continues
677 --
678 -- Post Failure
679 -- Error handled by procedure
680 --
681 -- Access Status
682 -- Internal table handler use only.
683 --
684 Procedure chk_code_rule_dependency(p_ler_chg_pgm_enrt_id in number,
685 p_enrt_cd in varchar2,
686 p_enrt_rl in number,
687 p_dflt_enrt_cd in varchar2,
688 p_dflt_enrt_rl in number,
689 p_effective_date in date,
690 p_object_version_number in number) is
691 --
692 l_proc varchar2(72) := g_package||'chk_code_rule_dependency ';
696 --
693 l_api_updating boolean;
694 --
695 Begin
697 hr_utility.set_location('Entering:'||l_proc, 5);
698 --
699 l_api_updating := ben_lge_shd.api_updating
700 (p_ler_chg_pgm_enrt_id => p_ler_chg_pgm_enrt_id,
701 p_effective_date => p_effective_date,
702 p_object_version_number => p_object_version_number);
703 --
704 if (l_api_updating
705 and
706 (nvl(p_enrt_cd,hr_api.g_varchar2)
707 <> nvl(ben_lge_shd.g_old_rec.enrt_cd,hr_api.g_varchar2) or
708 nvl(p_enrt_rl,hr_api.g_number)
709 <> nvl(ben_lge_shd.g_old_rec.enrt_rl,hr_api.g_number))
710
711 or not l_api_updating) then
712 --
713 if (p_enrt_cd = 'RL' and p_enrt_rl is null) then
714 --
715 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
716 fnd_message.raise_error;
717 --
718 end if;
719 --
720 if nvl(p_enrt_cd,hr_api.g_varchar2) <> 'RL' and p_enrt_rl is
721 not null then
722 --
723 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
724 fnd_message.raise_error;
725 --
726 end if;
727 --
728 end if;
729 --
730 if (l_api_updating
731 and
732 (nvl(p_dflt_enrt_cd,hr_api.g_varchar2)
733 <> nvl(ben_lge_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2) or
734 nvl(p_dflt_enrt_rl,hr_api.g_number)
735 <> nvl(ben_lge_shd.g_old_rec.dflt_enrt_rl,hr_api.g_number))
736
737 or not l_api_updating) then
738 --
739 if (p_dflt_enrt_cd = 'RL' and p_dflt_enrt_rl is null) then
740 --
741 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
742 fnd_message.raise_error;
743 --
744 end if;
745 --
746 if nvl(p_dflt_enrt_cd,hr_api.g_varchar2) <> 'RL' and p_dflt_enrt_rl is
747 not null then
748 --
749 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
750 fnd_message.raise_error;
751 --
752 end if;
753 --
754 end if;
755 hr_utility.set_location('Leaving:'||l_proc,10);
756 --
757 end chk_code_rule_dependency;
758 --
759 -- ----------------------------------------------------------------------------
760 -- |--------------------------< dt_update_validate >--------------------------|
761 -- ----------------------------------------------------------------------------
762 -- {Start Of Comments}
763 --
764 -- Description:
765 -- This procedure is used for referential integrity of datetracked
766 -- parent entities when a datetrack update operation is taking place
767 -- and where there is no cascading of update defined for this entity.
768 --
769 -- Prerequisites:
770 -- This procedure is called from the update_validate.
771 --
772 -- In Parameters:
773 --
774 -- Post Success:
775 -- Processing continues.
776 --
777 -- Post Failure:
778 --
779 -- Developer Implementation Notes:
780 -- This procedure should not need maintenance unless the HR Schema model
781 -- changes.
782 --
783 -- Access Status:
784 -- Internal Row Handler Use Only.
785 --
786 -- {End Of Comments}
787 -- ----------------------------------------------------------------------------
788 Procedure dt_update_validate
789 (p_ler_id in number default hr_api.g_number,
790 p_pgm_id in number default hr_api.g_number,
791 p_datetrack_mode in varchar2,
792 p_validation_start_date in date,
793 p_validation_end_date in date) Is
794 --
795 l_proc varchar2(72) := g_package||'dt_update_validate';
796 l_integrity_error Exception;
797 l_table_name all_tables.table_name%TYPE;
798 --
799 Begin
800 hr_utility.set_location('Entering:'||l_proc, 5);
801 --
802 -- Ensure that the p_datetrack_mode argument is not null
803 --
804 hr_api.mandatory_arg_error
805 (p_api_name => l_proc,
806 p_argument => 'datetrack_mode',
807 p_argument_value => p_datetrack_mode);
808 --
809 -- Only perform the validation if the datetrack update mode is valid
810 --
811 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
812 --
813 --
814 -- Ensure the arguments are not null
815 --
816 hr_api.mandatory_arg_error
817 (p_api_name => l_proc,
818 p_argument => 'validation_start_date',
819 p_argument_value => p_validation_start_date);
820 --
821 hr_api.mandatory_arg_error
822 (p_api_name => l_proc,
823 p_argument => 'validation_end_date',
824 p_argument_value => p_validation_end_date);
825 --
826 If ((nvl(p_ler_id, hr_api.g_number) <> hr_api.g_number) and
827 NOT (dt_api.check_min_max_dates
828 (p_base_table_name => 'ben_ler_f',
829 p_base_key_column => 'ler_id',
830 p_base_key_value => p_ler_id,
831 p_from_date => p_validation_start_date,
832 p_to_date => p_validation_end_date))) Then
836 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
833 l_table_name := 'ben_ler_f';
834 Raise l_integrity_error;
835 End If;
837 NOT (dt_api.check_min_max_dates
838 (p_base_table_name => 'ben_pgm_f',
839 p_base_key_column => 'pgm_id',
840 p_base_key_value => p_pgm_id,
841 p_from_date => p_validation_start_date,
842 p_to_date => p_validation_end_date))) Then
843 l_table_name := 'ben_pgm_f';
844 Raise l_integrity_error;
845 End If;
846 --
847 End If;
848 --
849 hr_utility.set_location(' Leaving:'||l_proc, 10);
850 Exception
851 When l_integrity_error Then
852 --
853 -- A referential integrity check was violated therefore
854 -- we must error
855 --
856 ben_utility.parent_integrity_error(p_table_name => l_table_name);
857 --
858 When Others Then
859 --
860 -- An unhandled or unexpected error has occurred which
861 -- we must report
862 --
863 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
864 fnd_message.set_token('PROCEDURE', l_proc);
865 fnd_message.set_token('STEP','15');
866 fnd_message.raise_error;
867 End dt_update_validate;
868 --
869 -- ----------------------------------------------------------------------------
870 -- |--------------------------< dt_delete_validate >--------------------------|
871 -- ----------------------------------------------------------------------------
872 -- {Start Of Comments}
873 --
874 -- Description:
875 -- This procedure is used for referential integrity of datetracked
876 -- child entities when either a datetrack DELETE or ZAP is in operation
877 -- and where there is no cascading of delete defined for this entity.
878 -- For the datetrack mode of DELETE or ZAP we must ensure that no
879 -- datetracked child rows exist between the validation start and end
880 -- dates.
881 --
882 -- Prerequisites:
883 -- This procedure is called from the delete_validate.
884 --
885 -- In Parameters:
886 --
887 -- Post Success:
888 -- Processing continues.
889 --
890 -- Post Failure:
891 -- If a row exists by determining the returning Boolean value from the
892 -- generic dt_api.rows_exist function then we must supply an error via
893 -- the use of the local exception handler l_rows_exist.
894 --
895 -- Developer Implementation Notes:
896 -- This procedure should not need maintenance unless the HR Schema model
897 -- changes.
898 --
899 -- Access Status:
900 -- Internal Row Handler Use Only.
901 --
902 -- {End Of Comments}
903 -- ----------------------------------------------------------------------------
904 Procedure dt_delete_validate
905 (p_ler_chg_pgm_enrt_id in number,
906 p_datetrack_mode in varchar2,
907 p_validation_start_date in date,
908 p_validation_end_date in date) Is
909 --
910 l_proc varchar2(72) := g_package||'dt_delete_validate';
911 l_rows_exist Exception;
912 l_table_name all_tables.table_name%TYPE;
913 --
914 Begin
915 hr_utility.set_location('Entering:'||l_proc, 5);
916 --
917 -- Ensure that the p_datetrack_mode argument is not null
918 --
919 hr_api.mandatory_arg_error
920 (p_api_name => l_proc,
921 p_argument => 'datetrack_mode',
922 p_argument_value => p_datetrack_mode);
923 --
924 -- Only perform the validation if the datetrack mode is either
925 -- DELETE or ZAP
926 --
927 If (p_datetrack_mode = 'DELETE' or
928 p_datetrack_mode = 'ZAP') then
929 --
930 --
931 -- Ensure the arguments are not null
932 --
933 hr_api.mandatory_arg_error
934 (p_api_name => l_proc,
935 p_argument => 'validation_start_date',
936 p_argument_value => p_validation_start_date);
937 --
938 hr_api.mandatory_arg_error
939 (p_api_name => l_proc,
940 p_argument => 'validation_end_date',
941 p_argument_value => p_validation_end_date);
942 --
943 hr_api.mandatory_arg_error
944 (p_api_name => l_proc,
945 p_argument => 'ler_chg_pgm_enrt_id',
946 p_argument_value => p_ler_chg_pgm_enrt_id);
947 --
948 --
949 --
950 End If;
951 --
952 hr_utility.set_location(' Leaving:'||l_proc, 10);
953 Exception
954 When l_rows_exist Then
955 --
956 -- A referential integrity check was violated therefore
957 -- we must error
958 --
959 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
960 fnd_message.set_token('TABLE_NAME', l_table_name);
961 fnd_message.raise_error;
962 When Others Then
963 --
964 -- An unhandled or unexpected error has occurred which
965 -- we must report
966 --
967 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
968 fnd_message.set_token('PROCEDURE', l_proc);
969 fnd_message.set_token('STEP','15');
970 fnd_message.raise_error;
971 End dt_delete_validate;
972 --
973 -- ----------------------------------------------------------------------------
977 (p_rec in ben_lge_shd.g_rec_type,
974 -- |---------------------------< insert_validate >----------------------------|
975 -- ----------------------------------------------------------------------------
976 Procedure insert_validate
978 p_effective_date in date,
979 p_datetrack_mode in varchar2,
980 p_validation_start_date in date,
981 p_validation_end_date in date) is
982 --
983 l_proc varchar2(72) := g_package||'insert_validate';
984 --
985 Begin
986 hr_utility.set_location('Entering:'||l_proc, 5);
987 --
988 -- Call all supporting business operations
989 --
990 --
991 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
992 --
993 chk_ler_chg_pgm_enrt_id
994 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
995 p_effective_date => p_effective_date,
996 p_object_version_number => p_rec.object_version_number);
997 --
998 chk_dflt_enrt_rl
999 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1000 p_dflt_enrt_rl => p_rec.dflt_enrt_rl,
1001 p_effective_date => p_effective_date,
1002 p_business_group_id => p_rec.business_group_id,
1003 p_object_version_number => p_rec.object_version_number);
1004 --
1005 chk_dflt_enrt_cd
1006 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1007 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
1008 p_effective_date => p_effective_date,
1009 p_object_version_number => p_rec.object_version_number);
1010 --
1011 chk_crnt_enrt_prclds_chg_flag
1012 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1013 p_crnt_enrt_prclds_chg_flag => p_rec.crnt_enrt_prclds_chg_flag,
1014 p_effective_date => p_effective_date,
1015 p_object_version_number => p_rec.object_version_number);
1016 --
1017 chk_auto_enrt_mthd_rl
1018 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1019 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
1020 p_effective_date => p_effective_date,
1021 p_business_group_id => p_rec.business_group_id,
1022 p_object_version_number => p_rec.object_version_number);
1023 --
1024 chk_enrt_mthd_cd
1025 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1026 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
1027 p_effective_date => p_effective_date,
1028 p_object_version_number => p_rec.object_version_number);
1029 --
1030 chk_enrt_cd
1031 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1032 p_enrt_cd => p_rec.enrt_cd,
1033 p_effective_date => p_effective_date,
1034 p_object_version_number => p_rec.object_version_number);
1035 --
1036 chk_stl_elig_cant_chg_flag
1037 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1038 p_stl_elig_cant_chg_flag => p_rec.stl_elig_cant_chg_flag,
1039 p_effective_date => p_effective_date,
1040 p_object_version_number => p_rec.object_version_number);
1041 --
1042 chk_enrt_rl
1043 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1044 p_enrt_rl => p_rec.enrt_rl,
1045 p_effective_date => p_effective_date,
1046 p_business_group_id => p_rec.business_group_id,
1047 p_object_version_number => p_rec.object_version_number);
1048 --
1049 chk_code_rule_dependency
1050 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1051 p_enrt_cd => p_rec.enrt_cd,
1052 p_enrt_rl => p_rec.enrt_rl,
1053 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
1054 p_dflt_enrt_rl => p_rec.dflt_enrt_rl,
1055 p_effective_date => p_effective_date,
1056 p_object_version_number => p_rec.object_version_number);
1057 --
1058 hr_utility.set_location(' Leaving:'||l_proc, 10);
1059 End insert_validate;
1060 --
1061 -- ----------------------------------------------------------------------------
1062 -- |---------------------------< update_validate >----------------------------|
1063 -- ----------------------------------------------------------------------------
1064 Procedure update_validate
1065 (p_rec in ben_lge_shd.g_rec_type,
1066 p_effective_date in date,
1067 p_datetrack_mode in varchar2,
1068 p_validation_start_date in date,
1069 p_validation_end_date in date) is
1070 --
1071 l_proc varchar2(72) := g_package||'update_validate';
1072 --
1073 Begin
1074 hr_utility.set_location('Entering:'||l_proc, 5);
1075 --
1076 -- Call all supporting business operations
1077 --
1078 --
1079 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1080 --
1081 chk_ler_chg_pgm_enrt_id
1082 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1083 p_effective_date => p_effective_date,
1084 p_object_version_number => p_rec.object_version_number);
1085 --
1086 chk_dflt_enrt_rl
1087 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1088 p_dflt_enrt_rl => p_rec.dflt_enrt_rl,
1089 p_effective_date => p_effective_date,
1090 p_business_group_id => p_rec.business_group_id,
1091 p_object_version_number => p_rec.object_version_number);
1092 --
1093 chk_dflt_enrt_cd
1094 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1095 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
1099 chk_crnt_enrt_prclds_chg_flag
1096 p_effective_date => p_effective_date,
1097 p_object_version_number => p_rec.object_version_number);
1098 --
1100 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1101 p_crnt_enrt_prclds_chg_flag => p_rec.crnt_enrt_prclds_chg_flag,
1102 p_effective_date => p_effective_date,
1103 p_object_version_number => p_rec.object_version_number);
1104 --
1105 chk_auto_enrt_mthd_rl
1106 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1107 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
1108 p_effective_date => p_effective_date,
1109 p_business_group_id => p_rec.business_group_id,
1110 p_object_version_number => p_rec.object_version_number);
1111 --
1112 chk_enrt_mthd_cd
1113 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1114 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
1115 p_effective_date => p_effective_date,
1116 p_object_version_number => p_rec.object_version_number);
1117 --
1118 chk_enrt_cd
1119 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1120 p_enrt_cd => p_rec.enrt_cd,
1121 p_effective_date => p_effective_date,
1122 p_object_version_number => p_rec.object_version_number);
1123 --
1124 chk_stl_elig_cant_chg_flag
1125 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1126 p_stl_elig_cant_chg_flag => p_rec.stl_elig_cant_chg_flag,
1127 p_effective_date => p_effective_date,
1128 p_object_version_number => p_rec.object_version_number);
1129 --
1130 chk_enrt_rl
1131 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1132 p_enrt_rl => p_rec.enrt_rl,
1133 p_effective_date => p_effective_date,
1134 p_business_group_id => p_rec.business_group_id,
1135 p_object_version_number => p_rec.object_version_number);
1136 --
1137 chk_code_rule_dependency
1138 (p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id,
1139 p_enrt_cd => p_rec.enrt_cd,
1140 p_enrt_rl => p_rec.enrt_rl,
1141 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
1142 p_dflt_enrt_rl => p_rec.dflt_enrt_rl,
1143 p_effective_date => p_effective_date,
1144 p_object_version_number => p_rec.object_version_number);
1145 --
1146 -- Call the datetrack update integrity operation
1147 --
1148 dt_update_validate
1149 (p_ler_id => p_rec.ler_id,
1150 p_pgm_id => p_rec.pgm_id,
1151 p_datetrack_mode => p_datetrack_mode,
1152 p_validation_start_date => p_validation_start_date,
1153 p_validation_end_date => p_validation_end_date);
1154 --
1155 hr_utility.set_location(' Leaving:'||l_proc, 10);
1156 End update_validate;
1157 --
1158 -- ----------------------------------------------------------------------------
1159 -- |---------------------------< delete_validate >----------------------------|
1160 -- ----------------------------------------------------------------------------
1161 Procedure delete_validate
1162 (p_rec in ben_lge_shd.g_rec_type,
1163 p_effective_date in date,
1164 p_datetrack_mode in varchar2,
1165 p_validation_start_date in date,
1166 p_validation_end_date in date) is
1167 --
1168 l_proc varchar2(72) := g_package||'delete_validate';
1169 --
1170 Begin
1171 hr_utility.set_location('Entering:'||l_proc, 5);
1172 --
1173 -- Call all supporting business operations
1174 --
1175 dt_delete_validate
1176 (p_datetrack_mode => p_datetrack_mode,
1177 p_validation_start_date => p_validation_start_date,
1178 p_validation_end_date => p_validation_end_date,
1179 p_ler_chg_pgm_enrt_id => p_rec.ler_chg_pgm_enrt_id);
1180 --
1181 hr_utility.set_location(' Leaving:'||l_proc, 10);
1182 End delete_validate;
1183 --
1184 --
1185 -- ---------------------------------------------------------------------------
1186 -- |---------------------< return_legislation_code >-------------------------|
1187 -- ---------------------------------------------------------------------------
1188 --
1189 function return_legislation_code
1190 (p_ler_chg_pgm_enrt_id in number) return varchar2 is
1191 --
1192 -- Declare cursor
1193 --
1194 cursor csr_leg_code is
1195 select a.legislation_code
1196 from per_business_groups a,
1197 ben_ler_chg_pgm_enrt_f b
1198 where b.ler_chg_pgm_enrt_id = p_ler_chg_pgm_enrt_id
1199 and a.business_group_id = b.business_group_id;
1200 --
1201 -- Declare local variables
1202 --
1203 l_legislation_code varchar2(150);
1204 l_proc varchar2(72) := g_package||'return_legislation_code';
1205 --
1206 begin
1207 --
1208 hr_utility.set_location('Entering:'|| l_proc, 10);
1209 --
1210 -- Ensure that all the mandatory parameter are not null
1211 --
1212 hr_api.mandatory_arg_error(p_api_name => l_proc,
1213 p_argument => 'ler_chg_pgm_enrt_id',
1214 p_argument_value => p_ler_chg_pgm_enrt_id);
1215 --
1216 open csr_leg_code;
1217 --
1218 fetch csr_leg_code into l_legislation_code;
1219 --
1220 if csr_leg_code%notfound then
1221 --
1222 close csr_leg_code;
1223 --
1224 -- The primary key is invalid therefore we must error
1225 --
1226 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1227 fnd_message.raise_error;
1228 --
1229 end if;
1230 --
1231 close csr_leg_code;
1232 --
1233 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1234 --
1235 return l_legislation_code;
1236 --
1237 end return_legislation_code;
1238 --
1239 end ben_lge_bus;