1 Package Body pay_ivl_bus as
2 /* $Header: pyivlrhi.pkb 120.0 2005/05/29 06:04:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ivl_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_input_value_id number default null;
15
16 cursor csr_row_count is
17 select count(*) from pay_input_values_f
18 where input_value_id = pay_ivl_shd.g_old_rec.input_value_id;
19 --
20 -- ---------------------------------------------------------------------------
21 -- |----------------------< set_security_group_id >--------------------------|
22 -- ---------------------------------------------------------------------------
23 --
24 Procedure set_security_group_id
25 (p_input_value_id in number
26 ,p_associated_column1 in varchar2 default null
27 ) is
28
29 --
30 -- Declare cursor
31 --
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , pay_input_values_f ivl
36 where ivl.input_value_id = p_input_value_id
37 and pbg.business_group_id = ivl.business_group_id;
38 --
39 -- Declare local variables
40 --
41 l_security_group_id number;
42 l_proc varchar2(72) := g_package||'set_security_group_id';
43 --
44 begin
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52
53 ,p_argument => 'input_value_id'
54 ,p_argument_value => p_input_value_id
55 );
56 --
57 open csr_sec_grp;
58 fetch csr_sec_grp into l_security_group_id;
59 --
60 if csr_sec_grp%notfound then
61 --
62 close csr_sec_grp;
63 --
64 -- The primary key is invalid therefore we must error
65 --
66 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
67 hr_multi_message.add
68 (p_associated_column1
69 => nvl(p_associated_column1,'INPUT_VALUE_ID')
70 );
71 --
72 else
73 close csr_sec_grp;
74 --
75 -- Set the security_group_id in CLIENT_INFO
76
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_input_value_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100
101 from per_business_groups pbg
102 , pay_input_values_f ivl
103 where ivl.input_value_id = p_input_value_id
104 and pbg.business_group_id (+) = ivl.business_group_id;
105 --
106 -- Declare local variables
107 --
108 l_legislation_code varchar2(150);
109 l_proc varchar2(72) := g_package||'return_legislation_code';
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'|| l_proc, 10);
114 --
115 -- Ensure that all the mandatory parameter are not null
116 --
117 hr_api.mandatory_arg_error
118 (p_api_name => l_proc
119 ,p_argument => 'input_value_id'
120 ,p_argument_value => p_input_value_id
121 );
122 --
123 if ( nvl(pay_ivl_bus.g_input_value_id, hr_api.g_number)
124
125 = p_input_value_id) then
126 --
127 -- The legislation code has already been found with a previous
128 -- call to this function. Just return the value in the global
129 -- variable.
130 --
131 l_legislation_code := pay_ivl_bus.g_legislation_code;
132 hr_utility.set_location(l_proc, 20);
133 else
134 --
135 -- The ID is different to the last call to this function
136 -- or this is the first call to this function.
137 --
138 open csr_leg_code;
139 fetch csr_leg_code into l_legislation_code;
140 --
141 if csr_leg_code%notfound then
142 --
143 -- The primary key is invalid therefore we must error
144 --
145 close csr_leg_code;
146 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
147 fnd_message.raise_error;
148
149 end if;
150 hr_utility.set_location(l_proc,30);
151 --
152 -- Set the global variables so the values are
153 -- available for the next call to this function.
154 --
155 close csr_leg_code;
156 pay_ivl_bus.g_input_value_id := p_input_value_id;
157 pay_ivl_bus.g_legislation_code := l_legislation_code;
158 end if;
159 hr_utility.set_location(' Leaving:'|| l_proc, 40);
160 return l_legislation_code;
161 end return_legislation_code;
162 --
163 -- ----------------------------------------------------------------------------
164 -- |-----------------------< chk_non_updateable_args >------------------------|
165 -- ----------------------------------------------------------------------------
166 -- {Start Of Comments}
167 --
168 -- Description:
169 -- This procedure is used to ensure that non updateable attributes have
170 -- not been updated. If an attribute has been updated an error is generated.
171 --
172
173 -- Pre Conditions:
174 -- g_old_rec has been populated with details of the values currently in
175 -- the database.
176 --
177 -- In Arguments:
178 -- p_rec has been populated with the updated values the user would like the
179 -- record set to.
180 --
181 -- Post Success:
182 -- Processing continues if all the non updateable attributes have not
183 -- changed.
184 --
185 -- Post Failure:
186 -- An application error is raised if any of the non updatable attributes
187 -- have been altered.
188 --
189 -- {End Of Comments}
190 -- ----------------------------------------------------------------------------
191 Procedure chk_non_updateable_args
192 (p_effective_date in date
193 ,p_rec in pay_ivl_shd.g_rec_type
194 ) IS
195 --
196
197 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
198 --
199 Begin
200 --
201 -- Only proceed with the validation if a row exists for the current
202 -- record in the HR Schema.
203 --
204 IF NOT pay_ivl_shd.api_updating
205 (p_input_value_id => p_rec.input_value_id
206 ,p_effective_date => p_effective_date
207 ,p_object_version_number => p_rec.object_version_number
208 ) THEN
209 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
210 fnd_message.set_token('PROCEDURE ', l_proc);
211 fnd_message.set_token('STEP ', '5');
212 fnd_message.raise_error;
213 END IF;
214
215 End chk_non_updateable_args;
216 --
217
218 -- ----------------------------------------------------------------------------
219 -- |--------------------------< dt_update_validate >--------------------------|
220 -- ----------------------------------------------------------------------------
221 -- {Start Of Comments}
222 --
223 -- Description:
224 -- This procedure is used for referential integrity of datetracked
225 -- parent entities when a datetrack update operation is taking place
226 -- and where there is no cascading of update defined for this entity.
227 --
228 -- Prerequisites:
229 -- This procedure is called from the update_validate.
230 --
231 -- In Parameters:
232 --
233 -- Post Success:
234 -- Processing continues.
235 --
236 -- Post Failure:
237 --
238 -- Developer Implementation Notes:
239 -- This procedure should not need maintenance unless the HR Schema model
240 -- changes.
241
242 --
243 -- Access Status:
244 -- Internal Row Handler Use Only.
245 --
246 -- {End Of Comments}
247 -- ----------------------------------------------------------------------------
248 Procedure dt_update_validate
249 (p_element_type_id in number default hr_api.g_number
250 ,p_datetrack_mode in varchar2
251 ,p_validation_start_date in date
252 ,p_validation_end_date in date
253 ) Is
254 --
255 l_proc varchar2(72) := g_package||'dt_update_validate';
256 --
257 Begin
258 --
259 -- Ensure that the p_datetrack_mode argument is not null
260 --
261 hr_api.mandatory_arg_error
262 (p_api_name => l_proc
263 ,p_argument => 'datetrack_mode'
264 ,p_argument_value => p_datetrack_mode
265
266 );
267 --
268 -- Mode will be valid, as this is checked at the start of the upd.
269 --
270 -- Ensure the arguments are not null
271 --
272 hr_api.mandatory_arg_error
273 (p_api_name => l_proc
274 ,p_argument => 'validation_start_date'
275 ,p_argument_value => p_validation_start_date
276 );
277 --
278 hr_api.mandatory_arg_error
279 (p_api_name => l_proc
280 ,p_argument => 'validation_end_date'
281 ,p_argument_value => p_validation_end_date
282 );
283 --
284 If ((nvl(p_element_type_id, hr_api.g_number) <> hr_api.g_number) and
285 NOT (dt_api.check_min_max_dates
286 (p_base_table_name => 'pay_element_types_f'
287 ,p_base_key_column => 'ELEMENT_TYPE_ID'
288 ,p_base_key_value => p_element_type_id
289
290 ,p_from_date => p_validation_start_date
291 ,p_to_date => p_validation_end_date))) Then
292 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
293 fnd_message.set_token('TABLE_NAME','element types');
294 hr_multi_message.add
295 (p_associated_column1 => pay_ivl_shd.g_tab_nam || '.ELEMENT_TYPE_ID');
296 End If;
297 --
298 Exception
299 When Others Then
300 --
301 -- An unhandled or unexpected error has occurred which
302 -- we must report
303 --
304 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
305 fnd_message.set_token('PROCEDURE', l_proc);
306 fnd_message.set_token('STEP','15');
307 fnd_message.raise_error;
308 End dt_update_validate;
309 --
310 -- ----------------------------------------------------------------------------
311 -- |--------------------------< dt_delete_validate >--------------------------|
312 -- ----------------------------------------------------------------------------
313
314 -- {Start Of Comments}
315 --
316 -- Description:
317 -- This procedure is used for referential integrity of datetracked
318 -- child entities when either a datetrack DELETE or ZAP is in operation
319 -- and where there is no cascading of delete defined for this entity.
320 -- For the datetrack mode of DELETE or ZAP we must ensure that no
321 -- datetracked child rows exist between the validation start and end
322 -- dates.
323 --
324 -- Prerequisites:
325 -- This procedure is called from the delete_validate.
326 --
327 -- In Parameters:
328 --
329 -- Post Success:
330 -- Processing continues.
331 --
332 -- Post Failure:
333 -- If a row exists by determining the returning Boolean value from the
334 -- generic dt_api.rows_exist function then we must supply an error via
335 -- the use of the local exception handler l_rows_exist.
336 --
337
338 -- Developer Implementation Notes:
339 -- This procedure should not need maintenance unless the HR Schema model
340 -- changes.
341 --
342 -- Access Status:
343 -- Internal Row Handler Use Only.
344 --
345 -- {End Of Comments}
346 -- ----------------------------------------------------------------------------
347 Procedure dt_delete_validate
348 (p_input_value_id in number
349 ,p_datetrack_mode in varchar2
350 ,p_validation_start_date in date
351 ,p_validation_end_date in date
352 ) Is
353 --
354 l_proc varchar2(72) := g_package||'dt_delete_validate';
355 --
356 Begin
357 --
358 -- Ensure that the p_datetrack_mode argument is not null
359 --
360 hr_api.mandatory_arg_error
361
362 (p_api_name => l_proc
363 ,p_argument => 'datetrack_mode'
364 ,p_argument_value => p_datetrack_mode
365 );
366 --
367 -- Only perform the validation if the datetrack mode is either
368 -- DELETE or ZAP
369 --
370 If (p_datetrack_mode = hr_api.g_delete or
371 p_datetrack_mode = hr_api.g_zap) then
372 --
373 --
374 -- Ensure the arguments are not null
375 --
376 hr_api.mandatory_arg_error
377 (p_api_name => l_proc
378 ,p_argument => 'validation_start_date'
379 ,p_argument_value => p_validation_start_date
380 );
381 --
382 hr_api.mandatory_arg_error
383 (p_api_name => l_proc
384 ,p_argument => 'validation_end_date'
385
386 ,p_argument_value => p_validation_end_date
387 );
388 --
389 hr_api.mandatory_arg_error
390 (p_api_name => l_proc
391 ,p_argument => 'input_value_id'
392 ,p_argument_value => p_input_value_id
393 );
394 --
395 If (dt_api.rows_exist
396 (p_base_table_name => 'ben_acty_base_rt_f'
397 ,p_base_key_column => 'input_value_id'
398 ,p_base_key_value => p_input_value_id
399 ,p_from_date => p_validation_start_date
400 ,p_to_date => p_validation_end_date
401 )) Then
402 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
403 fnd_message.set_token('TABLE_NAME','acty base rt');
404 hr_multi_message.add;
405 End If;
406 If (dt_api.rows_exist
407 (p_base_table_name => 'pay_link_input_values_f'
408 ,p_base_key_column => 'input_value_id'
409
410 ,p_base_key_value => p_input_value_id
411 ,p_from_date => p_validation_start_date
412 ,p_to_date => p_validation_end_date
413 )) Then
414 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
415 fnd_message.set_token('TABLE_NAME','link input values');
416 hr_multi_message.add;
417 End If;
418 --
419 End If;
420 --
421 Exception
422 When Others Then
423 --
424 -- An unhandled or unexpected error has occurred which
425 -- we must report
426 --
427 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
428 fnd_message.set_token('PROCEDURE', l_proc);
429 fnd_message.set_token('STEP','15');
430 fnd_message.raise_error;
431 --
432 End dt_delete_validate;
433
434 --
435 -- ----------------------------------------------------------------------------
436 -- |----------------------< chk_startup_action >------------------------------|
437 -- ----------------------------------------------------------------------------
438 --
439 -- Description:
440 -- This procedure will check that the current action is allowed according
441 -- to the current startup mode.
442 --
443 -- ----------------------------------------------------------------------------
444 PROCEDURE chk_startup_action
445 (p_insert IN boolean
446 ,p_business_group_id IN number
447 ,p_legislation_code IN varchar2
448 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
449 --
450 BEGIN
451 --
452 -- Call the supporting procedure to check startup mode
453 IF (p_insert) THEN
454 hr_startup_data_api_support.chk_startup_action
455
456 (p_generic_allowed => TRUE
457 ,p_startup_allowed => TRUE
458 ,p_user_allowed => TRUE
459 ,p_business_group_id => p_business_group_id
460 ,p_legislation_code => p_legislation_code
461 ,p_legislation_subgroup => p_legislation_subgroup
462 );
463 ELSE
464 hr_startup_data_api_support.chk_upd_del_startup_action
465 (p_generic_allowed => TRUE
466 ,p_startup_allowed => TRUE
467 ,p_user_allowed => TRUE
468 ,p_business_group_id => p_business_group_id
469 ,p_legislation_code => p_legislation_code
470 ,p_legislation_subgroup => p_legislation_subgroup
471 );
472 END IF;
473 --
474 END chk_startup_action;
475
476 --
477 -- ----------------------------------------------------------------------------
478 -- |----------------------< chk_lookup_type >------------------------------|
479 -- ----------------------------------------------------------------------------
480 --
481 -- Description:
482 -- This procedure is used to ensure that LOOKUP_TYPE can be entered only
483 -- when Unit Of Measure is 'Character' and is the one present in HR_LOOKUPS,
484 -- enabled and valid as of current date.
485 --
486 -- ----------------------------------------------------------------------------
487 PROCEDURE chk_lookup_type
488 ( p_lookup_type varchar2
489 ,p_uom varchar2
490 ,p_effective_date date
491 ) IS
492 cursor csr_lookup is
493 select 'X' from hr_lookups
494 where upper(lookup_type) = nvl(upper(p_lookup_type),lookup_type)
495 and enabled_flag = 'Y'
496 and p_effective_date between
497 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
498
499 l_dummy varchar2(1);
500
501 l_proc varchar2(72) := g_package||'chk_lookup_type';
502
503 BEGIN
504
505 hr_utility.set_location(' Entering:'|| l_proc, 10);
506
507 if p_lookup_type is not null and upper(p_uom) <> 'C' then
508 fnd_message.set_name('PAY','PAY_34117_INVALID_UOM');
509 fnd_message.raise_error;
510 else
511 open csr_lookup;
512 fetch csr_lookup into l_dummy;
513 if csr_lookup%notfound then
514 close csr_lookup;
515 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
516 fnd_message.raise_error;
517 end if;
518 close csr_lookup;
519 end if;
520
521 hr_utility.set_location(' Leaving:'|| l_proc, 10);
522
523 END chk_lookup_type;
524
525 --
526 -- ----------------------------------------------------------------------------
527 -- |----------------------< chk_formula_id >------------------------------|
528 -- ----------------------------------------------------------------------------
529 --
530 -- Description:
531 -- This procedure is used to ensure that FORMULA_ID is present in
532 -- FF_FORMULAS_F as of session date and must be of type
533 -- 'ELEMENT INPUT VALIDATION'
534 --
535 -- ----------------------------------------------------------------------------
536 PROCEDURE chk_formula_id
537 ( p_business_group_id number
538 ,p_legislation_code varchar2
539 ,p_effective_date date
540 ,p_formula_id number
541 ) IS
542
543 cursor csr_formula is
544 select 'X'
545 from ff_formulas_f ff, ff_formula_types ft
546 where nvl(ff.legislation_code,nvl(p_legislation_code,'~~nvl~~'))
547 = nvl(p_legislation_code,'~~nvl~~')
548 and nvl(ff.business_group_id, nvl(p_business_group_id,-1))=nvl(p_business_group_id,-1)
549 and p_effective_date between ff.effective_start_date and ff.effective_end_date
550 and ff.formula_type_id = ft.formula_type_id
551 and upper (ft.formula_type_name) = 'ELEMENT INPUT VALIDATION'
552 and ff.formula_id = p_formula_id;
553
554 l_dummy varchar2(1);
555
556 l_proc varchar2(72) := g_package||'chk_formula_id';
557
558 BEGIN
559
560 hr_utility.set_location(' Entering:'|| l_proc, 10);
561
562 if p_formula_id is not null then
563 open csr_formula;
564 fetch csr_formula into l_dummy;
565 if csr_formula%notfound then
566 close csr_formula;
567 fnd_message.set_name('PAY','PAY_34116_INVALID_FORMULA');
568 fnd_message.raise_error;
569 end if;
570 close csr_formula;
571 end if;
572
573 hr_utility.set_location(' Leaving:'|| l_proc, 10);
574
575 END chk_formula_id;
576 --
577 -- ----------------------------------------------------------------------------
578 -- |------------------------< chk_formula_validation >------------------------|
579 -- ----------------------------------------------------------------------------
580 Procedure chk_formula_validation
581 (p_default_value in varchar2
582 ,p_warning_or_error in varchar2
583 ,p_effective_date in date
584 ,p_input_value_id in number default null
585 ,p_formula_id in number default null
586 ,p_business_group_id in number
587 ,p_default_formula_warning out nocopy boolean
588 ,p_assignment_id_warning out nocopy boolean
589 ,p_formula_message out nocopy varchar2
590 ) is
591 --
592 l_proc varchar2(72) := g_package||'chk_formula_val';
593 l_formula_status varchar2(10);
594 l_formula_message fnd_new_messages.message_text%type;
595 l_formula_id number;
596 l_inputs ff_exec.inputs_t;
597 l_outputs ff_exec.outputs_t;
598 --
599 Cursor C_formula
600 is
601 select formula_id
602 from pay_input_values_f
603 where input_value_id = p_input_value_id
604 and p_effective_date between effective_start_date
605 and effective_end_date;
606 --
607 Begin
608 hr_utility.set_location(' Entering:'||l_proc, 5);
609 --
610 If p_business_group_id is not null then
611 --
612 -- Validation is not done for startup elements
613 --
614 l_formula_id := p_formula_id;
615 p_assignment_id_warning := false;
616
617 hr_utility.set_location(' Step 1:'||l_proc, 6);
618 If (l_formula_id is null) then
619 --
620 Open C_formula;
621 Fetch C_formula into l_formula_id;
622 Close C_formula;
623 --
624 End If;
625 --
626 hr_utility.set_location(' Step 3:'||l_proc, 8);
627 -- We need to call a formula to validate the default value.
628 --For Bug No. 2879170 added if condtion.
629 If l_formula_id is not null then
630 ff_exec.init_formula(l_formula_id,
631 p_effective_date,
632 l_inputs,
633 l_outputs);
634 --
635 -- Check the input count before attempting to
636 -- set the input and context values.
637 --
638 hr_utility.set_location(' Step 4:'||l_proc, 9);
639 If(l_inputs.count >= 1) then
640 -- Set up the inputs and contexts to formula.
641 For i in l_inputs.first..l_inputs.last loop
642 If l_inputs(i).name = 'ASSIGNMENT_ID' then
643 -- We cannot set assignment id at this level, hence
644 -- raise warning and quit
645 p_assignment_id_warning := True;
646 exit;
647 Elsif l_inputs(i).name = 'BUSINESS_GROUP_ID' then
648 -- Set the business_group_id context.
649 l_inputs(i).value := p_business_group_id;
650 Elsif l_inputs(i).name = 'DATE_EARNED' then
651 -- Set the date_earned context.
652 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
653 Elsif l_inputs(i).name = 'ENTRY_VALUE' then
654 -- Set the input to the entry value to be validated.
655 -- Note - need to pass database format to formula.
656 l_inputs(i).value := p_default_value;
657 Else
658 -- No context recognised.
659 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
660 fnd_message.set_token('PROCEDURE', l_proc);
661 fnd_message.set_token('STEP','10');
662 fnd_message.raise_error;
663 End if;
664 End loop;
665 End if;
666 --
667 -- Dont validate if the assignment context exists or if its a start up
668 -- element.
669 --
670 If not p_assignment_id_warning then
671 --
672 ff_exec.run_formula(l_inputs, l_outputs);
673 --
674 -- Now obtain the return values. There should be
675 -- exactly two outputs.
676 If l_outputs.count <> 2 then
677 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
678 fnd_message.set_token('PROCEDURE', l_proc);
679 fnd_message.set_token('STEP','20');
680 fnd_message.raise_error;
681 End if;
682 --
683 For i in l_outputs.first..l_outputs.last loop
684 If l_outputs(i).name = 'FORMULA_MESSAGE' then
685 --
686 hr_utility.set_location(' Step 5:'||l_proc, 10);
687 l_formula_message := l_outputs(i).value;
688 Elsif l_outputs(i).name = 'FORMULA_STATUS' then
689 --
690 hr_utility.set_location(' Step 6:'||l_proc, 11);
691 l_formula_status := upper(l_outputs(i).value);
692 Else
693 --
694 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
695 fnd_message.set_token('PROCEDURE', l_proc);
696 fnd_message.set_token('STEP','30');
697 fnd_message.raise_error;
698 --
699 End if;
700 End loop;
701 --
702 -- Check whether we have raised an error and act appropriately.
703 --
704 hr_utility.set_location(' Step 7:'||l_proc, 11);
705 If l_formula_status <> 'S' and p_warning_or_error = 'E' then
706 -- I.e. the formula validation failed and we need to raise an error.
707 If l_formula_message is null then
708 -- User not defined an error message.
709 --
710 fnd_message.set_name('PAY','PAY_33083_LK_INP_VAL_FORML_ERR');
711 fnd_message.raise_error;
712 Else
713 -- User has defined message and so we can raise it.
714 fnd_message.set_name('PAY','HR_ELE_ENTRY_FORMULA_HINT');
715 fnd_message.set_token('FORMULA_TEXT', l_formula_message, false);
716 fnd_message.raise_error;
717 End if;
718 Elsif l_formula_status <> 'S' and p_warning_or_error = 'W' then
719 -- We have failed validation, but only want to warn.
720 p_default_formula_warning := true;
721 --
722 End if;
723 --
724 End if;
725
726 p_formula_message := l_formula_message;
727 --
728 End If;
729 End If;
730 --
731 hr_utility.set_location(' Leaving:'||l_proc, 10);
732 End;
733 --
734 -- ----------------------------------------------------------------------------
735 -- |---------------------------<chk_value_set_id >----------------------------|
736 -- ----------------------------------------------------------------------------
737 PROCEDURE chk_value_set_id (
738 p_value_set_id number,
739 p_uom varchar2
740 ) is
741 --
742 l_proc varchar2(72) := g_package||'chk_value_set_id';
743 l_validation_type varchar2(1);
744 --
745 cursor csr_value_set is
746 select validation_type
747 from fnd_flex_value_sets
748 where flex_value_set_id = p_value_set_id;
749 --
750 BEGIN
751 --
752 hr_utility.set_location(' Entering:'|| l_proc, 10);
753 --
754 if p_value_set_id is not null then
755 -- Check uom is 'C'
756 if upper(p_uom) <> 'C' then
757 hr_utility.set_location(l_proc, 20);
758 fnd_message.set_name('PAY','PAY_34117_INVALID_UOM');
759 fnd_message.raise_error;
760 else
761 -- Check value set id is valid, i.e. that it exists and that the value
762 -- set is table-validated, i.e. validation_type is 'F'
763 open csr_value_set;
764 fetch csr_value_set into l_validation_type;
765 if csr_value_set%notfound or l_validation_type <> 'F' then
766 close csr_value_set;
767 hr_utility.set_location(l_proc, 30);
768 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
769 fnd_message.set_token('PROCEDURE', l_proc);
770 fnd_message.set_token('STEP','30');
771 fnd_message.raise_error;
772 end if;
773 close csr_value_set;
774 end if;
775 end if;
776 --
777 hr_utility.set_location(' Leaving:'|| l_proc, 40);
778 --
779 END chk_value_set_id;
780
781 -- ----------------------------------------------------------------------------
782 -- |----------------------<chk_upd_display_sequence >-------------------------|
783 -- ----------------------------------------------------------------------------
784 --
785 -- Description:
786 -- This procedure will check that display sequence is not updated
787 -- for an element's input values if there are paylink batch lines for the
788 -- element.
789 --
790 -- ----------------------------------------------------------------------------
791 PROCEDURE chk_upd_display_sequence
792 (p_element_type_id number
793 ,p_name varchar2
794 ,p_display_sequence number) is
795
796 l_proc varchar2(72) := g_package||'chk_upd_display_sequence';
797
798 BEGIN
799
800 hr_utility.set_location(' Entering:'|| l_proc, 10);
801
802 if (p_name <> pay_ivl_shd.g_old_rec.name or p_display_sequence
803 <> pay_ivl_shd.g_old_rec.display_sequence) then
804 pay_element_types_pkg.check_for_paylink_batches
805 (p_element_type_id => p_element_type_id
806 ,p_element_name => p_name);
807 end if;
808
809 hr_utility.set_location(' Leaving:'|| l_proc, 10);
810
811 END chk_upd_display_sequence;
812
813 --
814 -- ----------------------------------------------------------------------------
815 -- |----------------------<chk_upd_generate_db_items_flag >-------------------|
816 -- ----------------------------------------------------------------------------
817 --
818 -- Description:
819 -- This procedure will ensure that if generate_db_items_flag is updated
820 -- and the datetrack mode is not CORRECTION then it will force CORRECTION
821 -- mode.
822 --
823 -- ----------------------------------------------------------------------------
824 PROCEDURE chk_upd_generate_db_items_flag
825 (p_datetrack_mode IN OUT NOCOPY varchar2
826 ,p_generate_db_items_flag IN varchar2)
827 IS
828
829 l_proc varchar2(72) := g_package||'chk_upd_generate_db_items_flag';
830
831 l_count number;
832
833 BEGIN
834
835 hr_utility.set_location(' Entering:'|| l_proc, 10);
836
837 if (p_generate_db_items_flag <> pay_ivl_shd.g_old_rec.generate_db_items_flag)
838 and p_datetrack_mode <> 'CORRECTION' then
839 open csr_row_count;
840 fetch csr_row_count into l_count;
841 close csr_row_count;
842 if l_count > 1 then
843 fnd_message.set_name('PAY', 'PAY_34151_ELE_NO_DATE_UPD');
844 fnd_message.raise_error;
845 else
846 p_datetrack_mode := 'CORRECTION';
847 end if;
848 end if;
849
850 hr_utility.set_location(' Leaving:'|| l_proc, 10);
851
852 END chk_upd_generate_db_items_flag;
853
854 --
855 -- ----------------------------------------------------------------------------
856 -- |----------------------< chk_upd_name >-------------------------------------|
857 -- ----------------------------------------------------------------------------
858 --
859 -- Description:
860 -- This procedure will do the following checks if name is updated
861 -- 1) Ensure that the mode is CORRECTION , if not then force it.
862 -- 2) Check that the name is unique for the element type id
863 -- 3) Name is not updated if its a 'Pay Value' or updated to become a
864 -- 'Pay Value'
865 -- 4) Name is not updated if contributions used for element type is Yes
866 -- and the name is one of these 'Coverage','EE Contr' and 'ER Contr'
867 -- ----------------------------------------------------------------------------
868 PROCEDURE chk_upd_name
869 (p_datetrack_mode IN OUT NOCOPY varchar2
870 ,p_name IN varchar2
871 ,p_effective_date in date
872 ,p_element_type_id IN number)
873
874 IS
875
876 cursor csr_ben_contri is
877 select contributions_used from
878 ben_benefit_classifications
879 where benefit_classification_id in ( select distinct benefit_classification_id
880 from pay_element_types_f
881 where element_type_id = p_element_type_id
882 and p_effective_date between effective_start_date
883 and effective_end_date);
884
885 l_dummy varchar2(1);
886
887 l_proc varchar2(72) := g_package||'chk_upd_name';
888 l_name pay_input_values_f.name%type := p_name;
889 l_boolean boolean;
890 l_chk_name pay_input_values_f.name%type;
891
892 l_count number;
893
894 BEGIN
895
896 hr_utility.set_location(' Entering:'|| l_proc, 10);
897
898
899 if (p_name <> pay_ivl_shd.g_old_rec.name) then
900
901 -- Force Correction mode
902 if p_datetrack_mode <> 'CORRECTION' then
903 open csr_row_count;
904 fetch csr_row_count into l_count;
905 close csr_row_count;
906 if l_count > 1 then
907 fnd_message.set_name('PAY', 'PAY_34151_ELE_NO_DATE_UPD');
908 fnd_message.raise_error;
909 else
910 p_datetrack_mode := 'CORRECTION';
911 end if;
912 end if;
913
914 -- Check if the new name is unique
915 l_boolean := pay_input_values_pkg.name_not_unique
916 (p_element_type_id,
917 null,
918 l_name,
919 p_error_if_true => TRUE);
920
921
922 -- Check if the name is in proper format
923 hr_chkfmt.checkformat
924 (l_name,
925 'PAY_NAME',
926 l_chk_name,
927 null,
928 null,
929 'N',
930 l_chk_name,
931 null);
932
933 -- Check that PAY VALUE name is not updated
934 if upper(p_name) = 'PAY VALUE' then
935 fnd_message.set_name('PAY', 'PAY_34126_NAME_PAY_VAL_UPD');
936 fnd_message.raise_error;
937 end if;
938
939 -- Check for the new name
940 if upper(pay_ivl_shd.g_old_rec.name) = 'PAY VALUE' then
941 fnd_message.set_name('PAY', 'PAY_34126_NAME_PAY_VAL_UPD');
942 fnd_message.raise_error;
943 end if;
944
945 -- Name is not updated if contributions used for element type is Yes
946 -- and the name is one of these 'Coverage','EE Contr' and 'ER Contr'
947 open csr_ben_contri;
948 fetch csr_ben_contri into l_dummy;
949
950 if l_dummy = 'Y' and upper(pay_ivl_shd.g_old_rec.name)
951 in ('COVERAGE','EE CONTR','ER CONTR')
952 then
953 close csr_ben_contri;
954 fnd_message.set_name('PAY', 'PAY_33078_LK_INP_VAL_NO_UPD');
955 fnd_message.raise_error;
956 end if;
957 close csr_ben_contri;
958
959 end if;
960
961 hr_utility.set_location(' Leaving:'|| l_proc, 10);
962
963 END chk_upd_name;
964
965 --
966 -- ----------------------------------------------------------------------------
967 -- |----------------------< chk_upd_uom >-------------------------------------|
968 -- ----------------------------------------------------------------------------
969 --
970 -- Description:
971 -- This procedure will check that if the UOM is updated, force the datetrack
972 -- mode to CORRECTION, if not the same.Also check that the UOM is updated
973 -- only in its class and recreate db items.
974 --
975 -- ----------------------------------------------------------------------------
976 PROCEDURE chk_upd_uom
977 (p_datetrack_mode IN OUT NOCOPY varchar2
978 ,p_uom IN varchar2
979 ,p_input_value_id in number
980 ,p_effective_date in date)
981 IS
982 cursor csr_uom is
983 select lookup_code from
984 hr_lookups
985 where upper(substr(lookup_code,1,2))
986 = upper(substr(pay_ivl_shd.g_old_rec.uom,1,2))
987 and lookup_type = 'UNITS'
988 and enabled_flag = 'Y'
989 and p_effective_date between
990 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
991
992 l_dummy varchar2(1);
993
994 l_count number;
995
996 l_proc varchar2(72) := g_package||'chk_upd_uom';
997
998 BEGIN
999
1000 hr_utility.set_location(' Entering:'|| l_proc, 10);
1001
1002 -- Check that the UOM if updated to a not null value is
1003 -- within the class as the previous UOM value
1004 if (p_uom <> pay_ivl_shd.g_old_rec.uom and p_uom is not null) then
1005 if p_datetrack_mode <> 'CORRECTION' then
1006 open csr_row_count;
1007 fetch csr_row_count into l_count;
1008 close csr_row_count;
1009 if l_count > 1 then
1010 fnd_message.set_name('PAY', 'PAY_34151_ELE_NO_DATE_UPD');
1011 fnd_message.raise_error;
1012 else
1013 p_datetrack_mode := 'CORRECTION';
1014 end if;
1015 end if;
1016 if pay_ivl_shd.g_old_rec.uom is not null then
1017 for rec in csr_uom
1018 loop
1019 if substr(rec.lookup_code,1,2) <> substr(p_uom,1,2) then
1020 fnd_message.set_name('PAY', 'PAY_34127_UOM_UPD_CLASS');
1021 fnd_message.raise_error;
1022 end if;
1023 end loop;
1024 end if;
1025 end if;
1026
1027 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1028
1029 END chk_upd_uom;
1030
1031 --
1032 -- ----------------------------------------------------------------------------
1033 -- |----------------------< chk_upd_def_value_null >----------------------|
1034 -- ----------------------------------------------------------------------------
1035 --
1036 -- Description:
1037 -- This procedure will ensure that update fails if
1038 -- the input value is used in a pay basis or there exists
1039 -- element entries for this hot defaulted input value
1040 --
1041 -- ----------------------------------------------------------------------------
1042 PROCEDURE chk_upd_def_value_null
1043 (p_input_value_id in number
1044 ,p_effective_date in date
1045 ,p_default_value in varchar2
1046 ,p_pay_basis_warning out nocopy boolean)
1047 IS
1048
1049 cursor csr_pay_basis is
1050 select 1
1051 from per_pay_bases
1052 where input_value_id = p_input_value_id;
1053
1054 cursor csr_entries is
1055 select 1
1056 from pay_element_entry_values_f
1057 where input_value_id = p_input_value_id
1058 and p_effective_date between effective_start_date
1059 and effective_end_date;
1060
1061
1062 l_dummy varchar2(1);
1063
1064
1065 l_proc varchar2(72) := g_package||'chk_upd_def_value_null';
1066
1067 BEGIN
1068
1069 hr_utility.set_location(' Entering:'|| l_proc, 10);
1070
1071 open csr_pay_basis;
1072
1073 -- Check for pay basis
1074
1075 fetch csr_pay_basis into l_dummy;
1076 if csr_pay_basis%found then
1077 close csr_pay_basis;
1078 p_pay_basis_warning := TRUE;
1079 end if;
1080 close csr_pay_basis;
1081
1082
1083 if p_default_value is null then
1084 -- Check for element entries and Hot default flag
1085
1086 open csr_entries;
1087 fetch csr_entries into l_dummy;
1088 if csr_entries%found and pay_ivl_shd.g_old_rec.hot_default_flag = 'Y' then
1089 close csr_entries;
1090 fnd_message.set_name('PAY', 'PAY_34128_DEF_VAL_HOT_DEF');
1091 fnd_message.raise_error;
1092 end if;
1093 close csr_entries;
1094 end if;
1095
1096 hr_utility.set_location(' Entering:'|| l_proc, 10);
1097
1098 END chk_upd_def_value_null;
1099
1100 --
1101 -- ----------------------------------------------------------------------------
1102 -- |----------------------<chk_upd_mand_flag >--------------------------------|
1103 -- ----------------------------------------------------------------------------
1104 --
1105 -- Description:
1106 -- This procedure ensures that the mandatory flag cannot be changed from
1107 -- 'N' to 'Y'.
1108 --
1109 -- ----------------------------------------------------------------------------
1110 PROCEDURE chk_upd_mand_flag
1111 (p_mandatory_flag varchar2
1112 ) IS
1113
1114 l_proc varchar2(72) := g_package||'chk_upd_mand_flag';
1115
1116 BEGIN
1117
1118 hr_utility.set_location(' Entering:'|| l_proc, 10);
1119
1120
1121 if pay_ivl_shd.g_old_rec.mandatory_flag = 'N' and p_mandatory_flag = 'Y' then
1122 fnd_message.set_name('PAY', 'PAY_34125_MAN_FLAG_UPD');
1123 fnd_message.raise_error;
1124 end if;
1125
1126 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1127
1128 END chk_upd_mand_flag;
1129
1130 --
1131 -- ----------------------------------------------------------------------------
1132 -- |----------------------<chk_hot_default_flag >----------------------------|
1133 -- ----------------------------------------------------------------------------
1134 --
1135 -- Description:
1136 -- This procedure will check that hot default flag is not set to 'Y'
1137 -- if mandatory flag is not 'Y'
1138 --
1139 -- ----------------------------------------------------------------------------
1140 PROCEDURE chk_hot_default_flag
1141 (p_mandatory_flag varchar2
1142 ,p_hot_default_flag varchar2
1143 ) IS
1144 l_proc varchar2(72) := g_package||'chk_hot_default_flag';
1145
1146 BEGIN
1147
1148 hr_utility.set_location(' Entering:'|| l_proc, 10);
1149
1150 if p_hot_default_flag = 'Y' and p_mandatory_flag <> 'Y' then
1151 fnd_message.set_name('PAY','PAY_34119_HOT_DEFAULT_FLAG');
1152 fnd_message.raise_error;
1153 end if;
1154
1155 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1156
1157 END chk_hot_default_flag;
1158 --
1159 -- ----------------------------------------------------------------------------
1160 -- |----------------------<chk_name >-----------------------------------------|
1161 -- ----------------------------------------------------------------------------
1162 --
1163 -- Description:
1164 -- This procedure will do the following validations
1165 -- 1) Validate NAME as a valid db item name using hr_fmt.checkformat
1166 -- 2) NAME must be unique for a element
1167 -- 3) The name 'Pay Value' must have a UOM of 'Money' if the element
1168 -- classifications is of PAYMENTS type
1169 --
1170 -- ----------------------------------------------------------------------------
1171 PROCEDURE chk_name
1172 (p_name varchar2
1173 ,p_element_type_id number
1174 ,p_uom varchar2
1175 ) IS
1176
1177 cursor csr_classification is
1178 select non_payments_flag
1179 from pay_element_classifications
1180 where classification_id in ( select distinct classification_id
1181 from pay_element_types_f
1182 where element_type_id = p_element_type_id);
1183
1184 l_boolean boolean;
1185 l_name pay_input_values_f.name%type := p_name;
1186 l_dummy varchar2(100);
1187
1188 l_proc varchar2(72) := g_package||'chk_name';
1189
1190 BEGIN
1191
1192 hr_utility.set_location(' Entering:'|| l_proc, 10);
1193
1194 -- Check that the name is unique
1195
1196 l_boolean := pay_input_values_pkg.name_not_unique
1197 (p_element_type_id,
1198 null,
1199 l_name,
1200 p_error_if_true => TRUE);
1201
1202 -- Check the name for proper format
1203 hr_chkfmt.checkformat
1204 (l_name,
1205 'PAY_NAME',
1206 l_dummy,
1207 null,
1208 null,
1209 'N',
1210 l_dummy,
1211 null);
1212
1213 -- Check that the name 'Pay Value' must have a UOM of 'Money' if the element
1214 -- classifications is of PAYMENTS type
1215
1216 open csr_classification;
1217 fetch csr_classification into l_dummy;
1218 close csr_classification;
1219
1220
1221 if upper(l_name) = 'PAY VALUE' and upper(p_uom) <> 'M' and l_dummy = 'N' then
1222 fnd_message.set_name('PAY', 'PAY_34122_UOM_MONEY_PAYMENTS');
1223 fnd_message.raise_error;
1224 end if;
1225
1226 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1227
1228 END chk_name;
1229
1230 --
1231 -- ----------------------------------------------------------------------------
1232 -- |----------------------<chk_uom >-----------------------------------------|
1233 -- ----------------------------------------------------------------------------
1234 --
1235 -- Description:
1236 -- This procedure will do the following validations
1237 -- 1) The UOM cannot be 'Money' if no currencies have been specified for the
1238 -- element type
1239 -- 2) Validate UOM with HR_LOOKUPS having LOOKUP_TYPE as 'UNITS'
1240 -- and lookup_code not equal to 'M' if element_types'
1241 -- output_currency_code is null
1242 --
1243 -- ----------------------------------------------------------------------------
1244 PROCEDURE chk_uom
1245 (p_element_type_id in number
1246 ,p_uom in varchar2
1247 ,p_effective_date in date
1248 ) IS
1249 cursor csr_currency is
1250
1251 select input_currency_code
1252 from pay_element_types_f
1253 where element_type_id = p_element_type_id;
1254
1255 cursor csr_lookup is
1256 select 'X' from hr_lookups
1257 where upper(lookup_code) = nvl(upper(p_uom),lookup_code)
1258 and lookup_type = 'UNITS'
1259 and enabled_flag = 'Y'
1260 and p_effective_date between
1261 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
1262
1263 l_dummy varchar2(1);
1264
1265 l_proc varchar2(72) := g_package||'chk_uom';
1266
1267 BEGIN
1268
1269 hr_utility.set_location(' Entering:'|| l_proc, 10);
1270
1271 for rec in csr_currency
1272 loop
1273 if rec.input_currency_code is null and upper(p_uom) = 'M' then
1274 fnd_message.set_name('PAY', 'PAY_6626_INPVAL_NO_MONEY_UOM');
1275 fnd_message.raise_error;
1276 end if;
1277 end loop;
1278
1279 open csr_lookup;
1280
1281 fetch csr_lookup into l_dummy;
1282 if csr_lookup%notfound then
1283 fnd_message.set_name('PAY', 'PAY_6171_INPVAL_NO_LOOKUP');
1284 fnd_message.raise_error;
1285 end if;
1286
1287 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1288
1289 END chk_uom;
1290
1291 --
1292 -- ----------------------------------------------------------------------------
1293 -- |----------------------<chk_default_value >--------------------------------|
1294 -- ----------------------------------------------------------------------------
1295 --
1296 -- Description:
1297 -- This procedure will do the following validations
1298 -- 1) DEFAULT_VALUE has to be validated against HR_LOOKUPS for lookup_type
1299 -- equal to specified in LOOKUP_TYPE field (active as of current date),
1300 -- if not null
1301 -- 2) DEFAULT_VALUE must lie between MIN_VALUE and MAX_VALUE (if LOOKUP_TYPE is
1302 -- null) and if DEFAULT_VALUE does not lie between MIN_VALUE and MAX_VALUE
1303 -- then depending on whether WARNING_OR_ERROR is 'E', an error must be
1304 -- raised else just a warning must be issued
1305 -- ----------------------------------------------------------------------------
1306 PROCEDURE chk_default_value
1307 ( p_default_value in varchar2
1308 ,p_lookup_type in varchar2
1309 ,p_value_set_id in number
1310 ,p_min_value in varchar2
1311 ,p_max_value in varchar2
1312 ,p_warning_or_error in varchar2
1313 ,p_effective_date in date
1314 ,p_default_val_warning out nocopy boolean
1315 )
1316 IS
1317 --
1318 cursor csr_lookup is
1319 select 'X' from hr_lookups
1320 where upper(lookup_code) = nvl(upper(p_default_value),lookup_code)
1321 and lookup_type = p_lookup_type
1322 and enabled_flag = 'Y'
1323 and p_effective_date between
1324 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
1325 --
1326 l_dummy varchar(1);
1327 l_proc varchar2(72) := g_package||'chk_default_value';
1328 --
1329 BEGIN
1330 --
1331 hr_utility.set_location(' Entering:'|| l_proc, 10);
1332 --
1333 if p_default_value is not null then
1334 --
1335 if p_lookup_type is not null then
1336 --
1337 hr_utility.set_location(l_proc, 20);
1338 --
1339 open csr_lookup;
1340 fetch csr_lookup into l_dummy;
1341 --
1342 if csr_lookup%notfound then
1343 --
1344 hr_utility.set_location(l_proc, 30);
1345 --
1346 close csr_lookup;
1347 --
1348 fnd_message.set_name('PAY', 'PAY_6171_INPVAL_NO_LOOKUP');
1349 fnd_message.raise_error;
1350 --
1351 end if;
1352 --
1353 close csr_lookup;
1354 --
1355 elsif p_value_set_id is not null then
1356 --
1357 hr_utility.set_location(l_proc,40);
1358 if pay_input_values_pkg.decode_vset_value (
1359 p_value_set_id,
1360 p_default_value ) is null then
1361 --
1362 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1363 fnd_message.set_token('PROCEDURE', l_proc);
1364 fnd_message.set_token('STEP','40');
1365 fnd_message.raise_error;
1366 --
1367 end if;
1368 --
1369 elsif (p_min_value is not null or p_max_value is not null) and
1370 (p_default_value <= nvl(p_min_value,p_default_value) or
1371 p_default_value >= nvl(p_max_value,p_default_value )) then
1372 --
1373 if p_warning_or_error = 'E' then
1374 --
1375 hr_utility.set_location(l_proc,50);
1376 --
1377 fnd_message.set_name('PAY', 'PAY_6303_INPUT_VALUE_OUT_RANGE');
1378 fnd_message.raise_error;
1379 --
1380 elsif p_warning_or_error = 'W' then
1381 --
1382 hr_utility.set_location(l_proc,60);
1383 --
1384 p_default_val_warning := TRUE ;
1385 --
1386 end if;
1387 --
1388 end if;
1389 --
1390 end if;
1391 --
1392 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1393 --
1394 END chk_default_value;
1395
1396 --
1397 -- ----------------------------------------------------------------------------
1398 -- |----------------------<chk_upd_default_value >----------------------------|
1399 -- ----------------------------------------------------------------------------
1400 --
1401 -- Description:
1402 -- This procedure will do the following validations
1403 -- 1) DEFAULT_VALUE has to be validated against HR_LOOKUPS for lookup_type
1404 -- equal to specified in LOOKUP_TYPE field (active as of current date),
1405 -- if not null
1406 -- 2) DEFAULT_VALUE must lie between MIN_VALUE and MAX_VALUE (if LOOKUP_TYPE
1407 -- is null) and if DEFAULT_VALUE does not lie between MIN_VALUE and
1408 -- MAX_VALUE then depending on whether WARNING_OR_ERROR is 'E', an
1409 -- error must be raised else just a warning must be issued
1410 -- ----------------------------------------------------------------------------
1411 PROCEDURE chk_upd_default_value
1412 ( p_default_value in varchar2
1413 ,p_lookup_type in varchar2
1414 ,p_value_set_id in number
1415 ,p_min_value in varchar2
1416 ,p_max_value in varchar2
1417 ,p_warning_or_error in varchar2
1418 ,p_effective_date in date
1419 ,p_default_val_warning out nocopy boolean
1420 )
1421 IS
1422 --
1423 cursor csr_lookup is
1424 select 'X'
1425 from hr_lookups
1426 where upper(lookup_code) = nvl(upper(p_default_value),lookup_code)
1427 and lookup_type = decode(p_lookup_type,
1428 hr_api.g_varchar2, pay_ivl_shd.g_old_rec.lookup_type,
1429 p_lookup_type)
1430 and enabled_flag = 'Y'
1431 and p_effective_date between nvl(start_date_active,hr_api.g_sot)
1432 and nvl(end_date_active,hr_api.g_eot);
1433 --
1434 l_dummy varchar(1);
1435 l_value_set_id number;
1436 l_proc varchar2(72) := g_package||'chk_upd_default_value';
1437 --
1438 BEGIN
1439 --
1440 hr_utility.set_location(' Entering:'|| l_proc, 10);
1441 --
1442 if p_lookup_type is not null or (
1443 pay_ivl_shd.g_old_rec.lookup_type is not null and
1444 p_lookup_type = hr_api.g_varchar2
1445 ) then
1446 --
1447 open csr_lookup;
1448 fetch csr_lookup into l_dummy;
1449 --
1450 if csr_lookup%notfound then
1451 --
1452 close csr_lookup;
1453 --
1454 fnd_message.set_name('PAY', 'PAY_6171_INPVAL_NO_LOOKUP');
1455 fnd_message.raise_error;
1456 --
1457 end if;
1458 --
1459 close csr_lookup;
1460 --
1461 elsif p_value_set_id is not null and p_default_value is not null and
1462 p_default_value <> hr_api.g_varchar2 and (
1463 p_value_set_id <> hr_api.g_number or (
1464 pay_ivl_shd.g_old_rec.value_set_id is not null and
1465 p_value_set_id = hr_api.g_number
1466 )
1467 ) then
1468 --
1469 if p_value_set_id = hr_api.g_number then
1470 --
1471 -- Value set id is not changing, use the old one
1472 --
1473 l_value_set_id := pay_ivl_shd.g_old_rec.value_set_id;
1474 --
1475 else
1476 --
1477 -- Value set id is changing, use the new one
1478 --
1479 l_value_set_id := p_value_set_id;
1480 --
1481 end if;
1482 --
1483 hr_utility.set_location(l_proc,20);
1484 --
1485 -- Validate the default value using l_value_set_id
1486 --
1487 if pay_input_values_pkg.decode_vset_value (
1488 l_value_set_id,
1489 p_default_value ) is null then
1490 --
1491 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1492 fnd_message.set_token('PROCEDURE', l_proc);
1493 fnd_message.set_token('STEP','20');
1494 fnd_message.raise_error;
1495 --
1496 end if;
1497 --
1498 elsif pay_ivl_shd.g_old_rec.min_value is not null
1499 or pay_ivl_shd.g_old_rec.max_value is not null then
1500 --
1501 if p_min_value <> pay_ivl_shd.g_old_rec.min_value OR p_max_value <> pay_ivl_shd.g_old_rec.max_value then
1502 --
1503 if (p_default_value <= p_min_value or
1504 p_default_value >= p_max_value) then
1505 --
1506 if p_warning_or_error = 'E' then
1507 --
1508 fnd_message.set_name('PAY', 'PAY_6303_INPUT_VALUE_OUT_RANGE');
1509 fnd_message.raise_error;
1510 --
1511 elsif p_warning_or_error = 'W' then
1512 --
1513 p_default_val_warning := TRUE ;
1514 --
1515 end if;
1516 --
1517 end if;
1518 --
1519 else
1520 --
1521 if (p_default_value <= pay_ivl_shd.g_old_rec.min_value or
1522 p_default_value >= pay_ivl_shd.g_old_rec.max_value) then
1523 --
1524 if p_warning_or_error = 'E' then
1525 --
1526 fnd_message.set_name('PAY', 'PAY_6303_INPUT_VALUE_OUT_RANGE');
1527 fnd_message.raise_error;
1528 --
1529 elsif p_warning_or_error = 'W' then
1530 --
1531 p_default_val_warning := TRUE ;
1532 --
1533 end if;
1534 --
1535 end if;
1536 --
1537 end if;
1538 --
1539 end if;
1540 --
1541 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1542 --
1543 END chk_upd_default_value;
1544
1545
1546 --
1547 -- ----------------------------------------------------------------------------
1548 -- |----------------------<chk_max_min_value >--------------------------------|
1549 -- ----------------------------------------------------------------------------
1550 --
1551 -- Description:
1552 -- This procedure will check if MIN_VALUE is less than or equal to MAX_VALUE
1553 --
1554 -- ----------------------------------------------------------------------------
1555 PROCEDURE chk_max_min_value
1556 (p_max_value in varchar2
1557 ,p_min_value in varchar2
1558 ,p_warning_or_error in varchar2
1559 ,p_lookup_type in varchar2
1560 ,p_min_max_warning out nocopy boolean
1561 )
1562 IS
1563 l_proc varchar2(72) := g_package||'chk_max_min_value';
1564 BEGIN
1565 hr_utility.set_location(' Entering:'|| l_proc, 10);
1566 if p_min_value > p_max_value then
1567 if p_warning_or_error = 'E' then
1568 fnd_message.set_name('PAY', 'HR_51975_ALL_MAX_MORE_MIN');
1569 fnd_message.raise_error;
1570 elsif p_warning_or_error = 'W' then
1571 p_min_max_warning := TRUE;
1572 end if;
1573 end if;
1574 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1575 END chk_max_min_value;
1576 --
1577 -- ----------------------------------------------------------------------------
1578 -- |----------------------<chk_warning_or_error>------------------------------|
1579 -- ----------------------------------------------------------------------------
1580 --
1581 -- Description:
1582 -- This procedure will do the following validations
1583 -- 1) WARNING_OR_ERROR must be set in case a MIN_VALUE/MAX_VALUE/FORMULA_ID
1584 -- is specified
1585 -- 2) Validate with HR_LOOKUPS having LOOKUP_TYPE as 'WARNING_ERROR'
1586 -- and active as of current date
1587 -- ----------------------------------------------------------------------------
1588 PROCEDURE chk_warning_or_error
1589 (p_warning_or_error in varchar2
1590 ,p_lookup_type in varchar2
1591 ,p_min_value in varchar2
1592 ,p_max_value in varchar2
1593 ,p_formula_id in varchar2
1594 ,p_effective_date in date
1595 )
1596 IS
1597 cursor csr_lookup is
1598 select 'X' from hr_lookups
1599 where upper(lookup_code) = nvl(upper(p_warning_or_error),lookup_code)
1600 and lookup_type = 'WARNING_ERROR'
1601 and enabled_flag = 'Y'
1602 and p_effective_date between
1603 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
1604
1605 l_dummy varchar2(1);
1606 l_proc varchar2(72) := g_package||'chk_warning_or_error';
1607 BEGIN
1608
1609 hr_utility.set_location(' Entering:'|| l_proc, 10);
1610
1611 if (p_min_value is not null or p_max_value is not null or p_formula_id
1612 is not null)
1613 and p_warning_or_error is null then
1614 fnd_message.set_name('PAY','PAY_34121_WARN_ERROR_MAND');
1615 fnd_message.raise_error;
1616 elsif (p_min_value is null and p_max_value is null) and p_formula_id is null
1617 and p_warning_or_error is not null then
1618 fnd_message.set_name('PAY','PAY_6908_INPVAL_ERROR_VAL');
1619 fnd_message.raise_error;
1620 end if;
1621
1622 open csr_lookup;
1623 fetch csr_lookup into l_dummy;
1624 if csr_lookup%notfound then
1625 close csr_lookup;
1626 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1627 fnd_message.raise_error;
1628 end if;
1629 close csr_lookup;
1630
1631 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1632 END chk_warning_or_error;
1633
1634 --
1635 -- ----------------------------------------------------------------------------
1636 -- |----------------------<chk_other_insert_val>------------------------------|
1637 -- ----------------------------------------------------------------------------
1638 --
1639 -- Description:
1640 -- This procedure will do the following validations
1641 -- 1) Only 15 input values can be created for each element
1642 -- 2) Insert should not be allowed if there exists
1643 -- element entries or pay run results for this element type
1644 -- 3) Any of the following combinations can be entered at a time,
1645 -- 1) FORMULA_ID and DEFAULT_VALUE
1646 -- 2) LOOKUP_TYPE and DEFAULT_VALUE
1647 -- 3) DEFAULT_VALUE, MIN_VALUE and MAX_VALUE
1648 -- 4) VALUE_SET_ID and DEFAULT_VALUE
1649 --
1650 -- ----------------------------------------------------------------------------
1651 PROCEDURE chk_other_insert_val
1652 (p_element_type_id in number
1653 ,p_formula_id in number
1654 ,p_lookup_type in varchar2
1655 ,p_value_set_id in number
1656 ,p_min_value in varchar2
1657 ,p_max_value in varchar2
1658 ,p_start_date in date
1659 ,p_end_date in date
1660 ,p_pay_basis_warning out nocopy boolean
1661 )
1662 IS
1663 cursor csr_run_results is
1664 select 1
1665 from dual
1666 where exists
1667 (select /*+ INDEX(PAYROLL PAY_PAYROLL_ACTIONS_PK)
1668 INDEX(ASSIGN PAY_ASSIGNMENT_ACTIONS_PK) */ 1
1669 from pay_run_results RUN,
1670 pay_payroll_actions PAYROLL,
1671 pay_assignment_actions ASSIGN
1672 where run.element_type_id = p_element_type_id
1673 and assign.assignment_action_id = run.assignment_action_id
1674 and assign.payroll_action_id = payroll.payroll_action_id
1675 and payroll.effective_date between p_start_date
1676 and p_end_date);
1677
1678 cursor csr_pay_basis is
1679 select 1 pay
1680 from per_pay_bases
1681 where input_value_id in ( select input_value_id
1682 from pay_input_values_f
1683 where element_type_id = p_element_type_id);
1684
1685 l_dummy varchar2(1);
1686 l_proc varchar2(72) := g_package||'chk_other_insert_val';
1687
1688 BEGIN
1689
1690 hr_utility.set_location(' Entering:'|| l_proc, 10);
1691 if pay_input_values_pkg.no_of_input_values(p_element_type_id) >= 15 then
1692 fnd_message.set_name('PAY','HR_7124_INPVAL_MAX_ENTRIES');
1693 fnd_message.raise_error;
1694 end if;
1695
1696 open csr_run_results;
1697 fetch csr_run_results into l_dummy;
1698 if csr_run_results%found
1699 or pay_element_types_pkg.element_entries_exist (
1700 p_element_type_id,
1701 p_error_if_true => TRUE
1702 ) then
1703 fnd_message.set_name('PAY','PAY_34123_CANNOT_CR_INP_VAL');
1704 fnd_message.raise_error;
1705 end if;
1706 close csr_run_results;
1707 if p_formula_id is not null and (
1708 p_lookup_type is not null or
1709 p_value_set_id is not null or
1710 p_min_value is not null or
1711 p_max_value is not null
1712 ) then
1713 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1714 fnd_message.raise_error;
1715 elsif p_lookup_type is not null and (
1716 p_formula_id is not null or
1717 p_value_set_id is not null or
1718 p_min_value is not null or
1719 p_max_value is not null
1720 ) then
1721 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1722 fnd_message.raise_error;
1723 elsif p_value_set_id is not null and (
1724 p_formula_id is not null or
1725 p_lookup_type is not null or
1726 p_min_value is not null or
1727 p_max_value is not null
1728 ) then
1729 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1730 fnd_message.raise_error;
1731 elsif (p_min_value is not null or p_max_value is not null) and (
1732 p_formula_id is not null or
1733 p_lookup_type is not null or
1734 p_value_set_id is not null
1735 ) then
1736 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1737 fnd_message.raise_error;
1738 end if;
1739
1740 for rec in csr_pay_basis
1741 loop
1742 if rec.pay = '1' then
1743 p_pay_basis_warning := TRUE;
1744 end if;
1745 end loop;
1746
1747 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1748
1749 END chk_other_insert_val;
1750 --
1751 -- ----------------------------------------------------------------------------
1752 -- |----------------------<chk_other_upd_val>------------------------------|
1753 -- ----------------------------------------------------------------------------
1754 --
1755 -- Description:
1756 -- This procedure will check only one of the following combinations can be
1757 -- entered at a time,
1758 -- 1) FORMULA_ID and DEFAULT_VALUE
1759 -- 2) LOOKUP_TYPE and DEFAULT_VALUE
1760 -- 3) DEFAULT_VALUE, MIN_VALUE and MAX_VALUE
1761 -- 4) VALUE_SET_ID and DEFAULT_VALUE
1762 --
1763 -- ----------------------------------------------------------------------------
1764 PROCEDURE chk_other_upd_val
1765 (p_formula_id in number
1766 ,p_lookup_type in varchar2
1767 ,p_value_set_id in number
1768 ,p_min_value in varchar2
1769 ,p_max_value in varchar2
1770 )
1771 IS
1772
1773 BEGIN
1774
1775 if pay_ivl_shd.g_old_rec.formula_id is not null and
1776 p_formula_id is not null and (
1777 p_lookup_type is not null or
1778 p_value_set_id is not null or
1779 p_min_value is not null or
1780 p_max_value is not null
1781 ) then
1782
1783 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1784 fnd_message.raise_error;
1785
1786 elsif pay_ivl_shd.g_old_rec.lookup_type is not null and
1787 p_lookup_type is not null and (
1788 p_formula_id is not null or
1789 p_value_set_id is not null or
1790 p_min_value is not null or
1791 p_max_value is not null
1792 ) then
1793
1794 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1795 fnd_message.raise_error;
1796
1797 elsif pay_ivl_shd.g_old_rec.value_set_id is not null and
1798 p_value_set_id is not null and (
1799 p_formula_id is not null or
1800 p_lookup_type is not null or
1801 p_min_value is not null or
1802 p_max_value is not null
1803 ) then
1804
1805 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1806 fnd_message.raise_error;
1807
1808 elsif (pay_ivl_shd.g_old_rec.min_value is not null and
1809 p_min_value is not null or
1810 pay_ivl_shd.g_old_rec.max_value is not null and
1811 p_max_value is not null) and (
1812 p_formula_id is not null or
1813 p_lookup_type is not null or
1814 p_value_set_id is not null
1815 ) then
1816
1817 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1818 fnd_message.raise_error;
1819
1820 end if;
1821
1822 END chk_other_upd_val;
1823
1824 --
1825 -- ----------------------------------------------------------------------------
1826 -- |----------------------<chk_raise_warning>------------------------------|
1827 -- ----------------------------------------------------------------------------
1828 --
1829 -- Description:
1830 -- This procedure will set p_link_inp_val_warning to TRUE if name or
1831 -- default value or lookup type or min value or max value or warning_or_error
1832 -- is updated
1833 --
1834 -- ----------------------------------------------------------------------------
1835 PROCEDURE chk_raise_warning
1836 (p_lookup_type in varchar2
1837 ,p_name in varchar2
1838 ,p_default_value in varchar2
1839 ,p_min in varchar2
1840 ,p_max in varchar2
1841 ,p_warning_or_error in varchar2
1842 ,p_link_inp_val_warning IN OUT NOCOPY boolean)
1843 IS
1844
1845 l_proc varchar2(72) := g_package||'chk_raise_warning';
1846
1847 BEGIN
1848
1849 hr_utility.set_location(' Entering:'|| l_proc, 10);
1850
1851
1852 if p_lookup_type <> pay_ivl_shd.g_old_rec.lookup_type
1853 OR p_default_value <> pay_ivl_shd.g_old_rec.default_value OR p_min <> pay_ivl_shd.g_old_rec.min_value
1854 OR p_max <> pay_ivl_shd.g_old_rec.max_value OR p_warning_or_error <> pay_ivl_shd.g_old_rec.warning_or_error
1855 then
1856 p_link_inp_val_warning := TRUE;
1857 end if;
1858
1859 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1860
1861 END chk_raise_warning;
1862
1863 --
1864 -- ----------------------------------------------------------------------------
1865 -- |----------------------<chk_delete_allowed>------------------------------|
1866 -- ----------------------------------------------------------------------------
1867 --
1868 -- Description:
1869 -- This procedure will do the following validations
1870 -- a) Not allowed for all delete modes if the element has
1871 -- benefit contributions_used flag as 'Y'
1872 -- and name is 'Coverage' or 'EE Contr' or 'ER Contr', except when
1873 -- the element itself is deleted.
1874 -- b) Not allowed for date effective delete for the following scenarios
1875 -- 1. Formula result rules apply to this input value (either as
1876 -- indirect or update recurring) after the new end date and
1877 -- the rules are not self-referential.
1878 -- 2. An absence exists for a range of time outside of
1879 -- the new date-effective lifetime of the input value.
1880 -- c) Not allowed for ZAP mode for the following scenarios
1881 -- 1. Element entry values for the input value exist.
1882 -- 2. Run result values for the input value exist.
1883 -- 3. Compiled formulae use db items of the input value
1884 -- 4. Formula result rules refer to the input value, and those
1885 -- rules are not self-referential.
1886 -- 5. Absence attendance types exist for this input value.
1887 -- 6. BackPay rules exist for this input value.
1888 -- 7. The input value is PAY_VALUE and links exist for the
1889 -- element type with a costable type of 'Distributed'.
1890 -- 8. The input value is used by a salary basis.
1891 -- 9. The input value is used by an accrual plan.
1892 -- 10. The input value is used by a net calculation rule.
1893 --
1894 -- ----------------------------------------------------------------------------
1895 PROCEDURE chk_delete_allowed
1896 (p_datetrack_mode in varchar2
1897 ,p_input_value_id in number
1898 ,p_element_type_id in number
1899 ,p_effective_date in date
1900 ,p_start_date in date
1901 ,p_end_date in date)
1902 IS
1903 cursor csr_classification is
1904
1905 select contributions_used
1906 from ben_benefit_classifications
1907 where benefit_classification_id in ( select distinct benefit_classification_id
1908 from pay_element_types_f
1909 where element_type_id = p_element_type_id
1910 and p_effective_date between effective_start_date
1911 and effective_end_date);
1912
1913 cursor csr_name is
1914 select 'X'
1915 from pay_input_values_f
1916 where input_value_id = p_input_value_id
1917 and upper(name) in ('COVERAGE','EE CONTR','ER CONTR');
1918
1919 l_dummy varchar2(100);
1920 l_name varchar2(100);
1921
1922 l_proc varchar2(72) := g_package||'chk_delete_allowed';
1923
1924 BEGIN
1925
1926 hr_utility.set_location(' Entering:'|| l_proc, 10);
1927
1928 open csr_classification;
1929 fetch csr_classification into l_dummy;
1930 close csr_classification;
1931
1932 open csr_name;
1933 fetch csr_name into l_name;
1934
1935 if csr_name%found and l_dummy = 'Y' then
1936 close csr_name;
1937 fnd_message.set_name('PAY','PAY_34129_INPVAL_NAME_DEL');
1938 fnd_message.raise_error;
1939 end if;
1940 close csr_name;
1941
1942 if pay_input_values_pkg.deletion_allowed (
1943 p_input_value_id,
1944 p_datetrack_mode ,
1945 p_start_date ,
1946 p_end_date ,
1947 TRUE) then
1948 null;
1949 end if;
1950
1951 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1952
1953 END chk_delete_allowed;
1954
1955 --
1956 -- ----------------------------------------------------------------------------
1957 -- |---------------------------< insert_validate >----------------------------|
1958 -- ----------------------------------------------------------------------------
1959 Procedure insert_validate
1960 (p_rec in pay_ivl_shd.g_rec_type
1961 ,p_effective_date in date
1962 ,p_datetrack_mode in varchar2
1963 ,p_validation_start_date in date
1964 ,p_validation_end_date in date
1965 ,p_default_val_warning out nocopy boolean
1966 ,p_min_max_warning out nocopy boolean
1967 ,p_pay_basis_warning out nocopy boolean
1968 ,p_formula_warning out nocopy boolean
1969 ,p_assignment_id_warning out nocopy boolean
1970 ,p_formula_message out nocopy varchar2
1971 ) is
1972 --
1973 l_proc varchar2(72) := g_package||'insert_validate';
1974 --
1975 Begin
1976 hr_utility.set_location('Entering:'||l_proc, 5);
1977 --
1978 -- Call all supporting business operations
1979 --
1980 --
1981 chk_startup_action(true
1982 ,p_rec.business_group_id
1983 ,p_rec.legislation_code
1984 );
1985 IF hr_startup_data_api_support.g_startup_mode
1986 NOT IN ('GENERIC','STARTUP') THEN
1987 --
1988 -- Validate Important Attributes
1989 --
1990 hr_api.validate_bus_grp_id
1991 (p_business_group_id => p_rec.business_group_id
1992 ,p_associated_column1 => pay_ivl_shd.g_tab_nam
1993 || '.BUSINESS_GROUP_ID');
1994 --
1995 -- after validating the set of important attributes,
1996 -- if Multiple Message Detection is enabled and at least
1997 -- one error has been found then abort further validation.
1998 --
1999 hr_multi_message.end_validation_set;
2000 END IF;
2001
2002 pay_ivl_bus.chk_other_insert_val
2003 (p_element_type_id => p_rec.element_type_id
2004 ,p_formula_id => p_rec.formula_id
2005 ,p_lookup_type => p_rec.lookup_type
2006 ,p_value_set_id => p_rec.value_set_id
2007 ,p_min_value => p_rec.min_value
2008 ,p_max_value => p_rec.max_value
2009 ,p_start_date => p_validation_start_date
2010 ,p_end_date => p_validation_end_date
2011 ,p_pay_basis_warning => p_pay_basis_warning
2012 );
2013
2014 pay_ivl_bus.chk_lookup_type
2015 (p_lookup_type => p_rec.lookup_type
2016 ,p_uom => p_rec.uom
2017 ,p_effective_date => p_effective_date);
2018
2019 pay_ivl_bus.chk_formula_id
2020 ( p_business_group_id => p_rec.business_group_id
2021 ,p_legislation_code => p_rec.legislation_code
2022 ,p_effective_date => p_effective_date
2023 ,p_formula_id => p_rec.formula_id
2024 );
2025
2026 pay_ivl_bus.chk_formula_validation
2027 ( p_default_value => p_rec.default_value
2028 ,p_warning_or_error => p_rec.warning_or_error
2029 ,p_effective_date => p_effective_date
2030 ,p_formula_id => p_rec.formula_id
2031 ,p_business_group_id => p_rec.business_group_id
2032 ,p_default_formula_warning => p_formula_warning
2033 ,p_assignment_id_warning => p_assignment_id_warning
2034 ,p_formula_message => p_formula_message
2035 );
2036
2037 pay_ivl_bus.chk_value_set_id (
2038 p_value_set_id => p_rec.value_set_id,
2039 p_uom => p_rec.uom
2040 );
2041
2042 pay_ivl_bus.chk_hot_default_flag
2043 (p_mandatory_flag => p_rec.mandatory_flag
2044 ,p_hot_default_flag => p_rec.hot_default_flag
2045 );
2046
2047 pay_ivl_bus.chk_name
2048 (p_name => p_rec.name
2049 ,p_element_type_id => p_rec.element_type_id
2050 ,p_uom => p_rec.uom
2051 );
2052
2053 pay_ivl_bus.chk_uom
2054 (p_element_type_id => p_rec.element_type_id
2055 ,p_uom => p_rec.uom
2056 ,p_effective_date => p_effective_date );
2057
2058 pay_ivl_bus.chk_default_value
2059 ( p_default_value => p_rec.default_value
2060 ,p_lookup_type => p_rec.lookup_type
2061 ,p_value_set_id => p_rec.value_set_id
2062 ,p_min_value => p_rec.min_value
2063 ,p_max_value => p_rec.max_value
2064 ,p_warning_or_error => p_rec.warning_or_error
2065 ,p_effective_date => p_effective_date
2066 ,p_default_val_warning => p_default_val_warning
2067 );
2068
2069 pay_ivl_bus.chk_max_min_value
2070 (p_max_value => p_rec.max_value
2071 ,p_min_value => p_rec.min_value
2072 ,p_warning_or_error => p_rec.warning_or_error
2073 ,p_lookup_type => p_rec.lookup_type
2074 ,p_min_max_warning => p_min_max_warning
2075 );
2076
2077 pay_ivl_bus.chk_warning_or_error
2078 (p_warning_or_error => p_rec.warning_or_error
2079 ,p_lookup_type => p_rec.lookup_type
2080 ,p_min_value => p_rec.min_value
2081 ,p_max_value => p_rec.max_value
2082 ,p_formula_id => p_rec.formula_id
2083 ,p_effective_date => p_effective_date
2084 );
2085 --
2086 --
2087 -- Validate Dependent Attributes
2088 --
2089 --
2090 hr_utility.set_location(' Leaving:'||l_proc, 10);
2091 --
2092 End insert_validate;
2093 --
2094 -- ----------------------------------------------------------------------------
2095 -- |---------------------------< update_validate >----------------------------|
2096 -- ----------------------------------------------------------------------------
2097 Procedure update_validate
2098 (p_rec in pay_ivl_shd.g_rec_type
2099 ,p_effective_date in date
2100 ,p_datetrack_mode in out nocopy varchar2
2101 ,p_validation_start_date in date
2102 ,p_validation_end_date in date
2103 ,p_default_val_warning out nocopy boolean
2104 ,p_min_max_warning out nocopy boolean
2105 ,p_link_inp_val_warning out nocopy boolean
2106 ,p_pay_basis_warning out nocopy boolean
2107 ,p_formula_warning out nocopy boolean
2108 ,p_assignment_id_warning out nocopy boolean
2109 ,p_formula_message out nocopy varchar2
2110 ) is
2111 --
2112 l_proc varchar2(72) := g_package||'update_validate';
2113 l_default_formula_warning boolean;
2114 --
2115 Begin
2116 hr_utility.set_location('Entering:'||l_proc, 5);
2117 --
2118 -- Call all supporting business operations
2119 --
2120 --
2121 chk_startup_action(false
2122 ,p_rec.business_group_id
2123 ,p_rec.legislation_code
2124 );
2125 IF hr_startup_data_api_support.g_startup_mode
2126 NOT IN ('GENERIC','STARTUP') THEN
2127 --
2128 -- Validate Important Attributes
2129 --
2130
2131 hr_api.validate_bus_grp_id
2132 (p_business_group_id => p_rec.business_group_id
2133 ,p_associated_column1 => pay_ivl_shd.g_tab_nam
2134 || '.BUSINESS_GROUP_ID');
2135 --
2136 -- After validating the set of important attributes,
2137 -- if Multiple Message Detection is enabled and at least
2138 -- one error has been found then abort further validation.
2139 --
2140 hr_multi_message.end_validation_set;
2141
2142 END IF;
2143
2144 pay_ivl_bus.chk_other_upd_val
2145 (p_formula_id => p_rec.formula_id
2146 ,p_lookup_type => p_rec.lookup_type
2147 ,p_value_set_id => p_rec.value_set_id
2148 ,p_min_value => p_rec.min_value
2149 ,p_max_value => p_rec.max_value);
2150
2151 pay_ivl_bus.chk_upd_generate_db_items_flag
2152 (p_datetrack_mode => p_datetrack_mode
2153 ,p_generate_db_items_flag => p_rec.generate_db_items_flag);
2154
2155 pay_ivl_bus.chk_upd_name
2156 (p_datetrack_mode => p_datetrack_mode
2157 ,p_name => p_rec.name
2158 ,p_effective_date => p_effective_date
2159 ,p_element_type_id => p_rec.element_type_id);
2160
2161 pay_ivl_bus.chk_upd_uom
2162 (p_datetrack_mode => p_datetrack_mode
2163 ,p_uom => p_rec.uom
2164 ,p_input_value_id => p_rec.input_value_id
2165 ,p_effective_date => p_effective_date);
2166
2167 pay_ivl_bus.chk_upd_def_value_null
2168 (p_input_value_id => p_rec.input_value_id
2169 ,p_effective_date => p_effective_date
2170 ,p_default_value => p_rec.default_value
2171 ,p_pay_basis_warning => p_pay_basis_warning);
2172
2173 pay_ivl_bus.chk_upd_mand_flag
2174 (p_mandatory_flag => p_rec.mandatory_flag
2175 );
2176
2177 pay_ivl_bus.chk_hot_default_flag
2178 (p_mandatory_flag => p_rec.mandatory_flag
2179 ,p_hot_default_flag => p_rec.hot_default_flag);
2180
2181 pay_ivl_bus.chk_lookup_type
2182 (p_lookup_type => p_rec.lookup_type
2183 ,p_uom => p_rec.uom
2184 ,p_effective_date => p_effective_date);
2185
2186 pay_ivl_bus.chk_formula_id
2187 ( p_business_group_id => p_rec.business_group_id
2188 ,p_legislation_code => p_rec.legislation_code
2189 ,p_effective_date => p_effective_date
2190 ,p_formula_id => p_rec.formula_id
2191 );
2192
2193 pay_ivl_bus.chk_formula_validation
2194 ( p_default_value => p_rec.default_value
2195 ,p_warning_or_error => p_rec.warning_or_error
2196 ,p_effective_date => p_effective_date
2197 ,p_formula_id => p_rec.formula_id
2198 ,p_business_group_id => p_rec.business_group_id
2199 ,p_default_formula_warning => p_formula_warning
2200 ,p_assignment_id_warning => p_assignment_id_warning
2201 ,p_formula_message => p_formula_message
2202 );
2203
2204 pay_ivl_bus.chk_value_set_id (
2205 p_value_set_id => p_rec.value_set_id,
2206 p_uom => p_rec.uom
2207 );
2208
2209 pay_ivl_bus.chk_upd_display_sequence
2210 (p_element_type_id => p_rec.element_type_id
2211 ,p_name => p_rec.name
2212 ,p_display_sequence => p_rec.display_sequence );
2213
2214 pay_ivl_bus.chk_max_min_value
2215 (p_max_value => p_rec.max_value
2216 ,p_min_value => p_rec.min_value
2217 ,p_warning_or_error => p_rec.warning_or_error
2218 ,p_lookup_type => p_rec.lookup_type
2219 ,p_min_max_warning => p_min_max_warning
2220 );
2221
2222 pay_ivl_bus.chk_warning_or_error
2223 (p_warning_or_error => p_rec.warning_or_error
2224 ,p_lookup_type => p_rec.lookup_type
2225 ,p_min_value => p_rec.min_value
2226 ,p_max_value => p_rec.max_value
2227 ,p_formula_id => p_rec.formula_id
2228 ,p_effective_date => p_effective_date
2229 );
2230
2231 pay_ivl_bus.chk_raise_warning
2232 (p_lookup_type => p_rec.lookup_type
2233 ,p_name => p_rec.name
2234 ,p_default_value => p_rec.default_value
2235 ,p_min => p_rec.min_value
2236 ,p_max => p_rec.max_value
2237 ,p_warning_or_error => p_rec.warning_or_error
2238 ,p_link_inp_val_warning => p_link_inp_val_warning);
2239
2240 if p_rec.default_value <> hr_api.g_varchar2 then
2241 pay_ivl_bus.chk_upd_default_value
2242 ( p_default_value => p_rec.default_value
2243 ,p_lookup_type => p_rec.lookup_type
2244 ,p_value_set_id => p_rec.value_set_id
2245 ,p_min_value => p_rec.min_value
2246 ,p_max_value => p_rec.max_value
2247 ,p_warning_or_error => p_rec.warning_or_error
2248 ,p_effective_date => p_effective_date
2249 ,p_default_val_warning => p_default_val_warning
2250 );
2251 end if;
2252 --
2253 --
2254 -- Validate Dependent Attributes
2255 --
2256 -- Call the datetrack update integrity operation
2257 --
2258 dt_update_validate
2259 (p_element_type_id => p_rec.element_type_id
2260 ,p_datetrack_mode => p_datetrack_mode
2261 ,p_validation_start_date => p_validation_start_date
2262 ,p_validation_end_date => p_validation_end_date
2263 );
2264 --
2265 chk_non_updateable_args
2266 (p_effective_date => p_effective_date
2267 ,p_rec => p_rec
2268 );
2269 --
2270 --
2271 hr_utility.set_location(' Leaving:'||l_proc, 10);
2272 End update_validate;
2273 --
2274 -- ----------------------------------------------------------------------------
2275 -- |---------------------------< delete_validate >----------------------------|
2276 -- ----------------------------------------------------------------------------
2277 Procedure delete_validate
2278 (p_rec in pay_ivl_shd.g_rec_type
2279 ,p_effective_date in date
2280 ,p_datetrack_mode in varchar2
2281 ,p_validation_start_date in date
2282 ,p_validation_end_date in date
2283
2284 ) is
2285 --
2286 l_proc varchar2(72) := g_package||'delete_validate';
2287 --
2288 Begin
2289 hr_utility.set_location('Entering:'||l_proc, 5);
2290 --
2291 --
2292 chk_startup_action(false
2293 ,pay_ivl_shd.g_old_rec.business_group_id
2294 ,pay_ivl_shd.g_old_rec.legislation_code
2295 );
2296 IF hr_startup_data_api_support.g_startup_mode
2297 NOT IN ('GENERIC','STARTUP') THEN
2298 --
2299 -- Validate Important Attributes
2300 --
2301 --
2302 -- After validating the set of important attributes,
2303 -- if Multiple Message Detection is enabled and at least
2304 -- one error has been found then abort further validation.
2305 --
2306 hr_multi_message.end_validation_set;
2307
2308 END IF;
2309
2310 pay_ivl_bus.chk_delete_allowed
2311 (p_datetrack_mode => p_datetrack_mode
2312 ,p_input_value_id => p_rec.input_value_id
2313 ,p_element_type_id => p_rec.element_type_id
2314 ,p_effective_date => p_effective_date
2315 ,p_start_date => p_validation_start_date
2316 ,p_end_date => p_validation_end_date);
2317
2318
2319 --
2320 -- Call all supporting business operations
2321 --
2322 dt_delete_validate
2323 (p_datetrack_mode => p_datetrack_mode
2324 ,p_validation_start_date => p_validation_start_date
2325 ,p_validation_end_date => p_validation_end_date
2326 ,p_input_value_id => p_rec.input_value_id
2327 );
2328 --
2329 hr_utility.set_location(' Leaving:'||l_proc, 10);
2330 End delete_validate;
2331 --
2332 end pay_ivl_bus;
[Home] [Help]