1 Package Body pay_liv_bus as
2 /* $Header: pylivrhi.pkb 120.1 2005/07/12 05:24:42 alogue noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_liv_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_link_input_value_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_link_input_value_id in number
22 ,p_associated_column1 in varchar2
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups_perf pbg
30 , pay_link_input_values_f liv
31 , pay_element_links_f pel
32 where liv.link_input_value_id = p_link_input_value_id
33 and liv.element_link_id = pel.element_link_id
34 and pbg.business_group_id = pel.business_group_id;
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'link_input_value_id'
50 ,p_argument_value => p_link_input_value_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id;
55 --
56 if csr_sec_grp%notfound then
57 --
58 close csr_sec_grp;
59 --
60 -- The primary key is invalid therefore we must error
61 --
62 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
63 hr_multi_message.add
64 (p_associated_column1
65 => nvl(p_associated_column1,'LINK_INPUT_VALUE_ID')
66 );
67 --
68 else
69 close csr_sec_grp;
70 --
71 -- Set the security_group_id in CLIENT_INFO
72 --
73 hr_api.set_security_group_id
74 (p_security_group_id => l_security_group_id
75 );
76 end if;
77 --
78 hr_utility.set_location(' Leaving:'|| l_proc, 20);
79 --
80 end set_security_group_id;
81 --
82 -- ---------------------------------------------------------------------------
83 -- |---------------------< return_legislation_code >-------------------------|
84 -- ---------------------------------------------------------------------------
85 --
86 Function return_legislation_code
87 (p_link_input_value_id in number
88 )
89 Return Varchar2 Is
90 --
91 -- Declare cursor
92 --
93 cursor csr_leg_code is
94 select pbg.legislation_code
95 from per_business_groups_perf pbg
96 , pay_link_input_values_f liv
97 , pay_element_links_f pel
98 where liv.link_input_value_id = p_link_input_value_id
99 and liv.element_link_id = pel.element_link_id
100 and pbg.business_group_id = pel.business_group_id;
101 --
102 -- Declare local variables
103 --
104 l_legislation_code varchar2(150);
105 l_proc varchar2(72) := g_package||'return_legislation_code';
106 --
107 Begin
108 --
109 hr_utility.set_location('Entering:'|| l_proc, 10);
110 --
111 -- Ensure that all the mandatory parameter are not null
112 --
113 hr_api.mandatory_arg_error
114 (p_api_name => l_proc
115 ,p_argument => 'link_input_value_id'
116 ,p_argument_value => p_link_input_value_id
117 );
118 --
119 if ( nvl(pay_liv_bus.g_link_input_value_id, hr_api.g_number)
120 = p_link_input_value_id) then
121 --
122 -- The legislation code has already been found with a previous
123 -- call to this function. Just return the value in the global
124 -- variable.
125 --
126 l_legislation_code := pay_liv_bus.g_legislation_code;
127 hr_utility.set_location(l_proc, 20);
128 else
129 --
130 -- The ID is different to the last call to this function
131 -- or this is the first call to this function.
132 --
133 open csr_leg_code;
134 fetch csr_leg_code into l_legislation_code;
135 --
136 if csr_leg_code%notfound then
137 --
138 -- The primary key is invalid therefore we must error
139 --
140 close csr_leg_code;
141 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
142 fnd_message.raise_error;
143 end if;
144 hr_utility.set_location(l_proc,30);
145 --
146 -- Set the global variables so the values are
147 -- available for the next call to this function.
148 --
149 close csr_leg_code;
150 pay_liv_bus.g_link_input_value_id := p_link_input_value_id;
151 pay_liv_bus.g_legislation_code := l_legislation_code;
152 end if;
153 hr_utility.set_location(' Leaving:'|| l_proc, 40);
154 return l_legislation_code;
155 end return_legislation_code;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |-----------------------< chk_non_updateable_args >------------------------|
159 -- ----------------------------------------------------------------------------
160 -- {Start Of Comments}
161 --
162 -- Description:
163 -- This procedure is used to ensure that non updateable attributes have
164 -- not been updated. If an attribute has been updated an error is generated.
165 --
166 -- Pre Conditions:
167 -- g_old_rec has been populated with details of the values currently in
168 -- the database.
169 --
170 -- In Arguments:
171 -- p_rec has been populated with the updated values the user would like the
172 -- record set to.
173 --
174 -- Post Success:
175 -- Processing continues if all the non updateable attributes have not
176 -- changed.
177 --
178 -- Post Failure:
179 -- An application error is raised if any of the non updatable attributes
180 -- have been altered.
181 --
182 -- {End Of Comments}
183 -- ----------------------------------------------------------------------------
184 Procedure chk_non_updateable_args
185 (p_effective_date in date
186 ,p_rec in pay_liv_shd.g_rec_type
187 ) IS
188 --
189 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
190 l_error exception;
191 l_argument varchar2(30);
192
193 --
194 Begin
195 --
196 -- Only proceed with the validation if a row exists for the current
197 -- record in the HR Schema.
198 --
199 IF NOT pay_liv_shd.api_updating
200 (p_link_input_value_id => p_rec.link_input_value_id
201 ,p_effective_date => p_effective_date
202 ,p_object_version_number => p_rec.object_version_number
203 ) THEN
204 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
205 fnd_message.set_token('PROCEDURE ', l_proc);
206 fnd_message.set_token('STEP ', '5');
207 fnd_message.raise_error;
208 END IF;
209 --
210 IF nvl(p_rec.element_link_id, hr_api.g_number) <>
211 nvl(pay_liv_shd.g_old_rec.element_link_id, hr_api.g_number)
212 THEN
213 l_argument := 'p_element_link_id';
214 RAISE l_error;
215 END IF;
216
217 IF nvl(p_rec.input_value_id, hr_api.g_number) <>
218 nvl(pay_liv_shd.g_old_rec.input_value_id, hr_api.g_number)
219 THEN
220 l_argument := 'p_input_value_id';
221 RAISE l_error;
222 END IF;
223 hr_utility.set_location('Leaving:'||l_proc, 20);
224
225 EXCEPTION
226 WHEN l_error THEN
227 hr_utility.set_location('Leaving:'||l_proc, 25);
228 hr_api.argument_changed_error
229 (p_api_name => l_proc
230 ,p_argument => l_argument);
231 WHEN OTHERS THEN
232 hr_utility.set_location('Leaving:'||l_proc, 30);
233 RAISE;
234
235 End chk_non_updateable_args;
236 --
237 -- ----------------------------------------------------------------------------
238 -- |--------------------------< dt_update_validate >--------------------------|
239 -- ----------------------------------------------------------------------------
240 -- {Start Of Comments}
241 --
242 -- Description:
243 -- This procedure is used for referential integrity of datetracked
244 -- parent entities when a datetrack update operation is taking place
245 -- and where there is no cascading of update defined for this entity.
246 --
247 -- Prerequisites:
248 -- This procedure is called from the update_validate.
249 --
250 -- In Parameters:
251 --
252 -- Post Success:
253 -- Processing continues.
254 --
255 -- Post Failure:
256 --
257 -- Developer Implementation Notes:
258 -- This procedure should not need maintenance unless the HR Schema model
259 -- changes.
260 --
261 -- Access Status:
262 -- Internal Row Handler Use Only.
263 --
264 -- {End Of Comments}
265 -- ----------------------------------------------------------------------------
266 Procedure dt_update_validate
267 (p_input_value_id in number
268 ,p_element_link_id in number
269 ,p_datetrack_mode in varchar2
270 ,p_validation_start_date in date
271 ,p_validation_end_date in date
272 ) Is
273 --
274 l_proc varchar2(72) := g_package||'dt_update_validate';
275 --
276 Begin
277 --
278 -- Ensure that the p_datetrack_mode argument is not null
279 --
280 hr_api.mandatory_arg_error
281 (p_api_name => l_proc
282 ,p_argument => 'datetrack_mode'
283 ,p_argument_value => p_datetrack_mode
284 );
285 --
286 -- Mode will be valid, as this is checked at the start of the upd.
287 --
288 -- Ensure the arguments are not null
289 --
290 hr_api.mandatory_arg_error
291 (p_api_name => l_proc
292 ,p_argument => 'validation_start_date'
293 ,p_argument_value => p_validation_start_date
294 );
295 --
296 hr_api.mandatory_arg_error
297 (p_api_name => l_proc
298 ,p_argument => 'validation_end_date'
299 ,p_argument_value => p_validation_end_date
300 );
301 --
302 If ((nvl(p_input_value_id, hr_api.g_number) <> hr_api.g_number) and
303 NOT (dt_api.check_min_max_dates
304 (p_base_table_name => 'pay_input_values_f'
305 ,p_base_key_column => 'INPUT_VALUE_ID'
306 ,p_base_key_value => p_input_value_id
307 ,p_from_date => p_validation_start_date
308 ,p_to_date => p_validation_end_date))) Then
309 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
310 fnd_message.set_token('TABLE_NAME','input values');
311 hr_multi_message.add
312 (p_associated_column1 => pay_liv_shd.g_tab_nam || '.INPUT_VALUE_ID');
313 End If;
314 If ((nvl(p_element_link_id, hr_api.g_number) <> hr_api.g_number) and
315 NOT (dt_api.check_min_max_dates
316 (p_base_table_name => 'pay_element_links_f'
317 ,p_base_key_column => 'ELEMENT_LINK_ID'
318 ,p_base_key_value => p_element_link_id
319 ,p_from_date => p_validation_start_date
320 ,p_to_date => p_validation_end_date))) Then
321 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
322 fnd_message.set_token('TABLE_NAME','element links');
323 hr_multi_message.add
324 (p_associated_column1 => pay_liv_shd.g_tab_nam || '.ELEMENT_LINK_ID');
325 End If;
326 --
327 Exception
328 When Others Then
329 --
330 -- An unhandled or unexpected error has occurred which
331 -- we must report
332 --
333 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
334 fnd_message.set_token('PROCEDURE', l_proc);
335 fnd_message.set_token('STEP','15');
336 fnd_message.raise_error;
337 End dt_update_validate;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |--------------------------< dt_delete_validate >--------------------------|
341 -- ----------------------------------------------------------------------------
342 -- {Start Of Comments}
343 --
344 -- Description:
345 -- This procedure is used for referential integrity of datetracked
346 -- child entities when either a datetrack DELETE or ZAP is in operation
347 -- and where there is no cascading of delete defined for this entity.
348 -- For the datetrack mode of DELETE or ZAP we must ensure that no
349 -- datetracked child rows exist between the validation start and end
350 -- dates.
351 --
352 -- Prerequisites:
353 -- This procedure is called from the delete_validate.
354 --
355 -- In Parameters:
356 --
357 -- Post Success:
358 -- Processing continues.
359 --
360 -- Post Failure:
361 -- If a row exists by determining the returning Boolean value from the
362 -- generic dt_api.rows_exist function then we must supply an error via
363 -- the use of the local exception handler l_rows_exist.
364 --
365 -- Developer Implementation Notes:
366 -- This procedure should not need maintenance unless the HR Schema model
367 -- changes.
368 --
369 -- Access Status:
370 -- Internal Row Handler Use Only.
371 --
372 -- {End Of Comments}
373 -- ----------------------------------------------------------------------------
374 Procedure dt_delete_validate
375 (p_link_input_value_id in number
376 ,p_datetrack_mode in varchar2
377 ,p_validation_start_date in date
378 ,p_validation_end_date in date
379 ) Is
380 --
381 l_proc varchar2(72) := g_package||'dt_delete_validate';
382 --
383 Begin
384 --
385 -- Ensure that the p_datetrack_mode argument is not null
386 --
387 hr_api.mandatory_arg_error
388 (p_api_name => l_proc
389 ,p_argument => 'datetrack_mode'
390 ,p_argument_value => p_datetrack_mode
391 );
392 --
393 -- Only perform the validation if the datetrack mode is either
394 -- DELETE or ZAP
395 --
396 If (p_datetrack_mode = hr_api.g_delete or
397 p_datetrack_mode = hr_api.g_zap) then
398 --
399 --
400 -- Ensure the arguments are not null
401 --
402 hr_api.mandatory_arg_error
403 (p_api_name => l_proc
404 ,p_argument => 'validation_start_date'
405 ,p_argument_value => p_validation_start_date
406 );
407 --
408 hr_api.mandatory_arg_error
409 (p_api_name => l_proc
410 ,p_argument => 'validation_end_date'
411 ,p_argument_value => p_validation_end_date
412 );
413 --
414 hr_api.mandatory_arg_error
415 (p_api_name => l_proc
416 ,p_argument => 'link_input_value_id'
417 ,p_argument_value => p_link_input_value_id
418 );
419 --
420 --
421 --
422 End If;
423 --
424 Exception
425 When Others Then
426 --
427 -- An unhandled or unexpected error has occurred which
428 -- we must report
429 --
430 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
431 fnd_message.set_token('PROCEDURE', l_proc);
432 fnd_message.set_token('STEP','15');
433 fnd_message.raise_error;
434 --
435 End dt_delete_validate;
436
437 --
438 -- check procedures
439 -- ----------------------------------------------------------------------------
440 -- |-------------------------< chk_warning_or_error >-------------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 -- Procedure to validate that either warning or error is defined if minimum
444 -- and/or maximum of Input value is specified.
445 --
446 Procedure chk_warning_or_error
447 (p_warning_or_error in varchar2
448 ,p_max_value in varchar2
449 ,p_min_value in varchar2
450 ) is
451 --
452 l_proc varchar2(72) := g_package||'chk_warning_or_error';
453 --
454 Begin
455 hr_utility.set_location('Entering:'||l_proc, 5);
456 --
457 If (p_max_value is not null or p_min_value is not null) then
458 If p_warning_or_error is null Then
459 fnd_message.set_name('PAY', 'PAY_33084_LK_INP_VAL_WARN_ERR');
460 fnd_message.raise_error;
461 End If;
462 End if;
463 --
464 hr_utility.set_location(' Leaving:'||l_proc, 10);
465 End chk_warning_or_error;
466
467 --
468 -- ----------------------------------------------------------------------------
469 -- |-------------------------< chk_min_and_max_values >-----------------------|
470 -- ----------------------------------------------------------------------------
471 --
472 -- Procedure to validate the following business rules:
473 --
474 -- 1. For a link input value the minimum should be less than or equal to
475 -- the maximum.
476 -- 2. The default should be within the range of minimum and maximum.
477 -- 3. If Input Value is to be validated by a Formula, then maximum or minimum
478 -- values should not be defined for a link input value.
479 --
480 Procedure chk_min_and_max_values
481 (p_input_value_id in number
482 ,p_effective_date in date
483 ,p_default_value in varchar2
484 ,p_max_value in varchar2
485 ,p_min_value in varchar2
486 ,p_warning_or_error in varchar2
487 ,p_default_range_warning out nocopy boolean
488 ) is
489 --
490 l_proc varchar2(72) := g_package||'chk_min_and_max_values';
491 l_exists varchar2(1);
492 l_min_max_failure varchar2(1);
493 l_formula_id pay_input_values_f.formula_id%type;
494 l_value varchar2(255);
495 --
496 Cursor C_formula_id
497 is
498 select formula_id
499 from pay_input_values_f
500 where input_value_id = p_input_value_id
501 and p_effective_date between effective_start_date
502 and effective_end_date;
503 --
504 Procedure chk_format
505 (p_input_value_id in number
506 ,p_effective_date in date
507 ,p_unformatted_value in varchar2
508 ,p_min_value in varchar2
509 ,p_max_value in varchar2
510 ,p_min_max_failure out nocopy varchar2
511 ) is
512 --
513 l_unformatted_value varchar2(255) := p_unformatted_value;
514 l_database_value varchar2(80);
515 l_min_max_failure varchar2(1);
516 l_checkformat_error boolean;
517 l_input_currency_code pay_element_types_f.input_currency_code%type;
518 l_message_text hr_lookups.meaning%type;
519 l_uom pay_input_values_f.uom%type;
520 --
521 Cursor C_currency_uom
522 is
523 select pet.input_currency_code, piv.uom
524 from pay_element_types_f pet,
525 pay_input_values_f piv
526 where pet.element_type_id = piv.element_type_id
527 and piv.input_value_id = p_input_value_id
528 and p_effective_date between pet.effective_start_date
529 and pet.effective_end_date
530 and p_effective_date between piv.effective_start_date
531 and piv.effective_end_date;
532 Begin
533 --
534 Open C_currency_uom;
535 Fetch C_currency_uom into l_input_currency_code,l_uom;
536 Close C_currency_uom;
537 --
538 begin
539 hr_chkfmt.checkformat(l_unformatted_value,
540 l_uom,
541 l_database_value,
542 p_min_value,
543 p_max_value,
544 'Y',
545 l_min_max_failure,
546 l_input_currency_code);
547 exception
548 when hr_utility.hr_error then
549 l_checkformat_error := true;
550 end;
551
552 p_min_max_failure := l_min_max_failure;
553
554 If (l_checkformat_error) then
555 --
556 begin
557 --
558 select meaning
559 into l_message_text
560 from hr_lookups
561 where lookup_type = 'UNITS'
562 and lookup_code = l_uom;
563 --
564 exception
565 when no_data_found then
566 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
567 fnd_message.set_token('PROCEDURE', 'PAY_LIV_BUS.CHK_FORMAT');
568 fnd_message.set_token('STEP', '2');
569 fnd_message.raise_error;
570 end;
571 --
572 fnd_message.set_name('PAY', 'PAY_6306_INPUT_VALUE_FORMAT');
573 fnd_message.set_token('UNIT_OF_MEASURE', l_message_text);
574 fnd_message.raise_error;
575 --
576 end if;
577 --
578 End chk_format;
579
580
581 Begin
582 hr_utility.set_location('Entering:'||l_proc, 5);
583 --
584 p_default_range_warning := False;
585 --
586 -- Validate min value with UOM
587 --
588 chk_format
589 (p_input_value_id => p_input_value_id
590 ,p_effective_date => p_effective_date
591 ,p_unformatted_value => p_min_value
592 ,p_min_value => null
593 ,p_max_value => null
594 ,p_min_max_failure => l_min_max_failure
595 );
596 --
597 -- Validate max value with UOM
598 --
599 chk_format
600 (p_input_value_id => p_input_value_id
601 ,p_effective_date => p_effective_date
602 ,p_unformatted_value => p_max_value
603 ,p_min_value => null
604 ,p_max_value => null
605 ,p_min_max_failure => l_min_max_failure
606 );
607 --
608 -- Validate default value with UOM
609 --
610 chk_format
611 (p_input_value_id => p_input_value_id
612 ,p_effective_date => p_effective_date
613 ,p_unformatted_value => p_default_value
614 ,p_min_value => p_min_value
615 ,p_max_value => p_max_value
616 ,p_min_max_failure => l_min_max_failure
617 );
618
619 If (p_min_value is not null and
620 p_max_value is not null) then
621 begin
622
623 l_value := p_min_value;
624
625 chk_format
626 (p_input_value_id => p_input_value_id
627 ,p_effective_date => p_effective_date
628 ,p_unformatted_value => l_value
629 ,p_min_value => p_min_value
630 ,p_max_value => p_max_value
631 ,p_min_max_failure => l_min_max_failure
632 );
633
634 exception
635 When Others then
636 fnd_message.set_name('PAY', 'HR_51976_ALL_MIN_LESS_MAX');
637 fnd_message.raise_error;
638 end;
639 End If;
640
641 hr_utility.set_location('l_min_max_failure '||l_min_max_failure,30);
642
643 If l_min_max_failure = 'F' Then
644 --
645 If nvl(p_warning_or_error,pay_liv_shd.g_old_rec.warning_or_error) = 'W'
646 then
647 p_default_range_warning := True;
648 Elsif nvl(p_warning_or_error,pay_liv_shd.g_old_rec.warning_or_error) = 'E'
649 then
650 fnd_message.set_name('PAY', 'HR_INPVAL_DEFAULT_INVALID');
651 fnd_message.raise_error;
652 End if;
653 --
654 End If;
655
656 Open C_formula_id;
657 Fetch C_formula_id Into l_formula_id;
658 Close C_formula_id;
659
660 If l_formula_id is not null and
661 (p_min_value is not null
662 or p_max_value is not null
663 or p_warning_or_error is null) Then
664 --
665 fnd_message.set_name('PAY', 'PAY_6905_INPVAL_FORMULA_VAL');
666 fnd_message.raise_error;
667 --
668 End If;
669 --
670 hr_utility.set_location(' Leaving:'||l_proc, 10);
671 --
672 End chk_min_and_max_values;
673
674 --
675 -- ----------------------------------------------------------------------------
676 -- |-------------------------< chk_costed_flag >------------------------------|
677 -- ----------------------------------------------------------------------------
678 --
679 -- Procedure to validate the following business rules:
680 --
681 -- 1. If the Element Link is not costed then the Link Input value cannot be
682 -- costed.
683 -- 2. If the Unit of Measurement of the Link Input Value is neither 'Money' nor
684 -- 'Hours' then the Link Input value cannot be costed.
685 -- 3. If the Element Link is Distributed, then only the 'Pay Value' can be
686 -- costed.
687 --
688 Procedure chk_costed_flag
689 (p_element_link_id in number
690 ,p_effective_date in date
691 ,p_input_value_id in number
692 ,p_costed_flag in varchar2
693 ) is
694 --
695 l_proc varchar2(72) := g_package||'chk_costed_flag';
696 l_costable_type pay_element_links_f.costable_type%type;
697 l_uom pay_input_values_f.uom%type;
698 l_name pay_input_values_f.name%type;
699
700 Cursor C_element_link
701 is
702 select costable_type
703 from pay_element_links_f
704 where element_link_id = p_element_link_id
705 and p_effective_date between effective_start_date
706 and effective_end_date;
707
708 Cursor C_input_values
709 is
710 select name, uom
711 from pay_input_values_f
712 where input_value_id = p_input_value_id
713 and p_effective_date between effective_start_date
714 and effective_end_date;
715 --
716 Begin
717 --
718 hr_utility.set_location('Entering:'||l_proc, 5);
719
720 Open C_element_link;
721 Fetch C_element_link into l_costable_type;
722 Close C_element_link;
723
724 If l_costable_type = 'N' and p_costed_flag <> 'N' Then
725 fnd_message.set_name('PAY', 'PAY_33080_LK_INP_VAL_NO_COST');
726 fnd_message.raise_error;
727 End if;
728
729 Open C_input_values;
730 Fetch C_input_values into l_name, l_uom;
731 Close C_input_values;
732
733 If (l_uom <> 'M' and l_uom not like 'H%'
734 and p_costed_flag = 'Y') Then
735 fnd_message.set_name('PAY', 'PAY_33081_LK_INP_VAL_UOM_COST');
736 fnd_message.raise_error;
737 End If;
738
739 If l_name <> 'Pay Value' Then
740 If l_costable_type = 'D' and p_costed_flag = 'Y' Then
741 fnd_message.set_name('PAY', 'PAY_33082_LK_INP_VAL_DIST_COST');
742 fnd_message.raise_error;
743 End If;
744 End if;
745
746 hr_utility.set_location(' Leaving:'||l_proc, 10);
747 --
748 End chk_costed_flag;
749
750 --
751 -- ----------------------------------------------------------------------------
752 -- |------------------------< chk_benefit_plan >------------------------------|
753 -- ----------------------------------------------------------------------------
754 --
755 -- Procedure to validate the following business rule:
756 --
757 -- 1. Input Values 'Coverage','EE Contr' and 'ER Contr' for type A benefit
758 -- plan cannot be updated at all.
759 --
760 Procedure chk_benefit_plan
761 (p_element_link_id in number
762 ,p_input_value_id in number
763 ,p_effective_date in date
764 ) is
765 --
766 l_proc varchar2(72) := g_package||'chk_benefit_plan';
767 l_contributions_used pay_element_links_v.contributions_used%type;
768 l_name pay_input_values_f.name%type;
769
770 Cursor C_contributions_used
771 is
772 select contributions_used
773 from pay_element_links_v
774 where element_link_id = p_element_link_id
775 and p_effective_date between effective_start_date
776 and effective_end_date;
777
778 Cursor C_name
779 is
780 select name
781 from pay_input_values_f
782 where input_value_id = p_input_value_id
783 and p_effective_date between effective_start_date
784 and effective_end_date;
785 --
786 Begin
787 hr_utility.set_location(' Entering:'||l_proc, 5);
788 --
789 Open C_contributions_used;
790 Fetch C_contributions_used into l_contributions_used;
791 Close C_contributions_used;
792 --
793 Open C_name;
794 Fetch C_name into l_name;
795 Close C_name;
796 --
797 If nvl(l_contributions_used,'N') = 'Y'
798 and (l_name in ('Coverage',
799 'EE Contr',
800 'ER Contr')) Then
801 fnd_message.set_name('PAY', 'PAY_33078_LK_INP_VAL_NO_UPD');
802 fnd_message.raise_error;
803 End If;
804 --
805 hr_utility.set_location(' Leaving:'||l_proc, 10);
806 End chk_benefit_plan;
807 --
808 -- ----------------------------------------------------------------------------
809 -- |------------------------< chk_default_value >-----------------------------|
810 -- ----------------------------------------------------------------------------
811 --
812 -- Procedure to validate the following business rule:
813 --
814 -- 1. If the Input Value is hot defaulted and entries exist, then the default
815 -- cannot be updated to NULL.
816 --
817 Procedure chk_default_value
818 (p_element_link_id in number
819 ,p_input_value_id in number
820 ,p_effective_date in date
821 ,p_default_value in varchar2
822 ) is
823 --
824 l_proc varchar2(72) := g_package||'chk_default_value';
825 l_exists varchar2(1);
826 l_hot_default_flag pay_input_values_f.hot_default_flag%type;
827 l_element_type_id pay_input_values_f.element_type_id%type;
828
829 Cursor c_hot_default_flag
830 is
831 select hot_default_flag, element_type_id
832 from pay_input_values_f
833 where input_value_id = p_input_value_id
834 and p_effective_date between effective_start_date
835 and effective_end_date;
836
837 Cursor c_element_entry(p_element_type_id number)
838 is
839 select null
840 from pay_element_entries_f
841 where element_link_id = p_element_link_id
842 and element_type_id = p_element_type_id
843 and p_effective_date between effective_start_date
844 and effective_end_date;
845 --
846 Begin
847 hr_utility.set_location(' Entering:'||l_proc, 5);
848 --
849 Open c_hot_default_flag;
850 Fetch c_hot_default_flag into l_hot_default_flag, l_element_type_id;
851 Close c_hot_default_flag;
852
853 If (l_hot_default_flag = 'Y'
854 and pay_liv_shd.g_old_rec.default_value is not null
855 and p_default_value is null) Then
856 Open c_element_entry(l_element_type_id);
857 Loop
858 Fetch c_element_entry into l_exists;
859 If c_element_entry%found Then
860 fnd_message.set_name('PAY', 'PAY_33079_LK_INP_VAL_NO_UPD');
861 fnd_message.raise_error;
862 Else
863 exit;
864 End if;
865 End Loop;
866 Close c_element_entry;
867 End if;
868 --
869 hr_utility.set_location(' Leaving:'||l_proc, 10);
870 End chk_default_value;
871 -- ----------------------------------------------------------------------------
872 -- |---------------------------< insert_validate >----------------------------|
873 -- ----------------------------------------------------------------------------
874 Procedure insert_validate
875 (p_rec in pay_liv_shd.g_rec_type
876 ,p_effective_date in date
877 ,p_datetrack_mode in varchar2
878 ,p_validation_start_date in date
879 ,p_validation_end_date in date
880 ) is
881 --
882 l_proc varchar2(72) := g_package||'insert_validate';
883 l_default_range_warning boolean;
884 --
885 Begin
886 hr_utility.set_location('Entering:'||l_proc, 5);
887 --
888 -- Call all supporting business operations
889 --
890 chk_warning_or_error
891 (p_warning_or_error => p_rec.warning_or_error
892 ,p_max_value => p_rec.max_value
893 ,p_min_value => p_rec.min_value
894 );
895
896 chk_min_and_max_values
897 (p_input_value_id => p_rec.input_value_id
898 ,p_effective_date => p_effective_date
899 ,p_default_value => p_rec.default_value
900 ,p_max_value => p_rec.max_value
901 ,p_min_value => p_rec.min_value
902 ,p_warning_or_error => p_rec.warning_or_error
903 ,p_default_range_warning => l_default_range_warning
904 );
905
906 chk_costed_flag
907 (p_element_link_id => p_rec.element_link_id
908 ,p_effective_date => p_effective_date
909 ,p_input_value_id => p_rec.input_value_id
910 ,p_costed_flag => p_rec.costed_flag
911 );
912
913 --
914 -- Validate Dependent Attributes
915 --
916 hr_utility.set_location(' Leaving:'||l_proc, 10);
917 End insert_validate;
918 --
919 -- ----------------------------------------------------------------------------
920 -- |---------------------------< update_validate >----------------------------|
921 -- ----------------------------------------------------------------------------
922 Procedure update_validate
923 (p_rec in pay_liv_shd.g_rec_type
924 ,p_effective_date in date
925 ,p_datetrack_mode in varchar2
926 ,p_validation_start_date in date
927 ,p_validation_end_date in date
928 ,p_default_range_warning out nocopy boolean
929 ,p_default_formula_warning out nocopy boolean
930 ,p_assignment_id_warning out nocopy boolean
931 ,p_formula_message out nocopy varchar2
932
933 ) is
934 --
935 l_proc varchar2(72) := g_package||'update_validate';
936 l_business_group_id pay_element_links_f.business_group_id%type;
937 l_formula_id number;
938 --
939 Cursor c_business_group_id
940 is
941 select business_group_id
942 from pay_element_links_f
943 where element_link_id = p_rec.element_link_id;
944 --
945 Cursor c_formula
946 is
947 select formula_id
948 from pay_input_values_f
949 where input_value_id = p_rec.input_value_id
950 and p_effective_date between effective_start_date
951 and effective_end_date;
952 --
953 Begin
954 hr_utility.set_location('Entering:'||l_proc, 5);
955 --
956 -- Call all supporting business operations
957 --
958 chk_warning_or_error
959 (p_warning_or_error => p_rec.warning_or_error
960 ,p_max_value => p_rec.max_value
961 ,p_min_value => p_rec.min_value
962 );
963
964 chk_min_and_max_values
965 (p_input_value_id => p_rec.input_value_id
966 ,p_effective_date => p_effective_date
967 ,p_default_value => p_rec.default_value
968 ,p_max_value => p_rec.max_value
969 ,p_min_value => p_rec.min_value
970 ,p_warning_or_error => p_rec.warning_or_error
971 ,p_default_range_warning => p_default_range_warning
972 );
973
974 chk_costed_flag
975 (p_element_link_id => p_rec.element_link_id
976 ,p_effective_date => p_effective_date
977 ,p_input_value_id => p_rec.input_value_id
978 ,p_costed_flag => p_rec.costed_flag
979 );
980
981 chk_benefit_plan
982 (p_element_link_id => p_rec.element_link_id
983 ,p_input_value_id => p_rec.input_value_id
984 ,p_effective_date => p_effective_date
985 );
986
987 chk_default_value
988 (p_element_link_id => p_rec.element_link_id
989 ,p_input_value_id => p_rec.input_value_id
990 ,p_effective_date => p_effective_date
991 ,p_default_value => p_rec.default_value
992 );
993
994 --
995 Open c_business_group_id;
996 Fetch c_business_group_id into l_business_group_id;
997 Close c_business_group_id;
998 --
999 Open c_formula;
1000 Fetch c_formula into l_formula_id;
1001 Close c_formula;
1002 --
1003 If (p_rec.default_value is not null and l_formula_id is not null) then
1004 pay_ivl_bus.chk_formula_validation
1005 (p_default_value => p_rec.default_value
1006 ,p_warning_or_error => p_rec.warning_or_error
1007 ,p_effective_date => p_effective_date
1008 ,p_input_value_id => p_rec.input_value_id
1009 ,p_formula_id => l_formula_id
1010 ,p_business_group_id => l_business_group_id
1011 ,p_default_formula_warning => p_default_formula_warning
1012 ,p_assignment_id_warning => p_assignment_id_warning
1013 ,p_formula_message => p_formula_message
1014 );
1015 End If;
1016 --
1017 -- Validate Dependent Attributes
1018 --
1019 -- Call the datetrack update integrity operation
1020 --
1021 dt_update_validate
1022 (p_input_value_id => p_rec.input_value_id
1023 ,p_element_link_id => p_rec.element_link_id
1024 ,p_datetrack_mode => p_datetrack_mode
1025 ,p_validation_start_date => p_validation_start_date
1026 ,p_validation_end_date => p_validation_end_date
1027 );
1028 --
1029 chk_non_updateable_args
1030 (p_effective_date => p_effective_date
1031 ,p_rec => p_rec
1032 );
1033 --
1034 --
1035 hr_utility.set_location(' Leaving:'||l_proc, 10);
1036 End update_validate;
1037 --
1038 -- ----------------------------------------------------------------------------
1039 -- |---------------------------< delete_validate >----------------------------|
1040 -- ----------------------------------------------------------------------------
1041 Procedure delete_validate
1042 (p_rec in pay_liv_shd.g_rec_type
1043 ,p_effective_date in date
1044 ,p_datetrack_mode in varchar2
1045 ,p_validation_start_date in date
1046 ,p_validation_end_date in date
1047 ) is
1048 --
1049 l_proc varchar2(72) := g_package||'delete_validate';
1050 --
1051 Begin
1052 hr_utility.set_location('Entering:'||l_proc, 5);
1053 --
1054 -- Call all supporting business operations
1055 --
1056 dt_delete_validate
1057 (p_datetrack_mode => p_datetrack_mode
1058 ,p_validation_start_date => p_validation_start_date
1059 ,p_validation_end_date => p_validation_end_date
1060 ,p_link_input_value_id => p_rec.link_input_value_id
1061 );
1062 --
1063 hr_utility.set_location(' Leaving:'||l_proc, 10);
1064 End delete_validate;
1065 --
1066 end pay_liv_bus;
[Home] [Help]