[Home] [Help]
PACKAGE BODY: APPS.BEN_CPN_BUS
Source
1 Package Body ben_cpn_bus as
2 /* $Header: becpnrhi.pkb 120.0 2005/05/28 01:15:18 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cpn_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_cntng_prtn_prfl_rt_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_prfl_rt_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_prfl_rt_id(p_cntng_prtn_prfl_rt_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_prfl_rt_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_cpn_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_cntng_prtn_prfl_rt_id => p_cntng_prtn_prfl_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_cntng_prtn_prfl_rt_id,hr_api.g_number)
55 <> ben_cpn_shd.g_old_rec.cntng_prtn_prfl_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_cpn_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_prfl_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_cpn_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_prfl_rt_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_prfl_rt_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_prfl_rt_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_cpn_shd.api_updating
135 (p_cntng_prtn_prfl_rt_id => p_cntng_prtn_prfl_rt_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_cpn_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_prfl_rt_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_prfl_rt_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_cpn_shd.api_updating
220 (p_cntng_prtn_prfl_rt_id => p_cntng_prtn_prfl_rt_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_cpn_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_prfl_rt_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_prfl_rt_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_cpn_shd.api_updating
296 (p_cntng_prtn_prfl_rt_id => p_cntng_prtn_prfl_rt_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_cpn_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_prfl_rt_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_prfl_rt_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_cpn_shd.api_updating
383 (p_cntng_prtn_prfl_rt_id => p_cntng_prtn_prfl_rt_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_cpn_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_prfl_rt_id PK of record being inserted or updated.
434 -- pymt_must_be_rcvd_uom Value of lookup code.
435 -- effective_date effective date
436 -- object_version_number Object version number of record being
437 -- inserted or updated.
438 --
439 -- Post Success
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_prfl_rt_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_cpn_shd.api_updating
462 (p_cntng_prtn_prfl_rt_id => p_cntng_prtn_prfl_rt_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_cpn_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_prfl_rt_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_prfl_rt_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_cpn_shd.api_updating
542 (p_cntng_prtn_prfl_rt_id => p_cntng_prtn_prfl_rt_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_cpn_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_cpn_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_cpn_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
567 --
568 fnd_message.set_name('BEN','BEN_98765_UOM_AND_RL_CHOSEN');
569 fnd_message.raise_error;
570 --
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_vrbl_rt_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_vrbl_rt_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_vrbl_rt_prfl_f',
650 p_base_key_column => 'vrbl_rt_prfl_id',
651 p_base_key_value => p_vrbl_rt_prfl_id,
652 p_from_date => p_validation_start_date,
653 p_to_date => p_validation_end_date))) Then
654 l_table_name := 'ben_vrbl_rt_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:
707 -- This procedure should not need maintenance unless the HR Schema model
708 -- changes.
709 --
710 -- Access Status:
711 -- Internal Row Handler Use Only.
712 --
713 -- {End Of Comments}
714 -- ----------------------------------------------------------------------------
715 Procedure dt_delete_validate
716 (p_cntng_prtn_prfl_rt_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_prfl_rt_id',
757 p_argument_value => p_cntng_prtn_prfl_rt_id);
758 --
759 /*
760 If (dt_api.rows_exist
761 (p_base_table_name => 'ben_cntng_prtn_prfl_rt_f',
762 p_base_key_column => 'cntng_prtn_prfl_rt_id',
763 p_base_key_value => p_cntng_prtn_prfl_rt_id,
764 p_from_date => p_validation_start_date,
765 p_to_date => p_validation_end_date)) Then
766 l_table_name := 'ben_cntng_prtn_prfl_rt_f';
767 Raise l_rows_exist;
768 End If;
769 */
770 --
771 End If;
772 --
773 hr_utility.set_location(' Leaving:'||l_proc, 10);
774 Exception
775 When l_rows_exist Then
776 --
777 -- A referential integrity check was violated therefore
778 -- we must error
779 --
780 ben_utility.child_exists_error(p_table_name => l_table_name);
781 --
782 When Others Then
783 --
784 -- An unhandled or unexpected error has occurred which
785 -- we must report
786 --
787 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
788 fnd_message.set_token('PROCEDURE', l_proc);
789 fnd_message.set_token('STEP','15');
790 fnd_message.raise_error;
791 End dt_delete_validate;
792 --
793 -- ----------------------------------------------------------------------------
794 -- |---------------------------< insert_validate >----------------------------|
795 -- ----------------------------------------------------------------------------
796 Procedure insert_validate
797 (p_rec in ben_cpn_shd.g_rec_type,
798 p_effective_date in date,
799 p_datetrack_mode in varchar2,
800 p_validation_start_date in date,
801 p_validation_end_date in date) is
802 --
803 l_proc varchar2(72) := g_package||'insert_validate';
804 --
805 Begin
806 hr_utility.set_location('Entering:'||l_proc, 5);
807 --
808 -- Call all supporting business operations
809 --
810 --
811 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
812 --
813 chk_cntng_prtn_prfl_rt_id
814 (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
815 p_effective_date => p_effective_date,
816 p_object_version_number => p_rec.object_version_number);
817 --
818 /*
819 -- chk_cntng_frmr_prtt_dsge_rl
820 -- (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
821 -- p_cntng_frmr_prtt_dsge_rl => p_rec.cntng_frmr_prtt_dsge_rl,
822 -- p_effective_date => p_effective_date,
823 -- p_object_version_number => p_rec.object_version_number);
824 --
825 -- chk_cntng_frmr_prtt_rl
826 -- (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
827 -- p_cntng_frmr_prtt_rl => p_rec.cntng_frmr_prtt_rl,
828 -- p_effective_date => p_effective_date,
829 -- p_object_version_number => p_rec.object_version_number);
830 --
831 -- chk_dsge_must_be_redsgd_flag
832 -- (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
833 -- p_dsge_must_be_redsgd_flag => p_rec.dsge_must_be_redsgd_flag,
834 -- p_effective_date => p_effective_date,
835 -- p_object_version_number => p_rec.object_version_number);
836 */
837 --
838 chk_pymt_must_be_rcvd_rl
839 (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
840 p_pymt_must_be_rcvd_rl => p_rec.pymt_must_be_rcvd_rl,
841 p_business_group_id => p_rec.business_group_id,
842 p_effective_date => p_effective_date,
843 p_object_version_number => p_rec.object_version_number);
844 --
845 chk_pymt_must_be_rcvd_uom
846 (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
847 p_pymt_must_be_rcvd_uom => p_rec.pymt_must_be_rcvd_uom,
848 p_effective_date => p_effective_date,
849 p_object_version_number => p_rec.object_version_number);
850 --
851 chk_pymt_must_be_rcvd_dep(p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
852 p_pymt_must_be_rcvd_rl => p_rec.pymt_must_be_rcvd_rl,
853 p_pymt_must_be_rcvd_num => p_rec.pymt_must_be_rcvd_num,
854 p_pymt_must_be_rcvd_uom => p_rec.pymt_must_be_rcvd_uom,
855 p_business_group_id => p_rec.business_group_id,
856 p_effective_date => p_effective_date,
857 p_object_version_number => p_rec.object_version_number);
858 --
859 hr_utility.set_location(' Leaving:'||l_proc, 10);
860 End insert_validate;
861 --
862 -- ----------------------------------------------------------------------------
863 -- |---------------------------< update_validate >----------------------------|
864 -- ----------------------------------------------------------------------------
865 Procedure update_validate
866 (p_rec in ben_cpn_shd.g_rec_type,
867 p_effective_date in date,
868 p_datetrack_mode in varchar2,
869 p_validation_start_date in date,
870 p_validation_end_date in date) is
871 --
872 l_proc varchar2(72) := g_package||'update_validate';
873 --
874 Begin
875 hr_utility.set_location('Entering:'||l_proc, 5);
876 --
877 -- Call all supporting business operations
878 --
879 --
880 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
881 --
882 chk_cntng_prtn_prfl_rt_id
883 (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
884 p_effective_date => p_effective_date,
885 p_object_version_number => p_rec.object_version_number);
886 --
887 /*
888 -- chk_cntng_frmr_prtt_dsge_rl
889 -- (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
890 -- p_cntng_frmr_prtt_dsge_rl => p_rec.cntng_frmr_prtt_dsge_rl,
891 -- p_effective_date => p_effective_date,
892 -- p_object_version_number => p_rec.object_version_number);
893 --
894 -- chk_cntng_frmr_prtt_rl
895 -- (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
896 -- p_cntng_frmr_prtt_rl => p_rec.cntng_frmr_prtt_rl,
897 -- p_effective_date => p_effective_date,
898 -- p_object_version_number => p_rec.object_version_number);
899 --
900 -- chk_dsge_must_be_redsgd_flag
901 -- (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
902 -- p_dsge_must_be_redsgd_flag => p_rec.dsge_must_be_redsgd_flag,
903 -- p_effective_date => p_effective_date,
904 -- p_object_version_number => p_rec.object_version_number);
905 */
906 --
907 chk_pymt_must_be_rcvd_rl
908 (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
909 p_pymt_must_be_rcvd_rl => p_rec.pymt_must_be_rcvd_rl,
910 p_business_group_id => p_rec.business_group_id,
911 p_effective_date => p_effective_date,
912 p_object_version_number => p_rec.object_version_number);
913 --
914 chk_pymt_must_be_rcvd_uom
915 (p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
916 p_pymt_must_be_rcvd_uom => p_rec.pymt_must_be_rcvd_uom,
917 p_effective_date => p_effective_date,
918 p_object_version_number => p_rec.object_version_number);
919 --
920 --
921 chk_pymt_must_be_rcvd_dep(p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id,
922 p_pymt_must_be_rcvd_rl => p_rec.pymt_must_be_rcvd_rl,
923 p_pymt_must_be_rcvd_num => p_rec.pymt_must_be_rcvd_num,
924 p_pymt_must_be_rcvd_uom => p_rec.pymt_must_be_rcvd_uom,
925 p_business_group_id => p_rec.business_group_id,
926 p_effective_date => p_effective_date,
927 p_object_version_number => p_rec.object_version_number);
928 --
929 --
930 -- Call the datetrack update integrity operation
931 --
932 dt_update_validate
933 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
934 p_datetrack_mode => p_datetrack_mode,
935 p_validation_start_date => p_validation_start_date,
936 p_validation_end_date => p_validation_end_date);
937 --
938 hr_utility.set_location(' Leaving:'||l_proc, 10);
939 End update_validate;
940 --
941 -- ----------------------------------------------------------------------------
942 -- |---------------------------< delete_validate >----------------------------|
943 -- ----------------------------------------------------------------------------
944 Procedure delete_validate
945 (p_rec in ben_cpn_shd.g_rec_type,
946 p_effective_date in date,
947 p_datetrack_mode in varchar2,
948 p_validation_start_date in date,
949 p_validation_end_date in date) is
950 --
951 l_proc varchar2(72) := g_package||'delete_validate';
952 --
953 Begin
954 hr_utility.set_location('Entering:'||l_proc, 5);
955 --
956 -- Call all supporting business operations
957 --
958 dt_delete_validate
959 (p_datetrack_mode => p_datetrack_mode,
960 p_validation_start_date => p_validation_start_date,
961 p_validation_end_date => p_validation_end_date,
962 p_cntng_prtn_prfl_rt_id => p_rec.cntng_prtn_prfl_rt_id);
963 --
964 hr_utility.set_location(' Leaving:'||l_proc, 10);
965 End delete_validate;
966 --
967 --
968 -- ---------------------------------------------------------------------------
969 -- |---------------------< return_legislation_code >-------------------------|
970 -- ---------------------------------------------------------------------------
971 --
972 function return_legislation_code
973 (p_cntng_prtn_prfl_rt_id in number) return varchar2 is
974 --
975 -- Declare cursor
976 --
977 cursor csr_leg_code is
978 select a.legislation_code
979 from per_business_groups a,
980 ben_cntng_prtn_prfl_rt_f b
981 where b.cntng_prtn_prfl_rt_id = p_cntng_prtn_prfl_rt_id
982 and a.business_group_id = b.business_group_id;
983 --
984 -- Declare local variables
985 --
986 l_legislation_code varchar2(150);
987 l_proc varchar2(72) := g_package||'return_legislation_code';
988 --
989 begin
990 --
991 hr_utility.set_location('Entering:'|| l_proc, 10);
992 --
993 -- Ensure that all the mandatory parameter are not null
994 --
995 hr_api.mandatory_arg_error(p_api_name => l_proc,
996 p_argument => 'cntng_prtn_prfl_rt_id',
997 p_argument_value => p_cntng_prtn_prfl_rt_id);
998 --
999 open csr_leg_code;
1000 --
1001 fetch csr_leg_code into l_legislation_code;
1002 --
1003 if csr_leg_code%notfound then
1004 --
1005 close csr_leg_code;
1006 --
1007 -- The primary key is invalid therefore we must error
1008 --
1009 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1010 fnd_message.raise_error;
1011 --
1012 end if;
1013 --
1014 close csr_leg_code;
1015 --
1016 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1017 --
1018 return l_legislation_code;
1019 --
1020 end return_legislation_code;
1021 --
1022 end ben_cpn_bus;