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