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