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