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