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